[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_PUB
Source
1 PACKAGE BODY jtf_rs_resource_pub AS
2 /* $Header: jtfrsprb.pls 120.5 2006/03/14 17:26:49 nsinghai 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
1512
1513 CURSOR c_resource_id(
1514 l_resource_id IN NUMBER)
1515 IS
1516 SELECT resource_id
1517 FROM jtf_rs_resource_extns
1518 WHERE resource_id = l_resource_id;
1519
1520
1521 CURSOR c_resource_number(
1522 l_resource_number IN VARCHAR2)
1523 IS
1524 SELECT resource_id
1525 FROM jtf_rs_resource_extns
1526 WHERE resource_number = l_resource_number;
1527
1528
1529 BEGIN
1530
1531 l_resource_id := p_resource_id;
1532 l_resource_number := p_resource_number;
1533 l_managing_emp_id := p_managing_emp_id;
1534 l_start_date_active := p_start_date_active;
1535 l_end_date_active := p_end_date_active;
1536 l_time_zone := p_time_zone;
1537 l_cost_per_hr := p_cost_per_hr;
1538 l_primary_language := p_primary_language;
1539 l_secondary_language := p_secondary_language;
1540 l_support_site_id := p_support_site_id;
1541 l_ies_agent_login := p_ies_agent_login;
1542 l_server_group_id := p_server_group_id;
1543 l_assigned_to_group_id := p_assigned_to_group_id;
1544 l_cost_center := p_cost_center;
1545 l_charge_to_cost_center := p_charge_to_cost_center;
1546 l_comp_currency_code := p_comp_currency_code;
1547 l_commissionable_flag := p_commissionable_flag;
1548 l_hold_reason_code := p_hold_reason_code;
1549 l_hold_payment := p_hold_payment;
1550 l_comp_service_team_id := p_comp_service_team_id;
1551 l_user_id := p_user_id;
1552 l_object_version_num := p_object_version_num;
1553
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;
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;
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
1633
1634 /* Call the private procedure with the validated parameters. */
1635
1636 jtf_rs_resource_pvt.update_resource
1637 (P_API_VERSION => 1,
1638 P_INIT_MSG_LIST => fnd_api.g_false,
1639 P_COMMIT => fnd_api.g_false,
1640 P_RESOURCE_ID => l_resource_id,
1641 P_MANAGING_EMP_ID => l_managing_emp_id,
1642 P_START_DATE_ACTIVE => l_start_date_active,
1643 P_END_DATE_ACTIVE => l_end_date_active,
1644 P_TIME_ZONE => l_time_zone,
1645 P_COST_PER_HR => l_cost_per_hr,
1646 P_PRIMARY_LANGUAGE => l_primary_language,
1647 P_SECONDARY_LANGUAGE => l_secondary_language,
1648 P_SUPPORT_SITE_ID => l_support_site_id,
1649 P_IES_AGENT_LOGIN => l_ies_agent_login,
1650 P_SERVER_GROUP_ID => l_server_group_id,
1651 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1652 P_COST_CENTER => l_cost_center,
1653 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1654 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1655 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1656 P_HOLD_REASON_CODE => l_hold_reason_code,
1657 P_HOLD_PAYMENT => l_hold_payment,
1658 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1659 P_USER_ID => l_user_id,
1660 --P_LOCATION => l_location,
1661 P_OBJECT_VERSION_NUM => l_object_version_num,
1662 X_RETURN_STATUS => x_return_status,
1663 X_MSG_COUNT => x_msg_count,
1664 X_MSG_DATA => x_msg_data,
1665 --added this so that the overloaded procedure for update resource is called
1666 -- otherwise all source coulmns were being set to null
1667 P_SOURCE_NAME => fnd_api.g_miss_char
1668 );
1669
1670
1671 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1672
1673 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1674 RAISE FND_API.G_EXC_ERROR;
1675 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1677 END IF;
1678
1679 END IF;
1680
1681 /* Return the new value of the object version number */
1682
1683 p_object_version_num := l_object_version_num;
1684
1685 IF fnd_api.to_boolean(p_commit) THEN
1686
1687 COMMIT WORK;
1688
1689 END IF;
1690
1691 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1692
1693
1694
1695 EXCEPTION
1696 WHEN fnd_api.g_exc_error THEN
1697 ROLLBACK TO update_resource_pub;
1698 x_return_status := fnd_api.g_ret_sts_error;
1699 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1700 p_data => x_msg_data);
1701 WHEN fnd_api.g_exc_unexpected_error THEN
1702 ROLLBACK TO update_resource_pub;
1703 x_return_status := fnd_api.g_ret_sts_unexp_error;
1704 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1705 p_data => x_msg_data);
1706 WHEN OTHERS THEN
1707 ROLLBACK TO update_resource_pub;
1708 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1709 fnd_message.set_token('P_SQLCODE',SQLCODE);
1710 fnd_message.set_token('P_SQLERRM',SQLERRM);
1711 fnd_message.set_token('P_API_NAME', l_api_name);
1712 FND_MSG_PUB.add;
1713 x_return_status := fnd_api.g_ret_sts_unexp_error;
1714 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1715 p_data => x_msg_data);
1716
1717 END update_resource;
1718
1719 /* Procedure to update the resource with new columns based on input values
1720 passed by calling routines. */
1721
1722 PROCEDURE update_resource
1723 (P_API_VERSION IN NUMBER,
1724 P_INIT_MSG_LIST IN VARCHAR2,
1725 P_COMMIT IN VARCHAR2,
1726 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
1727 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
1728 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
1729 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
1730 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
1731 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
1732 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
1733 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
1734 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
1735 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
1736 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
1737 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
1738 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
1739 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
1740 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
1741 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
1742 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
1743 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
1744 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
1745 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
1746 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
1747 -- P_LOCATION IN MDSYS.SDO_GEOMETRY,
1748 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
1749 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
1750 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
1751 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
1752 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
1753 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
1754 P_SOURCE_ORG_ID IN NUMBER,
1755 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
1756 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
1757 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
1758 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
1759 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
1760 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
1761 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
1762 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
1763 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
1764 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
1765 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
1766 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
1767 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
1768 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
1769 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
1770 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
1771 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
1772 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
1773 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
1774 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
1775 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
1776 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
1777 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
1778 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
1779 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
1780 P_USER_NAME IN VARCHAR2,
1781 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1782 X_MSG_COUNT OUT NOCOPY NUMBER,
1783 X_MSG_DATA OUT NOCOPY VARCHAR2,
1784 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
1785 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
1786 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
1787 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
1788 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
1789 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
1790 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
1791 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
1792 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
1793 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
1794 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
1795 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
1796 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
1797 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
1798 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
1799 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
1800 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
1801 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE
1802 ) IS
1803
1804 l_api_version CONSTANT NUMBER := 1.0;
1805 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
1806 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1807 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1808 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
1809 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
1810 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
1811 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
1812 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
1813 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
1814 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
1815 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
1816 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
1817 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
1818 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
1819 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
1820 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
1821 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
1822 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
1823 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
1824 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
1825 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
1826 l_user_id jtf_rs_resource_extns.user_id%TYPE;
1827 -- l_location mdsys.sdo_geometry;
1828 l_object_version_num jtf_rs_resource_extns.object_version_number%TYPE;
1829 l_user_name jtf_rs_resource_extns.user_name%type;
1830 l_check_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1831 l_check_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1832
1833
1834 CURSOR c_resource_id(
1835 l_resource_id IN NUMBER)
1836 IS
1837 SELECT resource_id
1838 FROM jtf_rs_resource_extns
1839 WHERE resource_id = l_resource_id;
1840
1841
1842 CURSOR c_resource_number(
1843 l_resource_number IN VARCHAR2)
1844 IS
1845 SELECT resource_id
1846 FROM jtf_rs_resource_extns
1847 WHERE resource_number = l_resource_number;
1848
1849
1850 BEGIN
1851
1852 l_resource_id := p_resource_id;
1853 l_resource_number := p_resource_number;
1854 l_managing_emp_id := p_managing_emp_id;
1855 l_start_date_active := p_start_date_active;
1856 l_end_date_active := p_end_date_active;
1857 l_time_zone := p_time_zone;
1858 l_cost_per_hr := p_cost_per_hr;
1859 l_primary_language := p_primary_language;
1860 l_secondary_language := p_secondary_language;
1861 l_support_site_id := p_support_site_id;
1862 l_ies_agent_login := p_ies_agent_login;
1863 l_server_group_id := p_server_group_id;
1864 l_assigned_to_group_id := p_assigned_to_group_id;
1865 l_cost_center := p_cost_center;
1866 l_charge_to_cost_center := p_charge_to_cost_center;
1867 l_comp_currency_code := p_comp_currency_code;
1868 l_commissionable_flag := p_commissionable_flag;
1869 l_hold_reason_code := p_hold_reason_code;
1870 l_hold_payment := p_hold_payment;
1871 l_comp_service_team_id := p_comp_service_team_id;
1872 l_user_id := p_user_id;
1873 -- l_location := p_location;
1874 l_object_version_num := p_object_version_num;
1875 l_user_name := p_user_name;
1876
1877
1878 SAVEPOINT update_resource_pub;
1879
1880 x_return_status := fnd_api.g_ret_sts_success;
1881
1882 -- dbms_output.put_line(' Started Update Resource Pub ');
1883
1884
1885 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1886
1887 RAISE fnd_api.g_exc_unexpected_error;
1888
1889 END IF;
1890
1891
1892 IF fnd_api.to_boolean(p_init_msg_list) THEN
1893
1894 fnd_msg_pub.initialize;
1895
1896 END IF;
1897
1898
1899 /* Validate the Resource. */
1900
1901 IF l_resource_id IS NULL AND l_resource_number is NULL THEN
1902
1903 -- dbms_output.put_line('Resource Id and Resource Number are null');
1904
1905 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
1906 fnd_msg_pub.add;
1907
1908 RAISE fnd_api.g_exc_error;
1909
1910 END IF;
1911
1912
1913 IF l_resource_id IS NOT NULL THEN
1914
1915 OPEN c_resource_id(l_resource_id);
1916
1917 FETCH c_resource_id INTO l_check_resource_id;
1918
1919 IF c_resource_id%NOTFOUND THEN
1920
1921 -- dbms_output.put_line('Invalid or Inactive Resource');
1922
1923 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
1924 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
1925 fnd_msg_pub.add;
1926
1927 RAISE fnd_api.g_exc_error;
1928
1929 END IF;
1930
1931 CLOSE c_resource_id;
1932
1933 ELSIF l_resource_number IS NOT NULL THEN
1934
1935 OPEN c_resource_number(l_resource_number);
1936
1937 FETCH c_resource_number INTO l_check_resource_number;
1938
1939 IF c_resource_number%NOTFOUND THEN
1940
1941 -- dbms_output.put_line('Invalid or Inactive Resource');
1942
1943 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_NUMBER');
1944 fnd_message.set_token('P_RESOURCE_NUMBER', l_resource_number);
1945 fnd_msg_pub.add;
1946
1947 RAISE fnd_api.g_exc_error;
1948
1949
1950 END IF;
1951
1952 CLOSE c_resource_number;
1953
1954 END IF;
1955
1956
1957
1958 /* Call the private procedure with the validated parameters. */
1959
1960 jtf_rs_resource_pvt.update_resource
1961 (P_API_VERSION => 1,
1962 P_INIT_MSG_LIST => fnd_api.g_false,
1963 P_COMMIT => fnd_api.g_false,
1964 P_RESOURCE_ID => l_resource_id,
1965 P_MANAGING_EMP_ID => l_managing_emp_id,
1966 P_START_DATE_ACTIVE => l_start_date_active,
1967 P_END_DATE_ACTIVE => l_end_date_active,
1968 P_TIME_ZONE => l_time_zone,
1969 P_COST_PER_HR => l_cost_per_hr,
1970 P_PRIMARY_LANGUAGE => l_primary_language,
1971 P_SECONDARY_LANGUAGE => l_secondary_language,
1972 P_SUPPORT_SITE_ID => l_support_site_id,
1973 P_IES_AGENT_LOGIN => l_ies_agent_login,
1974 P_SERVER_GROUP_ID => l_server_group_id,
1975 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1976 P_COST_CENTER => l_cost_center,
1977 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1978 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1979 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1980 P_HOLD_REASON_CODE => l_hold_reason_code,
1981 P_HOLD_PAYMENT => l_hold_payment,
1982 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1983 P_USER_ID => l_user_id,
1984 --P_LOCATION => l_location,
1985 P_ATTRIBUTE1 => p_attribute1,
1986 P_ATTRIBUTE2 => p_attribute2,
1987 P_ATTRIBUTE3 => p_attribute3,
1988 P_ATTRIBUTE4 => p_attribute4,
1989 P_ATTRIBUTE5 => p_attribute5,
1990 P_ATTRIBUTE6 => p_attribute6,
1991 P_ATTRIBUTE7 => p_attribute7,
1992 P_ATTRIBUTE8 => p_attribute8,
1993 P_ATTRIBUTE9 => p_attribute9,
1994 P_ATTRIBUTE10 => p_attribute10,
1995 P_ATTRIBUTE11 => p_attribute11,
1996 P_ATTRIBUTE12 => p_attribute12,
1997 P_ATTRIBUTE13 => p_attribute13,
1998 P_ATTRIBUTE14 => p_attribute14,
1999 P_ATTRIBUTE15 => p_attribute15,
2000 P_ATTRIBUTE_CATEGORY => p_attribute_category,
2001 P_OBJECT_VERSION_NUM => l_object_version_num,
2002 P_RESOURCE_NAME => P_RESOURCE_NAME,
2003 P_SOURCE_NAME => P_SOURCE_NAME,
2004 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
2005 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
2006 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
2007 P_SOURCE_PHONE => P_SOURCE_PHONE,
2008 P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
2009 P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
2010 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
2011 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
2012 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
2013 P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
2014 P_SOURCE_CITY => P_SOURCE_CITY,
2015 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
2016 P_SOURCE_STATE => P_SOURCE_STATE,
2017 P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
2018 P_SOURCE_COUNTY => P_SOURCE_COUNTY,
2019 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
2020 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
2021 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
2022 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
2023 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
2024 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
2025 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
2026 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
2027 P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
2028 P_SOURCE_STATUS => P_SOURCE_STATUS,
2029 P_SOURCE_OFFICE => P_SOURCE_OFFICE,
2030 P_SOURCE_LOCATION => P_SOURCE_LOCATION,
2031 P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
2032 P_ADDRESS_ID => P_ADDRESS_ID,
2033 P_USER_NAME => P_USER_NAME,
2034 X_RETURN_STATUS => x_return_status,
2035 X_MSG_COUNT => x_msg_count,
2036 X_MSG_DATA => x_msg_data,
2037 P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
2038 P_SOURCE_PAGER => P_SOURCE_PAGER
2039 );
2040
2041
2042 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2043
2044 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2045 RAISE FND_API.G_EXC_ERROR;
2046 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2048 END IF;
2049
2050 END IF;
2051
2052
2053
2054 IF fnd_api.to_boolean(p_commit) THEN
2055
2056 COMMIT WORK;
2057
2058 END IF;
2059
2060 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2061
2062
2063
2064 EXCEPTION
2065
2066 WHEN fnd_api.g_exc_error THEN
2067 ROLLBACK TO update_resource_pub;
2068 x_return_status := fnd_api.g_ret_sts_error;
2069 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2070 p_data => x_msg_data);
2071 WHEN fnd_api.g_exc_unexpected_error THEN
2072 ROLLBACK TO update_resource_pub;
2073 x_return_status := fnd_api.g_ret_sts_unexp_error;
2074 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2075 p_data => x_msg_data);
2076 WHEN OTHERS THEN
2077 ROLLBACK TO update_resource_pub;
2078 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2079 fnd_message.set_token('P_SQLCODE',SQLCODE);
2080 fnd_message.set_token('P_SQLERRM',SQLERRM);
2081 fnd_message.set_token('P_API_NAME', l_api_name);
2082 FND_MSG_PUB.add;
2083 x_return_status := fnd_api.g_ret_sts_unexp_error;
2084 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2085 p_data => x_msg_data);
2086
2087
2088 END update_resource;
2089
2090
2091 /* Procedure to delete the resource of type TBH */
2092
2093 PROCEDURE delete_resource
2094 (P_API_VERSION IN NUMBER,
2095 P_INIT_MSG_LIST IN VARCHAR2,
2096 P_COMMIT IN VARCHAR2,
2097 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2098 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2099 X_MSG_COUNT OUT NOCOPY NUMBER,
2100 X_MSG_DATA OUT NOCOPY VARCHAR2
2101 ) IS
2102
2103 l_api_version CONSTANT NUMBER := 1.0;
2104 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
2105 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
2106
2107 CURSOR res_cur(
2108 l_resource_id IN NUMBER)
2109 IS
2110 SELECT resource_id,
2111 category
2112 FROM jtf_rs_resource_extns
2113 WHERE resource_id = l_resource_id;
2114
2115 res_rec res_cur%rowtype;
2116
2117 l_bind_id number;
2118
2119
2120 BEGIN
2121
2122 l_resource_id := p_resource_id;
2123
2124 SAVEPOINT delete_resource_pub;
2125 x_return_status := fnd_api.g_ret_sts_success;
2126 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2127 RAISE fnd_api.g_exc_unexpected_error;
2128 END IF;
2129
2130 IF fnd_api.to_boolean(p_init_msg_list) THEN
2131 fnd_msg_pub.initialize;
2132 END IF;
2133
2134
2135 /* Validate the Resource. */
2136 IF l_resource_id IS NULL THEN
2137 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
2138 fnd_msg_pub.add;
2139 RAISE fnd_api.g_exc_error;
2140
2141 END IF;
2142
2143
2144 IF l_resource_id IS NOT NULL THEN
2145 OPEN res_cur(l_resource_id);
2146 FETCH res_cur INTO res_rec;
2147 IF res_cur%NOTFOUND THEN
2148 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
2149 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
2150 fnd_msg_pub.add;
2151 RAISE fnd_api.g_exc_error;
2152
2153 END IF;
2154 IF res_rec.category <> 'TBH'
2155 THEN
2156 fnd_message.set_name('JTF', 'JTF_RS_NOT_TBH');
2157 fnd_msg_pub.add;
2158 RAISE fnd_api.g_exc_error;
2159 END IF;
2160 CLOSE res_cur;
2161
2162 END IF;
2163
2164
2165
2166 /* Call the private procedure with the validated parameters. */
2167 --call private api for delete
2168 JTF_RS_RESOURCE_PVT.DELETE_RESOURCE(
2169 P_API_VERSION => 1.0,
2170 P_INIT_MSG_LIST => fnd_api.g_false,
2171 P_COMMIT => fnd_api.g_false,
2172 P_RESOURCE_ID => l_resource_id,
2173 X_RETURN_STATUS => x_return_status,
2174 X_MSG_COUNT => x_msg_count,
2175 X_MSG_DATA => x_msg_data );
2176
2177
2178 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2179 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2180 RAISE FND_API.G_EXC_ERROR;
2181 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2183 END IF;
2184 END IF;
2185
2186 IF fnd_api.to_boolean(p_commit) THEN
2187 COMMIT WORK;
2188 END IF;
2189 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2190
2191 EXCEPTION
2192 WHEN fnd_api.g_exc_error THEN
2193 ROLLBACK TO delete_resource_pub;
2194 x_return_status := fnd_api.g_ret_sts_error;
2195 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2196 p_data => x_msg_data);
2197 WHEN fnd_api.g_exc_unexpected_error THEN
2198 ROLLBACK TO delete_resource_pub;
2199 x_return_status := fnd_api.g_ret_sts_unexp_error;
2200 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2201 p_data => x_msg_data);
2202 WHEN OTHERS THEN
2203 ROLLBACK TO delete_resource_pub;
2204 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2205 fnd_message.set_token('P_SQLCODE',SQLCODE);
2206 fnd_message.set_token('P_SQLERRM',SQLERRM);
2207 fnd_message.set_token('P_API_NAME', l_api_name);
2208 FND_MSG_PUB.add;
2209 x_return_status := fnd_api.g_ret_sts_unexp_error;
2210 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2211 p_data => x_msg_data);
2212
2213 END delete_resource;
2214
2215
2216 /* Procedure to create the resource with the resource synchronizing parameters. */
2217
2218 PROCEDURE create_resource
2219 (P_API_VERSION IN NUMBER,
2220 P_INIT_MSG_LIST IN VARCHAR2,
2221 P_COMMIT IN VARCHAR2,
2222 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
2223 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE DEFAULT NULL,
2224 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE DEFAULT NULL,
2225 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE DEFAULT NULL,
2226 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE DEFAULT NULL,
2227 P_MANAGING_EMP_NUM IN PER_EMPLOYEES_CURRENT_X.EMPLOYEE_NUM%TYPE DEFAULT NULL,
2228 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
2229 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
2230 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE DEFAULT NULL,
2231 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE DEFAULT NULL,
2232 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE DEFAULT NULL,
2233 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE DEFAULT NULL,
2234 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE DEFAULT NULL,
2235 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE DEFAULT NULL,
2236 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE DEFAULT NULL,
2237 P_INTERACTION_CENTER_NAME IN VARCHAR2 DEFAULT NULL,
2238 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE DEFAULT NULL,
2239 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE DEFAULT NULL,
2240 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE DEFAULT NULL,
2241 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE DEFAULT NULL,
2242 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
2243 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE DEFAULT NULL,
2244 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
2245 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE DEFAULT NULL,
2246 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE DEFAULT NULL,
2247 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE DEFAULT NULL,
2248 --P_LOCATION IN MDSYS.SDO_GEOMETRY DEFAULT NULL,
2249 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2250 X_MSG_COUNT OUT NOCOPY NUMBER,
2251 X_MSG_DATA OUT NOCOPY VARCHAR2,
2252 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2253 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
2254 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE DEFAULT NULL,
2255 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
2256 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE DEFAULT NULL,
2257 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE DEFAULT NULL,
2258 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE DEFAULT NULL,
2259 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE DEFAULT NULL,
2260 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE DEFAULT NULL,
2261 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE DEFAULT NULL,
2262 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE DEFAULT NULL,
2263 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE DEFAULT NULL,
2264 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE DEFAULT NULL,
2265 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE DEFAULT NULL,
2266 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE DEFAULT NULL,
2267 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE DEFAULT NULL,
2268 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE DEFAULT NULL,
2269 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE DEFAULT NULL,
2270 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE DEFAULT NULL,
2271 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE DEFAULT NULL,
2272 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%type DEFAULT NULL,
2273 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%type DEFAULT NULL,
2274 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%type DEFAULT NULL,
2275 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%type DEFAULT NULL,
2276 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE DEFAULT NULL,
2277 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE DEFAULT NULL,
2278 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE DEFAULT NULL,
2279 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE DEFAULT NULL,
2280 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE DEFAULT NULL,
2281 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE DEFAULT NULL,
2282 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE DEFAULT NULL,
2283 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE DEFAULT NULL,
2284 P_USER_NAME IN VARCHAR2,
2285 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE DEFAULT NULL,
2286 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE DEFAULT NULL,
2287 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE DEFAULT NULL,
2288 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE DEFAULT NULL,
2289 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE DEFAULT NULL,
2290 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE DEFAULT NULL,
2291 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE DEFAULT NULL,
2292 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE DEFAULT NULL,
2293 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE DEFAULT NULL,
2294 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE DEFAULT NULL,
2295 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE DEFAULT NULL,
2296 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE DEFAULT NULL,
2297 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE DEFAULT NULL,
2298 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE DEFAULT NULL,
2299 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE DEFAULT NULL,
2300 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE DEFAULT NULL,
2301 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE DEFAULT NULL,
2302 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL
2303 )
2304 IS
2305
2306 l_api_version CONSTANT NUMBER := 1.0;
2307 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
2308
2309 --duplicated from the create_resource api above
2310 l_category jtf_rs_resource_extns.category%TYPE;
2311 l_source_id jtf_rs_resource_extns.source_id%TYPE;
2312 l_address_id jtf_rs_resource_extns.address_id%TYPE;
2313 l_contact_id jtf_rs_resource_extns.contact_id%TYPE;
2314 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
2315 l_managing_emp_num per_employees_current_x.employee_num%TYPE;
2316 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
2317 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
2318 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
2319 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
2320 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
2321 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
2322 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
2323 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
2324 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
2325 l_interaction_center_name VARCHAR2(256);
2326 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
2327 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
2328 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
2329 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
2330 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
2331 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
2332 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
2333 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
2334 l_user_id jtf_rs_resource_extns.user_id%TYPE;
2335 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
2336 --l_location MDSYS.SDO_GEOMETRY := p_location;
2337
2338 --added for NOCOPY
2339 l_managing_emp_id_out jtf_rs_resource_extns.managing_employee_id%TYPE ;
2340 l_server_group_id_out jtf_rs_resource_extns.server_group_id%TYPE ;
2341 l_comp_service_team_id_out jtf_rs_resource_extns.comp_service_team_id%TYPE;
2342
2343
2344 l_user_name jtf_rs_resource_extns.user_name%type;
2345 l_check_flag VARCHAR2(1);
2346 l_found BOOLEAN;
2347
2348
2349 /* Changed from view to direct table query stripping out unnecessary table joins
2350 for SQL Rep perf bug 4956627. Query logic taken from view JTF_RS_PARTNERS_VL.
2351 Nishant Singhai (13-Mar-2006)
2352 */
2353 /*
2354 CURSOR c_validate_partner(
2355 l_party_id IN NUMBER)
2356 IS
2357 SELECT 'Y'
2358 FROM jtf_rs_partners_vl
2359 WHERE party_id = l_party_id;
2360 */
2361 CURSOR c_validate_partner(l_party_id IN NUMBER)
2362 IS
2363 SELECT 'Y'
2364 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
2365 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
2366 WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' AND PARTY.PARTY_ID = REL.SUBJECT_ID)
2367 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
2368 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
2369 'CUSTOMER_INDIRECTLY_MANAGED_BY')
2370 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2371 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2372 AND REL.DIRECTIONAL_FLAG = 'F'
2373 AND REL.STATUS = 'A'
2374 AND PARTY.STATUS = 'A'
2375 AND PARTY2.STATUS = 'A'
2376 AND PARTY3.STATUS = 'A'
2377 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
2378 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
2379 AND REL.OBJECT_ID = PARTY3.PARTY_ID
2380 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
2381 AND party.party_id = l_party_id
2382 UNION ALL
2383 SELECT 'Y'
2384 FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
2385 HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
2386 WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND PARTY.PARTY_ID = REL.PARTY_ID )
2387 AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
2388 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
2389 'CUSTOMER_INDIRECTLY_MANAGED_BY')
2390 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2391 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2392 AND REL.DIRECTIONAL_FLAG = 'F'
2393 AND REL.STATUS = 'A'
2394 AND PARTY.STATUS = 'A'
2395 AND PARTY2.STATUS = 'A'
2396 AND PARTY3.STATUS = 'A'
2397 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
2398 AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
2399 AND REL.OBJECT_ID = PARTY3.PARTY_ID
2400 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
2401 AND party.party_id = l_party_id
2402 ;
2403
2404 CURSOR c_validate_partner_address (
2405 l_party_id IN NUMBER,
2406 l_party_site_id IN NUMBER)
2407 IS
2408 SELECT 'Y'
2409 FROM hz_party_sites
2410 WHERE party_id = l_party_id
2411 AND party_site_id = l_party_site_id;
2412
2413 CURSOR c_validate_partner_contact(
2414 l_party_id IN NUMBER,
2415 l_party_site_id IN NUMBER,
2416 l_contact_id IN NUMBER)
2417 IS
2418 SELECT 'Y'
2419 FROM jtf_rs_party_contacts_vl
2420 WHERE party_id = l_party_id
2421 AND nvl (party_site_id,-99) = nvl (l_party_site_id,-99)
2422 AND contact_id = l_contact_id;
2423
2424 /* -- Direct query from tables. But does not improve performance or shared memory
2425 -- significantly. So not using it as it will lead to dual maintainence (view + this logic)
2426 -- Test performed for SQL Rep Bug 4956627
2427
2428 CURSOR c_validate_partner_contact(
2429 l_party_id IN NUMBER,
2430 l_party_site_id IN NUMBER,
2431 l_contact_id IN NUMBER)
2432 IS
2433 SELECT 'Y'
2434 -- SELECT PARTY.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID , ORG_CONT.ORG_CONTACT_ID CONTACT_ID ,
2435 -- ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER , PARTY.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2436 FROM HZ_PARTIES PARTY , HZ_RELATIONSHIPS PARTY_REL , HZ_ORG_CONTACTS ORG_CONT ,
2437 HZ_ORG_CONTACT_ROLES CONT_ROLE
2438 WHERE PARTY.STATUS = 'A'
2439 AND PARTY.PARTY_TYPE = 'PERSON'
2440 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2441 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2442 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2443 AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
2444 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2445 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2446 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2447 AND PARTY_REL.STATUS = 'A'
2448 AND party.party_id = l_party_id
2449 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2450 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2451 UNION ALL
2452 SELECT 'Y'
2453 -- SELECT PARTY5.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
2454 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
2455 -- PARTY5.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2456 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_PARTIES PARTY5 , HZ_RELATIONSHIPS PARTY_REL ,
2457 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
2458 WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
2459 AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
2460 AND PARTY5.STATUS = 'A'
2461 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
2462 AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
2463 AND PARTY3.PARTY_TYPE = 'PERSON'
2464 AND PARTY3.STATUS = 'A'
2465 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
2466 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
2467 AND PARTY4.STATUS = 'A'
2468 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2469 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2470 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2471 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2472 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2473 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2474 AND PARTY_REL.STATUS = 'A'
2475 AND party5.party_id = l_party_id
2476 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2477 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2478 UNION ALL
2479 SELECT 'Y'
2480 -- SELECT PARTY4.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
2481 -- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
2482 -- PARTY3.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
2483 FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_RELATIONSHIPS PARTY_REL ,
2484 HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
2485 WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
2486 AND PARTY3.PARTY_TYPE = 'PERSON'
2487 AND PARTY3.STATUS = 'A'
2488 AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
2489 AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
2490 AND PARTY4.STATUS = 'A'
2491 AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
2492 AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
2493 AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
2494 AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
2495 AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
2496 AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2497 AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
2498 AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
2499 AND PARTY_REL.STATUS = 'A'
2500 AND party4.party_id = l_party_id
2501 AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
2502 AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
2503 ;
2504 */
2505
2506 CURSOR c_validate_party_address(
2507 l_party_site_id IN NUMBER )
2508 IS
2509 SELECT 'Y'
2510 FROM hz_party_sites
2511 WHERE party_site_id = l_party_site_id;
2512
2513
2514 CURSOR c_validate_party_contact(
2515 l_party_id IN NUMBER,
2516 l_party_site_id IN NUMBER,
2517 l_contact_id IN NUMBER)
2518 IS
2519 SELECT 'Y'
2520 /* FROM jtf_rs_party_contacts_vl
2521 WHERE party_id = l_party_id
2522 AND nvl(party_site_id, 0) = nvl(l_party_site_id, 0)
2523 AND contact_id = l_contact_id; */
2524 -- changed the query the validate party contact id according to bug 2954064 as provided by the PRM team , sudarsana 2nd july 2004
2525 FROM hz_relationships hzr,
2526 hz_org_contacts hzoc
2527 WHERE hzr.party_id = l_party_id
2528 AND hzoc.org_contact_id = l_contact_id
2529 AND hzr.directional_flag = 'F'
2530 AND hzr.relationship_code = 'EMPLOYEE_OF'
2531 AND hzr.subject_table_name ='HZ_PARTIES'
2532 AND hzr.object_table_name ='HZ_PARTIES'
2533 AND hzr.start_date <= SYSDATE
2534 AND (hzr.end_date is null or hzr.end_date > SYSDATE)
2535 AND hzr.status = 'A'
2536 AND hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id;
2537
2538 /* SQL Rep perf improvement bug 4956627 Nishant Singhai (14-Mar-2006) fixed by
2539 modifying query logic given in bug # 4052112
2540 OIC expanded the definition of compensation analyst to include any active user in the
2541 system regardless of their assignment to a CN responsibility.
2542 */
2543 CURSOR c_assigned_to_group_id(
2544 l_assigned_to_group_id IN NUMBER)
2545 IS
2546 SELECT u.user_id
2547 FROM fnd_user u,
2548 jtf_rs_resource_extns r
2549 WHERE u.user_id = r.user_id
2550 AND u.user_id = l_assigned_to_group_id;
2551
2552 CURSOR c_validate_user_id(
2553 l_user_id IN NUMBER)
2554 IS
2555 SELECT 'Y'
2556 FROM jtf_rs_resource_extns
2557 WHERE user_id = l_user_id;
2558
2559 -- Enh 3947611 2-dec-2004 added cursor to check emp existence
2560 CURSOR c_emp_exist(p_person_id IN NUMBER)
2561 IS
2562 SELECT 'x' value,full_name
2563 FROM per_all_people_f
2564 WHERE person_id = p_person_id;
2565
2566 r_emp_exist c_emp_exist%rowtype;
2567
2568
2569
2570 BEGIN
2571
2572 l_category := upper(p_category);
2573 l_source_id := p_source_id;
2574 l_address_id := p_address_id;
2575 l_contact_id := p_contact_id;
2576 l_managing_emp_id := p_managing_emp_id;
2577 l_managing_emp_num := p_managing_emp_num;
2578 l_start_date_active := p_start_date_active;
2579 l_end_date_active := p_end_date_active;
2580 l_time_zone := p_time_zone;
2581 l_cost_per_hr := p_cost_per_hr;
2582 l_primary_language := p_primary_language;
2583 l_secondary_language := p_secondary_language;
2584 l_support_site_id := p_support_site_id;
2585 l_ies_agent_login := p_ies_agent_login;
2586 l_server_group_id := p_server_group_id;
2587 l_interaction_center_name := p_interaction_center_name;
2588 l_assigned_to_group_id := p_assigned_to_group_id;
2589 l_cost_center := p_cost_center;
2590 l_charge_to_cost_center := p_charge_to_cost_center;
2591 l_comp_currency_code := p_comp_currency_code;
2592 l_commissionable_flag := p_commissionable_flag;
2593 l_hold_reason_code := p_hold_reason_code;
2594 l_hold_payment := p_hold_payment;
2595 l_comp_service_team_id := p_comp_service_team_id;
2596 l_user_id := p_user_id;
2597 l_transaction_number := p_transaction_number;
2598
2599 --Standard Start of API SAVEPOINT
2600 SAVEPOINT CREATE_RESOURCE_SP;
2601
2602 x_return_status := fnd_api.g_ret_sts_success;
2603
2604 --Standard Call to check API compatibility
2605 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2606 THEN
2607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2608 END IF;
2609
2610 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2611 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2612 THEN
2613 FND_MSG_PUB.Initialize;
2614 END IF;
2615
2616 /* Validate the Resource Category */
2617
2618 jtf_resource_utl.validate_resource_category(
2619 p_category => l_category,
2620 x_return_status => x_return_status
2621 );
2622
2623
2624 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2625
2626 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2627 RAISE FND_API.G_EXC_ERROR;
2628 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2630 END IF;
2631
2632 END IF;
2633
2634
2635 /* Validate Source ID */
2636
2637 jtf_resource_utl.validate_source_id (
2638 p_category => l_category,
2639 p_source_id => l_source_id,
2640 p_address_id => l_address_id,
2641 x_return_status => x_return_status
2642 );
2643
2644 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2645 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2646 RAISE FND_API.G_EXC_ERROR;
2647 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2649 END IF;
2650
2651 END IF;
2652
2653
2654 /* Validations for category as OTHER and TBH */
2655
2656 IF l_category IN ('OTHER', 'TBH') THEN
2657
2658 /* Validate that the source_id, address_id, contact_id and managing_employee_id
2659 are all NULL */
2660
2661 IF (l_source_id IS NOT NULL OR l_address_id IS NOT NULL
2662 OR l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL
2663 OR l_managing_emp_num IS NOT NULL) THEN
2664
2665 -- dbms_output.put_line('For OTHER category, source_id, address_id, contact_id and managing_emp_id should be all null');
2666
2667 fnd_message.set_name('JTF', 'JTF_RS_OTHER_IDS_NOT_NULL');
2668 fnd_msg_pub.add;
2669
2670 RAISE fnd_api.g_exc_error;
2671
2672 END IF;
2673
2674 END IF;
2675
2676 /* Validations for category as PARTNER */
2677
2678 IF l_category = 'PARTNER' THEN
2679
2680 /* Validate the source_id */
2681
2682 IF (l_source_id IS NULL) THEN
2683 -- dbms_output.put_line('For PARTNER category, source_id should not be null');
2684 fnd_message.set_name('JTF', 'JTF_RS_PARTNER_IDS_NULL');
2685 fnd_msg_pub.add;
2686 RAISE fnd_api.g_exc_error;
2687
2688 ELSE
2689 OPEN c_validate_partner(l_source_id);
2690 FETCH c_validate_partner INTO l_check_flag;
2691 IF c_validate_partner%NOTFOUND THEN
2692 -- dbms_output.put_line('Partner does not exist for the passed source_id');
2693 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTNER_IDS');
2694 fnd_msg_pub.add;
2695 RAISE fnd_api.g_exc_error;
2696
2697 END IF;
2698 CLOSE c_validate_partner;
2699 END IF;
2700
2701 /* Validate the address_id if specified */
2702
2703 IF l_address_id IS NOT NULL THEN
2704 OPEN c_validate_partner_address(l_source_id, l_address_id);
2705 FETCH c_validate_partner_address INTO l_check_flag;
2706 IF c_validate_partner_address%NOTFOUND THEN
2707 -- dbms_output.put_line('Invalid Partner Address Id');
2708 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_ADDRESS_ID');
2709 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
2710 fnd_msg_pub.add;
2711 RAISE fnd_api.g_exc_error;
2712
2713 END IF;
2714 CLOSE c_validate_partner_address;
2715 END IF;
2716
2717
2718 /* Validate the contact_id if specified */
2719
2720 IF l_contact_id IS NOT NULL THEN
2721 OPEN c_validate_partner_contact(l_source_id, l_address_id, l_contact_id);
2722 FETCH c_validate_partner_contact INTO l_check_flag;
2723 IF c_validate_partner_contact%NOTFOUND THEN
2724 -- dbms_output.put_line('Invalid Partner Contact Id');
2725 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTNER_CONTACT_ID');
2726 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
2727 fnd_msg_pub.add;
2728 RAISE fnd_api.g_exc_error;
2729
2730 END IF;
2731 CLOSE c_validate_partner_contact;
2732 END IF;
2733
2734 END IF;
2735
2736 /* For all other Categories, validate the source_id from jtf_objects */
2737 /* Enh 3947611 2-dec-2004 : added EMPLOYEE to the exception also. Import future dated employees
2738 this had to be an exception else the seed data for object EMPLOYEE if jtf_objects had to be changed. This may have
2739 some backward compatibility issues for consumers who use JTF_OBJECTS to validate OR list EMPLOYEE
2740 */
2741
2742 IF l_category NOT IN ('OTHER' , 'PARTNER' , 'TBH', 'EMPLOYEE') THEN
2743 IF l_source_id IS NULL THEN
2744 -- dbms_output.put_line('Source Id should not be Null');
2745 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
2746 fnd_msg_pub.add;
2747 RAISE fnd_api.g_exc_error;
2748
2749 END IF;
2750
2751 jtf_resource_utl.check_object_existence_migr(
2752
2753 P_OBJECT_CODE => l_category,
2754 P_SELECT_ID => l_source_id,
2755 P_OBJECT_USER_CODE => 'RESOURCE_CATEGORIES',
2756 P_RS_ID_PUB_FLAG => G_RS_ID_PUB_FLAG,
2757 X_FOUND => l_found,
2758 X_RETURN_STATUS => x_return_status
2759 );
2760
2761 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2762
2763 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2764 RAISE FND_API.G_EXC_ERROR;
2765 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2767 END IF;
2768
2769 END IF;
2770
2771
2772 IF l_found = FALSE THEN
2773
2774 -- dbms_output.put_line('Invalid Source Id');
2775
2776 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
2777 fnd_message.set_token('P_SOURCE_ID', l_source_id);
2778 fnd_msg_pub.add;
2779
2780 RAISE fnd_api.g_exc_error;
2781
2782
2783 END IF;
2784
2785 END IF;
2786
2787
2788 /* Enh 3947611 2-dec-2004:EMPLOYEE VALIDATION has been removed from the above code. so adding validation
2789 for EMPLOYEE
2790 */
2791
2792 if l_category = 'EMPLOYEE' THEN
2793 -- First check is null check for source id
2794 IF l_source_id IS NULL THEN
2795 fnd_message.set_name('JTF', 'JTF_RS_SOURCE_ID_NULL');
2796 fnd_msg_pub.add;
2797 RAISE fnd_api.g_exc_error;
2798 END IF;
2799
2800 open c_emp_exist(l_source_id);
2801 fetch c_emp_exist into r_emp_exist;
2802 close c_emp_exist;
2803
2804 if(nvl(r_emp_exist.value , 'y') <> 'x')
2805 then
2806 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SOURCE_ID');
2807 fnd_message.set_token('P_SOURCE_ID', l_source_id);
2808 fnd_msg_pub.add;
2809 RAISE fnd_api.g_exc_error;
2810 end if;
2811
2812 END IF; -- end of check l_category = 'EMPLOYEE'
2813
2814 /* Validations for category as PARTY */
2815
2816 IF l_category = 'PARTY' THEN
2817
2818 /* Validate the address_id if specified */
2819
2820 IF l_address_id IS NOT NULL THEN
2821
2822 OPEN c_validate_party_address(l_address_id);
2823
2824 FETCH c_validate_party_address INTO l_check_flag;
2825
2826
2827 IF c_validate_party_address%NOTFOUND THEN
2828
2829 -- dbms_output.put_line('Invalid Party Address');
2830
2831 fnd_message.set_name('JTF', 'JTF_RS_INVALID_PARTY_ADDRESS');
2832 fnd_message.set_token('P_ADDRESS_ID', l_address_id);
2833 fnd_msg_pub.add;
2834
2835 RAISE fnd_api.g_exc_error;
2836
2837
2838 END IF;
2839
2840 /* Close the cursor */
2841
2842 CLOSE c_validate_party_address;
2843
2844
2845 END IF;
2846
2847
2848 /* Validate the contact_id if specified */
2849
2850 IF l_contact_id IS NOT NULL THEN
2851
2852 OPEN c_validate_party_contact(l_source_id, l_address_id, l_contact_id);
2853
2854 FETCH c_validate_party_contact INTO l_check_flag;
2855
2856
2857 IF c_validate_party_contact%NOTFOUND THEN
2858
2859 -- dbms_output.put_line('Invalid Party Contact Id');
2860
2861 fnd_message.set_name('JTF', 'JTF_RS_ERR_PARTY_CONTACT_ID');
2862 fnd_message.set_token('P_CONTACT_ID', l_contact_id);
2863 fnd_msg_pub.add;
2864
2865 RAISE fnd_api.g_exc_error;
2866
2867 END IF;
2868
2869
2870 /* Close the cursor */
2871
2872 CLOSE c_validate_party_contact;
2873
2874 END IF;
2875
2876 END IF;
2877
2878
2879
2880 /* Validations for category as SUPPLIER_CONTACT */
2881
2882 IF l_category = 'SUPPLIER_CONTACT' THEN
2883
2884 /* Validate that the address_id and contact_id are NULL */
2885
2886 -- address_id check (NOT NULL) being removed, to store the address_id of supplier contact
2887 -- Fix for bug # 3812930
2888 IF (l_contact_id IS NOT NULL) THEN
2889
2890 -- dbms_output.put_line('For SUPPLIER_CONTACT category, address_id and contact_id should be null');
2891
2892 fnd_message.set_name('JTF', 'JTF_RS_SC_IDS_NOT_NULL');
2893 fnd_msg_pub.add;
2894
2895 RAISE fnd_api.g_exc_error;
2896
2897 END IF;
2898
2899 END IF;
2900
2901
2902 /* Validations for category as EMPLOYEE */
2903
2904 IF (l_category = 'EMPLOYEE') THEN
2905
2906 /* Validate that the address_id, contact_id and managing_emp_id are NULL */
2907
2908 --address_id check (null) being removed, to store the address_id of employee 03/26/01
2909
2910 IF (l_contact_id IS NOT NULL OR l_managing_emp_id IS NOT NULL OR l_managing_emp_num IS NOT NULL) THEN
2911
2912 -- dbms_output.put_line('For EMPLOYEE category, contact_id should be null');
2913
2914 fnd_message.set_name('JTF', 'JTF_RS_EMP_IDS_NOT_NULL');
2915 fnd_msg_pub.add;
2916
2917 RAISE fnd_api.g_exc_error;
2918
2919
2920 END IF;
2921
2922 END IF;
2923
2924
2925
2926 /* Validate the Managing Employee Id if specified */
2927
2928 jtf_resource_utl.validate_employee_resource(
2929 p_emp_resource_id => l_managing_emp_id,
2930 p_emp_resource_number => l_managing_emp_num,
2931 x_return_status => x_return_status,
2932 x_emp_resource_id => l_managing_emp_id_out
2933 );
2934
2935 -- added for NOCOPY
2936 l_managing_emp_id := l_managing_emp_id_out;
2937
2938 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2939
2940 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2941 RAISE FND_API.G_EXC_ERROR;
2942 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2944 END IF;
2945
2946 END IF;
2947
2948
2949
2950 /* Validate that the Start Date Active is specified */
2951
2952 IF l_start_date_active IS NULL THEN
2953
2954 -- dbms_output.put_line('Start Date Active cannot be null');
2955
2956 fnd_message.set_name('JTF', 'JTF_RS_START_DATE_NULL');
2957 fnd_msg_pub.add;
2958
2959 RAISE fnd_api.g_exc_error;
2960
2961
2962 END IF;
2963
2964
2965
2966 /* Validate the Time Zone */
2967
2968 IF l_time_zone IS NOT NULL THEN
2969
2970 jtf_resource_utl.validate_time_zone(
2971 p_time_zone_id => l_time_zone,
2972 x_return_status => x_return_status
2973 );
2974
2975 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2976
2977 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2978 RAISE FND_API.G_EXC_ERROR;
2979 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2981 END IF;
2982
2983 END IF;
2984
2985 END IF;
2986
2987
2988
2989 /* Validate the Primary Language */
2990
2991 IF l_primary_language IS NOT NULL THEN
2992
2993 jtf_resource_utl.validate_nls_language(
2994 p_nls_language => l_primary_language,
2995 x_return_status => x_return_status
2996 );
2997
2998 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2999
3000 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3001 RAISE FND_API.G_EXC_ERROR;
3002 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3004 END IF;
3005
3006 END IF;
3007
3008 END IF;
3009
3010
3011
3012 /* Validate the Secondary Language */
3013
3014 IF l_secondary_language IS NOT NULL THEN
3015
3016 jtf_resource_utl.validate_nls_language(
3017 p_nls_language => l_secondary_language,
3018 x_return_status => x_return_status
3019 );
3020
3021 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3022
3023 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3024 RAISE FND_API.G_EXC_ERROR;
3025 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3027 END IF;
3028
3029 END IF;
3030
3031 END IF;
3032
3033
3034
3035 /* Validate the Support Site */
3036
3037 IF l_support_site_id IS NOT NULL THEN
3038
3039 jtf_resource_utl.validate_support_site_id(
3040 p_support_site_id => l_support_site_id,
3041 x_return_status => x_return_status
3042 );
3043
3044 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3045
3046 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3047 RAISE FND_API.G_EXC_ERROR;
3048 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3050 END IF;
3051
3052 END IF;
3053
3054 END IF;
3055
3056
3057
3058 /* Validate the Server Group. */
3059
3060 jtf_resource_utl.validate_server_group(
3061 p_server_group_id => l_server_group_id,
3062 p_server_group_name => l_interaction_center_name,
3063 x_return_status => x_return_status,
3064 x_server_group_id => l_server_group_id_out
3065 );
3066 -- added for NOCOPY
3067 l_server_group_id := l_server_group_id_out;
3068
3069 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3070
3071 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3072 RAISE FND_API.G_EXC_ERROR;
3073 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3075 END IF;
3076
3077 END IF;
3078
3079
3080
3081 /* Validate the assigned_to_group_id if specified */
3082
3083 IF l_assigned_to_group_id IS NOT NULL THEN
3084
3085 OPEN c_assigned_to_group_id(l_assigned_to_group_id);
3086
3087 FETCH c_assigned_to_group_id INTO l_assigned_to_group_id;
3088
3089
3090 IF c_assigned_to_group_id%NOTFOUND THEN
3091
3092 -- dbms_output.put_line('Invalid Assigned To Group Id');
3093
3094 fnd_message.set_name('JTF', 'JTF_RS_ERR_ASSIGN_TO_GRP_ID');
3095 fnd_message.set_token('P_ASSIGNED_TO_GROUP_ID', l_assigned_to_group_id);
3096 fnd_msg_pub.add;
3097
3098 RAISE fnd_api.g_exc_error;
3099
3100
3101 END IF;
3102
3103
3104 /* Close the cursor */
3105
3106 CLOSE c_assigned_to_group_id;
3107
3108 END IF;
3109
3110
3111
3112 /* Validate the Comp Currency Code */
3113
3114 IF l_comp_currency_code IS NOT NULL THEN
3115
3116 jtf_resource_utl.validate_currency_code(
3117 p_currency_code => l_comp_currency_code,
3118 x_return_status => x_return_status
3119 );
3120
3121 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3122
3123 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3124 RAISE FND_API.G_EXC_ERROR;
3125 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3127 END IF;
3128
3129 END IF;
3130
3131 END IF;
3132
3133
3134 /* Validate the value of the commisionable flag */
3135
3136 IF l_commissionable_flag <> 'Y' AND l_commissionable_flag <> 'N' THEN
3137
3138 -- dbms_output.put_line('Commissionable Flag should either be ''Y'' or ''N'' ');
3139
3140 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3141 fnd_msg_pub.add;
3142
3143 RAISE fnd_api.g_exc_error;
3144
3145
3146 END IF;
3147
3148
3149 /* Validate the value of the Hold Payment flag */
3150
3151 IF l_hold_payment <> 'Y' AND l_hold_payment <> 'N' THEN
3152
3153 -- dbms_output.put_line('Hold Payment should either be ''Y'' or ''N'' ');
3154
3155 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3156 fnd_msg_pub.add;
3157
3158 RAISE fnd_api.g_exc_error;
3159
3160
3161 END IF;
3162
3163
3164
3165 /* Validate the Hold Reason Code */
3166
3167 IF l_hold_reason_code IS NOT NULL THEN
3168
3169 jtf_resource_utl.validate_hold_reason_code(
3170 p_hold_reason_code => l_hold_reason_code,
3171 x_return_status => x_return_status
3172 );
3173
3174 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3175
3176 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3177 RAISE FND_API.G_EXC_ERROR;
3178 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3180 END IF;
3181
3182 END IF;
3183
3184 END IF;
3185
3186
3187 /* Validate that the user_id should only be specified in case of
3188 'EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT' categories */
3189
3190 IF l_category NOT IN ('EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT') THEN
3191
3192 IF l_user_id IS NOT NULL THEN
3193
3194 fnd_message.set_name('JTF', 'JTF_RS_USERID_ERROR');
3195 fnd_msg_pub.add;
3196
3197 RAISE fnd_api.g_exc_error;
3198
3199
3200 END IF;
3201
3202 ELSE
3203
3204 /* Validate the User Id if specified */
3205
3206 IF l_user_id IS NOT NULL THEN
3207
3208 jtf_resource_utl.validate_user_id(
3209 p_user_id => l_user_id,
3210 p_category => l_category,
3211 p_source_id => l_source_id,
3212 x_return_status => x_return_status
3213 );
3214
3215 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3216
3217 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3218 RAISE FND_API.G_EXC_ERROR;
3219 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3221 END IF;
3222 else
3223
3224 OPEN c_validate_user_id(l_user_id);
3225
3226 FETCH c_validate_user_id INTO l_check_flag;
3227
3228
3229 IF c_validate_user_id%FOUND THEN
3230
3231 -- dbms_output.put_line('duplicate user Id');
3232
3233 fnd_message.set_name('JTF', 'JTF_RS_ERR_DUPLICATE_USER_ID');
3234 fnd_message.set_token('P_USER_ID', l_user_id);
3235 fnd_msg_pub.add;
3236
3237 RAISE fnd_api.g_exc_error;
3238
3239
3240 END IF;
3241
3242
3243 /* Close the cursor */
3244
3245 CLOSE c_validate_user_id;
3246
3247
3248
3249 END IF;
3250
3251 END IF;
3252
3253 END IF;
3254
3255
3256 /* Validate the Comp Service Team Id if specified */
3257
3258 IF l_comp_service_team_id IS NOT NULL THEN
3259
3260 jtf_resource_utl.validate_resource_team(
3261 p_team_id => l_comp_service_team_id,
3262 p_team_number => null,
3263 x_return_status => x_return_status,
3264 x_team_id => l_comp_service_team_id_out
3265 );
3266 -- added for NOCOPY
3267 l_comp_service_team_id := l_comp_service_team_id_out;
3268
3269 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3270
3271 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3272 RAISE FND_API.G_EXC_ERROR;
3273 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3275 END IF;
3276
3277 END IF;
3278
3279 END IF;
3280
3281
3282
3283 /* Check the Global Variable for Resource ID, and call the appropriate Private API */
3284
3285 IF G_RS_ID_PUB_FLAG = 'Y' THEN
3286
3287 /* Call the private procedure with the validated parameters. */
3288 jtf_rs_resource_pvt.create_resource (
3289 P_API_VERSION => 1,
3290 P_INIT_MSG_LIST => fnd_api.g_false,
3291 P_COMMIT => fnd_api.g_false,
3292 P_CATEGORY => l_category,
3293 P_SOURCE_ID => l_source_id,
3294 P_ADDRESS_ID => l_address_id,
3295 P_CONTACT_ID => l_contact_id,
3296 P_MANAGING_EMP_ID => l_managing_emp_id,
3297 P_START_DATE_ACTIVE => l_start_date_active,
3298 P_END_DATE_ACTIVE => l_end_date_active,
3299 P_TIME_ZONE => l_time_zone,
3300 P_COST_PER_HR => l_cost_per_hr,
3301 P_PRIMARY_LANGUAGE => l_primary_language,
3302 P_SECONDARY_LANGUAGE => l_secondary_language,
3303 P_SUPPORT_SITE_ID => l_support_site_id,
3304 P_IES_AGENT_LOGIN => l_ies_agent_login,
3305 P_SERVER_GROUP_ID => l_server_group_id,
3306 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
3307 P_COST_CENTER => l_cost_center,
3308 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
3309 P_COMP_CURRENCY_CODE => l_comp_currency_code,
3310 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
3311 P_HOLD_REASON_CODE => l_hold_reason_code,
3312 P_HOLD_PAYMENT => l_hold_payment,
3313 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
3314 P_USER_ID => l_user_id,
3315 P_TRANSACTION_NUMBER => l_transaction_number,
3316 --P_LOCATION => l_location,
3317 X_RETURN_STATUS => x_return_status,
3318 X_MSG_COUNT => x_msg_count,
3319 X_MSG_DATA => x_msg_data,
3320 X_RESOURCE_ID => x_resource_id,
3321 X_RESOURCE_NUMBER => x_resource_number,
3322 P_ATTRIBUTE1 => p_attribute1,
3323 P_ATTRIBUTE2 => p_attribute2,
3324 P_ATTRIBUTE3 => p_attribute3,
3325 P_ATTRIBUTE4 => p_attribute4,
3326 P_ATTRIBUTE5 => p_attribute5,
3327 P_ATTRIBUTE6 => p_attribute6,
3328 P_ATTRIBUTE7 => p_attribute7,
3329 P_ATTRIBUTE8 => p_attribute8,
3330 P_ATTRIBUTE9 => p_attribute9,
3331 P_ATTRIBUTE10 => p_attribute10,
3332 P_ATTRIBUTE11 => p_attribute11,
3333 P_ATTRIBUTE12 => p_attribute12,
3334 P_ATTRIBUTE13 => p_attribute13,
3335 P_ATTRIBUTE14 => p_attribute14,
3336 P_ATTRIBUTE15 => p_attribute15,
3337 P_ATTRIBUTE_CATEGORY => p_attribute_category,
3338 P_RESOURCE_NAME => P_RESOURCE_NAME ,
3339 P_SOURCE_NAME => P_SOURCE_NAME,
3340 P_SOURCE_NUMBER => P_SOURCE_NUMBER,
3341 P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
3342 P_SOURCE_EMAIL => P_SOURCE_EMAIL,
3343 P_SOURCE_PHONE => P_SOURCE_PHONE,
3344 P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
3345 P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
3346 P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
3347 P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
3348 P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
3349 P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
3350 P_SOURCE_CITY => P_SOURCE_CITY,
3351 P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
3352 P_SOURCE_STATE => P_SOURCE_STATE,
3353 P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
3354 P_SOURCE_COUNTY => P_SOURCE_COUNTY,
3355 P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
3356 P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
3357 P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
3358 P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
3359 P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
3360 P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
3361 P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
3362 P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
3363 P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
3364 P_SOURCE_STATUS => P_SOURCE_STATUS,
3365 P_SOURCE_OFFICE => P_SOURCE_OFFICE,
3366 P_SOURCE_LOCATION => P_SOURCE_LOCATION,
3367 P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
3368 P_USER_NAME => P_USER_NAME,
3369 P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
3370 P_SOURCE_PAGER => P_SOURCE_PAGER
3371
3372 );
3373
3374
3375
3376 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3377 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3378 RAISE FND_API.G_EXC_ERROR;
3379 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 END IF;
3382 END IF;
3383
3384 ELSE
3385
3386 -- dbms_output.put_line ('Before call to the private API create_resource_migrate' );
3387
3388 /* Call the private procedure for Migration. */
3389
3390 jtf_rs_resource_pvt.create_resource_migrate (
3391 P_API_VERSION => 1,
3392 P_INIT_MSG_LIST => fnd_api.g_false,
3393 P_COMMIT => fnd_api.g_false,
3394 P_CATEGORY => l_category,
3395 P_SOURCE_ID => l_source_id,
3396 P_ADDRESS_ID => l_address_id,
3397 P_CONTACT_ID => l_contact_id,
3398 P_MANAGING_EMP_ID => l_managing_emp_id,
3399 P_START_DATE_ACTIVE => l_start_date_active,
3400 P_END_DATE_ACTIVE => l_end_date_active,
3401 P_TIME_ZONE => l_time_zone,
3402 P_COST_PER_HR => l_cost_per_hr,
3403 P_PRIMARY_LANGUAGE => l_primary_language,
3404 P_SECONDARY_LANGUAGE => l_secondary_language,
3405 P_SUPPORT_SITE_ID => l_support_site_id,
3406 P_IES_AGENT_LOGIN => l_ies_agent_login,
3407 P_SERVER_GROUP_ID => l_server_group_id,
3408 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
3409 P_COST_CENTER => l_cost_center,
3410 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
3411 P_COMP_CURRENCY_CODE => l_comp_currency_code,
3412 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
3413 P_HOLD_REASON_CODE => l_hold_reason_code,
3414 P_HOLD_PAYMENT => l_hold_payment,
3415 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
3416 P_USER_ID => l_user_id,
3417 P_TRANSACTION_NUMBER => l_transaction_number,
3418 --P_LOCATION => l_location,
3419 P_RESOURCE_ID => G_RESOURCE_ID,
3420 P_ATTRIBUTE1 => G_ATTRIBUTE1,
3421 P_ATTRIBUTE2 => G_ATTRIBUTE2,
3422 P_ATTRIBUTE3 => G_ATTRIBUTE3,
3423 P_ATTRIBUTE4 => G_ATTRIBUTE4,
3424 P_ATTRIBUTE5 => G_ATTRIBUTE5,
3425 P_ATTRIBUTE6 => G_ATTRIBUTE6,
3426 P_ATTRIBUTE7 => G_ATTRIBUTE7,
3427 P_ATTRIBUTE8 => G_ATTRIBUTE8,
3428 P_ATTRIBUTE9 => G_ATTRIBUTE9,
3429 P_ATTRIBUTE10 => G_ATTRIBUTE10,
3430 P_ATTRIBUTE11 => G_ATTRIBUTE11,
3431 P_ATTRIBUTE12 => G_ATTRIBUTE12,
3432 P_ATTRIBUTE13 => G_ATTRIBUTE13,
3433 P_ATTRIBUTE14 => G_ATTRIBUTE14,
3434 P_ATTRIBUTE15 => G_ATTRIBUTE15,
3435 P_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
3436 X_RETURN_STATUS => x_return_status,
3437 X_MSG_COUNT => x_msg_count,
3438 X_MSG_DATA => x_msg_data,
3439 X_RESOURCE_ID => x_resource_id,
3440 X_RESOURCE_NUMBER => x_resource_number
3441 );
3442 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3443 -- dbms_output.put_line('Failed status from call to private procedure');
3444 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3445 RAISE FND_API.G_EXC_ERROR;
3446 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3448 END IF;
3449 END IF;
3450
3451 END IF;
3452
3453 IF (x_return_status <> fnd_api.g_ret_sts_success)
3454 THEN
3455 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3456 RAISE FND_API.G_EXC_ERROR;
3457 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3459 END IF;
3460 END IF;
3461
3462 IF fnd_api.to_boolean (p_commit)
3463 THEN
3464 COMMIT WORK;
3465 END IF;
3466
3467
3468 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3469
3470 EXCEPTION
3471 WHEN fnd_api.g_exc_error THEN
3472 ROLLBACK TO create_resource_sp;
3473 x_return_status := fnd_api.g_ret_sts_error;
3474 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3475 p_data => x_msg_data);
3476 WHEN fnd_api.g_exc_unexpected_error THEN
3477 ROLLBACK TO create_resource_sp;
3478 x_return_status := fnd_api.g_ret_sts_unexp_error;
3479 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3480 p_data => x_msg_data);
3481 WHEN OTHERS THEN
3482 ROLLBACK TO create_resource_sp;
3483 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3484 fnd_message.set_token('P_SQLCODE',SQLCODE);
3485 fnd_message.set_token('P_SQLERRM',SQLERRM);
3486 fnd_message.set_token('P_API_NAME', l_api_name);
3487 FND_MSG_PUB.add;
3488 x_return_status := fnd_api.g_ret_sts_unexp_error;
3489 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3490 p_data => x_msg_data);
3491
3492 END;
3493 END jtf_rs_resource_pub;