[Home] [Help]
PACKAGE BODY: APPS.HR_FORM_DATA_GROUP_ITEMS_PKG
Source
1 package body HR_FORM_DATA_GROUP_ITEMS_PKG as
2 /* $Header: hrfgilct.pkb 120.1 2011/04/28 11:59:02 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_FORM_DATA_GROUP_ITEM_ID in NUMBER,
26 X_FORM_DATA_GROUP_ID in NUMBER,
27 X_FORM_ITEM_ID in NUMBER,
28 X_OBJECT_VERSION_NUMBER in NUMBER,
29 X_CREATED_BY in NUMBER,
30 X_CREATION_DATE in DATE,
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_FORM_DATA_GROUP_ITEMS
36 where FORM_DATA_GROUP_ITEM_ID = X_FORM_DATA_GROUP_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_FORM_DATA_GROUP_ITEMS',
48 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
49 PER_RIC_PKG.ref_info_rec('HR_FORM_DATA_GROUPS_B', PER_RIC_PKG.column_info_tbl(
50 PER_RIC_PKG.col_info_rec('FORM_DATA_GROUP_ID',NULL,X_FORM_DATA_GROUP_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 --
59 -- Code Ends Here.
60 --
61 insert into HR_FORM_DATA_GROUP_ITEMS (
62 FORM_DATA_GROUP_ID,
63 FORM_ITEM_ID,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 FORM_DATA_GROUP_ITEM_ID,
68 OBJECT_VERSION_NUMBER,
69 CREATED_BY,
70 CREATION_DATE
71 ) values(
72 X_FORM_DATA_GROUP_ID,
73 X_FORM_ITEM_ID,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_LOGIN,
77 X_FORM_DATA_GROUP_ITEM_ID,
78 X_OBJECT_VERSION_NUMBER,
79 X_CREATED_BY,
80 X_CREATION_DATE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_FORM_DATA_GROUP_ITEM_ID in NUMBER,
94 X_FORM_DATA_GROUP_ID in NUMBER,
95 X_FORM_ITEM_ID in NUMBER,
96 X_OBJECT_VERSION_NUMBER in NUMBER
97 ) is
98 cursor c1 is select
99 FORM_DATA_GROUP_ID,
100 FORM_ITEM_ID,
101 OBJECT_VERSION_NUMBER
102 from HR_FORM_DATA_GROUP_ITEMS
103 where FORM_DATA_GROUP_ITEM_ID = X_FORM_DATA_GROUP_ITEM_ID
104 for update of FORM_DATA_GROUP_ITEM_ID nowait;
105 begin
106 for tlinfo in c1 loop
107 if ( (tlinfo.FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID)
108 AND (tlinfo.FORM_ITEM_ID = X_FORM_ITEM_ID)
109 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
110 ) then
111 null;
112 else
113 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114 app_exception.raise_exception;
115 end if;
116 end loop;
117 return;
118 end LOCK_ROW;
119
120 procedure UPDATE_ROW (
121 X_FORM_DATA_GROUP_ITEM_ID in NUMBER,
122 X_FORM_DATA_GROUP_ID in NUMBER,
123 X_FORM_ITEM_ID in NUMBER,
124 X_OBJECT_VERSION_NUMBER in NUMBER,
125 X_LAST_UPDATE_DATE in DATE,
126 X_LAST_UPDATED_BY in NUMBER,
127 X_LAST_UPDATE_LOGIN in NUMBER
128 ) is
129 begin
130 update HR_FORM_DATA_GROUP_ITEMS set
131 FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID,
132 FORM_ITEM_ID = X_FORM_ITEM_ID,
133 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
134 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
135 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
136 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
137 where FORM_DATA_GROUP_ITEM_ID = X_FORM_DATA_GROUP_ITEM_ID;
138
139 if (sql%notfound) then
140 raise no_data_found;
141 end if;
142 end UPDATE_ROW;
143
144 procedure DELETE_ROW (
145 X_FORM_DATA_GROUP_ITEM_ID in NUMBER
146 ) is
147 begin
148
149 delete from HR_FORM_DATA_GROUP_ITEMS
150 where FORM_DATA_GROUP_ITEM_ID = X_FORM_DATA_GROUP_ITEM_ID;
151
152 if (sql%notfound) then
153 raise no_data_found;
154 end if;
155
156 end DELETE_ROW;
157 procedure LOAD_ROW (
158 X_APPLICATION_SHORT_NAME in VARCHAR2,
159 X_FORM_NAME in VARCHAR2,
160 X_DATA_GROUP_NAME in VARCHAR2,
161 X_FULL_ITEM_NAME in VARCHAR2,
162 X_RADIO_BUTTON_NAME in VARCHAR2,
163 X_OWNER in VARCHAR2,
164 X_OBJECT_VERSION_NUMBER in VARCHAR2) is
165 X_ROWID ROWID;
166 X_CREATION_DATE DATE;
167 X_CREATED_BY NUMBER;
168 X_LAST_UPDATE_DATE DATE;
169 X_LAST_UPDATED_BY NUMBER;
170 X_LAST_UPDATE_LOGIN NUMBER;
171 X_FORM_ID NUMBER;
172 X_APPLICATION_ID NUMBER;
173 X_FORM_DATA_GROUP_ID NUMBER;
174 X_FORM_ITEM_ID NUMBER;
175 X_FORM_DATA_GROUP_ITEM_ID NUMBER;
176 begin
177
178 OWNER_TO_WHO (
179 X_OWNER,
180 X_CREATION_DATE,
181 X_CREATED_BY,
182 X_LAST_UPDATE_DATE,
183 X_LAST_UPDATED_BY,
184 X_LAST_UPDATE_LOGIN
185 );
186
187 select application_id
188 into x_application_id
189 from fnd_application
190 where application_short_name = x_application_short_name;
191
192 select form_id
193 into x_form_id
194 from fnd_form
195 where form_name = x_form_name
196 and application_id = x_application_id;
197
198 select hfi.form_item_id
199 into x_form_item_id
200 from hr_form_items_b hfi
201 where hfi.application_id = x_application_id
202 and hfi.form_id = x_form_id
203 and hfi.full_item_name = x_full_item_name
204 and ( (hfi.radio_button_name = x_radio_button_name)
205 or (hfi.radio_button_name is null and x_radio_button_name is null) );
206
207 select form_data_group_id
208 into x_form_data_group_id
209 from hr_form_data_groups_b fdg
210 where fdg.data_group_name = x_data_group_name
211 and fdg.application_id = x_application_id
212 and fdg.form_id = x_form_id;
213
214 begin
215 select fgi.form_data_group_item_id
216 into x_form_data_group_item_id
217 from hr_form_data_group_items fgi
218 where fgi.form_data_group_id = x_form_data_group_id
219 and fgi.form_item_id = x_form_item_id;
220 exception
221 when no_data_found then
222 select hr_form_data_group_items_s.nextval
223 into x_form_data_group_item_id
224 from dual;
225 end;
226
227 begin
228 UPDATE_ROW (
229 X_FORM_DATA_GROUP_ITEM_ID,
230 X_FORM_DATA_GROUP_ID,
231 X_FORM_ITEM_ID,
232 to_number(X_OBJECT_VERSION_NUMBER),
233 X_LAST_UPDATE_DATE,
234 X_LAST_UPDATED_BY,
235 X_LAST_UPDATE_LOGIN
236 );
237
238 exception
239 when no_data_found then
240 INSERT_ROW (
241 X_ROWID,
242 X_FORM_DATA_GROUP_ITEM_ID,
243 X_FORM_DATA_GROUP_ID,
244 X_FORM_ITEM_ID,
245 to_number(X_OBJECT_VERSION_NUMBER),
246 X_CREATED_BY,
247 X_CREATION_DATE,
248 X_LAST_UPDATE_DATE,
249 X_LAST_UPDATED_BY,
250 X_LAST_UPDATE_LOGIN);
251 end;
252 end LOAD_ROW;
253 end HR_FORM_DATA_GROUP_ITEMS_PKG;