VIEW: APPS.CE_PO_FC_ORDERS_V

Source

View Text - Preformatted

SELECT POH.SEGMENT1, /* 1 */ DECODE(POLL.MATCHING_BASIS, 'AMOUNT', (GREATEST(POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED, 0) - NVL(POD.AMOUNT_CANCELLED, 0) + NVL(POD.NONRECOVERABLE_TAX,0),0) * NVL(POD.RATE,NVL(POH.RATE,1))), (GREATEST(POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_BILLED, 0) - /* 2 */ NVL(POD.QUANTITY_CANCELLED, 0),0) * (NVL(POLL.PRICE_OVERRIDE,0)) + NVL(POD.NONRECOVERABLE_TAX,0)) * NVL(POD.RATE,NVL(POH.RATE,1))), DECODE(POLL.MATCHING_BASIS, 'AMOUNT', (GREATEST(POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED, 0) - NVL(POD.AMOUNT_CANCELLED, 0) + NVL(POD.NONRECOVERABLE_TAX,0),0)), (GREATEST(POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_BILLED, 0) - /* 3 */ NVL(POD.QUANTITY_CANCELLED, 0),0) * (NVL(POLL.PRICE_OVERRIDE,0)) + NVL(POD.NONRECOVERABLE_TAX,0))), NVL(POLL.PROMISED_DATE, NVL(POLL.NEED_BY_DATE, NVL(POR.ACCEPTANCE_DUE_DATE, /* 4 */ DECODE(POR.PO_RELEASE_ID, NULL, POLL.CREATION_DATE, POR.RELEASE_DATE)))), NVL(NVL(POH.CURRENCY_CODE,FSP.INVOICE_CURRENCY_CODE), /* 5 */ SOB.CURRENCY_CODE), DECODE(POLL.PO_RELEASE_ID, NULL,NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE'), /* 6 */ NVL(POR.AUTHORIZATION_STATUS,'INCOMPLETE')), DECODE(POH.VENDOR_SITE_ID, NULL,NVL(VENDOR.PAYMENT_PRIORITY,99), /* 7 */ NVL(SITE.PAYMENT_PRIORITY,99)), NVL(VENDOR.VENDOR_TYPE_LOOKUP_CODE,'-1'), /* 8 */ DECODE(POH.VENDOR_SITE_ID,NULL,NVL(VENDOR.PAY_GROUP_LOOKUP_CODE,'-1'), /* 9 */ NVL(SITE.PAY_GROUP_LOOKUP_CODE,'-1')), POD.ORG_ID, /* 10 */ POD.PROJECT_ID, /* 11 */ POL.LINE_NUM, /* 12 */ POD.PO_DISTRIBUTION_ID, /* 13 */ POLL.VALUE_BASIS, /* 14 */ POL.PURCHASE_BASIS, /* 15 */ POLL.MATCHING_BASIS /* 16 */ FROM PO_HEADERS_ALL POH, AP_SUPPLIER_SITES_ALL SITE, AP_SUPPLIERS VENDOR, PO_RELEASES_ALL POR, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL POLL, PO_DISTRIBUTIONS_ALL POD, PO_DOCUMENT_TYPES_ALL PDT, FINANCIALS_SYSTEM_PARAMS_ALL FSP, GL_LEDGERS SOB WHERE POH.ORG_ID = PDT.ORG_ID AND POL.ORG_ID = POH.ORG_ID AND POLL.ORG_ID = POL.ORG_ID AND POD.ORG_ID = FSP.ORG_ID AND SITE.ORG_ID = POH.ORG_ID AND POR.ORG_ID(+) = POLL.ORG_ID AND POH.VENDOR_SITE_ID = SITE.VENDOR_SITE_ID(+) AND POH.VENDOR_ID = VENDOR.VENDOR_ID (+) AND SOB.LEDGER_ID = POD.SET_OF_BOOKS_ID AND SOB.object_type_code = 'L' AND nvl(SOB.complete_flag, 'Y') = 'Y' AND POD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+) AND POLL.SHIPMENT_TYPE IN ('STANDARD', 'BLANKET','PLANNED','SCHEDULED') AND POLL.PO_LINE_ID = POL.PO_LINE_ID AND POLL.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('FROZEN','CANCELLED', 'FINALLY CLOSED') AND NVL(POH.CANCEL_FLAG,'N') = 'N' AND NVL(POH.FROZEN_FLAG,'N') = 'N' AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PDT.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND NVL(POL.PURCHASE_BASIS, 'GOODS') NOT IN ('TEMP LABOR') AND NOT exists (SELECT 1 FROM ap_invoice_lines_all ail WHERE ail.po_header_id = poh.po_header_id AND ail.po_line_id = pol.po_line_id and ail.po_line_location_id = POLL.LINE_LOCATION_ID )

View Text - HTML Formatted

SELECT POH.SEGMENT1
, /* 1 */ DECODE(POLL.MATCHING_BASIS
, 'AMOUNT'
, (GREATEST(POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED
, 0) - NVL(POD.AMOUNT_CANCELLED
, 0) + NVL(POD.NONRECOVERABLE_TAX
, 0)
, 0) * NVL(POD.RATE
, NVL(POH.RATE
, 1)))
, (GREATEST(POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_BILLED
, 0) - /* 2 */ NVL(POD.QUANTITY_CANCELLED
, 0)
, 0) * (NVL(POLL.PRICE_OVERRIDE
, 0)) + NVL(POD.NONRECOVERABLE_TAX
, 0)) * NVL(POD.RATE
, NVL(POH.RATE
, 1)))
, DECODE(POLL.MATCHING_BASIS
, 'AMOUNT'
, (GREATEST(POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED
, 0) - NVL(POD.AMOUNT_CANCELLED
, 0) + NVL(POD.NONRECOVERABLE_TAX
, 0)
, 0))
, (GREATEST(POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_BILLED
, 0) - /* 3 */ NVL(POD.QUANTITY_CANCELLED
, 0)
, 0) * (NVL(POLL.PRICE_OVERRIDE
, 0)) + NVL(POD.NONRECOVERABLE_TAX
, 0)))
, NVL(POLL.PROMISED_DATE
, NVL(POLL.NEED_BY_DATE
, NVL(POR.ACCEPTANCE_DUE_DATE
, /* 4 */ DECODE(POR.PO_RELEASE_ID
, NULL
, POLL.CREATION_DATE
, POR.RELEASE_DATE))))
, NVL(NVL(POH.CURRENCY_CODE
, FSP.INVOICE_CURRENCY_CODE)
, /* 5 */ SOB.CURRENCY_CODE)
, DECODE(POLL.PO_RELEASE_ID
, NULL
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, /* 6 */ NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE'))
, DECODE(POH.VENDOR_SITE_ID
, NULL
, NVL(VENDOR.PAYMENT_PRIORITY
, 99)
, /* 7 */ NVL(SITE.PAYMENT_PRIORITY
, 99))
, NVL(VENDOR.VENDOR_TYPE_LOOKUP_CODE
, '-1')
, /* 8 */ DECODE(POH.VENDOR_SITE_ID
, NULL
, NVL(VENDOR.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 9 */ NVL(SITE.PAY_GROUP_LOOKUP_CODE
, '-1'))
, POD.ORG_ID
, /* 10 */ POD.PROJECT_ID
, /* 11 */ POL.LINE_NUM
, /* 12 */ POD.PO_DISTRIBUTION_ID
, /* 13 */ POLL.VALUE_BASIS
, /* 14 */ POL.PURCHASE_BASIS
, /* 15 */ POLL.MATCHING_BASIS /* 16 */
FROM PO_HEADERS_ALL POH
, AP_SUPPLIER_SITES_ALL SITE
, AP_SUPPLIERS VENDOR
, PO_RELEASES_ALL POR
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, PO_DOCUMENT_TYPES_ALL PDT
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, GL_LEDGERS SOB
WHERE POH.ORG_ID = PDT.ORG_ID
AND POL.ORG_ID = POH.ORG_ID
AND POLL.ORG_ID = POL.ORG_ID
AND POD.ORG_ID = FSP.ORG_ID
AND SITE.ORG_ID = POH.ORG_ID
AND POR.ORG_ID(+) = POLL.ORG_ID
AND POH.VENDOR_SITE_ID = SITE.VENDOR_SITE_ID(+)
AND POH.VENDOR_ID = VENDOR.VENDOR_ID (+)
AND SOB.LEDGER_ID = POD.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y'
AND POD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'PLANNED'
, 'SCHEDULED')
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ('FROZEN'
, 'CANCELLED'
, 'FINALLY CLOSED')
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.FROZEN_FLAG
, 'N') = 'N'
AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND NVL(POL.PURCHASE_BASIS
, 'GOODS') NOT IN ('TEMP LABOR')
AND NOT EXISTS (SELECT 1
FROM AP_INVOICE_LINES_ALL AIL
WHERE AIL.PO_HEADER_ID = POH.PO_HEADER_ID
AND AIL.PO_LINE_ID = POL.PO_LINE_ID
AND AIL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID )