APPS.AS_PIPELINE_MOVE_PUB SQL Statements

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

Line: 77

SELECT LD.LEAD_ID LEAD_ID
FROM AS_LEADS_ALL LD
WHERE LD.DECISION_DATE BETWEEN from_close_date AND to_close_date
  AND LD.WIN_PROBABILITY BETWEEN from_win_prob AND to_win_prob
  AND STATUS || '' IN in_status
  AND EXISTS
 	 (SELECT 1
 	    FROM AS_ACCESSES_ALL ACC
 	   WHERE ACC.LEAD_ID = LD.LEAD_ID
 	     AND ACC.SALESFORCE_ID = from_sf_id
 	     AND ACC.SALES_GROUP_ID = from_sg_id);

Line: 119

SELECT LEAD_LINE_ID,
CREDIT_TYPE_ID,
SUM(CREDIT_AMOUNT) CR_AMT ,
SUM(CREDIT_PERCENT) CR_PCT
  FROM AS_SALES_CREDITS ASSC
  WHERE ASSC.LEAD_ID = l_lead_id
    AND ASSC.SALESFORCE_ID = l_salesforceid
    AND ASSC.SALESGROUP_ID = l_salesgroupid
    AND ASSC.PERSON_ID = l_personid
GROUP BY LEAD_LINE_ID,CREDIT_TYPE_ID;

Line: 132

	   SELECT RES.SOURCE_ID,RES.RESOURCE_ID INTO from_person,from_res FROM JTF_RS_RESOURCE_EXTNS RES, FND_USER USR WHERE RES.SOURCE_ID = USR.EMPLOYEE_ID AND RES.CATEGORY = 'EMPLOYEE' AND USR.USER_NAME =  p_from_user;

Line: 133

	   SELECT RES.SOURCE_ID,RES.RESOURCE_ID INTO to_person,to_res FROM JTF_RS_RESOURCE_EXTNS RES, FND_USER USR WHERE RES.SOURCE_ID = USR.EMPLOYEE_ID AND RES.CATEGORY = 'EMPLOYEE' AND USR.USER_NAME =  p_to_user;

Line: 134

	   SELECT GROUP_ID INTO from_group_id FROM JTF_RS_GROUPS_B WHERE GROUP_NUMBER = p_from_grp;

Line: 135

	   SELECT GROUP_ID INTO to_group_id FROM JTF_RS_GROUPS_B WHERE GROUP_NUMBER = p_to_grp;

Line: 136

	   SELECT COUNT(1) INTO from_is_group_correct FROM JTF_RS_GROUP_MEMBERS WHERE RESOURCE_ID = from_res AND GROUP_ID = from_group_id AND DELETE_FLAG = 'N';

Line: 137

	   SELECT COUNT(1) INTO to_is_group_correct FROM JTF_RS_GROUP_MEMBERS WHERE RESOURCE_ID = to_res AND GROUP_ID = to_group_id AND DELETE_FLAG = 'N';

Line: 164

	   status_str := '(SELECT DISTINCT UPPER(status_code) FROM as_statuses_b WHERE enabled_flag = ''Y'' AND opp_flag = ''Y'')';

Line: 169

	     SELECT REPLACE(UPPER(TRIM(p_status)),',',''',''') INTO status_tokenized FROM DUAL;

Line: 172

	     SELECT REPLACE(UPPER(TRIM(p_status)),',',''',''') INTO status_tokenized FROM DUAL;

