[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
gc_validate_inv := gc_validate_inv||' (SELECT i.invoice_id';
gc_validate_inv := gc_validate_inv||' SELECT ''Unreleased Hold exists''';
SELECT count(1)
INTO ln_inv_id
FROM (SELECT i.invoice_id invoice_id
FROM ap_invoices_all i, ap_invoice_distributions d
WHERE d.invoice_id = i.invoice_id
AND i.invoice_id = p_in_inv_id
AND d.posted_flag IN ('N', 'Y') --Included correct validation status , Bug9397505
AND i.validation_request_id IS NULL
AND ( NOT EXISTS (
SELECT 'Unreleased Hold exists'
FROM ap_holds h
WHERE h.invoice_id = i.invoice_id
AND h.hold_lookup_code IN
('QTY ORD', 'QTY REC', 'AMT ORD', 'AMT REC',
'QUALITY', 'PRICE', 'TAX DIFFERENCE',
'CURRENCY DIFFERENCE', 'REC EXCEPTION',
'TAX VARIANCE', 'PO NOT APPROVED', 'PO REQUIRED',
'MAX SHIP AMOUNT', 'MAX RATE AMOUNT',
'MAX TOTAL AMOUNT', 'TAX AMOUNT RANGE',
'MAX QTY ORD', 'MAX QTY REC', 'MAX AMT ORD',
'MAX AMT REC', 'CANT CLOSE PO', 'CANT TRY PO CLOSE',
'LINE VARIANCE')
AND h.release_lookup_code IS NULL)));
SELECT SUM(DECODE(transaction_type,'P',-1*accounted_amount,accounted_amount)) amount
INTO ln_amount
FROM
( SELECT 'I' transaction_type
,SUM(NVL(ai.invoice_amount * NVL(ai.exchange_rate,1),0)) accounted_amount
FROM ap_invoices ai
WHERE ai.vendor_id = p_in_vendor_id
AND ai.vendor_site_id = p_in_vendor_site_id
AND ai.invoice_type_lookup_code <> 'PREPAYMENT' --Bug9252303
AND ai.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
AND ai.org_id = p_in_org_id
AND ((AP_TP_STMT_PKG.invoice_validate_status(ai.invoice_id) = 'Y' --'Y' Bug9252303
AND P_UNVALIDATED_TRX = 'N')
OR P_UNVALIDATED_TRX = 'Y')
AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
= DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
,'UNACCOUNTED','N'
,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
AND ((P_UNAPPROVED_TRX = 'N'
AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE','CANCEL','CANCELLED'))
OR (P_UNAPPROVED_TRX = 'Y' ))
UNION ALL
SELECT 'P' transaction_type
,SUM(NVL(aip.amount * NVL(aip.exchange_rate,1),0)) accounted_amount
FROM ap_invoice_payments aip
,ap_checks ac
WHERE aip.check_id = ac.check_id
AND ac.vendor_id = p_in_vendor_id
AND ac.vendor_site_id = p_in_vendor_site_id
-- AND ac.check_date < TO_DATE(P_FROM_DOC_DATE,'RRRR/MM/DD HH24:MI:SS') Bug 14724500
AND aip.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND ac.org_id = p_in_org_id
AND ac.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ac.currency_code,P_CURRENCY),ac.currency_code)
AND ((P_ACCOUNTED = 'ACCOUNTED' AND aip.posted_flag = 'Y')
OR (P_ACCOUNTED = 'UNACCOUNTED' AND aip.posted_flag = 'N')
OR (P_ACCOUNTED = 'BOTH'))
--Bug9252303: Commented below query that selects prepayment applications.
/* UNION ALL
SELECT 'A' transaction_type
,SUM((NVL(aid.amount,0)* NVL(ai.exchange_rate,1))*-1) amount_applied
FROM ap_invoices ai
,ap_invoice_distributions_all aid
,ap_invoices aipre
,ap_invoice_distributions_all aidpre
WHERE ai.invoice_id = aid.invoice_id
AND aid.prepay_distribution_id = aidpre.invoice_distribution_id
AND aipre.invoice_id = aidpre.invoice_id
AND aid.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
AND ai.vendor_id = p_in_vendor_id
AND ai.vendor_site_id = p_in_vendor_site_id
AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
AND ai.org_id = p_in_org_id
AND ai.wfapproval_status NOT IN ('CANCEL','CANCELLED')
AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
= DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
,'UNACCOUNTED','N'
,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
AND ((P_UNAPPROVED_TRX = 'N'
AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE'))
OR (P_UNAPPROVED_TRX = 'Y' ))*/
);