[Home] [Help]
349: s_disposition_id number_idx_tbl;
350:
351: s_order_type number_idx_tbl;
352:
353: TYPE order_number IS TABLE OF msc_st_supplies.order_number%TYPE
354: INDEX BY BINARY_INTEGER;
355: s_order_number order_number ;
356: empty_sorder_number order_number ;
357:
358: s_new_order_quantity number_idx_tbl;
359: s_old_order_quantity number_idx_tbl;
360: s_firm_planned_type number_idx_tbl;
361:
362: TYPE wip_entity_name IS TABLE OF msc_st_supplies.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
363: s_wip_entity_name wip_entity_name ;
364: empty_swip_entity_name wip_entity_name ;
365:
366: TYPE lot_number IS TABLE OF msc_st_supplies.lot_number%TYPE INDEX BY BINARY_INTEGER;
362: TYPE wip_entity_name IS TABLE OF msc_st_supplies.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
363: s_wip_entity_name wip_entity_name ;
364: empty_swip_entity_name wip_entity_name ;
365:
366: TYPE lot_number IS TABLE OF msc_st_supplies.lot_number%TYPE INDEX BY BINARY_INTEGER;
367: s_lot_number lot_number ;
368:
369: s_expiration_date date_idx_tbl;
370: s_firm_quantity number_idx_tbl;
767: * DESCRIPTION
768: * This procedure will take the production orders, batches and FPOs,
769: * that have valid item/warehouse definitions as defined in the
770: * the plant/whse eff and write them to the table msc_std_demands and \
771: * msc_st_supplies. The products and byproducts will be written as
772: * supplies and ingredients as demands
773: * HISTORY
774: * M Craig
775: * 04/03/2000 - Using mtl_organization_id instead of organization_id from
786: * Resource Seq is incremented only if the
787: * activity is Changed
788: * Sridhar 31-MAR-2003 B2882286 Ensuring the Order so that if the last batch
789: * resource requirements are written
790: * Sridhar 30-APR-2003 B2919303 Added Operation Seq Number in msc_st_supplies
791: * and in msc_st_demands table
792: * Sridhar 09-MAY-2003 B2919303 Added line_no and included in order by clause
793: * Sridhar 12-MAY-2003 B2953953 Populated BY_PRODUCT_USING_ASSY_ID with
794: * product_line which is the assembly_item_id
2228: i := 1 ;
2229: log_message(s_organization_id.FIRST || ' *s*' || s_organization_id.LAST );
2230: IF s_organization_id.FIRST > 0 THEN
2231: FORALL i IN s_organization_id.FIRST..s_organization_id.LAST
2232: INSERT INTO msc_st_supplies (
2233: plan_id,
2234: inventory_item_id,
2235: organization_id,
2236: sr_instance_id,
2793: * insert_supplies
2794: *
2795: * DESCRIPTION
2796: * This procedure will take the parameter values and insert a row into
2797: * the table msc_st_supplies
2798: * HISTORY
2799: * M Craig
2800: * 2/10/2000 - Populating Order number column with Wip Entity Name ( porder_no )
2801: * 2/24/2003 - populating Firmed batches Indicator, Qty and Date
2822: vproduct_item_id NUMBER ; /* B2953953 - CoProduct */
2823: BEGIN
2824:
2825: st_supplies :=
2826: ' INSERT INTO msc_st_supplies ( '
2827: ||' plan_id, inventory_item_id, organization_id, sr_instance_id, '
2828: ||' new_schedule_date, old_schedule_date, new_wip_start_date, '
2829: ||' old_wip_start_date, last_unit_completion_date, disposition_id, '
2830: ||' order_type, order_number, new_order_quantity, old_order_quantity, '
2876: 2 ;
2877:
2878: EXCEPTION
2879: WHEN OTHERS THEN
2880: log_message('Failure occured during the insert into msc_st_supplies');
2881: log_message(sqlerrm);
2882: RAISE;
2883:
2884: END insert_supplies;
3081: * NAME
3082: * onhand_inventory
3083: *
3084: * DESCRIPTION
3085: * This procedure will insert records into the table msc_st_supplies
3086: * for the onhand balances in inventory. The insert is split into 3 parts
3087: * one for non-lot controlled, lot controlled, and lot and status
3088: * controlled item. Each inserted will need touse a distnct list from
3089: * the table gmp_item_aps. The table may contain multiple values for
3164: * NAME
3165: * extract_onhand_balances
3166: *
3167: * DESCRIPTION
3168: * This procedure will insert records into the table msc_st_supplies
3169: * for the onhand balances in inventory. The insert is split into 3 parts
3170: * one for non-lot controlled, lot controlled, and lot and status
3171: * controlled item. Each inserted will need touse a distnct list from
3172: * the table gmp_item_aps. The table may contain multiple values for
3198: onhand information of items in different lots
3199:
3200: The three select statements are replaced by a single select statement. */
3201:
3202: v_sql_stmt := ' INSERT into msc_st_supplies ( '
3203: || ' plan_id, '
3204: || ' inventory_item_id, '
3205: || ' organization_id, '
3206: || ' sr_instance_id, '
3220: || ' :pinstance_id, '
3221: || ' NVL(mln.hold_date, :prun_date), ' /* Confirm : should we have hold date here. */
3222: || ' :prun_date, '
3223: || ' 18, ' /* onhand inventory value */
3224: /* Discrete Lot and parent lot are now 80 chars long. Lot_number in msc_st_supplies is 30 chars long.
3225: Hence there could be a problem as the lot number is the pkey in mtl_lot_numbers. */
3226: || ' substrb(DECODE(mln.parent_lot_number, NULL, '', mln.parent_lot_number||:pdelimiter) '
3227: || ' ||mln.lot_number, 1, 30), '
3228: || ' mln.expiration_date, '
3250: /* Query to select the production order details where the batch/fpo is pending
3251: the balances from ic_summ for the item/whse that are not lot controlled
3252: are inserted */
3253: /*
3254: v_sql_stmt := 'INSERT into msc_st_supplies ('
3255: || ' plan_id,'
3256: || ' inventory_item_id,'
3257: || ' organization_id,'
3258: || ' sr_instance_id,'
3291: items. The lot can not be status controlled, that will be in the next
3292: insert the lot number is the combo of lot and sublot
3293: */
3294: /*
3295: v_sql_stmt := 'INSERT into msc_st_supplies ('
3296: || ' plan_id,'
3297: || ' inventory_item_id,'
3298: || ' organization_id,'
3299: || ' sr_instance_id,'
3356:
3357: /* B2623374 -- Rajesh Patangya PORT BUG FOR 2446925 (OM ATP CHECK TO
3358: RECOGNIZE THE ORDER PROCESSING FLAG) */
3359: /*
3360: v_sql_stmt := 'INSERT into msc_st_supplies ('
3361: || ' plan_id,'
3362: || ' inventory_item_id,'
3363: || ' organization_id,'
3364: || ' sr_instance_id,'
3527: * NAME
3528: * Extract_inventory_transfer_supplies
3529: *
3530: * DESCRIPTION
3531: * This procedure will insert records into the table msc_st_supplies
3532: * and msc_st_demands for pending inventory transfers.
3533: * HISTORY
3534: * 25-Jan-2003 B1332662 Created New procedure to insert supplies
3535: * Per discussions with APS team the specifics are
3550: pdoc_type := 'XFER';
3551:
3552: return_status := TRUE ;
3553:
3554: v_sql_stmt := 'INSERT into msc_st_supplies ('
3555: || ' plan_id,'
3556: || ' inventory_item_id,'
3557: || ' organization_id,'
3558: || ' sr_instance_id,'