[Home] [Help]
PACKAGE BODY: APPS.HR_TEMPLATE_ITEMS_PKG
Source
1 package body HR_TEMPLATE_ITEMS_PKG as
2 /* $Header: hrtiwlct.pkb 120.1 2011/04/28 10:46:39 sidsaxen ship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATION_DATE out nocopy DATE,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_DATE out nocopy DATE,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if X_OWNER = 'SEED' then
13 X_CREATED_BY := 1;
14 X_LAST_UPDATED_BY := 1;
15 else
16 X_CREATED_BY := 0;
17 X_LAST_UPDATED_BY := 0;
18 end if;
19 X_CREATION_DATE := sysdate;
20 X_LAST_UPDATE_DATE := sysdate;
21 X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23 procedure INSERT_ROW (
24 X_ROWID in out nocopy VARCHAR2,
25 X_TEMPLATE_ITEM_ID in NUMBER,
26 X_FORM_ITEM_ID in NUMBER,
27 X_OBJECT_VERSION_NUMBER in NUMBER,
28 X_FORM_TEMPLATE_ID in NUMBER,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from HR_TEMPLATE_ITEMS_B
36 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID
37 ;
38 begin
39
40 --
41 -- Added the following code as a part of Zero Downtime Patching Project.
42 -- Code Starts Here.
43 --
44
45 BEGIN
46 PER_RIC_PKG.chk_integrity (
47 p_entity_name => 'HR_TEMPLATE_ITEMS_B',
48 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
49 PER_RIC_PKG.ref_info_rec('HR_FORM_TEMPLATES_B', PER_RIC_PKG.column_info_tbl(
50 PER_RIC_PKG.col_info_rec('FORM_TEMPLATE_ID',NULL,X_TEMPLATE_ITEM_ID,NULL))),
51 PER_RIC_PKG.ref_info_rec('HR_FORM_ITEMS_B', PER_RIC_PKG.column_info_tbl(
52 PER_RIC_PKG.col_info_rec('FORM_ITEM_ID',NULL,X_FORM_ITEM_ID,NULL)))
53 ),
54 p_ref_type => 'INS');
55
56 END;
57 --
58 -- Code Ends Here.
59 --
60 insert into HR_TEMPLATE_ITEMS_B (
61 LAST_UPDATED_BY,
62 FORM_ITEM_ID,
63 LAST_UPDATE_DATE,
64 TEMPLATE_ITEM_ID,
65 OBJECT_VERSION_NUMBER,
66 FORM_TEMPLATE_ID,
67 LAST_UPDATE_LOGIN,
68 CREATED_BY,
69 CREATION_DATE
70 ) values(
71 X_LAST_UPDATED_BY,
72 X_FORM_ITEM_ID,
73 X_LAST_UPDATE_DATE,
74 X_TEMPLATE_ITEM_ID,
75 X_OBJECT_VERSION_NUMBER,
76 X_FORM_TEMPLATE_ID,
77 X_LAST_UPDATE_LOGIN,
78 X_CREATED_BY,
79 X_CREATION_DATE);
80 open c;
81 fetch c into X_ROWID;
82 if (c%notfound) then
83 close c;
84 raise no_data_found;
85 end if;
86 close c;
87
88 end INSERT_ROW;
89
90 procedure LOCK_ROW (
91 X_TEMPLATE_ITEM_ID in NUMBER,
92 X_FORM_ITEM_ID in NUMBER,
93 X_OBJECT_VERSION_NUMBER in NUMBER,
94 X_FORM_TEMPLATE_ID in NUMBER
95 ) is
96 cursor c1 is select
97 FORM_ITEM_ID,
98 OBJECT_VERSION_NUMBER,
99 FORM_TEMPLATE_ID
100 from HR_TEMPLATE_ITEMS_B
101 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID
102 for update of TEMPLATE_ITEM_ID nowait;
103 begin
104 for tlinfo in c1 loop
105 if (tlinfo.FORM_ITEM_ID = X_FORM_ITEM_ID)
106 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
107 AND (tlinfo.FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID)
108 then
109 null;
110 else
111 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
112 app_exception.raise_exception;
113 end if;
114 end loop;
115 return;
116 end LOCK_ROW;
117
118 procedure UPDATE_ROW (
119 X_TEMPLATE_ITEM_ID in NUMBER,
120 X_FORM_ITEM_ID in NUMBER,
121 X_OBJECT_VERSION_NUMBER in NUMBER,
122 X_FORM_TEMPLATE_ID in NUMBER,
123 X_LAST_UPDATE_DATE in DATE,
124 X_LAST_UPDATED_BY in NUMBER,
125 X_LAST_UPDATE_LOGIN in NUMBER
126 ) is
127 begin
128 update HR_TEMPLATE_ITEMS_B set
129 FORM_ITEM_ID = X_FORM_ITEM_ID,
130 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
131 FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID,
132 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
133 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
135 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID;
136
137 if (sql%notfound) then
138 raise no_data_found;
139 end if;
140 end UPDATE_ROW;
141
142 procedure DELETE_ROW (
143 X_TEMPLATE_ITEM_ID in NUMBER
144 ) is
145 begin
146
147 --
148 -- Added the following code as a part of Zero Downtime Patching Project.
149 -- Code Starts Here.
150 --
151
152 BEGIN
153 PER_RIC_PKG.chk_integrity (
154 p_entity_name => 'HR_TEMPLATE_ITEMS_B',
155 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
156 PER_RIC_PKG.ref_info_rec('HR_ITEM_PROPERTIES_B', PER_RIC_PKG.column_info_tbl(
157 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_ID',NULL,X_TEMPLATE_ITEM_ID,NULL))),
158 PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_ITEM_CONTEXTS_B', PER_RIC_PKG.column_info_tbl(
159 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_ID',NULL,X_TEMPLATE_ITEM_ID,NULL))),
160 PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_ITEM_TAB_PAGES', PER_RIC_PKG.column_info_tbl(
161 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_ID',NULL,X_TEMPLATE_ITEM_ID,NULL)))
162 ),
163 p_ref_type => 'DEL');
164
165 END;
166 --
167 -- Code Ends Here.
168 --
169
170 delete from HR_TEMPLATE_ITEMS_B
171 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176
177 end DELETE_ROW;
178
179 procedure LOAD_ROW (
180 X_APPLICATION_SHORT_NAME in VARCHAR2,
181 X_FORM_NAME in VARCHAR2,
182 X_FULL_ITEM_NAME in VARCHAR2,
183 X_RADIO_BUTTON_NAME in VARCHAR2,
184 X_TEMPLATE_NAME in VARCHAR2,
185 X_TERRITORY_SHORT_NAME in VARCHAR2,
186 X_OWNER in VARCHAR2,
187 X_OBJECT_VERSION_NUMBER in VARCHAR2) is
188 X_ROWID ROWID;
189 X_CREATION_DATE DATE;
190 X_CREATED_BY NUMBER;
191 X_LAST_UPDATE_DATE DATE;
192 X_LAST_UPDATED_BY NUMBER;
193 X_LAST_UPDATE_LOGIN NUMBER;
194 X_FORM_ID NUMBER;
195 X_APPLICATION_ID NUMBER;
196 X_FORM_ITEM_ID NUMBER;
197 X_TEMPLATE_ITEM_ID NUMBER;
198 X_FORM_TEMPLATE_ID NUMBER;
199 begin
200
201 OWNER_TO_WHO (
202 X_OWNER,
203 X_CREATION_DATE,
204 X_CREATED_BY,
205 X_LAST_UPDATE_DATE,
206 X_LAST_UPDATED_BY,
207 X_LAST_UPDATE_LOGIN
208 );
209
210 select application_id
211 into x_application_id
212 from fnd_application
213 where application_short_name = x_application_short_name;
214
215 select form_id
216 into x_form_id
217 from fnd_form
218 where form_name = x_form_name
219 and application_id = x_application_id;
220
221 select form_item_id
222 into x_form_item_id
223 from hr_form_items_b
224 where full_item_name = x_full_item_name
225 and application_id = x_application_id
226 and form_id = x_form_id
227 and ( radio_button_name = x_radio_button_name
228 or (radio_button_name is null and x_radio_button_name is null) );
229
230 select form_template_id
231 into x_form_template_id
232 from hr_form_templates_b
233 where template_name = x_template_name
234 and ( (legislation_code is null and x_territory_short_name is null)
235 or (legislation_code = x_territory_short_name) )
236 and application_id = x_application_id
237 and form_id = x_form_id;
238
239 begin
240 select template_item_id
241 into x_template_item_id
242 from hr_template_items_b
243 where form_template_id = x_form_template_id
244 and form_item_id = x_form_item_id;
245 exception
246 when no_data_found then
247 select hr_template_items_b_s.nextval
248 into x_template_item_id
249 from dual;
250 end;
251
252
253 begin
254 UPDATE_ROW (
255 X_TEMPLATE_ITEM_ID,
256 X_FORM_ITEM_ID,
257 to_number(X_OBJECT_VERSION_NUMBER),
258 X_FORM_TEMPLATE_ID,
259 X_LAST_UPDATE_DATE,
260 X_LAST_UPDATED_BY,
261 X_LAST_UPDATE_LOGIN
262 );
263 exception
264 when no_data_found then
265 INSERT_ROW (
266 X_ROWID,
267 X_TEMPLATE_ITEM_ID,
268 X_FORM_ITEM_ID,
269 to_number(X_OBJECT_VERSION_NUMBER),
270 X_FORM_TEMPLATE_ID,
271 X_CREATION_DATE,
272 X_CREATED_BY,
273 X_LAST_UPDATE_DATE,
274 X_LAST_UPDATED_BY,
275 X_LAST_UPDATE_LOGIN);
276 end;
277 end LOAD_ROW;
278 end HR_TEMPLATE_ITEMS_PKG;