[Home] [Help]
PACKAGE BODY: APPS.HR_SOURCE_FORM_TEMPLATES_PKG
Source
1 package body HR_SOURCE_FORM_TEMPLATES_PKG as
2 /* $Header: hrsftlct.pkb 115.3 2002/12/11 06:50:42 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_SOURCE_FORM_TEMPLATE_ID in NUMBER,
26 X_FORM_TEMPLATE_ID_FROM in NUMBER,
27 X_FORM_TEMPLATE_ID_TO in NUMBER,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 cursor C is select ROWID from HR_SOURCE_FORM_TEMPLATES
35 where SOURCE_FORM_TEMPLATE_ID = X_SOURCE_FORM_TEMPLATE_ID
36 ;
37 begin
38 insert into HR_SOURCE_FORM_TEMPLATES (
39 FORM_TEMPLATE_ID_FROM,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN,
43 CREATED_BY,
44 CREATION_DATE,
45 FORM_TEMPLATE_ID_TO,
46 SOURCE_FORM_TEMPLATE_ID)
47 values (
48 X_FORM_TEMPLATE_ID_FROM,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN,
52 X_CREATED_BY,
53 X_CREATION_DATE,
54 X_FORM_TEMPLATE_ID_TO,
55 X_SOURCE_FORM_TEMPLATE_ID);
56
57 open c;
58 fetch c into X_ROWID;
59 if (c%notfound) then
60 close c;
61 raise no_data_found;
62 end if;
63 close c;
64
65 end INSERT_ROW;
66
67 procedure LOCK_ROW (
68 X_SOURCE_FORM_TEMPLATE_ID in NUMBER,
69 X_FORM_TEMPLATE_ID_FROM in NUMBER,
70 X_FORM_TEMPLATE_ID_TO in NUMBER
71 ) is
72 cursor c1 is select
73 FORM_TEMPLATE_ID_FROM,
74 FORM_TEMPLATE_ID_TO
75 from HR_SOURCE_FORM_TEMPLATES
76 where SOURCE_FORM_TEMPLATE_ID = X_SOURCE_FORM_TEMPLATE_ID
77 for update of SOURCE_FORM_TEMPLATE_ID nowait;
78 begin
79 for tlinfo in c1 loop
80 if ((tlinfo.FORM_TEMPLATE_ID_FROM = X_FORM_TEMPLATE_ID_FROM)
81 OR ((tlinfo.FORM_TEMPLATE_ID_FROM is null) AND (X_FORM_TEMPLATE_ID_FROM is null)))
82 AND (tlinfo.FORM_TEMPLATE_ID_TO = X_FORM_TEMPLATE_ID_TO)
83 then
84 null;
85 else
86 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
87 app_exception.raise_exception;
88 end if;
89 end loop;
90 return;
91 end LOCK_ROW;
92
93 procedure UPDATE_ROW (
94 X_SOURCE_FORM_TEMPLATE_ID in NUMBER,
95 X_FORM_TEMPLATE_ID_FROM in NUMBER,
96 X_FORM_TEMPLATE_ID_TO in NUMBER,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATED_BY in NUMBER,
99 X_LAST_UPDATE_LOGIN in NUMBER
100 ) is
101 begin
102 update HR_SOURCE_FORM_TEMPLATES set
103 FORM_TEMPLATE_ID_FROM = X_FORM_TEMPLATE_ID_FROM,
104 FORM_TEMPLATE_ID_TO = X_FORM_TEMPLATE_ID_TO,
105 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
106 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
108 where SOURCE_FORM_TEMPLATE_ID = X_SOURCE_FORM_TEMPLATE_ID;
109
110 if (sql%notfound) then
111 raise no_data_found;
112 end if;
113 end UPDATE_ROW;
114
115 procedure DELETE_ROW (
116 X_SOURCE_FORM_TEMPLATE_ID in NUMBER
117 ) is
118 begin
119 delete from HR_SOURCE_FORM_TEMPLATES
120 where SOURCE_FORM_TEMPLATE_ID = X_SOURCE_FORM_TEMPLATE_ID;
121
122 if (sql%notfound) then
123 raise no_data_found;
124 end if;
125
126 end DELETE_ROW;
127
128 procedure LOAD_ROW (
129 X_APPLICATION_SHORT_NAME_TO in VARCHAR2,
130 X_FORM_NAME_TO in VARCHAR2,
131 X_TEMPLATE_NAME_TO in VARCHAR2,
132 X_TERRITORY_SHORT_NAME_TO in VARCHAR2 ,
133 X_APPLICATION_SHORT_NAME_FROM in VARCHAR2,
134 X_FORM_NAME_FROM in VARCHAR2,
135 X_TEMPLATE_NAME_FROM in VARCHAR2,
136 X_TERRITORY_SHORT_NAME_FROM in VARCHAR2,
137 X_OWNER in VARCHAR2) is
138 X_ROWID ROWID;
139 X_CREATION_DATE DATE;
140 X_CREATED_BY NUMBER;
141 X_LAST_UPDATE_DATE DATE;
142 X_LAST_UPDATED_BY NUMBER;
143 X_LAST_UPDATE_LOGIN NUMBER;
144 X_FORM_ID NUMBER;
145 X_LEGISLATION_CODE VARCHAR2(4);
146 X_APPLICATION_ID NUMBER;
147 X_FORM_TEMPLATE_ID_TO NUMBER;
148 X_FORM_TEMPLATE_ID_FROM NUMBER;
149 X_SOURCE_FORM_TEMPLATE_ID NUMBER;
150 begin
151
152 OWNER_TO_WHO (
153 X_OWNER,
154 X_CREATION_DATE,
155 X_CREATED_BY,
156 X_LAST_UPDATE_DATE,
157 X_LAST_UPDATED_BY,
158 X_LAST_UPDATE_LOGIN
159 );
160
161 select application_id
162 into x_application_id
163 from fnd_application
164 where application_short_name = x_application_short_name_to;
165
166 select form_id
167 into x_form_id
168 from fnd_form
169 where form_name = x_form_name_to
170 and application_id = x_application_id;
171
172 select form_template_id
173 into x_form_template_id_to
174 from hr_form_templates_b
175 where template_name = x_template_name_to
176 and ( (legislation_code is null and x_territory_short_name_to is null)
177 or (legislation_code = x_territory_short_name_to) )
178 and application_id = x_application_id
179 and form_id = x_form_id;
180
181 IF ( ltrim(rtrim(x_application_short_name_from)) IS NOT NULL ) AND
182 ( ltrim(rtrim(x_form_name_from)) IS NOT NULL ) AND
183 ( ltrim(rtrim(x_template_name_from)) IS NOT NULL ) THEN
184
185 select application_id
186 into x_application_id
187 from fnd_application
188 where application_short_name = x_application_short_name_from;
189
190 select form_id
191 into x_form_id
192 from fnd_form
193 where form_name = x_form_name_from
194 and application_id = x_application_id;
195
196 select form_template_id
197 into x_form_template_id_from
198 from hr_form_templates_b
199 where template_name = x_template_name_from
200 and ( (legislation_code is null and x_territory_short_name_from is null)
201 or (legislation_code = x_territory_short_name_from) )
202 and application_id = x_application_id
203 and form_id = x_form_id;
204
205 ELSE
206 x_form_template_id_from := null;
207 END IF;
208
209 begin
210
211 select source_form_template_id
212 into x_source_form_template_id
213 from hr_source_form_templates
214 where form_template_id_to = x_form_template_id_to
215 and nvl(form_template_id_from,hr_api.g_number) = nvl(x_form_template_id_from,hr_api.g_number);
216
217 exception
218 when no_data_found then
219 select hr_source_form_templates_s.nextval
220 into x_source_form_template_id
221 from dual;
222 end;
223
224
225 begin
226 UPDATE_ROW (
227 X_SOURCE_FORM_TEMPLATE_ID,
228 X_FORM_TEMPLATE_ID_FROM,
229 X_FORM_TEMPLATE_ID_TO,
230 X_LAST_UPDATE_DATE,
231 X_LAST_UPDATED_BY,
232 X_LAST_UPDATE_LOGIN
233 );
234 exception
235 when no_data_found then
236 INSERT_ROW (
237 X_ROWID,
238 X_SOURCE_FORM_TEMPLATE_ID,
239 X_FORM_TEMPLATE_ID_FROM,
240 X_FORM_TEMPLATE_ID_TO,
241 X_CREATION_DATE,
242 X_CREATED_BY,
243 X_LAST_UPDATE_DATE,
244 X_LAST_UPDATED_BY,
245 X_LAST_UPDATE_LOGIN);
246 end;
247
248 end LOAD_ROW;
249 end HR_SOURCE_FORM_TEMPLATES_PKG;