[Home] [Help]
289: /* setup_tables
290:
291: Clean up tables related to the Current Capacity Utilization report.
292: Currently, only need to truncate:
293: 1. opi_dbi_wms_curr_utz_sub_stg
294: 2. opi_dbi_wms_curr_utz_sub_f
295: 1. opi_dbi_wms_curr_utz_item_f
296:
297: History:
309: -- Initialization block
310: l_stmt_id := 0;
311:
312: l_stmt_id := 10;
313: -- Truncate OPI_DBI_WMS_CURR_UTZ_SUB_STG
314: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
315: 'OPI_DBI_WMS_CURR_UTZ_SUB_STG');
316:
317: l_stmt_id := 20;
311:
312: l_stmt_id := 10;
313: -- Truncate OPI_DBI_WMS_CURR_UTZ_SUB_STG
314: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
315: 'OPI_DBI_WMS_CURR_UTZ_SUB_STG');
316:
317: l_stmt_id := 20;
318: -- Truncate OPI_DBI_WMS_CURR_UTZ_SUB_F
319: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
485: l_stmt_id := 130;
486: -- Everything successful so commit and truncate the staging table.
487: commit;
488: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
489: 'OPI_DBI_WMS_CURR_UTZ_SUB_STG');
490: print_stage_done_mesg (l_proc_name, l_stmt_id);
491:
492: errbuf := '';
493: retcode := C_SUCCESS;
595:
596: /* extract_locator_capacities
597:
598: Extract the locator weight and volume capacities in the
599: staging table, OPI_DBI_WMS_CURR_UTZ_SUB_STG. The capacities
600: should be stored in the reporting units of the measures.
601: If the weight or volume capacity is null, store NULL.
602: If the conversion is not defined in the reporting UOM table,
603: OPI_DBI_REP_UOM_STD_CONV_F, store -1.
709: -- joins to the standard conversion rates tables based on these
710: -- from and reporting UOMs.
711: --
712: INSERT /*+ append */
713: INTO opi_dbi_wms_curr_utz_sub_stg (
714: organization_id,
715: subinventory_code,
716: locator_id,
717: weight_capacity_b,
921: s_program_id,
922: s_program_login_id,
923: s_program_application_id,
924: s_request_id
925: FROM opi_dbi_wms_curr_utz_sub_stg stg
926: GROUP BY
927: stg.organization_id,
928: rollup (decode (stg.subinventory_code,
929: NULL, NULL,
968: -- Cursor for missing weight conversions.
969: CURSOR wt_missing_rates_csr IS
970: SELECT DISTINCT
971: weight_uom_code uom_code
972: FROM opi_dbi_wms_curr_utz_sub_stg
973: WHERE weight_capacity_rep < 0;
974:
975: -- Cursor for missing volume conversions.
976: CURSOR vol_missing_rates_csr IS
975: -- Cursor for missing volume conversions.
976: CURSOR vol_missing_rates_csr IS
977: SELECT DISTINCT
978: volume_uom_code uom_code
979: FROM opi_dbi_wms_curr_utz_sub_stg
980: WHERE volume_capacity_rep < 0;
981:
982: BEGIN
983:
1068:
1069: Extract the item storage in all locators of warehouses that
1070: have weight and/or volume capacities defined. The locator
1071: capacities are stored in the locator staging table,
1072: OPI_DBI_WMS_CURR_UTZ_SUB_STG. The item weights/volumes must
1073: be stored in the reporting UOM values. If a locator does
1074: not have weight capacity defined the item's weight in that
1075: locator is ignored. Same goes for the item volume. If the
1076: conversion rate for the weight/volume to the reporting UOMs
1139: -- The item setup form guarantees that a UOM be specified for the
1140: -- item weight/volume before a value is specified. So no need to
1141: -- worry about missing UOMs and specified values.
1142: --
1143: -- The subinventory capacity staging table, OPI_DBI_WMS_CURR_UTZ_SUB_STG,
1144: -- has all the locator level records. Use the values in the reporting
1145: -- UOM value columns of the staging table. NULL values in those
1146: -- columns indicate that the corresponding capacity was not specified
1147: -- for the measure on that locator.
1341: moq.transaction_quantity)) volume_qty
1342: FROM
1343: mtl_onhand_quantities moq,
1344: mtl_parameters mp,
1345: opi_dbi_wms_curr_utz_sub_stg loc,
1346: eni_oltp_item_star items
1347: WHERE mp.wms_enabled_flag = 'Y'
1348: AND moq.organization_id = mp.organization_id
1349: AND moq.organization_id = items.organization_id