[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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'
AND acc.status = 'A' -- added for bug 10087650 PNAVEENK
GROUP BY WIN.TRANS_OBJECT_id,
WIN.RESOURCE_ID,
WIN.GROUP_ID,
WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
OPEN c_UpdateProfile(l_worker_id);
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;
IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
CLOSE C_UPDATEPROFILE;
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Party Profile Rows Updated ' || l_customer_id.count);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Party Profile Rows Updated ' || l_customer_id.count);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during party bulk update.. Performing row update..');
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Party Profile Rows Updated ' ||l_cnt);
IF C_UPDATEPROFILE%ISOPEN THEN
CLOSE C_UPDATEPROFILE;
l_customer_id.delete;
l_terr_id.delete;
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'
AND acc.status = 'A' -- added for bug 10087650 PNAVEENK
GROUP BY WIN.TRANS_OBJECT_id,
WIN.RESOURCE_ID,
WIN.GROUP_ID,
WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
OPEN c_UpdateProfile(l_worker_id);
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;
IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
CLOSE C_UPDATEPROFILE;
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Account Profile Rows Updated ' || l_customer_id.count);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Account Pofile Rows Updated ' || l_customer_id.count);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during bulk update.. Performing row update..');
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Account Profile Rows Updated ' ||l_cnt);
IF C_UPDATEPROFILE%ISOPEN THEN
CLOSE C_UPDATEPROFILE;
l_customer_id.delete;
l_terr_id.delete;
/*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;*/
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'
AND acc.status = 'A' -- added for bug 10087650 PNAVEENK
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;
select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
OPEN c_UpdateProfile(l_worker_id);
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;
IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
CLOSE C_UPDATEPROFILE;
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Site Profile Rows Updated ' || l_customer_id.count);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Site Profile Rows Updated ' || l_customer_id.count);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during bulk update.. Performing row update..');
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);
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Site Profile Rows Updated ' ||l_cnt);
IF C_UPDATEPROFILE%ISOPEN THEN
CLOSE C_UPDATEPROFILE;
l_customer_id.delete;
l_terr_id.delete;
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));
IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
SELECT GROUP_NAME
INTO l_resource_name
FROM jtf_rs_groups_vl
WHERE group_id = l_missSF_id(i);
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);
IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
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' )) ;
FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inserted to the AR_COLLECTORS. Collector_ID ');
FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
SELECT GROUP_NAME
INTO l_resource_name
FROM jtf_rs_groups_vl
WHERE group_id = l_missSF_id(i);
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);
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' )) ;
FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
END LOOP; /* Update While loop; l_attempts < 3 */
IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');
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 acc.status = 'A' -- added for bug 10087650 PNAVEENK
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;
IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);
WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
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;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Records Updated: ' || l_first || '-'|| l_last);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
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;
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
END LOOP; /* Update While loop; l_attempts < 3 */
IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');
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 acc.status = 'A' -- added for bug 10087650 PNAVEENK
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;
IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);
WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
END LOOP; /* Update While loop; l_attempts < 3 */
IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');
FND_FILE.PUT_LINE(FND_FILE.LOG,'---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');