Query to get GL Encumbrance Details for Source REQ, PO and Invoice
SELECT '1REQ' TYPE, prh.requisition_header_id header_id,
prh.segment1 num, prd.budget_account_id ccid,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date last_update_date,
SUM (NVL (prd.encumbered_amount, 0)) amount,prh.authorization_status status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id =prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (prh.cancel_flag, 'N') = 'N'
---- AND prd.budget_account_id = v_code_combination_id
AND prd.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND prd.org_id = v_org_id
GROUP BY prh.requisition_header_id,
prh.segment1,
prd.budget_account_id,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date,prh.authorization_status
UNION ALL
SELECT '2PO' TYPE, poh.po_header_id header_id,
poh.segment1 num, pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date last_update_date,
SUM (NVL (pda.encumbered_amount, 0)) amount,poh.authorization_status status
FROM po_headers_all poh, po_distributions_all pda
WHERE poh.po_header_id = pda.po_header_id
AND pda.org_id = v_org_id
AND poh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (poh.cancel_flag, 'N') = 'N'
----- AND pda.budget_account_id = v_code_combination_id
AND pda.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND NOT EXISTS ( --added to exclude the PO match Inovice --
SELECT 1
FROM ap_invoices_all aia, ap_invoice_lines_all apia
WHERE aia.invoice_id = apia.invoice_id
AND aia.org_id = apia.org_id
AND apia.po_header_id = pda.po_header_id
AND apia.org_id = pda.org_id
AND ap_invoices_utility_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = 'APPROVED'
union --added on 31mar17 to exclude po prepay invoice --
SELECT 1
FROM ap_invoices_all aia
where aia.quick_po_header_id=pda.po_header_id
and aia.org_id=pda.org_id
)
GROUP BY poh.po_header_id,
poh.segment1,
pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date,poh.authorization_status
UNION ALL
SELECT '3INV' TYPE, aia.invoice_id header_id,
aia.invoice_num num,
aid.dist_code_combination_id ccid,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date last_update_date,
round(SUM(NVL ((aid.amount*nvl(aia.EXCHANGE_RATE,1)), 0)),2) amount,
'APPROVED' status
FROM ap_invoices_all aia,
ap_invoice_distributions_all aid
WHERE aia.invoice_id = aid.invoice_id
AND aia.org_id = aid.org_id
---AND aid.dist_code_combination_id = v_code_combination_id
AND aid.dist_code_combination_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND aia.org_id = v_org_id
AND aia.cancelled_date IS NULL
and aid.invoice_id not in ( 504841)------added on 29AUG16. For Special Case Journal has been made against invoice---
AND apps.ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) IN ('APPROVED', 'FULL', 'UNPAID','AVAILABLE')
GROUP BY aia.invoice_id,
aia.invoice_num,
aid.dist_code_combination_id,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code) main
WHERE 1 = 1
--AND main.header_id NOT IN v_header_id
AND TO_CHAR (last_update_date, 'YYYYMMDDHH24MISS') <=v_last_date
group by main.status
No comments:
Post a Comment