VIEW: APPS.ITG_PO_HEADER_ARCH_V

Source

View Text - Preformatted

SELECT PHA.PO_HEADER_ID POID, PHA.SEGMENT1 CONTRACTB, PHA.REVISION_NUM REVISION_NUM, -9999 RELEASE_ID, to_number(null) RELEASE_NUM, PHA.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE, PHA.CURRENCY_CODE PO_CURRENCY, PHA.ACCEPTANCE_REQUIRED_FLAG 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, DECODE(PV.ONE_TIME_FLAG, 'N', '0', '1') SUPP_OTF, 'Supplier' SUPP_PARTNER_TYPE, 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 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, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY, HOU_V.LOCATION_ID BUYING_ORG_LOCATION_ID, 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.LOCATION_ID BILLTO_ORG_LOCATION_ID, 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, '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.MEANING FOB_NAME, PLCFOB.DESCRIPTION FOB_DESC, PLCFR.LOOKUP_CODE FREIGHT_CODE, PLCFR.MEANING FREIGHT_NAME, PLCFR.DESCRIPTION FREIGHT_DESC, PHA.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER, PHA.NOTE_TO_VENDOR NOTE_TO_VENDOR, PHA.NOTE_TO_RECEIVER NOTE_TO_RECEIVER, PHA.COMMENTS COMMENTS, PHA.START_DATE_ACTIVE START_DATE_ACTIVE, PHA.END_DATE_ACTIVE END_DATE_ACTIVE, PHA.CONFIRMING_ORDER_FLAG CONFIRMING_ORDER_FLAG, PHA.RATE EXCHANGE_RATE, PHA.RATE_DATE EXCH_RATE_DATE, PHA.ATTRIBUTE1 DFF_HDR_ATTR1, PHA.ATTRIBUTE2 DFF_HDR_ATTR2, PHA.ATTRIBUTE3 DFF_HDR_ATTR3, PHA.ATTRIBUTE4 DFF_HDR_ATTR4, PHA.ATTRIBUTE5 DFF_HDR_ATTR5, PHA.ATTRIBUTE6 DFF_HDR_ATTR6, PHA.ATTRIBUTE7 DFF_HDR_ATTR7, PHA.ATTRIBUTE8 DFF_HDR_ATTR8, PHA.ATTRIBUTE9 DFF_HDR_ATTR9, PHA.ATTRIBUTE10 DFF_HDR_ATTR10, PHA.ATTRIBUTE11 DFF_HDR_ATTR11, PHA.ATTRIBUTE12 DFF_HDR_ATTR12, PHA.ATTRIBUTE13 DFF_HDR_ATTR13, PHA.ATTRIBUTE14 DFF_HDR_ATTR14, PHA.ATTRIBUTE15 DFF_HDR_ATTR15, PHA.ATTRIBUTE_CATEGORY DFF_HDR_ATTR16, AC.CARDMEMBER_NAME CARD_NAME, AC.CARD_NUMBER CARD_NUM, AC.CARD_EXPIRATION_DATE CARD_EXP_DATE, PV.ATTRIBUTE1 DFF_VEND_ATTR1, PV.ATTRIBUTE2 DFF_VEND_ATTR2, PV.ATTRIBUTE3 DFF_VEND_ATTR3, PV.ATTRIBUTE4 DFF_VEND_ATTR4, PV.ATTRIBUTE5 DFF_VEND_ATTR5, PV.ATTRIBUTE6 DFF_VEND_ATTR6, PV.ATTRIBUTE7 DFF_VEND_ATTR7, PV.ATTRIBUTE8 DFF_VEND_ATTR8, PV.ATTRIBUTE9 DFF_VEND_ATTR9, PV.ATTRIBUTE10 DFF_VEND_ATTR10, PV.ATTRIBUTE11 DFF_VEND_ATTR11, PV.ATTRIBUTE12 DFF_VEND_ATTR12, PV.ATTRIBUTE13 DFF_VEND_ATTR13, PV.ATTRIBUTE14 DFF_VEND_ATTR14, PV.ATTRIBUTE15 DFF_VEND_ATTR15, PV.ATTRIBUTE_CATEGORY DFF_VEND_ATTR16, PVSA.ATTRIBUTE1 DFF_VEND_SITE_ATTR1, PVSA.ATTRIBUTE2 DFF_VEND_SITE_ATTR2, PVSA.ATTRIBUTE3 DFF_VEND_SITE_ATTR3, PVSA.ATTRIBUTE4 DFF_VEND_SITE_ATTR4, PVSA.ATTRIBUTE5 DFF_VEND_SITE_ATTR5, PVSA.ATTRIBUTE6 DFF_VEND_SITE_ATTR6, PVSA.ATTRIBUTE7 DFF_VEND_SITE_ATTR7, PVSA.ATTRIBUTE8 DFF_VEND_SITE_ATTR8, PVSA.ATTRIBUTE9 DFF_VEND_SITE_ATTR9, PVSA.ATTRIBUTE10 DFF_VEND_SITE_ATTR10, PVSA.ATTRIBUTE11 DFF_VEND_SITE_ATTR11, PVSA.ATTRIBUTE12 DFF_VEND_SITE_ATTR12, PVSA.ATTRIBUTE13 DFF_VEND_SITE_ATTR13, PVSA.ATTRIBUTE14 DFF_VEND_SITE_ATTR14, PVSA.ATTRIBUTE15 DFF_VEND_SITE_ATTR15, PVSA.ATTRIBUTE_CATEGORY DFF_VEND_SITE_ATTR16, PV.CUSTOMER_NUM CUSTOMER_NUM, PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX, PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL, PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' ' || PVC.LAST_NAME SUP_CONTACT_NAME, PVC.AREA_CODE || '-' || PVC.PHONE SUP_CONTACT_PHONE, PHA.LAST_UPDATE_DATE LAST_UPDATE_DATE, PH.PCARD_ID PCARD_ID, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE, PV.NUM_1099 NUM_1099, PHA.REVISION_NUM PO_REVISION_NUM, PHA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG, PHA.SHIPPING_CONTROL SHIPPING_CONTROL, DECODE(PHA.CANCEL_FLAG, 'Y', 'CANCELLED', 'OPEN') POSTATUS, HLAB.LOCATION_CODE BILL_TO_LOCATION_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, FND_LOOKUP_VALUES_VL PLCFOB, FND_LOOKUP_VALUES_VL 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 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 NVL(PVSA.ORG_ID,-99) = NVL(PHA.ORG_ID,-99) and NVL(FSPA.ORG_ID,-99) = NVL(PHA.ORG_ID,-99) 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 >= trunc(SYSDATE) and PAPF.EFFECTIVE_START_DATE <= trunc(SYSDATE) and PLCFOB.LOOKUP_TYPE(+) = 'FOB' and PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE and PLCFOB.VIEW_APPLICATION_ID(+) = 201 and PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS' and PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE and PLCFR.VIEW_APPLICATION_ID(+) = 201 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 and PH.PO_HEADER_ID = PHA.PO_HEADER_ID and GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID union all SELECT PHA.PO_HEADER_ID POID, PHA.SEGMENT1 CONTRACTB, PRAA.REVISION_NUM REVISION_NUM, PRAA.PO_RELEASE_ID RELEASE_ID, PRAA.RELEASE_NUM RELEASE_NUM, 'RELEASE' TYPE_LOOKUP_CODE, PHA.CURRENCY_CODE PO_CURRENCY, PRAA.ACCEPTANCE_REQUIRED_FLAG ACCEPTANCE_REQUIRED_FLAG, PRAA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE, PRAA.RELEASE_DATE CREATION_DATE, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM, PV.VENDOR_NAME SUPP_ORG_NAME, DECODE(PV.ONE_TIME_FLAG, 'N', '0', '1') SUPP_OTF, 'Supplier' SUPP_PARTNER_TYPE, PV.SEGMENT1 SUPPLIER_PARTNER_ID, NVL(PVSA.ECE_TP_LOCATION_CODE, PHA.VENDOR_ID) SUPPLIER_PARTNER_ID_X, POGOT_S.ECX_GET_TOTAL('R', PRAA.PO_RELEASE_ID, PHA.CURRENCY_CODE) ORDER_TOTAL, PV.PAYMENT_CURRENCY_CODE SUPP_CURRENCY, PV.VAT_REGISTRATION_NUM 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, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY, HOU_V.LOCATION_ID BUYING_ORG_LOCATION_ID, 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, PRAA.AGENT_ID BUYER_ID, PAPF.FULL_NAME BUYER_FULL_NAME, PAPF.EMAIL_ADDRESS BUYER_EMAIL, HR_GENERAL.GET_WORK_PHONE(PRAA.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.LOCATION_ID BILLTO_ORG_LOCATION_ID, 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, '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.MEANING FOB_NAME, PLCFOB.DESCRIPTION FOB_DESC, PLCFR.LOOKUP_CODE FREIGHT_CODE, PLCFR.MEANING FREIGHT_NAME, PLCFR.DESCRIPTION FREIGHT_DESC, PHA.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER, PHA.NOTE_TO_VENDOR NOTE_TO_VENDOR, PHA.NOTE_TO_RECEIVER NOTE_TO_RECEIVER, PHA.COMMENTS COMMENTS, PHA.START_DATE_ACTIVE START_DATE_ACTIVE, PHA.END_DATE_ACTIVE END_DATE_ACTIVE, PHA.CONFIRMING_ORDER_FLAG CONFIRMING_ORDER_FLAG, PHA.RATE EXCHANGE_RATE, PHA.RATE_DATE EXCH_RATE_DATE, PHA.ATTRIBUTE1 DFF_HDR_ATTR1, PHA.ATTRIBUTE2 DFF_HDR_ATTR2, PHA.ATTRIBUTE3 DFF_HDR_ATTR3, PHA.ATTRIBUTE4 DFF_HDR_ATTR4, PHA.ATTRIBUTE5 DFF_HDR_ATTR5, PHA.ATTRIBUTE6 DFF_HDR_ATTR6, PHA.ATTRIBUTE7 DFF_HDR_ATTR7, PHA.ATTRIBUTE8 DFF_HDR_ATTR8, PHA.ATTRIBUTE9 DFF_HDR_ATTR9, PHA.ATTRIBUTE10 DFF_HDR_ATTR10, PHA.ATTRIBUTE11 DFF_HDR_ATTR11, PHA.ATTRIBUTE12 DFF_HDR_ATTR12, PHA.ATTRIBUTE13 DFF_HDR_ATTR13, PHA.ATTRIBUTE14 DFF_HDR_ATTR14, PHA.ATTRIBUTE15 DFF_HDR_ATTR15, PHA.ATTRIBUTE_CATEGORY DFF_HDR_ATTR16, AC.CARDMEMBER_NAME CARD_NAME, AC.CARD_NUMBER CARD_NUM, AC.CARD_EXPIRATION_DATE CARD_EXP_DATE, PV.ATTRIBUTE1 DFF_VEND_ATTR1, PV.ATTRIBUTE2 DFF_VEND_ATTR2, PV.ATTRIBUTE3 DFF_VEND_ATTR3, PV.ATTRIBUTE4 DFF_VEND_ATTR4, PV.ATTRIBUTE5 DFF_VEND_ATTR5, PV.ATTRIBUTE6 DFF_VEND_ATTR6, PV.ATTRIBUTE7 DFF_VEND_ATTR7, PV.ATTRIBUTE8 DFF_VEND_ATTR8, PV.ATTRIBUTE9 DFF_VEND_ATTR9, PV.ATTRIBUTE10 DFF_VEND_ATTR10, PV.ATTRIBUTE11 DFF_VEND_ATTR11, PV.ATTRIBUTE12 DFF_VEND_ATTR12, PV.ATTRIBUTE13 DFF_VEND_ATTR13, PV.ATTRIBUTE14 DFF_VEND_ATTR14, PV.ATTRIBUTE15 DFF_VEND_ATTR15, PV.ATTRIBUTE_CATEGORY DFF_VEND_ATTR16, PVSA.ATTRIBUTE1 DFF_VEND_SITE_ATTR1, PVSA.ATTRIBUTE2 DFF_VEND_SITE_ATTR2, PVSA.ATTRIBUTE3 DFF_VEND_SITE_ATTR3, PVSA.ATTRIBUTE4 DFF_VEND_SITE_ATTR4, PVSA.ATTRIBUTE5 DFF_VEND_SITE_ATTR5, PVSA.ATTRIBUTE6 DFF_VEND_SITE_ATTR6, PVSA.ATTRIBUTE7 DFF_VEND_SITE_ATTR7, PVSA.ATTRIBUTE8 DFF_VEND_SITE_ATTR8, PVSA.ATTRIBUTE9 DFF_VEND_SITE_ATTR9, PVSA.ATTRIBUTE10 DFF_VEND_SITE_ATTR10, PVSA.ATTRIBUTE11 DFF_VEND_SITE_ATTR11, PVSA.ATTRIBUTE12 DFF_VEND_SITE_ATTR12, PVSA.ATTRIBUTE13 DFF_VEND_SITE_ATTR13, PVSA.ATTRIBUTE14 DFF_VEND_SITE_ATTR14, PVSA.ATTRIBUTE15 DFF_VEND_SITE_ATTR15, PVSA.ATTRIBUTE_CATEGORY DFF_VEND_SITE_ATTR16, PV.CUSTOMER_NUM CUSTOMER_NUM, PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX, PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL, PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' ' || PVC.LAST_NAME SUP_CONTACT_NAME, PVC.AREA_CODE || '-' || PVC.PHONE SUP_CONTACT_PHONE, PHA.LAST_UPDATE_DATE LAST_UPDATE_DATE, PH.PCARD_ID PCARD_ID, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE, PV.NUM_1099 NUM_1099, PHA.REVISION_NUM PO_REVISION_NUM, PRAA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG, PHA.SHIPPING_CONTROL SHIPPING_CONTROL, DECODE(PHA.CANCEL_FLAG, 'Y', 'CANCELLED', 'OPEN') POSTATUS, HLAB.LOCATION_CODE BILL_TO_LOCATION_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, fnd_lookup_values_vl PLCFOB, fnd_lookup_values_vl 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.LATEST_EXTERNAL_FLAG = 'Y' 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 NVL(PVSA.ORG_ID,-99) = NVL(PHA.ORG_ID,-99) and NVL(FSPA.ORG_ID,-99) = NVL(PHA.ORG_ID,-99) and HOU_V.ORGANIZATION_ID(+) = PHA.ORG_ID and HLAB.LOCATION_ID = PHA.BILL_TO_LOCATION_ID and PAPF.PERSON_ID = PRAA.AGENT_ID and PAPF.EFFECTIVE_END_DATE >= trunc(SYSDATE) and PAPF.EFFECTIVE_START_DATE <= trunc(SYSDATE) and PLCFOB.LOOKUP_TYPE(+) = 'FOB' and PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE and PLCFOB.VIEW_APPLICATION_ID(+) = 201 and PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS' and PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE and PLCFR.VIEW_APPLICATION_ID(+) = 201 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 and PH.PO_HEADER_ID = PHA.PO_HEADER_ID and GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT PHA.PO_HEADER_ID POID
, PHA.SEGMENT1 CONTRACTB
, PHA.REVISION_NUM REVISION_NUM
, -9999 RELEASE_ID
, TO_NUMBER(NULL) RELEASE_NUM
, PHA.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, PHA.CURRENCY_CODE PO_CURRENCY
, PHA.ACCEPTANCE_REQUIRED_FLAG 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
, DECODE(PV.ONE_TIME_FLAG
, 'N'
, '0'
, '1') SUPP_OTF
, 'SUPPLIER' SUPP_PARTNER_TYPE
, 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 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
, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X
, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.LOCATION_ID BUYING_ORG_LOCATION_ID
, 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.LOCATION_ID BILLTO_ORG_LOCATION_ID
, 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
, '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.MEANING FOB_NAME
, PLCFOB.DESCRIPTION FOB_DESC
, PLCFR.LOOKUP_CODE FREIGHT_CODE
, PLCFR.MEANING FREIGHT_NAME
, PLCFR.DESCRIPTION FREIGHT_DESC
, PHA.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER
, PHA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER NOTE_TO_RECEIVER
, PHA.COMMENTS COMMENTS
, PHA.START_DATE_ACTIVE START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE END_DATE_ACTIVE
, PHA.CONFIRMING_ORDER_FLAG CONFIRMING_ORDER_FLAG
, PHA.RATE EXCHANGE_RATE
, PHA.RATE_DATE EXCH_RATE_DATE
, PHA.ATTRIBUTE1 DFF_HDR_ATTR1
, PHA.ATTRIBUTE2 DFF_HDR_ATTR2
, PHA.ATTRIBUTE3 DFF_HDR_ATTR3
, PHA.ATTRIBUTE4 DFF_HDR_ATTR4
, PHA.ATTRIBUTE5 DFF_HDR_ATTR5
, PHA.ATTRIBUTE6 DFF_HDR_ATTR6
, PHA.ATTRIBUTE7 DFF_HDR_ATTR7
, PHA.ATTRIBUTE8 DFF_HDR_ATTR8
, PHA.ATTRIBUTE9 DFF_HDR_ATTR9
, PHA.ATTRIBUTE10 DFF_HDR_ATTR10
, PHA.ATTRIBUTE11 DFF_HDR_ATTR11
, PHA.ATTRIBUTE12 DFF_HDR_ATTR12
, PHA.ATTRIBUTE13 DFF_HDR_ATTR13
, PHA.ATTRIBUTE14 DFF_HDR_ATTR14
, PHA.ATTRIBUTE15 DFF_HDR_ATTR15
, PHA.ATTRIBUTE_CATEGORY DFF_HDR_ATTR16
, AC.CARDMEMBER_NAME CARD_NAME
, AC.CARD_NUMBER CARD_NUM
, AC.CARD_EXPIRATION_DATE CARD_EXP_DATE
, PV.ATTRIBUTE1 DFF_VEND_ATTR1
, PV.ATTRIBUTE2 DFF_VEND_ATTR2
, PV.ATTRIBUTE3 DFF_VEND_ATTR3
, PV.ATTRIBUTE4 DFF_VEND_ATTR4
, PV.ATTRIBUTE5 DFF_VEND_ATTR5
, PV.ATTRIBUTE6 DFF_VEND_ATTR6
, PV.ATTRIBUTE7 DFF_VEND_ATTR7
, PV.ATTRIBUTE8 DFF_VEND_ATTR8
, PV.ATTRIBUTE9 DFF_VEND_ATTR9
, PV.ATTRIBUTE10 DFF_VEND_ATTR10
, PV.ATTRIBUTE11 DFF_VEND_ATTR11
, PV.ATTRIBUTE12 DFF_VEND_ATTR12
, PV.ATTRIBUTE13 DFF_VEND_ATTR13
, PV.ATTRIBUTE14 DFF_VEND_ATTR14
, PV.ATTRIBUTE15 DFF_VEND_ATTR15
, PV.ATTRIBUTE_CATEGORY DFF_VEND_ATTR16
, PVSA.ATTRIBUTE1 DFF_VEND_SITE_ATTR1
, PVSA.ATTRIBUTE2 DFF_VEND_SITE_ATTR2
, PVSA.ATTRIBUTE3 DFF_VEND_SITE_ATTR3
, PVSA.ATTRIBUTE4 DFF_VEND_SITE_ATTR4
, PVSA.ATTRIBUTE5 DFF_VEND_SITE_ATTR5
, PVSA.ATTRIBUTE6 DFF_VEND_SITE_ATTR6
, PVSA.ATTRIBUTE7 DFF_VEND_SITE_ATTR7
, PVSA.ATTRIBUTE8 DFF_VEND_SITE_ATTR8
, PVSA.ATTRIBUTE9 DFF_VEND_SITE_ATTR9
, PVSA.ATTRIBUTE10 DFF_VEND_SITE_ATTR10
, PVSA.ATTRIBUTE11 DFF_VEND_SITE_ATTR11
, PVSA.ATTRIBUTE12 DFF_VEND_SITE_ATTR12
, PVSA.ATTRIBUTE13 DFF_VEND_SITE_ATTR13
, PVSA.ATTRIBUTE14 DFF_VEND_SITE_ATTR14
, PVSA.ATTRIBUTE15 DFF_VEND_SITE_ATTR15
, PVSA.ATTRIBUTE_CATEGORY DFF_VEND_SITE_ATTR16
, PV.CUSTOMER_NUM CUSTOMER_NUM
, PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX
, PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL
, PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' ' || PVC.LAST_NAME SUP_CONTACT_NAME
, PVC.AREA_CODE || '-' || PVC.PHONE SUP_CONTACT_PHONE
, PHA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PH.PCARD_ID PCARD_ID
, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE
, PV.NUM_1099 NUM_1099
, PHA.REVISION_NUM PO_REVISION_NUM
, PHA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG
, PHA.SHIPPING_CONTROL SHIPPING_CONTROL
, DECODE(PHA.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, 'OPEN') POSTATUS
, HLAB.LOCATION_CODE BILL_TO_LOCATION_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
, FND_LOOKUP_VALUES_VL PLCFOB
, FND_LOOKUP_VALUES_VL 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 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 NVL(PVSA.ORG_ID
, -99) = NVL(PHA.ORG_ID
, -99)
AND NVL(FSPA.ORG_ID
, -99) = NVL(PHA.ORG_ID
, -99)
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 >= TRUNC(SYSDATE)
AND PAPF.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PLCFOB.LOOKUP_TYPE(+) = 'FOB'
AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE
AND PLCFOB.VIEW_APPLICATION_ID(+) = 201
AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND PLCFR.VIEW_APPLICATION_ID(+) = 201
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
AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID
AND GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID UNION ALL SELECT PHA.PO_HEADER_ID POID
, PHA.SEGMENT1 CONTRACTB
, PRAA.REVISION_NUM REVISION_NUM
, PRAA.PO_RELEASE_ID RELEASE_ID
, PRAA.RELEASE_NUM RELEASE_NUM
, 'RELEASE' TYPE_LOOKUP_CODE
, PHA.CURRENCY_CODE PO_CURRENCY
, PRAA.ACCEPTANCE_REQUIRED_FLAG ACCEPTANCE_REQUIRED_FLAG
, PRAA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PRAA.RELEASE_DATE CREATION_DATE
, PHA.VENDOR_ORDER_NUM SUPPLIER_ORDER_NUM
, PV.VENDOR_NAME SUPP_ORG_NAME
, DECODE(PV.ONE_TIME_FLAG
, 'N'
, '0'
, '1') SUPP_OTF
, 'SUPPLIER' SUPP_PARTNER_TYPE
, PV.SEGMENT1 SUPPLIER_PARTNER_ID
, NVL(PVSA.ECE_TP_LOCATION_CODE
, PHA.VENDOR_ID) SUPPLIER_PARTNER_ID_X
, POGOT_S.ECX_GET_TOTAL('R'
, PRAA.PO_RELEASE_ID
, PHA.CURRENCY_CODE) ORDER_TOTAL
, PV.PAYMENT_CURRENCY_CODE SUPP_CURRENCY
, PV.VAT_REGISTRATION_NUM 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
, HLAB.ECE_TP_LOCATION_CODE PARTNER_ID_X
, GSOB.CURRENCY_CODE BUYING_ORG_CURRENCY
, HOU_V.LOCATION_ID BUYING_ORG_LOCATION_ID
, 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
, PRAA.AGENT_ID BUYER_ID
, PAPF.FULL_NAME BUYER_FULL_NAME
, PAPF.EMAIL_ADDRESS BUYER_EMAIL
, HR_GENERAL.GET_WORK_PHONE(PRAA.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.LOCATION_ID BILLTO_ORG_LOCATION_ID
, 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
, '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.MEANING FOB_NAME
, PLCFOB.DESCRIPTION FOB_DESC
, PLCFR.LOOKUP_CODE FREIGHT_CODE
, PLCFR.MEANING FREIGHT_NAME
, PLCFR.DESCRIPTION FREIGHT_DESC
, PHA.NOTE_TO_AUTHORIZER NOTE_TO_AUTHORIZER
, PHA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER NOTE_TO_RECEIVER
, PHA.COMMENTS COMMENTS
, PHA.START_DATE_ACTIVE START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE END_DATE_ACTIVE
, PHA.CONFIRMING_ORDER_FLAG CONFIRMING_ORDER_FLAG
, PHA.RATE EXCHANGE_RATE
, PHA.RATE_DATE EXCH_RATE_DATE
, PHA.ATTRIBUTE1 DFF_HDR_ATTR1
, PHA.ATTRIBUTE2 DFF_HDR_ATTR2
, PHA.ATTRIBUTE3 DFF_HDR_ATTR3
, PHA.ATTRIBUTE4 DFF_HDR_ATTR4
, PHA.ATTRIBUTE5 DFF_HDR_ATTR5
, PHA.ATTRIBUTE6 DFF_HDR_ATTR6
, PHA.ATTRIBUTE7 DFF_HDR_ATTR7
, PHA.ATTRIBUTE8 DFF_HDR_ATTR8
, PHA.ATTRIBUTE9 DFF_HDR_ATTR9
, PHA.ATTRIBUTE10 DFF_HDR_ATTR10
, PHA.ATTRIBUTE11 DFF_HDR_ATTR11
, PHA.ATTRIBUTE12 DFF_HDR_ATTR12
, PHA.ATTRIBUTE13 DFF_HDR_ATTR13
, PHA.ATTRIBUTE14 DFF_HDR_ATTR14
, PHA.ATTRIBUTE15 DFF_HDR_ATTR15
, PHA.ATTRIBUTE_CATEGORY DFF_HDR_ATTR16
, AC.CARDMEMBER_NAME CARD_NAME
, AC.CARD_NUMBER CARD_NUM
, AC.CARD_EXPIRATION_DATE CARD_EXP_DATE
, PV.ATTRIBUTE1 DFF_VEND_ATTR1
, PV.ATTRIBUTE2 DFF_VEND_ATTR2
, PV.ATTRIBUTE3 DFF_VEND_ATTR3
, PV.ATTRIBUTE4 DFF_VEND_ATTR4
, PV.ATTRIBUTE5 DFF_VEND_ATTR5
, PV.ATTRIBUTE6 DFF_VEND_ATTR6
, PV.ATTRIBUTE7 DFF_VEND_ATTR7
, PV.ATTRIBUTE8 DFF_VEND_ATTR8
, PV.ATTRIBUTE9 DFF_VEND_ATTR9
, PV.ATTRIBUTE10 DFF_VEND_ATTR10
, PV.ATTRIBUTE11 DFF_VEND_ATTR11
, PV.ATTRIBUTE12 DFF_VEND_ATTR12
, PV.ATTRIBUTE13 DFF_VEND_ATTR13
, PV.ATTRIBUTE14 DFF_VEND_ATTR14
, PV.ATTRIBUTE15 DFF_VEND_ATTR15
, PV.ATTRIBUTE_CATEGORY DFF_VEND_ATTR16
, PVSA.ATTRIBUTE1 DFF_VEND_SITE_ATTR1
, PVSA.ATTRIBUTE2 DFF_VEND_SITE_ATTR2
, PVSA.ATTRIBUTE3 DFF_VEND_SITE_ATTR3
, PVSA.ATTRIBUTE4 DFF_VEND_SITE_ATTR4
, PVSA.ATTRIBUTE5 DFF_VEND_SITE_ATTR5
, PVSA.ATTRIBUTE6 DFF_VEND_SITE_ATTR6
, PVSA.ATTRIBUTE7 DFF_VEND_SITE_ATTR7
, PVSA.ATTRIBUTE8 DFF_VEND_SITE_ATTR8
, PVSA.ATTRIBUTE9 DFF_VEND_SITE_ATTR9
, PVSA.ATTRIBUTE10 DFF_VEND_SITE_ATTR10
, PVSA.ATTRIBUTE11 DFF_VEND_SITE_ATTR11
, PVSA.ATTRIBUTE12 DFF_VEND_SITE_ATTR12
, PVSA.ATTRIBUTE13 DFF_VEND_SITE_ATTR13
, PVSA.ATTRIBUTE14 DFF_VEND_SITE_ATTR14
, PVSA.ATTRIBUTE15 DFF_VEND_SITE_ATTR15
, PVSA.ATTRIBUTE_CATEGORY DFF_VEND_SITE_ATTR16
, PV.CUSTOMER_NUM CUSTOMER_NUM
, PVSA.FAX_AREA_CODE || PVSA.FAX SUPPLIER_FAX
, PVSA.EMAIL_ADDRESS SUPPLIER_EMAIL
, PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' ' || PVC.LAST_NAME SUP_CONTACT_NAME
, PVC.AREA_CODE || '-' || PVC.PHONE SUP_CONTACT_PHONE
, PHA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PH.PCARD_ID PCARD_ID
, PVSA.VENDOR_SITE_CODE SUP_SITE_CODE
, PV.NUM_1099 NUM_1099
, PHA.REVISION_NUM PO_REVISION_NUM
, PRAA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG
, PHA.SHIPPING_CONTROL SHIPPING_CONTROL
, DECODE(PHA.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, 'OPEN') POSTATUS
, HLAB.LOCATION_CODE BILL_TO_LOCATION_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
, FND_LOOKUP_VALUES_VL PLCFOB
, FND_LOOKUP_VALUES_VL 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.LATEST_EXTERNAL_FLAG = 'Y'
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 NVL(PVSA.ORG_ID
, -99) = NVL(PHA.ORG_ID
, -99)
AND NVL(FSPA.ORG_ID
, -99) = NVL(PHA.ORG_ID
, -99)
AND HOU_V.ORGANIZATION_ID(+) = PHA.ORG_ID
AND HLAB.LOCATION_ID = PHA.BILL_TO_LOCATION_ID
AND PAPF.PERSON_ID = PRAA.AGENT_ID
AND PAPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND PAPF.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PLCFOB.LOOKUP_TYPE(+) = 'FOB'
AND PLCFOB.LOOKUP_CODE(+) = PHA.FOB_LOOKUP_CODE
AND PLCFOB.VIEW_APPLICATION_ID(+) = 201
AND PLCFR.LOOKUP_TYPE(+) = 'FREIGHT TERMS'
AND PLCFR.LOOKUP_CODE(+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND PLCFR.VIEW_APPLICATION_ID(+) = 201
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
AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID
AND GSOB.SET_OF_BOOKS_ID = FSPA.SET_OF_BOOKS_ID