View: ECE_POO_HEADERS_V

Product:	EC - e-Commerce Gateway
Description:	/*#* This view extracts header information* for the outbound Purchase Order (850/ORDERS) transaction.* @rep:scope private* @rep:product PO * @rep:lifecycle active* @rep:displayname Purchase Order Header View* @rep:category BUSINESS_ENTI
Implementation/DBA Data:	APPS.ECE_POO_HEADERS_V

View Text

SELECT 'EDI' COMMUNICATION_METHOD
, ETPD.TEST_FLAG TEST_FLAG
, 'POO' DOCUMENT_ID
, PH.TYPE_LOOKUP_CODE DOCUMENT_TYPE
, PH.SEGMENT1 DOCUMENT_CODE
, ETPD.TRANSLATOR_CODE TRANSLATOR_CODE
, PVS.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, ETPH.TP_DESCRIPTION TP_DESCRIPTION
, ETPH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETPH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, SYSDATE TRANSACTION_DATE
, ETPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY
, ETPH.ATTRIBUTE1 TPH_ATTRIBUTE1
, ETPH.ATTRIBUTE2 TPH_ATTRIBUTE2
, ETPH.ATTRIBUTE3 TPH_ATTRIBUTE3
, ETPH.ATTRIBUTE4 TPH_ATTRIBUTE4
, ETPH.ATTRIBUTE5 TPH_ATTRIBUTE5
, ETPH.ATTRIBUTE6 TPH_ATTRIBUTE6
, ETPH.ATTRIBUTE7 TPH_ATTRIBUTE7
, ETPH.ATTRIBUTE8 TPH_ATTRIBUTE8
, ETPH.ATTRIBUTE9 TPH_ATTRIBUTE9
, ETPH.ATTRIBUTE10 TPH_ATTRIBUTE10
, ETPH.ATTRIBUTE11 TPH_ATTRIBUTE11
, ETPH.ATTRIBUTE12 TPH_ATTRIBUTE12
, ETPH.ATTRIBUTE13 TPH_ATTRIBUTE13
, ETPH.ATTRIBUTE14 TPH_ATTRIBUTE14
, ETPH.ATTRIBUTE15 TPH_ATTRIBUTE15
, ETPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY
, ETPD.ATTRIBUTE1 TPD_ATTRIBUTE1
, ETPD.ATTRIBUTE2 TPD_ATTRIBUTE2
, ETPD.ATTRIBUTE3 TPD_ATTRIBUTE3
, ETPD.ATTRIBUTE4 TPD_ATTRIBUTE4
, ETPD.ATTRIBUTE5 TPD_ATTRIBUTE5
, PH.SEGMENT1 PO_NUMBER
, 0 POR_RELEASE_ID
, 0 POR_RELEASE_NUM
, TO_DATE(NULL) POR_RELEASE_DATE
, PH.CREATION_DATE CREATION_DATE
, PH.REVISION_NUM REVISION_NUM
, TO_DATE(PH.REVISED_DATE
, 'DD-MON-RR HH24:MI') REVISED_DATE
, PH.COMMENTS COMMENTS
, PH.TYPE_LOOKUP_CODE PO_TYPE
, T.NAME PAYMENT_TERMS
, FCC.CURRENCY_CODE CURRENCY_CODE
, PH.RATE CURRENCY_RATE
, PH.SHIP_VIA_LOOKUP_CODE SHIP_VIA
, PH.FOB_LOOKUP_CODE FOB_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS
, PH.CANCEL_FLAG CANCEL_FLAG
, PH.ACCEPTANCE_REQUIRED_FLAG ACCEPTANCE_REQUIRED_FLAG
, PH.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, PH.CONFIRMING_ORDER_FLAG CONFIRMING_ORDER_FLAG
, PH.START_DATE BLANKET_START_DATE
, PH.END_DATE BLANKET_END_DATE
, NVL(PH.BLANKET_TOTAL_AMOUNT
, '') BLANKET_TOTAL_AMOUNT
, VN.SEGMENT1 SUPPLIER_NUMBER
, VN.VENDOR_NAME SUPPLIER_NAME
, PH.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PH.ATTRIBUTE_CATEGORY PO_ATTRIBUTE_CATEGORY
, PH.ATTRIBUTE1 PO_ATTRIBUTE1
, PH.ATTRIBUTE2 PO_ATTRIBUTE2
, PH.ATTRIBUTE3 PO_ATTRIBUTE3
, PH.ATTRIBUTE4 PO_ATTRIBUTE4
, PH.ATTRIBUTE5 PO_ATTRIBUTE5
, PH.ATTRIBUTE6 PO_ATTRIBUTE6
, PH.ATTRIBUTE7 PO_ATTRIBUTE7
, PH.ATTRIBUTE8 PO_ATTRIBUTE8
, PH.ATTRIBUTE9 PO_ATTRIBUTE9
, PH.ATTRIBUTE10 PO_ATTRIBUTE10
, PH.ATTRIBUTE11 PO_ATTRIBUTE11
, PH.ATTRIBUTE12 PO_ATTRIBUTE12
, PH.ATTRIBUTE13 PO_ATTRIBUTE13
, PH.ATTRIBUTE14 PO_ATTRIBUTE14
, PH.ATTRIBUTE15 PO_ATTRIBUTE15
, VN.ATTRIBUTE_CATEGORY SU_ATTRIBUTE_CATEGORY
, VN.ATTRIBUTE1 SU_ATTRIBUTE1
, VN.ATTRIBUTE2 SU_ATTRIBUTE2
, VN.ATTRIBUTE3 SU_ATTRIBUTE3
, VN.ATTRIBUTE4 SU_ATTRIBUTE4
, VN.ATTRIBUTE5 SU_ATTRIBUTE5
, VN.ATTRIBUTE6 SU_ATTRIBUTE6
, VN.ATTRIBUTE7 SU_ATTRIBUTE7
, VN.ATTRIBUTE8 SU_ATTRIBUTE8
, VN.ATTRIBUTE9 SU_ATTRIBUTE9
, VN.ATTRIBUTE10 SU_ATTRIBUTE10
, VN.ATTRIBUTE11 SU_ATTRIBUTE11
, VN.ATTRIBUTE12 SU_ATTRIBUTE12
, VN.ATTRIBUTE13 SU_ATTRIBUTE13
, VN.ATTRIBUTE14 SU_ATTRIBUTE14
, VN.ATTRIBUTE15 SU_ATTRIBUTE15
, PVS.ATTRIBUTE_CATEGORY SS_ATTRIBUTE_CATEGORY
, PVS.ATTRIBUTE1 SS_ATTRIBUTE1
, PVS.ATTRIBUTE2 SS_ATTRIBUTE2
, PVS.ATTRIBUTE3 SS_ATTRIBUTE3
, PVS.ATTRIBUTE4 SS_ATTRIBUTE4
, PVS.ATTRIBUTE5 SS_ATTRIBUTE5
, PVS.ATTRIBUTE6 SS_ATTRIBUTE6
, PVS.ATTRIBUTE7 SS_ATTRIBUTE7
, PVS.ATTRIBUTE8 SS_ATTRIBUTE8
, PVS.ATTRIBUTE9 SS_ATTRIBUTE9
, PVS.ATTRIBUTE10 SS_ATTRIBUTE10
, PVS.ATTRIBUTE11 SS_ATTRIBUTE11
, PVS.ATTRIBUTE12 SS_ATTRIBUTE12
, PVS.ATTRIBUTE13 SS_ATTRIBUTE13
, PVS.ATTRIBUTE14 SS_ATTRIBUTE14
, PVS.ATTRIBUTE15 SS_ATTRIBUTE15
, VN.CUSTOMER_NUM CUSTOMER_NUMBER
, PH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.ADDRESS_LINE1 SU_ADDRESS_LINE1
, PVS.ADDRESS_LINE2 SU_ADDRESS_LINE2
, PVS.ADDRESS_LINE3 SU_ADDRESS_LINE3
, PVS.ADDRESS_LINE4 SU_ADDRESS_LINE4
, PVS.CITY SU_CITY
, PVS.ZIP SU_ZIP
, PVS.COUNTRY SU_COUNTRY
, PVS.STATE SU_STATE
, PVS.PROVINCE SU_PROVINCE
, PVS.AREA_CODE SU_AREA_CODE
, PVS.PHONE SU_PHONE
, PVS.FAX_AREA_CODE SU_FAX_AREA_CODE
, PVS.FAX SU_FAX
, PVS.TELEX SU_TELEX
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
, PVC.LAST_NAME CN_LAST_NAME
, PVC.FIRST_NAME CN_FIRST_NAME
, PVC.AREA_CODE CN_AREA_CODE
, PVC.PHONE CN_PHONE
, PH.SHIP_TO_LOCATION_ID ST_LOCATION_ID
, LC.LOCATION_CODE ST_LOCATION_CODE
, LC.ECE_TP_LOCATION_CODE ST_EDI_LOCATION_CODE
, HRA.LAST_NAME ST_CONTACT_LAST_NAME
, HRA.FIRST_NAME ST_CONTACT_FIRST_NAME
, LC.DESCRIPTION ST_NAME
, LC.ADDRESS_LINE_1 ST_ADDRESS_LINE1
, LC.ADDRESS_LINE_2 ST_ADDRESS_LINE2
, LC.ADDRESS_LINE_3 ST_ADDRESS_LINE3
, LC.TOWN_OR_CITY ST_CITY
, LC.POSTAL_CODE ST_POSTAL_CODE
, LC.COUNTRY ST_COUNTRY
, LC.REGION_1 ST_REGION1
, LC.REGION_2 ST_REGION2
, LC.REGION_3 ST_REGION3
, LC.TELEPHONE_NUMBER_1 ST_PHONE1
, LC.TELEPHONE_NUMBER_2 ST_PHONE2
, LC.TELEPHONE_NUMBER_3 ST_PHONE3
, PH.BILL_TO_LOCATION_ID BT_LOCATION_ID
, LC2.LOCATION_CODE BT_LOCATION_CODE
, LC2.ECE_TP_LOCATION_CODE BT_EDI_LOCATION_CODE
, HRB.LAST_NAME BT_CONTACT_LAST_NAME
, HRB.FIRST_NAME BT_CONTACT_FIRST_NAME
, LT2.DESCRIPTION BT_NAME
, LC2.ADDRESS_LINE_1 BT_ADDRESS_LINE1
, LC2.ADDRESS_LINE_2 BT_ADDRESS_LINE2
, LC2.ADDRESS_LINE_3 BT_ADDRESS_LINE3
, LC2.TOWN_OR_CITY BT_CITY
, LC2.POSTAL_CODE BT_POSTAL_CODE
, LC2.COUNTRY BT_COUNTRY
, LC2.REGION_1 BT_REGION1
, LC2.REGION_2 BT_REGION2
, LC2.REGION_3 BT_REGION3
, LC2.TELEPHONE_NUMBER_1 BT_PHONE1
, LC2.TELEPHONE_NUMBER_2 BT_PHONE2
, LC2.TELEPHONE_NUMBER_3 BT_PHONE3
, PPF.LAST_NAME BUYER_LAST_NAME
, PPF.FIRST_NAME BUYER_FIRST_NAME
, PPF.EMAIL_ADDRESS BUYER_EMAIL_ADDRESS
, PP.PHONE_NUMBER BUYER_WORK_TELEPHONE
, PA.TELEPHONE_NUMBER_1 BUYER_MISC_TELEPHONE1
, PA.TELEPHONE_NUMBER_2 BUYER_MISC_TELEPHONE2
, PA.TELEPHONE_NUMBER_3 BUYER_MISC_TELEPHONE3
, PH.PO_HEADER_ID PO_HEADER_ID
, ETPD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, DECODE (APCP.CARD_TYPE_LOOKUP_CODE
, 'SUPPLIER'
, VN2.VENDOR_NAME
, APC.CARDMEMBER_NAME) PCARDMEMBER_NAME
, ICC.MASKED_CC_NUMBER PCARD_NUMBER
, APC.CARD_EXPIRATION_DATE PCARD_EXPIRATION_DATE
, APCP.CARD_BRAND_LOOKUP_CODE PCARD_BRAND
, ETPD.MAP_ID MAP_ID
, PH.ORG_ID ORG_ID
, PH.SHIPPING_CONTROL SHIPPING_CONTROL
FROM PO_HEADERS_ARCHIVE PHA
, PO_HEADERS PH
, ECE_TP_HEADERS ETPH
, ECE_TP_DETAILS ETPD
, FND_CURRENCIES_VL FCC
, PO_VENDORS VN
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PPF
, PER_ALL_PEOPLE_F HRA
, PER_PHONES PP
, PER_ALL_PEOPLE_F HRB
, HR_LOCATIONS LC
, HR_LOCATIONS_ALL LC2
, HR_LOCATIONS_ALL_TL LT2
, AP_TERMS T
, AP_CARD_PROGRAMS APCP
, AP_CARD_PROFILES APCF
, AP_CARDS APC
, AP_CARD_SUPPLIERS ACS
, PO_VENDORS VN2
, IBY_CREDITCARD ICC
WHERE ETPH.TP_HEADER_ID = PVS.TP_HEADER_ID
AND ETPD.TP_HEADER_ID = ETPH.TP_HEADER_ID
AND ETPD.DOCUMENT_ID = 'POO'
AND ETPD.EDI_FLAG = 'Y'
AND ETPD.DOCUMENT_TYPE = PH.TYPE_LOOKUP_CODE
AND VN.VENDOR_ID = PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID (+)
AND NVL(PH.CONSIGNED_CONSUMPTION_FLAG
, 'N') != 'Y'
AND LC.LOCATION_ID = PH.SHIP_TO_LOCATION_ID
AND LC2.LOCATION_ID = PH.BILL_TO_LOCATION_ID
AND NVL (LC2.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL (HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND LC2.LOCATION_ID = LT2.LOCATION_ID
AND LT2.LANGUAGE = USERENV('LANG')
AND PPF.PERSON_ID = PH.AGENT_ID
AND PA.PERSON_ID (+) = PPF.PERSON_ID
AND PA.PRIMARY_FLAG (+) = 'Y'
AND PA.DATE_FROM (+) <= TRUNC(SYSDATE)
AND NVL(PA.DATE_TO (+)
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND PPF.BUSINESS_GROUP_ID + 0 = ( SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID)
, 0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND HRA.PERSON_ID (+) = LC.DESIGNATED_RECEIVER_ID
AND HRB.PERSON_ID (+) = LC2.DESIGNATED_RECEIVER_ID
AND PH.TERMS_ID = T.TERM_ID (+)
AND PH.PCARD_ID = APC.CARD_ID (+)
AND NVL(PH.AUTHORIZATION_STATUS
, 'X') = 'APPROVED'
AND APC.PROFILE_ID = APCF.PROFILE_ID (+)
AND APCF.CARD_PROGRAM_ID = APCP.CARD_PROGRAM_ID (+)
AND PH.PCARD_ID =ACS.CARD_ID(+)
AND ACS.VENDOR_ID =VN2.VENDOR_ID(+)
AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE
AND PH.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PH.REVISION_NUM = PHA.REVISION_NUM
AND PHA.LATEST_EXTERNAL_FLAG = 'Y'
AND NVL(PH.PRINT_COUNT
, 0) = 0
AND NVL(PH.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND NVL(PHA.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND NVL(PH.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PH.USER_HOLD_FLAG
, 'N') = 'N'
AND PPF.PERSON_ID = PP.PARENT_ID (+)
AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+)='W1'
AND TRUNC(SYSDATE)<=HRA.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE)>=HRA.EFFECTIVE_START_DATE(+)
AND TRUNC(SYSDATE)<=HRB.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE)>=HRB.EFFECTIVE_START_DATE(+)
AND TRUNC(SYSDATE) >= PP.DATE_FROM(+)
AND TRUNC(SYSDATE) <= NVL(PP.DATE_TO(+)
, SYSDATE)
AND APC.CARD_REFERENCE_ID = ICC.INSTRID (+) UNION ALL SELECT 'EDI' COMMUNCATION_METHOD
, ETPD.TEST_FLAG TEST_FLAG
, 'POO' DOCUMENT_ID
, 'RELEASE' DOCUMENT_TYPE
, PH.SEGMENT1 DOCUMENT_CODE
, ETPD.TRANSLATOR_CODE TRANSLATOR_CODE
, PVS.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, ETPH.TP_DESCRIPTION TP_DESCRIPTION
, ETPH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1
, ETPH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2
, SYSDATE TRANSACTION_DATE
, ETPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY
, ETPH.ATTRIBUTE1 TPH_ATTRIBUTE1
, ETPH.ATTRIBUTE2 TPH_ATTRIBUTE2
, ETPH.ATTRIBUTE3 TPH_ATTRIBUTE3
, ETPH.ATTRIBUTE4 TPH_ATTRIBUTE4
, ETPH.ATTRIBUTE5 TPH_ATTRIBUTE5
, ETPH.ATTRIBUTE6 TPH_ATTRIBUTE6
, ETPH.ATTRIBUTE7 TPH_ATTRIBUTE7
, ETPH.ATTRIBUTE8 TPH_ATTRIBUTE8
, ETPH.ATTRIBUTE9 TPH_ATTRIBUTE9
, ETPH.ATTRIBUTE10 TPH_ATTRIBUTE10
, ETPH.ATTRIBUTE11 TPH_ATTRIBUTE11
, ETPH.ATTRIBUTE12 TPH_ATTRIBUTE12
, ETPH.ATTRIBUTE13 TPH_ATTRIBUTE13
, ETPH.ATTRIBUTE14 TPH_ATTRIBUTE14
, ETPH.ATTRIBUTE15 TPH_ATTRIBUTE15
, ETPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY
, ETPD.ATTRIBUTE1 TPD_ATTRIBUTE1
, ETPD.ATTRIBUTE2 TPD_ATTRIBUTE2
, ETPD.ATTRIBUTE3 TPD_ATTRIBUTE3
, ETPD.ATTRIBUTE4 TPD_ATTRIBUTE4
, ETPD.ATTRIBUTE5 TPD_ATTRIBUTE5
, PH.SEGMENT1 PO_NUMBER
, PR.PO_RELEASE_ID POR_RELEASE_ID
, PR.RELEASE_NUM POR_RELEASE_NUM
, PR.RELEASE_DATE POR_RELEASE_DATE
, PR.CREATION_DATE CREATION_DATE
, PR.REVISION_NUM REVISION_NUM
, TO_DATE(PR.REVISED_DATE
, 'DD-MON-RR HH24:MI') REVISED_DATE
, PH.COMMENTS COMMENTS
, PH.TYPE_LOOKUP_CODE PO_TYPE
, T.NAME PAYMENT_TERMS
, FCC.CURRENCY_CODE CURRENCY_CODE
, PH.RATE CURRENCY_RATE
, PH.SHIP_VIA_LOOKUP_CODE SHIP_VIA
, PH.FOB_LOOKUP_CODE FOB_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS
, PR.CANCEL_FLAG CANCEL_FLAG
, PR.ACCEPTANCE_REQUIRED_FLAG ACCEPTANCE_REQUIRED_FLAG
, PR.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE
, '' CONFIRMING_ORDER_FLAG
, TO_DATE(NULL) BLANKET_START_DATE
, TO_DATE(NULL) BLANKET_END_DATE
, TO_NUMBER(NULL) BLANKET_TOTAL_AMOUNT
, VN.SEGMENT1 SUPPLIER_NUMBER
, VN.VENDOR_NAME SUPPLIER_NAME
, PR.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PR.ATTRIBUTE_CATEGORY PO_ATTRIBUTE_CATEGORY
, PR.ATTRIBUTE1 PO_ATTRIBUTE1
, PR.ATTRIBUTE2 PO_ATTRIBUTE2
, PR.ATTRIBUTE3 PO_ATTRIBUTE3
, PR.ATTRIBUTE4 PO_ATTRIBUTE4
, PR.ATTRIBUTE5 PO_ATTRIBUTE5
, PR.ATTRIBUTE6 PO_ATTRIBUTE6
, PR.ATTRIBUTE7 PO_ATTRIBUTE7
, PR.ATTRIBUTE8 PO_ATTRIBUTE8
, PR.ATTRIBUTE9 PO_ATTRIBUTE9
, PR.ATTRIBUTE10 PO_ATTRIBUTE10
, PR.ATTRIBUTE11 PO_ATTRIBUTE11
, PR.ATTRIBUTE12 PO_ATTRIBUTE12
, PR.ATTRIBUTE13 PO_ATTRIBUTE13
, PR.ATTRIBUTE14 PO_ATTRIBUTE14
, PR.ATTRIBUTE15 PO_ATTRIBUTE15
, VN.ATTRIBUTE_CATEGORY SU_ATTRIBUTE_CATEGORY
, VN.ATTRIBUTE1 SU_ATTRIBUTE1
, VN.ATTRIBUTE2 SU_ATTRIBUTE2
, VN.ATTRIBUTE3 SU_ATTRIBUTE3
, VN.ATTRIBUTE4 SU_ATTRIBUTE4
, VN.ATTRIBUTE5 SU_ATTRIBUTE5
, VN.ATTRIBUTE6 SU_ATTRIBUTE6
, VN.ATTRIBUTE7 SU_ATTRIBUTE7
, VN.ATTRIBUTE8 SU_ATTRIBUTE8
, VN.ATTRIBUTE9 SU_ATTRIBUTE9
, VN.ATTRIBUTE10 SU_ATTRIBUTE10
, VN.ATTRIBUTE11 SU_ATTRIBUTE11
, VN.ATTRIBUTE12 SU_ATTRIBUTE12
, VN.ATTRIBUTE13 SU_ATTRIBUTE13
, VN.ATTRIBUTE14 SU_ATTRIBUTE14
, VN.ATTRIBUTE15 SU_ATTRIBUTE15
, PVS.ATTRIBUTE_CATEGORY SS_ATTRIBUTE_CATEGORY
, PVS.ATTRIBUTE1 SS_ATTRIBUTE1
, PVS.ATTRIBUTE2 SS_ATTRIBUTE2
, PVS.ATTRIBUTE3 SS_ATTRIBUTE3
, PVS.ATTRIBUTE4 SS_ATTRIBUTE4
, PVS.ATTRIBUTE5 SS_ATTRIBUTE5
, PVS.ATTRIBUTE6 SS_ATTRIBUTE6
, PVS.ATTRIBUTE7 SS_ATTRIBUTE7
, PVS.ATTRIBUTE8 SS_ATTRIBUTE8
, PVS.ATTRIBUTE9 SS_ATTRIBUTE9
, PVS.ATTRIBUTE10 SS_ATTRIBUTE10
, PVS.ATTRIBUTE11 SS_ATTRIBUTE11
, PVS.ATTRIBUTE12 SS_ATTRIBUTE12
, PVS.ATTRIBUTE13 SS_ATTRIBUTE13
, PVS.ATTRIBUTE14 SS_ATTRIBUTE14
, PVS.ATTRIBUTE15 SS_ATTRIBUTE15
, VN.CUSTOMER_NUM CUSTOMER_NUMBER
, PH.VENDOR_SITE_ID VENDOR_SITE_ID
, PVS.ADDRESS_LINE1 SU_ADDRESS_LINE1
, PVS.ADDRESS_LINE2 SU_ADDRESS_LINE2
, PVS.ADDRESS_LINE3 SU_ADDRESS_LINE3
, PVS.ADDRESS_LINE4 SU_ADDRESS_LINE4
, PVS.CITY SU_CITY
, PVS.ZIP SU_ZIP
, PVS.COUNTRY SU_COUNTRY
, PVS.STATE SU_STATE
, PVS.PROVINCE SU_PROVINCE
, PVS.AREA_CODE SU_AREA_CODE
, PVS.PHONE SU_PHONE
, PVS.FAX_AREA_CODE SU_FAX_AREA_CODE
, PVS.FAX SU_FAX
, PVS.TELEX SU_TELEX
, PVS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
, PVC.LAST_NAME CN_LAST_NAME
, PVC.FIRST_NAME CN_FIRST_NAME
, PVC.AREA_CODE CN_AREA_CODE
, PVC.PHONE CN_PHONE
, PH.SHIP_TO_LOCATION_ID ST_LOCATION_ID
, LC.LOCATION_CODE ST_LOCATION_CODE
, LC.ECE_TP_LOCATION_CODE ST_EDI_LOCATION_CODE
, HRA.LAST_NAME ST_CONTACT_LAST_NAME
, HRA.FIRST_NAME ST_CONTACT_FIRST_NAME
, LC.DESCRIPTION ST_NAME
, LC.ADDRESS_LINE_1 ST_ADDRESS_LINE1
, LC.ADDRESS_LINE_2 ST_ADDRESS_LINE2
, LC.ADDRESS_LINE_3 ST_ADDRESS_LINE3
, LC.TOWN_OR_CITY ST_CITY
, LC.POSTAL_CODE ST_POSTAL_CODE
, LC.COUNTRY ST_COUNTRY
, LC.REGION_1 ST_REGION1
, LC.REGION_2 ST_REGION2
, LC.REGION_3 ST_REGION3
, LC.TELEPHONE_NUMBER_1 ST_PHONE1
, LC.TELEPHONE_NUMBER_2 ST_PHONE2
, LC.TELEPHONE_NUMBER_3 ST_PHONE3
, PH.BILL_TO_LOCATION_ID BT_LOCATION_ID
, LC2.LOCATION_CODE BT_LOCATION_CODE
, LC2.ECE_TP_LOCATION_CODE BT_EDI_LOCATION_CODE
, HRB.LAST_NAME BT_CONTACT_LAST_NAME
, HRB.FIRST_NAME BT_CONTACT_FIRST_NAME
, LT2.DESCRIPTION BT_NAME
, LC2.ADDRESS_LINE_1 BT_ADDRESS_LINE1
, LC2.ADDRESS_LINE_2 BT_ADDRESS_LINE2
, LC2.ADDRESS_LINE_3 BT_ADDRESS_LINE3
, LC2.TOWN_OR_CITY BT_CITY
, LC2.POSTAL_CODE BT_POSTAL_CODE
, LC2.COUNTRY BT_COUNTRY
, LC2.REGION_1 BT_REGION1
, LC2.REGION_2 BT_REGION2
, LC2.REGION_3 BT_REGION3
, LC2.TELEPHONE_NUMBER_1 BT_PHONE1
, LC2.TELEPHONE_NUMBER_2 BT_PHONE2
, LC2.TELEPHONE_NUMBER_3 BT_PHONE3
, PPF.LAST_NAME BUYER_LAST_NAME
, PPF.FIRST_NAME BUYER_FIRST_NAME
, PPF.EMAIL_ADDRESS BUYER_EMAIL_ADDRESS
, PP.PHONE_NUMBER BUYER_WORK_TELEPHONE
, PA.TELEPHONE_NUMBER_1 BUYER_MISC_TELEPHONE1
, PA.TELEPHONE_NUMBER_2 BUYER_MISC_TELEPHONE2
, PA.TELEPHONE_NUMBER_3 BUYER_MISC_TELEPHONE3
, PH.PO_HEADER_ID PO_HEADER_ID
, ETPD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, DECODE (APCP.CARD_TYPE_LOOKUP_CODE
, 'SUPPLIER'
, VN2.VENDOR_NAME
, APC.CARDMEMBER_NAME) PCARDMEMBER_NAME
, ICC.MASKED_CC_NUMBER PCARD_NUMBER
, APC.CARD_EXPIRATION_DATE PCARD_EXPIRATION_DATE
, APCP.CARD_BRAND_LOOKUP_CODE PCARD_BRAND
, ETPD.MAP_ID MAP_ID
, PH.ORG_ID ORG_ID
, PR.SHIPPING_CONTROL SHIPPING_CONTROL
FROM PO_HEADERS PH
, PO_RELEASES PR
, PO_RELEASES_ARCHIVE PRA
, ECE_TP_HEADERS ETPH
, ECE_TP_DETAILS ETPD
, FND_CURRENCIES_VL FCC
, PO_VENDORS VN
, PO_VENDOR_SITES PVS
, PO_VENDOR_CONTACTS PVC
, PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PPF
, PER_ALL_PEOPLE_F HRA
, PER_PHONES PP
, PER_ALL_PEOPLE_F HRB
, HR_LOCATIONS LC
, HR_LOCATIONS_ALL LC2
, HR_LOCATIONS_ALL_TL LT2
, AP_TERMS T
, AP_CARD_PROGRAMS APCP
, AP_CARD_PROFILES APCF
, AP_CARDS APC
, AP_CARD_SUPPLIERS ACS
, PO_VENDORS VN2
, IBY_CREDITCARD ICC
WHERE ETPH.TP_HEADER_ID = PVS.TP_HEADER_ID
AND ETPD.TP_HEADER_ID = ETPH.TP_HEADER_ID
AND ETPD.DOCUMENT_ID = 'POO'
AND ETPD.EDI_FLAG = 'Y'
AND ETPD.DOCUMENT_TYPE = 'RELEASE'
AND VN.VENDOR_ID = PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID (+)
AND NVL(PR.CONSIGNED_CONSUMPTION_FLAG
, 'N') != 'Y'
AND LC.LOCATION_ID = PH.SHIP_TO_LOCATION_ID
AND LC2.LOCATION_ID = PH.BILL_TO_LOCATION_ID
AND NVL (LC2.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL (HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND LC2.LOCATION_ID = LT2.LOCATION_ID
AND LT2.LANGUAGE = USERENV('LANG')
AND PPF.PERSON_ID = PR.AGENT_ID
AND PA.PERSON_ID (+) = PPF.PERSON_ID
AND PA.PRIMARY_FLAG (+) = 'Y'
AND PA.DATE_FROM (+) <= TRUNC(SYSDATE)
AND NVL(PA.DATE_TO (+)
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND PPF.BUSINESS_GROUP_ID + 0 = ( SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID)
, 0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND HRA.PERSON_ID (+) = LC.DESIGNATED_RECEIVER_ID
AND HRB.PERSON_ID (+) = LC2.DESIGNATED_RECEIVER_ID
AND PH.TERMS_ID = T.TERM_ID (+)
AND PR.PCARD_ID = APC.CARD_ID (+)
AND NVL(PR.AUTHORIZATION_STATUS
, 'X') = 'APPROVED'
AND APC.PROFILE_ID = APCF.PROFILE_ID (+)
AND APCF.CARD_PROGRAM_ID = APCP.CARD_PROGRAM_ID (+)
AND PR.PCARD_ID =ACS.CARD_ID(+)
AND ACS.VENDOR_ID =VN2.VENDOR_ID(+)
AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE
AND PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PR.PO_RELEASE_ID = PRA.PO_RELEASE_ID
AND PR.REVISION_NUM = PRA.REVISION_NUM
AND PRA.LATEST_EXTERNAL_FLAG = 'Y'
AND NVL(PR.PRINT_COUNT
, 0) = 0
AND NVL(PR.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND NVL(PRA.EDI_PROCESSED_FLAG
, 'N') = 'N'
AND NVL(PR.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PR.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PR.HOLD_FLAG
, 'N') = 'N'
AND PPF.PERSON_ID = PP.PARENT_ID (+)
AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+)='W1'
AND TRUNC(SYSDATE)<=HRA.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE)>=HRA.EFFECTIVE_START_DATE(+)
AND TRUNC(SYSDATE)<=HRB.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE)>=HRB.EFFECTIVE_START_DATE(+)
AND TRUNC(SYSDATE) >= PP.DATE_FROM(+)
AND TRUNC(SYSDATE) <= NVL(PP.DATE_TO(+)
, SYSDATE)
AND APC.CARD_REFERENCE_ID = ICC.INSTRID (+)

Columns