[Home] [Help]
188: -- Truncates all the staging Tables, LOG Table and the FACT Tables as well.
189: -- As these are truncate statements this procedure does a commit.
190: -- Common/Misc Tables:
191: -- OPI_DBI_OPM_INV_LED_CURRENT
192: -- OPI_DBI_OPM_INV_STG
193: -- OPI_DBI_CONVERSION_RATES
194: -- OPI_DBI_INV_ITEM_COSTS_TMP
195: -- OPI_PMI_COST_PARAM_GTMP
196: -- OPI_DBI_INV_VALUE_LOG -- added as part of CPCS Change.
234: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
235: END IF;
236:
237: l_stmt_num := 20;
238: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_OPM_INV_STG';
239:
240: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
241: l_debug_msg := 'OPI_DBI_OPM_INV_STG table truncated';
242: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
237: l_stmt_num := 20;
238: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_OPM_INV_STG';
239:
240: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
241: l_debug_msg := 'OPI_DBI_OPM_INV_STG table truncated';
242: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
243: END IF;
244:
245: l_stmt_num := 30;
871: ,stg2.subinventory_code
872: ,-1 cost_group_id -- there is no cost_group_id required for opm items for finding cost
873: ,stg2.inventory_item_id
874: ,-1*stg2.onhand_qty
875: FROM opi_dbi_opm_inv_stg stg2) balance
876: GROUP BY balance.organization_id
877: ,balance.subinventory_code
878: ,balance.cost_group_id
879: ,balance.inventory_item_id;
1295: SELECT organization_id,
1296: inventory_item_id,
1297: -1 * intransit_qty tot_prim_qty,
1298: -1 cost_group_id
1299: FROM OPI_DBI_OPM_INV_STG) xy
1300: GROUP BY inventory_item_id,
1301: organization_id,
1302: cost_group_id,
1303: g_global_start_date
1392: ,stg2.subinventory_code
1393: ,-1 cost_group_id
1394: ,stg2.inventory_item_id
1395: ,-1*stg2.wip_value_b
1396: FROM opi_dbi_opm_inv_stg stg2) balance
1397: GROUP BY balance.organization_id
1398: ,balance.subinventory_code
1399: ,balance.cost_group_id
1400: ,balance.inventory_item_id;
2606: FROM OPI_DBI_INV_BEG_STG fact,
2607: mtl_parameters mp
2608: WHERE fact.organization_id =mp.organization_id
2609: union all
2610: select /*+ parallel(OPI_DBI_OPM_INV_STG) */
2611: organization_id,
2612: subinventory_code,
2613: inventory_item_id,
2614: transaction_date,
2623: 0 onhand_value_b_draft,
2624: 0 intransit_value_b_draft,
2625: 0 wip_value_b_draft,
2626: 3 source
2627: FROM opi_dbi_opm_inv_stg
2628: ) activity,
2629: mtl_system_items msi
2630: WHERE activity.organization_id = msi.organization_id
2631: AND activity.inventory_item_id =msi.inventory_item_id
2968: END IF;
2969: -- This procedure merges data from various
2970: -- staging tables populated in initial load.
2971: -- 1. OPI_DBI_ONH_QTY_STG -- onhand quantity
2972: -- 2. opi_dbi_opm_inv_stg -- Pre r12 OPM DATA
2973: -- 3. OPI_DBI_INTRANSIT_STG -- Intransit Qty and Value
2974: -- 4. OPI_DBI_INV_BEG_STG -- Inception qty and value
2975: -- 5. OPI_DBI_ONHAND_STG -- On hand value
2976: -- 6. OPI_DBI_CONVERSION_RATES -- currency conversion rates