[Home] [Help]
87: UPDATE ar_collectors ARC set
88: resource_type = 'RS_RESOURCE',
89: resource_id =
90: ( SELECT max(jtfrs.resource_id)
91: FROM jtf_rs_resource_extns jtfrs
92: WHERE
93: jtfrs.source_id is NOT NULL
94: AND jtfrs.category = 'EMPLOYEE'
95: AND Trunc(start_date_active) <= Trunc(sysdate)
140:
141:
142: CURSOR INSERT_RESOURCE IS
143: SELECT jtfrs.resource_id,SOURCE_ID,SOURCE_NAME,jtfrs.user_name
144: from fnd_user_resp_groups furg, fnd_responsibility_vl fr, jtf_rs_resource_extns jtfrs
145: WHERE fr.responsibility_id = furg.responsibility_id
146: AND jtfrs.user_id = furg.user_id
147: AND furg.start_date < sysdate and (furg.end_date is null or furg.end_date > sysdate)
148: AND jtfrs.source_id is not null and jtfrs.user_id is not null
248: BEGIN
249: SELECT 1
250: INTO l_check
251: FROM
252: jtf_rs_resource_extns jtfrs,JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv
253: WHERE
254: jtrr.role_resource_id = jtfrs.resource_id
255: AND jtv.role_id = jtrr.role_id
256: AND jtv.ROLE_CODE = 'IEX_AGENT'
336: ar_collectors ac
337: WHERE employee_id is not null
338: AND employee_id not in (SELECT jtr.source_id
339: FROM JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv,
340: JTF_RS_RESOURCE_EXTNS jtr
341: WHERE jtv.ROLE_CODE = 'IEX_AGENT' AND jtv.role_id = jtrr.role_id
342: AND jtrr.role_resource_id = jtr.resource_id
343: AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
344: AND jtr.source_id is not null)
342: AND jtrr.role_resource_id = jtr.resource_id
343: AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
344: AND jtr.source_id is not null)
345: AND employee_id not in (SELECT jtr.source_id from
346: jtf_rs_resource_extns jtr where source_id is not null)
347: AND resource_id is not null
348: ORDER BY 1;
349:
350: */
376: FROM
377: ar_collectors ac, FND_USER fuser, per_all_people_f pemp ,hz_parties hp
378: WHERE ac.employee_id is not null
379: AND ac.employee_id not in (SELECT jtr.source_id from
380: jtf_rs_resource_extns jtr where source_id is not null)
381: AND ac.resource_id is null
382: AND ac.employee_id = fuser.employee_id
383: and ac.employee_id = pemp.person_id
384: and pemp.party_id = hp.party_id
416: FROM
417: FND_USER_RESP_GROUPS FURG,
418: FND_RESPONSIBILITY FR ,
419: FND_USER FU,
420: JTF_RS_RESOURCE_EXTNS JRS
421: WHERE
422: FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
423: AND JRS.SOURCE_ID = FU.EMPLOYEE_ID
424: AND FU.USER_ID = FURG.USER_ID
483: FOR i IN L_USER_ID.FIRST .. L_USER_ID.LAST
484: LOOP
485:
486: BEGIN
487: --UPdate the JTF_RS_RESOURCE_EXTNS Table with the value from FND_USER.
488: UPDATE JTF_RS_RESOURCE_EXTNS
489: SET USER_ID = L_USER_ID(i),
490: USER_NAME = L_USER_NAME(i),
491: last_update_date = SYSDATE,
484: LOOP
485:
486: BEGIN
487: --UPdate the JTF_RS_RESOURCE_EXTNS Table with the value from FND_USER.
488: UPDATE JTF_RS_RESOURCE_EXTNS
489: SET USER_ID = L_USER_ID(i),
490: USER_NAME = L_USER_NAME(i),
491: last_update_date = SYSDATE,
492: last_updated_by = -1
492: last_updated_by = -1
493: WHERE SOURCE_ID = L_EMPLOYEE_ID(I)
494: AND CATEGORY = 'EMPLOYEE'
495: -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
496: --AND L_USER_ID(I) NOT IN (SELECT NVL(USER_ID,-1) FROM JTF_RS_RESOURCE_EXTNS WHERE CATEGORY = 'EMPLOYEE');
497: and user_id is null;
498: -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
499: EXCEPTION WHEN OTHERS THEN
500: -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
506: -- Check whether Collections Role Exists for the Resource and Create if Role does'nt exist.
507: BEGIN
508: SELECT ROLE_ID INTO L_CHECK_ROLE
509: FROM JTF_RS_ROLE_RELATIONS_VL JRR,
510: JTF_RS_RESOURCE_EXTNS JRE
511: WHERE
512: JRE.SOURCE_ID = L_EMPLOYEE_ID(I)
513: AND JRR.ROLE_RESOURCE_ID = JRE.RESOURCE_ID
514: AND JRR.ROLE_CODE = 'IEX_AGENT';