[Home] [Help]
355: s_disposition_id number_idx_tbl;
356:
357: s_order_type number_idx_tbl;
358:
359: TYPE order_number IS TABLE OF msc_st_supplies.order_number%TYPE
360: INDEX BY BINARY_INTEGER;
361: s_order_number order_number ;
362: empty_sorder_number order_number ;
363:
365: s_old_order_quantity number_idx_tbl;
366: s_firm_planned_type number_idx_tbl;
367: s_process_seq_id number_idx_tbl ; -- Bug 8349005 Vpedarla
368:
369: TYPE wip_entity_name IS TABLE OF msc_st_supplies.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
370: s_wip_entity_name wip_entity_name ;
371: empty_swip_entity_name wip_entity_name ;
372:
373: TYPE lot_number IS TABLE OF msc_st_supplies.lot_number%TYPE INDEX BY BINARY_INTEGER;
369: TYPE wip_entity_name IS TABLE OF msc_st_supplies.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
370: s_wip_entity_name wip_entity_name ;
371: empty_swip_entity_name wip_entity_name ;
372:
373: TYPE lot_number IS TABLE OF msc_st_supplies.lot_number%TYPE INDEX BY BINARY_INTEGER;
374: s_lot_number lot_number ;
375:
376: s_expiration_date date_idx_tbl;
377: s_firm_quantity number_idx_tbl;
774: * DESCRIPTION
775: * This procedure will take the production orders, batches and FPOs,
776: * that have valid item/warehouse definitions as defined in the
777: * the plant/whse eff and write them to the table msc_std_demands and \
778: * msc_st_supplies. The products and byproducts will be written as
779: * supplies and ingredients as demands
780: * HISTORY
781: * M Craig
782: * 04/03/2000 - Using mtl_organization_id instead of organization_id from
793: * Resource Seq is incremented only if the
794: * activity is Changed
795: * Sridhar 31-MAR-2003 B2882286 Ensuring the Order so that if the last batch
796: * resource requirements are written
797: * Sridhar 30-APR-2003 B2919303 Added Operation Seq Number in msc_st_supplies
798: * and in msc_st_demands table
799: * Sridhar 09-MAY-2003 B2919303 Added line_no and included in order by clause
800: * Sridhar 12-MAY-2003 B2953953 Populated BY_PRODUCT_USING_ASSY_ID with
801: * product_line which is the assembly_item_id
962: || ';END;' ;
963: EXECUTE IMMEDIATE v_sql_stmt ;
964:
965: BEGIN -- B8349005 Vpedarla
966: select NVL(max(process_seq_id),0) into l_process_seq_id from msc_st_supplies
967: where sr_instance_id = pinstance_id ;
968: log_message('production_orders pinstance_id = ' || pinstance_id || ' AND l_process_seq_id = ' || l_process_seq_id );
969: EXCEPTION
970: WHEN no_data_found then
2455: i := 1 ;
2456: log_message(s_organization_id.FIRST || ' *s*' || s_organization_id.LAST );
2457: IF s_organization_id.FIRST > 0 THEN
2458: FORALL i IN s_organization_id.FIRST..s_organization_id.LAST
2459: INSERT INTO msc_st_supplies (
2460: plan_id,
2461: inventory_item_id,
2462: organization_id,
2463: sr_instance_id,
3039: * insert_supplies
3040: *
3041: * DESCRIPTION
3042: * This procedure will take the parameter values and insert a row into
3043: * the table msc_st_supplies
3044: * HISTORY
3045: * M Craig
3046: * 2/10/2000 - Populating Order number column with Wip Entity Name ( porder_no )
3047: * 2/24/2003 - populating Firmed batches Indicator, Qty and Date
3068: vproduct_item_id NUMBER ; /* B2953953 - CoProduct */
3069: BEGIN
3070:
3071: st_supplies :=
3072: ' INSERT INTO msc_st_supplies ( '
3073: ||' plan_id, inventory_item_id, organization_id, sr_instance_id, '
3074: ||' new_schedule_date, old_schedule_date, new_wip_start_date, '
3075: ||' old_wip_start_date, last_unit_completion_date, disposition_id, '
3076: ||' order_type, order_number, new_order_quantity, old_order_quantity, '
3122: 2 ;
3123:
3124: EXCEPTION
3125: WHEN OTHERS THEN
3126: log_message('Failure occured during the insert into msc_st_supplies');
3127: log_message(sqlerrm);
3128: RAISE;
3129:
3130: END insert_supplies;
3327: * NAME
3328: * onhand_inventory
3329: *
3330: * DESCRIPTION
3331: * This procedure will insert records into the table msc_st_supplies
3332: * for the onhand balances in inventory. The insert is split into 3 parts
3333: * one for non-lot controlled, lot controlled, and lot and status
3334: * controlled item. Each inserted will need touse a distnct list from
3335: * the table gmp_item_aps. The table may contain multiple values for
3410: * NAME
3411: * extract_onhand_balances
3412: *
3413: * DESCRIPTION
3414: * This procedure will insert records into the table msc_st_supplies
3415: * for the onhand balances in inventory. The insert is split into 3 parts
3416: * one for non-lot controlled, lot controlled, and lot and status
3417: * controlled item. Each inserted will need touse a distnct list from
3418: * the table gmp_item_aps. The table may contain multiple values for
3444: onhand information of items in different lots
3445:
3446: The three select statements are replaced by a single select statement. */
3447:
3448: v_sql_stmt := ' INSERT into msc_st_supplies ( '
3449: || ' plan_id, '
3450: || ' inventory_item_id, '
3451: || ' organization_id, '
3452: || ' sr_instance_id, '
3466: || ' :pinstance_id, '
3467: || ' NVL(mln.hold_date, :prun_date), ' /* Confirm : should we have hold date here. */
3468: || ' :prun_date, '
3469: || ' 18, ' /* onhand inventory value */
3470: /* Discrete Lot and parent lot are now 80 chars long. Lot_number in msc_st_supplies is 30 chars long.
3471: Hence there could be a problem as the lot number is the pkey in mtl_lot_numbers. */
3472: || ' substrb(DECODE(mln.parent_lot_number, NULL, '', mln.parent_lot_number||:pdelimiter) '
3473: || ' ||mln.lot_number, 1, 30), '
3474: || ' mln.expiration_date, '
3496: /* Query to select the production order details where the batch/fpo is pending
3497: the balances from ic_summ for the item/whse that are not lot controlled
3498: are inserted */
3499: /*
3500: v_sql_stmt := 'INSERT into msc_st_supplies ('
3501: || ' plan_id,'
3502: || ' inventory_item_id,'
3503: || ' organization_id,'
3504: || ' sr_instance_id,'
3537: items. The lot can not be status controlled, that will be in the next
3538: insert the lot number is the combo of lot and sublot
3539: */
3540: /*
3541: v_sql_stmt := 'INSERT into msc_st_supplies ('
3542: || ' plan_id,'
3543: || ' inventory_item_id,'
3544: || ' organization_id,'
3545: || ' sr_instance_id,'
3602:
3603: /* B2623374 -- Rajesh Patangya PORT BUG FOR 2446925 (OM ATP CHECK TO
3604: RECOGNIZE THE ORDER PROCESSING FLAG) */
3605: /*
3606: v_sql_stmt := 'INSERT into msc_st_supplies ('
3607: || ' plan_id,'
3608: || ' inventory_item_id,'
3609: || ' organization_id,'
3610: || ' sr_instance_id,'
3773: * NAME
3774: * Extract_inventory_transfer_supplies
3775: *
3776: * DESCRIPTION
3777: * This procedure will insert records into the table msc_st_supplies
3778: * and msc_st_demands for pending inventory transfers.
3779: * HISTORY
3780: * 25-Jan-2003 B1332662 Created New procedure to insert supplies
3781: * Per discussions with APS team the specifics are
3796: pdoc_type := 'XFER';
3797:
3798: return_status := TRUE ;
3799:
3800: v_sql_stmt := 'INSERT into msc_st_supplies ('
3801: || ' plan_id,'
3802: || ' inventory_item_id,'
3803: || ' organization_id,'
3804: || ' sr_instance_id,'