PL/SQL Anonymous Block to update Customer GST number:
- As we do not have any Standard API / Process to update the GST number in Oracle, the only way to correct the existing records is to do a manual update.
- Oracle Note ID: GST – How to Update the Third Party Registration Number? (Doc ID 2296301.1)
- If needed we can end date the existing line and create a new line with a new value for the customer but we want to update the existing line it can be achieved via the below Script.
Note: I have created a custom table XXX_JAI_PARTY_REG_LINES_STG with the below columns to store the information of customers with new and Old GST numbers.
Table Creation Script :
create table XXX_JAI_PARTY_REG_LINES_STG
(CUSTOMER_NAME varchar2(1000)
,ACCOUNT_NUMBER varchar2(1000)
,SITE varchar2(1000)
,SITE_NUMBER varchar2(1000)
,OLD_GST_NUMBER varchar2(1000)
,NEW_GST_NUMBER varchar2(1000)
,STATUS varchar2(10)
,ATTRIBUTE1 varchar2(2000)
,ATTRIBUTE2 varchar2(2000));
Anonymous Block to update GST number:
declare
cursor c_gst_update is
select PARTY_SITE_NUMBER,hpa.PARTY_NUMBER,hca.account_number,hpa.party_name, JPRL.REGISTRATION_NUMBER,IJPRL.NEW_GST_NUMBER,
JPRL.PARTY_REG_ID , JPRL.PARTY_REG_LINE_ID
from apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcsua,
apps.hz_parties hpa,
apps.hz_party_sites hpas,
apps.jai_party_regs jpr,
apps.JAI_PARTY_REG_LINES JPRL,
apps.XXX_JAI_PARTY_REG_LINES_STG IJPRL
where 1=1
and hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hca.party_id = hpa.party_id
and hcasa.party_site_id = hpas.party_site_id
and hca.CUST_ACCOUNT_ID = jpr.party_id
and hcasa.CUST_ACCT_SITE_ID = jpr.party_site_id
and JPRL.PARTY_REG_ID = JPR.PARTY_REG_ID
and JPRL.REGISTRATION_TYPE_CODE = ‘GSTIN’
AND JPR.PARTY_TYPE_CODE IN (‘THIRD_PARTY’,’THIRD_PARTY_SITE’)
and IJPRL.account_number = hca.account_number
and IJPRL.SITE_NUMBER = hpas.PARTY_SITE_NUMBER
and IJPRL.status is null
and JPRL.EFFECTIVE_to is null;
begin
for v_gst_update in c_gst_update loop
begin
update apps.JAI_PARTY_REG_LINES set REGISTRATION_NUMBER = v_gst_update.NEW_GST_NUMBER
where PARTY_REG_ID = v_gst_update.PARTY_REG_ID
and PARTY_REG_LINE_ID = v_gst_update.PARTY_REG_LINE_ID
and EFFECTIVE_to is null;
update XXX_JAI_PARTY_REG_LINES_STG set status = ‘U’ , attribute1 = ‘Updated via Script’
where account_number = v_gst_update.account_number
and SITE_NUMBER = v_gst_update.PARTY_SITE_NUMBER
and status is null;
DBMS_OUTPUT.PUT_LINE(‘Updated the value for Customer account :’ || v_gst_update.account_number || ‘-‘ || ‘and SITE_NUMBER :’ ||v_gst_update.PARTY_SITE_NUMBER);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error while updating the Customer account :’ || v_gst_update.account_number);
END;
end loop;
end ;
