VIEW: APPS.ECE_POO_HEADERS_V

Source

View Text - Preformatted

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 PH.VENDOR_SITE_ID = PVC.VENDOR_SITE_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 ,NVL(PR.NOTE_TO_VENDOR, PH.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 PH.VENDOR_SITE_ID = PVC.VENDOR_SITE_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 (+)

View Text - HTML Formatted

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 PH.VENDOR_SITE_ID = PVC.VENDOR_SITE_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
, NVL(PR.NOTE_TO_VENDOR
, PH.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 PH.VENDOR_SITE_ID = PVC.VENDOR_SITE_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 (+)