eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSC_PLAN_ASSIGNMENT_PKG SQL Statements

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

Line: 86


					 -- no. of recs that are inserted into CSC_CUST_PLANS
G_ERRBUF		                    VARCHAR2(2000) := NULL;

Line: 109


G_DEL_IDX                          NUMBER := 0; -- index for delete tables

Line: 343


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.plan_id               = p_plan_id
   and    a.customized_plan       = 'N'
   and ( (     a.use_for_cust_account = 'N'
	   and b.cust_account_id is null  )
      OR (     a.use_for_cust_account = 'Y'
	   and b.cust_account_id  is not null ) )
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active,   sysdate );

Line: 449


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.profile_check_id      = p_check_id
   and    a.customized_plan       = 'N'
   and ( (     a.use_for_cust_account = 'N'
	   and b.cust_account_id is null  )
      OR (     a.use_for_cust_account = 'Y'
	   and b.cust_account_id  is not null ) )
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active,   sysdate );

Line: 551


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.customized_plan       = 'N'
   and    b.party_id              = p_party_id
   and    b.cust_account_id      is NULL
   and    a.use_for_cust_account  = 'N'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active,   sysdate );

Line: 650


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.customized_plan       = 'N'
   and    b.cust_account_id       = p_cust_account_id
   and    a.use_for_cust_account  = 'Y'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active,   sysdate );

Line: 752


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.plan_id               = p_plan_id
   and    a.customized_plan       = 'N'
   and    b.party_id              = p_party_id
   and    b.cust_account_id      is NULL
   and    a.use_for_cust_account  = 'N'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active, sysdate ) ;

Line: 863


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.plan_id               = p_plan_id
   and    a.customized_plan       = 'N'
   and    b.cust_account_id       = p_cust_account_id
   and    a.use_for_cust_account  = 'Y'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active, sysdate ) ;

Line: 970


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.profile_check_id      = p_check_id
   and    a.customized_plan       = 'N'
   and    b.party_id              = p_party_id
   and    b.cust_account_id       is null
   and    a.use_for_cust_account  = 'N'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active, sysdate ) ;

Line: 1079


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.profile_check_id      = p_check_id
   and    a.customized_plan       = 'N'
   and    b.cust_account_id       = p_cust_account_id
   and    a.use_for_cust_account  = 'Y'
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active, sysdate ) ;

Line: 1188


   select a.plan_id,              a.profile_check_id,     a.relational_operator,
          a.criteria_value_low,   a.criteria_value_high,  a.start_date_active,
	  a.end_date_active,      a.use_for_cust_account,
	  b.party_id,             b.cust_account_id,      b.value
   from   csc_plan_headers_b a,
	  csc_prof_check_results b
   where  a.profile_check_id      = b.check_id
   and    a.customized_plan       = 'N'
   and  (   ( a.use_for_cust_account = 'N' and
	      b.cust_account_id is null )
	 OR ( a.use_for_cust_account = 'Y' and
              b.cust_account_id is not null )  )
   and    sysdate between nvl(a.start_date_active, sysdate )
		      and nvl(a.end_date_active, sysdate ) ;

Line: 1315


   L_DEL_IDX                          NUMBER := 0; -- index for delete tables

Line: 1484


            DELETE_CUST_PLANS (
               P_PLAN_ID_TBL            =>  L_PLAN_ID_DEL_TBL,
               P_PARTY_ID_TBL           =>  L_PARTY_ID_DEL_TBL,
               P_CUST_ID_TBL            =>  L_CUST_ID_DEL_TBL,
               X_RETURN_STATUS          =>  x_return_status );

Line: 1493


               g_mesg := 'Delete of customer to plan association failed.';

Line: 1499


            L_PLAN_ID_DEL_TBL.DELETE;

Line: 1500


            L_PARTY_ID_DEL_TBL.DELETE;

Line: 1501


            L_CUST_ID_DEL_TBL.DELETE;

Line: 1516


               g_mesg := 'Insert of new customer to plan association failed.';

Line: 1522


            L_PLAN_ID_ADD_TBL.DELETE;

Line: 1523


            L_START_DATE_ACTIVE_ADD_TBL.DELETE;

