[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- select all the out variables from the view ap_invoices_v
debug_info := 'select out variables from ap_invoices_v';
SELECT ai.invoice_num,
ai.invoice_amount,
ai.invoice_date,
ai.vendor_id,
ai.vendor_site_id,
HP.PARTY_NAME VENDOR_NAME,
PV.SEGMENT1 VENDOR_NUMBER,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
ai.invoice_currency_code,
ai.invoice_type_lookup_code,
ai.description,
DECODE(PV.TYPE_1099, '','', DECODE(ASP.COMBINED_FILING_FLAG, 'N', '',
DECODE(ASP.INCOME_TAX_REGION_FLAG, 'Y', DECODE(PVS.country, 'US',PVS.state, NULL), ASP.INCOME_TAX_REGION))) INCOME_TAX_REGION,
-- ai.ussgl_transaction_code, - bug 4277744
ai.awt_group_id,
ai.batch_id,
ai.gl_date,
ai.terms_id,
AT.NAME TERMS_NAME ,
AP_INVOICES_PKG.GET_PERIOD_NAME( AI.GL_DATE, NULL, AI.ORG_ID) PERIOD_NAME,
FC.MINIMUM_ACCOUNTABLE_UNIT,
FC.PRECISION PRECISION,
ai.release_amount_net_of_tax
INTO
P_invoice_num,
P_invoice_amount,
P_invoice_date,
P_vendor_id,
P_vendor_site_id,
P_vendor_name,
P_vendor_number,
P_vendor_site_code,
P_inv_curr_code,
P_inv_type_lookup_code,
P_inv_description,
P_income_tax_region,
-- P_ussgl_transaction_code, - Bug 4277744
P_awt_group_id,
P_batch_id,
P_gl_date,
P_payment_terms_id,
P_payment_terms_name,
P_period_name,
P_minimum_accountable_unit,
P_precision,
P_release_amount_net_of_tax
FROM
ap_invoices_all ai,
FND_CURRENCIES FC,
HZ_PARTIES HP,
ap_suppliers pv,
ap_supplier_sites_all pvs,
AP_TERMS AT,
AP_SYSTEM_PARAMETERS ASP,
FND_TERRITORIES_TL FND
WHERE ai.invoice_id = P_invoice_id
AND AI.TERMS_ID = AT.TERM_ID (+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND AI.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE (+)
AND AI.ORG_ID = ASP.ORG_ID
AND FND.territory_code(+) = AI.taxation_country
AND (AI.TAXATION_COUNTRY IS NULL OR FND.LANGUAGE = USERENV('LANG'))
AND AI.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = PV.PARTY_ID (+);
SELECT vendor_type_lookup_code ,
vat_registration_num
INTO P_vendor_type_lookup_code,
P_vat_registration_num
FROM po_vendors
WHERE vendor_id = P_vendor_id;
debug_info := 'select the po_number';
SELECT segment1
INTO P_po_number
FROM po_headers
WHERE po_header_id = P_quick_po_id;
-- select item_structure id for Item category for the product
-- Purchasing
-- Get the structure id for Purchasing
SELECT mdsv.structure_id
INTO P_item_structure_id
FROM mtl_default_sets_view mdsv
WHERE mdsv.functional_area_id = 2;
SELECT count(*)
INTO p_num_po_dists
FROM po_distributions
WHERE line_location_id = P_line_location_id;
SELECT po_distribution_id
INTO p_po_distribution_id
FROM po_distributions
WHERE line_location_id = P_line_location_id;
SELECT nvl(sum(nvl(quantity_invoiced,0)),0)-nvl(sum(nvl(price_correct_qty,0)),0) --6509492
INTO p_billed_qty
FROM ap_invoice_distributions AID
WHERE NVL(AID.rcv_transaction_id,P_rcv_transaction_id)
= P_rcv_transaction_id
AND AID.po_distribution_id = P_po_distribution_id
--BUGFIX:5641346
AND line_type_lookup_code NOT IN ('RETAINAGE','PREPAY');
Procedure Insert_Adjusted_Receipt_IDs (
p_parent_rcv_txn_id IN NUMBER,
p_adjusted_rcv_txn_id IN NUMBER,
p_adjusted_date IN DATE,
p_user_id IN NUMBER,
p_login_id IN NUMBER) IS
Begin
-- find out if the receipt is matched -check quantity billed on the
-- receipt
-- Insert data into the table AP_MATCHED_RECT_ADJ_ALL
-- set all who column dates to sysdate and conc program related
-- columns to null
-- just entering the stub package right now.
null;
End Insert_Adjusted_receipt_Ids;
Select Nvl(Sum(ail.quantity_invoiced), 0)
Into l_existing_corr_qty
From ap_invoice_lines_all ail
,ap_invoices_all ai
Where ail.corrected_inv_id = p_invoice_id
And ail.corrected_line_number = p_line_number
And ail.match_type = 'QTY_CORRECTION'
And ail.invoice_id = ai.invoice_id
And ai.cancelled_date is null;
Select Nvl(Sum(ail.unit_price * ail.quantity_invoiced), 0)
Into l_correction_amount
From ap_invoice_lines_all ail
,ap_invoices_all ai
Where ail.corrected_inv_id = p_invoice_id
And ail.corrected_line_number = p_line_number
And ail.match_type = 'PRICE_CORRECTION'
And ai.invoice_id = ail.invoice_id
And ai.cancelled_date is null;
Select (NVL(ail.unit_price, 0) * NVL(ail.quantity_invoiced, 0)),
NVL( ail.quantity_invoiced,0)
Into l_original_amount, l_original_qty_invoiced
From ap_invoice_lines_all ail
Where ail.invoice_id = p_invoice_id
And ail.line_number = p_line_number;
Select Nvl(Sum(aid.corrected_quantity), 0)
Into l_existing_corr_qty
From ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
Where aid.corrected_invoice_dist_id = p_invoice_dist_id
And ail.line_number = aid.invoice_line_number
And ail.match_type = 'QTY_CORRECTION'
And aid.invoice_id=ail.invoice_id; --bug 5015014
Select Nvl(Sum(ail.amount), 0)
Into l_existing_corr_amt
From ap_invoice_lines_all ail
,ap_invoices_all ai
Where ail.corrected_inv_id = p_invoice_id
And ail.corrected_line_number = p_line_number
And ail.match_type In ( 'QTY_CORRECTION', 'PRICE_CORRECTION',
'AMOUNT_CORRECTION')
And ai.invoice_id = ail.invoice_id
And ai.cancelled_date is null;
SELECT count(*)
INTO p_num_line_dists
FROM ap_invoice_distributions_all
WHERE invoice_id = P_invoice_id
AND invoice_line_number = P_invoice_line_number
AND line_type_lookup_code in ('ITEM', 'ACCRUAL')
AND prepay_distribution_id is NULL;
SELECT invoice_distribution_id
INTO p_inv_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = P_invoice_id
AND invoice_line_number = P_invoice_line_number
AND line_type_lookup_code in ('ITEM', 'ACCRUAL')
AND prepay_distribution_id is NULL;
SELECT *
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_invoice_line_number;
SELECT code_combination_id
INTO l_dist_tab(l_index).dist_ccid
FROM po_distributions_ap_v
WHERE po_distribution_id = l_invoice_line_rec.po_distribution_id;
SELECT invoice_distribution_id, dist_code_combination_id
INTO l_corr_inv_dist_id, l_dist_ccid
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_line_rec.corrected_inv_id
AND invoice_line_number = l_invoice_line_rec.corrected_line_number
AND po_distribution_id = l_invoice_line_rec.po_distribution_id;
SELECT sum(amount)
INTO p_billed_amt
FROM ap_invoice_distributions AID
WHERE NVL(AID.rcv_transaction_id,P_rcv_transaction_id)
= P_rcv_transaction_id
AND AID.po_distribution_id = P_po_distribution_id
--Bugfix:5641346
AND AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY');
Select amount
Into l_dist_amt
From ap_invoice_distributions_all
Where invoice_distribution_id = p_invoice_dist_id;
Select Nvl(Sum(aid.amount), 0)
Into l_existing_corr_amt
From ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
Where aid.corrected_invoice_dist_id = p_invoice_dist_id
And ail.line_number = aid.invoice_line_number
And ail.match_type = 'LINE_CORRECTION'
And aid.invoice_id=ail.invoice_id; --bug5015014
Select Nvl(Sum(aarl.amount), 0)
Into l_total_amount
From ap_allocation_rule_lines aarl
Where invoice_id = p_invoice_id
And to_invoice_line_number = p_line_number;
Select amount
Into l_line_amt
From ap_invoice_lines_all
Where invoice_id = p_invoice_id
And line_number = p_line_number;
Select Nvl(Sum(ail.amount), 0)
Into l_existing_corr_amt
From ap_invoice_lines_all ail
Where ail.corrected_inv_id = p_invoice_id
And ail.corrected_line_number = p_line_number
And ail.match_type = 'LINE_CORRECTION';
Select nvl(quantity_invoiced,0)
Into l_line_qty
From ap_invoice_lines_all
Where invoice_id = p_invoice_id
And line_number = p_line_number;
Select Nvl(Sum(ail.quantity_invoiced), 0)
Into l_existing_corr_qty
From ap_invoice_lines_all ail
Where ail.corrected_inv_id = p_invoice_id
And ail.corrected_line_number = p_line_number
And ail.match_type = 'LINE_CORRECTION';
Select Nvl(Sum(amount), 0)
Into l_invoice_amt
From ap_invoice_lines_all
Where invoice_id = p_invoice_id
And match_type = 'NOT_MATCHED';
Select Nvl(Sum(ail.amount), 0)
Into l_existing_corr_amt
From ap_invoice_lines_all ail
Where ail.corrected_inv_id = p_invoice_id
And ail.match_type = 'LINE_CORRECTION';
SELECT aid.po_distribution_id,
decode(pll.matching_basis,'QUANTITY',sum(nvl(aid.quantity_invoiced,0))),
decode(pll.matching_basis,'AMOUNT',sum(nvl(aid.amount,0))),
pll.matching_basis
FROM ap_invoice_distributions_v aid,
ap_invoices ai,
po_line_locations pll
WHERE pll.line_location_id = P_Po_Line_Location_Id
AND pll.shipment_type <> 'PREPAYMENT'
AND aid.line_location_id =pll.line_location_id
AND aid.invoice_id = ai.invoice_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
GROUP BY aid.po_distribution_id, pll.matching_basis;
select aid.po_distribution_id,
decode(pll.matching_basis,'QUANTITY',sum(nvl(aid.quantity_invoiced,0))),
decode(pll.matching_basis,'AMOUNT',sum(nvl(aid.amount,0))),
pll.matching_basis
from ap_invoice_distributions aid,
po_distributions_all pd,
po_line_locations pll
where pll.line_location_id = p_po_line_location_id
and pll.shipment_type <> 'PREPAYMENT'
and aid.po_distribution_id = pd.po_distribution_id
and pd.line_location_id = pll.line_location_id
and aid.line_type_lookup_code = 'PREPAY'
group by aid.po_distribution_id,pll.matching_basis;
l_debug_info := 'Update Po_Distributions';
UPDATE po_distributions pod
SET quantity_financed = l_dist_tab(i).total_dist_qty_invoiced,
quantity_recouped = l_dist_tab(i).total_dist_qty_applied,
quantity_billed = nvl(quantity_billed,0) - (l_dist_tab(i).total_dist_qty_invoiced -
l_dist_tab(i).total_dist_qty_applied)
WHERE pod.po_distribution_id = l_dist_tab(i).po_distribution_id
AND pod.quantity_financed IS NULL;
UPDATE po_distributions pod
SET amount_financed = l_total_dist_amt_invoiced,
amount_recouped = l_total_dist_amt_applied,
amount_billed = nvl(amount_billed,0) - (l_dist_tab(i).total_dist_amt_invoiced -
l_dist_tab(i).total_dist_amt_applied)
WHERE pod.po_distribution_id = l_dist_tab(i).po_distribution_id
AND pod.amount_financed IS NULL;
l_debug_info := 'Update Po_Shipments with the cumulative totals';
UPDATE po_line_locations
SET quantity_financed = l_total_shipment_qty_invoiced,
quantity_recouped = l_total_shipment_qty_applied,
quantity_billed = nvl(quantity_billed,0) - (l_total_shipment_qty_invoiced - l_total_shipment_qty_applied)
WHERE line_location_id = p_po_line_location_id
AND quantity_financed IS NULL;
UPDATE po_line_locations
SET amount_financed = l_total_shipment_amt_invoiced,
amount_recouped = l_total_shipment_amt_applied,
amount_billed = nvl(amount_billed,0) - (l_total_shipment_amt_invoiced - l_total_shipment_amt_applied)
WHERE line_location_id = p_po_line_location_id
AND amount_financed IS NULL;
P_Last_Update_Login IN NUMBER,
P_Error_Message OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
CURSOR Prepayment_Invoice_Lines IS
/* select matched prepayments */
SELECT ai.invoice_id prepayment_invoice_id,
ai.invoice_num prepayment_invoice_num,
ail.line_number prepayment_line_number,
decode(pll.payment_type,'ADVANCE',2,1) prepayment_order_number,
AP_Prepay_Utils_Pkg.Get_Ln_Prep_Amt_Remain_Recoup(
ai.invoice_id,ail.line_number) prepay_amount_remaining,
max(aip.accounting_date) prepayment_payment_date
FROM ap_invoices ai,
ap_invoice_lines ail,
po_line_locations pll,
ap_invoice_payments aip
WHERE ai.invoice_id = ail.invoice_id
AND ail.po_line_id = p_po_line_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ail.line_type_lookup_code = 'ITEM'
AND pll.po_line_id = p_po_line_id
AND pll.payment_type IS NOT NULL
AND AP_PREPAY_UTILS_PKG.Get_Ln_Prep_Amt_Remain_Recoup(ai.invoice_id,ail.line_number) > 0
AND aip.invoice_id = ai.invoice_id
AND pll.line_location_id = ail.po_line_location_id
--bugfix:4880825 removed '+1' from the NVL condition
AND nvl(ai.earliest_settlement_date,SYSDATE) <= SYSDATE
AND NVL(ail.discarded_flag,'N') <> 'Y'
--Do we need to check this, since by the time cursor is fetched and the one-by-one
--prepayment is applied, it could be the case that the prepayment_invoice which was
--locked when selecting, could be actually unlocked by the time actual application happens.
--So, just checking if the line if locked or not just before application should be sufficient?
-- AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
GROUP BY aip.invoice_id, ai.invoice_id,ai.invoice_num,
ail.line_number,pll.payment_type,aip.accounting_date
ORDER BY prepayment_payment_date,prepayment_order_number;
SELECT invoice_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type,
payment_currency_code,
payment_cross_rate_date,
payment_cross_rate_type
FROM AP_Invoices
WHERE invoice_id = CV_Invoice_ID;
SELECT prepay_distribution_id,
amount
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_number
AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND prepay_distribution_id IS NOT NULL;
P_LAST_UPDATE_LOGIN => p_last_update_login,
P_CALLING_SEQUENCE => l_curr_calling_sequence,
P_CALLING_MODE => 'RECOUPMENT',
P_ERROR_MESSAGE => l_error_message);
,p_line_number_to_delete => NULL
,p_Interface_Invoice_Id => NULL
,p_all_error_messages => 'N'
,p_error_code => p_error_message
,p_calling_sequence => l_curr_calling_sequence)) THEN
RAISE tax_exception;
UPDATE ap_invoice_distributions
SET prepay_amount_remaining = prepay_amount_remaining + l_recoup_amount_list(i)
WHERE invoice_distribution_id = l_prepay_dist_id_list(i);
l_debug_info := 'Update payment schedules with the tax on recouped distributions';
SELECT sum(aid.amount)
INTO l_recouped_tax_amount
FROM ap_invoice_distributions aid,
ap_invoice_distributions aid1,
ap_invoice_lines ail
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_invoice_line_number
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.line_type_lookup_code in ('REC_TAX','NONREC_TAX','TIPV','TRV','TERV')
AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.line_type_lookup_code = 'PREPAY';
l_debug_info := 'Update Payment Schedules l_recouped_tax_amt_in_pay_curr is '||l_recouped_tax_amt_in_pay_curr;
l_success := AP_PREPAY_PKG.Update_Payment_Schedule(
p_invoice_id,
l_prepayment_invoice_id,
l_prepayment_line_number,
(-1)*l_recouped_tax_amt_in_pay_curr,
'APPLICATION',
l_inv_pay_curr_code,
p_user_id,
p_last_update_login,
l_curr_calling_sequence,
'RECOUPMENT',
l_error_message);
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
SELECT sum(aid.amount)
INTO l_recouped_amount
FROM ap_invoice_distributions aid,
ap_invoice_lines ail
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_invoice_line_number
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
AND (aid.line_type_lookup_code = 'PREPAY'
OR (aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX') and
aid.prepay_distribution_id IS NOT NULL)
OR (aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
and aid.related_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND aid1.prepay_distribution_id IS NOT NULL)
)
);
SELECT sum(aid.amount)
INTO l_recouped_amount
FROM ap_invoice_distributions aid,
ap_invoice_lines ail
WHERE aid.invoice_id = p_invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
AND (aid.line_type_lookup_code = 'PREPAY'
OR (aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX') and
aid.prepay_distribution_id IS NOT NULL)
OR (aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
and aid.related_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND aid1.prepay_distribution_id IS NOT NULL)
)
);
SELECT sum(aid.amount)
INTO l_recouped_amount
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_invoice_line_number
AND aid.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id IN (SELECT aid1.invoice_distribution_id
FROM ap_invoice_distributions aid1
WHERE aid1.invoice_id = p_prepay_invoice_id
AND aid1.invoice_line_number = p_prepay_line_number);
SELECT sum(aid.amount)
INTO l_recouped_tax_amount
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_invoice_line_number
AND
((aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
and aid.prepay_distribution_id IN (SELECT aid1.invoice_distribution_id
FROM ap_invoice_distributions aid1
WHERE aid1.invoice_id = p_prepay_invoice_id
AND aid1.invoice_line_number = p_prepay_line_number)
) OR
(aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
and aid.related_id IN (SELECT invoice_distribution_id
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.invoice_line_number = aid.invoice_line_number
AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND aid2.prepay_distribution_id IN
(SELECT aid4.invoice_distribution_id
FROM ap_invoice_distributions aid4
WHERE aid4.invoice_id = p_prepay_invoice_id
AND aid4.invoice_line_number = p_prepay_line_number)
)
)
);
SELECT rcv.transaction_id,
pll.matching_basis,
pll.line_location_id
FROM rcv_transactions rcv,
rcv_shipment_lines rsl,
po_line_locations pll
WHERE rcv.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_line_id = p_rcv_shipment_line_id
AND pll.line_location_id = rcv.po_line_location_id
AND rcv.transaction_type IN ('RECEIVE','MATCH');
SELECT po_distribution_id
FROM
rcv_transactions
WHERE
transaction_type = 'DELIVER'
START WITH transaction_id = p_rcv_transaction_id
CONNECT BY parent_transaction_id = PRIOR transaction_id
AND PRIOR transaction_type <> 'DELIVER';
SELECT ail.rcv_shipment_line_id,
ail.quantity_invoiced,
ail.amount,
ail.unit_price,
ai.invoice_currency_code,
ai.invoice_type_lookup_code,
ail.unit_meas_lookup_code,
ail.retained_amount,
ail.match_type,
ail.po_distribution_id
INTO l_rcv_shipment_line_id,
l_total_match_quantity,
l_total_match_amount,
l_match_unit_price,
l_invoice_currency_code,
l_invoice_type_lookup_code,
l_unit_meas_lookup_code,
l_retained_amount,
l_match_type,
l_po_distribution_id
FROM ap_invoice_lines_all ail,
ap_invoices ai
WHERE ai.invoice_id = p_invoice_id
AND ail.invoice_id = ai.invoice_id
AND ail.line_number = p_invoice_line_number;
SELECT rcv.transaction_id,
pll.matching_basis,
pll.line_location_id
FROM rcv_transactions rcv,
rcv_shipment_lines rsl,
po_line_locations pll
WHERE rcv.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_line_id = p_rcv_shipment_line_id
AND pll.line_location_id = rcv.po_line_location_id
AND rcv.transaction_type IN ('RECEIVE','MATCH');