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.POS_PO_SUMMARY_V

Source

View Text - Preformatted


SELECT 'PO' PO_RELEASE_FLAG, POH.ACCEPTANCE_DUE_DATE, POH.APPROVED_DATE, NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE') AUTHORIZATION_STATUS, POH.CLOSED_DATE, POH.COMMENTS, POH.FIRM_DATE, POH.NOTE_TO_AUTHORIZER, POH.NOTE_TO_RECEIVER, POH.NOTE_TO_VENDOR, POH.PRINT_COUNT, POH.PRINTED_DATE, POH.RATE, POH.RATE_DATE, POH.RATE_TYPE, POH.REVISED_DATE, POH.REVISION_NUM, POH.AGENT_ID, POH.BILL_TO_LOCATION_ID, POH.FROM_HEADER_ID, POH.PO_HEADER_ID, POH.SHIP_TO_LOCATION_ID, POH.TERMS_ID, POH.VENDOR_CONTACT_ID, POH.VENDOR_ID, POH.VENDOR_SITE_ID, NVL(POH.CLOSED_CODE, 'OPEN') CLOSED_CODE, POH.CURRENCY_CODE, NVL(POH.FIRM_STATUS_LOOKUP_CODE,'N') FIRM_STATUS_LOOKUP_CODE, POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, POH.SHIP_VIA_LOOKUP_CODE, POH.TYPE_LOOKUP_CODE, POH.ACCEPTANCE_REQUIRED_FLAG, POH.APPROVAL_REQUIRED_FLAG, POH.APPROVED_FLAG, DECODE (POH.CANCEL_FLAG, 'I', NULL, POH.CANCEL_FLAG) CANCEL_FLAG, POH.CONFIRMING_ORDER_FLAG, POH.ENABLED_FLAG, NVL(POH.FROZEN_FLAG, 'N') FROZEN_FLAG, POH.SUMMARY_FLAG, NVL(POH.USER_HOLD_FLAG, 'N') USER_HOLD_FLAG, POH.CREATED_BY, POH.CREATION_DATE, POH.LAST_UPDATED_BY, POH.LAST_UPDATE_DATE, POH.LAST_UPDATE_LOGIN, POH.PROGRAM_APPLICATION_ID, POH.PROGRAM_ID, POH.PROGRAM_UPDATE_DATE, POH.REQUEST_ID, POH.SEGMENT1 PO_NUM, POS_GET.GET_PERSON_NAME_CACHE(POH.AGENT_ID) AGENT_NAME, V.VENDOR_NAME, VS.VENDOR_SITE_CODE, VS.ADDRESS_LINE1, VS.ADDRESS_LINE2, VS.ADDRESS_LINE3, VS.CITY, VS.STATE, VS.ZIP, VS.COUNTRY, DECODE (VS.PHONE, NULL, NULL,'('||VS.AREA_CODE||') '||VS.PHONE) PHONE, DECODE (VS.FAX, NULL, NULL,'('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX, AT.NAME, DECODE(POH.TYPE_LOOKUP_CODE, 'BLANKET',fnd_message.get_string('POS','POS_POTYPE_BLKT'), 'CONTRACT',fnd_message.get_string('POS','POS_POTYPE_CNTR'),'STANDARD',fnd_message.get_string('POS','POS_POTYPE_STD'),'PLANNED',fnd_message.get_string('POS','POS_POTYPE_PLND')) TYPE_NAME , HRL1.LOCATION_CODE SHIP_TO_LOCATION, HRL1.ADDRESS_LINE_1 SHIP_ADDR1, HRL1.ADDRESS_LINE_2 SHIP_ADDR2, HRL1.ADDRESS_LINE_3 SHIP_ADDR3, HRL1.TOWN_OR_CITY SHIP_CITY, HRL1.REGION_2 SHIP_STATE, HRL1.POSTAL_CODE SHIP_ZIP, HRL1.COUNTRY SHIP_COUNTRY, HRL2.LOCATION_CODE BILL_TO_LOCATION, HRL2.ADDRESS_LINE_1 BILL_ADDR1, HRL2.ADDRESS_LINE_2 BILL_ADDR2, HRL2.ADDRESS_LINE_3 BILL_ADDR3, HRL2.TOWN_OR_CITY BILL_CITY, HRL2.REGION_2 BILL_STATE, HRL2.POSTAL_CODE BILL_ZIP, HRL2.COUNTRY BILL_COUNTRY, POLC2.MEANING FOB_DSP, POLC3.MEANING FREIGHT_TERMS_DSP, TO_DATE(NULL) CANCEL_DATE, NULL CANCEL_REASON, TO_NUMBER(NULL) CANCELLED_BY, NULL CANCELLED_BY_NAME, TO_NUMBER(NULL) HOLD_BY, TO_DATE(NULL) HOLD_DATE, NULL HOLD_REASON, TO_NUMBER(NULL) RELEASE_NUM, NULL RELEASE_TYPE, TO_NUMBER(NULL) PO_RELEASE_ID, DECODE(POH.TYPE_LOOKUP_CODE, 'BLANKET', TO_CHAR(POH.BLANKET_TOTAL_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE, 30)), 'CONTRACT', TO_CHAR(POH.BLANKET_TOTAL_AMOUNT, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE, 30)), TO_CHAR(POS_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID), FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE, 30))) AMOUNT, V.ATTRIBUTE14 SUPPLIER_URL, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG, POA.ACTION_DATE ACTION_DATE, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POH.ORG_ID, HOU.NAME ORG_NAME, POH.ATTRIBUTE1, POH.ATTRIBUTE2, POH.ATTRIBUTE3, POH.ATTRIBUTE4, POH.ATTRIBUTE5, POH.ATTRIBUTE6, POH.ATTRIBUTE7, POH.ATTRIBUTE8, POH.ATTRIBUTE9, POH.ATTRIBUTE10, POH.ATTRIBUTE11, POH.ATTRIBUTE12, POH.ATTRIBUTE13, POH.ATTRIBUTE14, POH.ATTRIBUTE15, POH.ATTRIBUTE_CATEGORY, DECODE(POH.CANCEL_FLAG, 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'), 'N',decode(POH.FROZEN_FLAG, 'Y',fnd_message.get_string('POS','POS_PO_FROZEN'), 'N',polc.displayed_field ), NULL,decode(POH.FROZEN_FLAG, 'Y',fnd_message.get_string('POS','POS_PO_FROZEN'), 'N',polc.displayed_field ), polc.displayed_field ) PO_STATUS FROM PO_VENDOR_SITES_ALL VS, PO_VENDORS V, PO_ACCEPTANCES POA, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, FND_LOOKUP_VALUES_VL POLC2, FND_LOOKUP_VALUES_VL POLC3, AP_TERMS AT, PO_HEADERS_ALL POH, HR_ALL_ORGANIZATION_UNITS_TL HOU, PO_LOOKUP_CODES POLC WHERE POH.TYPE_LOOKUP_CODE IN ('BLANKET','CONTRACT','PLANNED','STANDARD') AND POH.VENDOR_ID = V.VENDOR_ID AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND POH.APPROVED_FLAG = 'Y' AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID AND POLC2.LOOKUP_CODE (+)= POH.FOB_LOOKUP_CODE AND POLC2.LOOKUP_TYPE (+)= 'FOB' AND POLC2.VIEW_APPLICATION_ID (+)= 201 AND POLC3.LOOKUP_CODE (+) = POH.FREIGHT_TERMS_LOOKUP_CODE AND POLC3.LOOKUP_TYPE (+)= 'FREIGHT TERMS' AND POLC3.VIEW_APPLICATION_ID (+)= 201 AND AT.TERM_ID (+) = POH.TERMS_ID AND POH.PO_HEADER_ID = POA.PO_HEADER_ID (+) AND POH.REVISION_NUM = POA.REVISION_NUM (+) AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM PO_ACCEPTANCES PA2 WHERE POH.PO_HEADER_ID = PA2.PO_HEADER_ID AND PA2.PO_RELEASE_ID is null AND POH.REVISION_NUM = PA2.REVISION_NUM) OR POA.CREATION_DATE IS NULL) AND POA.PO_RELEASE_ID IS NULL AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID AND HOU.LANGUAGE (+)= USERENV('LANG') AND POLC.LOOKUP_CODE = NVL(POH.CLOSED_CODE, 'OPEN') AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE' UNION ALL SELECT 'RELEASE' PO_RELEASE_FLAG, POR.ACCEPTANCE_DUE_DATE, POR.APPROVED_DATE, NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE') AUTHORIZATION_STATUS, POH.CLOSED_DATE, POH.COMMENTS, POR.FIRM_DATE, POH.NOTE_TO_AUTHORIZER, POH.NOTE_TO_RECEIVER, POR.NOTE_TO_VENDOR, POR.PRINT_COUNT, POR.PRINTED_DATE, POH.RATE, POH.RATE_DATE, POH.RATE_TYPE, POR.REVISED_DATE, POR.REVISION_NUM, POR.AGENT_ID, POH.BILL_TO_LOCATION_ID, POH.FROM_HEADER_ID, POR.PO_HEADER_ID, POH.SHIP_TO_LOCATION_ID, POH.TERMS_ID, POH.VENDOR_CONTACT_ID, POH.VENDOR_ID, POH.VENDOR_SITE_ID, NVL(POR.CLOSED_CODE, 'OPEN') CLOSED_CODE, POH.CURRENCY_CODE, NVL(POR.FIRM_STATUS_LOOKUP_CODE,'N') FIRM_STATUS_LOOKUP_CODE, POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, POH.SHIP_VIA_LOOKUP_CODE, POH.TYPE_LOOKUP_CODE, POR.ACCEPTANCE_REQUIRED_FLAG, POH.APPROVAL_REQUIRED_FLAG, POR.APPROVED_FLAG, DECODE (POR.CANCEL_FLAG, 'I', NULL,POR.CANCEL_FLAG) CANCEL_FLAG, POH.CONFIRMING_ORDER_FLAG, POH.ENABLED_FLAG, NVL(POR.FROZEN_FLAG, 'N') FROZEN_FLAG, POH.SUMMARY_FLAG, NVL(POR.HOLD_FLAG,'N') USER_HOLD_FLAG, POR.CREATED_BY, POR.RELEASE_DATE, POR.LAST_UPDATED_BY, POR.LAST_UPDATE_DATE, POR.LAST_UPDATE_LOGIN, POR.PROGRAM_APPLICATION_ID, POR.PROGRAM_ID, POR.PROGRAM_UPDATE_DATE, POR.REQUEST_ID, POH.SEGMENT1 PO_NUM, POS_GET.GET_PERSON_NAME_CACHE(POR.AGENT_ID) AGENT_NAME, V.VENDOR_NAME, VS.VENDOR_SITE_CODE, VS.ADDRESS_LINE1, VS.ADDRESS_LINE2, VS.ADDRESS_LINE3, VS.CITY, VS.STATE, VS.ZIP, VS.COUNTRY, DECODE (VS.PHONE, NULL, NULL, '('||VS.AREA_CODE||') '||VS.PHONE) PHONE, DECODE (VS.FAX, NULL, NULL, '('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX, AT.NAME, DECODE(POR.RELEASE_TYPE, 'BLANKET', fnd_message.get_string('POS','POS_POTYPE_BLKTR'), 'SCHEDULED',fnd_message.get_string('POS','POS_POTYPE_PLNDR')) TYPE_NAME , HRL1.LOCATION_CODE SHIP_TO_LOCATION, HRL1.ADDRESS_LINE_1 SHIP_ADDR1, HRL1.ADDRESS_LINE_2 SHIP_ADDR2, HRL1.ADDRESS_LINE_3 SHIP_ADDR3, HRL1.TOWN_OR_CITY SHIP_CITY, HRL1.REGION_2 SHIP_STATE, HRL1.POSTAL_CODE SHIP_ZIP, HRL1.COUNTRY SHIP_COUNTRY, HRL2.LOCATION_CODE BILL_TO_LOCATION, HRL2.ADDRESS_LINE_1 BILL_ADDR1, HRL2.ADDRESS_LINE_2 BILL_ADDR2, HRL2.ADDRESS_LINE_3 BILL_ADDR3, HRL2.TOWN_OR_CITY BILL_CITY, HRL2.REGION_2 BILL_STATE, HRL2.POSTAL_CODE BILL_ZIP, HRL2.COUNTRY BILL_COUNTRY, POLC2.MEANING FOB_DSP, POLC3.MEANING FREIGHT_TERMS_DSP, POR.CANCEL_DATE, POR.CANCEL_REASON, POR.CANCELLED_BY, PO_INQ_SV.GET_PERSON_NAME(POR.CANCELLED_BY) CANCELLED_BY_NAME, POR.HOLD_BY, POR.HOLD_DATE, POR.HOLD_REASON, POR.RELEASE_NUM, POR.RELEASE_TYPE, POR.PO_RELEASE_ID, TO_CHAR(POS_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID), FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE,30)) AMOUNT, V.ATTRIBUTE14 SUPPLIER_URL, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG, POA.ACTION_DATE ACTION_DATE, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POR.ORG_ID, HOU.NAME ORG_NAME, POR.ATTRIBUTE1, POR.ATTRIBUTE2, POR.ATTRIBUTE3, POR.ATTRIBUTE4, POR.ATTRIBUTE5, POR.ATTRIBUTE6, POR.ATTRIBUTE7, POR.ATTRIBUTE8, POR.ATTRIBUTE9, POR.ATTRIBUTE10, POR.ATTRIBUTE11, POR.ATTRIBUTE12, POR.ATTRIBUTE13, POR.ATTRIBUTE14, POR.ATTRIBUTE15, POR.ATTRIBUTE_CATEGORY, DECODE(nvl(POH.CANCEL_FLAG,'N'), 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'), 'N',decode(nvl(POH.FROZEN_FLAG,'N'), 'Y',fnd_message.get_string('POS','POS_PO_FROZEN'), 'N',polc.displayed_field ), NULL,decode(POH.FROZEN_FLAG, 'Y',fnd_message.get_string('POS','POS_PO_FROZEN'), 'N',polc.displayed_field ), polc.displayed_field ) PO_STATUS FROM PO_VENDOR_SITES_ALL VS, PO_VENDORS V, PO_RELEASES_ALL POR, PO_ACCEPTANCES POA, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, FND_LOOKUP_VALUES_VL POLC2, FND_LOOKUP_VALUES_VL POLC3, AP_TERMS AT, PO_HEADERS_ALL POH, HR_ALL_ORGANIZATION_UNITS_TL HOU, PO_LOOKUP_CODES POLC WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND POH.VENDOR_ID = V.VENDOR_ID AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET','PLANNED') AND POR.APPROVED_FLAG = 'Y' AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID AND POLC2.LOOKUP_CODE (+)= POH.FOB_LOOKUP_CODE AND POLC2.LOOKUP_TYPE (+)= 'FOB' AND POLC2.VIEW_APPLICATION_ID (+)= 201 AND POLC3.LOOKUP_CODE (+)= POH.FREIGHT_TERMS_LOOKUP_CODE AND POLC3.LOOKUP_TYPE (+)= 'FREIGHT TERMS' AND POLC3.VIEW_APPLICATION_ID (+)= 201 AND AT.TERM_ID (+) = POH.TERMS_ID AND POR.PO_RELEASE_ID = POA.PO_RELEASE_ID (+) AND POR.REVISION_NUM = POA.REVISION_NUM (+) AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM PO_ACCEPTANCES PA2 WHERE POR.PO_RELEASE_ID= PA2.PO_RELEASE_ID AND POR.REVISION_NUM = PA2.REVISION_NUM) OR POA.CREATION_DATE IS NULL) AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID AND HOU.LANGUAGE (+)= USERENV('LANG') AND POLC.LOOKUP_CODE = NVL(POR.CLOSED_CODE, 'OPEN') AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE'