Line: 1524


            L_END_DATE_ACTIVE_ADD_TBL.DELETE;

Line: 1525


            L_PARTY_ID_ADD_TBL.DELETE;

Line: 1526


            L_CUST_ID_ADD_TBL.DELETE;

Line: 1532


      DELETE_CUST_PLANS (
         P_PLAN_ID_TBL            =>  L_PLAN_ID_DEL_TBL,
         P_PARTY_ID_TBL           =>  L_PARTY_ID_DEL_TBL,
         P_CUST_ID_TBL            =>  L_CUST_ID_DEL_TBL,
         X_RETURN_STATUS          =>  x_return_status );

Line: 1542


            g_mesg := 'Delete of customer to plan association failed.';

Line: 1559


            g_mesg := 'Insert of new customer to plan association failed.';

Line: 1648


   select sysdate
   into   l_ins_start_date
   from   sys.dual;

Line: 1653


      INSERT INTO csc_cust_plans (
         CUST_PLAN_ID,                   PLAN_ID,                PARTY_ID,
         CUST_ACCOUNT_ID,                START_DATE_ACTIVE,
         END_DATE_ACTIVE,                MANUAL_FLAG,            PLAN_STATUS_CODE,
         REQUEST_ID,                     PROGRAM_APPLICATION_ID, PROGRAM_ID,
         PROGRAM_UPDATE_DATE,            LAST_UPDATE_DATE,       CREATION_DATE,
         LAST_UPDATED_BY,                CREATED_BY,             LAST_UPDATE_LOGIN,
         ATTRIBUTE1,                     ATTRIBUTE2,             ATTRIBUTE3,
         ATTRIBUTE4,                     ATTRIBUTE5,             ATTRIBUTE6,
         ATTRIBUTE7,                     ATTRIBUTE8,             ATTRIBUTE9,
         ATTRIBUTE10,                    ATTRIBUTE11,            ATTRIBUTE12,
         ATTRIBUTE13,                    ATTRIBUTE14,            ATTRIBUTE15,
         ATTRIBUTE_CATEGORY,             OBJECT_VERSION_NUMBER )
      SELECT
	    CSC_CUST_PLANS_S.NEXTVAL,       p_plan_id_tbl(i),       p_party_id_tbl(i),
	    p_cust_id_tbl(i),               p_start_date_active_tbl(i),
	    p_end_date_active_tbl(i),       'N',                    CSC_CORE_UTILS_PVT.APPLY_PLAN,
	    NULL,                           NULL,                   NULL,
	    NULL,                           sysdate,                sysdate,
	    FND_GLOBAL.USER_ID,             FND_GLOBAL.USER_ID,     FND_GLOBAL.CONC_LOGIN_ID,
	    NULL,                           NULL,                   NULL,
	    NULL,                           NULL,                   NULL,
	    NULL,                           NULL,                   NULL,
	    NULL,                           NULL,                   NULL,
	    NULL,                           NULL,                   NULL,
	    NULL,                           1
      FROM  SYS.DUAL
	 WHERE NOT EXISTS ( select 1
					from   csc_cust_plans
					where  plan_id                  = p_plan_id_tbl(i)
					and    party_id                 = p_party_id_tbl(i)
					and    nvl(cust_account_id, 0)  = nvl(p_cust_id_tbl(i), 0)
				    );

Line: 1687


   select sysdate
   into   l_ins_end_date
   from   sys.dual;

