[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_WMS_RTP_F_C
Source
1 PACKAGE BODY OPI_DBI_WMS_RTP_F_C AS
2 /* $Header: OPIDEWMSRTPB.pls 120.0 2005/05/24 17:14:27 appldev noship $ */
3 g_init boolean := false;
4
5 /* PUBLIC PROCEDURE */
6 PROCEDURE initial_load (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER)
8 IS
9 l_opi_schema VARCHAR2(30);
10 l_status VARCHAR2(30);
11 l_industry VARCHAR2(30);
12
13 l_stmt VARCHAR2(4000);
14 BEGIN
15 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_opi_schema)) THEN
16 l_stmt := 'TRUNCATE TABLE ' || l_opi_schema || '.OPI_DBI_WMS_RTP_F';
17 EXECUTE IMMEDIATE l_stmt;
18 g_init := true;
19 populate_rtp_fact (errbuf, retcode);
20 END IF;
21
22 EXCEPTION
23 WHEN OTHERS THEN
24 Errbuf:= Sqlerrm;
25 Retcode:=sqlcode;
26
27 ROLLBACK;
28 POA_LOG.debug_line('initial_load' || Sqlerrm || sqlcode || sysdate);
29 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
30
31 END initial_load;
32
33
34
35 /* PUBLIC PROCEDURE */
36 PROCEDURE populate_rtp_fact (errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY NUMBER)
38 IS
39 l_go_ahead BOOLEAN := false;
40 l_count NUMBER := 0;
41
42 l_opi_schema VARCHAR2(30);
43 l_status VARCHAR2(30);
44 l_industry VARCHAR2(30);
45
46 l_stmt varchar2(4000);
47 l_start_date VARCHAR2(22);
48 l_end_date varchar2(22);
49 l_glob_date VARCHAR2(22);
50
51 l_start_time DATE;
52 l_login number;
53 l_user number;
54 l_dop NUMBER := 1;
55 d_start_date DATE;
56 d_end_date DATE;
57 d_glob_date DATE;
58 BEGIN
59 Errbuf :=NULL;
60 Retcode:=0;
61
62 DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'OPI DBI RTP COLLECT', action_name => 'start');
63 l_dop := bis_common_parameters.get_degree_of_parallelism;
64 -- default DOP to profile in EDW_PARALLEL_SRC if 2nd param is not passed
65 l_go_ahead := bis_collection_utilities.setup('OPIDBIRTP');
66 if (g_init) then
67 execute immediate 'alter session set hash_area_size=104857600';
68 execute immediate 'alter session set sort_area_size=104857600';
69 end if;
70 IF (NOT l_go_ahead) THEN
71 errbuf := fnd_message.get;
72 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
73 END IF;
74 bis_collection_utilities.g_debug := FALSE;
75
76 IF(g_init) THEN
77 l_start_date := To_char(bis_common_parameters.get_global_start_date
78 , 'YYYY/MM/DD HH24:MI:SS');
79 d_start_date := bis_common_parameters.get_global_start_date;
80 ELSE
81 l_start_date := To_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('OPIDBIRTP')) - 0.004,'YYYY/MM/DD HH24:MI:SS');
82 /* note that if there is not a success record in the log, we should get global start date as l_start_date */
83 d_start_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('OPIDBIRTP')) - 0.004;
84 END IF;
85
86 l_end_date := To_char(Sysdate, 'YYYY/MM/DD HH24:MI:SS');
87 d_end_date := Sysdate;
88
89 bis_collection_utilities.log( 'The collection range is from '||
90 l_start_date ||' to '|| l_end_date, 0);
91
92 l_glob_date := To_char(bis_common_parameters.get_global_start_date, 'YYYY/MM/DD HH24:MI:SS');
93 d_glob_date := bis_common_parameters.get_global_start_date;
94
95 DBMS_APPLICATION_INFO.SET_ACTION('rates');
96 if (not(fnd_installation.get_app_info('OPI', l_status, l_industry, l_opi_schema))) then
97 bis_collection_utilities.log('Error getting app info '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
98 RAISE_APPLICATION_ERROR (-20000, 'Error in GET_APP_INFO: ' || errbuf);
99 end if;
100 l_stmt := 'TRUNCATE TABLE ' || l_opi_schema || '.OPI_DBI_WMS_UOM_RATES';
101 execute immediate l_stmt;
102
103 if(g_init) then
104
105 insert /*+ APPEND PARALLEL (opi_dbi_wms_uom_rates) */ into opi_dbi_wms_uom_rates
106 (
107 inventory_item_id,
108 primary_uom_code,
109 transaction_uom_code,
110 rate
111 )
112 select
113 inventory_item_id,
114 primary_uom_code,
115 transaction_uom_code,
116 ( case when primary_uom_code = transaction_uom_code then 1
117 else opi_dbi_wms_utility_pkg.get_uom_rate(
118 inventory_item_id,
119 primary_uom_code,
120 transaction_uom_code
121 )
122 end
123 ) rate
124 from
125 (
126 select /*+ PARALLEL (wdth) PARALLEL (msi) PARALLEL (rtx) PARALLEL (oplan)
127 PARALLEL (sinv) USE_HASH(wdth) USE_HASH(msi) USE_HASH(rtx) USE_HASH(oplan)
128 USE_HASH(sinv) pq_distribute(oplan hash,hash) pq_distribute(msi hash,hash)
129 pq_distribute(rtx hash,hash) */ distinct
130 wdth.inventory_item_id,
131 msi.primary_uom_code,
132 wdth.transaction_uom_code
133 from
134 wms_dispatched_tasks_history wdth,
135 mtl_system_items msi,
136 poa_dbi_rtx_f rtx,
137 wms_op_plans_b oplan,
138 mtl_secondary_inventories sinv
139 where
140 nvl(wdth.is_parent, 'N') = 'Y'
141 and wdth.task_type = 2
142 and wdth.status = 6
143 and nvl(sinv.subinventory_type, 1) = 1
144 and oplan.plan_type_id = 1
145 and wdth.dest_subinventory_code = sinv.secondary_inventory_name
146 and wdth.inventory_item_id = msi.inventory_item_id
147 and wdth.organization_id = msi.organization_id
148 and wdth.source_document_id = rtx.transaction_id
149 and wdth.organization_id = sinv.organization_id
150 and wdth.operation_plan_id = oplan.operation_plan_id
151 and wdth.last_update_date >= d_start_date
152 and (wdth.last_update_date is null or wdth.last_update_date <= d_end_date)
153 and wdth.creation_date >= d_start_date
154 );
155
156 else
157
158 insert /*+ APPEND */ into opi_dbi_wms_uom_rates
159 (
160 inventory_item_id,
161 primary_uom_code,
162 transaction_uom_code,
163 rate
164 )
165 select
166 inventory_item_id,
167 primary_uom_code,
168 transaction_uom_code,
169 ( case when primary_uom_code = transaction_uom_code then 1
170 else opi_dbi_wms_utility_pkg.get_uom_rate(
171 inventory_item_id,
172 primary_uom_code,
173 transaction_uom_code
174 )
175 end
176 ) rate
177 from
178 (
179 select /*+ leading(wdth) */ distinct
180 wdth.inventory_item_id,
181 msi.primary_uom_code,
182 wdth.transaction_uom_code
183 from
184 wms_dispatched_tasks_history wdth,
185 mtl_system_items msi,
186 poa_dbi_rtx_f rtx,
187 wms_op_plans_b oplan,
188 mtl_secondary_inventories sinv
189 where
190 nvl(wdth.is_parent, 'N') = 'Y'
191 and wdth.task_type = 2
192 and wdth.status = 6
193 and nvl(sinv.subinventory_type, 1) = 1
194 and oplan.plan_type_id = 1
195 and wdth.dest_subinventory_code = sinv.secondary_inventory_name
196 and wdth.inventory_item_id = msi.inventory_item_id
197 and wdth.organization_id = msi.organization_id
198 and wdth.source_document_id = rtx.transaction_id
199 and wdth.organization_id = sinv.organization_id
200 and wdth.operation_plan_id = oplan.operation_plan_id
201 and wdth.last_update_date between d_start_date and d_end_date
202 and wdth.creation_date >= d_glob_date
203 );
204
205 end if;
206
207 if (opi_dbi_wms_utility_pkg.g_missing_uom) then
208 opi_dbi_wms_utility_pkg.g_missing_uom := false;
209 errbuf := 'there are missing uom conversions';
210 raise_application_error (-20000, 'error in rates table collection: ' || errbuf);
211 end if;
212
213 COMMIT;
214 DBMS_APPLICATION_INFO.SET_ACTION('stats');
215 fnd_stats.gather_table_stats(OWNNAME => l_opi_schema, TABNAME => 'OPI_DBI_WMS_UOM_RATES');
216
217 bis_collection_utilities.log('Populate base table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
218 -- bis_collection_utilities.log('Identified '||
219
220 l_start_time := sysdate;
221 l_login := fnd_global.login_id;
222 l_user := fnd_global.user_id;
223 DBMS_APPLICATION_INFO.SET_ACTION('collect');
224
225 IF (g_init) THEN
226
227 bis_collection_utilities.log('Initial Load - populate base fact. '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
228 INSERT /*+ APPEND PARALLEL(t) */ INTO opi_dbi_wms_rtp_f t (
229 task_id,
230 transaction_id,
231 organization_id,
232 subinventory_code,
233 inventory_item_id,
234 operation_plan_id,
235 op_plan_instance_id,
236 source_document_id,
237 putaway_completion_date,
238 putaway_quantity,
239 putaway_uom_code,
240 putaway_uom_conv_rate,
241 rcv_transaction_date,
242 last_update_date,
243 created_by,
244 last_updated_by,
245 last_update_login,
246 program_id,
247 program_login_id,
248 program_application_id,
249 request_id
250 ) SELECT task_id,
251 transaction_id,
252 organization_id,
253 subinventory_code,
254 inventory_item_id,
255 operation_plan_id,
256 op_plan_instance_id,
257 source_document_id,
258 putaway_completion_date,
259 putaway_quantity,
260 putaway_uom_code,
261 putaway_uom_conv_rate,
262 rcv_transaction_date,
263 current_time,
264 login_id,
265 login_id,
266 user_id,
267 null,
268 null,
269 null,
270 null
271 FROM (
272 SELECT /*+ PARALLEL(wdth) PARALLEL(rtx) PARALLEL(oplan) PARALLEL(sinv) PARALLEL(msi)
273 PARALLEL(rat) USE_HASH(wdth) use_hash(rtx) use_hash(oplan) use_hash(sinv)
274 use_hash(msi) use_hash(rat) pq_distribute(wdth hash,hash)
275 pq_distribute(oplan hash,hash) pq_distribute(msi hash,hash) pq_distribute(rtx hash,hash) */
276 wdth.task_id,
277 wdth.transaction_id,
278 wdth.organization_id,
279 wdth.dest_subinventory_code subinventory_code,
280 wdth.inventory_item_id,
281 wdth.operation_plan_id,
282 wdth.op_plan_instance_id,
283 wdth.source_document_id,
284 wdth.drop_off_time putaway_completion_date,
285 wdth.transaction_quantity putaway_quantity,
286 msi.primary_uom_code putaway_uom_code,
287 rtx.receive_txn_date rcv_transaction_date,
288 rat.rate putaway_uom_conv_rate,
289 l_start_time current_time,
290 l_login login_id,
291 l_user user_id
292 FROM wms_dispatched_tasks_history wdth,
293 poa_dbi_rtx_f rtx,
294 wms_op_plans_b oplan,
295 mtl_secondary_inventories sinv,
296 mtl_system_items msi,
297 opi_dbi_wms_uom_rates rat
298 WHERE nvl(wdth.is_parent, 'N') = 'Y' -- make sure that op plan started after inspections have 18/1/27 and task_type of 2, checked that 18/1/27 is true.. need to check 2
299 and wdth.task_type = 2
300 and wdth.status = 6
301 and nvl(sinv.subinventory_type, 1) = 1
302 and oplan.plan_type_id = 1
303 and wdth.dest_subinventory_code = sinv.secondary_inventory_name
304 and wdth.inventory_item_id = msi.inventory_item_id
305 and wdth.organization_id = msi.organization_id
306 and wdth.organization_id = sinv.organization_id
307 and wdth.operation_plan_id = oplan.operation_plan_id
308 and wdth.source_document_id = rtx.transaction_id
309 and wdth.inventory_item_id = rat.inventory_item_id
310 and wdth.transaction_uom_code = rat.transaction_uom_code
311 and msi.primary_uom_code = rat.primary_uom_code
312 and wdth.creation_date >= d_start_date
313 and wdth.last_update_date >= d_start_date
314 and (wdth.last_update_date is null or wdth.last_update_date <= d_end_date) );
315 COMMIT;
316 else
317 /*incremental branch*/
318 merge INTO opi_dbi_wms_rtp_f T
319 using (
320 select /*+ leading(wdth) */
321 wdth.task_id,
322 wdth.transaction_id,
323 wdth.organization_id,
327 wdth.op_plan_instance_id,
324 wdth.dest_subinventory_code subinventory_code,
325 wdth.inventory_item_id,
326 wdth.operation_plan_id,
328 wdth.source_document_id,
329 wdth.drop_off_time putaway_completion_date,
330 wdth.transaction_quantity putaway_quantity,
331 msi.primary_uom_code putaway_uom_code,
332 rtx.receive_txn_date rcv_transaction_date,
333 rat.rate putaway_uom_conv_rate,
334 l_start_time current_time,
335 l_login login_id,
336 l_user user_id
337 FROM wms_dispatched_tasks_history wdth,
338 poa_dbi_rtx_f rtx,
339 wms_op_plans_b oplan,
340 mtl_secondary_inventories sinv,
341 mtl_system_items msi,
342 opi_dbi_wms_uom_rates rat
343 WHERE nvl(wdth.is_parent, 'N') = 'Y' -- make sure that op plan started after inspections have 18/1/27 and task_type of 2, checked that 18/1/27 is true.. need to check 2
344 and wdth.task_type = 2
345 and wdth.status = 6
346 and nvl(sinv.subinventory_type, 1) = 1
347 and oplan.plan_type_id = 1
348 and wdth.dest_subinventory_code = sinv.secondary_inventory_name
349 and wdth.inventory_item_id = msi.inventory_item_id
350 and wdth.organization_id = msi.organization_id
351 and wdth.organization_id = sinv.organization_id
352 and wdth.operation_plan_id = oplan.operation_plan_id
353 and wdth.source_document_id = rtx.transaction_id
354 and wdth.last_update_date between d_start_date and d_end_date
355 and wdth.inventory_item_id = rat.inventory_item_id
356 and msi.primary_uom_code = rat.primary_uom_code
357 and wdth.transaction_uom_code = rat.transaction_uom_code
358 and wdth.creation_date >= d_glob_date
359 ) s
360 ON (t.task_id = s.task_id)
361 WHEN matched THEN UPDATE SET
362 t.organization_id = s.organization_id,
363 t.subinventory_code = s.subinventory_code,
364 t.inventory_item_id = s.inventory_item_id,
365 t.operation_plan_id = s.operation_plan_id,
366 t.op_plan_instance_id = s.op_plan_instance_id,
367 t.source_document_id = s.source_document_id,
368 t.putaway_completion_date = s.putaway_completion_date,
369 t.putaway_quantity = s.putaway_quantity,
370 t.putaway_uom_code = s.putaway_uom_code,
371 t.putaway_uom_conv_rate = s.putaway_uom_conv_rate,
372 t.rcv_transaction_date = s.rcv_transaction_date ,
373 t.last_update_date = sysdate,
374 t.created_by = s.user_id,
375 t.last_updated_by = s.user_id,
376 t.last_update_login = s.login_id,
377 t.program_id = null,
378 t.program_login_id = null,
379 t.program_application_id = null,
380 t.request_id = null
381
382 WHEN NOT matched THEN INSERT (
383 t.task_id,
384 t.transaction_id,
385 t.organization_id,
386 t.subinventory_code,
387 t.inventory_item_id,
388 t.operation_plan_id,
389 t.op_plan_instance_id,
390 t.source_document_id,
391 t.putaway_completion_date,
392 t.putaway_quantity,
393 t.putaway_uom_code,
394 t.putaway_uom_conv_rate,
395 t.rcv_transaction_date,
396 t.last_update_date,
397 t.created_by,
398 t.last_updated_by,
399 t.last_update_login,
400 t.program_id,
401 t.program_login_id,
402 t.program_application_id,
403 t.request_id
404 ) VALUES (
405 s.task_id,
406 s.transaction_id,
407 s.organization_id,
408 s.subinventory_code,
409 s.inventory_item_id,
410 s.operation_plan_id,
411 s.op_plan_instance_id,
412 s.source_document_id,
413 s.putaway_completion_date,
414 s.putaway_quantity,
415 s.putaway_uom_code,
416 s.putaway_uom_conv_rate,
417 s.rcv_transaction_date,
418 sysdate,
419 s.user_id,
420 s.user_id,
421 s.login_id,
422 null,
423 null,
424 null,
425 null
426 );
427
428 COMMIT;
429
430 END IF;
431
432 bis_collection_utilities.log('Collection complete '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
433 bis_collection_utilities.wrapup(TRUE, l_count, 'OPI DBI WMS RTP COLLECTION SUCEEDED', To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
434 g_init := false;
435 DBMS_APPLICATION_INFO.set_module(NULL, NULL);
436 EXCEPTION
437 WHEN OTHERS THEN
438 DBMS_APPLICATION_INFO.SET_ACTION('error');
439 errbuf:=sqlerrm;
440 retcode:=sqlcode;
441 bis_collection_utilities.log('Collection failed with '||errbuf||':'||retcode||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
442 -- dbms_output.put_line(l_start_date || l_end_date);
443 bis_collection_utilities.wrapup(FALSE, l_count, errbuf||':'||retcode,
444 To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
445
446
447 RAISE;
448 END populate_rtp_fact;
449
450
451 END OPI_DBI_WMS_RTP_F_C;