[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.2 2010/08/30 06:30:01 rgokavar 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 | Modification History :
887 |
888 | Sudhir Gokavarapu ER9864816 - FS_SETUP_COMPLETE information is added.
889 |
890 */
891
892 /*UPDATE for Resource Synchronization */
893 PROCEDURE UPDATE_RESOURCE(
894 P_API_VERSION IN NUMBER,
895 P_INIT_MSG_LIST IN VARCHAR2,
896 P_COMMIT IN VARCHAR2,
897 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
898 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
899 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
900 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
901 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
902 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
903 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
904 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
905 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
906 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
907 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
908 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
909 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
910 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
911 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
912 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
913 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
914 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
915 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
916 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
917 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
918 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
919 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
920 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
921 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
922 P_TRANSACTION_NUMBER IN NUMBER,
923 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
924 P_OBJECT_VERSION_NUMBER IN NUMBER,
925 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
926 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
927 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
928 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
929 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
930 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
931 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE,
932 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
933 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
934 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
935 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
936 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
937 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
938 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
939 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
940 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
941 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
942 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
943 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
944 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
945 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
946 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
947 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
948 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
949 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
950 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
951 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
952 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
953 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
954 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
955 P_USER_NAME IN JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE,
956 P_PARTY_ID IN JTF_RS_RESOURCE_EXTNS.PERSON_PARTY_ID%TYPE,
957 P_SOURCE_JOB_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_ID%TYPE,
958 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
959 X_MSG_COUNT OUT NOCOPY NUMBER,
960 X_MSG_DATA OUT NOCOPY VARCHAR2,
961 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
962 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
963 P_FS_SETUP_COMPLETE IN JTF_RS_RESOURCE_EXTNS.FS_SETUP_COMPLETE%TYPE )
964 IS
965
966 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
967 IS
968 SELECT category
969 ,resource_number
970 ,source_id
971 ,address_id
972 ,contact_id
973 ,managing_employee_id
974 ,start_date_active
975 ,end_date_active
976 ,time_zone
977 ,cost_per_hr
978 ,primary_language
979 ,secondary_language
980 ,support_site_id
981 ,ies_agent_login
982 ,server_group_id
983 ,assigned_to_group_id
984 ,cost_center
985 ,charge_to_cost_center
986 ,compensation_currency_code
987 ,commissionable_flag
988 ,hold_reason_code
989 ,hold_payment
990 ,comp_service_team_id
991 ,transaction_number
992 ,object_version_number
993 --,location
994 , user_id
995 , RESOURCE_NAME
996 , SOURCE_NAME
997 , SOURCE_NUMBER
998 , SOURCE_JOB_TITLE
999 , SOURCE_EMAIL
1000 , SOURCE_PHONE
1001 , SOURCE_ORG_ID
1002 , SOURCE_ORG_NAME
1003 , SOURCE_ADDRESS1
1004 , SOURCE_ADDRESS2
1005 , SOURCE_ADDRESS3
1006 , SOURCE_ADDRESS4
1007 , SOURCE_CITY
1008 , SOURCE_POSTAL_CODE
1009 , SOURCE_STATE
1010 , SOURCE_PROVINCE
1011 , SOURCE_COUNTY
1012 , SOURCE_COUNTRY
1013 , SOURCE_MGR_ID
1014 , SOURCE_MGR_NAME
1015 , SOURCE_BUSINESS_GRP_ID
1016 , SOURCE_BUSINESS_GRP_NAME
1017 , SOURCE_FIRST_NAME
1018 , SOURCE_MIDDLE_NAME
1019 , SOURCE_LAST_NAME
1020 , SOURCE_CATEGORY
1021 , SOURCE_STATUS
1022 , SOURCE_OFFICE
1023 , SOURCE_LOCATION
1024 , SOURCE_MAILSTOP
1025 , USER_NAME
1026 , SOURCE_JOB_ID
1027 , PERSON_PARTY_ID
1028 , SOURCE_MOBILE_PHONE
1029 , SOURCE_PAGER
1030 , FS_SETUP_COMPLETE
1031 FROM jtf_rs_resource_extns_vl
1032 WHERE resource_id = l_resource_id;
1033
1034
1035 --declare variables
1036 --old value
1037 l_resource_number jtf_rs_resource_extns.resource_number%type;
1038 l_category jtf_rs_resource_extns.category%type;
1039 l_source_id jtf_rs_resource_extns.source_id%type ;
1040 l_address_id jtf_rs_resource_extns.address_id%type ;
1041 l_contact_id jtf_rs_resource_extns.contact_id%type ;
1042 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%type ;
1043 l_start_date_active jtf_rs_resource_extns.start_date_active%type;
1044 l_end_date_active jtf_rs_resource_extns.end_date_active%type ;
1045 l_time_zone jtf_rs_resource_extns.time_zone%type ;
1046 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type ;
1047 l_primary_language jtf_rs_resource_extns.primary_language%type ;
1048 l_secondary_language jtf_rs_resource_extns.secondary_language%type ;
1049 l_support_site_id jtf_rs_resource_extns.support_site_id%type ;
1050 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%type ;
1051 l_server_group_id jtf_rs_resource_extns.server_group_id%type ;
1052 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%type ;
1053 l_cost_center jtf_rs_resource_extns.cost_center%type ;
1054 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%type ;
1055 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%type ;
1056 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%type ;
1057 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%type ;
1058 l_hold_payment jtf_rs_resource_extns.hold_payment%type ;
1059 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%type ;
1060 --l_location mdsys.sdo_geometry ;
1061 l_transaction_number number;
1062 l_user_id_o jtf_rs_resource_extns.user_id%type;
1063 l_object_version_number number;
1064
1065 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
1066 l_source_name jtf_rs_resource_extns.source_name%type;
1067 l_source_number jtf_rs_resource_extns.source_number%type;
1068 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
1069 l_source_email jtf_rs_resource_extns.source_email%type;
1070 l_source_phone jtf_rs_resource_extns.source_phone%type;
1071 l_source_org_id jtf_rs_resource_extns.source_org_id%type;
1072 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
1073 l_source_address1 jtf_rs_resource_extns.source_address1%type;
1074 l_source_address2 jtf_rs_resource_extns.source_address2%type;
1075 l_source_address3 jtf_rs_resource_extns.source_address3%type;
1076 l_source_address4 jtf_rs_resource_extns.source_address4%type;
1077 l_source_city jtf_rs_resource_extns.source_city%type;
1078 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
1079 l_source_state jtf_rs_resource_extns.source_state%type;
1080 l_source_province jtf_rs_resource_extns.source_province%type;
1081 l_source_county jtf_rs_resource_extns.source_county%type;
1082 l_source_country jtf_rs_resource_extns.source_country%type;
1083 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
1084 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
1085 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
1086 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
1087 l_source_first_name jtf_rs_resource_extns.source_first_name%type;
1088 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type;
1089 l_source_last_name jtf_rs_resource_extns.source_last_name%type;
1090 l_source_category jtf_rs_resource_extns.source_category%type;
1091 l_source_status jtf_rs_resource_extns.source_status%type;
1092 l_source_office jtf_rs_resource_extns.source_office%type;
1093 l_source_location jtf_rs_resource_extns.source_location%type;
1094 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
1095 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
1096 l_source_pager jtf_rs_resource_extns.source_pager%type;
1097 l_user_name jtf_rs_resource_extns.user_name%type;
1098 l_source_job_id jtf_rs_resource_extns.source_job_id%type;
1099 l_party_id jtf_rs_resource_extns.person_party_id%type;
1100 l_fs_setup_complete jtf_rs_resource_extns.fs_setup_complete%type;
1101
1102 --new values
1103 l_resource_number_n jtf_rs_resource_extns.resource_number%type;
1104 l_category_n jtf_rs_resource_extns.category%type;
1105 l_source_id_n jtf_rs_resource_extns.source_id%type ;
1106 l_address_id_n jtf_rs_resource_extns.address_id%type ;
1107 l_contact_id_n jtf_rs_resource_extns.contact_id%type ;
1108 l_managing_emp_id_n jtf_rs_resource_extns.managing_employee_id%type ;
1109 l_start_date_active_n jtf_rs_resource_extns.start_date_active%type;
1110 l_end_date_active_n jtf_rs_resource_extns.end_date_active%type ;
1111 l_time_zone_n jtf_rs_resource_extns.time_zone%type ;
1112 l_cost_per_hr_n jtf_rs_resource_extns.cost_per_hr%type ;
1113 l_primary_language_n jtf_rs_resource_extns.primary_language%type ;
1114 l_secondary_language_n jtf_rs_resource_extns.secondary_language%type ;
1115 l_support_site_id_n jtf_rs_resource_extns.support_site_id%type ;
1116 l_ies_agent_login_n jtf_rs_resource_extns.ies_agent_login%type ;
1117 l_server_group_id_n jtf_rs_resource_extns.server_group_id%type ;
1118 l_assigned_to_group_id_n jtf_rs_resource_extns.assigned_to_group_id%type ;
1119 l_cost_center_n jtf_rs_resource_extns.cost_center%type ;
1120 l_charge_to_cost_center_n jtf_rs_resource_extns.charge_to_cost_center%type ;
1121 l_comp_currency_code_n jtf_rs_resource_extns.compensation_currency_code%type ;
1122 l_commissionable_flag_n jtf_rs_resource_extns.commissionable_flag%type ;
1123 l_hold_reason_code_n jtf_rs_resource_extns.hold_reason_code%type ;
1124 l_hold_payment_n jtf_rs_resource_extns.hold_payment%type ;
1125 l_comp_service_team_id_n jtf_rs_resource_extns.comp_service_team_id%type ;
1126 --l_location_n mdsys.sdo_geometry ;
1127 l_transaction_number_n number;
1128 l_user_id_n jtf_rs_resource_extns.user_id%type;
1129 l_object_version_number_n number;
1130
1131 l_resource_name_n jtf_rs_resource_extns_tl.resource_name%type;
1132 l_source_name_n jtf_rs_resource_extns.source_name%type;
1133 l_source_number_n jtf_rs_resource_extns.source_number%type;
1134 l_source_job_title_n jtf_rs_resource_extns.source_job_title%type;
1135 l_source_email_n jtf_rs_resource_extns.source_email%type;
1136 l_source_phone_n jtf_rs_resource_extns.source_phone%type;
1137 l_source_org_id_n jtf_rs_resource_extns.source_org_id%type;
1138 l_source_org_name_n jtf_rs_resource_extns.source_org_name%type;
1139 l_source_address1_n jtf_rs_resource_extns.source_address1%type;
1140 l_source_address2_n jtf_rs_resource_extns.source_address2%type;
1141 l_source_address3_n jtf_rs_resource_extns.source_address3%type;
1142 l_source_address4_n jtf_rs_resource_extns.source_address4%type;
1143 l_source_city_n jtf_rs_resource_extns.source_city%type;
1144 l_source_postal_code_n jtf_rs_resource_extns.source_postal_code%type;
1145 l_source_state_n jtf_rs_resource_extns.source_state%type;
1146 l_source_province_n jtf_rs_resource_extns.source_province%type;
1147 l_source_county_n jtf_rs_resource_extns.source_county%type;
1148 l_source_country_n jtf_rs_resource_extns.source_country%type;
1149 l_source_mgr_id_n jtf_rs_resource_extns.source_mgr_id%type;
1150 l_source_mgr_name_n jtf_rs_resource_extns.source_mgr_name%type;
1151 l_source_business_grp_id_n jtf_rs_resource_extns.source_business_grp_id%type;
1152 l_source_business_grp_name_n jtf_rs_resource_extns.source_business_grp_name%type;
1153 l_source_first_name_n jtf_rs_resource_extns.source_first_name%type;
1154 l_source_middle_name_n jtf_rs_resource_extns.source_middle_name%type;
1155 l_source_last_name_n jtf_rs_resource_extns.source_last_name%type;
1156 l_source_category_n jtf_rs_resource_extns.source_category%type;
1157 l_source_status_n jtf_rs_resource_extns.source_status%type;
1158 l_source_office_n jtf_rs_resource_extns.source_office%type;
1159 l_source_location_n jtf_rs_resource_extns.source_location%type;
1160 l_source_mailstop_n jtf_rs_resource_extns.source_mailstop%type;
1161 l_source_mobile_phone_n jtf_rs_resource_extns.source_mobile_phone%type;
1162 l_source_pager_n jtf_rs_resource_extns.source_pager%type;
1163 l_user_name_n jtf_rs_resource_extns.user_name%type;
1164 l_source_job_id_n jtf_rs_resource_extns.source_job_id%type;
1165 l_party_id_n jtf_rs_resource_extns.person_party_id%type;
1166 l_fs_setup_complete_n jtf_rs_resource_extns.fs_setup_complete%type;
1167
1168 rr_old_rec rr_old_cur%rowtype;
1169 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
1170 l_row_id varchar2(24) := null;
1171 l_dummy varchar2(10) := 'S';
1172
1173 --other variables
1174 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
1175 l_api_version CONSTANT NUMBER :=1.0;
1176 l_date Date := sysdate;
1177 l_user_id Number := 1;
1178 l_login_id Number := 1;
1179
1180
1181 BEGIN
1182
1183 --Standard Start of API SAVEPOINT
1184 SAVEPOINT RESOURCE_EXTN_AUDIT;
1185
1186 x_return_status := fnd_api.g_ret_sts_success;
1187
1188 --Standard Call to check API compatibility
1189 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1190 THEN
1191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1192 END IF;
1193
1194 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1195 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1196 THEN
1197 FND_MSG_PUB.Initialize;
1198 END IF;
1199
1200 l_date := sysdate;
1201 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1202 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1203
1204
1205 open rr_old_cur(p_resource_id);
1206 FETCH rr_old_cur into rr_old_rec;
1207 close rr_old_cur;
1208
1209 if nvl(p_category,fnd_api.g_miss_char) <> NVL(rr_old_rec.category,fnd_api.g_miss_char)
1210 then
1211 l_category := rr_old_rec.category;
1212 l_category_n := p_category;
1213 end if;
1214 if nvl(p_resource_number,fnd_api.g_miss_char) <> nvl(rr_old_rec.resource_number, fnd_api.g_miss_char)
1215 then
1216 l_resource_number := rr_old_rec.resource_number;
1217 l_resource_number_n:= p_resource_number;
1218 end if;
1219 if nvl(p_source_id, -1) <> nvl(rr_old_rec.source_id, -1)
1220 then
1221 l_source_id := rr_old_rec.source_id ;
1222 l_source_id_n := p_source_id ;
1223 end if;
1224 if nvl(p_address_id, -1) <> nvl(rr_old_rec.address_id, -1)
1225 then
1226 l_address_id := rr_old_rec.address_id;
1227 l_address_id_n := p_address_id ;
1228 end if;
1229 if nvl(p_contact_id, -1) <> nvl(rr_old_rec.contact_id , -1)
1230 then
1231 l_contact_id := rr_old_rec.contact_id;
1232 l_contact_id_n := p_contact_id ;
1233 end if;
1234 if nvl(p_managing_emp_id, -1) <> nvl(rr_old_rec.managing_employee_id , -1)
1235 then
1236 l_managing_emp_id := rr_old_rec.managing_employee_id ;
1237 l_managing_emp_id_n := p_managing_emp_id ;
1238 end if;
1239 if p_start_date_active <> rr_old_rec.start_date_active
1240 then
1241 l_start_date_active := rr_old_rec.start_date_active;
1242 l_start_date_active_n := p_start_date_active ;
1243 end if;
1244 if nvl(p_end_date_active,fnd_api.g_miss_date)<> nvl(rr_old_rec.end_date_active,fnd_api.g_miss_date)
1245 then
1246 l_end_date_active := rr_old_rec.end_date_active;
1247 l_end_date_active_n := p_end_date_active ;
1248 end if;
1249 if nvl(p_time_zone, -1) <> nvl(rr_old_rec.time_zone, -1)
1250 then
1251 l_time_zone := rr_old_rec.time_zone;
1252 l_time_zone_n := p_time_zone ;
1253 end if;
1254 if nvl(p_cost_per_hr, -1) <> nvl(rr_old_rec.cost_per_hr, -1)
1255 then
1256 l_cost_per_hr := rr_old_rec.cost_per_hr;
1257 l_cost_per_hr_n := p_cost_per_hr ;
1258 end if;
1259 if nvl(p_primary_language, fnd_api.g_miss_char) <> nvl(rr_old_rec.primary_language,fnd_api.g_miss_char)
1260 then
1261 l_primary_language := rr_old_rec.primary_language;
1262 l_primary_language_n := p_primary_language;
1263 end if;
1264 if nvl(p_secondary_language,fnd_api.g_miss_char) <> nvl(rr_old_rec.secondary_language, fnd_api.g_miss_char)
1265 then
1266 l_secondary_language := rr_old_rec.secondary_language;
1267 l_secondary_language_n := p_secondary_language;
1268 end if;
1269 if nvl(p_support_site_id,fnd_api.g_miss_num) <> nvl(rr_old_rec.support_site_id,fnd_api.g_miss_num)
1270 then
1271 l_support_site_id := rr_old_rec.support_site_id;
1272 l_support_site_id_n := p_support_site_id ;
1273 end if;
1274 if nvl(p_ies_agent_login, fnd_api.g_miss_char)<> nvl(rr_old_rec.ies_agent_login, fnd_api.g_miss_char)
1275 then
1276 l_ies_agent_login := rr_old_rec.ies_agent_login;
1277 l_ies_agent_login_n := p_ies_agent_login ;
1278 end if;
1279 if nvl(p_server_group_id ,fnd_api.g_miss_num) <> nvl(rr_old_rec.server_group_id, fnd_api.g_miss_num)
1280 then
1281 l_server_group_id := rr_old_rec.server_group_id;
1282 l_server_group_id_n := p_server_group_id ;
1283 end if;
1284 if nvl(p_server_group_id, -1) <> nvl(rr_old_rec.server_group_id, -1)
1285 then
1286 l_server_group_id := rr_old_rec.server_group_id;
1287 l_server_group_id_n := p_server_group_id ;
1288 end if;
1289 if nvl(p_assigned_to_group_id, -1) <> nvl(rr_old_rec.assigned_to_group_id, -1)
1290 then
1291 l_assigned_to_group_id := rr_old_rec.assigned_to_group_id;
1292 l_assigned_to_group_id_n := p_assigned_to_group_id;
1293 end if;
1294 if nvl(p_cost_center,fnd_api.g_miss_char) <> nvl(rr_old_rec.cost_center, fnd_api.g_miss_char)
1295 then
1296 l_cost_center := rr_old_rec.cost_center;
1297 l_cost_center_n := p_cost_center ;
1298 end if;
1299 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)
1300 then
1301 l_charge_to_cost_center := rr_old_rec.charge_to_cost_center;
1302 l_charge_to_cost_center_n := p_charge_to_cost_center ;
1303 end if;
1304 if nvl(p_comp_currency_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.compensation_currency_code,fnd_api.g_miss_char)
1305 then
1306 l_comp_currency_code := rr_old_rec.compensation_currency_code;
1307 l_comp_currency_code_n := p_comp_currency_code ;
1308 end if;
1309 if nvl(p_commissionable_flag,fnd_api.g_miss_char) <> nvl(rr_old_rec.commissionable_flag , fnd_api.g_miss_char)
1310 then
1311 l_commissionable_flag := rr_old_rec.commissionable_flag;
1312 l_commissionable_flag_n := p_commissionable_flag ;
1313 end if;
1314 if nvl(p_hold_reason_code,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_reason_code, fnd_api.g_miss_char)
1315 then
1316 l_hold_reason_code := rr_old_rec.hold_reason_code;
1317 l_hold_reason_code_n := p_hold_reason_code ;
1318 end if;
1319 if nvl(p_hold_payment,fnd_api.g_miss_char) <> nvl(rr_old_rec.hold_payment, fnd_api.g_miss_char)
1320 then
1321 l_hold_payment:= rr_old_rec.hold_payment;
1322 l_hold_payment_n := p_hold_payment ;
1323 end if;
1324 if nvl(p_comp_service_team_id, -1) <> nvl(rr_old_rec.comp_service_team_id, -1)
1325 then
1326 l_comp_service_team_id := rr_old_rec.comp_service_team_id;
1327 l_comp_service_team_id_n := p_comp_service_team_id ;
1328 end if;
1329
1330 /*if(p_location.sdo_gtype <> rr_old_rec.location.sdo_gtype
1331 OR p_location.sdo_srid <> rr_old_rec.location.sdo_srid
1332 OR p_location.sdo_point.x <> rr_old_rec.location.sdo_point.x
1333 OR p_location.sdo_point.y <> rr_old_rec.location.sdo_point.y
1334 OR p_location.sdo_point.z <> rr_old_rec.location.sdo_point.z)
1335 then
1336 l_location := rr_old_rec.location ;
1337 l_location_n := p_location ;
1338 end if; */
1339 if nvl(p_transaction_number, -1) <> nvl(rr_old_rec.transaction_number, -1)
1340 then
1341 l_transaction_number := rr_old_rec.transaction_number;
1342 l_transaction_number_n := p_transaction_number;
1343 end if;
1344 if nvl(p_object_version_number, -1) <> nvl(rr_old_rec.object_version_number, -1)
1345 then
1346 l_object_version_number := rr_old_rec.object_version_number;
1347 l_object_version_number_n := p_object_version_number;
1348 end if;
1349 if nvl(p_user_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.user_id, fnd_api.g_miss_num)
1350 then
1351 l_user_id_o := rr_old_rec.user_id;
1352 l_user_id_n := p_user_id;
1353 end if;
1354
1355 if nvl(p_resource_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.resource_name, fnd_api.g_miss_char)
1356 then
1357 l_resource_name := rr_old_rec.resource_name;
1358 l_resource_name_n := p_resource_name;
1359 end if;
1360
1361 if nvl(p_source_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_name, fnd_api.g_miss_char)
1362 then
1363 l_source_name := rr_old_rec.source_name;
1364 l_source_name_n := p_source_name;
1365 end if;
1366
1367 if nvl(p_source_job_title, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_job_title, fnd_api.g_miss_char)
1368 then
1369 l_source_job_title := rr_old_rec.source_job_title;
1370 l_source_job_title_n := p_source_job_title;
1371 end if;
1372
1373 if nvl(p_source_email, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_email, fnd_api.g_miss_char)
1374 then
1375 l_source_email := rr_old_rec.source_email;
1376 l_source_email_n := p_source_email;
1377 end if;
1378
1379 if nvl(p_source_number, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_number, fnd_api.g_miss_char)
1380 then
1381 l_source_number := rr_old_rec.source_number;
1382 l_source_number_n := p_source_number;
1383 end if;
1384
1385 if nvl(p_source_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_phone, fnd_api.g_miss_char)
1386 then
1387 l_source_phone := rr_old_rec.source_phone;
1388 l_source_phone_n := p_source_phone;
1389 end if;
1390
1391 if nvl(p_source_org_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_org_id, fnd_api.g_miss_num)
1392 then
1393 l_source_org_id := rr_old_rec.source_org_id;
1394 l_source_org_id_n := p_source_org_id;
1395 end if;
1396
1397 if nvl(p_source_org_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_org_name, fnd_api.g_miss_char)
1398 then
1399 l_source_org_name := rr_old_rec.source_org_name;
1400 l_source_org_name_n := p_source_org_name;
1401 end if;
1402
1403 if nvl(p_source_address1, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address1, fnd_api.g_miss_char)
1404 then
1405 l_source_address1 := rr_old_rec.source_address1;
1406 l_source_address1_n := p_source_address1;
1407 end if;
1408
1409 if nvl(p_source_address2, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address2, fnd_api.g_miss_char)
1410 then
1411 l_source_address2 := rr_old_rec.source_address2;
1412 l_source_address2_n := p_source_address2;
1413 end if;
1414
1415 if nvl(p_source_address3, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address3, fnd_api.g_miss_char)
1416 then
1417 l_source_address3 := rr_old_rec.source_address3;
1418 l_source_address3_n := p_source_address3;
1419 end if;
1420
1421 if nvl(p_source_address4, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address4, fnd_api.g_miss_char)
1422 then
1423 l_source_address4 := rr_old_rec.source_address4;
1424 l_source_address4_n := p_source_address4;
1425 end if;
1426
1427 if nvl(p_source_city, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_city, fnd_api.g_miss_char)
1428 then
1429 l_source_city := rr_old_rec.source_city;
1430 l_source_city_n := p_source_city;
1431 end if;
1432
1433 if nvl(p_source_postal_code, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_postal_code, fnd_api.g_miss_char)
1434 then
1435 l_source_postal_code := rr_old_rec.source_postal_code;
1436 l_source_postal_code_n := p_source_postal_code;
1437 end if;
1438
1439 if nvl(p_source_state, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_state, fnd_api.g_miss_char)
1440 then
1441 l_source_state := rr_old_rec.source_state;
1442 l_source_state_n := p_source_state;
1443 end if;
1444
1445 if nvl(p_source_province, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_province, fnd_api.g_miss_char)
1446 then
1447 l_source_province := rr_old_rec.source_province;
1448 l_source_province_n := p_source_province;
1449 end if;
1450
1451 if nvl(p_source_county, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_county, fnd_api.g_miss_char)
1452 then
1453 l_source_county := rr_old_rec.source_county;
1454 l_source_county_n := p_source_county;
1455 end if;
1456
1457 if nvl(p_source_country, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_country, fnd_api.g_miss_char)
1458 then
1459 l_source_country := rr_old_rec.source_country;
1460 l_source_country_n := p_source_country;
1461 end if;
1462
1463 if nvl(p_source_mgr_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_mgr_id, fnd_api.g_miss_num)
1464 then
1465 l_source_mgr_id := rr_old_rec.source_mgr_id;
1466 l_source_mgr_id_n := p_source_mgr_id;
1467 end if;
1468
1469 if nvl(p_source_mgr_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mgr_name, fnd_api.g_miss_char)
1470 then
1471 l_source_mgr_name := rr_old_rec.source_mgr_name;
1472 l_source_mgr_name_n := p_source_mgr_name;
1473 end if;
1474
1475 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)
1476 then
1477 l_source_business_grp_id := rr_old_rec.source_business_grp_id;
1478 l_source_business_grp_id_n := p_source_business_grp_id;
1479 end if;
1480
1481 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)
1482 then
1483 l_source_business_grp_name := rr_old_rec.source_business_grp_name;
1484 l_source_business_grp_name_n := p_source_business_grp_name;
1485 end if;
1486
1487 if nvl(p_source_first_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_first_name, fnd_api.g_miss_char)
1488 then
1489 l_source_first_name := rr_old_rec.source_first_name;
1490 l_source_first_name_n := p_source_first_name;
1491 end if;
1492
1493 if nvl(p_source_middle_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_middle_name, fnd_api.g_miss_char)
1494 then
1495 l_source_middle_name := rr_old_rec.source_middle_name;
1496 l_source_middle_name_n := p_source_middle_name;
1497 end if;
1498
1499 if nvl(p_source_last_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_last_name, fnd_api.g_miss_char)
1500 then
1501 l_source_last_name := rr_old_rec.source_last_name;
1502 l_source_last_name_n := p_source_last_name;
1503 end if;
1504
1505 if nvl(p_source_category, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_category, fnd_api.g_miss_char)
1506 then
1507 l_source_category := rr_old_rec.source_category;
1508 l_source_category_n := p_source_category;
1509 end if;
1510
1511 if nvl(p_source_status, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_status, fnd_api.g_miss_char)
1512 then
1513 l_source_status := rr_old_rec.source_status;
1514 l_source_status_n := p_source_status;
1515 end if;
1516
1517 if nvl(p_source_office, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_office, fnd_api.g_miss_char)
1518 then
1519 l_source_office := rr_old_rec.source_office;
1520 l_source_office_n := p_source_office;
1521 end if;
1522
1523 if nvl(p_source_location, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_location, fnd_api.g_miss_char)
1524 then
1525 l_source_location := rr_old_rec.source_location;
1526 l_source_location_n := p_source_location;
1527 end if;
1528
1529 if nvl(p_source_mailstop, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mailstop, fnd_api.g_miss_char)
1530 then
1531 l_source_mailstop := rr_old_rec.source_mailstop;
1532 l_source_mailstop_n := p_source_mailstop;
1533 end if;
1534
1535 if nvl(p_source_mobile_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mobile_phone, fnd_api.g_miss_char)
1536 then
1537 l_source_mobile_phone := rr_old_rec.source_mobile_phone;
1538 l_source_mobile_phone_n := p_source_mobile_phone;
1539 end if;
1540
1541 if nvl(p_source_pager, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_pager, fnd_api.g_miss_char)
1542 then
1543 l_source_pager := rr_old_rec.source_pager;
1544 l_source_pager_n := p_source_pager;
1545 end if;
1546
1547 if nvl(p_user_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.user_name, fnd_api.g_miss_char)
1548 then
1549 l_user_name := rr_old_rec.user_name;
1550 l_user_name_n := p_user_name;
1551 end if;
1552
1553 if nvl(p_source_job_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_job_id, fnd_api.g_miss_num)
1554 then
1555 l_source_job_id := rr_old_rec.source_job_id;
1556 l_source_job_id_n := p_source_job_id;
1557 end if;
1558
1559 if nvl(p_party_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.person_party_id, fnd_api.g_miss_num)
1560 then
1561 l_party_id := rr_old_rec.person_party_id;
1562 l_party_id_n := p_party_id;
1563 end if;
1564
1565
1566 if nvl(p_fs_setup_complete, fnd_api.g_miss_char) <> nvl(rr_old_rec.fs_setup_complete, fnd_api.g_miss_char)
1567 then
1568 l_fs_setup_complete := rr_old_rec.fs_setup_complete;
1569 l_fs_setup_complete_n := p_fs_setup_complete;
1570 end if;
1571
1572 select jtf_rs_resource_extn_aud_s.nextval
1573 into l_resource_extn_aud_id
1574 from dual;
1575
1576 /* CALL TABLE HANDLER */
1577 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
1578 X_ROWID => l_row_id,
1579 x_resource_audit_id => l_resource_extn_aud_id,
1580 x_resource_id => p_resource_id,
1581 x_new_category => l_category_n,
1582 x_old_category => l_category,
1583 x_new_resource_number => l_resource_number_n ,
1584 x_old_resource_number => l_resource_number ,
1585 x_new_source_id => l_source_id_n ,
1586 x_old_source_id => l_source_id ,
1587 x_new_address_id => l_address_id_n ,
1588 x_old_address_id => l_address_id ,
1589 x_new_contact_id => l_contact_id_n ,
1590 x_old_contact_id => l_contact_id ,
1591 x_new_managing_employee_id => l_managing_emp_id_n ,
1592 x_old_managing_employee_id => l_managing_emp_id ,
1593 x_new_start_date_active => l_start_date_active_n ,
1594 x_old_start_date_active => l_start_date_active ,
1595 x_new_end_date_active => l_end_date_active_n ,
1596 x_old_end_date_active => l_end_date_active ,
1597 x_new_time_zone => l_time_zone_n ,
1598 x_old_time_zone => l_time_zone ,
1599 x_new_cost_per_hr => l_cost_per_hr_n ,
1600 x_old_cost_per_hr => l_cost_per_hr ,
1601 x_new_primary_language => l_primary_language_n ,
1602 x_old_primary_language =>l_primary_language,
1603 x_new_secondary_language => l_secondary_language_n ,
1604 x_old_secondary_language => l_secondary_language ,
1605 x_new_support_site_id => l_support_site_id_n ,
1606 x_old_support_site_id => l_support_site_id ,
1607 x_new_ies_agent_login => l_ies_agent_login_n ,
1608 x_old_ies_agent_login => l_ies_agent_login ,
1609 x_new_server_group_id => l_server_group_id_n ,
1610 x_old_server_group_id => l_server_group_id ,
1611 x_new_assigned_to_group_id => l_assigned_to_group_id_n ,
1612 x_old_assigned_to_group_id =>l_assigned_to_group_id,
1613 x_new_cost_center => l_cost_center_n ,
1614 x_old_cost_center => l_cost_center ,
1615 x_new_charge_to_cost_center => l_charge_to_cost_center_n ,
1616 x_old_charge_to_cost_center => l_charge_to_cost_center ,
1617 x_new_compensation_currency_co => l_comp_currency_code_n ,
1618 x_old_compensation_currency_co => l_comp_currency_code ,
1619 x_new_commissionable_flag => l_commissionable_flag_n ,
1620 x_old_commissionable_flag => l_commissionable_flag ,
1621 x_new_hold_reason_code => l_hold_reason_code_n ,
1622 x_old_hold_reason_code => l_hold_reason_code ,
1623 x_new_hold_payment => l_hold_payment_n ,
1624 x_old_hold_payment => l_hold_payment ,
1625 x_new_comp_service_team_id => l_comp_service_team_id_n ,
1626 x_old_comp_service_team_id => l_comp_service_team_id ,
1627 x_new_transaction_number => l_transaction_number_n ,
1628 x_old_transaction_number => l_transaction_number ,
1629 x_new_object_version_number => l_object_version_number_n ,
1630 x_old_object_version_number => l_object_version_number ,
1631 x_new_user_id => l_user_id_n,
1632 x_old_user_id => l_user_id_o,
1633 --x_old_location => p_location,
1634 --x_new_location => p_location,
1635 X_NEW_RESOURCE_NAME => l_resource_name_n,
1636 X_OLD_RESOURCE_NAME => l_resource_name,
1637 X_NEW_SOURCE_NAME => l_source_name_n,
1638 X_OLD_SOURCE_NAME => l_source_name,
1639 X_NEW_SOURCE_NUMBER => l_source_number_n,
1640 X_OLD_SOURCE_NUMBER => l_source_number,
1641 X_NEW_SOURCE_JOB_TITLE => l_source_job_title_n,
1642 X_OLD_SOURCE_JOB_TITLE => l_source_job_title,
1643 X_NEW_SOURCE_EMAIL => l_source_email_n,
1644 X_OLD_SOURCE_EMAIL => l_source_email,
1645 X_NEW_SOURCE_PHONE => l_source_phone_n,
1646 X_OLD_SOURCE_PHONE => l_source_phone,
1647 X_NEW_SOURCE_ORG_ID => l_source_org_id_n,
1648 X_OLD_SOURCE_ORG_ID => l_source_org_id,
1649 X_NEW_SOURCE_ORG_NAME => l_source_org_name_n,
1650 X_OLD_SOURCE_ORG_NAME => l_source_org_name,
1651 X_NEW_SOURCE_ADDRESS1 => l_source_address1_n,
1652 X_OLD_SOURCE_ADDRESS1 => l_source_address1,
1653 X_NEW_SOURCE_ADDRESS2 => l_source_address2_n,
1654 X_OLD_SOURCE_ADDRESS2 => l_source_address2,
1655 X_NEW_SOURCE_ADDRESS3 => l_source_address3_n,
1656 X_OLD_SOURCE_ADDRESS3 => l_source_address3,
1657 X_NEW_SOURCE_ADDRESS4 => l_source_address4_n,
1658 X_OLD_SOURCE_ADDRESS4 => l_source_address4,
1659 X_NEW_SOURCE_CITY => l_source_city_n,
1660 X_OLD_SOURCE_CITY => l_source_city,
1661 X_NEW_SOURCE_POSTAL_CODE => l_source_postal_code_n,
1662 X_OLD_SOURCE_POSTAL_CODE => l_source_postal_code,
1663 X_NEW_SOURCE_STATE => l_source_state_n,
1664 X_OLD_SOURCE_STATE => l_source_state,
1665 X_NEW_SOURCE_PROVINCE => l_source_province_n,
1666 X_OLD_SOURCE_PROVINCE => l_source_province,
1667 X_NEW_SOURCE_COUNTY => l_source_county_n,
1668 X_OLD_SOURCE_COUNTY => l_source_county,
1669 X_NEW_SOURCE_COUNTRY => l_source_country_n,
1670 X_OLD_SOURCE_COUNTRY => l_source_country,
1671 X_NEW_SOURCE_MGR_ID => l_source_mgr_id_n,
1672 X_OLD_SOURCE_MGR_ID => l_source_mgr_id,
1673 X_NEW_SOURCE_MGR_NAME => l_source_mgr_name_n,
1674 X_OLD_SOURCE_MGR_NAME => l_source_mgr_name,
1675 X_NEW_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id_n,
1676 X_OLD_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id,
1677 X_NEW_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name_n,
1678 X_OLD_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name,
1679 X_NEW_SOURCE_FIRST_NAME => l_source_first_name_n,
1680 X_OLD_SOURCE_FIRST_NAME => l_source_first_name,
1681 X_NEW_SOURCE_MIDDLE_NAME => l_source_middle_name_n,
1682 X_OLD_SOURCE_MIDDLE_NAME => l_source_middle_name,
1683 X_NEW_SOURCE_LAST_NAME => l_source_last_name_n,
1684 X_OLD_SOURCE_LAST_NAME => l_source_last_name,
1685 X_NEW_SOURCE_CATEGORY => l_source_category_n,
1686 X_OLD_SOURCE_CATEGORY => l_source_category,
1687 X_NEW_SOURCE_STATUS => l_source_status_n,
1688 X_OLD_SOURCE_STATUS => l_source_status,
1689 X_NEW_SOURCE_OFFICE => l_source_office_n,
1690 X_OLD_SOURCE_OFFICE => l_source_office,
1691 X_NEW_SOURCE_LOCATION => l_source_location_n,
1692 X_OLD_SOURCE_LOCATION => l_source_location,
1693 X_NEW_SOURCE_MAILSTOP => l_source_mailstop_n,
1694 X_OLD_SOURCE_MAILSTOP => l_source_mailstop,
1695 X_NEW_USER_NAME => l_user_name_n,
1696 X_OLD_USER_NAME => l_user_name,
1697 X_NEW_SOURCE_JOB_ID => l_source_job_id_n,
1698 X_OLD_SOURCE_JOB_ID => l_source_job_id,
1699 X_NEW_PARTY_ID => l_party_id_n,
1700 X_OLD_PARTY_ID => l_party_id,
1701 X_CREATION_DATE => l_date,
1702 X_CREATED_BY => l_user_id,
1703 X_LAST_UPDATE_DATE => l_date,
1704 X_LAST_UPDATED_BY => l_user_id,
1705 X_LAST_UPDATE_LOGIN => l_login_id,
1706 X_NEW_SOURCE_MOBILE_PHONE => l_source_mobile_phone_n,
1707 X_OLD_SOURCE_MOBILE_PHONE => l_source_mobile_phone,
1708 X_NEW_SOURCE_PAGER => l_source_pager_n,
1709 X_OLD_SOURCE_PAGER => l_source_pager,
1710 X_NEW_FS_SETUP_COMPLETE => l_fs_setup_complete_n,
1711 X_OLD_FS_SETUP_COMPLETE => l_fs_setup_complete
1712 );
1713
1714
1715 IF fnd_api.to_boolean (p_commit)
1716 THEN
1717 COMMIT WORK;
1718 END IF;
1719
1720 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1721
1722
1723 EXCEPTION
1724 WHEN fnd_api.g_exc_unexpected_error
1725 THEN
1726 ROLLBACK TO resource_extn_audit;
1727 x_return_status := fnd_api.g_ret_sts_unexp_error;
1728 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
1729 FND_MSG_PUB.add;
1730 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1731 WHEN OTHERS
1732 THEN
1733 ROLLBACK TO resource_extn_audit;
1734 x_return_status := fnd_api.g_ret_sts_unexp_error;
1735 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
1736 FND_MSG_PUB.add;
1737 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1738
1739
1740 END UPDATE_RESOURCE;
1741
1742
1743 /* DELETE procedure modified for Resource Synchronization */
1744
1745 PROCEDURE DELETE_RESOURCE(
1746 P_API_VERSION IN NUMBER,
1747 P_INIT_MSG_LIST IN VARCHAR2,
1748 P_COMMIT IN VARCHAR2,
1749 P_RESOURCE_ID IN NUMBER,
1750 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1751 X_MSG_COUNT OUT NOCOPY NUMBER,
1752 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1753 IS
1754
1755 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
1756 IS
1757 SELECT category
1758 ,resource_number
1759 ,source_id
1760 ,address_id
1761 ,contact_id
1762 ,managing_employee_id
1763 ,start_date_active
1764 ,end_date_active
1765 ,time_zone
1766 ,cost_per_hr
1767 ,primary_language
1768 ,secondary_language
1769 ,support_site_id
1770 ,ies_agent_login
1771 ,server_group_id
1772 ,assigned_to_group_id
1773 ,cost_center
1774 ,charge_to_cost_center
1775 ,compensation_currency_code
1776 ,commissionable_flag
1777 ,hold_reason_code
1778 ,hold_payment
1779 ,comp_service_team_id
1780 ,transaction_number
1781 ,object_version_number
1782 -- ,location
1783 ,user_id
1784 , RESOURCE_NAME
1785 , SOURCE_NAME
1786 , SOURCE_NUMBER
1787 , SOURCE_JOB_TITLE
1788 , SOURCE_EMAIL
1789 , SOURCE_PHONE
1790 , SOURCE_ORG_ID
1791 , SOURCE_ORG_NAME
1792 , SOURCE_ADDRESS1
1793 , SOURCE_ADDRESS2
1794 , SOURCE_ADDRESS3
1795 , SOURCE_ADDRESS4
1796 , SOURCE_CITY
1797 , SOURCE_POSTAL_CODE
1798 , SOURCE_STATE
1799 , SOURCE_PROVINCE
1800 , SOURCE_COUNTY
1801 , SOURCE_COUNTRY
1802 , SOURCE_MGR_ID
1803 , SOURCE_MGR_NAME
1804 , SOURCE_BUSINESS_GRP_ID
1805 , SOURCE_BUSINESS_GRP_NAME
1806 , SOURCE_FIRST_NAME
1807 , SOURCE_LAST_NAME
1808 , SOURCE_MIDDLE_NAME
1809 , SOURCE_CATEGORY
1810 , SOURCE_STATUS
1811 , SOURCE_OFFICE
1812 , SOURCE_LOCATION
1813 , SOURCE_MAILSTOP
1814 , USER_NAME
1815 , SOURCE_MOBILE_PHONE
1816 , SOURCE_PAGER
1817 FROM jtf_rs_resource_extns_vl
1818 WHERE resource_id = l_resource_id;
1819
1820 --declare variables
1821 --old value
1822 l_resource_number jtf_rs_resource_extns.resource_number%type;
1823 l_category jtf_rs_resource_extns.category%type;
1824 l_source_id jtf_rs_resource_extns.source_id%type ;
1825 l_address_id jtf_rs_resource_extns.address_id%type ;
1826 l_contact_id jtf_rs_resource_extns.contact_id%type ;
1827 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%type ;
1828 l_start_date_active jtf_rs_resource_extns.start_date_active%type;
1829 l_end_date_active jtf_rs_resource_extns.end_date_active%type ;
1830 l_time_zone jtf_rs_resource_extns.time_zone%type ;
1831 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type ;
1832 l_primary_language jtf_rs_resource_extns.primary_language%type ;
1833 l_secondary_language jtf_rs_resource_extns.secondary_language%type ;
1834 l_support_site_id jtf_rs_resource_extns.support_site_id%type ;
1835 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%type ;
1836 l_server_group_id jtf_rs_resource_extns.server_group_id%type ;
1837 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%type ;
1838 l_cost_center jtf_rs_resource_extns.cost_center%type ;
1839 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%type ;
1840 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%type ;
1841 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%type ;
1842 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%type ;
1843 l_hold_payment jtf_rs_resource_extns.hold_payment%type ;
1844 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%type ;
1845 --l_location mdsys.sdo_geometry ;
1846 l_transaction_number number;
1847 l_object_version_number number;
1848 l_user_id_o jtf_rs_resource_extns.user_id%type;
1849
1850 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
1851 l_source_name jtf_rs_resource_extns.source_name%type;
1852 l_source_number jtf_rs_resource_extns.source_number%type;
1853 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
1854 l_source_email jtf_rs_resource_extns.source_email%type;
1855 l_source_phone jtf_rs_resource_extns.source_phone%type;
1856 l_source_org_id jtf_rs_resource_extns.source_org_id%type;
1857 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
1858 l_source_address1 jtf_rs_resource_extns.source_address1%type;
1859 l_source_address2 jtf_rs_resource_extns.source_address2%type;
1860 l_source_address3 jtf_rs_resource_extns.source_address3%type;
1861 l_source_address4 jtf_rs_resource_extns.source_address4%type;
1862 l_source_city jtf_rs_resource_extns.source_city%type;
1863 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
1864 l_source_state jtf_rs_resource_extns.source_state%type;
1865 l_source_province jtf_rs_resource_extns.source_province%type;
1866 l_source_county jtf_rs_resource_extns.source_county%type;
1867 l_source_country jtf_rs_resource_extns.source_country%type;
1868 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
1869 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
1870 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
1871 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
1872 l_source_first_name jtf_rs_resource_extns.source_first_name%type ;
1873 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type ;
1874 l_source_last_name jtf_rs_resource_extns.source_last_name%type ;
1875 l_source_category jtf_rs_resource_extns.source_category%type ;
1876 l_source_status jtf_rs_resource_extns.source_status%type ;
1877 l_source_office jtf_rs_resource_extns.source_office%type ;
1878 l_source_location jtf_rs_resource_extns.source_location%type ;
1879 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type ;
1880 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type ;
1881 l_source_pager jtf_rs_resource_extns.source_pager%type ;
1882 l_user_name jtf_rs_resource_extns.user_name%type ;
1883
1884
1885 rr_old_rec rr_old_cur%rowtype;
1886 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
1887 l_row_id varchar2(24) := null;
1888
1889 --other variables
1890 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
1891 l_api_version CONSTANT NUMBER :=1.0;
1892 l_date Date := sysdate;
1893 l_user_id Number := 1;
1894 l_login_id Number := 1;
1895
1896
1897 BEGIN
1898
1899 --Standard Start of API SAVEPOINT
1900 SAVEPOINT RESOURCE_EXTN_AUDIT;
1901
1902 x_return_status := fnd_api.g_ret_sts_success;
1903
1904 --Standard Call to check API compatibility
1905 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1906 THEN
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908 END IF;
1909
1910 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1911 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1912 THEN
1913 FND_MSG_PUB.Initialize;
1914 END IF;
1915
1916 l_date := sysdate;
1917 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1918 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1919
1920
1921 open rr_old_cur(p_resource_id);
1922 FETCH rr_old_cur into rr_old_rec;
1923 close rr_old_cur;
1924
1925 l_category := rr_old_rec.category;
1926 l_resource_number := rr_old_rec.resource_number;
1927 l_source_id := rr_old_rec.source_id;
1928 l_address_id := rr_old_rec.address_id;
1929 l_contact_id := rr_old_rec.contact_id;
1930 l_managing_emp_id := rr_old_rec.managing_employee_id;
1931 l_start_date_active := rr_old_rec.start_date_active;
1932 l_end_date_active := rr_old_rec.end_date_active;
1933 l_time_zone := rr_old_rec.time_zone;
1934 l_cost_per_hr := rr_old_rec.cost_per_hr;
1935 l_primary_language := rr_old_rec.primary_language;
1936 l_secondary_language := rr_old_rec.secondary_language;
1937 l_support_site_id := rr_old_rec.support_site_id;
1938 l_ies_agent_login := rr_old_rec.ies_agent_login;
1939 l_server_group_id := rr_old_rec.server_group_id;
1940 l_assigned_to_group_id := rr_old_rec.assigned_to_group_id;
1941 l_cost_center := rr_old_rec.cost_center;
1942 l_charge_to_cost_center := rr_old_rec.charge_to_cost_center;
1943 l_comp_currency_code := rr_old_rec.compensation_currency_code ;
1944 l_commissionable_flag := rr_old_rec.commissionable_flag;
1945 l_hold_reason_code := rr_old_rec.hold_reason_code;
1946 l_hold_payment := rr_old_rec.hold_payment;
1947 l_comp_service_team_id := rr_old_rec.comp_service_team_id;
1948 --l_location := rr_old_rec.location;
1949 l_transaction_number := rr_old_rec.transaction_number;
1950 l_object_version_number := rr_old_rec.object_version_number;
1951 l_user_id_o := rr_old_rec.user_id;
1952 l_resource_name := rr_old_rec.resource_name;
1953 l_source_name := rr_old_rec.source_name;
1954 l_source_number := rr_old_rec.source_number;
1955 l_source_job_title := rr_old_rec.source_job_title;
1956 l_source_email := rr_old_rec.source_email;
1957 l_source_phone := rr_old_rec.source_phone;
1958 l_source_org_id := rr_old_rec.source_org_id;
1959 l_source_org_name := rr_old_rec.source_org_name;
1960 l_source_address1 := rr_old_rec.source_address1;
1961 l_source_address2 := rr_old_rec.source_address2;
1962 l_source_address3 := rr_old_rec.source_address3;
1963 l_source_address4 := rr_old_rec.source_address4;
1964 l_source_city := rr_old_rec.source_city;
1965 l_source_postal_code := rr_old_rec.source_postal_code;
1966 l_source_state := rr_old_rec.source_state;
1967 l_source_province := rr_old_rec.source_province;
1968 l_source_county := rr_old_rec.source_county;
1969 l_source_country := rr_old_rec.source_country;
1970 l_source_mgr_id := rr_old_rec.source_mgr_id;
1971 l_source_mgr_name := rr_old_rec.source_mgr_name;
1972 l_source_business_grp_id := rr_old_rec.source_business_grp_id;
1973 l_source_business_grp_name := rr_old_rec.source_business_grp_name;
1974 l_source_first_name := rr_old_rec.source_first_name;
1975 l_source_middle_name := rr_old_rec.source_middle_name;
1976 l_source_last_name := rr_old_rec.source_last_name;
1977 l_source_category := rr_old_rec.source_category;
1978 l_source_status := rr_old_rec.source_status;
1979 l_source_office := rr_old_rec.source_office;
1980 l_source_location := rr_old_rec.source_location;
1981 l_source_mailstop := rr_old_rec.source_mailstop;
1982 l_source_mobile_phone := rr_old_rec.source_mobile_phone;
1983 l_source_pager := rr_old_rec.source_pager;
1984 l_user_name := rr_old_rec.user_name;
1985
1986
1987 select jtf_rs_resource_extn_aud_s.nextval
1988 into l_resource_extn_aud_id
1989 from dual;
1990
1991 --CALL TABLE HANDLER
1992 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW (
1993 X_ROWID => l_row_id,
1994 x_resource_audit_id => l_resource_extn_aud_id,
1995 x_resource_id => p_resource_id,
1996 x_new_category => null,
1997 x_old_category => l_category,
1998 x_new_resource_number => null,
1999 x_old_resource_number => l_resource_number ,
2000 x_new_source_id => null,
2001 x_old_source_id => l_source_id,
2002 x_new_address_id => null,
2003 x_old_address_id => l_address_id,
2004 x_new_contact_id => null,
2005 x_old_contact_id => l_contact_id,
2006 x_new_managing_employee_id => null,
2007 x_old_managing_employee_id => l_managing_emp_id,
2008 x_new_start_date_active => null,
2009 x_old_start_date_active => l_start_date_active,
2010 x_new_end_date_active => null,
2011 x_old_end_date_active => l_end_date_active,
2012 x_new_time_zone => null,
2013 x_old_time_zone => l_time_zone,
2014 x_new_cost_per_hr => null,
2015 x_old_cost_per_hr => l_cost_per_hr,
2016 x_new_primary_language => null,
2017 x_old_primary_language =>l_primary_language,
2018 x_new_secondary_language => null,
2019 x_old_secondary_language => l_secondary_language,
2020 x_new_support_site_id => null,
2021 x_old_support_site_id => l_support_site_id,
2022 x_new_ies_agent_login => null,
2023 x_old_ies_agent_login => l_ies_agent_login,
2024 x_new_server_group_id => null,
2025 x_old_server_group_id => l_server_group_id,
2026 x_new_assigned_to_group_id => null,
2027 x_old_assigned_to_group_id =>l_assigned_to_group_id,
2028 x_new_cost_center => null,
2029 x_old_cost_center => l_cost_center,
2030 x_new_charge_to_cost_center => null,
2031 x_old_charge_to_cost_center => l_charge_to_cost_center,
2032 x_new_compensation_currency_co => null,
2033 x_old_compensation_currency_co => l_comp_currency_code,
2034 x_new_commissionable_flag => null,
2035 x_old_commissionable_flag => l_commissionable_flag,
2036 x_new_hold_reason_code => null,
2037 x_old_hold_reason_code => l_hold_reason_code,
2038 x_new_hold_payment => null,
2039 x_old_hold_payment => l_hold_payment,
2040 x_new_comp_service_team_id => null,
2041 x_old_comp_service_team_id => l_comp_service_team_id,
2042 x_new_transaction_number => null,
2043 x_old_transaction_number => l_transaction_number,
2044 x_new_object_version_number => null,
2045 x_old_object_version_number => l_object_version_number,
2046 x_new_user_id => null,
2047 x_old_user_id => l_user_id_o,
2048 --x_old_location => L_location,
2049 --x_new_location => null,
2050 X_NEW_RESOURCE_NAME => null,
2051 X_OLD_RESOURCE_NAME => l_resource_name,
2052 X_NEW_SOURCE_NAME => null,
2053 X_OLD_SOURCE_NAME => l_source_name,
2054 X_NEW_SOURCE_NUMBER => null,
2055 X_OLD_SOURCE_NUMBER => l_source_number,
2056 X_NEW_SOURCE_JOB_TITLE => null,
2057 X_OLD_SOURCE_JOB_TITLE => l_source_job_title,
2058 X_NEW_SOURCE_EMAIL => null,
2059 X_OLD_SOURCE_EMAIL => l_source_email,
2060 X_NEW_SOURCE_PHONE => null,
2061 X_OLD_SOURCE_PHONE => l_source_phone,
2062 X_NEW_SOURCE_ORG_ID => null,
2063 X_OLD_SOURCE_ORG_ID => l_source_org_id,
2064 X_NEW_SOURCE_ORG_NAME => null,
2065 X_OLD_SOURCE_ORG_NAME => l_source_org_name,
2066 X_NEW_SOURCE_ADDRESS1 => null,
2067 X_OLD_SOURCE_ADDRESS1 => l_source_address1,
2068 X_NEW_SOURCE_ADDRESS2 => null,
2069 X_OLD_SOURCE_ADDRESS2 => l_source_address2,
2070 X_NEW_SOURCE_ADDRESS3 => null,
2071 X_OLD_SOURCE_ADDRESS3 => l_source_address3,
2072 X_NEW_SOURCE_ADDRESS4 => null,
2073 X_OLD_SOURCE_ADDRESS4 => l_source_address4,
2074 X_NEW_SOURCE_CITY => null,
2075 X_OLD_SOURCE_CITY => l_source_city,
2076 X_NEW_SOURCE_POSTAL_CODE => null,
2077 X_OLD_SOURCE_POSTAL_CODE => l_source_postal_code,
2078 X_NEW_SOURCE_STATE => null,
2079 X_OLD_SOURCE_STATE => l_source_state,
2080 X_NEW_SOURCE_PROVINCE => null,
2081 X_OLD_SOURCE_PROVINCE => l_source_province,
2082 X_NEW_SOURCE_COUNTY => null,
2083 X_OLD_SOURCE_COUNTY => l_source_county,
2084 X_NEW_SOURCE_COUNTRY => null,
2085 X_OLD_SOURCE_COUNTRY => l_source_country,
2086 X_NEW_SOURCE_MGR_ID => null,
2087 X_OLD_SOURCE_MGR_ID => l_source_mgr_id,
2088 X_NEW_SOURCE_MGR_NAME => null,
2089 X_OLD_SOURCE_MGR_NAME => l_source_mgr_name,
2090 X_NEW_SOURCE_BUSINESS_GRP_ID => null,
2091 X_OLD_SOURCE_BUSINESS_GRP_ID => l_source_business_grp_id,
2092 X_NEW_SOURCE_BUSINESS_GRP_NAME => null,
2093 X_OLD_SOURCE_BUSINESS_GRP_NAME => l_source_business_grp_name,
2094 X_NEW_SOURCE_FIRST_NAME => null,
2095 X_OLD_SOURCE_FIRST_NAME => l_source_first_name,
2096 X_NEW_SOURCE_MIDDLE_NAME => null,
2097 X_OLD_SOURCE_MIDDLE_NAME => l_source_middle_name,
2098 X_NEW_SOURCE_LAST_NAME => null,
2099 X_OLD_SOURCE_LAST_NAME => l_source_last_name,
2100 X_NEW_SOURCE_CATEGORY => null,
2101 X_OLD_SOURCE_CATEGORY => l_source_category,
2102 X_NEW_SOURCE_STATUS => null,
2103 X_OLD_SOURCE_STATUS => l_source_status,
2104 X_NEW_SOURCE_OFFICE => null,
2105 X_OLD_SOURCE_OFFICE => l_source_office,
2106 X_NEW_SOURCE_LOCATION => null,
2107 X_OLD_SOURCE_LOCATION => l_source_location,
2108 X_NEW_SOURCE_MAILSTOP => null,
2109 X_OLD_SOURCE_MAILSTOP => l_source_mailstop,
2110 X_NEW_USER_NAME => null,
2111 X_OLD_USER_NAME => l_user_name,
2112 X_CREATION_DATE => l_date,
2113 X_CREATED_BY => l_user_id,
2114 X_LAST_UPDATE_DATE => l_date,
2115 X_LAST_UPDATED_BY => l_user_id,
2116 X_LAST_UPDATE_LOGIN => l_login_id,
2117 X_NEW_SOURCE_MOBILE_PHONE => null,
2118 X_OLD_SOURCE_MOBILE_PHONE => l_source_mobile_phone,
2119 X_NEW_SOURCE_PAGER => null,
2120 X_OLD_SOURCE_PAGER => l_source_pager
2121 );
2122
2123
2124 IF fnd_api.to_boolean (p_commit)
2125 THEN
2126 COMMIT WORK;
2127 END IF;
2128
2129 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2130
2131
2132 EXCEPTION
2133 WHEN fnd_api.g_exc_unexpected_error
2134 THEN
2135 ROLLBACK TO resource_extn_audit;
2136 x_return_status := fnd_api.g_ret_sts_unexp_error;
2137 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
2138 FND_MSG_PUB.add;
2139 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2140 WHEN OTHERS
2141 THEN
2142 ROLLBACK TO resource_extn_audit;
2143 x_return_status := fnd_api.g_ret_sts_unexp_error;
2144 fnd_message.set_name ('JTF', 'JTF_RS_RES_AUD_AUD_ERR');
2145 FND_MSG_PUB.add;
2146 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2147
2148
2149 END DELETE_RESOURCE;
2150 END; -- Package Body JTF_RS_RESOURCE_EXTNS_AUD_PVT