View: POS_PO_ARCH_SUMMARY_V

Product:	POS - iSupplier Portal
Description:
Implementation/DBA Data:	APPS.POS_PO_ARCH_SUMMARY_V

View Text

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'

Columns