VIEW: APPS.POS_PO_ARCH_SUMMARY_V

Source

View Text - Preformatted

SELECT 'PO' PO_RELEASE_FLAG, POHA.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, POHA.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',DECODE(POH.GLOBAL_AGREEMENT_FLAG,'Y', FND_MESSAGE.GET_STRING('POS','POS_POTYPE_GBLA'), 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, 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(PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0,'F',poh.po_header_id,null), 'CANCELLED', FND_MESSAGE.GET_STRING('POS','POS_PO_CANCELLED'), 'FROZEN', FND_MESSAGE.GET_STRING('POS','POS_PO_FROZEN'), 'CLOSED', FND_MESSAGE.GET_STRING('POS','POS_PO_CLOSED'), 'FINALLY CLOSED', FND_MESSAGE.GET_STRING('POS','POS_PO_FINALLYCLOSED'), 'ON HOLD', FND_MESSAGE.GET_STRING('POS','POS_ON_HOLD'), 'INTERNAL CHANGE', FND_MESSAGE.GET_STRING('POS','POS_PO_INTERNAL_CHANGE'), 'ACCEPTED', FND_MESSAGE.GET_STRING('POS','POS_ACCEPTED'), 'REJECTED', FND_MESSAGE.GET_STRING('POS','POS_REJECTED'), 'ACKNOWLEDGED', FND_MESSAGE.GET_STRING('POS','POS_PO_ACKNOWLEDGED'), 'PARTIALLY_ACKNOWLEDGED', FND_MESSAGE.GET_STRING('POS','POS_PO_PARTIALLY_ACKED'), 'SUPPLIER_CHANGE_PENDING', FND_MESSAGE.GET_STRING('POS','POS_PO_SUP_CHANGE'), 'ACK_REQUIRED', FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'), to_char(NULL) ) PO_STATUS, POHA.VENDOR_ORDER_NUM, NULL NEW_SUPPLIER_ORDER_NUMBER, NULL REQUEST_STATUS, NVL( POH.GLOBAL_AGREEMENT_FLAG,'N'), POHA.CONSIGNED_CONSUMPTION_FLAG, PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0,'F',poh.po_header_id,null) STATUS_CODE FROM PO_VENDOR_SITES_ALL VS, PO_VENDORS V, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, FND_LOOKUP_VALUES_VL POLC2, FND_LOOKUP_VALUES_VL POLC3, AP_TERMS AT, PO_HEADERS_ARCHIVE_ALL POH, PO_HEADERS_ALL POHA, 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.PO_HEADER_ID = POHA.PO_HEADER_ID AND POH.LATEST_EXTERNAL_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 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, PORA.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, PORA.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, 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(PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0,'F',null,por.po_release_id), 'CANCELLED', FND_MESSAGE.GET_STRING('POS','POS_PO_CANCELLED'), 'FROZEN', FND_MESSAGE.GET_STRING('POS','POS_PO_FROZEN'), 'CLOSED', FND_MESSAGE.GET_STRING('POS','POS_PO_CLOSED'), 'FINALLY CLOSED', FND_MESSAGE.GET_STRING('POS','POS_PO_FINALLYCLOSED'), 'ON HOLD', FND_MESSAGE.GET_STRING('POS','POS_ON_HOLD'), 'INTERNAL CHANGE', FND_MESSAGE.GET_STRING('POS','POS_PO_INTERNAL_CHANGE'), 'ACCEPTED', FND_MESSAGE.GET_STRING('POS','POS_ACCEPTED'), 'REJECTED', FND_MESSAGE.GET_STRING('POS','POS_REJECTED'), 'ACKNOWLEDGED', FND_MESSAGE.GET_STRING('POS','POS_PO_ACKNOWLEDGED'), 'PARTIALLY_ACKNOWLEDGED', FND_MESSAGE.GET_STRING('POS','POS_PO_PARTIALLY_ACKED'), 'SUPPLIER_CHANGE_PENDING', FND_MESSAGE.GET_STRING('POS','POS_PO_SUP_CHANGE'), 'ACK_REQUIRED', FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'), to_char(NULL) ) PO_STATUS, PORA.VENDOR_ORDER_NUM, NULL NEW_SUPPLIER_ORDER_NUMBER, null, null GLOBAL_AGREEMENT_FLAG, PORA.CONSIGNED_CONSUMPTION_FLAG, PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0,'F',null,por.po_release_id) STATUS_CODE FROM PO_VENDOR_SITES_ALL VS, PO_VENDORS V, PO_RELEASES_ARCHIVE_ALL POR, PO_RELEASES_ALL PORA, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, FND_LOOKUP_VALUES_VL POLC2, FND_LOOKUP_VALUES_VL POLC3, AP_TERMS AT, PO_HEADERS_ARCHIVE_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 POR.PO_RELEASE_ID = PORA.PO_RELEASE_ID AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET','PLANNED') AND POR.LATEST_EXTERNAL_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 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
, POHA.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
, POHA.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'
, DECODE(POH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_GBLA')
, 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
, 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(PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0
, 'F'
, POH.PO_HEADER_ID
, NULL)
, 'CANCELLED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CANCELLED')
, 'FROZEN'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'CLOSED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CLOSED')
, 'FINALLY CLOSED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FINALLYCLOSED')
, 'ON HOLD'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ON_HOLD')
, 'INTERNAL CHANGE'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_INTERNAL_CHANGE')
, 'ACCEPTED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ACCEPTED')
, 'REJECTED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_REJECTED')
, 'ACKNOWLEDGED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_ACKNOWLEDGED')
, 'PARTIALLY_ACKNOWLEDGED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_PARTIALLY_ACKED')
, 'SUPPLIER_CHANGE_PENDING'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_SUP_CHANGE')
, 'ACK_REQUIRED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ACCP_REQUIRED')
, TO_CHAR(NULL) ) PO_STATUS
, POHA.VENDOR_ORDER_NUM
, NULL NEW_SUPPLIER_ORDER_NUMBER
, NULL REQUEST_STATUS
, NVL( POH.GLOBAL_AGREEMENT_FLAG
, 'N')
, POHA.CONSIGNED_CONSUMPTION_FLAG
, PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0
, 'F'
, POH.PO_HEADER_ID
, NULL) STATUS_CODE
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_HEADERS_ARCHIVE_ALL POH
, PO_HEADERS_ALL POHA
, 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.PO_HEADER_ID = POHA.PO_HEADER_ID
AND POH.LATEST_EXTERNAL_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 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
, PORA.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
, PORA.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
, 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(PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0
, 'F'
, NULL
, POR.PO_RELEASE_ID)
, 'CANCELLED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CANCELLED')
, 'FROZEN'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FROZEN')
, 'CLOSED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_CLOSED')
, 'FINALLY CLOSED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_FINALLYCLOSED')
, 'ON HOLD'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ON_HOLD')
, 'INTERNAL CHANGE'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_INTERNAL_CHANGE')
, 'ACCEPTED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ACCEPTED')
, 'REJECTED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_REJECTED')
, 'ACKNOWLEDGED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_ACKNOWLEDGED')
, 'PARTIALLY_ACKNOWLEDGED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_PARTIALLY_ACKED')
, 'SUPPLIER_CHANGE_PENDING'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_PO_SUP_CHANGE')
, 'ACK_REQUIRED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_ACCP_REQUIRED')
, TO_CHAR(NULL) ) PO_STATUS
, PORA.VENDOR_ORDER_NUM
, NULL NEW_SUPPLIER_ORDER_NUMBER
, NULL
, NULL GLOBAL_AGREEMENT_FLAG
, PORA.CONSIGNED_CONSUMPTION_FLAG
, PO_ACKNOWLEDGE_PO_GRP.GET_PO_STATUS_CODE(1.0
, 'F'
, NULL
, POR.PO_RELEASE_ID) STATUS_CODE
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, PO_RELEASES_ARCHIVE_ALL POR
, PO_RELEASES_ALL PORA
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_HEADERS_ARCHIVE_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 POR.PO_RELEASE_ID = PORA.PO_RELEASE_ID
AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED')
AND POR.LATEST_EXTERNAL_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 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'