====REQUISITION QUERY====
SELECT
PRHA.SEGMENT1 REQ_NUM,HOU.NAME OPERATING_UNIT,PRHA.TYPE_LOOKUP_CODE,PAPF.FULL_NAME
,PRHA.CREATION_DATE,PRHA.AUTHORIZATION_STATUS,PRLA.LINE_NUM,MSIB.DESCRIPTION ,MSIB.SEGMENT1 ITEM
,PRLA.UNIT_MEAS_LOOKUP_CODE UOM,PRLA.UNIT_PRICE PRICE,PRLA.QUANTITY,PRLA.NEED_BY_DATE--
,PAPF1.FULL_NAME REQUESTER--,HLA.LOCATION_CODE----,PRLA.SUGGESTED_VENDOR_NAME SUPPLIER
,PRLA.SUGGESTED_VENDOR_LOCATION SITE,PRLA.SUGGESTED_VENDOR_CONTACT CONTACT
FROM
PO_REQUISITION_HEADERS_ALL PRHA,HR_OPERATING_UNITS HOU,PER_ALL_PEOPLE_F PAPF,PER_ALL_PEOPLE_F PAPF1
,MTL_SYSTEM_ITEMS_B MSIB,PO_REQUISITION_LINES_ALL PRLA,HR_LOCATIONS_ALL HLA
WHERE 1=1
AND PRHA.SEGMENT1='14314'
AND PRHA.ORG_ID=HOU.ORGANIZATION_ID
AND PRHA.PREPARER_ID=PAPF.PERSON_ID
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.ORG_ID=MSIB.ORGANIZATION_ID
AND PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PRLA.DELIVER_TO_LOCATION_ID=HLA.LOCATION_ID
AND PRLA.TO_PERSON_ID=PAPF1.PERSON_ID
=== RFQ’s QUERY ===
SELECT
HAOU.NAME OPERATING_UNIT,PHA.SEGMENT1 NUM,PLLA.SHIPMENT_TYPE,INITCAP(PHA.QUOTE_TYPE_LOOKUP_CODE)||' '||PHA.TYPE_LOOKUP_CODE TYPE--,HLA.LOCATION_CODESHIP_TO--,HLA1.LOCATION_CODE BILL_TO,TRUNC(PHA.CREATION_DATE) CREATION_DATE,PHA.STATUS_LOOKUP_CODE
,TO_CHAR(PHA.RFQ_CLOSE_DATE,'DD-MON-YYYY') CLOSE_DATE,PAPF.FULL_NAME BUYER
,PLA.LINE_NUM,MSIB.SEGMENT1 ITEM,PLA.ITEM_REVISION,MSIB.DESCRIPTION,PLA.UNIT_MEAS_LOOKUP_CODE UOM
,PLA.UNIT_PRICE,PLLA.SHIPMENT_NUM,PLLA.UNIT_MEAS_LOOKUP_CODE SHIP_UOM,PLLA.QUANTITY
,OOD.ORGANIZATION_CODE ORG,HLA2.LOCATION_CODE,PLLA.QUANTITY SHIPMENT_QTY,PLLA.PRICE_OVERRIDE
,PLLA.PRICE_DISCOUNT,PLLA.START_DATE,PLLA.END_DATE,PLLA.TERMS_ID,ATT.NAME PAYMENT_TERMS
FROM
PO_HEADERS_ALL PHA,HR_ALL_ORGANIZATION_UNITS HAOU,HR_LOCATIONS_ALL HLA
,HR_LOCATIONS_ALL HLA1,PER_ALL_PEOPLE_F PAPF,PO_LINES_ALL PLA,PO_LINE_TYPES_B PLTB
,MTL_SYSTEM_ITEMS_B MSIB,PO_LINE_LOCATIONS_ALL PLLA,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_LOCATIONS_ALL HLA2,AP_TERMS_TL ATT
WHERE 1=1
AND PHA.SEGMENT1='-110325'
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.SHIP_TO_LOCATION_ID=HLA.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID=HLA1.LOCATION_ID
AND PHA.AGENT_ID=PAPF.PERSON_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLTB.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PHA.ORG_ID =MSIB.ORGANIZATION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA2.LOCATION_ID
AND ATT.TERM_ID=PHA.TERMS_ID
==== QUOTATIONS ====
SELECT
HAOU.NAME OPERATING_UNIT,PHA.SEGMENT1 QUOTATION_NUM,AS1.VENDOR_NAME SUPPLIER
,ASSA.VENDOR_SITE_CODE SITE,INITCAP(PHA.QUOTE_TYPE_LOOKUP_CODE)||' '||INITCAP(PHA.TYPE_LOOKUP_CODE) TYPE
,PHA.CREATION_DATE,PHA.STATUS_LOOKUP_CODE STATUS,PHA.RFQ_CLOSE_DATE CLOSE_DATE
,PAV.AGENT_NAME BUYER,PLA.LINE_NUM,MSIB.SEGMENT1 ITEM,MSIB.DESCRIPTION,MSIB.PRIMARY_UNIT_OF_MEASURE UOM
,PLA.UNIT_PRICE PRICE,PLA.ITEM_REVISION,PLLA.SHIPMENT_NUM,OOD.ORGANIZATION_CODE ORG
,HLA.LOCATION_CODE,PLLA.PRICE_OVERRIDE,PLLA.PRICE_DISCOUNT,ATT.NAME
FROM
PO_HEADERS_ALL PHA,HR_ALL_ORGANIZATION_UNITS HAOU,AP_SUPPLIERS AS1,AP_SUPPLIER_SITES_ALL ASSA
,PO_AGENTS_V PAV,PO_LINES_ALL PLA,PO_LINE_TYPES_B PLTB,MTL_SYSTEM_ITEMS_B MSIB
,PO_LINE_LOCATIONS_ALL PLLA,HR_LOCATIONS_ALL HLA,ORG_ORGANIZATION_DEFINITIONS OOD,AP_TERMS_TL ATT
WHERE 1=1
AND PHA.ORG_ID = HAOU.ORGANIZATION_ID
AND PHA.SEGMENT1='502'
AND PHA.TYPE_LOOKUP_CODE='QUOTATION'
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.AGENT_ID=PAV.AGENT_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLTB.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PLA.ORG_ID=MSIB.ORGANIZATION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA.LOCATION_ID
AND OOD.ORGANIZATION_ID=PLLA.SHIP_TO_ORGANIZATION_ID
AND PLLA.TERMS_ID=ATT.TERM_ID
===== Standard Purchase Order ====
SELECT
HAOU.NAME,PHA.SEGMENT1 PO_NUM,AS1.VENDOR_NAME SUPPLIER,ASSP.VENDOR_SITE_CODE SITE ,PHA.TYPE_LOOKUP_CODE TYPE,PDTAT.TYPE_NAME -- 1,2,3 JOINS ARE REQUIRED
,POA.PO_TOTAL,PHA.CREATION_DATE,PHA.AUTHORIZATION_STATUS STATUS,PAV.AGENT_NAME
,PLA.LINE_NUM,PLT.LINE_TYPE,MSIB.SEGMENT1 ITEM,MSIB.DESCRIPTION,PLA.UNIT_PRICE,PLA.QUANTITY
,PLLA.SHIPMENT_NUM,OOD.ORGANIZATION_CODE,HLA.LOCATION_CODE,PAPF.FULL_NAME
FROM
PO_HEADERS_ALL PHA,HR_ALL_ORGANIZATION_UNITS HAOU,AP_SUPPLIERS AS1
,AP_SUPPLIER_SITES_ALL ASSP,PO_DOCUMENT_TYPES_ALL_TL PDTAT
,(SELECT PO_HEADER_ID,SUM(UNIT_PRICE*QUANTITY) PO_TOTAL FROM PO_LINES_ALL GROUP BY PO_HEADER_ID) POA
,PO_AGENTS_V PAV ,PO_LINES_ALL PLA,PO_LINE_TYPES PLT,MTL_SYSTEM_ITEMS_B MSIB
,PO_LINE_LOCATIONS_ALL PLLA,PO_LINE_TYPES PLT1,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_LOCATIONS_ALL HLA,PO_DISTRIBUTIONS_ALL PDA,PER_ALL_PEOPLE_F PAPF
WHERE 1=1
AND PHA.SEGMENT1='6039'
AND PHA.TYPE_LOOKUP_CODE='STANDARD'
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSP.VENDOR_SITE_ID
AND PDTAT.DOCUMENT_TYPE_CODE='PO'--1 JOIN
AND PHA.ORG_ID=PDTAT.ORG_ID --2 JOIN
AND LOWER(PHA.TYPE_LOOKUP_CODE)=LOWER(PDTAT.DOCUMENT_SUBTYPE) --3 JOIN
AND PHA.PO_HEADER_ID=POA.PO_HEADER_ID
AND PHA.AGENT_ID=PAV.AGENT_ID
AND PHA.PO_HEADER_ID= PLA.PO_HEADER_ID
AND PLT.LINE_TYPE_ID=PLA.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PLA.ORG_ID=MSIB.ORGANIZATION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLA.LINE_TYPE_ID=PLT1.LINE_TYPE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA.LOCATION_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PDA.DELIVER_TO_PERSON_ID=PAPF.PERSON_ID
===== Purchase Orders Created Through Requisition =====
Requisition No: 14314
SELECT * FROM FND_TABLES WHERE TABLE_NAME LIKE '%PO%DIS%'
SELECT
HAOU.NAME OPEATING_UNIT,PRHA.SEGMENT1 REQUISITION_NUM,PHA.SEGMENT1 PO_NUM
,PHA.TYPE_LOOKUP_CODE TYPE,PAV.AGENT_NAME BUYER,PHA.CREATION_DATE,PHA.AUTHORIZATION_STATUS STATUS
,AS1.VENDOR_NAME SUPPLIER,ASSA.VENDOR_SITE_CODE SITE,POA.AMOUNT,PLA.LINE_NUM,PLT.LINE_TYPE
,MSIB.SEGMENT1 ITEM,PLA.ITEM_REVISION REV,MSIB.DESCRIPTION,PLA.UNIT_MEAS_LOOKUP_CODE UOM
,PLA.QUANTITY LINE_QUANTITY,PLA.UNIT_PRICE PRICE,OOD.ORGANIZATION_CODE ORG,HLA.LOCATION_CODE
,PDA1.DISTRIBUTION_NUM,PAPF.FULL_NAME,HLA1.LOCATION_CODE
FROM
PO_REQUISITION_HEADERS_ALL PRHA,PO_REQUISITION_LINES_ALL PRLA,PO_REQ_DISTRIBUTIONS_ALL PRDA
,PO_DISTRIBUTIONS_ALL PDA,PO_LINES_ALL PLA,PO_HEADERS_ALL PHA
,HR_ALL_ORGANIZATION_UNITS HAOU,PO_AGENTS_V PAV,AP_SUPPLIERS AS1,AP_SUPPLIER_SITES_ALL ASSA
,(SELECT PO_HEADER_ID,SUM(UNIT_PRICE*QUANTITY) AMOUNT FROM PO_LINES_ALL GROUP BY PO_HEADER_ID) POA
,PO_LINE_TYPES PLT,MTL_SYSTEM_ITEMS_B MSIB,PO_LINE_LOCATIONS_ALL PLLA
,ORG_ORGANIZATION_DEFINITIONS OOD,HR_LOCATIONS_ALL HLA,PO_DISTRIBUTIONS_ALL PDA1
,PER_ALL_PEOPLE_F PAPF,HR_LOCATIONS_ALL HLA1
WHERE 1=1
AND PRHA.SEGMENT1='14314'
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.AGENT_ID=PAV.AGENT_ID
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.PO_HEADER_ID=POA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PLA.ORG_ID=MSIB.ORGANIZATION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA.SHIP_TO_LOCATION_ID
AND PLLA.LINE_LOCATION_ID=PDA1.LINE_LOCATION_ID
AND PDA1.DELIVER_TO_PERSON_ID=PAPF.PERSON_ID
AND PDA1.DELIVER_TO_LOCATION_ID=HLA1.LOCATION_ID
===== Planned Purchase Order Agreements & Releases =====
SELECT
HAOU.NAME ,PHA.SEGMENT1 ,PHA.TYPE_LOOKUP_CODE TYPE
,NVL2(PLLA.PO_RELEASE_ID,'RELEASED','AGREMENT') SUB_TYPE--NVL2(EXP1,EXP2,EXP3) IF EXP1 IS NULL EXP3 EXECUTED
--NVL2(EXP1,EXP2,EXP3) IF EXP1 IS NOT EXP2 EXECUTED
--,PRA.PO_RELEASE_ID ,PRA.RELEASE_NUM
--,PRA.RELEASE_REVISION_NUM ,AS1.VENDOR_NAME SUPPLIER ,ASSA.VENDOR_SITE_CODE SITE ,HLA.LOCATION_CODE
,PHA.AUTHORIZATION_STATUS STATUS ,POA.AMOUNT PO_TOTAL_AMOUNT ,PHA.CREATION_DATE ,PLA.LINE_NUM
,PLT.LINE_TYPE ,OOD.ORGANIZATION_CODE ORG ,HLA1.LOCATION_CODE LINE_LOCATION ,PLLA.QUANTITY LINE_QUANTITY
,PLLA.PROMISED_DATE ,PLLA.NEED_BY_DATE ,PLLA.QTY_RCV_TOLERANCE RECEIPT_TOLERANCE_LEVEL
,PLLA.RECEIVE_CLOSE_TOLERANCE RECIPT_CLOSE ,PDA.QUANTITY_ORDERED ,HLA2.LOCATION_CODE DIS_LOC
,PAPF.FULL_NAME REQUESTER ,PDA.DESTINATION_TYPE_CODE
FROM
PO_HEADERS_ALL PHA ,HR_ALL_ORGANIZATION_UNITS HAOU
,AP_SUPPLIERS AS1 ,AP_SUPPLIER_SITES_ALL ASSA
,HR_LOCATIONS_ALL HLA ,PO_LINES_ALL PLA ,PO_LINE_TYPES PLT
,(SELECT PO_HEADER_ID,SUM(UNIT_PRICE*QUANTITY) AMOUNT FROM PO_LINES_ALL GROUP BY PO_HEADER_ID) POA
,PO_LINE_LOCATIONS_ALL PLLA ,PO_DISTRIBUTIONS_ALL PDA ,PO_RELEASES_ALL PRA
,ORG_ORGANIZATION_DEFINITIONS OOD ,HR_LOCATIONS_ALL HLA1 ,HR_LOCATIONS_ALL HLA2 ,PER_ALL_PEOPLE_F PAPF
WHERE 1=1
AND PHA.SEGMENT1='6046'
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.SHIP_TO_LOCATION_ID=HLA.LOCATION_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND PLA.PO_HEADER_ID=POA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PHA.PO_HEADER_ID=PRA.PO_HEADER_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA1.LOCATION_ID
AND PDA.DELIVER_TO_LOCATION_ID=HLA2.LOCATION_ID
AND PDA.DELIVER_TO_PERSON_ID=PAPF.PERSON_ID
===== Blanket Purchase Orders Agreements and Releases =====
SELECT
HAOU.NAME OPERATING_UNIT ,PHA.SEGMENT1 ,PHA.TYPE_LOOKUP_CODE TYPE
,NVL2(PLLA.PO_RELEASE_ID,'RELEASE','AGREEMNET') SUB_TYPE
,AS1.VENDOR_NAME SUPPLIER ,ASSA.VENDOR_SITE_CODE SITE ,PHA.AUTHORIZATION_STATUS PO_STATUS
,PRA.AUTHORIZATION_STATUS RELEASE_STATUS
,NVL2(POA.AMOUNT,POA.AMOUNT,'0') RELEASED --NVL2(EXP1,EXP2,EXP3) IF EXP1 IS NULL EXP3 EXECUTED
--NVL2(EXP1,EXP2,EXP3) IF EXP1 IS NOT NULL EXP2 EXECUTED
,PLA.LINE_NUM ,PLT.LINE_TYPE ,MSIB.SEGMENT1 ITEM ,MSIB.DESCRIPTION ,PLLA.SHIPMENT_NUM
,OOD.ORGANIZATION_CODE ORG ,HLA.LOCATION_CODE SHIP_TO ,PLLA.QUANTITY ,PLLA.PRICE_OVERRIDE
,PLLA.PRICE_DISCOUNT ,PLLA.PROMISED_DATE ,PLLA.NEED_BY_DATE
FROM
PO_HEADERS_ALL PHA ,HR_ALL_ORGANIZATION_UNITS HAOU ,AP_SUPPLIERS AS1 ,AP_SUPPLIER_SITES_ALL ASSA
,PO_LINES_ALL PLA ,PO_LINE_TYPES PLT ,MTL_SYSTEM_ITEMS_B MSIB ,PO_LINE_LOCATIONS_ALL PLLA
,ORG_ORGANIZATION_DEFINITIONS OOD ,HR_LOCATIONS_ALL HLA,PO_RELEASES_ALL PRA
,(SELECT PO_HEADER_ID,SUM(PRICE_OVERRIDE*QUANTITY) AMOUNT
FROM PO_LINE_LOCATIONS_ALL WHERE SHIPMENT_TYPE='BLANKET' GROUP BY PO_HEADER_ID) POA
WHERE 1=1
AND PHA.SEGMENT1='6057'
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PHA.ORG_ID=MSIB.ORGANIZATION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID=HLA.LOCATION_ID
AND PHA.PO_HEADER_ID=POA.PO_HEADER_ID(+)
AND PHA.PO_HEADER_ID=PRA.PO_HEADER_ID
===== Contract Purchase Orders Agreements and Releases =====
SELECT
HAOU.NAME OPERATING_UNIT ,PHA.SEGMENT1 PO_NUM ,PHA.TYPE_LOOKUP_CODE TYPE ,PHA.CREATION_DATE
,NVL2(PLLA.PO_RELEASE_ID,'RELEASED','AGREEMENT') SUB_TYPE ,PHA.AUTHORIZATION_STATUS STATUS
,PRA.AUTHORIZATION_STATUS RELEASE_STATUS ,AS1.VENDOR_NAME SUPPLIER ,ASSA.VENDOR_SITE_CODE SITE
,PAV.AGENT_NAME BUYER ,POA.AMOUNT RELEASED_AMOUNT ,PLA.LINE_NUM ,PLT.LINE_TYPE ,PLA.CONTRACT_NUM
FROM
PO_HEADERS_ALL PHA ,HR_ALL_ORGANIZATION_UNITS HAOU ,AP_SUPPLIERS AS1 ,AP_SUPPLIER_SITES_ALL ASSA
,PO_AGENTS_V PAV ,PO_RELEASES_ALL PRA ,(SELECT PO_HEADER_ID,SUM(PRICE_OVERRIDE*QUANTITY) AMOUNT
FROM PO_LINE_LOCATIONS_ALL WHERE SHIPMENT_TYPE='STANDARD' GROUP BY PO_HEADER_ID) POA
,PO_LINES_ALL PLA ,PO_LINE_TYPES PLT ,PO_LINE_LOCATIONS_ALL PLLA
WHERE 1=1
AND PHA.SEGMENT1 IN ('6058','6059')
AND PHA.ORG_ID=HAOU.ORGANIZATION_ID
AND PHA.VENDOR_ID=AS1.VENDOR_ID
AND PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.AGENT_ID=PAV.AGENT_ID
AND PHA.PO_HEADER_ID=PRA.PO_HEADER_ID(+)
AND PHA.PO_HEADER_ID=POA.PO_HEADER_ID(+)
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID(+)
AND PLA.LINE_TYPE_ID=PLT.LINE_TYPE_ID(+)
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID(+)
No comments:
Post a Comment