APPS.ZX_TCM_BES_REGISTRATION_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 5

PROCEDURE UPDATE_SUPPLIERS(
 P_PARTY_ID IN NUMBER,
 P_AMOUNT_INCLUDES_TAX_FLAG IN VARCHAR2,
 P_AP_TAX_ROUNDING_RULE IN VARCHAR2,
 P_AUTO_TAX_CALC_FLAG IN VARCHAR2,
 P_OFFSET_TAX_FLAG IN VARCHAR2,
 P_VAT_CODE IN VARCHAR2,
 P_VAT_REGISTRATION_NUM IN VARCHAR2,
 P_WITHHOLDING_START_DATE IN DATE,
 P_WITHHOLDING_STATUS_CODE IN VARCHAR2)
 IS

 BEGIN

   UPDATE AP_SUPPLIERS
   SET AMOUNT_INCLUDES_TAX_FLAG = P_AMOUNT_INCLUDES_TAX_FLAG,
       AP_TAX_ROUNDING_RULE = P_AP_TAX_ROUNDING_RULE,
       AUTO_TAX_CALC_FLAG = P_AUTO_TAX_CALC_FLAG,
       OFFSET_TAX_FLAG    = P_OFFSET_TAX_FLAG,
       VAT_CODE = P_VAT_CODE,
       VAT_REGISTRATION_NUM = P_VAT_REGISTRATION_NUM,
       WITHHOLDING_START_DATE = P_WITHHOLDING_START_DATE,
       WITHHOLDING_STATUS_LOOKUP_CODE = P_WITHHOLDING_STATUS_CODE
   WHERE PARTY_ID = P_PARTY_ID;

Line: 33

PROCEDURE UPDATE_SUPPLIER_SITES(
 P_PARTY_SITE_ID IN NUMBER,
 P_AMOUNT_INCLUDES_TAX_FLAG IN VARCHAR2,
 P_AP_TAX_ROUNDING_RULE IN VARCHAR2,
 P_AUTO_TAX_CALC_FLAG IN VARCHAR2,
 P_OFFSET_TAX_FLAG IN VARCHAR2,
 P_VAT_CODE IN VARCHAR2,
 P_VAT_REGISTRATION_NUM IN VARCHAR2)
 IS
 BEGIN

   UPDATE ap_supplier_sites_all
   SET AMOUNT_INCLUDES_TAX_FLAG = P_AMOUNT_INCLUDES_TAX_FLAG,
       AP_TAX_ROUNDING_RULE = P_AP_TAX_ROUNDING_RULE,
       AUTO_TAX_CALC_FLAG = P_AUTO_TAX_CALC_FLAG,
       OFFSET_TAX_FLAG    = P_OFFSET_TAX_FLAG,
       VAT_CODE = P_VAT_CODE,
       VAT_REGISTRATION_NUM = P_VAT_REGISTRATION_NUM
	WHERE PARTY_SITE_ID = P_PARTY_SITE_ID;

Line: 88

	      (SELECT
	      decode (party.party_type, 'ORGANIZATION', 'THIRD_PARTY', 'PERSON', 'THIRD_PARTY') PARTY_TYPE_CODE,
	      L_TAX_CODE TAX_CLASSIFICATION_CODE,
	      L_PARTY_ID PARTY_ID,
	      FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
	      L_TAX_HEADER_LEVEL_FLAG ROUNDING_LEVEL_CODE,
	      L_TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
	      party.tax_reference TAX_REFERENCE,
	      SYSDATE CREATION_DATE,
	      FND_GLOBAL.User_ID CREATED_BY,
	      SYSDATE LAST_UPDATE_DATE,
	      FND_GLOBAL.User_ID LAST_UPDATED_BY,
	      FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
	      FROM HZ_PARTIES party
		  WHERE PARTY_ID = l_party_id) PTY
          ON (PTY.PARTY_ID = PTP.PARTY_ID)
          WHEN MATCHED THEN UPDATE SET
            PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
            PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
	        PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
	        PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
	        PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
          WHEN NOT MATCHED THEN
  	        INSERT (
	        PARTY_TYPE_CODE,
  	        TAX_CLASSIFICATION_CODE,
	        PARTY_TAX_PROFILE_ID,
	        PARTY_ID,
	        PROGRAM_LOGIN_ID,
	        ROUNDING_LEVEL_CODE,
	        ROUNDING_RULE_CODE,
	        REP_REGISTRATION_NUMBER,
	        CREATION_DATE,
	        CREATED_BY,
	        LAST_UPDATE_DATE,
	        LAST_UPDATED_BY,
	        LAST_UPDATE_LOGIN,
			OBJECT_VERSION_NUMBER)
	        VALUES (
	        PTY.PARTY_TYPE_CODE,
  	        PTY.TAX_CLASSIFICATION_CODE,
	        ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
	        PTY.PARTY_ID,
	        PTY.PROGRAM_LOGIN_ID,
	        PTY.ROUNDING_LEVEL_CODE,
	        PTY.ROUNDING_RULE_CODE,
	        PTY.TAX_REFERENCE,
	        PTY.CREATION_DATE,
	        PTY.CREATED_BY,
	        PTY.LAST_UPDATE_DATE,
	        PTY.LAST_UPDATED_BY,
	        PTY.LAST_UPDATE_LOGIN,
			1);

