[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE backward_update_time_fence IS TABLE OF mrp_forecast_designators.backward_update_time_fence%TYPE
INDEX BY BINARY_INTEGER;
i_backward_update_time_fence backward_update_time_fence ;
TYPE forward_update_time_fence IS TABLE OF mrp_forecast_designators.foreward_update_time_fence%TYPE
INDEX BY BINARY_INTEGER;
i_forward_update_time_fence forward_update_time_fence ;
l_fcst_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' nvl(sy.organization_id,msi.organization_id), ' /*B4931593*/
/*B4931593 - The forecast data is moved to the migrated organization.*/
|| ' h.forecast_id, '
|| ' d.line_id, '
|| ' h.forecast, '
|| ' h.forecast_set FSET , '
|| ' d.trans_date, '
|| ' d.orgn_code, '
|| ' (d.trans_qty * -1) trans_qty, '
|| ' 1 write_row_flag '
|| ' FROM '
|| ' mtl_system_items msi, '
|| ' ic_item_mst iim, '
|| ' ic_whse_mst wm, '
|| ' fc_fcst_hdr h, '
|| ' sy_orgn_mst sy, '/*B4931593*/
|| ' fc_fcst_dtl d '
|| ' WHERE '
|| ' msi.organization_id = wm.organization_id '
|| ' and sy.orgn_code = d.orgn_code '/*B4931593*/
|| ' and sy.migrated_ind = 1 '/*B4931593*/
|| ' 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.forecast_set is NOT NULL '
|| ' and h.delete_mark = 0 '
|| ' and d.delete_mark = 0 '
|| ' and d.trans_qty <> 0 '
-- || ' ORDER BY FSET, wm.organization_id ,h.forecast_id, msi.inventory_item_id ' ;
l_design_stmt := 'SELECT '||
' forecast_id, '||
' forecast, '||
' substr(forecast,1,10) DESGN, '||
' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
' 1 DESGN_IND ,' ||
' nvl(consumption_ind, 2), '||
' backward_time_fence, '||
' forward_time_fence '||
' FROM fc_fcst_hdr'||
' 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'||
' WHERE delete_mark = 0 '||
' AND forecast_set is NOT NULL '||
' GROUP BY forecast_set ' ||
' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
inserted to the discrete forecasting module. */
-- BEGIN
l_validation_stmt := ' SELECT forecast_designator, forecast_set '||
' FROM mrp_forecast_designators '||
' WHERE (forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_set || '''' ||
' OR forecast_designator = '|| '''' || fcst_hdr_tbl(fcst_locn).fcst_name|| '''' || ' ) '||
' AND organization_id = '||fcst_dtl_tbl(lp_cnt).organization_id ;
fcst_valid_tbl.DELETE;
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 */
fi_index := fi_index + 1 ;
/* ----------------------- Designator Insert --------------------- */
i := 1 ;
INSERT INTO mrp_forecast_designators (
forecast_designator,
forecast_set,
organization_id,
description,
disable_date,
consume_forecast,
update_type,
backward_update_time_fence,
foreward_update_time_fence,
bucket_type,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES (
i_designator(i) ,
i_forecast_set(i) ,
i_organization_id(i),
i_description(i) ,
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 */
SYSDATE,
0,
SYSDATE,
0
) ;
/* ----------------------- Forecast Item Insert --------------------- */
i := 1 ;
INSERT INTO mrp_forecast_items (
organization_id,
inventory_item_id,
forecast_designator,
last_update_date, /* Confirm WHO Column values*/
last_updated_by,
creation_date,
created_by
)
VALUES (
f_organization_id(i),
f_inventory_item_id(i),
f_forecast_designator(i),
SYSDATE,
0,
SYSDATE,
0
) ;
/* ----------------------- Forecast Interface Insert --------------------- */
i := 1 ;
INSERT INTO mrp_forecast_interface (
organization_id,
inventory_item_id,
forecast_date,
quantity,
bucket_type,
forecast_designator,
process_status,
confidence_percentage,
workday_control,
last_update_date, /* Confirm WHO Column values*/
last_updated_by,
creation_date,
created_by
)
VALUES (
f_int_organization_id(i),
f_int_inventory_item_id(i),
f_int_forecast_date(i),
f_int_quantity(i),
1, /* bucket_type */
f_int_forecast_designator(i),
2, /* process_status */
100, /* Need to confirm the value */
3, /* workday_control */
SYSDATE,
0,
SYSDATE,
0
) ;