[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(NVL(prepay_amount_remaining, total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code IN
('ITEM', 'ACCRUAL',
'REC_TAX', 'NONREC_TAX' )
AND NVL(reversal_flag,'N') <> 'Y';
SELECT NVL(SUM(NVL(prepay_amount_remaining, total_dist_amount)),0)
INTO l_prepay_amount_remaining_item
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code IN
('ITEM', 'ACCRUAL')
--'REC_TAX', 'NONREC_TAX' ) --bugfix:5609186
AND NVL(reversal_flag,'N') <> 'Y';
SELECT NVL(SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount)),0)
INTO l_prepay_amount_remaining_tax
FROM ap_invoice_distributions_all aid, --Tax line
ap_invoice_distributions_all aid1 --Item line
WHERE aid1.invoice_id = p_invoice_id
AND aid1.invoice_line_number = p_line_number
AND aid.invoice_id = aid1.invoice_id
AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND NVL(aid1.reversal_flag,'N') <> 'Y'
AND NVL(aid.reversal_flag,'N') <> 'Y'; */
UPDATE ap_invoice_lines
SET line_selected_for_appl_flag = 'Y',
prepay_appl_request_id = p_request_id
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number;
UPDATE ap_invoice_lines
SET line_selected_for_appl_flag = 'N',
prepay_appl_request_id = NULL
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number;
UPDATE ap_invoice_lines
SET line_selected_for_appl_flag = 'N',
prepay_appl_request_id = NULL
WHERE ( prepay_appl_request_id = p_request_id
OR prepay_appl_request_id IS NULL)
AND line_selected_for_appl_flag = 'Y';
l_already_selected_flag VARCHAR2(1);
SELECT NVL(line_selected_for_appl_flag,'N'),
prepay_appl_request_id
INTO l_already_selected_flag,
l_request_id
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number;
IF l_already_selected_flag = 'Y' AND
l_request_id IS NULL THEN
RETURN ('LOCKED');
IF l_already_selected_flag = 'Y' AND
l_request_id IS NOT NULL THEN
IF l_request_id = P_request_id THEN
RETURN ('UNLOCKED');
IF l_already_selected_flag = 'N' THEN
RETURN ('UNLOCKED');
SELECT invoice_num
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
AND aid.invoice_distribution_id = l_prepay_dist_id;
SELECT distribution_line_number
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = l_prepay_dist_id;
/* SELECT (0 - SUM(NVL(amount,0)))
INTO l_prepaid_amount
FROM ap_invoice_lines_all
WHERE invoice_id = l_invoice_id
AND line_type_lookup_code IN ('PREPAY', 'TAX')
AND NVL(invoice_includes_prepay_flag, 'N') = 'N' -- Bug 5675960. Added the NVL
AND nvl(prepay_invoice_id,-999)<>-999
AND nvl(prepay_line_number,-999)<>-999 ; */
SELECT (0 - SUM(NVL(aid.amount,0)))
INTO l_prepaid_amount
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id IS NOT NULL
AND NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';
which just selects the prepayment invoices for the vendor.This
is done for performance overheads.The comparison of earliest
settlement date would be done with the cursor variable,also the
earlier select statement which would call the get_total_prepays
as a filter is removed and logic is implemented here as this
would reduce the wait time*/
CURSOR prepayment_invoices IS
SELECT earliest_settlement_date,invoice_id
from ap_invoices
where vendor_id=l_vendor_id
and invoice_type_lookup_code='PREPAYMENT'
and earliest_settlement_date is not null --bug7015402
AND ((l_org_id is not null and org_id = l_org_id) or l_org_id is null);
which just selects the prepayment invoices for the vendor.This
is done for performance overheads.The comparison of earliest
settlement date would be done with the cursor variable,also the
earlier select statement which would call the get_total_prepays
as a filter is removed and logic is implemented here as this
would reduce the wait time*/
CURSOR prepayment_invoices IS
SELECT earliest_settlement_date,invoice_id
from ap_invoices
where vendor_id=l_vendor_id
and invoice_type_lookup_code='PREPAYMENT'
/*bug 7015402*/
and payment_status_flag = 'Y'
and earliest_settlement_date is not null
AND ((l_org_id is not null and org_id = l_org_id) or l_org_id is null);
SELECT SUM(total_dist_amount -
NVL(prepay_amount_remaining, total_dist_amount))
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE aid.invoice_id = P_invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ail.line_type_lookup_code <> 'TAX'
AND aid.line_type_lookup_code IN
('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
-- No need to include variances since the total_dist_amount
-- includes the variances total and it is store in the
-- nonrec tax distribution.
AND NVL(reversal_flag,'N') <> 'Y';
SELECT ABS(SUM(amount))
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = P_invoice_id
AND ail.line_type_lookup_code = 'PREPAY';
SELECT ai.invoice_type_lookup_code
INTO l_inv_type_lookup_code
FROM ap_invoices ai
WHERE ai.invoice_id = P_invoice_id;
SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE aid.invoice_id = P_invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ail.line_type_lookup_code <> 'TAX'
-- We will only get REC_TAX and NONREC_TAX dist for the
-- inclusive case (parent line is not TAX)
AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
AND aid.line_type_lookup_code IN
('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
-- there is no need to include the tax variance distr
-- here since the prepay_amount_remaining and the
-- total_dist_amount will be including them and it will
-- be stored at the primary nonrec tax dist.
AND nvl(aid.reversal_flag,'N') <> 'Y';
SELECT decode(AI.EARLIEST_SETTLEMENT_DATE,null,'PERMANENT','TEMPORARY')
FROM ap_invoices_all ai
WHERE ai.invoice_id = P_invoice_id;
SELECT SUM((NVL(aid1.amount, 0) - NVL(aid1.prepay_tax_diff_amount, 0))* -1)
INTO l_prepay_amt_applied
FROM ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2,
ap_invoice_lines_all ail
WHERE aid1.invoice_id = P_invoice_id
AND aid1.line_type_lookup_code IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')
AND aid1.invoice_id = ail.invoice_id
AND aid1.invoice_line_number = ail.line_number
AND ail.line_type_lookup_code = 'PREPAY'
AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
AND aid2.invoice_id = P_prepay_id
AND aid2.last_update_date = P_application_date ;
SELECT SUM(ail.amount * -1)
INTO l_prepay_amt_applied
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = P_invoice_id
AND ail.line_type_lookup_code = 'PREPAY'
AND ail.prepay_invoice_id = P_prepay_id;
SELECT sum(nvl(prepay_amount_remaining,total_dist_amount)),
AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id)
FROM ap_invoice_distributions aip
WHERE aip.invoice_id = X_Prepay_Id
AND aip.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND nvl(aip.reversal_flag,'N') <> 'Y'
AND nvl(aip.prepay_amount_remaining,amount) > 0
AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id) IN
(SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aid.dist_code_combination_id, X_Sob_Id)
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = X_Invoice_ID)
GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id)
ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id);
SELECT sum(amount)
INTO l_invoice_amount
FROM ap_invoice_distributions
WHERE invoice_id = X_Invoice_ID
AND line_type_lookup_code IN ('ITEM','PREPAY')
AND nvl(reversal_flag,'N') <> 'Y'
AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
dist_code_combination_id, X_Sob_Id)
= l_bal_segment;
SELECT SUM(nvl(prepay_amount_remaining,total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND NVL(reversal_flag,'N') <> 'Y';
SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX',
'TIPV', 'TRV')
AND NVL(reversal_flag,'N') <> 'Y';
SELECT SUM(NVL(ail.amount, 0))
INTO l_exclusive_tax_amt_applied
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = X_invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND NVL(ail.discarded_flag, 'N') <> 'Y'
AND NVL(ail.cancelled_flag, 'N') <> 'Y'
AND ail.prepay_invoice_id = X_prepay_invoice_id
AND ail.prepay_line_number = X_prepay_Line_Number;
SELECT ai.payment_status_flag,
ai.invoice_currency_code,
ai.payment_currency_code,
ai.payment_cross_rate_date,
ai.payment_cross_rate_type,
NVL(ai.invoice_amount, 0)
FROM ap_invoices_all ai
WHERE ai.invoice_id = X_Invoice_Id;
SELECT NVL(SUM(NVL(ail.amount,0)), 0)
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = X_Invoice_Id
AND ail.line_type_lookup_code <> 'TAX'
AND (ail.line_type_lookup_code <> 'PREPAY'
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'); */
SELECT SUM(nvl(aps.amount_remaining, 0))
INTO l_unpaid_amount
FROM ap_payment_schedules_all aps
WHERE aps.invoice_id = x_invoice_id;
SELECT SUM(decode(line_type_lookup_code, 'TAX', nvl(ail.amount, 0), 0)) l_tax_lines,
SUM(decode(line_type_lookup_code, 'TAX', 0, nvl(ail.amount, 0))) l_non_tax_lines,
SUM(decode(line_type_lookup_code, 'PREPAY', nvl(ail.amount, 0), 0)) l_prep_applied,
SUM(decode(line_type_lookup_code, 'AWT', nvl(ail.amount, 0), 0)) l_awt_lines
INTO l_tax_lines,
l_non_tax_lines,
l_prep_applied,
l_awt_lines
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = x_invoice_id;
SELECT nvl(SUM(nvl(amount, 0)), 0)
INTO l_item_lines_proration
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = x_invoice_id
AND ail.line_type_lookup_code <> 'TAX'
AND(ail.line_type_lookup_code <> 'PREPAY' OR nvl(ail.invoice_includes_prepay_flag, 'N') = 'Y');
SELECT nvl(SUM(nvl(amount, 0)), 0)
INTO l_tax_lines_proration
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = x_invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND ail.amount > 0;
SELECT nvl(SUM(amount), 0)
INTO l_sum_checks_payment
FROM ap_invoice_payments_all
WHERE invoice_id = x_invoice_id;
SELECT SUM(NVL(aps.amount_remaining, 0))
INTO l_unpaid_amount
FROM ap_payment_schedules_all aps
WHERE aps.invoice_id = X_Invoice_Id;
SELECT ai.payment_status_flag,
ai.invoice_currency_code,
ai.payment_currency_code,
ai.payment_cross_rate_date,
ai.payment_cross_rate_type,
NVL(ai.invoice_amount, 0)
FROM ap_invoices_all ai
WHERE ai.invoice_id = X_Invoice_Id;
SELECT NVL(SUM(NVL(ail.amount,0)), 0)
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = X_Invoice_Id
AND (ail.line_type_lookup_code <> 'PREPAY'
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT SUM(nvl(aps.amount_remaining, 0))
INTO l_unpaid_amount
FROM ap_payment_schedules_all aps
WHERE aps.invoice_id = x_invoice_id;
SELECT ai.payment_status_flag ,
ai.invoice_currency_code,
ai.payment_currency_code,
ai.payment_cross_rate_date,
ai.payment_cross_rate_type,
NVL(ai.invoice_amount, 0)
FROM ap_invoices_all ai
WHERE ai.invoice_id = X_Invoice_Id;
SELECT NVL(SUM(NVL(ail.included_tax_amount,0)), 0)
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = X_Invoice_Id
AND (ail.line_type_lookup_code <> 'PREPAY'
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT SUM(NVL(aps.amount_remaining, 0))
INTO l_unpaid_amount
FROM ap_payment_schedules aps
WHERE aps.invoice_id = X_Invoice_Id;
SELECT NVL(SUM(NVL(prepay_amount_remaining, total_dist_amount)), 0)
INTO l_remaining_inc_tax_dist
FROM ap_invoice_distributions_all
WHERE invoice_id = X_Invoice_Id
AND invoice_line_number = X_Line_Number
AND charge_applicable_to_dist_id = X_Invoice_Dist_Id
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND NVL(reversal_flag,'N') <> 'Y';