Line: 1692


	 INSERT INTO csc_cust_plans_audit (
         PLAN_AUDIT_ID,           PLAN_ID,                   PARTY_ID,
         CUST_ACCOUNT_ID,         PLAN_STATUS_CODE,
         REQUEST_ID,              PROGRAM_APPLICATION_ID,    PROGRAM_ID,
         PROGRAM_UPDATE_DATE,     LAST_UPDATE_DATE,          CREATION_DATE,
         LAST_UPDATED_BY,         CREATED_BY,                LAST_UPDATE_LOGIN,
         ATTRIBUTE1,              ATTRIBUTE2,                ATTRIBUTE3,
         ATTRIBUTE4,              ATTRIBUTE5,                ATTRIBUTE6,
         ATTRIBUTE7,              ATTRIBUTE8,                ATTRIBUTE9,
         ATTRIBUTE10,             ATTRIBUTE11,               ATTRIBUTE12,
         ATTRIBUTE13,             ATTRIBUTE14,               ATTRIBUTE15,
         ATTRIBUTE_CATEGORY,      OBJECT_VERSION_NUMBER )
      SELECT
	    CSC_CUST_PLANS_AUDIT_S.NEXTVAL, p_plan_id_tbl(i),   p_party_id_tbl(i),
	    p_cust_id_tbl(i),        CSC_CORE_UTILS_PVT.APPLY_PLAN,
	    NULL,                    NULL,                      NULL,
	    NULL,                    SYSDATE,                   SYSDATE,
	    FND_GLOBAL.USER_ID,      FND_GLOBAL.USER_ID,        FND_GLOBAL.CONC_LOGIN_ID,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    1
      FROM SYS.DUAL
	 WHERE EXISTS ( select 1
				 from   csc_cust_plans
				 where  plan_id  = p_plan_id_tbl(i)
				 and    party_id = p_party_id_tbl(i)
				 and    creation_date between l_ins_start_date and l_ins_end_date);

Line: 1731


PROCEDURE DELETE_CUST_PLANS (
    P_PLAN_ID_TBL                IN   CSC_PLAN_ID_TBL_TYPE,
    P_PARTY_ID_TBL               IN   CSC_PARTY_ID_TBL_TYPE,
    P_CUST_ID_TBL                IN   CSC_CUST_ID_TBL_TYPE,
    X_RETURN_STATUS              OUT  NOCOPY VARCHAR2 )
IS
   l_api_name                CONSTANT VARCHAR2(30) := 'DELETE_CUST_PLANS';

Line: 1746


   SAVEPOINT DELETE_CUST_PLANS_PVT;

Line: 1763


	 DELETE FROM csc_cust_plans
	 WHERE  plan_id                 = p_plan_id_tbl(i)
	 AND    party_id                = p_party_id_tbl(i)
	 AND    nvl(cust_account_id,0)  = nvl(p_cust_id_tbl(i), nvl(cust_account_id,0) )
	 AND    manual_flag             = 'N'
   RETURNING plan_id, party_id, cust_account_id
   BULK COLLECT INTO  l_plan_id_tbl, l_party_id_tbl, l_cust_id_tbl;

Line: 1772


	 INSERT INTO csc_cust_plans_audit (
         PLAN_AUDIT_ID,           PLAN_ID,                   PARTY_ID,
         CUST_ACCOUNT_ID,         PLAN_STATUS_CODE,
         REQUEST_ID,              PROGRAM_APPLICATION_ID,    PROGRAM_ID,
         PROGRAM_UPDATE_DATE,     LAST_UPDATE_DATE,          CREATION_DATE,
         LAST_UPDATED_BY,         CREATED_BY,                LAST_UPDATE_LOGIN,
         ATTRIBUTE1,              ATTRIBUTE2,                ATTRIBUTE3,
         ATTRIBUTE4,              ATTRIBUTE5,                ATTRIBUTE6,
         ATTRIBUTE7,              ATTRIBUTE8,                ATTRIBUTE9,
         ATTRIBUTE10,             ATTRIBUTE11,               ATTRIBUTE12,
         ATTRIBUTE13,             ATTRIBUTE14,               ATTRIBUTE15,
         ATTRIBUTE_CATEGORY,      OBJECT_VERSION_NUMBER )
      SELECT
	    CSC_CUST_PLANS_AUDIT_S.NEXTVAL, l_plan_id_tbl(i),   l_party_id_tbl(i),
	    l_cust_id_tbl(i),        CSC_CORE_UTILS_PVT.REMOVE_PLAN,
	    NULL,                    NULL,                      NULL,
	    NULL,                    SYSDATE,                   SYSDATE,
	    FND_GLOBAL.USER_ID,      FND_GLOBAL.USER_ID,        FND_GLOBAL.CONC_LOGIN_ID,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    NULL,                      NULL,
	    NULL,                    1
      FROM SYS.DUAL;

Line: 1804


END DELETE_CUST_PLANS;