VIEW: APPS.PO_CXML_HEADER_ARCH_V

Source

View Text - Preformatted

SELECT PHA.PO_HEADER_ID HEADER_ID , PHA.SEGMENT1 ORDERID , PHA.REVISION_NUM REVISION_NUM , to_number ( null ) RELEASE_ID , to_number ( null ) RELEASE_NUM , 'regular' ORDER_TYPE , PHA.CURRENCY_CODE PO_CURRENCY , PHA.ACCEPTANCE_REQUIRED_FLAG , PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE , PHA.CREATION_DATE CREATION_DATE , PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM , PV.VENDOR_NAME SUPP_ORG_NAME , PV.SEGMENT1 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 || PVSA.ADDRESS_LINE2 || PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE123 , 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 , PHA.ORG_ID PARTNER_ID , HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X , GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY , HOU_V.ADDRESS_LINE_1 || HOU_V.ADDRESS_LINE_2 || HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE123 , 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 , FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY , HLAB.ADDRESS_LINE_1 || HLAB.ADDRESS_LINE_2 || HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE123 , 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 , 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 NOTE_TO_VENDOR , PHA.NOTE_TO_RECEIVER , PHA.COMMENTS , PHA.START_DATE_ACTIVE , PHA.END_DATE_ACTIVE , PHA.CONFIRMING_ORDER_FLAG , PHA.RATE , PHA.RATE_DATE , AC.CARDMEMBER_NAME CARD_NAME , AC.CARD_NUMBER CARD_NUM , AC.CARD_EXPIRATION_DATE CARD_EXP_DATE , PV.CUSTOMER_NUM , PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX , PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL , PVC.FIRST_NAME||' '||PVC.MIDDLE_NAME||' '||PVC.LAST_NAME , PVC.AREA_CODE||'-'||PVC.PHONE , PHA.LAST_UPDATE_DATE , PH.PCARD_ID , PVSA.VENDOR_SITE_CODE SUP_SITE_CODE , PV.NUM_1099 NUM_1099 , 'ACKREQD' ACKREQD , 'ACKBYDATE' ACKBYDATE , 'SUPPNOTE' SUPPNOTE , PHA.SHIP_TO_LOCATION_ID , PHA.BILL_TO_LOCATION_ID , PHA.TYPE_LOOKUP_CODE 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 , AP_CARDS_ALL AC , PO_VENDOR_CONTACTS PVC , PO_HEADERS_ALL PH , GL_SETS_OF_BOOKS GSOB WHERE PHA.TYPE_LOOKUP_CODE = 'STANDARD' AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID 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 GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_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 PLCFOB.LOOKUP_TYPE(+) = 'FOB' AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS' AND PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE AND APT.TERM_ID(+) = PHA.TERMS_ID AND PHA.PCARD_ID = AC.CARD_ID ( + ) AND PVC.VENDOR_CONTACT_ID ( + ) = PHA.VENDOR_CONTACT_ID AND PVC.VENDOR_SITE_ID ( + ) = PHA.VENDOR_SITE_ID UNION ALL SELECT PRAA.PO_HEADER_ID HEADER_ID , PHA.SEGMENT1 ORDERID , PRAA.REVISION_NUM REVISION_NUM , PRAA.PO_RELEASE_ID RELEASE_ID , PRAA.RELEASE_NUM RELEASE_NUM , 'release' ORDER_TYPE , PHA.CURRENCY_CODE PO_CURRENCY , PHA.ACCEPTANCE_REQUIRED_FLAG , PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE , PRAA.RELEASE_DATE CREATION_DATE , PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM , PV.VENDOR_NAME SUPP_ORG_NAME , PV.SEGMENT1 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 || PVSA.ADDRESS_LINE2 || PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE123 , 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 , PHA.ORG_ID PARTNER_ID , HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X , GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY , HOU_V.ADDRESS_LINE_1 || HOU_V.ADDRESS_LINE_2 || HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE123 , 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 , FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY , HLAB.ADDRESS_LINE_1 || HLAB.ADDRESS_LINE_2 ||HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE123 , 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 , 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 NOTE_TO_VENDOR , PHA.NOTE_TO_RECEIVER , PHA.COMMENTS , PHA.START_DATE_ACTIVE , PHA.END_DATE_ACTIVE , PHA.CONFIRMING_ORDER_FLAG , PHA.RATE , PHA.RATE_DATE , AC.CARDMEMBER_NAME , AC.CARD_NUMBER , AC.CARD_EXPIRATION_DATE , PV.CUSTOMER_NUM , PVSA.FAX_AREA_CODE || PVSA.FAX , PVSA.EMAIL_ADDRESS , PVC.FIRST_NAME||' '||PVC.MIDDLE_NAME||' '||PVC.LAST_NAME , PVC.AREA_CODE||'-'||PVC.PHONE , PHA.LAST_UPDATE_DATE , PH.PCARD_ID , PVSA.VENDOR_SITE_CODE SUP_SITE_CODE , PV.NUM_1099 NUM_1099 , 'ACKREQD' ACKREQD , 'ACKBYDATE' ACKBYDATE , 'SUPPNOTE' SUPPNOTE , PHA.SHIP_TO_LOCATION_ID , PHA.BILL_TO_LOCATION_ID , PHA.TYPE_LOOKUP_CODE FROM PO_RELEASES_ARCHIVE_ALL PRAA , 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 , AP_CARDS_ALL AC , PO_VENDOR_CONTACTS PVC , PO_HEADERS_ALL PH , GL_SETS_OF_BOOKS GSOB WHERE PHA.PO_HEADER_ID = PRAA.PO_HEADER_ID AND PHA.REVISION_NUM = 0 AND PHA.TYPE_LOOKUP_CODE in ( 'BLANKET' , 'PLANNED' ) AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID 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 GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_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 PLCFOB.LOOKUP_TYPE(+) = 'FOB' AND PLCFOB.LOOKUP_CODE (+) = PHA.FOB_LOOKUP_CODE AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS' AND PLCFR.LOOKUP_CODE ( + ) = PHA.FREIGHT_TERMS_LOOKUP_CODE AND APT.TERM_ID(+) = PHA.TERMS_ID AND PHA.PCARD_ID = AC.CARD_ID ( + ) AND PVC.VENDOR_CONTACT_ID ( + ) = PHA.VENDOR_CONTACT_ID AND PVC.VENDOR_SITE_ID ( + ) = PHA.VENDOR_SITE_ID

