[Home] [Help]
PACKAGE BODY: APPS.HR_TEMPLATE_ITEMS_PKG
Source
1 package body HR_TEMPLATE_ITEMS_PKG as
2 /* $Header: hrtiwlct.pkb 115.3 2002/12/11 10:20:59 raranjan noship $ */
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 insert into HR_TEMPLATE_ITEMS_B (
40 LAST_UPDATED_BY,
41 FORM_ITEM_ID,
42 LAST_UPDATE_DATE,
43 TEMPLATE_ITEM_ID,
44 OBJECT_VERSION_NUMBER,
45 FORM_TEMPLATE_ID,
46 LAST_UPDATE_LOGIN,
47 CREATED_BY,
48 CREATION_DATE
49 ) values(
50 X_LAST_UPDATED_BY,
51 X_FORM_ITEM_ID,
52 X_LAST_UPDATE_DATE,
53 X_TEMPLATE_ITEM_ID,
54 X_OBJECT_VERSION_NUMBER,
55 X_FORM_TEMPLATE_ID,
56 X_LAST_UPDATE_LOGIN,
57 X_CREATED_BY,
58 X_CREATION_DATE);
59 open c;
60 fetch c into X_ROWID;
61 if (c%notfound) then
62 close c;
63 raise no_data_found;
64 end if;
65 close c;
66
67 end INSERT_ROW;
68
69 procedure LOCK_ROW (
70 X_TEMPLATE_ITEM_ID in NUMBER,
71 X_FORM_ITEM_ID in NUMBER,
72 X_OBJECT_VERSION_NUMBER in NUMBER,
73 X_FORM_TEMPLATE_ID in NUMBER
74 ) is
75 cursor c1 is select
76 FORM_ITEM_ID,
77 OBJECT_VERSION_NUMBER,
78 FORM_TEMPLATE_ID
79 from HR_TEMPLATE_ITEMS_B
80 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID
81 for update of TEMPLATE_ITEM_ID nowait;
82 begin
83 for tlinfo in c1 loop
84 if (tlinfo.FORM_ITEM_ID = X_FORM_ITEM_ID)
85 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
86 AND (tlinfo.FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID)
87 then
88 null;
89 else
90 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
91 app_exception.raise_exception;
92 end if;
93 end loop;
94 return;
95 end LOCK_ROW;
96
97 procedure UPDATE_ROW (
98 X_TEMPLATE_ITEM_ID in NUMBER,
99 X_FORM_ITEM_ID in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_FORM_TEMPLATE_ID in NUMBER,
102 X_LAST_UPDATE_DATE in DATE,
103 X_LAST_UPDATED_BY in NUMBER,
104 X_LAST_UPDATE_LOGIN in NUMBER
105 ) is
106 begin
107 update HR_TEMPLATE_ITEMS_B set
108 FORM_ITEM_ID = X_FORM_ITEM_ID,
109 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
110 FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID,
111 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
112 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
114 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119 end UPDATE_ROW;
120
121 procedure DELETE_ROW (
122 X_TEMPLATE_ITEM_ID in NUMBER
123 ) is
124 begin
125 delete from HR_TEMPLATE_ITEMS_B
126 where TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 end DELETE_ROW;
133
134 procedure LOAD_ROW (
135 X_APPLICATION_SHORT_NAME in VARCHAR2,
136 X_FORM_NAME in VARCHAR2,
137 X_FULL_ITEM_NAME in VARCHAR2,
138 X_RADIO_BUTTON_NAME in VARCHAR2,
139 X_TEMPLATE_NAME in VARCHAR2,
140 X_TERRITORY_SHORT_NAME in VARCHAR2,
141 X_OWNER in VARCHAR2,
142 X_OBJECT_VERSION_NUMBER in VARCHAR2) is
143 X_ROWID ROWID;
144 X_CREATION_DATE DATE;
145 X_CREATED_BY NUMBER;
146 X_LAST_UPDATE_DATE DATE;
147 X_LAST_UPDATED_BY NUMBER;
148 X_LAST_UPDATE_LOGIN NUMBER;
149 X_FORM_ID NUMBER;
150 X_APPLICATION_ID NUMBER;
151 X_FORM_ITEM_ID NUMBER;
152 X_TEMPLATE_ITEM_ID NUMBER;
153 X_FORM_TEMPLATE_ID NUMBER;
154 begin
155
156 OWNER_TO_WHO (
157 X_OWNER,
158 X_CREATION_DATE,
159 X_CREATED_BY,
160 X_LAST_UPDATE_DATE,
161 X_LAST_UPDATED_BY,
162 X_LAST_UPDATE_LOGIN
163 );
164
165 select application_id
166 into x_application_id
167 from fnd_application
168 where application_short_name = x_application_short_name;
169
170 select form_id
171 into x_form_id
172 from fnd_form
173 where form_name = x_form_name
174 and application_id = x_application_id;
175
176 select form_item_id
177 into x_form_item_id
178 from hr_form_items_b
179 where full_item_name = x_full_item_name
180 and application_id = x_application_id
181 and form_id = x_form_id
182 and ( radio_button_name = x_radio_button_name
183 or (radio_button_name is null and x_radio_button_name is null) );
184
185 select form_template_id
186 into x_form_template_id
187 from hr_form_templates_b
188 where template_name = x_template_name
189 and ( (legislation_code is null and x_territory_short_name is null)
190 or (legislation_code = x_territory_short_name) )
191 and application_id = x_application_id
192 and form_id = x_form_id;
193
194 begin
195 select template_item_id
196 into x_template_item_id
197 from hr_template_items_b
198 where form_template_id = x_form_template_id
199 and form_item_id = x_form_item_id;
200 exception
201 when no_data_found then
202 select hr_template_items_b_s.nextval
203 into x_template_item_id
204 from dual;
205 end;
206
207
208 begin
209 UPDATE_ROW (
210 X_TEMPLATE_ITEM_ID,
211 X_FORM_ITEM_ID,
212 to_number(X_OBJECT_VERSION_NUMBER),
213 X_FORM_TEMPLATE_ID,
214 X_LAST_UPDATE_DATE,
215 X_LAST_UPDATED_BY,
216 X_LAST_UPDATE_LOGIN
217 );
218 exception
219 when no_data_found then
220 INSERT_ROW (
221 X_ROWID,
222 X_TEMPLATE_ITEM_ID,
223 X_FORM_ITEM_ID,
224 to_number(X_OBJECT_VERSION_NUMBER),
225 X_FORM_TEMPLATE_ID,
226 X_CREATION_DATE,
227 X_CREATED_BY,
228 X_LAST_UPDATE_DATE,
229 X_LAST_UPDATED_BY,
230 X_LAST_UPDATE_LOGIN);
231 end;
232 end LOAD_ROW;
233 end HR_TEMPLATE_ITEMS_PKG;