DBA Data[Home] [Help]

APPS.IEX_PROCESS_ACCOUNT_WINNERS SQL Statements

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

Line: 164

    CURSOR c_UpdateProfile(c_worker_id number) IS
    SELECT WIN.TRANS_OBJECT_id,
           WIN.RESOURCE_id,
           nvl(WIN.GROUP_ID,-777) GROUP_ID,
           nvl(WIN.org_id,-777) org_id,
           win.resource_type,
           ACC.COLLECTOR_ID
    FROM JTF_TAE_1600_CUST_WINNERS WIN,
		AR_COLLECTORS ACC,
	    JTF_RS_ROLE_RELATIONS jtrr,
		JTF_RS_ROLES_B  jtr
    WHERE   WIN.SOURCE_ID = -1600
      AND WIN.worker_id = c_worker_id
      AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
      -- AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
      AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
      AND DECODE(ACC.RESOURCE_TYPE,
         'RS_RESOURCE', 'RS_EMPLOYEE',
         'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
      AND jtrr.role_resource_id =  WIN.RESOURCE_ID
      AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
    GROUP BY WIN.TRANS_OBJECT_id,
             WIN.RESOURCE_ID,
             WIN.GROUP_ID,
             WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
Line: 257

       OPEN c_UpdateProfile(l_worker_id);
Line: 260

          FETCH c_UpdateProfile BULK COLLECT INTO
	      l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
       	      LIMIT l_max_fetches;
Line: 264

  	    IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
Line: 265

    	    CLOSE C_UPDATEPROFILE;
Line: 270

            UPDATE  HZ_CUSTOMER_PROFILES ACC
             SET object_version_number =  nvl(object_version_number,0) + 1,
	     ACC.LAST_UPDATE_DATE = SYSDATE,
	     ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
	     ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
	     ACC.REQUEST_ID = p_terr_globals.request_id,
	     ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
	     ACC.PROGRAM_ID = p_terr_globals.prog_id,
	     ACC.PROGRAM_UPDATE_DATE = SYSDATE,
	     ACC.COLLECTOR_ID   = l_collector_id(i)
	     WHERE  ACC.PARTY_ID   = l_customer_id(i)
             AND ACC.SITE_USE_ID IS NULL
             AND ACC.CUST_ACCOUNT_ID = -1
	     AND ACC.COLLECTOR_ID <> l_collector_id(i);
Line: 285

             IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
Line: 290

            IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');
Line: 294

            UPDATE  HZ_CUSTOMER_PROFILES ACC
             SET object_version_number =  nvl(object_version_number,0) + 1,
	     ACC.LAST_UPDATE_DATE = SYSDATE,
	     ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
	     ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
	     ACC.REQUEST_ID = p_terr_globals.request_id,
	     ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
	     ACC.PROGRAM_ID = p_terr_globals.prog_id,
	     ACC.PROGRAM_UPDATE_DATE = SYSDATE,
	     ACC.COLLECTOR_ID   = l_collector_id(i)
	     WHERE  ACC.PARTY_ID   = l_customer_id(i)
             AND ACC.SITE_USE_ID IS NULL
             AND ACC.CUST_ACCOUNT_ID = -1
	     AND ACC.COLLECTOR_ID <> l_collector_id(i);	    EXCEPTION
Line: 309

                IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
Line: 319

	IF  C_UPDATEPROFILE%ISOPEN THEN
	  CLOSE C_UPDATEPROFILE;
Line: 325

    l_customer_id.delete;
Line: 326

    l_terr_id.delete;
Line: 358

    CURSOR c_UpdateProfile(c_worker_id number) IS
    SELECT WIN.TRANS_OBJECT_id,
           WIN.RESOURCE_id,
           nvl(WIN.GROUP_ID,-777) GROUP_ID,
           nvl(WIN.org_id,-777) org_id,
           win.resource_type,
           ACC.COLLECTOR_ID
    FROM JTF_TAE_1600_CUST_WINNERS WIN,
	  AR_COLLECTORS ACC,
	  JTF_RS_ROLE_RELATIONS jtrr,
	  JTF_RS_ROLES_B  jtr
    WHERE   WIN.SOURCE_ID = -1600
      AND WIN.worker_id = c_worker_id
      AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
      --AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag
      AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
      AND DECODE(ACC.RESOURCE_TYPE,
         'RS_RESOURCE', 'RS_EMPLOYEE',
         'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
      AND jtrr.role_resource_id =  WIN.RESOURCE_ID
      AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
      GROUP BY WIN.TRANS_OBJECT_id,
             WIN.RESOURCE_ID,
             WIN.GROUP_ID,
             WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
Line: 451

       OPEN c_UpdateProfile(l_worker_id);
Line: 454

          FETCH c_UpdateProfile BULK COLLECT INTO
	      l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
       	      LIMIT l_max_fetches;
Line: 458

  	    IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
Line: 459

    	    CLOSE C_UPDATEPROFILE;
Line: 464

            UPDATE  HZ_CUSTOMER_PROFILES ACC
             SET object_version_number    =  nvl(object_version_number,0) + 1,
	     ACC.LAST_UPDATE_DATE         = SYSDATE,
	     ACC.LAST_UPDATED_BY          = p_terr_globals.user_id,
	     ACC.LAST_UPDATE_LOGIN        = p_terr_globals.last_update_login,
	     ACC.REQUEST_ID               = p_terr_globals.request_id,
	     ACC.PROGRAM_APPLICATION_ID   = p_terr_globals.prog_appl_id,
	     ACC.PROGRAM_ID               = p_terr_globals.prog_id,
	     ACC.PROGRAM_UPDATE_DATE      = SYSDATE,
	     ACC.COLLECTOR_ID             = l_collector_id(i)
	     WHERE  ACC.PARTY_ID          = l_customer_id(i)
             AND ACC.SITE_USE_ID          IS NULL
             AND ACC.CUST_ACCOUNT_ID      <> -1
	     AND ACC.COLLECTOR_ID         <> l_collector_id(i);
Line: 479

             IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
Line: 484

            IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
Line: 488

            UPDATE  HZ_CUSTOMER_PROFILES ACC
             SET object_version_number    =  nvl(object_version_number,0) + 1,
	     ACC.LAST_UPDATE_DATE         = SYSDATE,
	     ACC.LAST_UPDATED_BY          = p_terr_globals.user_id,
	     ACC.LAST_UPDATE_LOGIN        = p_terr_globals.last_update_login,
	     ACC.REQUEST_ID               = p_terr_globals.request_id,
	     ACC.PROGRAM_APPLICATION_ID   = p_terr_globals.prog_appl_id,
	     ACC.PROGRAM_ID               = p_terr_globals.prog_id,
	     ACC.PROGRAM_UPDATE_DATE      = SYSDATE,
	     ACC.COLLECTOR_ID             = l_collector_id(i)
	     WHERE  ACC.PARTY_ID          = l_customer_id(i)
             AND ACC.SITE_USE_ID          IS NULL
             AND ACC.CUST_ACCOUNT_ID      <> -1
	     AND ACC.COLLECTOR_ID         <> l_collector_id(i);
Line: 503

                IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
Line: 513

	IF  C_UPDATEPROFILE%ISOPEN THEN
	  CLOSE C_UPDATEPROFILE;
Line: 520

    l_customer_id.delete;
Line: 521

    l_terr_id.delete;
Line: 554

   /*CURSOR c_UpdateProfile(c_worker_id number) IS
		SELECT WIN.TRANS_OBJECT_id,
			WIN.TRANS_DETAIL_OBJECT_ID,
			WIN.RESOURCE_id,
			nvl(WIN.GROUP_ID,-777) GROUP_ID,
			nvl(WIN.org_id,-777) org_id,
			win.resource_type,
			ACC.COLLECTOR_ID,
			cust_acct.cust_account_id,
			hzp.SITE_USE_ID
		FROM JTF_TAE_1600_CUST_WINNERS WIN,
			AR_COLLECTORS ACC,
			JTF_RS_ROLE_RELATIONS jtrr,
			JTF_RS_ROLES_B  jtr,
			HZ_CUST_SITE_USES_ALL hzp,
			HZ_CUST_ACCT_SITES_ALL acct_site,
			HZ_CUST_ACCOUNTS cust_acct
		WHERE WIN.SOURCE_ID = -1600
			AND   WIN.worker_id = c_worker_id
			AND   WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
			--AND   WIN.full_access_flag = 'Y'
			AND   ACC.RESOURCE_ID = WIN.RESOURCE_ID
			AND   DECODE(ACC.RESOURCE_TYPE,
			'RS_RESOURCE', 'RS_EMPLOYEE',
			'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
			AND jtrr.role_resource_id =  WIN.RESOURCE_ID
			AND jtr.ROLE_ID =  jtrr.role_id
			and jtr.role_type_code = 'COLLECTIONS'
			--AND win.trans_detail_object_id is not null
			--AND acct_site.party_site_id = win.trans_detail_object_id
			AND WIN.TRANS_OBJECT_ID = CUST_ACCT.PARTY_ID
			AND CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id
			AND acct_site.cust_acct_site_id = hzp.cust_acct_site_id
			AND hzp.SITE_USE_CODE = 'BILL_TO'
			AND WIN.ORG_ID = acct_site.ORG_ID
			AND acct_site.ORG_ID = hzp.ORG_ID
		GROUP BY WIN.TRANS_OBJECT_id,
			WIN.TRANS_DETAIL_OBJECT_ID,
			WIN.RESOURCE_ID,
			WIN.GROUP_ID,
			WIN.ORG_ID,
			WIN.RESOURCE_TYPE,
			ACC.COLLECTOR_ID,
			cust_acct.cust_account_id,
			hzp.SITE_USE_ID;*/
Line: 599

    CURSOR c_UpdateProfile(c_worker_id number) IS
    SELECT WIN.TRANS_OBJECT_id,
           WIN.TRANS_DETAIL_OBJECT_ID,
           WIN.RESOURCE_id,
           nvl(WIN.GROUP_ID,-777) GROUP_ID,
           nvl(WIN.org_id,-777) org_id,
           win.resource_type,
           ACC.COLLECTOR_ID,
	   acct_site.cust_account_id,
           hzp.SITE_USE_ID
    FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
	  JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B  jtr,
	  HZ_CUST_SITE_USES hzp,
    HZ_CUST_ACCT_SITES acct_site
    WHERE   WIN.SOURCE_ID = -1600
    AND   WIN.worker_id = c_worker_id
    AND   WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
    --AND   WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag.
    AND   win.org_id = hzp.org_id
    AND   ACC.RESOURCE_ID = WIN.RESOURCE_ID
    AND   DECODE(ACC.RESOURCE_TYPE,
         'RS_RESOURCE', 'RS_EMPLOYEE',
         'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
    AND jtrr.role_resource_id =  WIN.RESOURCE_ID
    AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
    AND win.trans_detail_object_id is not null
    AND acct_site.party_site_id = win.trans_detail_object_id
    AND acct_site.cust_acct_site_id = hzp.cust_acct_site_id
    AND hzp.SITE_USE_CODE = 'BILL_TO'
    GROUP BY WIN.TRANS_OBJECT_id,
    		 WIN.TRANS_DETAIL_OBJECT_ID,
             WIN.RESOURCE_ID,
             WIN.GROUP_ID,
             WIN.ORG_ID, WIN.RESOURCE_TYPE,
             ACC.COLLECTOR_ID,
	     acct_site.cust_account_id,
             hzp.SITE_USE_ID;
Line: 704

          IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
Line: 709

       OPEN c_UpdateProfile(l_worker_id);
Line: 712

          FETCH c_UpdateProfile BULK COLLECT INTO
                l_customer_id, l_partysite_id, l_salesforce_id, l_sales_group_id,
		l_org_id, l_Res_type, l_Collector_ID,l_cust_account_id, l_siteuse_id
        	LIMIT l_max_fetches;
Line: 717

  	    IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
Line: 718

    	    CLOSE C_UPDATEPROFILE;
Line: 723

             UPDATE  HZ_CUSTOMER_PROFILES ACC
	     SET object_version_number  =  nvl(object_version_number,0) + 1,
	     ACC.LAST_UPDATE_DATE       = SYSDATE,
 	     ACC.LAST_UPDATED_BY        = p_terr_globals.user_id,
	     ACC.LAST_UPDATE_LOGIN      = p_terr_globals.last_update_login,
   	     ACC.REQUEST_ID             = p_terr_globals.request_id,
	     ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
	     ACC.PROGRAM_ID             = p_terr_globals.prog_id,
	     ACC.PROGRAM_UPDATE_DATE    = SYSDATE,
             ACC.COLLECTOR_ID           = l_collector_id(i)
 	     WHERE  ACC.PARTY_ID        = l_customer_id(i)
	     AND ACC.CUST_ACCOUNT_ID    = l_cust_account_id(i)
      	     AND ACC.SITE_USE_ID        = l_siteuse_id(i)
	     AND ACC.COLLECTOR_ID       <> l_collector_id(i);
Line: 738

             IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
Line: 743

            IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
Line: 747

		UPDATE  HZ_CUSTOMER_PROFILES ACC
	        SET object_version_number  =  nvl(object_version_number,0) + 1,
		ACC.LAST_UPDATE_DATE       = SYSDATE,
		ACC.LAST_UPDATED_BY        = p_terr_globals.user_id,
		ACC.LAST_UPDATE_LOGIN      = p_terr_globals.last_update_login,
		ACC.REQUEST_ID             = p_terr_globals.request_id,
		ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
		ACC.PROGRAM_ID             = p_terr_globals.prog_id,
		ACC.PROGRAM_UPDATE_DATE    = SYSDATE,
	        ACC.COLLECTOR_ID           = l_collector_id(i)
	 	WHERE  ACC.PARTY_ID        = l_customer_id(i)
		AND ACC.CUST_ACCOUNT_ID    = l_cust_account_id(i)
                AND ACC.SITE_USE_ID        = l_siteuse_id(i)
		AND ACC.COLLECTOR_ID       <> l_collector_id(i);
Line: 763

                IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
Line: 773

	IF  C_UPDATEPROFILE%ISOPEN THEN
	  CLOSE C_UPDATEPROFILE;
Line: 782

    l_customer_id.delete;
Line: 783

    l_terr_id.delete;
Line: 836

    SELECT DISTINCT WIN.RESOURCE_id,
           nvl(WIN.GROUP_ID,-777) GROUP_ID,
           nvl(WIN.org_id,-777) org_id,
           win.resource_type,
		   WIN.PERSON_ID
    FROM  JTF_TAE_1600_CUST_WINNERS WIN,
    	  JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B  jtr
	WHERE WIN.SOURCE_ID = -1600
    AND   WIN.worker_id = c_worker_id
    AND   WIN.RESOURCE_TYPE IN ('RS_EMPLOYEE', 'RS_GROUP')
    AND jtrr.role_resource_id =  WIN.RESOURCE_ID
    AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
    AND NOT EXISTS
   	   (SELECT RESOURCE_ID, RESOURCE_TYPE FROM AR_COLLECTORS acc
   	      WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
   	            ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE',  WIN.RESOURCE_TYPE));
Line: 899

		IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
Line: 906

			WHILE l_attempts < 3 LOOP  /* Update While loop; l_attempts < 3 */
Line: 914

						      SELECT GROUP_NAME
					    	  INTO l_resource_name
						      FROM jtf_rs_groups_vl
						      WHERE group_id = l_missSF_id(i);
Line: 919

						      SELECT resource_name, source_id
						      INTO l_resource_name, l_source_id
						      FROM jtf_rs_resource_extns_vl
						      WHERE resource_id = l_missSF_id(i);
Line: 924

    		        	IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
Line: 926

					    INSERT INTO AR_COLLECTORS
					     (COLLECTOR_ID      ,
					      LAST_UPDATED_BY    ,
					      LAST_UPDATE_DATE   ,
					      LAST_UPDATE_LOGIN  ,
					      CREATION_DATE      ,
					      CREATED_BY         ,
					      NAME               ,
					      EMPLOYEE_ID        ,
					      DESCRIPTION        ,
					      STATUS             ,
					      RESOURCE_ID        ,
					      RESOURCE_TYPE       )
					     VALUES
					     (AR_COLLECTORS_S.NEXTVAL     ,
					      p_terr_globals.user_id ,
					      sysdate             ,
					      p_terr_globals.last_update_login ,
					      sysdate             ,
					      p_terr_globals.user_id  ,
					      substr(l_resource_name,1, 30),
					      l_source_id    ,
				    	  l_resource_name      ,
					      'A',
					      l_missSF_id(i),
					      decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
Line: 953

					      FND_FILE.PUT_LINE(FND_FILE.LOG, '    Inserted to the AR_COLLECTORS.  Collector_ID ');
Line: 957

						      FND_FILE.PUT_LINE(FND_FILE.LOG,'  Error while selecting resource/groupname' );
Line: 962

      			IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
Line: 966

						IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
Line: 973

							      SELECT GROUP_NAME
						    	  INTO l_resource_name
							      FROM jtf_rs_groups_vl
							      WHERE group_id = l_missSF_id(i);
Line: 978

							      SELECT resource_name, source_id
							      INTO l_resource_name, l_source_id
							      FROM jtf_rs_resource_extns_vl
							      WHERE resource_id = l_missSF_id(i);
Line: 984

					    INSERT INTO AR_COLLECTORS
					     (COLLECTOR_ID      ,
					      LAST_UPDATED_BY    ,
					      LAST_UPDATE_DATE   ,
					      LAST_UPDATE_LOGIN  ,
					      CREATION_DATE      ,
					      CREATED_BY         ,
					      NAME               ,
					      EMPLOYEE_ID        ,
					      DESCRIPTION        ,
					      STATUS             ,
					      RESOURCE_ID        ,
					      RESOURCE_TYPE       )
					     VALUES
					     (AR_COLLECTORS_S.NEXTVAL     ,
					      p_terr_globals.user_id  ,
					      sysdate             ,
					      p_terr_globals.last_update_login ,
					      sysdate             ,
					      p_terr_globals.user_id  ,
					      substr(l_resource_name,1,30)     ,
					      l_source_id    ,
				    	  l_resource_name      ,
					      'A',
					      l_missSF_id(i),
					      decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
Line: 1011

					      FND_FILE.PUT_LINE(FND_FILE.LOG, '             After Inserting in to the AR_COLLECTORS');
Line: 1015

                				IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
Line: 1032

			END LOOP; /* Update While loop; l_attempts < 3 */
Line: 1035

    	IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');
Line: 1080

    SELECT WIN.TRANS_OBJECT_id
    FROM JTF_TAE_1600_CUST_WINNERS WIN,
		AR_COLLECTORS ACC,
	    JTF_RS_ROLE_RELATIONS jtrr,
		JTF_RS_ROLES_B  jtr
    WHERE   WIN.SOURCE_ID = -1600
      AND WIN.worker_id = c_worker_id
      AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
      -- AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
      AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
      AND DECODE(ACC.RESOURCE_TYPE,
         'RS_RESOURCE', 'RS_EMPLOYEE',
         'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
      AND jtrr.role_resource_id =  WIN.RESOURCE_ID
      AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
     AND NOT EXISTS
   	   (SELECT 1 FROM HZ_CUSTOMER_PROFILES hcp
   	      WHERE hcp.CUST_ACCOUNT_ID  = -1 AND
   	            hcp.PARTY_ID  = WIN.TRANS_OBJECT_ID AND
   	            hcp.site_use_id is null  )
    GROUP BY WIN.TRANS_OBJECT_id;
Line: 1141

		IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);
Line: 1155

			WHILE l_attempts < 3 LOOP  /* Update While loop; l_attempts < 3 */
Line: 1163

	 			SELECT CUST_ACCOUNT_PROFILE_ID
				INTO l_profile_id
				FROM HZ_CUSTOMER_PROFILES hcp
   	                        WHERE hcp.CUST_ACCOUNT_ID  = -1
				AND  hcp.PARTY_ID  = l_missCustomer(i)
   	                        AND  hcp.site_use_id is null;
Line: 1189

						      FND_FILE.PUT_LINE(FND_FILE.LOG,'  Error while selecting resource/groupname' );
Line: 1196

      			IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
Line: 1200

					IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
Line: 1207

		 			SELECT CUST_ACCOUNT_PROFILE_ID
					INTO l_profile_id
					FROM HZ_CUSTOMER_PROFILES hcp
   				        WHERE hcp.CUST_ACCOUNT_ID  = -1
					AND  hcp.PARTY_ID  = l_missCustomer(i)
	   	                        AND  hcp.site_use_id is null;
Line: 1231

                				IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API  update');
Line: 1251

			END LOOP; /* Update While loop; l_attempts < 3 */
Line: 1254

    	IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');
Line: 1300

        SELECT win.trans_object_id, hzp.SITE_USE_ID,
          acct_site.cust_account_id
    FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
	  JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B  jtr,
	  HZ_CUST_SITE_USES hzp,
    HZ_CUST_ACCT_SITES acct_site
    WHERE   WIN.SOURCE_ID = -1600
     AND   WIN.worker_id = c_worker_id
      AND   WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
      -- AND   WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
      AND   ACC.RESOURCE_ID = WIN.RESOURCE_ID
      AND   DECODE(ACC.RESOURCE_TYPE,
         'RS_RESOURCE', 'RS_EMPLOYEE',
         'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
      AND jtrr.role_resource_id =  WIN.RESOURCE_ID
      AND jtr.ROLE_ID =  jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
      AND win.trans_detail_object_id is not null
      AND acct_site.party_site_id = win.trans_detail_object_id
      AND hzp.cust_acct_site_id = acct_site.cust_acct_site_id
      AND win.org_id = hzp.org_id
      and hzp.SITE_USE_CODE = 'BILL_TO'
	  AND NOT EXISTS
   	   (SELECT 1 FROM HZ_CUSTOMER_PROFILES hcp
   	      WHERE hcp.PARTY_ID  = WIN.TRANS_OBJECT_ID AND
   	            hcp.site_use_id = HZP.site_use_id )
    GROUP BY WIN.TRANS_OBJECT_ID, acct_site.cust_account_id, hzp.SITE_USE_ID;
Line: 1365

		IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);
Line: 1379

			WHILE l_attempts < 3 LOOP  /* Update While loop; l_attempts < 3 */
Line: 1405

						      FND_FILE.PUT_LINE(FND_FILE.LOG,'  Error while selecting resource/groupname' );
Line: 1410

      			IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
Line: 1414

						IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
Line: 1441

                				IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API  update');
Line: 1458

			END LOOP; /* Update While loop; l_attempts < 3 */
Line: 1461

    	IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');