[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_EXTNS_AUD_PVT
Source
1 Package Body JTF_RS_RESOURCE_EXTNS_AUD_PVT AS
2 /* $Header: jtfrsarb.pls 120.0 2005/05/11 08:19:11 appldev ship $ */
3 -- API Name : JTF_RESOURCE_EXTNS_AUD_PVT
4 -- Type : Private
5 -- Purpose : Inserts IN the JTF_RESOURCE_EXTN_AUD
6 -- Modification History
7 -- DATE NAME PURPOSE
8 -- 20 Jan 2000 S Choudhury Created
9 -- Notes:
10 --
11
12 g_pkg_name varchar2(30) := 'JTF_RESOURCE_EXTN_AUD_PVT';
13 /*FOR INSERT */
14 PROCEDURE INSERT_RESOURCE(
15 P_API_VERSION IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2,
17 P_COMMIT IN VARCHAR2,
18 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
19 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
20 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
21 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
22 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
23 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
24 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
25 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
26 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
27 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
28 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
29 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
30 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
31 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
32 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
33 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
34 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
35 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
36 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
37 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
38 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
39 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
40 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
41 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
42 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
43 P_TRANSACTION_NUMBER IN NUMBER,
44 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
45 P_OBJECT_VERSION_NUMBER IN NUMBER,
46 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
47 X_MSG_COUNT OUT NOCOPY NUMBER,
48 X_MSG_DATA OUT NOCOPY VARCHAR2 )
49 IS
50 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
51 l_row_id varchar2(24) := null;
52
53 --other variables
54 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE';
55 l_api_version CONSTANT NUMBER :=1.0;
56 l_date Date := sysdate;
57 l_user_id Number := 1;
58 l_login_id Number := 1;
59
60
61 BEGIN
62
63 --Standard Start of API SAVEPOINT
64 SAVEPOINT RESOURCE_EXTN_AUDIT;
65
66 x_return_status := fnd_api.g_ret_sts_success;
67
68 --Standard Call to check API compatibility
69 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
75 IF FND_API.To_boolean(P_INIT_MSG_LIST)
76 THEN
77 FND_MSG_PUB.Initialize;
78 END IF;
79
80 l_date := sysdate;
81 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
82 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
83
84
85 select jtf_rs_resource_extn_aud_s.nextval
86 into l_resource_extn_aud_id
87 from dual;
88
89 /* CALL TABLE HANDLER */
90
91 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
92 X_ROWID => l_row_id,
93 x_resource_audit_id => l_resource_extn_aud_id,
94 x_resource_id => p_resource_id,
95 x_new_category => p_category,
96 x_old_category => NULL,
97 x_new_resource_number => P_resource_number ,
98 x_old_resource_number => NULL,
99 x_new_source_id => p_source_id,
100 x_old_source_id => null,
101 x_new_address_id => p_address_id,
102 x_old_address_id => null,
103 x_new_contact_id => p_contact_id,
104 x_old_contact_id => null,
105 x_new_managing_employee_id => p_managing_emp_id,
106 x_old_managing_employee_id => null,
107 x_new_start_date_active => p_start_date_active,
108 x_old_start_date_active => null,
109 x_new_end_date_active => p_end_date_active,
110 x_old_end_date_active => null,
111 x_new_time_zone => p_time_zone,
112 x_old_time_zone => null,
113 x_new_cost_per_hr => p_cost_per_hr,
114 x_old_cost_per_hr => null,
115 x_new_primary_language => p_primary_language,
116 x_old_primary_language => NULL,
117 x_new_secondary_language => p_secondary_language,
118 x_old_secondary_language => NULL,
119 x_new_support_site_id => p_support_site_id,
120 x_old_support_site_id => NULL,
121 x_new_ies_agent_login => p_ies_agent_login,
122 x_old_ies_agent_login => null,
123 x_new_server_group_id => p_server_group_id,
124 x_old_server_group_id => null,
125 x_new_assigned_to_group_id => p_assigned_to_group_id,
126 x_old_assigned_to_group_id => null,
127 x_new_cost_center => p_cost_center,
128 x_old_cost_center => null,
129 x_new_charge_to_cost_center => p_charge_to_cost_center,
130 x_old_charge_to_cost_center => null,
131 x_new_compensation_currency_co => p_comp_currency_code,
132 x_old_compensation_currency_co => null,
133 x_new_commissionable_flag => p_commissionable_flag,
134 x_old_commissionable_flag => null,
135 x_new_hold_reason_code => p_hold_reason_code,
136 x_old_hold_reason_code => null,
137 x_new_hold_payment => p_hold_payment,
138 x_old_hold_payment => null,
139 x_new_comp_service_team_id => p_comp_service_team_id,
140 x_old_comp_service_team_id => null,
141 x_new_transaction_number => p_transaction_number,
142 x_old_transaction_number => null,
143 x_new_object_version_number => p_object_version_number,
144 x_old_object_version_number => null,
145 x_new_user_id => p_user_id,
146 x_old_user_id => null,
147 --x_old_location => null,
148 -- x_new_location => p_location,
149 X_CREATION_DATE => l_date,
150 X_CREATED_BY => l_user_id,
151 X_LAST_UPDATE_DATE => l_date,
152 X_LAST_UPDATED_BY => l_user_id,
153 X_LAST_UPDATE_LOGIN => l_login_id
154 );
155
156
157
158
159 IF fnd_api.to_boolean (p_commit)
160 THEN
161 COMMIT WORK;
162 END IF;
163
164 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
165
166
167 EXCEPTION
168 WHEN fnd_api.g_exc_unexpected_error
169 THEN
170 ROLLBACK TO resource_extn_audit;
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
173 FND_MSG_PUB.add;
174 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
175 WHEN OTHERS
176 THEN
177 ROLLBACK TO resource_extn_audit;
178 x_return_status := fnd_api.g_ret_sts_unexp_error;
179 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
180 FND_MSG_PUB.add;
181 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
182
183
184 END INSERT_RESOURCE;
185
186
187 /*INSERT for Resource Synchronization */
188 PROCEDURE INSERT_RESOURCE(
189 P_API_VERSION IN NUMBER,
190 P_INIT_MSG_LIST IN VARCHAR2,
191 P_COMMIT IN VARCHAR2,
192 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
193 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
194 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
195 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
196 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
197 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
198 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
199 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
200 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
201 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
202 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
203 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
204 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
205 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
206 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
207 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
208 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
209 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
210 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
211 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
212 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
213 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
214 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
215 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
216 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
217 P_TRANSACTION_NUMBER IN NUMBER,
218 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
219 P_OBJECT_VERSION_NUMBER IN NUMBER,
220 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
221 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
222 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
223 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
224 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
225 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
226 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE,
227 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
228 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
229 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
230 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
231 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
232 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
233 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
234 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
235 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
236 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
237 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
238 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
239 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
240 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
241 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
242 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
243 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
244 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
245 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
246 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
247 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
248 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
249 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
250 P_USER_NAME IN JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE,
251 P_PARTY_ID IN JTF_RS_RESOURCE_EXTNS.PERSON_PARTY_ID%TYPE,
252 P_SOURCE_JOB_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_ID%TYPE,
253 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
254 X_MSG_COUNT OUT NOCOPY NUMBER,
255 X_MSG_DATA OUT NOCOPY VARCHAR2,
256 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
257 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE)
258 IS
259 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
260 l_row_id varchar2(24) := null;
261
262 --other variables
263 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE';
264 l_api_version CONSTANT NUMBER :=1.0;
265 l_date Date := sysdate;
266 l_user_id Number := 1;
267 l_login_id Number := 1;
268
269
270 BEGIN
271
272 --Standard Start of API SAVEPOINT
273 SAVEPOINT RESOURCE_EXTN_AUDIT;
274
275 x_return_status := fnd_api.g_ret_sts_success;
276
277 --Standard Call to check API compatibility
278 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
279 THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
284 IF FND_API.To_boolean(P_INIT_MSG_LIST)
285 THEN
286 FND_MSG_PUB.Initialize;
287 END IF;
288
289 l_date := sysdate;
290 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
291 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
292
293
294 select jtf_rs_resource_extn_aud_s.nextval
295 into l_resource_extn_aud_id
296 from dual;
297
298 /* CALL TABLE HANDLER */
299
300 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
301 X_ROWID => l_row_id,
302 x_resource_audit_id => l_resource_extn_aud_id,
303 x_resource_id => p_resource_id,
304 x_new_category => p_category,
305 x_old_category => NULL,
306 x_new_resource_number => P_resource_number ,
307 x_old_resource_number => NULL,
308 x_new_source_id => p_source_id,
309 x_old_source_id => null,
310 x_new_address_id => p_address_id,
311 x_old_address_id => null,
312 x_new_contact_id => p_contact_id,
313 x_old_contact_id => null,
314 x_new_managing_employee_id => p_managing_emp_id,
315 x_old_managing_employee_id => null,
316 x_new_start_date_active => p_start_date_active,
317 x_old_start_date_active => null,
318 x_new_end_date_active => p_end_date_active,
319 x_old_end_date_active => null,
320 x_new_time_zone => p_time_zone,
321 x_old_time_zone => null,
322 x_new_cost_per_hr => p_cost_per_hr,
323 x_old_cost_per_hr => null,
324 x_new_primary_language => p_primary_language,
325 x_old_primary_language => NULL,
326 x_new_secondary_language => p_secondary_language,
327 x_old_secondary_language => NULL,
328 x_new_support_site_id => p_support_site_id,
329 x_old_support_site_id => NULL,
330 x_new_ies_agent_login => p_ies_agent_login,
331 x_old_ies_agent_login => null,
332 x_new_server_group_id => p_server_group_id,
333 x_old_server_group_id => null,
334 x_new_assigned_to_group_id => p_assigned_to_group_id,
335 x_old_assigned_to_group_id => null,
336 x_new_cost_center => p_cost_center,
337 x_old_cost_center => null,
338 x_new_charge_to_cost_center => p_charge_to_cost_center,
339 x_old_charge_to_cost_center => null,
340 x_new_compensation_currency_co => p_comp_currency_code,
341 x_old_compensation_currency_co => null,
342 x_new_commissionable_flag => p_commissionable_flag,
343 x_old_commissionable_flag => null,
344 x_new_hold_reason_code => p_hold_reason_code,
345 x_old_hold_reason_code => null,
346 x_new_hold_payment => p_hold_payment,
347 x_old_hold_payment => null,
348 x_new_comp_service_team_id => p_comp_service_team_id,
349 x_old_comp_service_team_id => null,
350 x_new_transaction_number => p_transaction_number,
351 x_old_transaction_number => null,
352 x_new_object_version_number => p_object_version_number,
353 x_old_object_version_number => null,
354 x_new_user_id => p_user_id,
355 x_old_user_id => null,
356 --x_new_location => p_location,
357 --x_old_location => null,
358 X_NEW_RESOURCE_NAME => p_resource_name,
359 X_OLD_RESOURCE_NAME => null,
360 X_NEW_SOURCE_NAME => p_source_name,
361 X_OLD_SOURCE_NAME => null,
362 X_NEW_SOURCE_NUMBER => p_source_number,
363 X_OLD_SOURCE_NUMBER => null,
364 X_NEW_SOURCE_JOB_TITLE => p_source_job_title,
365 X_OLD_SOURCE_JOB_TITLE => null,
366 X_NEW_SOURCE_EMAIL => p_source_email,
367 X_OLD_SOURCE_EMAIL => null,
368 X_NEW_SOURCE_PHONE => p_source_phone,
369 X_OLD_SOURCE_PHONE => null,
370 X_NEW_SOURCE_ORG_ID => p_source_org_id,
371 X_OLD_SOURCE_ORG_ID => null,
372 X_NEW_SOURCE_ORG_NAME => p_source_org_name,
373 X_OLD_SOURCE_ORG_NAME => null,
374 X_NEW_SOURCE_ADDRESS1 => p_source_address1,
375 X_OLD_SOURCE_ADDRESS1 => null,
376 X_NEW_SOURCE_ADDRESS2 => p_source_address2,
377 X_OLD_SOURCE_ADDRESS2 => null,
378 X_NEW_SOURCE_ADDRESS3 => p_source_address3,
379 X_OLD_SOURCE_ADDRESS3 => null,
380 X_NEW_SOURCE_ADDRESS4 => p_source_address4,
381 X_OLD_SOURCE_ADDRESS4 => null,
382 X_NEW_SOURCE_CITY => p_source_city,
383 X_OLD_SOURCE_CITY => null,
384 X_NEW_SOURCE_POSTAL_CODE => p_source_postal_code,
385 X_OLD_SOURCE_POSTAL_CODE => null,
386 X_NEW_SOURCE_STATE => p_source_state,
387 X_OLD_SOURCE_STATE => null,
388 X_NEW_SOURCE_PROVINCE => p_source_province,
389 X_OLD_SOURCE_PROVINCE => null,
390 X_NEW_SOURCE_COUNTY => p_source_county,
391 X_OLD_SOURCE_COUNTY => null,
392 X_NEW_SOURCE_COUNTRY => p_source_country,
393 X_OLD_SOURCE_COUNTRY => null,
394 X_NEW_SOURCE_MGR_ID => p_source_mgr_id,
395 X_OLD_SOURCE_MGR_ID => null,
396 X_NEW_SOURCE_MGR_NAME => p_source_mgr_name,
397 X_OLD_SOURCE_MGR_NAME => null,
398 X_NEW_SOURCE_BUSINESS_GRP_ID => p_source_business_grp_id,
399 X_OLD_SOURCE_BUSINESS_GRP_ID => null,
400 X_NEW_SOURCE_BUSINESS_GRP_NAME => p_source_business_grp_name,
401 X_OLD_SOURCE_BUSINESS_GRP_NAME => null,
402 X_NEW_SOURCE_FIRST_NAME => p_source_first_name,
403 X_OLD_SOURCE_FIRST_NAME => null,
404 X_NEW_SOURCE_MIDDLE_NAME => p_source_middle_name,
405 X_OLD_SOURCE_MIDDLE_NAME => null,
406 X_NEW_SOURCE_LAST_NAME => p_source_last_name,
407 X_OLD_SOURCE_LAST_NAME => null,
408 X_NEW_SOURCE_CATEGORY => p_source_category,
409 X_OLD_SOURCE_CATEGORY => null,
410 X_NEW_SOURCE_STATUS => p_source_status,
411 X_OLD_SOURCE_STATUS => null,
412 X_NEW_SOURCE_OFFICE => p_source_office,
413 X_OLD_SOURCE_OFFICE => null,
414 X_NEW_SOURCE_LOCATION => p_source_location,
415 X_OLD_SOURCE_LOCATION => null,
416 X_NEW_SOURCE_MAILSTOP => p_source_mailstop,
417 X_OLD_SOURCE_MAILSTOP => null,
418 X_NEW_USER_NAME => p_user_name,
419 X_OLD_USER_NAME => null,
420 X_NEW_SOURCE_JOB_ID => p_source_job_id,
421 X_OLD_SOURCE_JOB_ID => null,
422 X_NEW_PARTY_ID => p_party_id,
423 X_OLD_PARTY_ID => null,
424 X_CREATION_DATE => l_date,
425 X_CREATED_BY => l_user_id,
426 X_LAST_UPDATE_DATE => l_date,
427 X_LAST_UPDATED_BY => l_user_id,
428 X_LAST_UPDATE_LOGIN => l_login_id,
429 X_NEW_SOURCE_MOBILE_PHONE => p_source_mobile_phone,
430 X_OLD_SOURCE_MOBILE_PHONE => null,
431 X_NEW_SOURCE_PAGER => p_source_pager,
432 X_OLD_SOURCE_PAGER => null
433 );
434
435
436 IF fnd_api.to_boolean (p_commit)
437 THEN
438 COMMIT WORK;
439 END IF;
440
441 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
442
443
444 EXCEPTION
445 WHEN fnd_api.g_exc_unexpected_error
446 THEN
447 ROLLBACK TO resource_extn_audit;
448 x_return_status := fnd_api.g_ret_sts_unexp_error;
449 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
450 FND_MSG_PUB.add;
451 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
452 WHEN OTHERS
453 THEN
454 ROLLBACK TO resource_extn_audit;
455 x_return_status := fnd_api.g_ret_sts_unexp_error;
456 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
457 FND_MSG_PUB.add;
458 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
459
460
461 END INSERT_RESOURCE;
462
463
464 /* FOR UPDATE */
465 PROCEDURE UPDATE_RESOURCE(
466 P_API_VERSION IN NUMBER,
467 P_INIT_MSG_LIST IN VARCHAR2,
468 P_COMMIT IN VARCHAR2,
469 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
470 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
471 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
472 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
473 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
474 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
475 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
476 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
477 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
478 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
479 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
480 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
481 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
482 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
483 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
484 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
485 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
486 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
487 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
488 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
489 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
490 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
491 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
492 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
493 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
494 P_TRANSACTION_NUMBER IN NUMBER,
495 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
496 P_OBJECT_VERSION_NUMBER IN NUMBER,
497 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
498 X_MSG_COUNT OUT NOCOPY NUMBER,
499 X_MSG_DATA OUT NOCOPY VARCHAR2 )
500 IS
501
502 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
503 IS
504 SELECT category
505 ,resource_number
506 ,source_id
507 ,address_id
508 ,contact_id
509 ,managing_employee_id
510 ,start_date_active
511 ,end_date_active
512 ,time_zone
513 ,cost_per_hr
514 ,primary_language
515 ,secondary_language
516 ,support_site_id
517 ,ies_agent_login
518 ,server_group_id
519 ,assigned_to_group_id
520 ,cost_center
521 ,charge_to_cost_center
522 ,compensation_currency_code
523 ,commissionable_flag
524 ,hold_reason_code
525 ,hold_payment
526 ,comp_service_team_id
527 ,transaction_number
528 ,object_version_number
529 --,location
530 , user_id
531 FROM jtf_rs_resource_extns
532 WHERE resource_id = l_resource_id;
533
534
535 --declare variables
536 --old value
537 l_resource_number jtf_rs_resource_extns.resource_number%type;
538 l_category jtf_rs_resource_extns.category%type;
539 l_source_id jtf_rs_resource_extns.source_id%type ;
540 l_address_id jtf_rs_resource_extns.address_id%type ;
541 l_contact_id jtf_rs_resource_extns.contact_id%type ;
542 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%type ;
543 l_start_date_active jtf_rs_resource_extns.start_date_active%type;
544 l_end_date_active jtf_rs_resource_extns.end_date_active%type ;
545 l_time_zone jtf_rs_resource_extns.time_zone%type ;
546 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type ;
547 l_primary_language jtf_rs_resource_extns.primary_language%type ;
548 l_secondary_language jtf_rs_resource_extns.secondary_language%type ;
549 l_support_site_id jtf_rs_resource_extns.support_site_id%type ;
550 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%type ;
551 l_server_group_id jtf_rs_resource_extns.server_group_id%type ;
552 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%type ;
553 l_cost_center jtf_rs_resource_extns.cost_center%type ;
554 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%type ;
555 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%type ;
556 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%type ;
557 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%type ;
558 l_hold_payment jtf_rs_resource_extns.hold_payment%type ;
559 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%type ;
560 --l_location mdsys.sdo_geometry ;
561 l_transaction_number number;
562 l_user_id_o jtf_rs_resource_extns.user_id%type;
563 l_object_version_number number;
564
565
566
567
568
569
570 --new values
571 l_resource_number_n jtf_rs_resource_extns.resource_number%type;
572 l_category_n jtf_rs_resource_extns.category%type;
573 l_source_id_n jtf_rs_resource_extns.source_id%type ;
574 l_address_id_n jtf_rs_resource_extns.address_id%type ;
575 l_contact_id_n jtf_rs_resource_extns.contact_id%type ;
576 l_managing_emp_id_n jtf_rs_resource_extns.managing_employee_id%type ;
577 l_start_date_active_n jtf_rs_resource_extns.start_date_active%type;
578 l_end_date_active_n jtf_rs_resource_extns.end_date_active%type ;
579 l_time_zone_n jtf_rs_resource_extns.time_zone%type ;
580 l_cost_per_hr_n jtf_rs_resource_extns.cost_per_hr%type ;
581 l_primary_language_n jtf_rs_resource_extns.primary_language%type ;
582 l_secondary_language_n jtf_rs_resource_extns.secondary_language%type ;
583 l_support_site_id_n jtf_rs_resource_extns.support_site_id%type ;
584 l_ies_agent_login_n jtf_rs_resource_extns.ies_agent_login%type ;
585 l_server_group_id_n jtf_rs_resource_extns.server_group_id%type ;
586 l_assigned_to_group_id_n jtf_rs_resource_extns.assigned_to_group_id%type ;
587 l_cost_center_n jtf_rs_resource_extns.cost_center%type ;
588 l_charge_to_cost_center_n jtf_rs_resource_extns.charge_to_cost_center%type ;
589 l_comp_currency_code_n jtf_rs_resource_extns.compensation_currency_code%type ;
590 l_commissionable_flag_n jtf_rs_resource_extns.commissionable_flag%type ;
591 l_hold_reason_code_n jtf_rs_resource_extns.hold_reason_code%type ;
592 l_hold_payment_n jtf_rs_resource_extns.hold_payment%type ;
593 l_comp_service_team_id_n jtf_rs_resource_extns.comp_service_team_id%type ;
594 --l_location_n mdsys.sdo_geometry ;
595 l_transaction_number_n number;
596 l_user_id_n jtf_rs_resource_extns.user_id%type;
597 l_object_version_number_n number;
598
599 rr_old_rec rr_old_cur%rowtype;
600 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
601 l_row_id varchar2(24) := null;
602 l_dummy varchar2(10) := 'S';
603
604 --other variables
605 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
606 l_api_version CONSTANT NUMBER :=1.0;
607 l_date Date := sysdate;
608 l_user_id Number := 1;
609 l_login_id Number := 1;
610
611
612 BEGIN
613
614 --Standard Start of API SAVEPOINT
615 SAVEPOINT RESOURCE_EXTN_AUDIT;
616
617 x_return_status := fnd_api.g_ret_sts_success;
618
619 --Standard Call to check API compatibility
620 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
626 IF FND_API.To_boolean(P_INIT_MSG_LIST)
627 THEN
628 FND_MSG_PUB.Initialize;
629 END IF;
630
631 l_date := sysdate;
632 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
633 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
634
635
636 open rr_old_cur(p_resource_id);
637 FETCH rr_old_cur into rr_old_rec;
638 close rr_old_cur;
639
640 if nvl(p_category,fnd_api.g_miss_char) <> NVL(rr_old_rec.category,fnd_api.g_miss_char)
641 then
642 l_category := rr_old_rec.category;
643 l_category_n := p_category;
644 end if;
645 if nvl(p_resource_number,fnd_api.g_miss_char) <> nvl(rr_old_rec.resource_number, fnd_api.g_miss_char)
646 then
647 l_resource_number := rr_old_rec.resource_number;
648 l_resource_number_n:= p_resource_number;
649 end if;
650 if nvl(p_source_id, -1) <> nvl(rr_old_rec.source_id, -1)
651 then
652 l_source_id := rr_old_rec.source_id ;
653 l_source_id_n := p_source_id ;
654 end if;
655 if nvl(p_address_id, -1) <> nvl(rr_old_rec.address_id, -1)
656 then
657 l_address_id := rr_old_rec.address_id;
658 l_address_id_n := p_address_id ;
659 end if;
660 if nvl(p_contact_id, -1) <> nvl(rr_old_rec.contact_id , -1)
661 then
662 l_contact_id := rr_old_rec.contact_id;
663 l_contact_id_n := p_contact_id ;
664 end if;
665 if nvl(p_managing_emp_id, -1) <> nvl(rr_old_rec.managing_employee_id , -1)
666 then
667 l_managing_emp_id := rr_old_rec.managing_employee_id ;
668 l_managing_emp_id_n := p_managing_emp_id ;
669 end if;
670 if p_start_date_active <> rr_old_rec.start_date_active
671 then
672 l_start_date_active := rr_old_rec.start_date_active;
673 l_start_date_active_n := p_start_date_active ;
674 end if;
675 if nvl(p_end_date_active,fnd_api.g_miss_date)<> nvl(rr_old_rec.end_date_active,fnd_api.g_miss_date)
676 then
677 l_end_date_active := rr_old_rec.end_date_active;
678 l_end_date_active_n := p_end_date_active ;
679 end if;
680 if nvl(p_time_zone, -1) <> nvl(rr_old_rec.time_zone, -1)
681 then
682 l_time_zone := rr_old_rec.time_zone;
683 l_time_zone_n := p_time_zone ;
684 end if;
685 if nvl(p_cost_per_hr, -1) <> nvl(rr_old_rec.cost_per_hr, -1)
686 then
687 l_cost_per_hr := rr_old_rec.cost_per_hr;
688 l_cost_per_hr_n := p_cost_per_hr ;
689 end if;
690 if nvl(p_primary_language, fnd_api.g_miss_char) <> nvl(rr_old_rec.primary_language,fnd_api.g_miss_char)
691 then
692 l_primary_language := rr_old_rec.primary_language;
693 l_primary_language_n := p_primary_language;
694 end if;
695 if nvl(p_secondary_language,fnd_api.g_miss_char) <> nvl(rr_old_rec.secondary_language, fnd_api.g_miss_char)
696 then
697 l_secondary_language := rr_old_rec.secondary_language;
698 l_secondary_language_n := p_secondary_language;
699 end if;
700 if nvl(p_support_site_id,fnd_api.g_miss_num) <> nvl(rr_old_rec.support_site_id,fnd_api.g_miss_num)
701 then
702 l_support_site_id := rr_old_rec.support_site_id;
703 l_support_site_id_n := p_support_site_id ;
704 end if;
705 if nvl(p_ies_agent_login, fnd_api.g_miss_char)<> nvl(rr_old_rec.ies_agent_login, fnd_api.g_miss_char)
706 then
707 l_ies_agent_login := rr_old_rec.ies_agent_login;
708 l_ies_agent_login_n := p_ies_agent_login ;
709 end if;
710 if nvl(p_server_group_id ,fnd_api.g_miss_num) <> nvl(rr_old_rec.server_group_id, fnd_api.g_miss_num)
711 then
712 l_server_group_id := rr_old_rec.server_group_id;
713 l_server_group_id_n := p_server_group_id ;
714 end if;
715 if nvl(p_server_group_id, -1) <> nvl(rr_old_rec.server_group_id, -1)
716 then
717 l_server_group_id := rr_old_rec.server_group_id;
718 l_server_group_id_n := p_server_group_id ;
719 end if;
720 if nvl(p_assigned_to_group_id, -1) <> nvl(rr_old_rec.assigned_to_group_id, -1)
721 then
722 l_assigned_to_group_id := rr_old_rec.assigned_to_group_id;
723 l_assigned_to_group_id_n := p_assigned_to_group_id;
724 end if;
725 if nvl(p_cost_center,fnd_api.g_miss_char) <> nvl(rr_old_rec.cost_center, fnd_api.g_miss_char)
726 then
727 l_cost_center := rr_old_rec.cost_center;
728 l_cost_center_n := p_cost_center ;
729 end if;
730 if nvl(p_charge_to_cost_center,fnd_api.g_miss_char) <> nvl(rr_old_rec.charge_to_cost_center, fnd_api.g_miss_char)
731 then
732 l_charge_to_cost_center := rr_old_rec.charge_to_cost_center;
733 l_charge_to_cost_center_n := p_charge_to_cost_center ;
734 end if;
735 if nvl(p_comp_currency_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.compensation_currency_code,fnd_api.g_miss_char)
736 then
737 l_comp_currency_code := rr_old_rec.compensation_currency_code;
738 l_comp_currency_code_n := p_comp_currency_code ;
739 end if;
740 if nvl(p_commissionable_flag,fnd_api.g_miss_char) <> nvl(rr_old_rec.commissionable_flag , fnd_api.g_miss_char)
741 then
742 l_commissionable_flag := rr_old_rec.commissionable_flag;
743 l_commissionable_flag_n := p_commissionable_flag ;
744 end if;
745 if nvl(p_hold_reason_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_reason_code, fnd_api.g_miss_char)
746 then
747 l_hold_reason_code := rr_old_rec.hold_reason_code;
748 l_hold_reason_code_n := p_hold_reason_code ;
749 end if;
750 if nvl(p_hold_payment,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_payment, fnd_api.g_miss_char)
751 then
752 l_hold_payment:= rr_old_rec.hold_payment;
753 l_hold_payment_n := p_hold_payment ;
754 end if;
755 if nvl(p_comp_service_team_id, -1) <> nvl(rr_old_rec.comp_service_team_id, -1)
756 then
757 l_comp_service_team_id := rr_old_rec.comp_service_team_id;
758 l_comp_service_team_id_n := p_comp_service_team_id ;
759 end if;
760
761 /*if(p_location.sdo_gtype <> rr_old_rec.location.sdo_gtype
762 OR p_location.sdo_srid <> rr_old_rec.location.sdo_srid
763 OR p_location.sdo_point.x <> rr_old_rec.location.sdo_point.x
764 OR p_location.sdo_point.y <> rr_old_rec.location.sdo_point.y
765 OR p_location.sdo_point.z <> rr_old_rec.location.sdo_point.z)
766 then
767 l_location := rr_old_rec.location ;
768 l_location_n := p_location ;
769 end if; */
770 if nvl(p_transaction_number, -1) <> nvl(rr_old_rec.transaction_number, -1)
771 then
772 l_transaction_number := rr_old_rec.transaction_number;
773 l_transaction_number_n := p_transaction_number;
774 end if;
775 if nvl(p_object_version_number, -1) <> nvl(rr_old_rec.object_version_number, -1)
776 then
777 l_object_version_number := rr_old_rec.object_version_number;
778 l_object_version_number_n := p_object_version_number;
779 end if;
780 if nvl(p_user_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.user_id, fnd_api.g_miss_num)
781 then
782 l_user_id_o := rr_old_rec.user_id;
783 l_user_id_n := p_user_id;
784 end if;
785
786 select jtf_rs_resource_extn_aud_s.nextval
787 into l_resource_extn_aud_id
788 from dual;
789
790 /* CALL TABLE HANDLER */
791 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
792 X_ROWID => l_row_id,
793 x_resource_audit_id => l_resource_extn_aud_id,
794 x_resource_id => p_resource_id,
795 x_new_category => l_category_n,
796 x_old_category => l_category,
797 x_new_resource_number => l_resource_number_n ,
798 x_old_resource_number => l_resource_number ,
799 x_new_source_id => l_source_id_n ,
800 x_old_source_id => l_source_id ,
801 x_new_address_id => l_address_id_n ,
802 x_old_address_id => l_address_id ,
803 x_new_contact_id => l_contact_id_n ,
804 x_old_contact_id => l_contact_id ,
805 x_new_managing_employee_id => l_managing_emp_id_n ,
806 x_old_managing_employee_id => l_managing_emp_id ,
807 x_new_start_date_active => l_start_date_active_n ,
808 x_old_start_date_active => l_start_date_active ,
809 x_new_end_date_active => l_end_date_active_n ,
810 x_old_end_date_active => l_end_date_active ,
811 x_new_time_zone => l_time_zone_n ,
812 x_old_time_zone => l_time_zone ,
813 x_new_cost_per_hr => l_cost_per_hr_n ,
814 x_old_cost_per_hr => l_cost_per_hr ,
815 x_new_primary_language => l_primary_language_n ,
816 x_old_primary_language =>l_primary_language,
817 x_new_secondary_language => l_secondary_language_n ,
818 x_old_secondary_language => l_secondary_language ,
819 x_new_support_site_id => l_support_site_id_n ,
820 x_old_support_site_id => l_support_site_id ,
821 x_new_ies_agent_login => l_ies_agent_login_n ,
822 x_old_ies_agent_login => l_ies_agent_login ,
823 x_new_server_group_id => l_server_group_id_n ,
824 x_old_server_group_id => l_server_group_id ,
825 x_new_assigned_to_group_id => l_assigned_to_group_id_n ,
826 x_old_assigned_to_group_id =>l_assigned_to_group_id,
827 x_new_cost_center => l_cost_center_n ,
828 x_old_cost_center => l_cost_center ,
829 x_new_charge_to_cost_center => l_charge_to_cost_center_n ,
830 x_old_charge_to_cost_center => l_charge_to_cost_center ,
831 x_new_compensation_currency_co => l_comp_currency_code_n ,
832 x_old_compensation_currency_co => l_comp_currency_code ,
833 x_new_commissionable_flag => l_commissionable_flag_n ,
834 x_old_commissionable_flag => l_commissionable_flag ,
835 x_new_hold_reason_code => l_hold_reason_code_n ,
836 x_old_hold_reason_code => l_hold_reason_code ,
837 x_new_hold_payment => l_hold_payment_n ,
838 x_old_hold_payment => l_hold_payment ,
839 x_new_comp_service_team_id => l_comp_service_team_id_n ,
840 x_old_comp_service_team_id => l_comp_service_team_id ,
841 x_new_transaction_number => l_transaction_number_n ,
842 x_old_transaction_number => l_transaction_number ,
843 x_new_object_version_number => l_object_version_number_n ,
844 x_old_object_version_number => l_object_version_number ,
845 x_new_user_id => l_user_id_n,
846 x_old_user_id => l_user_id_o,
847 --x_old_location => p_location,
848 --x_new_location => p_location,
849 X_CREATION_DATE => l_date,
850 X_CREATED_BY => l_user_id,
851 X_LAST_UPDATE_DATE => l_date,
852 X_LAST_UPDATED_BY => l_user_id,
853 X_LAST_UPDATE_LOGIN => l_login_id
854 );
855
856
857
858
859 IF fnd_api.to_boolean (p_commit)
860 THEN
861 COMMIT WORK;
862 END IF;
863
864 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
865
866
867 EXCEPTION
868 WHEN fnd_api.g_exc_unexpected_error
869 THEN
870 ROLLBACK TO resource_extn_audit;
871 x_return_status := fnd_api.g_ret_sts_unexp_error;
872 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
873 FND_MSG_PUB.add;
874 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
875 WHEN OTHERS
876 THEN
877 ROLLBACK TO resource_extn_audit;
878 x_return_status := fnd_api.g_ret_sts_unexp_error;
879 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
880 FND_MSG_PUB.add;
881 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
882
883
884 END UPDATE_RESOURCE;
885
886
887 /*UPDATE for Resource Synchronization */
888 PROCEDURE UPDATE_RESOURCE(
889 P_API_VERSION IN NUMBER,
890 P_INIT_MSG_LIST IN VARCHAR2,
891 P_COMMIT IN VARCHAR2,
892 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
893 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
894 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
895 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
896 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
897 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
898 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
899 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
900 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
901 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
902 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
903 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
904 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
905 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
906 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
907 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
908 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
909 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
910 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
911 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
912 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
913 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
914 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
915 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
916 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
917 P_TRANSACTION_NUMBER IN NUMBER,
918 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
919 P_OBJECT_VERSION_NUMBER IN NUMBER,
920 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
921 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
922 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
923 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
924 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
925 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
926 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE,
927 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
928 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
929 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
930 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
931 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
932 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
933 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
934 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
935 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
936 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
937 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
938 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
939 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
940 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
941 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
942 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
943 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
944 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
945 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
946 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
947 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
948 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
949 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
950 P_USER_NAME IN JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE,
951 P_PARTY_ID IN JTF_RS_RESOURCE_EXTNS.PERSON_PARTY_ID%TYPE,
952 P_SOURCE_JOB_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_ID%TYPE,
953 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
954 X_MSG_COUNT OUT NOCOPY NUMBER,
955 X_MSG_DATA OUT NOCOPY VARCHAR2,
956 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
957 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE )
958 IS
959
960 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
961 IS
962 SELECT category
963 ,resource_number
964 ,source_id
965 ,address_id
966 ,contact_id
967 ,managing_employee_id
968 ,start_date_active
969 ,end_date_active
970 ,time_zone
971 ,cost_per_hr
972 ,primary_language
973 ,secondary_language
974 ,support_site_id
975 ,ies_agent_login
976 ,server_group_id
977 ,assigned_to_group_id
978 ,cost_center
979 ,charge_to_cost_center
980 ,compensation_currency_code
981 ,commissionable_flag
982 ,hold_reason_code
983 ,hold_payment
984 ,comp_service_team_id
985 ,transaction_number
986 ,object_version_number
987 --,location
988 , user_id
989 , RESOURCE_NAME
990 , SOURCE_NAME
991 , SOURCE_NUMBER
992 , SOURCE_JOB_TITLE
993 , SOURCE_EMAIL
994 , SOURCE_PHONE
995 , SOURCE_ORG_ID
996 , SOURCE_ORG_NAME
997 , SOURCE_ADDRESS1
998 , SOURCE_ADDRESS2
999 , SOURCE_ADDRESS3
1000 , SOURCE_ADDRESS4
1001 , SOURCE_CITY
1002 , SOURCE_POSTAL_CODE
1003 , SOURCE_STATE
1004 , SOURCE_PROVINCE
1005 , SOURCE_COUNTY
1006 , SOURCE_COUNTRY
1007 , SOURCE_MGR_ID
1008 , SOURCE_MGR_NAME
1009 , SOURCE_BUSINESS_GRP_ID
1010 , SOURCE_BUSINESS_GRP_NAME
1011 , SOURCE_FIRST_NAME
1012 , SOURCE_MIDDLE_NAME
1013 , SOURCE_LAST_NAME
1014 , SOURCE_CATEGORY
1015 , SOURCE_STATUS
1016 , SOURCE_OFFICE
1017 , SOURCE_LOCATION
1018 , SOURCE_MAILSTOP
1019 , USER_NAME
1020 , SOURCE_JOB_ID
1021 , PERSON_PARTY_ID
1022 , SOURCE_MOBILE_PHONE
1023 , SOURCE_PAGER
1024 FROM jtf_rs_resource_extns_vl
1025 WHERE resource_id = l_resource_id;
1026
1027
1028 --declare variables
1029 --old value
1030 l_resource_number jtf_rs_resource_extns.resource_number%type;
1031 l_category jtf_rs_resource_extns.category%type;
1032 l_source_id jtf_rs_resource_extns.source_id%type ;
1033 l_address_id jtf_rs_resource_extns.address_id%type ;
1034 l_contact_id jtf_rs_resource_extns.contact_id%type ;
1035 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%type ;
1036 l_start_date_active jtf_rs_resource_extns.start_date_active%type;
1037 l_end_date_active jtf_rs_resource_extns.end_date_active%type ;
1038 l_time_zone jtf_rs_resource_extns.time_zone%type ;
1039 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type ;
1040 l_primary_language jtf_rs_resource_extns.primary_language%type ;
1041 l_secondary_language jtf_rs_resource_extns.secondary_language%type ;
1042 l_support_site_id jtf_rs_resource_extns.support_site_id%type ;
1043 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%type ;
1044 l_server_group_id jtf_rs_resource_extns.server_group_id%type ;
1045 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%type ;
1046 l_cost_center jtf_rs_resource_extns.cost_center%type ;
1047 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%type ;
1048 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%type ;
1049 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%type ;
1050 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%type ;
1051 l_hold_payment jtf_rs_resource_extns.hold_payment%type ;
1052 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%type ;
1053 --l_location mdsys.sdo_geometry ;
1054 l_transaction_number number;
1055 l_user_id_o jtf_rs_resource_extns.user_id%type;
1056 l_object_version_number number;
1057
1058 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
1059 l_source_name jtf_rs_resource_extns.source_name%type;
1060 l_source_number jtf_rs_resource_extns.source_number%type;
1061 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
1062 l_source_email jtf_rs_resource_extns.source_email%type;
1063 l_source_phone jtf_rs_resource_extns.source_phone%type;
1064 l_source_org_id jtf_rs_resource_extns.source_org_id%type;
1065 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
1066 l_source_address1 jtf_rs_resource_extns.source_address1%type;
1067 l_source_address2 jtf_rs_resource_extns.source_address2%type;
1068 l_source_address3 jtf_rs_resource_extns.source_address3%type;
1069 l_source_address4 jtf_rs_resource_extns.source_address4%type;
1070 l_source_city jtf_rs_resource_extns.source_city%type;
1071 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
1072 l_source_state jtf_rs_resource_extns.source_state%type;
1073 l_source_province jtf_rs_resource_extns.source_province%type;
1074 l_source_county jtf_rs_resource_extns.source_county%type;
1075 l_source_country jtf_rs_resource_extns.source_country%type;
1076 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
1077 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
1078 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
1079 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
1080 l_source_first_name jtf_rs_resource_extns.source_first_name%type;
1081 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type;
1082 l_source_last_name jtf_rs_resource_extns.source_last_name%type;
1083 l_source_category jtf_rs_resource_extns.source_category%type;
1084 l_source_status jtf_rs_resource_extns.source_status%type;
1085 l_source_office jtf_rs_resource_extns.source_office%type;
1086 l_source_location jtf_rs_resource_extns.source_location%type;
1087 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
1088 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
1089 l_source_pager jtf_rs_resource_extns.source_pager%type;
1090 l_user_name jtf_rs_resource_extns.user_name%type;
1091 l_source_job_id jtf_rs_resource_extns.source_job_id%type;
1092 l_party_id jtf_rs_resource_extns.person_party_id%type;
1093
1094
1095 --new values
1096 l_resource_number_n jtf_rs_resource_extns.resource_number%type;
1097 l_category_n jtf_rs_resource_extns.category%type;
1098 l_source_id_n jtf_rs_resource_extns.source_id%type ;
1099 l_address_id_n jtf_rs_resource_extns.address_id%type ;
1100 l_contact_id_n jtf_rs_resource_extns.contact_id%type ;
1101 l_managing_emp_id_n jtf_rs_resource_extns.managing_employee_id%type ;
1102 l_start_date_active_n jtf_rs_resource_extns.start_date_active%type;
1103 l_end_date_active_n jtf_rs_resource_extns.end_date_active%type ;
1104 l_time_zone_n jtf_rs_resource_extns.time_zone%type ;
1105 l_cost_per_hr_n jtf_rs_resource_extns.cost_per_hr%type ;
1106 l_primary_language_n jtf_rs_resource_extns.primary_language%type ;
1107 l_secondary_language_n jtf_rs_resource_extns.secondary_language%type ;
1108 l_support_site_id_n jtf_rs_resource_extns.support_site_id%type ;
1109 l_ies_agent_login_n jtf_rs_resource_extns.ies_agent_login%type ;
1110 l_server_group_id_n jtf_rs_resource_extns.server_group_id%type ;
1111 l_assigned_to_group_id_n jtf_rs_resource_extns.assigned_to_group_id%type ;
1112 l_cost_center_n jtf_rs_resource_extns.cost_center%type ;
1113 l_charge_to_cost_center_n jtf_rs_resource_extns.charge_to_cost_center%type ;
1114 l_comp_currency_code_n jtf_rs_resource_extns.compensation_currency_code%type ;
1115 l_commissionable_flag_n jtf_rs_resource_extns.commissionable_flag%type ;
1116 l_hold_reason_code_n jtf_rs_resource_extns.hold_reason_code%type ;
1117 l_hold_payment_n jtf_rs_resource_extns.hold_payment%type ;
1118 l_comp_service_team_id_n jtf_rs_resource_extns.comp_service_team_id%type ;
1119 --l_location_n mdsys.sdo_geometry ;
1120 l_transaction_number_n number;
1121 l_user_id_n jtf_rs_resource_extns.user_id%type;
1122 l_object_version_number_n number;
1123
1124 l_resource_name_n jtf_rs_resource_extns_tl.resource_name%type;
1125 l_source_name_n jtf_rs_resource_extns.source_name%type;
1126 l_source_number_n jtf_rs_resource_extns.source_number%type;
1127 l_source_job_title_n jtf_rs_resource_extns.source_job_title%type;
1128 l_source_email_n jtf_rs_resource_extns.source_email%type;
1129 l_source_phone_n jtf_rs_resource_extns.source_phone%type;
1130 l_source_org_id_n jtf_rs_resource_extns.source_org_id%type;
1131 l_source_org_name_n jtf_rs_resource_extns.source_org_name%type;
1132 l_source_address1_n jtf_rs_resource_extns.source_address1%type;
1133 l_source_address2_n jtf_rs_resource_extns.source_address2%type;
1134 l_source_address3_n jtf_rs_resource_extns.source_address3%type;
1135 l_source_address4_n jtf_rs_resource_extns.source_address4%type;
1136 l_source_city_n jtf_rs_resource_extns.source_city%type;
1137 l_source_postal_code_n jtf_rs_resource_extns.source_postal_code%type;
1138 l_source_state_n jtf_rs_resource_extns.source_state%type;
1139 l_source_province_n jtf_rs_resource_extns.source_province%type;
1140 l_source_county_n jtf_rs_resource_extns.source_county%type;
1141 l_source_country_n jtf_rs_resource_extns.source_country%type;
1142 l_source_mgr_id_n jtf_rs_resource_extns.source_mgr_id%type;
1143 l_source_mgr_name_n jtf_rs_resource_extns.source_mgr_name%type;
1144 l_source_business_grp_id_n jtf_rs_resource_extns.source_business_grp_id%type;
1145 l_source_business_grp_name_n jtf_rs_resource_extns.source_business_grp_name%type;
1146 l_source_first_name_n jtf_rs_resource_extns.source_first_name%type;
1147 l_source_middle_name_n jtf_rs_resource_extns.source_middle_name%type;
1148 l_source_last_name_n jtf_rs_resource_extns.source_last_name%type;
1149 l_source_category_n jtf_rs_resource_extns.source_category%type;
1150 l_source_status_n jtf_rs_resource_extns.source_status%type;
1151 l_source_office_n jtf_rs_resource_extns.source_office%type;
1152 l_source_location_n jtf_rs_resource_extns.source_location%type;
1153 l_source_mailstop_n jtf_rs_resource_extns.source_mailstop%type;
1154 l_source_mobile_phone_n jtf_rs_resource_extns.source_mobile_phone%type;
1155 l_source_pager_n jtf_rs_resource_extns.source_pager%type;
1156 l_user_name_n jtf_rs_resource_extns.user_name%type;
1157 l_source_job_id_n jtf_rs_resource_extns.source_job_id%type;
1158 l_party_id_n jtf_rs_resource_extns.person_party_id%type;
1159
1160 rr_old_rec rr_old_cur%rowtype;
1161 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
1162 l_row_id varchar2(24) := null;
1163 l_dummy varchar2(10) := 'S';
1164
1165 --other variables
1166 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
1167 l_api_version CONSTANT NUMBER :=1.0;
1168 l_date Date := sysdate;
1169 l_user_id Number := 1;
1170 l_login_id Number := 1;
1171
1172
1173 BEGIN
1174
1175 --Standard Start of API SAVEPOINT
1176 SAVEPOINT RESOURCE_EXTN_AUDIT;
1177
1178 x_return_status := fnd_api.g_ret_sts_success;
1179
1180 --Standard Call to check API compatibility
1181 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1182 THEN
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 END IF;
1185
1186 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1187 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1188 THEN
1189 FND_MSG_PUB.Initialize;
1190 END IF;
1191
1192 l_date := sysdate;
1193 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1194 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1195
1196
1197 open rr_old_cur(p_resource_id);
1198 FETCH rr_old_cur into rr_old_rec;
1199 close rr_old_cur;
1200
1201 if nvl(p_category,fnd_api.g_miss_char) <> NVL(rr_old_rec.category,fnd_api.g_miss_char)
1202 then
1203 l_category := rr_old_rec.category;
1204 l_category_n := p_category;
1205 end if;
1206 if nvl(p_resource_number,fnd_api.g_miss_char) <> nvl(rr_old_rec.resource_number, fnd_api.g_miss_char)
1207 then
1208 l_resource_number := rr_old_rec.resource_number;
1209 l_resource_number_n:= p_resource_number;
1210 end if;
1211 if nvl(p_source_id, -1) <> nvl(rr_old_rec.source_id, -1)
1212 then
1213 l_source_id := rr_old_rec.source_id ;
1214 l_source_id_n := p_source_id ;
1215 end if;
1216 if nvl(p_address_id, -1) <> nvl(rr_old_rec.address_id, -1)
1217 then
1218 l_address_id := rr_old_rec.address_id;
1219 l_address_id_n := p_address_id ;
1220 end if;
1221 if nvl(p_contact_id, -1) <> nvl(rr_old_rec.contact_id , -1)
1222 then
1223 l_contact_id := rr_old_rec.contact_id;
1224 l_contact_id_n := p_contact_id ;
1225 end if;
1226 if nvl(p_managing_emp_id, -1) <> nvl(rr_old_rec.managing_employee_id , -1)
1227 then
1228 l_managing_emp_id := rr_old_rec.managing_employee_id ;
1229 l_managing_emp_id_n := p_managing_emp_id ;
1230 end if;
1231 if p_start_date_active <> rr_old_rec.start_date_active
1232 then
1233 l_start_date_active := rr_old_rec.start_date_active;
1234 l_start_date_active_n := p_start_date_active ;
1235 end if;
1236 if nvl(p_end_date_active,fnd_api.g_miss_date)<> nvl(rr_old_rec.end_date_active,fnd_api.g_miss_date)
1237 then
1238 l_end_date_active := rr_old_rec.end_date_active;
1239 l_end_date_active_n := p_end_date_active ;
1240 end if;
1241 if nvl(p_time_zone, -1) <> nvl(rr_old_rec.time_zone, -1)
1242 then
1243 l_time_zone := rr_old_rec.time_zone;
1244 l_time_zone_n := p_time_zone ;
1245 end if;
1246 if nvl(p_cost_per_hr, -1) <> nvl(rr_old_rec.cost_per_hr, -1)
1247 then
1248 l_cost_per_hr := rr_old_rec.cost_per_hr;
1249 l_cost_per_hr_n := p_cost_per_hr ;
1250 end if;
1251 if nvl(p_primary_language, fnd_api.g_miss_char) <> nvl(rr_old_rec.primary_language,fnd_api.g_miss_char)
1252 then
1253 l_primary_language := rr_old_rec.primary_language;
1254 l_primary_language_n := p_primary_language;
1255 end if;
1256 if nvl(p_secondary_language,fnd_api.g_miss_char) <> nvl(rr_old_rec.secondary_language, fnd_api.g_miss_char)
1257 then
1258 l_secondary_language := rr_old_rec.secondary_language;
1259 l_secondary_language_n := p_secondary_language;
1260 end if;
1261 if nvl(p_support_site_id,fnd_api.g_miss_num) <> nvl(rr_old_rec.support_site_id,fnd_api.g_miss_num)
1262 then
1263 l_support_site_id := rr_old_rec.support_site_id;
1264 l_support_site_id_n := p_support_site_id ;
1265 end if;
1266 if nvl(p_ies_agent_login, fnd_api.g_miss_char)<> nvl(rr_old_rec.ies_agent_login, fnd_api.g_miss_char)
1267 then
1268 l_ies_agent_login := rr_old_rec.ies_agent_login;
1269 l_ies_agent_login_n := p_ies_agent_login ;
1270 end if;
1271 if nvl(p_server_group_id ,fnd_api.g_miss_num) <> nvl(rr_old_rec.server_group_id, fnd_api.g_miss_num)
1272 then
1273 l_server_group_id := rr_old_rec.server_group_id;
1274 l_server_group_id_n := p_server_group_id ;
1275 end if;
1276 if nvl(p_server_group_id, -1) <> nvl(rr_old_rec.server_group_id, -1)
1277 then
1278 l_server_group_id := rr_old_rec.server_group_id;
1279 l_server_group_id_n := p_server_group_id ;
1280 end if;
1281 if nvl(p_assigned_to_group_id, -1) <> nvl(rr_old_rec.assigned_to_group_id, -1)
1282 then
1283 l_assigned_to_group_id := rr_old_rec.assigned_to_group_id;
1284 l_assigned_to_group_id_n := p_assigned_to_group_id;
1285 end if;
1286 if nvl(p_cost_center,fnd_api.g_miss_char) <> nvl(rr_old_rec.cost_center, fnd_api.g_miss_char)
1287 then
1288 l_cost_center := rr_old_rec.cost_center;
1289 l_cost_center_n := p_cost_center ;
1290 end if;
1291 if nvl(p_charge_to_cost_center,fnd_api.g_miss_char) <> nvl(rr_old_rec.charge_to_cost_center, fnd_api.g_miss_char)
1292 then
1293 l_charge_to_cost_center := rr_old_rec.charge_to_cost_center;
1294 l_charge_to_cost_center_n := p_charge_to_cost_center ;
1295 end if;
1296 if nvl(p_comp_currency_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.compensation_currency_code,fnd_api.g_miss_char)
1297 then
1298 l_comp_currency_code := rr_old_rec.compensation_currency_code;
1299 l_comp_currency_code_n := p_comp_currency_code ;
1300 end if;
1301 if nvl(p_commissionable_flag,fnd_api.g_miss_char) <> nvl(rr_old_rec.commissionable_flag , fnd_api.g_miss_char)
1302 then
1303 l_commissionable_flag := rr_old_rec.commissionable_flag;
1304 l_commissionable_flag_n := p_commissionable_flag ;
1305 end if;
1306 if nvl(p_hold_reason_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_reason_code, fnd_api.g_miss_char)
1307 then
1308 l_hold_reason_code := rr_old_rec.hold_reason_code;
1309 l_hold_reason_code_n := p_hold_reason_code ;
1310 end if;
1311 if nvl(p_hold_payment,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_payment, fnd_api.g_miss_char)
1312 then
1313 l_hold_payment:= rr_old_rec.hold_payment;
1314 l_hold_payment_n := p_hold_payment ;
1315 end if;
1316 if nvl(p_comp_service_team_id, -1) <> nvl(rr_old_rec.comp_service_team_id, -1)
1317 then
1318 l_comp_service_team_id := rr_old_rec.comp_service_team_id;
1319 l_comp_service_team_id_n := p_comp_service_team_id ;
1320 end if;
1321
1322 /*if(p_location.sdo_gtype <> rr_old_rec.location.sdo_gtype
1323 OR p_location.sdo_srid <> rr_old_rec.location.sdo_srid
1324 OR p_location.sdo_point.x <> rr_old_rec.location.sdo_point.x
1325 OR p_location.sdo_point.y <> rr_old_rec.location.sdo_point.y
1326 OR p_location.sdo_point.z <> rr_old_rec.location.sdo_point.z)
1327 then
1328 l_location := rr_old_rec.location ;
1329 l_location_n := p_location ;
1330 end if; */
1331 if nvl(p_transaction_number, -1) <> nvl(rr_old_rec.transaction_number, -1)
1332 then
1333 l_transaction_number := rr_old_rec.transaction_number;
1334 l_transaction_number_n := p_transaction_number;
1335 end if;
1336 if nvl(p_object_version_number, -1) <> nvl(rr_old_rec.object_version_number, -1)
1337 then
1338 l_object_version_number := rr_old_rec.object_version_number;
1339 l_object_version_number_n := p_object_version_number;
1340 end if;
1341 if nvl(p_user_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.user_id, fnd_api.g_miss_num)
1342 then
1343 l_user_id_o := rr_old_rec.user_id;
1344 l_user_id_n := p_user_id;
1345 end if;
1346
1347 if nvl(p_resource_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.resource_name, fnd_api.g_miss_char)
1348 then
1349 l_resource_name := rr_old_rec.resource_name;
1350 l_resource_name_n := p_resource_name;
1351 end if;
1352
1353 if nvl(p_source_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_name, fnd_api.g_miss_char)
1354 then
1355 l_source_name := rr_old_rec.source_name;
1356 l_source_name_n := p_source_name;
1357 end if;
1358
1359 if nvl(p_source_job_title, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_job_title, fnd_api.g_miss_char)
1360 then
1361 l_source_job_title := rr_old_rec.source_job_title;
1362 l_source_job_title_n := p_source_job_title;
1363 end if;
1364
1365 if nvl(p_source_email, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_email, fnd_api.g_miss_char)
1366 then
1367 l_source_email := rr_old_rec.source_email;
1368 l_source_email_n := p_source_email;
1369 end if;
1370
1371 if nvl(p_source_number, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_number, fnd_api.g_miss_char)
1372 then
1373 l_source_number := rr_old_rec.source_number;
1374 l_source_number_n := p_source_number;
1375 end if;
1376
1377 if nvl(p_source_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_phone, fnd_api.g_miss_char)
1378 then
1379 l_source_phone := rr_old_rec.source_phone;
1380 l_source_phone_n := p_source_phone;
1381 end if;
1382
1383 if nvl(p_source_org_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_org_id, fnd_api.g_miss_num)
1384 then
1385 l_source_org_id := rr_old_rec.source_org_id;
1386 l_source_org_id_n := p_source_org_id;
1387 end if;
1388
1389 if nvl(p_source_org_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_org_name, fnd_api.g_miss_char)
1390 then
1391 l_source_org_name := rr_old_rec.source_org_name;
1392 l_source_org_name_n := p_source_org_name;
1393 end if;
1394
1395 if nvl(p_source_address1, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address1, fnd_api.g_miss_char)
1396 then
1397 l_source_address1 := rr_old_rec.source_address1;
1398 l_source_address1_n := p_source_address1;
1399 end if;
1400
1401 if nvl(p_source_address2, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address2, fnd_api.g_miss_char)
1402 then
1403 l_source_address2 := rr_old_rec.source_address2;
1404 l_source_address2_n := p_source_address2;
1405 end if;
1406
1407 if nvl(p_source_address3, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address3, fnd_api.g_miss_char)
1408 then
1409 l_source_address3 := rr_old_rec.source_address3;
1410 l_source_address3_n := p_source_address3;
1411 end if;
1412
1413 if nvl(p_source_address4, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address4, fnd_api.g_miss_char)
1414 then
1415 l_source_address4 := rr_old_rec.source_address4;
1416 l_source_address4_n := p_source_address4;
1417 end if;
1418
1419 if nvl(p_source_city, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_city, fnd_api.g_miss_char)
1420 then
1421 l_source_city := rr_old_rec.source_city;
1422 l_source_city_n := p_source_city;
1423 end if;
1424
1425 if nvl(p_source_postal_code, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_postal_code, fnd_api.g_miss_char)
1426 then
1427 l_source_postal_code := rr_old_rec.source_postal_code;
1428 l_source_postal_code_n := p_source_postal_code;
1429 end if;
1430
1431 if nvl(p_source_state, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_state, fnd_api.g_miss_char)
1432 then
1433 l_source_state := rr_old_rec.source_state;
1434 l_source_state_n := p_source_state;
1435 end if;
1436
1437 if nvl(p_source_province, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_province, fnd_api.g_miss_char)
1438 then
1439 l_source_province := rr_old_rec.source_province;
1440 l_source_province_n := p_source_province;
1441 end if;
1442
1443 if nvl(p_source_county, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_county, fnd_api.g_miss_char)
1444 then
1445 l_source_county := rr_old_rec.source_county;
1446 l_source_county_n := p_source_county;
1447 end if;
1448
1449 if nvl(p_source_country, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_country, fnd_api.g_miss_char)
1450 then
1451 l_source_country := rr_old_rec.source_country;
1452 l_source_country_n := p_source_country;
1453 end if;
1454
1455 if nvl(p_source_mgr_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_mgr_id, fnd_api.g_miss_num)
1456 then
1457 l_source_mgr_id := rr_old_rec.source_mgr_id;
1458 l_source_mgr_id_n := p_source_mgr_id;
1459 end if;
1460
1461 if nvl(p_source_mgr_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mgr_name, fnd_api.g_miss_char)
1462 then
1463 l_source_mgr_name := rr_old_rec.source_mgr_name;
1464 l_source_mgr_name_n := p_source_mgr_name;
1465 end if;
1466
1467 if nvl(p_source_business_grp_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_business_grp_id, fnd_api.g_miss_num)
1468 then
1469 l_source_business_grp_id := rr_old_rec.source_business_grp_id;
1470 l_source_business_grp_id_n := p_source_business_grp_id;
1471 end if;
1472
1473 if nvl(p_source_business_grp_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_business_grp_name, fnd_api.g_miss_char)
1474 then
1475 l_source_business_grp_name := rr_old_rec.source_business_grp_name;
1476 l_source_business_grp_name_n := p_source_business_grp_name;
1477 end if;
1478
1479 if nvl(p_source_first_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_first_name, fnd_api.g_miss_char)
1480 then
1481 l_source_first_name := rr_old_rec.source_first_name;
1482 l_source_first_name_n := p_source_first_name;
1483 end if;
1484
1485 if nvl(p_source_middle_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_middle_name, fnd_api.g_miss_char)
1486 then
1487 l_source_middle_name := rr_old_rec.source_middle_name;
1488 l_source_middle_name_n := p_source_middle_name;
1489 end if;
1490
1491 if nvl(p_source_last_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_last_name, fnd_api.g_miss_char)
1492 then
1493 l_source_last_name := rr_old_rec.source_last_name;
1494 l_source_last_name_n := p_source_last_name;
1495 end if;
1496
1497 if nvl(p_source_category, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_category, fnd_api.g_miss_char)
1498 then
1499 l_source_category := rr_old_rec.source_category;
1500 l_source_category_n := p_source_category;
1501 end if;
1502
1503 if nvl(p_source_status, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_status, fnd_api.g_miss_char)
1504 then
1505 l_source_status := rr_old_rec.source_status;
1506 l_source_status_n := p_source_status;
1507 end if;
1508
1509 if nvl(p_source_office, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_office, fnd_api.g_miss_char)
1510 then
1511 l_source_office := rr_old_rec.source_office;
1512 l_source_office_n := p_source_office;
1513 end if;
1514
1515 if nvl(p_source_location, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_location, fnd_api.g_miss_char)
1516 then
1517 l_source_location := rr_old_rec.source_location;
1518 l_source_location_n := p_source_location;
1519 end if;
1520
1521 if nvl(p_source_mailstop, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mailstop, fnd_api.g_miss_char)
1522 then
1523 l_source_mailstop := rr_old_rec.source_mailstop;
1524 l_source_mailstop_n := p_source_mailstop;
1525 end if;
1526
1527 if nvl(p_source_mobile_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mobile_phone, fnd_api.g_miss_char)
1528 then
1529 l_source_mobile_phone := rr_old_rec.source_mobile_phone;
1530 l_source_mobile_phone_n := p_source_mobile_phone;
1531 end if;
1532
1533 if nvl(p_source_pager, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_pager, fnd_api.g_miss_char)
1534 then
1535 l_source_pager := rr_old_rec.source_pager;
1536 l_source_pager_n := p_source_pager;
1537 end if;
1538
1539 if nvl(p_user_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.user_name, fnd_api.g_miss_char)
1540 then
1541 l_user_name := rr_old_rec.user_name;
1542 l_user_name_n := p_user_name;
1543 end if;
1544
1545 if nvl(p_source_job_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_job_id, fnd_api.g_miss_num)
1546 then
1547 l_source_job_id := rr_old_rec.source_job_id;
1548 l_source_job_id_n := p_source_job_id;
1549 end if;
1550
1551 if nvl(p_party_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.person_party_id, fnd_api.g_miss_num)
1552 then
1553 l_party_id := rr_old_rec.person_party_id;
1554 l_party_id_n := p_party_id;
1555 end if;
1556
1557
1558
1559
1560 select jtf_rs_resource_extn_aud_s.nextval
1561 into l_resource_extn_aud_id
1562 from dual;
1563
1564 /* CALL TABLE HANDLER */
1565 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
1566 X_ROWID => l_row_id,
1567 x_resource_audit_id => l_resource_extn_aud_id,
1568 x_resource_id => p_resource_id,
1569 x_new_category => l_category_n,
1570 x_old_category => l_category,
1571 x_new_resource_number => l_resource_number_n ,
1572 x_old_resource_number => l_resource_number ,
1573 x_new_source_id => l_source_id_n ,
1574 x_old_source_id => l_source_id ,
1575 x_new_address_id => l_address_id_n ,
1576 x_old_address_id => l_address_id ,
1577 x_new_contact_id => l_contact_id_n ,
1578 x_old_contact_id => l_contact_id ,
1579 x_new_managing_employee_id => l_managing_emp_id_n ,
1580 x_old_managing_employee_id => l_managing_emp_id ,
1581 x_new_start_date_active => l_start_date_active_n ,
1582 x_old_start_date_active => l_start_date_active ,
1583 x_new_end_date_active => l_end_date_active_n ,
1584 x_old_end_date_active => l_end_date_active ,
1585 x_new_time_zone => l_time_zone_n ,
1586 x_old_time_zone => l_time_zone ,
1587 x_new_cost_per_hr => l_cost_per_hr_n ,
1588 x_old_cost_per_hr => l_cost_per_hr ,
1589 x_new_primary_language => l_primary_language_n ,
1590 x_old_primary_language =>l_primary_language,
1591 x_new_secondary_language => l_secondary_language_n ,
1592 x_old_secondary_language => l_secondary_language ,
1593 x_new_support_site_id => l_support_site_id_n ,
1594 x_old_support_site_id => l_support_site_id ,
1595 x_new_ies_agent_login => l_ies_agent_login_n ,
1596 x_old_ies_agent_login => l_ies_agent_login ,
1597 x_new_server_group_id => l_server_group_id_n ,
1598 x_old_server_group_id => l_server_group_id ,
1599 x_new_assigned_to_group_id => l_assigned_to_group_id_n ,
1600 x_old_assigned_to_group_id =>l_assigned_to_group_id,
1601 x_new_cost_center => l_cost_center_n ,
1602 x_old_cost_center => l_cost_center ,
1603 x_new_charge_to_cost_center => l_charge_to_cost_center_n ,
1604 x_old_charge_to_cost_center => l_charge_to_cost_center ,
1605 x_new_compensation_currency_co => l_comp_currency_code_n ,
1606 x_old_compensation_currency_co => l_comp_currency_code ,
1607 x_new_commissionable_flag => l_commissionable_flag_n ,
1608 x_old_commissionable_flag => l_commissionable_flag ,
1609 x_new_hold_reason_code => l_hold_reason_code_n ,
1610 x_old_hold_reason_code => l_hold_reason_code ,
1611 x_new_hold_payment => l_hold_payment_n ,
1612 x_old_hold_payment => l_hold_payment ,
1613 x_new_comp_service_team_id => l_comp_service_team_id_n ,
1614 x_old_comp_service_team_id => l_comp_service_team_id ,
1615 x_new_transaction_number => l_transaction_number_n ,
1616 x_old_transaction_number => l_transaction_number ,
1617 x_new_object_version_number => l_object_version_number_n ,
1618 x_old_object_version_number => l_object_version_number ,
1619 x_new_user_id => l_user_id_n,
1620 x_old_user_id => l_user_id_o,
1621 --x_old_location => p_location,
1622 --x_new_location => p_location,
1623 X_NEW_RESOURCE_NAME => l_resource_name_n,
1624 X_OLD_RESOURCE_NAME => l_resource_name,
1625 X_NEW_SOURCE_NAME => l_source_name_n,
1626 X_OLD_SOURCE_NAME => l_source_name,
1627 X_NEW_SOURCE_NUMBER => l_source_number_n,
1628 X_OLD_SOURCE_NUMBER => l_source_number,
1629 X_NEW_SOURCE_JOB_TITLE => l_source_job_title_n,
1630 X_OLD_SOURCE_JOB_TITLE => l_source_job_title,
1631 X_NEW_SOURCE_EMAIL => l_source_email_n,
1632 X_OLD_SOURCE_EMAIL => l_source_email,
1633 X_NEW_SOURCE_PHONE => l_source_phone_n,
1634 X_OLD_SOURCE_PHONE => l_source_phone,
1635 X_NEW_SOURCE_ORG_ID => l_source_org_id_n,
1636 X_OLD_SOURCE_ORG_ID => l_source_org_id,
1637 X_NEW_SOURCE_ORG_NAME => l_source_org_name_n,
1638 X_OLD_SOURCE_ORG_NAME => l_source_org_name,
1639 X_NEW_SOURCE_ADDRESS1 => l_source_address1_n,
1640 X_OLD_SOURCE_ADDRESS1 => l_source_address1,
1641 X_NEW_SOURCE_ADDRESS2 => l_source_address2_n,
1642 X_OLD_SOURCE_ADDRESS2 => l_source_address2,
1643 X_NEW_SOURCE_ADDRESS3 => l_source_address3_n,
1644 X_OLD_SOURCE_ADDRESS3 => l_source_address3,
1645 X_NEW_SOURCE_ADDRESS4 => l_source_address4_n,
1646 X_OLD_SOURCE_ADDRESS4 => l_source_address4,
1647 X_NEW_SOURCE_CITY => l_source_city_n,
1648 X_OLD_SOURCE_CITY => l_source_city,
1649 X_NEW_SOURCE_POSTAL_CODE => l_source_postal_code_n,
1650 X_OLD_SOURCE_POSTAL_CODE => l_source_postal_code,
1651 X_NEW_SOURCE_STATE => l_source_state_n,
1652 X_OLD_SOURCE_STATE => l_source_state,
1653 X_NEW_SOURCE_PROVINCE => l_source_province_n,
1654 X_OLD_SOURCE_PROVINCE => l_source_province,
1655 X_NEW_SOURCE_COUNTY => l_source_county_n,
1656 X_OLD_SOURCE_COUNTY => l_source_county,
1657 X_NEW_SOURCE_COUNTRY => l_source_country_n,
1658 X_OLD_SOURCE_COUNTRY => l_source_country,
1659 X_NEW_SOURCE_MGR_ID => l_source_mgr_id_n,
1660 X_OLD_SOURCE_MGR_ID => l_source_mgr_id,
1661 X_NEW_SOURCE_MGR_NAME => l_source_mgr_name_n,
1662 X_OLD_SOURCE_MGR_NAME => l_source_mgr_name,
1663 X_NEW_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id_n,
1664 X_OLD_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id,
1665 X_NEW_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name_n,
1666 X_OLD_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name,
1667 X_NEW_SOURCE_FIRST_NAME => l_source_first_name_n,
1668 X_OLD_SOURCE_FIRST_NAME => l_source_first_name,
1669 X_NEW_SOURCE_MIDDLE_NAME => l_source_middle_name_n,
1670 X_OLD_SOURCE_MIDDLE_NAME => l_source_middle_name,
1671 X_NEW_SOURCE_LAST_NAME => l_source_last_name_n,
1672 X_OLD_SOURCE_LAST_NAME => l_source_last_name,
1673 X_NEW_SOURCE_CATEGORY => l_source_category_n,
1674 X_OLD_SOURCE_CATEGORY => l_source_category,
1675 X_NEW_SOURCE_STATUS => l_source_status_n,
1676 X_OLD_SOURCE_STATUS => l_source_status,
1677 X_NEW_SOURCE_OFFICE => l_source_office_n,
1678 X_OLD_SOURCE_OFFICE => l_source_office,
1679 X_NEW_SOURCE_LOCATION => l_source_location_n,
1680 X_OLD_SOURCE_LOCATION => l_source_location,
1681 X_NEW_SOURCE_MAILSTOP => l_source_mailstop_n,
1682 X_OLD_SOURCE_MAILSTOP => l_source_mailstop,
1683 X_NEW_USER_NAME => l_user_name_n,
1684 X_OLD_USER_NAME => l_user_name,
1685 X_NEW_SOURCE_JOB_ID => l_source_job_id_n,
1686 X_OLD_SOURCE_JOB_ID => l_source_job_id,
1687 X_NEW_PARTY_ID => l_party_id_n,
1688 X_OLD_PARTY_ID => l_party_id,
1689 X_CREATION_DATE => l_date,
1690 X_CREATED_BY => l_user_id,
1691 X_LAST_UPDATE_DATE => l_date,
1692 X_LAST_UPDATED_BY => l_user_id,
1693 X_LAST_UPDATE_LOGIN => l_login_id,
1694 X_NEW_SOURCE_MOBILE_PHONE => l_source_mobile_phone_n,
1695 X_OLD_SOURCE_MOBILE_PHONE => l_source_mobile_phone,
1696 X_NEW_SOURCE_PAGER => l_source_pager_n,
1697 X_OLD_SOURCE_PAGER => l_source_pager
1698 );
1699
1700
1701 IF fnd_api.to_boolean (p_commit)
1702 THEN
1703 COMMIT WORK;
1704 END IF;
1705
1706 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1707
1708
1709 EXCEPTION
1710 WHEN fnd_api.g_exc_unexpected_error
1711 THEN
1712 ROLLBACK TO resource_extn_audit;
1713 x_return_status := fnd_api.g_ret_sts_unexp_error;
1714 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
1715 FND_MSG_PUB.add;
1716 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1717 WHEN OTHERS
1718 THEN
1719 ROLLBACK TO resource_extn_audit;
1720 x_return_status := fnd_api.g_ret_sts_unexp_error;
1721 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
1722 FND_MSG_PUB.add;
1723 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1724
1725
1726 END UPDATE_RESOURCE;
1727
1728
1729 /* DELETE procedure modified for Resource Synchronization */
1730
1731 PROCEDURE DELETE_RESOURCE(
1732 P_API_VERSION IN NUMBER,
1733 P_INIT_MSG_LIST IN VARCHAR2,
1734 P_COMMIT IN VARCHAR2,
1735 P_RESOURCE_ID IN NUMBER,
1736 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1737 X_MSG_COUNT OUT NOCOPY NUMBER,
1738 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1739 IS
1740
1741 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
1742 IS
1743 SELECT category
1744 ,resource_number
1745 ,source_id
1746 ,address_id
1747 ,contact_id
1748 ,managing_employee_id
1749 ,start_date_active
1750 ,end_date_active
1751 ,time_zone
1752 ,cost_per_hr
1753 ,primary_language
1754 ,secondary_language
1755 ,support_site_id
1756 ,ies_agent_login
1757 ,server_group_id
1758 ,assigned_to_group_id
1759 ,cost_center
1760 ,charge_to_cost_center
1761 ,compensation_currency_code
1762 ,commissionable_flag
1763 ,hold_reason_code
1764 ,hold_payment
1765 ,comp_service_team_id
1766 ,transaction_number
1767 ,object_version_number
1768 -- ,location
1769 ,user_id
1770 , RESOURCE_NAME
1771 , SOURCE_NAME
1772 , SOURCE_NUMBER
1773 , SOURCE_JOB_TITLE
1774 , SOURCE_EMAIL
1775 , SOURCE_PHONE
1776 , SOURCE_ORG_ID
1777 , SOURCE_ORG_NAME
1778 , SOURCE_ADDRESS1
1779 , SOURCE_ADDRESS2
1780 , SOURCE_ADDRESS3
1781 , SOURCE_ADDRESS4
1782 , SOURCE_CITY
1783 , SOURCE_POSTAL_CODE
1784 , SOURCE_STATE
1785 , SOURCE_PROVINCE
1786 , SOURCE_COUNTY
1787 , SOURCE_COUNTRY
1788 , SOURCE_MGR_ID
1789 , SOURCE_MGR_NAME
1790 , SOURCE_BUSINESS_GRP_ID
1791 , SOURCE_BUSINESS_GRP_NAME
1792 , SOURCE_FIRST_NAME
1793 , SOURCE_LAST_NAME
1794 , SOURCE_MIDDLE_NAME
1795 , SOURCE_CATEGORY
1796 , SOURCE_STATUS
1797 , SOURCE_OFFICE
1798 , SOURCE_LOCATION
1799 , SOURCE_MAILSTOP
1800 , USER_NAME
1801 , SOURCE_MOBILE_PHONE
1802 , SOURCE_PAGER
1803 FROM jtf_rs_resource_extns_vl
1804 WHERE resource_id = l_resource_id;
1805
1806 --declare variables
1807 --old value
1808 l_resource_number jtf_rs_resource_extns.resource_number%type;
1809 l_category jtf_rs_resource_extns.category%type;
1810 l_source_id jtf_rs_resource_extns.source_id%type ;
1811 l_address_id jtf_rs_resource_extns.address_id%type ;
1812 l_contact_id jtf_rs_resource_extns.contact_id%type ;
1813 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%type ;
1814 l_start_date_active jtf_rs_resource_extns.start_date_active%type;
1815 l_end_date_active jtf_rs_resource_extns.end_date_active%type ;
1816 l_time_zone jtf_rs_resource_extns.time_zone%type ;
1817 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type ;
1818 l_primary_language jtf_rs_resource_extns.primary_language%type ;
1819 l_secondary_language jtf_rs_resource_extns.secondary_language%type ;
1820 l_support_site_id jtf_rs_resource_extns.support_site_id%type ;
1821 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%type ;
1822 l_server_group_id jtf_rs_resource_extns.server_group_id%type ;
1823 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%type ;
1824 l_cost_center jtf_rs_resource_extns.cost_center%type ;
1825 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%type ;
1826 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%type ;
1827 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%type ;
1828 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%type ;
1829 l_hold_payment jtf_rs_resource_extns.hold_payment%type ;
1830 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%type ;
1831 --l_location mdsys.sdo_geometry ;
1832 l_transaction_number number;
1833 l_object_version_number number;
1834 l_user_id_o jtf_rs_resource_extns.user_id%type;
1835
1836 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
1837 l_source_name jtf_rs_resource_extns.source_name%type;
1838 l_source_number jtf_rs_resource_extns.source_number%type;
1839 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
1840 l_source_email jtf_rs_resource_extns.source_email%type;
1841 l_source_phone jtf_rs_resource_extns.source_phone%type;
1842 l_source_org_id jtf_rs_resource_extns.source_org_id%type;
1843 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
1844 l_source_address1 jtf_rs_resource_extns.source_address1%type;
1845 l_source_address2 jtf_rs_resource_extns.source_address2%type;
1846 l_source_address3 jtf_rs_resource_extns.source_address3%type;
1847 l_source_address4 jtf_rs_resource_extns.source_address4%type;
1848 l_source_city jtf_rs_resource_extns.source_city%type;
1849 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
1850 l_source_state jtf_rs_resource_extns.source_state%type;
1851 l_source_province jtf_rs_resource_extns.source_province%type;
1852 l_source_county jtf_rs_resource_extns.source_county%type;
1853 l_source_country jtf_rs_resource_extns.source_country%type;
1854 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
1855 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
1856 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
1857 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
1858 l_source_first_name jtf_rs_resource_extns.source_first_name%type ;
1859 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type ;
1860 l_source_last_name jtf_rs_resource_extns.source_last_name%type ;
1861 l_source_category jtf_rs_resource_extns.source_category%type ;
1862 l_source_status jtf_rs_resource_extns.source_status%type ;
1863 l_source_office jtf_rs_resource_extns.source_office%type ;
1864 l_source_location jtf_rs_resource_extns.source_location%type ;
1865 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type ;
1866 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type ;
1867 l_source_pager jtf_rs_resource_extns.source_pager%type ;
1868 l_user_name jtf_rs_resource_extns.user_name%type ;
1869
1870
1871 rr_old_rec rr_old_cur%rowtype;
1872 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
1873 l_row_id varchar2(24) := null;
1874
1875 --other variables
1876 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
1877 l_api_version CONSTANT NUMBER :=1.0;
1878 l_date Date := sysdate;
1879 l_user_id Number := 1;
1880 l_login_id Number := 1;
1881
1882
1883 BEGIN
1884
1885 --Standard Start of API SAVEPOINT
1886 SAVEPOINT RESOURCE_EXTN_AUDIT;
1887
1888 x_return_status := fnd_api.g_ret_sts_success;
1889
1890 --Standard Call to check API compatibility
1891 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1892 THEN
1893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1894 END IF;
1895
1896 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1897 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1898 THEN
1899 FND_MSG_PUB.Initialize;
1900 END IF;
1901
1902 l_date := sysdate;
1903 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1904 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1905
1906
1907 open rr_old_cur(p_resource_id);
1908 FETCH rr_old_cur into rr_old_rec;
1909 close rr_old_cur;
1910
1911 l_category := rr_old_rec.category;
1912 l_resource_number := rr_old_rec.resource_number;
1913 l_source_id := rr_old_rec.source_id;
1914 l_address_id := rr_old_rec.address_id;
1915 l_contact_id := rr_old_rec.contact_id;
1916 l_managing_emp_id := rr_old_rec.managing_employee_id;
1917 l_start_date_active := rr_old_rec.start_date_active;
1918 l_end_date_active := rr_old_rec.end_date_active;
1919 l_time_zone := rr_old_rec.time_zone;
1920 l_cost_per_hr := rr_old_rec.cost_per_hr;
1921 l_primary_language := rr_old_rec.primary_language;
1922 l_secondary_language := rr_old_rec.secondary_language;
1923 l_support_site_id := rr_old_rec.support_site_id;
1924 l_ies_agent_login := rr_old_rec.ies_agent_login;
1925 l_server_group_id := rr_old_rec.server_group_id;
1926 l_assigned_to_group_id := rr_old_rec.assigned_to_group_id;
1927 l_cost_center := rr_old_rec.cost_center;
1928 l_charge_to_cost_center := rr_old_rec.charge_to_cost_center;
1929 l_comp_currency_code := rr_old_rec.compensation_currency_code ;
1930 l_commissionable_flag := rr_old_rec.commissionable_flag;
1931 l_hold_reason_code := rr_old_rec.hold_reason_code;
1932 l_hold_payment := rr_old_rec.hold_payment;
1933 l_comp_service_team_id := rr_old_rec.comp_service_team_id;
1934 --l_location := rr_old_rec.location;
1935 l_transaction_number := rr_old_rec.transaction_number;
1936 l_object_version_number := rr_old_rec.object_version_number;
1937 l_user_id_o := rr_old_rec.user_id;
1938 l_resource_name := rr_old_rec.resource_name;
1939 l_source_name := rr_old_rec.source_name;
1940 l_source_number := rr_old_rec.source_number;
1941 l_source_job_title := rr_old_rec.source_job_title;
1942 l_source_email := rr_old_rec.source_email;
1943 l_source_phone := rr_old_rec.source_phone;
1944 l_source_org_id := rr_old_rec.source_org_id;
1945 l_source_org_name := rr_old_rec.source_org_name;
1946 l_source_address1 := rr_old_rec.source_address1;
1947 l_source_address2 := rr_old_rec.source_address2;
1948 l_source_address3 := rr_old_rec.source_address3;
1949 l_source_address4 := rr_old_rec.source_address4;
1950 l_source_city := rr_old_rec.source_city;
1951 l_source_postal_code := rr_old_rec.source_postal_code;
1952 l_source_state := rr_old_rec.source_state;
1953 l_source_province := rr_old_rec.source_province;
1954 l_source_county := rr_old_rec.source_county;
1955 l_source_country := rr_old_rec.source_country;
1956 l_source_mgr_id := rr_old_rec.source_mgr_id;
1957 l_source_mgr_name := rr_old_rec.source_mgr_name;
1958 l_source_business_grp_id := rr_old_rec.source_business_grp_id;
1959 l_source_business_grp_name := rr_old_rec.source_business_grp_name;
1960 l_source_first_name := rr_old_rec.source_first_name;
1961 l_source_middle_name := rr_old_rec.source_middle_name;
1962 l_source_last_name := rr_old_rec.source_last_name;
1963 l_source_category := rr_old_rec.source_category;
1964 l_source_status := rr_old_rec.source_status;
1965 l_source_office := rr_old_rec.source_office;
1966 l_source_location := rr_old_rec.source_location;
1967 l_source_mailstop := rr_old_rec.source_mailstop;
1968 l_source_mobile_phone := rr_old_rec.source_mobile_phone;
1969 l_source_pager := rr_old_rec.source_pager;
1970 l_user_name := rr_old_rec.user_name;
1971
1972
1973 select jtf_rs_resource_extn_aud_s.nextval
1974 into l_resource_extn_aud_id
1975 from dual;
1976
1977 --CALL TABLE HANDLER
1978 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
1979 X_ROWID => l_row_id,
1980 x_resource_audit_id => l_resource_extn_aud_id,
1981 x_resource_id => p_resource_id,
1982 x_new_category => null,
1983 x_old_category => l_category,
1984 x_new_resource_number => null,
1985 x_old_resource_number => l_resource_number ,
1986 x_new_source_id => null,
1987 x_old_source_id => l_source_id,
1988 x_new_address_id => null,
1989 x_old_address_id => l_address_id,
1990 x_new_contact_id => null,
1991 x_old_contact_id => l_contact_id,
1992 x_new_managing_employee_id => null,
1993 x_old_managing_employee_id => l_managing_emp_id,
1994 x_new_start_date_active => null,
1995 x_old_start_date_active => l_start_date_active,
1996 x_new_end_date_active => null,
1997 x_old_end_date_active => l_end_date_active,
1998 x_new_time_zone => null,
1999 x_old_time_zone => l_time_zone,
2000 x_new_cost_per_hr => null,
2001 x_old_cost_per_hr => l_cost_per_hr,
2002 x_new_primary_language => null,
2003 x_old_primary_language =>l_primary_language,
2004 x_new_secondary_language => null,
2005 x_old_secondary_language => l_secondary_language,
2006 x_new_support_site_id => null,
2007 x_old_support_site_id => l_support_site_id,
2008 x_new_ies_agent_login => null,
2009 x_old_ies_agent_login => l_ies_agent_login,
2010 x_new_server_group_id => null,
2011 x_old_server_group_id => l_server_group_id,
2012 x_new_assigned_to_group_id => null,
2013 x_old_assigned_to_group_id =>l_assigned_to_group_id,
2014 x_new_cost_center => null,
2015 x_old_cost_center => l_cost_center,
2016 x_new_charge_to_cost_center => null,
2017 x_old_charge_to_cost_center => l_charge_to_cost_center,
2018 x_new_compensation_currency_co => null,
2019 x_old_compensation_currency_co => l_comp_currency_code,
2020 x_new_commissionable_flag => null,
2021 x_old_commissionable_flag => l_commissionable_flag,
2022 x_new_hold_reason_code => null,
2023 x_old_hold_reason_code => l_hold_reason_code,
2024 x_new_hold_payment => null,
2025 x_old_hold_payment => l_hold_payment,
2026 x_new_comp_service_team_id => null,
2027 x_old_comp_service_team_id => l_comp_service_team_id,
2028 x_new_transaction_number => null,
2029 x_old_transaction_number => l_transaction_number,
2030 x_new_object_version_number => null,
2031 x_old_object_version_number => l_object_version_number,
2032 x_new_user_id => null,
2033 x_old_user_id => l_user_id_o,
2034 --x_old_location => L_location,
2035 --x_new_location => null,
2036 X_NEW_RESOURCE_NAME => null,
2037 X_OLD_RESOURCE_NAME => l_resource_name,
2038 X_NEW_SOURCE_NAME => null,
2039 X_OLD_SOURCE_NAME => l_source_name,
2040 X_NEW_SOURCE_NUMBER => null,
2041 X_OLD_SOURCE_NUMBER => l_source_number,
2042 X_NEW_SOURCE_JOB_TITLE => null,
2043 X_OLD_SOURCE_JOB_TITLE => l_source_job_title,
2044 X_NEW_SOURCE_EMAIL => null,
2045 X_OLD_SOURCE_EMAIL => l_source_email,
2046 X_NEW_SOURCE_PHONE => null,
2047 X_OLD_SOURCE_PHONE => l_source_phone,
2048 X_NEW_SOURCE_ORG_ID => null,
2049 X_OLD_SOURCE_ORG_ID => l_source_org_id,
2050 X_NEW_SOURCE_ORG_NAME => null,
2051 X_OLD_SOURCE_ORG_NAME => l_source_org_name,
2052 X_NEW_SOURCE_ADDRESS1 => null,
2053 X_OLD_SOURCE_ADDRESS1 => l_source_address1,
2054 X_NEW_SOURCE_ADDRESS2 => null,
2055 X_OLD_SOURCE_ADDRESS2 => l_source_address2,
2056 X_NEW_SOURCE_ADDRESS3 => null,
2057 X_OLD_SOURCE_ADDRESS3 => l_source_address3,
2058 X_NEW_SOURCE_ADDRESS4 => null,
2059 X_OLD_SOURCE_ADDRESS4 => l_source_address4,
2060 X_NEW_SOURCE_CITY => null,
2061 X_OLD_SOURCE_CITY => l_source_city,
2062 X_NEW_SOURCE_POSTAL_CODE => null,
2063 X_OLD_SOURCE_POSTAL_CODE => l_source_postal_code,
2064 X_NEW_SOURCE_STATE => null,
2065 X_OLD_SOURCE_STATE => l_source_state,
2066 X_NEW_SOURCE_PROVINCE => null,
2067 X_OLD_SOURCE_PROVINCE => l_source_province,
2068 X_NEW_SOURCE_COUNTY => null,
2069 X_OLD_SOURCE_COUNTY => l_source_county,
2070 X_NEW_SOURCE_COUNTRY => null,
2071 X_OLD_SOURCE_COUNTRY => l_source_country,
2072 X_NEW_SOURCE_MGR_ID => null,
2073 X_OLD_SOURCE_MGR_ID => l_source_mgr_id,
2074 X_NEW_SOURCE_MGR_NAME => null,
2075 X_OLD_SOURCE_MGR_NAME => l_source_mgr_name,
2076 X_NEW_SOURCE_BUSINESS_GRP_ID => null,
2077 X_OLD_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id,
2078 X_NEW_SOURCE_BUSINESS_GRP_NAME => null,
2079 X_OLD_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name,
2080 X_NEW_SOURCE_FIRST_NAME => null,
2081 X_OLD_SOURCE_FIRST_NAME => l_source_first_name,
2082 X_NEW_SOURCE_MIDDLE_NAME => null,
2083 X_OLD_SOURCE_MIDDLE_NAME => l_source_middle_name,
2084 X_NEW_SOURCE_LAST_NAME => null,
2085 X_OLD_SOURCE_LAST_NAME => l_source_last_name,
2086 X_NEW_SOURCE_CATEGORY => null,
2087 X_OLD_SOURCE_CATEGORY => l_source_category,
2088 X_NEW_SOURCE_STATUS => null,
2089 X_OLD_SOURCE_STATUS => l_source_status,
2090 X_NEW_SOURCE_OFFICE => null,
2091 X_OLD_SOURCE_OFFICE => l_source_office,
2092 X_NEW_SOURCE_LOCATION => null,
2093 X_OLD_SOURCE_LOCATION => l_source_location,
2094 X_NEW_SOURCE_MAILSTOP => null,
2095 X_OLD_SOURCE_MAILSTOP => l_source_mailstop,
2096 X_NEW_USER_NAME => null,
2097 X_OLD_USER_NAME => l_user_name,
2098 X_CREATION_DATE => l_date,
2099 X_CREATED_BY => l_user_id,
2100 X_LAST_UPDATE_DATE => l_date,
2101 X_LAST_UPDATED_BY => l_user_id,
2102 X_LAST_UPDATE_LOGIN => l_login_id,
2103 X_NEW_SOURCE_MOBILE_PHONE => null,
2104 X_OLD_SOURCE_MOBILE_PHONE => l_source_mobile_phone,
2105 X_NEW_SOURCE_PAGER => null,
2106 X_OLD_SOURCE_PAGER => l_source_pager
2107 );
2108
2109
2110 IF fnd_api.to_boolean (p_commit)
2111 THEN
2112 COMMIT WORK;
2113 END IF;
2114
2115 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2116
2117
2118 EXCEPTION
2119 WHEN fnd_api.g_exc_unexpected_error
2120 THEN
2121 ROLLBACK TO resource_extn_audit;
2122 x_return_status := fnd_api.g_ret_sts_unexp_error;
2123 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
2124 FND_MSG_PUB.add;
2125 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2126 WHEN OTHERS
2127 THEN
2128 ROLLBACK TO resource_extn_audit;
2129 x_return_status := fnd_api.g_ret_sts_unexp_error;
2130 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
2131 FND_MSG_PUB.add;
2132 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2133
2134
2135 END DELETE_RESOURCE;
2136 END; -- Package Body JTF_RS_RESOURCE_EXTNS_AUD_PVT