[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;
FUNCTION Update_Distributions
(P_Invoice_header_rec IN ap_invoices_all%ROWTYPE,
P_Calling_Mode IN VARCHAR2,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN;
SELECT SHIP_TO_ORGANIZATION_ID
INTO l_po_ship_to_org_id
FROM po_line_locations_all pll
WHERE pll.line_location_id = p_po_line_location_Id;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = c_invoice_Id;
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_type_lookup_code NOT IN ('TAX', 'AWT');
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_number = c_line_number
AND line_type_lookup_code NOT IN ('TAX', 'AWT');
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_type_lookup_code = 'TAX'
AND summary_tax_line_id IS NULL;
CURSOR c_selected_invoices IS
SELECT trx_id, event_class_code
FROM zx_trx_headers_gt
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);
UPDATE ap_invoice_lines_all ail
SET tax_already_calculated_flag = NULL
WHERE ail.invoice_id IN (SELECT DISTINCT(trx_id)
FROM zx_trx_headers_gt
WHERE application_id = 200
AND entity_code = 'AP_INVOICES')
AND NVL(ail.tax_already_calculated_flag, 'N') = 'Y'
AND NOT EXISTS
(SELECT 'Line Determining Factors Exist'
FROM zx_lines_det_factors zf
WHERE zf.application_id = 200
AND zf.entity_code = 'AP_INVOICES'
AND zf.event_class_code IN ('STANDARD INVOICES',
'PREPAYMENT INVOICES',
'EXPENSE REPORTS')
-- bug 7233679
AND ZF.TRX_LEVEL_TYPE = 'LINE'
AND ZF.INTERNAL_ORGANIZATION_ID = AIL.ORG_ID
AND ZF.lEDGER_ID=AIL.SET_OF_BOOKS_ID
-- bug 7233679
AND zf.trx_id = ail.invoice_id
AND zf.trx_line_id = ail.line_number);
l_debug_info := l_count ||' rows updated in ap_invoice_lines_all.';
DELETE FROM ZX_TRX_HEADERS_GT;
UPDATE ap_invoice_lines_all ail
SET tax_already_calculated_flag = NULL
WHERE ail.invoice_id = p_invoice_id
AND nvl(ail.tax_already_calculated_flag, 'N') = 'Y'
AND NOT EXISTS
(SELECT 'Line Determining Factors Exist'
from zx_lines_det_factors zf
where zf.application_id = 200
and zf.entity_code = 'AP_INVOICES'
and zf.event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
and zf.trx_id = ail.invoice_id
and zf.trx_line_id = ail.line_number);
l_debug_info := l_count ||' rows updated in ap_invoice_lines_all.';
DELETE FROM ZX_TRANSACTION_LINES_GT;
DELETE FROM ZX_IMPORT_TAX_LINES_GT;
DELETE FROM ZX_TRX_TAX_LINK_GT;
AP_ETAX_SERVICES_PKG.G_SITE_ATTRIBUTES.DELETE;
AP_ETAX_SERVICES_PKG.G_ORG_ATTRIBUTES.DELETE;
OPEN C_SELECTED_INVOICES;
FETCH C_SELECTED_INVOICES
BULK COLLECT INTO AP_ETAX_PKG.G_INV_ID_LIST,
AP_ETAX_PKG.G_EVNT_CLS_LIST
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN (C_SELECTED_INVOICES%NOTFOUND
AND AP_ETAX_PKG.G_INV_ID_LIST.COUNT <= 0);
DELETE FROM zx_trx_headers_gt
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 trx_id = l_inv_header_rec.invoice_id;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
CLOSE C_SELECTED_INVOICES;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
OPEN C_SELECTED_INVOICES;
FETCH C_SELECTED_INVOICES
BULK COLLECT INTO AP_ETAX_PKG.G_INV_ID_LIST,
AP_ETAX_PKG.G_EVNT_CLS_LIST
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN (C_SELECTED_INVOICES%NOTFOUND
AND AP_ETAX_PKG.G_INV_ID_LIST.COUNT <= 0);
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
CLOSE C_SELECTED_INVOICES;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_type_lookup_code NOT IN ('TAX', 'AWT');
l_debug_info := 'Step 2: Update trx_id in header';
UPDATE zx_trx_headers_gt
SET trx_id = P_Invoice_Id
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 trx_id = P_Interface_Invoice_Id;
UPDATE ap_invoice_lines_all ail
SET ail.tax_already_calculated_flag = NULL
WHERE ail.invoice_id = p_invoice_id
AND ail.line_type_lookup_code NOT IN ('TAX', 'AWT')
AND NVL(ail.tax_already_calculated_flag, 'N') = 'Y';
l_debug_info := 'Step 3: Update trx_id in Lines';
UPDATE zx_transaction_lines_gt
SET trx_id = P_Invoice_Id
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 trx_id = P_Interface_Invoice_Id;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = c_invoice_id;
SELECT aid.*
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.invoice_id = c_invoice_id
AND aid.line_type_lookup_code NOT IN
('AWT', 'REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (aid.line_type_lookup_code <> 'RETAINAGE'
or (aid.line_type_lookup_code = 'RETAINAGE'
and ail.line_type_lookup_code = 'RETAINAGE RELEASE'))
AND (related_id IS NULL
or related_id = invoice_distribution_id)
AND (aid.prepay_distribution_id IS NULL
or (aid.prepay_distribution_id IS NOT NULL
and ail.line_type_lookup_code = 'PREPAY'))
AND nvl(ail.discarded_flag, 'N') <> 'Y'
AND p_calling_mode <> 'DISTRIBUTE RECOUP'
UNION
SELECT aid.*
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.invoice_id = c_invoice_id
AND ail.line_type_lookup_code <> 'PREPAY'
AND aid.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id IS NOT NULL
AND p_calling_mode = 'DISTRIBUTE RECOUP';
SELECT aid.*
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.invoice_id = c_invoice_id
AND ail.line_number = c_line_number
AND aid.line_type_lookup_code NOT IN
('AWT', 'REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (related_id IS NULL
or related_id = invoice_distribution_id)
AND (aid.prepay_distribution_id IS NULL
or (aid.prepay_distribution_id IS NOT NULL
and ail.line_type_lookup_code = 'PREPAY'))
AND nvl(ail.discarded_flag, 'N') <> 'Y'
AND p_calling_mode <> 'DISTRIBUTE RECOUP'
UNION
SELECT aid.*
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.invoice_id = c_invoice_id
AND ail.line_number = c_line_number
AND ail.line_type_lookup_code <> 'PREPAY'
AND aid.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id IS NOT NULL
AND p_calling_mode = 'DISTRIBUTE RECOUP';
CURSOR c_selected_invoices IS
SELECT trx_id, event_class_code
FROM zx_trx_headers_gt
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);
SELECT amount,
NVL(included_tax_amount,0) included_tax_amount,
line_number,
(NVL(total_rec_tax_amt_funcl_curr,0) + NVL(total_nrec_tax_amt_funcl_curr,0)) base_included_tax_amount
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY') -- bug7338249
AND included_tax_amount IS NOT NULL ;--Bug6874234
SELECT amount,
NVL(included_tax_amount,0) included_tax_amount,
line_number,
(NVL(total_rec_tax_amt_funcl_curr,0) + NVL(total_nrec_tax_amt_funcl_curr,0)) base_included_tax_amount
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_number = c_line_number
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY') --bug7338249
AND included_tax_amount IS NOT NULL ;--Bug6874234
DELETE FROM ZX_TRX_HEADERS_GT;
DELETE FROM zx_itm_distributions_gt;
OPEN C_SELECTED_INVOICES;
FETCH C_SELECTED_INVOICES
BULK COLLECT INTO AP_ETAX_PKG.G_INV_ID_LIST,
AP_ETAX_PKG.G_EVNT_CLS_LIST
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN (C_SELECTED_INVOICES%NOTFOUND
AND AP_ETAX_PKG.G_INV_ID_LIST.COUNT <= 0);
SELECT count(1)
INTO l_tax_only_invoice
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = ap_etax_pkg.g_inv_id_list(i)
AND ail.line_type_lookup_code = 'TAX'
AND NOT EXISTS --To Make Sure We do not call Determine Recovery If we do not have any lines to process
(SELECT 'No Non Tax Line'
FROM ap_invoice_lines_all ail2
WHERE ail2.invoice_id = ail.Invoice_id
AND ail2.line_type_lookup_code <> 'TAX');
SELECT count(1)
INTO l_tax_only_invoice
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_inv_header_rec.Invoice_id
AND ail.line_type_lookup_code = 'TAX'
AND NOT EXISTS --To Make Sure We do not call Determine Recovery If we do not have any lines to process
(SELECT 'No Non Tax Line'
FROM ap_invoice_lines_all ail2
WHERE ail2.invoice_id = ail.Invoice_id
AND ail2.line_type_lookup_code <> 'TAX');
l_debug_info := 'Update dist amount for included TAX (ITEM line no and tax amount) '||p_line_number||' '||i.included_tax_amount;
UPDATE ap_invoice_distributions_All aid1
SET aid1.amount = aid1.amount + nvl((SELECT SUM(nvl(amount,0))
FROM ap_invoice_distributions_All aid2
WHERE aid2.invoice_id = p_invoice_id
AND aid2.invoice_line_number = i.line_number
AND aid2.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')),0),
aid1.base_amount = aid1.base_amount + nvl((SELECT SUM(nvl(base_amount,0))
FROM ap_invoice_distributions_All aid3
WHERE aid3.invoice_id = p_invoice_id
AND aid3.invoice_line_number = i.line_number
AND aid3.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')),0)
WHERE aid1.invoice_id = p_invoice_id
AND aid1.invoice_line_number = i.line_number
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY');--/*Bug7338249*/
l_debug_info := 'Test: Update dist amount for included TAX (tax amount) '|| i.included_tax_amount;
UPDATE ap_invoice_distributions_All aid1
SET aid1.amount = aid1.amount + nvl((SELECT SUM(nvl(amount,0))
FROM ap_invoice_distributions_All aid2
WHERE aid2.invoice_id = p_invoice_id
AND aid2.invoice_line_number = i.line_number
AND aid2.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')),0),
aid1.base_amount =aid1.base_amount + nvl((SELECT SUM(nvl(base_amount,0))
FROM ap_invoice_distributions_All aid3
WHERE aid3.invoice_id = p_invoice_id
AND aid3.invoice_line_number = i.line_number
AND aid3.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')),0)
WHERE aid1.invoice_id = p_invoice_id
AND aid1.invoice_line_number = i.line_number
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY');--/*Bug7338249*/
UPDATE /*+ ROWID (AID) */ AP_Invoice_Distributions_All AID
SET (RECOVERY_RATE_CODE,
RECOVERY_RATE_ID,
RECOVERY_TYPE_CODE) =
(SELECT REC.Tax_Rate_Code,
REC.Tax_Rate_ID,
REC.Recovery_Type_Code
FROM ZX_Rates_B RATE,
ZX_Rates_B REC
WHERE RATE.Tax_Rate_ID = AID.Tax_Code_ID
AND RATE.Tax_Regime_Code = REC.Tax_Regime_Code
AND RATE.Tax = REC.Tax
AND RATE.Tax_Status_Code = REC.Tax_Status_Code
AND RATE.Content_Owner_ID = REC.Content_Owner_ID
AND REC.Rate_type_code = 'RECOVERY'
AND REC.Effective_From <= AID.Accounting_Date
AND NVL(REC.Effective_To, AID.Accounting_Date) >= AID.Accounting_Date
AND REC.Active_Flag = 'Y'
AND REC.Percentage_Rate = AID.Rec_NRec_Rate
AND REC.Tax_Rate_Code = 'STANDARD-' || REC.Percentage_Rate
AND AID.Line_Type_Lookup_Code = 'REC_TAX')
WHERE AID.invoice_id = l_inv_header_rec.invoice_id
AND AID.historical_flag = 'Y'
AND AID.line_type_lookup_code = 'REC_TAX'
AND AID.recovery_rate_code Is Null
AND AID.recovery_rate_id Is Null
AND AID.recovery_type_code Is Null;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
CLOSE C_SELECTED_INVOICES;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
OPEN C_SELECTED_INVOICES;
FETCH C_SELECTED_INVOICES
BULK COLLECT INTO AP_ETAX_PKG.G_INV_ID_LIST,
AP_ETAX_PKG.G_EVNT_CLS_LIST
LIMIT AP_ETAX_PKG.G_BATCH_LIMIT;
EXIT WHEN (C_SELECTED_INVOICES%NOTFOUND
AND AP_ETAX_PKG.G_INV_ID_LIST.COUNT <= 0);
l_debug_info := 'Update Invoice Distributions';
IF NOT(AP_ETAX_SERVICES_PKG.Update_Distributions(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
CLOSE C_SELECTED_INVOICES;
AP_ETAX_PKG.G_INV_ID_LIST.DELETE;
AP_ETAX_PKG.G_EVNT_CLS_LIST.DELETE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
l_debug_info := 'Step 2: Update Header';
UPDATE zx_trx_headers_gt
SET trx_id = P_Invoice_Id
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 trx_id = P_Interface_Invoice_Id;
l_debug_info := 'Step 3: Update trx_id in Lines';
UPDATE zx_transaction_lines_gt
SET trx_id = P_Invoice_Id
WHERE trx_id = P_Interface_Invoice_Id;
l_debug_info := 'Step 4: Update trx_id in tax Lines';
UPDATE zx_import_tax_lines_gt
SET trx_id = P_Invoice_Id
WHERE trx_id = P_Interface_Invoice_Id;
l_debug_info := 'Step 5: Update trx_id in the allocation structure '||
'etax table';
UPDATE zx_trx_tax_link_gt
SET trx_id = P_Invoice_Id
WHERE trx_id = P_Interface_Invoice_Id;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id
AND quick_credit = 'Y'
AND credited_invoice_id IS NOT NULL;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = c_credited_inv;
INSERT INTO zx_rev_trx_headers_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
legal_entity_id,
trx_number
) VALUES
(l_inv_header_rec.org_id,
200,
'AP_INVOICES',
l_event_class_code_credited,
l_inv_header_rec.invoice_id,
l_inv_header_rec.legal_entity_id,
l_inv_header_rec.invoice_num);
INSERT INTO zx_reverse_trx_lines_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id
)
SELECT
l_inv_header_rec.org_id, -- internal_organization_id
200, -- reversing_appln_id
'AP_INVOICES', -- reversing_entity_code
l_event_class_code_crediting, -- reversing_evnt_cls_code
ail.invoice_id, -- reversing_trx_id
'LINE', -- reversing_trx_level_type
ail.line_number, -- reversing_trx_line_id
200, -- reversed_appln_id
'AP_INVOICES', -- reversed_entity_code
l_event_class_code_credited, -- reversed_evnt_cls_code
aic.invoice_id, -- reversed_trx_id
'LINE', -- reversed_trx_level_type
aic.line_number -- reversed_trx_line_id
FROM ap_invoice_lines_all ail,
ap_invoice_lines_all aic
WHERE ail.invoice_id = l_inv_header_rec.invoice_id
AND ail.line_type_lookup_code NOT IN ('TAX', 'AWT')
AND ail.corrected_inv_id = aic.invoice_id
AND ail.corrected_line_number = aic.line_number;
INSERT INTO zx_reverse_dist_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversing_trx_line_dist_id,
reversing_tax_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id,
reversed_trx_line_dist_id,
reversed_tax_line_id
)
SELECT
l_inv_header_rec.org_id, -- internal_organization_id
200, -- reversing_appln_id
'AP_INVOICES', -- reversing_entity_code
l_event_class_code_crediting, -- reversing_evnt_cls_code
aid.invoice_id, -- reversing_trx_id
'LINE', -- reversing_trx_level_type
aid.invoice_line_number, -- reversing_trx_line_id
aid.invoice_distribution_id, -- reversing_trx_line_dist_id
NULL, -- reversing_tax_line_id
200, -- reversed_appln_id
'AP_INVOICES', -- reversed_entity_code
l_event_class_code_credited, -- reversed_evnt_cls_code
idc.invoice_id, -- reversed_trx_id
'LINE', -- reversed_trx_level_type
idc.invoice_line_number, -- reversed_trx_line_id
idc.invoice_distribution_id, -- reversed_trx_line_dist_id
NULL -- reversed_tax_line_id
FROM ap_invoice_distributions_all aid,
ap_invoice_distributions_all idc
WHERE aid.invoice_id = l_inv_header_rec.invoice_id
AND aid.line_type_lookup_code NOT IN
('AWT', 'REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (aid.related_id IS NULL
OR aid.related_id = aid.invoice_distribution_id)
AND aid.corrected_invoice_dist_id = idc.invoice_distribution_id;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = P_Invoice_Id
AND line_type_lookup_code NOT IN ('TAX', 'AWT');
l_debug_info := 'Step 1: Update ap_invoice_lines_all from eTax '||
'repository';
UPDATE ap_invoice_lines_all ail
SET
(ail.description,
ail.cancelled_flag,
ail.last_updated_by,
ail.last_update_login,
ail.last_update_date,
ail.attribute_category,
ail.attribute1,
ail.attribute2,
ail.attribute3,
ail.attribute4,
ail.attribute5,
ail.attribute6,
ail.attribute7,
ail.attribute8,
ail.attribute9,
ail.attribute10,
ail.attribute11,
ail.attribute12,
ail.attribute13,
ail.attribute14,
ail.attribute15,
ail.global_attribute_category,
ail.global_attribute1,
ail.global_attribute2,
ail.global_attribute3,
ail.global_attribute4,
ail.global_attribute5,
ail.global_attribute6,
ail.global_attribute7,
ail.global_attribute8,
ail.global_attribute9,
ail.global_attribute10,
ail.global_attribute11,
ail.global_attribute12,
ail.global_attribute13,
ail.global_attribute14,
ail.global_attribute15,
ail.global_attribute16,
ail.global_attribute17,
ail.global_attribute18,
ail.global_attribute19,
ail.global_attribute20 ) = (
SELECT
zls.tax_regime_code||' - '||zls.tax, -- description
zls.cancel_flag, -- cancelled_flag
l_user_id, -- last_updated_by
l_login_id, -- last_update_login
l_sysdate, -- last_update_date
zls.attribute_category,
zls.attribute1,
zls.attribute2,
zls.attribute3,
zls.attribute4,
zls.attribute5,
zls.attribute6,
zls.attribute7,
zls.attribute8,
zls.attribute9,
zls.attribute10,
zls.attribute11,
zls.attribute12,
zls.attribute13,
zls.attribute14,
zls.attribute15,
zls.global_attribute_category,
zls.global_attribute1,
zls.global_attribute2,
zls.global_attribute3,
zls.global_attribute4,
zls.global_attribute5,
zls.global_attribute6,
zls.global_attribute7,
zls.global_attribute8,
zls.global_attribute9,
zls.global_attribute10,
zls.global_attribute11,
zls.global_attribute12,
zls.global_attribute13,
zls.global_attribute14,
zls.global_attribute15,
zls.global_attribute16,
zls.global_attribute17,
zls.global_attribute18,
zls.global_attribute19,
zls.global_attribute20
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_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');
DELETE FROM ZX_TRANSACTION_LINES_GT;
AP_ETAX_SERVICES_PKG.G_SITE_ATTRIBUTES.DELETE;
AP_ETAX_SERVICES_PKG.G_ORG_ATTRIBUTES.DELETE;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
l_debug_info := 'Step 12: Update Total Tax Amount';
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', NVL(zls.tax_amt, 0),
0)),
SUM(DECODE(NVL(zls.self_assessed_flag, 'N'),
'Y', NVL(zls.tax_amt, 0),
0))
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 = ai.invoice_id
AND NVL(zls.reporting_only_flag, 'N') = 'N')
WHERE ai.invoice_id = l_inv_header_rec.invoice_id;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
IF NOT(AP_ETAX_SERVICES_PKG.Update_AP(
P_Invoice_header_rec => l_inv_header_rec,
P_Calling_Mode => P_Calling_Mode,
P_All_Error_Messages => P_All_Error_Messages,
P_Error_Code => P_error_code,
P_Calling_Sequence => l_curr_calling_sequence)) THEN
l_return_status := FALSE;
SELECT encumbered_flag,
reversal_flag,
prepay_distribution_id,
accrual_posted_flag,
cash_posted_flag,
posted_flag,
org_id,
pa_addition_flag,
match_status_flag,
corrected_invoice_dist_id,
invoice_distribution_id,
po_distribution_id,
rcv_transaction_id,
accounting_event_id,
dist_match_type,
amount,
prepay_amount_remaining
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND line_type_lookup_code NOT IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV');
(SELECT encumbered_flag,
reversal_flag,
charge_applicable_to_dist_id,
prepay_distribution_id,
accrual_posted_flag,
cash_posted_flag,
posted_flag,
org_id,
pa_addition_flag,
match_status_flag,
corrected_invoice_dist_id,
invoice_distribution_id,
detail_tax_dist_id,
accounting_event_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (related_id IS NULL
OR (related_id = invoice_distribution_id
OR (related_id IS NOT NULL
AND line_type_lookup_code IN ('TRV', 'TERV', 'TIPV'))))
UNION ALL
SELECT encumbered_flag,
reversal_flag,
charge_applicable_to_dist_id,
prepay_distribution_id,
accrual_posted_flag,
cash_posted_flag,
posted_flag,
org_id,
pa_addition_flag,
match_status_flag,
corrected_invoice_dist_id,
invoice_distribution_id,
detail_tax_dist_id,
accounting_event_id
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TRV', 'TERV', 'TIPV')
AND (related_id IS NULL
OR (related_id = invoice_distribution_id
OR (related_id IS NOT NULL
AND line_type_lookup_code IN ('TRV', 'TERV', 'TIPV')))));
select should not fire.I found out that value of charge_applicable_to_dist_id is
-99 in this case.So aaded the AND condition to avoid the select. */
--this validation because it can be null for tax-only lines
SELECT po_distribution_id, rcv_transaction_id
INTO l_po_distribution_id, l_rcv_transaction_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id =
l_tax_dist_list_local(i).charge_applicable_to_dist_id;
SELECT 'Y'
INTO l_freeze_dist_flag
FROM ap_invoice_distributions_all
WHERE corrected_invoice_dist_id =
l_tax_dist_list_local(i).charge_applicable_to_dist_id
AND ROWNUM = 1;
delete zx_tax_dist_id_gt;--Bug7582775
l_debug_info := 'No Of Rows Deleted From zx_tax_dist_id_gt '||sql%rowcount;
INSERT INTO zx_tax_dist_id_gt(tax_dist_id)
VALUES (freeze_dist_list(m).tax_dist_id);
| FUNCTION - Global_Document_Update()
|
| DESCRIPTION
| Public function that will call the global_document_update service to
| inform eTax of a cancellation of an invoice, the freeze after the
| invoice is validated (meaning is ready to reporting), the unfreeze
| of an invoice because it has to be modified after it was validated, and
| the release of tax holds by the user.
| This API assumes the calling code controls the commit cycle.
| This function returns TRUE if the call to the service is successful.
| Otherwise, FALSE.
|
| PARAMETERS
| P_Invoice_Id - invoice id
| P_Calling_Mode - calling mode. Identifies which service to call
| 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
| 07-OCT-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Global_Document_Update(
P_Invoice_id IN NUMBER,
P_Line_Number IN NUMBER DEFAULT NULL,
P_Calling_Mode IN VARCHAR2,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
l_api_name VARCHAR2(30) := 'global_document_update'; -- bug 6321366
l_curr_calling_sequence := 'AP_ETAX_SERVICES_PKG.Global_Document_Update<-' ||
P_calling_sequence;
UPDATE ap_invoices_all ail
SET control_amount= NULL --Bug6887264
WHERE ail.invoice_id = p_invoice_id
AND P_Calling_Mode = 'CANCEL INVOICE'
AND ail.control_amount IS NOT NULL ;
UPDATE ap_invoice_lines_all ail
SET control_amount= NULL --Bug6887264
WHERE ail.invoice_id = p_invoice_id
AND P_Calling_Mode = 'CANCEL INVOICE'
AND ail.control_amount IS NOT NULL ;
ELSE -- update the tax only line amount to 0
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Update the tax only line amount to 0');
UPDATE ap_invoice_lines_all a
SET amount = 0
WHERE a.invoice_id = p_invoice_id
AND a.line_type_lookup_code = 'TAX'
AND exists
(select 'Tax Only Line'
from zx_lines_summary zx
where zx.summary_tax_line_id = a.summary_tax_line_id
and zx.tax_only_line_flag = 'Y');
l_debug_info := 'Step 6: Call to global_document_update service';
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'global_document_update values ');
zx_api_pub.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_transaction_rec => l_transaction_rec,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END Global_Document_Update;
| Public function that will call the global_document_update service to
| inform eTax the release of tax holds by the user.
| This function returns TRUE if the call to the service is successful.
| Otherwise, FALSE.
|
| PARAMETERS
| P_Invoice_Id - invoice id
| P_Calling_Mode - calling mode. Identifies which service to call
| P_Tax_Hold_Code - List of tax hold codes released in AP
| Posible values: TAX VARIANCE and TAX AMOUNT RANGE
| 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
| 05-NOV-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Release_Tax_Holds(
P_Invoice_id IN NUMBER,
P_Calling_Mode IN VARCHAR2,
P_Tax_Hold_Code IN Rel_Hold_Codes_Type,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
l_debug_info := 'Step 6: Call to global_document_update service';
zx_api_pub.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_transaction_rec => l_transaction_rec,
p_validation_status => l_validation_status,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
| FUNCTION - Mark_Tax_Lines_Deleted()
|
| DESCRIPTION
| Public function that will call the mark_tax_lines_deleted service.
| This API assumes the calling code controls the commit cycle.
| This function returns TRUE if the call to the service is successful.
| Otherwise, FALSE.
| This service should be called per invoice line.
|
| PARAMETERS
| P_Invoice_Id - invoice id
| P_Calling_Mode - calling mode. Identifies which service to call
| P_Line_Number_To_Delete - line number deleted in AP
| 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
| 07-OCT-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Mark_Tax_Lines_Deleted(
P_Invoice_id IN NUMBER,
P_Calling_Mode IN VARCHAR2,
P_Line_Number_To_Delete IN NUMBER,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
l_curr_calling_sequence := 'AP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted<-' ||
P_calling_sequence;
l_transaction_line_rec.trx_line_id := P_Line_Number_To_Delete;
l_debug_info := 'Step 6: Call to mark_tax_lines_deleted';
zx_api_pub.mark_tax_lines_deleted(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_transaction_line_rec => l_transaction_line_rec,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
' P_Line_Number_To_Delete = '||P_Line_Number_To_Delete||
' P_Calling_Mode ='||P_Calling_Mode||
' P_Error_Code = '||P_Error_Code||
' P_Calling_Sequence = '||P_Calling_Sequence);
END Mark_Tax_Lines_Deleted;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT hold_lookup_code
BULK COLLECT INTO l_hold_lookup_code
FROM ap_holds_all
WHERE invoice_id = l_inv_header_rec.invoice_id
AND org_id = l_inv_header_rec.org_id
AND hold_lookup_code IN ('TAX VARIANCE', 'TAX AMOUNT RANGE')
AND release_lookup_code is NULL;
ap_holds_pkg.insert_single_hold(
X_invoice_id => l_inv_header_rec.invoice_id,
X_hold_lookup_code => l_hold_codes_tab(i),
X_hold_type => NULL,
X_hold_reason => NULL,
X_held_by => l_user_id,
X_calling_sequence => l_curr_calling_sequence);
| This function returns TRUE if the insert to the temp table goes
| through successfully. Otherwise, FALSE.
|
| PARAMETERS
| P_Invoice_Header_Rec - record with invoice header information
| P_Calling_Mode - calling mode. it is used to
| P_eTax_Already_called_flag - Flag to know if this is the first time tax
| has been called
| P_Event_Class_Code - Event class code
| P_Event_Type_Code - Event type code
| P_error_code - Error code to be returned
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| DATE Author Action
| 07-OCT-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Populate_Headers_GT(
P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
P_Calling_Mode IN VARCHAR2,
P_eTax_Already_called_flag IN VARCHAR2,
P_Event_Class_Code OUT NOCOPY VARCHAR2,
P_Event_Type_Code OUT NOCOPY VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT invoice_num, invoice_type_lookup_code, invoice_date
FROM ap_invoices_all
WHERE invoice_id = c_tax_related_invoice_id;
SELECT NVL(precision, 0), NVL(minimum_accountable_unit,(1/power(10,precision)))
INTO l_precision, l_minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = P_Invoice_Header_Rec.invoice_currency_code;
SELECT name
INTO l_doc_seq_name
FROM fnd_document_sequences
WHERE doc_sequence_id = p_invoice_header_rec.doc_sequence_id;
DELETE FROM zx_trx_headers_gt
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 trx_id = p_invoice_header_rec.invoice_id;
INSERT INTO zx_trx_headers_gt(
internal_organization_id,
internal_org_location_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
hdr_trx_user_key1,
hdr_trx_user_key2,
hdr_trx_user_key3,
hdr_trx_user_key4,
hdr_trx_user_key5,
hdr_trx_user_key6,
trx_date,
trx_doc_revision,
ledger_id,
trx_currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
minimum_accountable_unit,
precision,
legal_entity_id,
rounding_ship_to_party_id,
rounding_ship_from_party_id,
rounding_bill_to_party_id,
rounding_bill_from_party_id,
rndg_ship_to_party_site_id,
rndg_ship_from_party_site_id,
rndg_bill_to_party_site_id,
rndg_bill_from_party_site_id,
establishment_id,
receivables_trx_type_id,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
rel_doc_hdr_trx_user_key1,
rel_doc_hdr_trx_user_key2,
rel_doc_hdr_trx_user_key3,
rel_doc_hdr_trx_user_key4,
rel_doc_hdr_trx_user_key5,
rel_doc_hdr_trx_user_key6,
related_doc_number,
related_doc_date,
default_taxation_country,
quote_flag,
ctrl_total_hdr_tx_amt,
trx_number,
trx_description,
trx_communicated_date,
batch_source_id,
batch_source_name,
doc_seq_id,
doc_seq_name,
doc_seq_value,
trx_due_date,
trx_type_description,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_exchange_rate,
tax_invoice_date,
tax_invoice_number,
tax_event_class_code,
tax_event_type_code,
doc_event_status,
rdng_ship_to_pty_tx_prof_id,
rdng_ship_from_pty_tx_prof_id,
rdng_bill_to_pty_tx_prof_id,
rdng_bill_from_pty_tx_prof_id,
rdng_ship_to_pty_tx_p_st_id,
rdng_ship_from_pty_tx_p_st_id,
rdng_bill_to_pty_tx_p_st_id,
rdng_bill_from_pty_tx_p_st_id,
bill_third_pty_acct_id,
bill_third_pty_acct_site_id,
ship_third_pty_acct_id,
ship_third_pty_acct_site_id
) VALUES (
p_invoice_header_rec.org_id, --internal_organization_id
NULL, --internal_org_location_id
l_application_id, --application_id
l_entity_code, --entity_code
P_event_class_code, --event_class_code
P_event_type_code, --event_type_code
p_invoice_header_rec.invoice_id, --trx_id
NULL, --hdr_trx_user_key1
NULL, --hdr_trx_user_key2
NULL, --hdr_trx_user_key3
NULL, --hdr_trx_user_key4
NULL, --hdr_trx_user_key5
NULL, --hdr_trx_user_key6
p_invoice_header_rec.invoice_date, --trx_date
NULL, --trx_doc_revision
p_invoice_header_rec.set_of_books_id, --ledger_id
p_invoice_header_rec.invoice_currency_code, --trx_currency_code
p_invoice_header_rec.exchange_date, --currency_conversion_date
p_invoice_header_rec.exchange_rate, --currency_conversion_rate
p_invoice_header_rec.exchange_rate_type, --currency_conversion_type
l_minimum_accountable_unit, --minimum_accountable_unit
l_precision, --precision
p_invoice_header_rec.legal_entity_id, --legal_entity_id
NULL, --rounding_ship_to_party_id
p_invoice_header_rec.party_id, --rounding_ship_from_party_id
NULL, --rounding_bill_to_party_id
p_invoice_header_rec.party_id, --rounding_bill_from_party_id
NULL, --rndg_ship_to_party_site_id
p_invoice_header_rec.party_site_id, --rndg_ship_from_party_site_id
NULL, --rndg_bill_to_party_site_id
p_invoice_header_rec.party_site_id, --rndg_bill_from_party_site_id
NULL, --establishment_id
NULL, --receivables_trx_type_id
l_related_inv_application_id, --related_doc_application_id
l_related_inv_entity_code, --related_doc_entity_code
l_related_event_class_code, --related_doc_event_class_code
p_invoice_header_rec.tax_related_invoice_id, --related_doc_trx_id
NULL, --rel_doc_hdr_trx_user_key1
NULL, --rel_doc_hdr_trx_user_key2
NULL, --rel_doc_hdr_trx_user_key3
NULL, --rel_doc_hdr_trx_user_key4
NULL, --rel_doc_hdr_trx_user_key5
NULL, --rel_doc_hdr_trx_user_key6
l_related_inv_number, --related_doc_number
l_related_inv_date, --related_doc_date
p_invoice_header_rec.taxation_country, --default_taxation_country
l_quote_flag, --quote_flag
p_invoice_header_rec.control_amount, --ctrl_total_hdr_tx_amt
p_invoice_header_rec.invoice_num, --trx_number
p_invoice_header_rec.description, --trx_description
NULL, --trx_communicated_date
NULL, --batch_source_id
NULL, --batch_source_name
p_invoice_header_rec.doc_sequence_id, --doc_seq_id
l_doc_seq_name, --doc_seq_name
nvl(to_char(p_invoice_header_rec.doc_sequence_value),--bug6656894
p_invoice_header_rec.voucher_num), --doc_seq_value
NULL, --trx_due_date
NULL, --trx_type_description
p_invoice_header_rec.document_sub_type, --document_sub_type
p_invoice_header_rec.supplier_tax_invoice_number, --supplier_tax_invoice_number
p_invoice_header_rec.supplier_tax_invoice_date, --supplier_tax_invoice_date
p_invoice_header_rec.supplier_tax_exchange_rate, --supplier_exchange_rate
p_invoice_header_rec.tax_invoice_recording_date, --tax_invoice_date
p_invoice_header_rec.tax_invoice_internal_seq, --tax_invoice_number
NULL, --tax_event_class_code
NULL, --tax_event_type_code
NULL, --doc_event_status
NULL, --rdng_ship_to_pty_tx_prof_id
NULL, --rdng_ship_from_pty_tx_prof_id
NULL, --rdng_bill_to_pty_tx_prof_id
NULL, --rdng_bill_from_pty_tx_prof_id
NULL, --rdng_ship_to_pty_tx_p_st_id
NULL, --rdng_ship_from_pty_tx_p_st_id
NULL, --rdng_bill_to_pty_tx_p_st_id
NULL, --rdng_bill_from_pty_tx_p_st_id
p_invoice_header_rec.vendor_id, --bill_third_pty_acct_id
p_invoice_header_rec.vendor_site_id, --bill_third_pty_acct_site_id
p_invoice_header_rec.vendor_id, --ship_third_pty_acct_id
p_invoice_header_rec.vendor_site_id --ship_third_pty_acct_site_id
);
| This function returns TRUE if the insert to the temp table goes
| through successfully. Otherwise, FALSE.
|
| PARAMETERS
| P_Invoice_Header_Rec - record with invoice header information
| P_Calling_Mode - calling mode. it is used to
| P_Event_Class_Code - Event class code
| P_Event_Type_Code - Event type code
| P_error_code - Error code to be returned
| P_calling_sequence - Calling sequence
|
| MODIFICATION HISTORY
| DATE Author Action
| 20-JAN-2004 SYIDNER Created
|
*============================================================================*/
FUNCTION Populate_Header_Import_GT(
P_Invoice_Header_Rec IN AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
P_Calling_Mode IN VARCHAR2,
P_Event_Class_Code OUT NOCOPY VARCHAR2,
P_Event_Type_Code OUT NOCOPY VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT invoice_num, invoice_type_lookup_code, invoice_date
FROM ap_invoices_all
WHERE invoice_id = c_tax_related_invoice_id;
SELECT NVL(precision, 0), NVL(minimum_accountable_unit,(1/power(10,precision)))
INTO l_precision, l_minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = P_Invoice_Header_Rec.invoice_currency_code;
DELETE FROM zx_trx_headers_gt
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 trx_id = p_invoice_header_rec.invoice_id;
INSERT INTO zx_trx_headers_gt(
internal_organization_id,
internal_org_location_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
hdr_trx_user_key1,
hdr_trx_user_key2,
hdr_trx_user_key3,
hdr_trx_user_key4,
hdr_trx_user_key5,
hdr_trx_user_key6,
trx_date,
trx_doc_revision,
ledger_id,
trx_currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
minimum_accountable_unit,
precision,
legal_entity_id,
rounding_ship_to_party_id,
rounding_ship_from_party_id,
rounding_bill_to_party_id,
rounding_bill_from_party_id,
rndg_ship_to_party_site_id,
rndg_ship_from_party_site_id,
rndg_bill_to_party_site_id,
rndg_bill_from_party_site_id,
establishment_id,
receivables_trx_type_id,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
rel_doc_hdr_trx_user_key1,
rel_doc_hdr_trx_user_key2,
rel_doc_hdr_trx_user_key3,
rel_doc_hdr_trx_user_key4,
rel_doc_hdr_trx_user_key5,
rel_doc_hdr_trx_user_key6,
related_doc_number,
related_doc_date,
default_taxation_country,
quote_flag,
ctrl_total_hdr_tx_amt,
trx_number,
trx_description,
trx_communicated_date,
batch_source_id,
batch_source_name,
doc_seq_id,
doc_seq_name,
doc_seq_value,
trx_due_date,
trx_type_description,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_exchange_rate,
tax_invoice_date,
tax_invoice_number,
tax_event_class_code,
tax_event_type_code,
doc_event_status,
rdng_ship_to_pty_tx_prof_id,
rdng_ship_from_pty_tx_prof_id,
rdng_bill_to_pty_tx_prof_id,
rdng_bill_from_pty_tx_prof_id,
rdng_ship_to_pty_tx_p_st_id,
rdng_ship_from_pty_tx_p_st_id,
rdng_bill_to_pty_tx_p_st_id,
rdng_bill_from_pty_tx_p_st_id,
bill_third_pty_acct_id,
bill_third_pty_acct_site_id,
ship_third_pty_acct_id,
ship_third_pty_acct_site_id
) VALUES (
p_invoice_header_rec.org_id, --internal_organization_id
NULL, --internal_org_location_id
l_application_id, --application_id
l_entity_code, --entity_code
P_event_class_code, --event_class_code
P_event_type_code, --event_type_code
p_invoice_header_rec.invoice_id, --trx_id
NULL, --hdr_trx_user_key1
NULL, --hdr_trx_user_key2
NULL, --hdr_trx_user_key3
NULL, --hdr_trx_user_key4
NULL, --hdr_trx_user_key5
NULL, --hdr_trx_user_key6
p_invoice_header_rec.invoice_date, --trx_date
NULL, --trx_doc_revision
p_invoice_header_rec.set_of_books_id, --ledger_id
p_invoice_header_rec.invoice_currency_code, --trx_currency_code
p_invoice_header_rec.exchange_date, --currency_conversion_date
p_invoice_header_rec.exchange_rate, --currency_conversion_rate
p_invoice_header_rec.exchange_rate_type, --currency_conversion_type
l_minimum_accountable_unit, --minimum_accountable_unit
l_precision, --precision
p_invoice_header_rec.legal_entity_id, --legal_entity_id
NULL, --rounding_ship_to_party_id
p_invoice_header_rec.party_id, --rounding_ship_from_party_id
NULL, --rounding_bill_to_party_id
p_invoice_header_rec.party_id, --rounding_bill_from_party_id
NULL, --rndg_ship_to_party_site_id
p_invoice_header_rec.party_site_id, --rndg_ship_from_party_site_id
NULL, --rndg_bill_to_party_site_id
p_invoice_header_rec.party_site_id, --rndg_bill_from_party_site_id
NULL, --establishment_id
NULL, --receivables_trx_type_id
l_related_inv_application_id, --related_doc_application_id
l_related_inv_entity_code, --related_doc_entity_code
l_related_event_class_code, --related_doc_event_class_code
p_invoice_header_rec.tax_related_invoice_id, --related_doc_trx_id
NULL, --rel_doc_hdr_trx_user_key1
NULL, --rel_doc_hdr_trx_user_key2
NULL, --rel_doc_hdr_trx_user_key3
NULL, --rel_doc_hdr_trx_user_key4
NULL, --rel_doc_hdr_trx_user_key5
NULL, --rel_doc_hdr_trx_user_key6
l_related_inv_number, --related_doc_number
l_related_inv_date, --related_doc_date
p_invoice_header_rec.taxation_country, --default_taxation_country
l_quote_flag, --quote_flag
p_invoice_header_rec.control_amount, --ctrl_total_hdr_tx_amt
p_invoice_header_rec.invoice_num, --trx_number
p_invoice_header_rec.description, --trx_description
NULL, --trx_communicated_date
NULL, --batch_source_id
NULL, --batch_source_name
NULL, --doc_seq_id
NULL, --doc_seq_name
NULL, --doc_seq_value
NULL, --trx_due_date
NULL, --trx_type_description
p_invoice_header_rec.document_sub_type, --document_sub_type
p_invoice_header_rec.supplier_tax_invoice_number, --supplier_tax_invoice_number
p_invoice_header_rec.supplier_tax_invoice_date, --supplier_tax_invoice_date
p_invoice_header_rec.supplier_tax_exchange_rate, --supplier_exchange_rate
p_invoice_header_rec.tax_invoice_recording_date, --tax_invoice_date
p_invoice_header_rec.tax_invoice_internal_seq, --tax_invoice_number
NULL, --tax_event_class_code
NULL, --tax_event_type_code
NULL, --doc_event_status
NULL, --rdng_ship_to_pty_tx_prof_id
NULL, --rdng_ship_from_pty_tx_prof_id
NULL, --rdng_bill_to_pty_tx_prof_id
NULL, --rdng_bill_from_pty_tx_prof_id
NULL, --rdng_ship_to_pty_tx_p_st_id
NULL, --rdng_ship_from_pty_tx_p_st_id
NULL, --rdng_bill_to_pty_tx_p_st_id
NULL, --rdng_bill_from_pty_tx_p_st_id
p_invoice_header_rec.vendor_id, --bill_third_pty_acct_id
p_invoice_header_rec.vendor_site_id, --bill_third_pty_acct_site_id
p_invoice_header_rec.vendor_id, --ship_third_pty_acct_id
p_invoice_header_rec.vendor_site_id --ship_third_pty_acct_site_id
);
-- l_line_level_action := 'UPDATE';
l_line_level_action := 'UPDATE';
SELECT 'Y'
INTO l_tax_already_calculated_line
FROM zx_lines_det_factors
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND trx_id = l_inv_line_list(i).invoice_id
AND trx_line_id = l_inv_line_list(i).line_number
AND ROWNUM = 1;
l_line_level_action := 'UPDATE';
SELECT line_amt_includes_tax_flag
INTO l_init_line_amt_incl_tax_fg
FROM zx_lines_det_factors
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND trx_id = l_inv_line_list(i).invoice_id
AND trx_line_id = l_inv_line_list(i).line_number
AND rownum = 1;
SELECT pd.code_combination_id
INTO l_default_ccid
FROM po_distributions_all pd
WHERE pd.line_location_id = l_inv_line_list(i).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 = l_inv_line_list(i).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 = l_inv_line_list(i).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 = l_inv_line_list(i).rcv_transaction_id)
AND rownum = 1;
SELECT aerd.code_combination_id
INTO l_inv_line_list(i).default_dist_ccid
FROM ap_exp_report_dists_all aerd,
ap_expense_report_lines_all aerl,
ap_invoices_all ai
WHERE aerd.report_header_id = l_inv_line_list(i).reference_key1
AND aerd.report_line_id = l_inv_line_list(i).reference_key2
AND aerd.report_line_id = aerl.report_line_id
AND aerd.report_header_id = aerl.report_header_id
AND ai.invoice_id = l_inv_line_list(i).invoice_id
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND rownum = 1;
SELECT sum(amount)
INTO trans_lines(i).line_amt
FROM ap_invoice_distributions_all aids
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code = 'PREPAY'
AND EXISTS
(select 'Prepayment Invoice'
from ap_invoice_distributions_all aidp
where aidp.invoice_distribution_id = aids.prepay_distribution_id
and aidp.invoice_id = l_inv_line_list(i).invoice_id
and aidp.invoice_line_number = l_inv_line_list(i).line_number);
l_debug_info := 'Bulk Insert into global temp table';
INSERT INTO zx_transaction_lines_gt
VALUES trans_lines(m);
SELECT location_id
INTO l_bill_to_location_id
FROM hr_all_organization_units
WHERE organization_id = P_Invoice_Header_Rec.org_id;
SELECT location_id, ship_to_location_id, fob_lookup_code
INTO l_location_id, l_ship_to_location_id, l_fob_point
FROM ap_supplier_sites_all
WHERE vendor_site_id = P_Invoice_Header_Rec.vendor_site_id;
INSERT INTO zx_trx_tax_link_gt (
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
summary_tax_line_number,
line_amt
) SELECT
200, -- application_id
'AP_INVOICES', -- entity_code
p_event_class_code, -- event_class_code
P_Invoice_Header_Rec.invoice_id, -- trx_id
'LINE', -- trx_level_type
aili.line_number, -- trx_line_id
p_inv_line_list(i).line_number, -- summary_tax_line_number
AP_UTILITIES_PKG.ap_round_currency(
p_inv_line_list(i).amount*aili.amount/l_prorating_total,
P_Invoice_Header_Rec.invoice_currency_code) -- line_amt
FROM ap_invoice_lines_interface aili
WHERE aili.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aili.line_number <> p_inv_line_list(i).line_number
AND aili.line_type_lookup_code <> 'TAX'
AND NVL(aili.taxable_flag, 'N') = 'Y';
SELECT SUM(NVL(line_amt,0))
INTO l_total_prorated
FROM zx_trx_tax_link_gt
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND summary_tax_line_number = p_inv_line_list(i).line_number;
UPDATE zx_trx_tax_link_gt
SET line_amt = line_amt + (p_inv_line_list(i).amount - l_total_prorated)
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND trx_line_id <> p_inv_line_list(i).line_number
AND trx_line_id =
(SELECT (MAX(aili.line_number))
FROM ap_invoice_lines_interface aili
WHERE aili.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aili.line_number <> p_inv_line_list(i).line_number
AND aili.amount <> 0
AND aili.line_type_lookup_code <> 'TAX'
AND NVL(aili.taxable_flag, 'N') = 'Y'
AND ABS(aili.amount) >=
( SELECT MAX(ABS(ail2.amount))
FROM ap_invoice_lines_interface ail2
WHERE ail2.invoice_id = aili.invoice_id
AND ail2.line_number <> p_inv_line_list(i).line_number
AND ail2.line_number <> aili.line_number
AND ail2.line_type_lookup_code <> 'TAX'
AND NVL(ail2.taxable_flag, 'N') = 'Y'));
SELECT SUM(NVL(amount, 0))
INTO l_prorating_total
FROM ap_invoice_lines_interface
WHERE invoice_id = P_Invoice_Header_Rec.invoice_id
AND line_number <> p_inv_line_list(i).line_number
AND line_type_lookup_code <> 'TAX' --Bug6608702**
AND line_group_number = p_inv_line_list(i).line_group_number;
l_debug_info := 'Step 18: Get Insert in global temp table';
INSERT INTO zx_trx_tax_link_gt (
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
summary_tax_line_number,
line_amt
) SELECT
200, -- application_id
'AP_INVOICES', -- entity_code
p_event_class_code, -- event_class_code
P_Invoice_Header_Rec.invoice_id, -- trx_id
'LINE', -- trx_level_type
aili.line_number, -- trx_line_id
p_inv_line_list(i).line_number, -- summary_tax_line_number
AP_UTILITIES_PKG.ap_round_currency(
p_inv_line_list(i).amount*aili.amount/l_prorating_total,
P_Invoice_Header_Rec.invoice_currency_code) -- line_amt
FROM ap_invoice_lines_interface aili
WHERE aili.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aili.line_number <> p_inv_line_list(i).line_number
AND aili.line_type_lookup_code <> 'TAX' --Bug6608702**
AND aili.line_group_number = p_inv_line_list(i).line_group_number;
SELECT SUM(NVL(line_amt,0))
INTO l_total_prorated
FROM zx_trx_tax_link_gt
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND summary_tax_line_number = p_inv_line_list(i).line_number;
UPDATE zx_trx_tax_link_gt
SET line_amt = line_amt + (p_inv_line_list(i).amount - l_total_prorated)
WHERE trx_id = P_Invoice_Header_Rec.invoice_id
AND trx_line_id <> p_inv_line_list(i).line_number
AND trx_line_id =
(SELECT (MAX(aili.line_number))
FROM ap_invoice_lines_interface aili
WHERE aili.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aili.line_number <> p_inv_line_list(i).line_number
AND aili.line_type_lookup_code <> 'TAX' --Bug6608702**
AND aili.amount <> 0
AND aili.line_group_number = p_inv_line_list(i).line_group_number
AND ABS(aili.amount) >=
( SELECT MAX(ABS(ail2.amount))
FROM ap_invoice_lines_interface ail2
WHERE ail2.invoice_id = aili.invoice_id
AND ail2.line_number <> p_inv_line_list(i).line_number
AND ail2.line_number <> aili.line_number
AND ail2.line_type_lookup_code <> 'TAX' --Bug6608702**
AND ail2.line_group_number =
p_inv_line_list(i).line_group_number));
l_debug_info := 'Step 12: Bulk Insert into global temp tables';
DELETE FROM zx_transaction_lines_gt
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 trx_id = p_invoice_header_rec.invoice_id;
INSERT INTO zx_transaction_lines_gt
VALUES trans_lines(m);
INSERT INTO zx_import_tax_lines_gt
VALUES tax_lines(m);
INSERT INTO zx_trx_tax_link_gt
VALUES link_lines(m);
SELECT SUM(NVL(amount, 0))
INTO l_prorating_total
FROM ap_invoice_lines
WHERE invoice_id = l_inv_tax_list(i).invoice_id
AND line_number <> l_inv_tax_list(i).line_number
AND line_type_lookup_code <> 'TAX' --Bug6608702
AND line_group_number = l_inv_tax_list(i).line_group_number;
l_debug_info := 'Step 18: Get Insert in global temp table';
INSERT INTO zx_trx_tax_link_gt (
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
summary_tax_line_number,
line_amt
) SELECT
200, -- application_id
'AP_INVOICES', -- entity_code
p_event_class_code, -- event_class_code
l_inv_tax_list(i).invoice_id, -- trx_id
'LINE', -- trx_level_type
ail.line_number, -- trx_line_id
l_inv_tax_list(i).line_number, -- summary_tax_line_number
AP_UTILITIES_PKG.ap_round_currency(
l_inv_tax_list(i).amount*ail.amount/l_prorating_total,
l_inv_header_rec2.invoice_currency_code) -- line_amt
FROM ap_invoice_lines ail
WHERE ail.invoice_id = l_inv_tax_list(i).invoice_id
AND ail.line_number <> l_inv_tax_list(i).line_number
AND ail.line_type_lookup_code <> 'TAX' --Bug6608702
AND ail.line_group_number = l_inv_tax_list(i).line_group_number;
SELECT SUM(NVL(line_amt,0))
INTO l_total_prorated
FROM zx_trx_tax_link_gt
WHERE trx_id = l_inv_tax_list(i).invoice_id
AND summary_tax_line_number = l_inv_tax_list(i).line_number;
UPDATE zx_trx_tax_link_gt
SET line_amt = line_amt + (l_inv_tax_list(i).amount - l_total_prorated)
WHERE trx_id = l_inv_tax_list(i).invoice_id
AND trx_line_id <> l_inv_tax_list(i).line_number
AND trx_line_id =
(SELECT (MAX(ail.line_number))
FROM ap_invoice_lines ail
WHERE ail.invoice_id = l_inv_tax_list(i).invoice_id
AND ail.line_number <> l_inv_tax_list(i).line_number
AND ail.line_type_lookup_code <> 'TAX' --Bug6608702
AND ail.amount <> 0
AND ail.line_group_number = l_inv_tax_list(i).line_group_number
AND ABS(ail.amount) >=
( SELECT MAX(ABS(ail2.amount))
FROM ap_invoice_lines ail2
WHERE ail2.invoice_id = ail.invoice_id
AND ail2.line_number <> l_inv_tax_list(i).line_number
AND ail2.line_number <> ail.line_number
AND ail2.line_type_lookup_code <> 'TAX' --Bug6608702
AND ail2.line_group_number =
l_inv_tax_list(i).line_group_number));
l_debug_info := 'Step 14: Bulk Insert into global temp tables';
INSERT INTO zx_transaction_lines_gt
VALUES trans_lines(m);
INSERT INTO zx_import_tax_lines_gt
VALUES tax_lines(m);
INSERT INTO zx_trx_tax_link_gt
VALUES link_lines(m);
l_dist_level_action := 'UPDATE';
l_debug_info := 'Step 3: Update the amount including IPV/ERV';
SELECT SUM(amount)
INTO l_amount
FROM ap_invoice_distributions_all
WHERE invoice_id = l_inv_dist_list(i).invoice_id
AND (related_id = l_inv_dist_list(i).related_id
or related_retainage_dist_id = l_inv_dist_list(i).related_retainage_dist_id);
SELECT quantity_invoiced, po_release_id
INTO l_line_quantity_invoiced, l_po_release_id
FROM ap_invoice_lines_all
WHERE invoice_id = l_inv_dist_list(i).invoice_id
AND line_number = l_inv_dist_list(i).invoice_line_number;
SELECT currency_conversion_rate
INTO l_receipt_curr_conv_rate
FROM rcv_transactions
WHERE transaction_id = l_inv_dist_list(i).rcv_transaction_id;
Select invoice_id, invoice_line_number
Into l_adj_doc_trx_id, l_adj_doc_line_id
From ap_invoice_distributions_all
Where invoice_distribution_id = l_inv_dist_list(i).corrected_invoice_dist_id;
SELECT invoice_id, invoice_line_number
INTO l_applied_from_trx_id, l_applied_from_line_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = l_inv_dist_list(i).prepay_distribution_id;
SELECT invoice_id, invoice_line_number
INTO l_prepay_inv_id, l_prepay_line_num
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = l_inv_dist_list(i).prepay_distribution_id;
l_debug_info := 'Step 7: Bulk Insert into global temp table';
INSERT INTO zx_itm_distributions_gt
VALUES trans_dists(m);
| FUNCTION - Update_AP()
|
| DESCRIPTION
| This function will handle the return of values from the eTax repository
| This will be called from all the functions that call the etax services
| in the case the call is successfull.
|
| PARAMETERS
| P_Invoice_header_rec - Invoice header info
| P_Calling_Mode - calling mode.
| 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
| 20-OCT-2003 SYIDNER Created
|
*============================================================================*/
FUNCTION Update_AP(
P_Invoice_header_rec IN ap_invoices_all%ROWTYPE,
P_Calling_Mode IN VARCHAR2,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
'AP_ETAX_SERVICES_PKG.Update_AP<-'||P_calling_sequence;
END Update_AP;
| based on the parameter p_invoice_header_rec. A psuedo-line is inserted
| into the GTT and removed after the tax amount is calculated.
|
| PARAMETERS
| P_Invoice_Header_Rec - Invoice header info
| P_Invoice_Lines_Rec - Invoice lines info
| P_Calling_Mode - Calling mode. (CALCULATE_QUOTE)
| 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
| 13-AUG-2004 Sanjay Created
*============================================================================*/
FUNCTION CALCULATE_QUOTE(
P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
P_Invoice_Lines_Rec IN ap_invoice_lines_all%ROWTYPE,
P_Calling_Mode IN VARCHAR2,
P_Tax_Amount OUT NOCOPY NUMBER,
P_Tax_Amt_Included OUT NOCOPY VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_debug_info VARCHAR2(240);
SELECT SUM(NVL(zdl.tax_amt,0))
FROM zx_detail_tax_lines_gt zdl
WHERE zdl.application_id = 200
AND zdl.entity_code = 'AP_INVOICES'
AND zdl.trx_id = P_Invoice_Lines_Rec.invoice_id
AND NVL(zdl.self_assessed_flag, 'N') = 'N'
AND NVL(zdl.reporting_only_flag, 'N') = 'N';
SELECT location_id, fob_lookup_code
INTO l_location_id, l_fob_point
FROM ap_supplier_sites_all
WHERE vendor_site_id = P_Invoice_Header_Rec.vendor_site_id;
SELECT location_id
INTO l_bill_to_location_id
FROM hr_all_organization_units
WHERE organization_id = P_Invoice_Header_Rec.org_id;
l_debug_info := 'Step 9: Insert into zx_transaction_lines_gt';
INSERT INTO zx_transaction_lines_gt
(
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
trx_line_type,
trx_line_date,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
line_amt,
trx_line_quantity,
unit_price,
product_id,
product_fisc_classification,
product_org_id,
uom_code,
product_type,
product_category,
fob_point,
ship_to_party_id,
ship_from_party_id,
bill_to_party_id,
bill_from_party_id,
ship_from_party_site_id,
bill_from_party_site_id,
ship_to_location_id,
ship_from_location_id,
bill_to_location_id,
bill_from_location_id,
account_ccid,
merchant_party_country,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
ref_doc_trx_level_type,
--applied_to_trx_line_id,
trx_line_number,
trx_line_description,
product_description,
trx_line_gl_date,
merchant_party_name,
merchant_party_document_number,
merchant_party_reference,
merchant_party_taxpayer_id,
merchant_party_tax_reg_number,
assessable_value,
line_amt_includes_tax_flag,
historical_flag,
ctrl_hdr_tx_appl_flag,
ctrl_total_line_tx_amt,
input_tax_classification_code
)
VALUES
(
200,
'AP_INVOICES',
l_event_class_code,
P_Invoice_Lines_Rec.invoice_id,
'LINE',
P_Invoice_Lines_Rec.line_number,
l_line_level_action,
P_Invoice_Lines_Rec.line_type_lookup_code,
P_Invoice_Header_Rec.invoice_date,
P_Invoice_Lines_Rec.trx_business_category,
P_Invoice_Lines_Rec.primary_intended_use,
P_Invoice_Lines_Rec.user_defined_fisc_class,
P_Invoice_Lines_Rec.amount,
P_Invoice_Lines_Rec.quantity_invoiced,
P_Invoice_Lines_Rec.unit_price,
P_Invoice_Lines_Rec.inventory_item_id,
P_Invoice_Lines_Rec.product_fisc_classification,
l_product_org_id,
P_Invoice_Lines_Rec.unit_meas_lookup_code,
P_Invoice_Lines_Rec.product_type,
P_Invoice_Lines_Rec.product_category,
l_fob_point,
P_Invoice_Lines_Rec.org_id,
P_Invoice_Header_Rec.party_id,
P_Invoice_Lines_Rec.org_id,
P_Invoice_Header_Rec.party_id,
P_Invoice_Header_Rec.party_site_id,
P_Invoice_Header_Rec.party_site_id,
P_Invoice_Lines_Rec.ship_to_location_id,
l_location_id,
l_bill_to_location_id,
l_location_id,
P_Invoice_Lines_Rec.default_dist_ccid,
P_Invoice_Lines_Rec.country_of_supply,
l_ref_doc_application_id,
l_ref_doc_entity_code,
l_ref_doc_event_class_code,
l_ref_doc_trx_id,
--Bug5680407 corrected the wrong ordering of below
--3 coulmns
P_Invoice_Lines_Rec.po_line_location_id,
l_ref_doc_line_quantity,
l_ref_doc_trx_level_type,
P_Invoice_Lines_Rec.line_number,
P_Invoice_Lines_Rec.description,
P_Invoice_Lines_Rec.item_description,
P_Invoice_Lines_Rec.accounting_date,
P_Invoice_Lines_Rec.merchant_name,
P_Invoice_Lines_Rec.merchant_document_number,
P_Invoice_Lines_Rec.merchant_reference,
P_Invoice_Lines_Rec.merchant_taxpayer_id,
P_Invoice_Lines_Rec.merchant_tax_reg_number,
P_Invoice_Lines_Rec.assessable_value,
l_line_amt_includes_tax_flag,
NVL(P_Invoice_Header_Rec.historical_flag, 'N'),
l_ctrl_hdr_tx_appl_flag,
P_Invoice_Lines_Rec.control_amount,
P_Invoice_Lines_Rec.tax_classification_code
);
SELECT distinct zdl.tax_amt_included_flag
INTO P_Tax_Amt_Included
FROM zx_detail_tax_lines_gt zdl
WHERE zdl.application_id = 200
AND zdl.entity_code = 'AP_INVOICES'
AND zdl.trx_id = P_Invoice_Lines_Rec.invoice_id
AND NVL(zdl.self_assessed_flag, 'N') = 'N'
AND NVL(zdl.reporting_only_flag, 'N') = 'N';
l_debug_info := 'Step 13: Delete eTax Global Temporary Tables';
BEGIN DELETE zx_trx_headers_gt;
BEGIN DELETE zx_transaction_lines_gt;
SELECT line_intended_use,
product_type,
product_category,
product_fisc_classification,
user_defined_fisc_class,
assessable_value,
input_tax_classification_code
FROM zx_lines_det_factors
WHERE application_id = p_application_id
AND internal_organization_id = p_org_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_level_type = p_trx_level_type
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id;
select
zx_dist.gl_date accounting_date,
'N' accrual_posted_flag,
'U' assets_addition_flag,
'N' assets_tracking_flag,
'N' cash_posted_flag,
AP_INVOICE_LINES_PKG.get_max_dist_line_num(
p_invoice_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(
zx_dist.gl_date,
tax_dist.org_id) period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
--P_Invoice_Header_Rec.batch_id batch_id,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
'N' encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,
'CANCEL', 'Y', Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
-- bug 5572121
-- NULL related_id,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
tax_dist.prepay_tax_diff_amount -- BUG 7338249
from ap_invoice_distributions_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TERV', 'TRV')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and item_dist.invoice_distribution_id(+) = zx_dist.trx_line_dist_id --bug7394712
and zx_dist.reverse_flag = 'Y'
--and (p_line_number IS NULL -- bug 6056777
-- or zx_dist.trx_line_id = p_line_number) --bug605677
--bugfix:5582836
and not exists(select detail_tax_dist_id
from ap_invoice_distributions aid
where aid.invoice_id = p_invoice_id
and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id);
select
zx_dist.gl_date accounting_date,
'N' accrual_posted_flag,
'U' assets_addition_flag,
'N' assets_tracking_flag,
'N' cash_posted_flag,
AP_INVOICE_LINES_PKG.get_max_dist_line_num(
p_invoice_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
ap_utilities_pkg.get_gl_period_name(
zx_dist.gl_date,
tax_dist.org_id) period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
--P_Invoice_Header_Rec.batch_id batch_id,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
'N' encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,
'CANCEL', 'Y', Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
-- bug 5572121
-- NULL related_id,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
tax_dist.prepay_tax_diff_amount -- BUG 7338249
from ap_invoice_distributions_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TERV', 'TRV')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and item_dist.invoice_distribution_id(+) = zx_dist.trx_line_dist_id --bug7394712
and zx_dist.reverse_flag = 'Y'
and zx_dist.trx_line_id = p_line_number --bug6056777
--bugfix:5582836
and not exists(select detail_tax_dist_id
from ap_invoice_distributions aid
where aid.invoice_id = p_invoice_id
and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id);
select
zx_dist.gl_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_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
tax_dist.period_name period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
--P_Invoice_Header_Rec.batch_id batch_id,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
'N' encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,
'CANCEL', 'Y', Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
-- bug 5572121
-- NULL related_id,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
zx_dist.self_assessed_flag self_assessed_flag,
-- bug 6805655
tax_dist.self_assessed_tax_liab_ccid self_assessed_tax_liab_ccid,
tax_dist.prepay_tax_diff_amount -- BUG 7338249
from ap_self_assessed_tax_dist_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and item_dist.invoice_distribution_id(+) = zx_dist.trx_line_dist_id --bug7394712
and zx_dist.reverse_flag = 'Y'
--and (p_line_number IS NULL -- bug 6056777
-- or zx_dist.trx_line_id = p_line_number) --bug605677
--bugfix:5582836
/* -- bug 6896627
and not exists(select detail_tax_dist_id
from ap_invoice_distributions aid
where aid.invoice_id = p_invoice_id
and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id)
*/
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
-- bug 6896627
;
select
zx_dist.gl_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_id,
tax_dist.invoice_line_number)+1
distribution_line_number,
tax_dist.dist_code_combination_id dist_code_combination_id,
tax_dist.invoice_id invoice_id,
l_user_id last_updated_by,
l_sysdate last_update_date,
tax_dist.line_type_lookup_code line_type_lookup_code,
tax_dist.period_name period_name,
tax_dist.set_of_books_id set_of_books_id,
(-tax_dist.amount) amount,
(-tax_dist.base_amount) base_amount,
--P_Invoice_Header_Rec.batch_id batch_id,
l_user_id created_by,
l_sysdate creation_date,
tax_dist.description description,
NULL final_match_flag,
tax_dist.income_tax_region income_tax_region,
l_user_id last_update_login,
NULL match_status_flag,
'N' posted_flag,
tax_dist.po_distribution_id po_distribution_id,
NULL program_application_id,
NULL program_id,
NULL program_update_date,
NULL quantity_invoiced,
NULL request_id,
'Y' reversal_flag,
tax_dist.type_1099 type_1099,
tax_dist.unit_price unit_price,
'N' encumbered_flag,
NULL stat_amount,
tax_dist.attribute1 attribute1,
tax_dist.attribute10 attribute10,
tax_dist.attribute11 attribute11,
tax_dist.attribute12 attribute12,
tax_dist.attribute13 attribute13,
tax_dist.attribute14 attribute14,
tax_dist.attribute15 attribute15,
tax_dist.attribute2 attribute2,
tax_dist.attribute3 attribute3,
tax_dist.attribute4 attribute4,
tax_dist.attribute5 attribute5,
tax_dist.attribute6 attribute6,
tax_dist.attribute7 attribute7,
tax_dist.attribute8 attribute8,
tax_dist.attribute9 attribute9,
tax_dist.attribute_category attribute_category,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_item_date) expenditure_item_date,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_organization_id) expenditure_organization_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.expenditure_type) expenditure_type,
tax_dist.parent_invoice_id parent_invoice_id,
decode(zx_dist.recoverable_flag,
'Y', 'E',
item_dist.pa_addition_flag) pa_addition_flag,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.pa_quantity) pa_quantity,
NULL prepay_amount_remaining,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_accounting_context) project_accounting_context,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.project_id) project_id,
decode(zx_dist.recoverable_flag,
'Y', NULL,
item_dist.task_id) task_id,
NULL packet_id,
'N' awt_flag,
tax_dist.awt_group_id awt_group_id,
NULL awt_tax_rate_id,
NULL awt_gross_amount,
NULL awt_invoice_id,
NULL awt_origin_group_id,
NULL reference_1,
NULL reference_2,
tax_dist.org_id org_id,
NULL awt_invoice_payment_id,
tax_dist.global_attribute_category global_attribute_category,
tax_dist.global_attribute1 global_attribute1,
tax_dist.global_attribute2 global_attribute2,
tax_dist.global_attribute3 global_attribute3,
tax_dist.global_attribute4 global_attribute4,
tax_dist.global_attribute5 global_attribute5,
tax_dist.global_attribute6 global_attribute6,
tax_dist.global_attribute7 global_attribute7,
tax_dist.global_attribute8 global_attribute8,
tax_dist.global_attribute9 global_attribute9,
tax_dist.global_attribute10 global_attribute10,
tax_dist.global_attribute11 global_attribute11,
tax_dist.global_attribute12 global_attribute12,
tax_dist.global_attribute13 global_attribute13,
tax_dist.global_attribute14 global_attribute14,
tax_dist.global_attribute15 global_attribute15,
tax_dist.global_attribute16 global_attribute16,
tax_dist.global_attribute17 global_attribute17,
tax_dist.global_attribute18 global_attribute18,
tax_dist.global_attribute19 global_attribute19,
tax_dist.global_attribute20 global_attribute20,
NULL receipt_verified_flag,
NULL receipt_required_flag,
NULL receipt_missing_flag,
NULL justification,
NULL expense_group,
NULL start_expense_date,
NULL end_expense_date,
NULL receipt_currency_code,
NULL receipt_conversion_rate,
NULL receipt_currency_amount,
NULL daily_amount,
NULL web_parameter_id,
NULL adjustment_reason,
decode(zx_dist.recoverable_flag,
'Y', NULL,
tax_dist.award_id) award_id,
NULL credit_card_trx_id,
tax_dist.dist_match_type dist_match_type,
tax_dist.rcv_transaction_id rcv_transaction_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
tax_dist.invoice_distribution_id parent_reversal_id,
tax_dist.tax_recoverable_flag tax_recoverable_flag,
NULL merchant_document_number,
NULL merchant_name,
NULL merchant_reference,
NULL merchant_tax_reg_number,
NULL merchant_taxpayer_id,
NULL country_of_supply,
NULL matched_uom_lookup_code,
NULL gms_burdenable_raw_cost,
NULL accounting_event_id,
tax_dist.prepay_distribution_id prepay_distribution_id,
NULL upgrade_posted_amt,
NULL upgrade_base_posted_amt,
'N' inventory_transfer_status,
NULL company_prepaid_invoice_id,
NULL cc_reversal_flag,
NULL awt_withheld_amt,
NULL pa_cmt_xface_flag,
decode(p_calling_mode,
'CANCEL', 'Y', Null) cancellation_flag,
tax_dist.invoice_line_number invoice_line_number,
tax_dist.corrected_invoice_dist_id corrected_invoice_dist_id,
tax_dist.rounding_amt rounding_amt,
zx_dist.trx_line_dist_id charge_applicable_to_dist_id,
NULL corrected_quantity,
-- bug 5572121
-- NULL related_id,
DECODE( tax_dist.related_id, NULL, NULL,
tax_dist.invoice_distribution_id,
ap_invoice_distributions_s.CURRVAL, NULL) related_id,
NULL asset_book_type_code,
NULL asset_category_id,
tax_dist.distribution_class distribution_class,
tax_dist.tax_code_id tax_code_id,
tax_dist.intended_use intended_use,
zx_dist.rec_nrec_tax_dist_id detail_tax_dist_id,
zx_dist.rec_nrec_rate rec_nrec_rate,
zx_dist.recovery_rate_id recovery_rate_id,
zx_dist.recovery_type_code recovery_type_code,
NULL withholding_tax_code_id,
NULL taxable_amount,
NULL taxable_base_amount,
tax_dist.tax_already_distributed_flag tax_already_distributed_flag,
tax_dist.summary_tax_line_id summary_tax_line_id,
'N' rcv_charge_addition_flag,
zx_dist.self_assessed_flag self_assessed_flag,
-- bug 6805655
tax_dist.self_assessed_tax_liab_ccid self_assessed_tax_liab_ccid,
tax_dist.prepay_tax_diff_amount -- BUG 7338249
from ap_self_assessed_tax_dist_all tax_dist,
ap_invoice_distributions_all item_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
and tax_dist.detail_tax_dist_id = zx_dist.reversed_tax_dist_id
and item_dist.invoice_distribution_id(+) = zx_dist.trx_line_dist_id --bug7394712
and zx_dist.reverse_flag = 'Y'
and zx_dist.trx_line_id = p_line_number --bug6056777
--bugfix:5582836
/* -- bug 6896627
and not exists(select detail_tax_dist_id
from ap_invoice_distributions aid
where aid.invoice_id = p_invoice_id
and aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id)
*/
AND NOT EXISTS
(SELECT aid.detail_tax_dist_id
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX'))
-- bug 6896627
;
l_debug_info := 'Step 1: Insert into zx_reverse_dist_gt';
INSERT INTO zx_reverse_dist_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversing_trx_line_dist_id,
reversing_tax_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id,
reversed_trx_line_dist_id,
reversed_tax_line_id
)
select distinct
item_dist.org_id internal_organization_id,
zx_dist.application_id reversing_appln_id,
zx_dist.entity_code reversing_entity_code,
zx_dist.event_class_code reversing_evnt_cls_code,
zx_dist.trx_id reversing_trx_id,
zx_dist.trx_level_type reversing_trx_level_type,
zx_dist.trx_line_id reversing_trx_line_id,
reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
zx_dist.tax_line_id reversing_tax_line_id,
zx_dist.application_id reversed_appln_id,
zx_dist.entity_code reversed_entity_code,
zx_dist.event_class_code reversed_evnt_cls_code,
zx_dist.trx_id reversed_trx_id,
zx_dist.trx_level_type reversed_trx_level_type,
zx_dist.trx_line_id reversed_trx_line_id,
zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
zx_dist.tax_line_id reversed_tax_line_id
from ap_invoice_distributions_all item_dist,
ap_invoice_distributions_all tax_dist,
ap_invoice_distributions_all reverse_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.invoice_id = item_dist.invoice_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX', 'TIPV', 'TRV', 'TERV')
and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
and nvl(zx_dist.reverse_flag, 'N') = 'N'
and (p_line_number IS NULL
or item_dist.invoice_line_number = p_line_number);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Row Count inserted into zx_reverse_dist_gt: ' || l_reverse_dist_count);
l_debug_info := 'Inserting self assessed tax entries into zx_reverse_dist_gt';
INSERT INTO zx_reverse_dist_gt(
internal_organization_id,
reversing_appln_id,
reversing_entity_code,
reversing_evnt_cls_code,
reversing_trx_id,
reversing_trx_level_type,
reversing_trx_line_id,
reversing_trx_line_dist_id,
reversing_tax_line_id,
reversed_appln_id,
reversed_entity_code,
reversed_evnt_cls_code,
reversed_trx_id,
reversed_trx_level_type,
reversed_trx_line_id,
reversed_trx_line_dist_id,
reversed_tax_line_id
)
select distinct
item_dist.org_id internal_organization_id,
zx_dist.application_id reversing_appln_id,
zx_dist.entity_code reversing_entity_code,
zx_dist.event_class_code reversing_evnt_cls_code,
zx_dist.trx_id reversing_trx_id,
zx_dist.trx_level_type reversing_trx_level_type,
zx_dist.trx_line_id reversing_trx_line_id,
reverse_dist.invoice_distribution_id reversing_trx_line_dist_id,
zx_dist.tax_line_id reversing_tax_line_id,
zx_dist.application_id reversed_appln_id,
zx_dist.entity_code reversed_entity_code,
zx_dist.event_class_code reversed_evnt_cls_code,
zx_dist.trx_id reversed_trx_id,
zx_dist.trx_level_type reversed_trx_level_type,
zx_dist.trx_line_id reversed_trx_line_id,
zx_dist.trx_line_dist_id reversed_trx_line_dist_id,
zx_dist.tax_line_id reversed_tax_line_id
from ap_invoice_distributions_all item_dist,
ap_self_assessed_tax_dist_all tax_dist,
ap_invoice_distributions_all reverse_dist,
zx_rec_nrec_dist zx_dist
where tax_dist.invoice_id = p_invoice_id
and tax_dist.invoice_id = item_dist.invoice_id
and tax_dist.charge_applicable_to_dist_id = item_dist.invoice_distribution_id
and item_dist.invoice_distribution_id = reverse_dist.parent_reversal_id
and tax_dist.line_type_lookup_code IN ('NONREC_TAX', 'REC_TAX')
and tax_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
and nvl(zx_dist.reverse_flag, 'N') = 'N'
and (p_line_number IS NULL
or item_dist.invoice_line_number = p_line_number);
l_debug_info := 'Step 3: Insert reverse tax distributions into ap_invoice_distributions';
INSERT INTO ap_invoice_distributions_all (
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
--batch_id,
created_by,
creation_date,
description,
final_match_flag,
income_tax_region,
last_update_login,
match_status_flag,
posted_flag,
po_distribution_id,
program_application_id,
program_id,
program_update_date,
quantity_invoiced,
request_id,
reversal_flag,
type_1099,
unit_price,
encumbered_flag,
stat_amount,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
parent_invoice_id,
pa_addition_flag,
pa_quantity,
prepay_amount_remaining,
project_accounting_context,
project_id,
task_id,
packet_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
reference_1,
reference_2,
org_id,
awt_invoice_payment_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
award_id,
credit_card_trx_id,
dist_match_type,
rcv_transaction_id,
invoice_distribution_id,
parent_reversal_id,
tax_recoverable_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
matched_uom_lookup_code,
gms_burdenable_raw_cost,
accounting_event_id,
prepay_distribution_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
inventory_transfer_status,
company_prepaid_invoice_id,
cc_reversal_flag,
awt_withheld_amt,
pa_cmt_xface_flag,
cancellation_flag,
invoice_line_number,
corrected_invoice_dist_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_quantity,
related_id,
asset_book_type_code,
asset_category_id,
distribution_class,
tax_code_id,
intended_use,
detail_tax_dist_id,
rec_nrec_rate,
recovery_rate_id,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
prepay_tax_diff_amount ) -- BUG 7338249
VALUES
(
l_reverse_tax_dist.accounting_date,
l_reverse_tax_dist.accrual_posted_flag,
l_reverse_tax_dist.assets_addition_flag,
l_reverse_tax_dist.assets_tracking_flag,
l_reverse_tax_dist.cash_posted_flag,
l_reverse_tax_dist.distribution_line_number,
l_reverse_tax_dist.dist_code_combination_id,
l_reverse_tax_dist.invoice_id,
l_reverse_tax_dist.last_updated_by,
l_reverse_tax_dist.last_update_date,
l_reverse_tax_dist.line_type_lookup_code,
l_reverse_tax_dist.period_name,
l_reverse_tax_dist.set_of_books_id,
l_reverse_tax_dist.amount,
l_reverse_tax_dist.base_amount,
--l_reverse_tax_dist.batch_id,
l_reverse_tax_dist.created_by,
l_reverse_tax_dist.creation_date,
l_reverse_tax_dist.description,
l_reverse_tax_dist.final_match_flag,
l_reverse_tax_dist.income_tax_region,
l_reverse_tax_dist.last_update_login,
l_reverse_tax_dist.match_status_flag,
l_reverse_tax_dist.posted_flag,
l_reverse_tax_dist.po_distribution_id,
l_reverse_tax_dist.program_application_id,
l_reverse_tax_dist.program_id,
l_reverse_tax_dist.program_update_date,
l_reverse_tax_dist.quantity_invoiced,
l_reverse_tax_dist.request_id,
l_reverse_tax_dist.reversal_flag,
l_reverse_tax_dist.type_1099,
l_reverse_tax_dist.unit_price,
l_reverse_tax_dist.encumbered_flag,
l_reverse_tax_dist.stat_amount,
l_reverse_tax_dist.attribute1,
l_reverse_tax_dist.attribute10,
l_reverse_tax_dist.attribute11,
l_reverse_tax_dist.attribute12,
l_reverse_tax_dist.attribute13,
l_reverse_tax_dist.attribute14,
l_reverse_tax_dist.attribute15,
l_reverse_tax_dist.attribute2,
l_reverse_tax_dist.attribute3,
l_reverse_tax_dist.attribute4,
l_reverse_tax_dist.attribute5,
l_reverse_tax_dist.attribute6,
l_reverse_tax_dist.attribute7,
l_reverse_tax_dist.attribute8,
l_reverse_tax_dist.attribute9,
l_reverse_tax_dist.attribute_category,
l_reverse_tax_dist.expenditure_item_date,
l_reverse_tax_dist.expenditure_organization_id,
l_reverse_tax_dist.expenditure_type,
l_reverse_tax_dist.parent_invoice_id,
l_reverse_tax_dist.pa_addition_flag,
l_reverse_tax_dist.pa_quantity,
l_reverse_tax_dist.prepay_amount_remaining,
l_reverse_tax_dist.project_accounting_context,
l_reverse_tax_dist.project_id,
l_reverse_tax_dist.task_id,
l_reverse_tax_dist.packet_id,
l_reverse_tax_dist.awt_flag,
l_reverse_tax_dist.awt_group_id,
l_reverse_tax_dist.awt_tax_rate_id,
l_reverse_tax_dist.awt_gross_amount,
l_reverse_tax_dist.awt_invoice_id,
l_reverse_tax_dist.awt_origin_group_id,
l_reverse_tax_dist.reference_1,
l_reverse_tax_dist.reference_2,
l_reverse_tax_dist.org_id,
l_reverse_tax_dist.awt_invoice_payment_id,
l_reverse_tax_dist.global_attribute_category,
l_reverse_tax_dist.global_attribute1,
l_reverse_tax_dist.global_attribute2,
l_reverse_tax_dist.global_attribute3,
l_reverse_tax_dist.global_attribute4,
l_reverse_tax_dist.global_attribute5,
l_reverse_tax_dist.global_attribute6,
l_reverse_tax_dist.global_attribute7,
l_reverse_tax_dist.global_attribute8,
l_reverse_tax_dist.global_attribute9,
l_reverse_tax_dist.global_attribute10,
l_reverse_tax_dist.global_attribute11,
l_reverse_tax_dist.global_attribute12,
l_reverse_tax_dist.global_attribute13,
l_reverse_tax_dist.global_attribute14,
l_reverse_tax_dist.global_attribute15,
l_reverse_tax_dist.global_attribute16,
l_reverse_tax_dist.global_attribute17,
l_reverse_tax_dist.global_attribute18,
l_reverse_tax_dist.global_attribute19,
l_reverse_tax_dist.global_attribute20,
l_reverse_tax_dist.receipt_verified_flag,
l_reverse_tax_dist.receipt_required_flag,
l_reverse_tax_dist.receipt_missing_flag,
l_reverse_tax_dist.justification,
l_reverse_tax_dist.expense_group,
l_reverse_tax_dist.start_expense_date,
l_reverse_tax_dist.end_expense_date,
l_reverse_tax_dist.receipt_currency_code,
l_reverse_tax_dist.receipt_conversion_rate,
l_reverse_tax_dist.receipt_currency_amount,
l_reverse_tax_dist.daily_amount,
l_reverse_tax_dist.web_parameter_id,
l_reverse_tax_dist.adjustment_reason,
l_reverse_tax_dist.award_id,
l_reverse_tax_dist.credit_card_trx_id,
l_reverse_tax_dist.dist_match_type,
l_reverse_tax_dist.rcv_transaction_id,
l_reverse_tax_dist.invoice_distribution_id,
l_reverse_tax_dist.parent_reversal_id,
l_reverse_tax_dist.tax_recoverable_flag,
l_reverse_tax_dist.merchant_document_number,
l_reverse_tax_dist.merchant_name,
l_reverse_tax_dist.merchant_reference,
l_reverse_tax_dist.merchant_tax_reg_number,
l_reverse_tax_dist.merchant_taxpayer_id,
l_reverse_tax_dist.country_of_supply,
l_reverse_tax_dist.matched_uom_lookup_code,
l_reverse_tax_dist.gms_burdenable_raw_cost,
l_reverse_tax_dist.accounting_event_id,
l_reverse_tax_dist.prepay_distribution_id,
l_reverse_tax_dist.upgrade_posted_amt,
l_reverse_tax_dist.upgrade_base_posted_amt,
l_reverse_tax_dist.inventory_transfer_status,
l_reverse_tax_dist.company_prepaid_invoice_id,
l_reverse_tax_dist.cc_reversal_flag,
l_reverse_tax_dist.awt_withheld_amt,
l_reverse_tax_dist.pa_cmt_xface_flag,
l_reverse_tax_dist.cancellation_flag,
l_reverse_tax_dist.invoice_line_number,
l_reverse_tax_dist.corrected_invoice_dist_id,
l_reverse_tax_dist.rounding_amt,
l_reverse_tax_dist.charge_applicable_to_dist_id,
l_reverse_tax_dist.corrected_quantity,
l_reverse_tax_dist.related_id,
l_reverse_tax_dist.asset_book_type_code,
l_reverse_tax_dist.asset_category_id,
l_reverse_tax_dist.distribution_class,
l_reverse_tax_dist.tax_code_id,
l_reverse_tax_dist.intended_use,
l_reverse_tax_dist.detail_tax_dist_id,
l_reverse_tax_dist.rec_nrec_rate,
l_reverse_tax_dist.recovery_rate_id,
l_reverse_tax_dist.recovery_type_code,
l_reverse_tax_dist.withholding_tax_code_id,
l_reverse_tax_dist.taxable_amount,
l_reverse_tax_dist.taxable_base_amount,
l_reverse_tax_dist.tax_already_distributed_flag,
l_reverse_tax_dist.summary_tax_line_id,
l_reverse_tax_dist.rcv_charge_addition_flag,
l_reverse_tax_dist.prepay_tax_diff_amount) ; -- BUG 7338249
l_debug_info := 'Step 3: Insert reverse self assessed tax distributions into ap_invoice_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_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
self_assessed_flag,
self_assessed_tax_liab_ccid, --bug6805655
prepay_tax_diff_amount -- BUG 7338249
)
VALUES
(
l_self_assess_rev_tax_dist.accounting_date,
l_self_assess_rev_tax_dist.accrual_posted_flag,
l_self_assess_rev_tax_dist.assets_addition_flag,
l_self_assess_rev_tax_dist.assets_tracking_flag,
l_self_assess_rev_tax_dist.cash_posted_flag,
l_self_assess_rev_tax_dist.distribution_line_number,
l_self_assess_rev_tax_dist.dist_code_combination_id,
l_self_assess_rev_tax_dist.invoice_id,
l_self_assess_rev_tax_dist.last_updated_by,
l_self_assess_rev_tax_dist.last_update_date,
l_self_assess_rev_tax_dist.line_type_lookup_code,
l_self_assess_rev_tax_dist.period_name,
l_self_assess_rev_tax_dist.set_of_books_id,
l_self_assess_rev_tax_dist.amount,
l_self_assess_rev_tax_dist.base_amount,
--l_self_assess_rev_tax_dist.batch_id,
l_self_assess_rev_tax_dist.created_by,
l_self_assess_rev_tax_dist.creation_date,
l_self_assess_rev_tax_dist.description,
l_self_assess_rev_tax_dist.final_match_flag,
l_self_assess_rev_tax_dist.income_tax_region,
l_self_assess_rev_tax_dist.last_update_login,
l_self_assess_rev_tax_dist.match_status_flag,
l_self_assess_rev_tax_dist.posted_flag,
l_self_assess_rev_tax_dist.po_distribution_id,
l_self_assess_rev_tax_dist.program_application_id,
l_self_assess_rev_tax_dist.program_id,
l_self_assess_rev_tax_dist.program_update_date,
l_self_assess_rev_tax_dist.quantity_invoiced,
l_self_assess_rev_tax_dist.request_id,
l_self_assess_rev_tax_dist.reversal_flag,
l_self_assess_rev_tax_dist.type_1099,
l_self_assess_rev_tax_dist.unit_price,
l_self_assess_rev_tax_dist.encumbered_flag,
l_self_assess_rev_tax_dist.stat_amount,
l_self_assess_rev_tax_dist.attribute1,
l_self_assess_rev_tax_dist.attribute10,
l_self_assess_rev_tax_dist.attribute11,
l_self_assess_rev_tax_dist.attribute12,
l_self_assess_rev_tax_dist.attribute13,
l_self_assess_rev_tax_dist.attribute14,
l_self_assess_rev_tax_dist.attribute15,
l_self_assess_rev_tax_dist.attribute2,
l_self_assess_rev_tax_dist.attribute3,
l_self_assess_rev_tax_dist.attribute4,
l_self_assess_rev_tax_dist.attribute5,
l_self_assess_rev_tax_dist.attribute6,
l_self_assess_rev_tax_dist.attribute7,
l_self_assess_rev_tax_dist.attribute8,
l_self_assess_rev_tax_dist.attribute9,
l_self_assess_rev_tax_dist.attribute_category,
l_self_assess_rev_tax_dist.expenditure_item_date,
l_self_assess_rev_tax_dist.expenditure_organization_id,
l_self_assess_rev_tax_dist.expenditure_type,
l_self_assess_rev_tax_dist.parent_invoice_id,
l_self_assess_rev_tax_dist.pa_addition_flag,
l_self_assess_rev_tax_dist.pa_quantity,
l_self_assess_rev_tax_dist.prepay_amount_remaining,
l_self_assess_rev_tax_dist.project_accounting_context,
l_self_assess_rev_tax_dist.project_id,
l_self_assess_rev_tax_dist.task_id,
l_self_assess_rev_tax_dist.packet_id,
l_self_assess_rev_tax_dist.awt_flag,
l_self_assess_rev_tax_dist.awt_group_id,
l_self_assess_rev_tax_dist.awt_tax_rate_id,
l_self_assess_rev_tax_dist.awt_gross_amount,
l_self_assess_rev_tax_dist.awt_invoice_id,
l_self_assess_rev_tax_dist.awt_origin_group_id,
l_self_assess_rev_tax_dist.reference_1,
l_self_assess_rev_tax_dist.reference_2,
l_self_assess_rev_tax_dist.org_id,
l_self_assess_rev_tax_dist.awt_invoice_payment_id,
l_self_assess_rev_tax_dist.global_attribute_category,
l_self_assess_rev_tax_dist.global_attribute1,
l_self_assess_rev_tax_dist.global_attribute2,
l_self_assess_rev_tax_dist.global_attribute3,
l_self_assess_rev_tax_dist.global_attribute4,
l_self_assess_rev_tax_dist.global_attribute5,
l_self_assess_rev_tax_dist.global_attribute6,
l_self_assess_rev_tax_dist.global_attribute7,
l_self_assess_rev_tax_dist.global_attribute8,
l_self_assess_rev_tax_dist.global_attribute9,
l_self_assess_rev_tax_dist.global_attribute10,
l_self_assess_rev_tax_dist.global_attribute11,
l_self_assess_rev_tax_dist.global_attribute12,
l_self_assess_rev_tax_dist.global_attribute13,
l_self_assess_rev_tax_dist.global_attribute14,
l_self_assess_rev_tax_dist.global_attribute15,
l_self_assess_rev_tax_dist.global_attribute16,
l_self_assess_rev_tax_dist.global_attribute17,
l_self_assess_rev_tax_dist.global_attribute18,
l_self_assess_rev_tax_dist.global_attribute19,
l_self_assess_rev_tax_dist.global_attribute20,
l_self_assess_rev_tax_dist.receipt_verified_flag,
l_self_assess_rev_tax_dist.receipt_required_flag,
l_self_assess_rev_tax_dist.receipt_missing_flag,
l_self_assess_rev_tax_dist.justification,
l_self_assess_rev_tax_dist.expense_group,
l_self_assess_rev_tax_dist.start_expense_date,
l_self_assess_rev_tax_dist.end_expense_date,
l_self_assess_rev_tax_dist.receipt_currency_code,
l_self_assess_rev_tax_dist.receipt_conversion_rate,
l_self_assess_rev_tax_dist.receipt_currency_amount,
l_self_assess_rev_tax_dist.daily_amount,
l_self_assess_rev_tax_dist.web_parameter_id,
l_self_assess_rev_tax_dist.adjustment_reason,
l_self_assess_rev_tax_dist.award_id,
l_self_assess_rev_tax_dist.credit_card_trx_id,
l_self_assess_rev_tax_dist.dist_match_type,
l_self_assess_rev_tax_dist.rcv_transaction_id,
l_self_assess_rev_tax_dist.invoice_distribution_id,
l_self_assess_rev_tax_dist.parent_reversal_id,
l_self_assess_rev_tax_dist.tax_recoverable_flag,
l_self_assess_rev_tax_dist.merchant_document_number,
l_self_assess_rev_tax_dist.merchant_name,
l_self_assess_rev_tax_dist.merchant_reference,
l_self_assess_rev_tax_dist.merchant_tax_reg_number,
l_self_assess_rev_tax_dist.merchant_taxpayer_id,
l_self_assess_rev_tax_dist.country_of_supply,
l_self_assess_rev_tax_dist.matched_uom_lookup_code,
l_self_assess_rev_tax_dist.gms_burdenable_raw_cost,
l_self_assess_rev_tax_dist.accounting_event_id,
l_self_assess_rev_tax_dist.prepay_distribution_id,
l_self_assess_rev_tax_dist.upgrade_posted_amt,
l_self_assess_rev_tax_dist.upgrade_base_posted_amt,
l_self_assess_rev_tax_dist.inventory_transfer_status,
l_self_assess_rev_tax_dist.company_prepaid_invoice_id,
l_self_assess_rev_tax_dist.cc_reversal_flag,
l_self_assess_rev_tax_dist.awt_withheld_amt,
l_self_assess_rev_tax_dist.pa_cmt_xface_flag,
l_self_assess_rev_tax_dist.cancellation_flag,
l_self_assess_rev_tax_dist.invoice_line_number,
l_self_assess_rev_tax_dist.corrected_invoice_dist_id,
l_self_assess_rev_tax_dist.rounding_amt,
l_self_assess_rev_tax_dist.charge_applicable_to_dist_id,
l_self_assess_rev_tax_dist.corrected_quantity,
l_self_assess_rev_tax_dist.related_id,
l_self_assess_rev_tax_dist.asset_book_type_code,
l_self_assess_rev_tax_dist.asset_category_id,
l_self_assess_rev_tax_dist.distribution_class,
l_self_assess_rev_tax_dist.tax_code_id,
l_self_assess_rev_tax_dist.intended_use,
l_self_assess_rev_tax_dist.detail_tax_dist_id,
l_self_assess_rev_tax_dist.rec_nrec_rate,
l_self_assess_rev_tax_dist.recovery_rate_id,
l_self_assess_rev_tax_dist.recovery_type_code,
l_self_assess_rev_tax_dist.withholding_tax_code_id,
l_self_assess_rev_tax_dist.taxable_amount,
l_self_assess_rev_tax_dist.taxable_base_amount,
l_self_assess_rev_tax_dist.tax_already_distributed_flag,
l_self_assess_rev_tax_dist.summary_tax_line_id,
l_self_assess_rev_tax_dist.rcv_charge_addition_flag,
l_self_assess_rev_tax_dist.self_assessed_flag,
l_self_assess_rev_tax_dist.self_assessed_tax_liab_ccid, --bug6805655
l_self_assess_rev_tax_dist.prepay_tax_diff_amount -- BUG 7338249
);
INSERT INTO ap_invoice_distributions_all (
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
--batch_id,
created_by,
creation_date,
description,
final_match_flag,
income_tax_region,
last_update_login,
match_status_flag,
posted_flag,
po_distribution_id,
program_application_id,
program_id,
program_update_date,
quantity_invoiced,
request_id,
reversal_flag,
type_1099,
unit_price,
encumbered_flag,
stat_amount,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
parent_invoice_id,
pa_addition_flag,
pa_quantity,
prepay_amount_remaining,
project_accounting_context,
project_id,
task_id,
packet_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
reference_1,
reference_2,
org_id,
awt_invoice_payment_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
award_id,
credit_card_trx_id,
dist_match_type,
rcv_transaction_id,
invoice_distribution_id,
parent_reversal_id,
tax_recoverable_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
matched_uom_lookup_code,
gms_burdenable_raw_cost,
accounting_event_id,
prepay_distribution_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
inventory_transfer_status,
company_prepaid_invoice_id,
cc_reversal_flag,
awt_withheld_amt,
pa_cmt_xface_flag,
cancellation_flag,
invoice_line_number,
corrected_invoice_dist_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_quantity,
related_id,
asset_book_type_code,
asset_category_id,
distribution_class,
tax_code_id,
intended_use,
detail_tax_dist_id,
rec_nrec_rate,
recovery_rate_id,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
prepay_tax_diff_amount) -- BUG 7338249
VALUES
(
l_reverse_tax_dist.accounting_date,
l_reverse_tax_dist.accrual_posted_flag,
l_reverse_tax_dist.assets_addition_flag,
l_reverse_tax_dist.assets_tracking_flag,
l_reverse_tax_dist.cash_posted_flag,
l_reverse_tax_dist.distribution_line_number,
l_reverse_tax_dist.dist_code_combination_id,
l_reverse_tax_dist.invoice_id,
l_reverse_tax_dist.last_updated_by,
l_reverse_tax_dist.last_update_date,
l_reverse_tax_dist.line_type_lookup_code,
l_reverse_tax_dist.period_name,
l_reverse_tax_dist.set_of_books_id,
l_reverse_tax_dist.amount,
l_reverse_tax_dist.base_amount,
--l_reverse_tax_dist.batch_id,
l_reverse_tax_dist.created_by,
l_reverse_tax_dist.creation_date,
l_reverse_tax_dist.description,
l_reverse_tax_dist.final_match_flag,
l_reverse_tax_dist.income_tax_region,
l_reverse_tax_dist.last_update_login,
l_reverse_tax_dist.match_status_flag,
l_reverse_tax_dist.posted_flag,
l_reverse_tax_dist.po_distribution_id,
l_reverse_tax_dist.program_application_id,
l_reverse_tax_dist.program_id,
l_reverse_tax_dist.program_update_date,
l_reverse_tax_dist.quantity_invoiced,
l_reverse_tax_dist.request_id,
l_reverse_tax_dist.reversal_flag,
l_reverse_tax_dist.type_1099,
l_reverse_tax_dist.unit_price,
l_reverse_tax_dist.encumbered_flag,
l_reverse_tax_dist.stat_amount,
l_reverse_tax_dist.attribute1,
l_reverse_tax_dist.attribute10,
l_reverse_tax_dist.attribute11,
l_reverse_tax_dist.attribute12,
l_reverse_tax_dist.attribute13,
l_reverse_tax_dist.attribute14,
l_reverse_tax_dist.attribute15,
l_reverse_tax_dist.attribute2,
l_reverse_tax_dist.attribute3,
l_reverse_tax_dist.attribute4,
l_reverse_tax_dist.attribute5,
l_reverse_tax_dist.attribute6,
l_reverse_tax_dist.attribute7,
l_reverse_tax_dist.attribute8,
l_reverse_tax_dist.attribute9,
l_reverse_tax_dist.attribute_category,
l_reverse_tax_dist.expenditure_item_date,
l_reverse_tax_dist.expenditure_organization_id,
l_reverse_tax_dist.expenditure_type,
l_reverse_tax_dist.parent_invoice_id,
l_reverse_tax_dist.pa_addition_flag,
l_reverse_tax_dist.pa_quantity,
l_reverse_tax_dist.prepay_amount_remaining,
l_reverse_tax_dist.project_accounting_context,
l_reverse_tax_dist.project_id,
l_reverse_tax_dist.task_id,
l_reverse_tax_dist.packet_id,
l_reverse_tax_dist.awt_flag,
l_reverse_tax_dist.awt_group_id,
l_reverse_tax_dist.awt_tax_rate_id,
l_reverse_tax_dist.awt_gross_amount,
l_reverse_tax_dist.awt_invoice_id,
l_reverse_tax_dist.awt_origin_group_id,
l_reverse_tax_dist.reference_1,
l_reverse_tax_dist.reference_2,
l_reverse_tax_dist.org_id,
l_reverse_tax_dist.awt_invoice_payment_id,
l_reverse_tax_dist.global_attribute_category,
l_reverse_tax_dist.global_attribute1,
l_reverse_tax_dist.global_attribute2,
l_reverse_tax_dist.global_attribute3,
l_reverse_tax_dist.global_attribute4,
l_reverse_tax_dist.global_attribute5,
l_reverse_tax_dist.global_attribute6,
l_reverse_tax_dist.global_attribute7,
l_reverse_tax_dist.global_attribute8,
l_reverse_tax_dist.global_attribute9,
l_reverse_tax_dist.global_attribute10,
l_reverse_tax_dist.global_attribute11,
l_reverse_tax_dist.global_attribute12,
l_reverse_tax_dist.global_attribute13,
l_reverse_tax_dist.global_attribute14,
l_reverse_tax_dist.global_attribute15,
l_reverse_tax_dist.global_attribute16,
l_reverse_tax_dist.global_attribute17,
l_reverse_tax_dist.global_attribute18,
l_reverse_tax_dist.global_attribute19,
l_reverse_tax_dist.global_attribute20,
l_reverse_tax_dist.receipt_verified_flag,
l_reverse_tax_dist.receipt_required_flag,
l_reverse_tax_dist.receipt_missing_flag,
l_reverse_tax_dist.justification,
l_reverse_tax_dist.expense_group,
l_reverse_tax_dist.start_expense_date,
l_reverse_tax_dist.end_expense_date,
l_reverse_tax_dist.receipt_currency_code,
l_reverse_tax_dist.receipt_conversion_rate,
l_reverse_tax_dist.receipt_currency_amount,
l_reverse_tax_dist.daily_amount,
l_reverse_tax_dist.web_parameter_id,
l_reverse_tax_dist.adjustment_reason,
l_reverse_tax_dist.award_id,
l_reverse_tax_dist.credit_card_trx_id,
l_reverse_tax_dist.dist_match_type,
l_reverse_tax_dist.rcv_transaction_id,
l_reverse_tax_dist.invoice_distribution_id,
l_reverse_tax_dist.parent_reversal_id,
l_reverse_tax_dist.tax_recoverable_flag,
l_reverse_tax_dist.merchant_document_number,
l_reverse_tax_dist.merchant_name,
l_reverse_tax_dist.merchant_reference,
l_reverse_tax_dist.merchant_tax_reg_number,
l_reverse_tax_dist.merchant_taxpayer_id,
l_reverse_tax_dist.country_of_supply,
l_reverse_tax_dist.matched_uom_lookup_code,
l_reverse_tax_dist.gms_burdenable_raw_cost,
l_reverse_tax_dist.accounting_event_id,
l_reverse_tax_dist.prepay_distribution_id,
l_reverse_tax_dist.upgrade_posted_amt,
l_reverse_tax_dist.upgrade_base_posted_amt,
l_reverse_tax_dist.inventory_transfer_status,
l_reverse_tax_dist.company_prepaid_invoice_id,
l_reverse_tax_dist.cc_reversal_flag,
l_reverse_tax_dist.awt_withheld_amt,
l_reverse_tax_dist.pa_cmt_xface_flag,
l_reverse_tax_dist.cancellation_flag,
l_reverse_tax_dist.invoice_line_number,
l_reverse_tax_dist.corrected_invoice_dist_id,
l_reverse_tax_dist.rounding_amt,
l_reverse_tax_dist.charge_applicable_to_dist_id,
l_reverse_tax_dist.corrected_quantity,
l_reverse_tax_dist.related_id,
l_reverse_tax_dist.asset_book_type_code,
l_reverse_tax_dist.asset_category_id,
l_reverse_tax_dist.distribution_class,
l_reverse_tax_dist.tax_code_id,
l_reverse_tax_dist.intended_use,
l_reverse_tax_dist.detail_tax_dist_id,
l_reverse_tax_dist.rec_nrec_rate,
l_reverse_tax_dist.recovery_rate_id,
l_reverse_tax_dist.recovery_type_code,
l_reverse_tax_dist.withholding_tax_code_id,
l_reverse_tax_dist.taxable_amount,
l_reverse_tax_dist.taxable_base_amount,
l_reverse_tax_dist.tax_already_distributed_flag,
l_reverse_tax_dist.summary_tax_line_id,
l_reverse_tax_dist.rcv_charge_addition_flag,
l_reverse_tax_dist.prepay_tax_diff_amount -- BUG 7338249
);
l_debug_info := 'Inserting reverse entries into ap_self_assessed_tax_dist_all';
INSERT INTO ap_self_assessed_tax_dist_all (
accounting_date,
accrual_posted_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
invoice_id,
last_updated_by,
last_update_date,
line_type_lookup_code,
period_name,
set_of_books_id,
amount,
base_amount,
--batch_id,
created_by,
creation_date,
description,
final_match_flag,
income_tax_region,
last_update_login,
match_status_flag,
posted_flag,
po_distribution_id,
program_application_id,
program_id,
program_update_date,
quantity_invoiced,
request_id,
reversal_flag,
type_1099,
unit_price,
encumbered_flag,
stat_amount,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
parent_invoice_id,
pa_addition_flag,
pa_quantity,
prepay_amount_remaining,
project_accounting_context,
project_id,
task_id,
packet_id,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
reference_1,
reference_2,
org_id,
awt_invoice_payment_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
award_id,
credit_card_trx_id,
dist_match_type,
rcv_transaction_id,
invoice_distribution_id,
parent_reversal_id,
tax_recoverable_flag,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
matched_uom_lookup_code,
gms_burdenable_raw_cost,
accounting_event_id,
prepay_distribution_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
inventory_transfer_status,
company_prepaid_invoice_id,
cc_reversal_flag,
awt_withheld_amt,
pa_cmt_xface_flag,
cancellation_flag,
invoice_line_number,
corrected_invoice_dist_id,
rounding_amt,
charge_applicable_to_dist_id,
corrected_quantity,
related_id,
asset_book_type_code,
asset_category_id,
distribution_class,
tax_code_id,
intended_use,
detail_tax_dist_id,
rec_nrec_rate,
recovery_rate_id,
recovery_type_code,
withholding_tax_code_id,
taxable_amount,
taxable_base_amount,
tax_already_distributed_flag,
summary_tax_line_id,
rcv_charge_addition_flag,
self_assessed_flag,
self_assessed_tax_liab_ccid, --bug6805655
prepay_tax_diff_amount -- BUG 7338249
)
VALUES
(
l_self_assess_rev_tax_dist_1.accounting_date,
l_self_assess_rev_tax_dist_1.accrual_posted_flag,
l_self_assess_rev_tax_dist_1.assets_addition_flag,
l_self_assess_rev_tax_dist_1.assets_tracking_flag,
l_self_assess_rev_tax_dist_1.cash_posted_flag,
l_self_assess_rev_tax_dist_1.distribution_line_number,
l_self_assess_rev_tax_dist_1.dist_code_combination_id,
l_self_assess_rev_tax_dist_1.invoice_id,
l_self_assess_rev_tax_dist_1.last_updated_by,
l_self_assess_rev_tax_dist_1.last_update_date,
l_self_assess_rev_tax_dist_1.line_type_lookup_code,
l_self_assess_rev_tax_dist_1.period_name,
l_self_assess_rev_tax_dist_1.set_of_books_id,
l_self_assess_rev_tax_dist_1.amount,
l_self_assess_rev_tax_dist_1.base_amount,
--l_self_assess_rev_tax_dist_1.batch_id,
l_self_assess_rev_tax_dist_1.created_by,
l_self_assess_rev_tax_dist_1.creation_date,
l_self_assess_rev_tax_dist_1.description,
l_self_assess_rev_tax_dist_1.final_match_flag,
l_self_assess_rev_tax_dist_1.income_tax_region,
l_self_assess_rev_tax_dist_1.last_update_login,
l_self_assess_rev_tax_dist_1.match_status_flag,
l_self_assess_rev_tax_dist_1.posted_flag,
l_self_assess_rev_tax_dist_1.po_distribution_id,
l_self_assess_rev_tax_dist_1.program_application_id,
l_self_assess_rev_tax_dist_1.program_id,
l_self_assess_rev_tax_dist_1.program_update_date,
l_self_assess_rev_tax_dist_1.quantity_invoiced,
l_self_assess_rev_tax_dist_1.request_id,
l_self_assess_rev_tax_dist_1.reversal_flag,
l_self_assess_rev_tax_dist_1.type_1099,
l_self_assess_rev_tax_dist_1.unit_price,
l_self_assess_rev_tax_dist_1.encumbered_flag,
l_self_assess_rev_tax_dist_1.stat_amount,
l_self_assess_rev_tax_dist_1.attribute1,
l_self_assess_rev_tax_dist_1.attribute10,
l_self_assess_rev_tax_dist_1.attribute11,
l_self_assess_rev_tax_dist_1.attribute12,
l_self_assess_rev_tax_dist_1.attribute13,
l_self_assess_rev_tax_dist_1.attribute14,
l_self_assess_rev_tax_dist_1.attribute15,
l_self_assess_rev_tax_dist_1.attribute2,
l_self_assess_rev_tax_dist_1.attribute3,
l_self_assess_rev_tax_dist_1.attribute4,
l_self_assess_rev_tax_dist_1.attribute5,
l_self_assess_rev_tax_dist_1.attribute6,
l_self_assess_rev_tax_dist_1.attribute7,
l_self_assess_rev_tax_dist_1.attribute8,
l_self_assess_rev_tax_dist_1.attribute9,
l_self_assess_rev_tax_dist_1.attribute_category,
l_self_assess_rev_tax_dist_1.expenditure_item_date,
l_self_assess_rev_tax_dist_1.expenditure_organization_id,
l_self_assess_rev_tax_dist_1.expenditure_type,
l_self_assess_rev_tax_dist_1.parent_invoice_id,
l_self_assess_rev_tax_dist_1.pa_addition_flag,
l_self_assess_rev_tax_dist_1.pa_quantity,
l_self_assess_rev_tax_dist_1.prepay_amount_remaining,
l_self_assess_rev_tax_dist_1.project_accounting_context,
l_self_assess_rev_tax_dist_1.project_id,
l_self_assess_rev_tax_dist_1.task_id,
l_self_assess_rev_tax_dist_1.packet_id,
l_self_assess_rev_tax_dist_1.awt_flag,
l_self_assess_rev_tax_dist_1.awt_group_id,
l_self_assess_rev_tax_dist_1.awt_tax_rate_id,
l_self_assess_rev_tax_dist_1.awt_gross_amount,
l_self_assess_rev_tax_dist_1.awt_invoice_id,
l_self_assess_rev_tax_dist_1.awt_origin_group_id,
l_self_assess_rev_tax_dist_1.reference_1,
l_self_assess_rev_tax_dist_1.reference_2,
l_self_assess_rev_tax_dist_1.org_id,
l_self_assess_rev_tax_dist_1.awt_invoice_payment_id,
l_self_assess_rev_tax_dist_1.global_attribute_category,
l_self_assess_rev_tax_dist_1.global_attribute1,
l_self_assess_rev_tax_dist_1.global_attribute2,
l_self_assess_rev_tax_dist_1.global_attribute3,
l_self_assess_rev_tax_dist_1.global_attribute4,
l_self_assess_rev_tax_dist_1.global_attribute5,
l_self_assess_rev_tax_dist_1.global_attribute6,
l_self_assess_rev_tax_dist_1.global_attribute7,
l_self_assess_rev_tax_dist_1.global_attribute8,
l_self_assess_rev_tax_dist_1.global_attribute9,
l_self_assess_rev_tax_dist_1.global_attribute10,
l_self_assess_rev_tax_dist_1.global_attribute11,
l_self_assess_rev_tax_dist_1.global_attribute12,
l_self_assess_rev_tax_dist_1.global_attribute13,
l_self_assess_rev_tax_dist_1.global_attribute14,
l_self_assess_rev_tax_dist_1.global_attribute15,
l_self_assess_rev_tax_dist_1.global_attribute16,
l_self_assess_rev_tax_dist_1.global_attribute17,
l_self_assess_rev_tax_dist_1.global_attribute18,
l_self_assess_rev_tax_dist_1.global_attribute19,
l_self_assess_rev_tax_dist_1.global_attribute20,
l_self_assess_rev_tax_dist_1.receipt_verified_flag,
l_self_assess_rev_tax_dist_1.receipt_required_flag,
l_self_assess_rev_tax_dist_1.receipt_missing_flag,
l_self_assess_rev_tax_dist_1.justification,
l_self_assess_rev_tax_dist_1.expense_group,
l_self_assess_rev_tax_dist_1.start_expense_date,
l_self_assess_rev_tax_dist_1.end_expense_date,
l_self_assess_rev_tax_dist_1.receipt_currency_code,
l_self_assess_rev_tax_dist_1.receipt_conversion_rate,
l_self_assess_rev_tax_dist_1.receipt_currency_amount,
l_self_assess_rev_tax_dist_1.daily_amount,
l_self_assess_rev_tax_dist_1.web_parameter_id,
l_self_assess_rev_tax_dist_1.adjustment_reason,
l_self_assess_rev_tax_dist_1.award_id,
l_self_assess_rev_tax_dist_1.credit_card_trx_id,
l_self_assess_rev_tax_dist_1.dist_match_type,
l_self_assess_rev_tax_dist_1.rcv_transaction_id,
l_self_assess_rev_tax_dist_1.invoice_distribution_id,
l_self_assess_rev_tax_dist_1.parent_reversal_id,
l_self_assess_rev_tax_dist_1.tax_recoverable_flag,
l_self_assess_rev_tax_dist_1.merchant_document_number,
l_self_assess_rev_tax_dist_1.merchant_name,
l_self_assess_rev_tax_dist_1.merchant_reference,
l_self_assess_rev_tax_dist_1.merchant_tax_reg_number,
l_self_assess_rev_tax_dist_1.merchant_taxpayer_id,
l_self_assess_rev_tax_dist_1.country_of_supply,
l_self_assess_rev_tax_dist_1.matched_uom_lookup_code,
l_self_assess_rev_tax_dist_1.gms_burdenable_raw_cost,
l_self_assess_rev_tax_dist_1.accounting_event_id,
l_self_assess_rev_tax_dist_1.prepay_distribution_id,
l_self_assess_rev_tax_dist_1.upgrade_posted_amt,
l_self_assess_rev_tax_dist_1.upgrade_base_posted_amt,
l_self_assess_rev_tax_dist_1.inventory_transfer_status,
l_self_assess_rev_tax_dist_1.company_prepaid_invoice_id,
l_self_assess_rev_tax_dist_1.cc_reversal_flag,
l_self_assess_rev_tax_dist_1.awt_withheld_amt,
l_self_assess_rev_tax_dist_1.pa_cmt_xface_flag,
l_self_assess_rev_tax_dist_1.cancellation_flag,
l_self_assess_rev_tax_dist_1.invoice_line_number,
l_self_assess_rev_tax_dist_1.corrected_invoice_dist_id,
l_self_assess_rev_tax_dist_1.rounding_amt,
l_self_assess_rev_tax_dist_1.charge_applicable_to_dist_id,
l_self_assess_rev_tax_dist_1.corrected_quantity,
l_self_assess_rev_tax_dist_1.related_id,
l_self_assess_rev_tax_dist_1.asset_book_type_code,
l_self_assess_rev_tax_dist_1.asset_category_id,
l_self_assess_rev_tax_dist_1.distribution_class,
l_self_assess_rev_tax_dist_1.tax_code_id,
l_self_assess_rev_tax_dist_1.intended_use,
l_self_assess_rev_tax_dist_1.detail_tax_dist_id,
l_self_assess_rev_tax_dist_1.rec_nrec_rate,
l_self_assess_rev_tax_dist_1.recovery_rate_id,
l_self_assess_rev_tax_dist_1.recovery_type_code,
l_self_assess_rev_tax_dist_1.withholding_tax_code_id,
l_self_assess_rev_tax_dist_1.taxable_amount,
l_self_assess_rev_tax_dist_1.taxable_base_amount,
l_self_assess_rev_tax_dist_1.tax_already_distributed_flag,
l_self_assess_rev_tax_dist_1.summary_tax_line_id,
l_self_assess_rev_tax_dist_1.rcv_charge_addition_flag,
l_self_assess_rev_tax_dist_1.self_assessed_flag,
l_self_assess_rev_tax_dist_1.self_assessed_tax_liab_ccid, --bug6805655
l_self_assess_rev_tax_dist_1.prepay_tax_diff_amount -- BUG 7338249
);
UPDATE ap_invoice_lines_all a
SET amount = (select nvl(tax_amt, 0)
from zx_lines_summary b
where b.summary_tax_line_id = a.summary_tax_line_id)
WHERE a.invoice_id = p_invoice_id
AND a.line_type_lookup_code = 'TAX'
AND exists
(select 'Detail Line'
from zx_lines zx
where zx.summary_tax_line_id = a.summary_tax_line_id
and zx.trx_id = p_invoice_id
and zx.trx_line_id = p_line_number);
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', NVL(zls.tax_amt, 0),
0)),
SUM(DECODE(NVL(zls.self_assessed_flag, 'N'),
'Y', NVL(zls.tax_amt, 0),
0))
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 = ai.invoice_id
AND NVL(zls.reporting_only_flag, 'N') = 'N')
WHERE ai.invoice_id = p_invoice_id;
l_debug_info := 'Step 4: Update reversal_flag';
UPDATE ap_invoice_distributions_all aid
SET reversal_flag = (select reverse_flag
from zx_rec_nrec_dist zx
where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = p_invoice_id
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Step 5: Update related_flag';
UPDATE ap_invoice_distributions aid
SET aid.related_id =
(SELECT invoice_distribution_id
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.parent_reversal_id =
(SELECT related_id
FROM ap_invoice_distributions_all aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.invoice_line_number = aid.invoice_line_number
AND aid2.invoice_distribution_id = aid.parent_reversal_id)
)
WHERE aid.related_id IS NULL
AND aid.parent_reversal_id IS NOT NULL
AND aid.invoice_id = p_invoice_id
AND aid.reversal_flag = 'Y'
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Step 4: Update reversal_flag';
UPDATE ap_self_assessed_tax_dist_all aid
SET reversal_flag = (select reverse_flag
from zx_rec_nrec_dist zx
where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = p_invoice_id
AND aid.detail_tax_dist_id IS NOT NULL;
l_debug_info := 'Step 5: Update related_flag';
UPDATE ap_self_assessed_tax_dist_all aid
SET aid.related_id =
(SELECT invoice_distribution_id
FROM ap_self_assessed_tax_dist_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
AND aid1.parent_reversal_id =
(SELECT related_id
FROM ap_self_assessed_tax_dist_all aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.invoice_line_number = aid.invoice_line_number
AND aid2.invoice_distribution_id = aid.parent_reversal_id)
)
WHERE aid.related_id IS NULL
AND aid.parent_reversal_id IS NOT NULL
AND aid.invoice_id = p_invoice_id
AND aid.reversal_flag = 'Y'
AND aid.detail_tax_dist_id IS NOT NULL;
SELECT *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT distinct prepLine.*
FROM ap_invoice_distributions_all invDist,
ap_invoice_distributions_all prepDist,
ap_invoice_lines_all prepLine
WHERE invDist.prepay_distribution_id = prepDist.invoice_distribution_id
AND prepLine.invoice_id = prepDist.invoice_id
AND prepLine.line_number = prepDist.invoice_line_number
AND invDist.line_type_lookup_code = 'PREPAY'
AND invDist.invoice_id = p_invoice_id
AND invDist.invoice_line_number = p_invoice_line_number;
DELETE FROM ZX_TRANSACTION_LINES_GT;
AP_ETAX_SERVICES_PKG.G_SITE_ATTRIBUTES.DELETE;
AP_ETAX_SERVICES_PKG.G_ORG_ATTRIBUTES.DELETE;
l_debug_info := 'Step 5.1: Update Tax Already Calculated Flag';
UPDATE ap_invoice_lines_all ail
SET ail.tax_already_calculated_flag = 'Y'
WHERE ail.invoice_id = p_invoice_id
AND ail.line_number = p_invoice_line_number;
DELETE FROM ZX_TRX_HEADERS_GT;
DELETE FROM ZX_TRANSACTION_LINES_GT;
DELETE FROM ZX_TRX_HEADERS_GT;
DELETE FROM ZX_TRANSACTION_LINES_GT;
Function Delete_Tax_Distributions
(p_invoice_id IN ap_invoice_distributions_all.invoice_id%Type,
p_calling_mode IN VARCHAR2,
p_all_error_messages IN VARCHAR2,
p_error_code OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
Cursor c_preview_dists (c_invoice_id Number) Is
Select org_id, invoice_id, invoice_line_number
From ap_invoice_distributions_all
--bugfix:5565648 added the 'invoice_id = c_invoice_id' as without that
--it was doing a full table scan
Where invoice_id = c_invoice_id
AND distribution_class = 'CANDIDATE'
And line_type_lookup_code Not In
('NONREC_TAX', 'REC_TAX', 'TRV', 'TERV', 'TIPV', 'AWT')
And (related_id IS NULL
or related_id = invoice_distribution_id);
l_api_name CONSTANT VARCHAR2(100) := 'Delete_Tax_distributions';
l_curr_calling_sequence := 'AP_ETAX_SERVICES_PKG.Delete_Tax_Distributions<-' ||
p_calling_sequence;
l_debug_info := 'Exit delete_tax_distributions';
Select * Into l_invoice_header_rec
From ap_invoices_all
Where invoice_id = p_invoice_id;
UPDATE ap_invoice_distributions_All aid1
SET aid1.amount = aid1.amount + nvl((SELECT SUM(nvl(aid2.amount,0))
FROM ap_invoice_distributions_All aid2
WHERE aid2.invoice_id = p_invoice_id
AND aid2.invoice_line_number = aid1.invoice_line_number
AND aid2.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')
AND aid2.distribution_class = 'CANDIDATE'),0),
aid1.base_amount = aid1.base_amount + nvl((SELECT SUM(nvl(aid3.base_amount,0))
FROM ap_invoice_distributions_All aid3
WHERE aid3.invoice_id = p_invoice_id
AND aid3.invoice_line_number = aid1.invoice_line_number
AND aid3.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TIPV')
AND aid3.distribution_class = 'CANDIDATE'),0)
WHERE aid1.invoice_id = p_invoice_id
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS');
l_debug_info := 'Step 5: Call eTax API to delete tax distributions';
ZX_API_PUB.delete_tax_distributions(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_line_rec => l_transaction_line_rec_type
);
DELETE FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_preview_dists.invoice_id
AND invoice_line_number = l_preview_dists.invoice_line_number;
END Delete_Tax_distributions;
SELECT 'Tax Distributions 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 rownum = 1;
/* 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 'Tax Distributions Exist'
INTO l_dummy
FROM ap_self_assessed_tax_dist_all asat,
zx_rec_nrec_dist zx_dist
WHERE invoice_id = p_invoice_id
AND asat.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND zx_dist.self_assessed_flag = 'Y'
AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND rownum = 1;
SELECT D.unit_price unit_price,
nvl(D.quantity_invoiced, 0) quantity_invoiced,
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 *
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT *
FROM ap_invoice_lines_all
WHERE invoice_id = P_Invoice_Id
AND line_type_lookup_code = 'TAX'
AND summary_tax_line_id IS NULL;
SELECT location_id
INTO l_bill_to_location_id
FROM hr_all_organization_units
WHERE organization_id = P_Invoice_Header_Rec.org_id;
SELECT location_id, fob_lookup_code
INTO l_location_id, l_fob_point
FROM ap_supplier_sites_all
WHERE vendor_site_id = P_Invoice_Header_Rec.vendor_site_id;
l_debug_info := 'Step 14: Bulk Insert into global temp tables';
DELETE FROM zx_transaction_lines_gt;
INSERT INTO zx_transaction_lines_gt
VALUES trans_lines(m);
DELETE FROM zx_trx_headers_gt;
INSERT INTO zx_trx_headers_gt(
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
ledger_id,
trx_currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
minimum_accountable_unit,
precision,
legal_entity_id,
rounding_ship_from_party_id,
rounding_bill_from_party_id,
rndg_ship_from_party_site_id,
rndg_bill_from_party_site_id,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
default_taxation_country,
quote_flag,
ctrl_total_hdr_tx_amt,
trx_number,
trx_description,
doc_seq_id,
doc_seq_name,
doc_seq_value,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_exchange_rate,
tax_invoice_date,
tax_invoice_number,
bill_third_pty_acct_id,
bill_third_pty_acct_site_id,
ship_third_pty_acct_id,
ship_third_pty_acct_site_id
)
SELECT
ai.org_id, --internal_organization_id
200, --application_id
'AP_INVOICES', --entity_code
(CASE
WHEN ai.invoice_type_lookup_code IN
('STANDARD', 'CREDIT', 'DEBIT', 'MIXED',
'ADJUSTMENT', 'PO PRICE ADJUST', 'INVOICE REQUEST',
'CREDIT MEMO REQUEST', 'RETAINAGE RELEASE')
THEN 'STANDARD INVOICES'
WHEN (ai.invoice_type_lookup_code = 'PREPAYMENT')
or (p_calling_mode IN ('RECOUPMENT', 'DISTRIBUTE RECOUP'))
THEN 'PREPAYMENT INVOICES'
WHEN ai.invoice_type_lookup_code = 'EXPENSE REPORT'
THEN 'EXPENSE REPORTS'
END), --event_class_code
(CASE
WHEN ai.invoice_type_lookup_code IN
('STANDARD', 'CREDIT', 'DEBIT', 'MIXED',
'ADJUSTMENT', 'PO PRICE ADJUST', 'INVOICE REQUEST',
'CREDIT MEMO REQUEST', 'RETAINAGE RELEASE')
THEN 'STANDARD '
WHEN (ai.invoice_type_lookup_code = 'PREPAYMENT')
or (p_calling_mode IN ('RECOUPMENT', 'DISTRIBUTE RECOUP'))
THEN 'PREPAYMENT '
WHEN ai.invoice_type_lookup_code = 'EXPENSE REPORT'
THEN 'EXPENSE REPORT '
END)||
DECODE(p_calling_mode,
'CALCULATE',
(CASE ((SELECT 'Y'
FROM ap_invoice_lines_all
WHERE invoice_id = ai.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)
--- Start for bug 6485124
UNION
(SELECT 'Y'
FROM zx_lines_det_factors
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND trx_id = ai.invoice_id
AND event_class_code in ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
AND ROWNUM=1))
--- End for bug 6485124
WHEN 'Y' THEN 'UPDATED'
ELSE 'CREATED'
END),
'DISTRIBUTE',
(CASE (SELECT 'Y'
FROM ap_invoice_distributions_all
WHERE invoice_id = ai.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)
WHEN 'Y' THEN 'REDISTRIBUTE'
ELSE 'DISTRIBUTE'
END)), --event_type_code
ai.invoice_id, --trx_id
ai.invoice_date, --trx_date
ai.set_of_books_id, --ledger_id
ai.invoice_currency_code, --trx_currency_code
ai.exchange_date, --currency_conversion_date
ai.exchange_rate, --currency_conversion_rate
ai.exchange_rate_type, --currency_conversion_type
NVL(cur.minimum_accountable_unit,
(1/power(10,cur.precision))), --minimum_accountable_unit
nvl(cur.precision,0), --precision
ai.legal_entity_id, --legal_entity_id
ai.party_id, --rounding_ship_from_party_id
ai.party_id, --rounding_bill_from_party_id
ai.party_site_id, --rndg_ship_from_party_site_id
ai.party_site_id, --rndg_bill_from_party_site_id
(CASE
WHEN related_ai.invoice_type_lookup_code IS NOT NULL
THEN 200
ELSE NULL
END), --related_doc_application_id
(CASE
WHEN related_ai.invoice_type_lookup_code IS NOT NULL
THEN 'AP_INVOICES'
ELSE NULL
END), --related_doc_entity_code
(CASE
WHEN related_ai.invoice_type_lookup_code IN
('STANDARD', 'CREDIT', 'DEBIT', 'MIXED',
'ADJUSTMENT', 'PO PRICE ADJUST', 'INVOICE REQUEST',
'CREDIT MEMO REQUEST', 'RETAINAGE RELEASE')
THEN 'STANDARD INVOICES'
WHEN ai.invoice_type_lookup_code = 'PREPAYMENT'
THEN 'PREPAYMENT INVOICES'
WHEN ai.invoice_type_lookup_code = 'EXPENSE REPORT'
THEN 'EXPENSE REPORTS'
END), --related_doc_event_class_code
ai.tax_related_invoice_id, --related_doc_trx_id
related_ai.invoice_num, --related_doc_number
related_ai.invoice_date, --related_doc_date
ai.taxation_country, --default_taxation_country
decode(p_calling_mode,
'CALCULATE QUOTE', 'Y', 'N'), --quote_flag
ai.control_amount, --ctrl_total_hdr_tx_amt
ai.invoice_num, --trx_number
ai.description, --trx_description
ai.doc_sequence_id, --doc_seq_id
doc.name, --doc_seq_name
nvl(to_char(ai.doc_sequence_value), ai.voucher_num), --doc_seq_value bug6656894
ai.document_sub_type, --document_sub_type
ai.supplier_tax_invoice_number, --supplier_tax_invoice_number
ai.supplier_tax_invoice_date, --supplier_tax_invoice_date
ai.supplier_tax_exchange_rate, --supplier_exchange_rate
ai.tax_invoice_recording_date, --tax_invoice_date
ai.tax_invoice_internal_seq, --tax_invoice_number
ai.vendor_id, --bill_third_pty_acct_id
ai.vendor_site_id, --bill_third_pty_acct_site_id
ai.vendor_id, --ship_third_pty_acct_id
ai.vendor_site_id --ship_third_pty_acct_site_id
FROM ap_invoices_all ai,
fnd_currencies cur,
fnd_document_sequences doc,
ap_invoices_all related_ai
WHERE ai.invoice_currency_code = cur.currency_code
AND ai.doc_sequence_id = doc.doc_sequence_id (+)
AND ai.tax_related_invoice_id = related_ai.invoice_id (+)
AND (nvl(ai.quick_credit,'N') = 'N'
OR ai.invoice_type_lookup_code NOT IN ('AWT', 'INTEREST'))
AND ai.validation_request_id = p_validation_request_id;
Print(l_api_name, 'Rows inserted in zx_trx_headers_gt '||g_invoices_to_process);
FUNCTION Update_Distributions(
P_Invoice_header_rec IN ap_invoices_all%ROWTYPE,
P_Calling_Mode IN VARCHAR2,
P_All_Error_Messages IN VARCHAR2,
P_Error_Code OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR c_reverse_dist (c_invoice_id NUMBER) IS
SELECT aid_reverse.invoice_distribution_id invoice_distribution_id,
aid_parent.invoice_distribution_id parent_reversal_id,
aid_parent.dist_code_combination_id parent_ccid
FROM ap_invoice_distributions_all aid_reverse,
ap_invoice_distributions_all aid_parent,
zx_rec_nrec_dist zx
WHERE aid_reverse.invoice_id = c_invoice_id
AND aid_reverse.detail_tax_dist_id = zx.rec_nrec_tax_dist_id
AND aid_parent.detail_tax_dist_id = zx.reversed_tax_dist_id
AND aid_parent.line_type_lookup_code = aid_reverse.line_type_lookup_code;
SELECT amount,included_tax_amount,line_number,
(total_rec_tax_amt_funcl_curr + total_nrec_tax_amt_funcl_curr) base_included_tax_amount
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY') --/*Bug7338249*/
AND included_tax_amount <> 0 ;
l_api_name CONSTANT VARCHAR2(100) := 'Update_Distributions';
l_debug_info := 'Step 1: Update the related_id column';
UPDATE ap_invoice_distributions_all aid
SET related_id = (SELECT DECODE(MIN(nrtax.invoice_distribution_id),
NULL, MIN(other.invoice_distribution_id),
MIN(nrtax.invoice_distribution_id))
FROM ap_invoice_distributions_all nrtax,
ap_invoice_distributions_all other
WHERE nrtax.invoice_id = aid.invoice_id
AND other.invoice_id = aid.invoice_id
AND nrtax.detail_tax_dist_id = aid.detail_tax_dist_id
AND other.detail_tax_dist_id = aid.detail_tax_dist_id
AND (nrtax.line_type_lookup_code = 'NONREC_TAX'
OR other.line_type_lookup_code IN ('TIPV', 'TRV'))
GROUP BY 1)
WHERE aid.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aid.line_type_lookup_code in ('NONREC_TAX', 'TIPV', 'TRV', 'TERV')
AND EXISTS (SELECT aid1.detail_tax_dist_id
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.detail_tax_dist_id = aid.detail_tax_dist_id
HAVING count(*) > 1
GROUP BY aid1.detail_tax_dist_id);
l_debug_info := 'Step 2: Update the related_id column for self assessed distributions';
UPDATE ap_self_assessed_tax_dist_all aid
SET related_id = (SELECT DECODE(MIN(nrtax.invoice_distribution_id),
NULL, MIN(other.invoice_distribution_id),
MIN(nrtax.invoice_distribution_id))
FROM ap_self_assessed_tax_dist_all nrtax,
ap_self_assessed_tax_dist_all other
WHERE nrtax.invoice_id = aid.invoice_id
AND other.invoice_id = aid.invoice_id
AND nrtax.detail_tax_dist_id = aid.detail_tax_dist_id
AND other.detail_tax_dist_id = aid.detail_tax_dist_id
AND (nrtax.line_type_lookup_code = 'NONREC_TAX'
OR other.line_type_lookup_code IN ('TIPV', 'TRV', 'TERV'))
GROUP BY 1)
WHERE aid.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aid.line_type_lookup_code in ('NONREC_TAX', 'TIPV', 'TRV', 'TERV')
AND EXISTS (SELECT aid1.detail_tax_dist_id
FROM ap_self_assessed_tax_dist_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.detail_tax_dist_id = aid.detail_tax_dist_id
HAVING count(*) > 1
GROUP BY aid1.detail_tax_dist_id);
l_debug_info := 'Step 3: Update rounding_amt for the primary NONREC tax dist';
UPDATE ap_invoice_distributions_all aid
SET rounding_amt =
(SELECT zd.func_curr_rounding_adjustment
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aid.line_type_lookup_code in ('NONREC_TAX', 'TIPV', 'TRV', 'TERV')
AND (aid.related_id IS NULL
OR aid.related_id = aid.invoice_distribution_id);
l_debug_info := 'Step 4: Update rounding_amt for the primary NONREC self assessed dist';
UPDATE ap_self_assessed_tax_dist_all aid
SET rounding_amt =
(SELECT zd.func_curr_rounding_adjustment
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = P_Invoice_Header_Rec.invoice_id
AND aid.line_type_lookup_code in ('NONREC_TAX', 'TIPV', 'TRV', 'TERV')
AND (aid.related_id IS NULL
OR aid.related_id = aid.invoice_distribution_id);
l_debug_info := 'Step 5: Update REC and NONREC totals at line level';
UPDATE ap_invoice_lines_all ail
SET (ail.total_rec_tax_amount,
ail.total_nrec_tax_amount,
ail.total_rec_tax_amt_funcl_curr,
ail.total_nrec_tax_amt_funcl_curr) =
(SELECT SUM(DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NVL(zd.rec_nrec_tax_amt, 0),
0)),
SUM(DECODE(NVL(zd.recoverable_flag, 'N'),
'N', NVL(zd.rec_nrec_tax_amt, 0),
0)),
SUM(DECODE(NVL(zd.recoverable_flag, 'N'),
'Y', NVL(zd.rec_nrec_tax_amt_funcl_curr, 0),
0)),
SUM(DECODE(NVL(zd.recoverable_flag, 'N'),
'N', NVL(zd.rec_nrec_tax_amt_funcl_curr, 0),
0))
FROM zx_rec_nrec_dist zd
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 zd.trx_id = ail.invoice_id
AND zd.trx_line_id = ail.line_number
AND NVL(zd.self_assessed_flag, 'N') = 'N')
WHERE ail.invoice_id = P_Invoice_Header_Rec.invoice_id
AND (ail.summary_tax_line_id IS NOT NULL
OR (ail.line_type_lookup_code <> 'TAX'
AND NVL(ail.included_tax_amount, 0) <> 0));
l_debug_info := 'Step 5: Update tax_already_distributed_flag';
UPDATE ap_invoice_distributions_all aid
SET aid.tax_already_distributed_flag = 'Y'
WHERE aid.invoice_id = p_invoice_header_rec.invoice_id
AND NVL(aid.tax_already_distributed_flag, 'N') = 'N'
AND aid.invoice_distribution_id IN
( SELECT aid1.charge_applicable_to_dist_id
FROM ap_invoice_distributions_all aid1,
zx_rec_nrec_dist zd
WHERE zd.REC_NREC_TAX_DIST_ID = aid1.DETAIL_TAX_DIST_ID
AND aid1.invoice_id = p_invoice_header_rec.invoice_id );
l_debug_info := 'Step 6: Update generate_dists on the invoice line';
UPDATE ap_invoice_lines_all ail
SET generate_dists = 'D'
WHERE ail.invoice_id = p_invoice_header_rec.invoice_id
AND ail.generate_dists <> 'D'
AND line_type_lookup_code = 'TAX'
AND EXISTS
(SELECT aid.invoice_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number);
l_debug_info := 'Step 7: Update Invoice Includes Prepay Flag';
UPDATE ap_invoice_distributions_all tax
SET tax.invoice_includes_prepay_flag = 'Y'
WHERE tax.invoice_id = p_invoice_header_rec.invoice_id
AND nvl(tax.invoice_includes_prepay_flag,'N') <> 'Y'
AND line_type_lookup_code in ('NONREC_TAX','REC_TAX','TIPV','TERV','TRV')
AND exists
(SELECT 1
FROM ap_invoice_lines_all prepay
WHERE prepay.invoice_id = tax.invoice_id
AND prepay.line_number = tax.invoice_line_number
AND prepay.line_type_lookup_code = 'TAX'
AND prepay.prepay_line_number IS NOT NULL
AND nvl(prepay.invoice_includes_prepay_flag,'N') = 'Y');
l_debug_info := 'Step 7.1: Select sum of dist amount for each ITEM line';
SELECT sum(amount) -- Select added for bug 6326552 to make sure
INTO l_dist_amt -- sure that we update the distributions only once.
FROM ap_invoice_distributions_All
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND invoice_line_number = i.line_number
AND line_type_lookup_code IN ('ITEM', --Bug6653070 Added SUM()
--instead of only amount
-- bug 7145041: add start
-- These lookup codes also have inclusive amount included in their amount.
'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY');
l_debug_info := 'Step 7.3: Select sum of dist amount for each TAX line';
SELECT SUM(amount) -- Select added for bug 6326552 to make sure
INTO l_tot_tax_amt -- sure that we update the distributions if
-- if included tax amount is changed at
-- line level. .
FROM ap_invoice_distributions_All
WHERE invoice_id = p_invoice_header_rec.invoice_id
AND invoice_line_number = i.line_number
AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV');
l_debug_info := 'Step 7.5: Update dist amount for each included TAX' ;
UPDATE ap_invoice_distributions_All aid1 --Bug6653070
SET aid1.amount = aid1.amount - nvl((SELECT SUM(nvl(amount,0))
FROM ap_invoice_distributions_All aid2
WHERE aid2.invoice_id = p_invoice_header_rec.invoice_id
AND aid2.invoice_line_number = i.line_number
AND aid2.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TRV')),0),
aid1.base_amount =aid1.base_amount- nvl((SELECT SUM(nvl(base_amount,0))
FROM ap_invoice_distributions_All aid3
WHERE aid3.invoice_id = p_invoice_header_rec.invoice_id
AND aid3.invoice_line_number = i.line_number
AND aid3.charge_applicable_to_dist_id = aid1.invoice_distribution_id
AND aid3.line_type_lookup_code IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TRV','TERV')),0)
WHERE aid1.invoice_id = p_invoice_header_rec.invoice_id
AND aid1.invoice_line_number = i.line_number
AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'FREIGHT', 'MISCELLANEOUS','PREPAY');/*Bug7338249*/
l_debug_info := 'Step 8: Update parent_reversal_id and parent_ccid';
UPDATE ap_invoice_distributions_all
SET parent_reversal_id = l_reveral_dist_tab(h).parent_reversal_id
,dist_code_combination_id = l_reveral_dist_tab(h).parent_ccid
WHERE invoice_distribution_id = l_reveral_dist_tab(h).invoice_distribution_id
AND nvl(reversal_flag, 'N') <> 'Y';
l_debug_info := 'Step 9: Update reversal_flag';
UPDATE ap_invoice_distributions_all aid
SET reversal_flag = (select reverse_flag
from zx_rec_nrec_dist zx
where zx.rec_nrec_tax_dist_id = aid.detail_tax_dist_id)
WHERE aid.invoice_id = p_invoice_header_rec.invoice_id
AND aid.detail_tax_dist_id IS NOT NULL;
END Update_Distributions;
SELECT location_id
INTO AP_ETAX_SERVICES_PKG.g_org_attributes(p_org_id).bill_to_location_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT hps.location_id
INTO AP_ETAX_SERVICES_PKG.g_site_attributes(p_vendor_site_id).location_id
FROM hz_party_sites hps
WHERE party_site_id = p_vendor_site_id;
SELECT location_id
,fob_lookup_code
INTO AP_ETAX_SERVICES_PKG.g_site_attributes(p_vendor_site_id).location_id
,AP_ETAX_SERVICES_PKG.g_site_attributes(p_vendor_site_id).fob_lookup_code
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id;
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
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 := 'Step 2: Update existing exclusive tax lines';
UPDATE ap_invoice_lines_all ail
SET
(ail.description,
ail.amount,
ail.base_amount,
ail.cancelled_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
zls.tax_regime_code||' - '||zls.tax, -- description
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
l_sysdate, -- last_update_date
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');
l_debug_info := 'Step 5: 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)
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
zls.tax_regime_code||' - '||zls.tax, -- description
'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
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
l_sysdate, -- creation_date
l_user_id, -- created_by
l_user_id, -- last_updated_by
l_sysdate, -- last_update_date
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
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);
l_debug_info := 'Step 8: 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');
l_debug_info := 'Step 10: 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', NVL(zls.tax_amt, 0),
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;
l_debug_info := 'Step 12: Update Invoice Includes Prepay Flag';
UPDATE ap_invoice_lines_all tax
SET tax.invoice_includes_prepay_flag = 'Y'
WHERE tax.invoice_id = P_Invoice_Header_Rec.Invoice_Id
AND tax.line_type_lookup_code = 'TAX'
AND 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');