View: PO_RELEASE_ARCHIVE_XML

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_RELEASE_ARCHIVE_XML

View Text

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
, 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.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.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
, 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
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 HRE.PERSON_ID = PRA.AGENT_ID
AND HRE.EMPLOYEE_NUMBER IS NOT NULL
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

Columns