VIEW: APPS.PO_PURCHASE_HISTORY_V

Source

View Text - Preformatted

SELECT PH.PO_HEADER_ID , PR.PO_RELEASE_ID , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.CREATION_DATE, 'PLANNED', PR.CREATION_DATE, 'BLANKET', PR.CREATION_DATE) , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.REVISED_DATE, 'PLANNED', PR.REVISED_DATE, 'BLANKET', PR.REVISED_DATE) , PH.VENDOR_ID , PV.VENDOR_NAME , PH.VENDOR_CONTACT_ID , DECODE(PH.VENDOR_CONTACT_ID, NULL, NULL, PVC.LAST_NAME||', '|| PVC.FIRST_NAME) , PV.PARENT_VENDOR_ID , PV1.VENDOR_NAME , PV.HOLD_FLAG , PH.VENDOR_SITE_ID , PVS.VENDOR_SITE_CODE , PV.WOMEN_OWNED_FLAG , PV.SMALL_BUSINESS_FLAG , PV.MINORITY_GROUP_LOOKUP_CODE , PLC1.DISPLAYED_FIELD , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.TYPE_LOOKUP_CODE, 'PLANNED', PR.RELEASE_TYPE, 'BLANKET', PR.RELEASE_TYPE) , PDTL.TYPE_NAME , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.SEGMENT1, 'PLANNED', PH.SEGMENT1||'-'||PR.RELEASE_NUM, 'BLANKET', PH.SEGMENT1||'-'|| PR.RELEASE_NUM) , PH.SEGMENT1 , PR.RELEASE_NUM , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.AGENT_ID, 'PLANNED', PR.AGENT_ID, 'BLANKET', PR.AGENT_ID) , PPF.FULL_NAME , PH.TERMS_ID , AT.NAME , PH.SHIP_VIA_LOOKUP_CODE , PH.FREIGHT_TERMS_LOOKUP_CODE , PH.FOB_LOOKUP_CODE , PH.CURRENCY_CODE , PH.RATE_TYPE , PH.RATE_DATE , PH.RATE , PH.AUTHORIZATION_STATUS , PLC2.DISPLAYED_FIELD , PL.PO_LINE_ID , PL.LINE_TYPE_ID , PLT.LINE_TYPE , PL.ITEM_ID , PL.ITEM_REVISION , PL.CATEGORY_ID , PL.ITEM_DESCRIPTION , PL.UNIT_MEAS_LOOKUP_CODE , PL.VENDOR_PRODUCT_NUM , PL.CONTRACT_NUM , PL.FROM_HEADER_ID , PH2.SEGMENT1 , PL.FROM_LINE_ID , PL2.LINE_NUM , PLL.SHIP_TO_LOCATION_ID , HL.LOCATION_CODE , PLL.SHIP_TO_ORGANIZATION_ID , MP.ORGANIZATION_CODE , HOUT.NAME , PLL.PROMISED_DATE , PLL.NEED_BY_DATE , PLL.QUANTITY_RECEIVED , PLL.QUANTITY_BILLED , PLL.QUANTITY_REJECTED , PLL.QUANTITY_ACCEPTED , PLL.LINE_LOCATION_ID , PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM , PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0) , DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PL.UNIT_PRICE, 'PLANNED', PLL.PRICE_OVERRIDE, 'BLANKET', PLL.PRICE_OVERRIDE) , ROUND(DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PL.UNIT_PRICE, 'PLANNED', PLL.PRICE_OVERRIDE, 'BLANKET', PLL.PRICE_OVERRIDE) * NVL(PH.RATE,1), 5) , GSB.CURRENCY_CODE , (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0)) * PLL.PRICE_OVERRIDE , ROUND((((PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0)) * PLL.PRICE_OVERRIDE) * NVL(PH.RATE,1)),5) , DCT.USER_CONVERSION_TYPE , PLT.OUTSIDE_OPERATION_FLAG , PLT.ORDER_TYPE_LOOKUP_CODE , PLC3.DISPLAYED_FIELD , PL.SECONDARY_UNIT_OF_MEASURE , PLL.SECONDARY_QUANTITY_RECEIVED , PLL.SECONDARY_QUANTITY_REJECTED , PLL.SECONDARY_QUANTITY_ACCEPTED , PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED,0) , PLL.PREFERRED_GRADE , PL.CONTRACT_ID ,PL.JOB_ID , PL.AMOUNT , PL.CONTRACTOR_FIRST_NAME , PL.CONTRACTOR_LAST_NAME , PL.START_DATE ,PL.EXPIRATION_DATE ,PLT.PURCHASE_BASIS , PH2.GLOBAL_AGREEMENT_FLAG , PH2.ORG_ID , PL.NEGOTIATED_BY_PREPARER_FLAG , PLT.MATCHING_BASIS , DECODE (PH.TYPE_LOOKUP_CODE, 'STANDARD', PL.MANUAL_PRICE_CHANGE_FLAG, 'BLANKET', PLL.MANUAL_PRICE_CHANGE_FLAG) ,PH2.TYPE_LOOKUP_CODE ,PLL.SECONDARY_QUANTITY_SHIPPED ,PH.ORG_ID FROM PO_HEADERS PH , PO_HEADERS_ALL PH2 , PO_RELEASES_ALL PR , PO_VENDORS PV , PO_VENDORS PV1 , PER_ALL_PEOPLE_F PPF , PO_VENDOR_SITES_ALL PVS , PO_VENDOR_CONTACTS PVC , PO_LOOKUP_CODES PLC1 , PO_LOOKUP_CODES PLC2 , PO_LOOKUP_CODES PLC3 , PO_LINES_ALL PL , PO_LINES_ALL PL2 , HR_LOCATIONS_ALL_TL HL , PO_LINE_LOCATIONS_ALL PLL , AP_TERMS AT , GL_SETS_OF_BOOKS GSB , GL_DAILY_CONVERSION_TYPES DCT , FINANCIALS_SYSTEM_PARAMS_ALL FSP , HR_ALL_ORGANIZATION_UNITS_TL HOUT , MTL_PARAMETERS MP , PO_LINE_TYPES PLT , PO_DOCUMENT_TYPES_ALL_B PDTB , PO_DOCUMENT_TYPES_ALL_TL PDTL WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET', 'PLANNED', 'STANDARD') AND PH.PO_HEADER_ID = PL.PO_HEADER_ID AND PL.PO_LINE_ID = PLL.PO_LINE_ID AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND PLL.SHIPMENT_TYPE IN ('BLANKET', 'SCHEDULED', 'STANDARD') AND ( ( ( PLT.ORDER_TYPE_LOOKUP_CODE IN ('QUANTITY','AMOUNT') ) AND ( PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0) > 0 ) ) OR ( ( PLT.ORDER_TYPE_LOOKUP_CODE IN ('FIXED PRICE','RATE') ) AND ( PLL.AMOUNT - NVL(PLL.AMOUNT_CANCELLED, 0) > 0 ) ) ) AND DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.APPROVED_DATE, 'PLANNED', PR.APPROVED_DATE, 'BLANKET', PR.APPROVED_DATE) IS NOT NULL AND DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.AGENT_ID, 'PLANNED', PR.AGENT_ID, 'BLANKET', PR.AGENT_ID) = PPF.PERSON_ID AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(PPF.EFFECTIVE_END_DATE, SYSDATE + 1) AND PH.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID AND PH.TERMS_ID = AT.TERM_ID(+) AND PH.AUTHORIZATION_STATUS = PLC2.LOOKUP_CODE AND PLC2.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PV.MINORITY_GROUP_LOOKUP_CODE = PLC1.LOOKUP_CODE(+) AND PLC1.LOOKUP_TYPE(+) = 'MINORITY GROUP' AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PL.FROM_HEADER_ID = PH2.PO_HEADER_ID(+) AND PL.FROM_LINE_ID = PL2.PO_LINE_ID(+) AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+) AND HL.LANGUAGE(+) = USERENV('LANG') AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID AND HOUT.LANGUAGE = USERENV('LANG') AND DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', 'PO', 'PLANNED', 'RELEASE', 'BLANKET', 'RELEASE') = PDTB.DOCUMENT_TYPE_CODE AND DECODE(PH.TYPE_LOOKUP_CODE, 'STANDARD', PH.TYPE_LOOKUP_CODE, 'PLANNED', PR.RELEASE_TYPE, 'BLANKET', PR.RELEASE_TYPE) = PDTB.DOCUMENT_SUBTYPE AND PDTB.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODE(+) AND PDTB.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPE(+) AND PDTB.ORG_ID = PDTL.ORG_ID AND PDTB.ORG_ID = PH.ORG_ID AND PDTL.LANGUAGE(+) = USERENV('LANG') AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND NVL(PVC.VENDOR_SITE_ID, PVS.VENDOR_SITE_ID) = PVS.VENDOR_SITE_ID AND PV.PARENT_VENDOR_ID = PV1.VENDOR_ID(+) AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+) AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+) AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC3.LOOKUP_CODE AND PLC3.LOOKUP_TYPE = 'ORDER TYPE' AND FSP.org_id = PH.ORG_ID

