[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* NAVIN: Alternate Resource selection */
TYPE gmp_alt_resource_typ IS RECORD
(
prim_resource_id PLS_INTEGER,
alt_resource_id PLS_INTEGER,
runtime_factor NUMBER, /* B2353759,alternate runtime_factor */
preference PLS_INTEGER, /* B5688153 Prod spec alternates */
inventory_item_id PLS_INTEGER /* B5688153 Prod spec alternates */
);
i_backward_update_time_fence number_idx_tbl;
i_forward_update_time_fence number_idx_tbl;
* Inserts Data into step charge staging table.
* HISTORY
* B4761946, 20-DEC-2005 Rajesh Patangya Changed the while loop logic
************************************************************************/
PROCEDURE inst_stp_chg_tbl(pinstance_id IN NUMBER, p_batch_loc IN NUMBER)
IS
rsrc_chg_loc NUMBER;
row_count := 1; /* NAVIN :- Maintains the row count. From set of repetitive rows, only one row is inserted. */
select NVL(max(process_seq_id),0) into l_process_seq_id from msc_st_supplies
where sr_instance_id = pinstance_id ;
' select uom_class '
||' from mtl_units_of_measure'||pdblink
||' where uom_code = :gmp_um_code ';
v_prod_cursor := 'SELECT'
|| ' h.batch_no,'
|| ' gp.organization_code, '
|| ' h.batch_id,'
|| ' ((h.batch_id * 2) + 1), '
|| ' h.organization_id, '
|| ' h.routing_id,'
|| ' h.plan_start_date, '
|| ' h.plan_cmplt_date end_date,'
|| ' h.ACTUAL_START_DATE, ' -- bug: 8624913
|| ' d.material_requirement_date, '
|| ' h.batch_status,'
|| ' h.batch_type,'
|| ' d.material_detail_id,'
|| ' d.line_no ,' /* B2919303 */
|| ' DECODE(d.inventory_item_id ,v.inventory_item_id,0,d.line_no) t_line_no,' /* B2953953 */
|| ' d.line_type,'
|| ' DECODE(d.line_type,1,3,d.line_type) t_line_type,' /* B2953953 */
|| ' (nvl(d.wip_plan_qty,plan_qty) - d.actual_qty ), '
|| ' DECODE(d.original_qty,0,Inv_Convert.Inv_Um_Convert'||pdblink
|| ' (d.inventory_item_id, 0,d.organization_id, NULL,1, '
||' d.dtl_um, msi.primary_uom_code,NULL,NULL),'
|| ' (d.original_primary_qty /d.original_qty)), '
|| ' d.inventory_item_id matl_item_id, '
|| ' v.inventory_item_id recipe_item_id, '
|| ' h.poc_ind, '
|| ' DECODE(h.firmed_ind,1,1,2), '
|| ' decode(d.release_type,0, -1, nvl(gbs.batchstep_no,-1)) batchstep_no,'
|| ' h.due_date,'
|| ' h.order_priority,'
||' ((gbsi.batchstep_id*2)+1) from_op_seq_id, ' /* B5461922 */
|| ' DECODE(d.line_type,1,gbsi.minimum_transfer_qty, NULL) , '
|| ' DECODE(d.line_type,1,gbsi.minimum_delay, NULL) t_minimum_delay, '
|| ' DECODE(d.line_type,1,gbsi.maximum_delay, NULL) t_maximum_delay,'
|| ' gbs.batchstep_no'
|| ' FROM'
|| ' gme_batch_header'||pdblink||' h,'
|| ' gme_material_details'||pdblink||' d,'
|| ' gme_batch_step_items'||pdblink||' gbsi,' /* 2919303 */
|| ' gme_batch_steps'||pdblink||' gbs,' /* 2919303 */
|| ' gmd_recipe_validity_rules'||pdblink||' v,'
|| ' mtl_parameters'||pdblink||' gp, ' -- Added this table to get the plant code
|| ' mtl_system_items'||pdblink||' msi '
|| ' WHERE'
|| ' h.batch_id = d.batch_id'
|| ' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
|| ' AND EXISTS (SELECT '
|| ' 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND gmd.inventory_item_id = v.inventory_item_id) '
|| ' AND h.organization_id = gp.organization_id '
|| ' AND gp.process_enabled_flag = '||''''||'Y'||'''' --invconv :- sowmya added
|| ' AND d.organization_id = msi.organization_id '
|| ' AND d.inventory_item_id = msi.inventory_item_id '
|| ' AND msi.process_execution_enabled_flag = '||''''||'Y'||''''
|| ' AND h.batch_type IN (0,10) '
|| ' AND d.material_detail_id = gbsi.material_detail_id (+)' /* 2919303 */
|| ' AND d.batch_id = gbsi.batch_id (+) ' /* 2919303 */
/* Bug 8614604 Vpedarla removed the whole check for product qty */
/* B3625247 - Sowmya -
* When a batch that is in WIP status in which the product is completed
* manually, observed that the ingredient and the resouce requirements were not
* collected on to the APS. Although in WIP the batch hasn't progressed.
* When a product is completed manually the transaction in ic_tran_pnd
* is updated where completed_ind = 1 and trans_qty = batch_output_qty.
* */
/*So in this case a new transaction for the item completed manully is
* inserted into ic_tran_pnd by GME. This new transaction for the product has
* the completed_ind = 0 and trans_qty = 0. */
/*To fetch this record added the condition in the whsere caluse and
* ensured that this condition works on the product transactions alone. */
-- B8342619 Rajesh Patangya
||' AND ( '
|| ' ( (nvl(d.wip_plan_qty,plan_qty) - nvl(d.actual_qty,0) ) > 0 ) '
|| ' OR '
|| ' ( d.inventory_item_id = v.inventory_item_id ) '
|| ' ) '
-- || ' AND (nvl(d.wip_plan_qty,plan_qty) - d.actual_qty ) > 0 ' /*B5100675*/
/*B5100675 - sowsubra - the demand for an ingredient in a batch, which has been consumed by a step
that has already completed should not be passed.And hence added a where clause to filter these rows*/
|| ' AND gbsi.batch_id = gbs.batch_id (+) ' /* 2919303 */
|| ' AND gbsi.batchstep_id = gbs.batchstep_id (+)'; /* 2919303 */
|| ' AND NOT EXISTS (SELECT 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND gmd.inventory_item_id = v.inventory_item_id '
|| ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
|| ' AND gmd.line_type = 1 ) ' ;
v_rsrc_cursor := 'SELECT'
|| ' h.batch_id,'
|| ' ((r.batch_id * 2) + 1), '
|| ' r.batchstep_no,'
|| ' NVL(o.sequence_dependent_ind, -1),' /* NAVIN: Moved this column up for order by clause and changed from NVL(o.sequence_dependent_ind,0) */
|| ' DECODE(gs.prim_rsrc_ind, 1,1,2,2,0,3),' /* This will ensure that ordering will always have primary first */
|| ' gs.resources,'
|| ' ((gri.instance_id * 2) + 1) , ' /* SOWMYA - As Per latest FDD */
|| ' NVL(t.sequence_dependent_ind,0), '
|| ' gs.plan_start_date,'
|| ' h.organization_id, '
|| ' gs.prim_rsrc_ind,'
|| ' c.resource_id,'
|| ' ((c.resource_id * 2) + 1),'
|| ' o.activity, '
|| ' go.oprn_no, '
|| ' go.oprn_vers, '
|| ' gs.plan_rsrc_count,'
|| ' gs.actual_rsrc_count,'
|| ' gs.actual_start_date,'
|| ' gs.plan_cmplt_date,'
|| ' gs.actual_cmplt_date,'
|| ' r.step_status, ' /* B3995361 */
|| ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id) resource_usage, ' -- summarized usage for the step resource
|| ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id, t.instance_id) resource_instance_usage, ' -- summarized usage for the step resource instances
|| ' nvl(gri.eqp_serial_number,to_char(gri.instance_number)), '
|| ' DECODE(gs.scale_type,0,2,1,1,2,3), '
|| ' c.capacity_constraint , '
|| ' r.plan_step_qty, '
|| ' NVL(r.minimum_transfer_qty,-1), '
|| ' NVL(o.material_ind,0), '
|| ' 1 schedule_flag, '
|| ' o.plan_start_date, '
|| ' (DECODE(c.utilization,0,100,NVL(c.utilization,100))/100) * '
|| ' (DECODE(c.efficiency,0,100,NVL(c.efficiency,100))/100), '
|| ' o.batchstep_activity_id, '
|| ' gs.group_sequence_id,'
|| ' gs.group_sequence_number,'
|| ' nvl(gs.firm_type,0),' /*Sowmya - If null then pass 0*/
|| ' gs.sequence_dependent_id setup_id,'
-- In the situation that value of calculate_charges at Step Resource has been
-- set to 0 or NULL the values will need to be adjusted for min and max capacity
-- at the resource level. min capacity will be set to 0 and the max capacity
-- will be set to 99999999999999999
|| ' DECODE(NVL(gs.calculate_charges,0), 0, 0, gs.min_capacity) t_min_capacity,'
|| ' DECODE(NVL(gs.calculate_charges,0), 0, 99999999999999999, gs.max_capacity) t_max_capacity,'
|| ' gs.sequence_dependent_usage, '
|| ' gs.batchstep_resource_id,'
/* NAVIN: for calculating WIP Charges */
|| ' r.step_status, '
|| ' r.plan_charges,'
|| ' gs.plan_rsrc_usage,'
-- Bug: 6925112 Vpedarla modified the actual_rsrc_usage column inserted a NVl funtion
|| ' nvl(gs.actual_rsrc_usage,0) actual_rsrc_usage,'
|| ' ((r.batchstep_id*2)+1),' /* Navin 6/23/2004 Added for resource charges*/
|| ' SUM(NVL(o.material_ind,0)) OVER (PARTITION BY '
|| ' o.batch_id, r.batchstep_id) mat_found, '
-- OPM break_ind values 0 and NULL maps to value 2 of MSC breakable_activity_flag
-- and 1 maps with 1.
|| ' DECODE(NVL(o.break_ind,0), 1, 1, 2) breakable_activity_flag , '
|| ' gs.usage_um ,' --invconv :- sowmya changed this to usage um
|| ' r.step_qty_um ,' --invconv :- sowmya changed this to step_qty_um
|| ' gri.equipment_item_id ,' /* SOWMYA - As Per latest FDD changes */
|| ' gs.plan_rsrc_count gmd_rsrc_count,' /*passed on msc_st_resource_requirements*/
|| ' r.plan_start_date, ' /* populate msc_st_job_operations.reco_start_date */
|| ' r.plan_cmplt_date, ' /* populate msc_st_job_operations.reco_completion_date */
|| ' DECODE(nvl(c.efficiency,0),0,100) ' /*B4320561 - If null then resource is 100%efficient */
|| ' FROM'
|| ' mtl_units_of_measure'||pdblink||' uom, '
|| ' mtl_units_of_measure'||pdblink||' uom2, '
|| ' gme_batch_header'||pdblink||' h,'
|| ' gme_batch_steps'||pdblink||' r,'
|| ' gme_batch_step_activities'||pdblink||' o,'
|| ' gme_batch_step_resources'||pdblink||' gs,'
|| ' gme_resource_txns'||pdblink||' t , '
|| ' gmp_resource_instances'||pdblink||' gri, '
|| ' gmd_operations'||pdblink||' go, '
|| ' cr_rsrc_dtl'||pdblink||' c'
|| ' WHERE'
|| ' h.batch_id = r.batch_id '
|| ' AND r.batch_id = o.batch_id'
|| ' AND r.batchstep_id = o.batchstep_id'
|| ' AND o.batchstep_activity_id = gs.batchstep_activity_id'
|| ' AND o.batch_id = t.doc_id'
|| ' AND gs.batchstep_resource_id = t.line_id'
|| ' AND t.completed_ind = 0 '
|| ' AND NVL(t.sequence_dependent_ind,0) = 0 ' /* B4900503, Rajesh Patangya */
|| ' AND t.delete_mark = 0 '
|| ' AND t.instance_id = gri.instance_id (+) '
|| ' AND nvl(gri.inactive_ind,0) = 0 '
|| ' AND c.organization_id = h.organization_id '
|| ' AND c.resources = gs.resources'
|| ' AND c.delete_mark = 0 '
|| ' AND nvl(c.inactive_ind,0) = 0 '
/*B4313202 COLLECTING DATA FOR COMPLETED OPERATIONS:Included a chk for step status = 3*/
|| ' AND r.step_status in (1, 2, 3)'
|| ' AND c.Schedule_Ind <> 3 ' /* NAVIN: gs.prim_rsrc_ind in (1,2) */
|| ' AND uom.uom_class = :gmp_uom_class '
|| ' AND uom.uom_code = gs.usage_um ' /* Sowmya - Alternate Resources */
|| ' AND uom2.uom_code = r.step_qty_um ' ;
|| ' AND NOT EXISTS (SELECT 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
|| ' AND gmd.line_type = 1 ) ' ;
|| ' AND go.delete_mark = 0 '
|| ' ORDER BY '
||' 1,2,3,4,5,6,7,8 DESC,9'; /* NAVIN: converted to position notation in Order By*/
' SELECT '
||' ((gbsc.batch_id*2)+1) x_batch_id,'
||' ((gbsc.batchstep_id*2)+1),' /* B5461922 */
|| ' ((crd.resource_id * 2) + 1),'
||' gbsc.charge_number,'
||' h.organization_id, '
||' gbs.batchstep_no,'
||' gbsc.activity_sequence_number,'
||' gbsc.charge_quantity, '
||' gbsc.plan_start_date, '
||' gbsc.plan_cmplt_date'
||' FROM'
||' gme_batch_step_charges'||pdblink||' gbsc,'
||' cr_rsrc_dtl'||pdblink||' crd,'
||' gmd_recipe_validity_rules'||pdblink||' v,'
||' gme_batch_steps'||pdblink||' gbs,'
||' gme_batch_header'||pdblink||' h'
||' WHERE '
||' h.batch_id = gbs.batch_id '
||' AND gbsc.batch_id = gbs.batch_id '
||' AND gbsc.batchstep_id = gbs.batchstep_id '
||' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
||' AND EXISTS (SELECT '
||' 1 '
||' FROM '
||' gme_material_details'||pdblink||' gmd '
||' WHERE '
||' gmd.batch_id = h.batch_id '
||' AND gmd.inventory_item_id = v.inventory_item_id) '
||' AND crd.resources = gbsc.resources '
||' AND crd.organization_id = h.organization_id '
||' AND gbs.step_status in (1, 2) ';
|| ' AND NOT EXISTS (SELECT 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
|| ' AND gmd.line_type = 1 ) ' ;
/* Alternate Resource selection */
/* B5688153, Rajesh Patangya prod spec alt*/
statement_alt_resource :=
' SELECT pcrd.resource_id, acrd.resource_id, '
||' cam.runtime_factor, '
/*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.inventory_item_id,-1) '
||' FROM cr_rsrc_dtl'||pdblink||' acrd, '
||' cr_rsrc_dtl'||pdblink||' pcrd, '
||' cr_ares_mst'||pdblink||' cam, '
||' gmp_altresource_products'||pdblink||' prod'
||' WHERE cam.alternate_resource = acrd.resources '
||' AND cam.primary_resource = pcrd.resources '
||' AND acrd.organization_id = pcrd.organization_id '
||' AND cam.primary_resource = prod.primary_resource(+) '
||' AND cam.alternate_resource = prod.alternate_resource(+) '
||' AND acrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id, '
||' DECODE(cam.preference,NULL,cam.runtime_factor,cam.preference),'
||' prod.inventory_item_id ' ;
'SELECT '
|| ' iwm.mtl_organization_id '
|| 'FROM '
|| ' sy_orgn_mst' ||pdblink|| ' sy, '
|| ' ic_whse_mst' ||pdblink|| ' iwm '
|| 'WHERE '
|| ' sy.orgn_code = :p1'
|| ' AND sy.resource_whse_code = iwm.whse_code';
/* nsinghi APSK - Insert Step related information in msc_st_job_operations
every time step changes. */
jo_index := jo_index + 1;
uom_conv_cursor := 'SELECT '
||' inv_convert.inv_um_convert'||pdblink
||' (:pitem, '
||' NULL, '
||' :orgid, '
||' 5 , '
||' :pqty, '
||' :pfrom_um, '
||' :pto_um , '
||' NULL , '
||' NULL '
||' ) '
||' FROM dual';
/* Bulk Insert for insert_resource_requirements */
rr_index := rr_index + 1 ;
Now check if the above resource inserted is a Primary. If it is
Primary then find its Alternates if existing, and then insert its rows
into msc_st_operation_resources table. Also keep track of number of
times alternates are inserted.
*/
IF rsrc_tab(r).prim_rsrc_ind = 1 THEN
---------------------------------------------------------------------
-- Use Bsearch technique to identify if any Alternate exists for the primary.
-- Enh_bsearch_alternate_rsrc is a new procedure to locate the Alternate Resource
-- for a given Primary resource in the PL/SQl table.
---------------------------------------------------------------------
alternate_rsrc_loc := Enh_bsearch_alternate_rsrc (rsrc_tab(r).resource_id);
/* Bulk Insert for Alternate_resource_requirements */
arr_index := arr_index + 1 ;
alternate resources that has been inserted for the Primary resource
of the group. Now insert all the resource records other than primary
with a value of Alternate_Number from 1 to v_alternate, to complete
the pattern of resource group.
NAVIN: */
IF rsrc_tab(r).prim_rsrc_ind <> 1 AND v_alternate > 0 THEN
/* B3995361 rpatangy start */
mk_alt_grp := 0 ;
/* Bulk Insert for insert_resource_requirements */
inst_indx := inst_indx + 1 ;
/* B3267522, Rajesh Patangya Do not insert demands, if ingradient is same as product
(single level circular reference) */
/* nsinghi INVCONV Start */
/* IF prod_tab(p).item_id <> product_line THEN */
IF prod_tab(p).matl_item_id <> product_line THEN
/* nsinghi INVCONV End */
-- gmp_debug_message('Demand Item '|| prod_tab(p).matl_item_id ||' Qty '||prod_tab(p).qty);
/* Demands Bulk inserts */
d_index := d_index + 1 ;
/* Supply Bulk Insert Assignments */
s_index := s_index + 1 ;
INSERT INTO msc_st_resource_requirements (
organization_id,
sr_instance_id,
supply_id,
supply_type, /* kbanddyo B6407864 Need to populate supply_type field */
resource_seq_num,
resource_id,
start_date,
end_date,
operation_hours_required,
usage_rate, /* B4637398 Rajesh Patangya */
assigned_units,
department_id,
wip_entity_id,
operation_seq_num,
deleted_flag,
firm_flag,
minimum_transfer_quantity,
parent_seq_num,
schedule_flag,
basis_type,
setup_id,
group_sequence_id,
group_sequence_number,
minimum_capacity,
maximum_capacity,
orig_resource_seq_num,
alternate_number,
hours_expended,
breakable_activity_flag,
inventory_item_id, /* B4777532 - product_item_id populated */
step_quantity, /* Sowmya - As per latest FDD changes*/
step_quantity_uom , /* Sowmya - As per latest FDD changes*/
maximum_assigned_units, /* Sowmya - As per latest FDD changes*/
unadjusted_resource_hours, /*B4320561 - Same as in wip (without eff and util) */
touch_time, /* B4320561 - Unadjusted res. hrs / efficiency.*/
activity_group_id, /* B3995361 rpatangy */
-- activity_name, /* B5338598 rpatangy */
operation_name, /* B5338598 rpatangy */
operation_sequence_id, /* B5461922 rpatangy */
operation_status -- Vpedarla 9319734
)
VALUES (
rr_organization_id(i),
rr_sr_instance_id(i),
rr_supply_id(i),
1, /* kbanddyo B6407864 supply_type = 1 for OPM batches*/
rr_resource_seq_num(i),
rr_resource_id(i),
rr_start_date(i),
rr_end_date(i),
rr_opr_hours_required(i),
nvl(rr_usage_rate(i),0), /* B4637398 Rajesh Patangya */
rr_assigned_units(i),
rr_department_id(i),
rr_wip_entity_id(i),
rr_operation_seq_num(i),
2,
rr_firm_flag(i),
rr_minimum_transfer_quantity(i),
rr_parent_seq_num(i),
rr_schedule_flag(i),
rr_basis_type(i),
rr_setup_id(i),
rr_sequence_id(i), -- group_sequence_id
rr_sequence_number(i), -- group_sequence_number
rr_min_capacity(i),
rr_max_capacity(i),
rr_original_seq_num(i),
rr_alternate_number(i),
rr_hours_expended(i),
rr_breakable_activity_flag(i),
rr_product_item_id(i), /* B4777532 - product_item_id populated */
rr_plan_step_qty(i), /* Sowmya - As per the latest FDD changes*/
rr_step_qty_uom(i) , /* Sowmya - As per the latest FDD changes*/
rr_gmd_rsrc_cnt(i),
rr_unadjusted_resource_hrs(i), /*B4320561 - sowsubra*/
rr_touch_time(i), /*B4320561 - sowsubra*/
rr_activity_group_id(i), /* B3995361 rpatangy */
-- rr_activity_name(i), /* B5338598 rpatangy */
rr_operation_no(i), /* B5338598 rpatangy */
rr_operation_sequence_id(i), /* B5461922 rpatangy */
rr_opr_status(i) -- Vpedarla 9319734
) ;
/* ----------------------- Supply Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_supplies (
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
new_schedule_date,
old_schedule_date,
new_wip_start_date,
old_wip_start_date,
last_unit_completion_date,
disposition_id,
order_type,
order_number,
new_order_quantity,
old_order_quantity,
firm_planned_type,
firm_quantity,
firm_date,
wip_entity_name,
lot_number,
expiration_date,
operation_seq_num,
by_product_using_assy_id,
deleted_flag,
requested_completion_date,
wip_status_code, /*B5100481*/
schedule_priority,
process_seq_id, /* B8349005 */
actual_start_date -- Bug: 8624913
)
VALUES (
-1,
s_inventory_item_id(i),
s_organization_id(i),
s_sr_instance_id(i),
s_new_schedule_date(i),
s_old_schedule_date(i),
s_new_wip_start_date(i),
s_old_wip_start_date(i),
s_lunit_completion_date(i),
s_disposition_id(i),
s_order_type(i),
s_order_number(i),
s_new_order_quantity(i),
s_old_order_quantity(i),
s_firm_planned_type(i), /* 2 */
s_firm_quantity(i),
s_firm_date(i),
s_wip_entity_name(i), /* Order Number */
null_value,
null_value,
s_operation_seq_num(i),
s_by_product_using_assy_id(i),
2, /* Deleted Flag */
s_requested_completion_date(i),
s_wip_status_code(i), /*B5100481 - 16 for pending, 3 for wip */
s_schedule_priority(i),
s_process_seq_id(i), /* B8349005 */
s_actual_start_date(i) -- Bug: 8624913
) ;
/* ----------------------- Demands Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_demands (
organization_id,
inventory_item_id,
sr_instance_id,
using_assembly_item_id,
using_assembly_demand_date,
using_requirement_quantity,
demand_type,
origination_type,
wip_entity_id,
demand_schedule_name,
order_number,
wip_entity_name,
selling_price,
operation_seq_num,
wip_status_code, /*B5100481*/
deleted_flag )
VALUES (
d_organization_id(i),
d_inventory_item_id(i),
d_sr_instance_id(i),
d_assembly_item_id(i),
d_demand_date(i),
d_requirement_quantity(i),
d_demand_type(i),
d_origination_type(i),
d_wip_entity_id(i),
d_demand_schedule(i),
d_order_number(i),
d_wip_entity_name(i),
d_selling_price(i),
d_operation_seq_num(i),
d_wip_status_code(i), /*B5100481*/
2 ) ;
/* NAVIN: ------------ START: Complex Route -- Collect Batch Step Dependencies in one insert-select ------------*/
sql_stmt :=
' INSERT INTO msc_st_job_operation_networks '
|| ' ( '
|| ' from_op_seq_id, '
|| ' to_op_seq_id, '
|| ' wip_entity_id, '
|| ' dependency_type, '
|| ' transition_type, '
|| ' sr_instance_id, '
|| ' deleted_flag, '
|| ' minimum_time_offset, '
|| ' maximum_time_offset, '
|| ' transfer_pct, '
|| ' from_op_seq_num, '
|| ' to_op_seq_num, '
|| ' apply_to_charges, '
|| ' organization_id '
|| ' ) '
|| ' SELECT '
||' ((gbsd.dep_step_id*2)+1), ' /* B5461922 */
||' ((gbsd.batchstep_id*2)+1),' /* B5461922 */
||' ((gbsd.batch_id * 2) + 1) x_batch_id, '
||' decode(gbsd.dep_type,0,1,2) dependency_type, '
||' 1, '
||' :1, '
||' 2, '
||' gbsd.standard_delay, '
||' gbsd.max_delay, '
||' gbsd.transfer_percent, '
||' gbs1.batchstep_no, '
||' gbs2.batchstep_no, '
||' DECODE(NVL(gbsd.chargeable_ind,0),1,1,2), ' /* convert a Null or 0 to a 2, a 1 remains a 1 */
/* nsinghi INVCONV Start */
/* ||' iwm.mtl_organization_id ' */
||' h.organization_id '
/* nsinghi INVCONV End */
||' FROM '
||' gme_batch_step_dependencies'||pdblink||' gbsd, '
||' gme_batch_header'||pdblink||' h,'
||' gme_batch_steps'||pdblink||' gbs1, '
||' gme_batch_steps'||pdblink||' gbs2 '
/* nsinghi INVCONV Start */
/* ||' ic_whse_mst'||pdblink||' iwm, '
||' sy_orgn_mst'||pdblink||' som ' */
/* nsinghi INVCONV End */
||' WHERE '
||' h.batch_id = gbsd.batch_id '
||' AND gbs1.batch_id = gbsd.batch_id '
||' AND gbs1.batchstep_id = gbsd.dep_step_id '
||' AND gbs2.batch_id = gbsd.batch_id '
||' AND gbs2.batchstep_id = gbsd.batchstep_id '
||' AND h.batch_status in (1, 2) ';
|| ' AND NOT EXISTS (SELECT 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
|| ' AND gmd.line_type = 1 ) ' ;
||' AND som.delete_mark = 0 '
||' AND som.resource_whse_code = iwm.whse_code ' ; */
/* NAVIN: ------------ END: Complex Route -- Collect Batch Step Dependencies in one insert-select ------------*/
/* NAVIN: ----------------------- MTQ with Hardlinks --------------------- */
i := 1 ;
INSERT INTO msc_st_job_operation_networks(
from_op_seq_id,
wip_entity_id,
dependency_type,
transition_type,
sr_instance_id,
deleted_flag,
from_item_id,
organization_id,
minimum_time_offset,
maximum_time_offset,
from_op_seq_num,
minimum_transfer_qty
)
VALUES
(
stp_var_itm_from_op_seq_id(i),
stp_var_itm_wip_entity_id(i),
5, -- dependency_type for mtq with hardlink
1, -- transition_type: primary
stp_var_itm_instance_id(i),
2,
stp_var_itm_FROM_item_ID(i),
stp_var_itm_organization_id(i),
stp_var_itm_min_tm_off(i),
stp_var_itm_max_tm_off(i),
stp_var_itm_from_op_seq_num(i),
stp_var_min_tran_qty(i)
);
INSERT INTO msc_st_resource_charges
(
sr_instance_id ,
resource_id ,
organization_id ,
department_id ,
wip_entity_id ,
operation_sequence_id ,
operation_seq_num ,
resource_seq_num ,
charge_number ,
charge_quantity ,
deleted_flag ,
charge_start_datetime ,
charge_end_datetime
)
VALUES
(
stp_instance_id(i) ,
stp_chg_resource_id(i) ,
stp_chg_organization_id(i),
stp_chg_department_id(i),
stp_chg_wip_entity_id(i),
stp_chg_operation_seq_id(i),
stp_chg_operation_seq_no(i),
stp_chg_resource_seq_num(i),
stp_chg_charge_num(i),
stp_chg_charge_quanitity(i),
2,
stp_chg_charge_start_dt_time(i) ,
stp_chg_charge_end_dt_time(i)
);
INSERT INTO msc_st_resource_instance_reqs (
supply_id,
organization_id,
sr_instance_id,
resource_seq_num,
resource_id,
res_instance_id,
start_date,
end_date,
resource_instance_hours,
/* NAVIN :- CHECK Should This be Included. It is
mentioned in FDD, but not included in APS script file. */
-- schedule_flag,
operation_seq_num,
department_id,
wip_entity_id,
serial_number,
deleted_flag,
parent_seq_num, /* Sowmya - as the column was changed from parent_seq_number to parent_seq_num */
orig_resource_seq_num,
equipment_item_id /*Sowmya - As per the latest FDD changes - End*/
)
VALUES (
rec_inst_supply_id(i) ,
rec_inst_organization_id(i) ,
rec_inst_sr_instance_id(i) ,
rec_inst_rec_resource_seq_num(i) ,
rec_inst_resource_id(i) ,
rec_inst_instance_id(i) ,
rec_inst_start_date(i) ,
rec_inst_end_date(i) ,
rec_inst_rsrc_instance_hours(i) ,
-- 1 , /* Schedule Flag 1 = Scheduled */
rec_inst_operation_seq_num(i) ,
rec_inst_department_id(i) ,
rec_inst_wip_entity_id(i) ,
rec_inst_serial_number(i) ,
2 , /* Delete Flag */
rec_inst_parent_seq_num(i) ,
rec_inst_original_seq_num(i),
rec_inst_equp_item_id(i) /*Sowmya - As per the latest FDD changes - End*/
) ;
INSERT INTO msc_st_job_op_resources
(
wip_entity_id,
organization_id,
sr_instance_id ,
operation_seq_num ,
resource_seq_num ,
resource_id ,
alternate_num ,
reco_start_date ,
reco_completion_date ,
usage_rate_or_amount ,
assigned_units ,
schedule_flag ,
parent_seq_num ,
recommended ,
department_id ,
uom_code ,
activity_group_id ,
basis_type ,
firm_flag ,
setup_id ,
schedule_seq_num ,
group_sequence_id ,
group_sequence_number ,
-- resource_batch_id ,
maximum_assigned_units ,
deleted_flag ,
batch_number
)
VALUES
(
arr_wip_entity_id(i),
arr_organization_id(i),
arr_sr_instance_id(i),
arr_operation_seq_num(i),
arr_res_seq_num(i),
arr_resource_id(i),
arr_alternate_num(i),
null_value,
null_value,
arr_usage_rate(i),
arr_assigned_units(i),
1,
null_value,
1,
arr_department_id(i),
arr_uom_code(i),
arr_activity_group_id(i),
arr_basis_type(i),
null_value,
arr_setup_id(i),
arr_schedule_seq_num(i),
null_value,
null_value,
-- null_value,
arr_maximum_assigned_units(i),
2,
null_value
);
INSERT INTO msc_st_job_operations
(
wip_entity_id,
sr_instance_id,
operation_seq_num,
recommended,
network_start_end,
reco_start_date,
reco_completion_date,
operation_sequence_id,
organization_id,
department_id,
minimum_transfer_quantity,
effectivity_date,
deleted_flag
)
VALUES
(
jo_wip_entity_id(i),
jo_instance_id(i),
jo_operation_seq_num(i),
jo_recommended(i),
jo_network_start_end(i),
jo_reco_start_date(i),
jo_reco_completion_date(i),
jo_operation_sequence_id(i),
jo_organization_id(i),
jo_department_id(i),
jo_minimum_transfer_quantity(i),
SYSDATE-100,
2
);
* insert_supplies
*
* DESCRIPTION
* This procedure will take the parameter values and insert a row into
* the table msc_st_supplies
* HISTORY
* M Craig
* 2/10/2000 - Populating Order number column with Wip Entity Name ( porder_no )
* 2/24/2003 - populating Firmed batches Indicator, Qty and Date
************************************************************************/
PROCEDURE insert_supplies(
pitem_id PLS_INTEGER,
porganization_id PLS_INTEGER,
pinstance_id PLS_INTEGER,
pdate DATE,
pstart_date DATE,
pend_date DATE,
pbatch_id PLS_INTEGER,
pqty NUMBER,
pfirmed_ind NUMBER,
pbatchstep_no NUMBER, /* Added pbatchstep_no - B2919303 */
porder_no VARCHAR2,
plot_number VARCHAR2,
pexpire_date DATE,
psupply_type NUMBER,
pproduct_item_id PLS_INTEGER) /* B2953953 - CoProduct */
AS
st_supplies VARCHAR2(4000) ;
' INSERT INTO msc_st_supplies ( '
||' plan_id, inventory_item_id, organization_id, sr_instance_id, '
||' new_schedule_date, old_schedule_date, new_wip_start_date, '
||' old_wip_start_date, last_unit_completion_date, disposition_id, '
||' order_type, order_number, new_order_quantity, old_order_quantity, '
||' firm_planned_type,firm_quantity,firm_date, wip_entity_name, '
||' lot_number, expiration_date,operation_seq_num, by_product_using_assy_id, '
||' deleted_flag ) '
||' VALUES '
||' (:p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, '
||' :p8, :p9, :p10, '
||' :p11,:p12,:p13,:p14,'
||' :p15,:p16,:p17,:p18,'
||' :p19,:p20,:p21,'
||' :p22,:p23 ) ' ;
log_message('Failure occured during the insert into msc_st_supplies');
END insert_supplies;
* insert_resource_requirements
*
* DESCRIPTION
* This procedure wil insert a row into the table
* msc_st_resource_requirements using the parameters passed in
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* 13-SEP-2002 - firm_flag = 1 for WIP steps B2266934
************************************************************************/
PROCEDURE insert_resource_requirements(
porganization_id IN PLS_INTEGER,
pinstance_id IN PLS_INTEGER,
pseq_num IN PLS_INTEGER,
presource_id IN PLS_INTEGER,
pstart_date IN DATE,
pend_date IN DATE,
presource_usage IN NUMBER,
prsrc_cnt IN NUMBER,
pbatchstep_no IN NUMBER, /* B1224660 new parm to write step number */
pbatch_id IN PLS_INTEGER,
pstep_status IN NUMBER,
pschedule_flag IN NUMBER,
pparent_seq_num IN NUMBER,
pmin_xfer_qty IN NUMBER)
AS
st_resource_requirements VARCHAR2(2000) ;
' INSERT INTO msc_st_resource_requirements ( '
||' organization_id, sr_instance_id, supply_id, resource_seq_num,'
||' resource_id, start_date, end_date, operation_hours_required,'
||' assigned_units, department_id, wip_entity_id, operation_seq_num, '
||' deleted_flag, firm_flag, minimum_transfer_quantity, '
||' parent_seq_num, schedule_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8, '
||' :p9, :p10,:p11,:p12, '
||' :p13,:p14, :p15, '
||' :p16, :p17 ) ';
log_message('Failure occured during the insert into msc_st_resource_requirements');
END insert_resource_requirements;
* insert_demands
*
* DESCRIPTION
* This procedure will take the parameter values and insert a row into
* the table msc_st_demands
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* P Dong
* 09/14/01 - added api_mode and pschedule_id parameters
************************************************************************/
PROCEDURE insert_demands(
pitem_id PLS_INTEGER,
porganization_id PLS_INTEGER,
pinstance_id PLS_INTEGER,
pbatch_id PLS_INTEGER,
pproduct_item_id PLS_INTEGER,
pdate DATE,
pqty NUMBER,
pbatchstep_no NUMBER, /* B2919303 - BatchStep */
porder_no VARCHAR2,
pdesignator VARCHAR2,
pnet_price NUMBER, /* B1200400 added net price */
porigination_type NUMBER,
api_mode BOOLEAN,
pschedule_id NUMBER )
AS
statement_demands_api VARCHAR2(3000) ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
SELECT DECODE(porigination_type,1,NULL,porder_no) ,
DECODE(porigination_type,1,porder_no,NULL)
INTO t_order_number, t_wip_entity_name
FROM dual ;
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price,operation_seq_num,deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14,:p15 )' ;
log_message('Failure occured during the insert into msc_st_demands');
END insert_demands;
* This procedure will insert records into the table msc_st_supplies
* for the onhand balances in inventory. The insert is split into 3 parts
* one for non-lot controlled, lot controlled, and lot and status
* controlled item. Each inserted will need touse a distnct list from
* the table gmp_item_aps. The table may contain multiple values for
* the item/whse combination
* HISTORY
* M Craig
* M Craig B1332662 changed to call two new procs to collect onhand and
* Inventory transfers
* Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
* Added handling of NO_DATA_FOUND Exception.
* And return the return_status as TRUE.
************************************************************************/
PROCEDURE onhand_inventory(
pdblink IN VARCHAR2,
pinstance_id IN PLS_INTEGER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
local_ret_status1 BOOLEAN := TRUE;
* This procedure will insert records into the table msc_st_supplies
* for the onhand balances in inventory. The insert is split into 3 parts
* one for non-lot controlled, lot controlled, and lot and status
* controlled item. Each inserted will need touse a distnct list from
* the table gmp_item_aps. The table may contain multiple values for
* the item/whse combination
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* 2/10/2000 - Populating sub inventory code with whse code - bug# 1172875
* M Craig B1332662 created a new function to just collect onhand inventory
* Sgidugu B2251375 - Changed Substr Function to substrb Function
************************************************************************/
PROCEDURE extract_onhand_balances(
pdblink IN VARCHAR2,
pinstance_id IN PLS_INTEGER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
BEGIN
/* nsinghi INVCONV Start */
/* Previously the logic to insert onhand information was split into the following 3 cursors:
1. Insert onhand information for items non lot and non status control. View ic_summ_inv_onhand_v
stores information of onhand and information retrieved from this view.
2. Insert onhand information for items Lot controlled and non status controlled. ic_loct_inv contains
onhand information of items in different lots
3. Insert onhand information for items Lot controlled and status controlled. ic_loct_inv contains
onhand information of items in different lots
The three select statements are replaced by a single select statement. */
v_sql_stmt := ' INSERT into msc_st_supplies ( '
|| ' plan_id, '
|| ' inventory_item_id, '
|| ' organization_id, '
|| ' sr_instance_id, '
|| ' new_schedule_date, '
|| ' new_dock_date, ' /* Confirm if this column is required */
|| ' order_type, '
|| ' lot_number, '
|| ' expiration_date, '
|| ' firm_planned_type, '
|| ' deleted_flag, '
|| ' subinventory_code, '/* Added new column subinventory Code */
|| ' new_order_quantity) '
|| ' SELECT '
|| ' -1, '
|| ' mon.inventory_item_id, '
|| ' mon.organization_id, '
|| ' :pinstance_id, '
|| ' NVL(mln.hold_date, :prun_date), ' /* Confirm : should we have hold date here. */
|| ' :prun_date, '
|| ' 18, ' /* onhand inventory value */
/* Discrete Lot and parent lot are now 80 chars long. Lot_number in msc_st_supplies is 30 chars long.
Hence there could be a problem as the lot number is the pkey in mtl_lot_numbers. */
|| ' substrb(DECODE(mln.parent_lot_number, NULL, '', mln.parent_lot_number||:pdelimiter) '
|| ' ||mln.lot_number, 1, 30), '
|| ' mln.expiration_date, '
|| ' 2, '
|| ' 2, '
|| ' mon.subinventory_code, ' /* Populating subinventory with whse code B1172875 */
|| ' INV_CONSIGNED_VALIDATIONS.GET_PLANNING_QUANTITY(2, 1, mon.organization_id, '
|| ' NULL, mon.inventory_item_id) '
|| ' FROM '
|| ' mtl_onhand_net'||pdblink||' mon, '
|| ' mtl_lot_numbers'||pdblink||' mln, '
|| ' mtl_parameters'||pdblink||' gp ' --invconv :- sowmya changed from gmd_parameters to mtl_parameters
|| ' WHERE '
|| ' mon.lot_number = mln.lot_number (+) '
|| ' AND mon.inventory_item_id = mln.inventory_item_id (+) '
|| ' AND mon.organization_id = mln.organization_id (+) '
|| ' AND mon.organization_id = gp.organization_id '; --sowmya changed
/* Query to select the production order details where the batch/fpo is pending
the balances from ic_summ for the item/whse that are not lot controlled
are inserted */
/*
v_sql_stmt := 'INSERT into msc_st_supplies ('
|| ' plan_id,'
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' new_schedule_date,'
|| ' order_type,'
|| ' firm_planned_type,'
|| ' deleted_flag,'
|| ' subinventory_code,' *//* New change , added subinventory Code column */
/* || ' new_order_quantity)'
|| ' SELECT '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id, '
|| ' :prun_date, '
|| ' 18,' */ /* onhand inventory value */
/* || ' 2,'
|| ' 2,'
|| ' s.whse_code,' *//* Populating subinventory with Whse code B1172875 */
/* || ' s.onhand_qty'
|| ' FROM '
|| ' ic_summ_inv_onhand_v' ||pdblink|| ' s,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
|| ' lot_control from'
|| ' gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.whse_code = i.whse_code '
|| ' and i.lot_control = 0'
|| ' and s.onhand_qty <> 0';
insert the lot number is the combo of lot and sublot
*/
/*
v_sql_stmt := 'INSERT into msc_st_supplies ('
|| ' plan_id,'
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' new_schedule_date,'
|| ' order_type,'
|| ' lot_number,'
|| ' expiration_date,'
|| ' firm_planned_type,'
|| ' deleted_flag,'
|| ' subinventory_code,' *//* Added new column subinventory Code */
/* || ' new_order_quantity)'
|| ' SELECT'
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id,'
|| ' :prun_date,'
|| ' 18,' *//* onhand inventory value */
/* || ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
|| ' l.sublot_no),1,30),'
|| ' l.expire_date,'
|| ' 2,'
|| ' 2,'
|| ' s.whse_code,' *//* Populating subinventory with whse code B1172875 */
/* || ' s.loct_onhand'
|| ' FROM'
|| ' ic_loct_inv'||pdblink||' s,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' ic_item_mst'||pdblink||' m,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
|| 'lot_control from gmp_item_aps'||pdblink||') i'
|| ' WHERE'
|| ' s.item_id = i.item_id'
|| ' and s.item_id = m.item_id'
|| ' and s.whse_code = i.whse_code'
|| ' and i.lot_control = 1'
|| ' and m.status_ctl = 0'
|| ' and s.lot_id = l.lot_id'
|| ' and s.lot_id > 0'
|| ' and l.delete_mark = 0'
|| ' and s.loct_onhand <> 0';
v_sql_stmt := 'INSERT into msc_st_supplies ('
|| ' plan_id,'
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' new_schedule_date,'
|| ' new_dock_date,'
|| ' order_type,'
|| ' lot_number,'
|| ' expiration_date,'
|| ' firm_planned_type,'
|| ' deleted_flag,'
|| ' subinventory_code,' */ /* added new column sub inventory code */
/* || ' new_order_quantity,'
|| ' NON_NETTABLE_QTY)' *//* (OM ATP CHECK TO RECOGNIZE THE ORDER PROCESSING FLAG)*/
/* || ' SELECT'
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id,'
|| ' DECODE(c.ic_hold_date,NULL,:prun_date,c.ic_hold_date),'
|| ' :prun_date,'
|| ' DECODE(c.ic_hold_date,NULL,18,8),' *//* onhand inventory value */
/* || ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
|| ' l.sublot_no),1,30),'
|| ' l.expire_date,'
|| ' 2,'
|| ' 2,'
|| ' s.whse_code,' *//* Populating subinventory code with whse code B1172875 */
/* || ' s.loct_onhand, '
|| ' decode(t.order_proc_ind,0,s.loct_onhand,0)'
|| ' FROM'
|| ' ic_loct_inv'||pdblink||' s,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' ic_item_mst'||pdblink||' m,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
|| ' lot_control from gmp_item_aps'||pdblink||') i,'
|| ' ic_lots_sts'||pdblink||' t,'
|| ' ic_lots_cpg'||pdblink||' c'
|| ' WHERE'
|| ' s.item_id = i.item_id'
|| ' and s.item_id = m.item_id'
|| ' and s.whse_code = i.whse_code'
|| ' and i.lot_control = 1'
|| ' and s.lot_id = l.lot_id'
|| ' and s.lot_id > 0'
|| ' and l.delete_mark = 0'
|| ' and m.status_ctl = 1'
|| ' and s.lot_status = t.lot_status'
|| ' and t.rejected_ind = 0'
|| ' and t.nettable_ind = 1'
|| ' and s.loct_onhand <> 0'
|| ' and c.item_id (+) = l.item_id'
|| ' and c.lot_id (+) = l.lot_id'
|| ' and c.ic_hold_date (+) > :run_date' ;
* This procedure will insert records into the table msc_st_demands
* According to APS team (Sam Tupe < prganesh Shah etc.
* The inventory transfer demand is similar to Internal Sales Order
* demand hence should be added to each of the demand schedule
* The specifics are
* demand_type = 6
* origination_type = 6
* disposition_id = same transfer_id This should match with the
* corresponding transaction_id of the supply created
* by the same transfer
* demand_schedule_name = OPM specific demand_schedule name - The
* MDS names used in forecast/SO extraction
* HISTORY
* 25-Jan-2003 B2756431
* Note : Old procedure extract_inv_transfers is now removed
* and replaced with these two new procedures
************************************************************************/
PROCEDURE extract_inv_transfer_demands(
pdblink IN VARCHAR2,
pinstance_id IN PLS_INTEGER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
pwhse_code IN VARCHAR2,
pdesignator IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
pdoc_type VARCHAR2(4) ;
v_sql_stmt := 'INSERT into msc_st_demands ('
|| ' organization_id,'
|| ' inventory_item_id,'
|| ' sr_instance_id,'
|| ' using_assembly_item_id,'
|| ' using_assembly_demand_date,'
|| ' using_requirement_quantity,'
|| ' demand_type,'
|| ' origination_type,'
|| ' order_number,'
|| ' demand_schedule_name,'
|| ' disposition_id,' /* B2756431 */
|| ' demand_source_type,' /* B2756431 */
|| ' original_system_reference,' /* B2756431 */
|| ' original_system_line_reference,' /* being added for B2756431 */
|| ' deleted_flag)'
|| ' SELECT '
|| ' i.organization_id,'
|| ' i.aps_item_id,'
|| ' :pinstance_id, '
|| ' i.aps_item_id,'
|| ' s.scheduled_release_date,'
|| ' s.release_quantity1,'
|| ' 1,' /* Discrete , other demands types are interpreted as continuous */
|| ' 6,' /* Orig_type should br 6 per Sam Tupe so change from 11 */
|| ' :pdoc_type || :pdelimiter || s.orgn_code ||'
|| ' :pdelimiter2 || s.transfer_no, '
|| ' :pdesignator,'
|| ' s.transfer_id,'
|| ' 8,' /* B2756431 Demand_source_type */
|| ' s.transfer_id,' /* B2756431 original_system_reference */
|| ' s.transfer_id,' /* B2756431 original_system_line_reference */
|| ' 2'
|| ' FROM '
|| ' ic_xfer_mst' ||pdblink|| ' s,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id '
|| ' from gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.from_warehouse = i.whse_code '
|| ' and s.transfer_status IN (1) '
|| ' and s.from_warehouse = :pwhse_code '
|| ' and s.release_quantity1 <> 0';
* This procedure will insert records into the table msc_st_supplies
* and msc_st_demands for pending inventory transfers.
* HISTORY
* 25-Jan-2003 B1332662 Created New procedure to insert supplies
* Per discussions with APS team the specifics are
* Order_type = 2
* Transaction_id = transafer_id of the transfer in OPM
************************************************************************/
PROCEDURE extract_inv_transfer_supplies(
pdblink IN VARCHAR2,
pinstance_id IN PLS_INTEGER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
pdoc_type VARCHAR2(4) ;
v_sql_stmt := 'INSERT into msc_st_supplies ('
|| ' plan_id,'
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' source_sr_instance_id,'
|| ' new_schedule_date,'
|| ' order_type,'
|| ' order_number,'
|| ' lot_number,'
|| ' firm_planned_type,'
|| ' deleted_flag,'
|| ' subinventory_code,'
|| ' transaction_id,' /* being added for B2756431 */
|| ' disposition_id,' /* being added for B2756431 */
|| ' po_line_id,' /* being added for B2756431 */
|| ' source_organization_id,' /* being added for B2756431 */
|| ' new_order_quantity)'
|| ' SELECT '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id, '
|| ' :pinstance_id, '
|| ' s.scheduled_receive_date, '
|| ' 2,' /* po requisition value */
|| ' :pdoc_type || :pdelimiter || s.orgn_code ||'
|| ' :pdelimiter2 || s.transfer_no, '
|| ' DECODE(s.lot_id, 0, NULL, '
|| ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter3 || '
|| ' l.sublot_no),1,30)),'
|| ' 2,'
|| ' 2,'
|| ' s.to_warehouse,'
|| ' s.transfer_id,' /* B2756431 transaction_id */
|| ' s.transfer_id,' /* B2756431 disposition_id */
|| ' s.transfer_id,' /* B2756431 po_line_id */
|| ' w.mtl_organization_id,' /* B2756431 source_organization_id */
|| ' s.release_quantity1'
|| ' FROM '
|| ' ic_xfer_mst' ||pdblink|| ' s,'
|| ' ic_whse_mst' ||pdblink|| ' w,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id '
|| ' from gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.to_warehouse = i.whse_code '
|| ' and s.from_warehouse = w.whse_code '
|| ' and s.transfer_status IN (1,2) '
|| ' and s.lot_id = l.lot_id'
|| ' and s.item_id = l.item_id'
|| ' and s.release_quantity1 <> 0';
* for the current schedule/whse. The rows will be inserted into the
* database in the procedure sales_forecast which calls this procedure.
* A unique designator must be created for each schedule/whse otherwise a
* number is added to make it unique. If the row exists already the value
* is returned otherwise the table is added to and the new value is returned
* in the out parameter
* HISTORY
* M Craig
************************************************************************/
PROCEDURE build_designator(
poccur IN NUMBER,
pdelimiter IN VARCHAR2,
pdesignator OUT NOCOPY VARCHAR2)
AS
temp_designator VARCHAR2(10);
if a row has alreday been inserted for the schedule and warehouse
use the value from that row and stop the loop
*/
IF desig_tab(i).schedule = sched_dtl_tab(poccur).schedule and
desig_tab(i).whse_code = sched_dtl_tab(poccur).whse_code THEN
pdesignator := desig_tab(i).designator;
* which demands are inserted. The standard procedure inserts into
* msc_st_demands.
* This new procedure inserts into gmp_demands_api. The difference between
* the two tables is the addition of a schedule_id column in
* gmp_demands_api. Also, this version of sales_forecast begins by
* truncating gmp_demands_api and leaves it populated after
* it completes. By contrast, msc_st_demands (which is an APS staging table)
* is immediately truncated after APS reads its data. This difference allows
* gmp_demands_api to be a general purpose version of msc_st_demands.
*
* HISTORY
* P. Dong
* 09/14/01 - Created
* 12/21/01 - Replaced TRUNCATE with DELETE
************************************************************************/
PROCEDURE sales_forecast_api(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_cp_enabled IN BOOLEAN ,
p_run_date IN DATE )
AS
lv_cp_enabled BOOLEAN;
DELETE FROM gmp_demands_api;
* 10/13/99 - Sridhar Added Designator Type column in the insert statement
* 12/17/99 - Changes made to the insert statement for designators,
* changed desig_tab(1).schedule and desig_tab(1).whse_code to
* desig_tab(i).schedule and desig_tab(i).whse_code
* 04/01/00 - Code Fix for Bug# 1137597.
* 07/01/00 - Code Fix for Error in Designators Insert
*
* 02-MAY-2002 Re-engineered By : Abhay Satpute, Rajesh Patangya
* Brief Logic of the new code
* Fetch the following data into PL/SQL tables
* a. Distinct schd/item/whse combinations
* b. Sales order details
* c. Forecast details
* d. Schedule forecast associations
* For each item combination loop through and
* For each change of schedule change mark reuqired
* forecast rows as well note down the stock and ord ind.
* For each item insert sales orders, unconsumed forecast
* or the forecast , based on the indicators
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
*
* Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
* Added handling of NO_DATA_FOUND Exception.
* And return the return_status as TRUE.
****************************************************************************/
PROCEDURE sales_forecast( pdblink IN VARCHAR2,
pinstance_id IN PLS_INTEGER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN,
api_mode IN BOOLEAN)
AS
TYPE gmp_cursor_typ IS REF CURSOR;
/* If forecast and sales order select queries have joins with gmp_item_aps
we need to select only schedules and warehouses here
ORDERED By Schedule , Aps_Item, Organization_id(Warehouse) */
/* Extract Schedule Details */
v_item_sql_stmt := 'SELECT DISTINCT'
|| ' h.schedule,'
|| ' h.schedule_id,'
|| ' h.order_ind,'
|| ' h.stock_ind,'
|| ' a.whse_code,'
|| ' d.orgn_code,'
|| ' a.organization_id, '
|| ' a.aps_item_id inventory_item_id'
|| ' FROM'
|| ' ps_schd_hdr'||pdblink||' h,'
|| ' ps_schd_dtl'||pdblink||' d,'
|| ' gmp_item_aps'||pdblink||' a'
|| ' WHERE'
|| ' h.schedule_id = d.schedule_id'
|| ' and d.orgn_code = a.plant_code'
|| ' and h.active_ind = 1'
|| ' and a.replen_ind = 1'
|| ' and (h.order_ind = 1 or h.stock_ind = 1)'
|| ' and h.delete_mark = 0'
|| ' and a.item_id > 0 '
|| ' ORDER BY'
|| ' h.schedule_id ASC,'
|| ' a.aps_item_id, '
|| ' a.organization_id ' ;
v_sales_sql_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.orgn_code, '
|| ' h.order_no, '
|| ' d.line_id, '
|| ' d.net_price, '
|| ' d.sched_shipdate, '
|| ' d.requested_shipdate, ' /* B2971996 */
|| ' (sum(t.trans_qty) * -1) trans_qty '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim,'
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' op_ordr_hdr'||pdblink||' h, '
|| ' op_ordr_dtl'||pdblink||' d, '
|| ' ic_tran_pnd'||pdblink||' t '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id '
|| ' AND msi.segment1 = iim.item_no '
|| ' and wm.delete_mark = 0 '
|| ' and h.order_id = d.order_id '
|| ' and h.order_status = 0 '
|| ' and h.delete_mark = 0 '
|| ' and h.order_id = t.doc_id '
|| ' and d.line_status >= 0 '
|| ' and d.line_status < 20 '
|| ' and h.from_whse = wm.whse_code '
|| ' and t.line_id = d.line_id '
|| ' and t.item_id = d.item_id '
|| ' and iim.item_id = t.item_id '
|| ' and iim.delete_mark = 0 '
|| ' AND iim.inactive_ind = 0 '
|| ' and t.trans_qty <> 0 '
|| ' and t.completed_ind = 0 '
|| ' and t.delete_mark = 0 '
|| ' and t.doc_type = :popso '
|| ' GROUP BY '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.orgn_code, '
|| ' h.order_no, '
|| ' d.line_id, '
|| ' d.net_price, '
|| ' d.sched_shipdate, '
|| ' d.requested_shipdate ' /* B2971996 */
|| ' UNION ALL '
|| ' SELECT '
|| ' items.inventory_item_id, '
|| ' items.organization_id, '
|| ' org.organization_code, '
|| ' TO_CHAR(hdr.order_number), '
|| ' TO_NUMBER(NULL), '
|| ' TO_NUMBER(NULL), '
|| ' mtl.requirement_date, '
|| ' dtl.request_date, ' /* B2971996 */
|| ' mtl.primary_uom_quantity '
|| ' FROM '
|| ' mtl_demand_omoe'||pdblink||' mtl, '
|| ' mtl_system_items'||pdblink||' items, '
|| ' oe_order_headers_all'||pdblink||' hdr, '
|| ' oe_order_lines_all'||pdblink||' dtl, '
|| ' mtl_parameters'||pdblink||' org '
|| ' WHERE '
|| ' items.organization_id = mtl.organization_id '
|| ' and items.inventory_item_id = mtl.inventory_item_id '
|| ' and NVL(mtl.completed_quantity,0) = 0 '
|| ' and mtl.open_flag = ' || '''Y'''
|| ' and mtl.available_to_mrp = 1 '
|| ' and mtl.parent_demand_id is NULL '
|| ' and mtl.demand_source_type IN (2,8) '
|| ' and mtl.demand_id = dtl.line_id '
|| ' and dtl.header_id = hdr.header_id '
-- B2743626, Changed the join to take process sales order (OMSO)
|| ' and dtl.ship_from_org_id = org.organization_id '
|| ' and org.process_enabled_flag = ' || '''Y'''
|| ' and NOT EXISTS '
|| ' (SELECT 1 '
|| ' FROM so_lines_all'||pdblink||' sl,'
|| ' so_lines_all'||pdblink||' slp,'
|| ' mtl_demand_omoe'||pdblink||' dem'
|| ' WHERE '
|| ' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id) '
|| ' and to_number(dem.demand_source_line) = sl.line_id(+) '
|| ' and dem.demand_source_type in (2,8) '
|| ' and sl.end_item_unit_number IS NULL '
|| ' and slp.end_item_unit_number IS NULL '
|| ' and dem.demand_id = mtl.demand_id '
|| ' and items.effectivity_control = 2) '
|| ' ORDER BY 1,2,7 DESC ' ;
v_forecast_sql_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast_id, '
|| ' h.forecast, '
|| ' d.orgn_code, '
|| ' d.trans_date, '
|| ' (sum(d.trans_qty * -1) ) trans_qty, '
|| ' (sum(d.trans_qty * -1) ) consumed_qty ,'
|| ' 0 use_fcst_flag '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim, '
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' fc_fcst_hdr'||pdblink||' h, '
|| ' fc_fcst_dtl'||pdblink||' d '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id '
|| ' and msi.segment1 = iim.item_no '
|| ' and wm.delete_mark = 0 '
|| ' and h.forecast_id = d.forecast_id '
|| ' and d.forecast_id > 0 '
|| ' and d.item_id = iim.item_id '
|| ' and d.whse_code = wm.whse_code '
|| ' and d.orgn_code = wm.orgn_code '
|| ' and h.delete_mark = 0 '
|| ' and d.delete_mark = 0 '
|| ' and d.trans_qty <> 0 '
|| ' and d.trans_date >= sysdate '
|| ' and EXISTS (SELECT 1 FROM '
|| ' ps_schd_for'||pdblink||' sf, '
|| ' ps_schd_hdr'||pdblink||' sh '
|| ' WHERE sh.schedule_id = sf.schedule_id '
|| ' and sh.delete_mark = 0 '
|| ' and sh.active_ind = 1 '
|| ' and sf.forecast_id = h.forecast_id) '
|| ' GROUP BY '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast, '
|| ' h.forecast_id, '
|| ' d.orgn_code, '
|| ' d.trans_date '
|| ' ORDER BY msi.inventory_item_id,msi.organization_id, '
|| ' d.trans_date DESC ' ;
/* Extract Schedule Forecast Association SQL selection */
v_association_sql_stmt := 'SELECT '
|| ' schedule_id, forecast_id '
|| ' from ps_schd_for'||pdblink
|| ' ORDER BY 1,2 ' ;
Insert_Designator;
l_insert_set_stmt VARCHAR2(2000);
l_fcst_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast_id, '
|| ' d.line_id, '
|| ' h.forecast, '
|| ' h.forecast_set FSET , '
|| ' d.trans_date, '
|| ' d.orgn_code, '
|| ' (d.trans_qty * -1) trans_qty, '
|| ' 0 use_fcst_flag '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim, '
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' fc_fcst_hdr'||pdblink||' h, '
|| ' fc_fcst_dtl'||pdblink||' d '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id ' ;
|| ' and wm.delete_mark = 0 '
|| ' and h.forecast_id = d.forecast_id '
|| ' and d.forecast_id > 0 '
|| ' and d.item_id = iim.item_id '
|| ' and d.whse_code = wm.whse_code '
|| ' and d.orgn_code = wm.orgn_code '
|| ' and h.forecast_set is NOT NULL '
|| ' and h.delete_mark = 0 '
|| ' and d.delete_mark = 0 '
|| ' and d.trans_qty <> 0 '
|| ' ORDER BY wm.mtl_organization_id ,FSET DESC,h.forecast_id ' ;
l_insert_set_stmt :=
' INSERT INTO msc_st_designators ( '
||' designator,forecast_set, organization_id, sr_instance_id, '
||' description, mps_relief, inventory_atp_flag, '
||' designator_type,disable_date,consume_forecast, '
||' update_type,backward_update_time_fence,forward_update_time_fence, '
||' bucket_type,deleted_flag,refresh_id ) '
||' VALUES '
||' ( :p1, :p2, :p3,:p4, '
||' :p5, :p6, :p7, '
||' :p8, :p9, :p10, '
||' :p11, :p12, :p13, '
||' :p14,:p15,:p16 ) ';
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity,demand_class,bucket_type, '
||' demand_type, origination_type, wip_entity_id, '
||' demand_schedule_name,forecast_designator, order_number,'
||' wip_entity_name,sales_order_line_id, selling_price, deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, :p6, '
||' :p7, :p8, :p9, '
||' :p10,:p11,:p12, '
||' :p13,:p14,:p15, '
||' :p16,:p17,:p18 )' ;
l_design_stmt := 'SELECT '||
' forecast_id, '||
' forecast, '||
' substr(forecast,1,10) DESGN, '||
' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
' 1 DESGN_IND ,' ||
' consumption_ind, '||
' backward_time_fence, '||
' forward_time_fence '||
' FROM fc_fcst_hdr'||pdbLink ||
' WHERE delete_mark = 0 '||
' UNION ALL '||
-- Add forecast_sets to the list
' SELECT '||
' -1 , '||
' min(forecast), '||
' forecast_set DESGN , '||
' to_char(NULL) FSET, '||
' 3 DESGN_IND, ' ||
' to_number(NULL), '||
' to_number(NULL), '||
' to_number(NULL) '||
' FROM fc_fcst_hdr'||pdblink ||
' WHERE delete_mark = 0 '||
' AND forecast_set is NOT NULL '||
' GROUP BY forecast_set ' ||
' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
' SELECT '||
' -1, '||
' forecast, '||
' substr(forecast,1,10) DESGN_IND , '||
' to_char(NULL) FSET, '||
' 2 DESGN_IND, '||
' to_number(NULL), '||
' to_number(NULL), '||
' to_number(NULL) '||
' FROM fc_fcst_hdr'||pdblink ||
' WHERE delete_mark = 0 '||
' AND forecast_set is NULL '||
-- With these changes some logic in designator generation has become redundant
*/
OPEN fcst_hdr for l_design_stmt ;
-- insert set name for currrent org
write_fcst_set := TRUE ;
i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
/* Demands Bulk inserts */
d_index := d_index + 1 ;
/* ----------------------- Demands Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_demands (
organization_id,
inventory_item_id,
sr_instance_id,
using_assembly_item_id,
using_assembly_demand_date,
using_requirement_quantity,
demand_class,
bucket_type,
demand_type,
origination_type,
wip_entity_id,
demand_schedule_name,
forecast_designator,
order_number,
wip_entity_name,
sales_order_line_id,
selling_price,
deleted_flag )
VALUES (
f_organization_id(i),
f_inventory_item_id(i),
f_sr_instance_id(i),
f_assembly_item_id(i),
f_demand_date(i),
f_requirement_quantity(i),
null_value, /* demand_class */
1, /* bucket_type */
1, /* demand_type */
29, /* origination_type */
null_value, /* wip_entity_id */
null_value, /* demand_schedule_name */
f_forecast_designator(i),
f_order_number(i),
null_value, /* wip_entity_name */
f_sales_order_line_id(i),
null_value, /* selling_price */
2 /* deleted_flag */
) ;
/* ----------------------- Designator Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_designators (
designator,
forecast_set,
organization_id,
sr_instance_id,
description,
mps_relief,
inventory_atp_flag,
designator_type,
disable_date,
consume_forecast,
update_type,
backward_update_time_fence,
forward_update_time_fence,
bucket_type,
deleted_flag,
refresh_id
)
VALUES (
i_designator(i) ,
i_forecast_set(i) ,
i_organization_id(i),
i_sr_instance_id(i) ,
i_description(i) ,
0, /* mps relief */
0, /* inventory atp flag */
6, /* designator type,For forecast the value will be 6 */
i_disable_date(i) ,
i_consume_forecast(i),
6, /* Update Type,For Process value will be 6 */
i_backward_update_time_fence(i),
i_forward_update_time_fence(i) ,
1, /* bucket_type */
2, /* deleted_flag */
0 /* refresh_id */
) ;
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE consume_forecast( pinventory_item_id IN PLS_INTEGER,
porganization_id IN PLS_INTEGER,
papi_mode IN BOOLEAN )
AS
cfcst_cnt PLS_INTEGER := 0 ;
* and insert into the destination table
* exit when item_id changes after noting down the counter position
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_forecast( pfcst_counter IN NUMBER,
pinventory_item_id IN PLS_INTEGER,
porganization_id IN PLS_INTEGER,
papi_mode IN BOOLEAN)
AS
fcst_i PLS_INTEGER := 0 ;
* and insert into the destination table
* exit when item_id changes after noting down the counter position
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_so( pso_counter IN NUMBER,
pinventory_item_id IN PLS_INTEGER,
porganization_id IN PLS_INTEGER,
papi_mode IN BOOLEAN)
AS
so_i PLS_INTEGER := 0 ;
* insert sales order into msc_st_demand
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
* 05/21/03 - B2971996 - Populating request_date in msc_st_demands table
************************************************************************/
PROCEDURE write_this_so(pcounter IN NUMBER,
sapi_mode IN BOOLEAN)
AS
statement_demands_api VARCHAR2(3000) := NULL ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price,request_date,deleted_flag ) ' /*B2971996*/
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14,:p15 )' ;
* insert forecast into msc_st_demand
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_this_fcst(pcounter IN NUMBER,
fapi_mode IN BOOLEAN)
AS
statement_demands_api VARCHAR2(3000) := NULL ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price, deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14 )' ;
SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
INTO cur_time FROM sys.dual ;
* insert_designator
*
* DESCRIPTION
* Insert all the designator for schedule/item/warehouse combination
* HISTORY
* Created By : Rajesh Patangya
************************************************************************/
PROCEDURE insert_designator IS
i PLS_INTEGER := 1 ;
' INSERT INTO msc_st_designators ( '
||' designator, organization_id, sr_instance_id, '
||' description, mps_relief, inventory_atp_flag, '
||' designator_type ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, :p6, '
||' :p7 ) ';
log_message('Failure occured in insert_designator');
END insert_designator;
* call the insert for resource requirements.
* HISTORY
* M Craig
************************************************************************/
PROCEDURE process_resource_rows(
pfirst_row IN NUMBER,
plast_row IN NUMBER,
pfound_mtl IN NUMBER,
porgn_id IN PLS_INTEGER,
pinstance_id IN PLS_INTEGER,
pinflate_wip IN NUMBER,
pmin_xfer_qty IN NUMBER)
IS
v_resource_usage PLS_INTEGER := 0;
/* Bulk Insert for insert_resource_requirements */
rr_index := rr_index + 1 ;
v_stmt_alt_rsrc := 'INSERT INTO MSC_ST_RESERVATIONS'
||' ( '
||' TRANSACTION_ID , '
||' INVENTORY_ITEM_ID , '
||' ORGANIZATION_ID, '
||' SR_INSTANCE_ID , '
||' REQUIREMENT_DATE , '
||' PARENT_DEMAND_ID , '
||' REVISION , '
||' DISPOSITION_ID , '
||' RESERVED_QUANTITY , '
||' DISPOSITION_TYPE , '
||' SUBINVENTORY , '
||' RESERVATION_TYPE , '
||' DEMAND_CLASS , '
||' AVAILABLE_TO_MRP , '
||' RESERVATION_FLAG , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' SUPPLY_SOURCE_HEADER_ID , '
||' SUPPLY_SOURCE_TYPE_ID , '
||' DELETED_FLAG '
||' ) '
||' SELECT '
||' ((gbo.batch_res_id * 2) + 1), '
/*Sowmya - INVCONV - Start*/
-- ||' gia.aps_item_id , '
/*Sowmya - INVCONV - Start*/
||' gbo.organization_id, '
||' :p1, '
||' gbo.scheduled_ship_date, '
||' gbo.so_line_id , '
||' NULL , '
||' gbo.order_id , '
||' gbo.reserved_qty , '
||' :p2 ,'
||' NULL , '
||' :p3 ,'
||' ool.demand_class_code , '
||' NULL , '
||' :p4 ,'
||' ool.project_id, '
||' ool.task_id, '
||' ppp.planning_group, '
||' ((gbo.batch_id * 2) + 1) , '
||' :p5 ,'
||' :p6 '
||' FROM '
||' gml_batch_so_reservations'||pdblink||' gbo, '
/*Sowmya - INVCONV - start*/
/* ||' (SELECT '
||' DISTINCT item_id, aps_item_id, organization_id , whse_code '
||' FROM gmp_item_aps'||pdblink||') gia, '*/
||' mtl_system_items'||pdblink||' msi, '
/*Sowmya - INVCONV - End*/
||' oe_order_lines_all'||pdblink||' ool, '
||' pjm_project_parameters'||pdblink||' ppp '
||' WHERE '
/*Sowmya - INVCONV - Start*/
-- ||' gbo.item_id = gia.item_id '
/*doubt : gml_batch_so_reservations does not have inventory item id ?? - to include join
for gml table and msi table*/
/*Sowmya - INVCONV - End*/
||' AND gbo.organization_id = gia.organization_id '
||' AND gbo.delete_mark = 0 '
||' AND gbo.so_line_id = ool.line_id '
||' AND ool.project_id = ppp.project_id (+) ';
/* ||' AND EXISTS ( SELECT 1 FROM sy_orgn_mst'||pdblink||' som '
||' WHERE gia.whse_code = som.resource_whse_code )' ;
log_message('Failure occured during the insert into msc_st_reservations');
* update_last_setup_id
*
* DESCRIPTION
* This procedure is triggered by the concurrent program for
* updating the last setup id.
*
* HISTORY
* Namit 14-09-2004 Procedure Created
************************************************************************/
/* INVCONV nsinghi Start */
/* ToDo: Need to make the changes */
/* INVCONV nsinghi End */
PROCEDURE update_last_setup_id (
effbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
f_orgn_code IN NUMBER,
t_orgn_code IN NUMBER
)
IS
TYPE ref_cursor_typ IS REF CURSOR;
x_select VARCHAR2(1000);
lsetup_updated BOOLEAN;
x_select := NULL;
lsetup_updated := TRUE;
X_select := ' SELECT '
||' gbsr.sequence_dependent_id, '
||' crd.resource_id, '
||' grt.instance_id, '
/*Sowmya - INVCONV - Start*/
-- ||' crd.orgn_code, '
||' crd.organization_id, '
/*Sowmya - INVCONV - End*/
||' gbsr.batch_id '
||' FROM gme_batch_step_resources gbsr, '
||' gme_resource_txns grt, '
/*Sowmya - INVCONV - Start*/
/*doubt - which table should be used to fetch the current user id*/
-- ||' sy_orgn_usr sou, '
/*Sowmya - INVCONV - End*/
||' cr_rsrc_dtl crd, '
||' gme_batch_header gbh, '
||' mtl_parameters mp ' /* sowmya added to pick the organization code in the
concurrent pgm*/
||' WHERE gbsr.batch_id = grt.doc_id '
||' AND gbh.batch_id = gbsr.batch_id '
/*Sowmya - INVCONV - Start*/
/* ||' AND gbh.plant_code = crd.orgn_code '
||' AND crd.orgn_code = sou.orgn_code '
||' AND sou.user_id = :user_id ' */
||' AND gbh.organization_id = crd.organization_id '
||' AND mp.organization_id = crd.organization_id '
/* doubt - complete the join the for the user tables*/
/*Sowmya - INVCONV - End*/
||' AND gbsr.batchstep_resource_id = grt.line_id '
||' AND grt.completed_ind = 1 '
||' AND crd.resources = gbsr.resources '
||' AND crd.resources = grt.resources '
||' AND crd.schedule_ind = 2 '
||' AND grt.instance_id IS NOT NULL '
||' AND crd.delete_mark = 0 ';
x_select := x_select
/*Sowmya - INVCONV - Start*/
-- ||' AND crd.orgn_code >= :frm_orgn ' ;
x_select := x_select
-- ||' AND crd.orgn_code <= :to_orgn ' ;
x_select := x_select
||' ORDER BY grt.resources, grt.instance_id, '
||' grt.end_date DESC, grt.poc_trans_id ' ;
OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ f_orgn_code, t_orgn_code;
OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ f_orgn_code;
OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ t_orgn_code;
OPEN cur_lsetup_id FOR x_select /*USING l_user_id*/;
lsetup_updated := FALSE;
IF NOT (lsetup_updated) THEN
lsetup_updated := TRUE;
UPDATE
gmp_resource_instances gri
SET gri.last_setup_id = v_last_setup_id
WHERE
gri.resource_id = v_resource_id
AND gri.instance_id = v_instance_id;
log_message(' NO_DATA_FOUND exception raised in Procedure: gmp_aps_ds_pull.update_last_setup_id ' );
END update_last_setup_id;