CREATE OR REPLACE PROCEDURE_iso_gst_tax_prc (p_trx_number 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, jtd.trx_id, jtd.trx_line_id,
DECODE (SUBSTR (DECODE (organization_code,
’05’, ‘111’,
’08’, ‘222’,
organization_code
),
-1,
1
),
‘1’, ‘IRISO Inter GST’,
‘2’, ‘IRISO Inter GST-1’,
‘3’, ‘IRISO Inter GST-2’,
‘5’, ‘IRISO Inter GST-3’,
‘9’, ‘IRISO Inter GST-4’
) tax_cat_list
FROM po_requisition_lines_all prla,
jai_tax_det_factors jtd,
org_organization_definitions ood,
po_requisition_headers_all prha
WHERE 1 = 1
AND prha.requisition_header_id = prla.requisition_header_id
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 prha.segment1 = p_trx_number
AND type_lookup_code = ‘INTERNAL’;
v_source_gst VARCHAR2 (250) := NULL;
v_dest_gst VARCHAR2 (250) := NULL;
v_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) := ‘ISO_gst_tax’;
l_progress VARCHAR2 (240) := ‘001’;
lc_error_message LONG := NULL;
lx_status VARCHAR2 (240) := NULL;
lx_error_message LONG := NULL;
v_third_party_reg_id NUMBER;
v_third_party_primary_reg_name VARCHAR2 (300);
v_third_party_primary_reg_num VARCHAR2 (300);
v_ref_doc_entity_code VARCHAR2 (300);
v_ref_doc_event_class_code VARCHAR2 (300);
v_ref_doc_trx_id NUMBER;
v_ref_doc_trx_type VARCHAR2 (300);
v_ref_doc_line_id NUMBER;
v_ref_doc_trx_level_type VARCHAR2 (300);
v_trx_loc_line_id NUMBER;
BEGIN
DBMS_OUTPUT.put_line (l_api_name || ‘Started : ‘ || l_progress);
DBMS_OUTPUT.put_line (‘p_trx_no : ‘ || p_trx_number || 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
NULL;
END;
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
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;
WHEN OTHERS
THEN
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
NULL;
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
NULL;
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
NULL;
END;
END IF;
END IF;
BEGIN
DBMS_OUTPUT.put_line
(‘v_return_msg :’ || v_return_msg);
IF v_return_msg IS NULL
THEN
DBMS_OUTPUT.put_line
(‘Insert : GST Insert’);
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,
third_party_reg_id, third_party_primary_reg_name,
third_party_primary_reg_num, ref_doc_entity_code,
ref_doc_event_class_code, ref_doc_trx_id,
ref_doc_trx_type, ref_doc_line_id,
ref_doc_trx_level_type, trx_loc_line_id)
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,
v_third_party_reg_id, v_third_party_primary_reg_name,
v_third_party_primary_reg_num, v_ref_doc_entity_code,
v_ref_doc_event_class_code, v_ref_doc_trx_id,
v_ref_doc_trx_type, v_ref_doc_line_id,
v_ref_doc_trx_level_type, v_trx_loc_line_id
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 jts.item_id = i.item_id
AND jts.override_tax_category_id = v_tax_category_id
AND trx_line_id = i.requisition_line_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
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
|| ‘ ****’
);
lx_error_message :=
lx_error_message
|| ‘ Requisition Tax Insert having issue Pleease validate manually’;
END;
END IF;
v_return_msg:=null;
END LOOP;
DBMS_OUTPUT.put_line (‘v_return_msg ‘ || v_return_msg);
END;
