[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT invoice_num
FROM ap_invoices
WHERE invoice_id =
(SELECT invoice_id
FROM ap_invoice_distributions
WHERE invoice_distribution_id = l_prepay_dist_id);
SELECT distribution_line_number
FROM ap_invoice_distributions
WHERE invoice_distribution_id = l_prepay_dist_id;
SELECT SUM(NVL(aid.amount,0))
INTO distribution_total
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ((aid.line_type_lookup_code NOT IN ('PREPAY', 'AWT')
AND aid.prepay_distribution_id IS NULL)
OR NVL(ail.invoice_includes_prepay_flag,'N') = l_y);
| 'S' - Selected
| 'P' - Partially Posted
| ---------------------------------------------------------------------
| -- Declare cursor to establish the invoice-level posting flag
| --
| -- The first two selects simply look at the posting flags. The 'S'
| -- one means the invoice distributions are selected for accounting
| -- processing. The 'P' is to cover one specific case when some of
| -- the distributions are fully posting (Y) and some are unposting (N).
| -- The status should be partial (P).
| --
| -- MOAC. Use ap_invoice_distributions_all table instead of SO view
| -- since this procedure is called when policy context is not set to
| -- the corresponding OU for the invoice_id
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 04-Mar-05 Yicao Rewrite the procedure for SLA project
*==========================================================================*/
FUNCTION get_posting_status(l_invoice_id IN NUMBER)
RETURN VARCHAR2 IS
invoice_posting_flag VARCHAR2(1);
SELECT cash_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag = 'Y'
UNION
SELECT accrual_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag <>'Y'
UNION
SELECT 'P'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'Y' AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'Y' AND l_cash_basis_flag <> 'Y'))
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y')) -- bug fix 6975868
UNION /*Added for bug 10039729*/
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION
SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
)
-- bug fix 6975868 begin
UNION
SELECT cash_posted_flag
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag = 'Y'
UNION
SELECT accrual_posted_flag
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag <>'Y'
UNION
SELECT 'P'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'Y'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'Y'
AND l_cash_basis_flag <> 'Y'))
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION /*Added for bug 10039729*/
SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ')
UNION
-- bug9440144
SELECT posted_flag
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION /*Added for bug 10039729*/
SELECT 'P'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
AND posted_flag = 'Y'
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION
SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
); -- bug fix 6975868;
| MOAC. Added org_id to select statement. |
+-----------------------------------------------------------------*/
SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N'),
asp.org_id
INTO l_cash_basis_flag,
l_org_id
FROM ap_invoices_all ai,
ap_system_parameters_all asp,
gl_sets_of_books sob
WHERE ai.invoice_id = l_invoice_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = sob.set_of_books_id;
SELECT 'D'
INTO invoice_posting_flag
FROM ap_invoice_distributions_all AID,
xla_events XE
WHERE AID.invoice_id = l_invoice_id
AND AID.accounting_event_id = XE.event_id
AND ((AID.accrual_posted_flag = 'N' AND l_cash_basis_flag = 'N') OR
(AID.cash_posted_flag = 'N' AND l_cash_basis_flag = 'Y'))
AND XE.process_status_code = 'D'
AND rownum < 2;
select count(1)
into dummy_a
from ap_invoices_all
where invoice_num = X_INVOICE_NUM
and vendor_id = X_VENDOR_ID
and org_id = X_ORG_ID -- Bug 5407785
AND (party_site_id = X_PARTY_SITE_ID /*Bug9105666*/
OR (party_site_id is null and X_PARTY_SITE_ID is null)) /*Bug9105666*/
and ((X_ROWID is null) or (rowid <> X_ROWID));
select count(1)
into dummy_b
from ap_history_invoices_all ahi,
ap_supplier_sites_all ass /*Bug9105666*/
where ahi.vendor_id = ass.vendor_id /*Bug9105666*/
and ahi.org_id = ass.org_id /*Bug9105666*/
and ahi.invoice_num = X_INVOICE_NUM
and ahi.vendor_id = X_VENDOR_ID -- Bug 5407785
and ahi.org_id = X_ORG_ID
AND (ass.party_site_id = X_PARTY_SITE_ID /*Bug9105666*/
OR (ass.party_site_id is null and X_PARTY_SITE_ID is null)); /*Bug9105666*/
select count(1)
into dummy
from ap_invoices
where voucher_num = X_VOUCHER_NUM
and ((X_ROWID is null) or (rowid <> X_ROWID));
select sum(l_count)
from
(
SELECT count(*) l_count
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND nvl(match_status_flag, 'Z') = l_status
UNION
SELECT count(*) l_count
FROM ap_self_Assessed_tax_dist_All
WHERE invoice_id = l_invoice_id
AND nvl(match_status_flag, 'Z') = l_status
);
SELECT NVL(fsp.purch_encumbrance_flag,'N'),
ai.org_id,
ai.force_revalidation_flag,
NVL(ai.net_of_retainage_flag,'N') --9503673
INTO encumbrance_flag,
l_org_id,
l_force_revalidation_flag,
l_net_of_retainage_flag --9503673
FROM ap_invoices_all ai,
financials_system_params_all fsp
WHERE ai.invoice_id = l_invoice_id
AND ai.set_of_books_id = fsp.set_of_books_id
AND ai.org_id = fsp.org_id;
SELECT count(*)
INTO invoice_holds
FROM ap_holds_all
WHERE invoice_id = l_invoice_id
AND release_lookup_code is NULL;
SELECT count(*)
INTO dist_var_hold
FROM ap_holds_all
WHERE invoice_id = l_invoice_id
AND hold_lookup_code IN (l_dist_variance, l_line_variance)
AND release_lookup_code is NULL;
SELECT ai.cancelled_date
INTO cancelled_date
FROM ap_invoices_all ai
WHERE ai.invoice_id = l_invoice_id;
SELECT count(*)
INTO match_flag_cnt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_id
AND aid.match_status_flag IS NOT NULL
AND rownum < 2;
SELECT count(*) --Bug8223290
INTO self_match_flag_cnt
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_id = l_invoice_id
--AND aid.match_status_flag IS NOT NULL
AND rownum < 2;
SELECT count(*)
INTO l_validated_cnt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_id
AND aid.match_status_flag = 'N'
AND rownum < 2;
SELECT 'N'
INTO invoice_approval_flag
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_id
AND ail.amount <>
( SELECT NVL(SUM(NVL(aid.amount,0)),0)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
--bugfix:4959567
AND ( aid.line_type_lookup_code <> 'RETAINAGE'
OR (ail.line_type_lookup_code = 'RETAINAGE RELEASE' AND
aid.line_type_lookup_code = 'RETAINAGE') )
/*
AND (ail.line_type_lookup_code <> 'ITEM'
OR (aid.line_type_lookup_code <> 'PREPAY'
and aid.prepay_tax_parent_id IS NULL)
)
*/
AND (AIL.line_type_lookup_code NOT IN ('ITEM', 'RETAINAGE RELEASE')
OR (AIL.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
AND (AID.prepay_distribution_id IS NULL
OR (AID.prepay_distribution_id IS NOT NULL
AND AID.line_type_lookup_code NOT IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')))))
);
SELECT 'N'
INTO invoice_approval_flag
FROM ap_invoice_lines_all AIL, ap_invoices_all A
WHERE AIL.invoice_id = A.invoice_id
AND AIL.invoice_id = l_invoice_id
AND ((AIL.line_type_lookup_code <> 'TAX'
and (AIL.line_type_lookup_code NOT IN ('AWT','PREPAY')
or NVL(AIL.invoice_includes_prepay_flag,'N') = 'Y') OR
(AIL.line_type_lookup_code = 'TAX'
/* bug 5222316 */
and (AIL.prepay_invoice_id IS NULL
or (AIL.prepay_invoice_id is not null
and NVL(AIL.invoice_includes_prepay_flag, 'N') = 'Y')))))
-- and AIL.prepay_invoice_id IS NULL)))
GROUP BY A.invoice_id, A.invoice_amount, A.net_of_retainage_flag
HAVING A.invoice_amount <>
nvl(SUM(nvl(AIL.amount,0) + decode(A.net_of_retainage_flag,
'Y', nvl(AIL.retained_amount,0),0)),0);
SELECT NVL(SUM(nvl(aid.amount,0)), 0)
INTO sum_distributions
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND (aid.line_type_lookup_code <> 'RETAINAGE'
OR (ail.line_type_lookup_code = 'RETAINAGE RELEASE'
and aid.line_type_lookup_code = 'RETAINAGE') )
AND ((aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
AND aid.prepay_distribution_id IS NULL)
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT DISTINCT NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1), ph.po_header_id, -- for CLM Bug 9503239
NVL(SUM(L.amount),0)
FROM ap_invoice_lines_all L,
po_headers PH
WHERE L.invoice_id = l_invoice_id
AND L.po_header_id = PH.po_header_id
AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_PO', 'ITEM_TO_RECEIPT', 'AMOUNT_CORRECTION',
'RETRO PRICE ADJUSTMENT','ITEM_TO_SERVICE_PO','ITEM_TO_SERVICE_RECEIPT') --Bug6931134
--added ITEM_TO_SERVICE_RECEIPT in bug 8891266
AND NVL (L.discarded_flag, 'N' ) <> 'Y'
AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
GROUP BY PH.po_header_id, NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1) -- for CLM Bug 9503239
HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
NVL(SUM(L.quantity_invoiced), 0) <> 0);
--Added below Select for bug 7550789
SELECT invoice_type_lookup_code
INTO l_invoice_type
FROM ap_invoices_all
WHERE invoice_id=l_invoice_id;
SELECT NVL(SUM(AIL.amount), 0)
INTO l_corrected_amount
FROM ap_invoice_lines_all AIL
WHERE corrected_inv_id = l_invoice_id
AND po_header_id = l_po_header_id
AND NVL( AIL.discarded_flag, 'N' ) <> 'Y'
AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y' ;
SELECT DISTINCT(pll.shipment_type)
FROM ap_invoice_lines L,
po_line_locations PLL
WHERE L.invoice_id = l_invoice_id
AND NOT EXISTS (SELECT AIL.corrected_inv_id
FROM ap_invoice_lines AIL
WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
AND AIL.corrected_inv_id = L.invoice_id)
AND L.po_line_location_id = PLL.line_location_id
AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
'RETRO PRICE ADJUSTMENT')
/*
5000309 fbreslin: exclude line if discared or cancled
*/
AND NVL (L.discarded_flag, 'N' ) <> 'Y'
AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
GROUP BY PLL.shipment_type
HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
NVL(SUM(L.quantity_invoiced), 0) <> 0);
SELECT DISTINCT(PRL.release_num)
FROM ap_invoice_lines L,
po_line_locations PLL,
po_releases PRL
WHERE L.invoice_id = l_invoice_id
AND NOT EXISTS (SELECT AIL.corrected_inv_id
FROM ap_invoice_lines AIL
WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
AND AIL.corrected_inv_id = L.invoice_id)
AND L.po_line_location_id = PLL.line_location_id
AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
'RETRO PRICE ADJUSTMENT')
/*
5000309 fbreslin: exclude line if discared or cancled
*/
AND NVL (L.discarded_flag, 'N' ) <> 'Y'
AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
AND PRL.po_release_id = PLL.po_release_id
GROUP BY PRL.release_num
HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
NVL(SUM(L.quantity_invoiced), 0) <> 0);
SELECT DISTINCT(rsh.receipt_num)
FROM ap_invoice_lines L,
rcv_transactions RTXN,
rcv_shipment_headers RSH
WHERE L.invoice_id = l_invoice_id
AND NOT EXISTS (SELECT AIL.corrected_inv_id
FROM ap_invoice_lines AIL
WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
AND AIL.corrected_inv_id = L.invoice_id)
AND L.rcv_transaction_id = RTXN.transaction_id
AND RSH.shipment_header_id = RTXN.shipment_header_id
AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_RECEIPT',
'RETRO PRICE ADJUSTMENT')
/*
5000309 fbreslin: exclude line if discared or cancled
*/
AND NVL (L.discarded_flag, 'N' ) <> 'Y'
AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
GROUP BY rsh.shipment_header_id, rsh.receipt_num
HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
NVL(SUM(L.quantity_invoiced), 0) <> 0);
SELECT DISTINCT(NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1)) -- for CLM Bug 9503239
FROM ap_invoice_lines L,
po_headers PH
WHERE L.invoice_id = l_invoice_id
AND NOT EXISTS (SELECT AIL.corrected_inv_id
FROM ap_invoice_lines AIL
WHERE NVL( AIL.discarded_flag, 'N' ) <> 'Y'
AND NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
AND AIL.corrected_inv_id = L.invoice_id)
AND L.po_header_id = PH.po_header_id
AND L.match_type IN ( 'PRICE_CORRECTION', 'QTY_CORRECTION',
'ITEM_TO_PO', 'ITEM_TO_RECEIPT',
'RETRO PRICE ADJUSTMENT')
/*
5000309 fbreslin: exclude line if discared or cancled
*/
AND NVL (L.discarded_flag, 'N' ) <> 'Y'
AND NVL (L.cancelled_flag, 'N' ) <> 'Y'
GROUP BY PH.po_header_id, NVL(ph.CLM_DOCUMENT_NUMBER , ph.segment1) -- for CLM Bug 9503239
HAVING ( NVL(SUM(L.amount), 0) <> 0 OR
NVL(SUM(L.quantity_invoiced), 0) <> 0);
select (0 - sum(nvl(amount,0)))
into amount_withheld
from ap_invoice_distributions_all /*Bug 16316552*/
where invoice_id = l_invoice_id
and line_type_lookup_code = 'AWT';
SELECT COUNT(*)
INTO notes_count
FROM po_note_references
WHERE table_name = 'AP_INVOICES'
AND foreign_id = l_invoice_id;
SELECT COUNT(*)
INTO holds_count
FROM ap_holds
WHERE release_lookup_code is null
AND invoice_id = l_invoice_id;
SELECT COUNT(*)
INTO holds_count
FROM ap_payment_schedules_all
WHERE hold_flag = 'Y'
AND invoice_id = l_invoice_id;
SELECT COUNT(*)
INTO prepay_count
FROM ap_invoices ai
WHERE vendor_id = l_vendor_id
AND (( l_org_id IS NOT NULL AND
ai.org_id = l_org_id)
OR l_org_id IS NULL)
AND invoice_type_lookup_code = 'PREPAYMENT'
AND earliest_settlement_date IS NOT NULL
AND AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(ai.invoice_id) > 0;
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'
/*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 nvl(encumbered_flag,'N')
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id;
SELECT nvl(encumbered_flag,'N')
FROM ap_self_assessed_tax_dist
WHERE invoice_id = l_invoice_id;
SELECT NVL(fsp.purch_encumbrance_flag,'N'), ai.org_id
INTO l_purch_encumbrance_flag, l_org_id
FROM ap_invoices_all ai,
financials_system_params_all fsp
WHERE ai.invoice_id = l_invoice_id
AND ai.org_id = fsp.org_id;
SELECT 'Y'
FROM ap_holds
WHERE invoice_id = l_invoice_id
AND hold_lookup_code = l_amount
AND release_lookup_code IS NULL;
SELECT 'Y'
FROM ap_holds
WHERE invoice_id = l_invoice_id
AND hold_lookup_code = l_vendor
AND release_lookup_code IS NULL;
SELECT invoice_num
FROM ap_invoices
WHERE vendor_id = P_vendor_id
AND vendor_site_id = P_vendor_site_id
AND invoice_amount = P_invoice_amount
AND invoice_currency_code = P_invoice_currency_code
AND invoice_type_lookup_code =
DECODE(P_invoice_type_lookup_code,
'CREDIT','DEBIT',
'DEBIT','CREDIT');
SELECT vendor_id
INTO l_vendor_id
FROM PO_VENDOR_SITES_ALL
WHERE vendor_site_id = P_vendor_site_id;
SELECT vendor_id
INTO l_vendor_id
FROM PO_VENDOR_SITES_ALL
WHERE vendor_site_id = P_vendor_site_id;
| FUNCTION - selected_for_payment_flag
|
| DESCRIPTION
| returns 'Y' if an invoice has been selected for payment; function
FUNCTION selected_for_payment_flag (P_invoice_id IN number)
RETURN varchar2
IS
l_flag varchar2(1) := 'N';
CURSOR selected_for_payment_cursor IS
SELECT 'Y'
FROM AP_SELECTED_INVOICES
WHERE invoice_id = P_invoice_id
UNION
SELECT 'Y'
FROM AP_PAYMENT_SCHEDULES_ALL
WHERE invoice_id = P_invoice_id
AND checkrun_id IS NOT NULL;
OPEN selected_for_payment_cursor;
FETCH selected_for_payment_cursor
INTO l_flag;
CLOSE selected_for_payment_cursor;
END selected_for_payment_flag;
SELECT 'Y'
FROM ap_invoice_payments
WHERE invoice_id = P_invoice_id
AND nvl(discount_taken,0) <> 0;
SELECT 'Y', p.org_id
FROM ap_invoice_payments p,
ap_checks c,
ap_system_parameters SP
WHERE p.invoice_id = P_invoice_id
AND p.org_id = sp.org_id
AND nvl(p.cash_posted_flag,'N') <> 'Y'
AND p.check_id = c.check_id
AND c.void_date IS NOT NULL
AND (sp.accounting_method_option = 'Cash' OR
sp.secondary_accounting_method = 'Cash');
SELECT 'Y'
FROM ap_invoice_payments
WHERE invoice_id = P_invoice_id;
SELECT SUM(nvl(prepay_amount_remaining,amount))
FROM ap_invoice_distributions_all aid,ap_invoices_all 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.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.earliest_settlement_date IS NOT NULL
AND ai.earliest_settlement_date <= trunc(SYSDATE);
SELECT decode(AI.EARLIEST_SETTLEMENT_DATE,null,'PERMANENT','TEMPORARY')
FROM ap_invoices_all ai
WHERE ai.invoice_id = P_invoice_id;
select decode(count(distinct(packet_id)),1,max(packet_id),'')
from ap_invoice_distributions
where invoice_id = P_Invoice_Id
and packet_id is not null;
CURSOR c_select_payment_status (cv_invoice_id NUMBER ) IS
SELECT payment_status_flag
FROM ap_payment_schedules_all
WHERE invoice_id = cv_invoice_id;
OPEN c_select_payment_status ( p_invoice_id );
FETCH c_select_payment_status into temp_ps_flag;
EXIT when c_select_payment_status%NOTFOUND;
CLOSE c_select_payment_status;
select asp.accounting_method_option,
nvl(asp.secondary_accounting_method, 'None'),
asp.org_id
into l_primary_acctg_method,
l_secondary_acctg_method,
l_org_id
from ap_system_parameters_all asp
where asp.org_id = P_org_id;
select count(*)
into l_count_pmt_posted
from ap_invoice_payments aip
where aip.posted_flag = 'Y'
and aip.invoice_id = p_invoice_id;
select count(*)
into l_count_pmt_hist_posted
from ap_payment_history aph
where aph.posted_flag = 'Y'
and aph.check_id in (select check_id
from ap_invoice_payments aip
where aip.invoice_id = p_invoice_id);
select count(*)
into l_count_prepaid_posted
from ap_invoice_distributions aid
where aid.posted_flag <> 'N'
and aid.invoice_id = p_invoice_id
and aid.line_type_lookup_code = 'PREPAY';
SELECT SUM(aid1.amount * -1)
INTO l_prepay_amt_applied
FROM ap_invoice_distributions aid1, ap_invoice_distributions aid2
WHERE aid1.invoice_id = P_invoice_id
AND aid1.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 count(invoice_distribution_id)
INTO l_count_distributions
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id;
| prepayment. This has been added to do not use a new select statement in
| the expense report import program.
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
FUNCTION get_amt_applied_per_prepay (
P_invoice_id IN NUMBER,
P_prepay_id IN NUMBER)
RETURN number
IS
l_prepay_amt_applied NUMBER := 0;
SELECT SUM(aid1.amount * -1)
INTO l_prepay_amt_applied
FROM ap_invoice_distributions aid1, ap_invoice_distributions aid2
WHERE aid1.invoice_id = P_invoice_id
AND aid1.line_type_lookup_code = 'PREPAY'
AND aid1.prepay_distribution_id = aid2.invoice_distribution_id
AND aid2.invoice_id = P_prepay_id;
SELECT count(*)
INTO l_explines_count
FROM ap_expense_report_lines
WHERE report_header_id = p_expense_report_id;
SELECT 'E'
INTO l_return_type
FROM ap_expense_report_headers aerh
WHERE aerh.vouchno = p_invoice_id;
SELECT nvl( MAX(line_number),0 )
INTO l_max_inv_line_num
FROM ap_invoice_lines
WHERE invoice_id = P_invoice_id;
SELECT SUM(NVL(amount,0))
INTO line_total
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id
AND ((ail.line_type_lookup_code not in ('PREPAY','AWT') --Bug 7372061 Excluded 'AWT' amount from the total line amount.
AND ail.prepay_invoice_id IS NULL
AND ail.prepay_line_number IS NULL)
OR nvl(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT decode(x_reporting_ledger_id, null, AI.base_amount, null),
AI.invoice_amount, -- invoice amount
AI.invoice_currency_code, -- invoice_currency_code
ASP.base_currency_code -- base_currency_code
FROM ap_invoices AI, ap_system_parameters ASP
WHERE AI.invoice_id = X_invoice_id
AND ASP.org_id = AI.org_id;
SELECT SUM(base_amount), SUM(amount)
INTO l_sum_base_amt, l_sum_amt
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = X_INVOICE_ID
AND line_type_lookup_code <> 'AWT'
AND (invoice_includes_prepay_flag = 'Y' OR
line_type_lookup_code <> 'PREPAY');
X_Rounded_Line_Numbers.delete;
X_Rounded_Line_Numbers.delete;
SELECT ail1.line_number
BULK COLLECT INTO l_Rounded_Line_Numbers
FROM ap_invoice_lines ail1
WHERE ail1.invoice_id = X_invoice_id
AND ail1.amount <> 0
AND LINE_TYPE_LOOKUP_CODE not in ('TAX', 'AWT') -- bug 9582952 --bug16090813
AND (EXISTS
(SELECT 'UNPOSTED'
FROM ap_invoice_distributions D1
WHERE D1.invoice_id = ail1.invoice_id
AND D1.invoice_line_number = ail1.line_number
AND NVL(D1.posted_flag, 'N') = 'N') OR
(NOT EXISTS
(SELECT 'X'
FROM ap_invoice_distributions D2
WHERE D2.invoice_id = ail1.invoice_id
AND D2.invoice_line_number = ail1.line_number)))
ORDER BY ail1.base_amount desc;
X_Rounded_Line_Numbers.delete;
SELECT 1
INTO l_active_count
FROM ap_invoice_lines AIL
WHERE ( NVL( AIL.discarded_flag, 'N' ) <> 'Y' AND
NVL( AIL.cancelled_flag, 'N' ) <> 'Y' )
AND AIL.corrected_inv_id = p_invoice_id
AND ROWNUM = 1 ;
SELECT 1
INTO l_quick_credit_count
FROM ap_invoices AI
WHERE AI.credited_invoice_id = P_invoice_id
AND NVL(AI.quick_credit, 'N') = 'Y'
AND AI.cancelled_date is null
AND ROWNUM = 1 ;
SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = P_Invoice_Id
AND EXISTS
(SELECT il.invoice_id
FROM ap_invoice_lines_all il
WHERE il.invoice_id = i.invoice_id
AND NVL(il.discarded_flag, 'N') <> 'Y'
AND NVL(il.cancelled_flag, 'N') <> 'Y'
AND il.match_type IN ('PRICE_CORRECTION',
'QTY_CORRECTION'));
SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = P_Invoice_Id
AND EXISTS
(SELECT il.invoice_id
FROM ap_invoice_lines_all il
WHERE il.invoice_id = i.invoice_id
AND il.line_type_lookup_code = 'PREPAY'
AND NVL(il.discarded_flag, 'N') <> 'Y'
AND NVL(il.cancelled_flag, 'N') <> 'Y');
SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = P_Invoice_Id
AND EXISTS
(SELECT il.invoice_id
FROM ap_invoice_lines_all il
WHERE il.invoice_id = i.invoice_id
AND il.line_type_lookup_code = 'AWT'
AND NVL(il.discarded_flag, 'N') <> 'Y'
AND NVL(il.cancelled_flag, 'N') <> 'Y');
SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = P_Invoice_Id
AND EXISTS
(SELECT ail.invoice_id
FROM ap_invoice_lines_all ail,
po_line_locations_all pll
WHERE ail.invoice_id = i.invoice_id
AND ail.po_line_location_id = pll.line_location_id
AND ail.org_id = pll.org_id
AND pll.closed_code = 'FINALLY CLOSED');
select nvl(max(distribution_line_number),0)
into l_max_dist_line_num
from ap_invoice_distributions
where invoice_id = P_invoice_id
and invoice_line_number = P_invoice_line_number;
SELECT invoice_num
INTO l_invoice_num
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
SELECT SUM(NVL(amount,0))
INTO retained_total
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.line_type_lookup_code = 'RETAINAGE'
AND EXISTS
(SELECT 'X' FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = p_invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE');
select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
into item_total
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and line_type_lookup_code IN ('ITEM','RETAINAGE RELEASE');
select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
into freight_total
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and org_id = p_org_id
and line_type_lookup_code = 'FREIGHT';
select sum(nvl(amount,0)) - sum(nvl(included_tax_amount,0))
into misc_total
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and org_id = p_org_id
and line_type_lookup_code = 'MISCELLANEOUS';
select sum(nvl(amount,0))
into prepay_app_total
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and org_id = p_org_id
and line_type_lookup_code = 'PREPAY';
SELECT ai.cancelled_date,
ai.approval_ready_flag,
ai.invoice_type_lookup_code,
ai.source,
ai.wfapproval_status /* Added for Bug 11924642 */
INTO l_cancelled_date,
l_approval_ready_flag,
l_invoice_type_lookup_code,
l_invoice_source,
l_wfapproval_status
FROM ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND ai.source = 'ISP';
SELECT count(*)
INTO l_negotiate_lines_count
FROM ap_apinv_approvers
WHERE invoice_id = p_invoice_id
AND approval_status = 'NEGOTIATE'
AND rownum =1;
SELECT count(*)
INTO l_negotiate_lines_count
FROM ap_holds_all
WHERE invoice_id = p_invoice_id
AND wf_status = 'NEGOTIATE'
AND rownum =1;
select t.bank_account_name,
t.bank_account_id,
t.bank_account_number
from (
SELECT b.bank_account_name,
b.ext_bank_account_id bank_account_id,
b.bank_account_number,
rank() over (partition by ibyu.instrument_id, ibyu.instrument_type order by ibyu.instrument_payment_use_id) not_dup,
ibypayee.supplier_site_id,/*bug 8345877*/
ibypayee.party_site_id,/*bug 8345877*/
ibypayee.org_id,/*bug 8345877*/
ibyu.order_of_preference /*bug 8345877*/
FROM IBY_PMT_INSTR_USES_ALL ibyu,
IBY_EXT_BANK_ACCOUNTS_V b,
IBY_EXTERNAL_PAYEES_ALL ibypayee
WHERE ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
AND (b.currency_code = p_invoice_currency_code OR b.currency_code is null
OR NVL(b.foreign_payment_use_flag,'N')='Y')
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
AND ibyu.payment_flow = 'DISBURSEMENTS'
AND ibypayee.payment_function = 'PAYABLES_DISB'
AND ibypayee.payee_party_id = p_party_id
/*bug 9462285. Modified end_date condition */
AND trunc(sysdate) between trunc(NVL(ibyu.start_date,sysdate-1)) AND trunc(decode(ibyu.end_date, null, sysdate+1, ibyu.end_date-1))
AND trunc(sysdate) between trunc(NVL(b.start_date,sysdate-1)) AND trunc(decode(b.end_date, null, sysdate+1, b.end_date-1))
AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_party_site_id)
AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
AND (ibypayee.org_id is null OR
(ibypayee.org_id = p_org_id AND ibypayee.org_type = 'OPERATING_UNIT'))) t
where t.not_dup=1 /*bug 8345877*/
order by t.supplier_site_id,
t.party_site_id,
t.org_id,
t.order_of_preference/*bug 8345877*/;
SELECT NVL(ph.clm_document_number, ph.segment1)
FROM po_headers PH
WHERE ph.segment1=p_po_number
AND ph.org_id=p_org_id;
| FUNCTION - Update_Invoice_Description
|
| DESCRIPTION
| API to update the Description of CREDIT MEMO
|
|
|
| KNOWN ISSUES:
|
| NOTES:
|
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 8-Jul-2011 anubagar Created the API
*==========================================================================*/
FUNCTION Update_Invoice_Description ( p_invoice_id IN NUMBER,
p_description IN VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
l_current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(200) := 'Update_Invoice_Description';
l_current_calling_sequence := 'AP_UTILITIES_PKG.Update_Description <- '||p_calling_sequence;
SELECT Invoice_type_lookup_code
INTO l_invoice_type
FROM ap_invoices_all
WHERE invoice_id=p_invoice_id;
UPDATE ap_invoices_all
SET description = p_description
WHERE invoice_id = p_invoice_id;
l_debug_info := 'Update Invoice Description Successfully';
END Update_Invoice_Description;
SELECT
(CASE
WHEN INVOICE_TYPE_LOOKUP_CODE is null
and invoice_amount >=0 then
'STANDARD'
WHEN INVOICE_TYPE_LOOKUP_CODE is null
and invoice_amount <0 then
'CREDIT'
ELSE INVOICE_TYPE_LOOKUP_CODE
END)
INTO L_INVOICE_TYPE_LOOKUP_CODE
FROM AP_INVOICES_INTERFACE
/* Modified for bug#15906783 */
WHERE INVOICE_ID in (SELECT INVOICE_ID
FROM ap_invoice_lines_interface
WHERE invoice_line_id = P_ID);
SELECT INVOICE_TYPE_LOOKUP_CODE
INTO L_INVOICE_TYPE_LOOKUP_CODE
FROM AP_INVOICES
WHERE INVOICE_ID = P_ID;