Script to update the recoverable Tax
Introduction:
Script to update the recoverable Tax.
Cause of the issue:
In PO Migration from one unit to another unit, the data of Recoverable tax are not migrated to new units. To update the same then we can use the below Script to update the recoverable Tax.
How do we solve:
The below is the Script to update the recoverable Tax in Purchase Order.
Script:
DECLARE
CURSOR c2
IS
SELECT jtl.tax_line_id, jtd.det_factor_id, intended_use, jtd.trx_id,
poh.segment1 AS ponumber, recoverable_flag,
rec_tax_amt_tax_curr, unround_tax_amt_tax_curr, jtd.org_id
FROM po_headers_all poh, jai_tax_det_factors jtd, jai_tax_lines jtl
WHERE 1 = 1
AND poh.po_header_id = jtd.trx_id
AND jtd.entity_code = ‘PURCHASE_ORDER’
AND jtl.trx_id = poh.po_header_id
AND jtl.trx_id = jtd.trx_id
AND jtd.det_factor_id = jtl.det_factor_id
AND jtd.org_id = jtl.org_id
AND jtl.entity_code = ‘PURCHASE_ORDER’
AND intended_use IS NULL
AND poh.type_lookup_code = ‘STANDARD’
AND jtd.org_id = p_org_id;
BEGIN
BEGIN
FOR j IN c2
LOOP
IF j.intended_use IS NULL
THEN
UPDATE ja.jai_tax_det_factors jtd
SET intended_use = ‘RECOVERABLE’
WHERE det_factor_id = j.det_factor_id AND jtd.org_id = j.org_id;
END IF;
IF j.recoverable_flag = ‘N’
THEN
UPDATE ja.jai_tax_lines
SET recoverable_flag = ‘Y’,
rec_tax_amt_tax_curr = j.unround_tax_amt_tax_curr
WHERE det_factor_id = j.det_factor_id
AND trx_id = j.trx_id
AND tax_line_id = j.tax_line_id
AND org_id = j.org_id;
END IF;
COMMIT;
fnd_file.put_line (fnd_file.LOG,
‘tax_line_id ‘ || j.det_factor_id || SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
‘det_factor_id ‘ || j.tax_line_id || SQLERRM
);
END LOOP;
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Error’ || SQLERRM);
END;
