| 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’) */ |
Recent Posts