View Text - HTML Formatted

SELECT PHA.PO_HEADER_ID HEADER_ID
, PHA.SEGMENT1 ORDERID
, PHA.REVISION_NUM REVISION_NUM
, TO_NUMBER ( NULL ) RELEASE_ID
, TO_NUMBER ( NULL ) RELEASE_NUM
, 'REGULAR' ORDER_TYPE
, PHA.CURRENCY_CODE PO_CURRENCY
, PHA.ACCEPTANCE_REQUIRED_FLAG
, PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PHA.CREATION_DATE CREATION_DATE
, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM
, PV.VENDOR_NAME SUPP_ORG_NAME
, PV.SEGMENT1 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 || PVSA.ADDRESS_LINE2 || PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE123
, 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
, PHA.ORG_ID PARTNER_ID
, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X
, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.ADDRESS_LINE_1 || HOU_V.ADDRESS_LINE_2 || HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE123
, 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
, FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY
, HLAB.ADDRESS_LINE_1 || HLAB.ADDRESS_LINE_2 || HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE123
, 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
, 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 NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER
, PHA.COMMENTS
, PHA.START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE
, PHA.CONFIRMING_ORDER_FLAG
, PHA.RATE
, PHA.RATE_DATE
, AC.CARDMEMBER_NAME CARD_NAME
, AC.CARD_NUMBER CARD_NUM
, AC.CARD_EXPIRATION_DATE CARD_EXP_DATE
, PV.CUSTOMER_NUM
, PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX
, PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL
, PVC.FIRST_NAME||' '||PVC.MIDDLE_NAME||' '||PVC.LAST_NAME
, PVC.AREA_CODE||'-'||PVC.PHONE
, PHA.LAST_UPDATE_DATE
, PH.PCARD_ID
, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE
, PV.NUM_1099 NUM_1099
, 'ACKREQD' ACKREQD
, 'ACKBYDATE' ACKBYDATE
, 'SUPPNOTE' SUPPNOTE
, PHA.SHIP_TO_LOCATION_ID
, PHA.BILL_TO_LOCATION_ID
, PHA.TYPE_LOOKUP_CODE
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
, AP_CARDS_ALL AC
, PO_VENDOR_CONTACTS PVC
, PO_HEADERS_ALL PH
, GL_SETS_OF_BOOKS GSOB
WHERE PHA.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID
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 GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_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 PLCFOB.LOOKUP_TYPE(+) = 'FOB'
AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE
AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND APT.TERM_ID(+) = PHA.TERMS_ID
AND PHA.PCARD_ID = AC.CARD_ID ( + )
AND PVC.VENDOR_CONTACT_ID ( + ) = PHA.VENDOR_CONTACT_ID
AND PVC.VENDOR_SITE_ID ( + ) = PHA.VENDOR_SITE_ID UNION ALL SELECT PRAA.PO_HEADER_ID HEADER_ID
, PHA.SEGMENT1 ORDERID
, PRAA.REVISION_NUM REVISION_NUM
, PRAA.PO_RELEASE_ID RELEASE_ID
, PRAA.RELEASE_NUM RELEASE_NUM
, 'RELEASE' ORDER_TYPE
, PHA.CURRENCY_CODE PO_CURRENCY
, PHA.ACCEPTANCE_REQUIRED_FLAG
, PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PRAA.RELEASE_DATE CREATION_DATE
, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM
, PV.VENDOR_NAME SUPP_ORG_NAME
, PV.SEGMENT1 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 || PVSA.ADDRESS_LINE2 || PVSA.ADDRESS_LINE3 SUPP_ADDR_LINE123
, 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
, PHA.ORG_ID PARTNER_ID
, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X
, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.ADDRESS_LINE_1 || HOU_V.ADDRESS_LINE_2 || HOU_V.ADDRESS_LINE_3 BUYING_ORG_ADDR_LINE123
, 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
, FSPA.PAYMENT_CURRENCY_CODE BILLTO_ORG_CURRENCY
, HLAB.ADDRESS_LINE_1 || HLAB.ADDRESS_LINE_2 ||HLAB.ADDRESS_LINE_3 BILLTO_ORG_ADDR_LINE123
, 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
, 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 NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER
, PHA.COMMENTS
, PHA.START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE
, PHA.CONFIRMING_ORDER_FLAG
, PHA.RATE
, PHA.RATE_DATE
, AC.CARDMEMBER_NAME
, AC.CARD_NUMBER
, AC.CARD_EXPIRATION_DATE
, PV.CUSTOMER_NUM
, PVSA.FAX_AREA_CODE || PVSA.FAX
, PVSA.EMAIL_ADDRESS
, PVC.FIRST_NAME||' '||PVC.MIDDLE_NAME||' '||PVC.LAST_NAME
, PVC.AREA_CODE||'-'||PVC.PHONE
, PHA.LAST_UPDATE_DATE
, PH.PCARD_ID
, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE
, PV.NUM_1099 NUM_1099
, 'ACKREQD' ACKREQD
, 'ACKBYDATE' ACKBYDATE
, 'SUPPNOTE' SUPPNOTE
, PHA.SHIP_TO_LOCATION_ID
, PHA.BILL_TO_LOCATION_ID
, PHA.TYPE_LOOKUP_CODE
FROM PO_RELEASES_ARCHIVE_ALL PRAA
, 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
, AP_CARDS_ALL AC
, PO_VENDOR_CONTACTS PVC
, PO_HEADERS_ALL PH
, GL_SETS_OF_BOOKS GSOB
WHERE PHA.PO_HEADER_ID = PRAA.PO_HEADER_ID
AND PHA.REVISION_NUM = 0
AND PHA.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED' )
AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID
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 GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_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 PLCFOB.LOOKUP_TYPE(+) = 'FOB'
AND PLCFOB.LOOKUP_CODE (+) = PHA.FOB_LOOKUP_CODE
AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE ( + ) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND APT.TERM_ID(+) = PHA.TERMS_ID
AND PHA.PCARD_ID = AC.CARD_ID ( + )
AND PVC.VENDOR_CONTACT_ID ( + ) = PHA.VENDOR_CONTACT_ID
AND PVC.VENDOR_SITE_ID ( + ) = PHA.VENDOR_SITE_ID