DBA Data[Home] [Help]

PACKAGE BODY: APPS.PER_JOB_INFO_TYPES_PKG

Source


1 PACKAGE BODY PER_JOB_INFO_TYPES_PKG as
2 /* $Header: perjobit.pkb 120.1 2011/04/28 09:56:20 sidsaxen ship $ */
3 
4 --------------------------------------------------------------------------------
5 g_dummy	number(1);	-- Dummy for cursor returns which are not needed
6 g_business_group_id number(15); -- For validating translation;
7 g_legislation_code varchar2(150); -- For validating translation;
8 --------------------------------------------------------------------------------
9 --
10 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE           VARCHAR2,
11                            P_ACTIVE_INACTIVE_FLAG       VARCHAR2,
12                            P_LEGISLATION_CODE           VARCHAR2,
13                            P_ROWID                      VARCHAR2,
14                            P_DESCRIPTION                VARCHAR2)
15 IS
16 L_DUMMY1  number;
17 CURSOR C1 IS
18  	select  1
19  	from    PER_JOB_INFO_types t
20  	where   upper(t.description) =  upper(P_DESCRIPTION)
21  	and     nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
22         	  =  nvl(P_LEGISLATION_CODE, 'XXX')
23  	and     (P_ROWID        is null
24         	 or P_ROWID    <> t.rowid);
25 BEGIN
26  OPEN C1;
27  FETCH C1 INTO L_DUMMY1;
28  IF C1%NOTFOUND THEN
29   CLOSE C1;
30  ELSE
31   CLOSE C1;
32   hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
33   hr_utility.raise_error;
34  END IF;
35 end UNIQUENESS_CHECK;
36 --
37 procedure INSERT_ROW (
38   X_ROWID in out nocopy VARCHAR2,
39   X_INFORMATION_TYPE in VARCHAR2,
40   X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
41   X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
42   X_LEGISLATION_CODE in VARCHAR2,
43   X_REQUEST_ID in NUMBER,
44   X_OBJECT_VERSION_NUMBER in NUMBER,
45   X_DESCRIPTION in VARCHAR2,
46   X_CREATION_DATE in DATE,
47   X_CREATED_BY in NUMBER,
48   X_LAST_UPDATE_DATE in DATE,
49   X_LAST_UPDATED_BY in NUMBER,
50   X_LAST_UPDATE_LOGIN in NUMBER
51 ) is
52   cursor C is select ROWID from PER_JOB_INFO_TYPES
53     where INFORMATION_TYPE = X_INFORMATION_TYPE
54     ;
55 begin
56   insert into PER_JOB_INFO_TYPES (
57     INFORMATION_TYPE,
58     ACTIVE_INACTIVE_FLAG,
59     MULTIPLE_OCCURENCES_FLAG,
60     LEGISLATION_CODE,
61     REQUEST_ID,
62     OBJECT_VERSION_NUMBER,
63     DESCRIPTION,
64     CREATION_DATE,
65     CREATED_BY,
66     LAST_UPDATE_DATE,
67     LAST_UPDATED_BY,
68     LAST_UPDATE_LOGIN
69   ) values (
70     X_INFORMATION_TYPE,
71     X_ACTIVE_INACTIVE_FLAG,
72     X_MULTIPLE_OCCURENCES_FLAG,
73     X_LEGISLATION_CODE,
74     X_REQUEST_ID,
75     X_OBJECT_VERSION_NUMBER,
76     X_DESCRIPTION,
77     X_CREATION_DATE,
78     X_CREATED_BY,
79     X_LAST_UPDATE_DATE,
80     X_LAST_UPDATED_BY,
81     X_LAST_UPDATE_LOGIN
82   );
83 
84   open c;
85   fetch c into X_ROWID;
86   if (c%notfound) then
87     close c;
88     raise no_data_found;
89   end if;
90   close c;
91 
92 end INSERT_ROW;
93 
94 procedure LOCK_ROW (
95   X_INFORMATION_TYPE in VARCHAR2,
96   X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
97   X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
98   X_LEGISLATION_CODE in VARCHAR2,
99   X_REQUEST_ID in NUMBER,
100   X_OBJECT_VERSION_NUMBER in NUMBER,
101   X_DESCRIPTION in VARCHAR2
102 ) is
103   cursor c is select
104       ACTIVE_INACTIVE_FLAG,
105       MULTIPLE_OCCURENCES_FLAG,
106       LEGISLATION_CODE,
107       REQUEST_ID,
108       OBJECT_VERSION_NUMBER
109     from PER_JOB_INFO_TYPES
110     where INFORMATION_TYPE = X_INFORMATION_TYPE
111     for update of INFORMATION_TYPE nowait;
112   recinfo c%rowtype;
113 
114 begin
115   open c;
116   fetch c into recinfo;
117   if (c%notfound) then
118     close c;
119     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120     app_exception.raise_exception;
121   end if;
122   close c;
123   if (    (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
124       AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
125       AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
126            OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
127       AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
128            OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
129       AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
130            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
131   ) then
132     null;
133   else
134     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135     app_exception.raise_exception;
136   end if;
137 
138   return;
139 end LOCK_ROW;
140 
141 procedure UPDATE_ROW (
142   X_INFORMATION_TYPE in VARCHAR2,
143   X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
144   X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
145   X_LEGISLATION_CODE in VARCHAR2,
146   X_REQUEST_ID in NUMBER,
147   X_OBJECT_VERSION_NUMBER in NUMBER,
148   X_DESCRIPTION in VARCHAR2,
149   X_LAST_UPDATE_DATE in DATE,
150   X_LAST_UPDATED_BY in NUMBER,
151   X_LAST_UPDATE_LOGIN in NUMBER
152 ) is
153 begin
154   update PER_JOB_INFO_TYPES set
155     ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
156     MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
157     LEGISLATION_CODE = X_LEGISLATION_CODE,
158     REQUEST_ID = X_REQUEST_ID,
159     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
160     DESCRIPTION = X_DESCRIPTION,
161     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
163     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
164   where INFORMATION_TYPE = X_INFORMATION_TYPE;
165 
166   if (sql%notfound) then
167     raise no_data_found;
168   end if;
169 
170  end UPDATE_ROW;
171 
172 procedure DELETE_ROW (
173   X_INFORMATION_TYPE in VARCHAR2
174 ) is
175 begin
176   --
177   -- Added the following code as a part of Zero Downtime Patching Project.
178   -- Code Starts Here.
179   --
180   per_ric_pkg.Chk_integrity(
181     p_entity_name=>'PER_JOB_INFO_TYPES',
182     p_ref_entity=>'PER_JOB_EXTRA_INFO',
183     p_ref_column_name=>'INFORMATION_TYPE',
184     p_ref_col_value_varchar=> X_INFORMATION_TYPE,
185     p_ref_col_value_number=> NULL,
186     p_ref_col_value_date=> NULL,
187     p_ref_type=>'DEL');
188   --
189   -- Code Ends Here
190   --
191   delete from PER_JOB_INFO_TYPES
192   where INFORMATION_TYPE = X_INFORMATION_TYPE;
193 
194   if (sql%notfound) then
195     raise no_data_found;
196   end if;
197 end DELETE_ROW;
198 
199 procedure LOAD_ROW
200   (X_INFORMATION_TYPE         in varchar2
201   ,X_ACTIVE_INACTIVE_FLAG     in varchar2
202   ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
203   ,X_DESCRIPTION              in varchar2
204   ,X_LEGISLATION_CODE         in varchar2
205   ,X_OBJECT_VERSION_NUMBER    in number
206   ,X_OWNER                    in varchar2
207   )
208 is
209   l_proc                        VARCHAR2(61) := 'PER_JOB_INFO_TYPES_PKG.LOAD_ROW';
210   l_rowid                       rowid;
211   l_request_id                  PER_JOB_INFO_types.request_id%TYPE;
212   l_progam_application_id       PER_JOB_INFO_types.program_application_id%TYPE;
213   l_program_id                  PER_JOB_INFO_types.program_id%TYPE;
214   l_program_update_date         PER_JOB_INFO_types.program_update_date%TYPE;
215   l_created_by                  PER_JOB_INFO_types.created_by%TYPE             := 0;
216   l_creation_date               PER_JOB_INFO_types.creation_date%TYPE          := SYSDATE;
217   l_last_update_date            PER_JOB_INFO_types.last_update_date%TYPE       := SYSDATE;
218   l_last_updated_by             PER_JOB_INFO_types.last_updated_by%TYPE         := 0;
219   l_last_update_login           PER_JOB_INFO_types.last_update_login%TYPE      := 0;
220 begin
221   -- Translate developer keys to internal parameters
222   if X_OWNER = 'SEED' then
223     l_created_by := 1;
224     l_last_updated_by := 1;
225   end if;
226   -- Update or insert row as appropriate
227   begin
228     UPDATE_ROW
229       (X_INFORMATION_TYPE         => X_INFORMATION_TYPE
230       ,X_ACTIVE_INACTIVE_FLAG     => X_ACTIVE_INACTIVE_FLAG
231       ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
232       ,X_DESCRIPTION              => X_DESCRIPTION
233       ,X_LEGISLATION_CODE         => X_LEGISLATION_CODE
234       ,X_OBJECT_VERSION_NUMBER    => X_OBJECT_VERSION_NUMBER
235       ,X_REQUEST_ID               => l_request_id
236       ,X_LAST_UPDATE_DATE         => l_last_update_date
237       ,X_LAST_UPDATED_BY          => l_last_updated_by
238       ,X_LAST_UPDATE_LOGIN        => l_last_update_login
239       );
240   exception
241     when no_data_found then
242       INSERT_ROW
243         (X_ROWID                    => l_rowid
244         ,X_INFORMATION_TYPE         => X_INFORMATION_TYPE
245         ,X_ACTIVE_INACTIVE_FLAG     => X_ACTIVE_INACTIVE_FLAG
246         ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
247         ,X_DESCRIPTION              => X_DESCRIPTION
248         ,X_LEGISLATION_CODE         => X_LEGISLATION_CODE
249         ,X_OBJECT_VERSION_NUMBER    => X_OBJECT_VERSION_NUMBER
250         ,X_REQUEST_ID               => l_request_id
251         ,X_CREATED_BY               => l_created_by
252         ,X_CREATION_DATE            => l_creation_date
253         ,X_LAST_UPDATE_DATE         => l_last_update_date
254         ,X_LAST_UPDATED_BY          => l_last_updated_by
255         ,X_LAST_UPDATE_LOGIN        => l_last_update_login
256         );
257   end;
258 --
259 end LOAD_ROW;
260 
261 procedure TRANSLATE_ROW
262   (X_INFORMATION_TYPE in varchar2
263   ,X_DESCRIPTION      in varchar2
264   ,X_OWNER            in varchar2
265   )
266 is
267 begin
268   UPDATE per_job_info_types
269      SET description = X_DESCRIPTION
270         ,last_update_date = SYSDATE
271         ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
272         ,last_update_login = 0
273    WHERE USERENV('LANG') = (select language_code from fnd_languages
274                             where installed_flag = 'B')
275      AND information_type = X_INFORMATION_TYPE;
276 end TRANSLATE_ROW;
277 
278 
279 END PER_JOB_INFO_TYPES_PKG;