VIEW: APPS.PO_RELEASE_ARCHIVE_XML

Source

View Text - Preformatted

SELECT PH.SEGMENT1 , PRA.REVISION_NUM, PRA.PRINT_COUNT, TO_CHAR(PRA.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, PRA.PRINTED_DATE, TO_CHAR(PRA.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, PH.AGENT_ID DOCUMENT_BUYER_AGENT_ID, DECODE(NVL(PRA.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PRA.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID, DECODE(NVL(PRA.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME, DECODE(NVL(PRA.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME, DECODE(NVL(PRA.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE, PRA.CANCEL_FLAG, nvl(PRA.ACCEPTANCE_REQUIRED_FLAG,'N'), TO_CHAR(PRA.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, PLC7.MEANING FOB, PLC8.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, PRA.ATTRIBUTE1, PRA.ATTRIBUTE2, PRA.ATTRIBUTE3, PRA.ATTRIBUTE4, PRA.ATTRIBUTE5, PRA.ATTRIBUTE6, PRA.ATTRIBUTE7, PRA.ATTRIBUTE8, PRA.ATTRIBUTE9, PRA.ATTRIBUTE10, PRA.ATTRIBUTE11, PRA.ATTRIBUTE12, PRA.ATTRIBUTE13, PRA.ATTRIBUTE14, PRA.ATTRIBUTE15, PH.VENDOR_SITE_ID, PH.PO_HEADER_ID, PRA.PO_RELEASE_ID, DECODE(PRA.APPROVED_FLAG,'Y','Y','N') APPROVED_FLAG, PVS.LANGUAGE, PH.VENDOR_ID, PRA.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, PRA.AUTHORIZATION_STATUS, PRA.USSGL_TRANSACTION_CODE, PRA.GOVERNMENT_CONTEXT, PRA.REQUEST_ID, PRA.PROGRAM_APPLICATION_ID, PRA.PROGRAM_ID, TO_CHAR(PRA.PROGRAM_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') PROGRAM_UPDATE_DATE, PRA.CLOSED_CODE, PRA.FROZEN_FLAG, PRA.RELEASE_TYPE, PRA.NOTE_TO_VENDOR, PRA.ORG_ID, TO_CHAR(PRA.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PRA.LAST_UPDATED_BY, PRA.RELEASE_NUM, PRA.AGENT_ID, TO_CHAR(PRA.RELEASE_DATE,'DD-MON-YYYY HH24:MI:SS') RELEASE_DATE, PRA.LAST_UPDATE_LOGIN, PRA.CREATED_BY, TO_CHAR(PRA.APPROVED_DATE,'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE, PRA.HOLD_BY, TO_CHAR(PRA.HOLD_DATE,'DD-MON-YYYY HH24:MI:SS') HOLD_DATE, PRA.HOLD_REASON, PRA.HOLD_FLAG, PRA.CANCELLED_BY, TO_CHAR(PRA.CANCEL_DATE,'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE, PRA.CANCEL_REASON, PRA.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PRA.FIRM_DATE,'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, PRA.ATTRIBUTE_CATEGORY, PRA.EDI_PROCESSED_FLAG, PRA.GLOBAL_ATTRIBUTE_CATEGORY, PRA.GLOBAL_ATTRIBUTE1, PRA.GLOBAL_ATTRIBUTE2, PRA.GLOBAL_ATTRIBUTE3, PRA.GLOBAL_ATTRIBUTE4, PRA.GLOBAL_ATTRIBUTE5, PRA.GLOBAL_ATTRIBUTE6, PRA.GLOBAL_ATTRIBUTE7, PRA.GLOBAL_ATTRIBUTE8, PRA.GLOBAL_ATTRIBUTE9, PRA.GLOBAL_ATTRIBUTE10, PRA.GLOBAL_ATTRIBUTE11, PRA.GLOBAL_ATTRIBUTE12, PRA.GLOBAL_ATTRIBUTE13, PRA.GLOBAL_ATTRIBUTE14, PRA.GLOBAL_ATTRIBUTE15, PRA.GLOBAL_ATTRIBUTE16, PRA.GLOBAL_ATTRIBUTE17, PRA.GLOBAL_ATTRIBUTE18, PRA.GLOBAL_ATTRIBUTE19, PRA.GLOBAL_ATTRIBUTE20, PRA.WF_ITEM_TYPE, PRA.WF_ITEM_KEY, PRA.PCARD_ID, PRA.PAY_ON_CODE, PRA.XML_FLAG, TO_CHAR(PRA.XML_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE, TO_CHAR(PRA.XML_CHANGE_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE, TO_CHAR(PRA.CBC_ACCOUNTING_DATE,'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE, PRA.CHANGE_REQUESTED_BY, FLV.MEANING SHIPPING_CONTROL, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PRA.ORG_ID)) OU_NAME, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE, DECODE(NVL(PRA.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(DECODE(PH.TYPE_LOOKUP_CODE,'BLANKET',PO_CORE_S.GET_ARCHIVE_TOTAL_FOR_ANY_REV (PRA.PO_RELEASE_ID,'R','RELEASE','BLANKET',PRA.REVISION_NUM, NULL), NULL),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(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLegalEntityDetails(PRA.ORG_ID)) LE_NAME, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine1()) LE_ADDR1, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine2()) LE_ADDR2, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine3()) LE_ADDR3, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLETownOrCity()) LE_TOWN_CITY, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEStateOrProvince()) LE_STAE_PROVINCE, DECODE(NVL(PRA.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEPostalCode()) LE_POSTALCODE, DECODE(NVL(PRA.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, PRA.CHANGE_SUMMARY, PRA.DOCUMENT_CREATION_METHOD, PRA.LATEST_EXTERNAL_FLAG, PRA.VENDOR_ORDER_NUM FROM FND_LOOKUP_VALUES PLC7, FND_LOOKUP_VALUES PLC8, 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_HEADERS_ALL PH, PO_RELEASES_ARCHIVE_ALL PRA, FND_TERRITORIES_TL FTE3, FND_LOOKUP_VALUES FLV, ORG_FREIGHT OFC, PO_AGENTS PA WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET','PLANNED') AND PH.PO_HEADER_ID = PRA.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 = PRA.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 PLC7.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE AND PLC7.LOOKUP_TYPE (+) = 'FOB' AND PLC7.VIEW_APPLICATION_ID(+) = 201 AND PLC7.LANGUAGE(+) = USERENV('LANG') AND DECODE(PLC7.LOOKUP_CODE, NULL, 1, PLC7.SECURITY_GROUP_ID) = DECODE(PLC7.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC7.LOOKUP_TYPE, PLC7.VIEW_APPLICATION_ID) ) AND PLC8.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE AND PLC8.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND PLC8.VIEW_APPLICATION_ID(+) = 201 AND PLC8.LANGUAGE(+) = USERENV('LANG') AND DECODE(PLC8.LOOKUP_CODE, NULL, 1,PLC8.SECURITY_GROUP_ID) = DECODE(PLC8.LOOKUP_CODE, NULL, 1,FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC8.LOOKUP_TYPE, PLC8.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 FLV.LOOKUP_CODE (+) = PRA.SHIPPING_CONTROL AND FLV.LOOKUP_TYPE (+) = 'SHIPPING CONTROL' AND FLV.LANGUAGE(+) = USERENV('LANG') AND FLV.VIEW_APPLICATION_ID(+) = 201 AND DECODE(FLV.LOOKUP_CODE, NULL, 1, FLV.SECURITY_GROUP_ID) = DECODE(FLV.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE, FLV.VIEW_APPLICATION_ID)) AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID AND PA.AGENT_ID = PRA.AGENT_ID

View Text - HTML Formatted

SELECT PH.SEGMENT1
, PRA.REVISION_NUM
, PRA.PRINT_COUNT
, TO_CHAR(PRA.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, PRA.PRINTED_DATE
, TO_CHAR(PRA.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
, PH.AGENT_ID DOCUMENT_BUYER_AGENT_ID
, DECODE(NVL(PRA.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PRA.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID
, DECODE(NVL(PRA.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME
, DECODE(NVL(PRA.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME
, DECODE(NVL(PRA.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE
, PRA.CANCEL_FLAG
, NVL(PRA.ACCEPTANCE_REQUIRED_FLAG
, 'N')
, TO_CHAR(PRA.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
, PLC7.MEANING FOB
, PLC8.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
, PRA.ATTRIBUTE1
, PRA.ATTRIBUTE2
, PRA.ATTRIBUTE3
, PRA.ATTRIBUTE4
, PRA.ATTRIBUTE5
, PRA.ATTRIBUTE6
, PRA.ATTRIBUTE7
, PRA.ATTRIBUTE8
, PRA.ATTRIBUTE9
, PRA.ATTRIBUTE10
, PRA.ATTRIBUTE11
, PRA.ATTRIBUTE12
, PRA.ATTRIBUTE13
, PRA.ATTRIBUTE14
, PRA.ATTRIBUTE15
, PH.VENDOR_SITE_ID
, PH.PO_HEADER_ID
, PRA.PO_RELEASE_ID
, DECODE(PRA.APPROVED_FLAG
, 'Y'
, 'Y'
, 'N') APPROVED_FLAG
, PVS.LANGUAGE
, PH.VENDOR_ID
, PRA.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
, PRA.AUTHORIZATION_STATUS
, PRA.USSGL_TRANSACTION_CODE
, PRA.GOVERNMENT_CONTEXT
, PRA.REQUEST_ID
, PRA.PROGRAM_APPLICATION_ID
, PRA.PROGRAM_ID
, TO_CHAR(PRA.PROGRAM_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') PROGRAM_UPDATE_DATE
, PRA.CLOSED_CODE
, PRA.FROZEN_FLAG
, PRA.RELEASE_TYPE
, PRA.NOTE_TO_VENDOR
, PRA.ORG_ID
, TO_CHAR(PRA.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PRA.LAST_UPDATED_BY
, PRA.RELEASE_NUM
, PRA.AGENT_ID
, TO_CHAR(PRA.RELEASE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RELEASE_DATE
, PRA.LAST_UPDATE_LOGIN
, PRA.CREATED_BY
, TO_CHAR(PRA.APPROVED_DATE
, 'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE
, PRA.HOLD_BY
, TO_CHAR(PRA.HOLD_DATE
, 'DD-MON-YYYY HH24:MI:SS') HOLD_DATE
, PRA.HOLD_REASON
, PRA.HOLD_FLAG
, PRA.CANCELLED_BY
, TO_CHAR(PRA.CANCEL_DATE
, 'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE
, PRA.CANCEL_REASON
, PRA.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PRA.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, PRA.ATTRIBUTE_CATEGORY
, PRA.EDI_PROCESSED_FLAG
, PRA.GLOBAL_ATTRIBUTE_CATEGORY
, PRA.GLOBAL_ATTRIBUTE1
, PRA.GLOBAL_ATTRIBUTE2
, PRA.GLOBAL_ATTRIBUTE3
, PRA.GLOBAL_ATTRIBUTE4
, PRA.GLOBAL_ATTRIBUTE5
, PRA.GLOBAL_ATTRIBUTE6
, PRA.GLOBAL_ATTRIBUTE7
, PRA.GLOBAL_ATTRIBUTE8
, PRA.GLOBAL_ATTRIBUTE9
, PRA.GLOBAL_ATTRIBUTE10
, PRA.GLOBAL_ATTRIBUTE11
, PRA.GLOBAL_ATTRIBUTE12
, PRA.GLOBAL_ATTRIBUTE13
, PRA.GLOBAL_ATTRIBUTE14
, PRA.GLOBAL_ATTRIBUTE15
, PRA.GLOBAL_ATTRIBUTE16
, PRA.GLOBAL_ATTRIBUTE17
, PRA.GLOBAL_ATTRIBUTE18
, PRA.GLOBAL_ATTRIBUTE19
, PRA.GLOBAL_ATTRIBUTE20
, PRA.WF_ITEM_TYPE
, PRA.WF_ITEM_KEY
, PRA.PCARD_ID
, PRA.PAY_ON_CODE
, PRA.XML_FLAG
, TO_CHAR(PRA.XML_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE
, TO_CHAR(PRA.XML_CHANGE_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE
, TO_CHAR(PRA.CBC_ACCOUNTING_DATE
, 'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE
, PRA.CHANGE_REQUESTED_BY
, FLV.MEANING SHIPPING_CONTROL
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PRA.ORG_ID)) OU_NAME
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE
, DECODE(NVL(PRA.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(DECODE(PH.TYPE_LOOKUP_CODE
, 'BLANKET'
, PO_CORE_S.GET_ARCHIVE_TOTAL_FOR_ANY_REV (PRA.PO_RELEASE_ID
, 'R'
, 'RELEASE'
, 'BLANKET'
, PRA.REVISION_NUM
, NULL)
, NULL)
, 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(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEGALENTITYDETAILS(PRA.ORG_ID)) LE_NAME
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE1()) LE_ADDR1
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE2()) LE_ADDR2
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE3()) LE_ADDR3
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLETOWNORCITY()) LE_TOWN_CITY
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLESTATEORPROVINCE()) LE_STAE_PROVINCE
, DECODE(NVL(PRA.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEPOSTALCODE()) LE_POSTALCODE
, DECODE(NVL(PRA.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
, PRA.CHANGE_SUMMARY
, PRA.DOCUMENT_CREATION_METHOD
, PRA.LATEST_EXTERNAL_FLAG
, PRA.VENDOR_ORDER_NUM
FROM FND_LOOKUP_VALUES PLC7
, FND_LOOKUP_VALUES PLC8
, 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_HEADERS_ALL PH
, PO_RELEASES_ARCHIVE_ALL PRA
, FND_TERRITORIES_TL FTE3
, FND_LOOKUP_VALUES FLV
, ORG_FREIGHT OFC
, PO_AGENTS PA
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'PLANNED')
AND PH.PO_HEADER_ID = PRA.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 = PRA.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 PLC7.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE
AND PLC7.LOOKUP_TYPE (+) = 'FOB'
AND PLC7.VIEW_APPLICATION_ID(+) = 201
AND PLC7.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PLC7.LOOKUP_CODE
, NULL
, 1
, PLC7.SECURITY_GROUP_ID) = DECODE(PLC7.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC7.LOOKUP_TYPE
, PLC7.VIEW_APPLICATION_ID) )
AND PLC8.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE
AND PLC8.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND PLC8.VIEW_APPLICATION_ID(+) = 201
AND PLC8.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PLC8.LOOKUP_CODE
, NULL
, 1
, PLC8.SECURITY_GROUP_ID) = DECODE(PLC8.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC8.LOOKUP_TYPE
, PLC8.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 FLV.LOOKUP_CODE (+) = PRA.SHIPPING_CONTROL
AND FLV.LOOKUP_TYPE (+) = 'SHIPPING CONTROL'
AND FLV.LANGUAGE(+) = USERENV('LANG')
AND FLV.VIEW_APPLICATION_ID(+) = 201
AND DECODE(FLV.LOOKUP_CODE
, NULL
, 1
, FLV.SECURITY_GROUP_ID) = DECODE(FLV.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE
, FLV.VIEW_APPLICATION_ID))
AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE
AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID
AND PA.AGENT_ID = PRA.AGENT_ID