[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Insert_Distributions (x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_retainage_dist_tab OUT NOCOPY retDistType);
Procedure Update_Payment_Schedules (x_invoice_id IN ap_invoices.invoice_id%type);
Procedure Update_PO_Shipment_Dists (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
x_retained_amount IN ap_invoice_lines_all.retained_amount%type,
x_retainage_dist_tab IN retDistType);
Select ai.invoice_amount invoice_amount,
ai.exchange_rate exchange_rate,
ai.invoice_currency_code invoice_currency_code,
ai.payment_currency_code payment_currency_code,
ai.payment_cross_rate payment_cross_rate,
ai.amount_applicable_to_discount amount_applicable_to_discount,
ai.invoice_type_lookup_code invoice_type_lookup_code,
ai.net_of_retainage_flag net_of_retainage_flag,
ail.amount line_amount,
ail.retained_amount retained_amount,
ail.po_line_location_id po_line_location_id,
decode(fc.minimum_accountable_unit,
null, round((ail.retained_amount * ai.exchange_rate),
fc.precision),
round((ail.retained_amount * ai.exchange_rate)
/ fc.minimum_accountable_unit) * fc.minimum_accountable_unit)
base_retained_amount,
fc.precision precision,
fc.minimum_accountable_unit minimum_accountable_unit,
fsp.retainage_code_combination_id retainage_code_combination_id
From ap_invoices ai,
ap_invoice_lines ail,
ap_system_parameters asp,
financials_system_parameters fsp,
fnd_currencies fc
Where ail.invoice_id = c_invoice_id
And ail.line_number = c_invoice_line_number
And ai.invoice_id = ail.invoice_id
And ai.org_id = asp.org_id
And asp.org_id = fsp.org_id
And ai.invoice_currency_code = fc.currency_code (+);
l_debug_info := 'Step 2: Insert Retainage Distributions';
Insert_Distributions (x_invoice_id => x_invoice_id,
x_invoice_line_number => x_invoice_line_number,
x_retainage_dist_tab => x_retainage_dist_tab);
l_debug_info := 'Step 3: Update Payment Schedules';
Update_Payment_Schedules (x_invoice_id => x_invoice_id);
l_debug_info := 'Step 4: Update PO Shipment/Distributions';
Update_PO_Shipment_Dists (x_line_location_id => g_invoice_info.po_line_location_id,
x_retained_amount => g_invoice_info.retained_amount,
x_retainage_dist_tab => x_retainage_dist_tab);
x_retainage_dist_tab.delete;
PROCEDURE Insert_Distributions (x_invoice_id IN NUMBER,
x_invoice_line_number IN NUMBER,
x_retainage_dist_tab OUT NOCOPY retDistType) AS
CURSOR c_invoice_distributions (c_invoice_id IN ap_invoices.invoice_id%type,
c_invoice_line_number IN ap_invoice_lines.line_number%type,
c_max_dist_line_number IN ap_invoice_lines.line_number%type,
c_retainage_rate IN number) IS
SELECT
aid.batch_id,
aid.invoice_id,
aid.invoice_line_number,
aid.invoice_distribution_id invoice_distribution_id,
ap_invoice_distributions_s.nextval retainage_distribution_id,
aid.distribution_line_number + c_max_dist_line_number retainage_dist_line_number,
'RETAINAGE' line_type_lookup_code,
aid.description,
aid.dist_match_type,
aid.distribution_class,
aid.org_id,
aid.accounting_date,
aid.period_name,
'N' posted_flag,
aid.set_of_books_id,
decode(g_invoice_info.minimum_accountable_unit,
null, round(aid.amount * c_retainage_rate,
g_invoice_info.precision),
round((aid.amount * c_retainage_rate)
/ g_invoice_info.minimum_accountable_unit)
* g_invoice_info.minimum_accountable_unit) amount,
decode(g_invoice_info.minimum_accountable_unit,
null, round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate),
g_invoice_info.precision),
round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate)
/ g_invoice_info.minimum_accountable_unit)
* g_invoice_info.minimum_accountable_unit) base_amount,
aid.match_status_flag,
aid.ussgl_transaction_code,
aid.ussgl_trx_code_context,
aid.po_distribution_id,
aid.rcv_transaction_id,
aid.unit_price,
aid.matched_uom_lookup_code,
aid.quantity_invoiced,
aid.final_match_flag,
aid.related_id,
aid.assets_addition_flag,
aid.project_id,
aid.task_id,
aid.expenditure_type,
aid.expenditure_item_date,
aid.expenditure_organization_id,
aid.pa_quantity,
'R' pa_addition_flag, -- Bug 5388196
aid.pa_cc_ar_invoice_id,
aid.pa_cc_ar_invoice_line_num,
aid.pa_cc_processed_code,
aid.award_id,
aid.gms_burdenable_raw_cost,
aid.awt_flag,
aid.awt_group_id,
aid.awt_tax_rate_id,
aid.awt_gross_amount,
aid.awt_invoice_id,
aid.awt_origin_group_id,
aid.awt_invoice_payment_id,
aid.awt_withheld_amt,
aid.inventory_transfer_status,
aid.reference_1,
aid.reference_2,
aid.receipt_verified_flag,
aid.receipt_required_flag,
aid.receipt_missing_flag,
aid.justification,
aid.expense_group,
aid.start_expense_date,
aid.end_expense_date,
aid.receipt_currency_code,
aid.receipt_conversion_rate,
aid.receipt_currency_amount,
aid.attribute_category,
aid.attribute1,
aid.attribute2,
aid.attribute3,
aid.attribute4,
aid.attribute5,
aid.attribute6,
aid.attribute7,
aid.attribute8,
aid.attribute9,
aid.attribute10,
aid.attribute11,
aid.attribute12,
aid.attribute13,
aid.attribute14,
aid.attribute15,
aid.global_attribute_category,
aid.global_attribute1,
aid.global_attribute2,
aid.global_attribute3,
aid.global_attribute4,
aid.global_attribute5,
aid.global_attribute6,
aid.global_attribute7,
aid.global_attribute8,
aid.global_attribute9,
aid.global_attribute10,
aid.global_attribute11,
aid.global_attribute12,
aid.global_attribute13,
aid.global_attribute14,
aid.global_attribute15,
aid.global_attribute16,
aid.global_attribute17,
aid.global_attribute18,
aid.global_attribute19,
aid.global_attribute20,
aid.intended_use
FROM ap_invoice_lines ail,
ap_invoice_distributions aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.invoice_id = c_invoice_id
AND ail.line_number = c_invoice_line_number
AND (
aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
or
(ail.match_type = 'PRICE_CORRECTION' and
aid.line_type_lookup_code = 'IPV')
);
l_api_name Constant Varchar2(100) := 'Insert_Distributions';
Print (l_api_name,'Insert_Distributions (+)');
x_retainage_dist_tab(i).last_updated_by := g_user_id;
x_retainage_dist_tab(i).last_update_date := sysdate;
x_retainage_dist_tab(i).last_update_login := g_login_id;
l_debug_info := 'Step 4: Insert Retainage Distributions';
INSERT INTO ap_invoice_distributions VALUES x_retainage_dist_tab(j);
l_debug_info := 'Step 5: Update related_retainage_dist_id on parent distributions';
UPDATE ap_invoice_distributions_all
SET related_retainage_dist_id = inv_dist_tab(k)
WHERE invoice_distribution_id = inv_dist_tab(k);
inv_dist_tab.delete;
item_dist_tab.delete;
Print (l_api_name, 'Insert_Distributions (-)');
END Insert_Distributions;
PROCEDURE Update_Payment_Schedules (x_invoice_id IN ap_invoices.invoice_id%type) AS
CURSOR c_payment_schedules (c_invoice_id IN ap_invoices.invoice_id%type) IS
SELECT *
FROM ap_payment_schedules_all
WHERE invoice_id = c_invoice_id
FOR UPDATE OF amount_remaining;
:= 'Update_Payment_Schedules';
Print (l_api_name, 'Update_Payment_Schedules (+)');
l_debug_info := 'Step 3: Bulk Update Payment Schedules';
UPDATE ap_payment_schedules_all
SET ROW = pay_sched_upd_tab(i)
WHERE invoice_id = x_invoice_id
AND payment_num = pay_num_tab(i);
l_debug_info := 'Step 4: Update amount_applicable_to_discount';
UPDATE ap_invoices_all
SET amount_applicable_to_discount = (amount_applicable_to_discount + l_retained_amount)
WHERE invoice_id = x_invoice_id;
pay_num_tab.delete;
pay_sched_upd_tab.delete;
Print (l_api_name, 'Update_Payment_Schedules(-)');
END Update_Payment_Schedules;
Procedure Update_PO_Shipment_Dists
(x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
x_retained_amount IN ap_invoice_lines_all.retained_amount%type,
x_retainage_dist_tab IN retDistType) AS
l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
l_api_name := 'Update_PO_Shipment_Dists';
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
P_Dist_Changes_Rec => l_po_ap_dist_rec,
X_Return_Status => l_return_status,
X_Msg_Data => l_msg_data);
END Update_PO_Shipment_Dists;