[Home] [Help]
193: SELECT SUBSTR (p_sql_text, l_num, l_length) INTO l_last_substring FROM DUAL;
194:
195: l_first_substring := l_first_substring || ' , ' || '''' || p_category || '''' || ' , sysdate, ' || FND_GLOBAL.USER_ID || ' , sysdate, ' || FND_GLOBAL.USER_ID || ' ' ;
196: l_q_str := l_first_substring || l_last_substring;
197: l_ins_qry := 'insert into ibu_usergroups_temp(source_id, category,creation_date, created_by, last_update_date, last_updated_by ) ';
198: l_ins_qry := l_ins_qry || l_q_str;
199: Log_Message(' Insert Temp table query = { ' || l_ins_qry || ' } ');
200:
201: cid := dbms_sql.open_cursor;
200:
201: cid := dbms_sql.open_cursor;
202:
203: -- Fixed Bug# 6641845
204: -- Delete the old records before inserting the new records in table ibu_usergroups_temp
205:
206: dbms_sql.parse(cid, 'delete from ibu_usergroups_temp', dbms_sql.v7);
207: l_rows_processed := dbms_sql.execute(cid);
208: Log_Message(' Insert Temp table rows deleted = ' || l_rows_processed);
202:
203: -- Fixed Bug# 6641845
204: -- Delete the old records before inserting the new records in table ibu_usergroups_temp
205:
206: dbms_sql.parse(cid, 'delete from ibu_usergroups_temp', dbms_sql.v7);
207: l_rows_processed := dbms_sql.execute(cid);
208: Log_Message(' Insert Temp table rows deleted = ' || l_rows_processed);
209:
210: dbms_sql.parse(cid, l_ins_qry, dbms_sql.v7);
213:
214: --dbms_sql.parse(cid, 'commit' , dbms_sql.v7);
215: dbms_sql.close_cursor(cid);
216:
217: select count(*) into l_cnt from ibu_usergroups_temp;
218: Log_Message('Count in ibu_usergroups_temp = ' || to_char(l_cnt) );
219: EXCEPTION
220: WHEN OTHERS THEN
221: Log_Message('Error in Insert_Temp_Table' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
214: --dbms_sql.parse(cid, 'commit' , dbms_sql.v7);
215: dbms_sql.close_cursor(cid);
216:
217: select count(*) into l_cnt from ibu_usergroups_temp;
218: Log_Message('Count in ibu_usergroups_temp = ' || to_char(l_cnt) );
219: EXCEPTION
220: WHEN OTHERS THEN
221: Log_Message('Error in Insert_Temp_Table' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
222: dbms_sql.close_cursor(cid);
229: l_emp_id fnd_user.employee_id%TYPE;
230:
231: Cursor l_source_csr IS
232: select source_id
233: from ibu_usergroups_temp ;
234: BEGIN
235: -- Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers and change the sourcE_id to employee_id instead of customer_id.
236: If p_category = 'PARTY' Then
237: For l_source_rec in l_source_csr Loop
245: Where person_id = A.employee_id)
246: and ( (A.end_date is null) OR (A.end_date > sysdate) );
247:
248: If l_emp_id is not null Then
249: Update ibu_usergroups_temp
250: Set Category = 'EMPLOYEE', Source_id = l_emp_id
251: Where source_id = l_source_rec.source_id;
252: End If;
253:
353: where group_id = p_group_id
354: and A.resource_id = B.resource_id
355: and A.delete_flag = 'N'
356: and A.resource_id not in ( select resource_id
357: from jtf_rs_resource_extns C, ibu_usergroups_temp D
358: where C.source_id = D.source_id
359: and C.category = D.category);
360:
361: Cursor l_cre_res_csr
360:
361: Cursor l_cre_res_csr
362: IS
363: select source_id, category
364: from ibu_usergroups_temp A
365: where not exists (select source_id
366: from jtf_rs_resource_extns
367: where source_id = A.source_id
368: and category = A.category
372: -- Do not create members whose resource records are end dated
373: Cursor l_cre_csr (p_group_id jtf_rs_groups_vl.group_id%TYPE)
374: IS
375: select B.resource_id, B.resource_number
376: from ibu_usergroups_temp A, jtf_rs_resource_extns B
377: where A.source_id = B.source_id
378: and A.category = B.category
379: and not exists ( select resource_id
380: from jtf_rs_group_members
670: and A.Usage = 'ISUPPORT';
671:
672: Cursor l_cre_mem_csr IS
673: select source_id, category
674: from ibu_usergroups_temp ;
675:
676: -- bug 3032219
677: -- to get the first start date of the employee
678: CURSOR c_emp_start_date(c_person_id IN NUMBER) IS