[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_PUB
Source
1 PACKAGE BODY jtf_rs_resource_pub AS
2 /* $Header: jtfrsprb.pls 120.7 2011/03/04 11:27:21 rgokavar ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resources.
6 Its main procedures are as following:
7 Create Resource
8 Update Resource
9 This package validates the input parameters to these procedures and then
10 Calls corresponding procedures from jtf_rs_resource_pvt to do business
11 validations and to do actual inserts, updates and deletes into tables.
12 ******************************************************************************************/
13
14
15 /* Package variables. */
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_PUB';
18
19
20 /* Procedure to create the resource based on input values
21 passed by calling routines. */
22 Function get_wf_role ( resource_id in number )
23 RETURN varchar2 IS
24
25 cursor c1 is select select_id , from_table , where_clause from jtf_objects_vl
26 where object_code in ( select object_code from jtf_object_usages
27 where object_user_code = 'RESOURCE_WORKFLOW' ) ;
28
29 --Adding this cursor to display the resource name in the message (repuri 03/12/01)
30 cursor c_rs_name (l_resource_id IN NUMBER) IS
31 SELECT resource_name from jtf_rs_resource_extns_tl
32 WHERE resource_id = l_resource_id
33 AND language = userenv ('LANG');
34
35 x number := 0;
36 select_statement varchar2(2000) := null ;
37 wf_rolename varchar2(60);
38
39 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
40 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
41
42 -- variables for dynamic bind to query
43 TYPE bind_rec_type IS RECORD (bind_value NUMBER);
44 TYPE bind_tbl_type IS TABLE OF bind_rec_type INDEX BY binary_integer;
45 bind_table bind_tbl_type;
46
47 BEGIN
48
49 l_resource_id := resource_id;
50
51 OPEN c_rs_name (l_resource_id);
52 FETCH c_rs_name INTO l_resource_name;
53 CLOSE c_rs_name;
54
55 --dbms_output.put_line('Resource Name - '||l_resource_name);
56
57 for i in c1 loop
58 if x <> 0 then
59 select_statement := select_statement || ' union ' ;
60 end if ;
61 /* BINDVAR_SCAN_IGNORE [1] */
62 select_statement := select_statement || ' select '||i.select_id||' from '||i.from_table
63 ||' where '||i.where_clause ||' and rs.resource_id = :x_resource_id ';
64 x := x + 1;
65 bind_table(x).bind_value := l_resource_id;
66
67 end loop;
68
69 -- Fix for Bug 4673722 (21-Oct-2005), changed the resource_id to bind variable (for perf).
70 -- Done after discussion with Hari regarding number of records to be supported. Since this
71 -- method of fetching wf_roles is obsoleted, it will not be enhanced in future regarding
72 -- seed data. Currently only 2 rows are seeded in jtf_objects_vl. As a buffer providing
73 -- extra 3 bind queries.
74 IF (x = 1) THEN
75 EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value;
76 ELSIF (x = 2) THEN
77 EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
78 bind_table(2).bind_value;
79 ELSIF (x = 3) THEN
80 EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
81 bind_table(2).bind_value,
82 bind_table(3).bind_value;
83 ELSIF (x = 4) THEN
84 EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
85 bind_table(2).bind_value,
86 bind_table(3).bind_value,
87 bind_table(4).bind_value;
88 ELSIF (x = 5) THEN
89 EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
90 bind_table(2).bind_value,
91 bind_table(3).bind_value,
92 bind_table(4).bind_value,
93 bind_table(5).bind_value;
94 END IF;
95
96 RETURN wf_rolename ;
97
98 exception
99 when no_data_found then
100 fnd_message.set_name('JTF', 'JTF_RS_ROLE_NOTFOUND');
101 fnd_message.set_token('P_RESOURCE_NAME', l_resource_name);
102 fnd_msg_pub.add;
103 return null ;
104 when too_many_rows then
105 fnd_message.set_name('JTF', 'JTF_RS_MORE_WF_ROLES');
106 fnd_message.set_token('P_RESOURCE_NAME', l_resource_name);
107 fnd_msg_pub.add;
108 return null ;
109 when others then
110 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_GET_ROLE_ERR');
111 fnd_msg_pub.add;
112 return null ;
113 END;
114
115
116 PROCEDURE create_resource
117 (P_API_VERSION IN NUMBER,
118 P_INIT_MSG_LIST IN VARCHAR2,
119 P_COMMIT IN VARCHAR2,
120 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
121 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
122 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
123 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
124 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
125 P_MANAGING_EMP_NUM IN PER_EMPLOYEES_CURRENT_X.EMPLOYEE_NUM%TYPE,
126 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
127 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
128 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
129 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
130 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
131 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
132 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
133 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
134 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
135 P_INTERACTION_CENTER_NAME IN VARCHAR2,
136 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
137 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
138 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
139 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
140 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
141 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
142 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
143 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
144 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
145 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE,
146 --P_LOCATION IN MDSYS.SDO_GEOMETRY DEFAULT NULL,
147 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
148 X_MSG_COUNT OUT NOCOPY NUMBER,
149 X_MSG_DATA OUT NOCOPY VARCHAR2,
150 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
151 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE
152 ) IS
153
154 l_api_version CONSTANT NUMBER := 1.0;
155 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
156 l_category jtf_rs_resource_extns.category%TYPE;
157 l_source_id jtf_rs_resource_extns.source_id%TYPE;
158 l_address_id jtf_rs_resource_extns.address_id%TYPE;
159 l_contact_id jtf_rs_resource_extns.contact_id%TYPE;
160 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
161 l_managing_emp_num per_employees_current_x.employee_num%TYPE;
162 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
163 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
164 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
165 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
166 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
167 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
168 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
169 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
170 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
171 l_interaction_center_name VARCHAR2(256);
172 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
173 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
174 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
175 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
176 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
177 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
178 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
179 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
180 l_user_id jtf_rs_resource_extns.user_id%TYPE;
181 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
182 --l_location MDSYS.SDO_GEOMETRY := p_location;
183
184 --added for NOCOPY
185 l_managing_emp_id_out jtf_rs_resource_extns.managing_employee_id%TYPE ;
186 l_server_group_id_out jtf_rs_resource_extns.server_group_id%TYPE ;
187 l_comp_service_team_id_out jtf_rs_resource_extns.comp_service_team_id%TYPE;
188
189 l_check_flag VARCHAR2(1);
190 l_source_name VARCHAR2(2000);
191 l_found BOOLEAN;
192
193
194 /* Changed from view to direct table query stripping out unnecessary table joins
195 for SQL Rep perf bug 4956627. Query logic taken from view JTF_RS_PARTNERS_VL.
196 Nishant Singhai (13-Mar-2006)
197 */
198 /*
199 CURSOR c_validate_partner(
200 l_party_id IN NUMBER)
201 IS
202 SELECT 'Y'
203 FROM jtf_rs_partners_vl
204 WHERE party_id = l_party_id;
205 */
206 CURSOR c_validate_partner(l_party_id IN NUMBER)
207 IS
208 SELECT 'Y'
209 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
210 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
211 WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' AND PARTY.PARTY_ID = REL.SUBJECT_ID)
212 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
213 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
214 'CUSTOMER_INDIRECTLY_MANAGED_BY')
215 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
216 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
217 AND REL.DIRECTIONAL_FLAG = 'F'
218 AND REL.STATUS = 'A'
219 AND PARTY.STATUS = 'A'
220 AND PARTY2.STATUS = 'A'
221 AND PARTY3.STATUS = 'A'
222 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
223 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
224 AND REL.OBJECT_ID = PARTY3.PARTY_ID
225 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
226 AND party.party_id = l_party_id
227 UNION ALL
228 SELECT 'Y'
229 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
230 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
231 WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND PARTY.PARTY_ID = REL.PARTY_ID )
232 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
233 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
234 'CUSTOMER_INDIRECTLY_MANAGED_BY')
235 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
236 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
237 AND REL.DIRECTIONAL_FLAG = 'F'
238 AND REL.STATUS = 'A'
239 AND PARTY.STATUS = 'A'
240 AND PARTY2.STATUS = 'A'
241 AND PARTY3.STATUS = 'A'
242 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
243 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
244 AND REL.OBJECT_ID = PARTY3.PARTY_ID
245 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
246 AND party.party_id = l_party_id
247 ;
248
249 CURSOR c_validate_partner_migr(
250 l_party_id IN NUMBER)
251 IS
252 SELECT 'Y',party_name
253 FROM jtf_rs_ptnr_migr_vl
254 WHERE party_id = l_party_id;
255
256 CURSOR c_validate_partner_address (
257 l_party_id IN NUMBER,
258 l_party_site_id IN NUMBER)
259 IS
260 SELECT 'Y'
261 FROM hz_party_sites
262 WHERE party_id = l_party_id
263 AND party_site_id = l_party_site_id;
264
265 CURSOR c_validate_partner_contact(
266 l_party_id IN NUMBER,
267 l_party_site_id IN NUMBER,
268 l_contact_id IN NUMBER)
269 IS
270 SELECT 'Y'
271 FROM jtf_rs_party_contacts_vl
272 WHERE party_id = l_party_id
273 AND nvl (party_site_id,-99) = nvl (l_party_site_id,-99)
274 AND contact_id = l_contact_id;
275
276 /* -- Direct query from tables. But does not improve performance or shared memory
277 -- significantly. So not using it as it will lead to dual maintainence (view + this logic)
278 -- Test performed for SQL Rep Bug 4956627
279
280 CURSOR c_validate_partner_contact(
281 l_party_id IN NUMBER,
282 l_party_site_id IN NUMBER,
283 l_contact_id IN NUMBER)
284 IS
285 SELECT 'Y'
286 -- SELECT PARTY.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID , ORG_CONT.ORG_CONTACT_ID CONTACT_ID ,
287 -- ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER , PARTY.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
288 FROM HZ_PARTIES PARTY , HZ_RELATIONSHIPS PARTY_REL , HZ_ORG_CONTACTS ORG_CONT ,
289 HZ_ORG_CONTACT_ROLES CONT_ROLE
290 WHERE PARTY.STATUS = 'A'
291 AND PARTY.PARTY_TYPE = 'PERSON'
292 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
293 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
294 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
295 AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
296 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
297 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
298 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
299 AND PARTY_REL.STATUS = 'A'
300 AND party.party_id = l_party_id
301 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
302 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
303 UNION ALL
304 SELECT 'Y'
305 -- SELECT PARTY5.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
306 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
307 -- PARTY5.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
308 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_PARTIES PARTY5 , HZ_RELATIONSHIPS PARTY_REL ,
309 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
310 WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
311 AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
312 AND PARTY5.STATUS = 'A'
313 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
314 AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
315 AND PARTY3.PARTY_TYPE = 'PERSON'
316 AND PARTY3.STATUS = 'A'
317 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
318 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
319 AND PARTY4.STATUS = 'A'
320 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
321 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
322 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
323 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
324 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
325 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
326 AND PARTY_REL.STATUS = 'A'
327 AND party5.party_id = l_party_id
328 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
329 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
330 UNION ALL
331 SELECT 'Y'
332 -- SELECT PARTY4.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
333 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
334 -- PARTY3.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
335 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_RELATIONSHIPS PARTY_REL ,
336 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
337 WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
338 AND PARTY3.PARTY_TYPE = 'PERSON'
339 AND PARTY3.STATUS = 'A'
340 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
341 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
342 AND PARTY4.STATUS = 'A'
343 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
344 AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
345 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
346 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
347 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
348 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
349 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
350 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
351 AND PARTY_REL.STATUS = 'A'
352 AND party4.party_id = l_party_id
353 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
354 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
355 ;
356 */
357 --
358
359
360
361 CURSOR c_validate_party_address(
362 l_party_site_id IN NUMBER )
363 IS
364 SELECT 'Y'
365 FROM hz_party_sites
366 WHERE party_site_id = l_party_site_id;
367
368
369 CURSOR c_validate_party_contact(
370 l_party_id IN NUMBER,
371 l_party_site_id IN NUMBER,
372 l_contact_id IN NUMBER)
373 IS
374 SELECT 'Y'
375 /* FROM jtf_rs_party_contacts_vl
376 WHERE party_id = l_party_id
377 AND nvl(party_site_id, 0) = nvl(l_party_site_id, 0)
378 AND contact_id = l_contact_id; */
379 -- changed the query the validate party contact id according to bug 2954064 as provided by the PRM team , sudarsana 2nd july 2004
380 FROM hz_relationships hzr,
381 hz_org_contacts hzoc
382 WHERE hzr.party_id = l_party_id
383 AND hzoc.org_contact_id = l_contact_id
384 AND hzr.directional_flag = 'F'
385 AND hzr.relationship_code = 'EMPLOYEE_OF'
386 AND hzr.subject_table_name ='HZ_PARTIES'
387 AND hzr.object_table_name ='HZ_PARTIES'
388 AND hzr.start_date <= SYSDATE
389 AND (hzr.end_date is null or hzr.end_date > SYSDATE)
390 AND hzr.status = 'A'
391 AND hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id;
392
393
394 /* SQL Rep perf improvement bug 4956627 Nishant Singhai (14-Mar-2006) fixed by
395 modifying query logic given in bug # 4052112
396 OIC expanded the definition of compensation analyst to include any active user in the
397 system regardless of their assignment to a CN responsibility.
398 */
399 CURSOR c_assigned_to_group_id(
400 l_assigned_to_group_id IN NUMBER)
401 IS
402 SELECT u.user_id
403 FROM fnd_user u,
404 jtf_rs_resource_extns r
405 WHERE u.user_id = r.user_id
406 AND u.user_id = l_assigned_to_group_id;
407
408
409 CURSOR c_validate_user_id(
410 l_user_id IN NUMBER)
411 IS
412 SELECT 'Y'
413 FROM jtf_rs_resource_extns
414 WHERE user_id = l_user_id;
415
416 -- Enh 3947611 2-dec-2004 added cursor to check emp existence
417 CURSOR c_emp_exist(p_person_id IN NUMBER)
418 IS
419 SELECT 'x' value,full_name
420 FROM per_all_people_f
421 WHERE person_id = p_person_id;
422
423 r_emp_exist c_emp_exist%rowtype;
424
425 BEGIN
426
427 l_category := upper(p_category);
428 l_source_id := p_source_id;
429 l_address_id := p_address_id;
430 l_contact_id := p_contact_id;
431 l_managing_emp_id := p_managing_emp_id;
432 l_managing_emp_num := p_managing_emp_num;
433 l_start_date_active := p_start_date_active;
434 l_end_date_active := p_end_date_active;
435 l_time_zone := p_time_zone;
436 l_cost_per_hr := p_cost_per_hr;
437 l_primary_language := p_primary_language;
438 l_secondary_language := p_secondary_language;
439 l_support_site_id := p_support_site_id;
440 l_ies_agent_login := p_ies_agent_login;
441 l_server_group_id := p_server_group_id;
442 l_interaction_center_name := p_interaction_center_name;
443 l_assigned_to_group_id := p_assigned_to_group_id;
444 l_cost_center := p_cost_center;
445 l_charge_to_cost_center := p_charge_to_cost_center;
446 l_comp_currency_code := p_comp_currency_code;
447 l_commissionable_flag := p_commissionable_flag;
448 l_hold_reason_code := p_hold_reason_code;
449 l_hold_payment := p_hold_payment;
450 l_comp_service_team_id := p_comp_service_team_id;
451 l_user_id := p_user_id;
452 l_transaction_number := p_transaction_number;
453
454
455 SAVEPOINT create_resource_pub;
456
457 x_return_status := fnd_api.g_ret_sts_success;
458
459 -- DBMS_OUTPUT.put_line(' Started Create Resource Pub ');
460
461
462
463 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
464
465 RAISE fnd_api.g_exc_unexpected_error;
466
467 END IF;
468
469
470 IF fnd_api.to_boolean(p_init_msg_list) THEN
471
472 fnd_msg_pub.initialize;
473
474 END IF;
475
476
477
478 /* Validate the Resource Category */
479
480 jtf_resource_utl.validate_resource_category(
481 p_category => l_category,
482 x_return_status => x_return_status
483 );
484
485
486 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
487 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
488 RAISE FND_API.G_EXC_ERROR;
489 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF;
492 END IF;
493
494 /* Validate Source ID */
495
496 jtf_resource_utl.validate_source_id (
497 p_category => l_category,
498 p_source_id => l_source_id,
499 p_address_id => l_address_id,
500 x_return_status => x_return_status
501 );
502
503 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
504 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
505 RAISE FND_API.G_EXC_ERROR;
506 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 END IF;
509 END IF;
510
511 /* Validations for category as OTHER and TBH */
512
513 IF l_category IN ('OTHER', 'TBH') THEN
514
515 /* Validate that the source_id, address_id, contact_id and managing_employee_id
516 are all NULL */
517
518 IF (l_source_id IS NOT NULL OR l_address_id IS NOT NULL
519 OR l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL
520 OR l_managing_emp_num IS NOT NULL) THEN
521
522 -- dbms_output.put_line('For OTHER category, source_id, address_id, contact_id and managing_emp_id should be all null');
523
524 fnd_message.set_name('JTF', 'JTF_RS_OTHER_IDS_NOT_NULL');
525 fnd_msg_pub.add;
526 RAISE fnd_api.g_exc_error;
527
528 END IF;
529
530 END IF;
531
532 /* Validations for category as PARTNER */
533
534 IF l_category = 'PARTNER' THEN
535
536 /* Validate the source_id */
537
538 IF (l_source_id IS NULL) THEN
539 -- dbms_output.put_line('For PARTNER category, source_id should not be null');
540 fnd_message.set_name('JTF', 'JTF_RS_PARTNER_IDS_NULL');
541 fnd_msg_pub.add;
542 RAISE fnd_api.g_exc_error;
543 ELSE
544 IF G_RS_ID_PUB_FLAG = 'Y' THEN --This flag is checked for migration purpose.
545 OPEN c_validate_partner(l_source_id);
546 FETCH c_validate_partner INTO l_check_flag;
547 IF c_validate_partner%NOTFOUND THEN
548 -- dbms_output.put_line('Partner does not exist for the passed source_id');
549 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTNER_IDS');
550 fnd_msg_pub.add;
551 RAISE fnd_api.g_exc_error;
552 END IF;
553 CLOSE c_validate_partner;
554 ELSIF G_RS_ID_PUB_FLAG = 'N' THEN
555 OPEN c_validate_partner_migr(l_source_id);
556 FETCH c_validate_partner_migr INTO l_check_flag,l_source_name;
557 JTF_RESOURCE_UTL.G_SOURCE_NAME := l_source_name;
558 IF c_validate_partner_migr%NOTFOUND THEN
559 -- dbms_output.put_line('Partner does not exist for the passed source_id');
560 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTNER_IDS');
561 fnd_msg_pub.add;
562 RAISE fnd_api.g_exc_error;
563 END IF;
564 CLOSE c_validate_partner_migr;
565 END IF;
566 END IF;
567
568 /* Validate the address_id if specified */
569
570 IF l_address_id IS NOT NULL THEN
571 OPEN c_validate_partner_address(l_source_id, l_address_id);
572 FETCH c_validate_partner_address INTO l_check_flag;
573 IF c_validate_partner_address%NOTFOUND THEN
574 -- dbms_output.put_line('Invalid Partner Address Id');
575 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_ADDRESS_ID');
576 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
577 fnd_msg_pub.add;
578 RAISE fnd_api.g_exc_error;
579 END IF;
580 CLOSE c_validate_partner_address;
581 END IF;
582
583
584 /* Validate the contact_id if specified */
585
586 IF l_contact_id IS NOT NULL THEN
587 OPEN c_validate_partner_contact(l_source_id, l_address_id, l_contact_id);
588 FETCH c_validate_partner_contact INTO l_check_flag;
589 IF c_validate_partner_contact%NOTFOUND THEN
590 -- dbms_output.put_line('Invalid Partner Contact Id');
591 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_CONTACT_ID');
592 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
593 fnd_msg_pub.add;
594 RAISE fnd_api.g_exc_error;
595 END IF;
596 CLOSE c_validate_partner_contact;
597 END IF;
598
599 END IF;
600
601 /* For all other Categories, validate the source_id from jtf_objects */
602 /* Enh 3947611 2-dec-2004 : added EMPLOYEE to the exception also. Import future dated employees
603 this had to be an exception else the seed data for object EMPLOYEE if jtf_objects had to be changed. This may have
604 some backward compatibility issues for consumers who use JTF_OBJECTS to validate OR list EMPLOYEE
605 */
606 IF l_category NOT IN ('OTHER' , 'PARTNER' , 'TBH', 'EMPLOYEE') THEN
607 IF l_source_id IS NULL THEN
608 -- dbms_output.put_line('Source Id should not be Null');
609 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
610 fnd_msg_pub.add;
611 RAISE fnd_api.g_exc_error;
612 END IF;
613
614 jtf_resource_utl.check_object_existence_migr(
615
616 P_OBJECT_CODE => l_category,
617 P_SELECT_ID => l_source_id,
618 P_OBJECT_USER_CODE => 'RESOURCE_CATEGORIES',
619 P_RS_ID_PUB_FLAG => G_RS_ID_PUB_FLAG,
620 X_FOUND => l_found,
621 X_RETURN_STATUS => x_return_status
622 );
623
624 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
625 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
626 RAISE FND_API.G_EXC_ERROR;
627 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630 END IF;
631
632 IF l_found = FALSE THEN
633 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
634 fnd_message.set_token('P_SOURCE_ID', l_source_id);
635 fnd_msg_pub.add;
636 RAISE fnd_api.g_exc_error;
637 END IF;
638
639 END IF;
640
641 /* Enh 3947611 2-dec-2004:EMPLOYEE VALIDATION has been removed from the above code. so adding validation
642 for EMPLOYEE
643 */
644
645 if l_category = 'EMPLOYEE' THEN
646 -- First check is null check for source id
647 IF l_source_id IS NULL THEN
648 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
649 fnd_msg_pub.add;
650 RAISE fnd_api.g_exc_error;
651 END IF;
652
653 open c_emp_exist(l_source_id);
654 fetch c_emp_exist into r_emp_exist;
655 close c_emp_exist;
656
657 if(nvl(r_emp_exist.value , 'y') <> 'x')
658 then
659 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
660 fnd_message.set_token('P_SOURCE_ID', l_source_id);
661 fnd_msg_pub.add;
662 RAISE fnd_api.g_exc_error;
663 end if;
664
665 END IF; -- end of check l_category = 'EMPLOYEE'
666
667 /* Validations for category as PARTY */
668
669 IF l_category = 'PARTY' THEN
670
671 /* Validate the address_id if specified */
672
673 IF l_address_id IS NOT NULL THEN
674
675 OPEN c_validate_party_address(l_address_id);
676
677 FETCH c_validate_party_address INTO l_check_flag;
678
679
680 IF c_validate_party_address%NOTFOUND THEN
681
682 -- dbms_output.put_line('Invalid Party Address');
683
684 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTY_ADDRESS');
685 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
686 fnd_msg_pub.add;
687
688 RAISE fnd_api.g_exc_error;
689
690 END IF;
691
692 /* Close the cursor */
693
694 CLOSE c_validate_party_address;
695
696
697 END IF;
698
699
700 /* Validate the contact_id if specified */
701
702 IF l_contact_id IS NOT NULL THEN
703
704 OPEN c_validate_party_contact(l_source_id, l_address_id, l_contact_id);
705
706 FETCH c_validate_party_contact INTO l_check_flag;
707
708
709 IF c_validate_party_contact%NOTFOUND THEN
710
711 -- dbms_output.put_line('Invalid Party Contact Id');
712
713 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTY_CONTACT_ID');
714 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
715 fnd_msg_pub.add;
716
717 RAISE fnd_api.g_exc_error;
718
719 END IF;
720
721
722 /* Close the cursor */
723
724 CLOSE c_validate_party_contact;
725
726 END IF;
727
728 END IF;
729
730
731
732 /* Validations for category as SUPPLIER_CONTACT */
733
734 IF l_category = 'SUPPLIER_CONTACT' THEN
735
736 /* Validate that the address_id and contact_id are NULL */
737
738 -- address_id check (NOT NULL) being removed, to store the address_id of supplier contact
739 -- Fix for bug # 3812930
740 IF (l_contact_id IS NOT NULL) THEN
741
742 -- dbms_output.put_line('For SUPPLIER_CONTACT category, address_id and contact_id should be null');
743
744 fnd_message.set_name('JTF', 'JTF_RS_SC_IDS_NOT_NULL');
745 fnd_msg_pub.add;
746
747 RAISE fnd_api.g_exc_error;
748
749 END IF;
750
751 END IF;
752
753
754 /* Validations for category as EMPLOYEE */
755 /* Removed 'WORKER' from the below code to fix bug # 3455951 */
756 IF l_category = 'EMPLOYEE' THEN
757
758 /* Validate that the address_id, contact_id and managing_emp_id are NULL */
759
760 --address_id check (null) being removed, to store the address_id of employee 03/26/01
761
762 IF (l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL OR l_managing_emp_num IS NOT NULL) THEN
763
764 -- dbms_output.put_line('For EMPLOYEE category, contact_id should be null');
765
766 fnd_message.set_name('JTF', 'JTF_RS_EMP_IDS_NOT_NULL');
767 fnd_msg_pub.add;
768
769 RAISE fnd_api.g_exc_error;
770
771 END IF;
772
773 END IF;
774
775
776
777 /* Validate the Managing Employee Id if specified */
778
779 jtf_resource_utl.validate_employee_resource(
780 p_emp_resource_id => l_managing_emp_id,
781 p_emp_resource_number => l_managing_emp_num,
782 x_return_status => x_return_status,
783 x_emp_resource_id => l_managing_emp_id_out
784 );
785 -- added for NOCOPY
786 l_managing_emp_id := l_managing_emp_id_out;
787
788 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
789 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
790 RAISE FND_API.G_EXC_ERROR;
791 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 END IF;
794
795 END IF;
796
797
798 /* Validate that the Start Date Active is specified */
799
800 IF l_start_date_active IS NULL THEN
801
802 -- dbms_output.put_line('Start Date Active cannot be null');
803
804 fnd_message.set_name('JTF', 'JTF_RS_START_DATE_NULL');
805 fnd_msg_pub.add;
806
807 RAISE fnd_api.g_exc_error;
808
809 END IF;
810
811
812
813 /* Validate the Time Zone */
814
815 IF l_time_zone IS NOT NULL THEN
816
817 jtf_resource_utl.validate_time_zone(
818 p_time_zone_id => l_time_zone,
819 x_return_status => x_return_status
820 );
821
822 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
823 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
824 RAISE FND_API.G_EXC_ERROR;
825 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828 END IF;
829
830 END IF;
831
832
833
834 /* Validate the Primary Language */
835
836 IF l_primary_language IS NOT NULL THEN
837
838 jtf_resource_utl.validate_nls_language(
839 p_nls_language => l_primary_language,
840 x_return_status => x_return_status
841 );
842
843 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
844 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
845 RAISE FND_API.G_EXC_ERROR;
846 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 END IF;
849 END IF;
850
851 END IF;
852
853
854
855 /* Validate the Secondary Language */
856
857 IF l_secondary_language IS NOT NULL THEN
858
859 jtf_resource_utl.validate_nls_language(
860 p_nls_language => l_secondary_language,
861 x_return_status => x_return_status
862 );
863
864 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
865 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
866 RAISE FND_API.G_EXC_ERROR;
867 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
869 END IF;
870 END IF;
871
872 END IF;
873
874
875
876 /* Validate the Support Site */
877
878 IF l_support_site_id IS NOT NULL THEN
879
880 jtf_resource_utl.validate_support_site_id(
881 p_support_site_id => l_support_site_id,
882 x_return_status => x_return_status
883 );
884
885 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
886 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
887 RAISE FND_API.G_EXC_ERROR;
888 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 END IF;
891 END IF;
892
893 END IF;
894
895
896
897 /* Validate the Server Group. */
898
899 jtf_resource_utl.validate_server_group(
900 p_server_group_id => l_server_group_id,
901 p_server_group_name => l_interaction_center_name,
902 x_return_status => x_return_status,
903 x_server_group_id => l_server_group_id_out
904 );
905
906 -- added for NOCOPY
907 l_server_group_id := l_server_group_id_out;
908
909 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
910 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
911 RAISE FND_API.G_EXC_ERROR;
912 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
914 END IF;
915 END IF;
916
917
918 /* Validate the assigned_to_group_id if specified */
919
920 IF l_assigned_to_group_id IS NOT NULL THEN
921
922 OPEN c_assigned_to_group_id(l_assigned_to_group_id);
923
924 FETCH c_assigned_to_group_id INTO l_assigned_to_group_id;
925
926
927 IF c_assigned_to_group_id%NOTFOUND THEN
928
929 -- dbms_output.put_line('Invalid Assigned To Group Id');
930
931 fnd_message.set_name('JTF', 'JTF_RS_ERR_ASSIGN_TO_GRP_ID');
932 fnd_message.set_token('P_ASSIGNED_TO_GROUP_ID', l_assigned_to_group_id);
933 fnd_msg_pub.add;
934 RAISE fnd_api.g_exc_error;
935
936 END IF;
937
938
939 /* Close the cursor */
940
941 CLOSE c_assigned_to_group_id;
942
943 END IF;
944
945
946
947 /* Validate the Comp Currency Code */
948
949 IF l_comp_currency_code IS NOT NULL THEN
950
951 jtf_resource_utl.validate_currency_code(
952 p_currency_code => l_comp_currency_code,
953 x_return_status => x_return_status
954 );
955
956 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
957 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
958 RAISE FND_API.G_EXC_ERROR;
959 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962 END IF;
963
964 END IF;
965
966
967 /* Validate the value of the commisionable flag */
968
969 IF l_commissionable_flag <> 'Y' AND l_commissionable_flag <> 'N' THEN
970
971 -- dbms_output.put_line('Commissionable Flag should either be ''Y'' or ''N'' ');
972
973 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
974 fnd_msg_pub.add;
975
976 RAISE fnd_api.g_exc_error;
977
978 END IF;
979
980
981 /* Validate the value of the Hold Payment flag */
982
983 IF l_hold_payment <> 'Y' AND l_hold_payment <> 'N' THEN
984
985 -- dbms_output.put_line('Hold Payment should either be ''Y'' or ''N'' ');
986
987 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
988 fnd_msg_pub.add;
989 RAISE fnd_api.g_exc_error;
990
991 END IF;
992
993
994
995 /* Validate the Hold Reason Code */
996
997 IF l_hold_reason_code IS NOT NULL THEN
998
999 jtf_resource_utl.validate_hold_reason_code(
1000 p_hold_reason_code => l_hold_reason_code,
1001 x_return_status => x_return_status
1002 );
1003
1004 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1005 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1006 RAISE FND_API.G_EXC_ERROR;
1007 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END IF;
1010 END IF;
1011
1012 END IF;
1013
1014
1015 /* Validate that the user_id should only be specified in case of
1016 'EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT' categories */
1017 /* Removed 'WORKER' from the below code to fix bug # 3455951 */
1018 IF l_category NOT IN ('EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT') THEN
1019
1020 IF l_user_id IS NOT NULL THEN
1021
1022 fnd_message.set_name('JTF', 'JTF_RS_USERID_ERROR');
1023 fnd_msg_pub.add;
1024
1025 RAISE fnd_api.g_exc_error;
1026
1027 END IF;
1028
1029 ELSE
1030
1031 /* Validate the User Id if specified */
1032
1033 IF l_user_id IS NOT NULL THEN
1034
1035 jtf_resource_utl.validate_user_id(
1036 p_user_id => l_user_id,
1037 p_category => l_category,
1038 p_source_id => l_source_id,
1039 x_return_status => x_return_status
1040 );
1041
1042 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1043
1044 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1045 RAISE FND_API.G_EXC_ERROR;
1046 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050 else
1051
1052 OPEN c_validate_user_id(l_user_id);
1053
1054 FETCH c_validate_user_id INTO l_check_flag;
1055
1056
1057 IF c_validate_user_id%FOUND THEN
1058
1059 -- dbms_output.put_line('duplicate user Id');
1060
1061 fnd_message.set_name('JTF', 'JTF_RS_ERR_DUPLICATE_USER_ID');
1062 fnd_message.set_token('P_USER_ID', l_user_id);
1063 fnd_msg_pub.add;
1064
1065 RAISE fnd_api.g_exc_error;
1066
1067 END IF;
1068
1069
1070 /* Close the cursor */
1071
1072 CLOSE c_validate_user_id;
1073
1074
1075
1076 END IF;
1077
1078 END IF;
1079
1080 END IF;
1081
1082
1083 /* Validate the Comp Service Team Id if specified */
1084
1085 IF l_comp_service_team_id IS NOT NULL THEN
1086
1087 jtf_resource_utl.validate_resource_team(
1088 p_team_id => l_comp_service_team_id,
1089 p_team_number => null,
1090 x_return_status => x_return_status,
1091 x_team_id => l_comp_service_team_id_out
1092 );
1093
1094 -- added for NOCOPY
1095 l_comp_service_team_id := l_comp_service_team_id_out;
1096
1097 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1098
1099 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1100 RAISE FND_API.G_EXC_ERROR;
1101 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 END IF;
1104
1105 END IF;
1106
1107 END IF;
1108
1109
1110 /* Check the Global Variable for Resource ID, and call the appropriate Private API */
1111
1112 -- dbms_output.put_line ('Before setting the global flag in create_resource');
1113
1114 IF G_RS_ID_PUB_FLAG = 'Y' THEN
1115
1116 /* Call the private procedure with the validated parameters. */
1117
1118 -- dbms_output.put_line ('Before call to the private procedure create_resource');
1119
1120 jtf_rs_resource_pvt.create_resource (
1121 P_API_VERSION => 1,
1122 P_INIT_MSG_LIST => fnd_api.g_false,
1123 P_COMMIT => fnd_api.g_false,
1124 P_CATEGORY => l_category,
1125 P_SOURCE_ID => l_source_id,
1126 P_ADDRESS_ID => l_address_id,
1127 P_CONTACT_ID => l_contact_id,
1128 P_MANAGING_EMP_ID => l_managing_emp_id,
1129 P_START_DATE_ACTIVE => l_start_date_active,
1130 P_END_DATE_ACTIVE => l_end_date_active,
1131 P_TIME_ZONE => l_time_zone,
1132 P_COST_PER_HR => l_cost_per_hr,
1133 P_PRIMARY_LANGUAGE => l_primary_language,
1134 P_SECONDARY_LANGUAGE => l_secondary_language,
1135 P_SUPPORT_SITE_ID => l_support_site_id,
1136 P_IES_AGENT_LOGIN => l_ies_agent_login,
1137 P_SERVER_GROUP_ID => l_server_group_id,
1138 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1139 P_COST_CENTER => l_cost_center,
1140 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1141 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1142 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1143 P_HOLD_REASON_CODE => l_hold_reason_code,
1144 P_HOLD_PAYMENT => l_hold_payment,
1145 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1146 P_USER_ID => l_user_id,
1147 P_TRANSACTION_NUMBER => l_transaction_number,
1148 --P_LOCATION => l_location,
1149 X_RETURN_STATUS => x_return_status,
1150 X_MSG_COUNT => x_msg_count,
1151 X_MSG_DATA => x_msg_data,
1152 X_RESOURCE_ID => x_resource_id,
1153 X_RESOURCE_NUMBER => x_resource_number
1154 );
1155
1156 /* jtf_rs_resource_pvt.create_resource (
1157 P_API_VERSION => 1,
1158 P_INIT_MSG_LIST => fnd_api.g_false,
1159 P_COMMIT => fnd_api.g_false,
1160 P_CATEGORY => l_category,
1161 P_SOURCE_ID => l_source_id,
1162 P_ADDRESS_ID => l_address_id,
1163 P_CONTACT_ID => l_contact_id,
1164 P_MANAGING_EMP_ID => l_managing_emp_id,
1165 P_START_DATE_ACTIVE => l_start_date_active,
1166 P_END_DATE_ACTIVE => l_end_date_active,
1167 P_TIME_ZONE => l_time_zone,
1168 P_COST_PER_HR => l_cost_per_hr,
1169 P_PRIMARY_LANGUAGE => l_primary_language,
1170 P_SECONDARY_LANGUAGE => l_secondary_language,
1171 P_SUPPORT_SITE_ID => l_support_site_id,
1172 P_IES_AGENT_LOGIN => l_ies_agent_login,
1173 P_SERVER_GROUP_ID => l_server_group_id,
1174 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1175 P_COST_CENTER => l_cost_center,
1176 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1177 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1178 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1179 P_HOLD_REASON_CODE => l_hold_reason_code,
1180 P_HOLD_PAYMENT => l_hold_payment,
1181 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1182 P_USER_ID => l_user_id,
1183 P_TRANSACTION_NUMBER => l_transaction_number,
1184 --P_LOCATION => l_location,
1185 X_RETURN_STATUS => x_return_status,
1186 X_MSG_COUNT => x_msg_count,
1187 X_MSG_DATA => x_msg_data,
1188 X_RESOURCE_ID => x_resource_id,
1189 X_RESOURCE_NUMBER => x_resource_number,
1190 P_RESOURCE_NAME => JTF_RS_RESOURCE_PUB.G_RESOURCE_NAME ,
1191 P_SOURCE_NAME => JTF_RS_RESOURCE_PUB.G_SOURCE_NAME,
1192 P_SOURCE_NUMBER => JTF_RS_RESOURCE_PUB.G_SOURCE_NUMBER,
1193 P_SOURCE_JOB_TITLE => JTF_RS_RESOURCE_PUB.G_SOURCE_JOB_TITLE,
1194 P_SOURCE_EMAIL => JTF_RS_RESOURCE_PUB.G_SOURCE_EMAIL,
1195 P_SOURCE_PHONE => JTF_RS_RESOURCE_PUB.G_SOURCE_PHONE,
1196 P_SOURCE_ORG_ID => JTF_RS_RESOURCE_PUB.G_SOURCE_ORG_ID,
1197 P_SOURCE_ORG_NAME => JTF_RS_RESOURCE_PUB.G_SOURCE_ORG_NAME,
1198 P_SOURCE_ADDRESS1 => JTF_RS_RESOURCE_PUB.G_SOURCE_ADDRESS1,
1199 P_SOURCE_ADDRESS2 => JTF_RS_RESOURCE_PUB.G_SOURCE_ADDRESS2,
1200 P_SOURCE_ADDRESS3 => JTF_RS_RESOURCE_PUB.G_SOURCE_ADDRESS3,
1201 P_SOURCE_ADDRESS4 => JTF_RS_RESOURCE_PUB.G_SOURCE_ADDRESS4,
1202 P_SOURCE_CITY => JTF_RS_RESOURCE_PUB.G_SOURCE_CITY,
1203 P_SOURCE_POSTAL_CODE => JTF_RS_RESOURCE_PUB.G_SOURCE_POSTAL_CODE,
1204 P_SOURCE_STATE => JTF_RS_RESOURCE_PUB.G_SOURCE_STATE,
1205 P_SOURCE_PROVINCE => JTF_RS_RESOURCE_PUB.G_SOURCE_PROVINCE,
1206 P_SOURCE_COUNTY => JTF_RS_RESOURCE_PUB.G_SOURCE_COUNTY,
1207 P_SOURCE_COUNTRY => JTF_RS_RESOURCE_PUB.G_SOURCE_COUNTRY
1208 );
1209
1210 */
1211
1212 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1213 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1214 RAISE FND_API.G_EXC_ERROR;
1215 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 END IF;
1218 END IF;
1219
1220 ELSE
1221
1222 -- dbms_output.put_line ('Before call to the private API create_resource_migrate' );
1223
1224 /* Call the private procedure for Migration. */
1225
1226 jtf_rs_resource_pvt.create_resource_migrate (
1227 P_API_VERSION => 1,
1228 P_INIT_MSG_LIST => fnd_api.g_false,
1229 P_COMMIT => fnd_api.g_false,
1230 P_CATEGORY => l_category,
1231 P_SOURCE_ID => l_source_id,
1232 P_ADDRESS_ID => l_address_id,
1233 P_CONTACT_ID => l_contact_id,
1234 P_MANAGING_EMP_ID => l_managing_emp_id,
1235 P_START_DATE_ACTIVE => l_start_date_active,
1236 P_END_DATE_ACTIVE => l_end_date_active,
1237 P_TIME_ZONE => l_time_zone,
1238 P_COST_PER_HR => l_cost_per_hr,
1239 P_PRIMARY_LANGUAGE => l_primary_language,
1240 P_SECONDARY_LANGUAGE => l_secondary_language,
1241 P_SUPPORT_SITE_ID => l_support_site_id,
1242 P_IES_AGENT_LOGIN => l_ies_agent_login,
1243 P_SERVER_GROUP_ID => l_server_group_id,
1244 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1245 P_COST_CENTER => l_cost_center,
1246 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1247 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1248 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1249 P_HOLD_REASON_CODE => l_hold_reason_code,
1250 P_HOLD_PAYMENT => l_hold_payment,
1251 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1252 P_USER_ID => l_user_id,
1253 P_TRANSACTION_NUMBER => l_transaction_number,
1254 --P_LOCATION => l_location,
1255 P_RESOURCE_ID => G_RESOURCE_ID,
1256 P_ATTRIBUTE1 => G_ATTRIBUTE1,
1257 P_ATTRIBUTE2 => G_ATTRIBUTE2,
1258 P_ATTRIBUTE3 => G_ATTRIBUTE3,
1259 P_ATTRIBUTE4 => G_ATTRIBUTE4,
1260 P_ATTRIBUTE5 => G_ATTRIBUTE5,
1261 P_ATTRIBUTE6 => G_ATTRIBUTE6,
1262 P_ATTRIBUTE7 => G_ATTRIBUTE7,
1263 P_ATTRIBUTE8 => G_ATTRIBUTE8,
1264 P_ATTRIBUTE9 => G_ATTRIBUTE9,
1265 P_ATTRIBUTE10 => G_ATTRIBUTE10,
1266 P_ATTRIBUTE11 => G_ATTRIBUTE11,
1267 P_ATTRIBUTE12 => G_ATTRIBUTE12,
1268 P_ATTRIBUTE13 => G_ATTRIBUTE13,
1269 P_ATTRIBUTE14 => G_ATTRIBUTE14,
1270 P_ATTRIBUTE15 => G_ATTRIBUTE15,
1271 P_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
1272 X_RETURN_STATUS => x_return_status,
1273 X_MSG_COUNT => x_msg_count,
1274 X_MSG_DATA => x_msg_data,
1275 X_RESOURCE_ID => x_resource_id,
1276 X_RESOURCE_NUMBER => x_resource_number
1277 );
1278 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1279 -- dbms_output.put_line('Failed status from call to private procedure');
1280 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1281 RAISE FND_API.G_EXC_ERROR;
1282 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1284 END IF;
1285 END IF;
1286
1287 END IF;
1288
1289 IF fnd_api.to_boolean(p_commit) THEN
1290
1291 COMMIT WORK;
1292
1293 END IF;
1294
1295 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1296
1297
1298 EXCEPTION
1299
1300
1301 WHEN fnd_api.g_exc_error THEN
1302 ROLLBACK TO create_resource_pub;
1303 x_return_status := fnd_api.g_ret_sts_error;
1304 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1305 p_data => x_msg_data);
1306 WHEN fnd_api.g_exc_unexpected_error THEN
1307 ROLLBACK TO create_resource_pub;
1308 x_return_status := fnd_api.g_ret_sts_unexp_error;
1309 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1310 p_data => x_msg_data);
1311 WHEN OTHERS THEN
1312 ROLLBACK TO create_resource_pub;
1313 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1314 fnd_message.set_token('P_SQLCODE',SQLCODE);
1315 fnd_message.set_token('P_SQLERRM',SQLERRM);
1316 fnd_message.set_token('P_API_NAME', l_api_name);
1317 FND_MSG_PUB.add;
1318 x_return_status := fnd_api.g_ret_sts_unexp_error;
1319 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1320 p_data => x_msg_data);
1321
1322 END create_resource;
1323
1324 PROCEDURE create_resource_migrate (
1325 P_API_VERSION IN NUMBER,
1326 P_INIT_MSG_LIST IN VARCHAR2,
1327 P_COMMIT IN VARCHAR2,
1328 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1329 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
1330 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
1331 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
1332 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
1333 P_MANAGING_EMP_NUM IN PER_EMPLOYEES_CURRENT_X.EMPLOYEE_NUM%TYPE,
1334 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
1335 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
1336 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
1337 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
1338 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
1339 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
1340 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
1341 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
1342 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
1343 P_INTERACTION_CENTER_NAME IN VARCHAR2,
1344 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
1345 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
1346 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
1347 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
1348 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
1349 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
1350 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
1351 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
1352 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
1353 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE,
1354 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
1355 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
1356 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
1357 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
1358 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
1359 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
1360 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
1361 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
1362 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
1363 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
1364 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
1365 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
1366 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
1367 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
1368 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
1369 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
1370 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
1371 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
1372 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1373 X_MSG_COUNT OUT NOCOPY NUMBER,
1374 X_MSG_DATA OUT NOCOPY VARCHAR2,
1375 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
1376 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE
1377 ) IS
1378
1379
1380 BEGIN
1381
1382 --dbms_output.put_line ('Inside the create_resource_migrate pub body');
1383
1384 JTF_RESOURCE_UTL.G_SOURCE_NAME := NULL;
1385
1386
1387 JTF_RS_RESOURCE_PUB.G_RS_ID_PUB_FLAG := 'N';
1388 JTF_RS_RESOURCE_PUB.G_RESOURCE_ID := P_RESOURCE_ID;
1389 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE1 := P_ATTRIBUTE1;
1390 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE2 := P_ATTRIBUTE2;
1391 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE3 := P_ATTRIBUTE3;
1392 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE4 := P_ATTRIBUTE4;
1393 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE5 := P_ATTRIBUTE5;
1394 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE6 := P_ATTRIBUTE6;
1395 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE7 := P_ATTRIBUTE7;
1396 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE8 := P_ATTRIBUTE8;
1397 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE9 := P_ATTRIBUTE9;
1398 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE10 := P_ATTRIBUTE10;
1399 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE11 := P_ATTRIBUTE11;
1400 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE12 := P_ATTRIBUTE12;
1401 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE13 := P_ATTRIBUTE13;
1402 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE14 := P_ATTRIBUTE14;
1403 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE15 := P_ATTRIBUTE15;
1404 JTF_RS_RESOURCE_PUB.G_ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY;
1405
1406 --dbms_output.put_line ('After assigning values to the Global variables');
1407
1408 jtf_rs_resource_pub.create_resource (
1409 P_API_VERSION => P_API_VERSION,
1410 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
1411 P_COMMIT => P_COMMIT,
1412 P_CATEGORY => P_CATEGORY,
1413 P_SOURCE_ID => P_SOURCE_ID,
1414 P_ADDRESS_ID => P_ADDRESS_ID,
1415 P_CONTACT_ID => P_CONTACT_ID,
1416 P_MANAGING_EMP_ID => P_MANAGING_EMP_ID,
1417 P_MANAGING_EMP_NUM => P_MANAGING_EMP_NUM,
1418 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
1419 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
1420 P_TIME_ZONE => P_TIME_ZONE,
1421 P_COST_PER_HR => P_COST_PER_HR,
1422 P_SECONDARY_LANGUAGE => P_SECONDARY_LANGUAGE,
1423 P_SUPPORT_SITE_ID => P_SUPPORT_SITE_ID,
1424 P_IES_AGENT_LOGIN => P_IES_AGENT_LOGIN,
1425 P_SERVER_GROUP_ID => P_SERVER_GROUP_ID,
1426 P_INTERACTION_CENTER_NAME => P_INTERACTION_CENTER_NAME,
1427 P_ASSIGNED_TO_GROUP_ID => P_ASSIGNED_TO_GROUP_ID,
1428 P_COST_CENTER => P_COST_CENTER,
1429 P_CHARGE_TO_COST_CENTER => P_CHARGE_TO_COST_CENTER,
1430 P_COMP_CURRENCY_CODE => P_COMP_CURRENCY_CODE,
1431 P_COMMISSIONABLE_FLAG => P_COMMISSIONABLE_FLAG,
1432 P_HOLD_REASON_CODE => P_HOLD_REASON_CODE,
1433 P_HOLD_PAYMENT => P_HOLD_PAYMENT,
1434 P_COMP_SERVICE_TEAM_ID => P_COMP_SERVICE_TEAM_ID,
1435 P_USER_ID => P_USER_ID,
1436 P_TRANSACTION_NUMBER => P_TRANSACTION_NUMBER,
1437 --P_LOCATION => P_LOCATION,
1438 X_RETURN_STATUS => X_RETURN_STATUS,
1439 X_MSG_COUNT => X_MSG_COUNT,
1440 X_MSG_DATA => X_MSG_DATA,
1441 X_RESOURCE_ID => X_RESOURCE_ID,
1442 X_RESOURCE_NUMBER => X_RESOURCE_NUMBER);
1443
1444 --dbms_output.put_line ('After Call to create_resource in the Migr API');
1445
1446 END create_resource_migrate;
1447
1448 /* Procedure to update the resource based on input values
1449 passed by calling routines . */
1450
1451 PROCEDURE update_resource
1452 (P_API_VERSION IN NUMBER,
1453 P_INIT_MSG_LIST IN VARCHAR2,
1454 P_COMMIT IN VARCHAR2,
1455 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
1456 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
1457 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
1458 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
1459 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
1460 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
1461 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
1462 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
1463 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
1464 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
1465 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
1466 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
1467 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
1468 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
1469 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
1470 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
1471 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
1472 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
1473 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
1474 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
1475 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
1476 -- P_LOCATION IN MDSYS.SDO_GEOMETRY,
1477 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
1478 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1479 X_MSG_COUNT OUT NOCOPY NUMBER,
1480 X_MSG_DATA OUT NOCOPY VARCHAR2
1481 ) IS
1482
1483 l_api_version CONSTANT NUMBER := 1.0;
1484 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
1485 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1486 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1487 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
1488 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
1489 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
1490 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
1491 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
1492 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
1493 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
1494 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
1495 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
1496 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
1497 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
1498 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
1499 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
1500 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
1501 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
1502 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
1503 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
1504 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
1505 l_user_id jtf_rs_resource_extns.user_id%TYPE;
1506 -- l_location mdsys.sdo_geometry;
1507 l_object_version_num jtf_rs_resource_extns.object_version_number%TYPE;
1508
1509 l_check_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1510 l_check_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1511 l_category jtf_rs_resource_extns.category%TYPE;
1512
1513
1514 CURSOR c_resource_id(
1515 l_resource_id IN NUMBER)
1516 IS
1517 SELECT resource_id,category
1518 FROM jtf_rs_resource_extns
1519 WHERE resource_id = l_resource_id;
1520
1521
1522 CURSOR c_resource_number(
1523 l_resource_number IN VARCHAR2)
1524 IS
1525 SELECT resource_id,category
1526 FROM jtf_rs_resource_extns
1527 WHERE resource_number = l_resource_number;
1528
1529
1530 BEGIN
1531
1532 l_resource_id := p_resource_id;
1533 l_resource_number := p_resource_number;
1534 l_managing_emp_id := p_managing_emp_id;
1535 l_start_date_active := p_start_date_active;
1536 l_end_date_active := p_end_date_active;
1537 l_time_zone := p_time_zone;
1538 l_cost_per_hr := p_cost_per_hr;
1539 l_primary_language := p_primary_language;
1540 l_secondary_language := p_secondary_language;
1541 l_support_site_id := p_support_site_id;
1542 l_ies_agent_login := p_ies_agent_login;
1543 l_server_group_id := p_server_group_id;
1544 l_assigned_to_group_id := p_assigned_to_group_id;
1545 l_cost_center := p_cost_center;
1546 l_charge_to_cost_center := p_charge_to_cost_center;
1547 l_comp_currency_code := p_comp_currency_code;
1548 l_commissionable_flag := p_commissionable_flag;
1549 l_hold_reason_code := p_hold_reason_code;
1550 l_hold_payment := p_hold_payment;
1551 l_comp_service_team_id := p_comp_service_team_id;
1552 l_user_id := p_user_id;
1553 l_object_version_num := p_object_version_num;
1554
1555 SAVEPOINT update_resource_pub;
1556
1557 x_return_status := fnd_api.g_ret_sts_success;
1558
1559 -- dbms_output.put_line(' Started Update Resource Pub ');
1560
1561
1562 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1563
1564 RAISE fnd_api.g_exc_unexpected_error;
1565
1566 END IF;
1567
1568
1569 IF fnd_api.to_boolean(p_init_msg_list) THEN
1570
1571 fnd_msg_pub.initialize;
1572
1573 END IF;
1574
1575
1576 /* Validate the Resource. */
1577
1578 IF l_resource_id IS NULL AND l_resource_number is NULL THEN
1579
1580 -- dbms_output.put_line('Resource Id and Resource Number are null');
1581
1582 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
1583 fnd_msg_pub.add;
1584
1585 RAISE fnd_api.g_exc_error;
1586
1587 END IF;
1588
1589
1590 IF l_resource_id IS NOT NULL THEN
1591
1592 OPEN c_resource_id(l_resource_id);
1593
1594 FETCH c_resource_id INTO l_check_resource_id,l_category;
1595
1596 IF c_resource_id%NOTFOUND THEN
1597
1598 -- dbms_output.put_line('Invalid or Inactive Resource');
1599
1600 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
1601 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
1602 fnd_msg_pub.add;
1603
1604 RAISE fnd_api.g_exc_error;
1605
1606 END IF;
1607
1608 CLOSE c_resource_id;
1609
1610 ELSIF l_resource_number IS NOT NULL THEN
1611
1612 OPEN c_resource_number(l_resource_number);
1613
1614 FETCH c_resource_number INTO l_check_resource_number,l_category;
1615
1616 IF c_resource_number%NOTFOUND THEN
1617
1618 -- dbms_output.put_line('Invalid or Inactive Resource');
1619
1620 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_NUMBER');
1621 fnd_message.set_token('P_RESOURCE_NUMBER', l_resource_number);
1622 fnd_msg_pub.add;
1623
1624 RAISE fnd_api.g_exc_error;
1625
1626 END IF;
1627
1628 CLOSE c_resource_number;
1629
1630 END IF;
1631
1632 IF l_category IN ('OTHER', 'TBH') THEN
1633
1634 /* Validate that the managing_employee_id is NULL */
1635
1636 IF ( l_managing_emp_id IS NOT NULL AND l_managing_emp_id <> fnd_api.g_miss_num) THEN
1637
1638 -- dbms_output.put_line('For OTHER category, source_id, address_id, contact_id and managing_emp_id should be all null');
1639
1640 fnd_message.set_name('JTF', 'JTF_RS_OTHER_IDS_NOT_NULL');
1641 fnd_msg_pub.add;
1642 RAISE fnd_api.g_exc_error;
1643
1644 END IF;
1645
1646 END IF;
1647
1648
1649 /* Call the private procedure with the validated parameters. */
1650
1651 jtf_rs_resource_pvt.update_resource
1652 (P_API_VERSION => 1,
1653 P_INIT_MSG_LIST => fnd_api.g_false,
1654 P_COMMIT => fnd_api.g_false,
1655 P_RESOURCE_ID => l_resource_id,
1656 P_MANAGING_EMP_ID => l_managing_emp_id,
1657 P_START_DATE_ACTIVE => l_start_date_active,
1658 P_END_DATE_ACTIVE => l_end_date_active,
1659 P_TIME_ZONE => l_time_zone,
1660 P_COST_PER_HR => l_cost_per_hr,
1661 P_PRIMARY_LANGUAGE => l_primary_language,
1662 P_SECONDARY_LANGUAGE => l_secondary_language,
1663 P_SUPPORT_SITE_ID => l_support_site_id,
1664 P_IES_AGENT_LOGIN => l_ies_agent_login,
1665 P_SERVER_GROUP_ID => l_server_group_id,
1666 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1667 P_COST_CENTER => l_cost_center,
1668 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1669 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1670 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1671 P_HOLD_REASON_CODE => l_hold_reason_code,
1672 P_HOLD_PAYMENT => l_hold_payment,
1673 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1674 P_USER_ID => l_user_id,
1675 --P_LOCATION => l_location,
1676 P_OBJECT_VERSION_NUM => l_object_version_num,
1677 X_RETURN_STATUS => x_return_status,
1678 X_MSG_COUNT => x_msg_count,
1679 X_MSG_DATA => x_msg_data,
1680 --added this so that the overloaded procedure for update resource is called
1681 -- otherwise all source coulmns were being set to null
1682 P_SOURCE_NAME => fnd_api.g_miss_char
1683 );
1684
1685
1686 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1687
1688 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1692 END IF;
1693
1694 END IF;
1695
1696 /* Return the new value of the object version number */
1697
1698 p_object_version_num := l_object_version_num;
1699
1700 IF fnd_api.to_boolean(p_commit) THEN
1701
1702 COMMIT WORK;
1703
1704 END IF;
1705
1706 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1707
1708
1709
1710 EXCEPTION
1711 WHEN fnd_api.g_exc_error THEN
1712 ROLLBACK TO update_resource_pub;
1713 x_return_status := fnd_api.g_ret_sts_error;
1714 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1715 p_data => x_msg_data);
1716 WHEN fnd_api.g_exc_unexpected_error THEN
1717 ROLLBACK TO update_resource_pub;
1718 x_return_status := fnd_api.g_ret_sts_unexp_error;
1719 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1720 p_data => x_msg_data);
1721 WHEN OTHERS THEN
1722 ROLLBACK TO update_resource_pub;
1723 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1724 fnd_message.set_token('P_SQLCODE',SQLCODE);
1725 fnd_message.set_token('P_SQLERRM',SQLERRM);
1726 fnd_message.set_token('P_API_NAME', l_api_name);
1727 FND_MSG_PUB.add;
1728 x_return_status := fnd_api.g_ret_sts_unexp_error;
1729 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1730 p_data => x_msg_data);
1731
1732 END update_resource;
1733
1734 /*
1735 | Modification History :
1736 |
1737 | Sudhir Gokavarapu ER9864816 - New procedure Created, FS_SETUP_COMPLETE parameter is added to
1738 | existing UPDATE_RESOURSE parameter list.
1739 | Complete business logic moved from old procedure to this new procedure.
1740 */
1741
1742
1743 /* Procedure to update the resource with new columns based on input values
1744 passed by calling routines along with FS Setup Complete Flag. */
1745
1746 PROCEDURE update_resource
1747 (P_API_VERSION IN NUMBER,
1748 P_INIT_MSG_LIST IN VARCHAR2,
1749 P_COMMIT IN VARCHAR2,
1750 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
1751 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
1752 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
1753 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
1754 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
1755 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
1756 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
1757 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
1758 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
1759 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
1760 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
1761 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
1762 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
1763 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
1764 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
1765 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
1766 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
1767 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
1768 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
1769 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
1770 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
1771 -- P_LOCATION IN MDSYS.SDO_GEOMETRY,
1772 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
1773 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
1774 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
1775 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
1776 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
1777 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
1778 P_SOURCE_ORG_ID IN NUMBER,
1779 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
1780 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
1781 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
1782 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
1783 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
1784 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
1785 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
1786 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
1787 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
1788 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
1789 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
1790 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
1791 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
1792 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
1793 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
1794 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
1795 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
1796 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
1797 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
1798 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
1799 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
1800 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
1801 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
1802 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
1803 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
1804 P_USER_NAME IN VARCHAR2,
1805 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1806 X_MSG_COUNT OUT NOCOPY NUMBER,
1807 X_MSG_DATA OUT NOCOPY VARCHAR2,
1808 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
1809 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
1810 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
1811 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
1812 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
1813 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
1814 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
1815 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
1816 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
1817 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
1818 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
1819 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
1820 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
1821 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
1822 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
1823 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
1824 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
1825 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
1826 P_FS_SETUP_COMPLETE IN JTF_RS_RESOURCE_EXTNS.FS_SETUP_COMPLETE%TYPE
1827 ) IS
1828
1829 l_api_version CONSTANT NUMBER := 1.0;
1830 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
1831 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1832 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1833 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
1834 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
1835 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
1836 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
1837 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
1838 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
1839 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
1840 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
1841 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
1842 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
1843 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
1844 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
1845 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
1846 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
1847 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
1848 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
1849 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
1850 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
1851 l_user_id jtf_rs_resource_extns.user_id%TYPE;
1852 -- l_location mdsys.sdo_geometry;
1853 l_object_version_num jtf_rs_resource_extns.object_version_number%TYPE;
1854 l_user_name jtf_rs_resource_extns.user_name%type;
1855 l_check_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1856 l_check_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1857 l_fs_setup_complete jtf_rs_resource_extns.fs_setup_complete%type;
1858 l_category jtf_rs_resource_extns.category%type;
1859 l_address_id jtf_rs_resource_extns.address_id%type;
1860
1861 CURSOR c_resource_id(
1862 l_resource_id IN NUMBER)
1863 IS
1864 SELECT resource_id,category
1865 FROM jtf_rs_resource_extns
1866 WHERE resource_id = l_resource_id;
1867
1868
1869 CURSOR c_resource_number(
1870 l_resource_number IN VARCHAR2)
1871 IS
1872 SELECT resource_id,category
1873 FROM jtf_rs_resource_extns
1874 WHERE resource_number = l_resource_number;
1875
1876
1877 BEGIN
1878
1879 l_resource_id := p_resource_id;
1880 l_resource_number := p_resource_number;
1881 l_managing_emp_id := p_managing_emp_id;
1882 l_start_date_active := p_start_date_active;
1883 l_end_date_active := p_end_date_active;
1884 l_time_zone := p_time_zone;
1885 l_cost_per_hr := p_cost_per_hr;
1886 l_primary_language := p_primary_language;
1887 l_secondary_language := p_secondary_language;
1888 l_support_site_id := p_support_site_id;
1889 l_ies_agent_login := p_ies_agent_login;
1890 l_server_group_id := p_server_group_id;
1891 l_assigned_to_group_id := p_assigned_to_group_id;
1892 l_cost_center := p_cost_center;
1893 l_charge_to_cost_center := p_charge_to_cost_center;
1894 l_comp_currency_code := p_comp_currency_code;
1895 l_commissionable_flag := p_commissionable_flag;
1896 l_hold_reason_code := p_hold_reason_code;
1897 l_hold_payment := p_hold_payment;
1898 l_comp_service_team_id := p_comp_service_team_id;
1899 l_user_id := p_user_id;
1900 -- l_location := p_location;
1901 l_object_version_num := p_object_version_num;
1902 l_user_name := p_user_name;
1903 l_fs_setup_complete := p_fs_setup_complete;
1904 l_address_id := p_address_id;
1905
1906 SAVEPOINT update_resource_pub;
1907
1908 x_return_status := fnd_api.g_ret_sts_success;
1909
1910 -- dbms_output.put_line(' Started Update Resource Pub ');
1911
1912
1913 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1914
1915 RAISE fnd_api.g_exc_unexpected_error;
1916
1917 END IF;
1918
1919
1920 IF fnd_api.to_boolean(p_init_msg_list) THEN
1921
1922 fnd_msg_pub.initialize;
1923
1924 END IF;
1925
1926
1927 /* Validate the Resource. */
1928
1929 IF l_resource_id IS NULL AND l_resource_number is NULL THEN
1930
1931 -- dbms_output.put_line('Resource Id and Resource Number are null');
1932
1933 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
1934 fnd_msg_pub.add;
1935
1936 RAISE fnd_api.g_exc_error;
1937
1938 END IF;
1939
1940
1941 IF l_resource_id IS NOT NULL THEN
1942
1943 OPEN c_resource_id(l_resource_id);
1944
1945 FETCH c_resource_id INTO l_check_resource_id,l_category;
1946
1947 IF c_resource_id%NOTFOUND THEN
1948
1949 -- dbms_output.put_line('Invalid or Inactive Resource');
1950
1951 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
1952 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
1953 fnd_msg_pub.add;
1954
1955 RAISE fnd_api.g_exc_error;
1956
1957 END IF;
1958
1959 CLOSE c_resource_id;
1960
1961 ELSIF l_resource_number IS NOT NULL THEN
1962
1963 OPEN c_resource_number(l_resource_number);
1964
1965 FETCH c_resource_number INTO l_check_resource_number,l_category;
1966
1967 IF c_resource_number%NOTFOUND THEN
1968
1969 -- dbms_output.put_line('Invalid or Inactive Resource');
1970
1971 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_NUMBER');
1972 fnd_message.set_token('P_RESOURCE_NUMBER', l_resource_number);
1973 fnd_msg_pub.add;
1974
1975 RAISE fnd_api.g_exc_error;
1976
1977
1978 END IF;
1979
1980 CLOSE c_resource_number;
1981
1982 END IF;
1983
1984 IF l_category IN ('OTHER', 'TBH') THEN
1985
1986 /* Validate that the source_id, address_id, contact_id and managing_employee_id
1987 are all NULL */
1988
1989 IF ((l_address_id IS NOT NULL AND l_address_id <> fnd_api.g_miss_num)
1990 OR (l_managing_emp_id IS NOT NULL AND l_managing_emp_id <> fnd_api.g_miss_num )) THEN
1991
1992 -- dbms_output.put_line('For OTHER category, source_id, address_id, contact_id and managing_emp_id should be all null');
1993
1994 fnd_message.set_name('JTF', 'JTF_RS_OTHER_IDS_NOT_NULL');
1995 fnd_msg_pub.add;
1996 RAISE fnd_api.g_exc_error;
1997
1998 END IF;
1999
2000 END IF;
2001
2002 /* Call the private procedure with the validated parameters. */
2003
2004 jtf_rs_resource_pvt.update_resource
2005 (P_API_VERSION => 1,
2006 P_INIT_MSG_LIST => fnd_api.g_false,
2007 P_COMMIT => fnd_api.g_false,
2008 P_RESOURCE_ID => l_resource_id,
2009 P_MANAGING_EMP_ID => l_managing_emp_id,
2010 P_START_DATE_ACTIVE => l_start_date_active,
2011 P_END_DATE_ACTIVE => l_end_date_active,
2012 P_TIME_ZONE => l_time_zone,
2013 P_COST_PER_HR => l_cost_per_hr,
2014 P_PRIMARY_LANGUAGE => l_primary_language,
2015 P_SECONDARY_LANGUAGE => l_secondary_language,
2016 P_SUPPORT_SITE_ID => l_support_site_id,
2017 P_IES_AGENT_LOGIN => l_ies_agent_login,
2018 P_SERVER_GROUP_ID => l_server_group_id,
2019 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
2020 P_COST_CENTER => l_cost_center,
2021 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
2022 P_COMP_CURRENCY_CODE => l_comp_currency_code,
2023 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
2024 P_HOLD_REASON_CODE => l_hold_reason_code,
2025 P_HOLD_PAYMENT => l_hold_payment,
2026 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
2027 P_USER_ID => l_user_id,
2028 --P_LOCATION => l_location,
2029 P_ATTRIBUTE1 => p_attribute1,
2030 P_ATTRIBUTE2 => p_attribute2,
2031 P_ATTRIBUTE3 => p_attribute3,
2032 P_ATTRIBUTE4 => p_attribute4,
2033 P_ATTRIBUTE5 => p_attribute5,
2034 P_ATTRIBUTE6 => p_attribute6,
2035 P_ATTRIBUTE7 => p_attribute7,
2036 P_ATTRIBUTE8 => p_attribute8,
2037 P_ATTRIBUTE9 => p_attribute9,
2038 P_ATTRIBUTE10 => p_attribute10,
2039 P_ATTRIBUTE11 => p_attribute11,
2040 P_ATTRIBUTE12 => p_attribute12,
2041 P_ATTRIBUTE13 => p_attribute13,
2042 P_ATTRIBUTE14 => p_attribute14,
2043 P_ATTRIBUTE15 => p_attribute15,
2044 P_ATTRIBUTE_CATEGORY => p_attribute_category,
2045 P_OBJECT_VERSION_NUM => l_object_version_num,
2046 P_RESOURCE_NAME => P_RESOURCE_NAME,
2047 P_SOURCE_NAME => P_SOURCE_NAME,
2048 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
2049 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
2050 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
2051 P_SOURCE_PHONE => P_SOURCE_PHONE,
2052 P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
2053 P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
2054 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
2055 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
2056 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
2057 P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
2058 P_SOURCE_CITY => P_SOURCE_CITY,
2059 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
2060 P_SOURCE_STATE => P_SOURCE_STATE,
2061 P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
2062 P_SOURCE_COUNTY => P_SOURCE_COUNTY,
2063 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
2064 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
2065 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
2066 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
2067 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
2068 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
2069 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
2070 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
2071 P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
2072 P_SOURCE_STATUS => P_SOURCE_STATUS,
2073 P_SOURCE_OFFICE => P_SOURCE_OFFICE,
2074 P_SOURCE_LOCATION => P_SOURCE_LOCATION,
2075 P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
2076 P_ADDRESS_ID => P_ADDRESS_ID,
2077 P_USER_NAME => P_USER_NAME,
2078 X_RETURN_STATUS => x_return_status,
2079 X_MSG_COUNT => x_msg_count,
2080 X_MSG_DATA => x_msg_data,
2081 P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
2082 P_SOURCE_PAGER => P_SOURCE_PAGER,
2083 P_FS_SETUP_COMPLETE => l_fs_setup_complete
2084 );
2085
2086
2087 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2088
2089 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2090 RAISE FND_API.G_EXC_ERROR;
2091 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2093 END IF;
2094
2095 END IF;
2096
2097
2098
2099 IF fnd_api.to_boolean(p_commit) THEN
2100
2101 COMMIT WORK;
2102
2103 END IF;
2104
2105 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2106
2107
2108
2109 EXCEPTION
2110
2111 WHEN fnd_api.g_exc_error THEN
2112 ROLLBACK TO update_resource_pub;
2113 x_return_status := fnd_api.g_ret_sts_error;
2114 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2115 p_data => x_msg_data);
2116 WHEN fnd_api.g_exc_unexpected_error THEN
2117 ROLLBACK TO update_resource_pub;
2118 x_return_status := fnd_api.g_ret_sts_unexp_error;
2119 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2120 p_data => x_msg_data);
2121 WHEN OTHERS THEN
2122 ROLLBACK TO update_resource_pub;
2123 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2124 fnd_message.set_token('P_SQLCODE',SQLCODE);
2125 fnd_message.set_token('P_SQLERRM',SQLERRM);
2126 fnd_message.set_token('P_API_NAME', l_api_name);
2127 FND_MSG_PUB.add;
2128 x_return_status := fnd_api.g_ret_sts_unexp_error;
2129 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2130 p_data => x_msg_data);
2131
2132
2133 END update_resource;
2134
2135 /*
2136 | Modification History :
2137 |
2138 | Sudhir Gokavarapu ER9864816 - Complete business logic moved from this procedure to new procedure
2139 | and calling new update_resource procedure with extra parameter.
2140 */
2141 /* Procedure to update the resource with new columns based on input values
2142 passed by calling routines. */
2143
2144 PROCEDURE update_resource
2145 (P_API_VERSION IN NUMBER,
2146 P_INIT_MSG_LIST IN VARCHAR2,
2147 P_COMMIT IN VARCHAR2,
2148 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2149 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
2150 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
2151 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
2152 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
2153 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
2154 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
2155 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
2156 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
2157 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
2158 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
2159 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
2160 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
2161 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
2162 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
2163 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
2164 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
2165 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
2166 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
2167 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
2168 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
2169 -- P_LOCATION IN MDSYS.SDO_GEOMETRY,
2170 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
2171 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
2172 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
2173 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
2174 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
2175 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
2176 P_SOURCE_ORG_ID IN NUMBER,
2177 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
2178 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
2179 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
2180 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
2181 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
2182 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
2183 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
2184 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
2185 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
2186 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
2187 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
2188 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
2189 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
2190 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
2191 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
2192 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
2193 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
2194 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
2195 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
2196 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
2197 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
2198 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
2199 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
2200 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
2201 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
2202 P_USER_NAME IN VARCHAR2,
2203 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2204 X_MSG_COUNT OUT NOCOPY NUMBER,
2205 X_MSG_DATA OUT NOCOPY VARCHAR2,
2206 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
2207 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
2208 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
2209 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
2210 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
2211 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
2212 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
2213 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
2214 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
2215 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
2216 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
2217 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
2218 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
2219 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
2220 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
2221 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
2222 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
2223 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE
2224 ) IS
2225 BEGIN
2226
2227 update_resource
2228 (P_API_VERSION => P_API_VERSION,
2229 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
2230 P_COMMIT => P_COMMIT,
2231 P_RESOURCE_ID => P_RESOURCE_ID,
2232 P_RESOURCE_NUMBER => P_RESOURCE_NUMBER,
2233 P_MANAGING_EMP_ID => P_MANAGING_EMP_ID,
2234 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
2235 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
2236 P_TIME_ZONE => P_TIME_ZONE,
2237 P_COST_PER_HR => P_COST_PER_HR,
2238 P_PRIMARY_LANGUAGE => P_PRIMARY_LANGUAGE,
2239 P_SECONDARY_LANGUAGE => P_SECONDARY_LANGUAGE,
2240 P_SUPPORT_SITE_ID => P_SUPPORT_SITE_ID,
2241 P_IES_AGENT_LOGIN => P_IES_AGENT_LOGIN,
2242 P_SERVER_GROUP_ID => P_SERVER_GROUP_ID,
2243 P_ASSIGNED_TO_GROUP_ID => P_ASSIGNED_TO_GROUP_ID,
2244 P_COST_CENTER => P_COST_CENTER,
2245 P_CHARGE_TO_COST_CENTER => P_CHARGE_TO_COST_CENTER,
2246 P_COMP_CURRENCY_CODE => P_COMP_CURRENCY_CODE,
2247 P_COMMISSIONABLE_FLAG => P_COMMISSIONABLE_FLAG,
2248 P_HOLD_REASON_CODE => P_HOLD_REASON_CODE,
2249 P_HOLD_PAYMENT => P_HOLD_PAYMENT,
2250 P_COMP_SERVICE_TEAM_ID => P_COMP_SERVICE_TEAM_ID,
2251 P_USER_ID => P_USER_ID,
2252 P_RESOURCE_NAME => P_RESOURCE_NAME,
2253 P_SOURCE_NAME => P_SOURCE_NAME,
2254 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
2255 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
2256 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
2257 P_SOURCE_PHONE => P_SOURCE_PHONE,
2258 P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
2259 P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
2260 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
2261 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
2262 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
2263 P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
2264 P_SOURCE_CITY => P_SOURCE_CITY,
2265 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
2266 P_SOURCE_STATE => P_SOURCE_STATE,
2267 P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
2268 P_SOURCE_COUNTY => P_SOURCE_COUNTY,
2269 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
2270 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
2271 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
2272 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
2273 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
2274 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
2275 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
2276 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
2277 P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
2278 P_SOURCE_STATUS => P_SOURCE_STATUS,
2279 P_SOURCE_OFFICE => P_SOURCE_OFFICE,
2280 P_SOURCE_LOCATION => P_SOURCE_LOCATION,
2281 P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
2282 P_ADDRESS_ID => P_ADDRESS_ID,
2283 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
2284 P_USER_NAME => P_USER_NAME,
2285 X_RETURN_STATUS => X_RETURN_STATUS,
2286 X_MSG_COUNT => X_MSG_COUNT,
2287 X_MSG_DATA => X_MSG_DATA,
2288 P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
2289 P_SOURCE_PAGER => P_SOURCE_PAGER,
2290 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2291 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2292 P_ATTRIBUTE3 => P_ATTRIBUTE3,
2293 P_ATTRIBUTE4 => P_ATTRIBUTE4,
2294 P_ATTRIBUTE5 => P_ATTRIBUTE5,
2295 P_ATTRIBUTE6 => P_ATTRIBUTE6,
2296 P_ATTRIBUTE7 => P_ATTRIBUTE7,
2297 P_ATTRIBUTE8 => P_ATTRIBUTE8,
2298 P_ATTRIBUTE9 => P_ATTRIBUTE9,
2299 P_ATTRIBUTE10 => P_ATTRIBUTE10,
2300 P_ATTRIBUTE11 => P_ATTRIBUTE11,
2301 P_ATTRIBUTE12 => P_ATTRIBUTE12,
2302 P_ATTRIBUTE13 => P_ATTRIBUTE13,
2303 P_ATTRIBUTE14 => P_ATTRIBUTE14,
2304 P_ATTRIBUTE15 => P_ATTRIBUTE15,
2305 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2306 P_FS_SETUP_COMPLETE => FND_API.G_MISS_CHAR
2307 ) ;
2308 END update_resource;
2309
2310 /* Procedure to delete the resource of type TBH */
2311
2312 PROCEDURE delete_resource
2313 (P_API_VERSION IN NUMBER,
2314 P_INIT_MSG_LIST IN VARCHAR2,
2315 P_COMMIT IN VARCHAR2,
2316 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2317 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2318 X_MSG_COUNT OUT NOCOPY NUMBER,
2319 X_MSG_DATA OUT NOCOPY VARCHAR2
2320 ) IS
2321
2322 l_api_version CONSTANT NUMBER := 1.0;
2323 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
2324 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
2325
2326 CURSOR res_cur(
2327 l_resource_id IN NUMBER)
2328 IS
2329 SELECT resource_id,
2330 category
2331 FROM jtf_rs_resource_extns
2332 WHERE resource_id = l_resource_id;
2333
2334 res_rec res_cur%rowtype;
2335
2336 l_bind_id number;
2337
2338
2339 BEGIN
2340
2341 l_resource_id := p_resource_id;
2342
2343 SAVEPOINT delete_resource_pub;
2344 x_return_status := fnd_api.g_ret_sts_success;
2345 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2346 RAISE fnd_api.g_exc_unexpected_error;
2347 END IF;
2348
2349 IF fnd_api.to_boolean(p_init_msg_list) THEN
2350 fnd_msg_pub.initialize;
2351 END IF;
2352
2353
2354 /* Validate the Resource. */
2355 IF l_resource_id IS NULL THEN
2356 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
2357 fnd_msg_pub.add;
2358 RAISE fnd_api.g_exc_error;
2359
2360 END IF;
2361
2362
2363 IF l_resource_id IS NOT NULL THEN
2364 OPEN res_cur(l_resource_id);
2365 FETCH res_cur INTO res_rec;
2366 IF res_cur%NOTFOUND THEN
2367 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
2368 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
2369 fnd_msg_pub.add;
2370 RAISE fnd_api.g_exc_error;
2371
2372 END IF;
2373 IF res_rec.category <> 'TBH'
2374 THEN
2375 fnd_message.set_name('JTF', 'JTF_RS_NOT_TBH');
2376 fnd_msg_pub.add;
2377 RAISE fnd_api.g_exc_error;
2378 END IF;
2379 CLOSE res_cur;
2380
2381 END IF;
2382
2383
2384
2385 /* Call the private procedure with the validated parameters. */
2386 --call private api for delete
2387 JTF_RS_RESOURCE_PVT.DELETE_RESOURCE(
2388 P_API_VERSION => 1.0,
2389 P_INIT_MSG_LIST => fnd_api.g_false,
2390 P_COMMIT => fnd_api.g_false,
2391 P_RESOURCE_ID => l_resource_id,
2392 X_RETURN_STATUS => x_return_status,
2393 X_MSG_COUNT => x_msg_count,
2394 X_MSG_DATA => x_msg_data );
2395
2396
2397 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2398 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2399 RAISE FND_API.G_EXC_ERROR;
2400 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2402 END IF;
2403 END IF;
2404
2405 IF fnd_api.to_boolean(p_commit) THEN
2406 COMMIT WORK;
2407 END IF;
2408 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2409
2410 EXCEPTION
2411 WHEN fnd_api.g_exc_error THEN
2412 ROLLBACK TO delete_resource_pub;
2413 x_return_status := fnd_api.g_ret_sts_error;
2414 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2415 p_data => x_msg_data);
2416 WHEN fnd_api.g_exc_unexpected_error THEN
2417 ROLLBACK TO delete_resource_pub;
2418 x_return_status := fnd_api.g_ret_sts_unexp_error;
2419 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2420 p_data => x_msg_data);
2421 WHEN OTHERS THEN
2422 ROLLBACK TO delete_resource_pub;
2423 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2424 fnd_message.set_token('P_SQLCODE',SQLCODE);
2425 fnd_message.set_token('P_SQLERRM',SQLERRM);
2426 fnd_message.set_token('P_API_NAME', l_api_name);
2427 FND_MSG_PUB.add;
2428 x_return_status := fnd_api.g_ret_sts_unexp_error;
2429 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2430 p_data => x_msg_data);
2431
2432 END delete_resource;
2433
2434
2435 /* Procedure to create the resource with the resource synchronizing parameters. */
2436
2437 PROCEDURE create_resource
2438 (P_API_VERSION IN NUMBER,
2439 P_INIT_MSG_LIST IN VARCHAR2,
2440 P_COMMIT IN VARCHAR2,
2441 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
2442 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE DEFAULT NULL,
2443 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE DEFAULT NULL,
2444 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE DEFAULT NULL,
2445 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE DEFAULT NULL,
2446 P_MANAGING_EMP_NUM IN PER_EMPLOYEES_CURRENT_X.EMPLOYEE_NUM%TYPE DEFAULT NULL,
2447 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
2448 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
2449 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE DEFAULT NULL,
2450 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE DEFAULT NULL,
2451 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE DEFAULT NULL,
2452 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE DEFAULT NULL,
2453 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE DEFAULT NULL,
2454 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE DEFAULT NULL,
2455 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE DEFAULT NULL,
2456 P_INTERACTION_CENTER_NAME IN VARCHAR2 DEFAULT NULL,
2457 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE DEFAULT NULL,
2458 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE DEFAULT NULL,
2459 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE DEFAULT NULL,
2460 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE DEFAULT NULL,
2461 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
2462 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE DEFAULT NULL,
2463 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
2464 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE DEFAULT NULL,
2465 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE DEFAULT NULL,
2466 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE DEFAULT NULL,
2467 --P_LOCATION IN MDSYS.SDO_GEOMETRY DEFAULT NULL,
2468 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2469 X_MSG_COUNT OUT NOCOPY NUMBER,
2470 X_MSG_DATA OUT NOCOPY VARCHAR2,
2471 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2472 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
2473 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE DEFAULT NULL,
2474 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
2475 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE DEFAULT NULL,
2476 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE DEFAULT NULL,
2477 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE DEFAULT NULL,
2478 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE DEFAULT NULL,
2479 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE DEFAULT NULL,
2480 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE DEFAULT NULL,
2481 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE DEFAULT NULL,
2482 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE DEFAULT NULL,
2483 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE DEFAULT NULL,
2484 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE DEFAULT NULL,
2485 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE DEFAULT NULL,
2486 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE DEFAULT NULL,
2487 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE DEFAULT NULL,
2488 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE DEFAULT NULL,
2489 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE DEFAULT NULL,
2490 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE DEFAULT NULL,
2491 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%type DEFAULT NULL,
2492 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%type DEFAULT NULL,
2493 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%type DEFAULT NULL,
2494 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%type DEFAULT NULL,
2495 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE DEFAULT NULL,
2496 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE DEFAULT NULL,
2497 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE DEFAULT NULL,
2498 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE DEFAULT NULL,
2499 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE DEFAULT NULL,
2500 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE DEFAULT NULL,
2501 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE DEFAULT NULL,
2502 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE DEFAULT NULL,
2503 P_USER_NAME IN VARCHAR2,
2504 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE DEFAULT NULL,
2505 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE DEFAULT NULL,
2506 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE DEFAULT NULL,
2507 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE DEFAULT NULL,
2508 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE DEFAULT NULL,
2509 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE DEFAULT NULL,
2510 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE DEFAULT NULL,
2511 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE DEFAULT NULL,
2512 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE DEFAULT NULL,
2513 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE DEFAULT NULL,
2514 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE DEFAULT NULL,
2515 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE DEFAULT NULL,
2516 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE DEFAULT NULL,
2517 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE DEFAULT NULL,
2518 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE DEFAULT NULL,
2519 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE DEFAULT NULL,
2520 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE DEFAULT NULL,
2521 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL
2522 )
2523 IS
2524
2525 l_api_version CONSTANT NUMBER := 1.0;
2526 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
2527
2528 --duplicated from the create_resource api above
2529 l_category jtf_rs_resource_extns.category%TYPE;
2530 l_source_id jtf_rs_resource_extns.source_id%TYPE;
2531 l_address_id jtf_rs_resource_extns.address_id%TYPE;
2532 l_contact_id jtf_rs_resource_extns.contact_id%TYPE;
2533 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
2534 l_managing_emp_num per_employees_current_x.employee_num%TYPE;
2535 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
2536 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
2537 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
2538 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
2539 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
2540 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
2541 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
2542 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
2543 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
2544 l_interaction_center_name VARCHAR2(256);
2545 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
2546 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
2547 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
2548 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
2549 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
2550 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
2551 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
2552 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
2553 l_user_id jtf_rs_resource_extns.user_id%TYPE;
2554 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
2555 --l_location MDSYS.SDO_GEOMETRY := p_location;
2556
2557 --added for NOCOPY
2558 l_managing_emp_id_out jtf_rs_resource_extns.managing_employee_id%TYPE ;
2559 l_server_group_id_out jtf_rs_resource_extns.server_group_id%TYPE ;
2560 l_comp_service_team_id_out jtf_rs_resource_extns.comp_service_team_id%TYPE;
2561
2562
2563 l_user_name jtf_rs_resource_extns.user_name%type;
2564 l_check_flag VARCHAR2(1);
2565 l_found BOOLEAN;
2566
2567
2568 /* Changed from view to direct table query stripping out unnecessary table joins
2569 for SQL Rep perf bug 4956627. Query logic taken from view JTF_RS_PARTNERS_VL.
2570 Nishant Singhai (13-Mar-2006)
2571 */
2572 /*
2573 CURSOR c_validate_partner(
2574 l_party_id IN NUMBER)
2575 IS
2576 SELECT 'Y'
2577 FROM jtf_rs_partners_vl
2578 WHERE party_id = l_party_id;
2579 */
2580 CURSOR c_validate_partner(l_party_id IN NUMBER)
2581 IS
2582 SELECT 'Y'
2583 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
2584 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
2585 WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' AND PARTY.PARTY_ID = REL.SUBJECT_ID)
2586 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
2587 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
2588 'CUSTOMER_INDIRECTLY_MANAGED_BY')
2589 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2590 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2591 AND REL.DIRECTIONAL_FLAG = 'F'
2592 AND REL.STATUS = 'A'
2593 AND PARTY.STATUS = 'A'
2594 AND PARTY2.STATUS = 'A'
2595 AND PARTY3.STATUS = 'A'
2596 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
2597 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
2598 AND REL.OBJECT_ID = PARTY3.PARTY_ID
2599 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
2600 AND party.party_id = l_party_id
2601 UNION ALL
2602 SELECT 'Y'
2603 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
2604 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
2605 WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND PARTY.PARTY_ID = REL.PARTY_ID )
2606 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
2607 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
2608 'CUSTOMER_INDIRECTLY_MANAGED_BY')
2609 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2610 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2611 AND REL.DIRECTIONAL_FLAG = 'F'
2612 AND REL.STATUS = 'A'
2613 AND PARTY.STATUS = 'A'
2614 AND PARTY2.STATUS = 'A'
2615 AND PARTY3.STATUS = 'A'
2616 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
2617 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
2618 AND REL.OBJECT_ID = PARTY3.PARTY_ID
2619 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
2620 AND party.party_id = l_party_id
2621 ;
2622
2623 CURSOR c_validate_partner_address (
2624 l_party_id IN NUMBER,
2625 l_party_site_id IN NUMBER)
2626 IS
2627 SELECT 'Y'
2628 FROM hz_party_sites
2629 WHERE party_id = l_party_id
2630 AND party_site_id = l_party_site_id;
2631
2632 CURSOR c_validate_partner_contact(
2633 l_party_id IN NUMBER,
2634 l_party_site_id IN NUMBER,
2635 l_contact_id IN NUMBER)
2636 IS
2637 SELECT 'Y'
2638 FROM jtf_rs_party_contacts_vl
2639 WHERE party_id = l_party_id
2640 AND nvl (party_site_id,-99) = nvl (l_party_site_id,-99)
2641 AND contact_id = l_contact_id;
2642
2643 /* -- Direct query from tables. But does not improve performance or shared memory
2644 -- significantly. So not using it as it will lead to dual maintainence (view + this logic)
2645 -- Test performed for SQL Rep Bug 4956627
2646
2647 CURSOR c_validate_partner_contact(
2648 l_party_id IN NUMBER,
2649 l_party_site_id IN NUMBER,
2650 l_contact_id IN NUMBER)
2651 IS
2652 SELECT 'Y'
2653 -- SELECT PARTY.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID , ORG_CONT.ORG_CONTACT_ID CONTACT_ID ,
2654 -- ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER , PARTY.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2655 FROM HZ_PARTIES PARTY , HZ_RELATIONSHIPS PARTY_REL , HZ_ORG_CONTACTS ORG_CONT ,
2656 HZ_ORG_CONTACT_ROLES CONT_ROLE
2657 WHERE PARTY.STATUS = 'A'
2658 AND PARTY.PARTY_TYPE = 'PERSON'
2659 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2660 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2661 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2662 AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
2663 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2664 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2665 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2666 AND PARTY_REL.STATUS = 'A'
2667 AND party.party_id = l_party_id
2668 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2669 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2670 UNION ALL
2671 SELECT 'Y'
2672 -- SELECT PARTY5.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
2673 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
2674 -- PARTY5.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2675 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_PARTIES PARTY5 , HZ_RELATIONSHIPS PARTY_REL ,
2676 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
2677 WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
2678 AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
2679 AND PARTY5.STATUS = 'A'
2680 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
2681 AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
2682 AND PARTY3.PARTY_TYPE = 'PERSON'
2683 AND PARTY3.STATUS = 'A'
2684 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
2685 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
2686 AND PARTY4.STATUS = 'A'
2687 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2688 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2689 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2690 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2691 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2692 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2693 AND PARTY_REL.STATUS = 'A'
2694 AND party5.party_id = l_party_id
2695 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2696 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2697 UNION ALL
2698 SELECT 'Y'
2699 -- SELECT PARTY4.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
2700 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
2701 -- PARTY3.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2702 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_RELATIONSHIPS PARTY_REL ,
2703 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
2704 WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
2705 AND PARTY3.PARTY_TYPE = 'PERSON'
2706 AND PARTY3.STATUS = 'A'
2707 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
2708 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
2709 AND PARTY4.STATUS = 'A'
2710 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2711 AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
2712 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
2713 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2714 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2715 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2716 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2717 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2718 AND PARTY_REL.STATUS = 'A'
2719 AND party4.party_id = l_party_id
2720 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2721 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2722 ;
2723 */
2724
2725 CURSOR c_validate_party_address(
2726 l_party_site_id IN NUMBER )
2727 IS
2728 SELECT 'Y'
2729 FROM hz_party_sites
2730 WHERE party_site_id = l_party_site_id;
2731
2732
2733 CURSOR c_validate_party_contact(
2734 l_party_id IN NUMBER,
2735 l_party_site_id IN NUMBER,
2736 l_contact_id IN NUMBER)
2737 IS
2738 SELECT 'Y'
2739 /* FROM jtf_rs_party_contacts_vl
2740 WHERE party_id = l_party_id
2741 AND nvl(party_site_id, 0) = nvl(l_party_site_id, 0)
2742 AND contact_id = l_contact_id; */
2743 -- changed the query the validate party contact id according to bug 2954064 as provided by the PRM team , sudarsana 2nd july 2004
2744 FROM hz_relationships hzr,
2745 hz_org_contacts hzoc
2746 WHERE hzr.party_id = l_party_id
2747 AND hzoc.org_contact_id = l_contact_id
2748 AND hzr.directional_flag = 'F'
2749 AND hzr.relationship_code = 'EMPLOYEE_OF'
2750 AND hzr.subject_table_name ='HZ_PARTIES'
2751 AND hzr.object_table_name ='HZ_PARTIES'
2752 AND hzr.start_date <= SYSDATE
2753 AND (hzr.end_date is null or hzr.end_date > SYSDATE)
2754 AND hzr.status = 'A'
2755 AND hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id;
2756
2757 /* SQL Rep perf improvement bug 4956627 Nishant Singhai (14-Mar-2006) fixed by
2758 modifying query logic given in bug # 4052112
2759 OIC expanded the definition of compensation analyst to include any active user in the
2760 system regardless of their assignment to a CN responsibility.
2761 */
2762 CURSOR c_assigned_to_group_id(
2763 l_assigned_to_group_id IN NUMBER)
2764 IS
2765 SELECT u.user_id
2766 FROM fnd_user u,
2767 jtf_rs_resource_extns r
2768 WHERE u.user_id = r.user_id
2769 AND u.user_id = l_assigned_to_group_id;
2770
2771 CURSOR c_validate_user_id(
2772 l_user_id IN NUMBER)
2773 IS
2774 SELECT 'Y'
2775 FROM jtf_rs_resource_extns
2776 WHERE user_id = l_user_id;
2777
2778 -- Enh 3947611 2-dec-2004 added cursor to check emp existence
2779 CURSOR c_emp_exist(p_person_id IN NUMBER)
2780 IS
2781 SELECT 'x' value,full_name
2782 FROM per_all_people_f
2783 WHERE person_id = p_person_id;
2784
2785 r_emp_exist c_emp_exist%rowtype;
2786
2787
2788
2789 BEGIN
2790
2791 l_category := upper(p_category);
2792 l_source_id := p_source_id;
2793 l_address_id := p_address_id;
2794 l_contact_id := p_contact_id;
2795 l_managing_emp_id := p_managing_emp_id;
2796 l_managing_emp_num := p_managing_emp_num;
2797 l_start_date_active := p_start_date_active;
2798 l_end_date_active := p_end_date_active;
2799 l_time_zone := p_time_zone;
2800 l_cost_per_hr := p_cost_per_hr;
2801 l_primary_language := p_primary_language;
2802 l_secondary_language := p_secondary_language;
2803 l_support_site_id := p_support_site_id;
2804 l_ies_agent_login := p_ies_agent_login;
2805 l_server_group_id := p_server_group_id;
2806 l_interaction_center_name := p_interaction_center_name;
2807 l_assigned_to_group_id := p_assigned_to_group_id;
2808 l_cost_center := p_cost_center;
2809 l_charge_to_cost_center := p_charge_to_cost_center;
2810 l_comp_currency_code := p_comp_currency_code;
2811 l_commissionable_flag := p_commissionable_flag;
2812 l_hold_reason_code := p_hold_reason_code;
2813 l_hold_payment := p_hold_payment;
2814 l_comp_service_team_id := p_comp_service_team_id;
2815 l_user_id := p_user_id;
2816 l_transaction_number := p_transaction_number;
2817
2818 --Standard Start of API SAVEPOINT
2819 SAVEPOINT CREATE_RESOURCE_SP;
2820
2821 x_return_status := fnd_api.g_ret_sts_success;
2822
2823 --Standard Call to check API compatibility
2824 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2825 THEN
2826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2827 END IF;
2828
2829 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2830 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2831 THEN
2832 FND_MSG_PUB.Initialize;
2833 END IF;
2834
2835 /* Validate the Resource Category */
2836
2837 jtf_resource_utl.validate_resource_category(
2838 p_category => l_category,
2839 x_return_status => x_return_status
2840 );
2841
2842
2843 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2844
2845 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2846 RAISE FND_API.G_EXC_ERROR;
2847 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2849 END IF;
2850
2851 END IF;
2852
2853
2854 /* Validate Source ID */
2855
2856 jtf_resource_utl.validate_source_id (
2857 p_category => l_category,
2858 p_source_id => l_source_id,
2859 p_address_id => l_address_id,
2860 x_return_status => x_return_status
2861 );
2862
2863 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2864 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2865 RAISE FND_API.G_EXC_ERROR;
2866 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2868 END IF;
2869
2870 END IF;
2871
2872
2873 /* Validations for category as OTHER and TBH */
2874
2875 IF l_category IN ('OTHER', 'TBH') THEN
2876
2877 /* Validate that the source_id, address_id, contact_id and managing_employee_id
2878 are all NULL */
2879
2880 IF (l_source_id IS NOT NULL OR l_address_id IS NOT NULL
2881 OR l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL
2882 OR l_managing_emp_num IS NOT NULL) THEN
2883
2884 -- dbms_output.put_line('For OTHER category, source_id, address_id, contact_id and managing_emp_id should be all null');
2885
2886 fnd_message.set_name('JTF', 'JTF_RS_OTHER_IDS_NOT_NULL');
2887 fnd_msg_pub.add;
2888
2889 RAISE fnd_api.g_exc_error;
2890
2891 END IF;
2892
2893 END IF;
2894
2895 /* Validations for category as PARTNER */
2896
2897 IF l_category = 'PARTNER' THEN
2898
2899 /* Validate the source_id */
2900
2901 IF (l_source_id IS NULL) THEN
2902 -- dbms_output.put_line('For PARTNER category, source_id should not be null');
2903 fnd_message.set_name('JTF', 'JTF_RS_PARTNER_IDS_NULL');
2904 fnd_msg_pub.add;
2905 RAISE fnd_api.g_exc_error;
2906
2907 ELSE
2908 OPEN c_validate_partner(l_source_id);
2909 FETCH c_validate_partner INTO l_check_flag;
2910 IF c_validate_partner%NOTFOUND THEN
2911 -- dbms_output.put_line('Partner does not exist for the passed source_id');
2912 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTNER_IDS');
2913 fnd_msg_pub.add;
2914 RAISE fnd_api.g_exc_error;
2915
2916 END IF;
2917 CLOSE c_validate_partner;
2918 END IF;
2919
2920 /* Validate the address_id if specified */
2921
2922 IF l_address_id IS NOT NULL THEN
2923 OPEN c_validate_partner_address(l_source_id, l_address_id);
2924 FETCH c_validate_partner_address INTO l_check_flag;
2925 IF c_validate_partner_address%NOTFOUND THEN
2926 -- dbms_output.put_line('Invalid Partner Address Id');
2927 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_ADDRESS_ID');
2928 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
2929 fnd_msg_pub.add;
2930 RAISE fnd_api.g_exc_error;
2931
2932 END IF;
2933 CLOSE c_validate_partner_address;
2934 END IF;
2935
2936
2937 /* Validate the contact_id if specified */
2938
2939 IF l_contact_id IS NOT NULL THEN
2940 OPEN c_validate_partner_contact(l_source_id, l_address_id, l_contact_id);
2941 FETCH c_validate_partner_contact INTO l_check_flag;
2942 IF c_validate_partner_contact%NOTFOUND THEN
2943 -- dbms_output.put_line('Invalid Partner Contact Id');
2944 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_CONTACT_ID');
2945 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
2946 fnd_msg_pub.add;
2947 RAISE fnd_api.g_exc_error;
2948
2949 END IF;
2950 CLOSE c_validate_partner_contact;
2951 END IF;
2952
2953 END IF;
2954
2955 /* For all other Categories, validate the source_id from jtf_objects */
2956 /* Enh 3947611 2-dec-2004 : added EMPLOYEE to the exception also. Import future dated employees
2957 this had to be an exception else the seed data for object EMPLOYEE if jtf_objects had to be changed. This may have
2958 some backward compatibility issues for consumers who use JTF_OBJECTS to validate OR list EMPLOYEE
2959 */
2960
2961 IF l_category NOT IN ('OTHER' , 'PARTNER' , 'TBH', 'EMPLOYEE') THEN
2962 IF l_source_id IS NULL THEN
2963 -- dbms_output.put_line('Source Id should not be Null');
2964 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
2965 fnd_msg_pub.add;
2966 RAISE fnd_api.g_exc_error;
2967
2968 END IF;
2969
2970 jtf_resource_utl.check_object_existence_migr(
2971
2972 P_OBJECT_CODE => l_category,
2973 P_SELECT_ID => l_source_id,
2974 P_OBJECT_USER_CODE => 'RESOURCE_CATEGORIES',
2975 P_RS_ID_PUB_FLAG => G_RS_ID_PUB_FLAG,
2976 X_FOUND => l_found,
2977 X_RETURN_STATUS => x_return_status
2978 );
2979
2980 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2981
2982 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2983 RAISE FND_API.G_EXC_ERROR;
2984 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2986 END IF;
2987
2988 END IF;
2989
2990
2991 IF l_found = FALSE THEN
2992
2993 -- dbms_output.put_line('Invalid Source Id');
2994
2995 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
2996 fnd_message.set_token('P_SOURCE_ID', l_source_id);
2997 fnd_msg_pub.add;
2998
2999 RAISE fnd_api.g_exc_error;
3000
3001
3002 END IF;
3003
3004 END IF;
3005
3006
3007 /* Enh 3947611 2-dec-2004:EMPLOYEE VALIDATION has been removed from the above code. so adding validation
3008 for EMPLOYEE
3009 */
3010
3011 if l_category = 'EMPLOYEE' THEN
3012 -- First check is null check for source id
3013 IF l_source_id IS NULL THEN
3014 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
3015 fnd_msg_pub.add;
3016 RAISE fnd_api.g_exc_error;
3017 END IF;
3018
3019 open c_emp_exist(l_source_id);
3020 fetch c_emp_exist into r_emp_exist;
3021 close c_emp_exist;
3022
3023 if(nvl(r_emp_exist.value , 'y') <> 'x')
3024 then
3025 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
3026 fnd_message.set_token('P_SOURCE_ID', l_source_id);
3027 fnd_msg_pub.add;
3028 RAISE fnd_api.g_exc_error;
3029 end if;
3030
3031 END IF; -- end of check l_category = 'EMPLOYEE'
3032
3033 /* Validations for category as PARTY */
3034
3035 IF l_category = 'PARTY' THEN
3036
3037 /* Validate the address_id if specified */
3038
3039 IF l_address_id IS NOT NULL THEN
3040
3041 OPEN c_validate_party_address(l_address_id);
3042
3043 FETCH c_validate_party_address INTO l_check_flag;
3044
3045
3046 IF c_validate_party_address%NOTFOUND THEN
3047
3048 -- dbms_output.put_line('Invalid Party Address');
3049
3050 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTY_ADDRESS');
3051 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
3052 fnd_msg_pub.add;
3053
3054 RAISE fnd_api.g_exc_error;
3055
3056
3057 END IF;
3058
3059 /* Close the cursor */
3060
3061 CLOSE c_validate_party_address;
3062
3063
3064 END IF;
3065
3066
3067 /* Validate the contact_id if specified */
3068
3069 IF l_contact_id IS NOT NULL THEN
3070
3071 OPEN c_validate_party_contact(l_source_id, l_address_id, l_contact_id);
3072
3073 FETCH c_validate_party_contact INTO l_check_flag;
3074
3075
3076 IF c_validate_party_contact%NOTFOUND THEN
3077
3078 -- dbms_output.put_line('Invalid Party Contact Id');
3079
3080 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTY_CONTACT_ID');
3081 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
3082 fnd_msg_pub.add;
3083
3084 RAISE fnd_api.g_exc_error;
3085
3086 END IF;
3087
3088
3089 /* Close the cursor */
3090
3091 CLOSE c_validate_party_contact;
3092
3093 END IF;
3094
3095 END IF;
3096
3097
3098
3099 /* Validations for category as SUPPLIER_CONTACT */
3100
3101 IF l_category = 'SUPPLIER_CONTACT' THEN
3102
3103 /* Validate that the address_id and contact_id are NULL */
3104
3105 -- address_id check (NOT NULL) being removed, to store the address_id of supplier contact
3106 -- Fix for bug # 3812930
3107 IF (l_contact_id IS NOT NULL) THEN
3108
3109 -- dbms_output.put_line('For SUPPLIER_CONTACT category, address_id and contact_id should be null');
3110
3111 fnd_message.set_name('JTF', 'JTF_RS_SC_IDS_NOT_NULL');
3112 fnd_msg_pub.add;
3113
3114 RAISE fnd_api.g_exc_error;
3115
3116 END IF;
3117
3118 END IF;
3119
3120
3121 /* Validations for category as EMPLOYEE */
3122
3123 IF (l_category = 'EMPLOYEE') THEN
3124
3125 /* Validate that the address_id, contact_id and managing_emp_id are NULL */
3126
3127 --address_id check (null) being removed, to store the address_id of employee 03/26/01
3128
3129 IF (l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL OR l_managing_emp_num IS NOT NULL) THEN
3130
3131 -- dbms_output.put_line('For EMPLOYEE category, contact_id should be null');
3132
3133 fnd_message.set_name('JTF', 'JTF_RS_EMP_IDS_NOT_NULL');
3134 fnd_msg_pub.add;
3135
3136 RAISE fnd_api.g_exc_error;
3137
3138
3139 END IF;
3140
3141 END IF;
3142
3143
3144
3145 /* Validate the Managing Employee Id if specified */
3146
3147 jtf_resource_utl.validate_employee_resource(
3148 p_emp_resource_id => l_managing_emp_id,
3149 p_emp_resource_number => l_managing_emp_num,
3150 x_return_status => x_return_status,
3151 x_emp_resource_id => l_managing_emp_id_out
3152 );
3153
3154 -- added for NOCOPY
3155 l_managing_emp_id := l_managing_emp_id_out;
3156
3157 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3158
3159 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3160 RAISE FND_API.G_EXC_ERROR;
3161 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3163 END IF;
3164
3165 END IF;
3166
3167
3168
3169 /* Validate that the Start Date Active is specified */
3170
3171 IF l_start_date_active IS NULL THEN
3172
3173 -- dbms_output.put_line('Start Date Active cannot be null');
3174
3175 fnd_message.set_name('JTF', 'JTF_RS_START_DATE_NULL');
3176 fnd_msg_pub.add;
3177
3178 RAISE fnd_api.g_exc_error;
3179
3180
3181 END IF;
3182
3183
3184
3185 /* Validate the Time Zone */
3186
3187 IF l_time_zone IS NOT NULL THEN
3188
3189 jtf_resource_utl.validate_time_zone(
3190 p_time_zone_id => l_time_zone,
3191 x_return_status => x_return_status
3192 );
3193
3194 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3195
3196 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3197 RAISE FND_API.G_EXC_ERROR;
3198 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3200 END IF;
3201
3202 END IF;
3203
3204 END IF;
3205
3206
3207
3208 /* Validate the Primary Language */
3209
3210 IF l_primary_language IS NOT NULL THEN
3211
3212 jtf_resource_utl.validate_nls_language(
3213 p_nls_language => l_primary_language,
3214 x_return_status => x_return_status
3215 );
3216
3217 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3218
3219 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3220 RAISE FND_API.G_EXC_ERROR;
3221 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3223 END IF;
3224
3225 END IF;
3226
3227 END IF;
3228
3229
3230
3231 /* Validate the Secondary Language */
3232
3233 IF l_secondary_language IS NOT NULL THEN
3234
3235 jtf_resource_utl.validate_nls_language(
3236 p_nls_language => l_secondary_language,
3237 x_return_status => x_return_status
3238 );
3239
3240 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3241
3242 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3243 RAISE FND_API.G_EXC_ERROR;
3244 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246 END IF;
3247
3248 END IF;
3249
3250 END IF;
3251
3252
3253
3254 /* Validate the Support Site */
3255
3256 IF l_support_site_id IS NOT NULL THEN
3257
3258 jtf_resource_utl.validate_support_site_id(
3259 p_support_site_id => l_support_site_id,
3260 x_return_status => x_return_status
3261 );
3262
3263 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3264
3265 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3266 RAISE FND_API.G_EXC_ERROR;
3267 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3269 END IF;
3270
3271 END IF;
3272
3273 END IF;
3274
3275
3276
3277 /* Validate the Server Group. */
3278
3279 jtf_resource_utl.validate_server_group(
3280 p_server_group_id => l_server_group_id,
3281 p_server_group_name => l_interaction_center_name,
3282 x_return_status => x_return_status,
3283 x_server_group_id => l_server_group_id_out
3284 );
3285 -- added for NOCOPY
3286 l_server_group_id := l_server_group_id_out;
3287
3288 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3289
3290 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3291 RAISE FND_API.G_EXC_ERROR;
3292 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3294 END IF;
3295
3296 END IF;
3297
3298
3299
3300 /* Validate the assigned_to_group_id if specified */
3301
3302 IF l_assigned_to_group_id IS NOT NULL THEN
3303
3304 OPEN c_assigned_to_group_id(l_assigned_to_group_id);
3305
3306 FETCH c_assigned_to_group_id INTO l_assigned_to_group_id;
3307
3308
3309 IF c_assigned_to_group_id%NOTFOUND THEN
3310
3311 -- dbms_output.put_line('Invalid Assigned To Group Id');
3312
3313 fnd_message.set_name('JTF', 'JTF_RS_ERR_ASSIGN_TO_GRP_ID');
3314 fnd_message.set_token('P_ASSIGNED_TO_GROUP_ID', l_assigned_to_group_id);
3315 fnd_msg_pub.add;
3316
3317 RAISE fnd_api.g_exc_error;
3318
3319
3320 END IF;
3321
3322
3323 /* Close the cursor */
3324
3325 CLOSE c_assigned_to_group_id;
3326
3327 END IF;
3328
3329
3330
3331 /* Validate the Comp Currency Code */
3332
3333 IF l_comp_currency_code IS NOT NULL THEN
3334
3335 jtf_resource_utl.validate_currency_code(
3336 p_currency_code => l_comp_currency_code,
3337 x_return_status => x_return_status
3338 );
3339
3340 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3341
3342 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3343 RAISE FND_API.G_EXC_ERROR;
3344 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3346 END IF;
3347
3348 END IF;
3349
3350 END IF;
3351
3352
3353 /* Validate the value of the commisionable flag */
3354
3355 IF l_commissionable_flag <> 'Y' AND l_commissionable_flag <> 'N' THEN
3356
3357 -- dbms_output.put_line('Commissionable Flag should either be ''Y'' or ''N'' ');
3358
3359 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3360 fnd_msg_pub.add;
3361
3362 RAISE fnd_api.g_exc_error;
3363
3364
3365 END IF;
3366
3367
3368 /* Validate the value of the Hold Payment flag */
3369
3370 IF l_hold_payment <> 'Y' AND l_hold_payment <> 'N' THEN
3371
3372 -- dbms_output.put_line('Hold Payment should either be ''Y'' or ''N'' ');
3373
3374 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3375 fnd_msg_pub.add;
3376
3377 RAISE fnd_api.g_exc_error;
3378
3379
3380 END IF;
3381
3382
3383
3384 /* Validate the Hold Reason Code */
3385
3386 IF l_hold_reason_code IS NOT NULL THEN
3387
3388 jtf_resource_utl.validate_hold_reason_code(
3389 p_hold_reason_code => l_hold_reason_code,
3390 x_return_status => x_return_status
3391 );
3392
3393 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3394
3395 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3396 RAISE FND_API.G_EXC_ERROR;
3397 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3399 END IF;
3400
3401 END IF;
3402
3403 END IF;
3404
3405
3406 /* Validate that the user_id should only be specified in case of
3407 'EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT' categories */
3408
3409 IF l_category NOT IN ('EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT') THEN
3410
3411 IF l_user_id IS NOT NULL THEN
3412
3413 fnd_message.set_name('JTF', 'JTF_RS_USERID_ERROR');
3414 fnd_msg_pub.add;
3415
3416 RAISE fnd_api.g_exc_error;
3417
3418
3419 END IF;
3420
3421 ELSE
3422
3423 /* Validate the User Id if specified */
3424
3425 IF l_user_id IS NOT NULL THEN
3426
3427 jtf_resource_utl.validate_user_id(
3428 p_user_id => l_user_id,
3429 p_category => l_category,
3430 p_source_id => l_source_id,
3431 x_return_status => x_return_status
3432 );
3433
3434 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3435
3436 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3437 RAISE FND_API.G_EXC_ERROR;
3438 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3440 END IF;
3441 else
3442
3443 OPEN c_validate_user_id(l_user_id);
3444
3445 FETCH c_validate_user_id INTO l_check_flag;
3446
3447
3448 IF c_validate_user_id%FOUND THEN
3449
3450 -- dbms_output.put_line('duplicate user Id');
3451
3452 fnd_message.set_name('JTF', 'JTF_RS_ERR_DUPLICATE_USER_ID');
3453 fnd_message.set_token('P_USER_ID', l_user_id);
3454 fnd_msg_pub.add;
3455
3456 RAISE fnd_api.g_exc_error;
3457
3458
3459 END IF;
3460
3461
3462 /* Close the cursor */
3463
3464 CLOSE c_validate_user_id;
3465
3466
3467
3468 END IF;
3469
3470 END IF;
3471
3472 END IF;
3473
3474
3475 /* Validate the Comp Service Team Id if specified */
3476
3477 IF l_comp_service_team_id IS NOT NULL THEN
3478
3479 jtf_resource_utl.validate_resource_team(
3480 p_team_id => l_comp_service_team_id,
3481 p_team_number => null,
3482 x_return_status => x_return_status,
3483 x_team_id => l_comp_service_team_id_out
3484 );
3485 -- added for NOCOPY
3486 l_comp_service_team_id := l_comp_service_team_id_out;
3487
3488 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3489
3490 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3491 RAISE FND_API.G_EXC_ERROR;
3492 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3494 END IF;
3495
3496 END IF;
3497
3498 END IF;
3499
3500
3501
3502 /* Check the Global Variable for Resource ID, and call the appropriate Private API */
3503
3504 IF G_RS_ID_PUB_FLAG = 'Y' THEN
3505
3506 /* Call the private procedure with the validated parameters. */
3507 jtf_rs_resource_pvt.create_resource (
3508 P_API_VERSION => 1,
3509 P_INIT_MSG_LIST => fnd_api.g_false,
3510 P_COMMIT => fnd_api.g_false,
3511 P_CATEGORY => l_category,
3512 P_SOURCE_ID => l_source_id,
3513 P_ADDRESS_ID => l_address_id,
3514 P_CONTACT_ID => l_contact_id,
3515 P_MANAGING_EMP_ID => l_managing_emp_id,
3516 P_START_DATE_ACTIVE => l_start_date_active,
3517 P_END_DATE_ACTIVE => l_end_date_active,
3518 P_TIME_ZONE => l_time_zone,
3519 P_COST_PER_HR => l_cost_per_hr,
3520 P_PRIMARY_LANGUAGE => l_primary_language,
3521 P_SECONDARY_LANGUAGE => l_secondary_language,
3522 P_SUPPORT_SITE_ID => l_support_site_id,
3523 P_IES_AGENT_LOGIN => l_ies_agent_login,
3524 P_SERVER_GROUP_ID => l_server_group_id,
3525 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
3526 P_COST_CENTER => l_cost_center,
3527 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
3528 P_COMP_CURRENCY_CODE => l_comp_currency_code,
3529 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
3530 P_HOLD_REASON_CODE => l_hold_reason_code,
3531 P_HOLD_PAYMENT => l_hold_payment,
3532 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
3533 P_USER_ID => l_user_id,
3534 P_TRANSACTION_NUMBER => l_transaction_number,
3535 --P_LOCATION => l_location,
3536 X_RETURN_STATUS => x_return_status,
3537 X_MSG_COUNT => x_msg_count,
3538 X_MSG_DATA => x_msg_data,
3539 X_RESOURCE_ID => x_resource_id,
3540 X_RESOURCE_NUMBER => x_resource_number,
3541 P_ATTRIBUTE1 => p_attribute1,
3542 P_ATTRIBUTE2 => p_attribute2,
3543 P_ATTRIBUTE3 => p_attribute3,
3544 P_ATTRIBUTE4 => p_attribute4,
3545 P_ATTRIBUTE5 => p_attribute5,
3546 P_ATTRIBUTE6 => p_attribute6,
3547 P_ATTRIBUTE7 => p_attribute7,
3548 P_ATTRIBUTE8 => p_attribute8,
3549 P_ATTRIBUTE9 => p_attribute9,
3550 P_ATTRIBUTE10 => p_attribute10,
3551 P_ATTRIBUTE11 => p_attribute11,
3552 P_ATTRIBUTE12 => p_attribute12,
3553 P_ATTRIBUTE13 => p_attribute13,
3554 P_ATTRIBUTE14 => p_attribute14,
3555 P_ATTRIBUTE15 => p_attribute15,
3556 P_ATTRIBUTE_CATEGORY => p_attribute_category,
3557 P_RESOURCE_NAME => P_RESOURCE_NAME ,
3558 P_SOURCE_NAME => P_SOURCE_NAME,
3559 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
3560 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
3561 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
3562 P_SOURCE_PHONE => P_SOURCE_PHONE,
3563 P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
3564 P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
3565 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
3566 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
3567 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
3568 P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
3569 P_SOURCE_CITY => P_SOURCE_CITY,
3570 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
3571 P_SOURCE_STATE => P_SOURCE_STATE,
3572 P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
3573 P_SOURCE_COUNTY => P_SOURCE_COUNTY,
3574 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
3575 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
3576 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
3577 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
3578 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
3579 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
3580 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
3581 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
3582 P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
3583 P_SOURCE_STATUS => P_SOURCE_STATUS,
3584 P_SOURCE_OFFICE => P_SOURCE_OFFICE,
3585 P_SOURCE_LOCATION => P_SOURCE_LOCATION,
3586 P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
3587 P_USER_NAME => P_USER_NAME,
3588 P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
3589 P_SOURCE_PAGER => P_SOURCE_PAGER
3590
3591 );
3592
3593
3594
3595 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3596 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3597 RAISE FND_API.G_EXC_ERROR;
3598 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3600 END IF;
3601 END IF;
3602
3603 ELSE
3604
3605 -- dbms_output.put_line ('Before call to the private API create_resource_migrate' );
3606
3607 /* Call the private procedure for Migration. */
3608
3609 jtf_rs_resource_pvt.create_resource_migrate (
3610 P_API_VERSION => 1,
3611 P_INIT_MSG_LIST => fnd_api.g_false,
3612 P_COMMIT => fnd_api.g_false,
3613 P_CATEGORY => l_category,
3614 P_SOURCE_ID => l_source_id,
3615 P_ADDRESS_ID => l_address_id,
3616 P_CONTACT_ID => l_contact_id,
3617 P_MANAGING_EMP_ID => l_managing_emp_id,
3618 P_START_DATE_ACTIVE => l_start_date_active,
3619 P_END_DATE_ACTIVE => l_end_date_active,
3620 P_TIME_ZONE => l_time_zone,
3621 P_COST_PER_HR => l_cost_per_hr,
3622 P_PRIMARY_LANGUAGE => l_primary_language,
3623 P_SECONDARY_LANGUAGE => l_secondary_language,
3624 P_SUPPORT_SITE_ID => l_support_site_id,
3625 P_IES_AGENT_LOGIN => l_ies_agent_login,
3626 P_SERVER_GROUP_ID => l_server_group_id,
3627 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
3628 P_COST_CENTER => l_cost_center,
3629 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
3630 P_COMP_CURRENCY_CODE => l_comp_currency_code,
3631 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
3632 P_HOLD_REASON_CODE => l_hold_reason_code,
3633 P_HOLD_PAYMENT => l_hold_payment,
3634 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
3635 P_USER_ID => l_user_id,
3636 P_TRANSACTION_NUMBER => l_transaction_number,
3637 --P_LOCATION => l_location,
3638 P_RESOURCE_ID => G_RESOURCE_ID,
3639 P_ATTRIBUTE1 => G_ATTRIBUTE1,
3640 P_ATTRIBUTE2 => G_ATTRIBUTE2,
3641 P_ATTRIBUTE3 => G_ATTRIBUTE3,
3642 P_ATTRIBUTE4 => G_ATTRIBUTE4,
3643 P_ATTRIBUTE5 => G_ATTRIBUTE5,
3644 P_ATTRIBUTE6 => G_ATTRIBUTE6,
3645 P_ATTRIBUTE7 => G_ATTRIBUTE7,
3646 P_ATTRIBUTE8 => G_ATTRIBUTE8,
3647 P_ATTRIBUTE9 => G_ATTRIBUTE9,
3648 P_ATTRIBUTE10 => G_ATTRIBUTE10,
3649 P_ATTRIBUTE11 => G_ATTRIBUTE11,
3650 P_ATTRIBUTE12 => G_ATTRIBUTE12,
3651 P_ATTRIBUTE13 => G_ATTRIBUTE13,
3652 P_ATTRIBUTE14 => G_ATTRIBUTE14,
3653 P_ATTRIBUTE15 => G_ATTRIBUTE15,
3654 P_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
3655 X_RETURN_STATUS => x_return_status,
3656 X_MSG_COUNT => x_msg_count,
3657 X_MSG_DATA => x_msg_data,
3658 X_RESOURCE_ID => x_resource_id,
3659 X_RESOURCE_NUMBER => x_resource_number
3660 );
3661 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3662 -- dbms_output.put_line('Failed status from call to private procedure');
3663 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3664 RAISE FND_API.G_EXC_ERROR;
3665 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3667 END IF;
3668 END IF;
3669
3670 END IF;
3671
3672 IF (x_return_status <> fnd_api.g_ret_sts_success)
3673 THEN
3674 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3675 RAISE FND_API.G_EXC_ERROR;
3676 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3678 END IF;
3679 END IF;
3680
3681 IF fnd_api.to_boolean (p_commit)
3682 THEN
3683 COMMIT WORK;
3684 END IF;
3685
3686
3687 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3688
3689 EXCEPTION
3690 WHEN fnd_api.g_exc_error THEN
3691 ROLLBACK TO create_resource_sp;
3692 x_return_status := fnd_api.g_ret_sts_error;
3693 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3694 p_data => x_msg_data);
3695 WHEN fnd_api.g_exc_unexpected_error THEN
3696 ROLLBACK TO create_resource_sp;
3697 x_return_status := fnd_api.g_ret_sts_unexp_error;
3698 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3699 p_data => x_msg_data);
3700 WHEN OTHERS THEN
3701 ROLLBACK TO create_resource_sp;
3702 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3703 fnd_message.set_token('P_SQLCODE',SQLCODE);
3704 fnd_message.set_token('P_SQLERRM',SQLERRM);
3705 fnd_message.set_token('P_API_NAME', l_api_name);
3706 FND_MSG_PUB.add;
3707 x_return_status := fnd_api.g_ret_sts_unexp_error;
3708 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3709 p_data => x_msg_data);
3710
3711 END;
3712 END jtf_rs_resource_pub;