[Home] [Help]
107: --****************************************************
108: IF P_UNVALIDATED_TRX = 'N' THEN
109: gc_validate_inv := ' AND ai.invoice_id IN ';
110: gc_validate_inv := gc_validate_inv||' (SELECT i.invoice_id';
111: gc_validate_inv := gc_validate_inv||' FROM ap_invoices_all i, ap_invoice_distributions d';
112: gc_validate_inv := gc_validate_inv||' WHERE d.invoice_id = i.invoice_id';
113: gc_validate_inv := gc_validate_inv||' AND i.invoice_id = ai.invoice_id';
114: gc_validate_inv := gc_validate_inv||' AND d.posted_flag IN (''N'', ''P'')';
115: gc_validate_inv := gc_validate_inv||' AND i.validation_request_id IS NULL';
173: BEGIN
174: SELECT count(1)
175: INTO ln_inv_id
176: FROM (SELECT i.invoice_id invoice_id
177: FROM ap_invoices_all i, ap_invoice_distributions d
178: WHERE d.invoice_id = i.invoice_id
179: AND i.invoice_id = p_in_inv_id
180: AND d.posted_flag IN ('N', 'P')
181: AND i.validation_request_id IS NULL
251: UNION ALL
252: SELECT 'A' transaction_type
253: ,SUM((NVL(aid.amount,0)* NVL(ai.exchange_rate,1))*-1) amount_applied
254: FROM ap_invoices ai
255: ,ap_invoice_distributions_all aid
256: ,ap_invoices aipre
257: ,ap_invoice_distributions_all aidpre
258: WHERE ai.invoice_id = aid.invoice_id
259: AND aid.prepay_distribution_id = aidpre.invoice_distribution_id
253: ,SUM((NVL(aid.amount,0)* NVL(ai.exchange_rate,1))*-1) amount_applied
254: FROM ap_invoices ai
255: ,ap_invoice_distributions_all aid
256: ,ap_invoices aipre
257: ,ap_invoice_distributions_all aidpre
258: WHERE ai.invoice_id = aid.invoice_id
259: AND aid.prepay_distribution_id = aidpre.invoice_distribution_id
260: AND aipre.invoice_id = aidpre.invoice_id
261: AND aid.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')