API for Assigning GST Registration Number, PAN Number and State to a Customer Oracle Apps R12

Declare
CURSOR C
 IS
SELECT DISTINCT A.*,
       B.CUST_ACCOUNT_ID,
       C.CUST_ACCT_SITE_ID,
       (SELECT X.REGIME_ID
        FROM APPS.JAI_REGIMES X
        WHERE X.REGIME_NAME='INDIA GST'
        ) REGIME_ID,
       (SELECT Y.REPORTING_CODE_ID
        FROM APPS.JAI_REPORTING_TYPES X,
             APPS.JAI_REPORTING_CODES Y
        WHERE X.REPORTING_TYPE_ID=Y.REPORTING_TYPE_ID
        AND Y.REPORTING_CODE=A.STATE_CODE
        AND X.REPORTING_TYPE_NAME='JAI_TP_IN_STATE'
        AND ROWNUM=1) REPORTING_CODE_ID,
         (SELECT Y.REPORTING_CODE_DESCRIPTION
        FROM APPS.JAI_REPORTING_TYPES X,
             APPS.JAI_REPORTING_CODES Y
        WHERE X.REPORTING_TYPE_ID=Y.REPORTING_TYPE_ID
        AND Y.REPORTING_CODE=A.STATE_CODE
        AND X.REPORTING_TYPE_NAME='JAI_TP_IN_STATE'
        AND ROWNUM=1) REPORTING_CODE_DESCRIPTION,
         (SELECT Y.REPORTING_CODE
        FROM APPS.JAI_REPORTING_TYPES X,
             APPS.JAI_REPORTING_CODES Y
        WHERE X.REPORTING_TYPE_ID=Y.REPORTING_TYPE_ID
        AND Y.REPORTING_CODE=A.STATE_CODE
        AND X.REPORTING_TYPE_NAME='JAI_TP_IN_STATE'
        AND ROWNUM=1) REPORTING_CODE,
         (SELECT X.REPORTING_TYPE_ID
        FROM APPS.JAI_REPORTING_TYPES X
        WHERE X.REPORTING_TYPE_NAME='JAI_TP_IN_STATE'
        AND ROWNUM=1) REPORTING_TYPE_ID
      FROM GST_Staging_table A,
     APPS.HZ_CUST_ACCOUNTS B,
     APPS.HZ_CUST_ACCT_SITES_ALL C,
     APPS.HZ_PARTY_SITES D,
     APPS.HR_OPERATING_UNITS E
WHERE a.CUSTOMER_NUMBER=B.ACCOUNT_NUMBER
AND B.CUST_ACCOUNT_ID=C.CUST_ACCOUNT_ID
AND C.PARTY_SITE_ID=D.PARTY_SITE_ID
AND a.SITE_NUMBER=D.PARTY_SITE_NUMBER
AND a.ORG_ID=C.ORG_ID
AND C.ORG_ID=E.ORGANIZATION_ID
--AND A.CUSTOMER_NUMBER='63339'
AND NOT EXISTS (SELECT 1 FROM JA.JAI_PARTY_REGS X WHERE X.PARTY_ID=B.CUST_ACCOUNT_ID AND
X.PARTY_SITE_ID=C.CUST_ACCT_SITE_ID);

BEGIN


  FOR C1 IN C
  LOOP
     
    INSERT INTO JA.JAI_PARTY_REGS
    (
     

PARTY_REG_ID,PARTY_TYPE_CODE,SUPPLIER_FLAG,CUSTOMER_FLAG,SITE_FLAG,PARTY_SITE_ID,ORG_CLASSIFICATION_CODE,ITEM_CATEGORY_LIST
     ,INVOICE_TAX_CATEGORY_ID,ORG_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,RECORD_TYPE_CODE,PARTY_ID
    )
    VALUES
    (
      JA.JAI_PARTY_REGS_S.NEXTVAL,'THIRD_PARTY_SITE','N','Y','Y',C1.CUST_ACCT_SITE_ID,NULL,NULL,NULL
     ,C1.org_id,SYSDATE,0,SYSDATE,0,'DEFINED',C1.CUST_ACCOUNT_ID
    );
   COMMIT;

 INSERT INTO JA.JAI_PARTY_REG_LINES
 (

PARTY_REG_ID,PARTY_REG_LINE_ID,LINE_CONTEXT,REGIME_ID,REGISTRATION_TYPE_CODE,REGISTRATION_NUMBER,SEC_REGISTRATION_TYPE_CODE
  ,SECONDARY_REGISTRATION_NUMBER,NUM_OF_RETURN_DAYS,TAX_AUTHORITY_ID,TAX_AUTHORITY_SITE_ID,ASSESSABLE_PRICE_LIST_ID
  ,DEFAULT_SECTION_CODE,EXEMPTION_TYPE,EXEMPTION_NUM,INTERCOMPANY_RECEIVABLE_CCID,INTERCOMPANY_PAYABLE_CCID
  ,TRACKING_NUM,EFFECTIVE_FROM,EFFECTIVE_TO,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,RECORD_TYPE_CODE
 )
 VALUES
 (
 

JA.JAI_PARTY_REGS_S.CURRVAL,JA.JAI_PARTY_REG_LINES_S.NEXTVAL,'REGISTRATIONS',C1.REGIME_ID,'GST',C1.PRIMARY_REGISTRATIOIN_NU

MBER,'PAN',
    C1.SECONDARY_REGISTRATIOIN_NUMBER
   ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'01-JUL-2017',NULL,SYSDATE,0,SYSDATE,0,'DEFINED'
 );
COMMIT;

 IF C1.REPORTING_TYPE_ID IS NOT NULL AND C1.REPORTING_CODE_ID IS NOT NULL THEN
 Insert into JA.JAI_REPORTING_ASSOCIATIONS
 (
   REPORTING_ASSOCIATION_ID,REPORTING_TYPE_ID,REPORTING_CODE_ID,REPORTING_TYPE_NAME,REPORTING_USAGE,REPORTING_CODE_DESCRIPTION
  ,REPORTING_CODE,ENTITY_CODE,ENTITY_ID,ENTITY_SOURCE_TABLE,REGIME_ID,REGIME_CODE,EFFECTIVE_FROM,EFFECTIVE_TO,CREATION_DATE
  ,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,RECORD_TYPE_CODE,STL_HDR_ID,REPORTING_CODE_DESCRIPTION#1
 )
 values
 (
   JA.JAI_REPORTING_ASSOCIATIONS_S.NEXTVAL,C1.REPORTING_TYPE_ID,C1.REPORTING_CODE_ID,'JAI_TP_IN_STATE','TD',C1.REPORTING_CODE_DESCRIPTION
  ,C1.REPORTING_CODE,'THIRD_PARTY',JA.JAI_PARTY_REGS_S.CURRVAL,'JAI_PARTY_REGS',C1.REGIME_ID,'GST IND','01-JUL-2017',null
  ,SYSDATE,2617,SYSDATE,2617,'DEFINED',null,C1.REPORTING_CODE_DESCRIPTION--null
  );
 COMMIT;
END IF;

 COMMIT;

  END LOOP;
END;