/*Tax*/
FUNCTION add_gst_tax_req (p_req_id NUMBER)
RETURN VARCHAR2
AS
CURSOR c
IS
/*
SELECT prla.requisition_header_id, prla.requisition_line_id,
prla.source_organization_id,
prla.destination_organization_id, prla.item_id
FROM po_requisition_lines_all prla, jai_tax_det_factors jtd
WHERE 1 = 1
AND prla.requisition_header_id = jtd.trx_id
AND prla.requisition_line_id = jtd.trx_line_id
AND jtd.override_tax_category_id IS NULL
AND requisition_header_id = p_req_id;
*/————Code Changes by Tax category based on source code on 21-Jul-2020——
SELECT prla.requisition_header_id, prla.requisition_line_id,
prla.source_organization_id,
prla.destination_organization_id, prla.item_id,
— DECODE(SUBSTR (organization_code, -1, 1), code changes done to include 05A and 08A
DECODE (SUBSTR (DECODE (organization_code,
’05A’, ‘555’,
’08A’, ‘555’,
organization_code
),
-1,
1
),
‘1’, ‘IRISO Inter GST’,
‘2’, ‘IRISO Inter GST-EPD’,
‘3’, ‘IRISO Inter GST-HVD’,
‘5’, ‘IRISO Inter GST-FDS’,
‘9’, ‘IRISO Inter GST-ORT’
) tax_cat_list
FROM po_requisition_lines_all prla,
jai_tax_det_factors jtd,
org_organization_definitions ood
WHERE 1 = 1
AND prla.requisition_header_id = jtd.trx_id
AND prla.requisition_line_id = jtd.trx_line_id
AND jtd.override_tax_category_id IS NULL
AND prla.source_organization_id = ood.organization_id
AND requisition_header_id = p_req_id;
v_source_gst VARCHAR2 (250) := NULL;
v_dest_gst VARCHAR2 (250) := NULL;
v_tax_category_id NUMBER;
vz_tax_category_id NUMBER;
v_item_type VARCHAR2 (250) := NULL;
v_item_no VARCHAR2 (250) := NULL;
v_template_id NUMBER;
v_return_msg VARCHAR2 (1000) := NULL;
v_regime_id NUMBER;
v_regime_code VARCHAR2 (250) := NULL;
v_regime_type VARCHAR2 (250) := NULL;
v_appl_id NUMBER;
v_rec_val VARCHAR2 (10) := NULL;
l_api_name VARCHAR2 (500)
:= g_module_prefix || ‘add_gst_tax_req’;
l_progress VARCHAR2 (240) := ‘001’;
lc_error_message LONG := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
BEGIN
DBMS_OUTPUT.put_line (l_api_name || ‘Started : ‘ || l_progress);
DBMS_OUTPUT.put_line (‘p_req_no : ‘ || p_req_id || CHR (13) || ‘001’);
BEGIN
SELECT regime_id, regime_code, regime_type
INTO v_regime_id, v_regime_code, v_regime_type
FROM jai_regimes
WHERE regime_code = ‘GST’;
SELECT application_id
INTO v_appl_id
FROM fnd_application
WHERE application_short_name = ‘PO’;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘****regime EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘v_regime_id : ‘
|| v_regime_id
|| ‘ v_regime_code : ‘
|| v_regime_code
|| ‘ v_regime_type : ‘
|| v_regime_type
|| CHR (13)
|| ‘002’
);
DBMS_OUTPUT.put_line (‘v_appl_id : ‘ || v_appl_id);
FOR i IN c
LOOP
DBMS_OUTPUT.put_line ( ‘ requisition_line_id : ‘
|| i.requisition_line_id
|| CHR (13)
|| ‘ i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘003’
);
BEGIN
SELECT registration_number
INTO v_source_gst
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = i.source_organization_id
AND party_type_code = ‘IO’);
EXCEPTION
WHEN OTHERS
THEN
v_return_msg :=
v_return_msg || ‘ Party Source GST Reg# Not found.’;
END;
DBMS_OUTPUT.put_line ( ‘ v_source_gst : ‘
|| v_source_gst
|| CHR (13)
|| ‘004’
);
BEGIN
SELECT registration_number
INTO v_dest_gst
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = i.destination_organization_id
AND party_type_code = ‘IO’);
EXCEPTION
WHEN OTHERS
THEN
v_return_msg :=
v_return_msg || ‘ Party Dest GST Reg# Not found.’;
END;
DBMS_OUTPUT.put_line ( ‘destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘ v_dest_gst : ‘
|| v_dest_gst
|| CHR (13)
|| ‘005’
);
IF v_source_gst <> v_dest_gst
THEN
DBMS_OUTPUT.put_line (‘ if condition : true ‘ || CHR (13)
|| ‘006’
);
BEGIN
SELECT tax_category_id
INTO v_tax_category_id
FROM jai_inv_itm_taxctg_dtls jiitd,
jai_inv_itm_taxctg_hdrs jiith
WHERE 1 = 1
AND jiitd.inventory_item_id = i.item_id
AND jiith.tax_category_list = i.tax_cat_list
–‘ IRISO Inter GST’ added for tax cat list based on source org code dt 20-Jul-2020
— AND jiith.organization_id = i.source_organization_id
AND jiitd.tax_category_list = jiith.tax_category_list;
/*Commented for not taking specific Tax category list against item
SELECT MAX (tax_category_id)
INTO v_tax_category_id
FROM jai_inv_itm_taxctg_dtls jiitd,
jai_inv_itm_taxctg_hdrs jiith
WHERE 1 = 1
AND jiitd.inventory_item_id = i.item_id
AND jiitd.tax_category_list = jiith.tax_category_list;
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
(‘Error : GST Tax Category is not attached to the Item’);
DBMS_OUTPUT.put_line (‘Error : Item ID :’ || i.item_id);
v_return_msg :=
v_return_msg
|| ‘ GST Tax Category’
|| ‘IRISO Inter GST’
|| ‘not attached to the Item.’
|| i.item_id;
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘GST Tax Category is not attached to the Item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** Category EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Error retrieving Tax Category for this item.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
v_tax_category_id := NULL;
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘Item No : ‘
|| v_item_no
|| CHR (13)
|| ‘ v_tax_category_id : ‘
|| v_tax_category_id
|| CHR (13)
|| ‘007’
);
IF v_tax_category_id IS NOT NULL
THEN
BEGIN
SELECT item_type, segment1
INTO v_item_type, v_item_no
FROM mtl_system_items_b
WHERE inventory_item_id = i.item_id
AND organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** item_type EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| ‘ Item type : ‘
|| v_item_type
|| CHR (13)
|| ‘007’
);
BEGIN
SELECT attribute_value
INTO v_rec_val
FROM jai_item_templ_hdr_v jih, jai_item_templ_dtls_v jid
WHERE 1 = 1
AND jih.template_hdr_id = jid.template_hdr_id
AND jid.attribute_code = ‘RECOVERABLE’
AND jih.inventory_item_id = i.item_id
AND jih.organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** attribute_value EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘v_rec_val : ‘
|| v_rec_val
|| CHR (13)
|| ‘008’
);
IF v_item_type IS NOT NULL
THEN
BEGIN
UPDATE jai_tax_det_factors
SET override_tax_category_id = v_tax_category_id,
user_modified_flag = ‘Y’,
intended_use =
DECODE (v_rec_val,
‘Y’, ‘RECOVERABLE’,
‘NON RECOVERABLE’
)
–sac_code_id = v_template_id
WHERE trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** UPDATE jai_tax_det_factors EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
END IF;
END IF;
BEGIN
INSERT INTO jai_tax_lines
(tax_line_id, det_factor_id, org_id,
organization_id, location_id, application_id,
entity_code, event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–frogen_flag,
party_type, party_id,
party_site_id, trx_uom, item_id, unit_price,
line_amt, trx_line_quantity, trx_currency_code,
tax_currency_code, functional_currency_code,
tax_regime_id, tax_regime_code, tax_regime_type,
first_party_reg_id, first_party_primary_reg_name,
first_party_primary_reg_num, reporting_only_flag,
tax_authority_id, tax_authority_site_id,
tax_type_id, abatement_flag,
update_vendor_on_transaction, offset_flag,
recoverable_flag, self_assessed_flag,
tax_point_basis, tax_rate_id, tax_rate_code,
tax_rate_type, recovery_percentage,
inclusive_flag, tax_rate_percentage,
actual_tax_rate, tax_rounded_to,
tax_rounding_factor, taxable_rounding_factor,
unround_taxable_amt_trx_curr,
unround_taxable_amt_tax_curr,
unround_taxable_amt_fun_curr,
unround_tax_amt_trx_curr,
unround_tax_amt_fun_curr,
rounded_tax_amt_trx_curr,
rounded_tax_amt_fun_curr,
rounded_taxable_amt_trx_curr,
rounded_taxable_amt_tax_curr,
rounded_taxable_amt_fun_curr,
rec_tax_amt_trx_curr, rec_tax_amt_funcl_curr,
nrec_tax_amt_trx_curr, nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr, manual_overridden_flag,
manual_tax_line_flag, tax_line_num, precedence_1,
record_type_code, creation_date, created_by,
last_update_date, last_updated_by, taxable_basis,
rounded_tax_amt_tax_curr, rec_tax_amt_tax_curr)
SELECT jai_tax_lines_s.NEXTVAL tax_line_id, det_factor_id,
org_id, organization_id, location_id,
v_appl_id application_id, entity_code,
event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
1 ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–‘n’ frogen_flag,
party_type, party_id, party_site_id,
trx_uom_code, item_id, unit_price, line_amt,
trx_line_quantity, trx_currency_code,
trx_currency_code tax_currency_code,
trx_currency_code functional_currency_code,
v_regime_id tax_regime_id,
v_regime_code tax_regime_code,
v_regime_type tax_regime_type,
(SELECT party_reg_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_reg_id,
v_regime_code first_party_primary_reg_name,
(SELECT registration_number
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_primary_reg_num,
‘N’ reporting_only_flag,
(SELECT tax_authority_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_id,
(SELECT tax_authority_site_id
FROM jai_party_reg_lines
WHERE regime_id =
v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_site_id,
jtc.tax_type_id tax_type_id, ‘N’ abatement_flag,
‘N’ update_vendor_on_transaction, ‘Y’ offset_flag,
— ‘N’ recoverable_flag,
(SELECT attribute_value
FROM jai_item_templ_hdr_v jitmh,
jai_item_templ_dtls_v jitml
WHERE 1 = 1
AND jitmh.template_hdr_id = jitml.template_hdr_id
AND jitmh.inventory_item_id = jts.item_id
AND jitmh.organization_id = jts.organization_id
AND attribute_code = ‘RECOVERABLE’
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id)
recoverable_flag,
— changed by vijay to include recoverable flag in tax
‘N’ self_assessed_flag, ‘DELIVERY’ tax_point_basis,
jtc.tax_rate_id tax_rate_id,
(SELECT tax_rate_code
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_code,
(SELECT tax_rate_type
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_type,
(SELECT recovery_percentage
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
recovery_percentage,
‘N’ inclusive_flag,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
tax_rate_percentage,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
actual_tax_rate,
‘ND’ tax_rounded_to, 2 tax_rounding_factor,
2 taxable_rounding_factor,
line_amt unround_taxable_amt_trx_curr,
line_amt unround_taxable_amt_tax_curr,
line_amt unround_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_fun_curr,
line_amt rounded_taxable_amt_trx_curr,
line_amt rounded_taxable_amt_tax_curr,
line_amt rounded_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_funcl_curr,
0 nrec_tax_amt_trx_curr, 0 nrec_tax_amt_tax_curr,
0 nrec_tax_amt_funcl_curr,
‘N’ manual_overridden_flag,
‘N’ manual_tax_line_flag,
jtc.line_number tax_line_num, 0 precedence_1,
(SELECT record_type_code
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
record_type_code,
SYSDATE creation_date, 0 created_by,
SYSDATE last_update_date, 0 last_updated_by,
0 taxable_basis,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_tax_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_tax_curr
FROM jai_tax_det_factors jts, jai_tax_category_lines jtc
WHERE 1 = 1
AND jtc.tax_category_id(+) = jts.override_tax_category_id
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
DBMS_OUTPUT.put_line ( ‘rowcount ‘
|| SQL%ROWCOUNT
|| CHR (13)
|| ‘009’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg = err_msg || ‘ Tax Line Attached.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Tax Category is not atached for this item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
DBMS_OUTPUT.put_line
( ‘**** Insert jai_tax_lines EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
ELSIF v_source_gst = v_dest_gst
THEN
DBMS_OUTPUT.put_line ( ‘ if GST condition : true ‘
|| CHR (13)
|| ‘0007’
);
BEGIN
SELECT tax_category_id
INTO v_tax_category_id
FROM jai_tax_categories
WHERE tax_category_name = ‘Within State GST 0%’
AND org_id = 82;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** Zero Tax Category EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
— UPDATE xx_iriso_stg_tab rec
— SET err_msg =
— err_msg
— || ‘Error retrieving Tax Category for this item.’
— WHERE req_header_id = i.requisition_header_id
— AND inventory_item_id = i.item_id
— — AND batch_run_id = p_batch_run_id
— AND status = ‘PR’;
v_tax_category_id := NULL;
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘i.source_organization_id : ‘
|| i.source_organization_id
|| CHR (13)
|| ‘Item No : ‘
|| v_item_no
|| CHR (13)
|| ‘ v_tax_category_id : ‘
|| v_tax_category_id
|| CHR (13)
|| ‘007’
);
IF v_tax_category_id IS NOT NULL
THEN
BEGIN
SELECT item_type, segment1
INTO v_item_type, v_item_no
FROM mtl_system_items_b
WHERE inventory_item_id = i.item_id
AND organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘**** item_type EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| ‘ Item type : ‘
|| v_item_type
|| CHR (13)
|| ‘007’
);
BEGIN
SELECT attribute_value
INTO v_rec_val
FROM jai_item_templ_hdr_v jih, jai_item_templ_dtls_v jid
WHERE 1 = 1
AND jih.template_hdr_id = jid.template_hdr_id
AND jid.attribute_code = ‘RECOVERABLE’
AND jih.inventory_item_id = i.item_id
AND jih.organization_id = i.destination_organization_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** attribute_value EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
DBMS_OUTPUT.put_line ( ‘ i.item_id : ‘
|| i.item_id
|| CHR (13)
|| ‘ i.destination_organization_id : ‘
|| i.destination_organization_id
|| CHR (13)
|| ‘v_rec_val : ‘
|| v_rec_val
|| CHR (13)
|| ‘008’
);
IF v_item_type IS NOT NULL
THEN
BEGIN
UPDATE jai_tax_det_factors
SET override_tax_category_id = v_tax_category_id,
user_modified_flag = ‘Y’,
intended_use =
DECODE (v_rec_val,
‘Y’, ‘RECOVERABLE’,
‘NON RECOVERABLE’
)
–sac_code_id = v_template_id
WHERE trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘**** UPDATE jai_tax_det_factors EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
END IF;
END IF;
BEGIN
INSERT INTO jai_tax_lines
(tax_line_id, det_factor_id, org_id,
organization_id, location_id, application_id,
entity_code, event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–frogen_flag,
party_type, party_id,
party_site_id, trx_uom, item_id, unit_price,
line_amt, trx_line_quantity, trx_currency_code,
tax_currency_code, functional_currency_code,
tax_regime_id, tax_regime_code, tax_regime_type,
first_party_reg_id, first_party_primary_reg_name,
first_party_primary_reg_num, reporting_only_flag,
tax_authority_id, tax_authority_site_id,
tax_type_id, abatement_flag,
update_vendor_on_transaction, offset_flag,
recoverable_flag, self_assessed_flag,
tax_point_basis, tax_rate_id, tax_rate_code,
tax_rate_type, recovery_percentage,
inclusive_flag, tax_rate_percentage,
actual_tax_rate, tax_rounded_to,
tax_rounding_factor, taxable_rounding_factor,
unround_taxable_amt_trx_curr,
unround_taxable_amt_tax_curr,
unround_taxable_amt_fun_curr,
unround_tax_amt_trx_curr,
unround_tax_amt_fun_curr,
rounded_tax_amt_trx_curr,
rounded_tax_amt_fun_curr,
rounded_taxable_amt_trx_curr,
rounded_taxable_amt_tax_curr,
rounded_taxable_amt_fun_curr,
rec_tax_amt_trx_curr, rec_tax_amt_funcl_curr,
nrec_tax_amt_trx_curr, nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr, manual_overridden_flag,
manual_tax_line_flag, tax_line_num, precedence_1,
record_type_code, creation_date, created_by,
last_update_date, last_updated_by, taxable_basis,
rounded_tax_amt_tax_curr, rec_tax_amt_tax_curr)
SELECT jai_tax_lines_s.NEXTVAL tax_line_id, det_factor_id,
org_id, organization_id, location_id,
v_appl_id application_id, entity_code,
event_class_code, event_type_code,
tax_event_class_code, tax_event_type_code,
1 ledger_id, trx_id, trx_line_id, trx_level_type,
trx_number, trx_line_number, trx_type, trx_date,
legal_entity_id,
–‘n’ frogen_flag,
party_type, party_id, party_site_id,
trx_uom_code, item_id, unit_price, line_amt,
trx_line_quantity, trx_currency_code,
trx_currency_code tax_currency_code,
trx_currency_code functional_currency_code,
v_regime_id tax_regime_id,
v_regime_code tax_regime_code,
v_regime_type tax_regime_type,
(SELECT party_reg_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_reg_id,
v_regime_code first_party_primary_reg_name,
(SELECT registration_number
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
first_party_primary_reg_num,
‘N’ reporting_only_flag,
(SELECT tax_authority_id
FROM jai_party_reg_lines
WHERE regime_id = v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_id,
(SELECT tax_authority_site_id
FROM jai_party_reg_lines
WHERE regime_id =
v_regime_id
AND party_reg_id IN (
SELECT party_reg_id
FROM jai_party_regs
WHERE party_id = organization_id
AND party_type_code = ‘IO’))
tax_authority_site_id,
jtc.tax_type_id tax_type_id, ‘N’ abatement_flag,
‘N’ update_vendor_on_transaction, ‘Y’ offset_flag,
— ‘N’ recoverable_flag,
(SELECT attribute_value
FROM jai_item_templ_hdr_v jitmh,
jai_item_templ_dtls_v jitml
WHERE 1 = 1
AND jitmh.template_hdr_id = jitml.template_hdr_id
AND jitmh.inventory_item_id = jts.item_id
AND jitmh.organization_id = jts.organization_id
AND attribute_code = ‘RECOVERABLE’
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id)
recoverable_flag,
— changed by vijay to include recoverable flag in tax
‘N’ self_assessed_flag, ‘DELIVERY’ tax_point_basis,
jtc.tax_rate_id tax_rate_id,
(SELECT tax_rate_code
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_code,
(SELECT tax_rate_type
FROM jai_tax_rates
WHERE tax_rate_id = jtc.tax_rate_id)
tax_rate_type,
(SELECT recovery_percentage
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
recovery_percentage,
‘N’ inclusive_flag,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
tax_rate_percentage,
(SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id =
jtc.tax_rate_id)
actual_tax_rate,
‘ND’ tax_rounded_to, 2 tax_rounding_factor,
2 taxable_rounding_factor,
line_amt unround_taxable_amt_trx_curr,
line_amt unround_taxable_amt_tax_curr,
line_amt unround_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 unround_tax_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_fun_curr,
line_amt rounded_taxable_amt_trx_curr,
line_amt rounded_taxable_amt_tax_curr,
line_amt rounded_taxable_amt_fun_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_trx_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_funcl_curr,
0 nrec_tax_amt_trx_curr, 0 nrec_tax_amt_tax_curr,
0 nrec_tax_amt_funcl_curr,
‘N’ manual_overridden_flag,
‘N’ manual_tax_line_flag,
jtc.line_number tax_line_num, 0 precedence_1,
(SELECT record_type_code
FROM jai_tax_rates
WHERE tax_rate_id =
jtc.tax_rate_id)
record_type_code,
SYSDATE creation_date, 0 created_by,
SYSDATE last_update_date, 0 last_updated_by,
0 taxable_basis,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rounded_tax_amt_tax_curr,
(line_amt * (SELECT tax_rate_percentage
FROM jai_tax_rate_details
WHERE tax_rate_id = jtc.tax_rate_id)
)
/ 100 rec_tax_amt_tax_curr
FROM jai_tax_det_factors jts, jai_tax_category_lines jtc
WHERE 1 = 1
AND jtc.tax_category_id(+) = jts.override_tax_category_id
AND trx_id = i.requisition_header_id
AND trx_line_id = i.requisition_line_id;
DBMS_OUTPUT.put_line ( ‘rowcount ‘
|| SQL%ROWCOUNT
|| CHR (13)
|| ‘009’
);
UPDATE xx_iriso_stg_tab rec
SET err_msg = err_msg || ‘ Tax Line Attached.’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
UPDATE xx_iriso_stg_tab rec
SET err_msg =
err_msg
|| ‘Tax Category is not atached for this item’
WHERE req_header_id = i.requisition_header_id
AND inventory_item_id = i.item_id
— AND batch_run_id = p_batch_run_id
AND status = ‘PR’;
DBMS_OUTPUT.put_line
( ‘**** Insert jai_tax_lines EXCPETION : ‘
|| l_api_name
|| SUBSTR (SQLERRM, 1, 3500)
|| ‘ Error raised in: ‘
|| $$plsql_unit
|| ‘ at line ‘
|| $$plsql_line
|| ‘ ****’
);
END;
/* ELSE
v_return_msg := ‘No Record inserted.’; */
END IF;
END LOOP;
DBMS_OUTPUT.put_line (‘v_return_msg ‘ || v_return_msg);
/*
IF TRIM (v_return_msg) IS NULL
THEN
v_return_msg := ‘Tax Line Attached. ‘;
END IF;
*/
RETURN v_return_msg;
END;
