Script to update GST information in Oracle Apps India Localization screens .
--> First create a dummy table (xxx_vendor_gst_codes ) and insert all supplier and GST information in this table.
--> Below script is used to insert the data directly in setup table jai_party_regs ,jai_party_reg_lines
DECLARE
l_count NUMBER := NULL ;
l_vendor_id NUMBER := NULL ;
l_vendor_site_id NUMBER := NULL ;
l_party_reg_id NUMBER := NULL ;
L_ORG_ID NUMBER := NULL ;
l_party_reg_id_value NUMBER := NULL ;
CURSOR c1 is
SELECT distinct vendor_id , vendor_site_id , org_id , GST_NUMBER FROM xxx.xxx_vendor_gst_codes;
BEGIN
FOR I in c1 loop
SELECT count(1)
INTO l_count
FROM jai_party_regs
WHERE party_id = i.vendor_id
AND party_site_id = i.vendor_site_id
AND org_id = i.ORG_ID ;
IF l_count = 0 THEN
BEGIN
SELECT jai_party_regs_s.NEXTVAL
INTO l_party_reg_id
FROM DUAL ;
INSERT INTO jai_party_regs
(party_reg_id,
party_type_code,
SUPPLIER_FLAG ,
CUSTOMER_FLAG,
party_id,
party_site_id,
org_id,
org_classification_code,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by,
record_type_code
)
VALUES (l_party_reg_id,
'THIRD_PARTY_SITE',
'Y',
'N',
i.vendor_id ,
i.vendor_site_id,
i.org_id,
'',
SYSDATE,
5562,
SYSDATE,
-1,
5562,
'USER DEFINED' );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error while inserting in jai_party_regs');
END ;
END IF ;
COMMIT ;
BEGIN
SELECT party_reg_id
INTO l_party_reg_id_value
FROM jai_party_regs
WHERE party_id = i.vendor_id
AND party_site_id = i.vendor_site_id
AND org_id = i.ORG_ID ;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END ;
IF l_party_reg_id_value IS NOT NULL THEN
NULL ;
BEGIN
INSERT INTO jai_party_reg_lines
(party_reg_id,
party_reg_line_id,
line_context,
regime_id,
registration_type_code,
registration_number,
effective_from,
effective_to,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by,
record_type_code
)
VALUES (l_party_reg_id_value,
jai_party_reg_lines_s.NEXTVAL ,
'REGISTRATIONS',
10000,
'GST' ,
i.GST_NUMBER,
'02-JAN-2017',
'',
SYSDATE,
5562,
SYSDATE,
-1,
5562,
'USER DEFINED' );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error while inserting in jai_party_reg_lines');
END ;
END IF ;
END LOOP ;
END ;
No comments:
Post a Comment