View: POS_PO_INVOICE_SUP_ORDERS_V

Product:	POS - iSupplier Portal
Description:	Fetches the invoices associated with a Purchase Order and the details of the PO and the invoice.
Implementation/DBA Data:	APPS.POS_PO_INVOICE_SUP_ORDERS_V

View Text

SELECT DISTINCT AID.INVOICE_ID
, 'PO' PO_RELEASE_FLAG
, POH.ROWID ROW_ID
, POH.ACCEPTANCE_DUE_DATE
, POH.AMOUNT_LIMIT
, POH.APPROVED_DATE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE') AUTHORIZATION_STATUS
, POH.BLANKET_TOTAL_AMOUNT
, POH.CLOSED_DATE
, POH.COMMENTS
, POH.END_DATE
, POH.END_DATE_ACTIVE
, POH.FIRM_DATE
, POH.GOVERNMENT_CONTEXT
, POH.MIN_RELEASE_AMOUNT
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POH.NOTE_TO_VENDOR
, POH.PRINT_COUNT
, POH.PRINTED_DATE
, POH.QUOTE_VENDOR_QUOTE_NUMBER
, POH.QUOTE_WARNING_DELAY
, POH.QUOTE_WARNING_DELAY_UNIT
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POH.REPLY_DATE
, POH.REVISED_DATE
, POH.REVISION_NUM
, POH.RFQ_CLOSE_DATE
, POH.START_DATE
, POH.START_DATE_ACTIVE
, POH.VENDOR_ORDER_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 PO_VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE PO_CURRENCY_CODE
, NVL(POH.FIRM_STATUS_LOOKUP_CODE
, 'N') FIRM_STATUS_LOOKUP_CODE
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.FROM_TYPE_LOOKUP_CODE
, POH.QUOTATION_CLASS_CODE
, POH.QUOTE_TYPE_LOOKUP_CODE
, POH.REPLY_METHOD_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.STATUS_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POH.USSGL_TRANSACTION_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 ORDER_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_DOCUMENT_NUM
, POH.SEGMENT2
, POH.SEGMENT3
, POH.SEGMENT4
, POH.SEGMENT5
, 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
, POS_GET.GET_PERSON_NAME(POH.AGENT_ID) BUYER_NAME
, V.VENDOR_NAME PO_VENDOR_NAME
, V.TYPE_1099
, VS.VENDOR_SITE_CODE PO_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
, DECODE (VC.LAST_NAME
, NULL
, NULL
, VC.LAST_NAME||'
, '|| VC.FIRST_NAME) PO_VENDOR_CONTACT
, TO_NUMBER(NULL) RELEASE_NUM
, NULL RELEASE_TYPE
, TO_NUMBER(NULL) PO_RELEASE_ID
, TO_CHAR(POS_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( POH.CURRENCY_CODE
, 30)) PO_TOTAL
, V.ATTRIBUTE14 SUPPLIER_URL
FROM PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_ALL POD
, AP_INVOICE_DISTRIBUTIONS_ALL AID
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 POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND POH.APPROVED_FLAG IN ('Y'
, 'R')
AND POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID UNION ALL SELECT DISTINCT AID.INVOICE_ID
, 'RELEASE'
, POR.ROWID
, POR.ACCEPTANCE_DUE_DATE
, POH.AMOUNT_LIMIT
, POR.APPROVED_DATE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.BLANKET_TOTAL_AMOUNT
, POH.CLOSED_DATE
, POH.COMMENTS
, POH.END_DATE
, POH.END_DATE_ACTIVE
, POR.FIRM_DATE
, POR.GOVERNMENT_CONTEXT
, POH.MIN_RELEASE_AMOUNT
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POR.NOTE_TO_VENDOR
, POR.PRINT_COUNT
, POR.PRINTED_DATE
, POH.QUOTE_VENDOR_QUOTE_NUMBER
, POH.QUOTE_WARNING_DELAY
, POH.QUOTE_WARNING_DELAY_UNIT
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POH.REPLY_DATE
, POR.REVISED_DATE
, POR.REVISION_NUM
, POH.RFQ_CLOSE_DATE
, POH.START_DATE
, POH.START_DATE_ACTIVE
, POH.VENDOR_ORDER_NUM
, POR.AGENT_ID BUYER_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 PO_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.FROM_TYPE_LOOKUP_CODE
, POH.QUOTATION_CLASS_CODE
, POH.QUOTE_TYPE_LOOKUP_CODE
, POH.REPLY_METHOD_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.STATUS_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POR.USSGL_TRANSACTION_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
, POH.SEGMENT2
, POH.SEGMENT3
, POH.SEGMENT4
, POH.SEGMENT5
, 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
, POS_GET.GET_PERSON_NAME(POR.AGENT_ID)
, V.VENDOR_NAME
, V.TYPE_1099
, 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)
, 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.SAFE_GET_FORMAT_MASK( POH.CURRENCY_CODE
, 30))
, V.ATTRIBUTE14
FROM PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_ALL POD
, AP_INVOICE_DISTRIBUTIONS_ALL AID
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 POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND POR.APPROVED_FLAG IN ('Y'
, 'R')
AND POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
AND POD.PO_HEADER_ID = POR.PO_HEADER_ID
AND POD.PO_RELEASE_ID = POR.PO_RELEASE_ID

Columns