Introduction:
Using the below SQL script we can get the Catalog and Non Catalog Po’s details from country wise.
Script:
SELECT
TO_CHAR(TO_DATE(p.creation_date,’DD-MON-RRRR’), ‘Q’)AS MY_QTR
,TO_CHAR(P.CREATION_DATE,’RRRR’) POYear
,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)
AND TRUNC(P.CREATION_DATE) >=’01-JAN-2018′
AND TRUNC(P.CREATION_DATE) <=’31-MAR-2019′
order by poyear
Got any queries?
Do drop a note by writing us at yeswanth.r@doyensys.com or use the comment section below to ask your questions
