VIEW: APPS.ICX_PO_SUPPLIER_ORDERS_V

Source

View Text - Preformatted

SELECT DISTINCT POH.SEGMENT1, 'PO' , POH.ACCEPTANCE_DUE_DATE , POH.APPROVED_DATE , NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE') , 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') , 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.APPROVAL_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.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 , PDT.TYPE_NAME , PO_INQ_SV.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 , TO_DATE(NULL) , NULL , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , TO_DATE(NULL) , NULL , TO_NUMBER(NULL) , NULL , TO_NUMBER(NULL) , decode(poh.type_lookup_code, 'BLANKET', to_char(poh.blanket_total_amount, fnd_currency.safe_get_format_mask(poh.currency_code,30)), to_char(PO_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID), fnd_currency.safe_get_format_mask(POH.CURRENCY_CODE,30))) , V.ATTRIBUTE14 FROM po_document_types_tl PDT, po_lookup_codes POLC, po_lookup_codes POLC2, po_lookup_codes POLC3, po_lookup_codes POLC4, po_vendors V, po_vendor_sites VS, po_vendor_contacts VC, ap_terms AT, hr_locations_all HRL1, hr_locations_all HRL2, gl_daily_conversion_types GLDC, po_headers POH WHERE PDT.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND PDT.LANGUAGE = USERENV('LANG') AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE 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.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.APPROVED_FLAG IN ('Y') AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL') UNION ALL SELECT DISTINCT POH.SEGMENT1, 'RELEASE' , POR.ACCEPTANCE_DUE_DATE , POR.APPROVED_DATE , NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE') , 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') , 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 , POH.APPROVAL_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||'-'|| 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 , PDT.TYPE_NAME , PO_INQ_SV.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 , POR.CANCEL_DATE , POR.CANCEL_REASON , POR.CANCELLED_BY , PO_INQ_SV.GET_PERSON_NAME(POR.CANCELLED_BY) , POR.HOLD_BY , POR.HOLD_DATE , POR.HOLD_REASON , POR.RELEASE_NUM , POR.RELEASE_TYPE , POR.PO_RELEASE_ID ,to_char( PO_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID), fnd_currency.safe_get_format_mask(POH.CURRENCY_CODE,30)) , V.ATTRIBUTE14 FROM PO_DOCUMENT_TYPES_TL PDT, PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_LOOKUP_CODES POLC3, PO_LOOKUP_CODES POLC4, PO_VENDORS V, PO_VENDOR_SITES VS, PO_VENDOR_CONTACTS VC, AP_TERMS AT, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, GL_DAILY_CONVERSION_TYPES GLDC, PO_RELEASES POR, PO_HEADERS POH WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND PDT.DOCUMENT_TYPE_CODE(+)= 'RELEASE' AND PDT.LANGUAGE = USERENV('LANG') AND PDT.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE 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.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE AND POLC.LOOKUP_CODE = NVL(POR.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(POR.CLOSED_CODE, 'OPEN') AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE' AND POH.TYPE_LOOKUP_CODE = 'BLANKET' AND POR.APPROVED_FLAG IN ('Y') AND POR.AUTHORIZATION_STATUS NOT IN ('IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL') AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')

View Text - HTML Formatted

SELECT DISTINCT POH.SEGMENT1
, 'PO'
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, 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')
, 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.APPROVAL_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.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
, PDT.TYPE_NAME
, PO_INQ_SV.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
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, TO_CHAR(POH.BLANKET_TOTAL_AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, TO_CHAR(PO_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)))
, V.ATTRIBUTE14
FROM PO_DOCUMENT_TYPES_TL PDT
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, GL_DAILY_CONVERSION_TYPES GLDC
, PO_HEADERS POH
WHERE PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.LANGUAGE = USERENV('LANG')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
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.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.APPROVED_FLAG IN ('Y')
AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL') UNION ALL SELECT DISTINCT POH.SEGMENT1
, 'RELEASE'
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, 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')
, 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
, POH.APPROVAL_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||'-'|| 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
, PDT.TYPE_NAME
, PO_INQ_SV.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
, POR.CANCEL_DATE
, POR.CANCEL_REASON
, POR.CANCELLED_BY
, PO_INQ_SV.GET_PERSON_NAME(POR.CANCELLED_BY)
, POR.HOLD_BY
, POR.HOLD_DATE
, POR.HOLD_REASON
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.PO_RELEASE_ID
, TO_CHAR( PO_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, V.ATTRIBUTE14
FROM PO_DOCUMENT_TYPES_TL PDT
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, GL_DAILY_CONVERSION_TYPES GLDC
, PO_RELEASES POR
, PO_HEADERS POH
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND PDT.DOCUMENT_TYPE_CODE(+)= 'RELEASE'
AND PDT.LANGUAGE = USERENV('LANG')
AND PDT.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE
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.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND GLDC.CONVERSION_TYPE (+) = POH.RATE_TYPE
AND POLC.LOOKUP_CODE = NVL(POR.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(POR.CLOSED_CODE
, 'OPEN')
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND POR.APPROVED_FLAG IN ('Y')
AND POR.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')