Line: 180

	      (SELECT
	      'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
	      L_TAX_CODE TAX_CLASSIFICATION_CODE,
	      L_PARTY_SITE_ID PARTY_SITE_ID,
	      FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
	      L_TAX_HEADER_LEVEL_FLAG ROUNDING_LEVEL_CODE,
	      L_TAX_ROUNDING_RULE ROUNDING_RULE_CODE,
	      NULL TAX_REFERENCE,
	      SYSDATE CREATION_DATE,
	      FND_GLOBAL.User_ID CREATED_BY,
	      SYSDATE LAST_UPDATE_DATE,
	      FND_GLOBAL.User_ID LAST_UPDATED_BY,
	      FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
		  FROM HZ_PARTY_SITES party
		  WHERE PARTY_SITE_ID = l_party_site_id) PTY
          ON (PTY.PARTY_SITE_ID = PTP.PARTY_ID)
          WHEN MATCHED THEN UPDATE SET
            PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
            PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
	        PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
	        PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
	        PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
          WHEN NOT MATCHED THEN
  	        INSERT (
	        PARTY_TYPE_CODE,
  	        TAX_CLASSIFICATION_CODE,
	        PARTY_TAX_PROFILE_ID,
	        PARTY_ID,
	        PROGRAM_LOGIN_ID,
	        ROUNDING_LEVEL_CODE,
	        ROUNDING_RULE_CODE,
	        REP_REGISTRATION_NUMBER,
	        CREATION_DATE,
	        CREATED_BY,
	        LAST_UPDATE_DATE,
	        LAST_UPDATED_BY,
	        LAST_UPDATE_LOGIN,
            OBJECT_VERSION_NUMBER)
	        VALUES (
	        PTY.PARTY_TYPE_CODE,
  	        PTY.TAX_CLASSIFICATION_CODE,
	        ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
	        PTY.PARTY_SITE_ID,
	        PTY.PROGRAM_LOGIN_ID,
	        PTY.ROUNDING_LEVEL_CODE,
	        PTY.ROUNDING_RULE_CODE,
	        PTY.TAX_REFERENCE,
	        PTY.CREATION_DATE,
	        PTY.CREATED_BY,
	        PTY.LAST_UPDATE_DATE,
	        PTY.LAST_UPDATED_BY,
	        PTY.LAST_UPDATE_LOGIN,
			1);

Line: 252

	      (SELECT
	      decode (party.party_type, 'ORGANIZATION', 'THIRD_PARTY', 'PERSON', 'THIRD_PARTY') PARTY_TYPE_CODE,
	      NULL TAX_CLASSIFICATION_CODE,
	      party.party_id PARTY_ID,
	      FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
	      NULL ROUNDING_LEVEL_CODE,
	      NULL ROUNDING_RULE_CODE,
	      party.tax_reference TAX_REFERENCE,
	      SYSDATE CREATION_DATE,
	      FND_GLOBAL.User_ID CREATED_BY,
	      SYSDATE LAST_UPDATE_DATE,
	      FND_GLOBAL.User_ID LAST_UPDATED_BY,
	      FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
	      FROM HZ_PARTIES party
		  WHERE party.request_id = p_request_id
		  AND (party.party_type ='ORGANIZATION' OR party.party_type ='PERSON')) PTY
          ON (PTY.PARTY_ID = PTP.PARTY_ID)
          WHEN MATCHED THEN UPDATE SET
            PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
            PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
	        PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
	        PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
	        PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
          WHEN NOT MATCHED THEN
  	        INSERT (
	        PARTY_TYPE_CODE,
  	        TAX_CLASSIFICATION_CODE,
	        PARTY_TAX_PROFILE_ID,
	        PARTY_ID,
	        PROGRAM_LOGIN_ID,
	        ROUNDING_LEVEL_CODE,
	        ROUNDING_RULE_CODE,
	        REP_REGISTRATION_NUMBER,
	        CREATION_DATE,
	        CREATED_BY,
	        LAST_UPDATE_DATE,
	        LAST_UPDATED_BY,
	        LAST_UPDATE_LOGIN,
			OBJECT_VERSION_NUMBER)
	        VALUES (
	        PTY.PARTY_TYPE_CODE,
  	        PTY.TAX_CLASSIFICATION_CODE,
	        ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
	        PTY.PARTY_ID,
	        PTY.PROGRAM_LOGIN_ID,
	        PTY.ROUNDING_LEVEL_CODE,
	        PTY.ROUNDING_RULE_CODE,
	        PTY.TAX_REFERENCE,
	        PTY.CREATION_DATE,
	        PTY.CREATED_BY,
	        PTY.LAST_UPDATE_DATE,
	        PTY.LAST_UPDATED_BY,
	        PTY.LAST_UPDATE_LOGIN,
			1);

