Introduction:-
Using the below SQL script we can get the Catalog and Non Catalog Po’s details from country wise.
Code:-
SELECT
P.SEGMENT1 “PO Number”
,PL.LINE_NUM “PO Line”,TO_CHAR(P.CREATION_DATE,’DD-MON-RRRR HH24:MI:SS’) “PO Created Date”
,PL.quantity “PO Line Qty”,pl.unit_price “PO LINE PRICE”
,(PL.quantity*pl.unit_price) “PO line Amt”
,(select segment1 from apps.mtl_categories where CATEGORY_ID=pl.CATEGORY_ID and rownum=1)”Po_category”
,rl.DOCUMENT_TYPE_CODE “Document Type”
,(select SEGMENT1 from apps.po_headers_all where po_header_id=pl.contract_id )”Contract Number”
,(select segment1 from apps.po_headers_all where po_header_id=pl.FROM_HEADER_ID )”Document Number”
,decode((select segment1 from apps.po_headers_all where po_header_id=pl.FROM_HEADER_ID ),NULL,’NON CATALOG’,’CATALOG’)”Catalog”
,p.org_id org_id
,(SELECT NVL(short_code,name)FROM apps.hr_operating_units WHERE organization_id = p.org_id) org_code
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
WHERE 1=1
and 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 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 (2210,56,78,98)
AND TRUNC(P.CREATION_DATE) >=’01-JAN-2018′
AND TRUNC(P.CREATION_DATE) <=’11-JAN-2024′
order by poyear
Posted by
Yeswanth
