SELECT R.SEGMENT1
"Req number", Rl.Line_Num "Req line", rl.suggested_vendor_product_code supplier_item, (SELECT DISTINCT Description FROM Apps.Mtl_Categories WHERE Category_Id=Rl.Category_Id ) "Req Line Category Description", (SELECT DISTINCT segment1 ||'.' ||segment2 FROM Apps.Mtl_Categories WHERE Category_Id=rl.category_id ) "Requisition Line Category" , TO_CHAR(R.APPROVED_DATE,'dd-MON-RR HH24:MI:SS') "Req Approved Date", P.SEGMENT1 "PO Number", p.revision_num "Revision number", PL.LINE_NUM "PO Line", TO_CHAR(P.CREATION_DATE,'DD-MON-RRRR HH24:MI:SS') "PO Created Date", pagen.agent_name "PO buyer", p.attribute10 "Contract Type", p.org_id org_id, (SELECT NVL(short_code,name) FROM apps.hr_operating_units WHERE organization_id = p.org_id ) org_code, aps.vendor_name FROM APPS.PO_HEADERS_ALL P, apps.po_lines_all pl, APPS.PO_DISTRIBUTIONS_ALL D, apps.po_agents_v pagen, apps.po_req_distributions_all rd, apps.po_requisition_lines_all rl, apps.po_requisition_headers_all r, apps.ap_suppliers aps WHERE P.PO_HEADER_ID = D.PO_HEADER_ID AND P.PO_HEADER_ID = PL.PO_HEADER_ID AND PL.PO_LINE_ID = D.PO_LINE_ID AND pagen.agent_id = p.agent_id and aps.vendor_id = p.vendor_id AND d.req_distribution_id = rd.distribution_id AND rd.requisition_line_id = rl.requisition_line_id AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID AND P.Org_Id IN (82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 ) AND TRUNC(R.APPROVED_DATE) >='01-NOV-2018' AND TRUNC(R.APPROVED_DATE) <='30-NOV-2018' /*ORDER BY org_id, TO_CHAR(R.APPROVED_DATE,'DD-MON-RRRR HH24:MI:SS')*/ UNION SELECT R.SEGMENT1 "Req number", Rl.Line_Num "Req line", rl.suggested_vendor_product_code supplier_item, (SELECT DISTINCT Description FROM Apps.Mtl_Categories WHERE Category_Id=Rl.Category_Id ) "Req Line Category Description", (SELECT DISTINCT segment1 ||'.' ||segment2 FROM Apps.Mtl_Categories WHERE Category_Id=rl.category_id ) "Requisition Line Category" , TO_CHAR(R.APPROVED_DATE,'dd-MON-RR HH24:MI:SS') "Req Approved Date", NULL "PO Number", NULL "PO Revision number", NULL "PO Line", NULL "PO Created Date", NULL "PO buyer", NULL "Contract Type", r.org_id org_id, (SELECT NVL(short_code,name) FROM apps.hr_operating_units WHERE organization_id = r.org_id ) org_code, aps.vendor_name FROM apps.po_requisition_headers_all r, apps.po_requisition_lines_all rl, apps.po_req_distributions_all rd , apps.ap_suppliers aps WHERE 1=1 AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID AND rd.requisition_line_id = rl.requisition_line_id and rl.vendor_id = aps.vendor_id AND Not exists ( select req_distribution_id from apps.po_distributions_all where req_distribution_id = rd.distribution_id) AND r.Org_Id IN (82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 ) AND TRUNC(R.APPROVED_DATE) >='01-NOV-2018' AND TRUNC(R.APPROVED_DATE) <='30-NOV-2018' /*ORDER BY --org_id, TO_CHAR(R.APPROVED_DATE,'DD-MON-RRRR HH24:MI:SS') */ |
Monday, 25 February 2019
All approved requisition report for the month including PO
Subscribe to:
Post Comments (Atom)
Nice Blog, I saw Somany unknown topics in this Blog. Thanks For sharing,Keep it up.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad