VIEW: APPS.POS_PO_HEADERS_ARCHIVE_V

Source

View Text - Preformatted

SELECT 'N', POH.ACCEPTANCE_DUE_DATE, POH.APPROVED_DATE, NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE'), POH.CLOSED_DATE, POH.COMMENTS, 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'), POH.CURRENCY_CODE, NVL(POH.FIRM_STATUS_LOOKUP_CODE,'N'), POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, POH.SHIP_VIA_LOOKUP_CODE, POH.TYPE_LOOKUP_CODE, POH.ACCEPTANCE_REQUIRED_FLAG, POH.APPROVED_FLAG, DECODE (POH.CANCEL_FLAG, 'I', NULL, POH.CANCEL_FLAG), POH.CONFIRMING_ORDER_FLAG, POH.ENABLED_FLAG, NVL(POH.FROZEN_FLAG, 'N'), POH.SUMMARY_FLAG, NVL(POH.USER_HOLD_FLAG, 'N'), 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, POH.SEGMENT1, POH.ATTRIBUTE_CATEGORY, 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, 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, POS_GET.GET_PERSON_NAME(POH.AGENT_ID), 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), DECODE (VS.FAX, NULL, NULL, '('||VS.FAX_AREA_CODE||') '||VS.FAX), DECODE (VC.LAST_NAME, NULL, NULL, VC.LAST_NAME||', '|| VC.FIRST_NAME), AT.NAME, HRL1.LOCATION_CODE, HRL2.LOCATION_CODE, GLDC.USER_CONVERSION_TYPE, POLC.DISPLAYED_FIELD, POLC2.DISPLAYED_FIELD, POLC3.DISPLAYED_FIELD, POLC4.DISPLAYED_FIELD, NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), TO_CHAR(POS_TOTALS_PO_SV.GET_PO_ARCHIVE_TOTAL( POH.PO_HEADER_ID, POH.REVISION_NUM,POH.TYPE_LOOKUP_CODE), FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE, 30)), V.ATTRIBUTE14, POH.ORG_ID, HOU.NAME FROM PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_LOOKUP_CODES POLC3, PO_LOOKUP_CODES POLC4, PO_VENDORS V, PO_VENDOR_SITES_ALL VS, PO_VENDOR_CONTACTS VC, AP_TERMS AT, HR_LOCATIONS_ALL_TL HRL1, HR_LOCATIONS_ALL_TL HRL2, GL_DAILY_CONVERSION_TYPES GLDC, PO_HEADERS_ARCHIVE_ALL POH, HR_ALL_ORGANIZATION_UNITS_TL HOU WHERE V.VENDOR_ID = POH.VENDOR_ID AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND VC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID AND AT.TERM_ID (+) = POH.TERMS_ID AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID AND HRL1.LANGUAGE (+) = USERENV('LANG') AND HRL2.LANGUAGE (+) = USERENV('LANG') AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE AND POLC.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE') AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND POLC2.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE AND POLC2.LOOKUP_TYPE (+) = 'FOB' AND POLC3.LOOKUP_CODE (+) =POH.FREIGHT_TERMS_LOOKUP_CODE AND POLC3.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND POLC4.LOOKUP_CODE = NVL(POH.CLOSED_CODE, 'OPEN') AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE' AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD', 'BLANKET','PLANNED', 'CONTRACT' ) AND POH.LATEST_EXTERNAL_FLAG = 'Y' AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID AND HOU.LANGUAGE (+)= USERENV('LANG') UNION ALL SELECT 'Y', POR.ACCEPTANCE_DUE_DATE, POR.APPROVED_DATE, NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE'), POH.CLOSED_DATE, POH.COMMENTS, 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'), POH.CURRENCY_CODE, NVL(POR.FIRM_STATUS_LOOKUP_CODE,'N'), POH.FOB_LOOKUP_CODE, POH.FREIGHT_TERMS_LOOKUP_CODE, POH.SHIP_VIA_LOOKUP_CODE, POH.TYPE_LOOKUP_CODE, POR.ACCEPTANCE_REQUIRED_FLAG, POR.APPROVED_FLAG, DECODE (POR.CANCEL_FLAG, 'I', NULL, POR.CANCEL_FLAG), POH.CONFIRMING_ORDER_FLAG, POH.ENABLED_FLAG, NVL(POR.FROZEN_FLAG, 'N'), POH.SUMMARY_FLAG, NVL(POR.HOLD_FLAG,'N'), 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, POH.SEGMENT1||'-'||POR.RELEASE_NUM, POR.ATTRIBUTE_CATEGORY, 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, DECODE(POR.RELEASE_TYPE, 'BLANKET',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_BLKTR'), 'SCHEDULED',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_PLNDR')) TYPE_NAME, POS_GET.GET_PERSON_NAME(POR.AGENT_ID), 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), DECODE (VS.FAX, NULL, NULL, '('||VS.FAX_AREA_CODE||') '||VS.FAX), DECODE (VC.LAST_NAME, NULL, NULL, VC.LAST_NAME||', '||VC.FIRST_NAME), AT.NAME, HRL1.LOCATION_CODE, HRL2.LOCATION_CODE, GLDC.USER_CONVERSION_TYPE, POLC.DISPLAYED_FIELD, POLC2.DISPLAYED_FIELD, POLC3.DISPLAYED_FIELD, POLC4.DISPLAYED_FIELD, POS_GET.GET_PERSON_NAME(POR.CANCELLED_BY), POR.RELEASE_NUM, POR.RELEASE_TYPE, POR.PO_RELEASE_ID, TO_CHAR(POS_TOTALS_PO_SV.GET_RELEASE_ARCHIVE_TOTAL( POR.PO_RELEASE_ID, POR.REVISION_NUM), FND_CURRENCY.SAFE_GET_FORMAT_MASK( POH.CURRENCY_CODE, 30)), V.ATTRIBUTE14, POR.ORG_ID, HOU.NAME FROM PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_LOOKUP_CODES POLC3, PO_LOOKUP_CODES POLC4, PO_VENDORS V, PO_VENDOR_SITES_ALL VS, PO_VENDOR_CONTACTS VC, AP_TERMS AT, HR_LOCATIONS_ALL_TL HRL1, HR_LOCATIONS_ALL_TL HRL2, GL_DAILY_CONVERSION_TYPES GLDC, PO_RELEASES_ARCHIVE_ALL POR, PO_HEADERS_ARCHIVE_ALL POH, HR_ALL_ORGANIZATION_UNITS_TL HOU WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND V.VENDOR_ID = POH.VENDOR_ID AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND VC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID AND AT.TERM_ID (+) = POH.TERMS_ID AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID AND HRL2.LANGUAGE (+) = USERENV('LANG') AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID AND HRL1.LANGUAGE (+) = USERENV('LANG') AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE AND POLC.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS, 'INCOMPLETE') AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND POLC2.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE AND POLC2.LOOKUP_TYPE (+) = 'FOB' AND POLC3.LOOKUP_CODE (+) =POH.FREIGHT_TERMS_LOOKUP_CODE AND POLC3.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND POLC4.LOOKUP_CODE = NVL(POH.CLOSED_CODE, 'OPEN') AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE' AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET', 'PLANNED') AND POR.LATEST_EXTERNAL_FLAG = 'Y' AND POH.LATEST_EXTERNAL_FLAG = 'Y' AND HOU.ORGANIZATION_ID (+)= POR.ORG_ID AND HOU.LANGUAGE (+)= USERENV('LANG')

