[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION update_parameter_table RETURN NUMBER IS
l_old_arr_window NUMBER;
SELECT on_time_window INTO l_old_arr_window FROM isc_dbi_fte_parameters;
BIS_COLLECTION_UTILITIES.Put_Line('Inserting data into isc_dbi_fte_parameters.');
INSERT INTO isc_dbi_fte_parameters (ON_TIME_WINDOW, LAST_UPDATE_DATE) VALUES (g_new_arr_window, sysdate);
BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
g_errbuf := 'Error in function UPDATE_PARAMETER_TABLE : '||sqlerrm;
END update_parameter_table;
INSERT /*+ APPEND PARALLEL(F) */ INTO isc_dbi_tmp_del_details F (
DELIVERY_DETAIL_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SHIPMENT_DIRECTION,
SUBINVENTORY_CODE,
TIME_IP_DATE_ID,
TIME_PR_DATE_ID,
DELIVERY_ID,
INITIAL_PICKUP_DATE,
MOVE_ORDER_LINE_ID,
PICK_RELEASED_DATE,
RELEASED_STATUS,
REQUESTED_QUANTITY,
REQUESTED_QUANTITY_UOM,
SHIPPED_QUANTITY,
WMS_ENABLED_FLAG)
SELECT /*+ USE_HASH(wnd,wda,wdd,mol,mmt,mp) PARALLEL(wnd) PARALLEL(wda) PARALLEL(wdd) PARALLEL(mol) PARALLEL(mmt) PARALLEL(mp) */
wdd.delivery_detail_id DELIVERY_DETAIL_ID,
wdd.inventory_item_id INVENTORY_ITEM_ID,
nvl(wnd.organization_id, wdd.organization_id) ORGANIZATION_ID,
nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
mmt.subinventory_code SUBINVENTORY_CODE,
trunc(wnd.initial_pickup_date) TIME_IP_DATE_ID,
trunc(mol.creation_date) TIME_PR_DATE_ID,
wnd.delivery_id DELIVERY_ID,
wnd.initial_pickup_date INITIAL_PICKUP_DATE,
wdd.move_order_line_id MOVE_ORDER_LINE_ID,
mol.creation_date PICK_RELEASED_DATE,
wdd.released_status RELEASED_STATUS,
wdd.requested_quantity REQUESTED_QUANTITY,
wdd.requested_quantity_uom REQUESTED_QUANTITY_UOM,
wdd.shipped_quantity SHIPPED_QUANTITY,
mp.wms_enabled_flag WMS_ENABLED_FLAG
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
mtl_txn_request_lines mol,
mtl_material_transactions mmt,
mtl_parameters mp
WHERE wdd.released_status in ('S','Y','C','L','P')
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wda.type,'S') in ('S','O')
AND nvl(wdd.container_flag,'N') = 'N'
AND wda.delivery_id = wnd.delivery_id (+)
AND wdd.move_order_line_id = mol.line_id (+)
AND wdd.transaction_id = mmt.transaction_id (+)
AND nvl(mmt.transaction_source_type_id,2) IN (2,8)
AND nvl(mmt.transaction_action_id,28) = 28
AND nvl(mmt.transaction_quantity,-1) < 0
AND wdd.organization_id = mp.organization_id
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;
INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_trip_stops tmp (
STOP_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
SERVICE_LEVEL,
TIME_ACTL_ARRL_DATE_ID,
TIME_INIT_DEPT_DATE_ID,
TIME_PLN_ARRL_DATE_ID,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
DISTANCE_TO_NEXT_STOP_TRX,
DISTANCE_UOM_CODE,
PLANNED_ARRIVAL_DATE,
STOP_RANK,
STOP_SEQUENCE_NUMBER,
TRIP_ID,
ULTIMATE_STOP_SEQUENCE_NUMBER)
SELECT /*+ USE_HASH(idl,wt,wts) PARALLEL(idl) PARALLEL(wt) PARALLEL(wts) */
wts.stop_id STOP_ID,
nvl(wt.carrier_id, -1) CARRIER_ID,
nvl(wt.mode_of_transport, -1) MODE_OF_TRANSPORT,
nvl(wt.service_level, -1) SERVICE_LEVEL,
trunc(wts.actual_arrival_date) TIME_ACTL_ARRL_DATE_ID,
trunc(min(wts.actual_departure_date) over (partition by wt.trip_id)) TIME_INIT_DEPT_DATE_ID,
trunc(wts.planned_arrival_date) TIME_PLN_ARRL_DATE_ID,
wts.actual_arrival_date ACTUAL_ARRIVAL_DATE,
wts.actual_departure_date ACTUAL_DEPARTURE_DATE,
wts.distance_to_next_stop DISTANCE_TO_NEXT_STOP_TRX,
wts.distance_uom DISTANCE_UOM_CODE,
wts.planned_arrival_date PLANNED_ARRIVAL_DATE,
rank() over (partition by wt.trip_id order by wts.stop_sequence_number) STOP_RANK,
wts.stop_sequence_number STOP_SEQUENCE_NUMBER,
wt.trip_id TRIP_ID,
max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
FROM (select /*+ PARALLEL(wts_tmp) */ distinct trip_id
from wsh_trip_stops wts_tmp where actual_departure_date > g_global_start_date) idl,
wsh_trips wt,
wsh_trip_stops wts
WHERE idl.trip_id = wt.trip_id
AND wt.trip_id = wts.trip_id
AND wt.status_code IN ('IT', 'CL')
AND wts.physical_stop_id IS NULL
AND wts.stop_sequence_number <> -99;
INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_del_legs tmp (
DELIVERY_LEG_ID,
CARRIER_ID,
SHIPMENT_DIRECTION,
MODE_OF_TRANSPORT,
ORGANIZATION_ID,
SERVICE_LEVEL,
TIME_INIT_DEPT_DATE_ID,
CONVERSION_DATE,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
DELIVERY_ID,
DROP_OFF_STOP_ID,
FREIGHT_COST_TRX,
FREIGHT_VOLUME_TRX,
FREIGHT_WEIGHT_TRX,
PICK_UP_STOP_ID,
TRIP_ID,
TRX_CURRENCY_CODE,
VOLUME_UOM_CODE,
WEIGHT_UOM_CODE,
WH_CURRENCY_CODE,
DELIVERY_TYPE,
PARENT_DELIVERY_LEG_ID)
SELECT /*+ USE_HASH(wnd,wdl,its,ifc,hoi,gsb) PARALLEL(wnd) PARALLEL(wdl) PARALLEL(its) PARALLEL(wfc) PARALLEL(wfct) PARALLEL(hoi) PARALLEL(gsb) */
wdl.delivery_leg_id DELIVERY_LEG_ID,
its.carrier_id CARRIER_ID,
nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
its.mode_of_transport MODE_OF_TRANSPORT,
wnd.organization_id ORGANIZATION_ID,
its.service_level SERVICE_LEVEL,
its.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
decode(upper(ifc.conversion_type_code),
'USER',ifc.conversion_date,
its.time_init_dept_date_id) CONVERSION_DATE,
decode(upper(ifc.conversion_type_code),
'USER', ifc.conversion_rate, null) CONVERSION_RATE,
nvl(ifc.conversion_type_code, nvl(g_treasury_rate_type, g_global_rate_type)) CONVERSION_TYPE_CODE,
wdl.delivery_id DELIVERY_ID,
wdl.drop_off_stop_id DROP_OFF_STOP_ID,
ifc.total_amount FREIGHT_COST_TRX,
decode(wdl.parent_delivery_leg_id,null, wnd.volume,decode(delivery_type,'CONSOLIDATION',wnd.volume,0)) FREIGHT_VOLUME_TRX,
decode(wdl.parent_delivery_leg_id,null, wnd.gross_weight,decode(delivery_type,'CONSOLIDATION',wnd.gross_weight,0)) FREIGHT_WEIGHT_TRX,
wdl.pick_up_stop_id PICK_UP_STOP_ID,
its.trip_id TRIP_ID,
ifc.currency_code TRX_CURRENCY_CODE,
wnd.volume_uom_code VOLUME_UOM_CODE,
wnd.weight_uom_code WEIGHT_UOM_CODE,
gsb.currency_code WH_CURRENCY_CODE,
wnd.delivery_type DELIVERY_TYPE,
wdl.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
(select /*+ PARALLEL(wfc) */
wfc.delivery_leg_id, total_amount, wfc.currency_code, conversion_type_code, conversion_rate, conversion_date
from wsh_freight_costs wfc,wsh_freight_cost_types wfct,
wsh_new_deliveries wnd, wsh_delivery_legs wdl
where wfc.delivery_detail_id IS NULL
AND wfc.freight_cost_type_id = wfct.freight_cost_type_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.delivery_leg_id = wfc.delivery_leg_id
AND (wdl.parent_delivery_leg_id is null
OR wnd.delivery_type = 'CONSOLIDATION')
AND wfct.name = 'SUMMARY'
AND wfct.freight_cost_type_code = 'FTESUMMARY') ifc,
isc_dbi_tmp_trip_stops its,
hr_organization_information hoi,
gl_sets_of_books gsb
WHERE wdl.delivery_id = wnd.delivery_id
AND wnd.initial_pickup_date >= g_global_start_date
AND nvl(wnd.shipping_control, 'NA') <> 'SUPPLIER'
AND wdl.pick_up_stop_id = its.stop_id
AND wdl.delivery_leg_id = ifc.delivery_leg_id(+)
AND hoi.org_information_context ='Accounting Information'
AND hoi.organization_id = wnd.organization_id
AND hoi.org_information1 = to_char(gsb.set_of_books_id);
INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_fte_invoices tmp (
INVOICE_HEADER_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
ORG_ID,
SERVICE_LEVEL,
SUPPLIER_ID,
APPROVED_AMT_TRX,
BILL_AMT_TRX,
BILL_NUMBER,
BILL_STATUS,
BILL_TYPE,
BOL,
CONVERSION_DATE,
CONVERSION_TYPE_CODE,
DELIVERY_LEG_ID,
TRIP_ID,
TRX_CURRENCY_CODE,
WH_CURRENCY_CODE)
SELECT /*+ USE_HASH(idl,wdi,fih,aspa,gsb) PARALLEL(idl) PARALLEL(wdi) PARALLEL(fih) PARALLEL(aspa) PARALLEL(gsb) */
fih.invoice_header_id INVOICE_HEADER_ID,
idl.carrier_id CARRIER_ID,
idl.mode_of_transport MODE_OF_TRANSPORT,
fih.org_id ORG_ID,
idl.service_level SERVICE_LEVEL,
fih.supplier_id SUPPLIER_ID,
fih.approved_amount APPROVED_AMT_TRX,
fih.total_amount BILL_AMT_TRX,
fih.bill_number BILL_NUMBER,
fih.bill_status BILL_STATUS,
fih.bill_type BILL_TYPE,
fih.bol BOL,
idl.time_init_dept_date_id CONVERSION_DATE,
nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
idl.delivery_leg_id DELIVERY_LEG_ID,
idl.trip_id TRIP_ID,
fih.currency_code TRX_CURRENCY_CODE,
gsb.currency_code WH_CURRENCY_CODE
FROM fte_invoice_headers fih,
wsh_document_instances wdi,
isc_dbi_tmp_del_legs idl,
ar_system_parameters_all aspa,
gl_sets_of_books gsb
WHERE fih.mode_of_transport = 'LTL'
AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
AND fih.bol = wdi.sequence_number
AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
AND wdi.document_type = 'BOL'
AND wdi.entity_id = idl.delivery_leg_id
AND fih.org_id = aspa.org_id
AND aspa.set_of_books_id = gsb.set_of_books_id
AND idl.mode_of_transport = 'LTL'
UNION ALL
SELECT /*+ USE_HASH(itr,wdi,fih,aspa,gsb) PARALLEL(itr) PARALLEL(wdi) PARALLEL(fih) PARALLEL(aspa) PARALLEL(gsb) */
fih.invoice_header_id INVOICE_HEADER_ID,
itr.carrier_id CARRIER_ID,
itr.mode_of_transport MODE_OF_TRANSPORT,
fih.org_id ORG_ID,
itr.service_level SERVICE_LEVEL,
fih.supplier_id SUPPLIER_ID,
fih.approved_amount APPROVED_AMT_TRX,
fih.total_amount BILL_AMT_TRX,
fih.bill_number BILL_NUMBER,
fih.bill_status BILL_STATUS,
fih.bill_type BILL_TYPE,
fih.bol BOL,
itr.time_init_dept_date_id CONVERSION_DATE,
nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
null DELIVERY_LEG_ID,
itr.trip_id TRIP_ID,
fih.currency_code TRX_CURRENCY_CODE,
gsb.currency_code WH_CURRENCY_CODE
FROM fte_invoice_headers fih,
wsh_document_instances wdi,
isc_dbi_tmp_trip_stops itr,
ar_system_parameters_all aspa,
gl_sets_of_books gsb
WHERE fih.mode_of_transport = 'TL'
AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
AND fih.bol = wdi.sequence_number
AND wdi.entity_name = 'WSH_TRIPS'
AND wdi.document_type = 'MBOL'
AND wdi.entity_id = itr.trip_id
AND itr.stop_rank = 1
AND fih.org_id = aspa.org_id
AND aspa.set_of_books_id = gsb.set_of_books_id;
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'WT', from_uom, g_reporting_weight_uom, NULL inventory_item_id,
decode(from_uom, g_reporting_weight_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_weight_uom))
FROM (SELECT /*+ PARALLEL(tmp1) */ DISTINCT weight_uom_code FROM_UOM
FROM isc_dbi_tmp_del_legs tmp1
WHERE weight_uom_code is not null);
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'VOL', from_uom, g_reporting_volume_uom, NULL inventory_item_id,
decode(from_uom, g_reporting_volume_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_volume_uom))
FROM (SELECT /*+ PARALLEL(tmp2) */ DISTINCT volume_uom_code FROM_UOM
FROM isc_dbi_tmp_del_legs tmp2
WHERE volume_uom_code is not null);
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'DIS', from_uom, g_reporting_distance_uom, NULL inventory_item_id,
decode(from_uom,g_reporting_distance_uom,1,opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_distance_uom))
FROM (SELECT /*+ PARALLEL(tmp3) */ DISTINCT distance_uom_code FROM_UOM
FROM isc_dbi_tmp_trip_stops tmp3
where distance_uom_code is not null);
INSERT /*+ APPEND */ INTO isc_dbi_fte_curr_rates
(TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
decode(trx_currency_code, wh_currency_code, 1,
fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
FROM (SELECT /*+ PARALLEL(idl) */
distinct trx_currency_code, wh_currency_code, conversion_date CONVERSION_DATE, conversion_type_code CONVERSION_TYPE_CODE
FROM isc_dbi_tmp_del_legs idl
WHERE idl.freight_cost_trx is not null
UNION
SELECT /*+ PARALLEL(ifi) */
distinct trx_currency_code, wh_currency_code, conversion_date, conversion_type_code CONVERSION_TYPE_CODE
FROM isc_dbi_tmp_fte_invoices ifi);
INSERT INTO isc_dbi_tmp_wdd_log (DELIVERY_DETAIL_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
SELECT delivery_detail_id, rowid LOG_ROWID, dml_type, last_update_date
FROM isc_dbi_wdd_change_log;
FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_WDD_LOG');
DELETE FROM isc_dbi_del_details_f
WHERE delivery_detail_id IN (SELECT DISTINCT log.delivery_detail_id
FROM isc_dbi_tmp_wdd_log log
WHERE NOT EXISTS (select '1' from wsh_delivery_details wdd
where wdd.delivery_detail_id = log.delivery_detail_id
and wdd.released_status in ('S','Y','C','L','P')));
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' delivery details from base summary in');
INSERT INTO isc_dbi_tmp_del_details F (
DELIVERY_DETAIL_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SHIPMENT_DIRECTION,
SUBINVENTORY_CODE,
TIME_IP_DATE_ID,
TIME_PR_DATE_ID,
DELIVERY_ID,
INITIAL_PICKUP_DATE,
MOVE_ORDER_LINE_ID,
PICK_RELEASED_DATE,
RELEASED_STATUS,
REQUESTED_QUANTITY,
REQUESTED_QUANTITY_UOM,
SHIPPED_QUANTITY,
WMS_ENABLED_FLAG)
SELECT /*+ leading(log) use_nl(mp) */
wdd.delivery_detail_id DELIVERY_DETAIL_ID,
wdd.inventory_item_id INVENTORY_ITEM_ID,
nvl(wnd.organization_id, wdd.organization_id) ORGANIZATION_ID,
nvl(wnd.shipment_direction, 'O') SHIPMENT_DIRECTION,
mmt.subinventory_code SUBINVENTORY_CODE,
trunc(wnd.initial_pickup_date) TIME_IP_DATE_ID,
trunc(mol.creation_date) TIME_PR_DATE_ID,
wnd.delivery_id DELIVERY_ID,
wnd.initial_pickup_date INITIAL_PICKUP_DATE,
wdd.move_order_line_id MOVE_ORDER_LINE_ID,
mol.creation_date PICK_RELEASED_DATE,
wdd.released_status RELEASED_STATUS,
wdd.requested_quantity REQUESTED_QUANTITY,
wdd.requested_quantity_uom REQUESTED_QUANTITY_UOM,
wdd.shipped_quantity SHIPPED_QUANTITY,
mp.wms_enabled_flag WMS_ENABLED_FLAG
FROM (select distinct delivery_detail_id from isc_dbi_tmp_wdd_log) log,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
mtl_txn_request_lines mol,
mtl_material_transactions mmt,
mtl_parameters mp
WHERE wdd.delivery_detail_id = log.delivery_detail_id
AND wdd.released_status in ('S','Y','C','L','P')
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wda.type,'S') in ('S','O')
AND nvl(wdd.container_flag,'N') = 'N'
AND wda.delivery_id = wnd.delivery_id (+)
AND wdd.move_order_line_id = mol.line_id (+)
AND wdd.transaction_id = mmt.transaction_id (+)
AND nvl(mmt.transaction_source_type_id,2) IN (2,8)
AND nvl(mmt.transaction_action_id,28) = 28
AND nvl(mmt.transaction_quantity,-1) < 0
AND wdd.organization_id = mp.organization_id
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;
UPDATE isc_dbi_tmp_del_details SET batch_id = ceil(rownum/g_batch_size);
FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
INSERT INTO isc_dbi_tmp_wts_log (STOP_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
SELECT stop_id, rowid LOG_ROWID, dml_type, last_update_date
FROM isc_dbi_wts_change_log;
FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_WTS_LOG');
DELETE /*+ index(a, ISC_DBI_DEL_LEGS_F_U1) */ FROM isc_dbi_del_legs_f a
WHERE delivery_leg_id IN (SELECT /*+ index(idl,ISC_DBI_DEL_LEGS_F_U1) use_nl( log, idl)*/ idl.delivery_leg_id
FROM isc_dbi_tmp_wts_log log,
isc_dbi_del_legs_f idl
WHERE (log.stop_id = idl.pick_up_stop_id or log.stop_id = idl.drop_off_stop_id)
AND NOT EXISTS (select '1' from wsh_delivery_legs wdl where wdl.delivery_leg_id = idl.delivery_leg_id));
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' delivery legs from base summary in');
DELETE FROM isc_dbi_trip_stops_f
WHERE stop_id IN (SELECT DISTINCT log.stop_id
FROM isc_dbi_tmp_wts_log log
WHERE NOT EXISTS (select '1' from wsh_trip_stops wts, wsh_trips wt
where log.stop_id = wts.stop_id
and wts.trip_id = wt.trip_id
and wt.status_code IN ('IT', 'CL')));
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' trip stops from base summary in');
INSERT INTO isc_dbi_tmp_trip_stops tmp (
STOP_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
SERVICE_LEVEL,
TIME_ACTL_ARRL_DATE_ID,
TIME_INIT_DEPT_DATE_ID,
TIME_PLN_ARRL_DATE_ID,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
DISTANCE_TO_NEXT_STOP_TRX,
DISTANCE_UOM_CODE,
PLANNED_ARRIVAL_DATE,
STOP_RANK,
STOP_SEQUENCE_NUMBER,
TRIP_ID,
ULTIMATE_STOP_SEQUENCE_NUMBER)
SELECT /*+ leading(log) */ wts.stop_id STOP_ID,
nvl(wt.carrier_id, -1) CARRIER_ID,
nvl(wt.mode_of_transport, -1) MODE_OF_TRANSPORT,
nvl(wt.service_level, -1) SERVICE_LEVEL,
trunc(wts.actual_arrival_date) TIME_ACTL_ARRL_DATE_ID,
trunc(min(wts.actual_departure_date) over (partition by wt.trip_id)) TIME_INIT_DEPT_DATE_ID,
trunc(wts.planned_arrival_date) TIME_PLN_ARRL_DATE_ID,
wts.actual_arrival_date ACTUAL_ARRIVAL_DATE,
wts.actual_departure_date ACTUAL_DEPARTURE_DATE,
wts.distance_to_next_stop DISTANCE_TO_NEXT_STOP_TRX,
wts.distance_uom DISTANCE_UOM_CODE,
wts.planned_arrival_date PLANNED_ARRIVAL_DATE,
rank() over (partition by wt.trip_id order by wts.stop_sequence_number) STOP_RANK,
wts.stop_sequence_number STOP_SEQUENCE_NUMBER,
wt.trip_id TRIP_ID,
max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
FROM (select /*+ no_merge index(tr) */ distinct tr.trip_id
from isc_dbi_tmp_wts_log tmp, wsh_trip_stops tr
where tmp.stop_id = tr.stop_id) log,
wsh_trips wt,
wsh_trip_stops wts
WHERE log.trip_id = wt.trip_id
AND wt.trip_id = wts.trip_id
AND wt.status_code IN ('IT', 'CL')
AND wts.physical_stop_id IS NULL
AND wts.stop_sequence_number <> -99;
INSERT INTO isc_dbi_tmp_del_legs tmp (
DELIVERY_LEG_ID,
CARRIER_ID,
SHIPMENT_DIRECTION,
MODE_OF_TRANSPORT,
ORGANIZATION_ID,
SERVICE_LEVEL,
TIME_INIT_DEPT_DATE_ID,
CONVERSION_DATE,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
DELIVERY_ID,
DROP_OFF_STOP_ID,
FREIGHT_COST_TRX,
FREIGHT_VOLUME_TRX,
FREIGHT_WEIGHT_TRX,
PICK_UP_STOP_ID,
TRIP_ID,
TRX_CURRENCY_CODE,
VOLUME_UOM_CODE,
WEIGHT_UOM_CODE,
WH_CURRENCY_CODE,
DELIVERY_TYPE,
PARENT_DELIVERY_LEG_ID)
SELECT /*+ leading(its) use_nl(wdl)use_nl(hoi) use_nl(gsb) use_nl (wnd) */
wdl.delivery_leg_id DELIVERY_LEG_ID,
its.carrier_id CARRIER_ID,
nvl(wnd.shipment_direction,'O') SHIPMENT_DIRECTION,
its.mode_of_transport MODE_OF_TRANSPORT,
wnd.organization_id ORGANIZATION_ID,
its.service_level SERVICE_LEVEL,
its.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
decode(upper(ifc.conversion_type_code),
'USER',ifc.conversion_date,
its.time_init_dept_date_id) CONVERSION_DATE,
decode(upper(ifc.conversion_type_code),
'USER', ifc.conversion_rate, null) CONVERSION_RATE,
nvl(ifc.conversion_type_code, nvl(g_treasury_rate_type, g_global_rate_type)) CONVERSION_TYPE_CODE,
wdl.delivery_id DELIVERY_ID,
wdl.drop_off_stop_id DROP_OFF_STOP_ID,
ifc.total_amount FREIGHT_COST_TRX,
decode(wdl.parent_delivery_leg_id,null, wnd.volume,decode(delivery_type,'CONSOLIDATION',wnd.volume,0)) FREIGHT_VOLUME_TRX,
decode(wdl.parent_delivery_leg_id,null, wnd.gross_weight,decode(delivery_type,'CONSOLIDATION',wnd.gross_weight,0)) FREIGHT_WEIGHT_TRX,
wdl.pick_up_stop_id PICK_UP_STOP_ID,
its.trip_id TRIP_ID,
ifc.currency_code TRX_CURRENCY_CODE,
wnd.volume_uom_code VOLUME_UOM_CODE,
wnd.weight_uom_code WEIGHT_UOM_CODE,
gsb.currency_code WH_CURRENCY_CODE,
wnd.delivery_type DELIVERY_TYPE,
wdl.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
(select /*+ use_nl (wfct, wfc) */
wfc.delivery_leg_id, total_amount, wfc.currency_code, conversion_type_code, conversion_rate, conversion_date
from wsh_freight_costs wfc,wsh_freight_cost_types wfct,
wsh_new_deliveries wnd, wsh_delivery_legs wdl
where wfc.delivery_detail_id IS NULL
AND wfc.freight_cost_type_id = wfct.freight_cost_type_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.delivery_leg_id = wfc.delivery_leg_id
AND (wdl.parent_delivery_leg_id is null
OR wnd.delivery_type = 'CONSOLIDATION')
AND wfct.name = 'SUMMARY'
AND wfct.freight_cost_type_code = 'FTESUMMARY') ifc,
isc_dbi_tmp_trip_stops its,
hr_organization_information hoi,
gl_sets_of_books gsb
WHERE wdl.delivery_id = wnd.delivery_id
AND wnd.initial_pickup_date >= g_global_start_date
AND nvl(wnd.shipping_control, 'NA') <> 'SUPPLIER'
AND wdl.pick_up_stop_id = its.stop_id
AND wdl.delivery_leg_id = ifc.delivery_leg_id(+)
AND hoi.org_information_context ='Accounting Information'
AND hoi.organization_id = wnd.organization_id
AND to_number(hoi.org_information1) = gsb.set_of_books_id;
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'WT', from_uom, g_reporting_weight_uom, NULL inventory_item_id,
decode(from_uom, g_reporting_weight_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_weight_uom))
FROM (SELECT DISTINCT weight_uom_code FROM_UOM
FROM isc_dbi_tmp_del_legs
WHERE weight_uom_code is not null);
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'VOL', from_uom, g_reporting_volume_uom, NULL inventory_item_id,
decode(from_uom, g_reporting_volume_uom, 1, opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_volume_uom))
FROM (SELECT DISTINCT volume_uom_code FROM_UOM
FROM isc_dbi_tmp_del_legs
WHERE volume_uom_code is not null);
INSERT INTO isc_dbi_fte_uom_rates (MEASURE_CODE, FROM_UOM_CODE, TO_UOM_CODE, INVENTORY_ITEM_ID, CONVERSION_RATE)
SELECT 'DIS', from_uom, g_reporting_distance_uom, NULL inventory_item_id,
decode(from_uom,g_reporting_distance_uom,1,opi_dbi_rep_uom_pkg.uom_convert(NULL, NULL, 1, from_uom, g_reporting_distance_uom))
FROM (SELECT DISTINCT distance_uom_code FROM_UOM
FROM isc_dbi_tmp_trip_stops
where distance_uom_code is not null);
INSERT INTO isc_dbi_fte_curr_rates
(TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
decode(trx_currency_code, wh_currency_code, 1,
fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
FROM (SELECT distinct trx_currency_code, wh_currency_code, conversion_date CONVERSION_DATE, conversion_type_code CONVERSION_TYPE_CODE
FROM isc_dbi_tmp_del_legs idl
WHERE idl.freight_cost_trx is not null);
UPDATE isc_dbi_tmp_del_legs SET batch_id = ceil(rownum/g_batch_size);
UPDATE isc_dbi_tmp_trip_stops SET batch_id = ceil(rownum/g_batch_size);
FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
INSERT INTO isc_dbi_tmp_fih_log (INVOICE_HEADER_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
SELECT invoice_header_id, rowid LOG_ROWID, dml_type, last_update_date
FROM isc_dbi_fih_change_log;
FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_FIH_LOG');
DELETE FROM isc_dbi_fte_invoices_f
WHERE invoice_header_id IN (SELECT DISTINCT log.invoice_header_id
FROM isc_dbi_tmp_fih_log log
WHERE NOT EXISTS (select '1' from fte_invoice_headers fih
where fih.invoice_header_id = log.invoice_header_id
and fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')));
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' invoice headers from base summary in');
INSERT INTO isc_dbi_tmp_fte_invoices tmp (
INVOICE_HEADER_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
ORG_ID,
SERVICE_LEVEL,
SUPPLIER_ID,
APPROVED_AMT_TRX,
BILL_AMT_TRX,
BILL_NUMBER,
BILL_STATUS,
BILL_TYPE,
BOL,
CONVERSION_DATE,
CONVERSION_TYPE_CODE,
DELIVERY_LEG_ID,
TRIP_ID,
TRX_CURRENCY_CODE,
WH_CURRENCY_CODE)
SELECT /*+ leading(log) */
fih.invoice_header_id INVOICE_HEADER_ID,
idl.carrier_id CARRIER_ID,
idl.mode_of_transport MODE_OF_TRANSPORT,
fih.org_id ORG_ID,
idl.service_level SERVICE_LEVEL,
fih.supplier_id SUPPLIER_ID,
fih.approved_amount APPROVED_AMT_TRX,
fih.total_amount BILL_AMT_TRX,
fih.bill_number BILL_NUMBER,
fih.bill_status BILL_STATUS,
fih.bill_type BILL_TYPE,
fih.bol BOL,
idl.time_init_dept_date_id CONVERSION_DATE,
nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
idl.delivery_leg_id DELIVERY_LEG_ID,
idl.trip_id TRIP_ID,
fih.currency_code TRX_CURRENCY_CODE,
gsb.currency_code WH_CURRENCY_CODE
FROM (select distinct invoice_header_id from isc_dbi_tmp_fih_log) log,
fte_invoice_headers fih,
wsh_document_instances wdi,
isc_dbi_del_legs_f idl,
ar_system_parameters_all aspa,
gl_sets_of_books gsb
WHERE log.invoice_header_id = fih.invoice_header_id
AND fih.mode_of_transport = 'LTL'
AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
AND fih.bol = wdi.sequence_number
AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
AND wdi.document_type = 'BOL'
AND wdi.entity_id = idl.delivery_leg_id
AND fih.org_id = aspa.org_id
AND aspa.set_of_books_id = gsb.set_of_books_id
AND idl.mode_of_transport = 'LTL'
UNION ALL
SELECT /*+ leading(log) */
fih.invoice_header_id INVOICE_HEADER_ID,
itr.carrier_id CARRIER_ID,
itr.mode_of_transport MODE_OF_TRANSPORT,
fih.org_id ORG_ID,
itr.service_level SERVICE_LEVEL,
fih.supplier_id SUPPLIER_ID,
fih.approved_amount APPROVED_AMT_TRX,
fih.total_amount BILL_AMT_TRX,
fih.bill_number BILL_NUMBER,
fih.bill_status BILL_STATUS,
fih.bill_type BILL_TYPE,
fih.bol BOL,
itr.time_init_dept_date_id CONVERSION_DATE,
nvl(g_treasury_rate_type, g_global_rate_type) CONVERSION_TYPE_CODE,
null DELIVERY_LEG_ID,
itr.trip_id TRIP_ID,
fih.currency_code TRX_CURRENCY_CODE,
gsb.currency_code WH_CURRENCY_CODE
FROM (select distinct invoice_header_id from isc_dbi_tmp_fih_log) log,
fte_invoice_headers fih,
wsh_document_instances wdi,
isc_dbi_trip_stops_f itr,
ar_system_parameters_all aspa,
gl_sets_of_books gsb
WHERE log.invoice_header_id = fih.invoice_header_id
AND fih.mode_of_transport = 'TL'
AND fih.bill_status in ('APPROVED', 'IN_PROGRESS', 'PAID', 'PARTIALLY PAID', 'PARTIAL_PAID')
AND fih.bol = wdi.sequence_number
AND wdi.entity_name = 'WSH_TRIPS'
AND wdi.document_type = 'MBOL'
AND wdi.entity_id = itr.trip_id
AND itr.stop_rank = 1
AND fih.org_id = aspa.org_id
AND aspa.set_of_books_id = gsb.set_of_books_id;
INSERT INTO isc_dbi_fte_curr_rates
(TRX_CURRENCY_CODE, WH_CURRENCY_CODE, CONVERSION_DATE, CONVERSION_TYPE_CODE, TRX_WH_RATE, WH_PRIM_RATE, WH_SEC_RATE)
SELECT trx_currency_code, wh_currency_code, conversion_date, conversion_type_code,
decode(trx_currency_code, wh_currency_code, 1,
fii_currency.get_rate(trx_currency_code, wh_currency_code, conversion_date, conversion_type_code)) TRX_WH_RATE,
decode(g_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_primary(wh_currency_code, conversion_date)) WH_PRIM_RATE,
decode(g_sec_global_currency, trx_currency_code, 1, wh_currency_code, 1,
fii_currency.get_global_rate_secondary(wh_currency_code, conversion_date)) WH_SEC_RATE
FROM (SELECT distinct trx_currency_code, wh_currency_code, conversion_date, conversion_type_code CONVERSION_TYPE_CODE
FROM isc_dbi_tmp_fte_invoices);
UPDATE isc_dbi_tmp_fte_invoices SET batch_id = ceil(rownum/g_batch_size);
FII_UTIL.Print_Timer('Updated the batch id for '|| l_total || ' rows in');
SELECT /*+ PARALLEL(tmp) */ delivery_detail_id,
to_char(time_ip_date_id, 'MM/DD/YYYY') time_ip_date_id,
to_char(time_pr_date_id, 'MM/DD/YYYY') time_pr_date_id
FROM isc_dbi_tmp_del_details tmp
WHERE (least(nvl(time_ip_date_id,l_time_min), nvl(time_pr_date_id,l_time_min)) < l_time_min
OR greatest(nvl(time_ip_date_id, l_time_max), nvl(time_pr_date_id, l_time_max)) > l_time_max);
SELECT /*+ PARALLEL(tmp) */
trip_id,
stop_id,
to_char(time_actl_arrl_date_id, 'MM/DD/YYYY') time_actl_arrl_date_id,
to_char(actual_departure_date, 'MM/DD/YYYY') time_actl_dept_date_id,
to_char(time_pln_arrl_date_id,'MM/DD/YYYY') time_pln_arrl_date_id
FROM isc_dbi_tmp_trip_stops tmp
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
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);
SELECT /*+ PARALLEL(tmp) */
min(time_ip_date_id), max(time_ip_date_id),
min(time_pr_date_id), max(time_pr_date_id)
INTO l_min_ip_date, l_max_ip_date, l_min_pr_date, l_max_pr_date
FROM isc_dbi_tmp_del_details tmp;
SELECT /*+ PARALLEL(tmp) */
min(time_actl_arrl_date_id), max(time_actl_arrl_date_id),
min(time_init_dept_date_id), max(time_init_dept_date_id),
min(time_pln_arrl_date_id), max(time_pln_arrl_date_id)
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
FROM isc_dbi_tmp_trip_stops tmp;
SELECT min(report_date), max(report_date)
INTO l_time_min, l_time_max
FROM fii_time_day;
SELECT delivery_detail_id,
to_char(time_ip_date_id, 'MM/DD/YYYY') time_ip_date_id,
to_char(time_pr_date_id, 'MM/DD/YYYY') time_pr_date_id
FROM isc_dbi_tmp_del_details
WHERE (least(nvl(time_ip_date_id,l_time_min), nvl(time_pr_date_id,l_time_min)) < l_time_min
OR greatest(nvl(time_ip_date_id, l_time_max), nvl(time_pr_date_id, l_time_max)) > l_time_max);
SELECT min(time_ip_date_id), max(time_ip_date_id),
min(time_pr_date_id), max(time_pr_date_id)
INTO l_min_ip_date, l_max_ip_date, l_min_pr_date, l_max_pr_date
FROM isc_dbi_tmp_del_details tmp;
SELECT min(report_date), max(report_date)
INTO l_time_min, l_time_max
FROM fii_time_day;
SELECT trip_id,
stop_id,
to_char(time_actl_arrl_date_id, 'MM/DD/YYYY') time_actl_arrl_date_id,
to_char(actual_departure_date, 'MM/DD/YYYY') time_actl_dept_date_id,
to_char(time_pln_arrl_date_id,'MM/DD/YYYY') time_pln_arrl_date_id
FROM isc_dbi_tmp_trip_stops
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
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);
SELECT min(time_actl_arrl_date_id), max(time_actl_arrl_date_id),
min(time_init_dept_date_id), max(time_init_dept_date_id),
min(time_pln_arrl_date_id), max(time_pln_arrl_date_id)
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
FROM isc_dbi_tmp_trip_stops tmp;
SELECT min(report_date), max(report_date)
INTO l_time_min, l_time_max
FROM fii_time_day;
SELECT /*+ PARALLEL(tmp) PARALLEL(item) */ distinct tmp.inventory_item_id, tmp.organization_id
FROM isc_dbi_tmp_del_details tmp,
eni_oltp_item_star item
WHERE tmp.inventory_item_id = item.inventory_item_id(+)
AND tmp.organization_id = item.organization_id(+)
AND item.inventory_item_id IS NULL
AND tmp.inventory_item_id IS NOT NULL;
SELECT distinct tmp.inventory_item_id, tmp.organization_id
FROM isc_dbi_tmp_del_details tmp,
eni_oltp_item_star item
WHERE tmp.inventory_item_id = item.inventory_item_id(+)
AND tmp.organization_id = item.organization_id(+)
AND item.inventory_item_id IS NULL
AND tmp.inventory_item_id IS NOT NULL;
SELECT distinct decode(trx_wh_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
trx_currency_code FROM_CURRENCY,
wh_currency_code TO_CURRENCY,
conversion_type_code RATE_TYPE,
decode(trx_wh_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_dbi_fte_curr_rates tmp
WHERE trx_wh_rate < 0
AND upper(conversion_type_code) <> 'USER'
UNION
SELECT distinct decode(wh_prim_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
wh_currency_code FROM_CURRENCY,
g_global_currency TO_CURRENCY,
g_global_rate_type RATE_TYPE,
decode(wh_prim_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_dbi_fte_curr_rates tmp
WHERE wh_prim_rate < 0
UNION
SELECT distinct decode(wh_sec_rate, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
wh_currency_code FROM_CURRENCY,
g_sec_global_currency TO_CURRENCY,
g_sec_global_rate_type RATE_TYPE,
decode(wh_sec_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_dbi_fte_curr_rates tmp
WHERE wh_sec_rate < 0
AND g_sec_curr_def = 'Y';
SELECT distinct inventory_item_id,
from_uom_code from_unit,
to_uom_code to_unit
FROM isc_dbi_fte_uom_rates
WHERE conversion_rate between -99999 and -99995;
SELECT name
FROM wsh_new_deliveries
WHERE delivery_id IN (SELECT distinct delivery_id
FROM isc_dbi_tmp_del_legs
WHERE (freight_weight_trx is not null and weight_uom_code is null)
OR (freight_volume_trx is not null and volume_uom_code is null));
FUNCTION INSERT_FACT RETURN NUMBER IS
l_detail_count NUMBER;
BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_details_f');
INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_DETAILS_F F
(DELIVERY_DETAIL_ID,
INVENTORY_ITEM_ID,
SHIPMENT_DIRECTION,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
TIME_IP_DATE_ID,
TIME_PR_DATE_ID,
DELIVERY_ID,
INITIAL_PICKUP_DATE,
MOVE_ORDER_LINE_ID,
PICK_RELEASED_DATE,
RELEASED_STATUS,
REQUESTED_QUANTITY,
REQUESTED_QUANTITY_UOM,
SHIPPED_QUANTITY,
WMS_ENABLED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
SELECT /*+ PARALLEL(v) */
v.delivery_detail_id DELIVERY_DETAIL_ID,
v.inventory_item_id INVENTORY_ITEM_ID,
v.shipment_direction SHIPMENT_DIRECTION,
v.organization_id ORGANIZATION_ID,
v.subinventory_code SUBINVENTORY_CODE,
v.time_ip_date_id TIME_IP_DATE_ID,
v.time_pr_date_id TIME_PR_DATE_ID,
v.delivery_id DELIVERY_ID,
v.initial_pickup_date INITIAL_PICKUP_DATE,
v.move_order_line_id MOVE_ORDER_LINE_ID,
v.pick_released_date PICK_RELEASED_DATE,
v.released_status RELEASED_STATUS,
v.requested_quantity REQUESTED_QUANTITY,
v.requested_quantity_uom REQUESTED_QUANTITY_UOM,
v.shipped_quantity SHIPPED_QUANTITY,
v.wms_enabled_flag WMS_ENABLED_FLAG,
-1 CREATED_BY,
sysdate CREATION_DATE,
-1 LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATE_LOGIN,
-1 PROGRAM_APPLICATION_ID,
-1 PROGRAM_ID,
sysdate PROGRAM_UPDATE_DATE,
-1 REQUEST_ID
FROM isc_dbi_tmp_del_details v;
FII_UTIL.Print_Timer('Inserted '|| l_detail_count ||' rows into isc_dbi_del_details_f in');
BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_legs_f');
INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_LEGS_F F
(DELIVERY_LEG_ID,
CARRIER_ID,
SHIPMENT_DIRECTION,
MODE_OF_TRANSPORT,
ORGANIZATION_ID,
SERVICE_LEVEL,
TIME_INIT_DEPT_DATE_ID,
DELIVERY_ID,
DROP_OFF_STOP_ID,
FREIGHT_COST_F,
FREIGHT_COST_G,
FREIGHT_COST_G1,
FREIGHT_VOLUME_G,
FREIGHT_VOLUME_TRX,
FREIGHT_WEIGHT_G,
FREIGHT_WEIGHT_TRX,
PICK_UP_STOP_ID,
TRIP_ID,
VOLUME_UOM_CODE,
WEIGHT_UOM_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DELIVERY_TYPE,
PARENT_DELIVERY_LEG_ID)
SELECT /*+ PARALLEL(v) */
v.delivery_leg_id DELIVERY_LEG_ID,
v.carrier_id CARRIER_ID,
v.shipment_direction SHIPMENT_DIRECTION,
v.mode_of_transport MODE_OF_TRANSPORT,
v.organization_id ORGANIZATION_ID,
v.service_level SERVICE_LEVEL,
v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
v.delivery_id DELIVERY_ID,
v.drop_off_stop_id DROP_OFF_STOP_ID,
v.freight_cost_trx
* nvl(v.conversion_rate, curr.trx_wh_rate) FREIGHT_COST_F,
v.freight_cost_trx
* decode(v.trx_currency_code, g_global_currency, 1, curr.trx_wh_rate * curr.wh_prim_rate) FREIGHT_COST_G,
v.freight_cost_trx
* decode(v.trx_currency_code, g_sec_global_currency, 1, curr.trx_wh_rate * curr.wh_sec_rate) FREIGHT_COST_G1,
v.freight_volume_trx * v_rates.conversion_rate FREIGHT_VOLUME_G,
v.freight_volume_trx FREIGHT_VOLUME_TRX,
v.freight_weight_trx * w_rates.conversion_rate FREIGHT_WEIGHT_G,
v.freight_weight_trx FREIGHT_WEIGHT_TRX,
v.pick_up_stop_id PICK_UP_STOP_ID,
v.trip_id TRIP_ID,
v.volume_uom_code VOLUME_UOM_CODE,
v.weight_uom_code WEIGHT_UOM_CODE,
-1 CREATED_BY,
sysdate CREATION_DATE,
-1 LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATE_LOGIN,
-1 PROGRAM_APPLICATION_ID,
-1 PROGRAM_ID,
sysdate PROGRAM_UPDATE_DATE,
-1 REQUEST_ID,
v.delivery_type DELIVERY_TYPE,
v.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
FROM isc_dbi_tmp_del_legs v,
isc_dbi_fte_curr_rates curr,
isc_dbi_fte_uom_rates w_rates,
isc_dbi_fte_uom_rates v_rates
WHERE v.weight_uom_code = w_rates.from_uom_code(+)
AND w_rates.measure_code(+) = 'WT'
AND v.volume_uom_code = v_rates.from_uom_code(+)
AND v_rates.measure_code(+) = 'VOL'
AND v.trx_currency_code = curr.trx_currency_code(+)
AND v.wh_currency_code = curr.wh_currency_code(+)
AND v.conversion_date = curr.conversion_date(+)
AND v.conversion_type_code = curr.conversion_type_code(+);
FII_UTIL.Print_Timer('Inserted '|| l_leg_count ||' rows into isc_dbi_del_legs_f in');
BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_trip_stops_f');
INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_TRIP_STOPS_F F
(STOP_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
SERVICE_LEVEL,
TIME_ACTL_ARRL_DATE_ID,
TIME_INIT_DEPT_DATE_ID,
TIME_PLN_ARRL_DATE_ID,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
DISTANCE_TO_NEXT_STOP_G,
DISTANCE_TO_NEXT_STOP_TRX,
DISTANCE_UOM_CODE,
PLANNED_ARRIVAL_DATE,
STOP_RANK,
STOP_SEQUENCE_NUMBER,
TRIP_FREIGHT_COST_G,
TRIP_FREIGHT_COST_G1,
TRIP_ID,
ULTIMATE_STOP_SEQUENCE_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
SELECT /*+ PARALLEL(v) PARALLEL(itr) */
v.stop_id STOP_ID,
v.carrier_id CARRIER_ID,
v.mode_of_transport MODE_OF_TRANSPORT,
v.service_level SERVICE_LEVEL,
v.time_actl_arrl_date_id TIME_ACTL_ARRL_DATE_ID,
v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
v.time_pln_arrl_date_id TIME_PLN_ARRL_DATE_ID,
v.actual_arrival_date ACTUAL_ARRIVAL_DATE,
v.actual_departure_date ACTUAL_DEPARTURE_DATE,
v.distance_to_next_stop_trx * d_rates.conversion_rate DISTANCE_TO_NEXT_STOP_G,
v.distance_to_next_stop_trx DISTANCE_TO_NEXT_STOP_TRX,
v.distance_uom_code DISTANCE_UOM_CODE,
v.planned_arrival_date PLANNED_ARRIVAL_DATE,
v.stop_rank STOP_RANK,
v.stop_sequence_number STOP_SEQUENCE_NUMBER,
itr.trip_freight_cost_g TRIP_FREIGHT_COST_G,
itr.trip_freight_cost_g1 TRIP_FREIGHT_COST_G1,
v.trip_id TRIP_ID,
v.ultimate_stop_sequence_number ULTIMATE_STOP_SEQUENCE_NUMBER,
-1 CREATED_BY,
sysdate CREATION_DATE,
-1 LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATE_LOGIN,
-1 PROGRAM_APPLICATION_ID,
-1 PROGRAM_ID,
sysdate PROGRAM_UPDATE_DATE,
-1 REQUEST_ID
FROM isc_dbi_tmp_trip_stops v,
(select /*+ PARALLEL(tmp) */ trip_id,
sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
* decode(tmp.trx_currency_code,g_global_currency,1,curr.trx_wh_rate * curr.wh_prim_rate)) TRIP_FREIGHT_COST_G,
sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
* decode(tmp.trx_currency_code,g_sec_global_currency,1,curr.trx_wh_rate*curr.wh_sec_rate)) TRIP_FREIGHT_COST_G1
from isc_dbi_tmp_del_legs tmp,
isc_dbi_fte_curr_rates curr
where tmp.trx_currency_code = curr.trx_currency_code(+)
and tmp.wh_currency_code = curr.wh_currency_code(+)
and tmp.conversion_date = curr.conversion_date(+)
and tmp.conversion_type_code = curr.conversion_type_code(+)
group by trip_id) itr,
isc_dbi_fte_uom_rates d_rates
WHERE v.trip_id = itr.trip_id
AND v.distance_uom_code = d_rates.from_uom_code(+)
AND d_rates.measure_code(+) = 'DIS';
FII_UTIL.Print_Timer('Inserted '|| l_stop_count ||' rows into isc_dbi_trip_stops_f in');
BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_fte_invoices_f');
INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_FTE_INVOICES_F F
(INVOICE_HEADER_ID,
CARRIER_ID,
MODE_OF_TRANSPORT,
ORG_ID,
SERVICE_LEVEL,
SUPPLIER_ID,
APPROVED_AMT_F,
APPROVED_AMT_G,
APPROVED_AMT_G1,
BILL_AMT_F,
BILL_AMT_G,
BILL_AMT_G1,
BILL_NUMBER,
BILL_STATUS,
BILL_TYPE,
BOL,
DELIVERY_LEG_ID,
TRIP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
SELECT /*+ PARALLEL(v) PARALLEL(curr) */
v.invoice_header_id INVOICE_HEADER_ID,
v.carrier_id CARRIER_ID,
v.mode_of_transport MODE_OF_TRANSPORT,
v.org_id ORG_ID,
v.service_level SERVICE_LEVEL,
v.supplier_id SUPPLIER_ID,
v.approved_amt_trx * curr.trx_wh_rate APPROVED_AMT_F,
v.approved_amt_trx
* decode(v.trx_currency_code, g_global_currency, 1,
curr.trx_wh_rate * curr.wh_prim_rate) APPROVED_AMT_G,
v.approved_amt_trx
* decode(v.trx_currency_code, g_sec_global_currency, 1,
curr.trx_wh_rate * curr.wh_sec_rate) APPROVED_AMT_G1,
v.bill_amt_trx * curr.trx_wh_rate BILL_AMT_F,
v.bill_amt_trx
* decode(v.trx_currency_code, g_global_currency, 1,
curr.trx_wh_rate * curr.wh_prim_rate) BILL_AMT_G,
v.bill_amt_trx
* decode(v.trx_currency_code, g_sec_global_currency, 1,
curr.trx_wh_rate * curr.wh_sec_rate) BILL_AMT_G1,
v.bill_number BILL_NUMBER,
v.bill_status BILL_STATUS,
v.bill_type BILL_TYPE,
v.bol BOL,
v.delivery_leg_id DELIVERY_LEG_ID,
v.trip_id TRIP_ID,
-1 CREATED_BY,
sysdate CREATION_DATE,
-1 LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATE_LOGIN,
-1 PROGRAM_APPLICATION_ID,
-1 PROGRAM_ID,
sysdate PROGRAM_UPDATE_DATE,
-1 REQUEST_ID
FROM isc_dbi_tmp_fte_invoices v,
isc_dbi_fte_curr_rates curr
WHERE v.trx_currency_code = curr.trx_currency_code
AND v.wh_currency_code = curr.wh_currency_code
AND v.conversion_date = curr.conversion_date
AND v.conversion_type_code = curr.conversion_type_code;
FII_UTIL.Print_Timer('Inserted '|| l_invoice_count ||' rows into isc_dbi_fte_invoices_f in');
g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
END insert_fact;
(select new.* from isc_dbi_tmp_del_details new, isc_dbi_del_details_f old
where new.delivery_detail_id = old.delivery_detail_id(+)
and new.batch_id = v_batch_id
and (old.delivery_detail_id is null
or new.inventory_item_id <> old.inventory_item_id
or new.shipment_direction <> old.shipment_direction
or new.organization_id <> old.organization_id
or nvl(new.subinventory_code, -1) <> nvl(old.subinventory_code, -1)
or nvl(new.delivery_id, -1) <> nvl(old.delivery_id, -1)
or nvl(new.initial_pickup_date,l_date) <> nvl(old.initial_pickup_date,l_date)
or nvl(new.move_order_line_id,-1) <> nvl(old.move_order_line_id,-1)
or nvl(new.pick_released_date,l_date) <> nvl(old.pick_released_date,l_date)
or nvl(new.released_status,'na') <> nvl(old.released_status,'na')
or new.requested_quantity <> old.requested_quantity
or new.requested_quantity_uom <> old.requested_quantity_uom
or nvl(new.shipped_quantity,-1) <> nvl(old.shipped_quantity,-1)
or nvl(new.wms_enabled_flag,'na') <> nvl(old.wms_enabled_flag,'na'))) v
ON (f.delivery_detail_id = v.delivery_detail_id)
WHEN MATCHED THEN UPDATE SET
f.inventory_item_id = v.inventory_item_id,
f.shipment_direction = v.shipment_direction,
f.organization_id = v.organization_id,
f.subinventory_code = v.subinventory_code,
f.time_ip_date_id = v.time_ip_date_id,
f.time_pr_date_id = v.time_pr_date_id,
f.delivery_id = v.delivery_id,
f.initial_pickup_date = v.initial_pickup_date,
f.move_order_line_id = v.move_order_line_id,
f.pick_released_date = v.pick_released_date,
f.released_status = v.released_status,
f.requested_quantity = v.requested_quantity,
f.requested_quantity_uom = v.requested_quantity_uom,
f.shipped_quantity = v.shipped_quantity,
f.wms_enabled_flag = v.wms_enabled_flag,
f.last_update_date = g_incre_start_date
WHEN NOT MATCHED THEN INSERT(
f.delivery_detail_id,
f.inventory_item_id,
f.shipment_direction,
f.organization_id,
f.subinventory_code,
f.time_ip_date_id,
f.time_pr_date_id,
f.delivery_id,
f.initial_pickup_date,
f.move_order_line_id,
f.pick_released_date,
f.released_status,
f.requested_quantity,
f.requested_quantity_uom,
f.shipped_quantity,
f.wms_enabled_flag,
f.created_by,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.last_update_login,
f.program_application_id,
f.program_id,
f.program_update_date,
f.request_id)
VALUES (
v.delivery_detail_id,
v.inventory_item_id,
v.shipment_direction,
v.organization_id,
v.subinventory_code,
v.time_ip_date_id,
v.time_pr_date_id,
v.delivery_id,
v.initial_pickup_date,
v.move_order_line_id,
v.pick_released_date,
v.released_status,
v.requested_quantity,
v.requested_quantity_uom,
v.shipped_quantity,
v.wms_enabled_flag,
-1,
g_incre_start_date,
-1,
g_incre_start_date,
-1,
-1,
-1,
g_incre_start_date,
-1);
(select new.*
from (select tmp.delivery_leg_id DELIVERY_LEG_ID,
tmp.carrier_id CARRIER_ID,
tmp.shipment_direction SHIPMENT_DIRECTION,
tmp.mode_of_transport MODE_OF_TRANSPORT,
tmp.organization_id ORGANIZATION_ID,
tmp.service_level SERVICE_LEVEL,
tmp.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
tmp.delivery_id DELIVERY_ID,
tmp.drop_off_stop_id DROP_OFF_STOP_ID,
tmp.freight_cost_trx
* nvl(tmp.conversion_rate, curr.trx_wh_rate) FREIGHT_COST_F,
tmp.freight_cost_trx
* decode(tmp.trx_currency_code, g_global_currency, 1, curr.trx_wh_rate * curr.wh_prim_rate) FREIGHT_COST_G,
tmp.freight_cost_trx
* decode(tmp.trx_currency_code, g_sec_global_currency, 1, curr.trx_wh_rate * curr.wh_sec_rate) FREIGHT_COST_G1,
tmp.freight_volume_trx * v_rates.conversion_rate FREIGHT_VOLUME_G,
tmp.freight_volume_trx FREIGHT_VOLUME_TRX,
tmp.freight_weight_trx * w_rates.conversion_rate FREIGHT_WEIGHT_G,
tmp.freight_weight_trx FREIGHT_WEIGHT_TRX,
tmp.pick_up_stop_id PICK_UP_STOP_ID,
tmp.trip_id TRIP_ID,
tmp.volume_uom_code VOLUME_UOM_CODE,
tmp.weight_uom_code WEIGHT_UOM_CODE,
tmp.delivery_type DELIVERY_TYPE,
tmp.parent_delivery_leg_id PARENT_DELIVERY_LEG_ID
from isc_dbi_tmp_del_legs tmp,
isc_dbi_fte_curr_rates curr,
isc_dbi_fte_uom_rates w_rates,
isc_dbi_fte_uom_rates v_rates
where tmp.weight_uom_code = w_rates.from_uom_code(+)
and w_rates.measure_code(+) = 'WT'
and tmp.volume_uom_code = v_rates.from_uom_code(+)
and v_rates.measure_code(+) = 'VOL'
and tmp.trx_currency_code = curr.trx_currency_code(+)
and tmp.wh_currency_code = curr.wh_currency_code(+)
and tmp.conversion_date = curr.conversion_date(+)
and tmp.conversion_type_code = curr.conversion_type_code(+)
and tmp.batch_id = v_batch_id) new, isc_dbi_del_legs_f old
where new.delivery_leg_id = old.delivery_leg_id(+)
and (old.delivery_leg_id is null
or new.carrier_id <> old.carrier_id
or new.shipment_direction <> old.shipment_direction
or new.mode_of_transport <> old.mode_of_transport
or new.organization_id <> old.organization_id
or new.service_level <> old.service_level
or new.time_init_dept_date_id <> old.time_init_dept_date_id
or new.delivery_id <> old.delivery_id
or new.drop_off_stop_id <> old.drop_off_stop_id
or nvl(new.freight_cost_f,-1) <> nvl(old.freight_cost_f,-1)
or nvl(new.freight_cost_g,-1) <> nvl(old.freight_cost_g,-1)
or nvl(new.freight_cost_g1,-1) <> nvl(old.freight_cost_g1,-1)
or nvl(new.freight_volume_g,-1) <> nvl(old.freight_volume_g,-1)
or nvl(new.freight_volume_trx,-1) <> nvl(old.freight_volume_trx,-1)
or nvl(new.freight_weight_g,-1) <> nvl(old.freight_weight_g,-1)
or nvl(new.freight_weight_trx,-1) <> nvl(old.freight_weight_trx,-1)
or new.pick_up_stop_id <> old.pick_up_stop_id
or new.trip_id <> old.trip_id
or nvl(new.volume_uom_code,'na') <> nvl(old.volume_uom_code,'na')
or nvl(new.weight_uom_code,'na') <> nvl(old.weight_uom_code,'na')
or nvl(new.delivery_type,'na') <> nvl(old.delivery_type,'na')
or nvl(new.parent_delivery_leg_id,-1) <> nvl(old.parent_delivery_leg_id,-1))) v
ON (f.delivery_leg_id = v.delivery_leg_id)
WHEN MATCHED THEN UPDATE SET
f.carrier_id = v.carrier_id,
f.shipment_direction = v.shipment_direction,
f.mode_of_transport = v.mode_of_transport,
f.organization_id = v.organization_id,
f.service_level = v.service_level,
f.time_init_dept_date_id = v.time_init_dept_date_id,
f.delivery_id = v.delivery_id,
f.drop_off_stop_id = v.drop_off_stop_id,
f.freight_cost_f = v.freight_cost_f,
f.freight_cost_g = v.freight_cost_g,
f.freight_cost_g1 = v.freight_cost_g1,
f.freight_volume_g = v.freight_volume_g,
f.freight_volume_trx = v.freight_volume_trx,
f.freight_weight_g = v.freight_weight_g,
f.freight_weight_trx = v.freight_weight_trx,
f.pick_up_stop_id = v.pick_up_stop_id,
f.trip_id = v.trip_id,
f.volume_uom_code = v.volume_uom_code,
f.weight_uom_code = v.weight_uom_code,
f.last_update_date = g_incre_start_date,
f.delivery_type = v.delivery_type,
f.parent_delivery_leg_id = v.parent_delivery_leg_id
WHEN NOT MATCHED THEN INSERT(
f.delivery_leg_id,
f.carrier_id,
f.shipment_direction,
f.mode_of_transport,
f.organization_id,
f.service_level,
f.time_init_dept_date_id,
f.delivery_id,
f.drop_off_stop_id,
f.freight_cost_f,
f.freight_cost_g,
f.freight_cost_g1,
f.freight_volume_g,
f.freight_volume_trx,
f.freight_weight_g,
f.freight_weight_trx,
f.pick_up_stop_id,
f.trip_id,
f.volume_uom_code,
f.weight_uom_code,
f.created_by,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.last_update_login,
f.program_application_id,
f.program_id,
f.program_update_date,
f.request_id,
f.delivery_type,
f.parent_delivery_leg_id)
VALUES (
v.delivery_leg_id,
v.carrier_id,
v.shipment_direction,
v.mode_of_transport,
v.organization_id,
v.service_level,
v.time_init_dept_date_id,
v.delivery_id,
v.drop_off_stop_id,
v.freight_cost_f,
v.freight_cost_g,
v.freight_cost_g1,
v.freight_volume_g,
v.freight_volume_trx,
v.freight_weight_g,
v.freight_weight_trx,
v.pick_up_stop_id,
v.trip_id,
v.volume_uom_code,
v.weight_uom_code,
-1,
g_incre_start_date,
-1,
g_incre_start_date,
-1,
-1,
-1,
g_incre_start_date,
-1,
v.delivery_type,
v.parent_delivery_leg_id);
(select new.*
from (select v.stop_id STOP_ID,
v.carrier_id CARRIER_ID,
v.mode_of_transport MODE_OF_TRANSPORT,
v.service_level SERVICE_LEVEL,
v.time_actl_arrl_date_id TIME_ACTL_ARRL_DATE_ID,
v.time_init_dept_date_id TIME_INIT_DEPT_DATE_ID,
v.time_pln_arrl_date_id TIME_PLN_ARRL_DATE_ID,
v.actual_arrival_date ACTUAL_ARRIVAL_DATE,
v.actual_departure_date ACTUAL_DEPARTURE_DATE,
v.distance_to_next_stop_trx * d_rates.conversion_rate DISTANCE_TO_NEXT_STOP_G,
v.distance_to_next_stop_trx DISTANCE_TO_NEXT_STOP_TRX,
v.distance_uom_code DISTANCE_UOM_CODE,
v.planned_arrival_date PLANNED_ARRIVAL_DATE,
v.stop_rank STOP_RANK,
v.stop_sequence_number STOP_SEQUENCE_NUMBER,
itr.trip_freight_cost_g TRIP_FREIGHT_COST_G,
itr.trip_freight_cost_g1 TRIP_FREIGHT_COST_G1,
v.trip_id TRIP_ID,
v.ultimate_stop_sequence_number ULTIMATE_STOP_SEQUENCE_NUMBER
from isc_dbi_tmp_trip_stops v,
(select trip_id,
sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
* decode(tmp.trx_currency_code,g_global_currency,1,curr.trx_wh_rate * curr.wh_prim_rate)) TRIP_FREIGHT_COST_G,
sum(decode(tmp.parent_delivery_leg_id,null,freight_cost_trx,decode(tmp.delivery_type,'CONSOLIDATION',freight_cost_trx,0))
* decode(tmp.trx_currency_code,g_sec_global_currency,1,curr.trx_wh_rate*curr.wh_sec_rate)) TRIP_FREIGHT_COST_G1
from isc_dbi_tmp_del_legs tmp,
isc_dbi_fte_curr_rates curr
where tmp.trx_currency_code = curr.trx_currency_code(+)
and tmp.wh_currency_code = curr.wh_currency_code(+)
and tmp.conversion_date = curr.conversion_date(+)
and tmp.conversion_type_code = curr.conversion_type_code(+)
group by trip_id) itr,
isc_dbi_fte_uom_rates d_rates
where v.trip_id = itr.trip_id
and v.distance_uom_code = d_rates.from_uom_code(+)
and d_rates.measure_code(+) = 'DIS'
and v.batch_id = v_batch_id) new, isc_dbi_trip_stops_f old
where new.stop_id = old.stop_id(+)
and (old.stop_id is null
or new.carrier_id <> old.carrier_id
or new.mode_of_transport <> old.mode_of_transport
or new.service_level <> old.service_level
or new.time_init_dept_date_id <> old.time_init_dept_date_id
or nvl(new.actual_arrival_date, l_date) <> nvl(old.actual_arrival_date, l_date)
or nvl(new.actual_departure_date, l_date) <> nvl(old.actual_departure_date, l_date)
or nvl(new.distance_to_next_stop_g, -1) <> nvl(old.distance_to_next_stop_g, -1)
or nvl(new.distance_to_next_stop_trx, -1) <> nvl(old.distance_to_next_stop_trx, -1)
or nvl(new.distance_uom_code, 'na') <> nvl(old.distance_uom_code, 'na')
or new.planned_arrival_date <> old.planned_arrival_date
or new.stop_rank <> old.stop_rank
or new.stop_sequence_number <> old.stop_sequence_number
or nvl(new.trip_freight_cost_g, -1) <> nvl(old.trip_freight_cost_g, -1)
or nvl(new.trip_freight_cost_g1, -1) <> nvl(old.trip_freight_cost_g1, -1)
or new.trip_id <> old.trip_id
or new.ultimate_stop_sequence_number <> old.ultimate_stop_sequence_number)) v
ON (f.stop_id = v.stop_id)
WHEN MATCHED THEN UPDATE SET
f.carrier_id = v.carrier_id,
f.mode_of_transport = v.mode_of_transport,
f.service_level = v.service_level,
f.time_actl_arrl_date_id = v.time_actl_arrl_date_id,
f.time_init_dept_date_id = v.time_init_dept_date_id,
f.time_pln_arrl_date_id = v.time_pln_arrl_date_id,
f.actual_arrival_date = v.actual_arrival_date,
f.actual_departure_date = v.actual_departure_date,
f.distance_to_next_stop_g = v.distance_to_next_stop_g,
f.distance_to_next_stop_trx = v.distance_to_next_stop_trx,
f.distance_uom_code = v.distance_uom_code,
f.planned_arrival_date = v.planned_arrival_date,
f.stop_rank = v.stop_rank,
f.stop_sequence_number = v.stop_sequence_number,
f.trip_freight_cost_g = v.trip_freight_cost_g,
f.trip_freight_cost_g1 = v.trip_freight_cost_g1,
f.trip_id = v.trip_id,
f.ultimate_stop_sequence_number = v.ultimate_stop_sequence_number,
f.last_update_date = g_incre_start_date
WHEN NOT MATCHED THEN INSERT(
f.stop_id,
f.carrier_id,
f.mode_of_transport,
f.service_level,
f.time_actl_arrl_date_id,
f.time_init_dept_date_id,
f.time_pln_arrl_date_id,
f.actual_arrival_date,
f.actual_departure_date,
f.distance_to_next_stop_g,
f.distance_to_next_stop_trx,
f.distance_uom_code,
f.planned_arrival_date,
f.stop_rank,
f.stop_sequence_number,
f.trip_freight_cost_g,
f.trip_freight_cost_g1,
f.trip_id,
f.ultimate_stop_sequence_number,
f.created_by,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.last_update_login,
f.program_application_id,
f.program_id,
f.program_update_date,
f.request_id)
VALUES (
v.stop_id,
v.carrier_id,
v.mode_of_transport,
v.service_level,
v.time_actl_arrl_date_id,
v.time_init_dept_date_id,
v.time_pln_arrl_date_id,
v.actual_arrival_date,
v.actual_departure_date,
v.distance_to_next_stop_g,
v.distance_to_next_stop_trx,
v.distance_uom_code,
v.planned_arrival_date,
v.stop_rank,
v.stop_sequence_number,
v.trip_freight_cost_g,
v.trip_freight_cost_g1,
v.trip_id,
v.ultimate_stop_sequence_number,
-1,
g_incre_start_date,
-1,
g_incre_start_date,
-1,
-1,
-1,
g_incre_start_date,
-1);
(select new.*
from (select tmp.invoice_header_id INVOICE_HEADER_ID,
tmp.carrier_id CARRIER_ID,
tmp.mode_of_transport MODE_OF_TRANSPORT,
tmp.org_id ORG_ID,
tmp.service_level SERVICE_LEVEL,
tmp.supplier_id SUPPLIER_ID,
tmp.approved_amt_trx * curr.trx_wh_rate APPROVED_AMT_F,
tmp.approved_amt_trx
* decode(tmp.trx_currency_code, g_global_currency, 1,
curr.trx_wh_rate * curr.wh_prim_rate) APPROVED_AMT_G,
tmp.approved_amt_trx
* decode(tmp.trx_currency_code, g_sec_global_currency, 1,
curr.trx_wh_rate * curr.wh_sec_rate) APPROVED_AMT_G1,
tmp.bill_amt_trx * curr.trx_wh_rate BILL_AMT_F,
tmp.bill_amt_trx
* decode(tmp.trx_currency_code, g_global_currency, 1,
curr.trx_wh_rate * curr.wh_prim_rate) BILL_AMT_G,
tmp.bill_amt_trx
* decode(tmp.trx_currency_code, g_sec_global_currency, 1,
curr.trx_wh_rate * curr.wh_sec_rate) BILL_AMT_G1,
tmp.bill_number BILL_NUMBER,
tmp.bill_status BILL_STATUS,
tmp.bill_type BILL_TYPE,
tmp.bol BOL,
tmp.delivery_leg_id DELIVERY_LEG_ID,
tmp.trip_id TRIP_ID
from isc_dbi_tmp_fte_invoices tmp, isc_dbi_fte_curr_rates curr
where tmp.trx_currency_code = curr.trx_currency_code
and tmp.wh_currency_code = curr.wh_currency_code
and tmp.conversion_date = curr.conversion_date
and tmp.conversion_type_code = curr.conversion_type_code
and tmp.batch_id = v_batch_id) new,
isc_dbi_fte_invoices_f old
where new.invoice_header_id = old.invoice_header_id(+)
and (old.invoice_header_id is null
or new.carrier_id <> old.carrier_id
or new.mode_of_transport <> old.mode_of_transport
or new.org_id <> old.org_id
or new.service_level <> old.service_level
or nvl(new.supplier_id, -1) <> nvl(old.supplier_id, -1)
or nvl(new.approved_amt_f, -1) <> nvl(old.approved_amt_f, -1)
or nvl(new.approved_amt_g, -1) <> nvl(old.approved_amt_g, -1)
or nvl(new.approved_amt_g1, -1) <> nvl(old.approved_amt_g1, -1)
or nvl(new.bill_amt_f, -1) <> nvl(old.bill_amt_f, -1)
or nvl(new.bill_amt_g, -1) <> nvl(old.bill_amt_g, -1)
or nvl(new.bill_amt_g1, -1) <> nvl(old.bill_amt_g1, -1)
or new.bill_number <> old.bill_number
or new.bill_status <> old.bill_status
or nvl(new.bill_type, 'na') <> nvl(old.bill_type, 'na')
or nvl(new.bol, 'na') <> nvl(old.bol,'na')
or nvl(new.delivery_leg_id, -1) <> nvl(old.delivery_leg_id, -1)
or new.trip_id <> old.trip_id)) v
ON (f.invoice_header_id = v.invoice_header_id)
WHEN MATCHED THEN UPDATE SET
f.carrier_id = v.carrier_id,
f.mode_of_transport = v.mode_of_transport,
f.org_id = v.org_id,
f.service_level = v.service_level,
f.supplier_id = v.supplier_id,
f.approved_amt_f = v.approved_amt_f,
f.approved_amt_g = v.approved_amt_g,
f.approved_amt_g1 = v.approved_amt_g1,
f.bill_amt_f = v.bill_amt_f,
f.bill_amt_g = v.bill_amt_g,
f.bill_amt_g1 = v.bill_amt_g1,
f.bill_number = v.bill_number,
f.bill_status = v.bill_status,
f.bill_type = v.bill_type,
f.bol = v.bol,
f.delivery_leg_id = v.delivery_leg_id,
f.trip_id = v.trip_id,
f.last_update_date = g_incre_start_date
WHEN NOT MATCHED THEN INSERT(
f.invoice_header_id,
f.carrier_id,
f.mode_of_transport,
f.org_id,
f.service_level,
f.supplier_id,
f.approved_amt_f,
f.approved_amt_g,
f.approved_amt_g1,
f.bill_amt_f,
f.bill_amt_g,
f.bill_amt_g1,
f.bill_number,
f.bill_status,
f.bill_type,
f.bol,
f.delivery_leg_id,
f.trip_id,
f.created_by,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.last_update_login,
f.program_application_id,
f.program_id,
f.program_update_date,
f.request_id)
VALUES (
v.invoice_header_id,
v.carrier_id,
v.mode_of_transport,
v.org_id,
v.service_level,
v.supplier_id,
v.approved_amt_f,
v.approved_amt_g,
v.approved_amt_g1,
v.bill_amt_f,
v.bill_amt_g,
v.bill_amt_g1,
v.bill_number,
v.bill_status,
v.bill_type,
v.bol,
v.delivery_leg_id,
v.trip_id,
-1,
g_incre_start_date,
-1,
g_incre_start_date,
-1,
-1,
-1,
g_incre_start_date,
-1);
IF (UPDATE_PARAMETER_TABLE = -1) THEN
RAISE l_failure;
g_row_count := Insert_fact;
Procedure update_detail_fact(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
l_failure EXCEPTION;
BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
IF (UPDATE_PARAMETER_TABLE = -1) THEN
RAISE l_failure;
DELETE FROM ISC_DBI_WDD_CHANGE_LOG
WHERE rowid IN (select log_rowid from isc_dbi_tmp_wdd_log);
FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_WDD_CHANGE_LOG in');
END update_detail_fact;
Procedure update_leg_stop_fact(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
l_failure EXCEPTION;
BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
IF (UPDATE_PARAMETER_TABLE = -1) THEN
RAISE l_failure;
DELETE FROM ISC_DBI_WTS_CHANGE_LOG
WHERE rowid IN (select log_rowid from isc_dbi_tmp_wts_log);
FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_WTS_CHANGE_LOG in');
END update_leg_stop_fact;
Procedure update_invoice_fact(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
l_failure EXCEPTION;
BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
IF (UPDATE_PARAMETER_TABLE = -1) THEN
RAISE l_failure;
DELETE FROM ISC_DBI_FIH_CHANGE_LOG
WHERE rowid IN (select log_rowid from isc_dbi_tmp_fih_log);
FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_FIH_CHANGE_LOG in');
END update_invoice_fact;