[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 2006/05/05 11:55:27 nsinghai 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,
8 P_INIT_MSG_LIST IN VARCHAR2,
9 P_COMMIT IN VARCHAR2,
10 P_IMP_RES_TBL IN imp_tbl_type,
11 X_RES_ID_TBL OUT NOCOPY res_id_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
136 ,loc.country country
137 ,loc.postal_code postal_code
138 ,asg.location_id address_id
139 ,asg.supervisor_id mgr_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')
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'
266 AND CT_POINT1.STATUS (+) = 'A'
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,
383 po_vendor_sites_all s,
380 c.last_name
381 FROM PO_VENDORS P,
382 po_vendor_contacts c,
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
391 CURSOR supp_cont_cur(l_person_id NUMBER) IS
392 SELECT p.segment1 ,
393 s.address_line1 ,
394 s.address_line2 ,
395 s.address_line3 ,
396 s.CITY,
397 s.STATE,
398 s.PROVINCE,
399 s.COUNTY,
400 s.COUNTRY,
401 rel.primary_phone_area_code ||' '|| rel.primary_phone_number phone,
402 s.zip,
403 s.org_id ,
404 o.name ,
405 per.person_FIRST_NAME first_name,
406 per.person_MIDDLE_NAME middle_name,
407 per.person_last_name last_name
408 FROM ap_suppliers P,
409 ap_supplier_contacts c,
410 ap_supplier_sites_all s,
411 hr_all_organization_units o,
412 hz_parties per,
413 hz_parties rel
414 WHERE c.vendor_contact_id = l_person_id
415 and c.vendor_site_id = s.vendor_site_id
416 and c.org_party_site_id = s.party_site_id
417 and per.party_id = c.per_party_id
418 and rel.party_id = c.rel_party_id
419 and s.vendor_id = p.vendor_id
420 and s.org_id = o.organization_id;
421
422 supp_cont_rec supp_cont_cur%rowtype;
423
424 i NUMBER;
425
426 BEGIN
427
428 SAVEPOINT import_resources;
429 x_return_status := fnd_api.g_ret_sts_success;
430
431 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
432 RAISE fnd_api.g_exc_unexpected_error;
433 END IF;
434
435 IF fnd_api.to_boolean(p_init_msg_list) THEN
436 fnd_msg_pub.initialize;
437 END IF;
438
439 -- initializing the transaction number
440 l_transaction_number := null;
441
442 IF p_imp_res_tbl.COUNT > 0 THEN
443 i := p_imp_res_tbl.FIRST;
444 LOOP
445
446 x_res_id_tbl(i).res_id := p_imp_res_tbl(i).resource_id;
447
448 IF (p_imp_res_tbl(i).selected = 'Y') THEN
449 IF (p_imp_res_tbl(i).comment_code = 'ROLE') THEN
450 --if resource with same role does not exist
451 IF(p_imp_res_tbl(i).role_id IS NOT NULL) THEN
452 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
453 p_api_version => 1.0,
454 p_init_msg_list => null,
455 p_commit => null,
456 p_role_resource_type => 'RS_INDIVIDUAL',
457 p_role_resource_id => p_imp_res_tbl(i).resource_id,
458 p_role_id => p_imp_res_tbl(i).role_id,
459 p_start_date_active => p_imp_res_tbl(i).role_start_date_active,
460 p_end_date_active => p_imp_res_tbl(i).role_end_date_active,
461 x_return_status => l_return_status,
462 x_msg_count => l_msg_count,
463 x_msg_data => l_msg_data,
464 x_role_relate_id => l_role_relate_id
465 );
466
467 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
468 RAISE fnd_api.g_exc_error;
469 END IF;
470 END IF;
471 ELSIF (p_imp_res_tbl(i).comment_code = 'NEW') THEN
472 --if resource does not exist for the person then insert both resource
473 -- Bug 3086823 : Transaction number should be created once for the entire set of records
474 IF l_transaction_number is null
475 THEN
476 SELECT jtf_rs_transaction_num_s.nextval
477 INTO l_transaction_number
478 FROM dual;
479 END IF;
480
481 x_transaction_num := l_transaction_number;
482
483 --if category is employee then fetch all the denormalized columns
484 IF (p_imp_res_tbl(i).category = 'EMPLOYEE') THEN
485 open emp_cur(p_imp_res_tbl(i).person_id);
486 fetch emp_cur INTO emp_rec;
487 IF emp_cur%NOTFOUND THEN
488 open future_emp_cur(p_imp_res_tbl(i).person_id);
489 fetch future_emp_cur INTO emp_rec;
490 close future_emp_cur;
491 END IF;
492 close emp_cur;
493
494
495 if emp_rec.assignment_type = 'E' then
496 l_source_number := emp_rec.employee_num;
497 l_assignment_type := 'E';
498 else
499 l_source_number := emp_rec.npw_number;
500 l_assignment_type := 'C';
501 end if;
502
503 open phone_cur(p_imp_res_tbl(i).person_id);
504 fetch phone_cur INTO phone_rec;
505 close phone_cur;
506
507 -- Adding the parts for mobile and pager (repuri 10/16/01)
508
509 open mobile_cur(p_imp_res_tbl(i).person_id);
510 fetch mobile_cur INTO mobile_rec;
511 close mobile_cur;
515 close pager_cur;
512
513 open pager_cur(p_imp_res_tbl(i).person_id);
514 fetch pager_cur INTO pager_rec;
516
517 --insert into jtf resource extn by calling the public api
518 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
519 p_api_version => 1.0,
520 p_init_msg_list => null,
521 p_commit => null,
522 p_category => p_imp_res_tbl(i).category,
523 p_source_id => p_imp_res_tbl(i).person_id,
524 p_address_id => emp_rec.address_id,
525 p_contact_id => p_imp_res_tbl(i).contact_id,
526 p_managing_emp_id => l_managing_emp_id,
527 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
528 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
529 p_transaction_number => l_transaction_number,
530 p_user_id => p_imp_res_tbl(i).user_id,
531 x_return_status => l_return_status,
532 x_msg_count => l_msg_count,
533 x_msg_data => l_msg_data,
534 x_resource_id => l_resource_id,
535 x_resource_number => l_resource_number,
536 p_source_name => p_imp_res_tbl(i).name,
537 p_resource_name => p_imp_res_tbl(i).name,
538 p_source_number => l_source_number,
539 p_source_phone => phone_rec.phone_number,
540 p_source_mobile_phone => mobile_rec.phone_number,
541 p_source_pager => pager_rec.phone_number,
542 p_source_email => emp_rec.email_address,
543 p_source_job_title => emp_rec.job_title,
544 p_source_business_grp_id => emp_rec.org_id,
545 p_source_business_grp_name => emp_rec.org_name,
546 p_source_address1 => emp_rec.address1,
547 p_source_address2 => emp_rec.address2,
548 p_source_address3 => emp_rec.address3,
549 p_source_city => emp_rec.city,
550 p_source_country => emp_rec.country,
551 p_source_postal_code => emp_rec.postal_code,
552 p_source_mgr_id => emp_rec.mgr_id,
553 p_source_mgr_name => emp_rec.mgr_name,
554 p_source_first_name => emp_rec.first_name,
555 p_source_middle_name => emp_rec.middle_name,
556 p_source_last_name => emp_rec.last_name,
557 p_source_category => null,
558 p_source_status => null,
559 p_user_name => p_imp_res_tbl(i).user_name,
560 p_source_mailstop => emp_rec.mailstop,
561 p_source_office => emp_rec.office_number,
562 p_source_location => emp_rec.internal_location
563 );
564 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
565 RAISE fnd_api.g_exc_error;
566 END IF;
567
568 x_res_id_tbl(i).res_id := l_resource_id;
569
570 IF (p_imp_res_tbl(i).create_salesperson = 'Y') THEN
571 IF p_imp_res_tbl(i).salesperson_number IS NULL THEN
572 fnd_message.set_name('JTF','JTF_RS_NO_SRP_NUM_ENT_FOR_EMP');
573 fnd_message.set_token('P_EMP_NAME',p_imp_res_tbl(i).name);
574 fnd_msg_pub.add;
575 RAISE fnd_api.g_exc_error;
576 ELSE
577 --Create a Salesperson for the Resource Created above
578 JTF_RS_SALESREPS_PUB.CREATE_SALESREP (
579 p_api_version => 1.0,
580 p_init_msg_list => null,
581 p_commit => null,
582 p_resource_id => l_resource_id,
583 p_sales_credit_type_id => p_imp_res_tbl(i).sales_credit_type_id,
584 p_salesrep_number => p_imp_res_tbl(i).salesperson_number,
585 p_name => p_imp_res_tbl(i).name,
586 p_status => 'A',
587 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
588 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
589 p_org_id => p_imp_res_tbl(i).org_id,
590 p_gl_id_rev => null,
591 p_gl_id_freight => null,
592 p_gl_id_rec => null,
593 p_set_of_books_id => null,
594 p_email_address => null,
595 p_wh_update_date => null,
596 p_sales_tax_geocode => null,
597 p_sales_tax_inside_city_limits => null,
598 x_return_status => l_return_status,
599 x_msg_count => l_msg_count,
603 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
600 x_msg_data => l_msg_data,
601 x_salesrep_id => l_salesrep_id
602 );
604 RAISE fnd_api.g_exc_error;
605 END IF;
606 END IF;
607 END IF;
608
609 IF (emp_rec.job_id IS NOT NULL) THEN
610 -- Get the Effective Start Date for the Current Job Assignment of this Employee.
611 OPEN c_get_asg_start_date (emp_rec.job_id, emp_rec.person_id, emp_rec.assignment_id, l_assignment_type);
612 FETCH c_get_asg_start_date INTO l_asg_start_date;
613 CLOSE c_get_asg_start_date;
614
615 FOR j in c_job_roles(emp_rec.job_id) LOOP
616 IF(j.role_id is not null) THEN
617 -- do not create roles of type SALES_COMP if Sales Rep has not been created -- bug 3579147
618 -- to avoid Vert User Hook error , sudarsana 8th July 2004
619 IF(j.role_type_code = 'SALES_COMP' AND
620 p_imp_res_tbl(i).create_salesperson <> 'Y')
621 THEN
622 null;
623 ELSE
624 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
625 p_api_version => 1.0,
626 p_init_msg_list => null,
627 p_commit => null,
628 p_role_resource_type => 'RS_INDIVIDUAL',
629 p_role_resource_id => l_resource_id,
630 p_role_id => j.role_id,
631 p_start_date_active => l_asg_start_date,
632 p_end_date_active => null,
633 x_return_status => l_return_status,
634 x_msg_count => l_msg_count,
635 x_msg_data => l_msg_data,
636 x_role_relate_id => l_role_relate_id
637 );
638 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
639 RAISE fnd_api.g_exc_error;
640 END IF; -- end of check for l_return_status
641 END IF; -- end of check for SALES_COMP
642 END IF; -- end of check for j.role_id
643 END LOOP;
644 END IF;
645
646 ELSIF (p_imp_res_tbl(i).category = 'PARTY') THEN
647 open par_cur(p_imp_res_tbl(i).person_id);
648 fetch par_cur INTO party_rec;
649 close par_cur;
650
651 -- added the party_contact cursor for bug 2954064, sudarsana 2nd July 04
652 open party_contact_cur(p_imp_res_tbl(i).person_id);
653 fetch party_contact_cur INTO party_contact_rec;
654 close party_contact_cur;
655
656 --insert into jtf resource extn by calling the public api
657 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
658 p_api_version => 1.0,
659 p_init_msg_list => null,
660 p_commit => null,
661 p_category => p_imp_res_tbl(i).category,
662 p_source_id => p_imp_res_tbl(i).person_id,
663 p_address_id => p_imp_res_tbl(i).address_id,
664 -- assigning party contact id for PARTY resource for bug 2954064 sudarsana 2nd July 04
665 p_contact_id => party_contact_rec.contact_id, --p_imp_res_tbl(i).contact_id,
666 p_managing_emp_id => l_managing_emp_id,
667 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
668 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
669 p_transaction_number => l_transaction_number,
670 p_user_id => p_imp_res_tbl(i).user_id,
671 x_return_status => l_return_status,
672 x_msg_count => l_msg_count,
673 x_msg_data => l_msg_data,
674 x_resource_id => l_resource_id,
675 x_resource_number => l_resource_number,
676 p_source_name => p_imp_res_tbl(i).name,
677 p_resource_name => p_imp_res_tbl(i).name,
678 p_source_number => party_rec.party_number,
679 p_source_phone => party_rec.phone,
680 p_source_email => party_rec.email_address,
681 p_source_job_title => null,
682 p_source_org_id => party_rec.org_id,
683 p_source_org_name => party_rec.org_name,
684 p_source_address1 => party_rec.address1,
685 p_source_address2 => party_rec.address2,
686 p_source_address3 => party_rec.address3,
687 p_source_address4 => party_rec.address4,
688 p_source_city => party_rec.city,
689 p_source_state => party_rec.state,
690 p_source_province => party_rec.province,
691 p_source_county => party_rec.county,
692 p_source_country => party_rec.country,
693 p_source_postal_code => party_rec.postal_code,
697 p_source_middle_name => party_rec.person_middle_name,
694 p_source_mgr_id => null,
695 p_source_mgr_name => null,
696 p_source_first_name => party_rec.person_first_name,
698 p_source_last_name => party_rec.person_last_name,
699 p_source_category => null,
700 p_source_status => null,
701 p_user_name => p_imp_res_tbl(i).user_name
702 );
703 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
704 RAISE fnd_api.g_exc_error;
705 END IF;
706
707 x_res_id_tbl(i).res_id := l_resource_id;
708
709 ELSIF (p_imp_res_tbl(i).category = 'PARTNER') THEN
710 open partner_cur(p_imp_res_tbl(i).person_id);
711 fetch partner_cur INTO partner_rec;
712 close partner_cur;
713
714 IF (p_imp_res_tbl(i).address_id IS NOT NULL) THEN
715 open partner_add_cur(p_imp_res_tbl(i).address_id);
716 fetch partner_add_cur INTO partner_add_rec;
717 close partner_add_cur;
718 l_address1 := partner_add_rec.address1;
719 l_address2 := partner_add_rec.address2;
720 l_address3 := partner_add_rec.address3;
721 l_address4 := partner_add_rec.address4;
722 l_city := partner_add_rec.city;
723 l_postal_code := partner_add_rec.postal_code;
724 l_state := partner_add_rec.state;
725 l_province := partner_add_rec.province;
726 l_county := partner_add_rec.county;
727 l_country := partner_add_rec.country;
728 ELSE
729 l_address1 := partner_rec.address1;
730 l_address2 := partner_rec.address2;
731 l_address3 := partner_rec.address3;
732 l_address4 := partner_rec.address4;
733 l_city := partner_rec.city;
734 l_postal_code := partner_rec.postal_code;
735 l_state := partner_rec.state;
736 l_province := partner_rec.province;
737 l_county := partner_rec.county;
738 l_country := partner_rec.country;
739 END IF;
740
741 --insert into jtf resource extn by calling the public api
742 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
743 p_api_version => 1.0,
744 p_init_msg_list => null,
745 p_commit => null,
746 p_category => p_imp_res_tbl(i).category,
747 p_source_id => p_imp_res_tbl(i).person_id,
748 p_address_id => p_imp_res_tbl(i).address_id,
749 p_contact_id => p_imp_res_tbl(i).contact_id,
750 p_managing_emp_id => l_managing_emp_id,
751 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
752 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
753 p_transaction_number => l_transaction_number,
754 p_user_id => p_imp_res_tbl(i).user_id,
755 x_return_status => l_return_status,
756 x_msg_count => l_msg_count,
757 x_msg_data => l_msg_data,
758 x_resource_id => l_resource_id,
759 x_resource_number => l_resource_number,
760 p_source_name => p_imp_res_tbl(i).name,
761 p_resource_name => p_imp_res_tbl(i).name,
762 p_source_number => partner_rec.party_number,
763 p_source_phone => partner_rec.phone,
764 p_source_email => partner_rec.email_address,
765 p_source_job_title => null,
766 p_source_org_id => partner_rec.org_id,
767 p_source_org_name => partner_rec.org_name,
768 p_source_address1 => l_address1,
769 p_source_address2 => l_address2,
770 p_source_address3 => l_address3,
771 p_source_address4 => l_address4,
772 p_source_city => l_city,
773 p_source_state => l_state,
774 p_source_province => l_province,
775 p_source_county => l_county,
776 p_source_country => l_country,
777 p_source_postal_code => l_postal_code,
778 p_source_mgr_id => null,
779 p_source_mgr_name => null,
780 p_source_first_name => partner_rec.person_first_name,
781 p_source_middle_name => partner_rec.person_middle_name,
782 p_source_last_name => partner_rec.person_last_name,
783 p_source_category => null,
784 p_source_status => null,
785 p_user_name => p_imp_res_tbl(i).user_name
786 );
787 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
788 RAISE fnd_api.g_exc_error;
789 END IF;
790
791 x_res_id_tbl(i).res_id := l_resource_id;
792
793 ELSIF (p_imp_res_tbl(i).category = 'SUPPLIER_CONTACT') THEN
794 open supp_cont_cur(p_imp_res_tbl(i).person_id);
795 fetch supp_cont_cur into supp_cont_rec;
796 close supp_cont_cur;
797
798 --insert into jtf resource extn by calling the public api
799 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
800 p_api_version => 1.0,
801 p_init_msg_list => null,
802 p_commit => null,
803 p_category => p_imp_res_tbl(i).category,
804 p_source_id => p_imp_res_tbl(i).person_id,
805 p_address_id => p_imp_res_tbl(i).address_id,
806 p_contact_id => p_imp_res_tbl(i).contact_id,
807 p_managing_emp_id => l_managing_emp_id,
808 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
809 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
810 p_transaction_number => l_transaction_number,
811 p_user_id => p_imp_res_tbl(i).user_id,
812 x_return_status => l_return_status,
813 x_msg_count => l_msg_count,
814 x_msg_data => l_msg_data,
815 x_resource_id => l_resource_id,
816 x_resource_number => l_resource_number,
817 p_source_name => p_imp_res_tbl(i).name,
818 p_resource_name => p_imp_res_tbl(i).name,
819 p_source_number => supp_cont_rec.segment1,
820 p_source_phone => supp_cont_rec.phone,
821 p_source_email => null,
822 p_source_job_title => null,
823 p_source_org_id => supp_cont_rec.org_id,
824 p_source_org_name => supp_cont_rec.name,
825 p_source_address1 => supp_cont_rec.address_line1,
826 p_source_address2 => supp_cont_rec.address_line2,
827 p_source_address3 => supp_cont_rec.address_line3,
828 p_source_address4 => null,
829 p_source_city => supp_cont_rec.city,
830 p_source_state => supp_cont_rec.state,
831 p_source_province => supp_cont_rec.province,
832 p_source_county => supp_cont_rec.county,
833 p_source_country => supp_cont_rec.country,
834 p_source_postal_code => supp_cont_rec.zip,
835 p_source_mgr_id => null,
836 p_source_mgr_name => null,
837 p_source_first_name => supp_cont_rec.first_name,
838 p_source_middle_name => supp_cont_rec.middle_name,
839 p_source_last_name => supp_cont_rec.last_name,
840 p_source_category => null,
841 p_source_status => null,
842 p_user_name => p_imp_res_tbl(i).user_name
843 );
844 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
845 RAISE fnd_api.g_exc_error;
846 END IF;
847
848 x_res_id_tbl(i).res_id := l_resource_id;
849
850 ELSE
851 --insert into jtf resource extn by calling the public api
852 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
853 p_api_version => 1.0,
854 p_init_msg_list => null,
855 p_commit => null,
856 p_category => p_imp_res_tbl(i).category,
857 p_source_id => p_imp_res_tbl(i).person_id,
858 p_address_id => p_imp_res_tbl(i).address_id,
859 p_contact_id => p_imp_res_tbl(i).contact_id,
860 p_managing_emp_id => l_managing_emp_id,
861 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
862 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
863 p_transaction_number => l_transaction_number,
864 p_user_id => p_imp_res_tbl(i).user_id,
865 x_return_status => l_return_status,
866 x_msg_count => l_msg_count,
867 x_msg_data => l_msg_data,
868 x_resource_id => l_resource_id,
869 x_resource_number => l_resource_number,
870 p_source_name => p_imp_res_tbl(i).name,
871 p_resource_name => p_imp_res_tbl(i).name,
872 p_user_name => p_imp_res_tbl(i).user_name
873 );
874 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
875 RAISE fnd_api.g_exc_error;
876 END IF;
877
878 x_res_id_tbl(i).res_id := l_resource_id;
879
880 END IF;
881
882 IF(p_imp_res_tbl(i).role_id is not null) THEN
883 --insert into role relate table
884 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
885 p_api_version => 1.0,
886 p_init_msg_list => null,
887 p_commit => null,
888 p_role_resource_type => 'RS_INDIVIDUAL',
889 p_role_resource_id => l_resource_id,
890 p_role_id => p_imp_res_tbl(i).role_id,
891 p_start_date_active => p_imp_res_tbl(i).rs_start_date_active,
892 p_end_date_active => p_imp_res_tbl(i).rs_end_date_active,
893 x_return_status => l_return_status,
894 x_msg_count => l_msg_count,
895 x_msg_data => l_msg_data,
896 x_role_relate_id => l_role_relate_id
897 );
898 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
899 RAISE fnd_api.g_exc_error;
900 END IF;
901 END IF;
902 END IF;
903 END IF;
904
905 EXIT WHEN (i = p_imp_res_tbl.LAST);
906 i := p_imp_res_tbl.NEXT(i);
907 END LOOP;
908 END IF;
909
910 IF fnd_api.to_boolean(p_commit) THEN
911 COMMIT WORK;
912 END IF;
913
914 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
915
916 EXCEPTION
917 WHEN fnd_api.g_exc_error THEN
918 ROLLBACK TO import_resources;
919 x_return_status := fnd_api.g_ret_sts_error;
920 x_transaction_num := NULL;
921 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
922 p_data => x_msg_data);
923 WHEN fnd_api.g_exc_unexpected_error THEN
924 ROLLBACK TO import_resources;
925 x_return_status := fnd_api.g_ret_sts_unexp_error;
926 x_transaction_num := NULL;
927 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
928 p_data => x_msg_data);
929 WHEN OTHERS THEN
930 ROLLBACK TO import_resources;
931 fnd_message.set_name ('JTF','JTF_RS_UNEXP_ERROR');
932 fnd_message.set_token('P_SQLCODE',SQLCODE);
933 fnd_message.set_token('P_SQLERRM',SQLERRM);
934 fnd_message.set_token('P_API_NAME',l_api_name);
935 FND_MSG_PUB.add;
936 x_return_status := fnd_api.g_ret_sts_unexp_error;
937 x_transaction_num := NULL;
938 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
939 p_data => x_msg_data);
940
941 END import_resources;
942
943 END jtf_rs_imp_res_pvt;