Example For BulkCollect and For all

CREATE OR REPLACE PROCEDURE proc_forecast_copy (

p_bu VARCHAR2,

p_plnt VARCHAR2,

p_fc_no VARCHAR2,

p_user VARCHAR2,

p_out_fc OUT VARCHAR2

)

IS

CURSOR c1

IS

SELECT *

FROM mfg_sales_forecast_hd

WHERE msfhd_bu = p_bu

AND msfhd_plnt = p_plnt

AND msfhd_fcast_no = p_fc_no;

 

cr1 c1%ROWTYPE;

 

TYPE t1 IS RECORD (

msfln_bu mfg_sales_forecast_ln.msfln_bu%TYPE,

msfln_plnt mfg_sales_forecast_ln.msfln_plnt%TYPE,

msfln_fcast_no mfg_sales_forecast_ln.msfln_fcast_no%TYPE,

msfln_seq_no mfg_sales_forecast_ln.msfln_seq_no%TYPE,

msfln_prod_id mfg_sales_forecast_ln.msfln_prod_id%TYPE,

msfln_prod_rev mfg_sales_forecast_ln.msfln_prod_rev%TYPE,

msfln_sales_area mfg_sales_forecast_ln.msfln_sales_area%TYPE,

msfln_terr_id mfg_sales_forecast_ln.msfln_terr_id%TYPE,

msfln_sub_terr_id mfg_sales_forecast_ln.msfln_sub_terr_id%TYPE,

msfln_sales_person mfg_sales_forecast_ln.msfln_sales_person%TYPE,

msfln_bucket mfg_sales_forecast_ln.msfln_bucket%TYPE,

msfln_fcast_qty mfg_sales_forecast_ln.msfln_fcast_qty%TYPE,

msfln_cre_by mfg_sales_forecast_ln.msfln_cre_by%TYPE,

msfln_cre_date mfg_sales_forecast_ln.msfln_cre_date%TYPE,

msfln_upd_by mfg_sales_forecast_ln.msfln_upd_by%TYPE,

msfln_upd_date mfg_sales_forecast_ln.msfln_upd_date%TYPE,

msfln_cust_id mfg_sales_forecast_ln.msfln_cust_id%TYPE

);

 

TYPE t2 IS TABLE OF t1;

 

t3 t2;

v_fc_no mfg_sales_forecast_ln.msfln_fcast_no%TYPE;

BEGIN

SELECT MAX (NVL (msfhd_fcast_no, 0)) + 1

INTO v_fc_no

FROM mfg_sales_forecast_hd

WHERE msfhd_bu = p_bu AND msfhd_plnt = p_plnt;

 

OPEN c1;

FETCH c1 INTO cr1;

 

INSERT INTO mfg_sales_forecast_hd

(msfhd_bu, msfhd_plnt, msfhd_fcast_date, msfhd_fcast_no,

msfhd_name,

msfhd_fcast_level, msfhd_cons_flag,

msfhd_fwd_cons_days, msfhd_bkd_cons_days, msfhd_cre_by,

msfhd_cre_date, msfhd_status, msfhd_upd_by, msfhd_upd_date,

msfhd_date_from, msfhd_date_to, msfhd_name2, msfhd_fc_mthd,

msfhd_fc_prd)

VALUES (p_bu, p_plnt, SYSDATE, v_fc_no,

'Forecast for' || ' ' || TO_CHAR (SYSDATE, 'MON'),

cr1.msfhd_fcast_level, cr1.msfhd_cons_flag,

cr1.msfhd_fwd_cons_days, cr1.msfhd_bkd_cons_days, p_user,

SYSDATE, 'E', NULL, NULL,

SYSDATE, (SYSDATE + 30), cr1.msfhd_name2, cr1.msfhd_fc_mthd,

cr1.msfhd_fc_prd);

 

CLOSE c1;

 

SELECT p_bu,p_plnt,v_fc_no,msfln_seq_no, msfln_prod_id, msfln_prod_rev, msfln_sales_area,

msfln_terr_id, msfln_sub_terr_id, msfln_sales_person,

msfln_bucket, msfln_fcast_qty,p_user,sysdate,null,null, msfln_cust_id

BULK COLLECT INTO t3

FROM mfg_sales_forecast_ln

WHERE msfln_bu = p_bu AND msfln_plnt = p_plnt AND msfln_fcast_no = p_fc_no;

 

FORALL i IN 1 .. t3.COUNT

INSERT INTO mfg_sales_forecast_ln VALUES t3(i);

COMMIT;

END;