[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ADSL.distribution_set_line_number,
ADSL.description,
ADSL.dist_code_combination_id,
GL.account_type,
ADSL.percent_distribution,
ADSL.project_id,
ADSL.task_id,
ADSL.expenditure_type,
ADSL.expenditure_organization_id,
ADSL.project_Accounting_context,
ADSL.award_id,
ADSL.attribute_category,
ADSL.attribute1,
ADSL.attribute2,
ADSL.attribute3,
ADSL.attribute4,
ADSL.attribute5,
ADSL.attribute6,
ADSL.attribute7,
ADSL.attribute8,
ADSL.attribute9,
ADSL.attribute10,
ADSL.attribute11,
ADSL.attribute12,
ADSL.attribute13,
ADSL.attribute14,
ADSL.attribute15,
ADSL.type_1099
FROM AP_Distribution_Set_Lines ADSL,
GL_Code_combinations GL
WHERE distribution_set_id = X_invoice_lines_rec.Distribution_Set_id
AND GL.code_combination_id = ADSL.dist_code_combination_id
ORDER BY distribution_set_line_number;
SELECT total_percent_distribution
INTO l_dist_set_percent_number
FROM ap_distribution_sets
WHERE distribution_set_id = X_invoice_lines_rec.distribution_set_id;
SELECT nvl(PVS.prepay_code_combination_id,
SP.prepay_code_combination_id),
AI.invoice_type_lookup_code
INTO l_prepay_dist_code_ccid,
l_invoice_type_lookup_code
FROM ap_invoices AI,
po_vendor_sites PVS,
ap_system_parameters SP
WHERE AI.invoice_id = X_invoice_lines_rec.invoice_id
AND PVS.vendor_site_id = AI.vendor_site_id;
SELECT employee_id
INTO l_employee_id
FROM ap_suppliers /* Bug 4718054 */
WHERE DECODE(SIGN(TO_DATE(TO_CHAR(START_DATE_ACTIVE,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
1, 'N', DECODE(SIGN(TO_DATE(TO_CHAR(END_DATE_ACTIVE ,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
-1, 'N', 0, 'N', 'Y')) = 'Y'
AND enabled_flag = 'Y'
AND vendor_id = X_vendor_id;
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = X_invoice_lines_rec.default_dist_ccid;
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = l_dset_line_ccid;
FUNCTION Insert_From_Dist_Set(
X_invoice_id IN NUMBER,
X_line_number IN NUMBER DEFAULT NULL,
X_GL_Date IN DATE,
X_Period_Name IN VARCHAR2,
X_Skeleton_Allowed IN VARCHAR2 DEFAULT 'N',
X_Generate_Dists IN VARCHAR2 DEFAULT 'Y',
X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
X_Error_Code OUT NOCOPY VARCHAR2,
X_Debug_Info OUT NOCOPY VARCHAR2,
X_Debug_Context OUT NOCOPY VARCHAR2,
X_Msg_Application OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_calling_sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR line_rec(X_line_number NUMBER) IS
SELECT invoice_id,
line_number,
line_type_lookup_code,
requester_id,
description,
line_source,
org_id,
line_group_number,
inventory_item_id,
item_description,
serial_number,
manufacturer,
model_number,
warranty_number,
generate_dists,
match_type,
distribution_set_id,
account_segment,
balancing_segment,
cost_center_segment,
overlay_dist_code_concat,
default_dist_ccid,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
def_acctg_start_date,
def_acctg_end_date,
def_acctg_number_of_periods,
def_acctg_period_type,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_invoiced,
unit_meas_lookup_code,
unit_price,
wfapproval_status,
discarded_flag,
original_amount,
original_base_amount,
original_rounding_amt,
cancelled_flag,
income_tax_region,
type_1099,
stat_amount,
prepay_invoice_id,
prepay_line_number,
invoice_includes_prepay_flag,
corrected_inv_id,
corrected_line_number,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
pa_quantity,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num ,
pa_cc_processed_code,
award_id,
awt_group_id,
reference_1,
reference_2,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
--ETAX: Invwkb
included_tax_amount,
primary_intended_use,
--Bugfix:4673607
application_id,
product_table,
reference_key1,
reference_key2,
reference_key3,
reference_key4,
reference_key5,
--bugfix:4674194
ship_to_location_id,
--bugfix:7022001
pay_awt_group_id
FROM AP_INVOICE_LINES
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
SELECT *
FROM AP_INVOICES
WHERE invoice_id = x_invoice_id;
current_calling_sequence := 'AP_INVOICE_LINES_PKG.insert_from_dist_set<-'
||X_calling_sequence;
SELECT total_percent_distribution,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
inactive_date
INTO l_dist_set_percent_number,
l_dist_set_description,
l_dist_set_attribute_category,
l_dist_set_attribute1,
l_dist_set_attribute2,
l_dist_set_attribute3,
l_dist_set_attribute4,
l_dist_set_attribute5,
l_dist_set_attribute6,
l_dist_set_attribute7,
l_dist_set_attribute8,
l_dist_set_attribute9,
l_dist_set_attribute10,
l_dist_set_attribute11,
l_dist_set_attribute12,
l_dist_set_attribute13,
l_dist_set_attribute14,
l_dist_set_attribute15,
l_inactive_date
FROM ap_distribution_sets
WHERE distribution_set_id = l_invoice_line_rec.distribution_set_id;
debug_info := 'Select header, vendor information and amount to distribute';
SELECT AI.batch_id,
AI.vendor_id,
AI.vendor_site_id,
AI.invoice_date,
AI.exchange_rate,
AI.exchange_date,
AI.exchange_rate_type,
AI.invoice_currency_code,
AI.set_of_books_id
INTO l_batch_id,
l_vendor_id,
l_vendor_site_id,
l_invoice_date,
l_exchange_rate,
l_exchange_date,
l_exchange_rate_type,
l_invoice_currency_code,
l_set_of_books_id
FROM ap_invoices AI
WHERE invoice_id = X_invoice_id;
SELECT gsob.chart_of_accounts_id,
ap.base_currency_code
INTO l_chart_of_accounts_id,
l_base_currency_code
FROM ap_system_parameters ap, gl_sets_of_books gsob
WHERE ap.set_of_books_id = gsob.set_of_books_id
AND ap.set_of_books_id = l_set_of_books_id
AND ap.org_id = l_invoice_line_rec.org_id;
debug_info := 'Calling AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set';
IF ( NOT (AP_INVOICE_DISTRIBUTIONS_PKG.Insert_From_Dist_Set(
l_batch_id,
X_invoice_id,
X_line_number,
y_dist_tab,
X_Generate_Permanent,
-- l_error_code,
debug_info,
debug_context,
current_calling_sequence))) then
IF (l_error_code IS NOT NULL) THEN
X_error_code := l_error_code;
END insert_from_dist_set;
SELECT nvl(max(distribution_line_number),0)
INTO l_max_dist_line_num
FROM ap_invoice_distributions_all -- Bug 7195488 Moac synonym replaced
WHERE invoice_id = X_invoice_id
AND invoice_line_number = X_line_number;
SELECT decode(x_reporting_ledger_id, null, AIL.base_amount, null),
AIL.amount, -- line_amount
AI.invoice_currency_code, -- invoice_currency_code
ASP.base_currency_code -- base_currency_code
FROM ap_invoices AI, ap_system_parameters ASP, ap_invoice_lines AIL
WHERE AI.invoice_id = X_invoice_id
AND AIL.invoice_id = AI.invoice_id
AND AIL.line_number = X_line_number
AND ASP.org_id = AI.org_id;
SELECT SUM(base_amount)
INTO l_sum_base_amt
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = X_INVOICE_ID
AND AID.invoice_line_number = X_LINE_NUMBER
AND AID.line_type_lookup_code NOT IN ('RETAINAGE', 'PREPAY')
AND AID.charge_applicable_to_dist_id NOT IN
(SELECT AID1.invoice_distribution_id
FROM ap_invoice_distributions AID1
WHERE AID1.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')
AND AID1.invoice_id = X_INVOICE_ID
AND AID1.invoice_line_number = X_LINE_NUMBER);
X_ROUND_DIST_ID_LIST.delete;
SELECT invoice_distribution_id
BULK COLLECT INTO l_round_dist_id_list
FROM AP_INVOICE_DISTRIBUTIONS aid1
WHERE aid1.invoice_id = X_INVOICE_ID
AND aid1.invoice_line_number = X_LINE_NUMBER
AND nvl(aid1.posted_flag, 'N') = 'N'
AND NVL(aid1.match_status_flag, 'N') IN ('N', 'S')
AND NVL(aid1.reversal_flag, 'N') = 'N' /* Bug 4121330 */
ORDER BY aid1.base_amount desc;
x_round_dist_id_list.delete;
| P_last_updated_by
| P_last_update_login
| P_error_code - Error code indicates why it is not discardable
| P_calling_sequence - For debugging purpose
|
| PROGRAM FLOW
|
| 1. check if line is discardable
| 2. if line is discardable/cancellable and matched - reverse match
| 3. reset the encumberance flag, create account event
| 4. if there is an active distribution - reverse distribution
| 5. populate the out message and set the return value
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 03/07/03 sfeng Created
|
*============================================================================*/
Function Discard_Inv_Line(
P_line_rec IN ap_invoice_lines%ROWTYPE,
P_calling_mode IN VARCHAR2,
P_inv_cancellable IN VARCHAR2 DEFAULT NULL,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_error_code OUT NOCOPY VARCHAR2,
P_token OUT NOCOPY VARCHAR2,
P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
IS
TYPE r_global_attr_arr IS VARRAY(1000) of VARCHAR2(150);
SELECT aid.po_distribution_id,
aid.matched_uom_lookup_code,
SUM( decode( AID.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0, /* Amount Based Matching */
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
NVL( AID.corrected_quantity, 0) +
nvl( AID.quantity_invoiced,0 ) ) ) ,
SUM(NVL(AID.amount, 0)) ,
aid.line_type_lookup_code,
pll.matching_basis,
aid1.invoice_id prepay_invoice_id,
aid1.invoice_line_number prepay_line_number
FROM ap_invoice_distributions_all AID ,
po_line_locations pll,
ap_invoice_distributions_all AID1
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.invoice_line_number = p_line_rec.line_number
--Contract Payments: Added the 'PREPAY' to the clause
AND aid.line_type_lookup_code in ('ITEM','ACCRUAL', 'IPV','ERV','PREPAY','RETAINAGE')
AND pll.line_location_id = p_line_rec.po_line_location_id
AND aid1.invoice_distribution_id(+) = aid.prepay_distribution_id
GROUP BY aid1.invoice_id,aid1.invoice_line_number,
aid.line_type_lookup_code,aid.po_distribution_id,pll.matching_basis,aid.matched_uom_lookup_code;
SELECT ail.*
FROM ap_allocation_rule_lines arl
,ap_invoice_lines_all ail
WHERE arl.invoice_id = c_invoice_id
AND arl.to_invoice_line_number = c_item_line_number
AND arl.invoice_id = ail.invoice_id
AND arl.chrg_invoice_line_number = ail.line_number
AND exists
(select aid.invoice_line_number
from ap_invoice_distributions_all aid
where aid.invoice_id = ail.invoice_id
and aid.invoice_line_number = ail.line_number);
select pll.line_location_id
,aid.matched_uom_lookup_code
,sum(nvl(aid.amount,0)) amount
,sum(decode(AID.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0,
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
NVL(AID.corrected_quantity, 0) + NVL(AID.quantity_invoiced,0))) quantity
from ap_invoice_lines_all ail
,ap_invoice_distributions_all aid
,po_distributions_all pod
,po_line_locations_all pll
where ail.invoice_id = p_line_rec.invoice_id
and ail.line_number = p_line_rec.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'
and aid.po_distribution_id = pod.po_distribution_id
and pll.line_location_id = pod.line_location_id
group by pll.line_location_id, aid.matched_uom_lookup_code;
Select *
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_line_rec.invoice_id;
SELECT invoice_type_lookup_code,
payment_status_flag,
invoice_amount,
payment_currency_code,
invoice_currency_code,
payment_cross_rate_date,
payment_cross_rate_type
INTO l_invoice_type_lookup_code,
l_payment_status_flag,
l_invoice_amount,
l_payment_currency_code,
l_invoice_currency_code,
l_payment_cross_rate_date,
l_payment_cross_rate_type
FROM ap_invoices
WHERE invoice_id = p_line_rec.invoice_id;
P_Last_Updated_By => P_last_updated_by,
P_Last_Update_Login => P_last_update_login,
P_Program_Application_Id => NULL,
P_Program_Id => NULL,
P_Request_Id => NULL,
P_Awt_Success => l_awt_success,
P_Inv_Line_No => P_line_rec.line_number,
P_dist_Line_No => NULL,
P_New_Invoice_Id => NULL,
P_New_dist_Line_No => NULL);
l_debug_info := 'Update allocation rule to pending on related charge lines';
update ap_allocation_rules ar
set status = 'PENDING'
where ar.invoice_id = p_line_rec.invoice_id
and exists (select arl.chrg_invoice_line_number
from ap_allocation_rule_lines arl
where arl.invoice_id = p_line_rec.invoice_id
and arl.to_invoice_line_number = p_line_rec.line_number
and arl.chrg_invoice_line_number = ar.chrg_invoice_line_number);
update ap_invoice_lines_all ail
set generate_dists = 'Y'
where ail.invoice_id = p_line_rec.invoice_id
and exists (select arl.chrg_invoice_line_number
from ap_allocation_rule_lines arl
where arl.invoice_id = p_line_rec.invoice_id
and arl.to_invoice_line_number = p_line_rec.line_number
and arl.chrg_invoice_line_number = ail.line_number);
l_debug_info := 'Delete allocation rule lines';
delete from ap_allocation_rule_lines
where invoice_id = p_line_rec.invoice_id
and to_invoice_line_number = p_line_rec.line_number;
SELECT included_tax_amount
INTO l_included_tax_amount
FROM ap_invoice_lines
WHERE invoice_id = p_line_rec.invoice_id
AND line_number = p_line_rec.line_number;
l_debug_info := 'Update billed/financed data for po distributions';
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
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);
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_recoup_line_loc_rec,
P_Dist_Changes_Rec => l_recoup_dist_rec,
X_Return_Status => l_return_status,
X_Msg_Data => l_msg_data);
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
p_line_rec.rcv_transaction_id ,
l_sum_matched_qty *(-1),
p_line_rec.unit_meas_lookup_code,
NVL(p_line_rec.amount, 0) * (-1));
| Step 1a. Update retained_amount_remaining on the original invoice |
+--------------------------------------------------------------------*/
IF p_line_rec.line_type_lookup_code = 'RETAINAGE RELEASE' AND
p_line_rec.retained_invoice_id IS NOT NULL AND
p_line_rec.retained_line_number IS NOT NULL THEN
UPDATE ap_invoice_lines_all
SET retained_amount_remaining = nvl(retained_amount_remaining, 0) + p_line_rec.amount
WHERE invoice_id = p_line_rec.retained_invoice_id
AND line_number = p_line_rec.retained_line_number;
l_debug_info := 'Update amount_paid on the invoice if the line had recouped amount';
UPDATE ap_invoices
SET amount_paid = nvl(amount_paid,0) - abs(l_recouped_amount) ,
payment_status_flag =
AP_INVOICES_UTILITY_PKG.get_payment_status(p_line_rec.invoice_id ),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = p_line_rec.last_update_login
WHERE invoice_id = p_line_rec.invoice_id;
l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule(
p_invoice_id => p_line_rec.invoice_id,
p_prepay_invoice_id => NULL,
p_prepay_line_num => NULL,
p_apply_amount => l_recouped_amount,
p_appl_type => 'UNAPPLICATION',
p_payment_currency_code => l_payment_currency_code,
p_user_id => FND_GLOBAL.user_id,
p_last_update_login => p_line_rec.last_update_login,
p_calling_sequence => p_calling_sequence,
p_calling_mode => 'RECOUPMENT',
p_error_message => l_error_message);
UPDATE ap_invoice_lines
SET original_amount = amount
,original_base_amount = base_amount
,original_rounding_amt = rounding_amt
,amount = 0
,base_amount = 0
,rounding_amt = 0
,retained_amount = 0
,retained_amount_remaining = 0
,included_tax_amount = 0
,discarded_flag = decode( p_calling_mode, 'DISCARD', 'Y', 'UNAPPLY_PREPAY','Y',NULL )
-- Bug 6669048. The cancelled_flag will be updated in the cancel API
-- ,cancelled_flag = decode( p_calling_mode, 'CANCEL', 'Y', NULL )
,generate_dists = decode( generate_dists, 'Y',
'N', generate_dists)
WHERE invoice_id = p_line_rec.invoice_id
AND line_number = p_line_rec.line_number;
SELECT count(*)
INTO l_distribution_count
FROM ap_invoice_distributions_all
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
AND ((line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV') and
prepay_distribution_id IS NULL) OR
(prepay_distribution_id IS NOT NULL)
)
AND NVL(reversal_flag, 'N') <> 'Y';
UPDATE ap_invoice_distributions
SET encumbered_flag = 'R'
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
AND match_status_flag <> 'A'
AND encumbered_flag IN ('N','H','P');
SELECT gl_date
INTO l_open_gl_date
FROM AP_INVOICES
WHERE invoice_id = P_Line_Rec.invoice_id;
| d.Insert reversal lines |
+-----------------------------------------------------------------*/
l_debug_info := 'Insert distribution reversals for existing lines';
INSERT INTO ap_invoice_distributions_all(
invoice_id,
invoice_line_number,
dist_code_combination_id,
invoice_distribution_id,
last_update_date,
last_updated_by,
accounting_date,
period_name,
set_of_books_id,
amount,
description,
type_1099,
tax_code_id,
posted_flag,
batch_id,
quantity_invoiced,
corrected_quantity,
unit_price,
match_status_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
prepay_amount_remaining,
prepay_distribution_id,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
po_distribution_id,
base_amount,
pa_addition_flag,
encumbered_flag,
accrual_posted_flag,
cash_posted_flag,
last_update_login,
creation_date,
created_by,
stat_amount,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
reversal_flag,
parent_invoice_id,
income_tax_region,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
pa_quantity,
project_id,
task_id,
quantity_variance,
base_quantity_variance,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
reference_1,
reference_2,
other_invoice_id,
awt_invoice_id,
awt_origin_group_id,
program_application_id,
program_id,
program_update_date,
request_id,
tax_recoverable_flag,
award_id,
start_expense_date,
merchant_document_number,
merchant_name,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
merchant_reference,
parent_reversal_id,
rcv_transaction_id,
dist_match_type,
matched_uom_lookup_code,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_Group,
end_Expense_Date,
receipt_Currency_Code,
receipt_Conversion_Rate,
receipt_Currency_Amount,
daily_Amount,
web_Parameter_Id,
adjustment_Reason,
credit_Card_Trx_Id,
company_Prepaid_Invoice_Id,
org_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_invoice_dist_id,
related_id,
asset_book_type_code,
asset_category_id,
accounting_event_id,
cancellation_flag,
distribution_class,
intended_use,
--Freight and Special Charges
rcv_charge_addition_flag)
(SELECT
Invoice_Id, -- invoice_id
Invoice_Line_Number, -- invoice_line_number
Dist_Code_Combination_Id, -- dist_code_combination_id
ap_invoice_distributions_s.NEXTVAL, -- distribution_id
sysdate, -- last_update_date
p_Last_Updated_By, -- last_updated_by
/* Bug 5584997, Getting the accounting_date from line rec */
DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.accounting_date,
ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)),
-- accounting_date
DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_line_rec.period_name,
ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)),
-- period_name,
Set_Of_Books_Id, -- set_of_book_id
-1 * Amount, -- amount
Description, -- description
Type_1099, -- type_1099
Tax_Code_Id, -- tax_code_id
'N', -- posted_flag,
Batch_Id, -- batch_id
DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
-- quantity_invoiced
DECODE(corrected_quantity, NULL, '',
DECODE(dist_match_type, 'PRICE_CORRECTION',
corrected_quantity, (-1)*corrected_quantity) ),
-- corrected_quanity
DECODE(unit_price, NULL,'',
DECODE(dist_match_type, 'PRICE_CORRECTION',
(-1)*unit_price, unit_price) ),
-- unit_price,
'N', -- match_status_flag
attribute_category, -- attribute_category
attribute1, -- attribute1
attribute2, -- attribute2
attribute3, -- attribute3
attribute4, -- attribute4
attribute5, -- attribute5
NULL, -- prepay_amount_remaining
prepay_distribution_id, -- prepay_distribution_id
'U', -- assets_addition_flag
Assets_Tracking_Flag, -- assets_tracking_flag
Distribution_Line_Number + l_max_line_num , -- distribution_line_number
Line_Type_Lookup_Code, -- line_type_lookup_code
Po_Distribution_Id, -- po_distribution_id
-1 * Base_Amount, -- base_amount
DECODE(Pa_Addition_Flag, 'E', 'E', 'N'), -- pa_addition_flag
DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
'N', -- accrual_posted_flag,
'N', -- cash_posted_flag,
p_Last_Update_Login, -- last_update_login
sysdate, -- creation_date,
FND_GLOBAL.user_id, -- created_by,
-1 * Stat_Amount, -- stat_amount
attribute11, -- attribute11,
attribute12, -- attribute12,
attribute13, -- attribute13,
attribute14, -- attribute14,
attribute6, -- attribute6,
attribute7, -- attribute7,
attribute8, -- attribute8,
attribute9, -- attribute9,
attribute10, -- attribute10,
attribute15, -- attribute15,
'Y', -- reversal_flag,
Parent_Invoice_Id, -- parent_invoice_id
Income_Tax_Region, -- income_tax_region
Final_Match_Flag, -- final_match_flag
Expenditure_Item_Date, -- expenditure_item_date
Expenditure_Organization_Id, -- expenditure_orgnization_id
Expenditure_Type, -- expenditure_type
-1 * Pa_Quantity, -- pa_quantity
Project_Id, -- project_id
Task_Id, -- task_id
-1 * Quantity_Variance, -- quantity_variance
-1 * Base_Quantity_Variance, -- base quantity_variance
awt_flag, -- awt_flag
awt_group_id, -- awt_group_id,
awt_tax_rate_id, -- awt_tax_rate_id
awt_gross_amount, -- awt_gross_amount
reference_1, -- reference_1
reference_2, -- reference_2
other_invoice_id, -- other_invoice_id
awt_invoice_id, -- awt_invoice_id
awt_origin_group_id, -- awt_origin_group_id
FND_GLOBAL.prog_appl_id, -- program_application_id
FND_GLOBAL.conc_program_id, -- program_id
SYSDATE, -- program_update_date,
FND_GLOBAL.conc_request_id, -- request_id
tax_recoverable_flag, -- tax_recoverable_flag
award_id, -- award_id
start_expense_date, -- start_expense_date
merchant_document_number, -- merchant_document_number
merchant_name, -- merchant_name
merchant_tax_reg_number, -- merchant_tax_reg_number
merchant_taxpayer_id, -- merchant_taxpayer_id
country_of_supply, -- country_of_supply
merchant_reference, -- merchant_reference
invoice_distribution_id, -- Parent_Reversal_Id
rcv_transaction_id, -- rcv_transaction_id
dist_match_type, -- dist_match_type
matched_uom_lookup_code, -- matched_uom_lookup_code
global_attribute_category, -- global_attribute_category
global_attribute1, -- global_attribute1
global_attribute2, -- global_attribute2
global_attribute3, -- global_attribute3
global_attribute4, -- global_attribute4
global_attribute5, -- global_attribute5
global_attribute6, -- global_attribute6
global_attribute7, -- global_attribute7
global_attribute8, -- global_attribute8
global_attribute9, -- global_attribute9
global_attribute10, -- global_attribute10
global_attribute11, -- global_attribute11
global_attribute12, -- global_attribute12
global_attribute13, -- global_attribute13
global_attribute14, -- global_attribute14
global_attribute15, -- global_attribute15
global_attribute16, -- global_attribute16
global_attribute17, -- global_attribute17
global_attribute18, -- global_attribute18
global_attribute19, -- global_attribute19
global_attribute20, -- global_attribute20
receipt_verified_flag, -- receipt_verified_flag
receipt_required_flag, -- receipt_required_flag
receipt_missing_flag, -- receipt_missing_flag
justification, -- justification
expense_Group, -- expense_Group
end_Expense_Date, -- end_Expense_Date
receipt_Currency_Code, -- receipt_Currency_Code
receipt_Conversion_Rate, -- receipt_Conversion_Rate
receipt_Currency_Amount, -- receipt_Currency_Amount
daily_Amount, -- daily_Amount
web_Parameter_Id, -- web_Parameter_Id
adjustment_Reason, -- adjustment_Reason
credit_Card_Trx_Id, -- credit_Card_Trx_Id
company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
org_id, -- MOAC project org_id
-1* rounding_amt, -- rounding_amt
charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
corrected_invoice_dist_id, -- corrected_invoice_dist_id
DECODE( related_id, NULL, NULL,
invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL,
--bugfix:4921399
NULL ), -- related_id
asset_book_type_code, -- asset_book_type_code
asset_category_id, -- asset_category_id
NULL, -- accounting_event_id
decode(p_calling_mode, 'CANCEL', 'Y', NULL), -- cancellation_flag
'PERMANENT',
intended_use, -- intended_use
'N' -- rcv_charge_addition_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
AND line_type_lookup_code NOT IN
('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (line_type_lookup_code <> 'AWT' OR
(line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
AND dist_code_combination_id
IN (SELECT dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
GROUP BY dist_code_combination_id,
po_distribution_id,
line_type_lookup_code,
prepay_distribution_id,
assets_tracking_flag,
type_1099,
project_id,
task_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
pa_addition_flag,
awt_group_id,
rcv_transaction_id) -- Bug 4159731
AND nvl(po_distribution_id,-99) IN
(SELECT
NVL(po_distribution_id, -99)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
GROUP BY dist_code_combination_id,
po_distribution_id,
line_type_lookup_code,
prepay_distribution_id,
assets_tracking_flag,
type_1099,
project_id,
task_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
pa_addition_flag,
awt_group_id,
rcv_transaction_id, -- Bug 4159731
tax_code_id) -- Bug 5191117
) ;
UPDATE ap_invoice_distributions aid
SET aid.related_id =
(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.parent_reversal_id =
(SELECT related_id
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.invoice_line_number = aid.invoice_line_number
AND aid2.invoice_distribution_id = aid.parent_reversal_id)
)
WHERE aid.related_id IS NULL
AND aid.parent_reversal_id IS NOT NULL
AND aid.invoice_id = p_line_rec.invoice_id
AND aid.invoice_line_number = p_line_rec.line_number
AND aid.reversal_flag = 'Y';
SELECT invoice_distribution_id,prepay_distribution_id
BULK COLLECT INTO l_key_value_list,l_key_value_list3
FROM ap_invoice_distributions
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number
AND line_type_lookup_code = 'PREPAY'
AND nvl(reversal_flag,'N') = 'Y'
AND parent_reversal_id IS NOT NULL;
l_debug_info := 'Update global context code';
p_last_update_login => p_last_update_login,
p_calling_sequence => p_calling_sequence );
UPDATE ap_invoice_distributions
SET reversal_flag = 'Y'
WHERE invoice_id = p_line_rec.invoice_id
AND invoice_line_number = p_line_rec.line_number;
SELECT aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N')
INTO l_tax_line_number,l_prepay_included
FROM ap_invoice_distributions aid, --tax dists
ap_invoice_lines ail1, --item line
ap_invoice_distributions aid1 --item distributions
WHERE ail1.invoice_id = aid1.invoice_id
AND ail1.invoice_id = p_line_rec.invoice_id
AND ail1.line_number = p_line_rec.line_number
AND aid.invoice_id = aid1.invoice_id
AND ail1.line_number = aid1.invoice_line_number
-- bug 7376110
-- The below condition added to handle prepayment with inclusive tax.
AND aid.invoice_line_number <> aid1.invoice_line_number
AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
GROUP BY aid.invoice_line_number,nvl(ail1.invoice_includes_prepay_flag,'N');
l_dummy := AP_PREPAY_PKG.Update_Prepayment(
l_prepay_dist_info,
NULL,
NULL,
p_line_rec.invoice_id,
l_tax_line_number,
'UNAPPLICATION',
NULL,
l_curr_calling_sequence,
l_error_code);
-- The query is modified to select the total amount of latest reversed tax distributions.
SELECT sum(aid1.amount)
INTO l_unapplied_tax_amount
FROM ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = p_line_rec.invoice_id
AND aid1.invoice_id = aid2.invoice_id
AND aid1.invoice_line_number = l_tax_line_number
AND aid2.invoice_line_number = p_line_rec.line_number
AND NVL(aid1.reversal_flag,'N') = 'Y'
AND NVL(aid2.reversal_flag,'N') = 'Y'
AND aid1.parent_reversal_id IS NOT NULL
AND aid2.parent_reversal_id IS NOT NULL
AND aid1.charge_applicable_to_dist_id = aid2.invoice_distribution_id;
l_debug_info := 'Update the payment schedule with the unapplied exclusive prepay tax amount';
l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule (
p_line_rec.invoice_id,
NULL,
NULL,
l_unapplied_tax_amt_pay_curr,
'UNAPPLICATION',
l_payment_currency_code,
FND_GLOBAL.user_id,
p_last_update_login,
p_calling_sequence,
NULL,
l_error_message);
l_dummy := AP_PREPAY_PKG.Update_Prepayment(
l_prepay_dist_info,
NULL,
NULL,
p_line_rec.invoice_id,
p_line_rec.line_number,
'UNAPPLICATION',
NULL,
l_curr_calling_sequence,
l_error_code);
||' P_last_updated_by = ' || P_last_updated_by
||' P_last_update_login = ' || P_last_update_login
||' P_calling_mode = ' || p_calling_mode);
l_debug_info := 'Insert reverse charge distributions';
INSERT INTO ap_invoice_distributions_all(
invoice_id,
invoice_line_number,
dist_code_combination_id,
invoice_distribution_id,
last_update_date,
last_updated_by,
accounting_date,
period_name,
set_of_books_id,
amount,
description,
type_1099,
tax_code_id,
posted_flag,
batch_id,
quantity_invoiced,
corrected_quantity,
unit_price,
match_status_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
prepay_amount_remaining,
prepay_distribution_id,
assets_addition_flag,
assets_tracking_flag,
distribution_line_number,
line_type_lookup_code,
po_distribution_id,
base_amount,
pa_addition_flag,
encumbered_flag,
accrual_posted_flag,
cash_posted_flag,
last_update_login,
creation_date,
created_by,
stat_amount,
attribute11,
attribute12,
attribute13,
attribute14,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute15,
reversal_flag,
parent_invoice_id,
income_tax_region,
final_match_flag,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
pa_quantity,
project_id,
task_id,
quantity_variance,
base_quantity_variance,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
reference_1,
reference_2,
other_invoice_id,
awt_invoice_id,
awt_origin_group_id,
program_application_id,
program_id,
program_update_date,
request_id,
tax_recoverable_flag,
award_id,
start_expense_date,
merchant_document_number,
merchant_name,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
merchant_reference,
parent_reversal_id,
rcv_transaction_id,
dist_match_type,
matched_uom_lookup_code,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_Group,
end_Expense_Date,
receipt_Currency_Code,
receipt_Conversion_Rate,
receipt_Currency_Amount,
daily_Amount,
web_Parameter_Id,
adjustment_Reason,
credit_Card_Trx_Id,
company_Prepaid_Invoice_Id,
org_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_invoice_dist_id,
related_id,
asset_book_type_code,
asset_category_id,
accounting_event_id,
cancellation_flag,
distribution_class,
intended_use,
--Freight and Special Charges
rcv_charge_addition_flag)
(SELECT
Invoice_Id, -- invoice_id
Invoice_Line_Number, -- invoice_line_number
Dist_Code_Combination_Id, -- dist_code_combination_id
ap_invoice_distributions_s.NEXTVAL, -- distribution_id
sysdate, -- last_update_date
fnd_global.user_id, -- last_updated_by
/* Bug 5584997, Getting the accounting_date from line rec */
DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.accounting_date,
ap_utilities_pkg.get_reversal_gl_date(p_inv_line_rec.accounting_date, org_id)),
-- accounting_date
DECODE(P_calling_mode,'UNAPPLY_PREPAY',p_inv_line_rec.period_name,
ap_utilities_pkg.get_reversal_period(p_inv_line_rec.accounting_date, org_id)),
-- period_name,
Set_Of_Books_Id, -- set_of_book_id
-1 * Amount, -- amount
Description, -- description
Type_1099, -- type_1099
Tax_Code_Id, -- tax_code_id
'N', -- posted_flag,
Batch_Id, -- batch_id
DECODE(quantity_invoiced, NULL, '', -1 * quantity_invoiced),
-- quantity_invoiced
DECODE(corrected_quantity, NULL, '',
DECODE(dist_match_type, 'PRICE_CORRECTION',
corrected_quantity, (-1)*corrected_quantity) ),
-- corrected_quanity
DECODE(unit_price, NULL,'',
DECODE(dist_match_type, 'PRICE_CORRECTION',
(-1)*unit_price, unit_price) ),
-- unit_price,
'N', -- match_status_flag
attribute_category, -- attribute_category
attribute1, -- attribute1
attribute2, -- attribute2
attribute3, -- attribute3
attribute4, -- attribute4
attribute5, -- attribute5
NULL, -- prepay_amount_remaining
prepay_distribution_id, -- prepay_distribution_id
'U', -- assets_addition_flag
Assets_Tracking_Flag, -- assets_tracking_flag
Distribution_Line_Number + l_max_line_num , -- distribution_line_number
Line_Type_Lookup_Code, -- line_type_lookup_code
Po_Distribution_Id, -- po_distribution_id
-1 * Base_Amount, -- base_amount
DECODE(Pa_Addition_Flag, 'E', 'E', 'N'), -- pa_addition_flag
DECODE( encumbered_flag, 'R', 'R', 'N'), -- encumbered_flag,
'N', -- accrual_posted_flag,
'N', -- cash_posted_flag,
fnd_global.login_id, -- last_update_login
sysdate, -- creation_date,
FND_GLOBAL.user_id, -- created_by,
-1 * Stat_Amount, -- stat_amount
attribute11, -- attribute11,
attribute12, -- attribute12,
attribute13, -- attribute13,
attribute14, -- attribute14,
attribute6, -- attribute6,
attribute7, -- attribute7,
attribute8, -- attribute8,
attribute9, -- attribute9,
attribute10, -- attribute10,
attribute15, -- attribute15,
'Y', -- reversal_flag,
Parent_Invoice_Id, -- parent_invoice_id
Income_Tax_Region, -- income_tax_region
Final_Match_Flag, -- final_match_flag
Expenditure_Item_Date, -- expenditure_item_date
Expenditure_Organization_Id, -- expenditure_orgnization_id
Expenditure_Type, -- expenditure_type
-1 * Pa_Quantity, -- pa_quantity
Project_Id, -- project_id
Task_Id, -- task_id
-1 * Quantity_Variance, -- quantity_variance
-1 * Base_Quantity_Variance, -- base quantity_variance
awt_flag, -- awt_flag
awt_group_id, -- awt_group_id,
awt_tax_rate_id, -- awt_tax_rate_id
awt_gross_amount, -- awt_gross_amount
reference_1, -- reference_1
reference_2, -- reference_2
other_invoice_id, -- other_invoice_id
awt_invoice_id, -- awt_invoice_id
awt_origin_group_id, -- awt_origin_group_id
FND_GLOBAL.prog_appl_id, -- program_application_id
FND_GLOBAL.conc_program_id, -- program_id
SYSDATE, -- program_update_date,
FND_GLOBAL.conc_request_id, -- request_id
tax_recoverable_flag, -- tax_recoverable_flag
award_id, -- award_id
start_expense_date, -- start_expense_date
merchant_document_number, -- merchant_document_number
merchant_name, -- merchant_name
merchant_tax_reg_number, -- merchant_tax_reg_number
merchant_taxpayer_id, -- merchant_taxpayer_id
country_of_supply, -- country_of_supply
merchant_reference, -- merchant_reference
invoice_distribution_id, -- Parent_Reversal_Id
rcv_transaction_id, -- rcv_transaction_id
dist_match_type, -- dist_match_type
matched_uom_lookup_code, -- matched_uom_lookup_code
global_attribute_category, -- global_attribute_category
global_attribute1, -- global_attribute1
global_attribute2, -- global_attribute2
global_attribute3, -- global_attribute3
global_attribute4, -- global_attribute4
global_attribute5, -- global_attribute5
global_attribute6, -- global_attribute6
global_attribute7, -- global_attribute7
global_attribute8, -- global_attribute8
global_attribute9, -- global_attribute9
global_attribute10, -- global_attribute10
global_attribute11, -- global_attribute11
global_attribute12, -- global_attribute12
global_attribute13, -- global_attribute13
global_attribute14, -- global_attribute14
global_attribute15, -- global_attribute15
global_attribute16, -- global_attribute16
global_attribute17, -- global_attribute17
global_attribute18, -- global_attribute18
global_attribute19, -- global_attribute19
global_attribute20, -- global_attribute20
receipt_verified_flag, -- receipt_verified_flag
receipt_required_flag, -- receipt_required_flag
receipt_missing_flag, -- receipt_missing_flag
justification, -- justification
expense_Group, -- expense_Group
end_Expense_Date, -- end_Expense_Date
receipt_Currency_Code, -- receipt_Currency_Code
receipt_Conversion_Rate, -- receipt_Conversion_Rate
receipt_Currency_Amount, -- receipt_Currency_Amount
daily_Amount, -- daily_Amount
web_Parameter_Id, -- web_Parameter_Id
adjustment_Reason, -- adjustment_Reason
credit_Card_Trx_Id, -- credit_Card_Trx_Id
company_Prepaid_Invoice_Id, -- company_Prepaid_Invoice_Id
org_id, -- MOAC project org_id
-1* rounding_amt, -- rounding_amt
charge_applicable_to_dist_id, -- charge_applicable_to_dist_id
corrected_invoice_dist_id, -- corrected_invoice_dist_id
DECODE( related_id, NULL, NULL,
invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL,
--bugfix:4921399
NULL ), -- related_id
asset_book_type_code, -- asset_book_type_code
asset_category_id, -- asset_category_id
NULL, -- accounting_event_id
decode(p_calling_mode, 'CANCEL', 'Y', NULL), -- cancellation_flag
'PERMANENT',
intended_use, -- intended_use
'N' -- rcv_charge_addition_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_inv_line_rec.invoice_id
AND invoice_line_number = p_inv_line_rec.line_number
AND (reversal_flag is null
or reversal_flag = 'N')
AND (
(line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
and prepay_distribution_id IS NULL) OR
prepay_distribution_id IS NOT NULL
)
AND (line_type_lookup_code <> 'AWT' OR
(line_type_lookup_code = 'AWT' AND awt_flag <> 'A'))
) ;
l_debug_info := 'Update reversal_flag on charge distributions';
UPDATE ap_invoice_distributions
SET reversal_flag = 'Y'
WHERE invoice_id = p_inv_line_rec.invoice_id
AND invoice_line_number = p_inv_line_rec.line_number;