[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_HIERARCHIES_PKG
Source
1 package body JTF_LOC_HIERARCHIES_PKG as
2 /* $Header: jtfllohb.pls 120.2 2005/11/07 14:04:33 cmehta ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOCATION_HIERARCHY_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_REQUEST_ID in NUMBER,
8 X_PROGRAM_APPLICATION_ID in NUMBER,
9 X_PROGRAM_ID in NUMBER,
10 X_PROGRAM_UPDATE_DATE in DATE,
11 X_CREATED_BY_APPLICATION_ID in NUMBER,
12 X_LOCATION_TYPE_CODE in VARCHAR2,
13 X_START_DATE_ACTIVE in DATE,
14 X_END_DATE_ACTIVE in DATE,
15 X_AREA1_ID in NUMBER,
16 X_AREA1_CODE in VARCHAR2,
17 X_AREA2_ID in NUMBER,
18 X_AREA2_CODE in VARCHAR2,
19 X_COUNTRY_ID in NUMBER,
20 X_COUNTRY_CODE in VARCHAR2,
21 X_COUNTRY_REGION_ID in NUMBER,
22 X_COUNTRY_REGION_CODE in VARCHAR2,
23 X_STATE_ID in NUMBER,
24 X_STATE_CODE in VARCHAR2,
25 X_STATE_REGION_ID in NUMBER,
26 X_STATE_REGION_CODE in VARCHAR2,
27 X_CITY_ID in NUMBER,
28 X_CITY_CODE in VARCHAR2,
29 X_POSTAL_CODE_ID in NUMBER,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER) is
35 cursor c is select ROWID from JTF_LOC_HIERARCHIES_B
36 where LOCATION_HIERARCHY_ID = X_LOCATION_HIERARCHY_ID
37 ;
38 begin
39 insert into JTF_LOC_HIERARCHIES_B (
40 LOCATION_HIERARCHY_ID,
41 OBJECT_VERSION_NUMBER,
42 REQUEST_ID,
43 PROGRAM_APPLICATION_ID,
44 PROGRAM_ID,
45 PROGRAM_UPDATE_DATE,
46 CREATED_BY_APPLICATION_ID,
47 LOCATION_TYPE_CODE,
48 START_DATE_ACTIVE,
49 END_DATE_ACTIVE,
50 AREA1_ID,
51 AREA1_CODE,
52 AREA2_ID,
53 AREA2_CODE,
54 COUNTRY_ID,
55 COUNTRY_CODE,
56 COUNTRY_REGION_ID,
57 COUNTRY_REGION_CODE,
58 STATE_ID,
59 STATE_CODE,
60 STATE_REGION_ID,
61 STATE_REGION_CODE,
62 CITY_ID,
63 CITY_CODE,
64 POSTAL_CODE_ID,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
71 X_LOCATION_HIERARCHY_ID,
72 X_OBJECT_VERSION_NUMBER,
73 X_REQUEST_ID,
74 X_PROGRAM_APPLICATION_ID,
75 X_PROGRAM_ID,
76 X_PROGRAM_UPDATE_DATE,
77 X_CREATED_BY_APPLICATION_ID,
78 X_LOCATION_TYPE_CODE,
79 X_START_DATE_ACTIVE,
80 X_END_DATE_ACTIVE,
81 X_AREA1_ID,
82 X_AREA1_CODE,
83 X_AREA2_ID,
84 X_AREA2_CODE,
85 X_COUNTRY_ID,
86 X_COUNTRY_CODE,
87 X_COUNTRY_REGION_ID,
88 X_COUNTRY_REGION_CODE,
89 X_STATE_ID,
90 X_STATE_CODE,
91 X_STATE_REGION_ID,
92 X_STATE_REGION_CODE,
93 X_CITY_ID,
94 X_CITY_CODE,
95 X_POSTAL_CODE_ID,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN
101 );
102
103 open c;
104 fetch c into X_ROWID;
105 if (c%notfound) then
106 close c;
107 raise no_data_found;
108 end if;
109 close c;
110 end INSERT_ROW;
111
112 procedure UPDATE_ROW (
113 X_LOCATION_HIERARCHY_ID in NUMBER,
114 X_OBJECT_VERSION_NUMBER in NUMBER,
115 X_REQUEST_ID in NUMBER,
116 X_PROGRAM_APPLICATION_ID in NUMBER,
117 X_PROGRAM_ID in NUMBER,
118 X_PROGRAM_UPDATE_DATE in DATE,
119 X_CREATED_BY_APPLICATION_ID in NUMBER,
120 X_LOCATION_TYPE_CODE in VARCHAR2,
121 X_START_DATE_ACTIVE in DATE,
122 X_END_DATE_ACTIVE in DATE,
123 X_AREA1_ID in NUMBER,
124 X_AREA1_CODE in VARCHAR2,
125 X_AREA2_ID in NUMBER,
126 X_AREA2_CODE in VARCHAR2,
127 X_COUNTRY_ID in NUMBER,
128 X_COUNTRY_CODE in VARCHAR2,
129 X_COUNTRY_REGION_ID in NUMBER,
130 X_COUNTRY_REGION_CODE in VARCHAR2,
131 X_STATE_ID in NUMBER,
132 X_STATE_CODE in VARCHAR2,
133 X_STATE_REGION_ID in NUMBER,
134 X_STATE_REGION_CODE in VARCHAR2,
135 X_CITY_ID in NUMBER,
136 X_CITY_CODE in VARCHAR2,
137 X_POSTAL_CODE_ID in NUMBER,
138 X_LAST_UPDATE_DATE in DATE,
139 X_LAST_UPDATED_BY in NUMBER,
140 X_LAST_UPDATE_LOGIN in NUMBER
141 ) is
142 begin
143 update JTF_LOC_HIERARCHIES_B set
144 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
145 REQUEST_ID = X_REQUEST_ID,
146 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
147 PROGRAM_ID = X_PROGRAM_ID,
148 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
149 CREATED_BY_APPLICATION_ID = X_CREATED_BY_APPLICATION_ID,
150 LOCATION_TYPE_CODE = X_LOCATION_TYPE_CODE,
151 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
152 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
153 AREA1_ID = X_AREA1_ID,
154 AREA1_CODE = X_AREA1_CODE,
155 AREA2_ID = X_AREA2_ID,
156 AREA2_CODE = X_AREA2_CODE,
157 COUNTRY_ID = X_COUNTRY_ID,
158 COUNTRY_CODE = X_COUNTRY_CODE,
159 COUNTRY_REGION_ID = X_COUNTRY_REGION_ID,
160 COUNTRY_REGION_CODE = X_COUNTRY_REGION_CODE,
161 STATE_ID = X_STATE_ID,
162 STATE_CODE = X_STATE_CODE,
163 STATE_REGION_ID = X_STATE_REGION_ID,
164 STATE_REGION_CODE = X_STATE_REGION_CODE,
165 CITY_ID = X_CITY_ID,
166 CITY_CODE = X_CITY_CODE,
167 POSTAL_CODE_ID = X_POSTAL_CODE_ID,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
171 where LOCATION_HIERARCHY_ID = X_LOCATION_HIERARCHY_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 X_LOCATION_HIERARCHY_ID in NUMBER
180 ) is
181 begin
182 delete from JTF_LOC_HIERARCHIES_B
183 where LOCATION_HIERARCHY_ID = X_LOCATION_HIERARCHY_ID;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188 end DELETE_ROW;
189
190 procedure LOAD_ROW(
191 X_LOCATION_HIERARCHY_ID in NUMBER,
192 X_REQUEST_ID in NUMBER,
193 X_PROGRAM_APPLICATION_ID in NUMBER,
194 X_PROGRAM_ID in NUMBER,
195 X_PROGRAM_UPDATE_DATE in DATE,
196 X_CREATED_BY_APPLICATION_ID in NUMBER,
197 X_LOCATION_TYPE_CODE in VARCHAR2,
198 X_START_DATE_ACTIVE in DATE,
199 X_END_DATE_ACTIVE in DATE,
200 X_AREA1_ID in NUMBER,
201 X_AREA1_CODE in VARCHAR2,
202 X_AREA2_ID in NUMBER,
203 X_AREA2_CODE in VARCHAR2,
204 X_COUNTRY_ID in NUMBER,
205 X_COUNTRY_CODE in VARCHAR2,
206 X_COUNTRY_REGION_ID in NUMBER,
207 X_COUNTRY_REGION_CODE in VARCHAR2,
208 X_STATE_ID in NUMBER,
209 X_STATE_CODE in VARCHAR2,
210 X_STATE_REGION_ID in NUMBER,
211 X_STATE_REGION_CODE in VARCHAR2,
212 X_CITY_ID in NUMBER,
213 X_CITY_CODE in VARCHAR2,
214 X_POSTAL_CODE_ID in NUMBER,
215 X_OWNER in VARCHAR2
216 ) is
217
218 l_user_id number := 0;
219 l_obj_verno number;
220 l_dummy_char varchar2(1);
221 l_row_id varchar2(100);
222 l_hier_id number;
223
224 cursor c_obj_verno is
225 select object_version_number
226 from JTF_LOC_HIERARCHIES_B
227 where LOCATION_HIERARCHY_ID = X_LOCATION_HIERARCHY_ID;
228
229 cursor c_chk_hier_exists is
230 select 'x'
231 from JTF_LOC_HIERARCHIES_B
232 where LOCATION_HIERARCHY_ID = X_LOCATION_HIERARCHY_ID;
233
234 cursor c_get_hierid is
235 select JTF_LOC_HIERARCHIES_B_S.nextval
236 from dual;
237
238 BEGIN
239
240 if X_OWNER = 'SEED' then
241 l_user_id := 1;
242 end if;
243
244 open c_chk_hier_exists;
245 fetch c_chk_hier_exists into l_dummy_char;
246 if c_chk_hier_exists%notfound
247 then
248 close c_chk_hier_exists;
249 if X_LOCATION_HIERARCHY_ID is null
250 then
251 open c_get_hierid;
252 fetch c_get_hierid into l_hier_id;
253 close c_get_hierid;
254 else
255 l_hier_id := X_LOCATION_HIERARCHY_ID;
256 end if;
257 l_obj_verno := 1;
258 JTF_LOC_HIERARCHIES_PKG.INSERT_ROW(
259 X_ROWID => l_row_id,
260 X_LOCATION_HIERARCHY_ID => l_hier_id,
261 X_OBJECT_VERSION_NUMBER => l_obj_verno,
262 X_REQUEST_ID => X_REQUEST_ID,
263 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
264 X_PROGRAM_ID => X_PROGRAM_ID,
265 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
266 X_CREATED_BY_APPLICATION_ID => X_CREATED_BY_APPLICATION_ID,
267 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
268 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
269 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
270 X_AREA1_ID => X_AREA1_ID,
271 X_AREA1_CODE => X_AREA1_CODE,
272 X_AREA2_ID => X_AREA2_ID,
273 X_AREA2_CODE => X_AREA2_CODE,
274 X_COUNTRY_ID => X_COUNTRY_ID,
275 X_COUNTRY_CODE => X_COUNTRY_CODE,
276 X_COUNTRY_REGION_ID => X_COUNTRY_REGION_ID,
277 X_COUNTRY_REGION_CODE => X_COUNTRY_REGION_CODE,
278 X_STATE_ID => X_STATE_ID,
279 X_STATE_CODE => X_STATE_CODE,
280 X_STATE_REGION_ID => X_STATE_REGION_ID,
281 X_STATE_REGION_CODE => X_STATE_REGION_CODE,
282 X_CITY_ID => X_CITY_ID,
283 X_CITY_CODE => X_CITY_CODE,
284 X_POSTAL_CODE_ID => X_POSTAL_CODE_ID,
285 X_CREATION_DATE => SYSDATE,
286 X_CREATED_BY => l_user_id,
287 X_LAST_UPDATE_DATE => SYSDATE,
288 X_LAST_UPDATED_BY => l_user_id,
289 X_LAST_UPDATE_LOGIN => 0
290 );
291 else
292 close c_chk_hier_exists;
293 open c_obj_verno;
294 fetch c_obj_verno into l_obj_verno;
295 close c_obj_verno;
296 JTF_LOC_HIERARCHIES_PKG.UPDATE_ROW(
297 X_LOCATION_HIERARCHY_ID => X_LOCATION_HIERARCHY_ID,
298 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
299 X_REQUEST_ID => X_REQUEST_ID,
300 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
301 X_PROGRAM_ID => X_PROGRAM_ID,
302 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
303 X_CREATED_BY_APPLICATION_ID => X_CREATED_BY_APPLICATION_ID,
304 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
305 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
306 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
307 X_AREA1_ID => X_AREA1_ID,
308 X_AREA1_CODE => X_AREA1_CODE,
309 X_AREA2_ID => X_AREA2_ID,
310 X_AREA2_CODE => X_AREA2_CODE,
311 X_COUNTRY_ID => X_COUNTRY_ID,
312 X_COUNTRY_CODE => X_COUNTRY_CODE,
313 X_COUNTRY_REGION_ID => X_COUNTRY_REGION_ID,
314 X_COUNTRY_REGION_CODE => X_COUNTRY_REGION_CODE,
315 X_STATE_ID => X_STATE_ID,
316 X_STATE_CODE => X_STATE_CODE,
317 X_STATE_REGION_ID => X_STATE_REGION_ID,
318 X_STATE_REGION_CODE => X_STATE_REGION_CODE,
319 X_CITY_ID => X_CITY_ID,
320 X_CITY_CODE => X_CITY_CODE,
321 X_POSTAL_CODE_ID => X_POSTAL_CODE_ID,
322 X_LAST_UPDATE_DATE => SYSDATE,
323 X_LAST_UPDATED_BY => l_user_id,
324 X_LAST_UPDATE_LOGIN => 0
325 );
326 end if;
327 END LOAD_ROW;
328
329 end JTF_LOC_HIERARCHIES_PKG;