[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 */
AND LINE_TYPE_LOOKUP_CODE NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV') -- bug 9582952
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 distinct aid1.invoice_id,aid1.invoice_line_number
FROM ap_invoice_distributions_all AID, --Inv dists
ap_invoice_distributions_all AID1 --prepay dists
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.invoice_line_number = p_line_rec.line_number
AND aid1.invoice_distribution_id = aid.prepay_distribution_id;
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 ail.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
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','PREPAY') --Added PREPAY for bug#9298560
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 *
FROM ap_invoice_distributions_all aid
WHERE 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'
AND NOT EXISTS (
SELECT invoice_distribution_id
FROM gms_award_distributions gad
WHERE aid.invoice_distribution_id = gad.invoice_distribution_id
);
select nvl(item_dist.accounting_date,
zx_dist.gl_date) accounting_date,
'N' accrual_posted_flag,
'U' assets_addition_flag,
tax_dist.assets_tracking_flag assets_tracking_flag, --Bug14772756
'N' cash_posted_flag,
AP_INVOICE_LINES_PKG.get_max_dist_line_num(
p_line_rec.invoice_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(
zx_dist.gl_date,
tax_dist.org_id) period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
DECODE(tax_dist.encumbered_flag,
'R', 'R', 'N') encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,'CANCEL INVOICE',
DECODE(tax_dist.prepay_distribution_id,NULL, 'Y',NULL),Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
(-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount,
tax_dist.pay_awt_group_id pay_awt_group_id
from ap_invoice_distributions_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = c_invoice_id
and tax_dist.invoice_id = zx_dist.trx_id
and zx_dist.application_id = 200
and zx_dist.entity_code = 'AP_INVOICES'
and zx_dist.event_class_code IN ('STANDARD INVOICES',
'PREPAYMENT INVOICES',
'EXPENSE REPORTS')
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TERV', 'TRV')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_id = c_invoice_id
and item_dist.invoice_line_number = c_item_line_number
and item_dist.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
and zx_dist.reverse_flag = 'Y'
and not exists(select detail_tax_dist_id
from ap_invoice_distributions aid
where aid.invoice_id = c_invoice_id
and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id);
select
nvl(item_dist.accounting_date,
zx_dist.gl_date) accounting_date,
'N' accrual_posted_flag,
'U' assets_addition_flag,
tax_dist.assets_tracking_flag assets_tracking_flag, --Bug14772756
'N' cash_posted_flag,
AP_ETAX_UTILITY_PKG.Get_Max_Dist_Num_Self(
p_line_rec.invoice_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
tax_dist.period_name period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
DECODE(tax_dist.encumbered_flag,
'R', 'R', 'N') encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,'CANCEL INVOICE',
DECODE(tax_dist.prepay_distribution_id,NULL,'Y',NULL),Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
zx_dist.self_assessed_flag self_assessed_flag,
tax_dist.self_assessed_tax_liab_ccid self_assessed_tax_liab_ccid,
(-1)*tax_dist.prepay_tax_diff_amount prepay_tax_diff_amount
from ap_self_assessed_tax_dist_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = c_invoice_id
and tax_dist.invoice_id = zx_dist.trx_id
and zx_dist.application_id = 200
and zx_dist.entity_code = 'AP_INVOICES'
and zx_dist.event_class_code IN ('STANDARD INVOICES',
'PREPAYMENT INVOICES',
'EXPENSE REPORTS')
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_id = c_invoice_id
and item_dist.invoice_line_number = c_item_line_number
and item_dist.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS')
and zx_dist.reverse_flag = 'Y'
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_id = c_invoice_id
AND aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
;
SELECT ai.invoice_type_lookup_code,
ai.payment_status_flag,
ai.invoice_amount,
ai.payment_currency_code,
ai.invoice_currency_code,
ai.payment_cross_rate_date,
ai.payment_cross_rate_type,
asp.base_currency_code,
ai.invoice_id,
ai.invoice_num,
ai.org_id,
ai.invoice_amount,
ai.base_amount,
ai.exchange_rate,
ai.invoice_currency_code,
ai.invoice_type_lookup_code,
ai.exchange_date,
ai.exchange_rate_type,
ai.vendor_id,
ai.invoice_date,
ai.disc_is_inv_less_tax_flag,
ai.exclude_freight_from_discount
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,
l_base_currency_code,
l_invoice_rec.invoice_id,
l_invoice_rec.invoice_num,
l_invoice_rec.org_id,
l_invoice_rec.invoice_amount,
l_invoice_rec.base_amount,
l_invoice_rec.exchange_rate,
l_invoice_rec.invoice_currency_code,
l_invoice_rec.invoice_type_lookup_code,
l_invoice_rec.exchange_date,
l_invoice_rec.exchange_rate_type,
l_invoice_rec.vendor_id,
l_invoice_rec.invoice_date,
l_invoice_rec.disc_is_inv_less_tax_flag,
l_invoice_rec.exclude_freight_from_discount
FROM ap_invoices ai,
ap_system_parameters_all asp
WHERE invoice_id = p_line_rec.invoice_id
AND ai.org_id = asp.org_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);
For I in(select invoice_distribution_id
from ap_invoice_distributions aid1
where aid1.invoice_id=P_line_rec.invoice_id
and aid1.line_type_lookup_code='AWT'
and aid1.awt_flag='A'
and aid1.parent_reversal_id is null --original dist
--for original dists there is no reversal dist created
and ( not exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.invoice_line_number=aid1.invoice_line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id)
--the reversal dist does not reverse the amount correctly
or exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.invoice_line_number=aid1.invoice_line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id
and -1 * aid2.amount <> aid1.amount))) --dists updated today
LOOP
prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
SELECT COUNT(1)
INTO l_manual_tax_lines
FROM ap_invoice_lines_all ail
WHERE invoice_id = P_line_rec.invoice_id
AND line_type_lookup_code = 'TAX'
AND summary_tax_line_id IS NULL;
SELECT COUNT(*) INTO l_itm_dist_count
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.prepay_distribution_id is not null
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all item
WHERE item.invoice_id = p_line_rec.invoice_id
AND item.prepay_distribution_id is null)
AND ROWNUM =1;
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);
DELETE zx_reverse_dist_gt; --Bug14383132
For I in(select invoice_distribution_id
from ap_invoice_distributions aid1
where aid1.invoice_id=l_chrg_line_rec.invoice_id
and aid1.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec. line_number
and aid1.parent_reversal_id is null --original dist
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'))
--for original dists there is no reversal dist created
and ( not exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=l_chrg_line_rec.invoice_id
and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id)
--the reversal dist does not reverse the amount correctly
or exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=l_chrg_line_rec.invoice_id
and aid2.invoice_line_number=l_chrg_line_rec.line_number --P_line_rec.line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id
and -1 * aid2.amount <> aid1.amount)) )
LOOP
prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
DELETE zx_transaction_lines_gt;
DELETE zx_import_tax_lines_gt;
DELETE zx_trx_tax_link_gt;
DELETE zx_reverse_dist_gt;
ELSE -- update the tax only line amount to 0
IF l_return_status THEN
open c_charge_lines (p_line_rec.invoice_id,
p_line_rec.line_number);
l_debug_info := 'Inserting reverse entries into ap_invoice_distributions_all after line discard';
INSERT INTO ap_invoice_distributions_all (
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
--batch_id,
created_by,
creation_date,
description,
final_match_flag,
income_tax_region,
last_update_login,
match_status_flag,
posted_flag,
po_distribution_id,
program_application_id,
program_id,
program_update_date,
quantity_invoiced,
request_id,
reversal_flag,
type_1099,
unit_price,
encumbered_flag,
stat_amount,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
parent_invoice_id,
pa_addition_flag,
pa_quantity,
prepay_amount_remaining,
project_accounting_context,
project_id,
task_id,
packet_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
reference_1,
reference_2,
org_id,
awt_invoice_payment_id,
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,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
award_id,
credit_card_trx_id,
dist_match_type,
rcv_transaction_id,
invoice_distribution_id,
parent_reversal_id,
tax_recoverable_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
matched_uom_lookup_code,
gms_burdenable_raw_cost,
accounting_event_id,
prepay_distribution_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
inventory_transfer_status,
company_prepaid_invoice_id,
cc_reversal_flag,
awt_withheld_amt,
pa_cmt_xface_flag,
cancellation_flag,
invoice_line_number,
corrected_invoice_dist_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_quantity,
related_id,
asset_book_type_code,
asset_category_id,
distribution_class,
tax_code_id,
intended_use,
detail_tax_dist_id,
rec_nrec_rate,
recovery_rate_id,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
prepay_tax_diff_amount,
pay_awt_group_id)
VALUES
(
l_reverse_tax_dist.accounting_date,
l_reverse_tax_dist.accrual_posted_flag,
l_reverse_tax_dist.assets_addition_flag,
l_reverse_tax_dist.assets_tracking_flag,
l_reverse_tax_dist.cash_posted_flag,
l_reverse_tax_dist.distribution_line_number,
l_reverse_tax_dist.dist_code_combination_id,
l_reverse_tax_dist.invoice_id,
l_reverse_tax_dist.last_updated_by,
l_reverse_tax_dist.last_update_date,
l_reverse_tax_dist.line_type_lookup_code,
l_reverse_tax_dist.period_name,
l_reverse_tax_dist.set_of_books_id,
l_reverse_tax_dist.amount,
l_reverse_tax_dist.base_amount,
--l_reverse_tax_dist.batch_id,
l_reverse_tax_dist.created_by,
l_reverse_tax_dist.creation_date,
l_reverse_tax_dist.description,
l_reverse_tax_dist.final_match_flag,
l_reverse_tax_dist.income_tax_region,
l_reverse_tax_dist.last_update_login,
l_reverse_tax_dist.match_status_flag,
l_reverse_tax_dist.posted_flag,
l_reverse_tax_dist.po_distribution_id,
l_reverse_tax_dist.program_application_id,
l_reverse_tax_dist.program_id,
l_reverse_tax_dist.program_update_date,
l_reverse_tax_dist.quantity_invoiced,
l_reverse_tax_dist.request_id,
l_reverse_tax_dist.reversal_flag,
l_reverse_tax_dist.type_1099,
l_reverse_tax_dist.unit_price,
l_reverse_tax_dist.encumbered_flag,
l_reverse_tax_dist.stat_amount,
l_reverse_tax_dist.attribute1,
l_reverse_tax_dist.attribute10,
l_reverse_tax_dist.attribute11,
l_reverse_tax_dist.attribute12,
l_reverse_tax_dist.attribute13,
l_reverse_tax_dist.attribute14,
l_reverse_tax_dist.attribute15,
l_reverse_tax_dist.attribute2,
l_reverse_tax_dist.attribute3,
l_reverse_tax_dist.attribute4,
l_reverse_tax_dist.attribute5,
l_reverse_tax_dist.attribute6,
l_reverse_tax_dist.attribute7,
l_reverse_tax_dist.attribute8,
l_reverse_tax_dist.attribute9,
l_reverse_tax_dist.attribute_category,
l_reverse_tax_dist.expenditure_item_date,
l_reverse_tax_dist.expenditure_organization_id,
l_reverse_tax_dist.expenditure_type,
l_reverse_tax_dist.parent_invoice_id,
l_reverse_tax_dist.pa_addition_flag,
l_reverse_tax_dist.pa_quantity,
l_reverse_tax_dist.prepay_amount_remaining,
l_reverse_tax_dist.project_accounting_context,
l_reverse_tax_dist.project_id,
l_reverse_tax_dist.task_id,
l_reverse_tax_dist.packet_id,
l_reverse_tax_dist.awt_flag,
l_reverse_tax_dist.awt_group_id,
l_reverse_tax_dist.awt_tax_rate_id,
l_reverse_tax_dist.awt_gross_amount,
l_reverse_tax_dist.awt_invoice_id,
l_reverse_tax_dist.awt_origin_group_id,
l_reverse_tax_dist.reference_1,
l_reverse_tax_dist.reference_2,
l_reverse_tax_dist.org_id,
l_reverse_tax_dist.awt_invoice_payment_id,
l_reverse_tax_dist.global_attribute_category,
l_reverse_tax_dist.global_attribute1,
l_reverse_tax_dist.global_attribute2,
l_reverse_tax_dist.global_attribute3,
l_reverse_tax_dist.global_attribute4,
l_reverse_tax_dist.global_attribute5,
l_reverse_tax_dist.global_attribute6,
l_reverse_tax_dist.global_attribute7,
l_reverse_tax_dist.global_attribute8,
l_reverse_tax_dist.global_attribute9,
l_reverse_tax_dist.global_attribute10,
l_reverse_tax_dist.global_attribute11,
l_reverse_tax_dist.global_attribute12,
l_reverse_tax_dist.global_attribute13,
l_reverse_tax_dist.global_attribute14,
l_reverse_tax_dist.global_attribute15,
l_reverse_tax_dist.global_attribute16,
l_reverse_tax_dist.global_attribute17,
l_reverse_tax_dist.global_attribute18,
l_reverse_tax_dist.global_attribute19,
l_reverse_tax_dist.global_attribute20,
l_reverse_tax_dist.receipt_verified_flag,
l_reverse_tax_dist.receipt_required_flag,
l_reverse_tax_dist.receipt_missing_flag,
l_reverse_tax_dist.justification,
l_reverse_tax_dist.expense_group,
l_reverse_tax_dist.start_expense_date,
l_reverse_tax_dist.end_expense_date,
l_reverse_tax_dist.receipt_currency_code,
l_reverse_tax_dist.receipt_conversion_rate,
l_reverse_tax_dist.receipt_currency_amount,
l_reverse_tax_dist.daily_amount,
l_reverse_tax_dist.web_parameter_id,
l_reverse_tax_dist.adjustment_reason,
l_reverse_tax_dist.award_id,
l_reverse_tax_dist.credit_card_trx_id,
l_reverse_tax_dist.dist_match_type,
l_reverse_tax_dist.rcv_transaction_id,
l_reverse_tax_dist.invoice_distribution_id,
l_reverse_tax_dist.parent_reversal_id,
l_reverse_tax_dist.tax_recoverable_flag,
l_reverse_tax_dist.merchant_document_number,
l_reverse_tax_dist.merchant_name,
l_reverse_tax_dist.merchant_reference,
l_reverse_tax_dist.merchant_tax_reg_number,
l_reverse_tax_dist.merchant_taxpayer_id,
l_reverse_tax_dist.country_of_supply,
l_reverse_tax_dist.matched_uom_lookup_code,
l_reverse_tax_dist.gms_burdenable_raw_cost,
l_reverse_tax_dist.accounting_event_id,
l_reverse_tax_dist.prepay_distribution_id,
l_reverse_tax_dist.upgrade_posted_amt,
l_reverse_tax_dist.upgrade_base_posted_amt,
l_reverse_tax_dist.inventory_transfer_status,
l_reverse_tax_dist.company_prepaid_invoice_id,
l_reverse_tax_dist.cc_reversal_flag,
l_reverse_tax_dist.awt_withheld_amt,
l_reverse_tax_dist.pa_cmt_xface_flag,
l_reverse_tax_dist.cancellation_flag,
l_reverse_tax_dist.invoice_line_number,
l_reverse_tax_dist.corrected_invoice_dist_id,
l_reverse_tax_dist.rounding_amt,
l_reverse_tax_dist.charge_applicable_to_dist_id,
l_reverse_tax_dist.corrected_quantity,
l_reverse_tax_dist.related_id,
l_reverse_tax_dist.asset_book_type_code,
l_reverse_tax_dist.asset_category_id,
l_reverse_tax_dist.distribution_class,
l_reverse_tax_dist.tax_code_id,
l_reverse_tax_dist.intended_use,
l_reverse_tax_dist.detail_tax_dist_id,
l_reverse_tax_dist.rec_nrec_rate,
l_reverse_tax_dist.recovery_rate_id,
l_reverse_tax_dist.recovery_type_code,
l_reverse_tax_dist.withholding_tax_code_id,
l_reverse_tax_dist.taxable_amount,
l_reverse_tax_dist.taxable_base_amount,
l_reverse_tax_dist.tax_already_distributed_flag,
l_reverse_tax_dist.summary_tax_line_id,
l_reverse_tax_dist.rcv_charge_addition_flag,
l_reverse_tax_dist.prepay_tax_diff_amount,
l_reverse_tax_dist.pay_awt_group_id);
INSERT into ZX_TAX_DIST_ID_GT (TAX_DIST_ID) values (l_reverse_tax_dist.detail_tax_dist_id) ;
l_debug_info := 'Inserting reverse entries into ap_self_assessed_tax_dist_all';
INSERT INTO ap_self_assessed_tax_dist_all (
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
--batch_id,
created_by,
creation_date,
description,
final_match_flag,
income_tax_region,
last_update_login,
match_status_flag,
posted_flag,
po_distribution_id,
program_application_id,
program_id,
program_update_date,
quantity_invoiced,
request_id,
reversal_flag,
type_1099,
unit_price,
encumbered_flag,
stat_amount,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
parent_invoice_id,
pa_addition_flag,
pa_quantity,
prepay_amount_remaining,
project_accounting_context,
project_id,
task_id,
packet_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
reference_1,
reference_2,
org_id,
awt_invoice_payment_id,
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,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
award_id,
credit_card_trx_id,
dist_match_type,
rcv_transaction_id,
invoice_distribution_id,
parent_reversal_id,
tax_recoverable_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
matched_uom_lookup_code,
gms_burdenable_raw_cost,
accounting_event_id,
prepay_distribution_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
inventory_transfer_status,
company_prepaid_invoice_id,
cc_reversal_flag,
awt_withheld_amt,
pa_cmt_xface_flag,
cancellation_flag,
invoice_line_number,
corrected_invoice_dist_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_quantity,
related_id,
asset_book_type_code,
asset_category_id,
distribution_class,
tax_code_id,
intended_use,
detail_tax_dist_id,
rec_nrec_rate,
recovery_rate_id,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
self_assessed_flag,
self_assessed_tax_liab_ccid,
prepay_tax_diff_amount
)
VALUES
(
l_self_assess_rev_tax_dist_1.accounting_date,
l_self_assess_rev_tax_dist_1.accrual_posted_flag,
l_self_assess_rev_tax_dist_1.assets_addition_flag,
l_self_assess_rev_tax_dist_1.assets_tracking_flag,
l_self_assess_rev_tax_dist_1.cash_posted_flag,
l_self_assess_rev_tax_dist_1.distribution_line_number,
l_self_assess_rev_tax_dist_1.dist_code_combination_id,
l_self_assess_rev_tax_dist_1.invoice_id,
l_self_assess_rev_tax_dist_1.last_updated_by,
l_self_assess_rev_tax_dist_1.last_update_date,
l_self_assess_rev_tax_dist_1.line_type_lookup_code,
l_self_assess_rev_tax_dist_1.period_name,
l_self_assess_rev_tax_dist_1.set_of_books_id,
l_self_assess_rev_tax_dist_1.amount,
l_self_assess_rev_tax_dist_1.base_amount,
--l_self_assess_rev_tax_dist_1.batch_id,
l_self_assess_rev_tax_dist_1.created_by,
l_self_assess_rev_tax_dist_1.creation_date,
l_self_assess_rev_tax_dist_1.description,
l_self_assess_rev_tax_dist_1.final_match_flag,
l_self_assess_rev_tax_dist_1.income_tax_region,
l_self_assess_rev_tax_dist_1.last_update_login,
l_self_assess_rev_tax_dist_1.match_status_flag,
l_self_assess_rev_tax_dist_1.posted_flag,
l_self_assess_rev_tax_dist_1.po_distribution_id,
l_self_assess_rev_tax_dist_1.program_application_id,
l_self_assess_rev_tax_dist_1.program_id,
l_self_assess_rev_tax_dist_1.program_update_date,
l_self_assess_rev_tax_dist_1.quantity_invoiced,
l_self_assess_rev_tax_dist_1.request_id,
l_self_assess_rev_tax_dist_1.reversal_flag,
l_self_assess_rev_tax_dist_1.type_1099,
l_self_assess_rev_tax_dist_1.unit_price,
l_self_assess_rev_tax_dist_1.encumbered_flag,
l_self_assess_rev_tax_dist_1.stat_amount,
l_self_assess_rev_tax_dist_1.attribute1,
l_self_assess_rev_tax_dist_1.attribute10,
l_self_assess_rev_tax_dist_1.attribute11,
l_self_assess_rev_tax_dist_1.attribute12,
l_self_assess_rev_tax_dist_1.attribute13,
l_self_assess_rev_tax_dist_1.attribute14,
l_self_assess_rev_tax_dist_1.attribute15,
l_self_assess_rev_tax_dist_1.attribute2,
l_self_assess_rev_tax_dist_1.attribute3,
l_self_assess_rev_tax_dist_1.attribute4,
l_self_assess_rev_tax_dist_1.attribute5,
l_self_assess_rev_tax_dist_1.attribute6,
l_self_assess_rev_tax_dist_1.attribute7,
l_self_assess_rev_tax_dist_1.attribute8,
l_self_assess_rev_tax_dist_1.attribute9,
l_self_assess_rev_tax_dist_1.attribute_category,
l_self_assess_rev_tax_dist_1.expenditure_item_date,
l_self_assess_rev_tax_dist_1.expenditure_organization_id,
l_self_assess_rev_tax_dist_1.expenditure_type,
l_self_assess_rev_tax_dist_1.parent_invoice_id,
l_self_assess_rev_tax_dist_1.pa_addition_flag,
l_self_assess_rev_tax_dist_1.pa_quantity,
l_self_assess_rev_tax_dist_1.prepay_amount_remaining,
l_self_assess_rev_tax_dist_1.project_accounting_context,
l_self_assess_rev_tax_dist_1.project_id,
l_self_assess_rev_tax_dist_1.task_id,
l_self_assess_rev_tax_dist_1.packet_id,
l_self_assess_rev_tax_dist_1.awt_flag,
l_self_assess_rev_tax_dist_1.awt_group_id,
l_self_assess_rev_tax_dist_1.awt_tax_rate_id,
l_self_assess_rev_tax_dist_1.awt_gross_amount,
l_self_assess_rev_tax_dist_1.awt_invoice_id,
l_self_assess_rev_tax_dist_1.awt_origin_group_id,
l_self_assess_rev_tax_dist_1.reference_1,
l_self_assess_rev_tax_dist_1.reference_2,
l_self_assess_rev_tax_dist_1.org_id,
l_self_assess_rev_tax_dist_1.awt_invoice_payment_id,
l_self_assess_rev_tax_dist_1.global_attribute_category,
l_self_assess_rev_tax_dist_1.global_attribute1,
l_self_assess_rev_tax_dist_1.global_attribute2,
l_self_assess_rev_tax_dist_1.global_attribute3,
l_self_assess_rev_tax_dist_1.global_attribute4,
l_self_assess_rev_tax_dist_1.global_attribute5,
l_self_assess_rev_tax_dist_1.global_attribute6,
l_self_assess_rev_tax_dist_1.global_attribute7,
l_self_assess_rev_tax_dist_1.global_attribute8,
l_self_assess_rev_tax_dist_1.global_attribute9,
l_self_assess_rev_tax_dist_1.global_attribute10,
l_self_assess_rev_tax_dist_1.global_attribute11,
l_self_assess_rev_tax_dist_1.global_attribute12,
l_self_assess_rev_tax_dist_1.global_attribute13,
l_self_assess_rev_tax_dist_1.global_attribute14,
l_self_assess_rev_tax_dist_1.global_attribute15,
l_self_assess_rev_tax_dist_1.global_attribute16,
l_self_assess_rev_tax_dist_1.global_attribute17,
l_self_assess_rev_tax_dist_1.global_attribute18,
l_self_assess_rev_tax_dist_1.global_attribute19,
l_self_assess_rev_tax_dist_1.global_attribute20,
l_self_assess_rev_tax_dist_1.receipt_verified_flag,
l_self_assess_rev_tax_dist_1.receipt_required_flag,
l_self_assess_rev_tax_dist_1.receipt_missing_flag,
l_self_assess_rev_tax_dist_1.justification,
l_self_assess_rev_tax_dist_1.expense_group,
l_self_assess_rev_tax_dist_1.start_expense_date,
l_self_assess_rev_tax_dist_1.end_expense_date,
l_self_assess_rev_tax_dist_1.receipt_currency_code,
l_self_assess_rev_tax_dist_1.receipt_conversion_rate,
l_self_assess_rev_tax_dist_1.receipt_currency_amount,
l_self_assess_rev_tax_dist_1.daily_amount,
l_self_assess_rev_tax_dist_1.web_parameter_id,
l_self_assess_rev_tax_dist_1.adjustment_reason,
l_self_assess_rev_tax_dist_1.award_id,
l_self_assess_rev_tax_dist_1.credit_card_trx_id,
l_self_assess_rev_tax_dist_1.dist_match_type,
l_self_assess_rev_tax_dist_1.rcv_transaction_id,
l_self_assess_rev_tax_dist_1.invoice_distribution_id,
l_self_assess_rev_tax_dist_1.parent_reversal_id,
l_self_assess_rev_tax_dist_1.tax_recoverable_flag,
l_self_assess_rev_tax_dist_1.merchant_document_number,
l_self_assess_rev_tax_dist_1.merchant_name,
l_self_assess_rev_tax_dist_1.merchant_reference,
l_self_assess_rev_tax_dist_1.merchant_tax_reg_number,
l_self_assess_rev_tax_dist_1.merchant_taxpayer_id,
l_self_assess_rev_tax_dist_1.country_of_supply,
l_self_assess_rev_tax_dist_1.matched_uom_lookup_code,
l_self_assess_rev_tax_dist_1.gms_burdenable_raw_cost,
l_self_assess_rev_tax_dist_1.accounting_event_id,
l_self_assess_rev_tax_dist_1.prepay_distribution_id,
l_self_assess_rev_tax_dist_1.upgrade_posted_amt,
l_self_assess_rev_tax_dist_1.upgrade_base_posted_amt,
l_self_assess_rev_tax_dist_1.inventory_transfer_status,
l_self_assess_rev_tax_dist_1.company_prepaid_invoice_id,
l_self_assess_rev_tax_dist_1.cc_reversal_flag,
l_self_assess_rev_tax_dist_1.awt_withheld_amt,
l_self_assess_rev_tax_dist_1.pa_cmt_xface_flag,
l_self_assess_rev_tax_dist_1.cancellation_flag,
l_self_assess_rev_tax_dist_1.invoice_line_number,
l_self_assess_rev_tax_dist_1.corrected_invoice_dist_id,
l_self_assess_rev_tax_dist_1.rounding_amt,
l_self_assess_rev_tax_dist_1.charge_applicable_to_dist_id,
l_self_assess_rev_tax_dist_1.corrected_quantity,
l_self_assess_rev_tax_dist_1.related_id,
l_self_assess_rev_tax_dist_1.asset_book_type_code,
l_self_assess_rev_tax_dist_1.asset_category_id,
l_self_assess_rev_tax_dist_1.distribution_class,
l_self_assess_rev_tax_dist_1.tax_code_id,
l_self_assess_rev_tax_dist_1.intended_use,
l_self_assess_rev_tax_dist_1.detail_tax_dist_id,
l_self_assess_rev_tax_dist_1.rec_nrec_rate,
l_self_assess_rev_tax_dist_1.recovery_rate_id,
l_self_assess_rev_tax_dist_1.recovery_type_code,
l_self_assess_rev_tax_dist_1.withholding_tax_code_id,
l_self_assess_rev_tax_dist_1.taxable_amount,
l_self_assess_rev_tax_dist_1.taxable_base_amount,
l_self_assess_rev_tax_dist_1.tax_already_distributed_flag,
l_self_assess_rev_tax_dist_1.summary_tax_line_id,
l_self_assess_rev_tax_dist_1.rcv_charge_addition_flag,
l_self_assess_rev_tax_dist_1.self_assessed_flag,
l_self_assess_rev_tax_dist_1.self_assessed_tax_liab_ccid,
l_self_assess_rev_tax_dist_1.prepay_tax_diff_amount
);
INSERT into ZX_TAX_DIST_ID_GT (TAX_DIST_ID) values (l_self_assess_rev_tax_dist_1.detail_tax_dist_id) ;
ZX_API_PUB.Update_Tax_dist_gl_date (
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status_service,
l_msg_count,
l_msg_data,
l_inv_cancel_date );
l_debug_info := 'Update reversal_flag';
UPDATE ap_invoice_distributions_all aid
SET reversal_flag = (select reverse_flag
from zx_rec_nrec_dist zx
where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Update related_flag';
UPDATE ap_invoice_distributions aid
SET aid.related_id =
(SELECT invoice_distribution_id
FROM ap_invoice_distributions_all 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_all 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.reversal_flag = 'Y'
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Update reversal_flag';
UPDATE ap_self_assessed_tax_dist_all aid
SET reversal_flag = (select reverse_flag
from zx_rec_nrec_dist zx
where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Update related_flag';
UPDATE ap_self_assessed_tax_dist_all aid
SET aid.related_id =
(SELECT invoice_distribution_id
FROM ap_self_assessed_tax_dist_all 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_self_assessed_tax_dist_all 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.reversal_flag = 'Y'
AND aid.detail_tax_dist_id IS NOT NULL;
DELETE zx_transaction_lines_gt;
DELETE zx_import_tax_lines_gt;
DELETE zx_trx_tax_link_gt;
DELETE zx_reverse_dist_gt;
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;
| Step 0. Delete all the unprocessed bc events for this invoice |
+-----------------------------------------------------------------*/
IF(p_calling_mode IN ('DISCARD', 'UNAPPLY_PREPAY')) THEN
AP_FUNDS_CONTROL_PKG.Encum_Unprocessed_Events_Del
(p_invoice_id => p_line_rec.invoice_id,
p_calling_sequence => l_curr_calling_sequence);
UPDATE ap_invoice_distributions aid
SET aid.encumbered_flag = 'R'
WHERE aid.invoice_id = p_line_rec.invoice_id
AND aid.invoice_line_number = p_line_rec.line_number
AND nvl(aid.encumbered_flag,'N') IN ('N','H','P')
AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
AND nvl(aid.reversal_flag,'N')<>'Y'
AND EXISTS (SELECT 1
FROM financials_system_params_all fsp
WHERE fsp.org_id = aid.org_id
AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
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_status1,
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_status1,
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
-- Bug 14305530: Added last_updated_by, last_update_login and last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
,last_update_date = sysdate
,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)
,quantity_invoiced = decode( p_calling_mode,
'DISCARD', quantity_invoiced - quantity_invoiced, --8560785
'CANCEL', quantity_invoiced - quantity_invoiced, --Introduced for bug#9570774
'UNAPPLY_PREPAY', quantity_invoiced - quantity_invoiced, --Introduced for bug#9298560
quantity_invoiced)
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';
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,
awt_related_id, --bug 8745752
retained_invoice_dist_id, -- Bug 8824235
pay_awt_group_id, /* Bug 9821980 */
inventory_transfer_status /* Bug#11067286 */ )
(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
--Bug9345786. Commented the following code.
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, */
--Bug9345786. Added following code instead.
(CASE
WHEN (P_calling_mode = 'UNAPPLY_PREPAY') THEN p_line_rec.accounting_date
WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date > p_line_rec.accounting_date) THEN
ap_utilities_pkg.get_reversal_gl_date(accounting_date, org_id)
WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date <= p_line_rec.accounting_date) THEN
ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)
ELSE
ap_utilities_pkg.get_reversal_gl_date(p_line_rec.accounting_date, org_id)
END), -- accounting_date
(CASE
WHEN (P_calling_mode = 'UNAPPLY_PREPAY') THEN p_line_rec.period_name
WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date > p_line_rec.accounting_date) THEN
ap_utilities_pkg.get_reversal_period(accounting_date, org_id)
WHEN (P_calling_mode <> 'UNAPPLY_PREPAY' AND accounting_date <= p_line_rec.accounting_date) THEN
ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)
ELSE
ap_utilities_pkg.get_reversal_period(p_line_rec.accounting_date, org_id)
END ), -- 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,
NULL , -- match_status_flag bug 11810934
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', 'R', 'R', 'N'), -- pa_addition_flag bug10012305
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',decode ( line_type_lookup_code ,'PREPAY' , NULL ,'Y'),null), -- cancellation_flag bug9173973
'PERMANENT',
intended_use, -- intended_use
'N', -- rcv_charge_addition_flag
awt_related_id, -- Bug 8745752
retained_invoice_dist_id, -- Bug 8824235
pay_awt_group_id, /* Bug 9821980 */
'N' /*Bug#11067286*/
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
AND nvl(reversal_flag,'N') <> 'Y' -- Bug 8326344
) ;
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';
UPDATE ap_invoice_distributions_all aid
SET aid.retained_amount_remaining = aid.retained_amount_remaining -
NVL((SELECT sum(d2.amount)
FROM ap_invoice_distributions_all d2
WHERE d2.parent_reversal_id is not null
AND d2.reversal_flag = 'Y'
AND d2.invoice_id = p_line_rec.invoice_id
AND d2.invoice_line_number = p_line_rec.line_number
AND d2.match_status_flag = 'N'
AND d2.retained_invoice_dist_id = aid.invoice_distribution_id), 0)
WHERE invoice_distribution_id in
(SELECT DISTINCT retained_invoice_dist_id
FROM ap_invoice_distributions_all d3
WHERE d3.reversal_flag = 'Y'
AND d3.invoice_id = p_line_rec.invoice_id
AND d3.invoice_line_number = p_line_rec.line_number
AND d3.parent_reversal_id is not null
AND d3.match_status_flag = 'N'
AND d3.retained_invoice_dist_id = aid.invoice_distribution_id);
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')
BULK COLLECT INTO l_tax_line_number,l_prepay_included /*Bug 9841966: Change to bulk collect*/
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,
-- replaced NULLS with correct column values for bug #8897523
l_prepay_invoice_id, --NULL
l_prepay_line_number, --NULL
p_line_rec.invoice_id,
l_tax_line_number(1), /*Bug 9841966: Passed the first tax element, this is dummy*/
'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(l_loop_counter) /*Bug 9841966: added loop counter*/
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);
For I in(select invoice_distribution_id
from ap_invoice_distributions aid1
where aid1.invoice_id=P_line_rec.invoice_id
and aid1.invoice_line_number=P_line_rec. line_number
and aid1.parent_reversal_id is null --original dist
--for original dists there is no reversal dist created
and ( not exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.invoice_line_number=P_line_rec.line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id)
--the reversal dist does not reverse the amount correctly
or exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.invoice_line_number=P_line_rec.line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id
and -1 * aid2.amount <> aid1.amount))
UNION
select invoice_distribution_id
from ap_invoice_distributions aid1
where aid1.invoice_id=P_line_rec.invoice_id
and aid1.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 aid1.parent_reversal_id is null --original dist
and aid1.line_type_lookup_code not in('MISCELLANEOUS','FREIGHT')
--for original dists there is no reversal dist created
and ( not exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.parent_reversal_id =aid1.invoice_distribution_id)
--the reversal dist does not reverse the amount correctly
or exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_line_rec.invoice_id
and aid2.parent_reversal_id =aid1.invoice_distribution_id
and -1 * aid2.amount <> aid1.amount)))
LOOP
prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
l_dummy := AP_PREPAY_PKG.Update_Prepayment(
l_prepay_dist_info,
-- replaced NULLS with correct column values for bug #8897523
l_prepay_invoice_id, --NULL
l_prepay_line_number, --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);
UPDATE ap_invoice_distributions aid
SET aid.encumbered_flag = 'R'
WHERE aid.invoice_id = p_inv_line_rec.invoice_id
AND aid.invoice_line_number = p_inv_line_rec.line_number
AND nvl(aid.match_status_flag,'N') <> 'A'
AND nvl(aid.encumbered_flag,'N') IN ('N','H','P')
AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
AND nvl(aid.reversal_flag,'N')<>'Y'
AND EXISTS (SELECT 1
FROM financials_system_params_all fsp
WHERE fsp.org_id = aid.org_id
AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
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,
pay_awt_group_id, /* Bug 9821980 */
tax_already_distributed_flag) --bug14383132
(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,
NULL , -- match_status_flag bug 11810934
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', 'R', 'R', 'N'), -- pa_addition_flag bug10012305
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',decode ( line_type_lookup_code ,'PREPAY' , NULL ,'Y'),null), -- cancellation_flag bug9173973
'PERMANENT',
intended_use, -- intended_use
'N', -- rcv_charge_addition_flag
pay_awt_group_id, /* Bug 9821980 */
'Y'
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
AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV');
--INTO zx_reverse_dist_gt. Once distributions are inserted CALL
--ZX_API_PUB.Reverse_Distributions(). Parameter list to this
--Function is listed in update *** PGAYEN 07/31/12 12:00 am *** on
--bug14383132
--Similar code is present in AP_ETAX_SERVICES_PKG.CANCEL_INVOICe
--Bug14383132: Start
---------------------------------------------------------------
l_debug_info := 'p_inv_line_rec.invoice_id: '||p_inv_line_rec.invoice_id||' p_inv_line_rec.line_number: '||p_inv_line_rec.line_number;
l_debug_info := 'Insert into zx_reverse_dist_gt';
INSERT INTO zx_reverse_dist_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversing_trx_line_dist_id,
reversing_tax_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id,
reversed_trx_line_dist_id,
reversed_tax_line_id
)
select distinct
item_dist.org_id internal_organization_id,
zx_dist.application_id reversing_appln_id,
zx_dist.entity_code reversing_entity_code,
zx_dist.event_class_code reversing_evnt_cls_code,
zx_dist.trx_id reversing_trx_id,
zx_dist.trx_level_type reversing_trx_level_type,
zx_dist.trx_line_id reversing_trx_line_id,
reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
zx_dist.tax_line_id reversing_tax_line_id,
zx_dist.application_id reversed_appln_id,
zx_dist.entity_code reversed_entity_code,
zx_dist.event_class_code reversed_evnt_cls_code,
zx_dist.trx_id reversed_trx_id,
zx_dist.trx_level_type reversed_trx_level_type,
zx_dist.trx_line_id reversed_trx_line_id,
zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
zx_dist.tax_line_id reversed_tax_line_id
from ap_invoice_distributions_all item_dist,
ap_invoice_distributions_all tax_dist,
ap_invoice_distributions_all reverse_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_inv_line_rec.invoice_id
and tax_dist.invoice_id = item_dist.invoice_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')
and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
and nvl(zx_dist.reverse_flag, 'N') = 'N'
and item_dist.invoice_line_number = p_inv_line_rec.line_number;
l_debug_info := 'Row Count inserted into zx_reverse_dist_gt: ' || l_reverse_dist_count;
l_debug_info := 'Inserting self assessed tax entries into zx_reverse_dist_gt';
INSERT INTO zx_reverse_dist_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversing_trx_line_dist_id,
reversing_tax_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id,
reversed_trx_line_dist_id,
reversed_tax_line_id
)
select distinct
item_dist.org_id internal_organization_id,
zx_dist.application_id reversing_appln_id,
zx_dist.entity_code reversing_entity_code,
zx_dist.event_class_code reversing_evnt_cls_code,
zx_dist.trx_id reversing_trx_id,
zx_dist.trx_level_type reversing_trx_level_type,
zx_dist.trx_line_id reversing_trx_line_id,
reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
zx_dist.tax_line_id reversing_tax_line_id,
zx_dist.application_id reversed_appln_id,
zx_dist.entity_code reversed_entity_code,
zx_dist.event_class_code reversed_evnt_cls_code,
zx_dist.trx_id reversed_trx_id,
zx_dist.trx_level_type reversed_trx_level_type,
zx_dist.trx_line_id reversed_trx_line_id,
zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
zx_dist.tax_line_id reversed_tax_line_id
from ap_invoice_distributions_all item_dist,
ap_self_assessed_tax_dist_all tax_dist,
ap_invoice_distributions_all reverse_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_inv_line_rec.invoice_id
and tax_dist.invoice_id = item_dist.invoice_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
and nvl(zx_dist.reverse_flag, 'N') = 'N'
and item_dist.invoice_line_number = p_inv_line_rec.line_number;
l_debug_info := 'Row Count inserted into zx_reverse_dist_gt: ' || l_sa_reverse_dist_count;
SELECT 'Tax Distributions Exist'
INTO l_dummy
FROM ap_invoice_distributions_all item_dist,
ap_invoice_distributions_all tax_dist
WHERE tax_dist.invoice_id = p_invoice_id
and tax_dist.invoice_id = item_dist.invoice_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')
and item_dist.invoice_line_number = p_line_number
AND NVL(tax_dist.reversal_flag, 'N') <> 'Y'
AND rownum = 1;
SELECT ' self assess Tax Distributions Exist'
INTO l_dummy
FROM ap_self_assessed_tax_dist_all asat,
zx_rec_nrec_dist zx_dist
WHERE asat.invoice_id = p_invoice_id
AND asat.invoice_line_number = p_line_number
AND asat.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND zx_dist.self_assessed_flag = 'Y'
AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND rownum = 1;