1 PACKAGE BODY PA_R_PROJECT_RESOURCES_PVT
2 -- $Header: PARCPRVB.pls 120.20.12010000.12 2010/02/10 06:27:01 amehrotr ship $
3 AS
4
5 --Global Variables
6 G_pkg_name VARCHAR2(30) := 'PA_R_PROJECT_RESOURCES_PVT';
7
8 --Variable for insert multiple assignment information for a resource.
9 G_p_id NUMBER := 0;
10 G_count NUMBER := 0;
11
12 --Variable to call crm workflow once for all hr assignments of a person
13 G_p_crmwf_id NUMBER := 0;
14
15 --Forward declaration
16
17 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
18
19 PROCEDURE start_crm_workflow ( p_person_id IN NUMBER,
20 p_assignment_start_date IN DATE,
21 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 x_error_message_code OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
23
24 -- Begin Bug 3086960. Added by Sachin.
25
26 --This cursor will loop through each person_id from HR
27 CURSOR CUR_RESOURCES(p_in_person_id IN NUMBER,
28 p_pull_term_res IN VARCHAR2,
29 p_term_range_date IN DATE,
30 p_person_type IN VARCHAR2) IS
31 SELECT person_id,
32 name,
33 organization_id,
34 assignment_start_date,
35 assignment_end_date,
36 start_date,termination_date,
37 default_OU,
38 calendar_id,
39 p_type,
40 user_type,
41 res_exists,
42 per_start_date,
43 per_end_date,
44 per_emp_number,
45 per_email,
46 per_work_phone,
47 per_business_group_id,
48 per_first_name,
49 per_last_name,
50 per_middle_name,
51 job_name,
52 supervisor_id,
53 org_name,
54 resource_type,
55 job_id,
56 job_group_id,
57 location_id
58 FROM pa_r_project_resources_v
59 WHERE person_id = p_in_person_id
60 AND (p_type = p_person_type or p_person_type = 'ALL')
61 UNION
62 -- This select is to get each person_id from HR that is terminated
63 -- if the user wants to pull in those people.
64 SELECT person_id,
65 name,
66 organization_id,
67 assignment_start_date,
68 assignment_end_date,
69 start_date,termination_date,
70 default_OU,
71 calendar_id,
72 p_type,
73 user_type,
74 res_exists,
75 per_start_date,
76 per_end_date,
77 per_emp_number,
78 per_email,
79 per_work_phone,
80 per_business_group_id,
81 per_first_name,
82 per_last_name,
83 per_middle_name,
84 job_name,
85 supervisor_id,
86 org_name,
87 resource_type,
88 job_id,
89 job_group_id,
90 location_id
91 FROM pa_r_project_resources_term_v
92 WHERE p_pull_term_res = 'Y'
93 and person_id = p_in_person_id
94 and termination_date >= p_term_range_date
95 AND (p_type = p_person_type or p_person_type = 'ALL')
96 ORDER BY person_id, assignment_start_date;
97
98 /*
99 --This cursor will loop through each person_id from HR
100 CURSOR CUR_RESOURCES(l_from_emp_num IN VARCHAR2, l_to_emp_num IN VARCHAR2, l_p_org_id IN NUMBER) IS
101 SELECT person_id,
102 name,
103 organization_id,
104 assignment_start_date,
105 assignment_end_date,
106 start_date,termination_date,
107 default_OU,
108 calendar_id,
109 p_type,
110 user_type,
111 res_exists,
112 per_start_date,
113 per_end_date,
114 per_emp_number,
115 per_email,
116 per_work_phone,
117 per_business_group_id,
118 per_first_name,
119 per_last_name,
120 per_middle_name,
121 job_name,
122 supervisor_id,
123 org_name,
124 resource_type,
125 job_id,
126 job_group_id,
127 location_id
128 FROM pa_r_project_resources_v
129 WHERE per_emp_number >= nvl(l_from_emp_num, per_emp_number) and
130 per_emp_number <= nvl(l_to_emp_num, per_emp_number) and
131 organization_id = nvl(l_p_org_id, organization_id)
132 ORDER BY person_id, assignment_start_date;
133 */
134
135 -- End Bug 3086960.
136
137 --This is a cursor for getting resource information from the
138 --view for a resource while performing individual insert.
139 CURSOR CUR_IND_PERSON (l_per_id IN NUMBER)
140 IS
141 SELECT person_id,
142 name,
143 organization_id,
144 assignment_start_date,
145 assignment_end_date,
146 start_date,
147 termination_date,
148 p_type,
149 user_type,
150 location_id,
151 per_start_date,
152 per_end_date,
153 per_emp_number,
154 per_email,
155 per_work_phone,
156 per_business_group_id,
157 per_first_name,
158 per_last_name,
159 per_middle_name,
160 job_name,
161 supervisor_id,
162 org_name,
163 resource_type,
164 job_id,
165 job_group_id
166 FROM pa_r_project_resources_ind_v
167 WHERE person_id = l_per_id
168 /* Start of Changes for Bug 6056112 */
169 UNION
170 SELECT person_id,
171 name,
172 organization_id,
173 assignment_start_date,
174 assignment_end_date,
175 start_date,
176 termination_date,
177 p_type,
178 user_type,
179 location_id,
180 per_start_date,
181 per_end_date,
182 per_emp_number,
183 per_email,
184 per_work_phone,
185 per_business_group_id,
186 per_first_name,
187 per_last_name,
188 per_middle_name,
189 job_name,
190 supervisor_id,
191 org_name,
192 resource_type,
193 job_id,
194 job_group_id
195 FROM pa_r_project_resources_ind_t_v
196 WHERE person_id = l_per_id
197 /* End of Changes for Bug 6056112 */
198 order by person_id, assignment_start_date;
199
200 --------------------------------------------------------------------------------------------------------------
201 -- This procedure prints the text which is being passed as the input
202 -- Input parameters
203 -- Parameters Type Required Description
204 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
205 -- Out parameters
206 ----------------------------------------------------------------------------------------------------------------
207 PROCEDURE log_message (p_log_msg IN VARCHAR2)
208 IS
209 BEGIN
210 --dbms_output.put_line('log: ' || p_log_msg);
211 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
212 pa_debug.write('start_crm_workflow: ' || 'Resource Pull', 'log: ' || p_log_msg, 3);
213 END IF;
214 NULL;
215 END log_message;
216
217 /* --------------------------------------------------------------------
218 FUNCTION: Max_Date
219 PURPOSE: This function compares the dates passed in and returns
220 the latest of these dates back to the calling function.
221 This function is called with the following IN parameters:
222 p_latest_start_date : Latest Start Date of the person
223 in all period of services he/she
224 has in HR.
225 p_earliest_utl_start_date : Earliest Date to start keeping
226 Utilization records. This value
227 is from profile PA_UTL_START_DATE
228 p_calander_start_date : The start date of the CRM calendar.
229 If all the IN Parameters are NULL, it will pass back
230 sysdate.
231 -------------------------------------------------------------------- */
232 FUNCTION Max_Date(p_latest_start_date IN DATE,
233 p_earliest_utl_start_date IN DATE,
234 p_calander_start_date IN DATE)
235 RETURN DATE
236 IS
237 l_latest_start_date DATE;
238 l_earliest_utl_start_date DATE;
239 l_calander_start_date DATE;
240 l_max_start_date DATE := sysdate;
241 BEGIN
242
243 IF p_latest_start_date is not null THEN
244 l_max_start_date := p_latest_start_date;
245 END IF;
246
247 IF p_earliest_utl_start_date is not null THEN
248 IF p_earliest_utl_start_date > l_max_start_date THEN
249 l_max_start_date := p_earliest_utl_start_date;
250 END IF;
251 END IF;
252
253 IF l_calander_start_date is not null THEN
254 IF l_calander_start_date > l_max_start_date THEN
255 l_max_start_date := l_calander_start_date;
256 END IF;
257 END IF;
258
259 IF l_max_start_date is null THEN
260 l_max_start_date := sysdate;
261 END IF;
262
263 RETURN l_max_start_date;
264
265 END Max_Date;
266
267 /* --------------------------------------------------------------------
268 FUNCTION: Validate_Person
269 PURPOSE: This function gives an error message based on the setup
270 not done for the person due to which the person cannot be
271 pulled in as a resource.
272 This API is called from CREATE_RESOURCE API only when that
273 API is called in single resource mode.
274 -------------------------------------------------------------------- */
275 Procedure Validate_Person(p_person_id IN NUMBER)
276 IS
277 l_assignment_id NUMBER;
278 l_job_id NUMBER;
279 l_person_type VARCHAR2(30);
280
281 /* Bug#2683266-Commented the cursor get_person_type and added cursor validate_person_type
282
283 cursor get_person_type
284 is
285 select person_id
286 from per_people_f per,
287 per_person_types ptype
288 where per.person_id = p_person_id
289 and per.person_type_id = ptype.person_type_id
290 and (ptype.system_person_type = 'EMP'
291 OR ptype.system_person_type = 'EMP_APL');
292
293 End of comment for bug#2683266 */
294
295 /* New cursor validate_person_type added for bug#2683266 */
296
297 cursor validate_person_type
298 is
299 select person_id
300 from per_all_people_f per /* per_people_f per Commented for bug 2983491 , Added per_all_people_f for it*/
301 where per.person_id = p_person_id
302 and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y');
303
304 cursor get_active_assignment
305 is
306 select asgn.assignment_id
307 from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
308 per_assignment_status_types status,
309 (select person_id, actual_termination_date from per_periods_of_service
310 union all
311 select person_id, actual_termination_date
312 from per_periods_of_placement) po
313 where asgn.person_id = p_person_id
314 and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
315 and asgn.person_id = po.person_id
316 and po.person_id = p_person_id
317 and asgn.assignment_status_type_id = status.assignment_status_type_id
318 and status.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
319 and asgn.assignment_type in ('E', 'C') --Added for 2911451
320 and rownum = 1;
321
322 cursor get_primary_assignment
323 is
324 select asgn.assignment_id
325 from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
326 (select person_id, actual_termination_date from per_periods_of_service
327 union all
328 select person_id, actual_termination_date
329 from per_periods_of_placement) po
330 where asgn.person_id = p_person_id
331 and asgn.primary_flag = 'Y'
332 and asgn.assignment_type in ('E', 'C') --Added for 2911451
333 and po.person_id = p_person_id
334 and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
335 and asgn.person_id = po.person_id
336 -- and pos.period_of_service_id = asgn.period_of_service_id -- FP M CWK
337 and rownum =1;
338
339 cursor get_job_on_assignment
340 is
341 select asgn.job_id
342 from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
343 (select person_id, actual_termination_date from per_periods_of_service
344 union all
345 select person_id, actual_termination_date
346 from per_periods_of_placement) po
347 where asgn.person_id = p_person_id
348 and asgn.primary_flag = 'Y'
349 and asgn.assignment_type in ('E', 'C')
350 and po.person_id = p_person_id
351 and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
352 and asgn.person_id = po.person_id
353 -- and pos.period_of_service_id = asgn.period_of_service_id -- FP M CWK
354 and asgn.job_id is not null
355 and rownum = 1;
356
357
358 BEGIN
359 /* Bug#2683266 - Changed get_person_type cursor to validate_person_type in code below */
360
361 OPEN validate_person_type;
362 FETCH validate_person_type into l_person_type;
363 IF validate_person_type%NOTFOUND THEN
364 CLOSE validate_person_type;
365 PA_UTILS.Add_Message( p_app_short_name => 'PA'
366 ,p_msg_name => 'PA_INVALID_PERSON_TYPE');
367
368 ELSE
369 CLOSE validate_person_type;
370 OPEN get_active_assignment;
371 FETCH get_active_assignment into l_assignment_id;
372 IF get_active_assignment%NOTFOUND THEN
373 CLOSE get_active_assignment;
374 PA_UTILS.Add_Message( p_app_short_name => 'PA'
375 ,p_msg_name => 'PA_NO_ACTIVE_ASSIGNMENT');
376 ELSE
377 CLOSE get_active_assignment;
378 OPEN get_primary_assignment;
379 FETCH get_primary_assignment into l_assignment_id;
380 IF get_primary_assignment%NOTFOUND THEN
381 CLOSE get_primary_assignment;
382 PA_UTILS.Add_Message( p_app_short_name => 'PA'
383 ,p_msg_name => 'PA_NO_PRIMARY_ASSIGNMENT');
384 ELSE
385 CLOSE get_primary_assignment;
386 OPEN get_job_on_assignment;
387 FETCH get_job_on_assignment into l_job_id;
388 IF get_job_on_assignment%NOTFOUND THEN
389 CLOSE get_job_on_assignment;
390 PA_UTILS.Add_Message( p_app_short_name => 'PA'
391 ,p_msg_name => 'PA_NO_JOB_ON_ASSIGNMENT');
392 ELSE
393 CLOSE get_job_on_assignment;
394 PA_UTILS.Add_Message( p_app_short_name => 'PA'
395 ,p_msg_name => 'PA_RS_INVALID_SETUP');
396 END IF;
397 END IF;
398 END IF;
399 END IF;
400 END Validate_Person;
401
402
403 /*Procedure : CRM Insert
404 This procedure checks if the resource exists in CRM and inserts the resource into CRM by calling the CRM public API jtf_rs_resource.create_resource if it does not exist.
405 It also inserts the calendar for the resource in CRM. If the resource exists the procedure checks to see if the calendar in CRM has been end dated and if so inserts the new calendar.
406 The procedure also checks for internal resources having multiple assignments who might have a different calendar for each assignment. If calendar is not present in hr_organization_information the calendar_id is got from profile option.*/
407
408 PROCEDURE INSERT_INTO_CRM(
409 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
410 P_PERSON_ID IN JTF_RS_RESOURCE_extns.SOURCE_id%TYPE,
411 P_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
412 P_START_DATE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
413 P_ASSIGNMENT_START_DATE IN DATE,
414 P_ASSIGNMENT_END_DATE IN DATE,
415 P_CALENDAR_ID IN NUMBER,
416 P_COUNT IN NUMBER,
417 X_CRM_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE , --File.Sql.39 bug 4440895
418 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
419 P_START_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_start_date%TYPE,
420 P_END_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_end_date%TYPE,
421 P_SOURCE_NUMBER IN pa_r_project_resources_ind_v.per_emp_number%TYPE,
422 P_SOURCE_JOB_TITLE IN pa_r_project_resources_ind_v.job_name%TYPE,
423 P_SOURCE_EMAIL IN pa_r_project_resources_ind_v.per_email%TYPE,
424 P_SOURCE_PHONE IN pa_r_project_resources_ind_v.per_work_phone%TYPE,
425 P_SOURCE_ADDRESS1 IN HR_LOCATIONS.ADDRESS_LINE_1%TYPE,
426 P_SOURCE_ADDRESS2 IN HR_LOCATIONS.ADDRESS_LINE_2%TYPE,
427 P_SOURCE_ADDRESS3 IN HR_LOCATIONS.ADDRESS_LINE_3%TYPE,
428 P_SOURCE_CITY IN HR_LOCATIONS.TOWN_OR_CITY%TYPE,
429 P_SOURCE_POSTAL_CODE IN HR_LOCATIONS.POSTAL_CODE%TYPE,
430 P_SOURCE_COUNTRY IN HR_LOCATIONS.COUNTRY%TYPE,
431 P_SOURCE_MGR_ID IN pa_r_project_resources_ind_v.supervisor_id%TYPE,
432 P_SOURCE_MGR_NAME IN PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
433 P_SOURCE_BUSINESS_GRP_ID IN pa_r_project_resources_ind_v.per_business_group_id%TYPE,
434 P_SOURCE_BUSINESS_GRP_NAME IN pa_r_project_resources_ind_v.org_name%TYPE,
435 P_SOURCE_FIRST_NAME IN pa_r_project_resources_ind_v.per_first_name%TYPE,
436 P_SOURCE_LAST_NAME IN pa_r_project_resources_ind_v.per_last_name%TYPE,
437 P_SOURCE_MIDDLE_NAME IN pa_r_project_resources_ind_v.per_middle_name%TYPE)
438 IS
439 l_insert VARCHAR2(1) := 'N';
440 l_end_date_insert VARCHAR2(1) := 'N'; -- Bug 4668272
441
442 --For inserting into jtf_cal_resource_assign.
443 l_cal_resource_assign_id JTF_CAL_RESOURCE_ASSIGN.CAL_RESOURCE_ASSIGN_ID%TYPE;
444 l_msg_count NUMBER;
445 l_msg_data VARCHAR2(100);
446 --Used to check return status in jtf_cal_resource_assign_pkg.insert_row
447 l_error VARCHAR2(1):= 'N';
448
449 l_crm_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
450 l_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
451
452 l_resource_type_code JTF_CAL_RESOURCE_ASSIGN.RESOURCE_TYPE_CODE%TYPE;
453 l_category JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE := P_CATEGORY;
454 l_start_date JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE := P_START_DATE_ACTIVE;
455 l_person_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE := P_PERSON_ID;
456
457 l_check_dup_id varchar2(1);
458 l_calendar_id NUMBER := TO_NUMBER(P_CALENDAR_ID);
459
460 l_rowid ROWID;
461 l_user_id NUMBER;
462
463 l_assignment_start_date DATE := P_ASSIGNMENT_START_DATE;
464 l_assignment_end_date DATE := P_ASSIGNMENT_END_DATE;
465
466 l_latest_start_date DATE;
467 l_earliest_utl_start_date DATE;
468 l_calander_start_date DATE;
469
470 -- For checking dates info in jtf_cal_resource_assign
471 l_min_start_date DATE;
472 l_max_end_date DATE; -- Bug 4668272
473 l_populate_jtf_res_cal VARCHAR2(1) := 'Y'; -- Bug 6411422
474
475 /*
476 * The following cursor is for CRM workaround for bug 1944726
477 */
478 CURSOR c_per_active IS
479 SELECT PER.PERSON_ID
480 FROM PER_ALL_PEOPLE_F PER, /*for bug 2983491 Replaced PER_PEOPLE_F PER , */
481 PER_ALL_ASSIGNMENTS_F ASGN
482 WHERE ASGN.PERSON_ID = PER.PERSON_ID
483 AND ASGN.PRIMARY_FLAG = 'Y'
484 AND ASGN.ASSIGNMENT_TYPE in ('E', 'C')
485 AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
486 AND TRUNC(SYSDATE) BETWEEN ASGN.EFFECTIVE_START_DATE AND ASGN.EFFECTIVE_END_DATE
487 AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
488 AND PER.PERSON_ID = p_person_id;
489
490 CURSOR CUR_CRMID(l_person_id in jtf_rs_resource_extns.source_id%type,
491 l_category in jtf_rs_resource_extns.category%type)
492 IS
493 select resource_id
494 from jtf_rs_resource_extns
495 where source_id = l_person_id
496 and category = l_category ;
497
498 CURSOR c_dup_resource_assign_id (l_cal_resource_assign_id IN jtf_cal_resource_assign.cal_resource_assign_id%type)
499 IS
500 SELECT 'X'
501 FROM jtf_cal_resource_assign
502 WHERE cal_resource_assign_id = l_cal_resource_assign_id;
503
504 CURSOR c_jtf_cal(l_cal_resource_assign_id in jtf_cal_resource_assign.cal_resource_assign_id%type)
505 IS
506 SELECT rowid
507 FROM jtf_cal_resource_assign
508 WHERE cal_resource_assign_id = l_cal_resource_assign_id;
509
510 CURSOR c_user_id(l_employee_id NUMBER)
511 IS
512 SELECT user_id
513 FROM fnd_user
514 WHERE employee_id = l_employee_id;
515
516 CURSOR cur_crm_cal(l_crm_resource_id in NUMBER)
517 IS
518 SELECT 'Y'
519 from jtf_cal_resource_assign
520 where resource_id = l_crm_resource_id
521 and resource_type_code = l_resource_type_code
522 and l_assignment_start_date between start_date_time and nvl(end_date_time, to_date('31/12/4712', 'DD/MM/YYYY'))
523 and primary_calendar_flag = 'Y';
524
525 -- Bug 4668272 - Added cursor to handle the case of the assignment
526 -- end date changing to be later than what it was.
527 CURSOR cur_crm_cal_end(l_crm_resource_id in NUMBER)
528 IS
529 SELECT 'Y'
530 from jtf_cal_resource_assign
531 where resource_id = l_crm_resource_id
532 and resource_type_code = l_resource_type_code
533 and l_assignment_end_date between start_date_time and nvl(end_date_time, to_date('31/12/4712', 'DD/MM/YYYY'))
534 and primary_calendar_flag = 'Y';
535
536 CURSOR cur_crm_check_date
537 IS
538 SELECT MIN(start_date_time)
539 from jtf_cal_resource_assign
540 where resource_id = l_crm_resource_id
541 and resource_type_code = l_resource_type_code
542 and primary_calendar_flag = 'Y'
543 and start_date_time between l_assignment_start_date and l_assignment_end_date;
544
545 -- Bug 4668272 - Added cursor to handle the case of the assignment
546 -- end date changing to be later than what it was. This is parallel
547 -- to the case that is already handled where the assignment
548 -- start date is changed to be earlier that it was. We want
549 -- to insert another record for the last end date + 1 to the
550 -- new end date. NOTE: In the case where the end date is changed to
551 -- be earlier than it was, we don't do anything since a calendar
552 -- already exists for the time period - we ignore the fact that
553 -- the end date is after the real HR end date since functionally
554 -- it doesn't affect anything. Similarly, when the start date is
555 -- changed to be later than it was we don't do anything.
556
557 CURSOR cur_crm_chk_end_date IS
558 SELECT MAX(end_date_time)
559 from jtf_cal_resource_assign
560 where resource_id = l_crm_resource_id
561 and resource_type_code = l_resource_type_code
562 and primary_calendar_flag = 'Y';
563 -- and end_date_time between l_assignment_start_date
564 -- and l_assignment_end_date;
565
566 l_cal_exist VARCHAR(1);
567 l_cal_exist_end VARCHAR(1);
568 l_fde VARCHAR(1); -- Added for bug4690946
569
570 BEGIN
571 PA_DEBUG.set_err_stack('Insert_into_CRM');
572
573 pa_debug.g_err_stage := 'Log: Start of Insert_into_CRM procedure';
574 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
575 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
576 END IF;
577 log_message('Inside insert_into_crm procedure');
578
579 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
580
581 open cur_crmid(l_person_id,l_category);
582 fetch cur_crmid into l_crm_resource_id;
583 close cur_crmid;
584
585 open c_user_id(p_person_id);
586 fetch c_user_id into l_user_id;
587 close c_user_id;
588
589 ----------------------------------
590 -- Check calendar
591 -- l_calendar_id must not be null
592 ----------------------------------
593
594 --Get Calendar_Id from Profile Options if NULL
595 if l_calendar_id is null then
596 pa_debug.g_err_stage := 'Log: Calendar_id null, get from Profile options';
597 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
598 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
599 END IF;
600 l_calendar_id := fnd_profile.value_specific('PA_PRM_DEFAULT_CALENDAR');
601 end if;
602
603 --Calendar_Id from Profile Options is NULL
604 --raise error
605 if l_calendar_id is null then
606 pa_debug.g_err_stage := 'Log: Calendar id null from Profile options - Invalid Calendar Setup';
607 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
608 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
609 END IF;
610 PA_UTILS.Add_Message(
611 p_app_short_name => 'PA'
612 ,p_msg_name => 'PA_INVALID_CAL_SETUP');
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 RAISE FND_API.G_EXC_ERROR;
615 end if;
616
617 -------------------------------------------
618 -- Resource does not exist in CRM
619 -- Get a crm_resource_id for this resource
620 -------------------------------------------
621 IF (l_crm_resource_id is null) THEN
622 --Call CRM public API
623 pa_debug.g_err_stage := 'Log: Resource does not exist in CRM, calling CRM Public API';
624 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
625 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
626 END IF;
627 log_message('Resource does not exist in CRM, call CRM Public API');
628
629 OPEN c_per_active ;
630 FETCH c_per_active INTO l_person_id;
631
632 /* Added for bug 4690946 */
633 BEGIN
634 SELECT 'Y'
635 INTO l_fde
636 FROM dual
637 WHERE EXISTS (
638 SELECT 'Y'
639 FROM per_all_people_f
640 WHERE person_id = l_person_id
641 AND (current_employee_flag = 'Y' OR
642 current_npw_flag = 'Y')
643 AND trunc(effective_start_date) > trunc(sysdate));
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 l_fde := 'N';
647 END;
648 /* End bug 4690946 */
649
650 ---------------------------------------------
651 -- Future-dated employee, start the workflow
652 ---------------------------------------------
653 IF c_per_active%NOTFOUND AND l_fde = 'Y' THEN
654 -- second condition added for 4690946
655
656 --This person is a future dated employee
657 --Workflow is launched below to pull this person on his start date
658 --This must be done only once for all assignments of this person
659
660 if G_p_crmwf_id <> p_person_id then
661
662 start_crm_workflow( p_person_id => p_person_id,
663 p_assignment_start_date => p_assignment_start_date,
664 x_return_status => x_return_status,
665 x_error_message_code => l_msg_data);
666
667 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
668 log_message('Error in CRM Public API');
669 pa_debug.g_err_stage := 'Log: Error in CRM Workaround Workflow API';
670 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
671 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
672 END IF;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 CLOSE c_per_active;
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678 G_p_crmwf_id := p_person_id;
679 end if;
680
681 ELSE
682
683 jtf_rs_resource_pub.create_resource(
684 P_API_VERSION => 1.0,
685 P_INIT_MSG_LIST => FND_API.G_FALSE,
686 P_COMMIT => FND_API.G_FALSE,
687 P_CATEGORY => l_category,
688 P_SOURCE_ID => l_person_id,
689 P_ADDRESS_ID => NULL,
690 P_CONTACT_ID => NULL,
691 P_MANAGING_EMP_ID => NULL,
692 P_MANAGING_EMP_NUM => NULL,
693 P_START_DATE_ACTIVE => l_start_date,
694 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
695 P_TIME_ZONE => NULL,
696 P_COST_PER_HR => NULL,
697 P_PRIMARY_LANGUAGE => NULL,
698 P_SECONDARY_LANGUAGE => NULL,
699 P_SUPPORT_SITE_ID => NULL,
700 P_IES_AGENT_LOGIN => NULL,
701 P_SERVER_GROUP_ID => NULL,
702 P_INTERACTION_CENTER_NAME => NULL,
703 P_ASSIGNED_TO_GROUP_ID => NULL,
704 P_COST_CENTER => NULL,
705 P_CHARGE_TO_COST_CENTER => NULL,
706 P_COMP_CURRENCY_CODE => NULL,
707 P_COMMISSIONABLE_FLAG => NULL,
708 P_HOLD_REASON_CODE => NULL,
709 P_HOLD_PAYMENT => NULL,
710 P_COMP_SERVICE_TEAM_ID => NULL,
711 P_USER_ID => NULL, -- change to NULL from l_user_id
712 P_TRANSACTION_NUMBER => NULL,
713 X_RETURN_STATUS => x_return_status,
714 X_MSG_COUNT => l_msg_count,
715 X_MSG_DATA => l_msg_data,
716 X_RESOURCE_ID => l_crm_resource_id,
717 X_RESOURCE_NUMBER => l_resource_number,
718 P_RESOURCE_NAME => p_name,
719 P_SOURCE_NAME => p_name,
720 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
721 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
722 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
723 P_SOURCE_PHONE => P_SOURCE_PHONE,
724 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
725 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
726 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
727 P_SOURCE_CITY => P_SOURCE_CITY,
728 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
729 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
730 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
731 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
732 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
733 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
734 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
735 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
736 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME );
737
738 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
739 log_message('Error in CRM Public API');
740 pa_debug.g_err_stage := 'Log: Error in CRM Public API';
741 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
742 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
743 END IF;
744
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 CLOSE c_per_active;
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749
750 pa_debug.g_err_stage := 'Log: After call to CRM Public API. CRM_RESOURCE_ID := ' || to_char(l_crm_resource_id);
751 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
752 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
753 END IF;
754 log_message('Out of CRM Public API');
755
756 END IF; /* end if for c_per_active%NOTFOUND */
757 CLOSE c_per_active;
758
759 END IF; /* end if for creating a crm resource id for this person */
760
761
762 --Get the correct resource_type_code based on category
763 pa_debug.g_err_stage := 'Log: Fetch the resource_type_code based on category';
764 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
765 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
766 END IF;
767 log_message('Fetch the resource_type_code based on category');
768
769 IF p_category = 'EMPLOYEE' THEN
770 l_resource_type_code := 'RS_EMPLOYEE';
771 ELSIF p_category = 'PARTY' THEN
772 l_resource_type_code := 'RS_PARTY';
773 ELSIF p_category = 'PARTNER' THEN
774 l_resource_type_code := 'RS_PARTNER';
775 END IF;
776
777
778 ------------------------------------------------------------------
779 -- Now check whether the resource has a calendar assigned
780 -- for this assignment start and end dates
781 -- This check is performed on an existing or a new crm resource.
782 -- Insert calendar appropriately if calendar assignment does not
783 -- exist yet.
784 ------------------------------------------------------------------
785
786
787 -- Changing the start date login for Bug 1697261
788 -- l_assignment_start_date := sysdate;
789
790 --------------------------------------------------------------------------
791 -- First, get the appropriate earliest start date for the calendar record
792 --------------------------------------------------------------------------
793 BEGIN
794 SELECT max(DATE_START)
795 INTO l_latest_start_date
796 FROM
797 (select person_id, date_start from per_periods_of_service
798 union all
799 select person_id, date_start from per_periods_of_placement) po
800 WHERE po.person_id = l_person_id
801 AND trunc(po.date_start) <= trunc(p_assignment_start_date); -- Added for bug 4465862;
802 EXCEPTION
803 WHEN OTHERS THEN
804 l_latest_start_date := null;
805 END;
806
807 BEGIN
808 -- l_earliest_utl_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY'); /* commenting for For Bug 7304151 */
809 l_earliest_utl_start_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
810 EXCEPTION
811 WHEN OTHERS THEN
812 l_earliest_utl_start_date := null;
813 END;
814
815 log_message('PA_UTL_START_DATE value: ' || l_earliest_utl_start_date);
816
817 BEGIN
818 SELECT start_date_active
819 INTO l_calander_start_date
820 FROM JTF_CALENDARS_B -- change to base table - bug 4350758
821 WHERE calendar_id = l_calendar_id;
822 EXCEPTION
823 WHEN OTHERS THEN
824 l_calander_start_date := null;
825 END;
826
827
828 l_assignment_start_date := Max_Date(l_latest_start_date,
829 l_earliest_utl_start_date,
830 l_calander_start_date);
831
832 log_message('l_start_date from max_date = ' || l_assignment_start_date);
833 log_message('p_start_date from parameter = ' || p_assignment_start_date);
834
835 if (trunc(p_assignment_start_date) > trunc(l_assignment_start_date)) then
836 log_message('Not the first HR assignment for the resource');
837 l_assignment_start_date := p_assignment_start_date;
838 end if;
839
840 l_assignment_end_date := p_assignment_end_date;
841
842
843 ---------------------------------------------------
844 --Second, check if calendar exists for the resource
845 --of the assignment start date
846 ---------------------------------------------------
847 -- hr_utility.trace('l_assignment_end_date : ' || l_assignment_end_date);
848 -- hr_utility.trace('l_assignment_start_date : ' || l_assignment_start_date);
849 open cur_crm_cal(l_crm_resource_id);
850 fetch cur_crm_cal into l_cal_exist;
851 close cur_crm_cal;
852
853 -- hr_utility.trace('22222 crm');
854 -- hr_utility.trace('l_cal_exist : ' || l_cal_exist);
855
856 -- Assign a calendar only for resource which has crm resource id
857 -- To take care of future resources (no crm resource id yet)
858 IF (l_cal_exist is null and l_crm_resource_id is not null) THEN
859
860 ------------------------------------------------------------------------
861 -- Calendar does not exist as of the assignment start date
862 -- This cursor checks if there is an already existing calendar assigned
863 -- to this resource, with a different start date but within the
864 -- assignment dates. Get this start date.
865 ------------------------------------------------------------------------
866 open cur_crm_check_date;
867 fetch cur_crm_check_date into l_min_start_date;
868 close cur_crm_check_date;
869
870 if(l_min_start_date IS NULL) then
871
872 -----------------------------------------------------------------------------
873 -- When the value of l_min_start_date is NULL, this means that no calendar
874 -- exist for this resource during the assignment time period.
875 -- Will need to do insert into jtf_cal_resource_assign.
876 -- This resource is a new crm resource.
877 -----------------------------------------------------------------------------
878 log_message('L_CAL_EXIST is null: ' || to_char(l_crm_resource_id));
879 log_message('Set insert = Y');
880 l_insert := 'Y';
881
882
883 else
884 -----------------------------------------------------------------------
885 -- Calendar exists for this resource but with a different start date.
886 -- The earliest start date is less than l_min_start_date value.
887 -- Will need to insert another row of calendar assignment for this
888 -- resource from earliest start date to l_min_start_date - 1
889 -----------------------------------------------------------------------
890
891 log_message('Start Date is earlier for : ' || to_char(l_crm_resource_id));
892 log_message('Old start date : ' || to_char(l_min_start_date, 'DD-MON-RR'));
893 log_message('New start date : ' || to_char(l_assignment_start_date, 'DD-MON-RR'));
894 log_message('Set insert = Y');
895 l_insert := 'Y';
896
897 -- Set assignment end date to the old_start_date - 1
898 l_assignment_end_date := l_min_start_date - 1;
899
900 end if;
901
902 END IF; /* end of cal is null check */
903
904
905 -- Begin Bug 4668272
906 open cur_crm_cal_end(l_crm_resource_id);
907 fetch cur_crm_cal_end into l_cal_exist_end;
908 close cur_crm_cal_end;
909
910 -- hr_utility.trace('22222 crm');
911 -- hr_utility.trace('l_cal_exist : ' || l_cal_exist_end);
912
913 -- Assign a calendar only for resource which has crm resource id
914 -- To take care of future resources (no crm resource id yet)
915 IF (l_cal_exist_end is null and l_crm_resource_id is not null) THEN
916
917 ----------------------------------------------------------------
918 -- Calendar does not exist as of the assignment end date
919 -- This cursor checks if there is an already existing calendar
920 -- assigned
921 -- to this resource, with a different end date but within the
922 -- assignment dates. Get this end date.
923 ----------------------------------------------------------------
924 open cur_crm_chk_end_date;
925 fetch cur_crm_chk_end_date into l_max_end_date;
926 close cur_crm_chk_end_date;
927
928 -- hr_utility.trace('l_max_end_date IS : ' || l_max_end_date);
929 -- Bug 4668272
930 l_end_date_insert := 'N';
931 IF l_max_end_date IS NULL THEN
932 -- No calendar record exists for this person, so insert
933 -- a new one.
934 l_end_date_insert := 'Y';
935 ELSIF (l_max_end_date between l_assignment_start_date AND
936 l_assignment_end_date) AND
937 trunc(l_max_end_date) <> trunc(l_assignment_end_date) THEN
938 -- hr_utility.trace('bug fixed case');
939 -- hr_utility.trace('l_max_end_date IS : ' || l_max_end_date);
940 -- hr_utility.trace('l_assignment_end_date IS : ' || l_assignment_end_date);
941
942 -- This is the case where the new end date is after the
943 -- existing end date - eg new: Dec 31 old: Dec 15.
944 -- Insert new record from Dec 16 to Dec 31.
945 -- Set assignment start date to the old end date + 1
946 l_assignment_start_date := l_max_end_date + 1;
947 l_end_date_insert := 'Y';
948
949 -- hr_utility.trace('l_assignment_start_date IS : ' || l_assignment_start_date);
950 ELSE
951 -- New end date is earlier than old one. Eg:
952 -- New: Dec 15, Old: Dec 31
953 -- Calendar already exists for this time so do nothing.
954 l_end_date_insert := 'N';
955
956 END IF;
957
958 END IF; /* end of cal end is null check */
959 -- End Bug 4668272
960
961 ---------------------------------------------------
962 --Assign the l_crm_resource_id to the out parameter
963 ---------------------------------------------------
964 x_crm_resource_id := l_crm_resource_id;
965
966 ---------------------------------------------------------
967 --Insert calendar details into jtf_cal_resource_assign
968 --For a totally new crm resource or to insert a partial
969 --period if start date changes to an earlier start date
970 --for an existing resource
971 ---------------------------------------------------------
972 -- IF (l_insert = 'Y') THEN
973 -- Bug 4668272
974 -- hr_utility.trace('l_end_date_insert IS : ' || l_end_date_insert);
975 IF (l_insert = 'Y') OR (l_end_date_insert = 'Y') THEN
976
977 log_message('Begin of insert into jtf_cal_resource_assign');
978 log_message('l_assignment_start ' || to_char(l_assignment_start_date, 'DD-MON-RR'));
979 log_message('l_assignment_end ' || to_char(l_assignment_end_date, 'DD-MON-RR'));
980
981 --Unique cal_resource_assign_id from sequence
982 LOOP
983 select jtf_cal_resource_assign_s.nextval
984 into l_cal_resource_assign_id
985 from dual;
986 OPEN c_dup_resource_assign_id(l_cal_resource_assign_id);
987 FETCH c_dup_resource_assign_id INTO l_check_dup_id;
988 EXIT WHEN c_dup_resource_assign_id%NOTFOUND;
989 CLOSE c_dup_resource_assign_id;
990 END LOOP;
991 CLOSE c_dup_resource_assign_id;
992
993 pa_debug.g_err_stage := 'Log: Selected unique cal_resource_assign_id';
994 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
995 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
996 END IF;
997 log_message('Selected unique cal_resource_assign_id');
998
999 pa_debug.g_err_stage := 'Log: User CRM API to Insert calendar into jtf_cal_resource_assign from '|| to_char(l_assignment_start_date,'DD-MON-RR')||' to '|| to_char(l_assignment_end_date,'DD-MON-RR');
1000 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1001 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1002 END IF;
1003 log_message('Insert calendar into jtf_cal_resource_assign');
1004
1005 --Added code to use CRM Table Handler API instead of direct insert
1006
1007 IF( l_assignment_start_date > P_ASSIGNMENT_END_DATE ) THEN -- Bug 6411422: IF condition added
1008 l_populate_jtf_res_cal := 'N';
1009 END IF ;
1010 IF( l_populate_jtf_res_cal = 'Y' ) THEN -- Bug 6411422: IF condition added
1011
1012 jtf_cal_resource_assign_pkg.insert_row(
1013 X_ERROR => l_error,
1014 X_ROWID => l_rowid,
1015 X_CAL_RESOURCE_ASSIGN_ID => l_cal_resource_assign_id,
1016 X_OBJECT_VERSION_NUMBER => 1,
1017 X_ATTRIBUTE5 => null,
1018 X_ATTRIBUTE6 => null,
1019 X_ATTRIBUTE7 => null,
1020 X_ATTRIBUTE8 => null,
1021 X_ATTRIBUTE9 => null,
1022 X_ATTRIBUTE10 => null,
1023 X_ATTRIBUTE11 => null,
1024 X_ATTRIBUTE12 => null,
1025 X_ATTRIBUTE13 => null,
1026 X_ATTRIBUTE14 => null,
1027 X_ATTRIBUTE15 => null,
1028 X_ATTRIBUTE_CATEGORY => null,
1029 X_START_DATE_TIME => l_assignment_start_date,
1030 X_END_DATE_TIME => l_assignment_end_date,
1031 X_CALENDAR_ID => l_calendar_id,
1032 X_RESOURCE_ID => l_crm_resource_id,
1033 X_RESOURCE_TYPE_CODE => l_resource_type_code,
1034 X_PRIMARY_CALENDAR_FLAG => 'Y',
1035 X_ATTRIBUTE1 => null,
1036 X_ATTRIBUTE2 => null,
1037 X_ATTRIBUTE3 => null,
1038 X_ATTRIBUTE4 => null,
1039 X_CREATION_DATE => sysdate,
1040 X_CREATED_BY => G_user_id,
1041 X_LAST_UPDATE_DATE => sysdate,
1042 X_LAST_UPDATED_BY => G_user_id,
1043 X_LAST_UPDATE_LOGIN => G_login_id
1044 );
1045
1046 /* The jtf table handler returns N or Y based on
1047 if the function call was a success or failure */
1048 IF NOT (l_error = 'N') THEN
1049 log_message('Error in CRM Table Handler API for INSERT_ROW');
1050 pa_debug.g_err_stage := 'Log: Error in CRM Table Handler API for INSERT_ROW';
1051 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1052 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1053 END IF;
1054 x_return_status := FND_API.G_RET_STS_ERROR;
1055 RAISE FND_API.G_EXC_ERROR;
1056 END IF;
1057
1058
1059 OPEN c_jtf_cal(l_cal_resource_assign_id);
1060 FETCH c_jtf_cal into l_rowid;
1061 IF (c_jtf_cal%NOTFOUND) THEN
1062 log_message('c_jtf_cal not found');
1063 CLOSE c_jtf_cal;
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 RAISE fnd_api.g_exc_error;
1066 END IF;
1067 CLOSE c_jtf_cal;
1068
1069
1070 --------------------------
1071 --check calendar validity
1072 --------------------------
1073 log_message('Checking calendar validity');
1074 pa_schedule_utils.check_calendar(
1075 P_JTF_RESOURCE_ID => l_crm_resource_id,
1076 P_START_DATE => l_assignment_start_date,
1077 P_END_DATE => l_assignment_end_date,
1078 X_RETURN_STATUS => x_return_status,
1079 X_MSG_COUNT => l_msg_count,
1080 X_MSG_DATA => l_msg_data );
1081
1082 log_message('return status for calendar validity: ' || x_return_status);
1083
1084 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1085 pa_debug.g_err_stage := 'Log: Calendar is not valid';
1086 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1087 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1088 END IF;
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090 RAISE FND_API.G_EXC_ERROR;
1091 END IF;
1092
1093 pa_debug.g_err_stage := 'Log: After insert into jtf_cal_resource_assign';
1094 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1095 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1096 END IF;
1097
1098 log_message('End of insert into jtf_cal_resource_assign');
1099
1100 END IF;
1101 END IF; -- Bug 6411422
1102
1103 pa_debug.g_err_stage := 'Log: End of Insert_into_CRM procedure';
1104 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1105 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1106 END IF;
1107 log_message('End of insert_into_crm procedure');
1108
1109 PA_DEBUG.Reset_Err_Stack;
1110
1111 EXCEPTION
1112 WHEN fnd_api.g_exc_error THEN
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114 X_CRM_RESOURCE_ID := NULL ; -- 4537865 RESET OUT PARAM
1115 WHEN OTHERS THEN
1116 X_CRM_RESOURCE_ID := NULL ; -- 4537865 RESET OUT PARAM
1117 -- Set the exception Message and the stack
1118 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Insert_into_CRM'
1119 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1121 RAISE;
1122 END INSERT_INTO_CRM;
1123
1124 /*Procedure : PA Insert
1125 This procedure inserts records into pa_resources and
1126 pa_resource_txn_attributes table. This procedure calls the table
1127 handler package PA_RESOURCE_PKG.INSERT_ROW1 and PA_RESOURCE_PKG.INSERT_ROW2 . */
1128
1129 PROCEDURE INSERT_INTO_PA(
1130 P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131 P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132 X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133 P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134 P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135 P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136 P_NAME IN PA_RESOURCES.NAME%TYPE,
1137 P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138 X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139 IS
1140 l_resource_txn_attribute_id PA_RESOURCE_TXN_ATTRIBUTES.RESOURCE_TXN_ATTRIBUTE_ID%TYPE;
1141 l_check_dup_id VARCHAR2(1);
1142 l_rowid ROWID;
1143 l_check_char VARCHAR2(1);
1144
1145 -- added for bug 3921534
1146 l_unit_of_measure pa_resources.unit_of_measure%type;
1147 l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;
1148 l_track_as_labor_flag pa_resources.track_as_labor_flag%type;
1149
1150 CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)
1151 IS
1152 SELECT 'X'
1153 FROM pa_resources
1154 WHERE resource_id = l_resource_id;
1155
1156 CURSOR c_dup_txn_attribute_id(l_resource_txn_attribute_id IN
1157 pa_resource_txn_attributes.
1158 resource_txn_attribute_id%type)
1159 IS
1160 SELECT 'X'
1161 FROM pa_resource_txn_attributes
1162 WHERE resource_txn_attribute_id = l_resource_txn_attribute_id;
1163
1164 CURSOR c_pa_resources( l_rowid IN ROWID ) IS
1165 SELECT 'Y'
1166 FROM pa_resources
1167 WHERE ROWID = l_rowid;
1168
1169 CURSOR c_pa_resource_txn_attributes( l_rowid IN ROWID ) IS
1170 SELECT 'Y'
1171 FROM pa_resource_txn_attributes
1172 WHERE ROWID = l_rowid;
1173
1174 BEGIN
1175 PA_DEBUG.set_err_stack('Insert_into_PA');
1176 pa_debug.g_err_stage := 'Log: Start of Insert_into_PA procedure';
1177 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1178 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1179 END IF;
1180
1181 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
1182
1183 --Fetch unique resource_id from sequence.
1184 LOOP
1185 Select pa_resources_s.nextval
1186 into x_resource_id
1187 from dual;
1188
1189 OPEN c_dup_resource_id(x_resource_id);
1190 FETCH c_dup_resource_id INTO l_check_dup_id;
1191 EXIT WHEN c_dup_resource_id%NOTFOUND;
1192 CLOSE c_dup_resource_id;
1193 END LOOP;
1194 CLOSE c_dup_resource_id;
1195
1196 --Fetch unique resource_txn_attribute_id from sequence.
1197 LOOP
1198 select pa_resource_txn_attributes_s.nextval
1199 into l_resource_txn_attribute_id
1200 from dual;
1201
1202 OPEN c_dup_txn_attribute_id(l_resource_txn_attribute_id);
1203 FETCH c_dup_txn_attribute_id INTO l_check_dup_id;
1204 EXIT WHEN c_dup_txn_attribute_id%NOTFOUND;
1205 CLOSE c_dup_txn_attribute_id;
1206 END LOOP;
1207 CLOSE c_dup_txn_attribute_id;
1208
1209 -- added for bug 3921534
1210 l_unit_of_measure := null;
1211 l_rollup_quantity_flag := null;
1212 l_track_as_labor_flag := null;
1213 if p_resource_type_id = 101 then
1214 -- Commented the following code for Perf fix 4902403
1215 -- SQL ID 14906035
1216
1217 -- select unit_of_measure
1218 -- ,rollup_quantity_flag
1219 -- ,track_as_labor_flag
1220 -- into l_unit_of_measure
1221 -- ,l_rollup_quantity_flag
1222 -- ,l_track_as_labor_flag
1223 -- from pa_employees_res_v
1224 -- where rownum = 1;
1225
1226 -- Included ths code for Perf fix 4902403
1227 l_unit_of_measure := 'HOURS';
1228 l_rollup_quantity_flag := 'N';
1229 l_track_as_labor_flag := 'Y';
1230 end if;
1231
1232 --Insert into pa_resources table
1233 pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';
1234 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1235 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1236 END IF;
1237 log_message('Calling insert_row1 procedure to do the actual inserts into PA_RESOURCES table');
1238
1239 PA_RESOURCE_PKG.INSERT_ROW1 (
1240 X_ROWID => l_rowid,
1241 X_RESOURCE_ID => x_resource_id,
1242 X_NAME => p_name,
1243 X_RESOURCE_TYPE_ID => p_resource_type_id,
1244 X_JTF_RESOURCE_ID => p_crm_resource_id,
1245 X_START_DATE_ACTIVE => p_start_date,
1246 X_END_DATE_ACTIVE => p_end_date,
1247 X_UNIT_OF_MEASURE => l_unit_of_measure, -- added for bug 3921534
1248 X_ROLLUP_QUANTITY_FLAG => l_rollup_quantity_flag, -- added for bug 3921534
1249 X_TRACK_AS_LABOR_FLAG => l_track_as_labor_flag, -- added for bug 3921534
1250 X_REQUEST_ID => G_request_id,
1251 X_PROGRAM_ID => G_program_id,
1252 X_PROGRAM_UPDATE_DATE => SYSDATE,
1253 X_PROGRAM_APPLICATION_ID => G_application_id,
1254 X_LAST_UPDATE_BY => G_user_id,
1255 X_LAST_UPDATE_DATE => SYSDATE,
1256 X_CREATION_DATE => SYSDATE,
1257 X_CREATED_BY => G_user_id,
1258 X_LAST_UPDATE_LOGIN => G_login_id,
1259 X_RETURN_STATUS => x_return_status);
1260
1261 pa_debug.g_err_stage := 'Log: After Insert_row1 procedure';
1262 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1263 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1264 END IF;
1265 log_message('After Insert Row 1 Procedure');
1266
1267 pa_debug.g_err_stage := 'Log: Calling Insert_row2 to insert into PA_RESOURCE_TXN_ATTRIBUTES table';
1268 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1269 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1270 END IF;
1271 log_message('Call insert_row2 procedure to do the actual inserts into PA_RESOURCE_TXN_ATTRIBUTES table');
1272
1273 --Insert into pa_resource_txn_attributes.
1274 PA_RESOURCE_PKG.INSERT_ROW2 (
1275 X_ROWID => l_rowid,
1276 X_RESOURCE_TXN_ATTRIBUTE_ID => l_resource_txn_attribute_id,
1277 X_RESOURCE_ID => x_resource_id,
1278 X_PERSON_ID => p_person_id,
1279 X_PARTY_ID => p_party_id,
1280 X_RESOURCE_FORMAT_ID => 5,
1281 X_REQUEST_ID => G_request_id,
1282 X_PROGRAM_ID => G_program_id,
1283 X_PROGRAM_UPDATE_DATE => SYSDATE,
1284 X_PROGRAM_APPLICATION_ID => G_application_id,
1285 X_LAST_UPDATE_BY => G_user_id,
1286 X_LAST_UPDATE_DATE => SYSDATE,
1287 X_CREATION_DATE => SYSDATE,
1288 X_CREATED_BY => G_user_id,
1289 X_LAST_UPDATE_LOGIN => G_login_id,
1290 X_RETURN_STATUS => x_return_status ) ;
1291
1292 pa_debug.g_err_stage := 'Log: After Insert_row2 procedure';
1293 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1294 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1295 END IF;
1296 log_message('End of insert_row2 procedure');
1297
1298 pa_debug.g_err_stage := 'Log: End of Insert_into_PA procedure';
1299 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1300 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1301 END IF;
1302 log_message('End of insert_into_pa procedure');
1303
1304 PA_DEBUG.Reset_Err_Stack;
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 -- Set the exception Message and the stack
1308 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Insert_into_PA'
1309 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1311 -- 4537865
1312 x_resource_id := NULL ;
1313 RAISE;
1314
1315 END INSERT_INTO_PA;
1316
1317 /*Procedure : Check_OU
1318 This procedure check if the default OU implements projects or not.
1319 Also if the default OU is NULL for a resource
1320 then gives an expected error. */
1321
1322 PROCEDURE CHECK_OU(
1323 P_DEFAULT_OU IN PA_RESOURCES_DENORM.RESOURCE_ORG_ID%TYPE,
1324 P_EXP_ORG IN VARCHAR2,
1325 X_EXP_OU OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1326 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1327 IS
1328 l_valid VARCHAR2(1);
1329 l_code VARCHAR2(100);
1330
1331 BEGIN
1332 PA_DEBUG.set_err_stack('Check_OU');
1333
1334 pa_debug.g_err_stage := 'Log: Start of Check_OU procedure';
1335 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1336 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1337 END IF;
1338 log_message('Start of check_ou procedure');
1339
1340 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
1341
1342 IF (p_default_OU IS NOT NULL) THEN
1343 --Check if OU is Expenditure OU
1344 log_message('Calling Check_Exp_OU');
1345 pa_hr_update_api.check_exp_OU(
1346 p_org_id => p_default_ou
1347 ,x_return_status => l_valid
1348 ,x_error_message_code => l_code);
1349 log_message('After Check_Exp_OU');
1350
1351 IF (l_valid = fnd_api.g_ret_sts_success) THEN
1352 --If OU is Exp OU
1353 x_exp_ou := 'Y';
1354 x_return_status := fnd_api.g_ret_sts_success;
1355 ELSE
1356 --If OU is not Exp OU
1357 --The code below is commented because for singlr org
1358 --implementations the default OU need not be
1359 --expenditure OU
1360 x_exp_ou := 'N';
1361 END IF; --IF l_valid
1362 ELSE
1363 IF (p_exp_org = 'YES') THEN
1364 --If OU is null and resource belongs to Exp Hier
1365 --The code below is commented because for singlr org
1366 --implementations the default OU need not be
1367 --expenditure OU
1368 x_exp_ou := 'N';
1369
1370 END IF;
1371 END IF;
1372
1373 pa_debug.g_err_stage := 'Log: End of Check_OU procedure';
1374 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1375 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1376 END IF;
1377 log_message('End of check_ou procedure');
1378
1379 PA_DEBUG.Reset_Err_Stack;
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 -- Set the exception Message and the stack
1383 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Check_OU'
1384 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1386 -- 4537865
1387 x_exp_ou := NULL ;
1388 raise;
1389
1390 END CHECK_OU;
1391
1392
1393 /*Procedure : Create Internal Resource
1394 This procedure is the main procedure called by the create_resource
1395 procedure for internal resources. First the procedure checks to see if
1396 the resource exists in PA, if it does not then it calls the CRM
1397 procedure to perform CRM checks, calls the Insert_into_Pa and
1398 Insert_into_Orgs procedures to insert resource into PA. Depending on
1399 the number of primary active assignments the Insert_into_Orgs
1400 procedure is called that many times.
1401
1402 If the resource does exist in PA, the procedure checks to see if CRM
1403 calendar has been changed and respectively process the CRM
1404 details. */
1405
1406 PROCEDURE CREATE_INTERNAL_RESOURCE(
1407 P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1408 P_NAME IN PA_RESOURCES.NAME%TYPE,
1409 P_ORGANIZATION_ID IN PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
1410 P_ASSIGNMENT_START_DATE IN DATE,
1411 P_ASSIGNMENT_END_DATE IN DATE,
1412 P_START_DATE IN DATE,
1413 P_DEFAULT_OU IN NUMBER,
1414 P_CALENDAR_ID IN NUMBER,
1415 P_SYSTEM_TYPE IN PER_PERSON_TYPES.SYSTEM_PERSON_TYPE%TYPE,
1416 P_USER_TYPE IN PER_PERSON_TYPES.USER_PERSON_TYPE%TYPE,
1417 P_RES_EXISTS IN VARCHAR2,
1418 P_COUNT IN NUMBER,
1419 P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1420 X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1421 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1422 P_START_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_start_date%TYPE,
1423 P_END_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_end_date%TYPE,
1424 P_SOURCE_NUMBER IN pa_r_project_resources_ind_v.per_emp_number%TYPE,
1425 P_SOURCE_JOB_TITLE IN pa_r_project_resources_ind_v.job_name%TYPE,
1426 P_SOURCE_EMAIL IN pa_r_project_resources_ind_v.per_email%TYPE,
1427 P_SOURCE_PHONE IN pa_r_project_resources_ind_v.per_work_phone%TYPE,
1428 P_SOURCE_ADDRESS1 IN HR_LOCATIONS.ADDRESS_LINE_1%TYPE,
1429 P_SOURCE_ADDRESS2 IN HR_LOCATIONS.ADDRESS_LINE_2%TYPE,
1430 P_SOURCE_ADDRESS3 IN HR_LOCATIONS.ADDRESS_LINE_3%TYPE,
1431 P_SOURCE_CITY IN HR_LOCATIONS.TOWN_OR_CITY%TYPE,
1432 P_SOURCE_POSTAL_CODE IN HR_LOCATIONS.POSTAL_CODE%TYPE,
1433 P_SOURCE_COUNTRY IN HR_LOCATIONS.COUNTRY%TYPE,
1434 P_SOURCE_MGR_ID IN pa_r_project_resources_ind_v.supervisor_id%TYPE,
1435 P_SOURCE_MGR_NAME IN PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
1436 P_SOURCE_BUSINESS_GRP_ID IN pa_r_project_resources_ind_v.per_business_group_id%TYPE,
1437 P_SOURCE_BUSINESS_GRP_NAME IN pa_r_project_resources_ind_v.org_name%TYPE,
1438 P_SOURCE_FIRST_NAME IN pa_r_project_resources_ind_v.per_first_name%TYPE,
1439 P_SOURCE_LAST_NAME IN pa_r_project_resources_ind_v.per_last_name%TYPE,
1440 P_SOURCE_MIDDLE_NAME IN pa_r_project_resources_ind_v.per_middle_name%TYPE)
1441 IS
1442 l_resource_type_code PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE := 'EMPLOYEE';
1443 l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1444
1445 l_res_id pa_resource_txn_attributes.resource_id%type;
1446 l_org_id NUMBER;
1447 l_start DATE;
1448 l_end DATE;
1449
1450 l_check VARCHAR2(1) := 'N';
1451 l_jtf_id NUMBER := null;
1452 l_cal_id NUMBER := TO_NUMBER(P_CALENDAR_ID);
1453
1454 x_crm_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
1455 x_error_message_code varchar2(1000);
1456
1457 l_valid VARCHAR2(1);
1458 l_code VARCHAR2(100);
1459
1460 CURSOR c_jtf_id_exist(l_res_id IN NUMBER)
1461 IS
1462 SELECT jtf_resource_id
1463 FROM pa_resources
1464 WHERE resource_id = l_res_id;
1465
1466 BEGIN
1467 PA_DEBUG.set_err_stack('Create_Internal_Resource');
1468
1469 pa_debug.g_err_stage := 'Log: Start of Create_Internal_Resource procedure';
1470 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1471 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1472 END IF;
1473 log_message('Inside create_internal_resource procedure');
1474
1475 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
1476
1477 --Check to see if resource exists in PA
1478 IF (p_res_exists = 'NOT EXISTS') THEN
1479
1480 pa_debug.g_err_stage := 'Log: Resource does not exist in PA';
1481 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1482 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1483 END IF;
1484 log_message('Resource does not exist');
1485
1486 pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure to check if resource exists in CRM';
1487 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1488 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1489 END IF;
1490 log_message('Call insert_into_crm procedure to check if resource exists in CRM');
1491
1492 --pa_debug.g_err_stage := 'Log: P_COUNT = ' || to_char(p_count);
1493 --pa_debug.write_file('LOG',pa_debug.g_err_stage);
1494 --Call Insert_into_crm procedure
1495 insert_into_crm(
1496 P_CATEGORY => p_resource_type,
1497 P_PERSON_ID => p_person_id,
1498 P_NAME => p_name,
1499 P_START_DATE => p_start_date,
1500 P_ASSIGNMENT_START_DATE => p_assignment_start_date,
1501 P_ASSIGNMENT_END_DATE => p_assignment_end_date,
1502 P_CALENDAR_ID => l_cal_id,
1503 P_COUNT => p_count,
1504 X_CRM_RESOURCE_ID => x_crm_resource_id,
1505 X_RETURN_STATUS => x_return_status,
1506 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
1507 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
1508 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
1509 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
1510 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
1511 P_SOURCE_PHONE => P_SOURCE_PHONE,
1512 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
1513 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
1514 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
1515 P_SOURCE_CITY => P_SOURCE_CITY,
1516 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
1517 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
1518 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
1519 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
1520 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
1521 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
1522 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
1523 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
1524 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
1525
1526 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1527 RAISE FND_API.G_EXC_ERROR;
1528 END IF;
1529
1530 pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure ';
1531 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1532 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1533 END IF;
1534 log_message('Out of insert_into_crm procedure');
1535 log_message('x_crm_resource_id = ' || x_crm_resource_id);
1536 log_message('p_person_id = ' || p_person_id);
1537
1538 /*--Get the resource_type_code inorder to get the resource_type_id
1539 pa_debug.g_err_stage := 'Log: Get the resource_type_code based on the user_type';
1540 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1541 log_message('Get the resource_type_code based on the user_type');
1542
1543 IF p_user_type = 'Employee' THEN
1544 l_resource_type_code := 'EMPLOYEE';
1545 ELSIF p_user_type = 'Contractor' THEN
1546 l_resource_type_code := 'CONTRACTOR';
1547 END IF;*/
1548
1549 --fetch the resource_type_id
1550 pa_debug.g_err_stage := 'Log: Get the resource_type_id based on the resource_type_code';
1551 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1552 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1553 END IF;
1554 log_message('Get the resource_type_id from the resource_type_code');
1555
1556 select resource_type_id into l_resource_type_id
1557 from pa_resource_types
1558 where resource_type_code = l_resource_type_code ;
1559
1560 pa_debug.g_err_stage := 'Log: After getting the resource_type_id based on the resource_type_code';
1561 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1562 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1563 END IF;
1564
1565 --This check is to check the number of assignments the resource has
1566 if (p_person_id <> G_p_id) then
1567 pa_debug.g_err_stage := 'Log: Calling Insert_into_PA procedure';
1568 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1569 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1570 END IF;
1571 log_message('Calling insert_into_pa');
1572
1573 INSERT_INTO_PA(
1574 P_RESOURCE_TYPE_ID => l_resource_type_id,
1575 P_CRM_RESOURCE_ID => x_crm_resource_id,
1576 X_RESOURCE_ID => x_resource_id,
1577 P_START_DATE => p_start_date,
1578 P_PERSON_ID => p_person_id,
1579 P_NAME => p_name,
1580 X_RETURN_STATUS => x_return_status );
1581
1582 pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure';
1583 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1584 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1585 END IF;
1586 log_message('Out of insert_into_pa');
1587
1588 G_p_id := p_person_id;
1589 --Assign the current person_id to the G_p_id so that
1590 --in the next run the insert_into_pa will not be
1591 --called.
1592
1593 end if;
1594
1595 --Below fixed for Bug 1555424
1596 select resource_id into l_res_id
1597 from pa_resource_txn_attributes
1598 where person_id = p_person_id
1599 and rownum = 1; -- added for bug 3086960.
1600
1601 x_resource_id := l_res_id;
1602
1603 ELSE
1604 --If resource exists in PA
1605 pa_debug.g_err_stage := 'Log: Resource exists in PA';
1606 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1607 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1608 END IF;
1609 log_message('For already existing resource: Resource is already in PA');
1610
1611 --Check to see if CRM calendar is end dated.
1612 --Call insert_into_crm
1613
1614 pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure to check if resource exists in CRM';
1615 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1616 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1617 END IF;
1618 log_message('Call insert_into_crm procedure to check if resource exists in CRM');
1619
1620 insert_into_crm(
1621 P_CATEGORY => p_resource_type,
1622 P_PERSON_ID => p_person_id,
1623 P_NAME => p_name,
1624 P_START_DATE => p_start_date,
1625 P_ASSIGNMENT_START_DATE => p_assignment_start_date,
1626 P_ASSIGNMENT_END_DATE => p_assignment_end_date,
1627 P_CALENDAR_ID => l_cal_id,
1628 P_COUNT => 1,
1629 X_CRM_RESOURCE_ID => x_crm_resource_id,
1630 X_RETURN_STATUS => x_return_status,
1631 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
1632 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
1633 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
1634 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
1635 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
1636 P_SOURCE_PHONE => P_SOURCE_PHONE,
1637 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
1638 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
1639 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
1640 P_SOURCE_CITY => P_SOURCE_CITY,
1641 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
1642 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
1643 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
1644 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
1645 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
1646 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
1647 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
1648 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
1649 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
1650 log_message('After CRM '|| x_return_status);
1651 pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure';
1652 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1653 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1654 END IF;
1655 log_message(' Out of insert_into_crm procedure');
1656 log_message('x_crm_resource_id = ' || x_crm_resource_id);
1657 log_message('p_person_id = ' || p_person_id);
1658
1659 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1660 RAISE FND_API.G_EXC_ERROR;
1661 END IF;
1662
1663 pa_debug.g_err_stage := 'Log: Get resource_id for person_id';
1664 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1665 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1666 END IF;
1667 log_message('Get resource_id for person_id');
1668
1669 --Get resource_id from PA for the resource
1670 select resource_id into l_res_id
1671 from pa_resource_txn_attributes
1672 where person_id = p_person_id and rownum=1;
1673
1674 x_resource_id := l_res_id ;
1675
1676 -- Begin Bug 3086960. Added by Sachin.
1677 DECLARE
1678 l_exists VARCHAR2(1);
1679 BEGIN
1680 -- Changed to where exists for bug 4350758
1681 -- no need for rownum when exists is used.
1682 SELECT 'X'
1683 INTO l_exists
1684 FROM dual
1685 WHERE EXISTS (SELECT 'Y'
1686 FROM pa_resource_txn_attributes
1687 WHERE resource_id = x_resource_id
1688 AND person_id <> p_person_id);
1689 -- AND ROWNUM = 1);
1690
1691 ----------------------------------------------------
1692 -- if for the same resource_id, another record
1693 -- with a different person_id exists in the table
1694 -- pa_resource_txn_attributes than raise exception
1695 -- and do nothing
1696 -- person not pulled because of above reason
1697 ----------------------------------------------------
1698 IF SQL%FOUND THEN
1699 RAISE fnd_api.g_exc_error;
1700 END IF;
1701 EXCEPTION
1702 WHEN NO_DATA_FOUND THEN
1703 NULL;
1704 END;
1705 -- End Bug 3086960
1706
1707 --To update jtf_resource_id for already existing
1708 --resource in Projects
1709 OPEN c_jtf_id_exist(l_res_id);
1710 FETCH c_jtf_id_exist INTO l_jtf_id;
1711 CLOSE c_jtf_id_exist;
1712
1713 IF (l_jtf_id is null) or (l_jtf_id <> x_crm_resource_id) THEN
1714 pa_debug.g_err_stage := 'Log: PA_RESOURCES.JTF_RESOURCE_ID does not exist or not equal to the CRM resource_id';
1715 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1716 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1717 END IF;
1718
1719 update pa_resources
1720 set jtf_resource_id = x_crm_resource_id,
1721 request_id = G_request_id,
1722 program_id = G_program_id,
1723 program_update_date = sysdate,
1724 program_application_id = G_application_id,
1725 last_update_date = sysdate,
1726 last_updated_by = G_user_id,
1727 last_update_login = G_login_id
1728 where resource_id = l_res_id;
1729
1730 pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure';
1731 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1732 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1733 END IF;
1734 insert_into_crm(
1735 P_CATEGORY => p_resource_type,
1736 P_PERSON_ID => p_person_id,
1737 P_NAME => p_name,
1738 P_START_DATE => p_start_date,
1739 P_ASSIGNMENT_START_DATE => p_assignment_start_date,
1740 P_ASSIGNMENT_END_DATE => p_assignment_end_date,
1741 P_CALENDAR_ID => l_cal_id,
1742 P_COUNT => 1,
1743 X_CRM_RESOURCE_ID => x_crm_resource_id,
1744 X_RETURN_STATUS => x_return_status,
1745 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
1746 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
1747 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
1748 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
1749 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
1750 P_SOURCE_PHONE => P_SOURCE_PHONE,
1751 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
1752 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
1753 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
1754 P_SOURCE_CITY => P_SOURCE_CITY,
1755 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
1756 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
1757 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
1758 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
1759 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
1760 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
1761 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
1762 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
1763 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
1764
1765 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766 RAISE FND_API.G_EXC_ERROR;
1767 END IF;
1768
1769 pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure';
1770 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1771 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1772 END IF;
1773 log_message(' Out of insert_into_crm procedure');
1774 log_message('x_crm_resource_id = ' || x_crm_resource_id);
1775 log_message('p_person_id = ' || p_person_id);
1776 log_message('After CRM '|| x_return_status);
1777 END IF;
1778 END IF;
1779 pa_debug.g_err_stage := 'Log: End of Create_Internal_Resource procedure';
1780 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1781 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1782 END IF;
1783 log_message('End of create_internal_resource procedure');
1784
1785 PA_DEBUG.Reset_Err_Stack;
1786 EXCEPTION
1787 WHEN FND_API.G_EXC_ERROR THEN
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789 -- 4537865
1790 x_resource_id := NULL ;
1791 WHEN OTHERS THEN
1792 -- Set the exception Message and the stack
1793 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Create_Internal_Resource'
1794 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1796 -- 4537865
1797 x_resource_id := NULL ;
1798
1799 RAISE;
1800 END CREATE_INTERNAL_RESOURCE;
1801
1802
1803 /*Procedure : Create External Resource
1804 This procedure is the main procedure called by the create_resource
1805 procedure for external resources. First the procedure checks to see if
1806 the resource exists in PA, if it does not then it calls the insert_into_pa
1807 to insert the resource into pa_resources and pa_resource_txn_attributes
1808 If the resource does exist in PA, the procedure just returns the resource_id
1809 of the external resource */
1810
1811 PROCEDURE CREATE_EXTERNAL_RESOURCE(
1812 P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
1813 P_RESOURCE_TYPE IN PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE,
1814 X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1815 X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1816 IS
1817 l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1818 l_valid VARCHAR2(1) := 'N';
1819 l_res_exists VARCHAR2(20);
1820 l_party_id PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE := P_PARTY_ID;
1821 l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
1822 l_name PA_RESOURCES.NAME%TYPE;
1823 l_start_date DATE;
1824 l_end_date DATE;
1825 l_return_status VARCHAR2(1);
1826
1827 BEGIN
1828 PA_DEBUG.set_err_stack('Create_External_Resource');
1829
1830 pa_debug.g_err_stage := 'Log: Start of Create_External_Resource procedure';
1831 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1832 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1833 END IF;
1834 log_message('Inside create_external_resource procedure');
1835
1836 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
1837
1838 --to get the resource type id for code HZ_PARTY
1839 select resource_type_id into l_resource_type_id
1840 from pa_resource_types
1841 where resource_type_code = p_resource_type;
1842
1843 --Call check_res_exists procedure
1844 pa_resource_utils.check_res_exists(
1845 P_PARTY_ID => l_party_id,
1846 X_VALID => l_valid,
1847 X_RETURN_STATUS => l_return_status);
1848
1849 IF (l_valid = 'Y') THEN
1850 l_res_exists := 'EXISTS';
1851 log_message('** external resource already exists in PA **');
1852 ELSE
1853 l_res_exists := 'NOT EXISTS';
1854 log_message('** external resource does not exist in PA **');
1855 END IF;
1856
1857 --insert into PA only if the record does not exists yet in the PA tables
1858 --CRM resource Id will be null for external resource
1859
1860 IF l_res_exists = 'NOT EXISTS' THEN
1861
1862 -- get party_name, start date, end date
1863 /* bug2498092 - need to truncate the party name from hz_parties
1864 because HZ_PARTIES.party_name is VARCHAR2(360) while
1865 pa_resources.name is only VARCHAR2(100) */
1866 /*Bug 3612182:Modified the substr to substrb*/
1867 select substrb(party_name,1,100), start_date, end_date
1868 into l_name, l_start_date, l_end_date
1869 from pa_party_resource_details_v
1870 where party_id = l_party_id;
1871
1872 pa_debug.g_err_stage := 'Log: Before Insert_into_PA procedure for external people';
1873 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1874 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1875 END IF;
1876 --call insert into PA
1877
1878 INSERT_INTO_PA(
1879 P_RESOURCE_TYPE_ID => l_resource_type_id,
1880 P_CRM_RESOURCE_ID => null,
1881 X_RESOURCE_ID => x_resource_id,
1882 P_START_DATE => l_start_date,
1883 P_END_DATE => l_end_date,
1884 P_PARTY_ID => l_party_id,
1885 P_NAME => l_name,
1886 X_RETURN_STATUS => x_return_status );
1887
1888 pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure for external people';
1889 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1890 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1891 END IF;
1892 log_message('** done with insert into PA for external people **');
1893
1894 ELSE
1895 pa_debug.g_err_stage := 'Log: External resource already in PA - retrieve resource_id';
1896 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1897 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1898 END IF;
1899
1900 log_message('** external resource already in PA - retrieve resource_id **');
1901 --Get resource_id from PA for the resource
1902 select resource_id into l_res_id
1903 from pa_resource_txn_attributes
1904 where party_id = l_party_id;
1905
1906 x_resource_id := l_res_id ;
1907
1908 END IF;
1909
1910
1911 pa_debug.g_err_stage := 'Log: End of Create_External_Resource procedure';
1912 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1913 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1914 END IF;
1915 log_message('End of create_external_resource procedure');
1916
1917 PA_DEBUG.Reset_Err_Stack;
1918 EXCEPTION
1919 WHEN FND_API.G_EXC_ERROR THEN
1920 x_return_status := FND_API.G_RET_STS_ERROR;
1921 -- 4537865
1922 x_resource_id := NULL ;
1923 WHEN OTHERS THEN
1924 -- Set the exception Message and the stack
1925 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Create_External_Resource'
1926 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1928 -- 4537865
1929 x_resource_id := NULL ;
1930 RAISE;
1931 END CREATE_EXTERNAL_RESOURCE;
1932
1933
1934 /*Procedure : Create Resource
1935 This is the private procedure that will be called by the public API
1936 which in turn calls the above procedures.*/
1937
1938 PROCEDURE CREATE_RESOURCE (
1939 P_COMMIT IN VARCHAR2,
1940 P_VALIDATE_ONLY IN VARCHAR2,
1941 P_INTERNAL IN VARCHAR2,
1942 P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1943 P_INDIVIDUAL IN VARCHAR2,
1944 P_CHECK_RESOURCE IN VARCHAR2,
1945 P_SCHEDULED_MEMBER_FLAG IN VARCHAR2,
1946 P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1947 P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
1948 P_FROM_EMP_NUM IN VARCHAR2,
1949 P_TO_EMP_NUM IN VARCHAR2,
1950 P_ORGANIZATION_ID IN NUMBER,
1951 P_REFRESH IN VARCHAR2,
1952 P_PULL_TERM_RES IN VARCHAR2 DEFAULT 'N',
1953 P_TERM_RANGE_DATE IN DATE DEFAULT NULL,
1954 P_PERSON_TYPE IN VARCHAR2 DEFAULT 'ALL',
1955 P_START_DATE IN DATE DEFAULT NULL, -- Bug 5337454
1956 -- Added parameters for PJR Resource Pull Enhancements - Bug 5130414
1957 P_SELECTION_OPTION IN VARCHAR2 DEFAULT NULL,
1958 P_ORG_STR_VERSION_ID IN NUMBER DEFAULT NULL,
1959 P_START_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
1960 -- End of parameters added for PJR Resource Pull Enhancements - Bug 5130414
1961 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1962 X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE) --File.Sql.39 bug 4440895
1963
1964 IS
1965 L_API_VERSION CONSTANT NUMBER := 1.0;
1966 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
1967 -- If no date specified, default to previous year.
1968 l_term_range_date DATE := trunc(nvl(p_term_range_date, ADD_MONTHS(trunc(sysdate), -12)));
1969
1970 l_resource_type JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE := P_RESOURCE_TYPE;
1971
1972 x_resource_number NUMBER;
1973
1974 l_name PA_RESOURCES.NAME%TYPE;
1975 l_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1976 l_assignment_start_date DATE;
1977 l_assignment_end_date DATE;
1978 l_start_date DATE;
1979 l_default_OU NUMBER;
1980 l_calendar_id NUMBER;
1981 l_system_type PER_PERSON_TYPES.SYSTEM_PERSON_TYPE%TYPE;
1982 l_user_type PER_PERSON_TYPES.USER_PERSON_TYPE%TYPE;
1983 l_res_exists VARCHAR2(20);
1984 l_exists VARCHAR2(1) := 'N';
1985 l_exp_org VARCHAR2(10);
1986 l_job_utilization VARCHAR2(1) := 'N';
1987 l_job_schedulable VARCHAR2(1) := 'N';
1988 l_job_id NUMBER;
1989 l_id NUMBER := P_PERSON_ID;
1990
1991 l_internal VARCHAR2(1) := P_INTERNAL;
1992 l_person_id NUMBER := P_PERSON_ID;
1993 l_individual VARCHAR2(1) := P_INDIVIDUAL ;
1994 l_party_id NUMBER := P_PARTY_ID;
1995 i NUMBER := 0;
1996
1997 ---- new variables for parameterized resource pull
1998 l_from_emp_num VARCHAR2(30) := P_FROM_EMP_NUM;
1999 l_to_emp_num VARCHAR2(30) := P_TO_EMP_NUM;
2000 l_p_org_id NUMBER := P_ORGANIZATION_ID;
2001 l_refresh VARCHAR2(5) := P_REFRESH;
2002 l_denorm_yes VARCHAR2(1) := 'N';
2003 -- new variables for enhanced parameterization in resource pull - Bug 5130414
2004 l_selection_option VARCHAR(20) := P_SELECTION_OPTION;
2005 l_org_str_version_id NUMBER := P_ORG_STR_VERSION_ID;
2006 l_start_organization_id NUMBER := P_START_ORGANIZATION_ID;
2007 ----
2008
2009 x_msg_count NUMBER;
2010 x_msg_data VARCHAR2(1000);
2011 l_counter NUMBER;
2012 l_msg_index_out NUMBER;
2013 l_max_count NUMBER := 1;
2014 x_error_message_code VARCHAR2(1000);
2015 x_exp_ou VARCHAR2(1);
2016 l_res_found VARCHAR2(1) := 'N';
2017 l_valid VARCHAR2(1) := 'N';
2018 l_return_status VARCHAR2(1000);
2019 l_supervisor_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
2020 l_address1 HR_LOCATIONS.ADDRESS_LINE_1%TYPE;
2021 l_address2 HR_LOCATIONS.ADDRESS_LINE_2%TYPE;
2022 l_address3 HR_LOCATIONS.ADDRESS_LINE_3%TYPE;
2023 l_city HR_LOCATIONS.TOWN_OR_CITY%TYPE;
2024 l_postal_code HR_LOCATIONS.POSTAL_CODE%TYPE;
2025 l_country HR_LOCATIONS.COUNTRY%TYPE;
2026 l_end_date DATE;
2027 G_person_id NUMBER := 0;
2028 l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
2029 -- l_org_type VARCHAR2(20); --Bug 4363092
2030 l_crm_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
2031 l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
2032 l_per_success VARCHAR2(1);
2033 l_asgmt_success VARCHAR2(1) := 'N';
2034
2035 -- variables for fix in message stack in individual pull
2036 l_msg_stack_num_old NUMBER := 0;
2037 l_msg_stack_num_new NUMBER := 0;
2038 l_at_least_one_success VARCHAR2(1) := 'N';
2039
2040 l_populate_denorm_flag VARCHAR2(1) := 'Y'; -- Bug 6411422
2041 l_per_start_date DATE; -- Bug 6411422
2042 l_prof_date DATE; -- Bug 6411422
2043
2044 -- Bug 3086960. Added dynamic SQL. Sachin
2045 sql_cursor NUMBER;
2046 l_rows NUMBER;
2047 l_sel_clause VARCHAR2(2000);
2048 l_from_clause VARCHAR2(2000);
2049 l_where_clause VARCHAR2(2000);
2050 l_stmt VARCHAR2(20000);
2051
2052 l_invol_term VARCHAR2(1); --bug 5683340
2053
2054 /*Added 2 new params for bug 4087022*/
2055 l_excep VARCHAR2(1) := 'N' ;
2056 l_resource_id PA_RESOURCES.RESOURCE_ID%TYPE;
2057
2058 PERSON_ERROR_EXCEPTION EXCEPTION;
2059 TERM_DATE_EXCEPTION EXCEPTION;
2060
2061 CURSOR CUR_CRMID(l_person_id in jtf_rs_resource_extns.source_id%type,
2062 l_resource_type in jtf_rs_resource_extns.category%type)
2063 IS
2064 select resource_id
2065 from jtf_rs_resource_extns
2066 where source_id = l_person_id
2067 and category = l_resource_type;
2068
2069 --MOAC Changes : Bug 4363092: Commenting this cursor as now As R12 will have multi org setup only
2070 /*
2071 CURSOR check_org_type IS
2072 select decode(substr(USERENV('CLIENT_INFO'),1,1),
2073 ' ', NULL,
2074 substr(USERENV('CLIENT_INFO'),1,10)) org from dual; */
2075
2076 /* Commenting starts for Bug 6263517 */
2077 /* Modifying existing cursor to get max terminated date (from both past terminated
2078 or future terminated date values )
2079 CURSOR get_max_end_date_term(p_person_id IN NUMBER) IS
2080 select max(assignment_end_date)
2081 from pa_r_project_resources_term_v res
2082 where res.person_id = p_person_id; */
2083 /* Commenting ends for Bug 6263517 */
2084
2085 /* Modified cursor starts for Bug 6263517 */
2086 CURSOR get_max_end_date_term(p_person_id IN NUMBER) IS
2087 SELECT MAX (asgn_end_date) FROM
2088 (select max(res.assignment_end_date) asgn_end_date
2089 from pa_r_project_resources_v res
2090 where res.person_id = p_person_id
2091 and res.assignment_end_date IS NOT NULL
2092 UNION
2093 select max(res.assignment_end_date) ass_end_date
2094 from pa_r_project_resources_term_v res
2095 where res.person_id = p_person_id
2096 and res.assignment_end_date IS NOT NULL ) ;
2097 /* Modified cursor ends for Bug 6263517 */
2098
2099 --MOAC Changes bug 4363092 - removed nvl used with org_id
2100 CURSOR get_max_asgmt_end_date IS
2101 select max(assignment_end_date)
2102 from pa_r_project_resources_ind_v res
2103 , hr_organization_information org_info
2104 , pa_all_organizations org
2105 where res.person_id = l_person_id
2106 and res.organization_id = org_info.organization_id
2107 and org_info.org_information_context = 'Exp Organization Defaults'
2108 and res.organization_id = org.organization_id
2109 and org.pa_org_use_type = 'EXPENDITURES'
2110 and org.inactive_date is null
2111 and (org.organization_id,org.org_id) = (
2112 select org1.organization_id, org1.org_id
2113 from pa_all_organizations org1
2114 where org1.pa_org_use_type = 'EXPENDITURES'
2115 and org1.inactive_date is null
2116 and org1.organization_id = org.organization_id
2117 and rownum = 1 );
2118
2119 CURSOR check_res_denorm IS
2120 select 'Y'
2121 from pa_resources_denorm
2122 where person_id = l_person_id
2123 and rownum=1;
2124
2125 /*Cursor added for Bug 6943551*/
2126 CURSOR cur_denorm_del(p_person_id IN NUMBER ) IS
2127 SELECT prd.person_id,
2128 prd.resource_effective_start_date,
2129 prd.resource_effective_end_date
2130 FROM pa_resources_denorm prd,
2131 per_all_assignments_f paf,
2132 per_assignment_status_types past
2133 WHERE prd.person_id = paf.person_id
2134 AND prd.person_id = p_person_id
2135 AND paf.assignment_status_type_id = past.assignment_status_type_id
2136 AND paf.primary_flag = 'Y'
2137 AND paf.assignment_type in ('E','C')
2138 AND prd.resource_effective_start_date = paf.effective_start_date
2139 AND past.per_system_status in ('SUSP_ASSIGN','SUSP_CWK_ASG')
2140 ORDER BY prd.resource_effective_start_date ;
2141
2142 /*Cursors added for Bug 7336526*/
2143 CURSOR cur_denorm_del_redundant(p_person_id IN NUMBER, l_prof_date IN DATE) IS
2144 SELECT prd.person_id,
2145 prd.resource_effective_start_date,
2146 prd.resource_effective_end_date
2147 FROM pa_resources_denorm prd
2148 WHERE prd.resource_effective_end_date < sysdate
2149 AND prd.resource_effective_end_date < l_prof_date
2150 AND prd.resource_effective_end_date is not null
2151 AND prd.person_id = p_person_id
2152 ORDER BY prd.resource_effective_start_date ;
2153
2154 CURSOR cur_denorm_del_term(l_prof_date IN DATE) IS
2155 SELECT prd.person_id,
2156 prd.assignment_start_date,
2157 prd.assignment_end_date,
2158 prd.organization_id
2159 FROM pa_r_project_resources_term_v prd
2160 WHERE per_end_date < sysdate
2161 AND assignment_end_date < sysdate
2162 AND per_end_date < l_prof_date
2163 AND per_end_date is not null
2164 AND organization_id = l_p_org_id
2165 ORDER BY per_start_date ;
2166
2167
2168 BEGIN
2169
2170 IF p_commit = fnd_api.g_true THEN
2171 SAVEPOINT res_pvt_create_resource;
2172 END IF;
2173
2174 PA_DEBUG.set_err_stack('Create_Resource');
2175
2176 G_p_id := 0;
2177 G_p_crmwf_id := 0;
2178
2179 if ((G_user_id = '-1') or (G_login_id is null)) then
2180 G_user_id := '1014';
2181 G_login_id := '-1';
2182 end if;
2183
2184 X_RETURN_STATUS := fnd_api.g_ret_sts_success;
2185
2186 --If internal resource
2187 IF (l_internal = 'Y' ) THEN
2188 pa_debug.g_err_stage := 'Log: For Internal Resources';
2189 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2190 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2191 END IF;
2192 log_message('For Internal Resources');
2193
2194 ------------------------------------------------------------
2195 -- This is the individual case condition
2196 -- It goes through a loop using pa_r_project_resources_ind_v
2197 ------------------------------------------------------------
2198 IF (l_individual = 'Y') THEN
2199 pa_debug.g_err_stage := 'Log: For a single Resource';
2200 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2201 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2202 END IF;
2203 log_message('For Ind. Resource');
2204
2205 l_msg_stack_num_old := FND_MSG_PUB.Count_Msg;
2206 log_message('Old Number of Stack messages = ' || to_char(l_msg_stack_num_old));
2207
2208 FOR eRec IN cur_ind_person(l_id) LOOP
2209
2210 BEGIN
2211
2212 -- do savepoint for every assignment
2213 -- if error occurs, it will rollback the assignment
2214 IF p_commit = fnd_api.g_true THEN
2215 SAVEPOINT res_pvt_create_resource;
2216 END IF;
2217 log_message('do SAVEPOINT for person:' || eRec.person_id || ' and assignment: ' || eRec.assignment_start_date);
2218
2219 l_res_found := 'Y';
2220 log_message('=========================');
2221
2222 --Getting the latest name of person - bug 3273964
2223 pa_debug.g_err_stage := 'OLD PERSON_NAME ==> ' || eRec.NAME;
2224 IF P_DEBUG_MODE = 'Y' THEN
2225 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2226 END IF;
2227
2228 eRec.NAME := pa_resource_utils.get_person_name_no_date(P_PERSON_ID => eRec.person_id);
2229 pa_debug.g_err_stage := 'NEW PERSON_NAME ==> ' || eRec.NAME;
2230 IF P_DEBUG_MODE = 'Y' THEN
2231 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2232 END IF;
2233
2234 log_message('PERSON_NAME ==> '|| eRec.NAME);
2235 -- End Bug 3273964
2236 --For selecting cursor columns into local variables.
2237 l_person_id := eRec.person_id;
2238 l_name := eRec.name;
2239 l_organization_id := eRec.organization_id;
2240 l_assignment_start_date := eRec.assignment_start_date;
2241 l_assignment_end_date := eRec.assignment_end_date;
2242 l_start_date := eRec.start_date;
2243 l_system_type := eRec.p_type;
2244 l_user_type := eRec.user_type;
2245
2246
2247 ------------------------------------------------------------------------
2248 -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and
2249 -- PA_RESOURCE_TXN_ATTRIBUTES. No need for calendar assignment, timeline
2250 -- generation and we also do not populate the pa_resources_denorm table
2251 ------------------------------------------------------------------------
2252
2253 IF (p_scheduled_member_flag = 'N') THEN
2254
2255 log_message('**** p_scheduled_member_flag = N *****');
2256 --to get the resource type id for code EMPLOYEE: now only handles this resource type
2257 select resource_type_id into l_resource_type_id
2258 from pa_resource_types
2259 where resource_type_code = 'EMPLOYEE';
2260
2261 open cur_crmid(l_person_id,l_resource_type);
2262 fetch cur_crmid into l_crm_resource_id;
2263
2264 IF cur_crmid%NOTFOUND THEN
2265 l_crm_resource_id := null;
2266 END IF;
2267 close cur_crmid;
2268
2269 --Call check_res_exists procedure
2270 pa_resource_utils.check_res_exists(
2271 P_PERSON_ID => l_person_id,
2272 X_VALID => l_valid,
2273 X_RETURN_STATUS => l_return_status);
2274
2275 IF (l_valid = 'Y') THEN
2276 l_res_exists := 'EXISTS';
2277 log_message('** resource already exists in PA **');
2278 ELSE
2279 l_res_exists := 'NOT EXISTS';
2280 log_message('** resource does not exist in PA **');
2281 END IF;
2282
2283 --insert into PA only if the record does not exists yet in the PA tables
2284 IF l_res_exists = 'NOT EXISTS' THEN
2285 pa_debug.g_err_stage := 'Log: Before Insert_into_PA procedure for non-scheduled';
2286 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2287 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2288 END IF;
2289 --call insert into PA
2290 INSERT_INTO_PA(
2291 P_RESOURCE_TYPE_ID => l_resource_type_id,
2292 P_CRM_RESOURCE_ID => l_crm_resource_id,
2293 X_RESOURCE_ID => x_resource_id,
2294 P_START_DATE => l_start_date,
2295 P_PERSON_ID => l_person_id,
2296 P_NAME => l_name,
2297 X_RETURN_STATUS => x_return_status );
2298
2299 pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure for non-scheduled';
2300 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2301 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2302 END IF;
2303 log_message('** insert into PA for non-scheduled member **');
2304
2305 ELSE
2306 pa_debug.g_err_stage := 'Log: Resource already in PA - retrieve resource_id';
2307 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2308 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2309 END IF;
2310
2311 log_message('** resource already in PA - retrieve resource_id **');
2312 --Get resource_id from PA for the resource
2313 select resource_id into l_res_id
2314 from pa_resource_txn_attributes
2315 where person_id = l_person_id
2316 and rownum = 1; -- added for bug 3086960.
2317
2318 x_resource_id := l_res_id ;
2319
2320 END IF;
2321
2322 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2323 x_return_status := FND_API.G_RET_STS_ERROR;
2324 RAISE FND_API.G_EXC_ERROR;
2325 END IF;
2326
2327 RETURN; --Exit out of resource API
2328
2329 ----------------------------------------------------------------
2330 -- This is the case it is for a scheduled role
2331 ----------------------------------------------------------------
2332 ELSIF (p_scheduled_member_flag = 'Y') THEN
2333
2334 --Call get_person_name procedure
2335 --to get supervisor name
2336 pa_resource_utils.get_person_name(
2337 P_PERSON_ID => eRec.supervisor_id,
2338 X_PERSON_NAME => l_supervisor_name,
2339 X_RETURN_STATUS => l_return_status);
2340
2341 -- the organization of a scheduled member needs to belong to exp. hierarchy
2342 -- this condition calls the function and checks if this is true
2343
2344 log_message('**** p_scheduled_member_flag = Y *****');
2345 pa_resource_utils.Check_Exp_Org (
2346 P_ORGANIZATION_ID => l_organization_id,
2347 X_VALID => l_valid,
2348 X_RETURN_STATUS => l_return_status);
2349
2350 IF (l_valid = 'Y') THEN
2351 l_exp_org := 'YES';
2352 ELSE
2353 l_exp_org := 'NO';
2354 END IF;
2355
2356 If l_exp_org = 'NO' Then
2357 pa_debug.g_err_stage := 'Log: Scheduled member does not belong to an Exp Hierarhy Org';
2358 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2359 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2360 END IF;
2361 log_message('Scheduled member does not belong to an Exp Hierarhy Org');
2362
2363 PA_UTILS.Add_Message(
2364 p_app_short_name => 'PA'
2365 ,p_msg_name => 'PA_NO_EXP_HIER');
2366
2367 x_return_status := FND_API.G_RET_STS_ERROR;
2368 RAISE FND_API.G_EXC_ERROR;
2369 End If;
2370
2371 -----------------------------------------------------
2372 -- Check if Job Utilizable_flag=N, if so, error out
2373 -----------------------------------------------------
2374 -- Bug 5337454 Added IF
2375 IF ((p_start_date is null)OR(p_start_date is not null and p_start_date >=eRec.assignment_start_date and p_start_date <=eRec.assignment_end_date)) THEN
2376 l_job_utilization := pa_hr_update_api.check_job_utilization
2377 (p_job_id => eRec.job_id
2378 ,p_person_id => null
2379 ,p_date => null);
2380
2381 IF l_job_utilization='N' OR l_job_utilization IS NULL THEN
2382 PA_UTILS.Add_Message(p_app_short_name => 'PA'
2383 ,p_msg_name => 'PA_NOT_SCHEDULABLE_JOB');
2384 -- bug 3146989: error msg in Add Team Member page
2385 -- Changed the msg since the utilizable_job doesn't
2386 -- really make sense for the Add Team Member page.
2387 -- ,p_msg_name => 'PA_NON_UTILIZABLE_JOB');
2388
2389 x_return_status := FND_API.G_RET_STS_ERROR;
2390 RAISE FND_API.G_EXC_ERROR;
2391 END IF;
2392 END IF;
2393 END IF;
2394
2395 ----------------------------------------------------------------
2396 -- The condition below only allows those scheduled members that
2397 -- belong to expenditure organizations and his job is utilizable
2398 ----------------------------------------------------------------
2399
2400 IF (l_exp_org = 'YES' AND l_job_utilization='Y') THEN
2401 log_message('** Resource belongs to an expenditure org **');
2402 --Call get_org_defaults procedure to get default
2403 --operating unit and default calendar for the organization
2404 pa_resource_utils.get_org_defaults (
2405 P_ORGANIZATION_ID => l_organization_id,
2406 X_DEFAULT_OU => l_default_ou,
2407 X_DEFAULT_CAL_ID => l_calendar_id,
2408 X_RETURN_STATUS => l_return_status);
2409
2410 --Call get_location_details procedure
2411 pa_resource_utils.get_location_details (
2412 P_LOCATION_ID => eRec.location_id,
2413 X_ADDRESS_LINE_1 => l_address1,
2414 x_address_line_2 => l_address2,
2415 x_address_line_3 => l_address3,
2416 x_town_or_city => l_city,
2417 x_postal_code => l_postal_code,
2418 x_country => l_country,
2419 x_return_status => l_return_status);
2420
2421 --This logic checks for resource in PA
2422 --only once for all its assignments
2423 IF(G_person_id <> l_person_id) THEN
2424 G_person_id := l_person_id;
2425
2426 -- do timeline savepoint ONCE for the resource
2427 -- if timeline error occurs, it will rollback for every assignments
2428 -- of this resource and raise an error
2429 IF p_commit = fnd_api.g_true THEN
2430 SAVEPOINT timeline_save;
2431 END IF;
2432
2433 --Call check_res_exists procedure
2434 pa_resource_utils.check_res_exists(
2435 P_PERSON_ID => l_person_id,
2436 X_VALID => l_valid,
2437 X_RETURN_STATUS => l_return_status);
2438
2439 IF (l_valid = 'Y') THEN
2440 l_res_exists := 'EXISTS';
2441 ELSE
2442 l_res_exists := 'NOT EXISTS';
2443 END IF;
2444
2445 END IF;
2446
2447
2448 log_message('Calling Create Internal Resource');
2449 --Call create_internal_resource procedure
2450 CREATE_INTERNAL_RESOURCE(
2451 P_PERSON_ID => l_person_id,
2452 P_NAME => l_name,
2453 P_ORGANIZATION_ID => l_organization_id,
2454 P_ASSIGNMENT_START_DATE => l_assignment_start_date,
2455 P_ASSIGNMENT_END_DATE => l_assignment_end_date,
2456 P_START_DATE => l_start_date,
2457 P_DEFAULT_OU => l_default_OU,
2458 P_CALENDAR_ID => l_calendar_id,
2459 P_SYSTEM_TYPE => l_system_type,
2460 P_USER_TYPE => l_user_type,
2461 P_RES_EXISTS => l_res_exists,
2462 P_COUNT => 1,--This value is not being used and is defaulted to 1
2463 P_RESOURCE_TYPE => l_resource_type,
2464 X_RESOURCE_ID => x_resource_id,
2465 X_RETURN_STATUS => x_return_status,
2466 P_START_DATE_ACTIVE => eRec.per_start_date,
2467 P_END_DATE_ACTIVE => eRec.per_end_date,
2468 P_SOURCE_NUMBER => eRec.per_emp_number,
2469 P_SOURCE_JOB_TITLE => eRec.job_name,
2470 P_SOURCE_EMAIL => eRec.per_email,
2471 P_SOURCE_PHONE => eRec.per_work_phone,
2472 P_SOURCE_ADDRESS1 => l_address1,
2473 P_SOURCE_ADDRESS2 => l_address2,
2474 P_SOURCE_ADDRESS3 => l_address3,
2475 P_SOURCE_CITY => l_city ,
2476 P_SOURCE_POSTAL_CODE => l_postal_code,
2477 P_SOURCE_COUNTRY => l_country,
2478 P_SOURCE_MGR_ID => eRec.supervisor_id,
2479 P_SOURCE_MGR_NAME => l_supervisor_name,
2480 P_SOURCE_BUSINESS_GRP_ID => eRec.per_business_group_id,
2481 P_SOURCE_BUSINESS_GRP_NAME => eRec.org_name,
2482 P_SOURCE_FIRST_NAME => eRec.per_first_name,
2483 P_SOURCE_LAST_NAME => eRec.per_last_name,
2484 P_SOURCE_MIDDLE_NAME => eRec.per_middle_name);
2485 log_message('After Create internal resource'|| x_return_status);
2486
2487 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2488 x_return_status := FND_API.G_RET_STS_ERROR;
2489 RAISE FND_API.G_EXC_ERROR;
2490 END IF;
2491
2492 -------------------------------------------------------------------------------
2493 --Adding call to check if OU implements Projects only if the org is a multi-org
2494 -------------------------------------------------------------------------------
2495
2496 log_message('Calling check_ou');
2497 --MOAC Changes : Bug 4363092: Commenting this cursor as now As R12 will have multi org setup only
2498 /* OPEN check_org_type;
2499 FETCH check_org_type into l_org_type;
2500 CLOSE check_org_type; */
2501
2502 -- case for Multi-Org
2503 -- IF l_org_type IS NOT NULL THEN -- Bug 4363092
2504
2505 CHECK_OU(
2506 P_DEFAULT_OU => l_default_OU,
2507 P_EXP_ORG => l_exp_org,
2508 X_EXP_OU => x_exp_ou,
2509 X_RETURN_STATUS => x_return_status);
2510
2511 IF(x_exp_ou = 'N') THEN
2512 pa_debug.g_err_stage := 'Log: Multi Org - OU does not implement Projects';
2513 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2514 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2515 END IF;
2516 PA_UTILS.Add_Message(
2517 p_app_short_name => 'PA'
2518 ,p_msg_name => 'PA_OU_NO_PROJECTS');
2519 x_return_status := FND_API.G_RET_STS_ERROR;
2520
2521 RAISE FND_API.G_EXC_ERROR;
2522 END IF;
2523
2524 log_message('After check_ou '|| x_return_status);
2525
2526 -- case for Single-Org
2527 /* ELSE -- Bug 4383092
2528 l_default_OU := NULL;
2529 END IF; */
2530
2531
2532 --------------------------------------------------------------------------
2533 -- The calling procedure has passed p_check_resource = Y,
2534 -- which means that if the resource already exists
2535 -- in the database, just pass back the resource_id.
2536 -- We do not want to create timeline data again for this resource.
2537 -- Create PRM Assignment and Create Project APIs currently call this
2538 -- API with p_check_resource = 'Y'. When a new resource is created
2539 -- (through HR triggers), or when a new HR Assignment is created,
2540 -- this flag value will be 'N' since we want the timeline data
2541 -- to be created. We are making this check in INDIVIDUAL='Y' mode
2542 -- only, since in the ALL mode, we will generate timeline for every
2543 -- resource.
2544 ---------------------------------------------------------------------------
2545
2546 IF p_check_resource='Y' AND l_res_exists = 'EXISTS' and x_resource_id is not null
2547 THEN
2548
2549 ---------------------------------------------------------------------
2550 -- Added code to take care of the case that if resource is end
2551 -- dated because its organization was removed from an expenditure
2552 -- hierarchy then if the organization is added back to expenditure
2553 -- hierarchy the resource continues to be end dated and has to be
2554 -- pulled again
2555 -----------------------------------------------------------------------
2556 BEGIN
2557 select 'Y'
2558 into l_exists
2559 from pa_resources_denorm
2560 where person_id = l_person_id
2561 and l_assignment_end_date BETWEEN resource_effective_start_date
2562 AND resource_effective_end_date;
2563
2564 EXCEPTION
2565 WHEN NO_DATA_FOUND THEN
2566 l_exists := 'N';
2567 WHEN OTHERS THEN
2568 l_exists := 'N';
2569 END;
2570 null;
2571 END IF;
2572
2573 IF l_exists = 'N' THEN
2574
2575 -- Adding call to populate resource denormalized tables
2576
2577 --------------------------------------------------------------------
2578 -- Bug Ref # 6411422
2579 -- Adding Profile Date Honoring Logic for Resource
2580 ---------------------------------------------------------------------
2581 -- l_prof_date := FND_PROFILE.value('PA_UTL_START_DATE') ; /* commenting for For Bug 7304151 */
2582 l_prof_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
2583 IF( l_prof_date IS NOT NULL ) THEN
2584 IF ( l_prof_date >= l_assignment_start_date AND l_prof_date <= l_assignment_end_date ) THEN
2585 log_message(' Profile Date is Later than the Employee Start Date');
2586 l_populate_denorm_flag := 'Y' ;
2587 l_assignment_start_date := l_prof_date;
2588 ELSE
2589 IF ( l_assignment_start_date >= l_prof_date ) THEN
2590 l_populate_denorm_flag := 'Y' ;
2591 ELSE
2592 l_populate_denorm_flag := 'N' ;
2593 END IF;
2594 END IF;
2595 END IF;
2596 IF ( l_populate_denorm_flag = 'Y' ) THEN -- Bug 6411422
2597
2598 pa_resource_pvt.populate_resources_denorm(
2599 p_resource_source_id => l_person_id
2600 , p_resource_id => x_resource_id
2601 , p_resource_name => l_name
2602 , p_resource_type => eRec.resource_type
2603 , p_person_type => eRec.p_type
2604 , p_resource_job_id => eRec.job_id
2605 , p_resource_job_group_id => eRec.job_group_id
2606 , p_resource_org_id => l_default_OU
2607 , p_resource_organization_id => l_organization_id
2608 , p_assignment_start_date => l_assignment_start_date
2609 , p_assignment_end_date => l_assignment_end_date
2610 , p_manager_id => eRec.supervisor_id
2611 , p_manager_name => l_supervisor_name
2612 , p_request_id => G_request_id
2613 , p_program_application_id => G_application_id
2614 , p_program_id => G_program_id
2615 , p_commit => fnd_api.G_false
2616 , p_validate_only => fnd_api.G_false
2617 , x_msg_data => x_msg_data
2618 , x_msg_count => x_msg_count
2619 , x_return_status => x_return_status);
2620
2621 log_message('After resources denorm '|| x_return_status);
2622 END IF; -- Bug 6411422
2623 END IF;
2624
2625 -- if the code arrives at this logic, then at least one
2626 -- HR assignment pull is a success
2627 l_at_least_one_success := 'Y';
2628
2629 END IF; /* End l_exp_org=YES check - for scheduled member only */
2630
2631 EXCEPTION
2632 WHEN FND_API.G_EXC_ERROR THEN
2633 IF p_commit = fnd_api.g_true THEN
2634 ROLLBACK TO res_pvt_create_resource;
2635 END IF;
2636
2637 log_message('ROLLBACK for this assignment');
2638 -- exit of resource API for non-scheduled member with error
2639 IF p_scheduled_member_flag = 'N' THEN
2640 RETURN;
2641 END IF;
2642 END;
2643
2644 END LOOP;
2645
2646 log_message('The value of l_res_found is ' || l_res_found);
2647
2648 IF (l_res_found = 'N') THEN
2649 validate_person(P_PERSON_ID);
2650 RAISE FND_API.G_EXC_ERROR;
2651
2652 -- for scheduled resource found in loop
2653 ELSIF (l_res_found = 'Y') THEN
2654
2655 -- if all HR assignments fail, then just rollback and
2656 -- raise an expected error
2657 IF l_at_least_one_success = 'N' THEN
2658 x_return_status := FND_API.G_RET_STS_ERROR;
2659 log_message('All HR assignments failed - raise an expected error');
2660
2661 RAISE FND_API.G_EXC_ERROR;
2662 END IF;
2663
2664 -- create timeline when check resource No OR
2665 -- check resource Yes and resource does not exist
2666 IF (p_check_resource='N' OR (p_check_resource='Y' AND l_res_exists='NOT EXISTS')) THEN
2667
2668
2669 /*Call added for bug 5683340*/
2670 log_message('before Calling pa_resource_utils.init_fte_sync_wf');
2671 pa_resource_utils.init_fte_sync_wf( p_person_id => l_person_id,
2672 x_invol_term => l_invol_term,
2673 x_return_status => x_return_status,
2674 x_msg_data => x_msg_data,
2675 x_msg_count => x_msg_count);
2676 log_message('After Calling pa_resource_utils.init_fte_sync_wf, x_return_status: '||x_return_status); --bug 5683340
2677
2678 /*IF - ELSIF block added for bug 5683340*/
2679 IF ((l_invol_term = 'N') AND (x_return_status = FND_API.G_RET_STS_SUCCESS )) THEN
2680
2681 -- Call Create Timeline after all assignments are in
2682 -- resource denorm table
2683 log_message('Calling Create_Timeline procedure');
2684
2685 PA_TIMELINE_PVT.Create_Timeline (
2686 p_start_resource_name => NULL,
2687 p_end_resource_name => NULL,
2688 p_resource_id => x_resource_id,
2689 p_start_date => NULL,
2690 p_end_date => NULL,
2691 x_return_status => x_return_status,
2692 x_msg_count => x_msg_count,
2693 x_msg_data => x_msg_data);
2694
2695 log_message('After timeline call: '|| x_return_status);
2696 log_message('Out of Create_Timeline procedure');
2697
2698 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2699 x_return_status := FND_API.G_RET_STS_ERROR;
2700
2701 -- timeline error occurs
2702 -- rollback for all assignments of this resource
2703 -- also raise an error
2704 IF p_commit = fnd_api.g_true THEN
2705 ROLLBACK TO timeline_save;
2706 END IF;
2707
2708 log_message('Timeline errors out - rollback all assignments for this resource');
2709
2710 RAISE FND_API.G_EXC_ERROR;
2711 END IF;
2712
2713 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN -- for bug 5683340
2714 log_message(' Call to pa_resource_utils.init_fte_sync_wf errored in individual pull');
2715 RAISE FND_API.G_EXC_ERROR;
2716
2717 END IF ; --IF ((l_invol_term = 'N') AND (x_return_status = FND_API.G_RET_STS_SUCCESS )) bug 5683340
2718
2719 END IF;
2720
2721 -- remove unwanted error message from the stack
2722 -- when at least one HR assignment pull is a success
2723 l_msg_stack_num_new := FND_MSG_PUB.Count_Msg;
2724 log_message('New Number of Stack messages = ' || to_char(l_msg_stack_num_new));
2725
2726 if (l_at_least_one_success = 'Y') then
2727 if (l_msg_stack_num_new > l_msg_stack_num_old) then
2728
2729 FOR I IN (l_msg_stack_num_old+1)..l_msg_stack_num_new LOOP
2730
2731 log_message('Deleting message at index: ' || FND_MSG_PUB.Count_Msg);
2732 FND_MSG_PUB.delete_msg(p_msg_index =>FND_MSG_PUB.Count_Msg);
2733
2734 END LOOP;
2735
2736 end if;
2737
2738 x_return_status := FND_API.G_RET_STS_SUCCESS;
2739 log_message('Return success if at least one assignment is a success');
2740 end if;
2741
2742 END IF;
2743
2744
2745 ------------------------------------------------------------
2746 -- This is the concurrent pull program
2747 -- It goes through a loop using pa_r_project_resources_v
2748 ------------------------------------------------------------
2749 ELSE /* Not an individual pull - Mass pull */
2750
2751 -- Check that terminated resources pull date is within
2752 -- a year ago
2753 -- hr_utility.trace_on(NULL, 'RMPULL');
2754 IF p_pull_term_res = 'Y' THEN
2755 -- hr_utility.trace('get terminated');
2756 -- hr_utility.trace('l_term_range_date is ' || l_term_range_date);
2757 -- hr_utility.trace('l_term_range_date is ' || to_char(l_term_range_date, 'YYYY/MM/DD HH24:MI:SS'));
2758 IF (l_selection_option <> 'EMP_RANGE' AND l_selection_option <> 'SINGLE_ORG') THEN -- bug 7482852
2759 if l_term_range_date < add_months(trunc(sysdate), -12) THEN
2760 -- hr_utility.trace('TERM_DATE_EXCEPTION ');
2761 Raise TERM_DATE_EXCEPTION;
2762 END IF;
2763 END IF;
2764 END IF;
2765
2766 -- hr_utility.trace('after my changes');
2767 pa_debug.g_err_stage := 'Log: Start of Create_Resource';
2768 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2769 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2770 END IF;
2771 log_message('For a batch of people');
2772
2773 --Inside the concurrent process
2774 pa_debug.g_err_stage := 'Log: Before looping through each person record';
2775 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2776 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2777 END IF;
2778 pa_debug.g_err_stage := '*******************************************************';
2779 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2780 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
2781 END IF;
2782 log_message('*****************************************');
2783 log_message('Before looping through each person record');
2784
2785
2786 -- Begin Bug 3086960. Added by Sachin.
2787
2788 sql_cursor := DBMS_SQL.OPEN_CURSOR;
2789 /* -- Commented for PJR Enhancement for resource pull - Bug 5130414
2790 -- Organization is not specified.
2791 IF l_p_org_id IS NULL THEN
2792 -- Employee range is specified
2793 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2794 l_from_clause := ' FROM per_all_people_f p ';
2795 -- changed l_where_clause for bug4954896
2796 --l_where_clause := ' WHERE p.employee_number >= ''' || l_from_emp_num || ''''|| ' AND p.employee_number <= ''' || l_to_emp_num || '''';
2797 l_where_clause := ' WHERE p.employee_number >= :from_emp_num AND p.employee_number <= :to_emp_num ';
2798 ELSE
2799 -- Organization is specified
2800 IF
2801 -- Employee range is specified
2802 (l_from_emp_num IS NOT NULL AND
2803 l_to_emp_num IS NOT NULL ) THEN
2804
2805 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2806 l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2807 -- changed l_where_clause for bug4954896
2808 -- l_where_clause := ' WHERE a.person_id = p.person_id '||
2809 -- ' AND a.organization_id = ' || l_p_org_id ||
2810 -- ' AND p.employee_number >= ''' || l_from_emp_num || ''''||
2811 -- ' AND p.employee_number <= ''' || l_to_emp_num || '''';
2812 l_where_clause := ' WHERE a.person_id = p.person_id '||
2813 ' AND a.organization_id = :org_id '||
2814 ' AND p.employee_number >= :from_emp_num '||
2815 ' AND p.employee_number <= :to_emp_num ';
2816 ELSE
2817 -- Employee range is not specified
2818 l_sel_clause := ' SELECT DISTINCT a.person_id ';
2819 l_from_clause := ' FROM per_all_assignments_f a';
2820 -- changed l_where_clause for Bug4954896
2821 --l_where_clause := ' WHERE a.organization_id = ' || l_p_org_id ;
2822 l_where_clause := ' WHERE a.organization_id = :org_id' ;
2823 END IF;
2824 END IF;
2825
2826 l_stmt := l_sel_clause || l_from_clause || l_where_clause;
2827
2828 -- hr_utility.trace('l_stmt is : ' || l_stmt);
2829 DBMS_SQL.PARSE(sql_cursor, l_stmt, dbms_sql.v7);
2830 -- Added for Bug4954896
2831 -- Start of Bug 4954896
2832 IF l_p_org_id IS NULL THEN
2833 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2834 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2835 ELSE
2836 -- Organization is specified
2837 IF
2838 -- Employee range is specified
2839 (l_from_emp_num IS NOT NULL AND
2840 l_to_emp_num IS NOT NULL ) THEN
2841 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2842 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2843 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2844 ELSE
2845 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2846 END IF;
2847 END IF;
2848 -- end of 4954896
2849 */
2850 -- Start - Bug 5130414 - PJR Resource Pull Enhancement
2851 IF l_selection_option = 'EMP_RANGE' THEN
2852 -- Employee range is specified
2853 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2854 --l_from_clause := ' FROM per_all_people_f p '; /*Commented for Bug 7012687 */
2855 l_from_clause := ' FROM per_all_people_f p , per_all_assignments_f a'; /*Added per_all_asgn_f for Bug 7012687 */
2856 l_where_clause := ' WHERE p.employee_number >= :from_emp_num AND p.employee_number <= :to_emp_num '||
2857 ' AND p.person_id = a.person_id '|| /*Added condition for Bug 7012687 */
2858 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2859 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2860 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2861 ELSIF l_selection_option = 'EMP_RANGE_ORG' THEN
2862 -- Employee range and organization is specified
2863 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2864 l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2865 l_where_clause := ' WHERE a.person_id = p.person_id '||
2866 ' AND a.organization_id = :org_id '||
2867 ' AND p.employee_number >= :from_emp_num '||
2868 ' AND p.employee_number <= :to_emp_num '||
2869 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2870 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2871 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2872 ELSIF l_selection_option = 'SINGLE_ORG' THEN
2873 -- Organization is specified
2874 l_sel_clause := ' SELECT DISTINCT a.person_id ';
2875 l_from_clause := ' FROM per_all_assignments_f a';
2876 l_where_clause := ' WHERE a.organization_id = :org_id'||
2877 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2878 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2879 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2880 ELSIF l_selection_option = 'START_ORG' THEN
2881 -- Organization hierarchy and starting organization is specified
2882 l_sel_clause := ' SELECT DISTINCT a.person_id ';
2883 l_from_clause := ' FROM per_all_assignments_f a';
2884 l_where_clause := ' WHERE a.organization_id IN ( '||
2885 ' SELECT hrorg.organization_id '||
2886 ' FROM hr_all_organization_units_tl hrorg, hr_organization_information orginfo '||
2887 ' WHERE hrorg.language = userenv(''LANG'') '||
2888 ' AND orginfo.organization_id = hrorg.organization_id '||
2889 ' AND orginfo.ORG_INFORMATION_CONTEXT = ''CLASS'' '||
2890 ' AND orginfo.ORG_INFORMATION1 = ''PA_EXPENDITURE_ORG'' '||
2891 ' AND orginfo.ORG_INFORMATION2 = ''Y'' '||
2892 ' AND hrorg.organization_id IN ( '||
2893 ' SELECT organization_id_child organization_id '||
2894 ' FROM PER_ORG_STRUCTURE_ELEMENTS '||
2895 ' WHERE org_structure_version_id = :org_str_version_id '||
2896 ' START WITH organization_id_parent = :start_org '||
2897 ' AND org_structure_version_id = :org_str_version_id '||
2898 ' CONNECT BY PRIOR organization_id_child = organization_id_parent '||
2899 ' AND org_structure_version_id = :org_str_version_id '||
2900 ' UNION ALL '||
2901 ' SELECT :start_org organization_id FROM dual) )'||
2902 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2903 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2904 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2905 ELSIF l_selection_option IS NULL THEN
2906 -- Selection option not specified (Will never occur, but a check for backward compliance)
2907 IF l_p_org_id IS NULL THEN
2908 -- Employee range is specified
2909 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2910 --l_from_clause := ' FROM per_all_people_f p '; /*Commented for Bug 7012687 */
2911 l_from_clause := ' FROM per_all_people_f p , per_all_assignments_f a '; /*Added per_all_asgn_f for Bug 7012687 */
2912 l_where_clause := ' WHERE p.employee_number >= :from_emp_num AND p.employee_number <= :to_emp_num '||
2913 ' AND p.person_id = a.person_id '|| /*Added condition for Bug 7012687 */
2914 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2915 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2916 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2917 ELSIF (l_from_emp_num IS NOT NULL AND l_to_emp_num IS NOT NULL) THEN
2918 -- Employee range and Organization is specified
2919 l_sel_clause := ' SELECT DISTINCT p.person_id ';
2920 l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2921 l_where_clause := ' WHERE a.person_id = p.person_id '||
2922 ' AND a.organization_id = :org_id '||
2923 ' AND p.employee_number >= :from_emp_num '||
2924 ' AND p.employee_number <= :to_emp_num '||
2925 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 6850503 */
2926 ' AND a.job_id is not null'|| /*Added condition for Bug 6850503 */
2927 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 6850503 */
2928 ELSE
2929 -- Only Organization is specified
2930 l_sel_clause := ' SELECT DISTINCT a.person_id ';
2931 l_from_clause := ' FROM per_all_assignments_f a';
2932 l_where_clause := ' WHERE a.organization_id = :org_id'||
2933 ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2934 ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2935 ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2936 END IF;
2937 END IF;
2938
2939 l_stmt := l_sel_clause || l_from_clause || l_where_clause;
2940 DBMS_SQL.PARSE(sql_cursor, l_stmt, dbms_sql.v7);
2941
2942 IF l_selection_option = 'EMP_RANGE' THEN
2943 -- Employee range is specified
2944 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2945 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2946 ELSIF l_selection_option = 'EMP_RANGE_ORG' THEN
2947 -- Employee range and organization is specified
2948 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2949 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2950 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2951 ELSIF l_selection_option = 'SINGLE_ORG' THEN
2952 -- Organization is specified
2953 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2954 ELSIF l_selection_option = 'START_ORG' THEN
2955 -- Organization hierarchy and starting organization is specified
2956 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_str_version_id',l_org_str_version_id);
2957 DBMS_SQL.BIND_VARIABLE(sql_cursor,':start_org',l_start_organization_id);
2958 ELSIF l_selection_option IS NULL THEN
2959 -- Selection option not specified (Will never occur, but a check for backward compliance)
2960 IF l_p_org_id IS NULL THEN
2961 -- Employee range is specified
2962 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2963 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2964 ELSIF (l_from_emp_num IS NOT NULL AND l_to_emp_num IS NOT NULL) THEN
2965 -- Employee range and Organization is specified
2966 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2967 DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
2968 DBMS_SQL.BIND_VARIABLE(sql_cursor,':to_emp_num',l_to_emp_num);
2969 ELSE
2970 -- Only Organization is specified
2971 DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
2972 END IF;
2973 END IF;
2974 -- End - Bug 5130414 - PJR Resource Pull Enhancement
2975
2976 DBMS_SQL.DEFINE_COLUMN(sql_cursor, 1, l_person_id);
2977 l_rows := DBMS_SQL.EXECUTE (sql_cursor);
2978
2979 -- hr_utility.trace('l_rows is : ' || l_rows);
2980 LOOP
2981 IF DBMS_SQL.FETCH_ROWS(sql_cursor) = 0 THEN
2982 EXIT;
2983 END IF;
2984 -- Get the person ID to process into l_person_id
2985 DBMS_SQL.COLUMN_VALUE(sql_cursor,1,l_person_id);
2986
2987
2988 -- hr_utility.trace('person id is : ' || l_person_id);
2989 -- Loop through cur_resources cursor
2990 FOR eRec IN cur_resources(l_person_id, p_pull_term_res,
2991 l_term_range_date, p_person_type) LOOP
2992 -- hr_utility.trace('inside loop for person ' || l_person_id);
2993
2994 -- Loop through cur_resources cursor
2995 -- FOR eRec IN cur_resources(l_from_emp_num, l_to_emp_num, l_p_org_id) LOOP
2996
2997 -- End Bug 3086960
2998
2999 BEGIN
3000
3001 -- For selecting cursor columns into local variables.
3002 -- l_person_id := eRec.person_id; -- Commented out for bug 3086960
3003
3004 --Check whether this person is already a resource in denorm
3005 --Skip this person if he is, when the l_refresh from concurrent
3006 --program parameter is No
3007 --do this check once for every person
3008 if (l_refresh = 'N'and G_person_id <> l_person_id) then
3009
3010 open check_res_denorm;
3011 fetch check_res_denorm into l_denorm_yes;
3012 close check_res_denorm;
3013
3014 if l_denorm_yes = 'Y' then
3015 pa_debug.g_err_stage := 'Log: Resource already in denorm and l_refresh=N: person_id=' || l_person_id;
3016 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3017 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3018 END IF;
3019 log_message('Resource already in denorm and l_refresh=N: person_id=' || l_person_id);
3020 raise PERSON_ERROR_EXCEPTION;
3021 end if;
3022
3023 end if;
3024
3025
3026 IF (i = l_max_count and G_person_id <> l_person_id ) THEN
3027 IF (fnd_api.to_boolean(p_commit)) THEN
3028 COMMIT WORK;
3029 log_message('Commit work - lock released');
3030 pa_debug.g_err_stage := 'Log: COMMIT for person_id = ' || G_person_id;
3031 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3032 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3033 END IF;
3034 /* Adding the following code to re-establish
3035 Savepoint after l_max_count resources are
3036 committed
3037 */
3038 i := 0;--reset loop variable
3039 -- SAVEPOINT res_pvt_create_resource;
3040 END IF;
3041 END IF;
3042
3043 log_message('G_person_id value = ' || G_person_id);
3044 log_message('l_person_id value = ' || l_person_id);
3045
3046 --Getting the latest name of person - bug 3273964
3047 pa_debug.g_err_stage := 'OLD PERSON_NAME ==> ' ||eRec.NAME;
3048 IF P_DEBUG_MODE = 'Y' THEN
3049 pa_debug.write_file('start_crm_workflow: ' ||'LOG',pa_debug.g_err_stage);
3050 END IF;
3051
3052 eRec.NAME := pa_resource_utils.get_person_name_no_date(P_PERSON_ID => eRec.person_id);
3053
3054 pa_debug.g_err_stage := 'NEW PERSON_NAME ==> ' || eRec.NAME;
3055 IF P_DEBUG_MODE = 'Y' THEN
3056 pa_debug.write_file('start_crm_workflow: ' ||'LOG',pa_debug.g_err_stage);
3057 END IF;
3058
3059 log_message('PERSON_NAME ==> '|| eRec.NAME);
3060 -- end bug 3273964
3061
3062 --set the global person_id variable
3063 IF(G_person_id <> l_person_id) THEN
3064
3065 G_person_id := l_person_id;
3066 log_message('G_person_id value - enter if condition = ' || G_person_id);
3067
3068 IF (fnd_api.to_boolean(p_commit)) THEN
3069 log_message('set SAVEPOINT for G_person_id' || G_person_id);
3070 SAVEPOINT res_pvt_create_resource;
3071 END IF;
3072
3073 -------------------------------------------------
3074 -- USER-LOCK: lock the person_id for processing
3075 -- If cannot get lock, then will continue
3076 -- with the next record (this is done by raising
3077 -- expected error which stores the error msg in
3078 -- the pa_reporting_exceptions table)
3079 -------------------------------------------------
3080 if(pa_resource_utils.acquire_user_lock(l_person_id, 'Resource Pull') <> 0) then
3081 pa_debug.g_err_stage := 'Log: Unable to acquire LOCK for person_id = ' || l_person_id;
3082 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3083 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3084 END IF;
3085 log_message('Unable to acquire LOCK for person_id = ' || l_person_id);
3086
3087 -- get the info of this person to be inserted into exception table
3088 l_name := eRec.name;
3089 l_organization_id := eRec.organization_id;
3090 l_assignment_start_date := eRec.assignment_start_date;
3091 l_assignment_end_date := eRec.assignment_end_date;
3092
3093 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3094 ,p_msg_name => 'PA_RS_SKIP_CANT_LOCK');
3095
3096 -- set the variable to N so in the next loop, if it is the same person Id
3097 -- too, that will raise person_error_exception and the code will not
3098 -- process this person
3099 l_per_success := 'N';
3100 RAISE FND_API.G_EXC_ERROR;
3101
3102 else
3103 pa_debug.g_err_stage := 'Log: Able to acquire LOCK for person_id = ' || l_person_id;
3104 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3105 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3106 END IF;
3107 log_message('Able to acquire LOCK for person_id = ' || l_person_id);
3108 end if;
3109
3110 i := i+1;
3111 log_message('i = ' || i);
3112
3113 -- set the success flag for this new person to Y
3114 l_per_success := 'Y';
3115
3116 -- get the latest HR assignment end date for this person id
3117
3118 -- hr_utility.trace('inside loop termination date is ' || eRec.termination_date);
3119 IF eRec.termination_date IS NULL THEN
3120 OPEN get_max_asgmt_end_date;
3121 FETCH get_max_asgmt_end_date INTO l_end_date;
3122 CLOSE get_max_asgmt_end_date;
3123 ELSE
3124 OPEN get_max_end_date_term(l_person_id);
3125 FETCH get_max_end_date_term INTO l_end_date;
3126 CLOSE get_max_end_date_term;
3127 END IF;
3128
3129
3130 ----------------------------------------------------
3131 -- If the same person_id is seen, and there has been
3132 -- an error in the previous records for this person,
3133 -- exception is raised and do nothing
3134 -- Person not pulled because at least one assignment
3135 -- has an error.
3136 ----------------------------------------------------
3137 ELSIF (G_person_id = l_person_id) THEN
3138 IF l_per_success = 'N' THEN
3139 log_message('raising person_error_exception');
3140 raise PERSON_ERROR_EXCEPTION;
3141 END IF;
3142 END IF;
3143
3144 pa_debug.g_err_stage := '*******************************************************';
3145 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3146 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3147 END IF;
3148 pa_debug.g_err_stage := 'PERSON_ID ==> ' || to_char(l_person_id);
3149 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3150 pa_debug.write_file('start_crm_workflow: ' || 'LOG', pa_debug.g_err_stage);
3151 END IF;
3152 log_message('*****************************************');
3153 log_message('PERSON_ID ==> '|| to_char(l_person_id));
3154
3155 l_name := eRec.name;
3156 l_organization_id := eRec.organization_id;
3157 l_assignment_start_date := eRec.assignment_start_date;
3158 l_assignment_end_date := eRec.assignment_end_date;
3159 l_start_date := eRec.start_date;
3160 l_default_OU := eRec.default_OU;
3161 l_calendar_id := eRec.calendar_id;
3162 l_system_type := eRec.p_type;
3163 l_user_type := eRec.user_type;
3164 l_res_exists := eRec.res_exists;
3165
3166 log_message('ORG_ID ==> '|| to_char(l_organization_id));
3167 log_message('OU ==> '|| to_char(l_default_OU));
3168
3169
3170 --Call get_person_name procedure
3171 --to get supervisor name
3172 pa_resource_utils.get_person_name(
3173 P_PERSON_ID => eRec.supervisor_id,
3174 X_PERSON_NAME => l_supervisor_name,
3175 X_RETURN_STATUS => l_return_status);
3176
3177
3178 --Call get_location_details procedure
3179 pa_resource_utils.get_location_details (
3180 P_LOCATION_ID => eRec.location_id,
3181 X_ADDRESS_LINE_1 => l_address1,
3182 x_address_line_2 => l_address2,
3183 x_address_line_3 => l_address3,
3184 x_town_or_city => l_city,
3185 x_postal_code => l_postal_code,
3186 x_country => l_country,
3187 x_return_status => l_return_status);
3188
3189
3190 pa_debug.g_err_stage := 'Log: Calling Create_Internal_Resource procedure';
3191 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3192 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3193 END IF;
3194
3195 --Call check_res_exists procedure Added for bug 5046096
3196 PA_RESOURCE_UTILS.CHECK_RES_EXISTS(
3197 p_person_id => l_person_id,
3198 x_valid => l_valid,
3199 x_return_status => l_return_status);
3200
3201 IF (l_valid = 'Y') THEN
3202 l_res_exists := 'EXISTS';
3203 ELSE
3204 l_res_exists := 'NOT EXISTS';
3205 END IF;
3206
3207 log_message('Before calling CREATE_INTERNAL_RESOURCE procedure');
3208 --Call CREATE_INTERNAL_RESOURCE procedure
3209 -- hr_utility.trace('before CREATE_INTERNAL_RESOURCE');
3210 CREATE_INTERNAL_RESOURCE(
3211 P_PERSON_ID => l_person_id,
3212 P_NAME => l_name,
3213 P_ORGANIZATION_ID => l_organization_id,
3214 P_ASSIGNMENT_START_DATE => l_assignment_start_date,
3215 P_ASSIGNMENT_END_DATE => l_assignment_end_date,
3216 P_START_DATE => l_start_date,
3217 P_DEFAULT_OU => l_default_OU,
3218 P_CALENDAR_ID => l_calendar_id,
3219 P_SYSTEM_TYPE => l_system_type,
3220 P_USER_TYPE => l_user_type,
3221 P_RES_EXISTS => l_res_exists,
3222 P_COUNT => 1,--This value is not being used and is defaulted to 1
3223 P_RESOURCE_TYPE => l_resource_type,
3224 X_RESOURCE_ID => x_resource_id,
3225 X_RETURN_STATUS => l_return_status, --x_return_status, -- Commented For bug 4087022
3226 P_START_DATE_ACTIVE => eRec.per_start_date,
3227 P_END_DATE_ACTIVE => eRec.per_end_date,
3228 P_SOURCE_NUMBER => eRec.per_emp_number,
3229 P_SOURCE_JOB_TITLE => eRec.job_name,
3230 P_SOURCE_EMAIL => eRec.per_email,
3231 P_SOURCE_PHONE => eRec.per_work_phone,
3232 P_SOURCE_ADDRESS1 => l_address1,
3233 P_SOURCE_ADDRESS2 => l_address2,
3234 P_SOURCE_ADDRESS3 => l_address3,
3235 P_SOURCE_CITY => l_city ,
3236 P_SOURCE_POSTAL_CODE => l_postal_code,
3237 P_SOURCE_COUNTRY => l_country,
3238 P_SOURCE_MGR_ID => eRec.supervisor_id,
3239 P_SOURCE_MGR_NAME => l_supervisor_name,
3240 P_SOURCE_BUSINESS_GRP_ID => eRec.per_business_group_id,
3241 P_SOURCE_BUSINESS_GRP_NAME => eRec.org_name,
3242 P_SOURCE_FIRST_NAME => eRec.per_first_name,
3243 P_SOURCE_LAST_NAME => eRec.per_last_name,
3244 P_SOURCE_MIDDLE_NAME => eRec.per_middle_name);
3245
3246 -- hr_utility.trace('after CREATE_INTERNAL_RESOURCE');
3247 -- hr_utility.trace('x_return_status is : ' || x_return_status);
3248 pa_debug.g_err_stage := 'Log: After Create_Internal_Resource procedure';
3249 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3250 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3251 END IF;
3252
3253 -- Commenting For bug 4087022
3254 -- IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3255 -- x_return_status := FND_API.G_RET_STS_ERROR;
3256 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3257 l_return_status := FND_API.G_RET_STS_ERROR;
3258 l_per_success := 'N';
3259 RAISE FND_API.G_EXC_ERROR;
3260 END IF;
3261
3262 log_message('After calling CREATE_INTERNAL_RESOURCE procedure');
3263
3264 log_message('Number of Stack messages = ' || to_char(FND_MSG_PUB.Count_Msg));
3265 -- log_message('Return Status after create internal resource ' || x_return_status); -- Commenting For bug 4087022
3266 log_message('Return Status after create internal resource ' || l_return_status);
3267
3268 --Adding call to check if OU is Exp OU only if the org is a Multi-Org
3269 log_message('Calling check_ou');
3270 pa_debug.g_err_stage := 'Log: Calling Check_OU procedure';
3271 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3272 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3273 END IF;
3274 --MOAC Changes : Bug 4363092: Commenting this cursor as now As R12 will have multi org setup only
3275 /* OPEN check_org_type;
3276 FETCH check_org_type into l_org_type;
3277 CLOSE check_org_type; */
3278
3279 -- case for Multi-Org
3280 -- IF l_org_type IS NOT NULL THEN --Bug 4363092
3281
3282 CHECK_OU(
3283 P_DEFAULT_OU => l_default_OU,
3284 P_EXP_ORG => 'YES',
3285 X_EXP_OU => x_exp_ou,
3286 -- X_RETURN_STATUS => x_return_status); -- Commenting For bug 4087022
3287 X_RETURN_STATUS => l_return_status);
3288
3289 IF(x_exp_ou = 'N') THEN
3290 pa_debug.g_err_stage := 'Log: Multi Org - OU does not implement Projects';
3291 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3292 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3293 END IF;
3294 PA_UTILS.Add_Message(
3295 p_app_short_name => 'PA'
3296 ,p_msg_name => 'PA_OU_NO_PROJECTS');
3297 -- x_return_status := FND_API.G_RET_STS_ERROR; -- Commenting For bug 4087022
3298 l_return_status := FND_API.G_RET_STS_ERROR;
3299 l_per_success := 'N';
3300
3301 RAISE FND_API.G_EXC_ERROR;
3302 END IF;
3303
3304 -- log_message('After check_ou '|| x_return_status); -- Commenting For bug 4087022
3305 log_message('After check_ou '|| l_return_status);
3306
3307 -- case for Single-Org
3308 /* ELSE -- Bug 4363092
3309 l_default_OU := NULL;
3310 END IF; */
3311
3312
3313 log_message('l_default_OU =====> '||to_char(l_default_OU));
3314
3315 --Adding call to populate resource denorm tables
3316 pa_debug.g_err_stage := 'Log: Calling Populate_Resources_Denorm procedure';
3317 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3318 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3319 END IF;
3320
3321 -- hr_utility.trace('before populate_resources_denorm');
3322
3323 --------------------------------------------------------------------
3324 -- Bug Ref # 6411422
3325 -- Adding Profile Date Honoring Logic for Resource
3326 ---------------------------------------------------------------------
3327 -- l_prof_date := FND_PROFILE.value('PA_UTL_START_DATE') ; /* commenting for For Bug 7304151 */
3328 l_prof_date := to_date(fnd_profile.value('PA_UTL_START_DATE'), 'DD/MM/YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'); /*Adding For Bug 7304151 */
3329 IF ( l_prof_date IS NOT NULL ) THEN
3330 IF ( l_prof_date >= l_assignment_start_date AND l_prof_date <= l_assignment_end_date ) THEN
3331 log_message(' Profile Date is Later than the Employee Start Date');
3332 l_populate_denorm_flag := 'Y' ;
3333 l_assignment_start_date := l_prof_date;
3334 ELSE
3335 IF ( l_assignment_start_date >= l_prof_date ) THEN
3336 l_populate_denorm_flag := 'Y' ;
3337 ELSE
3338 l_populate_denorm_flag := 'N' ;
3339 END IF;
3340 END IF;
3341 END IF;
3342 IF ( l_populate_denorm_flag = 'Y' ) THEN -- Bug 6411422
3343
3344 pa_resource_pvt.populate_resources_denorm(
3345 p_resource_source_id => l_person_id
3346 , p_resource_id => x_resource_id
3347 , p_resource_name => l_name
3348 , p_resource_type => eRec.resource_type
3349 , p_person_type => eRec.p_type
3350 , p_resource_job_id => eRec.job_id
3351 , p_resource_job_group_id => eRec.job_group_id
3352 , p_resource_org_id => l_default_OU
3353 , p_resource_organization_id => l_organization_id
3354 , p_assignment_start_date => l_assignment_start_date
3355 , p_assignment_end_date => l_assignment_end_date
3356 , p_manager_id => eRec.supervisor_id
3357 , p_manager_name => l_supervisor_name
3358 , p_request_id => G_request_id
3359 , p_program_application_id => G_application_id
3360 , p_program_id => G_program_id
3361 , p_commit => fnd_api.G_false
3362 , p_validate_only => fnd_api.G_false
3363 , x_msg_data => x_msg_data
3364 , x_msg_count => x_msg_count
3365 -- , x_return_status => x_return_status); -- Commenting For bug 4087022
3366 , x_return_status => l_return_status);
3367
3368 -- hr_utility.trace('after populate_resources_denorm');
3369 -- hr_utility.trace('x_return_status is : ' || x_return_status);
3370 -- hr_utility.trace('x_msg_data is : ' || x_msg_data);
3371 pa_debug.g_err_stage := 'Log: After Populate_Resources_Denorm procedure';
3372 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3373 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3374 END IF;
3375 -- log_message('After resources denorm '|| x_return_status); -- Commenting For bug 4087022
3376 log_message('After resources denorm '|| l_return_status);
3377 END IF; -- Bug 6411422
3378
3379 /* Changes start for Bug 5662589 */
3380 ---------------------------------------------------------------------
3381 --This IF tells us the last record for a person as from cur_resources.
3382 --Here we are going to delete all rows for pa_resources_denorm for
3383 --which the resource_effective_end_date is greater than
3384 --max(assignment_end_date) from pa_r_project_resources_term_v res
3385 ---------------------------------------------------------------------
3386 IF (trunc(l_end_date) = trunc(l_assignment_end_date))
3387 AND eRec.termination_date IS NOT NULL THEN
3388
3389 log_message('Delete Terminate resource denorm data');
3390 pa_debug.g_err_stage := 'Delete Terminate resource denorm data';
3391 IF P_DEBUG_MODE = 'Y' THEN
3392 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3393 END IF;
3394
3395 DELETE FROM PA_RESOURCES_DENORM
3396 WHERE person_id = l_person_id
3397 AND resource_effective_start_date > l_end_date;
3398
3399 log_message('right after calling deleting denorm data');
3400 pa_debug.g_err_stage := 'Log: after calling deleting denorm data';
3401 IF P_DEBUG_MODE = 'Y' THEN
3402 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3403 END IF;
3404
3405 END IF;
3406 /* Changes end for Bug 5662589 */
3407
3408 -------------------------------------------------------
3409 -- Call Create Timeline only for the last assignment
3410 -- where the latest HR assignment end date is equal to
3411 -- the l_assignment_end_date
3412 -------------------------------------------------------
3413 /*2749061: Do not create FI for past employees*/
3414 IF (trunc(l_end_date) = trunc(l_assignment_end_date)) THEN
3415 -- AND trunc(l_end_date) >= sysdate) THEN
3416
3417 /* Cleaning pa_resources_denorm start *** Bug 9198412 */
3418 IF (trunc(eRec.termination_date) IS NULL
3419 and trunc(l_assignment_end_date) < trunc(sysdate)
3420 and l_refresh = 'Y') THEN
3421
3422 delete from pa_resources_denorm
3423 where person_id = l_person_id
3424 and resource_effective_start_date > l_end_date;
3425
3426 END IF;
3427 /* Cleaning pa_resources_denorm start end *** Bug 9198412 */
3428
3429 /* Removing data from pa_resources_demorn as per 7336526 */
3430 IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3431 FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3432
3433 DELETE FROM pa_resources_denorm
3434 WHERE RESOURCE_EFFECTIVE_START_DATE = delrec2.RESOURCE_EFFECTIVE_START_DATE
3435 AND PERSON_ID = delrec2.PERSON_ID;
3436
3437 END LOOP ;
3438 END IF;
3439
3440
3441 /*Code starts for Bug 6943551*/
3442 FOR delrec IN cur_denorm_del(l_person_id) LOOP
3443
3444 DELETE FROM pa_resources_denorm
3445 WHERE RESOURCE_EFFECTIVE_START_DATE = delrec.RESOURCE_EFFECTIVE_START_DATE
3446 AND PERSON_ID = delrec.PERSON_ID; --bug#8840426
3447
3448 END LOOP ;
3449 /*Code ends for Bug 6943551*/
3450
3451 /*Call added for bug 5683340*/
3452 pa_debug.g_err_stage := 'Log: Calling pa_resource_utils.init_fte_sync_wf for PersonId =' || to_char(l_person_id) ;
3453 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3454 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3455 END IF;
3456
3457 log_message('Before Calling pa_resource_utils.init_fte_sync_wf');
3458 pa_resource_utils.init_fte_sync_wf( p_person_id => l_person_id,
3459 x_invol_term => l_invol_term,
3460 -- x_return_status => x_return_status, -- Commenting For bug 4087022
3461 x_return_status => l_return_status,
3462 x_msg_data => x_msg_data,
3463 x_msg_count => x_msg_count);
3464 -- log_message('After Calling pa_resource_utils.init_fte_sync_wf, x_return_status: '||x_return_status); -- bug 5683340 ---- Commenting For bug 4087022
3465 log_message('After Calling pa_resource_utils.init_fte_sync_wf, l_return_status: '||l_return_status); -- bug 5683340
3466
3467
3468 pa_debug.g_err_stage := 'Log: After Calling pa_resource_utils.init_fte_sync_wf for PersonId =' || to_char(l_person_id) ;
3469 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3470 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3471 END IF;
3472
3473
3474 /*IF - ELSIF block added for bug 5683340*/
3475 -- IF ((l_invol_term = 'N') AND (x_return_status = fnd_api.g_ret_sts_success )) THEN -- Commenting For bug 4087022
3476 IF ((l_invol_term = 'N') AND (l_return_status = fnd_api.g_ret_sts_success )) THEN
3477 pa_debug.g_err_stage := 'Log: Calling Create_Timeline procedure for PersonId =' || to_char(l_person_id) ;
3478 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3479 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3480 END IF;
3481 log_message('Calling Create_Timeline procedure after the last assignment for PersonId =' || to_char(l_person_id));
3482
3483 -- hr_utility.trace('before Create_Timeline');
3484 PA_TIMELINE_PVT.Create_Timeline (
3485 p_start_resource_name => NULL,
3486 p_end_resource_name => NULL,
3487 p_resource_id => x_resource_id,
3488 p_start_date => NULL,
3489 p_end_date => NULL,
3490 -- x_return_status => x_return_status, -- Commenting For bug 4087022
3491 x_return_status => l_return_status,
3492 x_msg_count => x_msg_count,
3493 x_msg_data => x_msg_data);
3494 -- hr_utility.trace('after Create_Timeline');
3495 -- hr_utility.trace('x_return_status is : ' || x_return_status);
3496 -- hr_utility.trace('x_msg_data is : ' || x_msg_data);
3497
3498 -- log_message('right after calling create_timeline, x_return_status : '||x_return_status); -- Commenting For bug 4087022
3499 log_message('right after calling create_timeline, l_return_status : '||l_return_status);
3500 pa_debug.g_err_stage := 'Log: After Create_Timeline procedure';
3501 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3502 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3503 END IF;
3504
3505 -- IF (x_return_status <> fnd_api.g_ret_sts_success) THEN -- Commenting For bug 4087022
3506 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3507 log_message('it will raise exception');
3508 pa_debug.g_err_stage := 'Log: Timeline API returned error';
3509 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3510 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3511 END IF;
3512 l_per_success := 'N';
3513 RAISE FND_API.G_EXC_ERROR;
3514 END IF;
3515
3516 -- log_message('After timeline call: ' || x_return_status); -- Commenting For bug 4087022
3517 log_message('After timeline call: ' || l_return_status);
3518
3519 -- ELSIF (x_return_status <> fnd_api.g_ret_sts_success ) THEN -- for bug 5683340 -- Commenting For bug 4087022
3520 ELSIF (l_return_status <> fnd_api.g_ret_sts_success ) THEN -- for bug 5683340
3521 pa_debug.g_err_stage := 'Log: init_fte_sync_wf API returned error';
3522 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3523 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3524 END IF;
3525 l_per_success := 'N';
3526 log_message(' Call to pa_resource_utils.init_fte_sync_wf errored in mass pull');
3527 RAISE FND_API.G_EXC_ERROR;
3528
3529 END IF ; --IF ((l_invol_term = 'N') AND (l_return_status = fnd_api.g_ret_sts_success ))
3530
3531 END IF;
3532
3533 -----------------------------------------------------------------------
3534 -- Populate msgs to the exception report for the people who has been
3535 -- pulled but won't be searchable because they are either non scheduable
3536 -- or there is no job mapping for the person.
3537 -----------------------------------------------------------------------
3538 IF ( l_populate_denorm_flag = 'Y' ) THEN -- bug 8669156
3539 l_job_id := PA_JOB_UTILS.Get_Job_Mapping (
3540 p_job_id => eRec.job_id
3541 ,p_job_group_id => eRec.job_group_id);
3542
3543 -- bug 4171563. Changed 'substr' to 'substrb' in the call to PA_MESSAGE_UTILS.save_messages
3544
3545 IF l_job_id IS NULL THEN
3546 PA_MESSAGE_UTILS.save_messages(
3547 p_request_id => G_request_id
3548 ,p_source_Type1 => 'RESOURCE_PULL'
3549 ,p_source_Type2 => 'PARCPRJR'
3550 ,p_context1 => l_person_id
3551 ,p_context2 => substrb(l_name, 1, 30)
3552 ,p_context3 => l_organization_id
3553 ,p_context4 => eRec.job_id
3554 ,p_context5 => substrb(l_supervisor_name, 1, 30)
3555 ,p_context10 => 'NO_JOB_MAP'
3556 ,p_date_context1 => l_assignment_start_date
3557 ,p_date_context2 => l_assignment_end_date
3558 ,p_use_fnd_msg => 'N'
3559 ,p_commit => FND_API.G_FALSE --p_commit
3560 ,x_return_status => l_return_status);
3561 END IF;
3562
3563 l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
3564 (p_job_id => eRec.job_id);
3565
3566 IF l_job_schedulable = 'N' THEN
3567 PA_MESSAGE_UTILS.save_messages(
3568 p_request_id => G_request_id
3569 ,p_source_Type1 => 'RESOURCE_PULL'
3570 ,p_source_Type2 => 'PARCPRJR'
3571 ,p_context1 => l_person_id
3572 ,p_context2 => substrb(l_name, 1, 30)
3573 ,p_context3 => l_organization_id
3574 ,p_context4 => eRec.job_id
3575 ,p_context5 => substrb(l_supervisor_name, 1, 30)
3576 ,p_context10 => 'NON_SCHEDULABLE'
3577 ,p_date_context1 => l_assignment_start_date
3578 ,p_date_context2 => l_assignment_end_date
3579 ,p_use_fnd_msg => 'N'
3580 ,p_commit => FND_API.G_FALSE --p_commit
3581 ,x_return_status => l_return_status);
3582 END IF;
3583 END IF; -- bug 8669156
3584 EXCEPTION
3585 WHEN FND_API.G_EXC_ERROR THEN
3586 --x_return_status := FND_API.G_RET_STS_ERROR;
3587
3588 l_excep := 'Y' ; -- Adding For bug 4087022
3589
3590 IF p_commit = fnd_api.g_true THEN
3591 log_message('rollback in inner exception block');
3592 ROLLBACK TO res_pvt_create_resource; --line 2813
3593 log_message('after rollback in inner exception block');
3594 END IF;
3595
3596 -- will still do this to display the error message in the log file
3597 FND_MSG_PUB.get (
3598 p_encoded => FND_API.G_FALSE,
3599 p_msg_index => 1,
3600 p_data => x_msg_data,
3601 p_msg_index_out => x_msg_count );
3602
3603 pa_debug.g_err_stage := 'Expected error for Person = ' || to_char(l_person_id);
3604 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3605 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3606 END IF;
3607 pa_debug.g_err_stage := 'ERROR: ' || substr(x_msg_data,1,200);
3608 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3609 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3610 END IF;
3611
3612 log_message('Expected error for Person = ' || to_char(l_person_id));
3613 log_message('ERROR: ' || substr(x_msg_data,1,200));
3614
3615 -- then save the error message in the pa_reporting_exceptions table
3616 PA_MESSAGE_UTILS.save_messages(
3617 p_request_id => G_request_id
3618 ,p_source_Type1 => 'RESOURCE_PULL'
3619 ,p_source_Type2 => 'PARCPRJR'
3620 ,p_context1 => l_person_id
3621 ,p_context2 => substrb(l_name, 1, 30) -- Bug 3086960. Changed Substrb. Sachin
3622 ,p_context3 => l_organization_id
3623 ,p_context10 => 'REJECTED'
3624 ,p_date_context1 => l_assignment_start_date
3625 ,p_date_context2 => l_assignment_end_date
3626 ,p_commit => p_commit
3627 ,x_return_status => l_return_status);
3628
3629 -- reset variable i
3630 i :=0;
3631
3632 WHEN PERSON_ERROR_EXCEPTION THEN
3633 null;
3634
3635 -- Begin Bug 3086960. Added by Sachin.
3636
3637 /* END;
3638
3639 END LOOP;
3640 */
3641 WHEN OTHERS THEN
3642
3643 /*Adding For bug 4087022*/
3644 l_excep := 'Y' ;
3645 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Create_Resource'
3646 ,p_procedure_name =>'Create_Resource' );
3647 /*For bug 4087022*/
3648
3649 IF p_commit = fnd_api.g_true THEN
3650 log_message('when others rollback in inner exception block') ;
3651 ROLLBACK TO res_pvt_create_resource;
3652 END IF;
3653
3654 -- will still do this to display the error message inthe log file
3655 FND_MSG_PUB.get (
3656 p_encoded => FND_API.G_FALSE,
3657 p_msg_index => 1,
3658 p_data => x_msg_data,
3659 p_msg_index_out => x_msg_count );
3660 log_message('*** When Others Un Expected error for Person = ' || TO_CHAR (l_person_id));
3661 log_message('ERROR: ' || SUBSTR(x_msg_data,1,200));
3662
3663 -- then save the error message in the pa_reporting_exceptions table
3664 PA_MESSAGE_UTILS.save_messages(
3665 p_request_id => G_request_id
3666 ,p_source_Type1 => 'RESOURCE_PULL'
3667 ,p_source_Type2 => 'PARCPRJR'
3668 ,p_context1 => l_person_id
3669 ,p_context2 => SUBSTRB(l_name,1,30)
3670 ,p_context3 => l_organization_id
3671 ,p_context10 => 'REJECTED'
3672 ,p_date_context1 => l_assignment_start_date
3673 ,p_date_context2 => l_assignment_end_date
3674 ,p_commit => p_commit
3675 ,x_return_status => l_return_status);
3676
3677 -- reset variable i
3678 i :=0;
3679
3680 END;
3681
3682 END LOOP;
3683 /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3684 IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3685 FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3686
3687 DELETE FROM pa_resources_denorm
3688 WHERE person_id = delrec3.person_id
3689 AND resource_effective_start_date = delrec3.assignment_start_date
3690 AND resource_organization_id = l_p_org_id;
3691
3692
3693 END LOOP ;
3694 END IF;
3695
3696
3697 END LOOP; -- Dynamic SQL loop
3698 DBMS_SQL.CLOSE_CURSOR(sql_cursor);
3699
3700 -- End Bug 3086960
3701
3702 pa_debug.g_err_stage := 'Log: Calling Populate_Org_Hier_Denorm procedure';
3703 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3704 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3705 END IF;
3706 log_message('Calling Populate_Org_Hier_Denorm procedure');
3707
3708 -- Begin Bug 3086960. Commented out for performance. Sachin
3709 /*
3710 PA_ORG_UTILS.Populate_Org_Hier_Denorm(
3711 x_return_status => x_return_status,
3712 x_msg_data => x_msg_data);
3713 */
3714 pa_debug.g_err_stage := 'Log: After Populate_Org_Hier_Denorm procedure';
3715 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3716 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3717 END IF;
3718 log_message('Out of Populate_Org_Hier_Denorm procedure');
3719
3720
3721 END IF; --For Individual, Internal person
3722
3723
3724 ELSE
3725 ----------------------------------------------------
3726 -- this is the else for the condition l_internal=Y
3727 -- For pulling external person
3728 -- PULLING EXTERNAL PEOPLE
3729 ----------------------------------------------------
3730
3731 IF (l_individual = 'Y') THEN
3732 pa_debug.g_err_stage := 'Log: For a single external Resource';
3733 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3734 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3735 END IF;
3736 log_message('For Ind. External Resource');
3737
3738 -----------------------------------------------------------------
3739 -- First, need to check if the resource_type is HZ_PARTY and
3740 -- party_id is passed. If Yes - the call CREATE_EXTERNAL_RESOURCE
3741 -----------------------------------------------------------------
3742 IF(l_resource_type = 'HZ_PARTY' and l_party_id is not null) THEN
3743 log_message('Creating External Person as a Resource');
3744
3745 CREATE_EXTERNAL_RESOURCE(
3746 P_PARTY_ID => l_party_id,
3747 P_RESOURCE_TYPE => l_resource_type,
3748 X_RESOURCE_ID => x_resource_id,
3749 X_RETURN_STATUS => x_return_status);
3750
3751
3752 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3753 x_return_status := FND_API.G_RET_STS_ERROR;
3754 RAISE FND_API.G_EXC_ERROR;
3755 END IF;
3756
3757 RETURN;
3758
3759 END IF;
3760
3761 END IF;
3762
3763 END IF; --For Internal
3764
3765 pa_debug.g_err_stage := 'Log: End of Create_Resource procedure';
3766 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3767 pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3768 END IF;
3769 log_message('End of create_resource procedure');
3770
3771 IF fnd_api.to_boolean(p_commit) THEN
3772 COMMIT WORK;
3773 END IF;
3774
3775 --Reset all Global Variables.
3776 PA_RESOURCE_PVT.G_Prev_Res_Source_Id := NULL;
3777
3778 G_pkg_name := NULL;
3779 G_p_id := NULL;
3780 G_count := NULL;
3781
3782 G_user_id := NULL;
3783 G_login_id := NULL;
3784 G_request_id := NULL;
3785 G_program_id := NULL;
3786 G_application_id := NULL;
3787
3788 PA_DEBUG.Reset_Err_Stack;
3789
3790 /*Added for bug 4087022*/
3791 /*to return status as warning when l_excep = Y , i.e., */
3792 /*when some resource raised exception in inner loop */
3793 IF (l_excep = 'Y') THEN
3794 x_return_status := 'W';
3795 x_resource_id := NULL ;
3796 END IF ;
3797
3798 EXCEPTION
3799
3800 WHEN TERM_DATE_EXCEPTION THEN
3801 x_return_status := FND_API.G_RET_STS_ERROR;
3802 PA_UTILS.Add_Message(
3803 p_app_short_name => 'PA'
3804 ,p_msg_name => 'PA_TERM_DATE_OVER_MAX');
3805
3806 WHEN FND_API.G_EXC_ERROR THEN
3807
3808 x_return_status := FND_API.G_RET_STS_ERROR;
3809 -- 4537865
3810 x_resource_id := NULL ;
3811
3812 WHEN OTHERS THEN
3813 log_message (' =============== ');
3814 log_message (' Raised Others in Create Resource Private');
3815
3816 log_message (SQLCODE || SQLERRM);
3817 IF p_commit = fnd_api.g_true THEN
3818 log_message('rollback in outer exception block');
3819 ROLLBACK TO res_pvt_create_resource; -- line 3005
3820 END IF;
3821 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Create_Resource'
3822 ,p_procedure_name =>PA_DEBUG.G_Err_Stack );
3823 X_RETURN_STATUS := fnd_api.g_ret_sts_unexp_error;
3824 -- 4537865
3825 x_resource_id := NULL ;
3826 raise;
3827
3828 END CREATE_RESOURCE;
3829
3830 PROCEDURE start_crm_workflow ( p_person_id IN NUMBER,
3831 p_assignment_start_date IN DATE,
3832 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3833 x_error_message_code OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3834 IS
3835
3836 ItemType varchar2(30) := 'PACRMUPD'; -- Identifies the workflow that will be executed.
3837 ItemKey number ;
3838 l_process VARCHAR2(30);
3839
3840 l_msg_count NUMBER;
3841 l_msg_data VARCHAR(2000);
3842 l_return_status VARCHAR2(1);
3843 -- l_api_version_number NUMBER := 1.0;
3844 l_data VARCHAR2(2000);
3845 l_msg_index_out NUMBER;
3846 l_save_thresh NUMBER;
3847
3848 l_err_code NUMBER := 0;
3849 l_err_stage VARCHAR2(2000);
3850 l_err_stack VARCHAR2(2000);
3851 --
3852 --
3853 BEGIN
3854
3855
3856 --
3857 -- Get a unique identifier for this specific workflow
3858 --
3859
3860
3861 SELECT pa_workflow_itemkey_s.nextval
3862 INTO itemkey
3863 FROM dual;
3864 --
3865 -- Since this workflow needs to be executed in the background we need
3866 -- to change the threshold. So we save the current threshold which
3867 -- will be used later on to change it back to the current threshold.
3868 --
3869
3870 l_save_thresh := wf_engine.threshold ;
3871
3872
3873 IF wf_engine.threshold < 0 THEN
3874 wf_engine.threshold := l_save_thresh ;
3875 END IF;
3876
3877
3878 --
3879 -- Set the threshold to bellow 0 so that the process will be created
3880 -- in the background
3881 --
3882
3883 wf_engine.threshold := -1 ;
3884
3885
3886 l_process := 'PROCESS_CRM_UPDATE' ;
3887
3888
3889 --
3890 -- Create the appropriate process
3891 --
3892 wf_engine.CreateProcess( ItemType => ItemType,
3893 ItemKey => ItemKey,
3894 process => l_process );
3895
3896 --
3897 -- Initialize workflow item attributes with the parameter values
3898 --
3899 wf_engine.SetItemAttrText ( itemtype => itemtype,
3900 itemkey => itemkey,
3901 aname => 'PROJECT_RESOURCE_ADMINISTRATOR',
3902 avalue => 'PASYSADMIN');
3903
3904 wf_engine.SetItemAttrDate ( itemtype => itemtype,
3905 itemkey => itemkey,
3906 aname => 'ASSIGNMENT_START_DATE',
3907 avalue => p_assignment_start_date);
3908
3909 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
3910 itemkey => itemkey,
3911 aname => 'PERSON_ID',
3912 avalue => p_person_id);
3913
3914 wf_engine.StartProcess ( itemtype => itemtype,
3915 itemkey => itemkey );
3916
3917 /* -- Insert to PA tables wf process information.
3918 -- This is required for displaying notifications on PA pages.
3919
3920 BEGIN
3921
3922 PA_WORKFLOW_UTILS.Insert_WF_Processes
3923 (p_wf_type_code => 'HR_CHANGE_MGMT'
3924 ,p_item_type => itemtype
3925 ,p_item_key => itemkey
3926 ,p_entity_key1 => to_char(p_person_id)
3927 ,p_entity_key2 => to_char(p_person_id)
3928 ,p_description => NULL
3929 ,p_err_code => l_err_code
3930 ,p_err_stage => l_err_stage
3931 ,p_err_stack => l_err_stack
3932 );
3933
3934 EXCEPTION
3935 WHEN OTHERS THEN
3936 null;
3937 END; */
3938
3939 wf_engine.threshold := l_save_thresh ;
3940 EXCEPTION
3941 WHEN OTHERS THEN
3942 null;
3943
3944 END start_crm_workflow;
3945
3946
3947 PROCEDURE create_future_crm_resource
3948 (itemtype IN VARCHAR2
3949 , itemkey IN VARCHAR2
3950 , actid IN NUMBER
3951 , funcmode IN VARCHAR2
3952 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3953 ) IS
3954
3955 l_person_id NUMBER;
3956 l_assignment_start_date DATE;
3957 l_current_asgn_start_date DATE;
3958 l_resource_id NUMBER;
3959
3960
3961 l_msg_count NUMBER;
3962 l_msg_data VARCHAR(2000);
3963 l_return_status VARCHAR2(1);
3964 l_api_version_number NUMBER := 1.0;
3965 l_data VARCHAR2(2000);
3966 l_msg_index_out NUMBER;
3967
3968 l_savepoint BOOLEAN;
3969
3970 BEGIN
3971
3972 --
3973 -- Get the workflow attribute values
3974 --
3975
3976 l_person_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3977 itemkey => itemkey,
3978 aname => 'PERSON_ID' );
3979
3980 l_assignment_start_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3981 itemkey => itemkey,
3982 aname => 'ASSIGNMENT_START_DATE' );
3983
3984 --
3985 -- Check if assignment start date has not changed
3986 --
3987 SAVEPOINT l_create_future_crm_resource ;
3988 l_savepoint := true;
3989
3990
3991 BEGIN
3992 SELECT min(effective_start_date)
3993 INTO l_current_asgn_start_date
3994 FROM per_all_assignments_f
3995 WHERE person_id = l_person_id
3996 AND primary_flag = 'Y'
3997 AND job_id IS NOT NULL
3998 AND assignment_type in ('E', 'C');
3999 EXCEPTION
4000 WHEN OTHERS THEN
4001 l_current_asgn_start_date := NULL;
4002 END;
4003
4004 IF (l_current_asgn_start_date IS NOT NULL) AND (l_current_asgn_start_date = l_assignment_start_date) THEN
4005 pa_r_project_resources_pub.create_resource (
4006 p_api_version => 1.0
4007 ,p_init_msg_list => NULL
4008 ,p_commit => FND_API.G_FALSE
4009 ,p_validate_only => NULL
4010 ,p_max_msg_count => NULL
4011 ,p_internal => 'Y'
4012 ,p_person_id => l_person_id
4013 ,p_individual => 'Y'
4014 ,p_scheduled_member_flag => 'Y'
4015 ,p_resource_type => NULL
4016 ,x_return_status => l_return_status
4017 ,x_msg_count => l_msg_count
4018 ,x_msg_data => l_msg_data
4019 ,x_resource_id => l_resource_id);
4020
4021 /* IF l_return_status <> 'S' then
4022 app_exception.raise_exception;
4023 END IF; */ -- Bug 7369682 : If error comes, we need to put it into Workkflow
4024
4025 END IF;
4026
4027 IF l_return_status = 'S' THEN
4028
4029 resultout := wf_engine.eng_completed||':'||'S';
4030 ELSE
4031
4032 --
4033 -- Set any error messages
4034 --
4035 l_savepoint := false;
4036 rollback to l_create_future_crm_resource ;
4037
4038 /*Added For bug 7690604 */
4039 l_data := FND_MESSAGE.get_string('PA', l_msg_data );
4040
4041 wf_engine.SetItemAttrText
4042 ( itemtype => itemtype
4043 , itemkey => itemkey
4044 , aname => 'ERROR_MSG'
4045 , avalue => l_data -- l_msg_data -- For bug 7690604
4046 );
4047
4048 resultout := wf_engine.eng_completed||':'||'F';
4049
4050 END IF;
4051
4052 EXCEPTION
4053
4054 WHEN OTHERS THEN
4055 wf_core.context('pa_r_project_resources_pvt',
4056 'create_future_crm_resource',
4057 itemtype,
4058 itemkey,
4059 to_char(actid),
4060 funcmode);
4061
4062 If (l_savepoint) then
4063 rollback to l_create_future_crm_resource ;
4064 End if;
4065
4066 wf_engine.SetItemAttrText ( itemtype => itemtype
4067 , itemkey => itemkey
4068 , aname => 'ERROR_MSG'
4069 , avalue => l_msg_data
4070 );
4071 resultout := wf_engine.eng_completed||':'||'F';
4072
4073 END create_future_crm_resource;
4074
4075 -----------------------------------------------------------------------------
4076 --------------------------< get_user_person_type >--------------------------|
4077 -----------------------------------------------------------------------------
4078 FUNCTION GET_USER_PERSON_TYPE
4079 (P_EFFECTIVE_DATE IN DATE
4080 ,P_PERSON_ID IN NUMBER
4081 )
4082 RETURN VARCHAR2
4083 IS
4084 CURSOR CSR_PERSON_TYPES
4085 (P_EFFECTIVE_DATE IN DATE
4086 ,P_PERSON_ID IN NUMBER
4087 )
4088 IS
4089 SELECT TTL.USER_PERSON_TYPE
4090 FROM PER_PERSON_TYPES_TL TTL
4091 ,PER_PERSON_TYPES TYP
4092 ,PER_PERSON_TYPE_USAGES_F PTU
4093 WHERE TTL.LANGUAGE = USERENV('LANG')
4094 AND TTL.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
4095 AND TYP.SYSTEM_PERSON_TYPE IN ('EMP','EX_EMP','CWK','EX_CWK')
4096 AND TYP.PERSON_TYPE_ID = PTU.PERSON_TYPE_ID
4097 AND P_EFFECTIVE_DATE BETWEEN PTU.EFFECTIVE_START_DATE
4098 AND PTU.EFFECTIVE_END_DATE
4099 AND PTU.PERSON_ID = P_PERSON_ID
4100 ORDER BY DECODE(TYP.SYSTEM_PERSON_TYPE
4101 ,'EMP' ,1
4102 ,'CWK' ,2
4103 ,'EX_EMP',3
4104 ,'EX_CWK',4
4105 ,5
4106 );
4107 L_USER_PERSON_TYPE VARCHAR2(2000);
4108 L_SEPARATOR HR_PERSON_TYPE_USAGE_INFO.G_USER_PERSON_TYPE_SEPARATOR%TYPE;
4109
4110 BEGIN
4111 L_SEPARATOR := HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE_SEPARATOR();
4112
4113 FOR L_PERSON_TYPE IN CSR_PERSON_TYPES
4114 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE
4115 ,P_PERSON_ID => P_PERSON_ID
4116 )
4117 LOOP
4118 IF (L_USER_PERSON_TYPE IS NULL)
4119 THEN
4120 L_USER_PERSON_TYPE := L_PERSON_TYPE.USER_PERSON_TYPE;
4121 ELSE
4122 L_USER_PERSON_TYPE := L_USER_PERSON_TYPE
4123 || L_SEPARATOR
4124 || L_PERSON_TYPE.USER_PERSON_TYPE;
4125 END IF;
4126 END LOOP;
4127 RETURN L_USER_PERSON_TYPE;
4128 END GET_USER_PERSON_TYPE;
4129
4130 END PA_R_PROJECT_RESOURCES_PVT;
[Home] [Help]