View Text - HTML Formatted

SELECT PH.PO_HEADER_ID
, PR.PO_RELEASE_ID
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.CREATION_DATE
, 'PLANNED'
, PR.CREATION_DATE
, 'BLANKET'
, PR.CREATION_DATE)
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.REVISED_DATE
, 'PLANNED'
, PR.REVISED_DATE
, 'BLANKET'
, PR.REVISED_DATE)
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME)
, PV.PARENT_VENDOR_ID
, PV1.VENDOR_NAME
, PV.HOLD_FLAG
, PH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PV.WOMEN_OWNED_FLAG
, PV.SMALL_BUSINESS_FLAG
, PV.MINORITY_GROUP_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.TYPE_LOOKUP_CODE
, 'PLANNED'
, PR.RELEASE_TYPE
, 'BLANKET'
, PR.RELEASE_TYPE)
, PDTL.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.SEGMENT1
, 'PLANNED'
, PH.SEGMENT1||'-'||PR.RELEASE_NUM
, 'BLANKET'
, PH.SEGMENT1||'-'|| PR.RELEASE_NUM)
, PH.SEGMENT1
, PR.RELEASE_NUM
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.AGENT_ID
, 'PLANNED'
, PR.AGENT_ID
, 'BLANKET'
, PR.AGENT_ID)
, PPF.FULL_NAME
, PH.TERMS_ID
, AT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.RATE_TYPE
, PH.RATE_DATE
, PH.RATE
, PH.AUTHORIZATION_STATUS
, PLC2.DISPLAYED_FIELD
, PL.PO_LINE_ID
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID
, PL.ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.VENDOR_PRODUCT_NUM
, PL.CONTRACT_NUM
, PL.FROM_HEADER_ID
, PH2.SEGMENT1
, PL.FROM_LINE_ID
, PL2.LINE_NUM
, PLL.SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE
, PLL.SHIP_TO_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_REJECTED
, PLL.QUANTITY_ACCEPTED
, PLL.LINE_LOCATION_ID
, PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM
, PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PL.UNIT_PRICE
, 'PLANNED'
, PLL.PRICE_OVERRIDE
, 'BLANKET'
, PLL.PRICE_OVERRIDE)
, ROUND(DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PL.UNIT_PRICE
, 'PLANNED'
, PLL.PRICE_OVERRIDE
, 'BLANKET'
, PLL.PRICE_OVERRIDE) * NVL(PH.RATE
, 1)
, 5)
, GSB.CURRENCY_CODE
, (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) * PLL.PRICE_OVERRIDE
, ROUND((((PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) * PLL.PRICE_OVERRIDE) * NVL(PH.RATE
, 1))
, 5)
, DCT.USER_CONVERSION_TYPE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC3.DISPLAYED_FIELD
, PL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED
, 0)
, PLL.PREFERRED_GRADE
, PL.CONTRACT_ID
, PL.JOB_ID
, PL.AMOUNT
, PL.CONTRACTOR_FIRST_NAME
, PL.CONTRACTOR_LAST_NAME
, PL.START_DATE
, PL.EXPIRATION_DATE
, PLT.PURCHASE_BASIS
, PH2.GLOBAL_AGREEMENT_FLAG
, PH2.ORG_ID
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.MATCHING_BASIS
, DECODE (PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PL.MANUAL_PRICE_CHANGE_FLAG
, 'BLANKET'
, PLL.MANUAL_PRICE_CHANGE_FLAG)
, PH2.TYPE_LOOKUP_CODE
, PLL.SECONDARY_QUANTITY_SHIPPED
, PH.ORG_ID
FROM PO_HEADERS PH
, PO_HEADERS_ALL PH2
, PO_RELEASES_ALL PR
, PO_VENDORS PV
, PO_VENDORS PV1
, PER_ALL_PEOPLE_F PPF
, PO_VENDOR_SITES_ALL PVS
, PO_VENDOR_CONTACTS PVC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LINES_ALL PL
, PO_LINES_ALL PL2
, HR_LOCATIONS_ALL_TL HL
, PO_LINE_LOCATIONS_ALL PLL
, AP_TERMS AT
, GL_SETS_OF_BOOKS GSB
, GL_DAILY_CONVERSION_TYPES DCT
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_PARAMETERS MP
, PO_LINE_TYPES PLT
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PO_DOCUMENT_TYPES_ALL_TL PDTL
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'PLANNED'
, 'STANDARD')
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND PLL.SHIPMENT_TYPE IN ('BLANKET'
, 'SCHEDULED'
, 'STANDARD')
AND ( ( ( PLT.ORDER_TYPE_LOOKUP_CODE IN ('QUANTITY'
, 'AMOUNT') )
AND ( PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) > 0 ) ) OR ( ( PLT.ORDER_TYPE_LOOKUP_CODE IN ('FIXED PRICE'
, 'RATE') )
AND ( PLL.AMOUNT - NVL(PLL.AMOUNT_CANCELLED
, 0) > 0 ) ) )
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.APPROVED_DATE
, 'PLANNED'
, PR.APPROVED_DATE
, 'BLANKET'
, PR.APPROVED_DATE) IS NOT NULL
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.AGENT_ID
, 'PLANNED'
, PR.AGENT_ID
, 'BLANKET'
, PR.AGENT_ID) = PPF.PERSON_ID
AND SYSDATE BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, SYSDATE - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, SYSDATE + 1)
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.TERMS_ID = AT.TERM_ID(+)
AND PH.AUTHORIZATION_STATUS = PLC2.LOOKUP_CODE
AND PLC2.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PV.MINORITY_GROUP_LOOKUP_CODE = PLC1.LOOKUP_CODE(+)
AND PLC1.LOOKUP_TYPE(+) = 'MINORITY GROUP'
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PL.FROM_HEADER_ID = PH2.PO_HEADER_ID(+)
AND PL.FROM_LINE_ID = PL2.PO_LINE_ID(+)
AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, 'PO'
, 'PLANNED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE') = PDTB.DOCUMENT_TYPE_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.TYPE_LOOKUP_CODE
, 'PLANNED'
, PR.RELEASE_TYPE
, 'BLANKET'
, PR.RELEASE_TYPE) = PDTB.DOCUMENT_SUBTYPE
AND PDTB.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODE(+)
AND PDTB.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPE(+)
AND PDTB.ORG_ID = PDTL.ORG_ID
AND PDTB.ORG_ID = PH.ORG_ID
AND PDTL.LANGUAGE(+) = USERENV('LANG')
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND NVL(PVC.VENDOR_SITE_ID
, PVS.VENDOR_SITE_ID) = PVS.VENDOR_SITE_ID
AND PV.PARENT_VENDOR_ID = PV1.VENDOR_ID(+)
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC3.LOOKUP_CODE
AND PLC3.LOOKUP_TYPE = 'ORDER TYPE'
AND FSP.ORG_ID = PH.ORG_ID