eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JTF_RS_RESOURCE_EXTNS_PKG

Source


1 package body JTF_RS_RESOURCE_EXTNS_PKG as
2 /* $Header: jtfrsteb.pls 120.0 2005/05/11 08:22:10 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_RESOURCE_ID in NUMBER,
6   X_ATTRIBUTE12 in VARCHAR2,
7   X_ATTRIBUTE13 in VARCHAR2,
8   X_ATTRIBUTE14 in VARCHAR2,
9   X_ATTRIBUTE15 in VARCHAR2,
10   X_ATTRIBUTE_CATEGORY in VARCHAR2,
11   X_ATTRIBUTE11 in VARCHAR2,
12   X_ATTRIBUTE7 in VARCHAR2,
13   X_ATTRIBUTE8 in VARCHAR2,
14   X_ATTRIBUTE9 in VARCHAR2,
15   X_ATTRIBUTE10 in VARCHAR2,
16   X_CATEGORY in VARCHAR2,
17   X_RESOURCE_NUMBER in VARCHAR2,
18   X_SOURCE_ID in NUMBER,
19   X_ADDRESS_ID in NUMBER,
20   X_CONTACT_ID in NUMBER,
21   X_MANAGING_EMPLOYEE_ID in NUMBER,
22   X_START_DATE_ACTIVE in DATE,
23   X_END_DATE_ACTIVE in DATE,
24   X_TIME_ZONE in NUMBER,
25   X_COST_PER_HR in NUMBER,
26   X_PRIMARY_LANGUAGE in VARCHAR2,
27   X_SECONDARY_LANGUAGE in VARCHAR2,
28   X_SUPPORT_SITE_ID in NUMBER,
29   X_IES_AGENT_LOGIN in VARCHAR2,
30   X_SERVER_GROUP_ID in NUMBER,
31   X_ASSIGNED_TO_GROUP_ID in NUMBER,
32   X_COST_CENTER in VARCHAR2,
33   X_CHARGE_TO_COST_CENTER in VARCHAR2,
34   X_COMPENSATION_CURRENCY_CODE in VARCHAR2,
35   X_COMMISSIONABLE_FLAG in VARCHAR2,
36   X_HOLD_REASON_CODE in VARCHAR2,
37   X_HOLD_PAYMENT in VARCHAR2,
38   X_COMP_SERVICE_TEAM_ID in NUMBER,
39   X_TRANSACTION_NUMBER in NUMBER,
40   X_USER_ID in NUMBER,
41   --X_LOCATION in MDSYS.SDO_GEOMETRY,
42   X_ATTRIBUTE2 in VARCHAR2,
43   X_ATTRIBUTE3 in VARCHAR2,
44   X_ATTRIBUTE4 in VARCHAR2,
45   X_ATTRIBUTE5 in VARCHAR2,
46   X_ATTRIBUTE6 in VARCHAR2,
47   X_ATTRIBUTE1 in VARCHAR2,
48   X_CREATION_DATE in DATE,
49   X_CREATED_BY in NUMBER,
50   X_LAST_UPDATE_DATE in DATE,
51   X_LAST_UPDATED_BY in NUMBER,
52   X_LAST_UPDATE_LOGIN in NUMBER,
53   X_RESOURCE_NAME   in  VARCHAR2,
54   X_SOURCE_NAME in   VARCHAR2,
55   X_SOURCE_NUMBER   in  VARCHAR2,
56   X_SOURCE_JOB_TITLE  in   VARCHAR2,
57   X_SOURCE_EMAIL  in   VARCHAR2,
58   X_SOURCE_PHONE  in   VARCHAR2,
59   X_SOURCE_ORG_ID in   NUMBER,
60   X_SOURCE_ORG_NAME  in VARCHAR2,
61   X_SOURCE_ADDRESS1  in    VARCHAR2,
62   X_SOURCE_ADDRESS2  in  VARCHAR2,
63   X_SOURCE_ADDRESS3  in  VARCHAR2,
64   X_SOURCE_ADDRESS4  in  VARCHAR2,
65   X_SOURCE_CITY     in  VARCHAR2,
66   X_SOURCE_POSTAL_CODE  in  VARCHAR2,
67   X_SOURCE_STATE       in  VARCHAR2,
68   X_SOURCE_PROVINCE     in  VARCHAR2,
69   X_SOURCE_COUNTY      in   VARCHAR2,
70   X_SOURCE_COUNTRY     in   VARCHAR2,
71   X_SOURCE_MGR_ID       in  NUMBER,
72   X_SOURCE_MGR_NAME       in  VARCHAR2,
73   X_SOURCE_BUSINESS_GRP_ID       in  NUMBER,
74   X_SOURCE_BUSINESS_GRP_NAME     in   VARCHAR2,
75   X_SOURCE_FIRST_NAME       in  VARCHAR2,
76   X_SOURCE_LAST_NAME        in  VARCHAR2,
77   X_SOURCE_MIDDLE_NAME     in  VARCHAR2,
78   X_SOURCE_CATEGORY        in  VARCHAR2,
79   X_SOURCE_STATUS          in  VARCHAR2,
80   X_SOURCE_OFFICE          in  VARCHAR2,
81   X_SOURCE_LOCATION          in  VARCHAR2,
82   X_SOURCE_MAILSTOP          in  VARCHAR2,
83   X_USER_NAME              in  VARCHAR2,
84   X_SOURCE_JOB_ID              in  VARCHAR2,
85   X_PARTY_ID              in  VARCHAR2,
86   X_SOURCE_MOBILE_PHONE          in  VARCHAR2,
87   X_SOURCE_PAGER          in  VARCHAR2
88 ) is
89   cursor C is select ROWID from JTF_RS_RESOURCE_EXTNS
90     where RESOURCE_ID = X_RESOURCE_ID
91     ;
92 begin
93   insert into JTF_RS_RESOURCE_EXTNS (
94     ATTRIBUTE12,
95     ATTRIBUTE13,
96     ATTRIBUTE14,
97     ATTRIBUTE15,
98     ATTRIBUTE_CATEGORY,
99     ATTRIBUTE11,
100     ATTRIBUTE7,
101     ATTRIBUTE8,
102     ATTRIBUTE9,
103     ATTRIBUTE10,
104     LAST_UPDATE_DATE,
105     LAST_UPDATE_LOGIN,
106     CATEGORY,
107     RESOURCE_NUMBER,
108     SOURCE_ID,
109     ADDRESS_ID,
110     CONTACT_ID,
111     MANAGING_EMPLOYEE_ID,
112     START_DATE_ACTIVE,
113     END_DATE_ACTIVE,
114     TIME_ZONE,
115     COST_PER_HR,
116     PRIMARY_LANGUAGE,
117     SECONDARY_LANGUAGE,
118     SUPPORT_SITE_ID,
119     IES_AGENT_LOGIN,
120     SERVER_GROUP_ID,
121     ASSIGNED_TO_GROUP_ID,
122     COST_CENTER,
123     CHARGE_TO_COST_CENTER,
124     COMPENSATION_CURRENCY_CODE,
125     COMMISSIONABLE_FLAG,
126     HOLD_REASON_CODE,
127     HOLD_PAYMENT,
128     COMP_SERVICE_TEAM_ID,
129     TRANSACTION_NUMBER,
130     USER_ID,
131     --LOCATION,
132     OBJECT_VERSION_NUMBER,
133     ATTRIBUTE1,
134     ATTRIBUTE2,
135     ATTRIBUTE3,
136     ATTRIBUTE4,
137     ATTRIBUTE5,
138     ATTRIBUTE6,
139     RESOURCE_ID,
140     CREATED_BY,
141     CREATION_DATE,
142     LAST_UPDATED_BY,
143     SOURCE_NAME     ,
144     SOURCE_NUMBER    ,
145     SOURCE_JOB_TITLE  ,
146     SOURCE_EMAIL     ,
147     SOURCE_PHONE    ,
148     SOURCE_ORG_ID   ,
149     SOURCE_ORG_NAME ,
150     SOURCE_ADDRESS1 ,
151     SOURCE_ADDRESS2 ,
152     SOURCE_ADDRESS3 ,
153     SOURCE_ADDRESS4 ,
154     SOURCE_CITY      ,
155     SOURCE_POSTAL_CODE,
156     SOURCE_STATE      ,
157     SOURCE_PROVINCE   ,
158     SOURCE_COUNTY     ,
159     SOURCE_COUNTRY  ,
160     SOURCE_MGR_ID,
161     SOURCE_MGR_NAME,
162     SOURCE_BUSINESS_GRP_ID,
163     SOURCE_BUSINESS_GRP_NAME,
164     SOURCE_FIRST_NAME,
165     SOURCE_LAST_NAME,
166     SOURCE_MIDDLE_NAME,
167     SOURCE_CATEGORY,
168     SOURCE_STATUS,
169     SOURCE_OFFICE,
170     SOURCE_LOCATION,
171     SOURCE_MAILSTOP,
172     USER_NAME,
173     SOURCE_JOB_ID,
174     PERSON_PARTY_ID,
175     SOURCE_MOBILE_PHONE,
176     SOURCE_PAGER
177   ) values (
178     X_ATTRIBUTE12,
179     X_ATTRIBUTE13,
180     X_ATTRIBUTE14,
181     X_ATTRIBUTE15,
182     X_ATTRIBUTE_CATEGORY,
183     X_ATTRIBUTE11,
184     X_ATTRIBUTE7,
185     X_ATTRIBUTE8,
186     X_ATTRIBUTE9,
187     X_ATTRIBUTE10,
188     X_LAST_UPDATE_DATE,
189     X_LAST_UPDATE_LOGIN,
190     X_CATEGORY,
191     X_RESOURCE_NUMBER,
192     X_SOURCE_ID,
193     X_ADDRESS_ID,
194     X_CONTACT_ID,
195     X_MANAGING_EMPLOYEE_ID,
196     X_START_DATE_ACTIVE,
197     X_END_DATE_ACTIVE,
198     X_TIME_ZONE,
199     X_COST_PER_HR,
200     X_PRIMARY_LANGUAGE,
201     X_SECONDARY_LANGUAGE,
202     X_SUPPORT_SITE_ID,
203     X_IES_AGENT_LOGIN,
204     X_SERVER_GROUP_ID,
205     X_ASSIGNED_TO_GROUP_ID,
206     X_COST_CENTER,
207     X_CHARGE_TO_COST_CENTER,
208     X_COMPENSATION_CURRENCY_CODE,
209     X_COMMISSIONABLE_FLAG,
210     X_HOLD_REASON_CODE,
211     nvl(X_HOLD_PAYMENT,'N'), -- Bug Number 1717900
212     X_COMP_SERVICE_TEAM_ID,
213     X_TRANSACTION_NUMBER,
214     X_USER_ID,
215     --X_LOCATION,
216     1,
217     X_ATTRIBUTE1,
218     X_ATTRIBUTE2,
219     X_ATTRIBUTE3,
220     X_ATTRIBUTE4,
221     X_ATTRIBUTE5,
222     X_ATTRIBUTE6,
223     X_RESOURCE_ID,
224     X_CREATED_BY,
225     X_CREATION_DATE,
226     X_LAST_UPDATED_BY,
227     X_SOURCE_NAME     ,
228     X_SOURCE_NUMBER    ,
229     X_SOURCE_JOB_TITLE  ,
230     X_SOURCE_EMAIL     ,
231     X_SOURCE_PHONE    ,
232     X_SOURCE_ORG_ID   ,
233     X_SOURCE_ORG_NAME ,
234     X_SOURCE_ADDRESS1 ,
235     X_SOURCE_ADDRESS2 ,
236     X_SOURCE_ADDRESS3 ,
237     X_SOURCE_ADDRESS4 ,
238     X_SOURCE_CITY      ,
239     X_SOURCE_POSTAL_CODE,
240     X_SOURCE_STATE      ,
241     X_SOURCE_PROVINCE   ,
242     X_SOURCE_COUNTY     ,
243     X_SOURCE_COUNTRY      ,
244     X_SOURCE_MGR_ID,
245     X_SOURCE_MGR_NAME,
246     X_SOURCE_BUSINESS_GRP_ID,
247     X_SOURCE_BUSINESS_GRP_NAME,
248     X_SOURCE_FIRST_NAME,
249     X_SOURCE_LAST_NAME,
250     X_SOURCE_MIDDLE_NAME,
251     X_SOURCE_CATEGORY,
252     X_SOURCE_STATUS,
253     X_SOURCE_OFFICE,
254     X_SOURCE_LOCATION,
255     X_SOURCE_MAILSTOP,
256     X_USER_NAME,
257     X_SOURCE_JOB_ID,
258     X_PARTY_ID,
259     X_SOURCE_MOBILE_PHONE,
260     X_SOURCE_PAGER
261     );
262 
263   insert into JTF_RS_RESOURCE_EXTNS_TL (
264     RESOURCE_ID,
265     CATEGORY,
266     CREATED_BY,
267     CREATION_DATE,
268     LAST_UPDATED_BY,
269     LAST_UPDATE_DATE,
270     LAST_UPDATE_LOGIN,
271     RESOURCE_NAME,
272     LANGUAGE,
273     SOURCE_LANG
274   ) select
275     X_RESOURCE_ID,
276     X_CATEGORY,
277     X_CREATED_BY,
278     X_CREATION_DATE,
279     X_LAST_UPDATED_BY,
280     X_LAST_UPDATE_DATE,
281     X_LAST_UPDATE_LOGIN,
282     X_RESOURCE_NAME,
283     L.LANGUAGE_CODE,
284     userenv('LANG')
285   from FND_LANGUAGES L
286   where L.INSTALLED_FLAG in ('I', 'B')
287   and not exists
288     (select NULL
289     from JTF_RS_RESOURCE_EXTNS_TL T
290     where T.RESOURCE_ID = X_RESOURCE_ID
291     and T.LANGUAGE = L.LANGUAGE_CODE);
292 
293   open c;
294   fetch c into X_ROWID;
295   if (c%notfound) then
296     close c;
297     raise no_data_found;
298   end if;
299   close c;
300 
301 end INSERT_ROW;
302 
303 procedure LOCK_ROW (
304   X_RESOURCE_ID in NUMBER,
305   X_OBJECT_VERSION_NUMBER in NUMBER
306 ) is
307   cursor c1 is select
308       OBJECT_VERSION_NUMBER
309     from JTF_RS_RESOURCE_EXTNS_VL
310     where RESOURCE_ID = X_RESOURCE_ID
311     for update of RESOURCE_ID nowait;
312     tlinfo c1%rowtype ;
313 begin
314         open c1;
315         fetch c1 into tlinfo;
316         if (c1%notfound) then
317                 close c1;
318                 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
319             app_exception.raise_exception;
320          end if;
321          close c1;
322 
323   if (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
324   then
325     null;
326   else
327         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
328         app_exception.raise_exception;
329       end if;
330 
331 end LOCK_ROW;
332 
333 procedure UPDATE_ROW (
334   X_RESOURCE_ID in NUMBER,
335   X_ATTRIBUTE12 in VARCHAR2,
336   X_ATTRIBUTE13 in VARCHAR2,
337   X_ATTRIBUTE14 in VARCHAR2,
338   X_ATTRIBUTE15 in VARCHAR2,
339   X_ATTRIBUTE_CATEGORY in VARCHAR2,
340   X_ATTRIBUTE11 in VARCHAR2,
341   X_ATTRIBUTE7 in VARCHAR2,
342   X_ATTRIBUTE8 in VARCHAR2,
343   X_ATTRIBUTE9 in VARCHAR2,
344   X_ATTRIBUTE10 in VARCHAR2,
345   X_CATEGORY in VARCHAR2,
346   X_RESOURCE_NUMBER in VARCHAR2,
347   X_SOURCE_ID in NUMBER,
348   X_ADDRESS_ID in NUMBER,
349   X_CONTACT_ID in NUMBER,
350   X_MANAGING_EMPLOYEE_ID in NUMBER,
351   X_START_DATE_ACTIVE in DATE,
352   X_END_DATE_ACTIVE in DATE,
353   X_TIME_ZONE in NUMBER,
354   X_COST_PER_HR in NUMBER,
355   X_PRIMARY_LANGUAGE in VARCHAR2,
356   X_SECONDARY_LANGUAGE in VARCHAR2,
357   X_SUPPORT_SITE_ID in NUMBER,
358   X_IES_AGENT_LOGIN in VARCHAR2,
359   X_SERVER_GROUP_ID in NUMBER,
360   X_ASSIGNED_TO_GROUP_ID in NUMBER,
361   X_COST_CENTER in VARCHAR2,
362   X_CHARGE_TO_COST_CENTER in VARCHAR2,
363   X_COMPENSATION_CURRENCY_CODE in VARCHAR2,
364   X_COMMISSIONABLE_FLAG in VARCHAR2,
365   X_HOLD_REASON_CODE in VARCHAR2,
366   X_HOLD_PAYMENT in VARCHAR2,
367   X_COMP_SERVICE_TEAM_ID in NUMBER,
368   X_TRANSACTION_NUMBER in NUMBER,
369   X_USER_ID in NUMBER,
370   --X_LOCATION in MDSYS.SDO_GEOMETRY,
371   X_OBJECT_VERSION_NUMBER in NUMBER,
372   X_ATTRIBUTE2 in VARCHAR2,
373   X_ATTRIBUTE3 in VARCHAR2,
374   X_ATTRIBUTE4 in VARCHAR2,
375   X_ATTRIBUTE5 in VARCHAR2,
376   X_ATTRIBUTE6 in VARCHAR2,
377   X_ATTRIBUTE1 in VARCHAR2,
378   X_LAST_UPDATE_DATE in DATE,
379   X_LAST_UPDATED_BY in NUMBER,
380   X_LAST_UPDATE_LOGIN in NUMBER,
381   X_RESOURCE_NAME   in  VARCHAR2,
382   X_SOURCE_NAME in   VARCHAR2,
383   X_SOURCE_NUMBER   in  VARCHAR2,
384   X_SOURCE_JOB_TITLE  in   VARCHAR2,
385   X_SOURCE_EMAIL  in   VARCHAR2,
386   X_SOURCE_PHONE  in   VARCHAR2,
387   X_SOURCE_ORG_ID in   NUMBER,
388   X_SOURCE_ORG_NAME  in VARCHAR2,
389   X_SOURCE_ADDRESS1  in    VARCHAR2,
390   X_SOURCE_ADDRESS2  in  VARCHAR2,
391   X_SOURCE_ADDRESS3  in  VARCHAR2,
392   X_SOURCE_ADDRESS4  in  VARCHAR2,
393   X_SOURCE_CITY     in  VARCHAR2,
394   X_SOURCE_POSTAL_CODE  in  VARCHAR2,
395   X_SOURCE_STATE       in  VARCHAR2,
396   X_SOURCE_PROVINCE     in  VARCHAR2,
397   X_SOURCE_COUNTY      in   VARCHAR2,
398   X_SOURCE_COUNTRY     in   VARCHAR2,
399   X_SOURCE_MGR_ID       in  NUMBER,
400   X_SOURCE_MGR_NAME       in  VARCHAR2,
401   X_SOURCE_BUSINESS_GRP_ID       in  NUMBER,
402   X_SOURCE_BUSINESS_GRP_NAME     in   VARCHAR2,
403   X_SOURCE_FIRST_NAME       in  VARCHAR2,
404   X_SOURCE_LAST_NAME        in  VARCHAR2,
405   X_SOURCE_MIDDLE_NAME     in  VARCHAR2,
406   X_SOURCE_CATEGORY        in  VARCHAR2,
407   X_SOURCE_STATUS          in  VARCHAR2,
408   X_SOURCE_OFFICE          in  VARCHAR2,
409   X_SOURCE_LOCATION          in  VARCHAR2,
410   X_SOURCE_MAILSTOP          in  VARCHAR2,
411   X_USER_NAME              in  VARCHAR2,
412   X_SOURCE_JOB_ID              in  VARCHAR2,
413   X_PARTY_ID              in  VARCHAR2,
414   X_SOURCE_MOBILE_PHONE          in  VARCHAR2,
415   X_SOURCE_PAGER          in  VARCHAR2
416 ) is
417 begin
418   update JTF_RS_RESOURCE_EXTNS set
419     ATTRIBUTE12 = X_ATTRIBUTE12,
420     ATTRIBUTE13 = X_ATTRIBUTE13,
421     ATTRIBUTE14 = X_ATTRIBUTE14,
422     ATTRIBUTE15 = X_ATTRIBUTE15,
423     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
424     ATTRIBUTE11 = X_ATTRIBUTE11,
425     ATTRIBUTE7 = X_ATTRIBUTE7,
426     ATTRIBUTE8 = X_ATTRIBUTE8,
427     ATTRIBUTE9 = X_ATTRIBUTE9,
428     ATTRIBUTE10 = X_ATTRIBUTE10,
429     CATEGORY = X_CATEGORY,
430     RESOURCE_NUMBER = X_RESOURCE_NUMBER,
431     SOURCE_ID = X_SOURCE_ID,
432     ADDRESS_ID = X_ADDRESS_ID,
433     CONTACT_ID = X_CONTACT_ID,
434     MANAGING_EMPLOYEE_ID = X_MANAGING_EMPLOYEE_ID,
435     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
436     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
437     TIME_ZONE = X_TIME_ZONE,
438     COST_PER_HR = X_COST_PER_HR,
439     PRIMARY_LANGUAGE = X_PRIMARY_LANGUAGE,
440     SECONDARY_LANGUAGE = X_SECONDARY_LANGUAGE,
441     SUPPORT_SITE_ID = X_SUPPORT_SITE_ID,
442     IES_AGENT_LOGIN = X_IES_AGENT_LOGIN,
443     SERVER_GROUP_ID = X_SERVER_GROUP_ID,
444     ASSIGNED_TO_GROUP_ID = X_ASSIGNED_TO_GROUP_ID,
445     COST_CENTER = X_COST_CENTER,
446     CHARGE_TO_COST_CENTER = X_CHARGE_TO_COST_CENTER,
447     COMPENSATION_CURRENCY_CODE = X_COMPENSATION_CURRENCY_CODE,
448     COMMISSIONABLE_FLAG = X_COMMISSIONABLE_FLAG,
449     HOLD_REASON_CODE = X_HOLD_REASON_CODE,
450     HOLD_PAYMENT = nvl(X_HOLD_PAYMENT,'N'),
451     COMP_SERVICE_TEAM_ID = X_COMP_SERVICE_TEAM_ID,
452     TRANSACTION_NUMBER = X_TRANSACTION_NUMBER,
453     USER_ID = X_USER_ID,
454     --LOCATION = X_LOCATION,
455     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
456     ATTRIBUTE2 = X_ATTRIBUTE2,
457     ATTRIBUTE3 = X_ATTRIBUTE3,
458     ATTRIBUTE4 = X_ATTRIBUTE4,
459     ATTRIBUTE5 = X_ATTRIBUTE5,
460     ATTRIBUTE6 = X_ATTRIBUTE6,
461     ATTRIBUTE1 = X_ATTRIBUTE1,
462     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
463     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
464     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
465     SOURCE_NAME = X_SOURCE_NAME     ,
466     SOURCE_NUMBER   =  X_SOURCE_NUMBER    ,
467     SOURCE_JOB_TITLE =  X_SOURCE_JOB_TITLE  ,
471     SOURCE_ORG_NAME =  X_SOURCE_ORG_NAME ,
468     SOURCE_EMAIL    =  X_SOURCE_EMAIL     ,
469     SOURCE_PHONE   =  X_SOURCE_PHONE    ,
470     SOURCE_ORG_ID  =  X_SOURCE_ORG_ID   ,
472     SOURCE_ADDRESS1 =  X_SOURCE_ADDRESS1 ,
473     SOURCE_ADDRESS2 =  X_SOURCE_ADDRESS2 ,
474     SOURCE_ADDRESS3 =  X_SOURCE_ADDRESS3 ,
475     SOURCE_ADDRESS4 =  X_SOURCE_ADDRESS4 ,
476     SOURCE_CITY     =  X_SOURCE_CITY      ,
477     SOURCE_POSTAL_CODE = X_SOURCE_POSTAL_CODE,
478     SOURCE_STATE     =  X_SOURCE_STATE      ,
479     SOURCE_PROVINCE  =  X_SOURCE_PROVINCE   ,
480     SOURCE_COUNTY    =  X_SOURCE_COUNTY     ,
481     SOURCE_COUNTRY     =  X_SOURCE_COUNTRY      ,
482     SOURCE_MGR_ID = X_SOURCE_MGR_ID,
483     SOURCE_MGR_NAME = X_SOURCE_MGR_NAME,
484     SOURCE_BUSINESS_GRP_ID = X_SOURCE_BUSINESS_GRP_ID,
485     SOURCE_BUSINESS_GRP_NAME = X_SOURCE_BUSINESS_GRP_NAME,
486     SOURCE_FIRST_NAME = X_SOURCE_FIRST_NAME,
487     SOURCE_MIDDLE_NAME = X_SOURCE_MIDDLE_NAME,
488     SOURCE_LAST_NAME = X_SOURCE_LAST_NAME,
489     SOURCE_CATEGORY = X_SOURCE_CATEGORY,
490     SOURCE_STATUS = X_SOURCE_STATUS,
491     SOURCE_OFFICE = X_SOURCE_OFFICE,
492     SOURCE_LOCATION = X_SOURCE_LOCATION,
493     SOURCE_MAILSTOP = X_SOURCE_MAILSTOP,
494     USER_NAME     = X_USER_NAME,
495     SOURCE_JOB_ID     = X_SOURCE_JOB_ID,
496     PERSON_PARTY_ID     = X_PARTY_ID,
497     SOURCE_MOBILE_PHONE = X_SOURCE_MOBILE_PHONE,
498     SOURCE_PAGER = X_SOURCE_PAGER
499   where RESOURCE_ID = X_RESOURCE_ID;
500 
501   if (sql%notfound) then
502     raise no_data_found;
503   end if;
504 
505   update JTF_RS_RESOURCE_EXTNS_TL set
506     RESOURCE_NAME = X_RESOURCE_NAME,
507     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
508     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
509     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
510     SOURCE_LANG = userenv('LANG')
511   where RESOURCE_ID = X_RESOURCE_ID
512   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
513 
514   if (sql%notfound) then
515     raise no_data_found;
516   end if;
517 end UPDATE_ROW;
518 
519 procedure DELETE_ROW (
520   X_RESOURCE_ID in NUMBER
521 ) is
522 begin
523 
524   delete from JTF_RS_RESOURCE_EXTNS_TL
525   where RESOURCE_ID = X_RESOURCE_ID;
526 
527   if (sql%notfound) then
528     raise no_data_found;
529   end if;
530 
531   delete from JTF_RS_RESOURCE_EXTNS
532   where RESOURCE_ID = X_RESOURCE_ID;
533 
534   if (sql%notfound) then
535     raise no_data_found;
536   end if;
537 
538 end DELETE_ROW;
539 
540 procedure ADD_LANGUAGE
541 is
542 begin
543   delete from JTF_RS_RESOURCE_EXTNS_TL T
544   where not exists
545     (select NULL
546     from JTF_RS_RESOURCE_EXTNS B
547     where B.RESOURCE_ID = T.RESOURCE_ID
548     );
549 
550   update JTF_RS_RESOURCE_EXTNS_TL T set (
551       RESOURCE_NAME
552     ) = (select
553       B.RESOURCE_NAME
554     from JTF_RS_RESOURCE_EXTNS_TL B
555     where B.RESOURCE_ID = T.RESOURCE_ID
556     and B.LANGUAGE = T.SOURCE_LANG)
557   where (
558       T.RESOURCE_ID,
559       T.LANGUAGE
560   ) in (select
561       SUBT.RESOURCE_ID,
562       SUBT.LANGUAGE
563     from JTF_RS_RESOURCE_EXTNS_TL SUBB, JTF_RS_RESOURCE_EXTNS_TL SUBT
564     where SUBB.RESOURCE_ID = SUBT.RESOURCE_ID
565     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
566     and (SUBB.RESOURCE_NAME <> SUBT.RESOURCE_NAME
567       or (SUBB.RESOURCE_NAME is null and SUBT.RESOURCE_NAME is not null)
568       or (SUBB.RESOURCE_NAME is not null and SUBT.RESOURCE_NAME is null)
569   ));
570 
571   insert into JTF_RS_RESOURCE_EXTNS_TL (
572     RESOURCE_ID,
573     CREATED_BY,
574     CREATION_DATE,
575     LAST_UPDATED_BY,
576     LAST_UPDATE_DATE,
577     LAST_UPDATE_LOGIN,
578     RESOURCE_NAME,
579     CATEGORY,
580     LANGUAGE,
581     SOURCE_LANG
582   ) select
583     B.RESOURCE_ID,
584     B.CREATED_BY,
585     B.CREATION_DATE,
586     B.LAST_UPDATED_BY,
587     B.LAST_UPDATE_DATE,
588     B.LAST_UPDATE_LOGIN,
589     B.RESOURCE_NAME,
590     B.CATEGORY,
591     L.LANGUAGE_CODE,
592     B.SOURCE_LANG
593   from JTF_RS_RESOURCE_EXTNS_TL B, FND_LANGUAGES L
594   where L.INSTALLED_FLAG in ('I', 'B')
595   and B.LANGUAGE = userenv('LANG')
596   and not exists
597     (select NULL
598     from JTF_RS_RESOURCE_EXTNS_TL T
599     where T.RESOURCE_ID = B.RESOURCE_ID
600     and T.LANGUAGE = L.LANGUAGE_CODE);
601 end ADD_LANGUAGE;
602 
603 
604 Procedure TRANSLATE_ROW
605 (x_resource_id  in number,
606  x_resource_name in varchar2,
607  x_Last_update_date in date,
608  x_last_updated_by in number,
609  x_last_update_login in number)
610 is
611 begin
612 
613 Update jtf_rs_resource_extns_tl set
614 resource_name		= nvl(x_resource_name,resource_name),
615 last_update_date	= nvl(x_last_update_date,sysdate),
616 last_updated_by		= x_last_updated_by,
617 last_update_login	= 0,
618 source_lang		= userenv('LANG')
619 where resource_id		= x_resource_id
620 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
621 
622   if (sql%notfound) then
623     raise no_data_found;
624   end if;
625 end TRANSLATE_ROW;
626 
627 end JTF_RS_RESOURCE_EXTNS_PKG;