[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ac.check_number,
ac.check_id,
ac.check_date,
ai.invoice_num,
ai.invoice_id,
aip.accounting_date,
aip.invoice_payment_type,
ac.payment_method_lookup_code,
alc2.displayed_field
FROM ap_invoice_payments aip,
ap_checks ac,
ap_invoices ai,
ap_lookup_codes alc2
WHERE aip.invoice_id = l_invoice_id
AND aip.payment_num = l_payment_num
AND aip.check_id = ac.check_id
AND aip.other_invoice_id = ai.invoice_id (+)
AND alc2.lookup_type = 'PAYMENT METHOD'
AND alc2.lookup_code = ac.payment_method_code
UNION ALL
SELECT ac.check_number,
ac.check_id,
ac.check_date,
ai.invoice_num,
ai.invoice_id,
aip.accounting_date,
aip.invoice_payment_type,
ac.payment_method_lookup_code,
alc2.displayed_field
FROM ap_invoice_payments aip,
ap_checks ac,
ap_invoices ai,
ap_lookup_codes alc2,
ap_invoice_lines ail1,
ap_invoice_lines ail2,
ap_invoice_distributions aid1,
ap_invoice_distributions aid2
WHERE aip.invoice_id = ail2.invoice_id
AND ail1.invoice_id = l_invoice_id
AND aip.payment_num = l_payment_num
AND aid1.invoice_id = ail1.invoice_id
AND aid1.invoice_line_number = ail1.line_number
AND aid2.invoice_id = ail2.invoice_id
AND aid2.invoice_line_number = ail2.line_number
AND aip.check_id = ac.check_id
AND aip.other_invoice_id = ai.invoice_id (+)
AND alc2.lookup_type = 'PAYMENT METHOD'
AND alc2.lookup_code = ac.payment_method_code
AND (ail1.line_type_lookup_code = 'PREPAY' OR
ail1.line_type_lookup_code = 'TAX' AND
aid1.prepay_tax_parent_id IS NOT NULL
)
AND aid2.INVOICE_DISTRIBUTION_ID = aid1.prepay_distribution_id;
SELECT displayed_field
INTO type_trans
FROM ap_lookup_codes
WHERE lookup_type = 'NLS TRANSLATION'
AND lookup_code = 'PREPAY';
SELECT SUM(AIP.AMOUNT)
INTO l_total_payment_made
FROM AP_INVOICE_PAYMENTS AIP, AP_INVOICES AI
WHERE AIP.INVOICE_ID = AI.INVOICE_ID
AND AI.INVOICE_ID = l_invoice_id;
SELECT check_id
INTO l_check_id
FROM
( SELECT ac.check_id check_id
FROM ap_invoice_payments aip,
ap_checks ac
WHERE aip.check_id = ac.check_id
AND aip.invoice_id = l_invoice_id
UNION ALL
SELECT ac.check_id check_id
FROM ap_invoice_payments aip,
ap_checks ac,
ap_invoice_lines ail1,
ap_invoice_lines ail2,
ap_invoice_distributions aid1,
ap_invoice_distributions aid2
WHERE aip.check_id = ac.check_id
AND ail1.invoice_id = l_invoice_id
AND ail2.invoice_id = aip.invoice_id
AND aid1.invoice_id = ail1.invoice_id
AND aid1.invoice_line_number = ail1.line_number
AND aid2.invoice_id = ail2.invoice_id
AND aid2.invoice_line_number = ail2.line_number
AND (ail1.line_type_lookup_code = 'PREPAY' OR
ail1.line_type_lookup_code = 'TAX' AND
aid1.prepay_tax_parent_id IS NOT NULL
)
AND aid2.invoice_distribution_id = aid1.prepay_distribution_id
)
WHERE ROWNUM = 1;
SELECT MAX(check_date)
INTO l_last_payment_date
FROM
( SELECT ac.check_date check_date
FROM ap_invoice_payments aip,
ap_checks ac
WHERE aip.check_id = ac.check_id
AND aip.invoice_id = l_invoice_id
UNION ALL
SELECT ac.check_date check_date
FROM ap_invoice_payments aip,
ap_checks ac,
ap_invoice_lines ail1,
ap_invoice_lines ail2,
ap_invoice_distributions aid1,
ap_invoice_distributions aid2
WHERE aip.check_id = ac.check_id
AND ail1.invoice_id = l_invoice_id
AND ail2.invoice_id = aip.invoice_id
AND aid1.invoice_id = ail1.invoice_id
AND aid1.invoice_line_number = ail1.line_number
AND aid2.invoice_id = ail2.invoice_id
AND aid2.invoice_line_number = ail2.line_number
AND (ail1.line_type_lookup_code = 'PREPAY' OR
ail1.line_type_lookup_code = 'TAX' AND
aid1.prepay_tax_parent_id IS NOT NULL
)
AND aid2.invoice_distribution_id = aid1.prepay_distribution_id
);
SELECT SUM(nvl(prepay_amount_remaining,amount))
INTO l_prepay_amt_invoiced
FROM ap_invoice_distributions aid,ap_invoices ai
WHERE aid.invoice_id = P_invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y'
AND ai.invoice_id = P_invoice_id
AND ai.earliest_settlement_date IS NOT NULL
AND ai.earliest_settlement_date <= trunc(SYSDATE);
SELECT SUM(nvl(prepay_amount_remaining,amount))
INTO l_prepay_amt_invoiced
FROM ap_invoice_distributions aid,ap_invoices ai
WHERE aid.invoice_id = P_invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y'
AND ai.invoice_id = P_invoice_id
AND ai.earliest_settlement_date IS NOT NULL
AND ai.earliest_settlement_date <= trunc(SYSDATE)
AND nvl(prepay_amount_remaining,amount) = aid.amount;
select nvl(sum(maximum_amount_to_apply),0)
INTO l_prepay_amt_expensed
from ap_expense_report_headers
where employee_id = p_employee_id
and vouchno = 0
and default_currency_code = p_currency
AND prepay_num = p_Invoice_num
AND advance_invoice_to_apply = P_invoice_id;
select nvl(sum(maximum_amount_to_apply),0)
INTO l_prepay_amt_expensed
from ap_expense_report_headers
where employee_id = p_employee_id
and vouchno = 0
and default_currency_code = p_currency
AND prepay_num = p_Invoice_num
AND advance_invoice_to_apply = P_invoice_id
AND report_header_id <> P_header_id;
SELECT SUM(NVL(prepay_amount_remaining,amount))
INTO l_prepay_amt_invoiced
FROM ap_invoice_distributions aid,ap_invoices ai
WHERE aid.invoice_id = P_invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y'
AND ai.invoice_id = P_invoice_id
AND ai.earliest_settlement_date IS NOT NULL
AND ai.earliest_settlement_date <= TRUNC(SYSDATE);
SELECT NVL(SUM(maximum_amount_to_apply),0)
INTO l_prepay_amt_expensed
FROM ap_expense_report_headers
WHERE employee_id = p_employee_id
AND vouchno = 0
AND default_currency_code = p_currency
AND prepay_num = p_Invoice_num
AND advance_invoice_to_apply = P_invoice_id;
SELECT SUM(NVL(aid.prepay_amount_remaining,aid.amount))
INTO l_prepay_amt_available
FROM ap_invoice_distributions aid,ap_invoice_lines al
WHERE aid.invoice_id = al.invoice_id
AND aid.invoice_line_number= al.line_number
and al.line_number=line_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y'
AND al.invoice_id = P_invoice_id;