View: PO_RELEASE_XML

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

View Text

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
, 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.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
, 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
, 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_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 HRE.PERSON_ID = PR.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 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

Columns