Line: 310

	      (SELECT 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
	      NULL TAX_CLASSIFICATION_CODE,
	      party.party_site_id PARTY_ID,
	      FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
	      NULL ROUNDING_LEVEL_CODE,
	      NULL ROUNDING_RULE_CODE,
	      NULL TAX_REFERENCE,
	      SYSDATE CREATION_DATE,
	      FND_GLOBAL.User_ID CREATED_BY,
	      SYSDATE LAST_UPDATE_DATE,
	      FND_GLOBAL.User_ID LAST_UPDATED_BY,
	      FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
	      FROM HZ_PARTY_SITES party
		  WHERE party.request_id = p_request_id ) PTY
          ON (PTY.PARTY_ID = PTP.PARTY_ID)
          WHEN MATCHED THEN UPDATE SET
            PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
	        PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
	        PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
	        PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
          WHEN NOT MATCHED THEN
  	        INSERT (
	        PARTY_TYPE_CODE,
  	        TAX_CLASSIFICATION_CODE,
	        PARTY_TAX_PROFILE_ID,
	        PARTY_ID,
	        PROGRAM_LOGIN_ID,
	        ROUNDING_LEVEL_CODE,
	        ROUNDING_RULE_CODE,
	        REP_REGISTRATION_NUMBER,
	        CREATION_DATE,
	        CREATED_BY,
	        LAST_UPDATE_DATE,
	        LAST_UPDATED_BY,
	        LAST_UPDATE_LOGIN,
			OBJECT_VERSION_NUMBER)
	        VALUES (
	        PTY.PARTY_TYPE_CODE,
  	        PTY.TAX_CLASSIFICATION_CODE,
	        ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
	        PTY.PARTY_ID,
	        PTY.PROGRAM_LOGIN_ID,
	        PTY.ROUNDING_LEVEL_CODE,
	        PTY.ROUNDING_RULE_CODE,
	        PTY.TAX_REFERENCE,
	        PTY.CREATION_DATE,
	        PTY.CREATED_BY,
	        PTY.LAST_UPDATE_DATE,
	        PTY.LAST_UPDATED_BY,
	        PTY.LAST_UPDATE_LOGIN,
			1);

Line: 380

	      (SELECT 'ZX_PARTY_TAX_PROFILE' OWNER_TABLE_NAME,
	              PARTY_TAX_PROFILE_ID OWNER_TABLE_ID,
	              'VENDOR TYPE' CLASS_CATEGORY,
	              OBJECT_VERSION_NUMBER
             FROM ZX_PARTY_TAX_PROFILE
		 WHERE Party_id = p_party_id
		 AND   PARTY_TYPE_CODE ='THIRD_PARTY' ) PTP
          ON (HZ.OWNER_TABLE_ID = PTP.OWNER_TABLE_ID
              AND HZ.OWNER_TABLE_NAME = PTP.OWNER_TABLE_NAME
              AND HZ.CLASS_CATEGORY=PTP.CLASS_CATEGORY)
          WHEN MATCHED THEN UPDATE SET
              HZ.CLASS_CODE = P_CLASS_CODE,
              HZ.LAST_UPDATE_DATE=SYSDATE,
	        HZ.LAST_UPDATED_BY= FND_GLOBAL.User_ID,
	        HZ.LAST_UPDATE_LOGIN=FND_GLOBAL.Login_ID,
	        HZ.OBJECT_VERSION_NUMBER = HZ.OBJECT_VERSION_NUMBER +1
          WHEN NOT MATCHED THEN
  	        INSERT (
               CODE_ASSIGNMENT_ID,
               OWNER_TABLE_NAME,
               OWNER_TABLE_ID,
               CLASS_CATEGORY,
               CLASS_CODE,
               PRIMARY_FLAG,
               CONTENT_SOURCE_TYPE,
               ACTUAL_CONTENT_SOURCE,
               IMPORTANCE_RANKING,
               START_DATE_ACTIVE,
               END_DATE_ACTIVE,
               CREATED_BY,
               CREATION_DATE,
               LAST_UPDATE_LOGIN,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY ,
               STATUS ,
               OBJECT_VERSION_NUMBER,
               CREATED_BY_MODULE,
               APPLICATION_ID,
               RANK,
               REQUEST_ID,
               PROGRAM_APPLICATION_ID,
               PROGRAM_ID
               )
               VALUES(
               HZ_CODE_ASSIGNMENTS_S.nextval,
               PTP.OWNER_TABLE_NAME ,
               PTP.OWNER_TABLE_ID,
               PTP.CLASS_CATEGORY  ,
               P_CLASS_CODE,
               'N' ,
               'USER_ENTERED',
               'USER_ENTERED',
               null,
               sysdate,
               null,
               FND_GLOBAL.User_ID ,
               SYSDATE ,
               FND_GLOBAL.Login_ID,
               SYSDATE ,
               FND_GLOBAL.User_ID,
               NULL ,
               1,
               'EBTAX',
               235,
               null ,
               null,
               fnd_global.PROG_APPL_ID ,
               fnd_global.CONC_PROGRAM_ID);