Line: 176

             SELECT '(TRIM(''' || REPLACE(REPLACE(UPPER(TRIM(p_status)),',','''),TRIM('''),' ','') || '''))' INTO status_str FROM DUAL;

Line: 186

	   sqlstr := ' SELECT LD.LEAD_ID lead_id ';

Line: 193

	   sqlstr := sqlstr || ' (SELECT 1';

Line: 202

		--* UPDATE SALES CREDITS **
		--** CASE 1 - SR2 already exists in as_sales_credits **
		--** add up the salescredits for SR1 to SR2, update SR2's salescredits and delete the salescredits for SR1 **
		       --dbms_output.put_line('CASE 1 - SR2 already exists in as_sales_credits');

Line: 220

	          SELECT COUNT(*) INTO is_in_sales_credits
                  FROM AS_SALES_CREDITS SC
                  WHERE SC.LEAD_ID = L_LEAD_ID
                    AND SC.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
                    AND SC.SALESFORCE_ID = to_res
                    AND SC.SALESGROUP_ID = to_group_id
                    AND SC.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;

Line: 229

		       -- update salescredits for SR2
				   UPDATE AS_SALES_CREDITS ASSC
				      SET object_version_number =  nvl(object_version_number,0) + 1, ASSC.CREDIT_AMOUNT = ASSC.CREDIT_AMOUNT + SC_REC.CR_AMT,
					  ASSC.CREDIT_PERCENT = ASSC.CREDIT_PERCENT + SC_REC.CR_PCT
				   WHERE ASSC.LEAD_ID = L_LEAD_ID
				           AND ASSC.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
					   AND ASSC.SALESFORCE_ID = to_res
					   AND ASSC.SALESGROUP_ID = to_group_id
					   AND ASSC.PERSON_ID = to_person
					   AND ASSC.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID
					   AND ROWID = (SELECT MIN(ROWID)
							FROM AS_SALES_CREDITS z
							WHERE z.LEAD_ID = L_LEAD_ID
							AND z.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
							AND z.SALESFORCE_ID = ASSC.SALESFORCE_ID
							AND z.SALESGROUP_ID = ASSC.SALESGROUP_ID
							AND z.PERSON_ID = ASSC.PERSON_ID
 							AND z.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID);

Line: 251

					UPDATE AS_SALES_CREDITS
					   SET object_version_number =  nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
					       PERSON_ID = to_person,
					       SALESGROUP_ID = to_group_id
					 WHERE LEAD_ID = L_LEAD_ID
					   AND LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
					   AND SALESFORCE_ID = from_res
					   AND PERSON_ID = from_person
					   AND SALESGROUP_ID = from_group_id
					   AND CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;

Line: 264

    	             -- delete salescredits for SR1
		        DELETE FROM AS_SALES_CREDITS
    			 WHERE LEAD_ID = L_LEAD_ID
    			   AND LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
		           AND SALESFORCE_ID = from_res
       			   AND SALESGROUP_ID = from_group_id
       			   AND CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;

Line: 271

       			--dbms_output.put_line('After deleteing sales credit:'|| SQL%ROWCOUNT);

Line: 273

		--* UPDATE LEADS *
		SELECT COUNT(*) INTO is_owner FROM AS_LEADS_ALL L WHERE L.LEAD_ID = L_LEAD_ID AND L.OWNER_SALESFORCE_ID = from_res AND L.OWNER_SALES_GROUP_ID = from_group_id;

Line: 275

		SELECT COUNT(*) INTO is_in_sales_team FROM AS_ACCESSES_ALL ACC WHERE ACC.LEAD_ID = L_LEAD_ID AND ACC.SALESFORCE_ID = to_res AND ACC.SALES_GROUP_ID = to_group_id;

Line: 279

		        UPDATE AS_LEADS_ALL L
			   SET object_version_number =  nvl(object_version_number,0) + 1, L.OWNER_SALESFORCE_ID = to_res,
			       L.OWNER_SALES_GROUP_ID = to_group_id
      		      	 WHERE L.LEAD_ID = L_LEAD_ID
      		      	   AND L.OWNER_SALESFORCE_ID = from_res
      		      	   AND L.OWNER_SALES_GROUP_ID = from_group_id;

Line: 285

			--* UPDATE SALES TEAM *
		  	 --* CASE 1 - SR1/GRP1 moved to SR2/GRP2 *
		  	  If is_in_sales_team = 1 THEN --* CASE 1A - SR2/GRP2 is already in SALES TEAM *
		  	    BEGIN
                          	-- delete SR1/GR1 from SALES TEAM
                          	DELETE FROM AS_ACCESSES_ALL ACC
                          	WHERE ACC.LEAD_ID = L_LEAD_ID
                          	  AND ACC.SALESFORCE_ID = from_res
				  AND ACC.SALES_GROUP_ID = from_group_id
				  AND ACC.PERSON_ID = from_person ;

Line: 296

                		UPDATE AS_ACCESSES_ALL ACC
                		   SET object_version_number =  nvl(object_version_number,0) + 1, OWNER_FLAG = 'Y',
	       			       TEAM_LEADER_FLAG = 'Y'
	       			 WHERE ACC.LEAD_ID = L_LEAD_ID
	       			   AND ACC.SALESFORCE_ID = to_res
				   AND ACC.SALES_GROUP_ID = to_group_id
				   AND ACC.PERSON_ID = to_person ;

Line: 306

                                UPDATE AS_ACCESSES_ALL ACC
			           SET object_version_number =  nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
			               SALES_GROUP_ID = to_group_id,
			               PERSON_ID = to_person,
			               OWNER_FLAG = 'Y',
			    	       TEAM_LEADER_FLAG = 'Y'
			    	 WHERE ACC.LEAD_ID = L_LEAD_ID
			    	   AND ACC.SALESFORCE_ID = from_res
			           AND ACC.SALES_GROUP_ID = from_group_id
				   AND ACC.PERSON_ID = from_person ;

Line: 326

				-- delete SR1/GR1 from SALES TEAM
			       DELETE FROM AS_ACCESSES_ALL ACC
				WHERE ACC.LEAD_ID = L_LEAD_ID
				  AND ACC.SALESFORCE_ID = from_res
				  AND ACC.SALES_GROUP_ID = from_group_id
				  AND ACC.PERSON_ID = from_person ;

Line: 337

			       UPDATE AS_ACCESSES_ALL ACC
			          SET object_version_number =  nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
			              SALES_GROUP_ID = to_group_id,
			              PERSON_ID = to_person
				WHERE ACC.LEAD_ID = L_LEAD_ID
				  AND ACC.SALESFORCE_ID = from_res
				  AND ACC.SALES_GROUP_ID = from_group_id
				  AND ACC.PERSON_ID = from_person ;

Line: 396

   select res.source_id, res.resource_id
   from JTF_RS_RESOURCE_EXTNS res, FND_USER usr
   where res.source_id = usr.employee_id
   and res.category = 'EMPLOYEE'
   and usr.user_name = x_user;

Line: 402

   select group_id
   from JTF_RS_GROUPS_B
   where group_number = x_sg_num;

Line: 406

   select count(1)
   from JTF_RS_GROUP_MEMBERS
   where resource_id = x_sf_id
   and group_id = x_grp_id;

Line: 510

      update AS_SALES_CREDITS sc
      set object_version_number =  nvl(object_version_number,0) + 1, salesforce_id = to_sf_id,
	     person_id = to_person,
	     salesgroup_id = to_sg_id
      where exists
            ( select ld.lead_id
              from AS_LEADS_ALL ld
              where ld.win_probability between from_win_prob and to_win_prob
              and ld.decision_date between from_close_date and to_close_date
		    and ld.lead_id = sc.lead_id )
      and sc.salesforce_id = from_sf_id
      and sc.salesgroup_id = from_sg_id
      and sc.person_id = from_person;

Line: 529

         update AS_SALES_CREDITS sc
         set object_version_number =  nvl(object_version_number,0) + 1, salesforce_id = to_sf_id,
	        person_id = to_person,
	        salesgroup_id = to_sg_id
         where exists
               ( select ld.lead_id
                 from AS_LEADS_ALL ld
                 where ld.win_probability between from_win_prob and to_win_prob
                 and ld.decision_date between from_close_date and to_close_date
                 and ld.status = ld_status(i_count).status
		       and ld.lead_id = sc.lead_id )
         and sc.salesforce_id = from_sf_id
         and sc.salesgroup_id = from_sg_id
         and sc.person_id = from_person;