[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_RESRC_LEVL_PUB_W
Source
1 package body ahl_ltp_resrc_levl_pub_w as
2 /* $Header: AHLWRLGB.pls 120.2 2006/05/04 07:41 anraj noship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5 rosetta_g_mistake_date_high date := to_date('01/01/+4710', 'MM/DD/SYYYY');
6 rosetta_g_mistake_date_low date := to_date('01/01/-4710', 'MM/DD/SYYYY');
7
8 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
9 function rosetta_g_miss_date_in_map(d date) return date as
10 begin
11 if d > rosetta_g_mistake_date_high then return fnd_api.g_miss_date; end if;
12 if d < rosetta_g_mistake_date_low then return fnd_api.g_miss_date; end if;
13 return d;
14 end;
15
16 procedure rosetta_table_copy_in_p3(t out nocopy ahl_ltp_resrc_levl_pub.aval_resources_tbl, a0 JTF_VARCHAR2_TABLE_100
17 , a1 JTF_DATE_TABLE
18 , a2 JTF_DATE_TABLE
19 , a3 JTF_NUMBER_TABLE
20 , a4 JTF_VARCHAR2_TABLE_100
21 , a5 JTF_NUMBER_TABLE
22 , a6 JTF_NUMBER_TABLE
23 , a7 JTF_VARCHAR2_TABLE_100
24 , a8 JTF_VARCHAR2_TABLE_100
25 , a9 JTF_VARCHAR2_TABLE_300
26 ) as
27 ddindx binary_integer; indx binary_integer;
28 begin
29 if a0 is not null and a0.count > 0 then
30 if a0.count > 0 then
31 indx := a0.first;
32 ddindx := 1;
33 while true loop
34 t(ddindx).period_string := a0(indx);
35 t(ddindx).period_start := rosetta_g_miss_date_in_map(a1(indx));
36 t(ddindx).period_end := rosetta_g_miss_date_in_map(a2(indx));
37 t(ddindx).required_capacity := a3(indx);
38 t(ddindx).dept_name := a4(indx);
39 t(ddindx).resource_id := a5(indx);
40 t(ddindx).resource_type := a6(indx);
41 t(ddindx).resource_type_meaning := a7(indx);
42 t(ddindx).resource_name := a8(indx);
43 t(ddindx).resource_description := a9(indx);
44 ddindx := ddindx+1;
45 if a0.last =indx
46 then exit;
47 end if;
48 indx := a0.next(indx);
49 end loop;
50 end if;
51 end if;
52 end rosetta_table_copy_in_p3;
53 procedure rosetta_table_copy_out_p3(t ahl_ltp_resrc_levl_pub.aval_resources_tbl, a0 out nocopy JTF_VARCHAR2_TABLE_100
54 , a1 out nocopy JTF_DATE_TABLE
55 , a2 out nocopy JTF_DATE_TABLE
56 , a3 out nocopy JTF_NUMBER_TABLE
57 , a4 out nocopy JTF_VARCHAR2_TABLE_100
58 , a5 out nocopy JTF_NUMBER_TABLE
59 , a6 out nocopy JTF_NUMBER_TABLE
60 , a7 out nocopy JTF_VARCHAR2_TABLE_100
61 , a8 out nocopy JTF_VARCHAR2_TABLE_100
62 , a9 out nocopy JTF_VARCHAR2_TABLE_300
63 ) as
64 ddindx binary_integer; indx binary_integer;
65 begin
66 if t is null or t.count = 0 then
67 a0 := JTF_VARCHAR2_TABLE_100();
68 a1 := JTF_DATE_TABLE();
69 a2 := JTF_DATE_TABLE();
70 a3 := JTF_NUMBER_TABLE();
71 a4 := JTF_VARCHAR2_TABLE_100();
72 a5 := JTF_NUMBER_TABLE();
73 a6 := JTF_NUMBER_TABLE();
74 a7 := JTF_VARCHAR2_TABLE_100();
75 a8 := JTF_VARCHAR2_TABLE_100();
76 a9 := JTF_VARCHAR2_TABLE_300();
77 else
78 a0 := JTF_VARCHAR2_TABLE_100();
79 a1 := JTF_DATE_TABLE();
80 a2 := JTF_DATE_TABLE();
81 a3 := JTF_NUMBER_TABLE();
82 a4 := JTF_VARCHAR2_TABLE_100();
83 a5 := JTF_NUMBER_TABLE();
84 a6 := JTF_NUMBER_TABLE();
85 a7 := JTF_VARCHAR2_TABLE_100();
86 a8 := JTF_VARCHAR2_TABLE_100();
87 a9 := JTF_VARCHAR2_TABLE_300();
88 if t.count > 0 then
89 a0.extend(t.count);
90 a1.extend(t.count);
91 a2.extend(t.count);
92 a3.extend(t.count);
93 a4.extend(t.count);
94 a5.extend(t.count);
95 a6.extend(t.count);
96 a7.extend(t.count);
97 a8.extend(t.count);
98 a9.extend(t.count);
99 ddindx := t.first;
100 indx := 1;
101 while true loop
102 a0(indx) := t(ddindx).period_string;
103 a1(indx) := t(ddindx).period_start;
104 a2(indx) := t(ddindx).period_end;
105 a3(indx) := t(ddindx).required_capacity;
106 a4(indx) := t(ddindx).dept_name;
107 a5(indx) := t(ddindx).resource_id;
108 a6(indx) := t(ddindx).resource_type;
109 a7(indx) := t(ddindx).resource_type_meaning;
110 a8(indx) := t(ddindx).resource_name;
111 a9(indx) := t(ddindx).resource_description;
112 indx := indx+1;
113 if t.last =ddindx
114 then exit;
115 end if;
116 ddindx := t.next(ddindx);
117 end loop;
118 end if;
119 end if;
120 end rosetta_table_copy_out_p3;
121
122 procedure rosetta_table_copy_in_p4(t out nocopy ahl_ltp_resrc_levl_pub.resource_con_tbl, a0 JTF_NUMBER_TABLE
123 , a1 JTF_NUMBER_TABLE
124 , a2 JTF_VARCHAR2_TABLE_100
125 , a3 JTF_VARCHAR2_TABLE_100
126 , a4 JTF_VARCHAR2_TABLE_100
127 , a5 JTF_VARCHAR2_TABLE_100
128 , a6 JTF_NUMBER_TABLE
129 , a7 JTF_NUMBER_TABLE
130 , a8 JTF_NUMBER_TABLE
131 ) as
132 ddindx binary_integer; indx binary_integer;
133 begin
134 if a0 is not null and a0.count > 0 then
135 if a0.count > 0 then
136 indx := a0.first;
137 ddindx := 1;
138 while true loop
139 t(ddindx).visit_id := a0(indx);
140 t(ddindx).task_id := a1(indx);
141 t(ddindx).visit_name := a2(indx);
142 t(ddindx).visit_task_name := a3(indx);
143 t(ddindx).task_type_code := a4(indx);
144 t(ddindx).dept_name := a5(indx);
145 t(ddindx).quantity := a6(indx);
146 t(ddindx).required_units := a7(indx);
147 t(ddindx).available_units := a8(indx);
148 ddindx := ddindx+1;
149 if a0.last =indx
150 then exit;
151 end if;
152 indx := a0.next(indx);
153 end loop;
154 end if;
155 end if;
156 end rosetta_table_copy_in_p4;
157 procedure rosetta_table_copy_out_p4(t ahl_ltp_resrc_levl_pub.resource_con_tbl, a0 out nocopy JTF_NUMBER_TABLE
158 , a1 out nocopy JTF_NUMBER_TABLE
159 , a2 out nocopy JTF_VARCHAR2_TABLE_100
160 , a3 out nocopy JTF_VARCHAR2_TABLE_100
161 , a4 out nocopy JTF_VARCHAR2_TABLE_100
162 , a5 out nocopy JTF_VARCHAR2_TABLE_100
163 , a6 out nocopy JTF_NUMBER_TABLE
164 , a7 out nocopy JTF_NUMBER_TABLE
165 , a8 out nocopy JTF_NUMBER_TABLE
166 ) as
167 ddindx binary_integer; indx binary_integer;
168 begin
169 if t is null or t.count = 0 then
170 a0 := JTF_NUMBER_TABLE();
171 a1 := JTF_NUMBER_TABLE();
172 a2 := JTF_VARCHAR2_TABLE_100();
173 a3 := JTF_VARCHAR2_TABLE_100();
174 a4 := JTF_VARCHAR2_TABLE_100();
175 a5 := JTF_VARCHAR2_TABLE_100();
176 a6 := JTF_NUMBER_TABLE();
177 a7 := JTF_NUMBER_TABLE();
178 a8 := JTF_NUMBER_TABLE();
179 else
180 a0 := JTF_NUMBER_TABLE();
181 a1 := JTF_NUMBER_TABLE();
182 a2 := JTF_VARCHAR2_TABLE_100();
183 a3 := JTF_VARCHAR2_TABLE_100();
184 a4 := JTF_VARCHAR2_TABLE_100();
185 a5 := JTF_VARCHAR2_TABLE_100();
186 a6 := JTF_NUMBER_TABLE();
187 a7 := JTF_NUMBER_TABLE();
188 a8 := JTF_NUMBER_TABLE();
189 if t.count > 0 then
190 a0.extend(t.count);
191 a1.extend(t.count);
192 a2.extend(t.count);
193 a3.extend(t.count);
194 a4.extend(t.count);
195 a5.extend(t.count);
196 a6.extend(t.count);
197 a7.extend(t.count);
198 a8.extend(t.count);
199 ddindx := t.first;
200 indx := 1;
201 while true loop
202 a0(indx) := t(ddindx).visit_id;
203 a1(indx) := t(ddindx).task_id;
204 a2(indx) := t(ddindx).visit_name;
205 a3(indx) := t(ddindx).visit_task_name;
206 a4(indx) := t(ddindx).task_type_code;
207 a5(indx) := t(ddindx).dept_name;
208 a6(indx) := t(ddindx).quantity;
209 a7(indx) := t(ddindx).required_units;
210 a8(indx) := t(ddindx).available_units;
211 indx := indx+1;
212 if t.last =ddindx
213 then exit;
214 end if;
215 ddindx := t.next(ddindx);
216 end loop;
217 end if;
218 end if;
219 end rosetta_table_copy_out_p4;
220
221 procedure derive_resource_capacity(p_api_version NUMBER
222 , p_init_msg_list VARCHAR2
223 , p_commit VARCHAR2
224 , p_validation_level NUMBER
225 , p_module_type VARCHAR2
226 , p5_a0 VARCHAR2
227 , p5_a1 VARCHAR2
228 , p5_a2 NUMBER
229 , p5_a3 NUMBER
230 , p5_a4 DATE
231 , p5_a5 DATE
232 , p5_a6 DATE
233 , p5_a7 DATE
234 , p5_a8 VARCHAR2
235 , p5_a9 NUMBER
236 , p5_a10 NUMBER
237 , p5_a11 NUMBER
238 , p5_a12 VARCHAR2
239 , p5_a13 VARCHAR2
240 , p6_a0 out nocopy JTF_VARCHAR2_TABLE_100
241 , p6_a1 out nocopy JTF_DATE_TABLE
242 , p6_a2 out nocopy JTF_DATE_TABLE
243 , p6_a3 out nocopy JTF_NUMBER_TABLE
244 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
245 , p6_a5 out nocopy JTF_NUMBER_TABLE
246 , p6_a6 out nocopy JTF_NUMBER_TABLE
247 , p6_a7 out nocopy JTF_VARCHAR2_TABLE_100
248 , p6_a8 out nocopy JTF_VARCHAR2_TABLE_100
249 , p6_a9 out nocopy JTF_VARCHAR2_TABLE_300
250 , x_return_status out nocopy VARCHAR2
251 , x_msg_count out nocopy NUMBER
252 , x_msg_data out nocopy VARCHAR2
253 )
254
255 as
256 ddp_req_resources ahl_ltp_resrc_levl_pub.req_resources_rec;
257 ddx_aval_resources_tbl ahl_ltp_resrc_levl_pub.aval_resources_tbl;
258 ddindx binary_integer; indx binary_integer;
259 begin
260
261 -- copy data to the local IN or IN-OUT args, if any
262
263
264
265
266
267 ddp_req_resources.org_name := p5_a0;
268 ddp_req_resources.dept_name := p5_a1;
269 ddp_req_resources.dept_id := p5_a2;
270 ddp_req_resources.plan_id := p5_a3;
271 ddp_req_resources.start_date := rosetta_g_miss_date_in_map(p5_a4);
272 ddp_req_resources.end_date := rosetta_g_miss_date_in_map(p5_a5);
273 ddp_req_resources.display_start_date := rosetta_g_miss_date_in_map(p5_a6);
274 ddp_req_resources.display_end_date := rosetta_g_miss_date_in_map(p5_a7);
275 ddp_req_resources.uom_code := p5_a8;
276 ddp_req_resources.required_capacity := p5_a9;
277 ddp_req_resources.resource_id := p5_a10;
278 ddp_req_resources.resource_type := p5_a11;
279 ddp_req_resources.aso_bom_type := p5_a12;
280 ddp_req_resources.resource_type_meaning := p5_a13;
281
282
283
284
285
286 -- here's the delegated call to the old PL/SQL routine
287 ahl_ltp_resrc_levl_pub.derive_resource_capacity(p_api_version,
288 p_init_msg_list,
289 p_commit,
290 p_validation_level,
291 p_module_type,
292 ddp_req_resources,
293 ddx_aval_resources_tbl,
294 x_return_status,
295 x_msg_count,
296 x_msg_data);
297
298 -- copy data back from the local variables to OUT or IN-OUT args, if any
299
300
301
302
303
304
305 ahl_ltp_resrc_levl_pub_w.rosetta_table_copy_out_p3(ddx_aval_resources_tbl, p6_a0
306 , p6_a1
307 , p6_a2
308 , p6_a3
309 , p6_a4
310 , p6_a5
311 , p6_a6
312 , p6_a7
313 , p6_a8
314 , p6_a9
315 );
316
317
318
319 end;
320
321 procedure derive_resource_consum(p_api_version NUMBER
322 , p_init_msg_list VARCHAR2
323 , p_commit VARCHAR2
324 , p_validation_level NUMBER
325 , p_module_type VARCHAR2
326 , p5_a0 VARCHAR2
327 , p5_a1 VARCHAR2
328 , p5_a2 NUMBER
329 , p5_a3 NUMBER
330 , p5_a4 DATE
331 , p5_a5 DATE
332 , p5_a6 DATE
333 , p5_a7 DATE
334 , p5_a8 VARCHAR2
335 , p5_a9 NUMBER
336 , p5_a10 NUMBER
337 , p5_a11 NUMBER
338 , p5_a12 VARCHAR2
339 , p5_a13 VARCHAR2
340 , p6_a0 out nocopy JTF_NUMBER_TABLE
341 , p6_a1 out nocopy JTF_NUMBER_TABLE
342 , p6_a2 out nocopy JTF_VARCHAR2_TABLE_100
343 , p6_a3 out nocopy JTF_VARCHAR2_TABLE_100
344 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
345 , p6_a5 out nocopy JTF_VARCHAR2_TABLE_100
346 , p6_a6 out nocopy JTF_NUMBER_TABLE
347 , p6_a7 out nocopy JTF_NUMBER_TABLE
348 , p6_a8 out nocopy JTF_NUMBER_TABLE
349 , x_return_status out nocopy VARCHAR2
350 , x_msg_count out nocopy NUMBER
351 , x_msg_data out nocopy VARCHAR2
352 )
353
354 as
355 ddp_req_resources ahl_ltp_resrc_levl_pub.req_resources_rec;
356 ddx_resource_con_tbl ahl_ltp_resrc_levl_pub.resource_con_tbl;
357 ddindx binary_integer; indx binary_integer;
358 begin
359
360 -- copy data to the local IN or IN-OUT args, if any
361
362
363
364
365
366 ddp_req_resources.org_name := p5_a0;
367 ddp_req_resources.dept_name := p5_a1;
368 ddp_req_resources.dept_id := p5_a2;
369 ddp_req_resources.plan_id := p5_a3;
370 ddp_req_resources.start_date := rosetta_g_miss_date_in_map(p5_a4);
371 ddp_req_resources.end_date := rosetta_g_miss_date_in_map(p5_a5);
372 ddp_req_resources.display_start_date := rosetta_g_miss_date_in_map(p5_a6);
373 ddp_req_resources.display_end_date := rosetta_g_miss_date_in_map(p5_a7);
374 ddp_req_resources.uom_code := p5_a8;
375 ddp_req_resources.required_capacity := p5_a9;
376 ddp_req_resources.resource_id := p5_a10;
377 ddp_req_resources.resource_type := p5_a11;
378 ddp_req_resources.aso_bom_type := p5_a12;
379 ddp_req_resources.resource_type_meaning := p5_a13;
380
381
382
383
384
385 -- here's the delegated call to the old PL/SQL routine
386 ahl_ltp_resrc_levl_pub.derive_resource_consum(p_api_version,
387 p_init_msg_list,
388 p_commit,
389 p_validation_level,
390 p_module_type,
391 ddp_req_resources,
392 ddx_resource_con_tbl,
393 x_return_status,
394 x_msg_count,
395 x_msg_data);
396
397 -- copy data back from the local variables to OUT or IN-OUT args, if any
398
399
400
401
402
403
404 ahl_ltp_resrc_levl_pub_w.rosetta_table_copy_out_p4(ddx_resource_con_tbl, p6_a0
405 , p6_a1
406 , p6_a2
407 , p6_a3
408 , p6_a4
409 , p6_a5
410 , p6_a6
411 , p6_a7
412 , p6_a8
413 );
414
415
416
417 end;
418
419 end ahl_ltp_resrc_levl_pub_w;