View: PO_PURCHASE_HISTORY_V

Product:	PO - Purchasing
Description:	- Retrofitted
Implementation/DBA Data:	APPS.PO_PURCHASE_HISTORY_V

View Text

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 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

Columns