[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_WSH_FTE_OBJECTS_C
Source
1 PACKAGE BODY ISC_DBI_WSH_FTE_OBJECTS_C AS
2 /* $Header: ISCSCF9B.pls 120.8 2006/09/13 06:41:03 abhdixi noship $ */
3
4 g_batch_size NUMBER;
5 g_global_currency VARCHAR2(30);
6 g_global_rate_type VARCHAR2(80);
7 g_sec_global_currency VARCHAR2(30);
8 g_sec_global_rate_type VARCHAR2(80);
9 g_global_start_date DATE;
10 g_treasury_rate_type VARCHAR2(80);
11 g_reporting_weight_uom VARCHAR2(30);
12 g_reporting_volume_uom VARCHAR2(30);
13 g_reporting_distance_uom VARCHAR2(30);
14 g_new_arr_window NUMBER;
15
16 g_errbuf VARCHAR2(2000);
17 g_retcode VARCHAR2(200);
18 g_row_count NUMBER;
19 g_push_from_date DATE;
20 g_push_to_date DATE;
21 g_incre_start_date DATE;
22 g_load_mode VARCHAR2(30);
23 g_isc_schema VARCHAR2(50);
24 g_sec_curr_def VARCHAR2(1);
25
26 -- =================
27 -- Private Functions
28 -- =================
29
30 -- -----------
31 -- CHECK_SETUP
32 -- -----------
33
34 FUNCTION check_setup RETURN NUMBER IS
35
36 l_list dbms_sql.varchar2_table;
37 l_status VARCHAR2(30);
38 l_industry VARCHAR2(30);
39 l_setup NUMBER;
40
41 BEGIN
42
43 l_list(1) := 'BIS_GLOBAL_START_DATE';
44 IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN
45 BIS_COLLECTION_UTILITIES.put_line(' ');
46 BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the global start date has not been set up.');
47 BIS_COLLECTION_UTILITIES.put_line(' ');
48 l_setup := -999;
49 END IF;
50
51 IF (nvl(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'),'N') <> 'Y') THEN
52 BIS_COLLECTION_UTILITIES.put_line(' ');
53 BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the profile option "ISC: Shipping/Transportation Execution DBI Installation" has not been set to Y.');
54 BIS_COLLECTION_UTILITIES.put_line(' ');
55 l_setup := -999;
56 END IF;
57
58 g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
59 IF (g_sec_curr_def = 'E') THEN
60 BIS_COLLECTION_UTILITIES.put_line(' ');
61 BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.');
62 BIS_COLLECTION_UTILITIES.put_line(' ');
63 l_setup := -999;
64 END IF;
65
66 g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
67 BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
68
69 g_global_start_date := bis_common_parameters.get_global_start_date;
70 BIS_COLLECTION_UTILITIES.put_line('The global start date is ' || g_global_start_date);
71
72 g_global_currency := bis_common_parameters.get_currency_code;
73 BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
74
75 g_global_rate_type := bis_common_parameters.get_rate_type;
76 BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
77
78 g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
79 BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
80
81 g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
82 BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
83
84 g_treasury_rate_type := bis_common_parameters.get_treasury_rate_type;
85 -- IF (g_treasury_rate_type IS NULL) THEN
86 -- g_errbuf := 'Collection aborted because the set-up of the DBI Global Parameter "Treasury Rate Type" is incomplete. Please verify the proper set-up of the Treasury Rate Type.';
87 -- return(-1);
88 -- END IF;
89 BIS_COLLECTION_UTILITIES.put_line('The treasury rate type is ' || g_treasury_rate_type);
90
91 BEGIN
92 g_new_arr_window := FND_PROFILE.VALUE('FTE_CARRIER_ARR_WINDOW');
93 IF (g_new_arr_window IS NULL) THEN
94 BIS_COLLECTION_UTILITIES.put_line(' ');
95 BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up.');
96 BIS_COLLECTION_UTILITIES.put_line(' ');
97 l_setup := -999;
98 ELSE
99 BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is ' || g_new_arr_window);
100 END IF;
101 EXCEPTION
102 WHEN VALUE_ERROR THEN
103 g_retcode := sqlcode;
104 BIS_COLLECTION_UTILITIES.put_line(' ');
105 BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up correctly.');
106 BIS_COLLECTION_UTILITIES.Put_Line('Please set up the profile option as a valid number.');
107 BIS_COLLECTION_UTILITIES.put_line(' ');
108 l_setup := -999;
109 END;
110
111 IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, g_isc_schema)) THEN
112 BIS_COLLECTION_UTILITIES.put_line(' ');
113 BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted while retrieving schema information.');
114 BIS_COLLECTION_UTILITIES.put_line(' ');
115 l_setup := -999;
116 END IF;
117
118 IF (l_setup = -999) THEN
119 g_errbuf := 'Collection aborted because the setup has not been completed. Please refer to the log file for the details.';
120 return(-1);
121 END IF;
122
123 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp tables');
124 FII_UTIL.Start_Timer;
125
126 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WDD_LOG';
127 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WTS_LOG';
128 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FIH_LOG';
129
130 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_DETAILS';
131 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_LEGS';
132 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_TRIP_STOPS';
133 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FTE_INVOICES';
134 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_CURR_RATES';
135 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_UOM_RATES';
136
137 FII_UTIL.Stop_Timer;
138 FII_UTIL.Print_Timer('Truncated the temp tables in');
139 BIS_COLLECTION_UTILITIES.Put_Line(' ');
140
141 RETURN(1);
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 g_errbuf := 'Error in function CHECK_SETUP : '||sqlerrm;
146 RETURN(-1);
147
148 END check_setup;
149
150 FUNCTION get_reporting_uom RETURN NUMBER IS
151
152 BEGIN
153 g_reporting_weight_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('WT');
154 IF (g_reporting_weight_uom IS NULL) THEN
155 g_retcode := 1;
156 g_errbuf := g_errbuf || 'DBI Weight Reporting UOM has not been set up.';
157 BIS_COLLECTION_UTILITIES.put_line(' ');
158 BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Weight Reporting UOM has not been set up.');
159 BIS_COLLECTION_UTILITIES.put_line(' ');
160 ELSE
161 BIS_COLLECTION_UTILITIES.put_line('The reporting weight uom is ' || g_reporting_weight_uom);
162 END IF;
163
164 g_reporting_volume_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('VOL');
165 IF (g_reporting_volume_uom IS NULL) THEN
166 g_retcode := 1;
167 g_errbuf := g_errbuf || 'DBI Volume Reporting UOM has not been set up.';
168 BIS_COLLECTION_UTILITIES.put_line(' ');
169 BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Volume Reporting UOM has not been set up.');
170 BIS_COLLECTION_UTILITIES.put_line(' ');
171 ELSE
172 BIS_COLLECTION_UTILITIES.put_line('The reporting volume uom is ' || g_reporting_volume_uom);
173 END IF;
174
175 g_reporting_distance_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('DIS');
176 IF (g_reporting_distance_uom IS NULL) THEN
177 g_retcode := 1;
178 g_errbuf := g_errbuf || 'DBI Distance Reporting UOM has not been set up.';
179 BIS_COLLECTION_UTILITIES.put_line(' ');
180 BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Distance Reporting UOM has not been set up.');
181 BIS_COLLECTION_UTILITIES.put_line(' ');
182 ELSE
183 BIS_COLLECTION_UTILITIES.put_line('The reporting distance uom is ' || g_reporting_distance_uom);
184 END IF;
185
186 RETURN(1);
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 g_errbuf := 'Error in function CHECK_SETUP : '||sqlerrm;
191 RETURN(-1);
192 END get_reporting_uom;
193
194
195 FUNCTION update_parameter_table RETURN NUMBER IS
196
197 l_old_arr_window NUMBER;
198
199 BEGIN
200
201 BEGIN
202 SELECT on_time_window INTO l_old_arr_window FROM isc_dbi_fte_parameters;
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 BIS_COLLECTION_UTILITIES.Put_Line('Inserting data into isc_dbi_fte_parameters.');
206 INSERT INTO isc_dbi_fte_parameters (ON_TIME_WINDOW, LAST_UPDATE_DATE) VALUES (g_new_arr_window, sysdate);
207 l_old_arr_window := g_new_arr_window;
208 END;
209
210 IF (l_old_arr_window IS NULL or g_new_arr_window <> l_old_arr_window) THEN
211 g_retcode := 1;
212 g_errbuf := 'The profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.';
213 BIS_COLLECTION_UTILITIES.Put_Line(' ');
214 BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215 BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216 BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217 BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218 BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219 BIS_COLLECTION_UTILITIES.Put_Line(' ');
220 BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221 BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
222 UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
223 END IF;
224
225 RETURN(1);
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 g_errbuf := 'Error in function UPDATE_PARAMETER_TABLE : '||sqlerrm;
230 RETURN(-1);
231
232 END update_parameter_table;
233
234 FUNCTION SET_WMS_PTS_GSD RETURN NUMBER IS
235
236 l_overwrite BOOLEAN;
237
238 BEGIN
239
240 l_overwrite := FALSE;
241
242 BIS_COLLECTION_UTILITIES.Put_Line('Setting the 11.5.10 CU1 date for WMS page.');
243 OPI_DBI_WMS_UTILITY_PKG.Set_Wms_Pts_Gsd(l_overwrite);
244
245 RETURN(1);
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 g_errbuf := 'Error in function SET_WMS_PTS_GSD : '||sqlerrm;
250 RETURN(-1);
251
252 END set_wms_pts_gsd;
253
254 -- --------------------
255 -- IDENTIFY_CHANGE_INIT
256 -- --------------------
257
258 FUNCTION IDENTIFY_CHANGE_INIT RETURN NUMBER IS
259
260 l_detail_count NUMBER;
261 l_leg_count NUMBER;
262 l_stop_count NUMBER;
263 l_invoice_count NUMBER;
264 l_total NUMBER;
265 -- l_stmt VARCHAR2(8000);
266 -- l_from_date VARCHAR2(30);
267 -- l_to_date VARCHAR2(30);
268
269 BEGIN
270
271 l_detail_count := 0;
272 l_leg_count := 0;
273 l_stop_count := 0;
274 l_invoice_count := 0;
275 l_total := 0;
276
277 -- l_from_date := to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS');
278 -- l_to_date := to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS');
279
280 BIS_COLLECTION_UTILITIES.put_line('Identifying delivery details');
281 FII_UTIL.Start_Timer;
282
283 INSERT /*+ APPEND PARALLEL(F) */ INTO isc_dbi_tmp_del_details F (
284 DELIVERY_DETAIL_ID,
285 INVENTORY_ITEM_ID,
286 ORGANIZATION_ID,
287 SHIPMENT_DIRECTION,
288 SUBINVENTORY_CODE,
289 TIME_IP_DATE_ID,
290 TIME_PR_DATE_ID,
291 DELIVERY_ID,
292 INITIAL_PICKUP_DATE,
293 MOVE_ORDER_LINE_ID,
294 PICK_RELEASED_DATE,
295 RELEASED_STATUS,
296 REQUESTED_QUANTITY,
297 REQUESTED_QUANTITY_UOM,
298 SHIPPED_QUANTITY,
299 WMS_ENABLED_FLAG)
300 SELECT /*+ USE_HASH(wnd,wda,wdd,mol,mmt,mp) PARALLEL(wnd) PARALLEL(wda) PARALLEL(wdd) PARALLEL(mol) PARALLEL(mmt) PARALLEL(mp) */
301 wdd.delivery_detail_id DELIVERY_DETAIL_ID,
302 wdd.inventory_item_id INVENTORY_ITEM_ID,
303 nvl(wnd.organization_id, wdd.organization_id) ORGANIZATION_ID,
304 nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
305 mmt.subinventory_code SUBINVENTORY_CODE,
306 trunc(wnd.initial_pickup_date) TIME_IP_DATE_ID,
307 trunc(mol.creation_date) TIME_PR_DATE_ID,
308 wnd.delivery_id DELIVERY_ID,
309 wnd.initial_pickup_date INITIAL_PICKUP_DATE,
310 wdd.move_order_line_id MOVE_ORDER_LINE_ID,
311 mol.creation_date PICK_RELEASED_DATE,
312 wdd.released_status RELEASED_STATUS,
313 wdd.requested_quantity REQUESTED_QUANTITY,
314 wdd.requested_quantity_uom REQUESTED_QUANTITY_UOM,
315 wdd.shipped_quantity SHIPPED_QUANTITY,
316 mp.wms_enabled_flag WMS_ENABLED_FLAG
317 FROM wsh_delivery_details wdd,
318 wsh_delivery_assignments wda,
319 wsh_new_deliveries wnd,
320 mtl_txn_request_lines mol,
321 mtl_material_transactions mmt,
322 mtl_parameters mp
323 WHERE wdd.released_status in ('S','Y','C','L','P')
324 AND wdd.delivery_detail_id = wda.delivery_detail_id
325 AND nvl(wda.type,'S') in ('S','O')
326 AND nvl(wdd.container_flag,'N') = 'N'
327 AND wda.delivery_id = wnd.delivery_id (+)
328 AND wdd.move_order_line_id = mol.line_id (+)
329 AND wdd.transaction_id = mmt.transaction_id (+)
330 AND nvl(mmt.transaction_source_type_id,2) IN (2,8)
331 AND nvl(mmt.transaction_action_id,28) = 28
332 AND nvl(mmt.transaction_quantity,-1) < 0
333 AND wdd.organization_id = mp.organization_id
334 AND nvl((CASE WHEN wdd.released_status in ('C', 'L', 'P') THEN wnd.initial_pickup_date ELSE null END), g_global_start_date) >= g_global_start_date;
335
336 l_detail_count := sql%rowcount;
337 FII_UTIL.Stop_Timer;
338 FII_UTIL.Print_Timer('Identified ' || l_detail_count || ' delivery details in');
339 COMMIT;
340
341 BIS_COLLECTION_UTILITIES.put_line('Identifying trip stops');
342 FII_UTIL.Start_Timer;
343
344 INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_trip_stops tmp (
345 STOP_ID,
346 CARRIER_ID,
347 MODE_OF_TRANSPORT,
348 SERVICE_LEVEL,
349 TIME_ACTL_ARRL_DATE_ID,
350 TIME_INIT_DEPT_DATE_ID,
351 TIME_PLN_ARRL_DATE_ID,
352 ACTUAL_ARRIVAL_DATE,
353 ACTUAL_DEPARTURE_DATE,
354 DISTANCE_TO_NEXT_STOP_TRX,
355 DISTANCE_UOM_CODE,
356 PLANNED_ARRIVAL_DATE,
357 STOP_RANK,
358 STOP_SEQUENCE_NUMBER,
359 TRIP_ID,
360 ULTIMATE_STOP_SEQUENCE_NUMBER)
361 SELECT /*+ USE_HASH(idl,wt,wts) PARALLEL(idl) PARALLEL(wt) PARALLEL(wts) */
362 wts.stop_id STOP_ID,
363 nvl(wt.carrier_id, -1) CARRIER_ID,
364 nvl(wt.mode_of_transport, -1) MODE_OF_TRANSPORT,
365 nvl(wt.service_level, -1) SERVICE_LEVEL,
366 trunc(wts.actual_arrival_date) TIME_ACTL_ARRL_DATE_ID,
367 trunc(min(wts.actual_departure_date) over (partition by wt.trip_id)) TIME_INIT_DEPT_DATE_ID,
368 trunc(wts.planned_arrival_date) TIME_PLN_ARRL_DATE_ID,
369 wts.actual_arrival_date ACTUAL_ARRIVAL_DATE,
370 wts.actual_departure_date ACTUAL_DEPARTURE_DATE,
371 wts.distance_to_next_stop DISTANCE_TO_NEXT_STOP_TRX,
372 wts.distance_uom DISTANCE_UOM_CODE,
373 wts.planned_arrival_date PLANNED_ARRIVAL_DATE,
374 rank() over (partition by wt.trip_id order by wts.stop_sequence_number) STOP_RANK,
375 wts.stop_sequence_number STOP_SEQUENCE_NUMBER,
376 wt.trip_id TRIP_ID,
377 max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
378 FROM (select /*+ PARALLEL(wts_tmp) */ distinct trip_id
379 from wsh_trip_stops wts_tmp where actual_departure_date > g_global_start_date) idl,
380 wsh_trips wt,
381 wsh_trip_stops wts
382 WHERE idl.trip_id = wt.trip_id
383 AND wt.trip_id = wts.trip_id
384 AND wt.status_code IN ('IT', 'CL')
385 AND wts.physical_stop_id IS NULL
386 AND wts.stop_sequence_number <> -99;
387
388 l_stop_count := sql%rowcount;
389 FII_UTIL.Stop_Timer;
390 FII_UTIL.Print_Timer('Identified ' || l_stop_count || ' trip stops in');
391 COMMIT;
392
393 BIS_COLLECTION_UTILITIES.put_line('Identifying delivery legs');
394 FII_UTIL.Start_Timer;
395
396 INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_del_legs tmp (
397 DELIVERY_LEG_ID,
398 CARRIER_ID,
399 SHIPMENT_DIRECTION,
400 MODE_OF_TRANSPORT,
401 ORGANIZATION_ID,
402 SERVICE_LEVEL,
403 TIME_INIT_DEPT_DATE_ID,
404 CONVERSION_DATE,
405 CONVERSION_RATE,
406 CONVERSION_TYPE_CODE,
407 DELIVERY_ID,
408 DROP_OFF_STOP_ID,
409 FREIGHT_COST_TRX,
410 FREIGHT_VOLUME_TRX,
411 FREIGHT_WEIGHT_TRX,
412 PICK_UP_STOP_ID,
413 TRIP_ID,
414 TRX_CURRENCY_CODE,
415 VOLUME_UOM_CODE,
416 WEIGHT_UOM_CODE,
417 WH_CURRENCY_CODE,
418 DELIVERY_TYPE,
419 PARENT_DELIVERY_LEG_ID)
420 SELECT /*+ USE_HASH(wnd,wdl,its,ifc,hoi,gsb) PARALLEL(wnd) PARALLEL(wdl) PARALLEL(its) PARALLEL(wfc) PARALLEL(wfct) PARALLEL(hoi) PARALLEL(gsb) */
421 wdl.delivery_leg_id DELIVERY_LEG_ID,
422 its.carrier_id CARRIER_ID,
423 nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
424 its.mode_of_transport MODE_OF_TRANSPORT,
425 wnd.organization_id ORGANIZATION_ID,
426 its.service_level SERVICE_LEVEL,
427 its.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
428 decode(upper(ifc.conversion_type_code),
429 'USER',ifc.conversion_date,
430 its.time_init_dept_date_id) CONVERSION_DATE,
431 decode(upper(ifc.conversion_type_code),
432 'USER', ifc.conversion_rate, null) CONVERSION_RATE,
433 nvl(ifc.conversion_type_code, nvl(g_treasury_rate_type, g_global_rate_type)) CONVERSION_TYPE_CODE,
434 wdl.delivery_id DELIVERY_ID,
435 wdl.drop_off_stop_id DROP_OFF_STOP_ID,
436 ifc.total_amount FREIGHT_COST_TRX,
437 decode(wdl.parent_delivery_leg_id,null, wnd.volume,decode(delivery_type,'CONSOLIDATION',wnd.volume,0)) FREIGHT_VOLUME_TRX,
438 decode(wdl.parent_delivery_leg_id,null, wnd.gross_weight,decode(delivery_type,'CONSOLIDATION',wnd.gross_weight,0)) FREIGHT_WEIGHT_TRX,
439 wdl.pick_up_stop_id PICK_UP_STOP_ID,
440 its.trip_id TRIP_ID,
441 ifc.currency_code TRX_CURRENCY_CODE,
442 wnd.volume_uom_code VOLUME_UOM_CODE,
443 wnd.weight_uom_code WEIGHT_UOM_CODE,
444 gsb.currency_code WH_CURRENCY_CODE,
445 wnd.delivery_type DELIVERY_TYPE,
446 wdl.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
447 FROM wsh_new_deliveries wnd,
448 wsh_delivery_legs wdl,
449 (select /*+ PARALLEL(wfc) */
450 wfc.delivery_leg_id, total_amount, wfc.currency_code, conversion_type_code, conversion_rate, conversion_date
451 from wsh_freight_costs wfc,wsh_freight_cost_types wfct,
452 wsh_new_deliveries wnd, wsh_delivery_legs wdl
453 where wfc.delivery_detail_id IS NULL
454 AND wfc.freight_cost_type_id = wfct.freight_cost_type_id
455 AND wnd.delivery_id = wdl.delivery_id
456 AND wdl.delivery_leg_id = wfc.delivery_leg_id
457 AND (wdl.parent_delivery_leg_id is null
458 OR wnd.delivery_type = 'CONSOLIDATION')
459 AND wfct.name = 'SUMMARY'
460 AND wfct.freight_cost_type_code = 'FTESUMMARY') ifc,
461 isc_dbi_tmp_trip_stops its,
462 hr_organization_information hoi,
463 gl_sets_of_books gsb
464 WHERE wdl.delivery_id = wnd.delivery_id
465 AND wnd.initial_pickup_date >= g_global_start_date
466 AND nvl(wnd.shipping_control, 'NA') <> 'SUPPLIER'
467 AND wdl.pick_up_stop_id = its.stop_id
468 AND wdl.delivery_leg_id = ifc.delivery_leg_id(+)
469 AND hoi.org_information_context ='Accounting Information'
470 AND hoi.organization_id = wnd.organization_id
471 AND hoi.org_information1 = to_char(gsb.set_of_books_id);
472
473 l_leg_count := sql%rowcount;
474 FII_UTIL.Stop_Timer;
475 FII_UTIL.Print_Timer('Identified ' || l_leg_count || ' delivery legs in');
476 COMMIT;
477
478 FII_UTIL.Start_Timer;
479
480 INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_fte_invoices tmp (
481 INVOICE_HEADER_ID,
482 CARRIER_ID,
483 MODE_OF_TRANSPORT,
484 ORG_ID,
485 SERVICE_LEVEL,
486 SUPPLIER_ID,
487 APPROVED_AMT_TRX,
488 BILL_AMT_TRX,
489 BILL_NUMBER,
490 BILL_STATUS,
491 BILL_TYPE,
492 BOL,
493 CONVERSION_DATE,
494 CONVERSION_TYPE_CODE,
495 DELIVERY_LEG_ID,
496 TRIP_ID,
497 TRX_CURRENCY_CODE,
498 WH_CURRENCY_CODE)
499 SELECT /*+ USE_HASH(idl,wdi,fih,aspa,gsb) PARALLEL(idl) PARALLEL(wdi) PARALLEL(fih) PARALLEL(aspa) PARALLEL(gsb) */
500 fih.invoice_header_id INVOICE_HEADER_ID,
501 idl.carrier_id CARRIER_ID,
502 idl.mode_of_transport MODE_OF_TRANSPORT,
503 fih.org_id ORG_ID,
504 idl.service_level SERVICE_LEVEL,
505 fih.supplier_id SUPPLIER_ID,
506 fih.approved_amount APPROVED_AMT_TRX,
507 fih.total_amount BILL_AMT_TRX,
508 fih.bill_number BILL_NUMBER,
509 fih.bill_status BILL_STATUS,
510 fih.bill_type BILL_TYPE,
511 fih.bol BOL,
512 idl.time_init_dept_date_id CONVERSION_DATE,
513 nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
514 idl.delivery_leg_id DELIVERY_LEG_ID,
515 idl.trip_id TRIP_ID,
516 fih.currency_code TRX_CURRENCY_CODE,
517 gsb.currency_code WH_CURRENCY_CODE
518 FROM fte_invoice_headers fih,
519 wsh_document_instances wdi,
520 isc_dbi_tmp_del_legs idl,
521 ar_system_parameters_all aspa,
522 gl_sets_of_books gsb
523 WHERE fih.mode_of_transport = 'LTL'
524 AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
525 AND fih.bol = wdi.sequence_number
526 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
527 AND wdi.document_type = 'BOL'
528 AND wdi.entity_id = idl.delivery_leg_id
529 AND fih.org_id = aspa.org_id
530 AND aspa.set_of_books_id = gsb.set_of_books_id
531 AND idl.mode_of_transport = 'LTL'
532 UNION ALL
533 SELECT /*+ USE_HASH(itr,wdi,fih,aspa,gsb) PARALLEL(itr) PARALLEL(wdi) PARALLEL(fih) PARALLEL(aspa) PARALLEL(gsb) */
534 fih.invoice_header_id INVOICE_HEADER_ID,
535 itr.carrier_id CARRIER_ID,
536 itr.mode_of_transport MODE_OF_TRANSPORT,
537 fih.org_id ORG_ID,
538 itr.service_level SERVICE_LEVEL,
539 fih.supplier_id SUPPLIER_ID,
540 fih.approved_amount APPROVED_AMT_TRX,
541 fih.total_amount BILL_AMT_TRX,
542 fih.bill_number BILL_NUMBER,
543 fih.bill_status BILL_STATUS,
544 fih.bill_type BILL_TYPE,
545 fih.bol BOL,
546 itr.time_init_dept_date_id CONVERSION_DATE,
547 nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
548 null DELIVERY_LEG_ID,
549 itr.trip_id TRIP_ID,
550 fih.currency_code TRX_CURRENCY_CODE,
551 gsb.currency_code WH_CURRENCY_CODE
552 FROM fte_invoice_headers fih,
553 wsh_document_instances wdi,
554 isc_dbi_tmp_trip_stops itr,
555 ar_system_parameters_all aspa,
556 gl_sets_of_books gsb
557 WHERE fih.mode_of_transport = 'TL'
558 AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
559 AND fih.bol = wdi.sequence_number
560 AND wdi.entity_name = 'WSH_TRIPS'
561 AND wdi.document_type = 'MBOL'
562 AND wdi.entity_id = itr.trip_id
563 AND itr.stop_rank = 1
564 AND fih.org_id = aspa.org_id
565 AND aspa.set_of_books_id = gsb.set_of_books_id;
566
567 l_invoice_count := sql%rowcount;
568 FII_UTIL.Stop_Timer;
569 FII_UTIL.Print_Timer('Identified ' || l_invoice_count || ' invoice headers in');
570 COMMIT;
571
572 FII_UTIL.Start_Timer;
573
574 IF g_reporting_weight_uom IS NOT NULL THEN
575 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
576 SELECT 'WT', from_uom, g_reporting_weight_uom, NULL inventory_item_id,
577 decode(from_uom, g_reporting_weight_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_weight_uom))
578 FROM (SELECT /*+ PARALLEL(tmp1) */ DISTINCT weight_uom_code FROM_UOM
579 FROM isc_dbi_tmp_del_legs tmp1
580 WHERE weight_uom_code is not null);
581 END IF;
582
583 IF g_reporting_volume_uom IS NOT NULL THEN
584 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
585 SELECT 'VOL', from_uom, g_reporting_volume_uom, NULL inventory_item_id,
586 decode(from_uom, g_reporting_volume_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_volume_uom))
587 FROM (SELECT /*+ PARALLEL(tmp2) */ DISTINCT volume_uom_code FROM_UOM
588 FROM isc_dbi_tmp_del_legs tmp2
589 WHERE volume_uom_code is not null);
590 END IF;
591
592 IF g_reporting_distance_uom IS NOT NULL THEN
593 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
594 SELECT 'DIS', from_uom, g_reporting_distance_uom, NULL inventory_item_id,
595 decode(from_uom,g_reporting_distance_uom,1,opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_distance_uom))
596 FROM (SELECT /*+ PARALLEL(tmp3) */ DISTINCT distance_uom_code FROM_UOM
597 FROM isc_dbi_tmp_trip_stops tmp3
598 where distance_uom_code is not null);
599 END IF;
600
601 -- INSERT INTO isc_dbi_fte_uom_rates (FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
602 -- SELECT from_uom, to_uom, NULL inventory_item_id,
603 -- decode(from_uom, to_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, to_uom))
604 -- FROM (SELECT /*+ PARALLEL(tmp1) */ DISTINCT weight_uom_code FROM_UOM, 'WT' TO_UOM
605 -- FROM isc_dbi_tmp_del_legs tmp1
606 -- WHERE weight_uom_code is not null
607 -- UNION
608 -- SELECT /*+ PARALLEL(tmp2) */ DISTINCT volume_uom_code FROM_UOM, 'VOL' TO_UOM
609 -- FROM isc_dbi_tmp_del_legs tmp2
610 -- WHERE volume_uom_code is not null
611 -- UNION
612 -- SELECT /*+ PARALLEL(tmp3) */ DISTINCT distance_uom_code, 'DIS' TO_UOM
613 -- FROM isc_dbi_tmp_trip_stops tmp3
614 -- where distance_uom_code is not null);
615
616 FII_UTIL.Stop_Timer;
617 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' uom rates in');
618 COMMIT;
619
620 FII_UTIL.Start_Timer;
621
622 INSERT /*+ APPEND */ INTO isc_dbi_fte_curr_rates
623 (TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
624 SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
625 decode(trx_currency_code, wh_currency_code, 1,
626 fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
627 decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
628 fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
629 decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
630 fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
631 FROM (SELECT /*+ PARALLEL(idl) */
632 distinct trx_currency_code, wh_currency_code, conversion_date CONVERSION_DATE, conversion_type_code CONVERSION_TYPE_CODE
633 FROM isc_dbi_tmp_del_legs idl
634 WHERE idl.freight_cost_trx is not null
635 UNION
636 SELECT /*+ PARALLEL(ifi) */
637 distinct trx_currency_code, wh_currency_code, conversion_date, conversion_type_code CONVERSION_TYPE_CODE
638 FROM isc_dbi_tmp_fte_invoices ifi);
639
640 FII_UTIL.Stop_Timer;
641 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
642 COMMIT;
643
644 l_total := l_detail_count + l_leg_count + l_stop_count + l_invoice_count;
645 RETURN(l_total);
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 g_errbuf := 'Error in Function IDENTIFY_CHANGE_INIT : '||sqlerrm;
650 g_retcode := sqlcode;
651 RETURN(-1);
652 END;
653
654 -- --------------------
655 -- IDENTIFY_CHANGE_ICRL
656 -- --------------------
657
658 FUNCTION IDENTIFY_CHANGE_DETAIL_ICRL RETURN NUMBER IS
659
660 l_total NUMBER;
661
662 BEGIN
663
664 l_total := 0;
665
666 FII_UTIL.Start_Timer;
667
668 INSERT INTO isc_dbi_tmp_wdd_log (DELIVERY_DETAIL_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
669 SELECT delivery_detail_id, rowid LOG_ROWID, dml_type, last_update_date
670 FROM isc_dbi_wdd_change_log;
671
672 FII_UTIL.Stop_Timer;
673 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_WDD_LOG');
674
675 COMMIT;
676
677 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
678 TABNAME => 'ISC_DBI_TMP_WDD_LOG');
679
680 BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
681 FII_UTIL.Start_Timer;
682
683 DELETE FROM isc_dbi_del_details_f
684 WHERE delivery_detail_id IN (SELECT DISTINCT log.delivery_detail_id
685 FROM isc_dbi_tmp_wdd_log log
686 WHERE NOT EXISTS (select '1' from wsh_delivery_details wdd
687 where wdd.delivery_detail_id = log.delivery_detail_id
688 and wdd.released_status in ('S','Y','C','L','P')));
689
690 FII_UTIL.Stop_Timer;
691 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' delivery details from base summary in');
692 COMMIT;
693
694 FII_UTIL.Start_Timer;
695
696 INSERT INTO isc_dbi_tmp_del_details F (
697 DELIVERY_DETAIL_ID,
698 INVENTORY_ITEM_ID,
699 ORGANIZATION_ID,
700 SHIPMENT_DIRECTION,
701 SUBINVENTORY_CODE,
702 TIME_IP_DATE_ID,
703 TIME_PR_DATE_ID,
704 DELIVERY_ID,
705 INITIAL_PICKUP_DATE,
706 MOVE_ORDER_LINE_ID,
707 PICK_RELEASED_DATE,
708 RELEASED_STATUS,
709 REQUESTED_QUANTITY,
710 REQUESTED_QUANTITY_UOM,
711 SHIPPED_QUANTITY,
712 WMS_ENABLED_FLAG)
713 SELECT /*+ leading(log) use_nl(mp) */
714 wdd.delivery_detail_id DELIVERY_DETAIL_ID,
715 wdd.inventory_item_id INVENTORY_ITEM_ID,
716 nvl(wnd.organization_id, wdd.organization_id) ORGANIZATION_ID,
717 nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
718 mmt.subinventory_code SUBINVENTORY_CODE,
719 trunc(wnd.initial_pickup_date) TIME_IP_DATE_ID,
720 trunc(mol.creation_date) TIME_PR_DATE_ID,
721 wnd.delivery_id DELIVERY_ID,
722 wnd.initial_pickup_date INITIAL_PICKUP_DATE,
723 wdd.move_order_line_id MOVE_ORDER_LINE_ID,
724 mol.creation_date PICK_RELEASED_DATE,
725 wdd.released_status RELEASED_STATUS,
726 wdd.requested_quantity REQUESTED_QUANTITY,
727 wdd.requested_quantity_uom REQUESTED_QUANTITY_UOM,
728 wdd.shipped_quantity SHIPPED_QUANTITY,
729 mp.wms_enabled_flag WMS_ENABLED_FLAG
730 FROM (select distinct delivery_detail_id from isc_dbi_tmp_wdd_log) log,
731 wsh_delivery_details wdd,
732 wsh_delivery_assignments wda,
733 wsh_new_deliveries wnd,
734 mtl_txn_request_lines mol,
735 mtl_material_transactions mmt,
736 mtl_parameters mp
737 WHERE wdd.delivery_detail_id = log.delivery_detail_id
738 AND wdd.released_status in ('S','Y','C','L','P')
739 AND wdd.delivery_detail_id = wda.delivery_detail_id
740 AND nvl(wda.type,'S') in ('S','O')
741 AND nvl(wdd.container_flag,'N') = 'N'
742 AND wda.delivery_id = wnd.delivery_id (+)
743 AND wdd.move_order_line_id = mol.line_id (+)
744 AND wdd.transaction_id = mmt.transaction_id (+)
745 AND nvl(mmt.transaction_source_type_id,2) IN (2,8)
746 AND nvl(mmt.transaction_action_id,28) = 28
747 AND nvl(mmt.transaction_quantity,-1) < 0
748 AND wdd.organization_id = mp.organization_id
749 AND nvl((CASE WHEN wdd.released_status in ('C', 'L', 'P') THEN wnd.initial_pickup_date ELSE null END), g_global_start_date) >= g_global_start_date;
750
751 FII_UTIL.Stop_Timer;
752 FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' delivery details in');
753 COMMIT;
754
755 FII_UTIL.Start_Timer;
756
757 UPDATE isc_dbi_tmp_del_details SET batch_id = ceil(rownum/g_batch_size);
758 l_total := sql%rowcount;
759 COMMIT;
760
761 FII_UTIL.Stop_Timer;
762 FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
763
764 RETURN(l_total);
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 g_errbuf := 'Error in Function IDENTIFY_CHANGE_DETAIL_ICRL : '||sqlerrm;
769 g_retcode := sqlcode;
770 RETURN(-1);
771 END identify_change_detail_icrl;
772
773 FUNCTION IDENTIFY_CHANGE_STOP_LEG_ICRL RETURN NUMBER IS
774
775 l_total NUMBER;
776
777 BEGIN
778
779 l_total := 0;
780
781 FII_UTIL.Start_Timer;
782
783 INSERT INTO isc_dbi_tmp_wts_log (STOP_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
784 SELECT stop_id, rowid LOG_ROWID, dml_type, last_update_date
785 FROM isc_dbi_wts_change_log;
786
787 FII_UTIL.Stop_Timer;
788 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_WTS_LOG');
789
790 COMMIT;
791
792 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
793 TABNAME => 'ISC_DBI_TMP_WTS_LOG');
794
795 BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
796 FII_UTIL.Start_Timer;
797
798 DELETE /*+ index(a, ISC_DBI_DEL_LEGS_F_U1) */ FROM isc_dbi_del_legs_f a
799 WHERE delivery_leg_id IN (SELECT /*+ index(idl,ISC_DBI_DEL_LEGS_F_U1) use_nl( log, idl)*/ idl.delivery_leg_id
800 FROM isc_dbi_tmp_wts_log log,
801 isc_dbi_del_legs_f idl
802 WHERE (log.stop_id = idl.pick_up_stop_id or log.stop_id = idl.drop_off_stop_id)
803 AND NOT EXISTS (select '1' from wsh_delivery_legs wdl where wdl.delivery_leg_id = idl.delivery_leg_id));
804
805 FII_UTIL.Stop_Timer;
806 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' delivery legs from base summary in');
807 COMMIT;
808
809 FII_UTIL.Start_Timer;
810
811 -- DELETE FROM isc_dbi_trip_stops_f
812 -- WHERE stop_id IN (SELECT DISTINCT wts.stop_id
813 -- FROM isc_dbi_tmp_wts_log log,
814 -- isc_dbi_
815 -- WHERE dml_type = 'DELETE');
816
817 DELETE FROM isc_dbi_trip_stops_f
818 WHERE stop_id IN (SELECT DISTINCT log.stop_id
819 FROM isc_dbi_tmp_wts_log log
820 WHERE NOT EXISTS (select '1' from wsh_trip_stops wts, wsh_trips wt
821 where log.stop_id = wts.stop_id
822 and wts.trip_id = wt.trip_id
823 and wt.status_code IN ('IT', 'CL')));
824
825 FII_UTIL.Stop_Timer;
826 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' trip stops from base summary in');
827 COMMIT;
828
829 FII_UTIL.Start_Timer;
830
831 INSERT INTO isc_dbi_tmp_trip_stops tmp (
832 STOP_ID,
833 CARRIER_ID,
834 MODE_OF_TRANSPORT,
835 SERVICE_LEVEL,
836 TIME_ACTL_ARRL_DATE_ID,
837 TIME_INIT_DEPT_DATE_ID,
838 TIME_PLN_ARRL_DATE_ID,
839 ACTUAL_ARRIVAL_DATE,
840 ACTUAL_DEPARTURE_DATE,
841 DISTANCE_TO_NEXT_STOP_TRX,
842 DISTANCE_UOM_CODE,
843 PLANNED_ARRIVAL_DATE,
844 STOP_RANK,
845 STOP_SEQUENCE_NUMBER,
846 TRIP_ID,
847 ULTIMATE_STOP_SEQUENCE_NUMBER)
848 SELECT /*+ leading(log) */ wts.stop_id STOP_ID,
849 nvl(wt.carrier_id, -1) CARRIER_ID,
850 nvl(wt.mode_of_transport, -1) MODE_OF_TRANSPORT,
851 nvl(wt.service_level, -1) SERVICE_LEVEL,
852 trunc(wts.actual_arrival_date) TIME_ACTL_ARRL_DATE_ID,
853 trunc(min(wts.actual_departure_date) over (partition by wt.trip_id)) TIME_INIT_DEPT_DATE_ID,
854 trunc(wts.planned_arrival_date) TIME_PLN_ARRL_DATE_ID,
855 wts.actual_arrival_date ACTUAL_ARRIVAL_DATE,
856 wts.actual_departure_date ACTUAL_DEPARTURE_DATE,
857 wts.distance_to_next_stop DISTANCE_TO_NEXT_STOP_TRX,
858 wts.distance_uom DISTANCE_UOM_CODE,
859 wts.planned_arrival_date PLANNED_ARRIVAL_DATE,
860 rank() over (partition by wt.trip_id order by wts.stop_sequence_number) STOP_RANK,
861 wts.stop_sequence_number STOP_SEQUENCE_NUMBER,
862 wt.trip_id TRIP_ID,
863 max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
864 FROM (select /*+ no_merge index(tr) */ distinct tr.trip_id
865 from isc_dbi_tmp_wts_log tmp, wsh_trip_stops tr
866 where tmp.stop_id = tr.stop_id) log,
867 wsh_trips wt,
868 wsh_trip_stops wts
869 WHERE log.trip_id = wt.trip_id
870 AND wt.trip_id = wts.trip_id
871 AND wt.status_code IN ('IT', 'CL')
872 AND wts.physical_stop_id IS NULL
873 AND wts.stop_sequence_number <> -99;
874
875 FII_UTIL.Stop_Timer;
876 FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' trip stops in');
877 COMMIT;
878
879 INSERT INTO isc_dbi_tmp_del_legs tmp (
880 DELIVERY_LEG_ID,
881 CARRIER_ID,
882 SHIPMENT_DIRECTION,
883 MODE_OF_TRANSPORT,
884 ORGANIZATION_ID,
885 SERVICE_LEVEL,
886 TIME_INIT_DEPT_DATE_ID,
887 CONVERSION_DATE,
888 CONVERSION_RATE,
889 CONVERSION_TYPE_CODE,
890 DELIVERY_ID,
891 DROP_OFF_STOP_ID,
892 FREIGHT_COST_TRX,
893 FREIGHT_VOLUME_TRX,
894 FREIGHT_WEIGHT_TRX,
895 PICK_UP_STOP_ID,
896 TRIP_ID,
897 TRX_CURRENCY_CODE,
898 VOLUME_UOM_CODE,
899 WEIGHT_UOM_CODE,
900 WH_CURRENCY_CODE,
901 DELIVERY_TYPE,
902 PARENT_DELIVERY_LEG_ID)
903 SELECT /*+ leading(its) use_nl(wdl)use_nl(hoi) use_nl(gsb) use_nl (wnd) */
904 wdl.delivery_leg_id DELIVERY_LEG_ID,
905 its.carrier_id CARRIER_ID,
906 nvl(wnd.shipment_direction,'O') SHIPMENT_DIRECTION,
907 its.mode_of_transport MODE_OF_TRANSPORT,
908 wnd.organization_id ORGANIZATION_ID,
909 its.service_level SERVICE_LEVEL,
910 its.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
911 decode(upper(ifc.conversion_type_code),
912 'USER',ifc.conversion_date,
913 its.time_init_dept_date_id) CONVERSION_DATE,
914 decode(upper(ifc.conversion_type_code),
915 'USER', ifc.conversion_rate, null) CONVERSION_RATE,
916 nvl(ifc.conversion_type_code, nvl(g_treasury_rate_type, g_global_rate_type)) CONVERSION_TYPE_CODE,
917 wdl.delivery_id DELIVERY_ID,
918 wdl.drop_off_stop_id DROP_OFF_STOP_ID,
919 ifc.total_amount FREIGHT_COST_TRX,
920 decode(wdl.parent_delivery_leg_id,null, wnd.volume,decode(delivery_type,'CONSOLIDATION',wnd.volume,0)) FREIGHT_VOLUME_TRX,
921 decode(wdl.parent_delivery_leg_id,null, wnd.gross_weight,decode(delivery_type,'CONSOLIDATION',wnd.gross_weight,0)) FREIGHT_WEIGHT_TRX,
922 wdl.pick_up_stop_id PICK_UP_STOP_ID,
923 its.trip_id TRIP_ID,
924 ifc.currency_code TRX_CURRENCY_CODE,
925 wnd.volume_uom_code VOLUME_UOM_CODE,
926 wnd.weight_uom_code WEIGHT_UOM_CODE,
927 gsb.currency_code WH_CURRENCY_CODE,
928 wnd.delivery_type DELIVERY_TYPE,
929 wdl.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
930 FROM wsh_new_deliveries wnd,
931 wsh_delivery_legs wdl,
932 (select /*+ use_nl (wfct, wfc) */
933 wfc.delivery_leg_id, total_amount, wfc.currency_code, conversion_type_code, conversion_rate, conversion_date
934 from wsh_freight_costs wfc,wsh_freight_cost_types wfct,
935 wsh_new_deliveries wnd, wsh_delivery_legs wdl
936 where wfc.delivery_detail_id IS NULL
937 AND wfc.freight_cost_type_id = wfct.freight_cost_type_id
938 AND wnd.delivery_id = wdl.delivery_id
939 AND wdl.delivery_leg_id = wfc.delivery_leg_id
940 AND (wdl.parent_delivery_leg_id is null
941 OR wnd.delivery_type = 'CONSOLIDATION')
942 AND wfct.name = 'SUMMARY'
943 AND wfct.freight_cost_type_code = 'FTESUMMARY') ifc,
944 isc_dbi_tmp_trip_stops its,
945 hr_organization_information hoi,
946 gl_sets_of_books gsb
947 WHERE wdl.delivery_id = wnd.delivery_id
948 AND wnd.initial_pickup_date >= g_global_start_date
949 AND nvl(wnd.shipping_control, 'NA') <> 'SUPPLIER'
950 AND wdl.pick_up_stop_id = its.stop_id
951 AND wdl.delivery_leg_id = ifc.delivery_leg_id(+)
952 AND hoi.org_information_context ='Accounting Information'
953 AND hoi.organization_id = wnd.organization_id
954 AND to_number(hoi.org_information1) = gsb.set_of_books_id;
955
956 FII_UTIL.Stop_Timer;
957 FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' delivery legs in');
958 COMMIT;
959
960 FII_UTIL.Start_Timer;
961
962 IF g_reporting_weight_uom IS NOT NULL THEN
963 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
964 SELECT 'WT', from_uom, g_reporting_weight_uom, NULL inventory_item_id,
965 decode(from_uom, g_reporting_weight_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_weight_uom))
966 FROM (SELECT DISTINCT weight_uom_code FROM_UOM
967 FROM isc_dbi_tmp_del_legs
968 WHERE weight_uom_code is not null);
969 END IF;
970
971 IF g_reporting_volume_uom IS NOT NULL THEN
972 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
973 SELECT 'VOL', from_uom, g_reporting_volume_uom, NULL inventory_item_id,
974 decode(from_uom, g_reporting_volume_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_volume_uom))
975 FROM (SELECT DISTINCT volume_uom_code FROM_UOM
976 FROM isc_dbi_tmp_del_legs
977 WHERE volume_uom_code is not null);
978 END IF;
979
980 IF g_reporting_distance_uom IS NOT NULL THEN
981 INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
982 SELECT 'DIS', from_uom, g_reporting_distance_uom, NULL inventory_item_id,
983 decode(from_uom,g_reporting_distance_uom,1,opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_distance_uom))
984 FROM (SELECT DISTINCT distance_uom_code FROM_UOM
985 FROM isc_dbi_tmp_trip_stops
986 where distance_uom_code is not null);
987 END IF;
988
989 FII_UTIL.Stop_Timer;
990 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' uom rates in');
991 COMMIT;
992
993 FII_UTIL.Start_Timer;
994
995 INSERT INTO isc_dbi_fte_curr_rates
996 (TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
997 SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
998 decode(trx_currency_code, wh_currency_code, 1,
999 fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
1000 decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
1001 fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
1002 decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
1003 fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
1004 FROM (SELECT distinct trx_currency_code, wh_currency_code, conversion_date CONVERSION_DATE, conversion_type_code CONVERSION_TYPE_CODE
1005 FROM isc_dbi_tmp_del_legs idl
1006 WHERE idl.freight_cost_trx is not null);
1007
1008 FII_UTIL.Stop_Timer;
1009 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
1010 COMMIT;
1011
1012 FII_UTIL.Start_Timer;
1013
1014 UPDATE isc_dbi_tmp_del_legs SET batch_id = ceil(rownum/g_batch_size);
1015 l_total := l_total + sql%rowcount;
1016
1017 UPDATE isc_dbi_tmp_trip_stops SET batch_id = ceil(rownum/g_batch_size);
1018 l_total := l_total + sql%rowcount;
1019
1020 COMMIT;
1021
1022 FII_UTIL.Stop_Timer;
1023 FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
1024
1025 RETURN(l_total);
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 g_errbuf := 'Error in Function IDENTIFY_CHANGE_STOP_LEG_ICRL : '||sqlerrm;
1030 g_retcode := sqlcode;
1031 RETURN(-1);
1032 END identify_change_stop_leg_icrl;
1033
1034 FUNCTION IDENTIFY_CHANGE_INVOICE_ICRL RETURN NUMBER IS
1035
1036 l_total NUMBER;
1037
1038 BEGIN
1039
1040 l_total := 0;
1041
1042 FII_UTIL.Start_Timer;
1043
1044 INSERT INTO isc_dbi_tmp_fih_log (INVOICE_HEADER_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
1045 SELECT invoice_header_id, rowid LOG_ROWID, dml_type, last_update_date
1046 FROM isc_dbi_fih_change_log;
1047
1048 FII_UTIL.Stop_Timer;
1049 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_FIH_LOG');
1050
1051 COMMIT;
1052
1053 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
1054 TABNAME => 'ISC_DBI_TMP_FIH_LOG');
1055
1056 BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
1057 FII_UTIL.Start_Timer;
1058
1059 DELETE FROM isc_dbi_fte_invoices_f
1060 WHERE invoice_header_id IN (SELECT DISTINCT log.invoice_header_id
1061 FROM isc_dbi_tmp_fih_log log
1062 WHERE NOT EXISTS (select '1' from fte_invoice_headers fih
1063 where fih.invoice_header_id = log.invoice_header_id
1064 and fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')));
1065
1066 FII_UTIL.Stop_Timer;
1067 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' invoice headers from base summary in');
1068 COMMIT;
1069
1070 FII_UTIL.Start_Timer;
1071
1072 INSERT INTO isc_dbi_tmp_fte_invoices tmp (
1073 INVOICE_HEADER_ID,
1074 CARRIER_ID,
1075 MODE_OF_TRANSPORT,
1076 ORG_ID,
1077 SERVICE_LEVEL,
1078 SUPPLIER_ID,
1079 APPROVED_AMT_TRX,
1080 BILL_AMT_TRX,
1081 BILL_NUMBER,
1082 BILL_STATUS,
1083 BILL_TYPE,
1084 BOL,
1085 CONVERSION_DATE,
1086 CONVERSION_TYPE_CODE,
1087 DELIVERY_LEG_ID,
1088 TRIP_ID,
1089 TRX_CURRENCY_CODE,
1090 WH_CURRENCY_CODE)
1091 SELECT /*+ leading(log) */
1092 fih.invoice_header_id INVOICE_HEADER_ID,
1093 idl.carrier_id CARRIER_ID,
1094 idl.mode_of_transport MODE_OF_TRANSPORT,
1095 fih.org_id ORG_ID,
1096 idl.service_level SERVICE_LEVEL,
1097 fih.supplier_id SUPPLIER_ID,
1098 fih.approved_amount APPROVED_AMT_TRX,
1099 fih.total_amount BILL_AMT_TRX,
1100 fih.bill_number BILL_NUMBER,
1101 fih.bill_status BILL_STATUS,
1102 fih.bill_type BILL_TYPE,
1103 fih.bol BOL,
1104 idl.time_init_dept_date_id CONVERSION_DATE,
1105 nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
1106 idl.delivery_leg_id DELIVERY_LEG_ID,
1107 idl.trip_id TRIP_ID,
1108 fih.currency_code TRX_CURRENCY_CODE,
1109 gsb.currency_code WH_CURRENCY_CODE
1110 FROM (select distinct invoice_header_id from isc_dbi_tmp_fih_log) log,
1111 fte_invoice_headers fih,
1112 wsh_document_instances wdi,
1113 isc_dbi_del_legs_f idl,
1114 ar_system_parameters_all aspa,
1115 gl_sets_of_books gsb
1116 WHERE log.invoice_header_id = fih.invoice_header_id
1117 AND fih.mode_of_transport = 'LTL'
1118 AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
1119 AND fih.bol = wdi.sequence_number
1120 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
1121 AND wdi.document_type = 'BOL'
1122 AND wdi.entity_id = idl.delivery_leg_id
1123 AND fih.org_id = aspa.org_id
1124 AND aspa.set_of_books_id = gsb.set_of_books_id
1125 AND idl.mode_of_transport = 'LTL'
1126 UNION ALL
1127 SELECT /*+ leading(log) */
1128 fih.invoice_header_id INVOICE_HEADER_ID,
1129 itr.carrier_id CARRIER_ID,
1130 itr.mode_of_transport MODE_OF_TRANSPORT,
1131 fih.org_id ORG_ID,
1132 itr.service_level SERVICE_LEVEL,
1133 fih.supplier_id SUPPLIER_ID,
1134 fih.approved_amount APPROVED_AMT_TRX,
1135 fih.total_amount BILL_AMT_TRX,
1136 fih.bill_number BILL_NUMBER,
1137 fih.bill_status BILL_STATUS,
1138 fih.bill_type BILL_TYPE,
1139 fih.bol BOL,
1140 itr.time_init_dept_date_id CONVERSION_DATE,
1141 nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
1142 null DELIVERY_LEG_ID,
1143 itr.trip_id TRIP_ID,
1144 fih.currency_code TRX_CURRENCY_CODE,
1145 gsb.currency_code WH_CURRENCY_CODE
1146 FROM (select distinct invoice_header_id from isc_dbi_tmp_fih_log) log,
1147 fte_invoice_headers fih,
1148 wsh_document_instances wdi,
1149 isc_dbi_trip_stops_f itr,
1150 ar_system_parameters_all aspa,
1151 gl_sets_of_books gsb
1152 WHERE log.invoice_header_id = fih.invoice_header_id
1153 AND fih.mode_of_transport = 'TL'
1154 AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
1155 AND fih.bol = wdi.sequence_number
1156 AND wdi.entity_name = 'WSH_TRIPS'
1157 AND wdi.document_type = 'MBOL'
1158 AND wdi.entity_id = itr.trip_id
1159 AND itr.stop_rank = 1
1160 AND fih.org_id = aspa.org_id
1161 AND aspa.set_of_books_id = gsb.set_of_books_id;
1162
1163 FII_UTIL.Stop_Timer;
1164 FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' invoice headers in');
1165 COMMIT;
1166
1167 FII_UTIL.Start_Timer;
1168
1169 INSERT INTO isc_dbi_fte_curr_rates
1170 (TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
1171 SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
1172 decode(trx_currency_code, wh_currency_code, 1,
1173 fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
1174 decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
1175 fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
1176 decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
1177 fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
1178 FROM (SELECT distinct trx_currency_code, wh_currency_code, conversion_date, conversion_type_code CONVERSION_TYPE_CODE
1179 FROM isc_dbi_tmp_fte_invoices);
1180
1181 FII_UTIL.Stop_Timer;
1182 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
1183 COMMIT;
1184
1185 FII_UTIL.Start_Timer;
1186
1187 UPDATE isc_dbi_tmp_fte_invoices SET batch_id = ceil(rownum/g_batch_size);
1188 l_total := l_total + sql%rowcount;
1189
1190 COMMIT;
1191
1192 FII_UTIL.Stop_Timer;
1193 FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
1194
1195 RETURN(l_total);
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 g_errbuf := 'Error in Function IDENTIFY_CHANGE_INVOICE_ICRL : '||sqlerrm;
1200 g_retcode := sqlcode;
1201 RETURN(-1);
1202 END identify_change_invoice_icrl;
1203
1204 -- ---------------------
1205 -- CHECK_TIME_CONTINUITY
1206 -- ---------------------
1207
1208 FUNCTION CHECK_TIME_CONTINUITY_INIT RETURN NUMBER IS
1209
1210 l_min_ip_date DATE;
1211 l_max_ip_date DATE;
1212 l_min_pr_date DATE;
1213 l_max_pr_date DATE;
1214 l_min_actl_arrl_date DATE;
1215 l_max_actl_arrl_date DATE;
1216 l_min_init_dept_date DATE;
1217 l_max_init_dept_date DATE;
1218 l_min_pln_arrl_date DATE;
1219 l_max_pln_arrl_date DATE;
1220 l_min DATE;
1221 l_max DATE;
1222 l_is_missing BOOLEAN;
1223 l_time_min DATE;
1224 l_time_max DATE;
1225 l_time_missing BOOLEAN;
1226
1227 CURSOR Lines_Missing_Date IS
1228 SELECT /*+ PARALLEL(tmp) */ delivery_detail_id,
1229 to_char(time_ip_date_id, 'MM/DD/YYYY') time_ip_date_id,
1230 to_char(time_pr_date_id, 'MM/DD/YYYY') time_pr_date_id
1231 FROM isc_dbi_tmp_del_details tmp
1232 WHERE (least(nvl(time_ip_date_id,l_time_min), nvl(time_pr_date_id,l_time_min)) < l_time_min
1233 OR greatest(nvl(time_ip_date_id, l_time_max), nvl(time_pr_date_id, l_time_max)) > l_time_max);
1234
1235 CURSOR Stops_Missing_Date IS
1236 SELECT /*+ PARALLEL(tmp) */
1237 trip_id,
1238 stop_id,
1239 to_char(time_actl_arrl_date_id, 'MM/DD/YYYY') time_actl_arrl_date_id,
1240 to_char(actual_departure_date, 'MM/DD/YYYY') time_actl_dept_date_id,
1241 to_char(time_pln_arrl_date_id,'MM/DD/YYYY') time_pln_arrl_date_id
1242 FROM isc_dbi_tmp_trip_stops tmp
1243 WHERE (least(nvl(time_actl_arrl_date_id,l_time_min),nvl(trunc(actual_departure_date),l_time_min), nvl(time_pln_arrl_date_id,l_time_min)) < l_time_min
1244 OR greatest(nvl(time_actl_arrl_date_id,l_time_max),nvl(trunc(actual_departure_date),l_time_max), nvl(time_pln_arrl_date_id,l_time_max)) > l_time_max);
1245
1246 l_line LINES_MISSING_DATE%ROWTYPE;
1247 l_stop STOPS_MISSING_DATE%ROWTYPE;
1248
1249 BEGIN
1250
1251 l_is_missing := TRUE;
1252 l_time_missing := TRUE;
1253
1254 FII_UTIL.Start_Timer;
1255
1256 BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the initial load');
1257
1258 SELECT /*+ PARALLEL(tmp) */
1259 min(time_ip_date_id), max(time_ip_date_id),
1260 min(time_pr_date_id), max(time_pr_date_id)
1261 INTO l_min_ip_date, l_max_ip_date, l_min_pr_date, l_max_pr_date
1262 FROM isc_dbi_tmp_del_details tmp;
1263
1264 SELECT /*+ PARALLEL(tmp) */
1265 min(time_actl_arrl_date_id), max(time_actl_arrl_date_id),
1266 min(time_init_dept_date_id), max(time_init_dept_date_id),
1267 min(time_pln_arrl_date_id), max(time_pln_arrl_date_id)
1268 INTO l_min_actl_arrl_date, l_max_actl_arrl_date, l_min_init_dept_date, l_max_init_dept_date, l_min_pln_arrl_date, l_max_pln_arrl_date
1269 FROM isc_dbi_tmp_trip_stops tmp;
1270
1271 l_min := least(nvl(l_min_ip_date,sysdate), nvl(l_min_pr_date,sysdate), nvl(l_min_actl_arrl_date,sysdate),
1272 nvl(l_min_init_dept_date,sysdate), nvl(l_min_pln_arrl_date,sysdate));
1273 l_max := greatest(nvl(l_max_ip_date,sysdate), nvl(l_max_pr_date,sysdate), nvl(l_max_actl_arrl_date,sysdate),
1274 nvl(l_max_init_dept_date,sysdate), nvl(l_max_pln_arrl_date,sysdate));
1275
1276 FII_UTIL.Stop_Timer;
1277 FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1278
1279 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1280 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1281 FII_UTIL.Start_Timer;
1282
1283 FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1284
1285 IF (l_is_missing) THEN
1286 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1287 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1288
1289 SELECT min(report_date), max(report_date)
1290 INTO l_time_min, l_time_max
1291 FROM fii_time_day;
1292
1293 OPEN lines_missing_date;
1294 FETCH lines_missing_date INTO l_line;
1295
1296 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1297 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1298 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL'),18,' ')
1299 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1300 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1301 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1302
1303 WHILE LINES_MISSING_DATE%FOUND LOOP
1304 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1305 ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1306 ||' - '||RPAD(nvl(l_line.time_pr_date_id,' '),19,' '));
1307 FETCH Lines_Missing_Date INTO l_line;
1308 END LOOP;
1309
1310 CLOSE LINES_MISSING_DATE;
1311 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------+');
1312
1313 OPEN stops_missing_date;
1314 FETCH stops_missing_date INTO l_stop;
1315
1316 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1317 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1318 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1319 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1320 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1321 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1322 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1323 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1324
1325 WHILE STOPS_MISSING_DATE%FOUND LOOP
1326 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1327 ||' - '||RPAD(l_stop.stop_id,18,' ')
1328 ||' - '||RPAD(l_stop.time_actl_arrl_date_id,19,' ')
1329 ||' - '||RPAD(l_stop.time_actl_dept_date_id,21,' ')
1330 ||' - '||RPAD(nvl(l_stop.time_pln_arrl_date_id,' '),20,' '));
1331 FETCH STOPS_MISSING_DATE INTO l_stop;
1332 END LOOP;
1333
1334 CLOSE STOPS_MISSING_DATE;
1335 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1336
1337 RETURN (-999);
1338 ELSE
1339 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1340 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1341 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1342 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1343 END IF;
1344
1345 FII_UTIL.Stop_Timer;
1346 FII_UTIL.Print_Timer('Completed time continuity check in');
1347
1348 RETURN(1);
1349
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY_INIT : '||sqlerrm;
1353 g_retcode := sqlcode;
1354 RETURN(-1);
1355 END;
1356
1357 FUNCTION CHECK_TIME_CONTINUITY_DETAIL RETURN NUMBER IS
1358
1359 l_min_ip_date DATE;
1360 l_max_ip_date DATE;
1361 l_min_pr_date DATE;
1362 l_max_pr_date DATE;
1363 l_min DATE;
1364 l_max DATE;
1365 l_is_missing BOOLEAN;
1366 l_time_min DATE;
1367 l_time_max DATE;
1368 l_time_missing BOOLEAN;
1369
1370 CURSOR Lines_Missing_Date IS
1371 SELECT delivery_detail_id,
1372 to_char(time_ip_date_id, 'MM/DD/YYYY') time_ip_date_id,
1373 to_char(time_pr_date_id, 'MM/DD/YYYY') time_pr_date_id
1374 FROM isc_dbi_tmp_del_details
1375 WHERE (least(nvl(time_ip_date_id,l_time_min), nvl(time_pr_date_id,l_time_min)) < l_time_min
1376 OR greatest(nvl(time_ip_date_id, l_time_max), nvl(time_pr_date_id, l_time_max)) > l_time_max);
1377
1378 l_line LINES_MISSING_DATE%ROWTYPE;
1379
1380 BEGIN
1381
1382 l_is_missing := TRUE;
1383 l_time_missing := TRUE;
1384
1385 FII_UTIL.Start_Timer;
1386
1387 BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the incremental load');
1388
1389 SELECT min(time_ip_date_id), max(time_ip_date_id),
1390 min(time_pr_date_id), max(time_pr_date_id)
1391 INTO l_min_ip_date, l_max_ip_date, l_min_pr_date, l_max_pr_date
1392 FROM isc_dbi_tmp_del_details tmp;
1393
1394 l_min := least(nvl(l_min_ip_date,sysdate), nvl(l_min_pr_date,sysdate));
1395 l_max := greatest(nvl(l_max_ip_date,sysdate), nvl(l_max_pr_date,sysdate));
1396
1397 FII_UTIL.Stop_Timer;
1398 FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1399
1400 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1401 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1402 FII_UTIL.Start_Timer;
1403
1404 FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1405
1406 IF (l_is_missing) THEN
1407 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1408 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1409
1410 SELECT min(report_date), max(report_date)
1411 INTO l_time_min, l_time_max
1412 FROM fii_time_day;
1413
1414 OPEN lines_missing_date;
1415 FETCH lines_missing_date INTO l_line;
1416
1417 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1418 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1419 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL_ID'),18,' ')
1420 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1421 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1422 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1423
1424 WHILE LINES_MISSING_DATE%FOUND LOOP
1425 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1426 ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1427 ||' - '||RPAD(nvl(l_line.time_pr_date_id,' '),19,' '));
1428 FETCH Lines_Missing_Date INTO l_line;
1429 END LOOP;
1430
1431 CLOSE LINES_MISSING_DATE;
1432 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------+');
1433
1434 RETURN (-999);
1435 ELSE
1436 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1437 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1438 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1439 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1440 END IF;
1441
1442 FII_UTIL.Stop_Timer;
1443 FII_UTIL.Print_Timer('Completed time continuity check in');
1444
1445 RETURN(1);
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY_DETAIL : '||sqlerrm;
1450 g_retcode := sqlcode;
1451 RETURN(-1);
1452 END;
1453
1454 FUNCTION CHECK_TIME_CONTINUITY_STOP RETURN NUMBER IS
1455
1456 l_min_actl_arrl_date DATE;
1457 l_max_actl_arrl_date DATE;
1458 l_min_init_dept_date DATE;
1459 l_max_init_dept_date DATE;
1460 l_min_pln_arrl_date DATE;
1461 l_max_pln_arrl_date DATE;
1462 l_min DATE;
1463 l_max DATE;
1464 l_is_missing BOOLEAN;
1465 l_time_min DATE;
1466 l_time_max DATE;
1467 l_time_missing BOOLEAN;
1468
1469 CURSOR Stops_Missing_Date IS
1470 SELECT trip_id,
1471 stop_id,
1472 to_char(time_actl_arrl_date_id, 'MM/DD/YYYY') time_actl_arrl_date_id,
1473 to_char(actual_departure_date, 'MM/DD/YYYY') time_actl_dept_date_id,
1474 to_char(time_pln_arrl_date_id,'MM/DD/YYYY') time_pln_arrl_date_id
1475 FROM isc_dbi_tmp_trip_stops
1476 WHERE (least(nvl(time_actl_arrl_date_id,l_time_min), nvl(trunc(actual_departure_date),l_time_min), nvl(time_pln_arrl_date_id,l_time_min)) < l_time_min
1477 OR greatest(nvl(time_actl_arrl_date_id,l_time_max),nvl(trunc(actual_departure_date),l_time_max), nvl(time_pln_arrl_date_id,l_time_max)) > l_time_max);
1478
1479 l_stop STOPS_MISSING_DATE%ROWTYPE;
1480
1481 BEGIN
1482
1483 l_is_missing := TRUE;
1484 l_time_missing := TRUE;
1485
1486 FII_UTIL.Start_Timer;
1487 BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the incremental load');
1488
1489 SELECT min(time_actl_arrl_date_id), max(time_actl_arrl_date_id),
1490 min(time_init_dept_date_id), max(time_init_dept_date_id),
1491 min(time_pln_arrl_date_id), max(time_pln_arrl_date_id)
1492 INTO l_min_actl_arrl_date, l_max_actl_arrl_date, l_min_init_dept_date, l_max_init_dept_date, l_min_pln_arrl_date, l_max_pln_arrl_date
1493 FROM isc_dbi_tmp_trip_stops tmp;
1494
1495 l_min := least(nvl(l_min_actl_arrl_date,sysdate), nvl(l_min_init_dept_date,sysdate), nvl(l_min_pln_arrl_date,sysdate));
1496 l_max := greatest(nvl(l_max_actl_arrl_date,sysdate), nvl(l_max_init_dept_date,sysdate), nvl(l_max_pln_arrl_date,sysdate));
1497
1498 FII_UTIL.Stop_Timer;
1499 FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1500
1501 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1502 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1503 FII_UTIL.Start_Timer;
1504
1505 FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1506
1507 IF (l_is_missing) THEN
1508 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1509 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1510
1511 SELECT min(report_date), max(report_date)
1512 INTO l_time_min, l_time_max
1513 FROM fii_time_day;
1514
1515 OPEN stops_missing_date;
1516 FETCH stops_missing_date INTO l_stop;
1517
1518 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1519 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1520 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1521 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1522 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1523 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1524 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1525 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1526
1527 WHILE STOPS_MISSING_DATE%FOUND LOOP
1528 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1529 ||' - '||RPAD(l_stop.stop_id,18,' ')
1530 ||' - '||RPAD(l_stop.time_actl_arrl_date_id,19,' ')
1531 ||' - '||RPAD(l_stop.time_actl_dept_date_id,21,' ')
1532 ||' - '||RPAD(nvl(l_stop.time_pln_arrl_date_id,' '),20,' '));
1533 FETCH STOPS_MISSING_DATE INTO l_stop;
1534 END LOOP;
1535
1536 CLOSE STOPS_MISSING_DATE;
1537 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1538
1539 RETURN (-999);
1540 ELSE
1541 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1542 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1543 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1544 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1545 END IF;
1546
1547 FII_UTIL.Stop_Timer;
1548 FII_UTIL.Print_Timer('Completed time continuity check in');
1549
1550 RETURN(1);
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY_STOP : '||sqlerrm;
1555 g_retcode := sqlcode;
1556 RETURN(-1);
1557 END;
1558
1559 -- ----------------------------------------
1560 -- Identify Dangling Key for Item Dimension
1561 -- ----------------------------------------
1562
1563 FUNCTION IDENTIFY_DANGLING_ITEM RETURN NUMBER IS
1564
1565 CURSOR Dangling_Items_Init IS
1566 SELECT /*+ PARALLEL(tmp) PARALLEL(item) */ distinct tmp.inventory_item_id, tmp.organization_id
1567 FROM isc_dbi_tmp_del_details tmp,
1568 eni_oltp_item_star item
1569 WHERE tmp.inventory_item_id = item.inventory_item_id(+)
1570 AND tmp.organization_id = item.organization_id(+)
1571 AND item.inventory_item_id IS NULL
1572 AND tmp.inventory_item_id IS NOT NULL;
1573
1574 CURSOR Dangling_Items_Incre IS
1575 SELECT distinct tmp.inventory_item_id, tmp.organization_id
1576 FROM isc_dbi_tmp_del_details tmp,
1577 eni_oltp_item_star item
1578 WHERE tmp.inventory_item_id = item.inventory_item_id(+)
1579 AND tmp.organization_id = item.organization_id(+)
1580 AND item.inventory_item_id IS NULL
1581 AND tmp.inventory_item_id IS NOT NULL;
1582
1583 l_item NUMBER;
1584 l_org NUMBER;
1585 l_total NUMBER;
1586
1587 BEGIN
1588
1589 l_total := 0;
1590
1591 IF (g_load_mode = 'INITIAL') THEN
1592 OPEN dangling_items_init;
1593 FETCH dangling_items_init INTO l_item, l_org;
1594
1595 IF dangling_items_init%ROWCOUNT <> 0 THEN
1596 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1597 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598
1599 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605
1606 WHILE Dangling_Items_Init%FOUND LOOP
1607 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1608 FETCH Dangling_Items_Init INTO l_item, l_org;
1609 END LOOP;
1610 BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611 ELSE
1612 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614 BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1615 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1616 END IF;
1617 l_total := Dangling_Items_Init%ROWCOUNT;
1618 CLOSE Dangling_Items_Init;
1619
1620 ELSIF (g_load_mode = 'INCREMENTAL') THEN
1621 OPEN dangling_items_incre;
1622 FETCH dangling_items_incre INTO l_item, l_org;
1623
1624 IF dangling_items_incre%ROWCOUNT <> 0 THEN
1625 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1626 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627
1628 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634
1635 WHILE Dangling_Items_Incre%FOUND LOOP
1636 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1637 FETCH Dangling_Items_Incre INTO l_item, l_org;
1638 END LOOP;
1639 BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640 ELSE
1641 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643 BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1644 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1645 END IF;
1646 l_total := Dangling_Items_Incre%ROWCOUNT;
1647 CLOSE Dangling_Items_Incre;
1648 END IF;
1649
1650 RETURN(l_total);
1651
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654 g_errbuf := 'Error in Function IDENTIFY_DANGLING_ITEM : '||sqlerrm;
1655 g_retcode := sqlcode;
1656 RETURN(-1);
1657 END;
1658
1659 -- -----------------------------------
1660 -- Reporting of the missing currencies
1661 -- -----------------------------------
1662
1663 FUNCTION REPORT_MISSING_RATE RETURN NUMBER IS
1664
1665 CURSOR Missing_Currency_Conversion IS
1666 SELECT distinct decode(trx_wh_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1667 trx_currency_code FROM_CURRENCY,
1668 wh_currency_code TO_CURRENCY,
1669 conversion_type_code RATE_TYPE,
1670 decode(trx_wh_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1671 FROM isc_dbi_fte_curr_rates tmp
1672 WHERE trx_wh_rate < 0
1673 AND upper(conversion_type_code) <> 'USER'
1674 UNION
1675 SELECT distinct decode(wh_prim_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1676 wh_currency_code FROM_CURRENCY,
1677 g_global_currency TO_CURRENCY,
1678 g_global_rate_type RATE_TYPE,
1679 decode(wh_prim_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1680 FROM isc_dbi_fte_curr_rates tmp
1681 WHERE wh_prim_rate < 0
1682 UNION
1683 SELECT distinct decode(wh_sec_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1684 wh_currency_code FROM_CURRENCY,
1685 g_sec_global_currency TO_CURRENCY,
1686 g_sec_global_rate_type RATE_TYPE,
1687 decode(wh_sec_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1688 FROM isc_dbi_fte_curr_rates tmp
1689 WHERE wh_sec_rate < 0
1690 AND g_sec_curr_def = 'Y';
1691
1692 l_record Missing_Currency_Conversion%ROWTYPE;
1693 l_total NUMBER;
1694
1695 BEGIN
1696
1697 l_total := 0;
1698 OPEN Missing_Currency_Conversion;
1699 FETCH Missing_Currency_Conversion INTO l_record;
1700
1701 IF Missing_Currency_Conversion%ROWCOUNT <> 0
1702 THEN
1703 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1704 BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1705
1706 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1707 WHILE Missing_Currency_Conversion%FOUND LOOP
1708 l_total := l_total + 1;
1709 BIS_COLLECTION_UTILITIES.writeMissingRate(
1710 l_record.rate_type,
1711 l_record.from_currency,
1712 l_record.to_currency,
1713 l_record.curr_conv_date);
1714 FETCH Missing_Currency_Conversion INTO l_record;
1715 END LOOP;
1716 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1717 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1718
1719 ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1722 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1723 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1724 END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1725
1726 CLOSE Missing_Currency_Conversion;
1727
1728 RETURN(l_total);
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 g_errbuf := 'Error in Function REPORT_MISSING_RATE : '||sqlerrm;
1733 g_retcode := sqlcode;
1734 RETURN(-1);
1735 END;
1736
1737 -- ---------------------------------------------
1738 -- Reporting of the Missing UOM Conversion Rates
1739 -- ---------------------------------------------
1740
1741 FUNCTION REPORT_MISSING_UOM_RATE RETURN NUMBER IS
1742
1743 CURSOR Missing_UOM_Conversion IS
1744 SELECT distinct inventory_item_id,
1745 from_uom_code from_unit,
1746 to_uom_code to_unit
1747 FROM isc_dbi_fte_uom_rates
1748 WHERE conversion_rate between -99999 and -99995;
1749
1750 CURSOR Missing_Transaction_UOM IS
1751 SELECT name
1752 FROM wsh_new_deliveries
1753 WHERE delivery_id IN (SELECT distinct delivery_id
1754 FROM isc_dbi_tmp_del_legs
1755 WHERE (freight_weight_trx is not null and weight_uom_code is null)
1756 OR (freight_volume_trx is not null and volume_uom_code is null));
1757
1758 l_record Missing_UOM_Conversion%ROWTYPE;
1759 l_uom_record Missing_Transaction_UOM%ROWTYPE;
1760 l_total NUMBER;
1761
1762 BEGIN
1763
1764 l_total := 0;
1765 OPEN Missing_UOM_Conversion;
1766 FETCH Missing_UOM_Conversion INTO l_record;
1767
1768 IF Missing_UOM_Conversion%ROWCOUNT <> 0 THEN
1769 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1770 BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_UOM_NO_LOAD'));
1771
1772 OPI_DBI_REP_UOM_PKG.err_msg_header;
1773 WHILE Missing_UOM_Conversion%FOUND LOOP
1774 l_total := l_total + 1;
1775
1776 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms(
1777 nvl(l_record.from_unit,' '),
1778 nvl(l_record.to_unit,' '));
1779
1780 FETCH Missing_UOM_Conversion INTO l_record;
1781 END LOOP;
1782 OPI_DBI_REP_UOM_PKG.Err_Msg_Footer;
1783
1784 ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1785 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1786 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1787 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1788 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1789 END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1790
1791 CLOSE Missing_UOM_Conversion;
1792
1793 OPEN Missing_Transaction_UOM;
1794 FETCH Missing_Transaction_UOM INTO l_uom_record;
1795
1796 IF Missing_Transaction_UOM%ROWCOUNT <> 0 THEN
1797 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1798 g_errbuf := g_errbuf || 'There are transactions that do not have transaction UOMs.';
1799
1800 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804
1805 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1806
1807 WHILE Missing_Transaction_UOM%FOUND LOOP
1808 l_total := l_total + 1;
1809 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_uom_record.name,16,' '));
1810
1811 FETCH Missing_Transaction_UOM INTO l_uom_record;
1812 END LOOP;
1813 BIS_COLLECTION_UTILITIES.Put_Line_Out('+---------------------------------------------------------------------------+');
1814 END IF;
1815
1816 CLOSE Missing_Transaction_UOM;
1817
1818 RETURN(l_total);
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 g_errbuf := 'Error in Function REPORT_MISSING_UOM_RATE : '||sqlerrm;
1823 g_retcode := sqlcode;
1824 RETURN(-1);
1825 END;
1826
1827 -- --------------
1828 -- DANGLING_CHECK
1829 -- --------------
1830
1831 FUNCTION DANGLING_CHECK_INIT RETURN NUMBER IS
1832
1833 l_time_danling NUMBER;
1834 l_item_count NUMBER;
1835 l_miss_conv NUMBER;
1836 l_miss_uom NUMBER;
1837 l_dangling NUMBER;
1838
1839 BEGIN
1840
1841 l_time_danling := 0;
1842 l_item_count := 0;
1843 l_miss_conv := 0;
1844 l_miss_uom := 0;
1845 l_dangling := 0;
1846
1847 -- ----------------------------------------------------------
1848 -- Identify Missing Currency Rate
1849 -- When there is missing rate, exit the collection with error
1850 -- ----------------------------------------------------------
1851
1852 BIS_COLLECTION_UTILITIES.put_line(' ');
1853 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1854 FII_UTIL.Start_Timer;
1855
1856 l_miss_conv := REPORT_MISSING_RATE;
1857
1858 FII_UTIL.Stop_Timer;
1859 FII_UTIL.Print_Timer('Completed missing currency check in');
1860
1861 IF (l_miss_conv = -1) THEN
1862 return(-1);
1863 ELSIF (l_miss_conv > 0) THEN
1864 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
1865 l_dangling := -999;
1866 END IF;
1867
1868 -- --------------------------------------------------------------
1869 -- Identify Missing UOM Rate
1870 -- When there is missing UOM rate, exit the collection with error
1871 -- --------------------------------------------------------------
1872
1873 BIS_COLLECTION_UTILITIES.put_line(' ');
1874 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
1875 FII_UTIL.Start_Timer;
1876
1877 l_miss_uom := REPORT_MISSING_UOM_RATE;
1878
1879 FII_UTIL.Stop_Timer;
1880 FII_UTIL.Print_Timer('Completed missing UOM check in');
1881
1882 IF (l_miss_uom = -1) THEN
1883 return(-1);
1884 ELSIF (l_miss_uom > 0) THEN
1885 g_errbuf := g_errbuf || 'Collection aborted due to missing UOM conversion rates. ';
1886 l_dangling := -999;
1887 END IF;
1888
1889 -- ---------------------
1890 -- CHECK_TIME_CONTINUITY
1891 -- ---------------------
1892
1893 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1894 BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1895
1896 l_time_danling := CHECK_TIME_CONTINUITY_INIT;
1897
1898 IF (l_time_danling = -1) THEN
1899 return(-1);
1900 ELSIF (l_time_danling = -999) THEN
1901 g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
1902 l_dangling := -999;
1903 END IF;
1904
1905 -- -------------------------------------
1906 -- Check Dangling Key for Item Dimension
1907 -- -------------------------------------
1908
1909 BIS_COLLECTION_UTILITIES.put_line(' ');
1910 BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1911
1912 FII_UTIL.Start_Timer;
1913
1914 l_item_count := IDENTIFY_DANGLING_ITEM;
1915
1916 FII_UTIL.Stop_Timer;
1917 FII_UTIL.Print_Timer('Identified '||l_item_count||' dangling items in');
1918
1919 IF (l_item_count = -1)
1920 THEN return(-1);
1921 ELSIF (l_item_count > 0) THEN
1922 g_errbuf := g_errbuf || 'Collection aborted due to dangling items. ';
1923 l_dangling := -999;
1924 END IF;
1925
1926 IF (l_dangling = -999) THEN
1927 return(-1);
1928 END IF;
1929
1930 RETURN(1);
1931
1932 EXCEPTION
1933 WHEN OTHERS THEN
1934 g_errbuf := 'Error in Function DANGLING_CHECK_INIT : '||sqlerrm;
1935 g_retcode := sqlcode;
1936 RETURN(-1);
1937
1938 END dangling_check_init;
1939
1940 FUNCTION DANGLING_CHECK_DETAIL_ICRL RETURN NUMBER IS
1941
1942 l_time_danling NUMBER;
1943 l_item_count NUMBER;
1944 l_dangling NUMBER;
1945
1946 BEGIN
1947
1948 l_time_danling := 0;
1949 l_item_count := 0;
1950 l_dangling := 0;
1951
1952 -- ---------------------
1953 -- CHECK_TIME_CONTINUITY
1954 -- ---------------------
1955
1956 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1957 BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1958
1959 l_time_danling := CHECK_TIME_CONTINUITY_DETAIL;
1960
1961 IF (l_time_danling = -1) THEN
1962 return(-1);
1963 ELSIF (l_time_danling = -999) THEN
1964 g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
1965 l_dangling := -999;
1966 END IF;
1967
1968 -- -------------------------------------
1969 -- Check Dangling Key for Item Dimension
1970 -- -------------------------------------
1971
1972 BIS_COLLECTION_UTILITIES.put_line(' ');
1973 BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1974
1975 FII_UTIL.Start_Timer;
1976
1977 l_item_count := IDENTIFY_DANGLING_ITEM;
1978
1979 FII_UTIL.Stop_Timer;
1980 FII_UTIL.Print_Timer('Identified '||l_item_count||' dangling items in');
1981
1982 IF (l_item_count = -1) THEN
1983 return(-1);
1984 ELSIF (l_item_count > 0) THEN
1985 g_errbuf := g_errbuf || 'Collection aborted due to dangling items. ';
1986 l_dangling := -999;
1987 END IF;
1988
1989 IF (l_dangling = -999) THEN
1990 return(-1);
1991 END IF;
1992
1993 RETURN(1);
1994
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 g_errbuf := 'Error in Function DANGLING_CHECK_DETAIL_ICRL : '||sqlerrm;
1998 g_retcode := sqlcode;
1999 RETURN(-1);
2000
2001 END dangling_check_detail_icrl;
2002
2003
2004 FUNCTION DANGLING_CHECK_LEG_STOP_ICRL RETURN NUMBER IS
2005
2006 l_time_danling NUMBER;
2007 l_miss_conv NUMBER;
2008 l_miss_uom NUMBER;
2009 l_dangling NUMBER;
2010
2011 BEGIN
2012
2013 l_time_danling := 0;
2014 l_miss_conv := 0;
2015 l_miss_uom := 0;
2016 l_dangling := 0;
2017
2018 -- ----------------------------------------------------------
2019 -- Identify Missing Currency Rate
2020 -- When there is missing rate, exit the collection with error
2021 -- ----------------------------------------------------------
2022
2023 BIS_COLLECTION_UTILITIES.put_line(' ');
2024 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2025 FII_UTIL.Start_Timer;
2026
2027 l_miss_conv := REPORT_MISSING_RATE;
2028
2029 FII_UTIL.Stop_Timer;
2030 FII_UTIL.Print_Timer('Completed missing currency check in');
2031
2032 IF (l_miss_conv = -1) THEN
2033 return(-1);
2034 ELSIF (l_miss_conv > 0) THEN
2035 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
2036 l_dangling := -999;
2037 END IF;
2038
2039 -- --------------------------------------------------------------
2040 -- Identify Missing UOM Rate
2041 -- When there is missing UOM rate, exit the collection with error
2042 -- --------------------------------------------------------------
2043
2044 BIS_COLLECTION_UTILITIES.put_line(' ');
2045 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
2046 FII_UTIL.Start_Timer;
2047
2048 l_miss_uom := REPORT_MISSING_UOM_RATE;
2049
2050 FII_UTIL.Stop_Timer;
2051 FII_UTIL.Print_Timer('Completed missing UOM check in');
2052
2053 IF (l_miss_uom = -1) THEN
2054 return(-1);
2055 ELSIF (l_miss_uom > 0) THEN
2056 g_errbuf := g_errbuf || 'Collection aborted due to missing UOM conversion rates. ';
2057 l_dangling := -999;
2058 END IF;
2059
2060 -- ---------------------
2061 -- CHECK_TIME_CONTINUITY
2062 -- ---------------------
2063
2064 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2065 BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
2066
2067 l_time_danling := CHECK_TIME_CONTINUITY_STOP;
2068
2069 IF (l_time_danling = -1) THEN
2070 return(-1);
2071 ELSIF (l_time_danling = -999) THEN
2072 g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
2073 l_dangling := -999;
2074 END IF;
2075
2076 IF (l_dangling = -999) THEN
2077 return(-1);
2078 END IF;
2079
2080 RETURN(1);
2081
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 g_errbuf := 'Error in Function DANGLING_CHECK_LEG_STOP_ICRL : '||sqlerrm;
2085 g_retcode := sqlcode;
2086 RETURN(-1);
2087
2088 END dangling_check_leg_stop_icrl;
2089
2090
2091 FUNCTION DANGLING_CHECK_INVOICE_ICRL RETURN NUMBER IS
2092
2093 l_time_danling NUMBER;
2094 l_miss_conv NUMBER;
2095 l_dangling NUMBER;
2096
2097 BEGIN
2098
2099 l_time_danling := 0;
2100 l_miss_conv := 0;
2101 l_dangling := 0;
2102
2103 -- ----------------------------------------------------------
2104 -- Identify Missing Currency Rate
2105 -- When there is missing rate, exit the collection with error
2106 -- ----------------------------------------------------------
2107
2108 BIS_COLLECTION_UTILITIES.put_line(' ');
2109 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2110 FII_UTIL.Start_Timer;
2111
2112 l_miss_conv := REPORT_MISSING_RATE;
2113
2114 FII_UTIL.Stop_Timer;
2115 FII_UTIL.Print_Timer('Completed missing currency check in');
2116
2117 IF (l_miss_conv = -1) THEN
2118 return(-1);
2119 ELSIF (l_miss_conv > 0) THEN
2120 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
2121 l_dangling := -999;
2122 END IF;
2123
2124 IF (l_dangling = -999) THEN
2125 return(-1);
2126 END IF;
2127
2128 RETURN(1);
2129
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 g_errbuf := 'Error in Function DANGLING_CHECK_INVOICE_ICRL : '||sqlerrm;
2133 g_retcode := sqlcode;
2134 RETURN(-1);
2135
2136 END dangling_check_invoice_icrl;
2137
2138 -- -----------
2139 -- INSERT_FACT
2140 -- -----------
2141
2142 FUNCTION INSERT_FACT RETURN NUMBER IS
2143
2144 l_detail_count NUMBER;
2145 l_leg_count NUMBER;
2146 l_stop_count NUMBER;
2147 l_invoice_count NUMBER;
2148
2149 BEGIN
2150
2151 BIS_COLLECTION_UTILITIES.put_line(' ');
2152 BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_details_f');
2153 FII_UTIL.Start_Timer;
2154
2155 INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_DETAILS_F F
2156 (DELIVERY_DETAIL_ID,
2157 INVENTORY_ITEM_ID,
2158 SHIPMENT_DIRECTION,
2159 ORGANIZATION_ID,
2160 SUBINVENTORY_CODE,
2161 TIME_IP_DATE_ID,
2162 TIME_PR_DATE_ID,
2163 DELIVERY_ID,
2164 INITIAL_PICKUP_DATE,
2165 MOVE_ORDER_LINE_ID,
2166 PICK_RELEASED_DATE,
2167 RELEASED_STATUS,
2168 REQUESTED_QUANTITY,
2169 REQUESTED_QUANTITY_UOM,
2170 SHIPPED_QUANTITY,
2171 WMS_ENABLED_FLAG,
2172 CREATED_BY,
2173 CREATION_DATE,
2174 LAST_UPDATED_BY,
2175 LAST_UPDATE_DATE,
2176 LAST_UPDATE_LOGIN,
2177 PROGRAM_APPLICATION_ID,
2178 PROGRAM_ID,
2179 PROGRAM_UPDATE_DATE,
2180 REQUEST_ID)
2181 SELECT /*+ PARALLEL(v) */
2182 v.delivery_detail_id DELIVERY_DETAIL_ID,
2183 v.inventory_item_id INVENTORY_ITEM_ID,
2184 v.shipment_direction SHIPMENT_DIRECTION,
2185 v.organization_id ORGANIZATION_ID,
2186 v.subinventory_code SUBINVENTORY_CODE,
2187 v.time_ip_date_id TIME_IP_DATE_ID,
2188 v.time_pr_date_id TIME_PR_DATE_ID,
2189 v.delivery_id DELIVERY_ID,
2190 v.initial_pickup_date INITIAL_PICKUP_DATE,
2191 v.move_order_line_id MOVE_ORDER_LINE_ID,
2192 v.pick_released_date PICK_RELEASED_DATE,
2193 v.released_status RELEASED_STATUS,
2194 v.requested_quantity REQUESTED_QUANTITY,
2195 v.requested_quantity_uom REQUESTED_QUANTITY_UOM,
2196 v.shipped_quantity SHIPPED_QUANTITY,
2197 v.wms_enabled_flag WMS_ENABLED_FLAG,
2198 -1 CREATED_BY,
2199 sysdate CREATION_DATE,
2200 -1 LAST_UPDATED_BY,
2201 sysdate LAST_UPDATE_DATE,
2202 -1 LAST_UPDATE_LOGIN,
2203 -1 PROGRAM_APPLICATION_ID,
2204 -1 PROGRAM_ID,
2205 sysdate PROGRAM_UPDATE_DATE,
2206 -1 REQUEST_ID
2207 FROM isc_dbi_tmp_del_details v;
2208
2209 l_detail_count := sql%rowcount;
2210 FII_UTIL.Stop_Timer;
2211 FII_UTIL.Print_Timer('Inserted '|| l_detail_count ||' rows into isc_dbi_del_details_f in');
2212
2213 BIS_COLLECTION_UTILITIES.put_line(' ');
2214 BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_legs_f');
2215 FII_UTIL.Start_Timer;
2216
2217 INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_LEGS_F F
2218 (DELIVERY_LEG_ID,
2219 CARRIER_ID,
2220 SHIPMENT_DIRECTION,
2221 MODE_OF_TRANSPORT,
2222 ORGANIZATION_ID,
2223 SERVICE_LEVEL,
2224 TIME_INIT_DEPT_DATE_ID,
2225 DELIVERY_ID,
2226 DROP_OFF_STOP_ID,
2227 FREIGHT_COST_F,
2228 FREIGHT_COST_G,
2229 FREIGHT_COST_G1,
2230 FREIGHT_VOLUME_G,
2231 FREIGHT_VOLUME_TRX,
2232 FREIGHT_WEIGHT_G,
2233 FREIGHT_WEIGHT_TRX,
2234 PICK_UP_STOP_ID,
2235 TRIP_ID,
2236 VOLUME_UOM_CODE,
2237 WEIGHT_UOM_CODE,
2238 CREATED_BY,
2239 CREATION_DATE,
2240 LAST_UPDATED_BY,
2241 LAST_UPDATE_DATE,
2242 LAST_UPDATE_LOGIN,
2243 PROGRAM_APPLICATION_ID,
2244 PROGRAM_ID,
2245 PROGRAM_UPDATE_DATE,
2246 REQUEST_ID,
2247 DELIVERY_TYPE,
2248 PARENT_DELIVERY_LEG_ID)
2249 SELECT /*+ PARALLEL(v) */
2250 v.delivery_leg_id DELIVERY_LEG_ID,
2251 v.carrier_id CARRIER_ID,
2252 v.shipment_direction SHIPMENT_DIRECTION,
2253 v.mode_of_transport MODE_OF_TRANSPORT,
2254 v.organization_id ORGANIZATION_ID,
2255 v.service_level SERVICE_LEVEL,
2256 v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
2257 v.delivery_id DELIVERY_ID,
2258 v.drop_off_stop_id DROP_OFF_STOP_ID,
2259 v.freight_cost_trx
2260 * nvl(v.conversion_rate, curr.trx_wh_rate) FREIGHT_COST_F,
2261 v.freight_cost_trx
2262 * decode(v.trx_currency_code, g_global_currency, 1, curr.trx_wh_rate * curr.wh_prim_rate) FREIGHT_COST_G,
2263 v.freight_cost_trx
2264 * decode(v.trx_currency_code, g_sec_global_currency, 1, curr.trx_wh_rate * curr.wh_sec_rate) FREIGHT_COST_G1,
2265 v.freight_volume_trx * v_rates.conversion_rate FREIGHT_VOLUME_G,
2266 v.freight_volume_trx FREIGHT_VOLUME_TRX,
2267 v.freight_weight_trx * w_rates.conversion_rate FREIGHT_WEIGHT_G,
2268 v.freight_weight_trx FREIGHT_WEIGHT_TRX,
2269 v.pick_up_stop_id PICK_UP_STOP_ID,
2270 v.trip_id TRIP_ID,
2271 v.volume_uom_code VOLUME_UOM_CODE,
2272 v.weight_uom_code WEIGHT_UOM_CODE,
2273 -1 CREATED_BY,
2274 sysdate CREATION_DATE,
2275 -1 LAST_UPDATED_BY,
2276 sysdate LAST_UPDATE_DATE,
2277 -1 LAST_UPDATE_LOGIN,
2278 -1 PROGRAM_APPLICATION_ID,
2279 -1 PROGRAM_ID,
2280 sysdate PROGRAM_UPDATE_DATE,
2281 -1 REQUEST_ID,
2282 v.delivery_type DELIVERY_TYPE,
2283 v.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
2284 FROM isc_dbi_tmp_del_legs v,
2285 isc_dbi_fte_curr_rates curr,
2286 isc_dbi_fte_uom_rates w_rates,
2287 isc_dbi_fte_uom_rates v_rates
2288 WHERE v.weight_uom_code = w_rates.from_uom_code(+)
2289 AND w_rates.measure_code(+) = 'WT'
2290 AND v.volume_uom_code = v_rates.from_uom_code(+)
2291 AND v_rates.measure_code(+) = 'VOL'
2292 AND v.trx_currency_code = curr.trx_currency_code(+)
2293 AND v.wh_currency_code = curr.wh_currency_code(+)
2294 AND v.conversion_date = curr.conversion_date(+)
2295 AND v.conversion_type_code = curr.conversion_type_code(+);
2296
2297 l_leg_count := sql%rowcount;
2298 FII_UTIL.Stop_Timer;
2299 FII_UTIL.Print_Timer('Inserted '|| l_leg_count ||' rows into isc_dbi_del_legs_f in');
2300
2301 BIS_COLLECTION_UTILITIES.put_line(' ');
2302 BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_trip_stops_f');
2303 FII_UTIL.Start_Timer;
2304
2305 INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_TRIP_STOPS_F F
2306 (STOP_ID,
2307 CARRIER_ID,
2308 MODE_OF_TRANSPORT,
2309 SERVICE_LEVEL,
2310 TIME_ACTL_ARRL_DATE_ID,
2311 TIME_INIT_DEPT_DATE_ID,
2312 TIME_PLN_ARRL_DATE_ID,
2313 ACTUAL_ARRIVAL_DATE,
2314 ACTUAL_DEPARTURE_DATE,
2315 DISTANCE_TO_NEXT_STOP_G,
2316 DISTANCE_TO_NEXT_STOP_TRX,
2317 DISTANCE_UOM_CODE,
2318 PLANNED_ARRIVAL_DATE,
2319 STOP_RANK,
2320 STOP_SEQUENCE_NUMBER,
2321 TRIP_FREIGHT_COST_G,
2322 TRIP_FREIGHT_COST_G1,
2323 TRIP_ID,
2324 ULTIMATE_STOP_SEQUENCE_NUMBER,
2325 CREATED_BY,
2326 CREATION_DATE,
2327 LAST_UPDATED_BY,
2328 LAST_UPDATE_DATE,
2329 LAST_UPDATE_LOGIN,
2330 PROGRAM_APPLICATION_ID,
2331 PROGRAM_ID,
2332 PROGRAM_UPDATE_DATE,
2333 REQUEST_ID)
2334 SELECT /*+ PARALLEL(v) PARALLEL(itr) */
2335 v.stop_id STOP_ID,
2336 v.carrier_id CARRIER_ID,
2337 v.mode_of_transport MODE_OF_TRANSPORT,
2338 v.service_level SERVICE_LEVEL,
2339 v.time_actl_arrl_date_id TIME_ACTL_ARRL_DATE_ID,
2340 v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
2341 v.time_pln_arrl_date_id TIME_PLN_ARRL_DATE_ID,
2342 v.actual_arrival_date ACTUAL_ARRIVAL_DATE,
2343 v.actual_departure_date ACTUAL_DEPARTURE_DATE,
2344 v.distance_to_next_stop_trx * d_rates.conversion_rate DISTANCE_TO_NEXT_STOP_G,
2345 v.distance_to_next_stop_trx DISTANCE_TO_NEXT_STOP_TRX,
2346 v.distance_uom_code DISTANCE_UOM_CODE,
2347 v.planned_arrival_date PLANNED_ARRIVAL_DATE,
2348 v.stop_rank STOP_RANK,
2349 v.stop_sequence_number STOP_SEQUENCE_NUMBER,
2350 itr.trip_freight_cost_g TRIP_FREIGHT_COST_G,
2351 itr.trip_freight_cost_g1 TRIP_FREIGHT_COST_G1,
2352 v.trip_id TRIP_ID,
2353 v.ultimate_stop_sequence_number ULTIMATE_STOP_SEQUENCE_NUMBER,
2354 -1 CREATED_BY,
2355 sysdate CREATION_DATE,
2356 -1 LAST_UPDATED_BY,
2357 sysdate LAST_UPDATE_DATE,
2358 -1 LAST_UPDATE_LOGIN,
2359 -1 PROGRAM_APPLICATION_ID,
2360 -1 PROGRAM_ID,
2361 sysdate PROGRAM_UPDATE_DATE,
2362 -1 REQUEST_ID
2363 FROM isc_dbi_tmp_trip_stops v,
2364 (select /*+ PARALLEL(tmp) */ trip_id,
2365 sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
2366 * decode(tmp.trx_currency_code,g_global_currency,1,curr.trx_wh_rate * curr.wh_prim_rate)) TRIP_FREIGHT_COST_G,
2367 sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
2368 * decode(tmp.trx_currency_code,g_sec_global_currency,1,curr.trx_wh_rate*curr.wh_sec_rate)) TRIP_FREIGHT_COST_G1
2369 from isc_dbi_tmp_del_legs tmp,
2370 isc_dbi_fte_curr_rates curr
2371 where tmp.trx_currency_code = curr.trx_currency_code(+)
2372 and tmp.wh_currency_code = curr.wh_currency_code(+)
2373 and tmp.conversion_date = curr.conversion_date(+)
2374 and tmp.conversion_type_code = curr.conversion_type_code(+)
2375 group by trip_id) itr,
2376 isc_dbi_fte_uom_rates d_rates
2377 WHERE v.trip_id = itr.trip_id
2378 AND v.distance_uom_code = d_rates.from_uom_code(+)
2379 AND d_rates.measure_code(+) = 'DIS';
2380
2381 l_stop_count := sql%rowcount;
2382 FII_UTIL.Stop_Timer;
2383 FII_UTIL.Print_Timer('Inserted '|| l_stop_count ||' rows into isc_dbi_trip_stops_f in');
2384
2385 BIS_COLLECTION_UTILITIES.put_line(' ');
2386 BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_fte_invoices_f');
2387 FII_UTIL.Start_Timer;
2388
2389 INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_FTE_INVOICES_F F
2390 (INVOICE_HEADER_ID,
2391 CARRIER_ID,
2392 MODE_OF_TRANSPORT,
2393 ORG_ID,
2394 SERVICE_LEVEL,
2395 SUPPLIER_ID,
2396 APPROVED_AMT_F,
2397 APPROVED_AMT_G,
2398 APPROVED_AMT_G1,
2399 BILL_AMT_F,
2400 BILL_AMT_G,
2401 BILL_AMT_G1,
2402 BILL_NUMBER,
2403 BILL_STATUS,
2404 BILL_TYPE,
2405 BOL,
2406 DELIVERY_LEG_ID,
2407 TRIP_ID,
2408 CREATED_BY,
2409 CREATION_DATE,
2410 LAST_UPDATED_BY,
2411 LAST_UPDATE_DATE,
2412 LAST_UPDATE_LOGIN,
2413 PROGRAM_APPLICATION_ID,
2414 PROGRAM_ID,
2415 PROGRAM_UPDATE_DATE,
2416 REQUEST_ID)
2417 SELECT /*+ PARALLEL(v) PARALLEL(curr) */
2418 v.invoice_header_id INVOICE_HEADER_ID,
2419 v.carrier_id CARRIER_ID,
2420 v.mode_of_transport MODE_OF_TRANSPORT,
2421 v.org_id ORG_ID,
2422 v.service_level SERVICE_LEVEL,
2423 v.supplier_id SUPPLIER_ID,
2424 v.approved_amt_trx * curr.trx_wh_rate APPROVED_AMT_F,
2425 v.approved_amt_trx
2426 * decode(v.trx_currency_code, g_global_currency, 1,
2427 curr.trx_wh_rate * curr.wh_prim_rate) APPROVED_AMT_G,
2428 v.approved_amt_trx
2429 * decode(v.trx_currency_code, g_sec_global_currency, 1,
2430 curr.trx_wh_rate * curr.wh_sec_rate) APPROVED_AMT_G1,
2431 v.bill_amt_trx * curr.trx_wh_rate BILL_AMT_F,
2432 v.bill_amt_trx
2433 * decode(v.trx_currency_code, g_global_currency, 1,
2434 curr.trx_wh_rate * curr.wh_prim_rate) BILL_AMT_G,
2435 v.bill_amt_trx
2436 * decode(v.trx_currency_code, g_sec_global_currency, 1,
2437 curr.trx_wh_rate * curr.wh_sec_rate) BILL_AMT_G1,
2438 v.bill_number BILL_NUMBER,
2439 v.bill_status BILL_STATUS,
2440 v.bill_type BILL_TYPE,
2441 v.bol BOL,
2442 v.delivery_leg_id DELIVERY_LEG_ID,
2443 v.trip_id TRIP_ID,
2444 -1 CREATED_BY,
2445 sysdate CREATION_DATE,
2446 -1 LAST_UPDATED_BY,
2447 sysdate LAST_UPDATE_DATE,
2448 -1 LAST_UPDATE_LOGIN,
2449 -1 PROGRAM_APPLICATION_ID,
2450 -1 PROGRAM_ID,
2451 sysdate PROGRAM_UPDATE_DATE,
2452 -1 REQUEST_ID
2453 FROM isc_dbi_tmp_fte_invoices v,
2454 isc_dbi_fte_curr_rates curr
2455 WHERE v.trx_currency_code = curr.trx_currency_code
2456 AND v.wh_currency_code = curr.wh_currency_code
2457 AND v.conversion_date = curr.conversion_date
2458 AND v.conversion_type_code = curr.conversion_type_code;
2459
2460 l_invoice_count := sql%rowcount;
2461 FII_UTIL.Stop_Timer;
2462 FII_UTIL.Print_Timer('Inserted '|| l_invoice_count ||' rows into isc_dbi_fte_invoices_f in');
2463
2464 COMMIT;
2465 RETURN(l_detail_count + l_leg_count + l_stop_count + l_invoice_count);
2466
2467 EXCEPTION
2468 WHEN OTHERS THEN
2469 g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
2470 g_retcode := sqlcode;
2471 RETURN(-1);
2472
2473 END insert_fact;
2474
2475 -- -----------
2476 -- MERGE_FACT
2477 -- -----------
2478
2479 FUNCTION MERGE_DETAIL_FACT(p_batch number) RETURN NUMBER IS
2480
2481 l_count NUMBER;
2482 l_total NUMBER;
2483 l_max_batch NUMBER;
2484 l_date DATE;
2485
2486 BEGIN
2487
2488 l_total := 0;
2489 l_date := to_date('01/01/0001','DD/MM/YYYY');
2490
2491 FOR v_batch_id IN 1..p_batch
2492 LOOP
2493 FII_UTIL.Start_Timer;
2494 BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2495
2496 l_count := 0;
2497
2498 MERGE INTO ISC_DBI_DEL_DETAILS_F f
2499 USING
2500 (select new.* from isc_dbi_tmp_del_details new, isc_dbi_del_details_f old
2501 where new.delivery_detail_id = old.delivery_detail_id(+)
2502 and new.batch_id = v_batch_id
2503 and (old.delivery_detail_id is null
2504 or new.inventory_item_id <> old.inventory_item_id
2505 or new.shipment_direction <> old.shipment_direction
2506 or new.organization_id <> old.organization_id
2507 or nvl(new.subinventory_code, -1) <> nvl(old.subinventory_code, -1)
2508 or nvl(new.delivery_id, -1) <> nvl(old.delivery_id, -1)
2509 or nvl(new.initial_pickup_date,l_date) <> nvl(old.initial_pickup_date,l_date)
2510 or nvl(new.move_order_line_id,-1) <> nvl(old.move_order_line_id,-1)
2511 or nvl(new.pick_released_date,l_date) <> nvl(old.pick_released_date,l_date)
2512 or nvl(new.released_status,'na') <> nvl(old.released_status,'na')
2513 or new.requested_quantity <> old.requested_quantity
2514 or new.requested_quantity_uom <> old.requested_quantity_uom
2515 or nvl(new.shipped_quantity,-1) <> nvl(old.shipped_quantity,-1)
2516 or nvl(new.wms_enabled_flag,'na') <> nvl(old.wms_enabled_flag,'na'))) v
2517 ON (f.delivery_detail_id = v.delivery_detail_id)
2518 WHEN MATCHED THEN UPDATE SET
2519 f.inventory_item_id = v.inventory_item_id,
2520 f.shipment_direction = v.shipment_direction,
2521 f.organization_id = v.organization_id,
2522 f.subinventory_code = v.subinventory_code,
2523 f.time_ip_date_id = v.time_ip_date_id,
2524 f.time_pr_date_id = v.time_pr_date_id,
2525 f.delivery_id = v.delivery_id,
2526 f.initial_pickup_date = v.initial_pickup_date,
2527 f.move_order_line_id = v.move_order_line_id,
2528 f.pick_released_date = v.pick_released_date,
2529 f.released_status = v.released_status,
2530 f.requested_quantity = v.requested_quantity,
2531 f.requested_quantity_uom = v.requested_quantity_uom,
2532 f.shipped_quantity = v.shipped_quantity,
2533 f.wms_enabled_flag = v.wms_enabled_flag,
2534 f.last_update_date = g_incre_start_date
2535 WHEN NOT MATCHED THEN INSERT(
2536 f.delivery_detail_id,
2537 f.inventory_item_id,
2538 f.shipment_direction,
2539 f.organization_id,
2540 f.subinventory_code,
2541 f.time_ip_date_id,
2542 f.time_pr_date_id,
2543 f.delivery_id,
2544 f.initial_pickup_date,
2545 f.move_order_line_id,
2546 f.pick_released_date,
2547 f.released_status,
2548 f.requested_quantity,
2549 f.requested_quantity_uom,
2550 f.shipped_quantity,
2551 f.wms_enabled_flag,
2552 f.created_by,
2553 f.creation_date,
2554 f.last_updated_by,
2555 f.last_update_date,
2556 f.last_update_login,
2557 f.program_application_id,
2558 f.program_id,
2559 f.program_update_date,
2560 f.request_id)
2561 VALUES (
2562 v.delivery_detail_id,
2563 v.inventory_item_id,
2564 v.shipment_direction,
2565 v.organization_id,
2566 v.subinventory_code,
2567 v.time_ip_date_id,
2568 v.time_pr_date_id,
2569 v.delivery_id,
2570 v.initial_pickup_date,
2571 v.move_order_line_id,
2572 v.pick_released_date,
2573 v.released_status,
2574 v.requested_quantity,
2575 v.requested_quantity_uom,
2576 v.shipped_quantity,
2577 v.wms_enabled_flag,
2578 -1,
2579 g_incre_start_date,
2580 -1,
2581 g_incre_start_date,
2582 -1,
2583 -1,
2584 -1,
2585 g_incre_start_date,
2586 -1);
2587
2588 l_count := sql%rowcount;
2589 l_total := l_total + l_count;
2590 COMMIT;
2591 FII_UTIL.Stop_Timer;
2592 FII_UTIL.Print_Timer('Merged '||l_count|| ' rows in ');
2593
2594 END LOOP;
2595
2596 RETURN(l_total);
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 g_errbuf := 'Error in Function MERGE_DETAIL_FACT : '||sqlerrm;
2601 g_retcode := sqlcode;
2602 RETURN(-1);
2603
2604 END merge_detail_fact;
2605
2606 FUNCTION MERGE_LEG_STOP_FACT(p_batch number) RETURN NUMBER IS
2607
2608 l_count NUMBER;
2609 l_total NUMBER;
2610 l_max_batch NUMBER;
2611 l_date DATE;
2612
2613 BEGIN
2614
2615 l_total := 0;
2616 l_date := to_date('01/01/0001','DD/MM/YYYY');
2617
2618 FOR v_batch_id IN 1..p_batch
2619 LOOP
2620 FII_UTIL.Start_Timer;
2621 BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2622
2623 l_count := 0;
2624
2625 MERGE INTO ISC_DBI_DEL_LEGS_F f
2626 USING
2627 (select new.*
2628 from (select tmp.delivery_leg_id DELIVERY_LEG_ID,
2629 tmp.carrier_id CARRIER_ID,
2630 tmp.shipment_direction SHIPMENT_DIRECTION,
2631 tmp.mode_of_transport MODE_OF_TRANSPORT,
2632 tmp.organization_id ORGANIZATION_ID,
2633 tmp.service_level SERVICE_LEVEL,
2634 tmp.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
2635 tmp.delivery_id DELIVERY_ID,
2636 tmp.drop_off_stop_id DROP_OFF_STOP_ID,
2637 tmp.freight_cost_trx
2638 * nvl(tmp.conversion_rate, curr.trx_wh_rate) FREIGHT_COST_F,
2639 tmp.freight_cost_trx
2640 * decode(tmp.trx_currency_code, g_global_currency, 1, curr.trx_wh_rate * curr.wh_prim_rate) FREIGHT_COST_G,
2641 tmp.freight_cost_trx
2642 * decode(tmp.trx_currency_code, g_sec_global_currency, 1, curr.trx_wh_rate * curr.wh_sec_rate) FREIGHT_COST_G1,
2643 tmp.freight_volume_trx * v_rates.conversion_rate FREIGHT_VOLUME_G,
2644 tmp.freight_volume_trx FREIGHT_VOLUME_TRX,
2645 tmp.freight_weight_trx * w_rates.conversion_rate FREIGHT_WEIGHT_G,
2646 tmp.freight_weight_trx FREIGHT_WEIGHT_TRX,
2647 tmp.pick_up_stop_id PICK_UP_STOP_ID,
2648 tmp.trip_id TRIP_ID,
2649 tmp.volume_uom_code VOLUME_UOM_CODE,
2650 tmp.weight_uom_code WEIGHT_UOM_CODE,
2651 tmp.delivery_type DELIVERY_TYPE,
2652 tmp.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
2653 from isc_dbi_tmp_del_legs tmp,
2654 isc_dbi_fte_curr_rates curr,
2655 isc_dbi_fte_uom_rates w_rates,
2656 isc_dbi_fte_uom_rates v_rates
2657 where tmp.weight_uom_code = w_rates.from_uom_code(+)
2658 and w_rates.measure_code(+) = 'WT'
2659 and tmp.volume_uom_code = v_rates.from_uom_code(+)
2660 and v_rates.measure_code(+) = 'VOL'
2661 and tmp.trx_currency_code = curr.trx_currency_code(+)
2662 and tmp.wh_currency_code = curr.wh_currency_code(+)
2663 and tmp.conversion_date = curr.conversion_date(+)
2664 and tmp.conversion_type_code = curr.conversion_type_code(+)
2665 and tmp.batch_id = v_batch_id) new, isc_dbi_del_legs_f old
2666 where new.delivery_leg_id = old.delivery_leg_id(+)
2667 and (old.delivery_leg_id is null
2668 or new.carrier_id <> old.carrier_id
2669 or new.shipment_direction <> old.shipment_direction
2670 or new.mode_of_transport <> old.mode_of_transport
2671 or new.organization_id <> old.organization_id
2672 or new.service_level <> old.service_level
2673 or new.time_init_dept_date_id <> old.time_init_dept_date_id
2674 or new.delivery_id <> old.delivery_id
2675 or new.drop_off_stop_id <> old.drop_off_stop_id
2676 or nvl(new.freight_cost_f,-1) <> nvl(old.freight_cost_f,-1)
2677 or nvl(new.freight_cost_g,-1) <> nvl(old.freight_cost_g,-1)
2678 or nvl(new.freight_cost_g1,-1) <> nvl(old.freight_cost_g1,-1)
2679 or nvl(new.freight_volume_g,-1) <> nvl(old.freight_volume_g,-1)
2680 or nvl(new.freight_volume_trx,-1) <> nvl(old.freight_volume_trx,-1)
2681 or nvl(new.freight_weight_g,-1) <> nvl(old.freight_weight_g,-1)
2682 or nvl(new.freight_weight_trx,-1) <> nvl(old.freight_weight_trx,-1)
2683 or new.pick_up_stop_id <> old.pick_up_stop_id
2684 or new.trip_id <> old.trip_id
2685 or nvl(new.volume_uom_code,'na') <> nvl(old.volume_uom_code,'na')
2686 or nvl(new.weight_uom_code,'na') <> nvl(old.weight_uom_code,'na')
2687 or nvl(new.delivery_type,'na') <> nvl(old.delivery_type,'na')
2688 or nvl(new.parent_delivery_leg_id,-1) <> nvl(old.parent_delivery_leg_id,-1))) v
2689 ON (f.delivery_leg_id = v.delivery_leg_id)
2690 WHEN MATCHED THEN UPDATE SET
2691 f.carrier_id = v.carrier_id,
2692 f.shipment_direction = v.shipment_direction,
2693 f.mode_of_transport = v.mode_of_transport,
2694 f.organization_id = v.organization_id,
2695 f.service_level = v.service_level,
2696 f.time_init_dept_date_id = v.time_init_dept_date_id,
2697 f.delivery_id = v.delivery_id,
2698 f.drop_off_stop_id = v.drop_off_stop_id,
2699 f.freight_cost_f = v.freight_cost_f,
2700 f.freight_cost_g = v.freight_cost_g,
2701 f.freight_cost_g1 = v.freight_cost_g1,
2702 f.freight_volume_g = v.freight_volume_g,
2703 f.freight_volume_trx = v.freight_volume_trx,
2704 f.freight_weight_g = v.freight_weight_g,
2705 f.freight_weight_trx = v.freight_weight_trx,
2706 f.pick_up_stop_id = v.pick_up_stop_id,
2707 f.trip_id = v.trip_id,
2708 f.volume_uom_code = v.volume_uom_code,
2709 f.weight_uom_code = v.weight_uom_code,
2710 f.last_update_date = g_incre_start_date,
2711 f.delivery_type = v.delivery_type,
2712 f.parent_delivery_leg_id = v.parent_delivery_leg_id
2713 WHEN NOT MATCHED THEN INSERT(
2714 f.delivery_leg_id,
2715 f.carrier_id,
2716 f.shipment_direction,
2717 f.mode_of_transport,
2718 f.organization_id,
2719 f.service_level,
2720 f.time_init_dept_date_id,
2721 f.delivery_id,
2722 f.drop_off_stop_id,
2723 f.freight_cost_f,
2724 f.freight_cost_g,
2725 f.freight_cost_g1,
2726 f.freight_volume_g,
2727 f.freight_volume_trx,
2728 f.freight_weight_g,
2729 f.freight_weight_trx,
2730 f.pick_up_stop_id,
2731 f.trip_id,
2732 f.volume_uom_code,
2733 f.weight_uom_code,
2734 f.created_by,
2735 f.creation_date,
2736 f.last_updated_by,
2737 f.last_update_date,
2738 f.last_update_login,
2739 f.program_application_id,
2740 f.program_id,
2741 f.program_update_date,
2742 f.request_id,
2743 f.delivery_type,
2744 f.parent_delivery_leg_id)
2745 VALUES (
2746 v.delivery_leg_id,
2747 v.carrier_id,
2748 v.shipment_direction,
2749 v.mode_of_transport,
2750 v.organization_id,
2751 v.service_level,
2752 v.time_init_dept_date_id,
2753 v.delivery_id,
2754 v.drop_off_stop_id,
2755 v.freight_cost_f,
2756 v.freight_cost_g,
2757 v.freight_cost_g1,
2758 v.freight_volume_g,
2759 v.freight_volume_trx,
2760 v.freight_weight_g,
2761 v.freight_weight_trx,
2762 v.pick_up_stop_id,
2763 v.trip_id,
2764 v.volume_uom_code,
2765 v.weight_uom_code,
2766 -1,
2767 g_incre_start_date,
2768 -1,
2769 g_incre_start_date,
2770 -1,
2771 -1,
2772 -1,
2773 g_incre_start_date,
2774 -1,
2775 v.delivery_type,
2776 v.parent_delivery_leg_id);
2777
2778 MERGE INTO ISC_DBI_TRIP_STOPS_F f
2779 USING
2780 (select new.*
2781 from (select v.stop_id STOP_ID,
2782 v.carrier_id CARRIER_ID,
2783 v.mode_of_transport MODE_OF_TRANSPORT,
2784 v.service_level SERVICE_LEVEL,
2785 v.time_actl_arrl_date_id TIME_ACTL_ARRL_DATE_ID,
2786 v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
2787 v.time_pln_arrl_date_id TIME_PLN_ARRL_DATE_ID,
2788 v.actual_arrival_date ACTUAL_ARRIVAL_DATE,
2789 v.actual_departure_date ACTUAL_DEPARTURE_DATE,
2790 v.distance_to_next_stop_trx * d_rates.conversion_rate DISTANCE_TO_NEXT_STOP_G,
2791 v.distance_to_next_stop_trx DISTANCE_TO_NEXT_STOP_TRX,
2792 v.distance_uom_code DISTANCE_UOM_CODE,
2793 v.planned_arrival_date PLANNED_ARRIVAL_DATE,
2794 v.stop_rank STOP_RANK,
2795 v.stop_sequence_number STOP_SEQUENCE_NUMBER,
2796 itr.trip_freight_cost_g TRIP_FREIGHT_COST_G,
2797 itr.trip_freight_cost_g1 TRIP_FREIGHT_COST_G1,
2798 v.trip_id TRIP_ID,
2799 v.ultimate_stop_sequence_number ULTIMATE_STOP_SEQUENCE_NUMBER
2800 from isc_dbi_tmp_trip_stops v,
2801 (select trip_id,
2802 sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
2803 * decode(tmp.trx_currency_code,g_global_currency,1,curr.trx_wh_rate * curr.wh_prim_rate)) TRIP_FREIGHT_COST_G,
2804 sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
2805 * decode(tmp.trx_currency_code,g_sec_global_currency,1,curr.trx_wh_rate*curr.wh_sec_rate)) TRIP_FREIGHT_COST_G1
2806 from isc_dbi_tmp_del_legs tmp,
2807 isc_dbi_fte_curr_rates curr
2808 where tmp.trx_currency_code = curr.trx_currency_code(+)
2809 and tmp.wh_currency_code = curr.wh_currency_code(+)
2810 and tmp.conversion_date = curr.conversion_date(+)
2811 and tmp.conversion_type_code = curr.conversion_type_code(+)
2812 group by trip_id) itr,
2813 isc_dbi_fte_uom_rates d_rates
2814 where v.trip_id = itr.trip_id
2815 and v.distance_uom_code = d_rates.from_uom_code(+)
2816 and d_rates.measure_code(+) = 'DIS'
2817 and v.batch_id = v_batch_id) new, isc_dbi_trip_stops_f old
2818 where new.stop_id = old.stop_id(+)
2819 and (old.stop_id is null
2820 or new.carrier_id <> old.carrier_id
2821 or new.mode_of_transport <> old.mode_of_transport
2822 or new.service_level <> old.service_level
2823 or new.time_init_dept_date_id <> old.time_init_dept_date_id
2824 or nvl(new.actual_arrival_date, l_date) <> nvl(old.actual_arrival_date, l_date)
2825 or nvl(new.actual_departure_date, l_date) <> nvl(old.actual_departure_date, l_date)
2826 or nvl(new.distance_to_next_stop_g, -1) <> nvl(old.distance_to_next_stop_g, -1)
2827 or nvl(new.distance_to_next_stop_trx, -1) <> nvl(old.distance_to_next_stop_trx, -1)
2828 or nvl(new.distance_uom_code, 'na') <> nvl(old.distance_uom_code, 'na')
2829 or new.planned_arrival_date <> old.planned_arrival_date
2830 or new.stop_rank <> old.stop_rank
2831 or new.stop_sequence_number <> old.stop_sequence_number
2832 or nvl(new.trip_freight_cost_g, -1) <> nvl(old.trip_freight_cost_g, -1)
2833 or nvl(new.trip_freight_cost_g1, -1) <> nvl(old.trip_freight_cost_g1, -1)
2834 or new.trip_id <> old.trip_id
2835 or new.ultimate_stop_sequence_number <> old.ultimate_stop_sequence_number)) v
2836 ON (f.stop_id = v.stop_id)
2837 WHEN MATCHED THEN UPDATE SET
2838 f.carrier_id = v.carrier_id,
2839 f.mode_of_transport = v.mode_of_transport,
2840 f.service_level = v.service_level,
2841 f.time_actl_arrl_date_id = v.time_actl_arrl_date_id,
2842 f.time_init_dept_date_id = v.time_init_dept_date_id,
2843 f.time_pln_arrl_date_id = v.time_pln_arrl_date_id,
2844 f.actual_arrival_date = v.actual_arrival_date,
2845 f.actual_departure_date = v.actual_departure_date,
2846 f.distance_to_next_stop_g = v.distance_to_next_stop_g,
2847 f.distance_to_next_stop_trx = v.distance_to_next_stop_trx,
2848 f.distance_uom_code = v.distance_uom_code,
2849 f.planned_arrival_date = v.planned_arrival_date,
2850 f.stop_rank = v.stop_rank,
2851 f.stop_sequence_number = v.stop_sequence_number,
2852 f.trip_freight_cost_g = v.trip_freight_cost_g,
2853 f.trip_freight_cost_g1 = v.trip_freight_cost_g1,
2854 f.trip_id = v.trip_id,
2855 f.ultimate_stop_sequence_number = v.ultimate_stop_sequence_number,
2856 f.last_update_date = g_incre_start_date
2857 WHEN NOT MATCHED THEN INSERT(
2858 f.stop_id,
2859 f.carrier_id,
2860 f.mode_of_transport,
2861 f.service_level,
2862 f.time_actl_arrl_date_id,
2863 f.time_init_dept_date_id,
2864 f.time_pln_arrl_date_id,
2865 f.actual_arrival_date,
2866 f.actual_departure_date,
2867 f.distance_to_next_stop_g,
2868 f.distance_to_next_stop_trx,
2869 f.distance_uom_code,
2870 f.planned_arrival_date,
2871 f.stop_rank,
2872 f.stop_sequence_number,
2873 f.trip_freight_cost_g,
2874 f.trip_freight_cost_g1,
2875 f.trip_id,
2876 f.ultimate_stop_sequence_number,
2877 f.created_by,
2878 f.creation_date,
2879 f.last_updated_by,
2880 f.last_update_date,
2881 f.last_update_login,
2882 f.program_application_id,
2883 f.program_id,
2884 f.program_update_date,
2885 f.request_id)
2886 VALUES (
2887 v.stop_id,
2888 v.carrier_id,
2889 v.mode_of_transport,
2890 v.service_level,
2891 v.time_actl_arrl_date_id,
2892 v.time_init_dept_date_id,
2893 v.time_pln_arrl_date_id,
2894 v.actual_arrival_date,
2895 v.actual_departure_date,
2896 v.distance_to_next_stop_g,
2897 v.distance_to_next_stop_trx,
2898 v.distance_uom_code,
2899 v.planned_arrival_date,
2900 v.stop_rank,
2901 v.stop_sequence_number,
2902 v.trip_freight_cost_g,
2903 v.trip_freight_cost_g1,
2904 v.trip_id,
2905 v.ultimate_stop_sequence_number,
2906 -1,
2907 g_incre_start_date,
2908 -1,
2909 g_incre_start_date,
2910 -1,
2911 -1,
2912 -1,
2913 g_incre_start_date,
2914 -1);
2915
2916 l_count := sql%rowcount;
2917 l_total := l_total + l_count;
2918 COMMIT;
2919 FII_UTIL.Stop_Timer;
2920 FII_UTIL.Print_Timer('Merged '||l_count|| ' rows in ');
2921
2922 END LOOP;
2923
2924 RETURN(l_total);
2925
2926 EXCEPTION
2927 WHEN OTHERS THEN
2928 g_errbuf := 'Error in Function MERGE_LEG_STOP_FACT : '||sqlerrm;
2929 g_retcode := sqlcode;
2930 RETURN(-1);
2931
2932 END merge_leg_stop_fact;
2933
2934 FUNCTION MERGE_INVOICE_FACT(p_batch number) RETURN NUMBER IS
2935
2936 l_count NUMBER;
2937 l_total NUMBER;
2938 l_max_batch NUMBER;
2939 l_date DATE;
2940
2941 BEGIN
2942
2943 l_total := 0;
2944 l_date := to_date('01/01/0001','DD/MM/YYYY');
2945
2946 FOR v_batch_id IN 1..p_batch
2947 LOOP
2948 FII_UTIL.Start_Timer;
2949 BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2950
2951 l_count := 0;
2952
2953 MERGE INTO ISC_DBI_FTE_INVOICES_F f
2954 USING
2955 (select new.*
2956 from (select tmp.invoice_header_id INVOICE_HEADER_ID,
2957 tmp.carrier_id CARRIER_ID,
2958 tmp.mode_of_transport MODE_OF_TRANSPORT,
2959 tmp.org_id ORG_ID,
2960 tmp.service_level SERVICE_LEVEL,
2961 tmp.supplier_id SUPPLIER_ID,
2962 tmp.approved_amt_trx * curr.trx_wh_rate APPROVED_AMT_F,
2963 tmp.approved_amt_trx
2964 * decode(tmp.trx_currency_code, g_global_currency, 1,
2965 curr.trx_wh_rate * curr.wh_prim_rate) APPROVED_AMT_G,
2966 tmp.approved_amt_trx
2967 * decode(tmp.trx_currency_code, g_sec_global_currency, 1,
2968 curr.trx_wh_rate * curr.wh_sec_rate) APPROVED_AMT_G1,
2969 tmp.bill_amt_trx * curr.trx_wh_rate BILL_AMT_F,
2970 tmp.bill_amt_trx
2971 * decode(tmp.trx_currency_code, g_global_currency, 1,
2972 curr.trx_wh_rate * curr.wh_prim_rate) BILL_AMT_G,
2973 tmp.bill_amt_trx
2974 * decode(tmp.trx_currency_code, g_sec_global_currency, 1,
2975 curr.trx_wh_rate * curr.wh_sec_rate) BILL_AMT_G1,
2976 tmp.bill_number BILL_NUMBER,
2977 tmp.bill_status BILL_STATUS,
2978 tmp.bill_type BILL_TYPE,
2979 tmp.bol BOL,
2980 tmp.delivery_leg_id DELIVERY_LEG_ID,
2981 tmp.trip_id TRIP_ID
2982 from isc_dbi_tmp_fte_invoices tmp, isc_dbi_fte_curr_rates curr
2983 where tmp.trx_currency_code = curr.trx_currency_code
2984 and tmp.wh_currency_code = curr.wh_currency_code
2985 and tmp.conversion_date = curr.conversion_date
2986 and tmp.conversion_type_code = curr.conversion_type_code
2987 and tmp.batch_id = v_batch_id) new,
2988 isc_dbi_fte_invoices_f old
2989 where new.invoice_header_id = old.invoice_header_id(+)
2990 and (old.invoice_header_id is null
2991 or new.carrier_id <> old.carrier_id
2992 or new.mode_of_transport <> old.mode_of_transport
2993 or new.org_id <> old.org_id
2994 or new.service_level <> old.service_level
2995 or nvl(new.supplier_id, -1) <> nvl(old.supplier_id, -1)
2996 or nvl(new.approved_amt_f, -1) <> nvl(old.approved_amt_f, -1)
2997 or nvl(new.approved_amt_g, -1) <> nvl(old.approved_amt_g, -1)
2998 or nvl(new.approved_amt_g1, -1) <> nvl(old.approved_amt_g1, -1)
2999 or nvl(new.bill_amt_f, -1) <> nvl(old.bill_amt_f, -1)
3000 or nvl(new.bill_amt_g, -1) <> nvl(old.bill_amt_g, -1)
3001 or nvl(new.bill_amt_g1, -1) <> nvl(old.bill_amt_g1, -1)
3002 or new.bill_number <> old.bill_number
3003 or new.bill_status <> old.bill_status
3004 or nvl(new.bill_type, 'na') <> nvl(old.bill_type, 'na')
3005 or nvl(new.bol, 'na') <> nvl(old.bol,'na')
3006 or nvl(new.delivery_leg_id, -1) <> nvl(old.delivery_leg_id, -1)
3007 or new.trip_id <> old.trip_id)) v
3008 ON (f.invoice_header_id = v.invoice_header_id)
3009 WHEN MATCHED THEN UPDATE SET
3010 f.carrier_id = v.carrier_id,
3011 f.mode_of_transport = v.mode_of_transport,
3012 f.org_id = v.org_id,
3013 f.service_level = v.service_level,
3014 f.supplier_id = v.supplier_id,
3015 f.approved_amt_f = v.approved_amt_f,
3016 f.approved_amt_g = v.approved_amt_g,
3017 f.approved_amt_g1 = v.approved_amt_g1,
3018 f.bill_amt_f = v.bill_amt_f,
3019 f.bill_amt_g = v.bill_amt_g,
3020 f.bill_amt_g1 = v.bill_amt_g1,
3021 f.bill_number = v.bill_number,
3022 f.bill_status = v.bill_status,
3023 f.bill_type = v.bill_type,
3024 f.bol = v.bol,
3025 f.delivery_leg_id = v.delivery_leg_id,
3026 f.trip_id = v.trip_id,
3027 f.last_update_date = g_incre_start_date
3028 WHEN NOT MATCHED THEN INSERT(
3029 f.invoice_header_id,
3030 f.carrier_id,
3031 f.mode_of_transport,
3032 f.org_id,
3033 f.service_level,
3034 f.supplier_id,
3035 f.approved_amt_f,
3036 f.approved_amt_g,
3037 f.approved_amt_g1,
3038 f.bill_amt_f,
3039 f.bill_amt_g,
3040 f.bill_amt_g1,
3041 f.bill_number,
3042 f.bill_status,
3043 f.bill_type,
3044 f.bol,
3045 f.delivery_leg_id,
3046 f.trip_id,
3047 f.created_by,
3048 f.creation_date,
3049 f.last_updated_by,
3050 f.last_update_date,
3051 f.last_update_login,
3052 f.program_application_id,
3053 f.program_id,
3054 f.program_update_date,
3055 f.request_id)
3056 VALUES (
3057 v.invoice_header_id,
3058 v.carrier_id,
3059 v.mode_of_transport,
3060 v.org_id,
3061 v.service_level,
3062 v.supplier_id,
3063 v.approved_amt_f,
3064 v.approved_amt_g,
3065 v.approved_amt_g1,
3066 v.bill_amt_f,
3067 v.bill_amt_g,
3068 v.bill_amt_g1,
3069 v.bill_number,
3070 v.bill_status,
3071 v.bill_type,
3072 v.bol,
3073 v.delivery_leg_id,
3074 v.trip_id,
3075 -1,
3076 g_incre_start_date,
3077 -1,
3078 g_incre_start_date,
3079 -1,
3080 -1,
3081 -1,
3082 g_incre_start_date,
3083 -1);
3084
3085 l_count := sql%rowcount;
3086 l_total := l_total + l_count;
3087 COMMIT;
3088 FII_UTIL.Stop_Timer;
3089 FII_UTIL.Print_Timer('Merged '||l_count|| ' rows in ');
3090
3091 END LOOP;
3092
3093 RETURN(l_total);
3094
3095 EXCEPTION
3096 WHEN OTHERS THEN
3097 g_errbuf := 'Error in Function MERGE_INVOICE_FACT : '||sqlerrm;
3098 g_retcode := sqlcode;
3099 RETURN(-1);
3100
3101 END merge_invoice_fact;
3102
3103 FUNCTION WRAPUP RETURN NUMBER IS
3104
3105 BEGIN
3106
3107 -- ------------------------
3108 -- Truncate temp tables
3109 -- ------------------------
3110
3111 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp tables');
3112 FII_UTIL.Start_Timer;
3113
3114 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WDD_LOG';
3115 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WTS_LOG';
3116 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FIH_LOG';
3117
3118 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_DETAILS';
3119 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_LEGS';
3120 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_TRIP_STOPS';
3121 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FTE_INVOICES';
3122 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_CURR_RATES';
3123 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_UOM_RATES';
3124
3125 FII_UTIL.Stop_Timer;
3126 FII_UTIL.Print_Timer('Truncated the temp tables in');
3127 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3128
3129 -- ----------------------------------------------
3130 -- No exception raised so far. Successful. Call
3131 -- Wrapup to commit and insert messages into logs
3132 -- ----------------------------------------------
3133
3134 BIS_COLLECTION_UTILITIES.WRAPUP(
3135 TRUE,
3136 g_row_count,
3137 NULL,
3138 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3139 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3140 );
3141
3142 RETURN (1);
3143
3144 EXCEPTION
3145 WHEN OTHERS THEN
3146 g_errbuf := 'Error in Function WRAPUP : '||sqlerrm;
3147 g_retcode := sqlcode;
3148 RETURN(-1);
3149 END wrapup;
3150
3151 ---------------------
3152 -- Public Procedures
3153 ---------------------
3154
3155 Procedure load_facts(errbuf IN OUT NOCOPY VARCHAR2,
3156 retcode IN OUT NOCOPY VARCHAR2) IS
3157
3158 l_failure EXCEPTION;
3159 l_start DATE;
3160 l_end DATE;
3161 l_period_from DATE;
3162 l_period_to DATE;
3163 l_row_count NUMBER;
3164
3165 BEGIN
3166
3167 errbuf := NULL;
3168 retcode := '0';
3169 g_load_mode := 'INITIAL';
3170
3171 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3172 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3173
3174 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_WSH_FTE_F')) THEN
3175 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3176 return;
3177 END IF;
3178
3179 IF (CHECK_SETUP = -1)
3180 THEN RAISE l_failure;
3181 END IF;
3182
3183 IF (GET_REPORTING_UOM = -1)
3184 THEN RAISE l_failure;
3185 END IF;
3186
3187 IF (SET_WMS_PTS_GSD = -1)
3188 THEN RAISE l_failure;
3189 END IF;
3190
3191 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date := g_global_start_date;
3192 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date := sysdate;
3193
3194 BIS_COLLECTION_UTILITIES.put_line( 'The collection date range is from '||
3195 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
3196 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
3197 BIS_COLLECTION_UTILITIES.put_line(' ');
3198
3199 EXECUTE IMMEDIATE 'alter session set hash_area_size=104857600';
3200 EXECUTE IMMEDIATE 'alter session set sort_area_size=104857600';
3201
3202 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_WDD_CHANGE_LOG';
3203 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_WTS_CHANGE_LOG';
3204 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FIH_CHANGE_LOG';
3205
3206 -- -----------------------------
3207 -- Load Data into staging tables
3208 -- -----------------------------
3209
3210 l_row_count := IDENTIFY_CHANGE_INIT;
3211
3212 IF (l_row_count = -1)
3213 THEN RAISE l_failure;
3214 ELSIF (l_row_count = 0) THEN
3215
3216 -- Fix bug 4150188
3217 BIS_COLLECTION_UTILITIES.put_line(' ');
3218 BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3219 FII_UTIL.Start_Timer;
3220
3221 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3222 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_LEGS_F';
3223 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TRIP_STOPS_F';
3224 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_INVOICES_F';
3225 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_PARAMETERS';
3226
3227 FII_UTIL.Stop_Timer;
3228 FII_UTIL.Print_Timer('Truncated the fact tables in');
3229 g_row_count := 0;
3230
3231 ELSE
3232
3233 -- --------------
3234 -- Analyze tables
3235 -- --------------
3236
3237 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3238 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3239 FII_UTIL.Start_Timer;
3240
3241 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3242 TABNAME => 'ISC_DBI_TMP_DEL_DETAILS');
3243 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3244 TABNAME => 'ISC_DBI_TMP_DEL_LEGS');
3245 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3246 TABNAME => 'ISC_DBI_TMP_TRIP_STOPS');
3247 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3248 TABNAME => 'ISC_DBI_TMP_FTE_INVOICES');
3249 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3250 TABNAME => 'ISC_DBI_FTE_CURR_RATES');
3251 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3252 TABNAME => 'ISC_DBI_FTE_UOM_RATES');
3253
3254 FII_UTIL.Stop_Timer;
3255 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
3256
3257 IF (DANGLING_CHECK_INIT = -1) THEN
3258 RAISE l_failure;
3259 END IF;
3260
3261 -- ----------------------------------------
3262 -- Truncate base summaries for initial load
3263 -- ----------------------------------------
3264
3265 BIS_COLLECTION_UTILITIES.put_line(' ');
3266 BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3267 FII_UTIL.Start_Timer;
3268
3269 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3270 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_LEGS_F';
3271 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TRIP_STOPS_F';
3272 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_INVOICES_F';
3273 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_PARAMETERS';
3274
3275 FII_UTIL.Stop_Timer;
3276 FII_UTIL.Print_Timer('Truncated the fact tables in');
3277
3278 -- --------------------------------------------
3279 -- Update Parameter Table
3280 -- --------------------------------------------
3281
3282 IF (UPDATE_PARAMETER_TABLE = -1) THEN
3283 RAISE l_failure;
3284 END IF;
3285
3286 -- -------------------------------
3287 -- Insert data into base summaries
3288 -- -------------------------------
3289
3290 g_row_count := Insert_fact;
3291
3292 IF (g_row_count = -1) THEN
3293 RAISE l_failure;
3294 END IF;
3295
3296 END IF;
3297
3298 IF (WRAPUP = -1) THEN
3299 RAISE l_failure;
3300 END IF;
3301
3302 retcode := g_retcode;
3303 errbuf := g_errbuf;
3304
3305 EXCEPTION
3306
3307 WHEN L_FAILURE THEN
3308 ROLLBACK;
3309 BIS_COLLECTION_UTILITIES.put_line(' ');
3310 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3311 retcode := -1;
3312 errbuf := g_errbuf;
3313
3314 BIS_COLLECTION_UTILITIES.WRAPUP(
3315 FALSE,
3316 g_row_count,
3317 g_errbuf,
3318 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3319 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3320 );
3321
3322 WHEN OTHERS THEN
3323 ROLLBACK;
3324 g_errbuf := sqlerrm ||' - '||sqlcode;
3325 BIS_COLLECTION_UTILITIES.put_line(' ');
3326 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3327 retcode := -1;
3328 errbuf := g_errbuf;
3329
3330 BIS_COLLECTION_UTILITIES.WRAPUP(
3331 FALSE,
3332 g_row_count,
3333 g_errbuf,
3334 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3335 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3336 );
3337
3338 END load_facts;
3339
3340 Procedure update_detail_fact(errbuf IN OUT NOCOPY VARCHAR2,
3341 retcode IN OUT NOCOPY VARCHAR2) IS
3342
3343 l_failure EXCEPTION;
3344 l_row_count NUMBER;
3345
3346 BEGIN
3347 errbuf := NULL;
3348 retcode := '0';
3349 g_load_mode := 'INCREMENTAL';
3350 l_row_count := 0;
3351
3352 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3353 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3354
3355 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_DEL_DETAILS_F_INC')) THEN
3356 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3357 return;
3358 END IF;
3359
3360 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date := null;
3361 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date := sysdate;
3362
3363 IF (CHECK_SETUP = -1)
3364 THEN RAISE l_failure;
3365 END IF;
3366
3367 IF (SET_WMS_PTS_GSD = -1)
3368 THEN RAISE l_failure;
3369 END IF;
3370
3371 -- --------------------------------------------
3372 -- Identify Change for Delivery Details
3373 -- --------------------------------------------
3374
3375 BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3376
3377 g_incre_start_date := sysdate;
3378 BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3379
3380 l_row_count := IDENTIFY_CHANGE_DETAIL_ICRL;
3381
3382 IF (l_row_count = -1) THEN
3383 RAISE l_failure;
3384 ELSIF (l_row_count = 0) THEN
3385 g_row_count := 0;
3386 ELSE
3387
3388 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3389 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3390 FII_UTIL.Start_Timer;
3391
3392 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3393 TABNAME => 'ISC_DBI_TMP_DEL_DETAILS');
3394
3395 FII_UTIL.Stop_Timer;
3396 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
3397
3398 -- --------------------------------------------
3399 -- Dangling Checks
3400 -- --------------------------------------------
3401
3402 IF (DANGLING_CHECK_DETAIL_ICRL = -1) THEN
3403 RAISE l_failure;
3404 END IF;
3405
3406 -- --------------------------------------------
3407 -- Update Parameter Table
3408 -- --------------------------------------------
3409
3410 IF (UPDATE_PARAMETER_TABLE = -1) THEN
3411 RAISE l_failure;
3412 END IF;
3413
3414 -- --------------------------------------------
3415 -- Merge data into Sum2 table
3416 -- --------------------------------------------
3417
3418 BIS_COLLECTION_UTILITIES.put_line(' ');
3419 BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3420
3421 g_row_count := Merge_Detail_Fact(ceil(l_row_count/g_batch_size));
3422
3423 BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact table');
3424
3425 IF (g_row_count = -1) THEN
3426 RAISE l_failure;
3427 END IF;
3428
3429 END IF;
3430
3431 -- -------------------------------------------------
3432 -- Delete rows from change log tables base on rowid
3433 -- -------------------------------------------------
3434
3435 BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3436 FII_UTIL.Start_Timer;
3437
3438 DELETE FROM ISC_DBI_WDD_CHANGE_LOG
3439 WHERE rowid IN (select log_rowid from isc_dbi_tmp_wdd_log);
3440 -- AND last_update_date < g_incre_start_date;
3441
3442 FII_UTIL.Stop_Timer;
3443 FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_WDD_CHANGE_LOG in');
3444 COMMIT;
3445
3446 IF (WRAPUP = -1) THEN
3447 RAISE l_failure;
3448 END IF;
3449
3450 retcode := g_retcode;
3451 errbuf := g_errbuf;
3452
3453 EXCEPTION
3454
3455 WHEN L_FAILURE THEN
3456 ROLLBACK;
3457 BIS_COLLECTION_UTILITIES.put_line(' ');
3458 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3459 retcode := -1;
3460 errbuf := g_errbuf;
3461
3462 BIS_COLLECTION_UTILITIES.WRAPUP(
3463 FALSE,
3464 g_row_count,
3465 g_errbuf,
3466 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3467 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3468 );
3469
3470 WHEN OTHERS THEN
3471 ROLLBACK;
3472 g_errbuf := sqlerrm ||' - '||sqlcode;
3473 BIS_COLLECTION_UTILITIES.put_line(' ');
3474 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3475 retcode := -1;
3476 errbuf := g_errbuf;
3477
3478 BIS_COLLECTION_UTILITIES.WRAPUP(
3479 FALSE,
3480 g_row_count,
3481 g_errbuf,
3482 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3483 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3484 );
3485
3486 END update_detail_fact;
3487
3488 Procedure update_leg_stop_fact(errbuf IN OUT NOCOPY VARCHAR2,
3489 retcode IN OUT NOCOPY VARCHAR2) IS
3490
3491 l_failure EXCEPTION;
3492 l_start DATE;
3493 l_end DATE;
3494 l_period_from DATE;
3495 l_period_to DATE;
3496 l_row_count NUMBER;
3497
3498 BEGIN
3499 errbuf := NULL;
3500 retcode := '0';
3501 g_load_mode := 'INCREMENTAL';
3502 l_row_count := 0;
3503
3504 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3505 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3506
3507 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_LEG_STOP_F_INC')) THEN
3508 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3509 return;
3510 END IF;
3511
3512 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date := null;
3513 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date := sysdate;
3514
3515 IF (CHECK_SETUP = -1)
3516 THEN RAISE l_failure;
3517 END IF;
3518
3519 IF (GET_REPORTING_UOM = -1)
3520 THEN RAISE l_failure;
3521 END IF;
3522
3523 -- ------------------------------------------------
3524 -- Identify Change for Delivery Legs and Trip Stops
3525 -- ------------------------------------------------
3526
3527 BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3528
3529 g_incre_start_date := sysdate;
3530 BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3531 l_row_count := IDENTIFY_CHANGE_STOP_LEG_ICRL;
3532
3533 IF (l_row_count = -1) THEN
3534 RAISE l_failure;
3535 ELSIF (l_row_count = 0) THEN
3536 g_row_count := 0;
3537 ELSE
3538 -- --------------
3539 -- Analyze tables
3540 -- --------------
3541
3542 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3543 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3544 FII_UTIL.Start_Timer;
3545
3546 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3547 TABNAME => 'ISC_DBI_TMP_DEL_LEGS');
3548 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3549 TABNAME => 'ISC_DBI_TMP_TRIP_STOPS');
3550 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3551 TABNAME => 'ISC_DBI_FTE_CURR_RATES');
3552 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3553 TABNAME => 'ISC_DBI_FTE_UOM_RATES');
3554
3555 FII_UTIL.Stop_Timer;
3556 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
3557
3558 -- ---------------------
3559 -- Dangling Checking
3560 -- ---------------------
3561
3562 IF (DANGLING_CHECK_LEG_STOP_ICRL = -1) THEN
3563 RAISE l_failure;
3564 END IF;
3565
3566 -- --------------------------------------------
3567 -- Update Parameter Table
3568 -- --------------------------------------------
3569
3570 IF (UPDATE_PARAMETER_TABLE = -1) THEN
3571 RAISE l_failure;
3572 END IF;
3573
3574 -- --------------------------------------------
3575 -- Merge data into Sum2 table
3576 -- --------------------------------------------
3577
3578 BIS_COLLECTION_UTILITIES.put_line(' ');
3579 BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3580
3581 g_row_count := Merge_Leg_Stop_Fact(ceil(l_row_count/g_batch_size));
3582
3583 BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3584
3585 IF (g_row_count = -1) THEN
3586 RAISE l_failure;
3587 END IF;
3588
3589 END IF;
3590
3591 -- -------------------------------------------------
3592 -- Delete rows from change log tables base on rowid
3593 -- -------------------------------------------------
3594
3595 BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3596 FII_UTIL.Start_Timer;
3597
3598 DELETE FROM ISC_DBI_WTS_CHANGE_LOG
3599 WHERE rowid IN (select log_rowid from isc_dbi_tmp_wts_log);
3600 -- AND last_update_date < g_incre_start_date;
3601
3602 FII_UTIL.Stop_Timer;
3603 FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_WTS_CHANGE_LOG in');
3604 COMMIT;
3605
3606 IF (WRAPUP = -1) THEN
3607 RAISE l_failure;
3608 END IF;
3609
3610 retcode := g_retcode;
3611 errbuf := g_errbuf;
3612
3613 EXCEPTION
3614
3615 WHEN L_FAILURE THEN
3616 ROLLBACK;
3617 BIS_COLLECTION_UTILITIES.put_line(' ');
3618 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3619 retcode := -1;
3620 errbuf := g_errbuf;
3621
3622 BIS_COLLECTION_UTILITIES.WRAPUP(
3623 FALSE,
3624 g_row_count,
3625 g_errbuf,
3626 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3627 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3628 );
3629
3630 WHEN OTHERS THEN
3631 ROLLBACK;
3632 g_errbuf := sqlerrm ||' - '||sqlcode;
3633 BIS_COLLECTION_UTILITIES.put_line(' ');
3634 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3635 retcode := -1;
3636 errbuf := g_errbuf;
3637
3638 BIS_COLLECTION_UTILITIES.WRAPUP(
3639 FALSE,
3640 g_row_count,
3641 g_errbuf,
3642 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3643 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3644 );
3645
3646 END update_leg_stop_fact;
3647
3648 Procedure update_invoice_fact(errbuf IN OUT NOCOPY VARCHAR2,
3649 retcode IN OUT NOCOPY VARCHAR2) IS
3650
3651 l_failure EXCEPTION;
3652 l_row_count NUMBER;
3653
3654 BEGIN
3655 errbuf := NULL;
3656 retcode := '0';
3657 g_load_mode := 'INCREMENTAL';
3658 l_row_count := 0;
3659
3660 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3661 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3662
3663 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_FTE_INVOICES_F_INC')) THEN
3664 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3665 return;
3666 END IF;
3667
3668 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date := null;
3669 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date := sysdate;
3670
3671 IF (CHECK_SETUP = -1)
3672 THEN RAISE l_failure;
3673 END IF;
3674
3675 -- --------------------------------------------
3676 -- Identify Change for Invoice Headers
3677 -- --------------------------------------------
3678
3679 BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3680
3681 g_incre_start_date := sysdate;
3682 BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3683 l_row_count := IDENTIFY_CHANGE_INVOICE_ICRL;
3684
3685 IF (l_row_count = -1) THEN
3686 RAISE l_failure;
3687 ELSIF (l_row_count = 0) THEN
3688 g_row_count := 0;
3689 ELSE
3690 -- --------------
3691 -- Analyze tables
3692 -- --------------
3693
3694 BIS_COLLECTION_UTILITIES.Put_Line(' ');
3695 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3696 FII_UTIL.Start_Timer;
3697
3698 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3699 TABNAME => 'ISC_DBI_TMP_FTE_INVOICES');
3700 FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3701 TABNAME => 'ISC_DBI_FTE_CURR_RATES');
3702
3703 FII_UTIL.Stop_Timer;
3704 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
3705
3706 -- ---------------------
3707 -- Dangling Checking
3708 -- ---------------------
3709
3710 IF (DANGLING_CHECK_INVOICE_ICRL = -1) THEN
3711 RAISE l_failure;
3712 END IF;
3713
3714 -- --------------------------------------------
3715 -- Update Parameter Table
3716 -- --------------------------------------------
3717
3718 IF (UPDATE_PARAMETER_TABLE = -1) THEN
3719 RAISE l_failure;
3720 END IF;
3721
3722 -- --------------------------------------------
3723 -- Merge data into Base Summary
3724 -- --------------------------------------------
3725
3726 BIS_COLLECTION_UTILITIES.put_line(' ');
3727 BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3728
3729 g_row_count := Merge_Invoice_Fact(ceil(l_row_count/g_batch_size));
3730
3731 BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3732
3733 IF (g_row_count = -1) THEN
3734 RAISE l_failure;
3735 END IF;
3736
3737 END IF;
3738
3739 -- -------------------------------------------------
3740 -- Delete rows from change log tables base on rowid
3741 -- -------------------------------------------------
3742
3743 BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3744
3745 FII_UTIL.Start_Timer;
3746
3747 DELETE FROM ISC_DBI_FIH_CHANGE_LOG
3748 WHERE rowid IN (select log_rowid from isc_dbi_tmp_fih_log);
3749 -- AND last_update_date < g_incre_start_date;
3750
3751 FII_UTIL.Stop_Timer;
3752 FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_FIH_CHANGE_LOG in');
3753 COMMIT;
3754
3755 IF (WRAPUP = -1) THEN
3756 RAISE l_failure;
3757 END IF;
3758
3759 retcode := g_retcode;
3760 errbuf := g_errbuf;
3761
3762 EXCEPTION
3763
3764 WHEN L_FAILURE THEN
3765 ROLLBACK;
3766 BIS_COLLECTION_UTILITIES.put_line(' ');
3767 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3768 retcode := -1;
3769 errbuf := g_errbuf;
3770
3771 BIS_COLLECTION_UTILITIES.WRAPUP(
3772 FALSE,
3773 g_row_count,
3774 g_errbuf,
3775 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3776 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3777 );
3778
3779 WHEN OTHERS THEN
3780 ROLLBACK;
3781 g_errbuf := sqlerrm ||' - '||sqlcode;
3782 BIS_COLLECTION_UTILITIES.put_line(' ');
3783 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3784 retcode := -1;
3785 errbuf := g_errbuf;
3786
3787 BIS_COLLECTION_UTILITIES.WRAPUP(
3788 FALSE,
3789 g_row_count,
3790 g_errbuf,
3791 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3792 ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date
3793 );
3794
3795 END update_invoice_fact;
3796
3797 END ISC_DBI_WSH_FTE_OBJECTS_C;