[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_IMP_RES_PVT
Source
1 PACKAGE BODY jtf_rs_imp_res_pvt AS
2 /* $Header: jtfrsvub.pls 120.6.12020000.2 2012/07/17 12:22:17 rgokavar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_IMP_RES_PVT';
5
6 PROCEDURE import_resources(
7 P_API_VERSION IN NUMBER,
11 X_RES_ID_TBL OUT NOCOPY res_id_tbl_type,
8 P_INIT_MSG_LIST IN VARCHAR2,
9 P_COMMIT IN VARCHAR2,
10 P_IMP_RES_TBL IN imp_tbl_type,
12 X_TRANSACTION_NUM OUT NOCOPY NUMBER,
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_MSG_COUNT OUT NOCOPY NUMBER,
15 X_MSG_DATA OUT NOCOPY VARCHAR2
16 )
17 IS
18
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_RESOURCES';
21
22 l_return_status VARCHAR2(1);
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(2000);
25
26 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
27 l_salesrep_id jtf_rs_salesreps.salesrep_id%TYPE;
28
29 l_address1 jtf_rs_resource_extns.source_address1%TYPE;
30 l_address2 jtf_rs_resource_extns.source_address2%TYPE;
31 l_address3 jtf_rs_resource_extns.source_address3%TYPE;
32 l_address4 jtf_rs_resource_extns.source_address4%TYPE;
33 l_city jtf_rs_resource_extns.source_city%TYPE;
34 l_postal_code jtf_rs_resource_extns.source_postal_code%TYPE;
35 l_state jtf_rs_resource_extns.source_state%TYPE;
36 l_province jtf_rs_resource_extns.source_province%TYPE;
37 l_county jtf_rs_resource_extns.source_county%TYPE;
38 l_country jtf_rs_resource_extns.source_country%TYPE;
39
40 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE ;
41 l_resource_id jtf_rs_resource_extns.resource_id%TYPE ;
42 l_resource_number jtf_rs_resource_extns.resource_number%TYPE ;
43 l_role_relate_id jtf_rs_role_relations.role_relate_id%TYPE;
44
45 l_source_number jtf_rs_resource_extns.source_number%TYPE;
46 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
47
48 -- fetching the role_type_code in this cursor also so that if salesrep is not created then
49 -- do not create roles of type SALES_COMP -- bug 3579147
50 -- new cursor added (repuri 04/07/03)
51 CURSOR c_job_roles (l_job_id jtf_rs_job_roles.job_id%type) IS
52 SELECT a.role_id, b.role_type_code
53 FROM jtf_rs_job_roles a,
54 jtf_rs_roles_b b
55 WHERE a.job_id = l_job_id
56 AND a.role_id = b.role_id ;
57
58 CURSOR emp_cur(l_person_id NUMBER) IS
59 SELECT
60 ppl.employee_number employee_num
61 ,ppl.npw_number
62 ,ppl.full_name
63 ,ppl.first_name
64 ,ppl.middle_names middle_name
65 ,ppl.last_name
66 ,ppl.work_telephone
67 ,ppl.email_address
68 ,ppl.person_id
69 ,job.job_id
70 ,job.name job_title
71 ,ppl.business_group_id org_id
72 ,org.name org_name
73 ,loc.address_line_1 address1
74 ,loc.address_line_2 address2
75 ,loc.address_line_3 address3
76 ,loc.town_or_city city
77 ,loc.country country
78 ,loc.postal_code postal_code
79 ,asg.location_id address_id
80 ,asg.supervisor_id mgr_id
81 ,emp.full_name mgr_name
82 ,asg.assignment_id assignment_id
83 ,asg.assignment_type
84 ,ppl.office_number
85 ,ppl.internal_location
86 ,ppl.mailstop
87 FROM
88 per_all_people_f ppl
89 ,hr_all_organization_units org
90 ,per_assignments_f asg
91 ,per_jobs job
92 ,hr_locations_all loc -- Changed to hr_locations_all for performance fix bug # 4956645
93 -- ,per_all_people_f pep removed the table since we can get the work_telephone from ppl
94 ,per_all_people_f emp
95 WHERE ppl.person_id = l_person_id
96 AND trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
97 -- AND ppl.employee_number is not null
98 AND (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
99 AND ppl.business_group_id = org.organization_id
100 AND ppl.person_id = asg.person_id
101 AND asg.primary_flag = 'Y'
102 -- AND asg.assignment_type = 'E'
103 AND asg.assignment_type in ('E','C')
104 AND trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
105 AND asg.job_id = job.job_id (+)
106 AND asg.location_id = loc.location_id(+)
107 -- AND ppl.person_id = pep.person_id
108 -- AND trunc(sysdate) between pep.effective_start_date and pep.effective_end_date
109 -- AND pep.employee_number is not null
110 AND asg.supervisor_id = emp.person_id (+)
111 AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+);
112
113
114 emp_rec emp_cur%rowtype;
115
116
117 CURSOR future_emp_cur(l_person_id NUMBER) IS
118 SELECT
119 ppl.employee_number employee_num
120 ,ppl.npw_number
121 ,ppl.full_name
122 ,ppl.first_name
123 ,ppl.middle_names middle_name
124 ,ppl.last_name
125 ,ppl.work_telephone
126 ,ppl.email_address
127 ,ppl.person_id
128 ,job.job_id
129 ,job.name job_title
130 ,ppl.business_group_id org_id
131 ,org.name org_name
132 ,loc.address_line_1 address1
133 ,loc.address_line_2 address2
134 ,loc.address_line_3 address3
135 ,loc.town_or_city city
139 ,asg.supervisor_id mgr_id
136 ,loc.country country
137 ,loc.postal_code postal_code
138 ,asg.location_id address_id
140 ,emp.full_name mgr_name
141 ,asg.assignment_id assignment_id
142 ,asg.assignment_type
143 ,ppl.office_number
144 ,ppl.internal_location
145 ,ppl.mailstop
146 FROM
147 per_all_people_f ppl
148 ,hr_all_organization_units org
149 ,per_assignments_f asg
150 ,per_jobs job
151 ,hr_locations_all loc -- Changed to hr_locations_all for performance fix bug # 4956645
152 -- ,per_all_people_f pep removed the table since we can get the work_telephone from ppl
153 ,per_all_people_f emp
154 WHERE ppl.person_id = l_person_id
155 -- AND trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
156 -- Enh 3947611 2-dec-2004:changed the date check for future dated employees.
157 AND trunc(sysdate) <= trunc(ppl.effective_end_date)
158 -- AND ppl.employee_number is not null
159 AND (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
160 AND ppl.business_group_id = org.organization_id
161 AND ppl.person_id = asg.person_id
162 AND asg.primary_flag = 'Y'
163 -- AND asg.assignment_type = 'E'
164 AND asg.assignment_type in ('E','C')
165 -- AND trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
166 -- Enh 3947611 2-dec-2004: changed the date check for future dated employees.
167 AND trunc(sysdate) <= asg.effective_end_date
168 AND asg.job_id = job.job_id (+)
169 AND asg.location_id = loc.location_id(+)
170 -- AND ppl.person_id = pep.person_id
171 -- AND trunc(sysdate) between pep.effective_start_date and pep.effective_end_date
172 -- AND pep.employee_number is not null
173 AND asg.supervisor_id = emp.person_id (+)
174 -- AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
175 -- Enh 3947611 2-dec-2004:changed the date check for future dated employees who may have future dated employees
176 -- as manager.
177 AND trunc(sysdate) <= emp.effective_end_date (+);
178
179 future_emp_rec emp_cur%rowtype;
180
181 -- Cursor to get the Effective Start Date, for a given Job Assignment of an Employee.
182
183 CURSOR c_get_asg_start_date (l_job_id IN NUMBER, l_person_id IN NUMBER, l_assignment_id NUMBER, l_assignment_type IN VARCHAR2) IS
184 SELECT EFFECTIVE_START_DATE
185 FROM PER_all_assignments_f
186 WHERE person_id = l_person_id
187 AND ASSIGNMENT_TYPE = l_assignment_type
188 AND PRIMARY_FLAG = 'Y'
189 AND job_id = l_job_id
190 AND assignment_id = l_assignment_id
191 ORDER BY EFFECTIVE_START_DATE asc;
192
193 l_asg_start_date DATE;
194
195 CURSOR phone_cur(l_person_id NUMBER) IS
196 SELECT phone_number
197 FROM per_phones per
198 WHERE parent_table = 'PER_ALL_PEOPLE_F'
199 AND parent_id = l_person_id
200 AND phone_type = 'W1'
201 AND date_from = (SELECT MAX(date_from)
202 FROM per_phones p2
203 WHERE parent_table = 'PER_ALL_PEOPLE_F'
204 AND parent_id = l_person_id
205 AND phone_type = 'W1' );
206
207 phone_rec phone_cur%rowtype;
208
209 -- Adding the cursors for mobile and pager (repuri 10/16/01)
210
211 CURSOR mobile_cur(l_person_id NUMBER) IS
212 SELECT phone_number
213 FROM per_phones per
214 WHERE parent_table = 'PER_ALL_PEOPLE_F'
215 and parent_id = l_person_id
216 and phone_type = 'M'
217 and date_from = (SELECT MAX(date_from)
218 FROM per_phones p2
219 WHERE parent_table = 'PER_ALL_PEOPLE_F'
220 AND parent_id = l_person_id
221 AND phone_type = 'M' );
222
223 mobile_rec mobile_cur%rowtype;
224
225 CURSOR pager_cur(l_person_id NUMBER) IS
226 SELECT phone_number
227 FROM per_phones per
228 WHERE parent_table = 'PER_ALL_PEOPLE_F'
229 and parent_id = l_person_id
230 and phone_type = 'P'
231 and date_from = (SELECT MAX(date_from)
232 FROM per_phones p2
233 WHERE parent_table = 'PER_ALL_PEOPLE_F'
234 AND parent_id = l_person_id
235 AND phone_type = 'P' );
236
237 pager_rec pager_cur%rowtype;
238
239 CURSOR par_cur(l_person_id NUMBER) IS
240 SELECT PARTY.PARTY_NUMBER,
241 PARTY.PARTY_NAME,
242 PARTY.EMAIL_ADDRESS,
243 PARTY.ADDRESS1,
244 PARTY.ADDRESS2,
245 PARTY.ADDRESS3,
246 PARTY.ADDRESS4,
247 PARTY.CITY,
248 PARTY.POSTAL_CODE,
249 PARTY.STATE,
250 PARTY.PROVINCE,
251 PARTY.COUNTY,
252 PARTY.COUNTRY,
253 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
254 TO_NUMBER(NULL) ORG_ID,
255 NULL ORG_NAME,
256 PARTY.PERSON_FIRST_NAME,
257 PARTY.PERSON_MIDDLE_NAME,
258 PARTY.PERSON_LAST_NAME
259 FROM HZ_PARTIES PARTY,
260 HZ_CONTACT_POINTS CT_POINT1
261 WHERE PARTY.PARTY_ID = l_person_id
262 AND PARTY.PARTY_TYPE NOT IN ('ORGANIZATION', 'GROUP')
266 AND CT_POINT1.STATUS (+) = 'A'
263 AND CT_POINT1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
264 AND CT_POINT1.OWNER_TABLE_ID (+) = PARTY.PARTY_ID
265 AND CT_POINT1.PRIMARY_FLAG (+) = 'Y'
267 AND CT_POINT1.CONTACT_POINT_TYPE (+) = 'PHONE';
268
269 party_rec par_cur%rowtype;
270
271 -- cursor for party contact
272 CURSOR party_contact_cur(l_party_id IN NUMBER)
273 IS
274 SELECT hzoc.org_contact_id CONTACT_ID
275 FROM hz_relationships hzr,
276 hz_org_contacts hzoc
277 WHERE hzr.party_id = l_party_id
278 AND hzr.directional_flag = 'F'
279 AND hzr.relationship_code = 'EMPLOYEE_OF'
280 AND hzr.subject_table_name ='HZ_PARTIES'
281 AND hzr.object_table_name ='HZ_PARTIES'
282 AND hzr.start_date <= SYSDATE
283 AND (hzr.end_date is null or hzr.end_date > SYSDATE)
284 AND hzr.status = 'A'
285 AND hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id;
286
287 party_contact_rec party_contact_cur%rowtype;
288
289 CURSOR partner_cur(l_person_id NUMBER) IS
290 SELECT PARTY.PARTY_NUMBER,
291 PARTY.PARTY_NAME,
292 PARTY.EMAIL_ADDRESS,
293 PARTY.ADDRESS1,
294 PARTY.ADDRESS2,
295 PARTY.ADDRESS3,
296 PARTY.ADDRESS4,
297 PARTY.CITY,
298 PARTY.POSTAL_CODE,
299 PARTY.STATE,
300 PARTY.PROVINCE,
301 PARTY.COUNTY,
302 PARTY.COUNTRY,
303 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
304 REL.OBJECT_ID ORG_ID,
305 PARTY.PARTY_NAME ORG_NAME,
306 PARTY.PERSON_FIRST_NAME,
307 PARTY.PERSON_MIDDLE_NAME,
308 PARTY.PERSON_LAST_NAME
309 FROM HZ_PARTIES PARTY,
310 HZ_CONTACT_POINTS CT_POINT1,
311 -- HZ_PARTY_RELATIONSHIPS REL
312 HZ_RELATIONSHIPS REL
313 WHERE PARTY.PARTY_ID = l_person_id
314 AND (
315 ( PARTY.PARTY_TYPE = 'ORGANIZATION'
316 AND
317 PARTY.PARTY_ID = REL.SUBJECT_ID
318 )
319 OR
320 ( PARTY.PARTY_TYPE = 'PARTY_REALTIONSHIP'
321 AND
322 PARTY.PARTY_ID = REL.PARTY_ID
323 )
324 )
325 -- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_OF')
326 --
327 AND REL.RELATIONSHIP_CODE IN
328 ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
329 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
330 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
331 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
332 AND REL.DIRECTIONAL_FLAG = 'F'
333 AND REL.STATUS = 'A'
334 AND PARTY.STATUS = 'A'
335 --
336 AND CT_POINT1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
337 AND CT_POINT1.OWNER_TABLE_ID (+) = PARTY.PARTY_ID
338 AND CT_POINT1.PRIMARY_FLAG (+) = 'Y'
339 AND CT_POINT1.STATUS (+) = 'A'
340 AND CT_POINT1.CONTACT_POINT_TYPE (+) = 'PHONE';
341
342 partner_rec partner_cur%rowtype;
343
344 CURSOR partner_add_cur(l_address_id NUMBER) IS
345 SELECT LOC.ADDRESS1,
346 LOC.ADDRESS2,
347 LOC.ADDRESS3,
348 LOC.ADDRESS4,
349 LOC.CITY,
350 LOC.POSTAL_CODE,
351 LOC.STATE,
352 LOC.PROVINCE,
353 LOC.COUNTY,
354 LOC.COUNTRY
355 FROM HZ_PARTY_SITES SITE,
356 HZ_LOCATIONS LOC
357 WHERE SITE.PARTY_SITE_ID = l_address_id
358 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
359
360 partner_add_rec partner_add_cur%rowtype;
361
362 /* -- Modified cursor to go against AP tables below (as per AP teams reccomendation)
363 -- Fix for SQL rep perf bug 5025410 on 05-May-2006
364 CURSOR supp_cont_cur(l_person_id NUMBER) IS
365 SELECT p.segment1 ,
366 s.address_line1 ,
367 s.address_line2 ,
368 s.address_line3 ,
369 s.CITY,
370 s.STATE,
371 s.PROVINCE,
372 s.COUNTY,
373 s.COUNTRY,
374 c.area_code ||' '|| c.phone phone,
375 s.zip,
376 s.org_id ,
377 o.name ,
378 c.FIRST_NAME,
379 c.MIDDLE_NAME,
380 c.last_name
381 FROM PO_VENDORS P,
382 po_vendor_contacts c,
383 po_vendor_sites_all s,
384 hr_operating_units o
385 WHERE c.vendor_contact_id = l_person_id
386 and c.vendor_site_id = s.vendor_site_id
387 and s.vendor_id = p.vendor_id
388 and s.org_id = o.organization_id;
389 */
390 --Bug13820346
391 --From R12 onwards vendor_site_id will be NULL in ap_supplier_contacts
392 --to handle such scenario modified below cursor
393
394 --Bug14000288
395 --Added email_address in below cursor
396 CURSOR supp_cont_cur(l_person_id NUMBER,l_vendor_site_id NUMBER) IS
397 SELECT p.segment1 ,
398 s.address_line1 ,
399 s.address_line2 ,
400 s.address_line3 ,
401 s.CITY,
402 s.STATE,
403 s.PROVINCE,
404 s.COUNTY,
405 s.COUNTRY,
406 rel.primary_phone_area_code ||' '|| rel.primary_phone_number phone,
407 s.zip,
408 s.org_id ,
409 o.name ,
410 per.person_FIRST_NAME first_name,
411 per.person_MIDDLE_NAME middle_name,
415 ap_supplier_contacts c,
412 per.person_last_name last_name,
413 per.email_address
414 FROM ap_suppliers P,
416 ap_supplier_sites_all s,
417 hr_all_organization_units o,
418 hz_parties per,
419 hz_parties rel
420 WHERE c.vendor_contact_id = l_person_id
421 -- and c.vendor_site_id = s.vendor_site_id
422 and s.vendor_site_id = NVL(c.vendor_site_id ,s.vendor_site_id)
423 and s.vendor_site_id = l_vendor_site_id
424 and c.org_party_site_id = s.party_site_id
425 and per.party_id = c.per_party_id
426 and rel.party_id = c.rel_party_id
427 and s.vendor_id = p.vendor_id
428 and s.org_id = o.organization_id;
429
430 supp_cont_rec supp_cont_cur%rowtype;
431
432 i NUMBER;
433
434 BEGIN
435
436 SAVEPOINT import_resources;
437 x_return_status := fnd_api.g_ret_sts_success;
438
439 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
440 RAISE fnd_api.g_exc_unexpected_error;
441 END IF;
442
443 IF fnd_api.to_boolean(p_init_msg_list) THEN
444 fnd_msg_pub.initialize;
445 END IF;
446
447 -- initializing the transaction number
448 l_transaction_number := null;
449
450 IF p_imp_res_tbl.COUNT > 0 THEN
451 i := p_imp_res_tbl.FIRST;
452 LOOP
453
454 x_res_id_tbl(i).res_id := p_imp_res_tbl(i).resource_id;
455
456 IF (p_imp_res_tbl(i).selected = 'Y') THEN
457 IF (p_imp_res_tbl(i).comment_code = 'ROLE') THEN
458 --if resource with same role does not exist
459 IF(p_imp_res_tbl(i).role_id IS NOT NULL) THEN
460 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
461 p_api_version => 1.0,
462 p_init_msg_list => null,
463 p_commit => null,
464 p_role_resource_type => 'RS_INDIVIDUAL',
465 p_role_resource_id => p_imp_res_tbl(i).resource_id,
466 p_role_id => p_imp_res_tbl(i).role_id,
467 p_start_date_active => p_imp_res_tbl(i).role_start_date_active,
468 p_end_date_active => p_imp_res_tbl(i).role_end_date_active,
469 x_return_status => l_return_status,
470 x_msg_count => l_msg_count,
471 x_msg_data => l_msg_data,
472 x_role_relate_id => l_role_relate_id
473 );
474
475 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
476 RAISE fnd_api.g_exc_error;
477 END IF;
478 END IF;
479 ELSIF (p_imp_res_tbl(i).comment_code = 'NEW') THEN
480 --if resource does not exist for the person then insert both resource
481 -- Bug 3086823 : Transaction number should be created once for the entire set of records
482 IF l_transaction_number is null
483 THEN
484 SELECT jtf_rs_transaction_num_s.nextval
485 INTO l_transaction_number
486 FROM dual;
487 END IF;
488
489 x_transaction_num := l_transaction_number;
490
491 --if category is employee then fetch all the denormalized columns
492 IF (p_imp_res_tbl(i).category = 'EMPLOYEE') THEN
493 open emp_cur(p_imp_res_tbl(i).person_id);
494 fetch emp_cur INTO emp_rec;
495 IF emp_cur%NOTFOUND THEN
496 open future_emp_cur(p_imp_res_tbl(i).person_id);
497 fetch future_emp_cur INTO emp_rec;
498 close future_emp_cur;
499 END IF;
500 close emp_cur;
501
502
503 if emp_rec.assignment_type = 'E' then
504 l_source_number := emp_rec.employee_num;
505 l_assignment_type := 'E';
506 else
507 l_source_number := emp_rec.npw_number;
508 l_assignment_type := 'C';
509 end if;
510
511 open phone_cur(p_imp_res_tbl(i).person_id);
512 fetch phone_cur INTO phone_rec;
513 close phone_cur;
514
515 -- Adding the parts for mobile and pager (repuri 10/16/01)
516
517 open mobile_cur(p_imp_res_tbl(i).person_id);
518 fetch mobile_cur INTO mobile_rec;
519 close mobile_cur;
520
521 open pager_cur(p_imp_res_tbl(i).person_id);
522 fetch pager_cur INTO pager_rec;
523 close pager_cur;
524
525 --insert into jtf resource extn by calling the public api
526 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
527 p_api_version => 1.0,
528 p_init_msg_list => null,
529 p_commit => null,
530 p_category => p_imp_res_tbl(i).category,
531 p_source_id => p_imp_res_tbl(i).person_id,
532 p_address_id => emp_rec.address_id,
533 p_contact_id => p_imp_res_tbl(i).contact_id,
534 p_managing_emp_id => l_managing_emp_id,
535 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
536 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
537 p_transaction_number => l_transaction_number,
541 x_msg_data => l_msg_data,
538 p_user_id => p_imp_res_tbl(i).user_id,
539 x_return_status => l_return_status,
540 x_msg_count => l_msg_count,
542 x_resource_id => l_resource_id,
543 x_resource_number => l_resource_number,
544 p_source_name => p_imp_res_tbl(i).name,
545 p_resource_name => p_imp_res_tbl(i).name,
546 p_source_number => l_source_number,
547 p_source_phone => phone_rec.phone_number,
548 p_source_mobile_phone => mobile_rec.phone_number,
549 p_source_pager => pager_rec.phone_number,
550 p_source_email => emp_rec.email_address,
551 p_source_job_title => emp_rec.job_title,
552 p_source_business_grp_id => emp_rec.org_id,
553 p_source_business_grp_name => emp_rec.org_name,
554 p_source_address1 => emp_rec.address1,
555 p_source_address2 => emp_rec.address2,
556 p_source_address3 => emp_rec.address3,
557 p_source_city => emp_rec.city,
558 p_source_country => emp_rec.country,
559 p_source_postal_code => emp_rec.postal_code,
560 p_source_mgr_id => emp_rec.mgr_id,
561 p_source_mgr_name => emp_rec.mgr_name,
562 p_source_first_name => emp_rec.first_name,
563 p_source_middle_name => emp_rec.middle_name,
564 p_source_last_name => emp_rec.last_name,
565 p_source_category => null,
566 p_source_status => null,
567 p_user_name => p_imp_res_tbl(i).user_name,
568 p_source_mailstop => emp_rec.mailstop,
569 p_source_office => emp_rec.office_number,
570 p_source_location => emp_rec.internal_location
571 );
572 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
573 RAISE fnd_api.g_exc_error;
574 END IF;
575
576 x_res_id_tbl(i).res_id := l_resource_id;
577
578 IF (p_imp_res_tbl(i).create_salesperson = 'Y') THEN
579 IF p_imp_res_tbl(i).salesperson_number IS NULL THEN
580 fnd_message.set_name('JTF','JTF_RS_NO_SRP_NUM_ENT_FOR_EMP');
581 fnd_message.set_token('P_EMP_NAME',p_imp_res_tbl(i).name);
582 fnd_msg_pub.add;
583 RAISE fnd_api.g_exc_error;
584 ELSE
585 --Create a Salesperson for the Resource Created above
586 JTF_RS_SALESREPS_PUB.CREATE_SALESREP (
587 p_api_version => 1.0,
588 p_init_msg_list => null,
589 p_commit => null,
590 p_resource_id => l_resource_id,
591 p_sales_credit_type_id => p_imp_res_tbl(i).sales_credit_type_id,
592 p_salesrep_number => p_imp_res_tbl(i).salesperson_number,
593 p_name => p_imp_res_tbl(i).name,
594 p_status => 'A',
595 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
596 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
597 p_org_id => p_imp_res_tbl(i).org_id,
598 p_gl_id_rev => null,
599 p_gl_id_freight => null,
600 p_gl_id_rec => null,
601 p_set_of_books_id => null,
602 p_email_address => null,
603 p_wh_update_date => null,
604 p_sales_tax_geocode => null,
605 p_sales_tax_inside_city_limits => null,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data,
609 x_salesrep_id => l_salesrep_id
610 );
611 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
612 RAISE fnd_api.g_exc_error;
613 END IF;
614 END IF;
615 END IF;
616
617 IF (emp_rec.job_id IS NOT NULL) THEN
618 -- Get the Effective Start Date for the Current Job Assignment of this Employee.
619 OPEN c_get_asg_start_date (emp_rec.job_id, emp_rec.person_id, emp_rec.assignment_id, l_assignment_type);
620 FETCH c_get_asg_start_date INTO l_asg_start_date;
621 CLOSE c_get_asg_start_date;
622
623 FOR j in c_job_roles(emp_rec.job_id) LOOP
624 IF(j.role_id is not null) THEN
625 -- do not create roles of type SALES_COMP if Sales Rep has not been created -- bug 3579147
626 -- to avoid Vert User Hook error , sudarsana 8th July 2004
627 IF(j.role_type_code = 'SALES_COMP' AND
628 p_imp_res_tbl(i).create_salesperson <> 'Y')
629 THEN
630 null;
631 ELSE
632 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
633 p_api_version => 1.0,
634 p_init_msg_list => null,
638 p_role_id => j.role_id,
635 p_commit => null,
636 p_role_resource_type => 'RS_INDIVIDUAL',
637 p_role_resource_id => l_resource_id,
639 p_start_date_active => l_asg_start_date,
640 p_end_date_active => null,
641 x_return_status => l_return_status,
642 x_msg_count => l_msg_count,
643 x_msg_data => l_msg_data,
644 x_role_relate_id => l_role_relate_id
645 );
646 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
647 RAISE fnd_api.g_exc_error;
648 END IF; -- end of check for l_return_status
649 END IF; -- end of check for SALES_COMP
650 END IF; -- end of check for j.role_id
651 END LOOP;
652 END IF;
653
654 ELSIF (p_imp_res_tbl(i).category = 'PARTY') THEN
655 open par_cur(p_imp_res_tbl(i).person_id);
656 fetch par_cur INTO party_rec;
657 close par_cur;
658
659 -- added the party_contact cursor for bug 2954064, sudarsana 2nd July 04
660 open party_contact_cur(p_imp_res_tbl(i).person_id);
661 fetch party_contact_cur INTO party_contact_rec;
662 close party_contact_cur;
663
664 --insert into jtf resource extn by calling the public api
665 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
666 p_api_version => 1.0,
667 p_init_msg_list => null,
668 p_commit => null,
669 p_category => p_imp_res_tbl(i).category,
670 p_source_id => p_imp_res_tbl(i).person_id,
671 p_address_id => p_imp_res_tbl(i).address_id,
672 -- assigning party contact id for PARTY resource for bug 2954064 sudarsana 2nd July 04
673 p_contact_id => party_contact_rec.contact_id, --p_imp_res_tbl(i).contact_id,
674 p_managing_emp_id => l_managing_emp_id,
675 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
676 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
677 p_transaction_number => l_transaction_number,
678 p_user_id => p_imp_res_tbl(i).user_id,
679 x_return_status => l_return_status,
680 x_msg_count => l_msg_count,
681 x_msg_data => l_msg_data,
682 x_resource_id => l_resource_id,
683 x_resource_number => l_resource_number,
684 p_source_name => p_imp_res_tbl(i).name,
685 p_resource_name => p_imp_res_tbl(i).name,
686 p_source_number => party_rec.party_number,
687 p_source_phone => party_rec.phone,
688 p_source_email => party_rec.email_address,
689 p_source_job_title => null,
690 p_source_org_id => party_rec.org_id,
691 p_source_org_name => party_rec.org_name,
692 p_source_address1 => party_rec.address1,
693 p_source_address2 => party_rec.address2,
694 p_source_address3 => party_rec.address3,
695 p_source_address4 => party_rec.address4,
696 p_source_city => party_rec.city,
697 p_source_state => party_rec.state,
698 p_source_province => party_rec.province,
699 p_source_county => party_rec.county,
700 p_source_country => party_rec.country,
701 p_source_postal_code => party_rec.postal_code,
702 p_source_mgr_id => null,
703 p_source_mgr_name => null,
704 p_source_first_name => party_rec.person_first_name,
705 p_source_middle_name => party_rec.person_middle_name,
706 p_source_last_name => party_rec.person_last_name,
707 p_source_category => null,
708 p_source_status => null,
709 p_user_name => p_imp_res_tbl(i).user_name
710 );
711 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
712 RAISE fnd_api.g_exc_error;
713 END IF;
714
715 x_res_id_tbl(i).res_id := l_resource_id;
716
717 ELSIF (p_imp_res_tbl(i).category = 'PARTNER') THEN
718 open partner_cur(p_imp_res_tbl(i).person_id);
719 fetch partner_cur INTO partner_rec;
720 close partner_cur;
721
722 IF (p_imp_res_tbl(i).address_id IS NOT NULL) THEN
723 open partner_add_cur(p_imp_res_tbl(i).address_id);
724 fetch partner_add_cur INTO partner_add_rec;
725 close partner_add_cur;
726 l_address1 := partner_add_rec.address1;
727 l_address2 := partner_add_rec.address2;
728 l_address3 := partner_add_rec.address3;
729 l_address4 := partner_add_rec.address4;
730 l_city := partner_add_rec.city;
731 l_postal_code := partner_add_rec.postal_code;
732 l_state := partner_add_rec.state;
733 l_province := partner_add_rec.province;
734 l_county := partner_add_rec.county;
735 l_country := partner_add_rec.country;
736 ELSE
737 l_address1 := partner_rec.address1;
738 l_address2 := partner_rec.address2;
742 l_postal_code := partner_rec.postal_code;
739 l_address3 := partner_rec.address3;
740 l_address4 := partner_rec.address4;
741 l_city := partner_rec.city;
743 l_state := partner_rec.state;
744 l_province := partner_rec.province;
745 l_county := partner_rec.county;
746 l_country := partner_rec.country;
747 END IF;
748
749 --insert into jtf resource extn by calling the public api
750 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
751 p_api_version => 1.0,
752 p_init_msg_list => null,
753 p_commit => null,
754 p_category => p_imp_res_tbl(i).category,
755 p_source_id => p_imp_res_tbl(i).person_id,
756 p_address_id => p_imp_res_tbl(i).address_id,
757 p_contact_id => p_imp_res_tbl(i).contact_id,
758 p_managing_emp_id => l_managing_emp_id,
759 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
760 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
761 p_transaction_number => l_transaction_number,
762 p_user_id => p_imp_res_tbl(i).user_id,
763 x_return_status => l_return_status,
764 x_msg_count => l_msg_count,
765 x_msg_data => l_msg_data,
766 x_resource_id => l_resource_id,
767 x_resource_number => l_resource_number,
768 p_source_name => p_imp_res_tbl(i).name,
769 p_resource_name => p_imp_res_tbl(i).name,
770 p_source_number => partner_rec.party_number,
771 p_source_phone => partner_rec.phone,
772 p_source_email => partner_rec.email_address,
773 p_source_job_title => null,
774 p_source_org_id => partner_rec.org_id,
775 p_source_org_name => partner_rec.org_name,
776 p_source_address1 => l_address1,
777 p_source_address2 => l_address2,
778 p_source_address3 => l_address3,
779 p_source_address4 => l_address4,
780 p_source_city => l_city,
781 p_source_state => l_state,
782 p_source_province => l_province,
783 p_source_county => l_county,
784 p_source_country => l_country,
785 p_source_postal_code => l_postal_code,
786 p_source_mgr_id => null,
787 p_source_mgr_name => null,
788 p_source_first_name => partner_rec.person_first_name,
789 p_source_middle_name => partner_rec.person_middle_name,
790 p_source_last_name => partner_rec.person_last_name,
791 p_source_category => null,
792 p_source_status => null,
793 p_user_name => p_imp_res_tbl(i).user_name
794 );
795 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
796 RAISE fnd_api.g_exc_error;
797 END IF;
798
799 x_res_id_tbl(i).res_id := l_resource_id;
800
801 ELSIF (p_imp_res_tbl(i).category = 'SUPPLIER_CONTACT') THEN
802 open supp_cont_cur(p_imp_res_tbl(i).person_id,p_imp_res_tbl(i).address_id);
803 fetch supp_cont_cur into supp_cont_rec;
804 close supp_cont_cur;
805
806 --insert into jtf resource extn by calling the public api
807 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
808 p_api_version => 1.0,
809 p_init_msg_list => null,
810 p_commit => null,
811 p_category => p_imp_res_tbl(i).category,
812 p_source_id => p_imp_res_tbl(i).person_id,
813 p_address_id => p_imp_res_tbl(i).address_id,
814 p_contact_id => p_imp_res_tbl(i).contact_id,
815 p_managing_emp_id => l_managing_emp_id,
816 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
817 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
818 p_transaction_number => l_transaction_number,
819 p_user_id => p_imp_res_tbl(i).user_id,
820 x_return_status => l_return_status,
821 x_msg_count => l_msg_count,
822 x_msg_data => l_msg_data,
823 x_resource_id => l_resource_id,
824 x_resource_number => l_resource_number,
825 p_source_name => p_imp_res_tbl(i).name,
826 p_resource_name => p_imp_res_tbl(i).name,
827 p_source_number => supp_cont_rec.segment1,
828 p_source_phone => supp_cont_rec.phone,
829 p_source_email => supp_cont_rec.email_address,--null,
830 p_source_job_title => null,
831 p_source_org_id => supp_cont_rec.org_id,
832 p_source_org_name => supp_cont_rec.name,
833 p_source_address1 => supp_cont_rec.address_line1,
834 p_source_address2 => supp_cont_rec.address_line2,
835 p_source_address3 => supp_cont_rec.address_line3,
836 p_source_address4 => null,
837 p_source_city => supp_cont_rec.city,
838 p_source_state => supp_cont_rec.state,
839 p_source_province => supp_cont_rec.province,
840 p_source_county => supp_cont_rec.county,
841 p_source_country => supp_cont_rec.country,
842 p_source_postal_code => supp_cont_rec.zip,
843 p_source_mgr_id => null,
844 p_source_mgr_name => null,
845 p_source_first_name => supp_cont_rec.first_name,
846 p_source_middle_name => supp_cont_rec.middle_name,
847 p_source_last_name => supp_cont_rec.last_name,
848 p_source_category => null,
849 p_source_status => null,
850 p_user_name => p_imp_res_tbl(i).user_name
851 );
852 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
853 RAISE fnd_api.g_exc_error;
854 END IF;
855
856 x_res_id_tbl(i).res_id := l_resource_id;
857
858 ELSE
859 --insert into jtf resource extn by calling the public api
860 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
861 p_api_version => 1.0,
862 p_init_msg_list => null,
863 p_commit => null,
864 p_category => p_imp_res_tbl(i).category,
865 p_source_id => p_imp_res_tbl(i).person_id,
866 p_address_id => p_imp_res_tbl(i).address_id,
867 p_contact_id => p_imp_res_tbl(i).contact_id,
868 p_managing_emp_id => l_managing_emp_id,
869 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
870 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
871 p_transaction_number => l_transaction_number,
872 p_user_id => p_imp_res_tbl(i).user_id,
873 x_return_status => l_return_status,
874 x_msg_count => l_msg_count,
875 x_msg_data => l_msg_data,
876 x_resource_id => l_resource_id,
877 x_resource_number => l_resource_number,
878 p_source_name => p_imp_res_tbl(i).name,
879 p_resource_name => p_imp_res_tbl(i).name,
880 p_user_name => p_imp_res_tbl(i).user_name
881 );
882 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
883 RAISE fnd_api.g_exc_error;
884 END IF;
885
886 x_res_id_tbl(i).res_id := l_resource_id;
887
888 END IF;
889
890 IF(p_imp_res_tbl(i).role_id is not null) THEN
891 --insert into role relate table
892 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
893 p_api_version => 1.0,
894 p_init_msg_list => null,
895 p_commit => null,
896 p_role_resource_type => 'RS_INDIVIDUAL',
897 p_role_resource_id => l_resource_id,
898 p_role_id => p_imp_res_tbl(i).role_id,
899 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
900 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
901 x_return_status => l_return_status,
902 x_msg_count => l_msg_count,
903 x_msg_data => l_msg_data,
904 x_role_relate_id => l_role_relate_id
905 );
906 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
907 RAISE fnd_api.g_exc_error;
908 END IF;
909 END IF;
910 END IF;
911 END IF;
912
913 EXIT WHEN (i = p_imp_res_tbl.LAST);
914 i := p_imp_res_tbl.NEXT(i);
915 END LOOP;
916 END IF;
917
918 IF fnd_api.to_boolean(p_commit) THEN
919 COMMIT WORK;
920 END IF;
921
922 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
923
924 EXCEPTION
925 WHEN fnd_api.g_exc_error THEN
926 ROLLBACK TO import_resources;
927 x_return_status := fnd_api.g_ret_sts_error;
928 x_transaction_num := NULL;
929 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
930 p_data => x_msg_data);
931 WHEN fnd_api.g_exc_unexpected_error THEN
932 ROLLBACK TO import_resources;
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 x_transaction_num := NULL;
935 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
936 p_data => x_msg_data);
937 WHEN OTHERS THEN
938 ROLLBACK TO import_resources;
939 fnd_message.set_name ('JTF','JTF_RS_UNEXP_ERROR');
940 fnd_message.set_token('P_SQLCODE',SQLCODE);
941 fnd_message.set_token('P_SQLERRM',SQLERRM);
942 fnd_message.set_token('P_API_NAME',l_api_name);
943 FND_MSG_PUB.add;
944 x_return_status := fnd_api.g_ret_sts_unexp_error;
945 x_transaction_num := NULL;
946 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
947 p_data => x_msg_data);
948
949 END import_resources;
950
951 END jtf_rs_imp_res_pvt;