eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PO_RELEASE_XML

Source

View Text - Preformatted


SELECT PH.SEGMENT1, PR.REVISION_NUM, PR.PRINT_COUNT, TO_CHAR(PR.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, TO_CHAR(PR.PRINTED_DATE,'DD-MON-YYYY HH24:MI:SS') PRINTED_DATE, TO_CHAR(PR.REVISED_DATE,'DD-MON-YYYY HH24:MI:SS') REVISED_DATE, HRE.FIRST_NAME DOCUMENT_BUYER_FIRST_NAME, HRE.LAST_NAME DOCUMENT_BUYER_LAST_NAME, HRE.TITLE DOCUMENT_BUYER_TITLE, PR.AGENT_ID DOCUMENT_BUYER_AGENT_ID, DECODE(NVL(PR.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PR.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID, DECODE(NVL(PR.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME, DECODE(NVL(PR.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME, DECODE(NVL(PR.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE, PR.CANCEL_FLAG, nvl(PR.ACCEPTANCE_REQUIRED_FLAG,'N'), TO_CHAR(PR.ACCEPTANCE_DUE_DATE, 'DD-MON-YYYY HH24:MI:SS') ACCEPTANCE_DUE_DATE, FCC.CURRENCY_CODE, FCC.NAME CURRENCY_NAME, TO_CHAR(PH.RATE, PO_COMMUNICATION_PVT.GETFORMATMASK) RATE, NVL(OFC.FREIGHT_CODE_TL, PH.SHIP_VIA_LOOKUP_CODE) SHIP_VIA, PLC1.MEANING FOB, PLC2.MEANING FREIGHT_TERMS, T.NAME PAYMENT_TERMS, NVL(PVS.CUSTOMER_NUM, VN.CUSTOMER_NUM) CUSTOMER_NUM, VN.SEGMENT1 VENDOR_NUM, VN.VENDOR_NAME, PVS.ADDRESS_LINE1 VENDOR_ADDRESS_LINE1, PVS.ADDRESS_LINE2 VENDOR_ADDRESS_LINE2, PVS.ADDRESS_LINE3 VENDOR_ADDRESS_LINE3, PVS.CITY VENDOR_CITY, DECODE(PVS.STATE, NULL, DECODE(PVS.PROVINCE, NULL, PVS.COUNTY, PVS.PROVINCE), PVS.STATE) VENDOR_STATE, PVS.ZIP VENDOR_POSTAL_CODE, FTE3.TERRITORY_SHORT_NAME VENDOR_COUNTRY, PVS.PHONE VENDOR_PHONE, PVC.FIRST_NAME VENDOR_CONTACT_FIRST_NAME, PVC.LAST_NAME VENDOR_CONTACT_LAST_NAME, PVC.TITLE VENDOR_CONTACT_TITLE, PVS.FAX VENDOR_FAX, PR.ATTRIBUTE1, PR.ATTRIBUTE2, PR.ATTRIBUTE3, PR.ATTRIBUTE4, PR.ATTRIBUTE5, PR.ATTRIBUTE6, PR.ATTRIBUTE7, PR.ATTRIBUTE8, PR.ATTRIBUTE9, PR.ATTRIBUTE10, PR.ATTRIBUTE11, PR.ATTRIBUTE12, PR.ATTRIBUTE13, PR.ATTRIBUTE14, PR.ATTRIBUTE15, PH.VENDOR_SITE_ID, PH.PO_HEADER_ID, PR.PO_RELEASE_ID, DECODE(PR.APPROVED_FLAG,'Y','Y','N') APPROVED_FLAG, PVS.LANGUAGE, PH.VENDOR_ID, PR.CONSIGNED_CONSUMPTION_FLAG, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.BILL_TO_LOCATION_ID)) BILL_TO_LOCATION_ID, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) BILL_TO_LOCATION_NAME, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1() ) BILL_TO_ADDRESS_LINE1, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2() ) BILL_TO_ADDRESS_LINE2, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3() ) BILL_TO_ADDRESS_LINE3, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4() ) BILL_TO_ADDRESS_LINE4, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) BILL_TO_ADDRESS_INFO, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME()) BILL_TO_COUNTRY, PR.AUTHORIZATION_STATUS, PR.USSGL_TRANSACTION_CODE, PR.GOVERNMENT_CONTEXT, PR.REQUEST_ID, PR.PROGRAM_APPLICATION_ID, PR.PROGRAM_ID, PR.PROGRAM_UPDATE_DATE, PR.CLOSED_CODE, PR.FROZEN_FLAG, PR.RELEASE_TYPE, PR.NOTE_TO_VENDOR, PR.ORG_ID, TO_CHAR(PR.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PR.LAST_UPDATED_BY, PR.RELEASE_NUM, PR.AGENT_ID, TO_CHAR(PR.RELEASE_DATE,'DD-MON-YYYY HH24:MI:SS') RELEASE_DATE, PR.LAST_UPDATE_LOGIN, PR.CREATED_BY, TO_CHAR(PR.APPROVED_DATE,'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE, PR.HOLD_BY, TO_CHAR(PR.HOLD_DATE,'DD-MON-YYYY HH24:MI:SS') HOLD_DATE, PR.HOLD_REASON, PR.HOLD_FLAG, PR.CANCELLED_BY, TO_CHAR(PR.CANCEL_DATE,'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE, PR.CANCEL_REASON, PR.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PR.FIRM_DATE,'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, PR.ATTRIBUTE_CATEGORY, PR.EDI_PROCESSED_FLAG, PR.GLOBAL_ATTRIBUTE_CATEGORY, PR.GLOBAL_ATTRIBUTE1, PR.GLOBAL_ATTRIBUTE2, PR.GLOBAL_ATTRIBUTE3, PR.GLOBAL_ATTRIBUTE4, PR.GLOBAL_ATTRIBUTE5, PR.GLOBAL_ATTRIBUTE6, PR.GLOBAL_ATTRIBUTE7, PR.GLOBAL_ATTRIBUTE8, PR.GLOBAL_ATTRIBUTE9, PR.GLOBAL_ATTRIBUTE10, PR.GLOBAL_ATTRIBUTE11, PR.GLOBAL_ATTRIBUTE12, PR.GLOBAL_ATTRIBUTE13, PR.GLOBAL_ATTRIBUTE14, PR.GLOBAL_ATTRIBUTE15, PR.GLOBAL_ATTRIBUTE16, PR.GLOBAL_ATTRIBUTE17, PR.GLOBAL_ATTRIBUTE18, PR.GLOBAL_ATTRIBUTE19, PR.GLOBAL_ATTRIBUTE20, PR.WF_ITEM_TYPE, PR.WF_ITEM_KEY, PR.PCARD_ID, PR.PAY_ON_CODE, PR.XML_FLAG, TO_CHAR(PR.XML_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE, TO_CHAR(PR.XML_CHANGE_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE, TO_CHAR(PR.CBC_ACCOUNTING_DATE,'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE, PR.CHANGE_REQUESTED_BY, PLC3.MEANING SHIPPING_CONTROL, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PR.ORG_ID)) OU_NAME, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getOUCountry()) OU_COUNTRY, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PA.LOCATION_ID) REL_BUYER_LOCATION_ID, PO_COMMUNICATION_PVT.GETADDRESSLINE1() REL_BUYER_ADDRESS_LINE1, PO_COMMUNICATION_PVT.GETADDRESSLINE2() REL_BUYER_ADDRESS_LINE2, PO_COMMUNICATION_PVT.GETADDRESSLINE3() REL_BUYER_ADDRESS_LINE3, PO_COMMUNICATION_PVT.GETADDRESSLINE4() REL_BUYER_ADDRESS_LINE4, PO_COMMUNICATION_PVT.GETADDRESSINFO() REL_BUYER_CITY_STATE_ZIP, PO_COMMUNICATION_PVT.GETPHONE(PA.AGENT_ID) REL_BUYER_CONTACT_PHONE, PO_COMMUNICATION_PVT.GETEMAIL() REL_BUYER_CONTACT_EMAIL, PO_COMMUNICATION_PVT.GETFAX() REL_BUYER_CONTACT_FAX, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() REL_BUYER_COUNTRY, TO_CHAR(PO_CORE_S.GET_TOTAL('R',PR.PO_RELEASE_ID),PO_COMMUNICATION_PVT.GETFORMATMASK) TOTAL_AMOUNT, PVS.ADDRESS_LINE4 VENDOR_ADDRESS_LINE4, PVS.AREA_CODE VENDOR_AREA_CODE, PVC.AREA_CODE VENDOR_CONTACT_AREA_CODE, PVC.PHONE VENDOR_CONTACT_PHONE, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLegalEntityDetails(PR.ORG_ID)) LE_NAME, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine1()) LE_ADDR1, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine2()) LE_ADDR2, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine3()) LE_ADDR3, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLETownOrCity()) LE_TOWN_CITY, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEStateOrProvince()) LE_STAE_PROVINCE, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEPostalCode()) LE_POSTALCODE, DECODE(NVL(PR.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLECountry()) LE_COUNTRY, TO_CHAR(PH.START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, TO_CHAR(PH.END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE, TO_CHAR(NVL(PH.BLANKET_TOTAL_AMOUNT, ''),PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_AGREED, PR.CHANGE_SUMMARY, PR.DOCUMENT_CREATION_METHOD, PR.VENDOR_ORDER_NUM FROM FND_LOOKUP_VALUES PLC1, FND_LOOKUP_VALUES PLC2, FND_CURRENCIES_TL FCC, PO_VENDORS VN, PO_VENDOR_SITES_ALL PVS, PO_VENDOR_CONTACTS PVC, PER_ALL_PEOPLE_F HRE, AP_TERMS T, PO_RELEASES_ALL PR, PO_AGENTS PA, PO_HEADERS_ALL PH, FND_TERRITORIES_TL FTE3, FND_LOOKUP_VALUES PLC3, ORG_FREIGHT OFC WHERE PH.TYPE_LOOKUP_CODE = 'BLANKET' AND PH.PO_HEADER_ID = PR.PO_HEADER_ID 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_CONTACT_ID IS NULL OR PH.VENDOR_SITE_ID=PVC.VENDOR_SITE_ID) AND HRE.PERSON_ID = PR.AGENT_ID AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND PH.TERMS_ID = T.TERM_ID (+) AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE AND FCC.LANGUAGE = USERENV('LANG') AND PLC1.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE AND PLC1.LOOKUP_TYPE (+) = 'FOB' AND PLC1.VIEW_APPLICATION_ID(+) = 201 AND PLC1.LANGUAGE(+) = USERENV('LANG') AND DECODE(PLC1.LOOKUP_CODE, NULL, 1, PLC1.SECURITY_GROUP_ID) = DECODE(PLC1.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC1.LOOKUP_TYPE, PLC1.VIEW_APPLICATION_ID) ) AND PLC2.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE AND PLC2.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND PLC2.VIEW_APPLICATION_ID(+) = 201 AND PLC2.LANGUAGE(+) = USERENV('LANG') AND DECODE(PLC2.LOOKUP_CODE, NULL, 1,PLC2.SECURITY_GROUP_ID) = DECODE(PLC2.LOOKUP_CODE, NULL, 1,FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC2.LOOKUP_TYPE, PLC2.VIEW_APPLICATION_ID)) AND PVS.COUNTRY = FTE3.TERRITORY_CODE (+) AND DECODE(FTE3.TERRITORY_CODE, NULL, '1', FTE3.LANGUAGE) = DECODE(FTE3.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND PLC3.LOOKUP_CODE (+) = PR.SHIPPING_CONTROL AND PLC3.LOOKUP_TYPE (+) = 'SHIPPING CONTROL' AND PLC3.LANGUAGE(+) = USERENV('LANG') AND PLC3.VIEW_APPLICATION_ID(+) = 201 AND DECODE(PLC3.LOOKUP_CODE, NULL, 1, PLC3.SECURITY_GROUP_ID) = DECODE(PLC3.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC3.LOOKUP_TYPE, PLC3.VIEW_APPLICATION_ID)) AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID AND PA.AGENT_ID = PR.AGENT_ID

View Text - HTML Formatted

SELECT PH.SEGMENT1
, PR.REVISION_NUM
, PR.PRINT_COUNT
, TO_CHAR(PR.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, TO_CHAR(PR.PRINTED_DATE
, 'DD-MON-YYYY HH24:MI:SS') PRINTED_DATE
, TO_CHAR(PR.REVISED_DATE
, 'DD-MON-YYYY HH24:MI:SS') REVISED_DATE
, HRE.FIRST_NAME DOCUMENT_BUYER_FIRST_NAME
, HRE.LAST_NAME DOCUMENT_BUYER_LAST_NAME
, HRE.TITLE DOCUMENT_BUYER_TITLE
, PR.AGENT_ID DOCUMENT_BUYER_AGENT_ID
, DECODE(NVL(PR.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PR.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID
, DECODE(NVL(PR.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME
, DECODE(NVL(PR.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME
, DECODE(NVL(PR.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE
, PR.CANCEL_FLAG
, NVL(PR.ACCEPTANCE_REQUIRED_FLAG
, 'N')
, TO_CHAR(PR.ACCEPTANCE_DUE_DATE
, 'DD-MON-YYYY HH24:MI:SS') ACCEPTANCE_DUE_DATE
, FCC.CURRENCY_CODE
, FCC.NAME CURRENCY_NAME
, TO_CHAR(PH.RATE
, PO_COMMUNICATION_PVT.GETFORMATMASK) RATE
, NVL(OFC.FREIGHT_CODE_TL
, PH.SHIP_VIA_LOOKUP_CODE) SHIP_VIA
, PLC1.MEANING FOB
, PLC2.MEANING FREIGHT_TERMS
, T.NAME PAYMENT_TERMS
, NVL(PVS.CUSTOMER_NUM
, VN.CUSTOMER_NUM) CUSTOMER_NUM
, VN.SEGMENT1 VENDOR_NUM
, VN.VENDOR_NAME
, PVS.ADDRESS_LINE1 VENDOR_ADDRESS_LINE1
, PVS.ADDRESS_LINE2 VENDOR_ADDRESS_LINE2
, PVS.ADDRESS_LINE3 VENDOR_ADDRESS_LINE3
, PVS.CITY VENDOR_CITY
, DECODE(PVS.STATE
, NULL
, DECODE(PVS.PROVINCE
, NULL
, PVS.COUNTY
, PVS.PROVINCE)
, PVS.STATE) VENDOR_STATE
, PVS.ZIP VENDOR_POSTAL_CODE
, FTE3.TERRITORY_SHORT_NAME VENDOR_COUNTRY
, PVS.PHONE VENDOR_PHONE
, PVC.FIRST_NAME VENDOR_CONTACT_FIRST_NAME
, PVC.LAST_NAME VENDOR_CONTACT_LAST_NAME
, PVC.TITLE VENDOR_CONTACT_TITLE
, PVS.FAX VENDOR_FAX
, PR.ATTRIBUTE1
, PR.ATTRIBUTE2
, PR.ATTRIBUTE3
, PR.ATTRIBUTE4
, PR.ATTRIBUTE5
, PR.ATTRIBUTE6
, PR.ATTRIBUTE7
, PR.ATTRIBUTE8
, PR.ATTRIBUTE9
, PR.ATTRIBUTE10
, PR.ATTRIBUTE11
, PR.ATTRIBUTE12
, PR.ATTRIBUTE13
, PR.ATTRIBUTE14
, PR.ATTRIBUTE15
, PH.VENDOR_SITE_ID
, PH.PO_HEADER_ID
, PR.PO_RELEASE_ID
, DECODE(PR.APPROVED_FLAG
, 'Y'
, 'Y'
, 'N') APPROVED_FLAG
, PVS.LANGUAGE
, PH.VENDOR_ID
, PR.CONSIGNED_CONSUMPTION_FLAG
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.BILL_TO_LOCATION_ID)) BILL_TO_LOCATION_ID
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) BILL_TO_LOCATION_NAME
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1() ) BILL_TO_ADDRESS_LINE1
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2() ) BILL_TO_ADDRESS_LINE2
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3() ) BILL_TO_ADDRESS_LINE3
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4() ) BILL_TO_ADDRESS_LINE4
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) BILL_TO_ADDRESS_INFO
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME()) BILL_TO_COUNTRY
, PR.AUTHORIZATION_STATUS
, PR.USSGL_TRANSACTION_CODE
, PR.GOVERNMENT_CONTEXT
, PR.REQUEST_ID
, PR.PROGRAM_APPLICATION_ID
, PR.PROGRAM_ID
, PR.PROGRAM_UPDATE_DATE
, PR.CLOSED_CODE
, PR.FROZEN_FLAG
, PR.RELEASE_TYPE
, PR.NOTE_TO_VENDOR
, PR.ORG_ID
, TO_CHAR(PR.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PR.LAST_UPDATED_BY
, PR.RELEASE_NUM
, PR.AGENT_ID
, TO_CHAR(PR.RELEASE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RELEASE_DATE
, PR.LAST_UPDATE_LOGIN
, PR.CREATED_BY
, TO_CHAR(PR.APPROVED_DATE
, 'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE
, PR.HOLD_BY
, TO_CHAR(PR.HOLD_DATE
, 'DD-MON-YYYY HH24:MI:SS') HOLD_DATE
, PR.HOLD_REASON
, PR.HOLD_FLAG
, PR.CANCELLED_BY
, TO_CHAR(PR.CANCEL_DATE
, 'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE
, PR.CANCEL_REASON
, PR.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PR.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, PR.ATTRIBUTE_CATEGORY
, PR.EDI_PROCESSED_FLAG
, PR.GLOBAL_ATTRIBUTE_CATEGORY
, PR.GLOBAL_ATTRIBUTE1
, PR.GLOBAL_ATTRIBUTE2
, PR.GLOBAL_ATTRIBUTE3
, PR.GLOBAL_ATTRIBUTE4
, PR.GLOBAL_ATTRIBUTE5
, PR.GLOBAL_ATTRIBUTE6
, PR.GLOBAL_ATTRIBUTE7
, PR.GLOBAL_ATTRIBUTE8
, PR.GLOBAL_ATTRIBUTE9
, PR.GLOBAL_ATTRIBUTE10
, PR.GLOBAL_ATTRIBUTE11
, PR.GLOBAL_ATTRIBUTE12
, PR.GLOBAL_ATTRIBUTE13
, PR.GLOBAL_ATTRIBUTE14
, PR.GLOBAL_ATTRIBUTE15
, PR.GLOBAL_ATTRIBUTE16
, PR.GLOBAL_ATTRIBUTE17
, PR.GLOBAL_ATTRIBUTE18
, PR.GLOBAL_ATTRIBUTE19
, PR.GLOBAL_ATTRIBUTE20
, PR.WF_ITEM_TYPE
, PR.WF_ITEM_KEY
, PR.PCARD_ID
, PR.PAY_ON_CODE
, PR.XML_FLAG
, TO_CHAR(PR.XML_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE
, TO_CHAR(PR.XML_CHANGE_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE
, TO_CHAR(PR.CBC_ACCOUNTING_DATE
, 'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE
, PR.CHANGE_REQUESTED_BY
, PLC3.MEANING SHIPPING_CONTROL
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PR.ORG_ID)) OU_NAME
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUCOUNTRY()) OU_COUNTRY
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PA.LOCATION_ID) REL_BUYER_LOCATION_ID
, PO_COMMUNICATION_PVT.GETADDRESSLINE1() REL_BUYER_ADDRESS_LINE1
, PO_COMMUNICATION_PVT.GETADDRESSLINE2() REL_BUYER_ADDRESS_LINE2
, PO_COMMUNICATION_PVT.GETADDRESSLINE3() REL_BUYER_ADDRESS_LINE3
, PO_COMMUNICATION_PVT.GETADDRESSLINE4() REL_BUYER_ADDRESS_LINE4
, PO_COMMUNICATION_PVT.GETADDRESSINFO() REL_BUYER_CITY_STATE_ZIP
, PO_COMMUNICATION_PVT.GETPHONE(PA.AGENT_ID) REL_BUYER_CONTACT_PHONE
, PO_COMMUNICATION_PVT.GETEMAIL() REL_BUYER_CONTACT_EMAIL
, PO_COMMUNICATION_PVT.GETFAX() REL_BUYER_CONTACT_FAX
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() REL_BUYER_COUNTRY
, TO_CHAR(PO_CORE_S.GET_TOTAL('R'
, PR.PO_RELEASE_ID)
, PO_COMMUNICATION_PVT.GETFORMATMASK) TOTAL_AMOUNT
, PVS.ADDRESS_LINE4 VENDOR_ADDRESS_LINE4
, PVS.AREA_CODE VENDOR_AREA_CODE
, PVC.AREA_CODE VENDOR_CONTACT_AREA_CODE
, PVC.PHONE VENDOR_CONTACT_PHONE
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEGALENTITYDETAILS(PR.ORG_ID)) LE_NAME
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE1()) LE_ADDR1
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE2()) LE_ADDR2
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE3()) LE_ADDR3
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLETOWNORCITY()) LE_TOWN_CITY
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLESTATEORPROVINCE()) LE_STAE_PROVINCE
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEPOSTALCODE()) LE_POSTALCODE
, DECODE(NVL(PR.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLECOUNTRY()) LE_COUNTRY
, TO_CHAR(PH.START_DATE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE
, TO_CHAR(PH.END_DATE
, 'DD-MON-YYYY HH24:MI:SS') END_DATE
, TO_CHAR(NVL(PH.BLANKET_TOTAL_AMOUNT
, '')
, PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_AGREED
, PR.CHANGE_SUMMARY
, PR.DOCUMENT_CREATION_METHOD
, PR.VENDOR_ORDER_NUM
FROM FND_LOOKUP_VALUES PLC1
, FND_LOOKUP_VALUES PLC2
, FND_CURRENCIES_TL FCC
, PO_VENDORS VN
, PO_VENDOR_SITES_ALL PVS
, PO_VENDOR_CONTACTS PVC
, PER_ALL_PEOPLE_F HRE
, AP_TERMS T
, PO_RELEASES_ALL PR
, PO_AGENTS PA
, PO_HEADERS_ALL PH
, FND_TERRITORIES_TL FTE3
, FND_LOOKUP_VALUES PLC3
, ORG_FREIGHT OFC
WHERE PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PH.PO_HEADER_ID = PR.PO_HEADER_ID
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_CONTACT_ID IS NULL OR PH.VENDOR_SITE_ID=PVC.VENDOR_SITE_ID)
AND HRE.PERSON_ID = PR.AGENT_ID
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND PH.TERMS_ID = T.TERM_ID (+)
AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE
AND FCC.LANGUAGE = USERENV('LANG')
AND PLC1.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE
AND PLC1.LOOKUP_TYPE (+) = 'FOB'
AND PLC1.VIEW_APPLICATION_ID(+) = 201
AND PLC1.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PLC1.LOOKUP_CODE
, NULL
, 1
, PLC1.SECURITY_GROUP_ID) = DECODE(PLC1.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC1.LOOKUP_TYPE
, PLC1.VIEW_APPLICATION_ID) )
AND PLC2.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE
AND PLC2.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND PLC2.VIEW_APPLICATION_ID(+) = 201
AND PLC2.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PLC2.LOOKUP_CODE
, NULL
, 1
, PLC2.SECURITY_GROUP_ID) = DECODE(PLC2.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC2.LOOKUP_TYPE
, PLC2.VIEW_APPLICATION_ID))
AND PVS.COUNTRY = FTE3.TERRITORY_CODE (+)
AND DECODE(FTE3.TERRITORY_CODE
, NULL
, '1'
, FTE3.LANGUAGE) = DECODE(FTE3.TERRITORY_CODE
, NULL
, '1'
, USERENV('LANG'))
AND PLC3.LOOKUP_CODE (+) = PR.SHIPPING_CONTROL
AND PLC3.LOOKUP_TYPE (+) = 'SHIPPING CONTROL'
AND PLC3.LANGUAGE(+) = USERENV('LANG')
AND PLC3.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC3.LOOKUP_CODE
, NULL
, 1
, PLC3.SECURITY_GROUP_ID) = DECODE(PLC3.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC3.LOOKUP_TYPE
, PLC3.VIEW_APPLICATION_ID))
AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE
AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID
AND PA.AGENT_ID = PR.AGENT_ID