View Text - HTML Formatted

SELECT 'PO' PO_RELEASE_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE') AUTHORIZATION_STATUS
, POH.CLOSED_DATE
, POH.COMMENTS
, POH.FIRM_DATE
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POH.NOTE_TO_VENDOR
, POH.PRINT_COUNT
, POH.PRINTED_DATE
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POH.REVISED_DATE
, POH.REVISION_NUM
, POH.AGENT_ID
, POH.BILL_TO_LOCATION_ID
, POH.FROM_HEADER_ID
, POH.PO_HEADER_ID
, POH.SHIP_TO_LOCATION_ID
, POH.TERMS_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, NVL(POH.FIRM_STATUS_LOOKUP_CODE
, 'N') FIRM_STATUS_LOOKUP_CODE
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POH.ACCEPTANCE_REQUIRED_FLAG
, POH.APPROVAL_REQUIRED_FLAG
, POH.APPROVED_FLAG
, DECODE (POH.CANCEL_FLAG
, 'I'
, NULL
, POH.CANCEL_FLAG) CANCEL_FLAG
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POH.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.SUMMARY_FLAG
, NVL(POH.USER_HOLD_FLAG
, 'N') USER_HOLD_FLAG
, POH.CREATED_BY
, POH.CREATION_DATE
, POH.LAST_UPDATED_BY
, POH.LAST_UPDATE_DATE
, POH.LAST_UPDATE_LOGIN
, POH.PROGRAM_APPLICATION_ID
, POH.PROGRAM_ID
, POH.PROGRAM_UPDATE_DATE
, POH.REQUEST_ID
, POH.SEGMENT1 PO_NUM
, POS_GET.GET_PERSON_NAME_CACHE(POH.AGENT_ID) AGENT_NAME
, V.VENDOR_NAME
, VS.VENDOR_SITE_CODE
, VS.ADDRESS_LINE1
, VS.ADDRESS_LINE2
, VS.ADDRESS_LINE3
, VS.CITY
, VS.STATE
, VS.ZIP
, VS.COUNTRY
, DECODE (VS.PHONE
, NULL
, NULL
, '('||VS.AREA_CODE||') '||VS.PHONE) PHONE
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX
, AT.NAME
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_BLKT')
, 'CONTRACT'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_CNTR')
, 'STANDARD'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_STD')
, 'PLANNED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_PLND')) TYPE_NAME
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.ADDRESS_LINE_1 SHIP_ADDR1
, HRL1.ADDRESS_LINE_2 SHIP_ADDR2
, HRL1.ADDRESS_LINE_3 SHIP_ADDR3
, HRL1.TOWN_OR_CITY SHIP_CITY
, HRL1.REGION_2 SHIP_STATE
, HRL1.POSTAL_CODE SHIP_ZIP
, HRL1.COUNTRY SHIP_COUNTRY
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, HRL2.ADDRESS_LINE_1 BILL_ADDR1
, HRL2.ADDRESS_LINE_2 BILL_ADDR2
, HRL2.ADDRESS_LINE_3 BILL_ADDR3
, HRL2.TOWN_OR_CITY BILL_CITY
, HRL2.REGION_2 BILL_STATE
, HRL2.POSTAL_CODE BILL_ZIP
, HRL2.COUNTRY BILL_COUNTRY
, POLC2.MEANING FOB_DSP
, POLC3.MEANING FREIGHT_TERMS_DSP
, TO_DATE(NULL) CANCEL_DATE
, NULL CANCEL_REASON
, TO_NUMBER(NULL) CANCELLED_BY
, NULL CANCELLED_BY_NAME
, TO_NUMBER(NULL) HOLD_BY
, TO_DATE(NULL) HOLD_DATE
, NULL HOLD_REASON
, TO_NUMBER(NULL) RELEASE_NUM
, NULL RELEASE_TYPE
, TO_NUMBER(NULL) PO_RELEASE_ID
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, TO_CHAR(POH.BLANKET_TOTAL_AMOUNT
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, 'CONTRACT'
, TO_CHAR(POH.BLANKET_TOTAL_AMOUNT
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, TO_CHAR(POS_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))) AMOUNT
, V.ATTRIBUTE14 SUPPLIER_URL
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POA.ACTION_DATE ACTION_DATE
, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POH.ORG_ID
, HOU.NAME ORG_NAME
, POH.ATTRIBUTE1
, POH.ATTRIBUTE2
, POH.ATTRIBUTE3
, POH.ATTRIBUTE4
, POH.ATTRIBUTE5
, POH.ATTRIBUTE6
, POH.ATTRIBUTE7
, POH.ATTRIBUTE8
, POH.ATTRIBUTE9
, POH.ATTRIBUTE10
, POH.ATTRIBUTE11
, POH.ATTRIBUTE12
, POH.ATTRIBUTE13
, POH.ATTRIBUTE14
, POH.ATTRIBUTE15
, POH.ATTRIBUTE_CATEGORY
, DECODE(POH.CANCEL_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CANCELLED')
, 'N'
, DECODE(POH.FROZEN_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'N'
, POLC.DISPLAYED_FIELD )
, NULL
, DECODE(POH.FROZEN_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'N'
, POLC.DISPLAYED_FIELD )
, POLC.DISPLAYED_FIELD ) PO_STATUS
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, PO_ACCEPTANCES POA
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_LOOKUP_CODES POLC
WHERE POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'CONTRACT'
, 'PLANNED'
, 'STANDARD')
AND POH.VENDOR_ID = V.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.APPROVED_FLAG = 'Y'
AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID
AND POLC2.LOOKUP_CODE (+)= POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+)= 'FOB'
AND POLC2.VIEW_APPLICATION_ID (+)= 201
AND POLC3.LOOKUP_CODE (+) = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE (+)= 'FREIGHT TERMS'
AND POLC3.VIEW_APPLICATION_ID (+)= 201
AND AT.TERM_ID (+) = POH.TERMS_ID
AND POH.PO_HEADER_ID = POA.PO_HEADER_ID (+)
AND POH.REVISION_NUM = POA.REVISION_NUM (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POH.PO_HEADER_ID = PA2.PO_HEADER_ID
AND PA2.PO_RELEASE_ID IS NULL
AND POH.REVISION_NUM = PA2.REVISION_NUM) OR POA.CREATION_DATE IS NULL)
AND POA.PO_RELEASE_ID IS NULL
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')
AND POLC.LOOKUP_CODE = NVL(POH.CLOSED_CODE
, 'OPEN')
AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE' UNION ALL SELECT 'RELEASE' PO_RELEASE_FLAG
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE') AUTHORIZATION_STATUS
, POH.CLOSED_DATE
, POH.COMMENTS
, POR.FIRM_DATE
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POR.NOTE_TO_VENDOR
, POR.PRINT_COUNT
, POR.PRINTED_DATE
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POR.REVISED_DATE
, POR.REVISION_NUM
, POR.AGENT_ID
, POH.BILL_TO_LOCATION_ID
, POH.FROM_HEADER_ID
, POR.PO_HEADER_ID
, POH.SHIP_TO_LOCATION_ID
, POH.TERMS_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POR.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, NVL(POR.FIRM_STATUS_LOOKUP_CODE
, 'N') FIRM_STATUS_LOOKUP_CODE
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POR.ACCEPTANCE_REQUIRED_FLAG
, POH.APPROVAL_REQUIRED_FLAG
, POR.APPROVED_FLAG
, DECODE (POR.CANCEL_FLAG
, 'I'
, NULL
, POR.CANCEL_FLAG) CANCEL_FLAG
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POR.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.SUMMARY_FLAG
, NVL(POR.HOLD_FLAG
, 'N') USER_HOLD_FLAG
, POR.CREATED_BY
, POR.RELEASE_DATE
, POR.LAST_UPDATED_BY
, POR.LAST_UPDATE_DATE
, POR.LAST_UPDATE_LOGIN
, POR.PROGRAM_APPLICATION_ID
, POR.PROGRAM_ID
, POR.PROGRAM_UPDATE_DATE
, POR.REQUEST_ID
, POH.SEGMENT1 PO_NUM
, POS_GET.GET_PERSON_NAME_CACHE(POR.AGENT_ID) AGENT_NAME
, V.VENDOR_NAME
, VS.VENDOR_SITE_CODE
, VS.ADDRESS_LINE1
, VS.ADDRESS_LINE2
, VS.ADDRESS_LINE3
, VS.CITY
, VS.STATE
, VS.ZIP
, VS.COUNTRY
, DECODE (VS.PHONE
, NULL
, NULL
, '('||VS.AREA_CODE||') '||VS.PHONE) PHONE
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX
, AT.NAME
, DECODE(POR.RELEASE_TYPE
, 'BLANKET'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_BLKTR')
, 'SCHEDULED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_PLNDR')) TYPE_NAME
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.ADDRESS_LINE_1 SHIP_ADDR1
, HRL1.ADDRESS_LINE_2 SHIP_ADDR2
, HRL1.ADDRESS_LINE_3 SHIP_ADDR3
, HRL1.TOWN_OR_CITY SHIP_CITY
, HRL1.REGION_2 SHIP_STATE
, HRL1.POSTAL_CODE SHIP_ZIP
, HRL1.COUNTRY SHIP_COUNTRY
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, HRL2.ADDRESS_LINE_1 BILL_ADDR1
, HRL2.ADDRESS_LINE_2 BILL_ADDR2
, HRL2.ADDRESS_LINE_3 BILL_ADDR3
, HRL2.TOWN_OR_CITY BILL_CITY
, HRL2.REGION_2 BILL_STATE
, HRL2.POSTAL_CODE BILL_ZIP
, HRL2.COUNTRY BILL_COUNTRY
, POLC2.MEANING FOB_DSP
, POLC3.MEANING FREIGHT_TERMS_DSP
, POR.CANCEL_DATE
, POR.CANCEL_REASON
, POR.CANCELLED_BY
, PO_INQ_SV.GET_PERSON_NAME(POR.CANCELLED_BY) CANCELLED_BY_NAME
, POR.HOLD_BY
, POR.HOLD_DATE
, POR.HOLD_REASON
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.PO_RELEASE_ID
, TO_CHAR(POS_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)) AMOUNT
, V.ATTRIBUTE14 SUPPLIER_URL
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POA.ACTION_DATE ACTION_DATE
, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POR.ORG_ID
, HOU.NAME ORG_NAME
, POR.ATTRIBUTE1
, POR.ATTRIBUTE2
, POR.ATTRIBUTE3
, POR.ATTRIBUTE4
, POR.ATTRIBUTE5
, POR.ATTRIBUTE6
, POR.ATTRIBUTE7
, POR.ATTRIBUTE8
, POR.ATTRIBUTE9
, POR.ATTRIBUTE10
, POR.ATTRIBUTE11
, POR.ATTRIBUTE12
, POR.ATTRIBUTE13
, POR.ATTRIBUTE14
, POR.ATTRIBUTE15
, POR.ATTRIBUTE_CATEGORY
, DECODE(NVL(POH.CANCEL_FLAG
, 'N')
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CANCELLED')
, 'N'
, DECODE(NVL(POH.FROZEN_FLAG
, 'N')
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'N'
, POLC.DISPLAYED_FIELD )
, NULL
, DECODE(POH.FROZEN_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'N'
, POLC.DISPLAYED_FIELD )
, POLC.DISPLAYED_FIELD ) PO_STATUS
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, PO_RELEASES_ALL POR
, PO_ACCEPTANCES POA
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_LOOKUP_CODES POLC
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND POH.VENDOR_ID = V.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED')
AND POR.APPROVED_FLAG = 'Y'
AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND POLC2.LOOKUP_CODE (+)= POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+)= 'FOB'
AND POLC2.VIEW_APPLICATION_ID (+)= 201
AND POLC3.LOOKUP_CODE (+)= POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE (+)= 'FREIGHT TERMS'
AND POLC3.VIEW_APPLICATION_ID (+)= 201
AND AT.TERM_ID (+) = POH.TERMS_ID
AND POR.PO_RELEASE_ID = POA.PO_RELEASE_ID (+)
AND POR.REVISION_NUM = POA.REVISION_NUM (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POR.PO_RELEASE_ID= PA2.PO_RELEASE_ID
AND POR.REVISION_NUM = PA2.REVISION_NUM) OR POA.CREATION_DATE IS NULL)
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')
AND POLC.LOOKUP_CODE = NVL(POR.CLOSED_CODE
, 'OPEN')
AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE'