VIEW: APPS.PO_CLMREQ_DIST_FUNDS_USED_V

Source

View Text - Preformatted

SELECT req_distribution_id, sum(funds_used) funds_used, sum(distribution_amount) distribution_amount from ( (select decode(POLL.matching_basis , 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0) , ((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))*poll.price_override)) distribution_amount, nvl(pod.funded_value,0) funds_used, pod.req_distribution_id FROM po_line_locations_all poll, po_distributions_all pod , po_headers_all_sec poh WHERE pod.line_location_id = poll.line_location_id and pod.po_header_id = poh.po_header_id and pod.req_distribution_id is not null and pod.distribution_type = 'STANDARD' and pod.funded_value > 0) union all (select decode(POLL.matching_basis , 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0) , ((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))*poll.price_override)) distribution_amount, decode(pod.change_status, 'NEW' , nvl(pod.funded_value,0) , 'UPDATE', nvl(pod.change_in_funded_value, 0), 0) funds_used, pod.req_distribution_id FROM po_line_locations_draft_all poll, po_distributions_draft_all pod , po_headers_draft_all poh WHERE pod.line_location_id = poll.line_location_id and pod.draft_id = poll.draft_id and pod.po_header_id = poh.po_header_id and pod.draft_id = poh.draft_id and pod.req_distribution_id is not null and pod.distribution_type = 'STANDARD' and ((pod.change_status = 'NEW' and pod.funded_value > 0) or (pod.change_status = 'UPDATE' and pod.change_in_funded_value > 0)) and nvl(pod.delete_flag, 'N') = 'N' and nvl(poll.delete_flag, 'N') = 'N' and exists (select ref.style_id from po_doc_style_headers ref where ref.style_id = poh.style_id and ref.clm_flag='Y') and exists(select 1 from po_drafts d where d.draft_id = poh.draft_id and d.document_id = poh.po_header_id and d.status = 'DRAFT')) ) group by req_distribution_id

View Text - HTML Formatted

SELECT REQ_DISTRIBUTION_ID
, SUM(FUNDS_USED) FUNDS_USED
, SUM(DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT
FROM ( (SELECT DECODE(POLL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0))*POLL.PRICE_OVERRIDE)) DISTRIBUTION_AMOUNT
, NVL(POD.FUNDED_VALUE
, 0) FUNDS_USED
, POD.REQ_DISTRIBUTION_ID
FROM PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, PO_HEADERS_ALL_SEC POH
WHERE POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.REQ_DISTRIBUTION_ID IS NOT NULL
AND POD.DISTRIBUTION_TYPE = 'STANDARD'
AND POD.FUNDED_VALUE > 0) UNION ALL (SELECT DECODE(POLL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0))*POLL.PRICE_OVERRIDE)) DISTRIBUTION_AMOUNT
, DECODE(POD.CHANGE_STATUS
, 'NEW'
, NVL(POD.FUNDED_VALUE
, 0)
, 'UPDATE'
, NVL(POD.CHANGE_IN_FUNDED_VALUE
, 0)
, 0) FUNDS_USED
, POD.REQ_DISTRIBUTION_ID
FROM PO_LINE_LOCATIONS_DRAFT_ALL POLL
, PO_DISTRIBUTIONS_DRAFT_ALL POD
, PO_HEADERS_DRAFT_ALL POH
WHERE POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POD.DRAFT_ID = POLL.DRAFT_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.DRAFT_ID = POH.DRAFT_ID
AND POD.REQ_DISTRIBUTION_ID IS NOT NULL
AND POD.DISTRIBUTION_TYPE = 'STANDARD'
AND ((POD.CHANGE_STATUS = 'NEW'
AND POD.FUNDED_VALUE > 0) OR (POD.CHANGE_STATUS = 'UPDATE'
AND POD.CHANGE_IN_FUNDED_VALUE > 0))
AND NVL(POD.DELETE_FLAG
, 'N') = 'N'
AND NVL(POLL.DELETE_FLAG
, 'N') = 'N'
AND EXISTS (SELECT REF.STYLE_ID
FROM PO_DOC_STYLE_HEADERS REF
WHERE REF.STYLE_ID = POH.STYLE_ID
AND REF.CLM_FLAG='Y')
AND EXISTS(SELECT 1
FROM PO_DRAFTS D
WHERE D.DRAFT_ID = POH.DRAFT_ID
AND D.DOCUMENT_ID = POH.PO_HEADER_ID
AND D.STATUS = 'DRAFT')) ) GROUP BY REQ_DISTRIBUTION_ID