View Text - HTML Formatted

SELECT 'N'
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.CLOSED_DATE
, POH.COMMENTS
, 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')
, POH.CURRENCY_CODE
, NVL(POH.FIRM_STATUS_LOOKUP_CODE
, 'N')
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POH.ACCEPTANCE_REQUIRED_FLAG
, POH.APPROVED_FLAG
, DECODE (POH.CANCEL_FLAG
, 'I'
, NULL
, POH.CANCEL_FLAG)
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POH.FROZEN_FLAG
, 'N')
, POH.SUMMARY_FLAG
, NVL(POH.USER_HOLD_FLAG
, 'N')
, 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
, POH.SEGMENT1
, POH.ATTRIBUTE_CATEGORY
, 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
, 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
, POS_GET.GET_PERSON_NAME(POH.AGENT_ID)
, 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)
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX)
, DECODE (VC.LAST_NAME
, NULL
, NULL
, VC.LAST_NAME||'
, '|| VC.FIRST_NAME)
, AT.NAME
, HRL1.LOCATION_CODE
, HRL2.LOCATION_CODE
, GLDC.USER_CONVERSION_TYPE
, POLC.DISPLAYED_FIELD
, POLC2.DISPLAYED_FIELD
, POLC3.DISPLAYED_FIELD
, POLC4.DISPLAYED_FIELD
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_CHAR(POS_TOTALS_PO_SV.GET_PO_ARCHIVE_TOTAL( POH.PO_HEADER_ID
, POH.REVISION_NUM
, POH.TYPE_LOOKUP_CODE)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, V.ATTRIBUTE14
, POH.ORG_ID
, HOU.NAME
FROM PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS_ALL_TL HRL1
, HR_LOCATIONS_ALL_TL HRL2
, GL_DAILY_CONVERSION_TYPES GLDC
, PO_HEADERS_ARCHIVE_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE V.VENDOR_ID = POH.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND VC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND AT.TERM_ID (+) = POH.TERMS_ID
AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND HRL1.LANGUAGE (+) = USERENV('LANG')
AND HRL2.LANGUAGE (+) = USERENV('LANG')
AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE
AND POLC.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POLC2.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+) = 'FOB'
AND POLC3.LOOKUP_CODE (+) =POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND POLC4.LOOKUP_CODE = NVL(POH.CLOSED_CODE
, 'OPEN')
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD'
, 'BLANKET'
, 'PLANNED'
, 'CONTRACT' )
AND POH.LATEST_EXTERNAL_FLAG = 'Y'
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG') UNION ALL SELECT 'Y'
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.CLOSED_DATE
, POH.COMMENTS
, 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')
, POH.CURRENCY_CODE
, NVL(POR.FIRM_STATUS_LOOKUP_CODE
, 'N')
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POR.ACCEPTANCE_REQUIRED_FLAG
, POR.APPROVED_FLAG
, DECODE (POR.CANCEL_FLAG
, 'I'
, NULL
, POR.CANCEL_FLAG)
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POR.FROZEN_FLAG
, 'N')
, POH.SUMMARY_FLAG
, NVL(POR.HOLD_FLAG
, 'N')
, 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
, POH.SEGMENT1||'-'||POR.RELEASE_NUM
, POR.ATTRIBUTE_CATEGORY
, 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
, DECODE(POR.RELEASE_TYPE
, 'BLANKET'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_BLKTR')
, 'SCHEDULED'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_PLNDR')) TYPE_NAME
, POS_GET.GET_PERSON_NAME(POR.AGENT_ID)
, 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)
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX)
, DECODE (VC.LAST_NAME
, NULL
, NULL
, VC.LAST_NAME||'
, '||VC.FIRST_NAME)
, AT.NAME
, HRL1.LOCATION_CODE
, HRL2.LOCATION_CODE
, GLDC.USER_CONVERSION_TYPE
, POLC.DISPLAYED_FIELD
, POLC2.DISPLAYED_FIELD
, POLC3.DISPLAYED_FIELD
, POLC4.DISPLAYED_FIELD
, POS_GET.GET_PERSON_NAME(POR.CANCELLED_BY)
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.PO_RELEASE_ID
, TO_CHAR(POS_TOTALS_PO_SV.GET_RELEASE_ARCHIVE_TOTAL( POR.PO_RELEASE_ID
, POR.REVISION_NUM)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( POH.CURRENCY_CODE
, 30))
, V.ATTRIBUTE14
, POR.ORG_ID
, HOU.NAME
FROM PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS_ALL_TL HRL1
, HR_LOCATIONS_ALL_TL HRL2
, GL_DAILY_CONVERSION_TYPES GLDC
, PO_RELEASES_ARCHIVE_ALL POR
, PO_HEADERS_ARCHIVE_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND V.VENDOR_ID = POH.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND VC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND AT.TERM_ID (+) = POH.TERMS_ID
AND HRL1.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND HRL2.LANGUAGE (+) = USERENV('LANG')
AND HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL1.LANGUAGE (+) = USERENV('LANG')
AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE
AND POLC.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POLC2.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+) = 'FOB'
AND POLC3.LOOKUP_CODE (+) =POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND POLC4.LOOKUP_CODE = NVL(POH.CLOSED_CODE
, 'OPEN')
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED')
AND POR.LATEST_EXTERNAL_FLAG = 'Y'
AND POH.LATEST_EXTERNAL_FLAG = 'Y'
AND HOU.ORGANIZATION_ID (+)= POR.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')