[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(encumbered_flag,'N')
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number;
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 = p_invoice_id
AND ai.org_id = fsp.org_id;
| 'S' - Selected
| 'P' - Partial
| 'N' - Unposted
| ---------------------------------------------------------------------
| -- Declare cursor to establish the invoice-level posting flag
| --
| -- The first two selects simply look at the posting flags (cash and/or
| -- accrual) for the distributions. The rest 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
| 28-MAY-04 yicao SLA Obsolescence: Remove some accounting
| related options
*============================================================================*/
FUNCTION get_posting_status(
p_invoice_id IN NUMBER,
p_line_number IN NUMBER )
RETURN VARCHAR2
IS
invoice_line_posting_flag VARCHAR2(1);
SELECT cash_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND l_cash_basis_flag = 'Y'
UNION
SELECT accrual_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND l_cash_basis_flag <> 'Y'
UNION
SELECT 'P'
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
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 = p_invoice_id
AND invoice_line_number = p_line_number
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y')));
| 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 = p_invoice_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = sob.set_of_books_id;
SELECT nvl(match_status_flag, 'N')
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number;
SELECT NVL(fsp.purch_encumbrance_flag,'N'), ai.org_id
INTO encumbrance_flag, l_org_id
FROM ap_invoices_all ai,
financials_system_params_all fsp
WHERE ai.invoice_id = p_invoice_id
AND ai.org_id = fsp.org_id;
SELECT count(*)
INTO invoice_holds
FROM ap_holds_all
WHERE invoice_id = p_invoice_id
AND release_lookup_code is NULL;
SELECT count(*)
INTO dist_var_hold
FROM ap_holds_all
WHERE invoice_id = p_invoice_id
AND hold_lookup_code = 'DIST VARIANCE'
AND release_lookup_code is NULL;
SELECT count(*)
INTO l_cancelled_count
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND NVL(cancelled_flag, 'N' ) = 'Y';
SELECT count(*)
INTO match_flag_cnt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_line_number
AND aid.match_status_flag IS NOT NULL
AND rownum < 2;
SELECT accounting_date
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_line_rec.invoice_id
AND AID.invoice_line_number = p_line_rec.line_number
AND NVL(AID.reversal_flag, 'N') <> 'Y';
SELECT org_id
INTO l_org_id
FROM ap_invoices_all
WHERE invoice_id = p_line_rec.invoice_id;
SELECT count(*)
INTO l_po_dist_count
FROM po_distributions_all POD,
ap_invoice_distributions AID,
ap_invoices ai,
po_line_locations PLL,
po_lines PL
WHERE POD.po_distribution_id = AID.po_distribution_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = PL.po_line_id
AND AID.invoice_id = ai.invoice_id
AND AID.invoice_id = p_line_rec.invoice_id
AND POD.org_id = AID.org_id
AND AID.invoice_line_number = p_line_rec.line_number
AND NVL(AID.reversal_flag,'N')<>'Y'
AND aid.rcv_transaction_id is null --Bug5000472
HAVING (
(DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',
SUM(NVL(POD.quantity_financed, 0)),
SUM(NVL(POD.quantity_billed, 0)))
-
SUM(round(decode(AID.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0,
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
nvl( AID.quantity_invoiced, 0 ) +
nvl( AID.corrected_quantity,0 )
) *
po_uom_s.po_uom_convert(AID.matched_uom_lookup_code,
nvl(PLL.unit_meas_lookup_code,
PL.unit_meas_lookup_code),
PL.item_id), 15))
< 0)
OR (DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',
SUM(NVL(POD.amount_financed, 0)),
SUM(NVL(POD.amount_billed, 0))) -
SUM(NVL(AID.amount, 0)) < 0 ))
GROUP BY ai.invoice_type_lookup_code,AID.po_distribution_id;
SELECT count(*)
INTO l_rcv_dist_count
FROM rcv_transactions RT,
ap_invoice_distributions_all AID
WHERE RT.transaction_id = AID.rcv_transaction_id
AND AID.invoice_id = p_line_rec.invoice_id
AND AID.invoice_line_number = p_line_rec.line_number
AND AID.rcv_transaction_id is not null
AND NVL(AID.reversal_flag,'N')<>'Y'
AND (NVL(rt.quantity_billed,0) <
(SELECT SUM(decode( AID1.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0,
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
nvl( AID1.corrected_quantity,0 ) +
nvl( AID1.quantity_invoiced,0 )
)
)
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.rcv_transaction_id=aid.rcv_transaction_id
)
OR
NVL(rt.amount_billed,0) < (
SELECT SUM(NVL(AID2.amount,0))
FROM ap_invoice_distributions_all aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.invoice_line_number = aid.invoice_line_number
AND aid2.rcv_transaction_id=aid.rcv_transaction_id
)
);
SELECT count(*)
INTO l_final_close_count
FROM ap_invoice_lines AIL,
po_line_locations PLL
WHERE AIL.invoice_id = p_line_rec.invoice_id
AND AIL.line_number = p_line_rec.line_number
AND AIL.po_line_location_id = PLL.line_location_id
AND PLL.closed_code = 'FINALLY CLOSED';
SELECT NVL(purch_encumbrance_flag,'N')
INTO l_enc_enabled
FROM financials_system_params_all FSP,
ap_invoices_all AI
WHERE AI.invoice_id = p_line_rec.invoice_id
AND FSP.org_id = AI.org_id;
select 'Y'
into l_po_not_approved
from po_headers POH
where POH.po_header_id = p_line_rec.po_header_id
and POH.approved_flag <> 'Y'; --bug6653070
SELECT count(*)
INTO l_quick_credit_count
FROM ap_invoices AI
WHERE AI.invoice_id = p_line_rec.invoice_id
AND NVL(AI.quick_credit, 'N') = 'Y';
SELECT count(*)
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_line_rec.invoice_id
AND AIL.corrected_line_number = p_line_rec.line_number;
SELECT 1
INTO l_quick_credit_ref_count
FROM ap_invoices AI
WHERE AI.credited_invoice_id = p_line_rec.invoice_id
AND NVL(AI.quick_credit, 'N') = 'Y'
AND AI.cancelled_date is null
AND Rownum = 1;
SELECT count(*)
INTO l_invalid_acct_count
FROM ap_invoice_distributions D
WHERE D.invoice_id = p_line_rec.invoice_id
AND D.invoice_line_number = p_line_rec.line_number
AND D.posted_flag IN ('N', 'P')
AND ((EXISTS (select 'x'
from gl_code_combinations C
where D.dist_code_combination_id = C.code_combination_id (+)
and (C.code_combination_id is null
or C.detail_posting_allowed_flag = 'N'
or C.start_date_active > D.accounting_date
or C.end_date_active < D.accounting_date
or C.template_id is not null
or C.enabled_flag <> 'Y'
or C.summary_flag <> 'N'
)))
OR (D.dist_code_combination_id = -1));
SELECT count(*)
INTO l_reference_count
FROM ap_invoice_distributions AID
WHERE NVL(AID.cancellation_flag, 'N') <> 'Y'
AND NVL(AID.reversal_flag, 'N') <> 'Y'
AND AID.invoice_id = p_line_rec.invoice_id
AND AID.invoice_line_number <> p_line_rec.line_number
AND AID.charge_applicable_to_dist_id IS NOT NULL
AND AID.charge_applicable_to_dist_id IN
( SELECT AID2.invoice_distribution_id
FROM ap_invoice_distributions AID2
WHERE AID2.invoice_id = p_line_rec.invoice_id
AND AID2.invoice_line_number = p_line_rec.line_number
AND NVL(AID2.cancellation_flag, 'N') <> 'Y'
AND NVL(AID2.reversal_flag, 'N') <> 'Y' );
SELECT count(*)
INTO l_pending_count
FROM ap_allocation_rules AR,
ap_allocation_rule_lines ARL
WHERE AR.invoice_id = p_line_rec.invoice_id
AND AR.invoice_id = ARL.invoice_id
AND AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number
AND ARL.to_invoice_line_number = p_line_rec.line_number
AND AR.status = 'PENDING';
SELECT count(*)
INTO l_count
FROM ap_invoice_lines AIL,
ap_invoices AI
WHERE AIL.invoice_id = P_line_rec.invoice_id
AND AIL.line_number = P_line_rec.line_number
AND AIL.line_type_lookup_code = 'AWT'
AND NOT EXISTS ( SELECT invoice_distribution_id
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = AIL.invoice_id
AND aid.invoice_line_number = AIL.line_number
AND awt_flag = 'M' )
AND AI.invoice_id = AIL.invoice_id
AND AI.payment_status_flag in ('P', 'Y');
SELECT count(*)
INTO l_count
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = P_line_rec.invoice_id
AND AIL.line_number = P_line_rec.line_number
AND (ail.retained_amount IS NOT NULL AND
ail.retained_amount_remaining IS NOT NULL AND
abs(ail.retained_amount) <> abs(ail.retained_amount_remaining));
SELECT count(*)
INTO l_count
FROM ap_invoices_all ai
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_type_lookup_code = 'PREPAYMENT';
debug_info := 'Select from ap_allocation_rules';
Select count(*)
Into dummy
From ap_allocation_rules AR,
ap_allocation_rule_lines ARL
Where AR.invoice_id = p_Invoice_Id
And AR.invoice_id = ARL.invoice_id
And AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number
And ARL.to_invoice_line_number = p_line_number;
debug_info := 'Select from ap_invoics_all';
Select 1
Into dummy
From ap_invoices_all 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;
debug_info := 'Select from ap_invoice_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all
Where invoice_id = p_Invoice_Id
And invoice_line_number = p_Line_Number
And assets_addition_flag = 'Y';
debug_info := 'Select from ap_invoice_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all
Where invoice_id = p_Invoice_Id
And invoice_line_number = p_Line_Number
And accounting_event_id Is Not Null;
debug_info := 'Select from ap_invoice_lines_all';
Select count(*)
Into dummy
From ap_invoice_lines_all AIL
Where NVL(AIL.discarded_flag, 'N' ) <> 'Y'
And NVL( AIL.cancelled_flag, 'N' ) <> 'Y'
And AIL.corrected_inv_id = p_Invoice_Id
And AIL.corrected_line_number = p_Line_Number;
debug_info := 'Select from ap_invoic_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all AID
Where NVL(AID.cancellation_flag, 'N') <> 'Y'
And NVL(AID.reversal_flag, 'N') <> 'Y'
And AID.invoice_id = p_invoice_id
And AID.invoice_line_number <> p_line_number
And AID.charge_applicable_to_dist_id IS NOT NULL
And AID.charge_applicable_to_dist_id In
(Select AID2.invoice_distribution_id
From ap_invoice_distributions_all AID2
Where AID2.invoice_id = p_Invoice_Id
And AID2.invoice_line_number = p_Line_Number
And NVL(AID2.cancellation_flag, 'N') <> 'Y'
And NVL(AID2.reversal_flag, 'N') <> 'Y' );
debug_info := 'Select from ap_allocatin_rules';
Select count(*)
Into dummy
From ap_allocation_rules AR,
ap_allocation_rule_lines ARL
Where AR.invoice_id = p_Invoice_Id
And AR.invoice_id = ARL.invoice_id (+)
And AR.chrg_invoice_line_number = ARL.chrg_invoice_line_number (+)
And ARL.to_invoice_line_number (+) = p_line_number
And AR.status = 'PENDING';
debug_info := 'Select from ap_invoic_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all
Where invoice_id = p_Invoice_Id
And invoice_line_number = p_Line_Number
And pa_addition_flag In ('T', 'Y', 'Z') ;
| Public FUNCTION Can_Line_Be_Deleted
|
| Check if the particular invoice line can be deleted
|
| PROGRAM FLOW
|
| return TRUE - if line can be deleted
| return FALSE - otherwise and return error code.
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 03/10/13 bghose Created
*============================================================================*/
FUNCTION Can_Line_Be_Deleted (p_line_rec IN ap_invoice_lines%ROWTYPE,
p_error_code OUT NOCOPY Varchar2,
p_Calling_Sequence Varchar2) Return Boolean Is
current_calling_sequence Varchar2(2000);
'AP_INVOICE_LINES_UTILITY_PKG.Can_Line_Be_Deleted <-'||
p_Calling_Sequence;
p_error_code := 'AP_INV_LINE_DELETE_VALIDATED';
p_error_code := 'AP_INV_LINE_DELETE_CORR';
p_error_code := 'AP_INV_LINE_DELETE_ENCUMBERED';
p_error_code := 'AP_INV_LINE_DELETE_ACCOUNTED';
p_error_code := 'AP_INV_LINE_DELETE_PA';
End Can_Line_Be_Deleted;
Select decode(count(distinct(packet_id)),1,max(packet_id),'')
From ap_invoice_distributions
Where invoice_id = p_Invoice_Id
And invoice_line_number = p_Line_Number
And packet_id is not null;
SELECT 'Dist Total <> Invoice Line Amount'
FROM ap_invoice_lines AIL, ap_invoice_distributions D
WHERE AIL.invoice_id = D.invoice_id
AND AIL.line_number = p_line_number
AND AIL.invoice_id = p_invoice_id
AND AIL.line_number = D.invoice_line_number
AND (D.line_type_lookup_code <> 'RETAINAGE'
OR (AIL.line_type_lookup_code = 'RETAINAGE RELEASE'
and D.line_type_lookup_code = 'RETAINAGE'))
AND (AIL.line_type_lookup_code <> 'ITEM'
or (AIL.line_type_lookup_code = 'ITEM'
and (D.prepay_distribution_id IS NULL
or (D.prepay_distribution_id IS NOT NULL
and D.line_type_lookup_code NOT IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')))))
GROUP BY AIL.invoice_id, AIL.line_number, AIL.amount
HAVING AIL.amount <> nvl(SUM(nvl(D.amount,0)),0);
SELECT count(*)
FROM po_distributions_all POD,
ap_invoice_distributions AID
WHERE POD.po_distribution_id = AID.po_distribution_id
AND AID.invoice_id = P_Invoice_Id
AND POD.org_id = AID.org_id
AND AID.invoice_line_number = P_Line_Number
AND NVL(AID.reversal_flag,'N')<>'Y'
AND ( NVL(POD.quantity_billed, 0) -
decode( AID.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0, /* Ampunt Based Matching */
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
nvl( AID.corrected_quantity,0 ) +
nvl( AID.quantity_invoiced,0 ) ) < 0
OR
NVL(POD.amount_billed, 0) - NVL(AID.amount, 0) < 0 );
SELECT COUNT(*)
INTO l_count
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND generate_dists <> 'D'
AND ROWNUM = 1;
debug_info := 'Select from ap_allocatin_rules';
Select count(*)
Into dummy
From ap_allocation_rules AR
Where AR.invoice_id = p_Invoice_Id
And AR.chrg_invoice_line_number = p_line_number
And AR.status = 'PENDING';
SELECT 'Y'
INTO is_correction
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND corrected_inv_id IS NOT NULL
AND corrected_line_number IS NOT NULL;
SELECT count(*)
INTO l_count
FROM ap_invoice_lines_all
WHERE corrected_inv_id = p_invoice_id
AND corrected_line_number = p_line_number
AND line_type_lookup_code IN ('RETROITEM')
AND line_source = 'PO PRICE ADJUSTMENT'
AND match_type = 'RETRO PRICE ADJUSTMENT';
SELECT 'Y'
INTO is_po_price_adjustment
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND line_type_lookup_code = 'RETROITEM'
AND line_source = 'PO PRICE ADJUSTMENT'
AND match_type = 'RETRO PRICE ADJUSTMENT';
SELECT 'Y'
INTO is_prepayment
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND line_type_lookup_code = 'PREPAY';
SELECT currency_code
INTO l_currency_code
FROM po_headers_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id)
AND rownum < 2;
* Objective update ap_payment_schedules.remaining_amount for manual entry
* withholding lines
* This procedire has been moved from payment schedules library since it did
* not consider the
* ap lines model
* This PROCEDURE is added for Bug 6917289
* =============================================================================================*/
PROCEDURE Manual_Withhold_Tax(p_invoice_id IN number
,p_manual_withhold_amount IN number
) IS
l_inv_amt_remaining ap_payment_schedules.amount_remaining%TYPE := 0;
SELECT nvl(payment_cross_rate,0), payment_currency_code
INTO l_payment_cross_rate, l_payment_currency_code
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
INTO l_inv_amt_remaining, l_gross_amount
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id;
update ap_payment_schedules
set amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
(amount_remaining * (p_manual_withhold_amount/l_inv_amt_remaining)
* l_payment_cross_rate), l_payment_currency_code))
where invoice_id = p_invoice_id;
update ap_payment_schedules
set amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
(gross_amount * (p_manual_withhold_amount/l_gross_amount)
* l_payment_cross_rate), l_payment_currency_code)),
payment_status_flag = DECODE(payment_status_flag,'Y','P',payment_status_flag)
where invoice_id = p_invoice_id;
update ap_invoices
set payment_status_flag = DECODE(payment_status_flag,'Y','P',payment_status_flag)
where invoice_id = p_invoice_id ;
SELECT awt_flag
INTO l_awt_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND rownum = 1;