View: PO_ECX_HEADER_V

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

View Text

SELECT PHA.PO_HEADER_ID POID
, PHA.SEGMENT1 CONTRACTB
, PHA.TYPE_LOOKUP_CODE
, TO_NUMBER(NULL) RELEASE_ID
, TO_NUMBER(NULL) RELEASE_NUM
, PHA.ACCEPTANCE_REQUIRED_FLAG
, PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PHA.CREATION_DATE
, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM
, PHA.CURRENCY_CODE PO_CURRENCY
, PV.VENDOR_NAME SUPP_ORG_NAME
, DECODE (PV.ONE_TIME_FLAG
, 'N'
, '0'
, '1') SUPP_OTF
, 'SUPPLIER' SUPP_PARTNER_TYPE
, PHA.VENDOR_ID SUPPLIER_PARTNER_ID
, NVL(PVSA.ECE_TP_LOCATION_CODE
, PHA.VENDOR_ID) SUPPLIER_PARTNER_ID_X
, POGOT_S.ECX_GET_TOTAL ('H'
, PHA.PO_HEADER_ID
, PHA.CURRENCY_CODE) ORDER_TOTAL
, PV.PAYMENT_CURRENCY_CODE SUPP_CURRENCY
, PV.VAT_REGISTRATION_NUM
, PVSA.ADDRESS_LINE1 SUPP_ADDR_LINE1
, PVSA.ADDRESS_LINE2 SUPP_ADDR_LINE2
, PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE3
, PVSA.CITY SUPP_CITY
, PVSA.COUNTRY SUPP_COUNTRY
, PVSA.ZIP SUPP_ZIP
, PVSA.STATE SUPP_STATE
, PVSA.AREA_CODE || ' ' || PVSA.PHONE SUPP_PHONE
, HOUT.NAME BUYING_ORG_NAME
, '0' BUYING_ORG_OTF
, 'SOLDTO' BUYING_ORG_PARTNER_TYPE
, PHA.ORG_ID PARTNER_ID
, NVL(HLAB.ECE_TP_LOCATION_CODE
, PHA.ORG_ID) PARTNER_ID_X
, FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.ADDRESS_LINE_1 BUYING_ORG_ADDR_LINE1
, HOU_V.ADDRESS_LINE_2 BUYING_ORG_ADDR_LINE2
, HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE3
, HOU_V.TOWN_OR_CITY BUYING_ORG_CITY
, HOU_V.COUNTRY BUYING_ORG_COUNTRY
, HOU_V.POSTAL_CODE BUYING_ORG_ZIP
, HOU_V.REGION_2 BUYING_ORG_STATE
, HOU_V.TELEPHONE_NUMBER_1 BUYING_ORG_PHONE1
, HOU_V.TELEPHONE_NUMBER_2 BUYING_ORG_PHONE2
, HOU_V.TELEPHONE_NUMBER_3 BUYING_ORG_PHONE3
, PHA.AGENT_ID BUYER_ID
, PAPF.FULL_NAME BUYER_FULL_NAME
, PAPF.EMAIL_ADDRESS BUYER_EMAIL
, HR_GENERAL.GET_WORK_PHONE(PHA.AGENT_ID) BUYER_TELEPHONE
, HOU_V.NAME BILLTO_ORG_NAME
, '0' BILLTO_ORG_OTF
, 'BILLTO' BILLTO_PARTNER_TYPE
, FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY
, HLAB.ADDRESS_LINE_1 BILLTO_ORG_ADDR_LINE1
, HLAB.ADDRESS_LINE_2 BILLTO_ORG_ADDR_LINE2
, HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE3
, HLAB.TOWN_OR_CITY BILLTO_ORG_CITY
, HLAB.COUNTRY BILLTO_ORG_COUNTRY
, HLAB.POSTAL_CODE BILLTO_ORG_ZIP
, HLAB.REGION_2 BILLTO_ORG_STATE
, HLAB.TELEPHONE_NUMBER_1 BILLTO_ORG_PHONE1
, HLAB.TELEPHONE_NUMBER_2 BILLTO_ORG_PHONE2
, HLAB.TELEPHONE_NUMBER_3 BILLTO_ORG_PHONE3
, PHA.SHIP_VIA_LOOKUP_CODE CARRIER_ORG_NAME
, DECODE(PHA.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, '0') CARRIER_ORG_OTF
, DECODE(PHA.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, 'CARRIER') CARRIER_PARTNER_TYPE
, APT.TERM_ID PAYMENT_TERMS_ID
, APT.NAME PAYMENT_TERMS_NAME
, APT.DESCRIPTION PAYMENT_TERMS_DESCRIPTION
, PLCFOB.LOOKUP_CODE FOB_CODE
, PLCFOB.DISPLAYED_FIELD FOB_NAME
, PLCFOB.DESCRIPTION FOB_DESC
, PLCFR.LOOKUP_CODE FREIGHT_CODE
, PLCFR.DISPLAYED_FIELD FREIGHT_NAME
, PLCFR.DESCRIPTION FREIGHT_DESC
, PHA.NOTE_TO_AUTHORIZER
, PHA.NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER
, PHA.COMMENTS
, PHA.START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE
, PHA.REVISION_NUM
, PHA.CONFIRMING_ORDER_FLAG
FROM PO_HEADERS_ARCHIVE_ALL PHA
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_ORGANIZATION_UNITS_V HOU_V
, PER_ALL_PEOPLE_F PAPF
, HR_LOCATIONS_ALL HLAB
, PO_LOOKUP_CODES PLCFOB
, PO_LOOKUP_CODES PLCFR
, AP_TERMS APT
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
WHERE PHA.REVISION_NUM = 0
AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
AND PV.VENDOR_ID = PHA.VENDOR_ID
AND HOUT.ORGANIZATION_ID = PHA.ORG_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PVSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND PVSA.ORG_ID = PHA.ORG_ID
AND FSPA.ORG_ID = PHA.ORG_ID
AND HOU_V.ORGANIZATION_ID = PHA.ORG_ID
AND HLAB.LOCATION_ID = PHA.BILL_TO_LOCATION_ID
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PAPF.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(PLCFOB.LOOKUP_TYPE
, 'FOB') = 'FOB'
AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE
AND NVL(PLCFR.LOOKUP_TYPE
, 'FREIGHT TERMS') = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE (+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND APT.TERM_ID = PHA.TERMS_ID UNION ALL SELECT PHA.PO_HEADER_ID POID
, PHA.SEGMENT1 CONTRACTB
, 'RELEASE'
, POR.PO_RELEASE_ID RELEASE_ID
, POR.RELEASE_NUM RELEASE_NUM
, PHA.ACCEPTANCE_REQUIRED_FLAG
, PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PHA.CREATION_DATE
, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM
, PHA.CURRENCY_CODE PO_CURRENCY
, PV.VENDOR_NAME SUPP_ORG_NAME
, DECODE (PV.ONE_TIME_FLAG
, 'N'
, '0'
, '1') SUPP_OTF
, 'SUPPLIER' SUPP_PARTNER_TYPE
, PHA.VENDOR_ID SUPPLIER_PARTNER_ID
, NVL(PVSA.ECE_TP_LOCATION_CODE
, PHA.VENDOR_ID) SUPPLIER_PARTNER_ID_X
, POGOT_S.ECX_GET_TOTAL ('H'
, PHA.PO_HEADER_ID
, PHA.CURRENCY_CODE) ORDER_TOTAL
, PV.PAYMENT_CURRENCY_CODE SUPP_CURRENCY
, PV.VAT_REGISTRATION_NUM
, PVSA.ADDRESS_LINE1 SUPP_ADDR_LINE1
, PVSA.ADDRESS_LINE2 SUPP_ADDR_LINE2
, PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE3
, PVSA.CITY SUPP_CITY
, PVSA.COUNTRY SUPP_COUNTRY
, PVSA.ZIP SUPP_ZIP
, PVSA.STATE SUPP_STATE
, PVSA.AREA_CODE || ' ' || PVSA.PHONE SUPP_PHONE
, HOUT.NAME BUYING_ORG_NAME
, '0' BUYING_ORG_OTF
, 'SOLDTO' BUYING_ORG_PARTNER_TYPE
, PHA.ORG_ID PARTNER_ID
, NVL(HLAB.ECE_TP_LOCATION_CODE
, PHA.ORG_ID) PARTNER_ID_X
, FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.ADDRESS_LINE_1 BUYING_ORG_ADDR_LINE1
, HOU_V.ADDRESS_LINE_2 BUYING_ORG_ADDR_LINE2
, HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE3
, HOU_V.TOWN_OR_CITY BUYING_ORG_CITY
, HOU_V.COUNTRY BUYING_ORG_COUNTRY
, HOU_V.POSTAL_CODE BUYING_ORG_ZIP
, HOU_V.REGION_2 BUYING_ORG_STATE
, HOU_V.TELEPHONE_NUMBER_1 BUYING_ORG_PHONE1
, HOU_V.TELEPHONE_NUMBER_2 BUYING_ORG_PHONE2
, HOU_V.TELEPHONE_NUMBER_3 BUYING_ORG_PHONE3
, PHA.AGENT_ID BUYER_ID
, PAPF.FULL_NAME BUYER_FULL_NAME
, PAPF.EMAIL_ADDRESS BUYER_EMAIL
, HR_GENERAL.GET_WORK_PHONE(PHA.AGENT_ID) BUYER_TELEPHONE
, HOU_V.NAME BILLTO_ORG_NAME
, '0' BILLTO_ORG_OTF
, 'BILLTO' BILLTO_PARTNER_TYPE
, FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY
, HLAB.ADDRESS_LINE_1 BILLTO_ORG_ADDR_LINE1
, HLAB.ADDRESS_LINE_2 BILLTO_ORG_ADDR_LINE2
, HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE3
, HLAB.TOWN_OR_CITY BILLTO_ORG_CITY
, HLAB.COUNTRY BILLTO_ORG_COUNTRY
, HLAB.POSTAL_CODE BILLTO_ORG_ZIP
, HLAB.REGION_2 BILLTO_ORG_STATE
, HLAB.TELEPHONE_NUMBER_1 BILLTO_ORG_PHONE1
, HLAB.TELEPHONE_NUMBER_2 BILLTO_ORG_PHONE2
, HLAB.TELEPHONE_NUMBER_3 BILLTO_ORG_PHONE3
, PHA.SHIP_VIA_LOOKUP_CODE CARRIER_ORG_NAME
, DECODE(PHA.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, '0') CARRIER_ORG_OTF
, DECODE(PHA.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, 'CARRIER') CARRIER_PARTNER_TYPE
, APT.TERM_ID PAYMENT_TERMS_ID
, APT.NAME PAYMENT_TERMS_NAME
, APT.DESCRIPTION PAYMENT_TERMS_DESCRIPTION
, PLCFOB.LOOKUP_CODE FOB_CODE
, PLCFOB.DISPLAYED_FIELD FOB_NAME
, PLCFOB.DESCRIPTION FOB_DESC
, PLCFR.LOOKUP_CODE FREIGHT_CODE
, PLCFR.DISPLAYED_FIELD FREIGHT_NAME
, PLCFR.DESCRIPTION FREIGHT_DESC
, PHA.NOTE_TO_AUTHORIZER
, PHA.NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER
, PHA.COMMENTS
, PHA.START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE
, PHA.REVISION_NUM
, PHA.CONFIRMING_ORDER_FLAG
FROM PO_RELEASES_ARCHIVE_ALL POR
, PO_HEADERS_ARCHIVE_ALL PHA
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVSA
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_ORGANIZATION_UNITS_V HOU_V
, PER_ALL_PEOPLE_F PAPF
, HR_LOCATIONS_ALL HLAB
, PO_LOOKUP_CODES PLCFOB
, PO_LOOKUP_CODES PLCFR
, AP_TERMS APT
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
WHERE PHA.PO_HEADER_ID = POR.PO_HEADER_ID
AND PHA.REVISION_NUM = 0
AND POR.REVISION_NUM = 0
AND PHA.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'PLANNED')
AND PV.VENDOR_ID = PHA.VENDOR_ID
AND HOUT.ORGANIZATION_ID = PHA.ORG_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PVSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND PVSA.ORG_ID = PHA.ORG_ID
AND FSPA.ORG_ID = PHA.ORG_ID
AND HOU_V.ORGANIZATION_ID = PHA.ORG_ID
AND HLAB.LOCATION_ID = PHA.BILL_TO_LOCATION_ID
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PAPF.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(PLCFOB.LOOKUP_TYPE
, 'FOB') = 'FOB'
AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE
AND NVL(PLCFR.LOOKUP_TYPE
, 'FREIGHT TERMS') = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE (+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND APT.TERM_ID = PHA.TERMS_ID

Columns