[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_login_id ap_invoices_all.last_update_login%TYPE := FND_GLOBAL.login_id;
PROCEDURE insert_tax_distributions
(p_invoice_header_rec IN ap_invoices_all%ROWTYPE,
p_inv_dist_rec IN r_ins_tax_dist_info,
p_dist_code_combination_id IN NUMBER,
p_user_id IN NUMBER,
p_sysdate IN DATE,
p_login_id IN NUMBER,
p_calling_sequence IN VARCHAR2);
l_action_section := 'UPDATED';
IN ('MARK TAX LINES DELETED', 'FREEZE DISTRIBUTIONS')) THEN
l_action_section := 'UPDATED';
ELSIF ( p_calling_mode = 'DELETE INVOICE' ) THEN
l_action_section := 'PURGED';
SELECT invoice_num, invoice_date, invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = c_corrected_inv_id;
SELECT invoice_num, invoice_date, invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = c_prepay_inv_id;
SELECT transaction_date, transaction_type
FROM rcv_transactions
WHERE transaction_id = c_rcv_transaction;
SELECT pll.org_id, pll.quantity, pll.po_header_id, nvl(ph.rate ,1), pll.po_release_id, mum.uom_code
FROM po_line_locations_all pll, po_headers_all ph, mtl_units_of_measure mum
WHERE pll.line_location_id = c_po_line_location_Id
AND pll.po_header_id = ph.po_header_id
AND pll.unit_meas_lookup_code = mum.unit_of_measure (+);
SELECT pll.org_id, pll.quantity, pll.line_location_id,
pll.po_header_id, nvl(ph.rate,1), pll.po_release_id, mum.uom_code,
pd.quantity_ordered, pll.price_override
FROM po_line_locations_all pll, po_distributions_all pd,
po_headers_all ph, mtl_units_of_measure mum
WHERE pd.po_distribution_id = c_po_dist_Id
AND pd.line_location_id = pll.line_location_id
AND pll.po_header_id = ph.po_header_id
AND pll.unit_meas_lookup_code = mum.unit_of_measure (+);
SELECT inventory_organization_id
INTO AP_ETAX_SERVICES_PKG.g_fsp_attributes(l_org_id).inventory_organization_id
FROM financials_system_params_all
WHERE org_id = l_org_id;
SELECT aid.awt_group_id
FROM ap_invoice_distributions_all aid
-- ,ap_invoice_lines_all ail
WHERE aid.invoice_distribution_id = c_prepay_dist_id;
| update, or delete of existing exclusive tax lines in AP if required.
| It also handles the update of the total tax amounts. (Inclusive and
| self-assessed.
|
| PARAMETERS
| P_Invoice_Header_Rec - Header info
| P_error_code - Error code to be returned
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| DATE Author Action
| 15-OCT-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Return_Tax_Lines(
P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
Select ail.line_number
From ap_invoice_lines_all ail
Where ail.invoice_id = c_invoice_id
And ail.line_type_lookup_code = 'TAX'
And ail.prepay_invoice_id IS NULL;
(Select chrg_invoice_line_number
From ap_allocation_rule_lines arl
Where arl.invoice_id = ail.invoice_id
And arl.chrg_invoice_line_number = ail.line_number);
SELECT ail.line_number, ail.summary_tax_line_id
INTO l_invoice_line_number, l_ap_summary_tax_line_id
FROM AP_INVOICE_LINES_ALL ail
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND NOT EXISTS
(SELECT ls.summary_tax_line_id
FROM zx_lines_summary ls
WHERE ls.summary_tax_line_id = ail.summary_tax_line_id
AND ls.trx_id = ail.invoice_id
AND NVL(ls.tax_amt_included_flag, 'N') = 'N'
AND NVL(ls.self_assessed_flag, 'N') = 'N'
AND NVL(ls.reporting_only_flag, 'N') = 'N'
)
AND EXISTS
(SELECT 'Invoice Distributions Exist'
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND (aid.accounting_event_id IS NOT NULL
OR NVL(aid.match_status_flag,'N') IN ('A','T')
OR aid.bc_event_id IS NOT NULL
OR NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X')
) --This is done for Mexicana Bug7623255 to avoid such Data Fix Condition
)
--commented this condition as this is not required
--summary line should not be allowed to delete if it has frozen distribution
--as checked in above EXISTS hence no further checks needed. bug 10621602
/* AND EXISTS
(SELECT zl.summary_tax_line_id
FROM zx_lines_summary zl
WHERE zl.application_id = 200
AND zl.entity_code = 'AP_INVOICES'
AND zl.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zl.trx_id = ail.invoice_id
AND zl.tax_regime_code = ail.tax_regime_code
AND zl.tax = ail.tax
AND zl.tax_status_code = ail.tax_status_code
AND (zl.tax_rate_code = ail.tax_rate_code
OR zl.tax_rate_id = ail.tax_rate_id)
AND (zl.tax_jurisdiction_code = ail.tax_jurisdiction_code
OR zl.tax_jurisdiction_code IS NULL)
AND zl.tax_rate = ail.tax_rate
AND zl.tax_amt = ail.amount
AND NVL(zl.reporting_only_flag, 'N') = 'N'
AND NVL(zl.self_assessed_flag, 'N') = 'N'
AND NVL(zl.cancel_flag, 'N') = NVL(ail.cancelled_flag,'N')
AND zl.summary_tax_line_id <> ail.summary_tax_line_id
) */
AND rownum = 1;
FND_MESSAGE.SET_TOKEN('ERROR', 'Summary Tax Line Deleted by EBTax. This would cause orphan distributions.');
insert into ap_errors_gt(invoice_id , message_text) values(P_Invoice_Header_Rec.Invoice_id , 'Summary Tax Line Deleted by EBTax. This would cause orphan distributions.');
SELECT
approval_workflow_flag,
awt_include_tax_amt,
disc_is_inv_less_tax_flag,
base_currency_code,
combined_filing_flag,
income_tax_region_flag,
income_tax_region
INTO
l_wfapproval_flag,
l_awt_include_tax_amt,
l_disc_is_inv_less_tax_flag,
l_base_currency_code,
l_combined_filing_flag,
l_income_tax_region_flag,
l_income_tax_region
FROM ap_system_parameters_all
WHERE org_id = P_Invoice_Header_Rec.org_id;
l_debug_info := 'Update existing exclusive tax lines';
UPDATE ap_invoice_lines_all ail
SET
(-- Bug 10157751 ail.description,
ail.amount,
ail.base_amount,
ail.discarded_flag, --Bug9346774 Changed From Canceled_flag to discarded_flag
ail.last_updated_by,
ail.last_update_login,
ail.last_update_date,
ail.tax_regime_code,
ail.tax,
ail.tax_jurisdiction_code,
ail.tax_status_code,
ail.tax_rate_id,
ail.tax_rate_code,
ail.tax_rate,
ail.generate_dists) =
(
SELECT
/* Bug 10157751
DECODE( ail.line_source,
'MANUAL LINE ENTRY', ail.description,
'IMPORTED' , ail.description,
zls.tax_regime_code||' - '||zls.tax ), -- description : Bug 9383712 - Added DECODE
*/
zls.tax_amt, -- amount
zls.tax_amt_funcl_curr, -- base_amount
zls.cancel_flag, -- cancelled_flag
l_user_id, -- last_updated_by
l_login_id, -- last_update_login
sysdate, -- last_update_date /*Bug10101457: replaced l_sysdate with sysdate*/
zls.tax_regime_code, -- tax_regime_code
zls.tax, -- tax
zls.tax_jurisdiction_code, -- tax_jurisdiction_code
zls.tax_status_code, -- tax_status_code
zls.tax_rate_id, -- tax_rate_id
zls.tax_rate_code, -- tax_rate_code
zls.tax_rate, -- tax_rate
DECODE(ail.generate_dists,'D','D','Y') -- generate_dists bug 5460342
FROM zx_lines_summary zls
WHERE zls.summary_tax_line_id = ail.summary_tax_line_id
AND nvl(zls.reporting_only_flag, 'N') = 'N'
)
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND EXISTS
(SELECT ls.summary_tax_line_id
FROM zx_lines_summary ls
WHERE ls.summary_tax_line_id = ail.summary_tax_line_id
AND ls.trx_id = ail.invoice_id
AND NVL(ls.tax_amt_included_flag, 'N') = 'N'
AND NVL(ls.self_assessed_flag, 'N') = 'N'
AND NVL(ls.reporting_only_flag, 'N') = 'N');
for manual tax lines and tax only lines so that we do not delete and regenerate the tax lines . */
-------------------------------------------------------------------
l_debug_info := 'Update summary tax line id for manual tax lines';
UPDATE ap_invoice_lines_all ail
SET ail.summary_tax_line_id = (SELECT zl.summary_tax_line_id
FROM zx_lines zl
WHERE zl.application_id = 200
AND zl.entity_code = 'AP_INVOICES'
AND zl.event_class_code IN
('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zl.trx_id = ail.invoice_id
AND zl.tax_regime_code = ail.tax_regime_code
AND zl.tax = ail.tax
AND zl.tax_status_code = ail.tax_status_code
AND (zl.tax_rate_code = ail.tax_rate_code OR zl.tax_rate_id = ail.tax_rate_id)
AND (zl.tax_jurisdiction_code = ail.tax_jurisdiction_code OR zl.tax_jurisdiction_code IS NULL)
AND zl.tax_rate = ail.tax_rate
AND zl.manually_entered_flag = 'Y'
AND nvl(zl.reporting_only_flag, 'N') = 'N'
AND nvl(zl.self_assessed_flag, 'N') = 'N'
AND nvl(zl.cancel_flag, 'N') = nvl(ail.cancelled_flag,'N')
AND zl.proration_code = 'REGULAR_IMPORT' --Bug8524286
AND rownum = 1 )
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND ail.line_source in ('MANUAL LINE ENTRY','IMPORTED')
AND ail.summary_tax_line_id IS NULL
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_lines_all ail2
WHERE ail2.invoice_id = ail.invoice_id
AND ail2.line_number <> ail.line_number
AND ail2.tax_regime_code = ail.tax_regime_code
AND ail2.tax = ail.tax
AND ail2.tax_status_code = ail.tax_status_code
AND (ail2.tax_rate_code = ail.tax_rate_code OR ail2.tax_rate_id = ail.tax_rate_id)
AND (ail2.tax_jurisdiction_code = ail.tax_jurisdiction_code OR ail2.tax_jurisdiction_code IS NULL)
AND ail2.tax_rate = ail.tax_rate
AND ail2.line_type_lookup_code = 'TAX'
AND ail2.line_source in ('MANUAL LINE ENTRY','IMPORTED')
AND NVL(ail.prorate_across_all_items,'N') = NVL(ail2.prorate_across_all_items,'N')); --Bug7331216 --Bug9113046
l_debug_info := 'Delete exclusive tax lines if required';
DELETE ap_invoice_lines_all ail
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND NOT EXISTS (SELECT ls.summary_tax_line_id
FROM zx_lines_summary ls
WHERE ls.summary_tax_line_id = ail.summary_tax_line_id
AND ls.trx_id = ail.invoice_id
AND NVL(ls.tax_amt_included_flag, 'N') = 'N'
AND NVL(ls.self_assessed_flag, 'N') = 'N'
AND NVL(ls.reporting_only_flag, 'N') = 'N');
DELETE FROM ap_allocation_rules ar
WHERE ar.invoice_id = p_invoice_header_rec.invoice_id
AND NOT EXISTS
(SELECT 'y'
FROM ap_invoice_lines_all l
WHERE l.invoice_id = ar.invoice_id
AND l.line_number = ar.chrg_invoice_line_number);
DELETE FROM ap_allocation_rule_lines arl
WHERE arl.invoice_id = p_invoice_header_rec.invoice_id
AND NOT EXISTS
(SELECT 'y'
FROM ap_invoice_lines_all l
WHERE l.invoice_id = arl.invoice_id
AND l.line_number = arl.chrg_invoice_line_number);
l_debug_info := 'Insert exclusive tax lines';
INSERT INTO ap_invoice_lines_all (
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,
control_amount,
assessable_value,
total_rec_tax_amount,
total_nrec_tax_amount,
total_rec_tax_amt_funcl_curr,
total_nrec_tax_amt_funcl_curr,
included_tax_amount,
primary_intended_use,
ship_to_location_id,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
trx_business_category,
summary_tax_line_id,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_code_id,
pay_awt_group_id) --Bug8345264
SELECT
P_Invoice_Header_Rec.Invoice_Id, -- invoice_id
(SELECT NVL(MAX(ail2.line_number),0)
FROM ap_invoice_lines_all ail2
WHERE ail2.invoice_id = zls.trx_id) + ROWNUM, -- line_number
'TAX', -- line_type_lookup_code
null, -- requester_id
NVL( AI.description, zls.tax_regime_code||' - '||zls.tax ), -- description Bug 10157751
'ETAX', -- line_source
P_Invoice_Header_Rec.org_id, -- org_id
null, -- line_group_number
null, -- inventory_item_id
null, -- item_description
null, -- serial_number
null, -- manufacturer
null, -- model_number
null, -- warranty_number
DECODE(NVL(zls.tax_only_line_flag, 'N'),
'Y', 'D',
'Y'), -- generate_dists
DECODE(zls.applied_to_trx_id,
null, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'), -- match_type
null, -- distribution_set_id
null, -- account_segment
null, -- balancing_segment
null, -- cost_center_segment
null, -- overlay_dist_code_concat
null, -- default_dist_ccid
'N', -- prorate_across_all_items
l_gl_date, -- accounting_date
DECODE(NVL(zls.tax_only_line_flag, 'N'),
'N', DECODE(zls.applied_to_trx_id,
null, null, l_period_name),
l_period_name), -- period_name
'N', -- deferred_acctg_flag
null, -- def_acctg_start_date
null, -- def_acctg_end_date
null, -- def_acctg_number_of_periods
null, -- def_acctg_period_type
P_Invoice_Header_Rec.set_of_books_id, -- set_of_books_id
zls.tax_amt, -- amount
DECODE(P_Invoice_Header_Rec.invoice_currency_code,
l_base_currency_code, NULL,
zls.tax_amt_funcl_curr), -- base_amount
null, -- rounding_amt
null, -- quantity_invoiced
null, -- unit_meas_lookup_code
null, -- unit_price
l_wfapproval_status, -- wfapproval_status
'N', -- discarded_flag
null, -- original_amount
null, -- original_base_amount
null, -- original_rounding_amt
'N', -- cancelled_flag
DECODE(ap.type_1099,
'','',
DECODE(l_combined_filing_flag,
'N', '',
DECODE(l_income_tax_region_flag,
'Y', aps.state,
l_income_tax_region))), -- income_tax_region
ap.type_1099, -- type_1099
null, -- stat_amount
zls.applied_from_trx_id, -- prepay_invoice_id
zls.applied_from_line_id, -- prepay_line_number
prepay.invoice_includes_prepay_flag, -- invoice_includes_prepay_flag
zls.adjusted_doc_trx_id, -- corrected_inv_id
-- zls.adjusted_doc_line_id, -- corrected_line_number
null, -- corrected_line_number
null, -- po_header_id
null, -- po_line_id
null, -- po_release_id
null, -- po_line_location_id
null, -- po_distribution_id
zls.applied_to_trx_id, -- rcv_transaction_id
'N', -- final_match_flag
null, -- assets_tracking_flag
null, -- asset_book_type_code
null, -- asset_category_id
null, -- project_id
null, -- task_id
null, -- expenditure_type
null, -- expenditure_item_date
null, -- expenditure_organization_id
null, -- pa_quantity
null, -- pa_cc_ar_invoice_id
null, -- pa_cc_ar_invoice_line_num
null, -- pa_cc_processed_code
null, -- award_id
null, -- awt_group_id -- bug9035846
/* DECODE(l_awt_include_tax_amt,
'N', null,
DECODE(zls.applied_from_trx_id,
null, P_Invoice_Header_Rec.awt_group_id,
prepay.awt_group_id)), -- awt_group_id */
null, -- reference_1
null, -- reference_2
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
null, -- merchant_document_number
null, -- merchant_name
null, -- merchant_reference
null, -- merchant_tax_reg_number
null, -- merchant_taxpayer_id
null, -- country_of_supply
null, -- credit_card_trx_id
null, -- company_prepaid_invoice_id
null, -- cc_reversal_flag
sysdate, -- creation_date /*Bug10101457: replaced l_sysdate with sysdate*/
l_user_id, -- created_by
l_user_id, -- last_updated_by
sysdate, -- last_update_date /*Bug10101457: replaced l_sysdate with sysdate*/
l_login_id, -- last_update_login
null, -- program_application_id
null, -- program_id
null, -- program_update_date
null, -- request_id
zls.attribute_category, -- attribute_category
zls.attribute1, -- attribute1
zls.attribute2, -- attribute2
zls.attribute3, -- attribute3
zls.attribute4, -- attribute4
zls.attribute5, -- attribute5
zls.attribute6, -- attribute6
zls.attribute7, -- attribute7
zls.attribute8, -- attribute8
zls.attribute9, -- attribute9
zls.attribute10, -- attribute10
zls.attribute11, -- attribute11
zls.attribute12, -- attribute12
zls.attribute13, -- attribute13
zls.attribute14, -- attribute14
zls.attribute15, -- attribute15
zls.global_attribute_category, -- global_attribute_category
zls.global_attribute1, -- global_attribute1
zls.global_attribute2, -- global_attribute2
zls.global_attribute3, -- global_attribute3
zls.global_attribute4, -- global_attribute4
zls.global_attribute5, -- global_attribute5
zls.global_attribute6, -- global_attribute6
zls.global_attribute7, -- global_attribute7
zls.global_attribute8, -- global_attribute8
zls.global_attribute9, -- global_attribute9
zls.global_attribute10, -- global_attribute10
zls.global_attribute11, -- global_attribute11
zls.global_attribute12, -- global_attribute12
zls.global_attribute13, -- global_attribute13
zls.global_attribute14, -- global_attribute14
zls.global_attribute15, -- global_attribute15
zls.global_attribute16, -- global_attribute16
zls.global_attribute17, -- global_attribute17
zls.global_attribute18, -- global_attribute18
zls.global_attribute19, -- global_attribute19
zls.global_attribute20, -- global_attribute20
null, -- control_amount
null, -- assessable_value
null, -- total_rec_tax_amount
null, -- total_nrec_tax_amount
null, -- total_rec_tax_amt_funcl_curr
null, -- total_nrec_tax_amt_funcl_curr
null, -- included_tax_amount
null, -- primary_intended_use
null, -- ship_to_location_id
null, -- product_type
null, -- product_category
null, -- product_fisc_classification
null, -- user_defined_fisc_class
null, -- trx_business_category
zls.summary_tax_line_id, -- summary_tax_line_id
zls.tax_regime_code, -- tax_regime_code
zls.tax, -- tax
zls.tax_jurisdiction_code, -- tax_jurisdiction_code
zls.tax_status_code, -- tax_status_code
zls.tax_rate_id, -- tax_rate_id
zls.tax_rate_code, -- tax_rate_code
zls.tax_rate, -- tax_rate
null, -- tax_code_id
null -- pay_awt_group_id -- bug9035846
/* DECODE(l_awt_include_tax_amt,
'N', null,
DECODE(zls.applied_from_trx_id,
null, P_Invoice_Header_Rec.pay_awt_group_id,
prepay.pay_awt_group_id)) -- pay_awt_group_id Bug8345264 */
FROM ap_invoices_all ai,
ap_suppliers ap,
ap_supplier_sites_all aps,
zx_lines_summary zls,
ap_invoice_lines_all prepay
WHERE ai.invoice_id = p_invoice_header_rec.invoice_id
AND ai.vendor_id = ap.vendor_id
AND ai.vendor_site_id = aps.vendor_site_id
AND zls.application_id = 200
AND zls.entity_code = 'AP_INVOICES'
AND zls.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zls.trx_id = ai.invoice_id
AND NVL(zls.tax_amt_included_flag, 'N') = 'N'
AND NVL(zls.self_assessed_flag, 'N') = 'N'
AND NVL(zls.reporting_only_flag, 'N') = 'N'
AND zls.applied_from_trx_id = prepay.invoice_id(+)
AND zls.applied_from_line_id = prepay.line_number(+)
AND NOT EXISTS (SELECT il.summary_tax_line_id
FROM ap_invoice_lines_all il
WHERE il.invoice_id = ai.invoice_id
AND il.summary_tax_line_id = zls.summary_tax_line_id)
AND EXISTS
(SELECT 'Recoupment Exists'
FROM ZX_LINES ZL
WHERE ZL.application_id = ZLS.application_id
AND ZL.entity_code = ZLS.entity_code
AND ZL.event_class_code = ZLS.event_class_code
AND ZL.trx_id = ZLS.trx_id
AND ((nvl(ZL.tax_only_line_flag,'N') <> 'Y'
and sign(ZL.TRX_LINE_ID) <> -1)
OR nvl(ZL.tax_only_line_flag,'N') = 'Y')
AND ZL.SUMMARY_TAX_LINE_ID = ZLS.SUMMARY_TAX_LINE_ID);
l_debug_info := 'Update Inclusive tax amount';
UPDATE ap_invoice_lines_all ail
SET ail.included_tax_amount =
(SELECT /*+ index(ZL ZX_LINES_U1) */SUM(NVL(zl.tax_amt, 0))
FROM zx_lines zl
WHERE zl.application_id = 200
AND zl.entity_code = 'AP_INVOICES'
AND zl.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zl.trx_id = ail.invoice_id
AND zl.trx_line_id = ail.line_number
AND NVL(zl.self_assessed_flag, 'N') = 'N'
AND NVL(zl.reporting_only_flag, 'N') = 'N'
AND NVL(zl.tax_amt_included_flag, 'N') = 'Y')
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code NOT IN ('TAX', 'AWT');
IF NOT AP_ALLOCATION_RULES_PKG.insert_tax_allocations (
P_Invoice_Header_Rec.invoice_id,
L_Item_Line.line_number,
P_error_code ) THEN
NULL;
l_debug_info := 'Update total_tax_amount and self_assessed tax';
UPDATE ap_invoices_all ai
SET (ai.total_tax_amount,
ai.self_assessed_tax_amount) =
(SELECT SUM(DECODE(NVL(zls.self_assessed_flag, 'N'),
'N', case when exists (SELECT 'Prepay App Exists'
FROM ap_invoice_lines_all prepay
WHERE prepay.invoice_id = zls.trx_id
AND prepay.line_type_lookup_code = 'PREPAY'
AND prepay.prepay_invoice_id = zls.applied_from_trx_id
AND prepay.prepay_line_number = zls.applied_from_line_id
AND prepay.invoice_includes_prepay_flag = 'Y'
AND (prepay.discarded_flag is null
or prepay.discarded_flag = 'N')) THEN
0
ELSE NVL(zls.tax_amt, 0) end,
0)),
SUM(DECODE(NVL(zls.self_assessed_flag, 'N'),
'Y', NVL(zls.tax_amt, 0),
0))
FROM zx_lines_summary zls
WHERE zls.application_id = 200
AND zls.entity_code = 'AP_INVOICES'
AND zls.event_class_code IN
('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zls.trx_id = ai.invoice_id
AND NVL(zls.reporting_only_flag, 'N') = 'N')
WHERE ai.invoice_id = P_Invoice_Header_Rec.invoice_id
RETURNING ai.total_tax_amount, ai.self_assessed_tax_amount
INTO l_total_tax_amount, l_self_assessed_tax_amt;
UPDATE ap_invoices_all ai
SET ai.total_tax_amount = (nvl(ai.total_tax_amount,0) +
(SELECT NVL(sum(NVL(ail.amount,0)),0)
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = ai.invoice_id
AND line_type_lookup_code ='RETROTAX'))
WHERE ai.invoice_id = P_Invoice_Header_Rec.invoice_id
RETURNING ai.total_tax_amount
INTO l_total_tax_amount;
l_debug_info := 'Update tax_already_calculated_flag';
UPDATE ap_invoice_lines_all ail
SET ail.tax_already_calculated_flag = 'Y'
WHERE ail.invoice_id = P_invoice_header_rec.invoice_id
AND ail.line_type_lookup_code NOT IN ('TAX', 'AWT')
AND NVL(ail.tax_already_calculated_flag, 'N') = 'N';
we would need to pass event_type as UPDATED.
AND EXISTS
( SELECT zl.tax_line_id
FROM zx_lines zl
WHERE zl.trx_id = ail.invoice_id
AND zl.trx_line_id = ail.line_number
AND zl.application_id = 200
AND zl.entity_code = 'AP_INVOICES'
AND nvl(zl.reporting_only_flag, 'N') = 'N');
l_debug_info := 'Update Invoice Includes Prepay Flag';
UPDATE ap_invoice_lines_all tax
SET tax.invoice_includes_prepay_flag = CASE WHEN EXISTS (SELECT 'Prepay App Exists'
FROM ap_invoice_lines_all prepay
WHERE prepay.invoice_id = tax.invoice_id
AND prepay.line_type_lookup_code = 'PREPAY'
AND prepay.prepay_invoice_id = tax.prepay_invoice_id
AND prepay.prepay_line_number = tax.prepay_line_number
AND prepay.invoice_includes_prepay_flag = 'Y'
AND (prepay.discarded_flag is null or
prepay.discarded_flag = 'N')) THEN
'Y'
ELSE
'N'
END /*added the case for bug 8638881*/
WHERE tax.invoice_id = P_Invoice_Header_Rec.Invoice_Id
AND tax.line_type_lookup_code = 'TAX'
AND tax.prepay_invoice_id is not null;
| creation, update, or delete of existing distributions and TIPV and
| TERV distributions if required.
| It also handles the creation, update or delete of self-assessed
| distributions.
|
| PARAMETERS
| P_Invoice_Header_Rec - Header info
| P_All_Error_Messages - Should API return 1 error message or allow
| calling point to get them from message stack
| P_error_code - Error code to be returned
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| DATE Author Action
| 23-OCT-2003 SYIDNER Created
| 05-MAR-2004 SYIDNER Included changes for returning of tax
| distributions related to prepayment
| application distributions (PREPAY)
| In this case only the primary distribution
| should be created for the total value of
| the distribution (same as eTax.) No tax
| variances will be created for PREPAY type
| distributions
*============================================================================*/
FUNCTION Return_Tax_Distributions(
P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(2000);
SELECT /*+ leading(GT) index(GT ZX_TRX_HEADERS_GT_U1) cardinality(GT, 1) use_nl(AID) */ -- 9485828
detail_tax_dist_id,aid.summary_tax_line_id , invoice_id
FROM ap_invoice_distributions_all aid,
zx_trx_headers_gt gt
WHERE aid.line_type_lookup_code IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV')
AND (aid.accounting_event_id IS NOT NULL OR
NVL(aid.match_status_flag,'N') IN ('A','T') OR
NVL(aid.posted_flag,'N') ='Y' OR
NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X'))
AND gt.application_id = AP_ETAX_PKG.AP_APPLICATION_ID
AND gt.entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
AND gt.event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
AND gt.trx_id = aid.invoice_id
AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions' -- 9485828
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
AND NVL(SELF_ASSESSED_FLAG, 'N') = 'N');
SELECT /*+ leading(GT) index(GT ZX_TRX_HEADERS_GT_U1) cardinality(GT, 1) use_nl(AID) */ -- 9485828
detail_tax_dist_id, aid.summary_tax_line_id , invoice_id
FROM ap_self_assessed_tax_dist_all aid,
zx_trx_headers_gt gt
WHERE aid.line_type_lookup_code IN ('NONREC_TAX','REC_TAX')
AND (aid.accounting_event_id IS NOT NULL OR
NVL(aid.match_status_flag,'N') IN ('A','T') OR
NVL(aid.posted_flag,'N') ='Y' OR
NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X'))
AND gt.application_id = AP_ETAX_PKG.AP_APPLICATION_ID
AND gt.entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
AND gt.event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
AND gt.trx_id = aid.invoice_id
AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions' -- 9485828
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
AND NVL(SELF_ASSESSED_FLAG, 'N') = 'Y');
CURSOR insert_tax_dist IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
decode(NVL(zl.tax_only_line_flag,'N'),
'Y',parent_tax_line.default_dist_ccid,
parent_taxable_dist.dist_code_combination_id) dist_code_combination_id, ---for 6010950
-- this ccid is a temporary value that will be used if other
-- conditions are met before inserting the tax distribution.
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'REC_TAX',
'N', 'NONREC_TAX') line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
-- included the decode as part of the prepayment changes.
-- since for prepayment tax variances will not be created,
-- the dist amount should be the total including variances
-- bug 8317515: modify start
--BUG11656125 START--
DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',zd.rec_nrec_tax_amt,DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', zd.rec_nrec_tax_amt,
decode(parent_taxable_dist.line_type_lookup_code, 'PREPAY', zd.rec_nrec_tax_amt,
decode(nvl(zd.recoverable_flag,'N'),
'Y', zd.rec_nrec_tax_amt,
NVL(zd.rec_nrec_tax_amt, 0) -
get_tv(zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id ))))) amount, -- bug 9231678
ap_utilities_pkg.ap_round_currency(
DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',zd.rec_nrec_tax_amt_funcl_curr,DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', zd.rec_nrec_tax_amt_funcl_curr,
decode(parent_taxable_dist.line_type_lookup_code, 'PREPAY', zd.rec_nrec_tax_amt_funcl_curr,
decode(nvl(zd.recoverable_flag,'N'),
'Y', zd.rec_nrec_tax_amt_funcl_curr,
zd.rec_nrec_tax_amt_funcl_curr - --Bug12835115
(get_tv_base
(zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt, nvl(zd.ref_doc_per_unit_nrec_tax_amt,0),
zd.per_trx_curr_unit_nr_amt, zd.ref_per_trx_curr_unit_nr_amt, nvl(zd.currency_conversion_rate, 1),
zd.ref_doc_curr_conv_rate, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, asp.base_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id )+
find_val(zd.trx_line_dist_id)*
get_terv
(zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, zd.applied_to_doc_curr_conv_rate,
NULL, zd.per_unit_nrec_tax_amt, nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, parent_item_line.line_source, --Bug10416960
asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id,zd.unit_price)))))), --Bug10416960
asp.base_currency_code) base_amount, -- bug 9231678
--BUG11656125 END--
-- bug 8317515: modify end
-- included the decode as part of the prepayment changes.
-- since for prepayments tax variances will not be created,
-- the base_amount should be the total including variances
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.description,
'N', parent_tax_line.description) description,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(parent_item_line.type_1099,
NULL, NULL,
parent_item_line.income_tax_region),
'N', DECODE(parent_tax_line.type_1099,
NULL, NULL,
parent_tax_line.income_tax_region)) income_tax_region,
parent_taxable_dist.po_distribution_id po_distribution_id, --change for bug 8713009
-- now rec_tax dist will also have po_dist_id stamped on it
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.type_1099,
'N', parent_tax_line.type_1099) type_1099,
zd.attribute1 attribute1,
zd.attribute10 attribute10,
zd.attribute11 attribute11,
zd.attribute12 attribute12,
zd.attribute13 attribute13,
zd.attribute14 attribute14,
zd.attribute15 attribute15,
zd.attribute2 attribute2,
zd.attribute3 attribute3,
zd.attribute4 attribute4,
zd.attribute5 attribute5,
zd.attribute6 attribute6,
zd.attribute7 attribute7,
zd.attribute8 attribute8,
zd.attribute9 attribute9,
zd.attribute_category attribute_category,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_item_date,parent_taxable_dist.expenditure_item_date)) expenditure_item_date, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_organization_id,parent_taxable_dist.expenditure_organization_id)) expenditure_organization_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_type,parent_taxable_dist.expenditure_type)) expenditure_type, /* bug 11076722 modified bug 10155425 */
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E',
'N', decode(NVL(ZL.tax_only_line_flag,'N'),'Y','N',decode(parent_taxable_dist.pa_addition_flag,'Y','N',parent_taxable_dist.pa_addition_flag))) pa_addition_flag, /*Bug 14248119*/ /*Bug 13106066:added decode logic*/
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.pa_quantity,parent_taxable_dist.pa_quantity)) pa_quantity, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_accounting_context) project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.project_id,parent_taxable_dist.project_id)) project_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.task_id,parent_taxable_dist.task_id)) task_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(parent_tax_line.awt_group_id,
null,DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.awt_group_id, --Bug8334059
ap_etax_utility_pkg.Get_Prepay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.awt_group_id),
NULL),
parent_tax_line.awt_group_id), --Bug6648050 --bug 9035846 -- bug9117319
--Bug8334059
NULL),
NULL) awt_group_id,
--Bug6505640 Populating DFF's from Invoice distributions instead of Tax dists
parent_taxable_dist.global_attribute_category global_attribute_category,
parent_taxable_dist.global_attribute1 global_attribute1,
parent_taxable_dist.global_attribute2 global_attribute2,
parent_taxable_dist.global_attribute3 global_attribute3,
parent_taxable_dist.global_attribute4 global_attribute4,
parent_taxable_dist.global_attribute5 global_attribute5,
parent_taxable_dist.global_attribute6 global_attribute6,
parent_taxable_dist.global_attribute7 global_attribute7,
parent_taxable_dist.global_attribute8 global_attribute8,
parent_taxable_dist.global_attribute9 global_attribute9,
parent_taxable_dist.global_attribute10 global_attribute10,
parent_taxable_dist.global_attribute11 global_attribute11,
parent_taxable_dist.global_attribute12 global_attribute12,
parent_taxable_dist.global_attribute13 global_attribute13,
parent_taxable_dist.global_attribute14 global_attribute14,
parent_taxable_dist.global_attribute15 global_attribute15,
parent_taxable_dist.global_attribute16 global_attribute16,
parent_taxable_dist.global_attribute17 global_attribute17,
parent_taxable_dist.global_attribute18 global_attribute18,
parent_taxable_dist.global_attribute19 global_attribute19,
parent_taxable_dist.global_attribute20 global_attribute20,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.award_id,parent_taxable_dist.award_id)) award_id, /* bug 11076722 modified bug 10155425 */
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'),
'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
--Bug 8910531
'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.merchant_document_number merchant_document_number, --Bug14658408: Start
parent_taxable_dist.merchant_name merchant_name,
parent_taxable_dist.merchant_reference merchant_reference,
parent_taxable_dist.merchant_tax_reg_number merchant_tax_reg_number,
parent_taxable_dist.merchant_taxpayer_id merchant_taxpayer_id, --Bug14658408: End
parent_taxable_dist.cancellation_flag cancellation_flag,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', zd.trx_line_id,
'N', nvl(parent_tax_line.line_number,
parent_taxable_dist.invoice_line_number)) invoice_line_number,
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
zd.func_curr_rounding_adjustment) rounding_amt,
-- the rounding amount in the non-recoverable case will be populated
-- to the primary distribution later in the cycle.
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id,
NULL, 'CANDIDATE', 'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
zd.summary_tax_line_id summary_tax_line_id,
null extra_po_erv,
zd.taxable_amt taxable_amount,
zd.taxable_amt_funcl_curr taxable_base_amount,
pd.accrue_on_receipt_flag accrue_on_receipt_flag,
asp.allow_flex_override_flag allow_flex_override_flag,
fsp.purch_encumbrance_flag purch_encumbrance_flag,
asp.org_id org_id,
zd.tax_regime_id tax_regime_id,
zd.tax_id tax_id,
zd.tax_status_id tax_status_id,
zl.tax_jurisdiction_id tax_jurisdiction_id,
'N' par_dist_assets_tracking_flag, -- Bug 13821160
parent_taxable_dist.cancellation_flag parent_dist_cancellation_flag,
parent_taxable_dist.reversal_flag parent_dist_reversal_flag,
parent_taxable_dist.parent_reversal_id parent_dist_parent_reversal_id,
zd.reversed_tax_dist_id reversed_tax_dist_id,
zd.adjusted_doc_tax_dist_id adjusted_doc_tax_dist_id,
zd.applied_from_tax_dist_id applied_from_tax_dist_id,
-- the prepay_distribution_id will be populated with
-- invoice_distribution_id for the associated rec or
-- nonrec tax distributions
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', AP_ETAX_UTILITY_PKG.get_dist_id_for_tax_dist_id
(zd.applied_from_tax_dist_id),
decode(parent_taxable_dist.line_type_lookup_code,
'PREPAY', AP_ETAX_UTILITY_PKG.get_dist_id_for_tax_dist_id
(zd.applied_from_tax_dist_id), NULL)) prepay_distribution_id,
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', DECODE(NVL(zd.recoverable_flag, 'N'),
'N', (zd.prd_tax_amt - zd.rec_nrec_tax_amt), NULL),
NULL) prepay_tax_diff_amount,
ai.invoice_id invoice_id,
ai.batch_id batch_id,
ai.set_of_books_id set_of_books_id,
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(parent_tax_line.pay_awt_group_id,
NULL, DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y',DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.pay_awt_group_id,
ap_etax_utility_pkg.Get_Prepay_Pay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.pay_awt_group_id),
NULL),
parent_tax_line.pay_awt_group_id), --Bug6648050 -- bug 9035846 -- bug9117319
NULL),
NULL) pay_awt_group_id, --Bug8345264
zd.account_source_tax_rate_id account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_tax_line,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND NVL(zd.self_assessed_flag, 'N') = 'N'
AND NVL(zl.reporting_only_flag, 'N') = 'N'
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id (+)
AND zd.trx_id = parent_tax_line.invoice_id (+)
AND zd.summary_tax_line_id = parent_tax_line.summary_tax_line_id (+)
AND zd.trx_id = parent_item_line.invoice_id (+)
AND zd.trx_line_id = parent_item_line.line_number (+)
AND zd.trx_id = parent_taxable_dist.invoice_id (+)
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id (+)
--BUG11656125 START--
AND (zd.ref_doc_application_id IS NULL
or (zd.ref_doc_application_id IS NOT NULL
and (nvl(zd.recoverable_flag, 'N') = 'Y'
or (ai.invoice_type_lookup_code = 'PREPAYMENT' and nvl(zd.recoverable_flag, 'N') = 'N' )
or (parent_taxable_dist.prepay_distribution_id IS NOT NULL)
or ( zd.rec_nrec_tax_amt = 0 and nvl(zd.recoverable_flag, 'N') = 'N'
/* Commented for Bug 6906867*/ /* and zd.rec_nrec_rate = 100*/ ) -- added the condition for bug fix 6695517
or zd.rec_nrec_tax_amt <> ap_etax_utility_pkg.get_tv
(zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id))))
-- AND ((zd.recoverable_flag = 'N' AND zd.rec_nrec_rate<>0) OR zd.recoverable_flag = 'Y') --bug 6350100 -- commented out the condition for bug fix 6695517
--BUG11656125 END --
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = zd.trx_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = zd.trx_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('TRV','TERV','TIPV')
AND (NVL(aid.posted_flag,'N') = 'Y' OR
aid.accounting_event_id IS NOT NULL OR
NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X') OR
--Bug7419940 Dont allow any insert from posted / frozen variacnes it should be reversed
NVL(aid.reversal_flag,'N') = 'Y'))
--Bug8481532 Added Condition To exclude reversed distribuion from getting changed
AND (parent_taxable_dist.dist_match_type is null
or nvl(zd.recoverable_flag, 'N') = 'Y'
or parent_taxable_dist.dist_match_type <> 'PRICE_CORRECTION')
AND (parent_taxable_dist.prepay_distribution_id IS NULL
or (parent_taxable_dist.prepay_distribution_id IS NOT NULL
and (parent_item_line.line_number IS NOT NULL
or zd.trx_line_id = (select -1 * (aid.invoice_id || aid.invoice_line_number || parent_taxable_dist.invoice_line_number)
from ap_invoice_distributions_all aid
where aid.invoice_distribution_id = parent_taxable_dist.prepay_distribution_id))))
-- Bug 7462582
-- Reverting the fixes done in bugs 6805527 and 7389822 as Etax bug 7515711 will take care of these fixes.
/* Added by schitlap, epajaril to fix the issue in Bug 6805527 */
/*AND (nvl(parent_taxable_dist.reversal_flag, 'N') <> 'Y'
OR zd.reversed_tax_dist_id IS NULL) -- 7389822*/
ORDER BY detail_tax_dist_id ; --bug 8359426 --bug 9666759 Removed the desc clause
CURSOR insert_tax_variances IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
(CASE dist.line_type
WHEN 'TERV' THEN
DECODE(pd.destination_type_code, 'EXPENSE', pd.code_combination_id,
parent_taxable_dist.dist_code_combination_id)
ELSE
DECODE(pd.destination_type_code, 'EXPENSE',
DECODE(pd.accrue_on_receipt_flag, 'Y', pd.code_combination_id,
parent_taxable_dist.dist_code_combination_id),
pd.variance_account_id)
END) dist_code_combination_id,
(CASE dist.line_type
WHEN 'TIPV' THEN 'TIPV'
WHEN 'TERV' THEN 'TERV'
WHEN 'TRV' THEN 'TRV'
END) line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
(CASE dist.line_type
WHEN 'TIPV' THEN -- bug 10291717
ap_etax_utility_pkg.get_tipv(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type , zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)
WHEN 'TRV' THEN
(ap_etax_utility_pkg.get_tv(
zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id) -
ap_etax_utility_pkg.get_tipv(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, parent_item_line.match_type , zd.trx_line_dist_id , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id))
WHEN 'TERV' THEN 0
END) amount,
(CASE dist.line_type
WHEN 'TIPV' THEN
ap_utilities_pkg.ap_round_currency( -- bug 10291717
ap_etax_utility_pkg.get_tipv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, NULL,
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, asp.base_currency_code, --Bug10416960
parent_item_line.match_type , zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)
,asp.base_currency_code)
WHEN 'TRV' THEN
ap_utilities_pkg.ap_round_currency(
(ap_etax_utility_pkg.get_tv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, nvl(zd.currency_conversion_rate,1),
zd.ref_doc_curr_conv_rate, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, asp.base_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id)
- (DECODE(find_val(zd.trx_line_dist_id),0,1,0)*ap_etax_utility_pkg.get_terv(
zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate,
zd.applied_to_doc_curr_conv_rate, NULL, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id, zd.unit_price)) --Bug10416960
- ap_etax_utility_pkg.get_tipv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, NULL,
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, asp.base_currency_code, --Bug10416960
parent_item_line.match_type , zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)),
asp.base_currency_code) -- bug 9231678
WHEN 'TERV' THEN
ap_utilities_pkg.ap_round_currency
(ap_etax_utility_pkg.get_terv(
zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate,
zd.applied_to_doc_curr_conv_rate, NULL, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id, zd.unit_price), asp.base_currency_code) --Bug10416960
END) base_amount,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.description,
'N', parent_tax_line.description) description,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(parent_item_line.type_1099,
NULL, NULL, parent_item_line.income_tax_region),
'N', DECODE(parent_tax_line.type_1099,
NULL, NULL, parent_tax_line.income_tax_region)) income_tax_region,
parent_taxable_dist.po_distribution_id po_distribution_id, -- change for bug 8713009
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.type_1099,
'N', parent_tax_line.type_1099) type_1099,
zd.attribute1 attribute1,
zd.attribute10 attribute10,
zd.attribute11 attribute11,
zd.attribute12 attribute12,
zd.attribute13 attribute13,
zd.attribute14 attribute14,
zd.attribute15 attribute15,
zd.attribute2 attribute2,
zd.attribute3 attribute3,
zd.attribute4 attribute4,
zd.attribute5 attribute5,
zd.attribute6 attribute6,
zd.attribute7 attribute7,
zd.attribute8 attribute8,
zd.attribute9 attribute9,
zd.attribute_category attribute_category,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_item_date,parent_taxable_dist.expenditure_item_date)) expenditure_item_date, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_organization_id,parent_taxable_dist.expenditure_organization_id)) expenditure_organization_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_type,parent_taxable_dist.expenditure_type)) expenditure_type, /* bug 11076722 modified bug 10155425 */
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E',
'N', decode(parent_taxable_dist.pa_addition_flag,'Y','N',parent_taxable_dist.pa_addition_flag)) pa_addition_flag, /*bug13106066:Added decode logic*/
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.pa_quantity,parent_taxable_dist.pa_quantity)) pa_quantity, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_accounting_context) project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.project_id,parent_taxable_dist.project_id)) project_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.task_id,parent_taxable_dist.task_id)) task_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(parent_tax_line.awt_group_id,
null,DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.awt_group_id, --Bug8334059
ap_etax_utility_pkg.Get_Prepay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.awt_group_id),
NULL),
parent_tax_line.awt_group_id), --Bug6648050 --bug 9035846 -- bug9117319
--Bug8334059 --bug9200123
NULL),
NULL) awt_group_id,
zd.global_attribute_category global_attribute_category,
zd.global_attribute1 global_attribute1,
zd.global_attribute2 global_attribute2,
zd.global_attribute3 global_attribute3,
zd.global_attribute4 global_attribute4,
zd.global_attribute5 global_attribute5,
zd.global_attribute6 global_attribute6,
zd.global_attribute7 global_attribute7,
zd.global_attribute8 global_attribute8,
zd.global_attribute9 global_attribute9,
zd.global_attribute10 global_attribute10,
zd.global_attribute11 global_attribute11,
zd.global_attribute12 global_attribute12,
zd.global_attribute13 global_attribute13,
zd.global_attribute14 global_attribute14,
zd.global_attribute15 global_attribute15,
zd.global_attribute16 global_attribute16,
zd.global_attribute17 global_attribute17,
zd.global_attribute18 global_attribute18,
zd.global_attribute19 global_attribute19,
zd.global_attribute20 global_attribute20,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl( parent_tax_line.award_id,parent_taxable_dist.award_id)) award_id, /* bug 11076722 modified bug 10155425 */
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'),
'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
--Bug 8910531
'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.merchant_document_number merchant_document_number, --Bug14658408: Start
parent_taxable_dist.merchant_name merchant_name,
parent_taxable_dist.merchant_reference merchant_reference,
parent_taxable_dist.merchant_tax_reg_number merchant_tax_reg_number,
parent_taxable_dist.merchant_taxpayer_id merchant_taxpayer_id, --Bug14658408: End
parent_taxable_dist.cancellation_flag cancellation_flag,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', zd.trx_line_id,
'N', parent_tax_line.line_number) invoice_line_number,
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
NULL rounding_amt,
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id,
NULL, 'CANDIDATE', 'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
zd.summary_tax_line_id summary_tax_line_id,
NULL extra_po_erv,
NULL taxable_amount,
NULL taxable_base_amount,
pd.accrue_on_receipt_flag accrue_on_receipt_flag,
asp.allow_flex_override_flag allow_flex_override_flag,
fsp.purch_encumbrance_flag purch_encumbrance_flag,
asp.org_id org_id,
zd.tax_regime_id tax_regime_id,
zd.tax_id tax_id,
zd.tax_status_id tax_status_id,
zl.tax_jurisdiction_id tax_jurisdiction_id,
'N' par_dist_assets_tracking_flag, -- Bug 13821160
parent_taxable_dist.cancellation_flag parent_dist_cancellation_flag,
parent_taxable_dist.reversal_flag parent_dist_reversal_flag,
parent_taxable_dist.parent_reversal_id parent_dist_parent_reversal_id,
zd.reversed_tax_dist_id reversed_tax_dist_id,
zd.adjusted_doc_tax_dist_id adjusted_doc_tax_dist_id,
zd.applied_from_tax_dist_id applied_from_tax_dist_id,
NULL prepay_distribution_id,
NULL prepay_tax_diff_amount,
ai.invoice_id invoice_id,
ai.batch_id batch_id,
ai.set_of_books_id set_of_books_id,
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(parent_tax_line.pay_awt_group_id,
NULL, DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y',DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.pay_awt_group_id,
ap_etax_utility_pkg.Get_Prepay_Pay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.pay_awt_group_id),
NULL),
parent_tax_line.pay_awt_group_id), --Bug6648050 -- bug 9035846 -- bug9117319 --bug9200123
NULL),
NULL) pay_awt_group_id, --Bug8345264
zd.account_source_tax_rate_id account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_tax_line,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist,
ap_line_temp_gt dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND NVL(zd.recoverable_flag, 'N') = 'N'
AND NVL(zd.self_assessed_flag, 'N') = 'N'
AND NVL(zl.reporting_only_flag, 'N') = 'N'
--Bug9777752 Removed Outer join on PD
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id
--Bug9777752 Removed Outer join on PD
AND zd.trx_id = parent_tax_line.invoice_id(+)
AND zd.summary_tax_line_id = parent_tax_line.summary_tax_line_id(+)
AND zd.trx_id = parent_item_line.invoice_id(+)
AND zd.trx_line_id = parent_item_line.line_number(+)
AND zd.trx_id = parent_taxable_dist.invoice_id
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id
AND ai.invoice_type_lookup_code <> 'PREPAYMENT' --Bug11656125
AND parent_item_line.line_type_lookup_code(+) <> 'PREPAY'
AND parent_taxable_dist.line_type_lookup_code <> 'PREPAY'
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = zd.trx_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('TIPV', 'TERV', 'TRV'))
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = zd.trx_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code = 'NONREC_TAX'
AND (NVL(aid.posted_flag,'N') = 'Y' OR
aid.accounting_event_id IS NOT NULL OR
NVL(aid.encumbered_flag,'N') IN ('Y','D','W','X') OR
--Bug7419940 Dont allow any insert from posted / frozen variacnes it should be reversed
NVL(aid.reversal_flag,'N')='Y'))
--Bug8481532 Added Condition To exclude reversed distribuion from getting changed
AND ((dist.line_type = 'TIPV'
AND (ap_etax_utility_pkg.get_tipv
(zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, parent_item_line.match_type , zd.trx_line_dist_id , --bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id) <> 0
or parent_taxable_dist.dist_match_type = 'PRICE_CORRECTION'
))
OR
(dist.line_type = 'TRV'
and (ap_etax_utility_pkg.get_tv(
zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id)
- ap_etax_utility_pkg.get_tipv(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, parent_item_line.match_type , zd.trx_line_dist_id , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)) <> 0)
OR
(dist.line_type = 'TERV'
AND (ap_etax_utility_pkg.get_terv(
zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate,
zd.applied_to_doc_curr_conv_rate, NULL, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id, zd.unit_price)) <> 0)) --Bug10416960
-- bug 9231678 removed sign condition
ORDER BY detail_tax_dist_id;
CURSOR update_tax_dist IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
parent_taxable_dist.dist_code_combination_id dist_code_combination_id,
-- this ccid is a temporary value that will be used if other conditions
-- are met before inserting the tax distribution
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'REC_TAX',
'N', 'NONREC_TAX') line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
-- bug 8317515: modify start
--BUG11656125 START--
DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',zd.rec_nrec_tax_amt,DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', zd.rec_nrec_tax_amt,
decode(parent_taxable_dist.line_type_lookup_code, 'PREPAY', zd.rec_nrec_tax_amt,
decode(nvl(zd.recoverable_flag,'N'),
'Y', zd.rec_nrec_tax_amt,
(NVL(zd.rec_nrec_tax_amt, 0) -
get_tv(zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id)))))) amount, -- bug 9231678
ap_utilities_pkg.ap_round_currency(
DECODE(ai.invoice_type_lookup_code,'PREPAYMENT',zd.rec_nrec_tax_amt_funcl_curr,DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', zd.rec_nrec_tax_amt_funcl_curr,
decode(parent_taxable_dist.line_type_lookup_code, 'PREPAY', zd.rec_nrec_tax_amt_funcl_curr,
decode(nvl(zd.recoverable_flag,'N'),
'Y', zd.rec_nrec_tax_amt_funcl_curr,
(zd.rec_nrec_tax_amt_funcl_curr - --Bug12835115
(get_tv_base
(zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt, nvl(zd.ref_doc_per_unit_nrec_tax_amt,0),
zd.per_trx_curr_unit_nr_amt, zd.ref_per_trx_curr_unit_nr_amt, nvl(zd.currency_conversion_rate, 1),
zd.ref_doc_curr_conv_rate, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, asp.base_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id)+
find_val(zd.trx_line_dist_id)*get_terv
(zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, zd.applied_to_doc_curr_conv_rate,
NULL, zd.per_unit_nrec_tax_amt, nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, parent_item_line.line_source, --Bug10416960
asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.ref_per_trx_curr_unit_nr_amt ,
zd.rec_nrec_tax_dist_id,zd.unit_price))))))), --Bug10416960
asp.base_currency_code ) base_amount, -- bug 9231678
--BUG11656125 END--
-- bug 8317515: modify end
-- included the decode as part of the prepayment changes.
-- since for prepayment applic tax variances will not be created,
-- the base_amount should be the total including variances
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.description,
'N', parent_tax_line.description) description,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(parent_item_line.type_1099,
NULL, NULL,
parent_item_line.income_tax_region),
'N', DECODE(parent_tax_line.type_1099,
NULL, NULL,
parent_tax_line.income_tax_region)) income_tax_region,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.type_1099,
'N', parent_tax_line.type_1099) type_1099,
-- bug 6914575: modify start
-- Populating DFF's from Invoice distributions instead of Tax dists
aid.attribute1 attribute1,
aid.attribute10 attribute10,
aid.attribute11 attribute11,
aid.attribute12 attribute12,
aid.attribute13 attribute13,
aid.attribute14 attribute14,
aid.attribute15 attribute15,
aid.attribute2 attribute2,
aid.attribute3 attribute3,
aid.attribute4 attribute4,
aid.attribute5 attribute5,
aid.attribute6 attribute6,
aid.attribute7 attribute7,
aid.attribute8 attribute8,
aid.attribute9 attribute9,
aid.attribute_category attribute_category,
-- bug 6914575: modify end
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_item_date, parent_taxable_dist.expenditure_item_date)) expenditure_item_date, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_organization_id,parent_taxable_dist.expenditure_organization_id))
expenditure_organization_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.expenditure_type,parent_taxable_dist.expenditure_type)) expenditure_type, /* bug 11076722 modified bug 10155425 */
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E',
'N', parent_taxable_dist.pa_addition_flag) pa_addition_flag,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.pa_quantity,parent_taxable_dist.pa_quantity)) pa_quantity, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_accounting_context)
project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.project_id,parent_taxable_dist.project_id)) project_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', nvl(parent_tax_line.task_id,parent_taxable_dist.task_id)) task_id, /* bug 11076722 modified bug 10155425 */
aid.awt_group_id awt_group_id,
--Bug6505640 Populating DFF's from Invoice distributions instead of Tax dists
aid.global_attribute_category global_attribute_category,
aid.global_attribute1 global_attribute1,
aid.global_attribute2 global_attribute2,
aid.global_attribute3 global_attribute3,
aid.global_attribute4 global_attribute4,
aid.global_attribute5 global_attribute5,
aid.global_attribute6 global_attribute6,
aid.global_attribute7 global_attribute7,
aid.global_attribute8 global_attribute8,
aid.global_attribute9 global_attribute9,
aid.global_attribute10 global_attribute10,
aid.global_attribute11 global_attribute11,
aid.global_attribute12 global_attribute12,
aid.global_attribute13 global_attribute13,
aid.global_attribute14 global_attribute14,
aid.global_attribute15 global_attribute15,
aid.global_attribute16 global_attribute16,
aid.global_attribute17 global_attribute17,
aid.global_attribute18 global_attribute18,
aid.global_attribute19 global_attribute19,
aid.global_attribute20 global_attribute20,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', aid.award_id) award_id, /* bug 10155425 bug 10282701 */
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'),
'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
--Bug 8910531
'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.cancellation_flag cancellation_flag,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', zd.trx_line_id,
'N', nvl(parent_tax_line.line_number,
parent_taxable_dist.invoice_line_number)) invoice_line_number,
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
zd.func_curr_rounding_adjustment) rounding_amt,
-- This will update the rounding_amt in the recoverable dist
-- for the non-recoverable the primary distribution will be
-- updated later on
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id,
NULL, 'CANDIDATE',
'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
zd.taxable_amt taxable_amount,
zd.taxable_amt_funcl_curr taxable_base_amount,
zd.summary_tax_line_id summary_tax_line_id,
null extra_po_erv,
-- (zd.applied_to_doc_erv - zx.reference_doc_erv) null extra_po_erv,
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', DECODE(NVL(zd.recoverable_flag, 'N'),
'N', (zd.prd_tax_amt - zd.rec_nrec_tax_amt), NULL),
NULL) prepay_tax_diff_amount,
aid.invoice_distribution_id invoice_distribution_id,
aid.pay_awt_group_id pay_awt_group_id, --bug8345264
zd.account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_tax_line,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.invoice_id = aid.invoice_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y'
AND (NVL(aid.posted_flag,'N') <> 'Y' AND
aid.accounting_event_id IS NULL AND
NVL(aid.encumbered_flag, 'N') NOT IN ('Y','D','W','X')) --Bug7419940 Dont allow any insert from posted / frozen variacnes it should be reversed)
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id(+)
AND zd.trx_id = parent_tax_line.invoice_id (+)
AND zd.summary_tax_line_id = parent_tax_line.summary_tax_line_id(+)
AND zd.trx_id = parent_item_line.invoice_id(+)
AND zd.trx_line_id = parent_item_line.line_number(+)
AND zd.trx_id = parent_taxable_dist.invoice_id(+)
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id(+);
CURSOR update_tax_variances IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
(CASE aid.line_type_lookup_code
WHEN 'TERV' THEN
DECODE(pd.destination_type_code, 'EXPENSE', pd.code_combination_id,
parent_taxable_dist.dist_code_combination_id)
ELSE
DECODE(pd.destination_type_code, 'EXPENSE',
DECODE(pd.accrue_on_receipt_flag, 'Y', pd.code_combination_id,
parent_taxable_dist.dist_code_combination_id),
pd.variance_account_id)
END) dist_code_combination_id,
(CASE aid.line_type_lookup_code
WHEN 'TIPV' THEN 'TIPV'
WHEN 'TERV' THEN 'TERV'
WHEN 'TRV' THEN 'TRV'
END) line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
(CASE aid.line_type_lookup_code
WHEN 'TIPV' THEN -- bug 10291717
ap_etax_utility_pkg.get_tipv(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, parent_item_line.match_type , --Bug10416960
zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)
WHEN 'TRV' THEN
(ap_etax_utility_pkg.get_tv(
zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, ai.invoice_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id) -
ap_etax_utility_pkg.get_tipv(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,per_trx_curr_unit_nr_amt),
zd.price_diff, parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, parent_item_line.match_type , zd.trx_line_dist_id , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id))
WHEN 'TERV' THEN 0
END) amount,
(CASE aid.line_type_lookup_code
WHEN 'TIPV' THEN
ap_utilities_pkg.ap_round_currency(
ap_etax_utility_pkg.get_tipv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, NULL,
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, asp.base_currency_code, --Bug10416960
parent_item_line.match_type , zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id),
asp.base_currency_code)
WHEN 'TRV' THEN
ap_utilities_pkg.ap_round_currency(
(ap_etax_utility_pkg.get_tv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,0), zd.per_trx_curr_unit_nr_amt,
zd.ref_per_trx_curr_unit_nr_amt, nvl(zd.currency_conversion_rate,1),
zd.ref_doc_curr_conv_rate, zd.price_diff, parent_tax_line.corrected_inv_id,
parent_item_line.line_type_lookup_code, parent_item_line.line_source, asp.base_currency_code, --Bug10416960
parent_item_line.match_type, zd.unit_price , zd.trx_line_dist_id , zd.ref_doc_unit_price , zd.rec_nrec_tax_dist_id)
- (DECODE(find_val(zd.trx_line_dist_id),0,1,0)*ap_etax_utility_pkg.get_terv(
zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate,
zd.applied_to_doc_curr_conv_rate, NULL, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id,zd.unit_price)) --Bug10416960
- ap_etax_utility_pkg.get_tipv_base(
zd.rate_tax_factor, zd.trx_line_dist_qty,
nvl2(parent_taxable_dist.rcv_transaction_id,
ap_etax_utility_pkg.get_converted_price
(parent_taxable_dist.invoice_distribution_id), zd.unit_price),
zd.ref_doc_unit_price, nvl(zd.ref_per_trx_curr_unit_nr_amt,zd.per_trx_curr_unit_nr_amt),
zd.price_diff, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate, NULL,
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, ai.invoice_currency_code, asp.base_currency_code, --Bug10416960
parent_item_line.match_type , zd.trx_line_dist_id , zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id)),
asp.base_currency_code) -- bug 9231678
WHEN 'TERV' THEN
ap_utilities_pkg.ap_round_currency
(ap_etax_utility_pkg.get_terv(
zd.trx_line_dist_qty, zd.currency_conversion_rate, zd.ref_doc_curr_conv_rate,
zd.applied_to_doc_curr_conv_rate, NULL, zd.per_unit_nrec_tax_amt,
nvl(zd.ref_doc_per_unit_nrec_tax_amt,zd.per_unit_nrec_tax_amt),
parent_tax_line.corrected_inv_id, parent_item_line.line_type_lookup_code, --Bug10416960
parent_item_line.line_source, asp.base_currency_code , zd.trx_line_dist_id , zd.ref_doc_unit_price , --Bug10416960
zd.ref_per_trx_curr_unit_nr_amt , zd.rec_nrec_tax_dist_id,zd.unit_price), asp.base_currency_code) --Bug10416960
END) base_amount,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.description,
'N', parent_tax_line.description) description,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(parent_item_line.type_1099, NULL, NULL,
parent_item_line.income_tax_region),
'N', DECODE(parent_tax_line.type_1099, NULL, NULL,
parent_tax_line.income_tax_region)) income_tax_region,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.type_1099,
'N', parent_tax_line.type_1099) type_1099,
--Bug9346774
aid.attribute1 attribute1,
aid.attribute10 attribute10,
aid.attribute11 attribute11,
aid.attribute12 attribute12,
aid.attribute13 attribute13,
aid.attribute14 attribute14,
aid.attribute15 attribute15,
aid.attribute2 attribute2,
aid.attribute3 attribute3,
aid.attribute4 attribute4,
aid.attribute5 attribute5,
aid.attribute6 attribute6,
aid.attribute7 attribute7,
aid.attribute8 attribute8,
aid.attribute9 attribute9,
aid.attribute_category attribute_category,
--Bug9346774
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.expenditure_item_date, parent_taxable_dist.expenditure_item_date)) expenditure_item_date, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.expenditure_organization_id,parent_taxable_dist.expenditure_organization_id)) expenditure_organization_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.expenditure_type,parent_taxable_dist.expenditure_type)) expenditure_type, /* bug 11076722 modified bug 10155425 */
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E', 'N', parent_taxable_dist.pa_addition_flag) pa_addition_flag,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.pa_quantity,parent_taxable_dist.pa_quantity)) pa_quantity, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', parent_taxable_dist.project_accounting_context) project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.project_id,parent_taxable_dist.project_id)) project_id, /* bug 11076722 modified bug 10155425 */
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', nvl(parent_tax_line.task_id,parent_taxable_dist.task_id)) task_id, /* bug 11076722 modified bug 10155425 */
aid.awt_group_id awt_group_id,
--Bug9346774
aid.global_attribute_category global_attribute_category,
aid.global_attribute1 global_attribute1,
aid.global_attribute2 global_attribute2,
aid.global_attribute3 global_attribute3,
aid.global_attribute4 global_attribute4,
aid.global_attribute5 global_attribute5,
aid.global_attribute6 global_attribute6,
aid.global_attribute7 global_attribute7,
aid.global_attribute8 global_attribute8,
aid.global_attribute9 global_attribute9,
aid.global_attribute10 global_attribute10,
aid.global_attribute11 global_attribute11,
aid.global_attribute12 global_attribute12,
aid.global_attribute13 global_attribute13,
aid.global_attribute14 global_attribute14,
aid.global_attribute15 global_attribute15,
aid.global_attribute16 global_attribute16,
aid.global_attribute17 global_attribute17,
aid.global_attribute18 global_attribute18,
aid.global_attribute19 global_attribute19,
aid.global_attribute20 global_attribute20,
--Bug9346774
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL, 'N', aid.award_id) award_id, /* bug 10155425 bug 10282701 */
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED', 'OTHER_TO_RECEIPT'), 'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
--Bug 8910531
'Y', NULL, 'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.cancellation_flag cancellation_flag,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', zd.trx_line_id, 'N', parent_tax_line.line_number) invoice_line_number,
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
NULL rounding_amt,
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id, NULL, 'CANDIDATE', 'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
NULL taxable_amount,
NULL taxable_base_amount,
zd.summary_tax_line_id summary_tax_line_id,
NULL extra_po_erv,
NULL prepay_tax_diff_amount,
aid.invoice_distribution_id invoice_distribution_id,
aid.pay_awt_group_id pay_awt_group_id, --Bug8345264
zd.account_source_tax_rate_id account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_tax_line,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.invoice_id = aid.invoice_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('TIPV', 'TRV', 'TERV')
AND nvl(aid.reversal_flag,'N') <> 'Y'
AND (NVL(aid.posted_flag,'N') <> 'Y' AND
aid.accounting_event_id IS NULL AND
NVL(aid.encumbered_flag, 'N') NOT IN ('Y','D','W','X')) --Bug7419940 Dont allow any insert from posted / frozen variacnes it should be reversed)
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id(+)
AND zd.trx_id = parent_tax_line.invoice_id (+)
AND zd.summary_tax_line_id = parent_tax_line.summary_tax_line_id(+)
AND zd.trx_id = parent_item_line.invoice_id(+)
AND zd.trx_line_id = parent_item_line.line_number(+)
AND zd.trx_id = parent_taxable_dist.invoice_id(+)
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id(+)
AND ai.invoice_type_lookup_code <> 'PREPAYMENT' --BUG11656125
AND parent_item_line.line_type_lookup_code(+) <> 'PREPAY';
CURSOR insert_tax_self IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) INDEX (ZD ZX_REC_NREC_DIST_U3) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
-- Modified dist_code_combination_id value for bug#Bug9437885
--DECODE(pd.accrue_on_receipt_flag, 'Y', pd.code_combination_id,parent_taxable_dist.dist_code_combination_id) dist_code_combination_id,
DECODE(pd.destination_type_code,
'EXPENSE',DECODE(pd.accrue_on_receipt_flag, 'Y', pd.code_combination_id,parent_taxable_dist.dist_code_combination_id),
'INVENTORY',pd.variance_account_id,
'SHOP FLOOR',pd.variance_account_id, --Bug16003410
parent_taxable_dist.dist_code_combination_id) dist_code_combination_id, /*Bug13084462: Modified bug 9437885 to use variance account on self assess tax for invoice matched to PO for inventory items*/
-- this ccid is a temporary value that will be used if other conditions
-- are met before inserting the tax distribution
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'REC_TAX',
'N', 'NONREC_TAX') line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
zd.rec_nrec_tax_amt amount,
ap_utilities_pkg.ap_round_currency
(zd.rec_nrec_tax_amt_funcl_curr,asp.base_currency_code) base_amount,
parent_item_line.description description,
DECODE(parent_item_line.type_1099,
NULL, NULL,
parent_item_line.income_tax_region) income_tax_region,
parent_taxable_dist.po_distribution_id po_distribution_id, --changed for bug 8713009
parent_item_line.type_1099 type_1099,
zd.attribute1 attribute1,
zd.attribute10 attribute10,
zd.attribute11 attribute11,
zd.attribute12 attribute12,
zd.attribute13 attribute13,
zd.attribute14 attribute14,
zd.attribute15 attribute15,
zd.attribute2 attribute2,
zd.attribute3 attribute3,
zd.attribute4 attribute4,
zd.attribute5 attribute5,
zd.attribute6 attribute6,
zd.attribute7 attribute7,
zd.attribute8 attribute8,
zd.attribute9 attribute9,
zd.attribute_category attribute_category,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_item_date) expenditure_item_date,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_organization_id)
expenditure_organization_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_type) expenditure_type,
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E',
'N', 'N') pa_addition_flag, /* Bug 13594870 */
/* 'N', decode(parent_taxable_dist.pa_addition_flag,'Y','N',parent_taxable_dist.pa_addition_flag)) pa_addition_flag, Bug 13106066:added decode logic*/
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.pa_quantity) pa_quantity,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_accounting_context)
project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_id) project_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.task_id) task_id,
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.awt_group_id, --Bug8334059
ap_etax_utility_pkg.Get_Prepay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.awt_group_id),
NULL),
NULL),
NULL) awt_group_id,
zd.global_attribute_category global_attribute_category,
zd.global_attribute1 global_attribute1,
zd.global_attribute2 global_attribute2,
zd.global_attribute3 global_attribute3,
zd.global_attribute4 global_attribute4,
zd.global_attribute5 global_attribute5,
zd.global_attribute6 global_attribute6,
zd.global_attribute7 global_attribute7,
zd.global_attribute8 global_attribute8,
zd.global_attribute9 global_attribute9,
zd.global_attribute10 global_attribute10,
zd.global_attribute11 global_attribute11,
zd.global_attribute12 global_attribute12,
zd.global_attribute13 global_attribute13,
zd.global_attribute14 global_attribute14,
zd.global_attribute15 global_attribute15,
zd.global_attribute16 global_attribute16,
zd.global_attribute17 global_attribute17,
zd.global_attribute18 global_attribute18,
zd.global_attribute19 global_attribute19,
zd.global_attribute20 global_attribute20,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.award_id) award_id,
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'),
'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
--Bug 8910531
'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.merchant_document_number merchant_document_number, --Bug14658408: Start
parent_taxable_dist.merchant_name merchant_name,
parent_taxable_dist.merchant_reference merchant_reference,
parent_taxable_dist.merchant_tax_reg_number merchant_tax_reg_number,
parent_taxable_dist.merchant_taxpayer_id merchant_taxpayer_id, --Bug14658408: End
parent_taxable_dist.cancellation_flag cancellation_flag,
zd.trx_line_id invoice_line_number,
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
zd.func_curr_rounding_adjustment) rounding_amt,
-- the rounding amount in the non-recoverable case will be populated
-- to the primary distribution later in the cycle.
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id,
NULL, 'CANDIDATE',
'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
zd.summary_tax_line_id summary_tax_line_id,
null extra_po_erv,
zd.taxable_amt taxable_amount,
zd.taxable_amt_funcl_curr taxable_base_amount,
pd.accrue_on_receipt_flag accrue_on_receipt_flag,
asp.allow_flex_override_flag allow_flex_override_flag,
fsp.purch_encumbrance_flag purch_encumbrance_flag,
asp.org_id org_id,
zd.tax_regime_id tax_regime_id,
zd.tax_id tax_id,
zd.tax_status_id tax_status_id,
zl.tax_jurisdiction_id tax_jurisdiction_id,
'N', -- Bug 13821160
parent_taxable_dist.cancellation_flag parent_dist_cancellation_flag,
parent_taxable_dist.reversal_flag parent_dist_reversal_flag,
parent_taxable_dist.parent_reversal_id parent_dist_parent_reversal_id,
zd.reversed_tax_dist_id reversed_tax_dist_id,
zd.adjusted_doc_tax_dist_id adjusted_doc_tax_dist_id,
zd.applied_from_tax_dist_id applied_from_tax_dist_id,
-- the prepay_distribution_id will be populated with
-- invoice_distribution_id for the TAX rec or nonrec
-- asociated
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY',
AP_ETAX_UTILITY_PKG.Get_Dist_Id_For_Tax_Dist_Id(
zd.applied_from_tax_dist_id),
NULL) prepay_distribution_id,
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', DECODE(NVL(zd.recoverable_flag, 'N'),
'N', (zd.prd_tax_amt - zd.rec_nrec_tax_amt), NULL),
NULL) prepay_tax_diff_amount,
ai.invoice_id invoice_id,
ai.batch_id batch_id,
ai.set_of_books_id set_of_books_id,
parent_taxable_dist.pay_awt_group_id pay_awt_group_id, --Bug8345264
zd.account_source_tax_rate_id account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND NVL(zd.self_assessed_flag, 'N') = 'Y'
AND NVL(zl.reporting_only_flag, 'N') = 'N'
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id(+)
AND zd.trx_id = parent_item_line.invoice_id(+)
AND zd.trx_line_id = parent_item_line.line_number(+)
AND zd.trx_id = parent_taxable_dist.invoice_id(+)
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id(+)
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_id = ai.invoice_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
-- Bug 7462582
-- Reverting the fixes done in bugs 6805527 and 7389822 as Etax bug 7515711 will take care of these fixes.
/* Added by schitlap, epajaril to fix the issue in Bug 6805527 */
/*AND (nvl(parent_taxable_dist.reversal_flag, 'N') <> 'Y'
OR zd.reversed_tax_dist_id IS NULL) -- 7389822*/
ORDER BY detail_tax_dist_id;
CURSOR update_tax_self IS
SELECT /*+ leading(gt,zd) cardinality(gt,1) */
zd.gl_date accounting_date , --Bug6809792
/*For tax distributions accounting date will be stamped from zx_rec_nrec_dist
Ebtax will now store accounting date of opne period in their table*/
aid.dist_code_combination_id dist_code_combination_id, --9437885
-- this ccid is a temporary value that will be used if other conditions
-- are met before inserting the tax distribution
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'REC_TAX',
'N', 'NONREC_TAX') line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(zd.gl_date,ai.org_id) period_name,
zd.rec_nrec_tax_amt amount,
ap_utilities_pkg.ap_round_currency
(zd.rec_nrec_tax_amt_funcl_curr,asp.base_currency_code) base_amount,
-- included the decode as part of the prepayment changes.
-- since for prepayment applic tax variances will not be created,
-- the base_amount should be the total including variances
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.description,
'N', parent_tax_line.description) description,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(parent_item_line.type_1099,
NULL, NULL,
parent_item_line.income_tax_region),
'N', DECODE(parent_tax_line.type_1099,
NULL, NULL,
parent_tax_line.income_tax_region)) income_tax_region,
DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', parent_item_line.type_1099,
'N', parent_tax_line.type_1099) type_1099,
--Bug9346774
aid.attribute1 attribute1,
aid.attribute10 attribute10,
aid.attribute11 attribute11,
aid.attribute12 attribute12,
aid.attribute13 attribute13,
aid.attribute14 attribute14,
aid.attribute15 attribute15,
aid.attribute2 attribute2,
aid.attribute3 attribute3,
aid.attribute4 attribute4,
aid.attribute5 attribute5,
aid.attribute6 attribute6,
aid.attribute7 attribute7,
aid.attribute8 attribute8,
aid.attribute9 attribute9,
aid.attribute_category attribute_category,
--Bug9346774
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_item_date) expenditure_item_date,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_organization_id)
expenditure_organization_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.expenditure_type) expenditure_type,
parent_taxable_dist.parent_invoice_id parent_invoice_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', 'E',
'N', parent_taxable_dist.pa_addition_flag) pa_addition_flag,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.pa_quantity) pa_quantity,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_accounting_context)
project_accounting_context,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.project_id) project_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.task_id) task_id,
DECODE(NVL(asp.allow_awt_flag, 'N'),
'Y', DECODE(NVL(pvs.allow_awt_flag, 'N'),
'Y', DECODE(NVL(asp.awt_include_tax_amt, 'N'),
'Y', DECODE(NVL(zd.inclusive_flag, 'N'),
'Y', DECODE(zd.applied_from_tax_dist_id,
null, parent_taxable_dist.awt_group_id, --Bug8334059
ap_etax_utility_pkg.Get_Prepay_Awt_Group_Id(
parent_taxable_dist.prepay_distribution_id,
P_Calling_Sequence)),
'N', parent_taxable_dist.awt_group_id),
NULL),
NULL),
NULL) awt_group_id,
--Bug9346774
aid.global_attribute_category global_attribute_category,
aid.global_attribute1 global_attribute1,
aid.global_attribute2 global_attribute2,
aid.global_attribute3 global_attribute3,
aid.global_attribute4 global_attribute4,
aid.global_attribute5 global_attribute5,
aid.global_attribute6 global_attribute6,
aid.global_attribute7 global_attribute7,
aid.global_attribute8 global_attribute8,
aid.global_attribute9 global_attribute9,
aid.global_attribute10 global_attribute10,
aid.global_attribute11 global_attribute11,
aid.global_attribute12 global_attribute12,
aid.global_attribute13 global_attribute13,
aid.global_attribute14 global_attribute14,
aid.global_attribute15 global_attribute15,
aid.global_attribute16 global_attribute16,
aid.global_attribute17 global_attribute17,
aid.global_attribute18 global_attribute18,
aid.global_attribute19 global_attribute19,
aid.global_attribute20 global_attribute20,
--Bug9346774
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
'N', parent_taxable_dist.award_id) award_id,
DECODE(zd.ref_doc_dist_id,
NULL, DECODE(zl.applied_to_trx_id,
NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT'),
'NOT_MATCHED') dist_match_type,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
--Bug 8910531
'N', parent_taxable_dist.rcv_transaction_id) rcv_transaction_id,
zd.recoverable_flag tax_recoverable_flag,
parent_taxable_dist.cancellation_flag cancellation_flag,
zd.trx_line_id invoice_line_number, --Bug13859326
parent_taxable_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NULL,
zd.func_curr_rounding_adjustment) rounding_amt,
-- This will update the rounding_amt in the recoverable dist
-- for the non-recoverable the primary distribution will be
-- updated later on
decode(NVL(zl.tax_only_line_flag,'N'),
'Y', NULL, zd.trx_line_dist_id) charge_applicable_to_dist_id,
DECODE(zl.ref_doc_trx_id,
NULL, 'CANDIDATE',
'PERMANENT') distribution_class,
zd.tax_rate_id tax_code_id,
zd.rec_nrec_tax_dist_id detail_tax_dist_id,
zd.rec_nrec_rate rec_nrec_rate,
zd.recovery_rate_id recovery_rate_id,
zd.recovery_rate_code recovery_rate_name,
zd.recovery_type_code recovery_type_code,
zd.taxable_amt taxable_amount,
zd.taxable_amt_funcl_curr taxable_base_amount,
zd.summary_tax_line_id summary_tax_line_id,
null extra_po_erv,
-- (zd.applied_to_doc_erv - zx.reference_doc_erv) null extra_po_erv,
DECODE(parent_item_line.line_type_lookup_code,
'PREPAY', DECODE(NVL(zd.recoverable_flag, 'N'),
'N', (zd.prd_tax_amt - zd.rec_nrec_tax_amt), NULL),
NULL) prepay_tax_diff_amount,
aid.invoice_distribution_id invoice_distribution_id,
parent_taxable_dist.pay_awt_group_id pay_awt_group_id, --Bug8345264
zd.account_source_tax_rate_id
FROM zx_trx_headers_gt gt,
zx_rec_nrec_dist zd,
zx_lines zl,
ap_invoices_all ai,
ap_self_assessed_tax_dist_all aid,
ap_supplier_sites_all pvs,
ap_system_parameters_all asp,
financials_system_params_all fsp,
po_distributions_all pd,
ap_invoice_lines_all parent_tax_line,
ap_invoice_lines_all parent_item_line,
ap_invoice_distributions_all parent_taxable_dist
WHERE gt.APPLICATION_ID = zd.APPLICATION_ID
AND gt.ENTITY_CODE = zd.ENTITY_CODE
AND gt.EVENT_CLASS_CODE = zd.EVENT_CLASS_CODE
AND gt.TRX_ID = zd.TRX_ID
AND zd.tax_line_id = zl.tax_line_id
AND gt.trx_id = ai.invoice_id
AND ai.invoice_id = aid.invoice_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y'
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.set_of_books_id = asp.set_of_books_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = fsp.set_of_books_id
AND asp.org_id = fsp.org_id
AND parent_taxable_dist.po_distribution_id = pd.po_distribution_id(+)
AND zd.trx_id = parent_tax_line.invoice_id (+)
AND zd.summary_tax_line_id = parent_tax_line.summary_tax_line_id(+)
AND zd.trx_id = parent_item_line.invoice_id(+)
AND zd.trx_line_id = parent_item_line.line_number(+)
AND zd.trx_id = parent_taxable_dist.invoice_id(+)
AND zd.trx_line_dist_id = parent_taxable_dist.invoice_distribution_id(+);
DELETE FROM AP_Line_Temp_GT;
INSERT INTO AP_Line_Temp_GT (Line_Type)
VALUES (linetype(i));
l_debug_info := 'Get tax distributions for update';
OPEN UPDATE_TAX_DIST;
FETCH UPDATE_TAX_DIST
BULK COLLECT INTO L_INV_DIST_UPD
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN UPDATE_TAX_DIST%NOTFOUND
AND L_INV_DIST_UPD.COUNT <= 0;
l_debug_info := 'Tax distributions updated: '||l_inv_dist_upd.count;
*/ --Update flow should not update pa_addition_flag
UPDATE ap_invoice_distributions_all
SET accounting_date = l_inv_dist_upd(j).accounting_date,
last_updated_by = l_user_id,
last_update_date = sysdate, /*Bug10101457: replaced l_sysdate with sysdate*/
line_type_lookup_code = l_inv_dist_upd(j).line_type_lookup_code,
period_name = l_inv_dist_upd(j).period_name,
amount = l_inv_dist_upd(j).amount,
base_amount = l_inv_dist_upd(j).base_amount, -- bug 10350068
-- Bug 10157751 description = l_inv_dist_upd(j).description,
-- Bug 13880392 income_tax_region = l_inv_dist_upd(j).income_tax_region,
last_update_login = l_login_id,
-- Bug 13880392 type_1099 = l_inv_dist_upd(j).type_1099,
attribute1 = l_inv_dist_upd(j).attribute1,
attribute10 = l_inv_dist_upd(j).attribute10,
attribute11 = l_inv_dist_upd(j).attribute11,
attribute12 = l_inv_dist_upd(j).attribute12,
attribute13 = l_inv_dist_upd(j).attribute13,
attribute14 = l_inv_dist_upd(j).attribute14,
attribute15 = l_inv_dist_upd(j).attribute15,
attribute2 = l_inv_dist_upd(j).attribute2,
attribute3 = l_inv_dist_upd(j).attribute3,
attribute4 = l_inv_dist_upd(j).attribute4,
attribute5 = l_inv_dist_upd(j).attribute5,
attribute6 = l_inv_dist_upd(j).attribute6,
attribute7 = l_inv_dist_upd(j).attribute7,
attribute8 = l_inv_dist_upd(j).attribute8,
attribute9 = l_inv_dist_upd(j).attribute9,
attribute_category = l_inv_dist_upd(j).attribute_category,
expenditure_item_date = l_inv_dist_upd(j).expenditure_item_date,
expenditure_organization_id = l_inv_dist_upd(j).expenditure_organization_id,
expenditure_type = l_inv_dist_upd(j).expenditure_type,
parent_invoice_id = l_inv_dist_upd(j).parent_invoice_id,
pa_addition_flag = pa_addition_flag, --Bug10621602
pa_quantity = l_inv_dist_upd(j).pa_quantity,
project_accounting_context = l_inv_dist_upd(j).project_accounting_context,
project_id = l_inv_dist_upd(j).project_id,
task_id = l_inv_dist_upd(j).task_id,
awt_group_id = l_inv_dist_upd(j).awt_group_id,
global_attribute_category = l_inv_dist_upd(j).global_attribute_category,
global_attribute1 = l_inv_dist_upd(j).global_attribute1,
global_attribute2 = l_inv_dist_upd(j).global_attribute2,
global_attribute3 = l_inv_dist_upd(j).global_attribute3,
global_attribute4 = l_inv_dist_upd(j).global_attribute4,
global_attribute5 = l_inv_dist_upd(j).global_attribute5,
global_attribute6 = l_inv_dist_upd(j).global_attribute6,
global_attribute7 = l_inv_dist_upd(j).global_attribute7,
global_attribute8 = l_inv_dist_upd(j).global_attribute8,
global_attribute9 = l_inv_dist_upd(j).global_attribute9,
global_attribute10 = l_inv_dist_upd(j).global_attribute10,
global_attribute11 = l_inv_dist_upd(j).global_attribute11,
global_attribute12 = l_inv_dist_upd(j).global_attribute12,
global_attribute13 = l_inv_dist_upd(j).global_attribute13,
global_attribute14 = l_inv_dist_upd(j).global_attribute14,
global_attribute15 = l_inv_dist_upd(j).global_attribute15,
global_attribute16 = l_inv_dist_upd(j).global_attribute16,
global_attribute17 = l_inv_dist_upd(j).global_attribute17,
global_attribute18 = l_inv_dist_upd(j).global_attribute18,
global_attribute19 = l_inv_dist_upd(j).global_attribute19,
global_attribute20 = l_inv_dist_upd(j).global_attribute20,
award_id = l_inv_dist_upd(j).award_id,
dist_match_type = l_inv_dist_upd(j).dist_match_type,
rcv_transaction_id = l_inv_dist_upd(j).rcv_transaction_id,
tax_recoverable_flag = l_inv_dist_upd(j).tax_recoverable_flag,
cancellation_flag = l_inv_dist_upd(j).cancellation_flag,
--invoice_line_number = l_inv_dist_upd(j).invoice_line_number,
corrected_invoice_dist_id = l_inv_dist_upd(j).corrected_invoice_dist_id,
rounding_amt = l_inv_dist_upd(j).rounding_amt,
charge_applicable_to_dist_id = l_inv_dist_upd(j).charge_applicable_to_dist_id,
--distribution_class = l_inv_dist_upd(j).distribution_class, --Bug6678578
tax_code_id = l_inv_dist_upd(j).tax_code_id,
detail_tax_dist_id = l_inv_dist_upd(j).detail_tax_dist_id,
rec_nrec_rate = l_inv_dist_upd(j).rec_nrec_rate,
recovery_rate_id = l_inv_dist_upd(j).recovery_rate_id,
recovery_rate_name = l_inv_dist_upd(j).recovery_rate_name,
recovery_type_code = l_inv_dist_upd(j).recovery_type_code,
taxable_amount = l_inv_dist_upd(j).taxable_amount,
taxable_base_amount = l_inv_dist_upd(j).taxable_base_amount,
summary_tax_line_id = l_inv_dist_upd(j).summary_tax_line_id,
extra_po_erv = l_inv_dist_upd(j).extra_po_erv,
prepay_tax_diff_amount = l_inv_dist_upd(j).prepay_tax_diff_amount,
match_status_flag = decode (amount, l_inv_dist_upd(j).amount,
match_status_flag, 'N')
WHERE invoice_distribution_id = l_inv_dist_upd(j).invoice_distribution_id;
l_debug_info := 'Tax dist id updated: '||l_inv_dist_upd(j).invoice_distribution_id||
' Tax Detail Tax Dist Id: '||l_inv_dist_upd(j).detail_tax_dist_id||
' Summary Tax Line Id: '||l_inv_dist_upd(j).summary_tax_line_id||
' Amt: '||l_inv_dist_upd(j).amount||
' Base Amt: '||l_inv_dist_upd(j).base_amount;
L_INV_DIST_UPD.DELETE;
CLOSE UPDATE_TAX_DIST;
l_debug_info := 'Get tax variance distributions for update';
OPEN UPDATE_TAX_VARIANCES;
FETCH UPDATE_TAX_VARIANCES
BULK COLLECT INTO L_INV_DIST_UPD
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN UPDATE_TAX_VARIANCES%NOTFOUND
AND L_INV_DIST_UPD.COUNT <= 0;
l_debug_info := 'Tax Variance distributions updated: '||l_inv_dist_upd.count;
UPDATE ap_invoice_distributions_all
SET accounting_date = l_inv_dist_upd(j).accounting_date,
last_updated_by = l_user_id,
last_update_date = sysdate, /*Bug10101457: replaced l_sysdate with sysdate*/
line_type_lookup_code = l_inv_dist_upd(j).line_type_lookup_code,
period_name = l_inv_dist_upd(j).period_name,
amount = l_inv_dist_upd(j).amount,
base_amount = l_inv_dist_upd(j).base_amount, -- bug 10350068
-- Bug 10157751 description = l_inv_dist_upd(j).description,
-- Bug 13880392 income_tax_region = l_inv_dist_upd(j).income_tax_region,
last_update_login = l_login_id,
-- Bug 13880392 type_1099 = l_inv_dist_upd(j).type_1099,
attribute1 = l_inv_dist_upd(j).attribute1,
attribute10 = l_inv_dist_upd(j).attribute10,
attribute11 = l_inv_dist_upd(j).attribute11,
attribute12 = l_inv_dist_upd(j).attribute12,
attribute13 = l_inv_dist_upd(j).attribute13,
attribute14 = l_inv_dist_upd(j).attribute14,
attribute15 = l_inv_dist_upd(j).attribute15,
attribute2 = l_inv_dist_upd(j).attribute2,
attribute3 = l_inv_dist_upd(j).attribute3,
attribute4 = l_inv_dist_upd(j).attribute4,
attribute5 = l_inv_dist_upd(j).attribute5,
attribute6 = l_inv_dist_upd(j).attribute6,
attribute7 = l_inv_dist_upd(j).attribute7,
attribute8 = l_inv_dist_upd(j).attribute8,
attribute9 = l_inv_dist_upd(j).attribute9,
attribute_category = l_inv_dist_upd(j).attribute_category,
expenditure_item_date = l_inv_dist_upd(j).expenditure_item_date,
expenditure_organization_id = l_inv_dist_upd(j).expenditure_organization_id,
expenditure_type = l_inv_dist_upd(j).expenditure_type,
parent_invoice_id = l_inv_dist_upd(j).parent_invoice_id,
pa_addition_flag = pa_addition_flag, --Bug10621602
pa_quantity = l_inv_dist_upd(j).pa_quantity,
project_accounting_context = l_inv_dist_upd(j).project_accounting_context,
project_id = l_inv_dist_upd(j).project_id,
task_id = l_inv_dist_upd(j).task_id,
awt_group_id = l_inv_dist_upd(j).awt_group_id,
global_attribute_category = l_inv_dist_upd(j).global_attribute_category,
global_attribute1 = l_inv_dist_upd(j).global_attribute1,
global_attribute2 = l_inv_dist_upd(j).global_attribute2,
global_attribute3 = l_inv_dist_upd(j).global_attribute3,
global_attribute4 = l_inv_dist_upd(j).global_attribute4,
global_attribute5 = l_inv_dist_upd(j).global_attribute5,
global_attribute6 = l_inv_dist_upd(j).global_attribute6,
global_attribute7 = l_inv_dist_upd(j).global_attribute7,
global_attribute8 = l_inv_dist_upd(j).global_attribute8,
global_attribute9 = l_inv_dist_upd(j).global_attribute9,
global_attribute10 = l_inv_dist_upd(j).global_attribute10,
global_attribute11 = l_inv_dist_upd(j).global_attribute11,
global_attribute12 = l_inv_dist_upd(j).global_attribute12,
global_attribute13 = l_inv_dist_upd(j).global_attribute13,
global_attribute14 = l_inv_dist_upd(j).global_attribute14,
global_attribute15 = l_inv_dist_upd(j).global_attribute15,
global_attribute16 = l_inv_dist_upd(j).global_attribute16,
global_attribute17 = l_inv_dist_upd(j).global_attribute17,
global_attribute18 = l_inv_dist_upd(j).global_attribute18,
global_attribute19 = l_inv_dist_upd(j).global_attribute19,
global_attribute20 = l_inv_dist_upd(j).global_attribute20,
award_id = l_inv_dist_upd(j).award_id,
dist_match_type = l_inv_dist_upd(j).dist_match_type,
rcv_transaction_id = l_inv_dist_upd(j).rcv_transaction_id,
tax_recoverable_flag = l_inv_dist_upd(j).tax_recoverable_flag,
cancellation_flag = l_inv_dist_upd(j).cancellation_flag,
--invoice_line_number = l_inv_dist_upd(j).invoice_line_number,
corrected_invoice_dist_id = l_inv_dist_upd(j).corrected_invoice_dist_id,
rounding_amt = l_inv_dist_upd(j).rounding_amt,
charge_applicable_to_dist_id = l_inv_dist_upd(j).charge_applicable_to_dist_id,
--distribution_class = l_inv_dist_upd(j).distribution_class, --Bug6678578
tax_code_id = l_inv_dist_upd(j).tax_code_id,
detail_tax_dist_id = l_inv_dist_upd(j).detail_tax_dist_id,
rec_nrec_rate = l_inv_dist_upd(j).rec_nrec_rate,
recovery_rate_id = l_inv_dist_upd(j).recovery_rate_id,
recovery_rate_name = l_inv_dist_upd(j).recovery_rate_name,
recovery_type_code = l_inv_dist_upd(j).recovery_type_code,
taxable_amount = l_inv_dist_upd(j).taxable_amount,
taxable_base_amount = l_inv_dist_upd(j).taxable_base_amount,
summary_tax_line_id = l_inv_dist_upd(j).summary_tax_line_id,
extra_po_erv = l_inv_dist_upd(j).extra_po_erv,
prepay_tax_diff_amount = l_inv_dist_upd(j).prepay_tax_diff_amount,
match_status_flag = decode (amount, l_inv_dist_upd(j).amount,
match_status_flag, 'N')
WHERE invoice_distribution_id = l_inv_dist_upd(j).invoice_distribution_id;
l_debug_info := 'Tax dist id updated: '||l_inv_dist_upd(j).invoice_distribution_id||
' Tax Detail Tax Dist Id: '||l_inv_dist_upd(j).detail_tax_dist_id||
' Summary Tax Line Id: '||l_inv_dist_upd(j).summary_tax_line_id||
' Amt: '||l_inv_dist_upd(j).amount||
' Base Amt: '||l_inv_dist_upd(j).base_amount;
L_INV_DIST_UPD.DELETE;
CLOSE UPDATE_TAX_VARIANCES;
l_debug_info := 'Step 5: Get tax distributions for update';
OPEN UPDATE_TAX_SELF;
FETCH UPDATE_TAX_SELF
BULK COLLECT INTO L_INV_SELF_UPD
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN UPDATE_TAX_SELF%NOTFOUND
AND l_inv_self_upd.COUNT <= 0; --Bug13859326
l_debug_info := 'Step 5: Self assessed dist updated: '||l_inv_self_upd.COUNT;
UPDATE ap_self_assessed_tax_dist_all
SET accounting_date = l_inv_self_upd(j).accounting_date,
last_updated_by = l_user_id,
last_update_date = sysdate, /*Bug10101457: replaced l_sysdate with sysdate*/
line_type_lookup_code = l_inv_self_upd(j).line_type_lookup_code,
period_name = l_inv_self_upd(j).period_name,
amount = l_inv_self_upd(j).amount,
base_amount = l_inv_self_upd(j).base_amount, -- bug 10350068
-- Bug 10157751 description = l_inv_self_upd(j).description,
income_tax_region = l_inv_self_upd(j).income_tax_region,
last_update_login = l_login_id,
type_1099 = l_inv_self_upd(j).type_1099,
attribute1 = l_inv_self_upd(j).attribute1,
attribute10 = l_inv_self_upd(j).attribute10,
attribute11 = l_inv_self_upd(j).attribute11,
attribute12 = l_inv_self_upd(j).attribute12,
attribute13 = l_inv_self_upd(j).attribute13,
attribute14 = l_inv_self_upd(j).attribute14,
attribute15 = l_inv_self_upd(j).attribute15,
attribute2 = l_inv_self_upd(j).attribute2,
attribute3 = l_inv_self_upd(j).attribute3,
attribute4 = l_inv_self_upd(j).attribute4,
attribute5 = l_inv_self_upd(j).attribute5,
attribute6 = l_inv_self_upd(j).attribute6,
attribute7 = l_inv_self_upd(j).attribute7,
attribute8 = l_inv_self_upd(j).attribute8,
attribute9 = l_inv_self_upd(j).attribute9,
attribute_category = l_inv_self_upd(j).attribute_category,
expenditure_item_date = l_inv_self_upd(j).expenditure_item_date,
expenditure_organization_id = l_inv_self_upd(j).expenditure_organization_id,
expenditure_type = l_inv_self_upd(j).expenditure_type,
parent_invoice_id = l_inv_self_upd(j).parent_invoice_id,
pa_addition_flag = pa_addition_flag, --Bug10621602
pa_quantity = l_inv_self_upd(j).pa_quantity,
project_accounting_context = l_inv_self_upd(j).project_accounting_context,
project_id = l_inv_self_upd(j).project_id,
task_id = l_inv_self_upd(j).task_id,
--Bug8334059 Awt_group_id need not be updated since awt is not calculated
--awt_group_id = l_inv_self_upd(j).awt_group_id,
global_attribute_category = l_inv_self_upd(j).global_attribute_category,
global_attribute1 = l_inv_self_upd(j).global_attribute1,
global_attribute2 = l_inv_self_upd(j).global_attribute2,
global_attribute3 = l_inv_self_upd(j).global_attribute3,
global_attribute4 = l_inv_self_upd(j).global_attribute4,
global_attribute5 = l_inv_self_upd(j).global_attribute5,
global_attribute6 = l_inv_self_upd(j).global_attribute6,
global_attribute7 = l_inv_self_upd(j).global_attribute7,
global_attribute8 = l_inv_self_upd(j).global_attribute8,
global_attribute9 = l_inv_self_upd(j).global_attribute9,
global_attribute10 = l_inv_self_upd(j).global_attribute10,
global_attribute11 = l_inv_self_upd(j).global_attribute11,
global_attribute12 = l_inv_self_upd(j).global_attribute12,
global_attribute13 = l_inv_self_upd(j).global_attribute13,
global_attribute14 = l_inv_self_upd(j).global_attribute14,
global_attribute15 = l_inv_self_upd(j).global_attribute15,
global_attribute16 = l_inv_self_upd(j).global_attribute16,
global_attribute17 = l_inv_self_upd(j).global_attribute17,
global_attribute18 = l_inv_self_upd(j).global_attribute18,
global_attribute19 = l_inv_self_upd(j).global_attribute19,
global_attribute20 = l_inv_self_upd(j).global_attribute20,
award_id = l_inv_self_upd(j).award_id,
dist_match_type = l_inv_self_upd(j).dist_match_type,
rcv_transaction_id = l_inv_self_upd(j).rcv_transaction_id,
tax_recoverable_flag = l_inv_self_upd(j).tax_recoverable_flag,
cancellation_flag = l_inv_self_upd(j).cancellation_flag,
invoice_line_number = l_inv_self_upd(j).invoice_line_number,
corrected_invoice_dist_id = l_inv_self_upd(j).corrected_invoice_dist_id,
rounding_amt = l_inv_self_upd(j).rounding_amt,
charge_applicable_to_dist_id = l_inv_self_upd(j).charge_applicable_to_dist_id,
--distribution_class = l_inv_self_upd(j).distribution_class, --Bug6678578
tax_code_id = l_inv_self_upd(j).tax_code_id,
detail_tax_dist_id = l_inv_self_upd(j).detail_tax_dist_id,
rec_nrec_rate = l_inv_self_upd(j).rec_nrec_rate,
recovery_rate_id = l_inv_self_upd(j).recovery_rate_id,
recovery_rate_name = l_inv_self_upd(j).recovery_rate_name,
recovery_type_code = l_inv_self_upd(j).recovery_type_code,
taxable_amount = l_inv_self_upd(j).taxable_amount,
taxable_base_amount = l_inv_self_upd(j).taxable_base_amount,
summary_tax_line_id = l_inv_self_upd(j).summary_tax_line_id,
extra_po_erv = l_inv_self_upd(j).extra_po_erv,
prepay_tax_diff_amount = l_inv_self_upd(j).prepay_tax_diff_amount
WHERE invoice_distribution_id = l_inv_self_upd(j).invoice_distribution_id;
l_debug_info := 'Tax dist id updated: '||l_inv_self_upd(j).invoice_distribution_id||
' Tax Detail Tax Dist Id: '||l_inv_self_upd(j).detail_tax_dist_id||
' Summary Tax Line Id: '||l_inv_self_upd(j).summary_tax_line_id||
' Amt: '||l_inv_self_upd(j).amount||
' Base Amt: '||l_inv_self_upd(j).base_amount;
L_INV_SELF_UPD.DELETE;
CLOSE UPDATE_TAX_SELF;
l_debug_info := 'Step 6: Get tax distributions for insert';
OPEN INSERT_TAX_DIST;
FETCH INSERT_TAX_DIST
BULK COLLECT INTO L_INV_DIST_INS
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN INSERT_TAX_DIST%NOTFOUND
AND L_INV_DIST_INS.COUNT <= 0;
l_debug_info := 'Tax distributions to insert: '||l_inv_dist_ins.COUNT;
SELECT aid.line_type_lookup_code , aid.retained_invoice_dist_id
INTO l_par_line_type_lookup_code , l_par_retained_invoice_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = l_inv_dist_ins(i).charge_applicable_to_dist_id
AND aid.po_distribution_id = l_inv_dist_ins(i).po_distribution_id
AND aid.invoice_id = l_inv_dist_ins(i).invoice_id ;
SELECT aid.dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_invoice_distributions_all aid , ap_invoice_distributions_all aid1
WHERE aid1.invoice_distribution_id = l_par_retained_invoice_dist_id
AND aid1.line_type_lookup_code = 'RETAINAGE'
AND aid.invoice_distribution_id = aid1.related_retainage_dist_id
AND aid1.invoice_id = aid.invoice_id ;
SELECT TAX_ONLY_LINE_FLAG
INTO L_TAX_ONLY_LINE_FLAG
FROM ZX_REC_NREC_DIST
WHERE TRX_ID = L_INV_DIST_INS(I).INVOICE_ID
AND APPLICATION_ID = 200
AND REC_NREC_TAX_DIST_ID = L_INV_DIST_INS(I).DETAIL_TAX_DIST_ID;
SELECT dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE detail_tax_dist_id = l_inv_dist_ins(i).reversed_tax_dist_id
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX','TIPV', 'TRV', 'TERV')
AND rownum =1;--Bug7241425
SELECT dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE detail_tax_dist_id = l_inv_dist_ins(i).adjusted_doc_tax_dist_id
AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TIPV','TRV','TERV')--Bug7241425
AND rownum =1;--Bug7241425
SELECT dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = AP_ETAX_UTILITY_PKG.Get_Dist_Id_For_Tax_Dist_Id
(l_inv_dist_ins(i).applied_from_tax_dist_id);
l_debug_info := 'Insert REC/NONREC distributions';
select decode(account_type, 'A', 'Y',
'N')
into l_inv_dist_ins(i).par_dist_assets_tracking_flag
from gl_code_combinations
where code_combination_id = l_dist_code_combination_id;
ap_etax_utility_pkg.insert_tax_distributions
(p_invoice_header_rec => p_invoice_header_rec,
p_inv_dist_rec => l_inv_dist_ins(i),
p_dist_code_combination_id => l_dist_code_combination_id,
p_user_id => l_user_id,
p_sysdate => sysdate, /*Bug10101457: replaced l_sysdate with sysdate*/
p_login_id => l_login_id,
p_calling_sequence => l_curr_calling_sequence);
L_INV_DIST_INS.DELETE;
CLOSE INSERT_TAX_DIST;
OPEN INSERT_TAX_VARIANCES;
FETCH INSERT_TAX_VARIANCES
BULK COLLECT INTO L_INV_DIST_INS
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN INSERT_TAX_VARIANCES%NOTFOUND
AND L_INV_DIST_INS.COUNT <= 0;
SELECT 'Y'
INTO l_lcm_enabled
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_ID = L_INV_DIST_INS(i).rcv_transaction_id
AND LCM_SHIPMENT_LINE_ID IS NOT NULL;
l_debug_info := 'Insert Tax Variance Distributions';
ap_etax_utility_pkg.insert_tax_distributions(
p_invoice_header_rec => p_invoice_header_rec,
p_inv_dist_rec => l_inv_dist_ins(i),
p_dist_code_combination_id => l_dist_code_combination_id,
p_user_id => l_user_id,
p_sysdate => sysdate, /*Bug10101457: replaced l_sysdate with sysdate*/
p_login_id => l_login_id,
p_calling_sequence => l_curr_calling_sequence);
L_INV_DIST_INS.DELETE;
CLOSE INSERT_TAX_VARIANCES;
l_debug_info := 'Get self assessed dist for insert';
OPEN INSERT_TAX_SELF;
FETCH INSERT_TAX_SELF
BULK COLLECT INTO L_INV_SELF_INS
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN INSERT_TAX_SELF%NOTFOUND
AND L_INV_SELF_INS.COUNT <= 0;
l_debug_info := 'Insert self assessed tax distributions';
SELECT dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_self_assessed_tax_dist_all
WHERE detail_tax_dist_id = l_inv_self_ins(i).reversed_tax_dist_id
AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TIPV','TRV','TERV')--Bug8834205
AND rownum =1;
SELECT dist_code_combination_id
INTO l_dist_code_combination_id
FROM ap_self_assessed_tax_dist_all
WHERE detail_tax_dist_id = l_inv_self_ins(i).adjusted_doc_tax_dist_id
AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TIPV','TRV','TERV')--Bug8834205
AND rownum =1;
l_debug_info := 'Insert self assessed distributions';
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_rate_name,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
extra_po_erv,
prepay_tax_diff_amount,
self_assessed_tax_liab_ccid,
rcv_charge_addition_flag,
self_assessed_flag
) VALUES (
l_inv_self_ins(i).accounting_date, -- accounting_date
'N', -- accrual_posted_flag
'U', -- assets_addition_flag
'N', -- assets_tracking_flag
'N', -- cash_posted_flag
AP_ETAX_UTILITY_PKG.get_max_dist_num_self(
--P_Invoice_Header_Rec.invoice_id,
l_inv_self_ins(i).invoice_id,
l_inv_self_ins(i).invoice_line_number)+1, --Bug7611551
-- distribution_line_number
l_dist_code_combination_id, -- dist_code_combination_id
l_inv_self_ins(i).invoice_id, -- invoice_id Bug7611551
--P_Invoice_Header_Rec.invoice_id, -- invoice_id
l_user_id, -- last_updated_by
sysdate, -- last_update_date /*Bug10101457: replaced l_sysdate with sysdate*/
l_inv_self_ins(i).line_type_lookup_code,
-- line_type_lookup_code
l_inv_self_ins(i).period_name, -- period_name
l_inv_self_ins(i).set_of_books_id, -- set_of_books_id bug7611551
--P_Invoice_Header_Rec.set_of_books_id, -- set_of_books_id
l_inv_self_ins(i).amount, -- amount
l_inv_self_ins(i).base_amount, -- base_amount -- bug 10350068
l_inv_self_ins(i).batch_id, -- batch_id --Bug7611551
--P_Invoice_Header_Rec.batch_id, -- batch_id
l_user_id, -- created_by
sysdate, -- creation_date /*Bug10101457: replaced l_sysdate with sysdate*/
l_inv_self_ins(i).description, -- description
NULL, -- final_match_flag
l_inv_self_ins(i).income_tax_region, -- income_tax_region
l_login_id, -- last_update_login
NULL, -- match_status_flag
'N', -- posted_flag
l_inv_self_ins(i).po_distribution_id, -- po_distribution_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- quantity_invoiced
NULL, -- request_id
'N', -- reversal_flag
l_inv_self_ins(i).type_1099, -- type_1099
NULL, -- unit_price
'N', -- encumbered_flag
NULL, -- stat_amount
l_inv_self_ins(i).attribute1, -- attribute1
l_inv_self_ins(i).attribute10, -- attribute10
l_inv_self_ins(i).attribute11, -- attribute11,
l_inv_self_ins(i).attribute12, -- attribute12
l_inv_self_ins(i).attribute13, -- attribute13
l_inv_self_ins(i).attribute14, -- attribute14
l_inv_self_ins(i).attribute15, -- attribute15
l_inv_self_ins(i).attribute2, -- attribute2
l_inv_self_ins(i).attribute3, -- attribute3
l_inv_self_ins(i).attribute4, -- attribute4
l_inv_self_ins(i).attribute5, -- attribute5
l_inv_self_ins(i).attribute6, -- attribute6
l_inv_self_ins(i).attribute7, -- attribute7
l_inv_self_ins(i).attribute8, -- attribute8
l_inv_self_ins(i).attribute9, -- attribute9
l_inv_self_ins(i).attribute_category, -- attribute_category
l_inv_self_ins(i).expenditure_item_date,
-- expenditure_item_date
l_inv_self_ins(i).expenditure_organization_id,
-- expenditure_organization_id
l_inv_self_ins(i).expenditure_type, -- expenditure_type
l_inv_self_ins(i).parent_invoice_id, -- parent_invoice_id
l_inv_self_ins(i).pa_addition_flag, -- pa_addition_flag
l_inv_self_ins(i).pa_quantity, -- pa_quantity
NULL, -- prepay_amount_remaining
-- the prepay_amount_remaining will be populated for all the
-- prepayment distributions during the payment. And later will be
-- updated during the prepayment applications
l_inv_self_ins(i).project_accounting_context,
-- project_accounting_context
l_inv_self_ins(i).project_id, -- project_id
l_inv_self_ins(i).task_id, -- task_id
NULL, -- packet_id
'N', -- awt_flag
l_inv_self_ins(i).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
l_inv_self_ins(i).org_id, -- org_id --Bug7611551
--P_Invoice_Header_Rec.org_id, -- org_id
NULL, -- awt_invoice_payment_id
l_inv_self_ins(i).global_attribute_category,
-- global_attribute_category
l_inv_self_ins(i).global_attribute1, -- global_attribute1
l_inv_self_ins(i).global_attribute2, -- global_attribute2
l_inv_self_ins(i).global_attribute3, -- global_attribute3
l_inv_self_ins(i).global_attribute4, -- global_attribute4
l_inv_self_ins(i).global_attribute5, -- global_attribute5
l_inv_self_ins(i).global_attribute6, -- global_attribute6
l_inv_self_ins(i).global_attribute7, -- global_attribute7
l_inv_self_ins(i).global_attribute8, -- global_attribute8
l_inv_self_ins(i).global_attribute9, -- global_attribute9
l_inv_self_ins(i).global_attribute10, -- global_attribute10
l_inv_self_ins(i).global_attribute11, -- global_attribute11
l_inv_self_ins(i).global_attribute12, -- global_attribute12
l_inv_self_ins(i).global_attribute13, -- global_attribute13
l_inv_self_ins(i).global_attribute14, -- global_attribute14
l_inv_self_ins(i).global_attribute15, -- global_attribute15
l_inv_self_ins(i).global_attribute16, -- global_attribute16
l_inv_self_ins(i).global_attribute17, -- global_attribute17
l_inv_self_ins(i).global_attribute18, -- global_attribute18
l_inv_self_ins(i).global_attribute19, -- global_attribute19
l_inv_self_ins(i).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
l_inv_self_ins(i).award_id, -- award_id
NULL, -- credit_card_trx_id
l_inv_self_ins(i).dist_match_type, -- dist_match_type
l_inv_self_ins(i).rcv_transaction_id, -- rcv_transaction_id
ap_invoice_distributions_s.NEXTVAL, -- invoice_distribution_id
NULL, -- parent_reversal_id
l_inv_self_ins(i).tax_recoverable_flag,
-- tax_recoverable_flag
l_inv_self_ins(i).merchant_document_number, -- merchant_document_number --Bug14658408: Start
l_inv_self_ins(i).merchant_name, -- merchant_name
l_inv_self_ins(i).merchant_reference, -- merchant_reference
l_inv_self_ins(i).merchant_tax_reg_number, -- merchant_tax_reg_number
l_inv_self_ins(i).merchant_taxpayer_id, -- merchant_taxpayer_id --Bug14658408: End
NULL, -- country_of_supply
NULL, -- matched_uom_lookup_code
NULL, -- gms_burdenable_raw_cost
NULL, -- accounting_event_id
l_inv_self_ins(i).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
l_inv_self_ins(i).cancellation_flag, -- cancellation_flag
l_inv_self_ins(i).invoice_line_number,-- invoice_line_number
l_inv_self_ins(i).corrected_invoice_dist_id,
-- corrected_invoice_dist_id
l_inv_self_ins(i).rounding_amt, -- rounding_amt
l_inv_self_ins(i).charge_applicable_to_dist_id,
-- charge_applicable_to_dist_id
NULL, -- corrected_quantity
NULL, -- related_id
NULL, -- asset_book_type_code
NULL, -- asset_category_id
l_inv_self_ins(i).distribution_class, -- distribution_class
l_inv_self_ins(i).tax_code_id, -- tax_code_id
NULL, -- intended_use,
l_inv_self_ins(i).detail_tax_dist_id, -- detail_tax_dist_id
l_inv_self_ins(i).rec_nrec_rate, -- rec_nrec_rate
l_inv_self_ins(i).recovery_rate_id, -- recovery_rate_id
l_inv_self_ins(i).recovery_rate_name, -- recovery_rate_name
l_inv_self_ins(i).recovery_type_code, -- recovery_type_code
NULL, -- withholding_tax_code_id,
l_inv_self_ins(i).taxable_amount, -- taxable_amount
l_inv_self_ins(i).taxable_base_amount, -- taxable_base_amount
NULL, -- tax_already_distributed_flag
l_inv_self_ins(i).summary_tax_line_id, -- summary_tax_line_id
l_inv_self_ins(i).extra_po_erv, -- extra_po_erv
l_inv_self_ins(i).prepay_tax_diff_amount, -- prepay_tax_diff_amount
l_tax_liab_ccid, -- self_assessed_tax_liab_ccid
'N', -- rcv_charge_addition_flag
'Y' -- self_assessed_flag
);
L_INV_SELF_INS.DELETE;
CLOSE INSERT_TAX_SELF;
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Frozen Deleted Dist Count: '||sql%rowcount);
insert into ap_errors_gt(invoice_id , reference_key2 , reference_key1 , message_text)
values (
l_frozen_tax_del(i).invoice_id ,
l_frozen_tax_del(i).detail_tax_dist_id ,
l_frozen_tax_del(i).summary_tax_line_id,
'Frozen Tax Distribution Deleted by EBTax. This would cause orphan events.'
);
l_debug_info := 'Froze Dist Deleted: ' ||l_frozen_tax_dist_id;
FND_MESSAGE.SET_TOKEN('ERROR', 'Frozen Tax Distribution Deleted by EBTax. This would cause orphan events.');
l_debug_info := 'Delete tax distributions';
DELETE FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id IN
(SELECT /*+ leading(gt) cardinality(gt,1) */
dist.invoice_distribution_id
FROM zx_trx_headers_gt gt,
ap_invoice_distributions_all dist
WHERE gt.application_id = AP_ETAX_PKG.AP_APPLICATION_ID
AND gt.entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
AND gt.event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
AND gt.trx_id = dist.invoice_id
AND dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TIPV', 'TERV')
AND NOT EXISTS
(SELECT 'Tax Distributions'
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = dist.detail_tax_dist_id
AND NVL(SELF_ASSESSED_FLAG, 'N') = 'N')-- bug 7422547
); --Bug7634436
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Frozen Deleted Self Assessed Dist Count: '||sql%rowcount);
insert into ap_errors_gt(invoice_id , reference_key2 , reference_key1 , message_text)
values (
l_frozen_self_tax_del(i).invoice_id ,
l_frozen_self_tax_del(i).detail_tax_dist_id ,
l_frozen_self_tax_del(i).summary_tax_line_id,
'Frozen Self Assessed Tax Distribution Deleted by EBTax. This would cause orphan events.'
);
l_debug_info := 'Froze Self Assessed Dist Deleted: '||l_frozen_tax_dist_id;
FND_MESSAGE.SET_TOKEN('ERROR', 'Frozen Tax Distribution Deleted by EBTax. This would cause orphan events.');
l_debug_info := 'Get self assessed distributions to delete';
DELETE FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_distribution_id IN
(SELECT /*+ leading(gt) cardinality(gt,1) */
dist.invoice_distribution_id
FROM zx_trx_headers_gt gt,
ap_self_assessed_tax_dist_all dist
WHERE gt.application_id = AP_ETAX_PKG.AP_APPLICATION_ID
AND gt.entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
AND gt.event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
AND gt.trx_id = dist.invoice_id
AND dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND NOT EXISTS
(SELECT 'Tax Distributions'
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = dist.detail_tax_dist_id
AND NVL(SELF_ASSESSED_FLAG, 'N') = 'Y')-- bug 7422547
); --bug7634436
UPDATE ap_invoice_distributions_all aid
SET invoice_line_number = (SELECT line_number
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = aid.invoice_id
AND ail.summary_tax_line_id = aid.summary_tax_line_id
AND rownum = 1)
WHERE aid.invoice_distribution_id IN
(SELECT /*+ leading(gt) cardinality(gt,1) */
dist.invoice_distribution_id
FROM zx_trx_headers_gt gt,
ap_invoice_distributions_all dist
WHERE gt.application_id = 200
AND gt.entity_code = 'AP_INVOICES'
AND gt.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND gt.trx_id = dist.invoice_id
AND dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TIPV', 'TERV')
AND dist.invoice_line_number NOT IN
(SELECT line_number
FROM ap_invoice_lines_all al
WHERE al.invoice_id = dist.invoice_id)
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Orphan Distributions updated: '||sql%rowcount);
IF (insert_tax_dist%ISOPEN ) THEN
CLOSE insert_tax_dist;
SELECT *
FROM zx_transaction_lines_gt
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND trx_line_id = c_line_number;
SELECT *
FROM zx_import_tax_lines_gt
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND summary_tax_line_number = c_line_number;
SELECT ve.trx_id invoice_id,
ve.trx_line_id line_number,
ve.message_name rejection_code,
ve.summary_tax_line_number summary_tax_line_number,
ail.invoice_line_id invoice_line_id
FROM zx_validation_errors_gt ve,
ap_invoice_lines_interface ail
WHERE ve.application_id = 200
AND ve.entity_code = 'AP_INVOICES'
AND ve.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND ve.trx_id = P_Invoice_Header_Rec.invoice_id
AND ail.invoice_id = ve.trx_id;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
p_parent_table => 'AP_INVOICES_INTERFACE',
p_parent_id => rej_lines(i).invoice_id,
p_reject_code => rej_lines(i).rejection_code,
p_last_updated_by => l_user_id,
p_last_update_login => l_login_id,
p_calling_sequence => l_curr_calling_sequence) <> TRUE) THEN
RETURN FALSE;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
p_parent_table => 'AP_INVOICE_LINES_INTERFACE',
p_parent_id => rej_lines(i).invoice_line_id,
p_reject_code => rej_lines(i).rejection_code,
p_last_updated_by => l_user_id,
p_last_update_login => l_login_id,
p_calling_sequence => l_curr_calling_sequence) <> TRUE) THEN
RETURN FALSE;
ELSE -- there are no rejections for this invoice. Update pl/sql tables
-- with defaulted info
-- No need to update invoice header import pl/sql record since
-- eTax will not default any column.
-----------------------------------------------------------------
l_debug_info := 'Step 3: Loop through lines import pl/sql table';
l_debug_info := 'Step 4: Get trx and tax line info to update pl/sql '||
'table';
l_debug_info := 'Step 5: Update non-tax lines in pl/sql table';
l_debug_info := 'Step 6: Update tax lines in pl/sql table '||P_Invoice_Lines_Tab(i).line_number||
' Line Type'||P_Invoice_Lines_Tab(i).line_type_lookup_code;
l_debug_info := 'Step 7: Update tax line if tax only invoice in pl/sql table';
'update amount and base_amount for line(s)';
UPDATE ap_invoice_lines_all ail
SET (ail.amount, ail.base_amount) =
(SELECT NVL(ail.amount, 0) - SUM(NVL(zdl.tax_amt,0)),
NVL(ail.base_amount,0) - SUM(NVL(zdl.tax_amt_funcl_curr,0))
FROM zx_detail_tax_lines_gt zdl
WHERE zdl.application_id = 200
AND zdl.entity_code = 'AP_INVOICES'
AND zdl.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND zdl.trx_id = ail.invoice_id
AND zdl.trx_line_id = ail.line_number
AND NVL(zdl.self_assessed_flag, 'N') = 'N'
AND NVL(zdl.tax_amt_included_flag, 'N') = 'N')
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND ail.line_type_lookup_code NOT IN ('TAX', 'AWT');
SELECT message_text
INTO p_error_code
FROM zx_errors_gt
WHERE rownum = 1;
SELECT 'Y'
--- Start for bug 6485124
FROM zx_lines_det_factors
WHERE trx_id = p_invoice_id
AND application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN
('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND ROWNUM=1;
SELECT 'Y'
INTO l_tax_already_calculated_flag
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code <> 'AWT'
AND (tax_already_calculated_flag = 'Y'
OR summary_tax_line_id IS NOT NULL)
AND ROWNUM = 1;
SELECT 'Y'
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code <> 'AWT'
AND (tax_already_distributed_flag = 'Y'
OR detail_tax_dist_id IS NOT NULL)
AND (related_id IS NULL
OR related_id = invoice_distribution_id)
AND ROWNUM = 1;
SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE detail_tax_dist_id = P_Tax_Dist_Id
UNION /*Bug 13102935:added self assessed tax table*/
SELECT invoice_distribution_id
FROM AP_SELF_ASSESSED_TAX_DIST_ALL
WHERE detail_tax_dist_id = P_Tax_Dist_Id;
l_debug_info := 'Select values from ap_invoice_distributions for tax distribution id';
SELECT aid1.invoice_distribution_id parent_distribution_id,
ai.invoice_type_lookup_code ,
ail.line_type_lookup_code,
ail1.line_type_lookup_code parent_line_type_lookup_code,
ail1.line_number parent_line_number,
ail1.discarded_flag,
aid.posted_flag,
aid.encumbered_flag,
aid.reversal_flag,
aid.prepay_distribution_id,
aid.pa_addition_flag,
nvl(aid.match_status_flag,'N'),
aid1.po_distribution_id,
aid1.rcv_transaction_id,
aid1.amount,
aid1.prepay_amount_remaining
INTO l_parent_distribution_id,
l_invoice_type_lookup_code,
l_line_type_lookup_code,
l_parent_line_type_lookup_code,
l_parent_line_number,
l_discarded_flag,
l_encumbered_flag,
l_posted_flag,
l_reversal_flag,
l_prepay_distribution_id,
l_pa_addition_flag,
l_match_status_flag,
l_po_distribution_id,
l_rcv_transaction_id,
l_parent_dist_amount,
l_prepay_amount_remaining
FROM ap_invoice_distributions aid,
ap_invoice_distributions aid1,
ap_invoices ai,
ap_invoice_lines ail,
ap_invoice_lines ail1
WHERE ai.invoice_id = p_invoice_id
AND ail.invoice_id = ai.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.invoice_id = ai.invoice_id
AND aid1.invoice_id = ai.invoice_id
AND aid.invoice_distribution_id = p_tax_dist_id
/* Outer join is needed since charge_applicable_to_dist_id can be NULL
for Tax-Only lines */
AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id(+)
AND ail1.invoice_id = p_invoice_id
AND aid1.invoice_line_number = ail1.line_number(+);
SELECT zl.discarded_flag
INTO l_discarded_flag
FROM zx_lines zl,
zx_rec_nrec_dist zd,
ap_invoice_distributions aid
WHERE zl.tax_line_id = zd.tax_line_id
AND nvl(zl.reporting_only_flag, 'N') = 'N'
AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
AND aid.invoice_distribution_id = p_tax_dist_id; */
| FUNCTION - Is_Tax_Line_Delete_Allowed()
|
| DESCRIPTION
| This function will return TRUE when Detail Tax line can be deleted in ETAX,
| else will return FALSE.
|
| When the function returns TRUE, then user can delete the TAX line else
| should not be allowed to delete the TAX line.
|
| PARAMETERS
| P_Invoice_Id - Is the invoice_id of the of the Invoice which owns this
| detail tax line indirectly through the summary tax line.
| P_Detail_Tax_Line_Id - Is the id for a Detail Tax Line in eTax
|
| USAGE: This function is called from ETAX security functions in APXINWKB.fmb
| from the form procedure 'IS_TAX_LINE_DELETE_ALLOWED'.
|
| MODIFICATION HISTORY
| DATE Author Action
| 04-JUL-2004 SMYADAM Created
|
*============================================================================*/
FUNCTION IS_TAX_LINE_DELETE_ALLOWED(P_Invoice_Id IN NUMBER,
P_Detail_Tax_Line_Id IN NUMBER,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
CURSOR Tax_Distributions IS
SELECT aid.invoice_distribution_id
FROM ap_invoice_distributions aid,
zx_rec_nrec_dist zd
WHERE aid.invoice_id = p_invoice_id
AND aid.detail_tax_dist_id = zd.rec_nrec_tax_dist_id
AND zd.tax_line_id = p_detail_tax_line_id;
l_tax_line_delete_allowed varchar2(1) := 'Y';
l_api_name CONSTANT VARCHAR2(100) := 'IS_TAX_LINE_DELETE_ALLOWED';
l_curr_calling_sequence := p_calling_sequence ||'->AP_Etax_Utility_Pkg.Is_Tax_Line_Delete_Allowed';
EXIT WHEN tax_distributions%NOTFOUND OR l_tax_line_delete_allowed = 'N';
l_tax_line_delete_allowed := 'N';
l_debug_info := 'Tax Line Delete Allowed '||l_tax_line_delete_allowed;
IF (l_tax_line_delete_allowed = 'N') THEN
RETURN(FALSE);
END IS_TAX_LINE_DELETE_ALLOWED;
SELECT territory_short_name
FROM fnd_territories_tl
WHERE territory_code = c_ctry_code
AND language = userenv ('LANG');
SELECT ai.invoice_num tax_related_invoice_num
FROM ap_invoices ai
WHERE ai.invoice_id = c_inv_id;
SELECT classification_name
FROM zx_fc_intended_use_v
WHERE classification_code = c_class_code
AND country_code = c_ctry_code;
SELECT classification_name
FROM zx_fc_business_categories_v
WHERE classification_code = c_class_code
AND (country_code = c_txn_ctry OR
country_code IS NULL);
SELECT classification_name
FROM zx_fc_product_fiscal_v
WHERE classification_code = c_class_code
AND (country_code = c_txn_ctry OR
country_code IS NULL);
SELECT classification_name
FROM zx_fc_user_defined_v
WHERE classification_code = c_class_code
AND (country_code = c_txn_ctry OR
country_code IS NULL);
SELECT classification_name
FROM zx_fc_intended_use_v
WHERE classification_code = c_class_code
AND (country_code = c_txn_ctry OR
country_code IS NULL);
SELECT classification_name
FROM zx_product_types_v
WHERE classification_code = c_class_code;
SELECT classification_name
FROM zx_fc_product_categories_v
WHERE classification_code = c_class_code
AND (country_code = c_txn_ctry OR
country_code IS NULL);
SELECT vl.tax_regime_name
FROM zx_regimes_vl vl
WHERE vl.country_code = c_txn_ctry
AND vl.tax_regime_code = c_tax_regime_code;
SELECT tax_full_name
FROM zx_sco_taxes
WHERE tax_regime_code = c_tax_regime
AND tax = c_tax;
SELECT tax_jurisdiction_name
FROM zx_jurisdictions_vl
WHERE tax_regime_code = c_tax_regime
AND tax = c_tax
AND tax_jurisdiction_code = c_tax_jurisdiction;
SELECT tax_status_name
FROM zx_sco_status
WHERE tax_regime_code = c_tax_regime
AND tax = c_tax
AND tax_status_code = c_tax_status;
PROCEDURE insert_tax_distributions(
p_invoice_header_rec IN ap_invoices_all%ROWTYPE,
p_inv_dist_rec IN r_ins_tax_dist_info,
p_dist_code_combination_id IN NUMBER,
p_user_id IN NUMBER,
p_sysdate IN DATE,
p_login_id IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_curr_calling_sequence VARCHAR2(2000);
l_api_name := 'Insert_Tax_Distributions';
l_curr_calling_sequence := 'Insert_Tax_Distributions <- '||p_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ETAX_UTILITY_PKG.Insert_Tax_Distributions(+)');
l_debug_info := 'Step 11: Insert new distributions including variances';
select ap_invoice_distributions_s.NEXTVAL into l_invoice_distribution_id from dual;
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_rate_name,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
extra_po_erv,
prepay_tax_diff_amount,
--Freight and Special Charges
rcv_charge_addition_flag,
pay_awt_group_id) --Bug8345264
VALUES (
p_inv_dist_rec.accounting_date, -- accounting_date
'N', -- accrual_posted_flag
'U', -- assets_addition_flag
p_inv_dist_rec.par_dist_assets_tracking_flag, -- assets_tracking_flag -- Bug 13821160
'N', -- cash_posted_flag
l_dist_line_num, -- distribution_line_number /*bug 10282701*/
P_dist_code_combination_id, -- dist_code_combination_id
p_inv_dist_rec.invoice_id, -- invoice_id
P_user_id, -- last_updated_by
P_sysdate, -- last_update_date
p_inv_dist_rec.line_type_lookup_code,
-- line_type_lookup_code
p_inv_dist_rec.period_name, -- period_name
p_inv_dist_rec.set_of_books_id, -- set_of_books_id
p_inv_dist_rec.amount, -- amount
p_inv_dist_rec.base_amount, -- base_amount -- bug 10350068
p_inv_dist_rec.batch_id, -- batch_id
P_user_id, -- created_by
P_sysdate, -- creation_date
p_inv_dist_rec.description, -- description
NULL, -- final_match_flag
p_inv_dist_rec.income_tax_region, -- income_tax_region
P_login_id, -- last_update_login
NULL, -- match_status_flag
'N', -- posted_flag
p_inv_dist_rec.po_distribution_id, -- po_distribution_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- quantity_invoiced
NULL, -- request_id
'N', -- reversal_flag
p_inv_dist_rec.type_1099, -- type_1099
NULL, -- unit_price
'N', -- encumbered_flag
NULL, -- stat_amount
p_inv_dist_rec.attribute1, -- attribute1
p_inv_dist_rec.attribute10, -- attribute10
p_inv_dist_rec.attribute11, -- attribute11,
p_inv_dist_rec.attribute12, -- attribute12
p_inv_dist_rec.attribute13, -- attribute13
p_inv_dist_rec.attribute14, -- attribute14
p_inv_dist_rec.attribute15, -- attribute15
p_inv_dist_rec.attribute2, -- attribute2
p_inv_dist_rec.attribute3, -- attribute3
p_inv_dist_rec.attribute4, -- attribute4
p_inv_dist_rec.attribute5, -- attribute5
p_inv_dist_rec.attribute6, -- attribute6
p_inv_dist_rec.attribute7, -- attribute7
p_inv_dist_rec.attribute8, -- attribute8
p_inv_dist_rec.attribute9, -- attribute9
p_inv_dist_rec.attribute_category, -- attribute_category
p_inv_dist_rec.expenditure_item_date,
-- expenditure_item_date
p_inv_dist_rec.expenditure_organization_id,
-- expenditure_organization_id
p_inv_dist_rec.expenditure_type, -- expenditure_type
p_inv_dist_rec.parent_invoice_id, -- parent_invoice_id
p_inv_dist_rec.pa_addition_flag, -- pa_addition_flag
p_inv_dist_rec.pa_quantity, -- pa_quantity
NULL, -- prepay_amount_remaining
-- the prepay_amount_remaining will be populated for all the
-- prepayment distributions during the payment. And later will be
-- updated during the prepayment applications
p_inv_dist_rec.project_accounting_context,
-- project_accounting_context
p_inv_dist_rec.project_id, -- project_id
p_inv_dist_rec.task_id, -- task_id
NULL, -- packet_id
'N', -- awt_flag
p_inv_dist_rec.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
p_inv_dist_rec.org_id, -- org_id
NULL, -- awt_invoice_payment_id
p_inv_dist_rec.global_attribute_category,
-- global_attribute_category
p_inv_dist_rec.global_attribute1, -- global_attribute1
p_inv_dist_rec.global_attribute2, -- global_attribute2
p_inv_dist_rec.global_attribute3, -- global_attribute3
p_inv_dist_rec.global_attribute4, -- global_attribute4
p_inv_dist_rec.global_attribute5, -- global_attribute5
p_inv_dist_rec.global_attribute6, -- global_attribute6
p_inv_dist_rec.global_attribute7, -- global_attribute7
p_inv_dist_rec.global_attribute8, -- global_attribute8
p_inv_dist_rec.global_attribute9, -- global_attribute9
p_inv_dist_rec.global_attribute10, -- global_attribute10
p_inv_dist_rec.global_attribute11, -- global_attribute11
p_inv_dist_rec.global_attribute12, -- global_attribute12
p_inv_dist_rec.global_attribute13, -- global_attribute13
p_inv_dist_rec.global_attribute14, -- global_attribute14
p_inv_dist_rec.global_attribute15, -- global_attribute15
p_inv_dist_rec.global_attribute16, -- global_attribute16
p_inv_dist_rec.global_attribute17, -- global_attribute17
p_inv_dist_rec.global_attribute18, -- global_attribute18
p_inv_dist_rec.global_attribute19, -- global_attribute19
p_inv_dist_rec.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
p_inv_dist_rec.award_id, -- award_id
NULL, -- credit_card_trx_id
p_inv_dist_rec.dist_match_type, -- dist_match_type
p_inv_dist_rec.rcv_transaction_id, -- rcv_transaction_id
l_invoice_distribution_id, -- invoice_distribution_id
NULL, -- parent_reversal_id
p_inv_dist_rec.tax_recoverable_flag,
-- tax_recoverable_flag
p_inv_dist_rec.merchant_document_number, -- merchant_document_number --Bug14658408: Start
p_inv_dist_rec.merchant_name, -- merchant_name
p_inv_dist_rec.merchant_reference, -- merchant_reference
p_inv_dist_rec.merchant_tax_reg_number, -- merchant_tax_reg_number
p_inv_dist_rec.merchant_taxpayer_id, -- merchant_taxpayer_id --Bug14658408: End
NULL, -- country_of_supply
NULL, -- matched_uom_lookup_code
NULL, -- gms_burdenable_raw_cost
NULL, -- accounting_event_id
p_inv_dist_rec.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
p_inv_dist_rec.cancellation_flag, -- cancellation_flag
p_inv_dist_rec.invoice_line_number,-- invoice_line_number
p_inv_dist_rec.corrected_invoice_dist_id,
-- corrected_invoice_dist_id
p_inv_dist_rec.rounding_amt, -- rounding_amt
p_inv_dist_rec.charge_applicable_to_dist_id,
-- charge_applicable_to_dist_id
NULL, -- corrected_quantity
NULL, -- related_id
NULL, -- asset_book_type_code
NULL, -- asset_category_id
p_inv_dist_rec.distribution_class, -- distribution_class
p_inv_dist_rec.tax_code_id, -- tax_code_id
NULL, -- intended_use,
p_inv_dist_rec.detail_tax_dist_id, -- detail_tax_dist_id
p_inv_dist_rec.rec_nrec_rate, -- rec_nrec_rate
p_inv_dist_rec.recovery_rate_id, -- recovery_rate_id
p_inv_dist_rec.recovery_rate_name, -- recovery_rate_name
p_inv_dist_rec.recovery_type_code, -- recovery_type_code
NULL, -- withholding_tax_code_id,
p_inv_dist_rec.taxable_amount, -- taxable_amount
p_inv_dist_rec.taxable_base_amount, -- taxable_base_amount
NULL, -- tax_already_distributed_flag
p_inv_dist_rec.summary_tax_line_id, -- summary_tax_line_id
p_inv_dist_rec.extra_po_erv, -- extra_po_erv
p_inv_dist_rec.prepay_tax_diff_amount, -- prepay_tax_diff_amount
'N', -- rcv_charge_addition_flag
p_inv_dist_rec.pay_awt_group_id --pay_awt_group_id Bug8345264
);
l_debug_info := 'Call the gms_ap_api.CREATE_AWARD_DISTRIBUTIONS from insert_tax_distributions';
l_debug_info := 'Return from gms_ap_api.CREATE_AWARD_DISTRIBUTIONS to insert_tax_distributions';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ETAX_UTILITY_PKG.Insert_Tax_Distributions(-)');
SELECT matching_basis, payment_type, shipment_type
INTO l_matching_basis, l_payment_type, l_shipment_type
FROM po_line_locations_all
WHERE line_location_id = P_Line_Location_Id;
SELECT 'Y'
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code <> 'AWT'
AND (tax_already_distributed_flag = 'Y'
OR detail_tax_dist_id IS NOT NULL)
AND (related_id IS NULL
OR related_id = invoice_distribution_id)
AND ROWNUM = 1;
SELECT D.unit_price unit_price,
pll.matching_basis match_basis,
pll.match_option match_option,
pl.unit_meas_lookup_code po_uom,
D.matched_uom_lookup_code rcv_uom,
rsl.item_id rcv_item_id
FROM ap_invoice_distributions_all D,
po_distributions_all PD,
po_lines_all PL,
po_line_locations_all PLL,
rcv_transactions RTXN,
rcv_shipment_lines RSL
WHERE D.invoice_distribution_id = c_inv_dist_id
AND D.po_distribution_id = PD.po_distribution_id
AND PL.po_header_id = PD.po_header_id
AND PL.po_line_id = PD.po_line_id
AND PD.line_location_id = PLL.line_location_id
AND D.rcv_transaction_id = RTXN.transaction_id
AND RTXN.shipment_line_id = RSL.shipment_line_id;
SELECT nvl(max(distribution_line_number),0)
INTO l_max_dist_line_num
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = X_invoice_id
AND invoice_line_number = X_line_number;
SELECT aid.pay_awt_group_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = c_prepay_dist_id;
SELECT COUNT(posted_flag)
INTO l_Posted_count
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND NVL(posted_flag,'N') = 'Y';
SELECT count(line_number)
INTO l_Prepay_line_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code ='PREPAY'
AND (NVL(discarded_flag,'N') <> 'Y');
SELECT count(prepay_distribution_id)
INTO l_Prepay_dist_count
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND prepay_distribution_id IS NOT NULL
AND (NVL(reversal_flag,'N') <> 'Y');
SELECT Historical_flag,
Awt_Flag,
quick_credit,
Invoice_type_lookup_code
INTO l_Hist_Flag,
l_Awt_Computed,
l_quick_credit,
l_Invoice_Type
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT count(invoice_id)
INTO l_Payment_Stat_Flag
FROM ap_invoice_payments_all
WHERE invoice_id = p_invoice_id
AND NVL(reversal_flag,'N') <> 'Y';
SELECT COUNT(CORRECTED_INV_ID)
INTO l_corr_inv_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND CORRECTED_INV_ID IS NOT NULL
AND (NVL(discarded_flag,'N') <> 'Y');
SELECT count(line_number)
INTO l_manual_Awt_line
FROM ap_invoice_lines_all ail
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code ='AWT'
AND (NVL(discarded_flag,'N') <> 'Y')
AND line_source = 'MANUAL LINE ENTRY'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number);
SELECT COUNT(1)
INTO l_Awt_line_count
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND NVL(aid.reversal_flag,'N') <> 'Y'
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_related_id IN
(SELECT aid1.invoice_distribution_id
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = p_line_number
AND aid1.line_type_lookup_code <> 'AWT');
SELECT COUNT(1)
INTO l_Encumbered_count
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND NVL(encumbered_flag,'N') = 'Y';
SELECT COUNT(po_distribution_id)
INTO l_Po_Dist_id_count
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND po_distribution_id IS NOT NULL;
SELECT COUNT(po_header_id)
INTO l_Po_line_num_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND po_header_id IS NOT NULL;
l_allow_update NUMBER ;
SELECT COUNT('INCLUSIVE TAX CALC ON POSTED DISTS')
INTO l_allow_update
FROM zx_rec_nrec_dist ZD
WHERE ZD.application_id = 200
AND ZD.entity_code = 'AP_INVOICES'
AND ZD.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND ZD.trx_id = p_invoice_id
AND ZD.inclusive_flag = 'Y'
AND ZD.rec_nrec_tax_dist_id IN
( SELECT AID.detail_tax_dist_id
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.invoice_line_number = p_line_number
AND AID.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
AND AID.detail_tax_dist_id IS NOT NULL
AND EXISTS ( SELECT 'LINE HAS ANY POSTED DIST'
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id = AID.invoice_id
AND AID1.invoice_line_number = AID.invoice_line_number
AND AID1.line_type_lookup_code <> AID.line_type_lookup_code
AND AID1.invoice_distribution_id = AID.charge_applicable_to_dist_id
AND ( AID1.posted_flag = 'Y' OR
NVL( AID1.encumbered_flag, 'N' ) IN ('Y', 'D', 'W', 'X')
)
)
)
AND ROWNUM = 1 ;
SELECT COUNT('INCLUSIVE TAX CALC ON POSTED DISTS')
INTO l_allow_update
FROM zx_rec_nrec_dist ZD
WHERE ZD.application_id = 200
AND ZD.entity_code = 'AP_INVOICES'
AND ZD.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND ZD.trx_id = p_invoice_id
AND ZD.inclusive_flag = 'Y'
AND ZD.rec_nrec_tax_dist_id IN
( SELECT AID.detail_tax_dist_id
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
AND AID.detail_tax_dist_id IS NOT NULL
AND EXISTS ( SELECT 'LINE HAS ANY POSTED DIST'
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id = AID.invoice_id
AND AID1.invoice_line_number = AID.invoice_line_number
AND AID1.line_type_lookup_code <> AID.line_type_lookup_code
AND AID1.invoice_distribution_id = AID.charge_applicable_to_dist_id
AND ( AID1.posted_flag = 'Y' OR
NVL( AID1.encumbered_flag, 'N' ) IN ('Y', 'D', 'W', 'X')
)
)
)
AND ROWNUM = 1 ;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info||l_allow_update);
IF l_allow_update > 0 THEN
RETURN FALSE ;
/* SELECT 'Tax Only Line Exist'
INTO l_dummy
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TIPV', 'TRV', 'TERV')
AND charge_applicable_to_dist_id IS NULL
AND rownum = 1; */
SELECT 'Tax Only Line Exist'
INTO l_dummy
FROM zx_lines_summary zls
WHERE application_id = AP_ETAX_PKG.AP_APPLICATION_ID
AND entity_code = AP_ETAX_PKG.AP_ENTITY_CODE
AND event_class_code IN (AP_ETAX_PKG.AP_INV_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_PP_EVENT_CLASS_CODE,
AP_ETAX_PKG.AP_ER_EVENT_CLASS_CODE)
AND zls.trx_id = p_invoice_id
AND NVL(zls.reporting_only_flag, 'N') = 'N'
AND NVL(zls.tax_only_line_flag, 'N') = 'Y'
AND rownum = 1;
SELECT pd.code_combination_id
INTO l_default_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id = p_po_line_location_id
AND rownum = 1;
SELECT pd.code_combination_id
INTO l_default_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id =
(SELECT pod.line_location_id
FROM po_distributions_all pod
WHERE po_distribution_id = p_po_distribution_id )
AND rownum = 1;
SELECT pd.code_combination_id
INTO l_default_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id =
(SELECT rcv.po_line_location_id
FROM rcv_shipment_lines rcv
WHERE rcv.shipment_line_id = p_rcv_shipment_line_id )
AND rownum = 1;
SELECT pd.code_combination_id
INTO l_default_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id =
(SELECT rcv.po_line_location_id
FROM rcv_transactions rcv
WHERE rcv.transaction_id = p_rcv_transaction_id )
AND rownum = 1;
SELECT aerd.code_combination_id
INTO l_default_ccid
FROM ap_exp_report_dists_all aerd,
ap_expense_report_lines_all aerl,
ap_invoices_all ai
WHERE aerd.report_header_id = p_reference_key1
AND aerd.report_line_id = p_reference_key2
AND aerd.report_line_id = aerl.report_line_id
AND aerd.report_header_id = aerl.report_header_id
AND ai.invoice_id = p_invoice_id
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND rownum = 1;
SELECT distribution_set_id
INTO l_distribution_set_id
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = p_invoice_id
AND AIL.line_number = p_invoice_line_number ;
SELECT dist_code_combination_id
INTO l_default_ccid
FROM ap_distribution_set_lines ADSL
WHERE ADSL.distribution_set_id = l_distribution_set_id
AND rownum = 1 ;
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, added included_tax_amount as modified the def
--of ap_invoices_pkg.r_invoice_line_rec
INCLUDED_TAX_AMOUNT,
PRIMARY_INTENDED_USE,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
--bugfix:4674194
SHIP_TO_LOCATION_ID,
--bug7022001
PAY_AWT_GROUP_ID
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number;
SELECT attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_invoice_attribute_rec.attribute_category,
l_invoice_attribute_rec.attribute1,
l_invoice_attribute_rec.attribute2,
l_invoice_attribute_rec.attribute3,
l_invoice_attribute_rec.attribute4,
l_invoice_attribute_rec.attribute5,
l_invoice_attribute_rec.attribute6,
l_invoice_attribute_rec.attribute7,
l_invoice_attribute_rec.attribute8,
l_invoice_attribute_rec.attribute9,
l_invoice_attribute_rec.attribute10,
l_invoice_attribute_rec.attribute11,
l_invoice_attribute_rec.attribute12,
l_invoice_attribute_rec.attribute13,
l_invoice_attribute_rec.attribute14,
l_invoice_attribute_rec.attribute15
FROM ap_invoices_all
WHERE invoice_id = l_invoice_line_rec.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_invoice_line_rec.set_of_books_id
AND ap.org_id = l_invoice_line_rec.org_id; -- Bug 13858818;
SELECT invoice_date, vendor_id , invoice_type_lookup_code , invoice_currency_code ,
exchange_rate_type , exchange_date , exchange_rate
INTO l_invoice_date , l_vendor_id , l_invoice_type_lookup_code , l_invoice_currency ,
l_exchange_rate_type , l_exchange_date , l_exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_invoice_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 = l_vendor_id ;
SELECT default_code_comb_id
INTO l_employee_ccid
FROM PER_ASSIGNMENTS_F
WHERE person_id = l_employee_id
AND set_of_books_id = l_invoice_line_rec.set_of_books_id
AND trunc(sysdate) BETWEEN trunc(effective_start_date)
AND nvl(trunc(effective_end_date), trunc(sysdate));
SELECT WEB_PARAMETER_ID
INTO l_web_parameter_id
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number;
p_default_last_updated_by => FND_GLOBAL.user_id,
p_default_last_update_login => FND_GLOBAL.login_id,
p_pa_default_dist_ccid => l_dist_ccid,
p_pa_concatenated_segments => l_dist_code_concat, --OUT NOCOPY
p_debug_Info => debug_Info, --OUT NOCOPY
p_debug_Context => debug_Context, --OUT NOCOPY
p_calling_sequence => 'Get_Proj_And_Acc_For_Chrg_Dist' ,
p_default_dist_ccid => l_invoice_line_rec.default_dist_ccid --IN bug 5386396
))) THEN
debug_info := debug_info || ': Error encountered in pa_flexbuild';
l_dont_allow_update NUMBER ;
SELECT COUNT('DISTS POSTED OR ENCUMBERED')
INTO l_dont_allow_update
FROM ap_invoice_distributions_all AId
WHERE AID.invoice_id = p_invoice_id
AND AID.invoice_line_number = p_line_number
AND (AID.posted_flag = 'Y' OR
NVL(AID.encumbered_flag, 'N' ) IN ('Y', 'D', 'W', 'X'))
AND ROWNUM = 1 ;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info||l_dont_allow_update);
IF l_dont_allow_update > 0 THEN
RETURN TRUE ;