[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_cleared_select VARCHAR2(2000);
SELECT xdln.event_id pay_event_id ,
xlah.ae_header_id,
xlah.ledger_id,
SUM(xdln.unrounded_accounted_dr) gross_amount,
MIN(aip.invoice_id) invoice_id
FROM ap_invoice_distributions aid ,
ap_invoice_payments aip ,
ap_payment_hist_dists payh ,
xla_distribution_links xdln ,
xla_ae_headers xlah
WHERE aip.invoice_id = pn_invoice_id
AND aip.check_id = pn_check_id
AND aip.invoice_id = aid.invoice_id
AND (aip.accounting_date >= pd_start_date AND aip.accounting_date <= pd_end_date)
AND aip.reversal_inv_pmt_id IS NULL
AND payh.invoice_payment_id = aip.invoice_payment_id
AND payh.accounting_event_id = aip.accounting_event_id
AND payh.invoice_distribution_id = aid.invoice_distribution_id
AND payh.accounting_event_id = xdln.event_id
AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
GROUP BY xdln.event_id,
xlah.ae_header_id,
xlah.ledger_id;
SELECT xdln.event_id pay_event_id ,
xlah.ae_header_id,
xlah.ledger_id,
SUM(xdln.unrounded_accounted_dr) gross_amount,
MIN(aip.invoice_id) invoice_id
FROM ap_invoice_distributions aid ,
ap_invoice_payments aip ,
ap_payment_hist_dists payh ,
xla_distribution_links xdln ,
xla_ae_headers xlah
WHERE aip.invoice_id = pn_invoice_id
AND aip.check_id = pn_check_id
AND aip.invoice_id = aid.invoice_id
AND (aip.accounting_date >= pd_start_date AND aip.accounting_date <= pd_end_date)
AND aip.reversal_inv_pmt_id IS NOT NULL
AND payh.invoice_payment_id = aip.invoice_payment_id
AND payh.accounting_event_id = aip.accounting_event_id
AND payh.invoice_distribution_id = aid.invoice_distribution_id
AND payh.accounting_event_id = xdln.event_id
AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
GROUP BY xdln.event_id,
xlah.ae_header_id,
xlah.ledger_id;
SELECT xdln.event_id event,
xlah.ae_header_id ,
xlah.ledger_id ,
SUM(xdln.unrounded_accounted_dr) pay_amount
FROM ap_invoice_distributions_all aid ,
ap_payment_hist_dists payh ,
xla_distribution_links xdln ,
xla_ae_headers xlah ,
ap_invoice_payments_all aip
WHERE aid.invoice_id = cn_invoice_id
AND aip.invoice_payment_id = payh.invoice_payment_id
AND aip.check_id = pn_check_id
AND aid.line_type_lookup_code ='AWT'
AND payh.accounting_event_id = cn_event_id
AND payh.invoice_distribution_id = aid.invoice_distribution_id
AND xdln.event_id = payh.accounting_event_id
AND xdln.source_distribution_id_num_1 = payh.payment_hist_dist_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xlah.accounting_entry_status_code = 'F'
GROUP BY xdln.event_id,
xlah.ae_header_id,
xlah.ledger_id;
SELECT ai.invoice_id invoice_id
FROM ap_invoice_payments aip,
ap_invoice_distributions aid,
ap_invoices ai
WHERE ai.invoice_id = cn_invoice_id
AND ai.invoice_id = aip.invoice_id
AND aip.invoice_id = aid.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aip.accounting_event_id = aid.accounting_event_id
AND ROWNUM =1;
SELECT xdln.event_id event,
xlah.ae_header_id ,
xlah.ledger_id ,
SUM(xdln.unrounded_accounted_dr) pay_amount
FROM ap_invoice_distributions_all aid ,
xla_distribution_links xdln,
xla_ae_headers xlah
WHERE aid.invoice_id = cn_invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND xdln.event_id = aid.accounting_event_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xlah.accounting_entry_status_code = 'F'
GROUP BY xdln.event_id,
xlah.ae_header_id,
xlah.ledger_id;
SELECT SUM(xdln.unrounded_accounted_dr) INTO ln_invoice_amount
FROM ap_invoice_distributions_all aid ,
xla_distribution_links xdln,
xla_ae_headers xlah
WHERE aid.invoice_id = ln_invoice_id
AND aid.line_type_lookup_code <> 'AWT'
AND xdln.event_id = aid.accounting_event_id
AND xdln.unrounded_accounted_dr IS NOT NULL
AND xlah.ae_header_id = xdln.ae_header_id
AND xlah.event_id = xdln.event_id
AND xlah.ledger_id = p_ledger_id
AND xdln.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xlah.accounting_entry_status_code = 'F';
SELECT SIGN(NVL(aip.payment_base_amount,aip.amount)) INTO ln_sign
FROM ap_invoice_payments aip
WHERE aip.invoice_id = ln_invoice_id
AND aip.accounting_event_id = ln_event_id;
SELECT invoice_type_lookup_code INTO lv_invoice_type
FROM ap_invoices WHERE invoice_id = ln_invoice_id;
REM it fetches the AWT data and updates the Global Varieble
REM gn_awt_amount. This function fetches data from this GT Varieble
REM Parameters: InvoiceID, Accounting Start and End Date, Payment Void or Not
REM +======================================================================+
*/
FUNCTION get_awt_amount
RETURN NUMBER
IS
vn_ret_awt NUMBER;