[Home] [Help]
203: -- WIP Tables
204: -- OPI_DBI_WIP_STG
205: -- Onhand WIP Tables
206: -- OPI_DBI_ONH_QTY_STG
207: -- OPI_DBI_INV_BEG_STG
208: -- OPI_DBI_ONHAND_STG
209: -- IF p_stage = 'PRE_INIT' THEN
210: -- Cleanup the OPI_DBI_INV_VALUE_F
211: PROCEDURE clean_staging_tables(p_stage varchar2)
316: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
317: END IF;
318:
319: l_stmt_num := 120;
320: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_BEG_STG';
321:
322: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
323: l_debug_msg := 'OPI_DBI_INV_BEG_STG table truncated.';
324: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
319: l_stmt_num := 120;
320: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_BEG_STG';
321:
322: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
323: l_debug_msg := 'OPI_DBI_INV_BEG_STG table truncated.';
324: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
325: END IF;
326:
327: l_stmt_num := 130;
394: -- null, then replace it with default cost group.
395:
396: l_stmt_num :=10;
397: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
398: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG from inception date less than gsd';
399: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
400: l_debug_msg := 'for standard costing organization';
401: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
402: END IF;
401: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
402: END IF;
403:
404:
405: UPDATE OPI_DBI_INV_BEG_STG fact
406: SET (onhand_value_b ,intransit_value_b) =
407: (SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
408: -- ideally max is not required as standard cost
409: -- revision date is timestamp.
446: csc2.cost_method,
447: csc2.cost_group_id)
448: where ( nvl(fact.onhand_qty,0) <> 0 or nvl(fact.intransit_qty,0) <> 0 );
449:
450: l_debug_msg := 'Updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
451: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
452:
453: l_stmt_num := 20;
454: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
451: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
452:
453: l_stmt_num := 20;
454: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
455: l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG from inception date greater than gsd';
456: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
457: l_debug_msg := 'for standard costing organization';
458: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
459: END IF;
473: -- are excluded from here. scrap, lot merge, lot split, logical
474: -- and lot qty update. Avg cost and layer cost updates,
475: -- container pack, unpack, split.
476:
477: UPDATE OPI_DBI_INV_BEG_STG fact
478: SET (onhand_value_b ,intransit_value_b) =
479: (SELECT /*+ NO_MERGE, leading(mmt1) */
480: mmt2.prior_cost * onhand_qty onhand_value_b,
481: mmt2.prior_cost *intransit_qty intransit_value_b
485: mmt.inventory_item_id,
486: NULL cost_group_id, --RS: Bug fix 5219487 nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
487: min(mmt.transaction_id) trx_id
488: FROM mtl_material_transactions mmt,
489: OPI_DBI_INV_BEG_STG stg,
490: mtl_parameters p
491: WHERE primary_cost_method = 1
492: AND stg.organization_id = p.organization_id
493: AND stg.inventory_item_id = mmt.inventory_item_id
522: )
523: where ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
524: or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
525:
526: l_debug_msg := 'Updating staging table table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
527: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
528:
529: l_stmt_num := 30;
530: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
527: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
528:
529: l_stmt_num := 30;
530: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
531: l_debug_msg := 'updated data into OPI_DBI_INV_BEG_STG for standard costing organization';
532: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
533: l_debug_msg := 'form cst_item_costs for which cost is not found till now';
534: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
535: END IF;
539: -- as mentioned in step2 for the items where there are no
540: -- cost updates prior and post GSD the item cost is available
541: -- in CIC. Hence for such items update the cost.
542:
543: UPDATE OPI_DBI_INV_BEG_STG fact
544: SET (onhand_value_b ,intransit_value_b) =
545: (SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
546: nvl(csc.item_cost,0) *onhand_qty onhand_value_b,
547: nvl(csc.item_cost,0) *intransit_qty intransit_value_b
551: And csc.cost_type_id =1 )
552: WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
553: or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
554:
555: l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
556: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
557:
558: l_stmt_num := 40;
559: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
556: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
557:
558: l_stmt_num := 40;
559: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
560: l_debug_msg := 'updating data into OPI_DBI_INV_BEG_STG for Non standard costing organization';
561: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
562: END IF;
563:
564:
564:
565: -- Cost update for average costing orgs.
566: -- get a txn after GSD and prior cost on this txn is the
567: -- item cost as of the GSD for average costing org.
568: UPDATE OPI_DBI_INV_BEG_STG fact
569: SET (onhand_value_b ,intransit_value_b) =
570: (SELECT /*+ NO_MERGE, leading(mmt1) */
571: mmt2.prior_cost * onhand_qty onhand_value_b,
572: mmt2.prior_cost *intransit_qty intransit_value_b
577: nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
578: p.primary_cost_method cost_method,
579: min(mmt.transaction_id) trx_id
580: FROM mtl_material_transactions mmt,
581: OPI_DBI_INV_BEG_STG stg,
582: mtl_parameters p
583: WHERE primary_cost_method <> 1
584: AND stg.organization_id = p.organization_id
585: AND stg.inventory_item_id = mmt.inventory_item_id
606: )
607: WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
608: or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
609:
610: l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
611: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
612:
613: l_stmt_num := 50;
614: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
611: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
612:
613: l_stmt_num := 50;
614: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
615: l_debug_msg := 'updating data into OPI_DBI_INV_BEG_STG for Non standard costing organization';
616: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
617: END IF;
618:
619:
621: -- In case there is no txn found in MMT after GSD then get the
622: -- cost from CQL Table as this mean there are no cost updates
623: -- after GSD.
624: -- item, org and cost_group is unique in this table.
625: UPDATE OPI_DBI_INV_BEG_STG fact
626: SET (onhand_value_b ,intransit_value_b) =
627: (SELECT
628: nvl(cst.item_cost,0) * onhand_qty onhand_value_b,
629: nvl(cst.item_cost,0) * intransit_qty intransit_value_b
640: WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0 )
641: OR (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
642:
643:
644: l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
645: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
646:
647: commit;
648:
740: SELECT distinct
741: fact.organization_id,
742: fact.inventory_item_id,
743: fact.transaction_date
744: FROM OPI_DBI_INV_BEG_STG fact,
745: mtl_parameters p
746: WHERE fact.organization_id = p.organization_id
747: AND p.process_enabled_flag ='Y'
748: ORDER BY fact.organization_id ,
762: END IF;
763:
764: l_stmt_num :=10;
765: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
766: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Process costing organization';
767: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
768: END IF;
769:
770: /*UPDATE OPI_DBI_INV_BEG_STG fact
766: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Process costing organization';
767: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
768: END IF;
769:
770: /*UPDATE OPI_DBI_INV_BEG_STG fact
771: SET (onhand_value_b ,intransit_value_b, COST_FOUND_FLAG) =
772: (SELECT onhand_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) onhand_value_b,
773: intransit_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) intransit_value_b,
774: NULL
781: l_opm_cost := GET_OPM_ITEM_COST(l_organization_id => opm_org_cost_info.organization_id,
782: l_inventory_item_id => opm_org_cost_info.inventory_item_id,
783: l_txn_date => opm_org_cost_info.transaction_date);
784:
785: UPDATE OPI_DBI_INV_BEG_STG fact
786: SET onhand_value_b =onhand_qty * l_opm_cost
787: ,intransit_value_b = intransit_qty * l_opm_cost
788: where fact.organization_id =opm_org_cost_info.organization_id
789: AND fact.inventory_item_id =opm_org_cost_info.inventory_item_id;
791: l_row_count := l_row_count + SQL%ROWCOUNT ;
792:
793: END LOOP;
794:
795: l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG for OPM - ' || l_row_count || ' rows. ';
796: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
797:
798: commit;
799:
813:
814:
815: -------------------------------------------------------------------------------
816: --This procedure will provide on hand values
817: -- in table OPI_DBI_INV_BEG_STG
818:
819: PROCEDURE GET_ONHAND_BALANCE
820: IS
821: l_stmt_num NUMBER;
836: END IF;
837:
838: l_stmt_num :=10;
839: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
840: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
841: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
842: END IF;
843:
844: INSERT INTO OPI_DBI_INV_BEG_STG
840: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
841: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
842: END IF;
843:
844: INSERT INTO OPI_DBI_INV_BEG_STG
845: (organization_id
846: ,subinventory_code
847: ,cost_group_id
848: ,inventory_item_id
877: ,balance.subinventory_code
878: ,balance.cost_group_id
879: ,balance.inventory_item_id;
880:
881: l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
882: OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
883:
884: commit;
885:
1182: END IF;
1183:
1184: l_stmt_num := 10;
1185: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1186: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Intransit Inception load ';
1187: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1188: END IF;
1189:
1190: OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion := 1;
1189:
1190: OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion := 1;
1191:
1192: INSERT /*+ append parallel(opi_dbi_intransit_stg) */
1193: INTO OPI_DBI_INV_BEG_STG
1194: ( organization_id ,
1195: inventory_item_id,
1196: subinventory_code,
1197: Cost_group_id,
1305:
1306: if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <>1 then
1307: Raise UOM_CONV_ERROR;
1308: end if;
1309: l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
1310: OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
1311:
1312: commit;
1313:
1350: END IF;
1351:
1352: l_stmt_num :=10;
1353: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1354: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
1355: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1356: END IF;
1357:
1358: INSERT INTO OPI_DBI_INV_BEG_STG
1354: l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
1355: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1356: END IF;
1357:
1358: INSERT INTO OPI_DBI_INV_BEG_STG
1359: (organization_id
1360: ,subinventory_code
1361: ,cost_group_id
1362: ,inventory_item_id
1398: ,balance.subinventory_code
1399: ,balance.cost_group_id
1400: ,balance.inventory_item_id;
1401:
1402: l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
1403: OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
1404:
1405: commit;
1406:
2585: 0 wip_value_b_draft,
2586: source
2587: from OPI_DBI_INTRANSIT_STG
2588: union all
2589: select /*+ parallel(OPI_DBI_INV_BEG_STG) */
2590: fact.organization_id,
2591: fact.subinventory_code,
2592: fact.inventory_item_id,
2593: fact.transaction_date,
2602: 0 onhand_value_b_draft,
2603: 0 intransit_value_b_draft,
2604: 0 wip_value_b_draft,
2605: decode(mp.process_enabled_flag,'Y',2,1) source
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) */
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
2977: OPI_DBI_INV_VALUE_INIT_PKG.merge_initial_load;
2978: