PROCEDURE ins_mat_data
IS
   CURSOR c1
   IS
      SELECT mii_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
             bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
             SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0, 
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
             item_ig_code, item_anly_code_01, item_anly_code_02,
             NVL (bsl_qty_bu, 0) / 1000 jci_wt_kg, bsl_cost_1 jci_cost,
             bsl_val_1 jci_val, 0 jcrf_wt_kg, 0 jcrf_val, 0 jcro_wt_kg,
             0 jcro_val
        FROM os_batch_stk_ledger, om_item, ot_mat_iss_head, ot_mat_iss_item
       WHERE bsl_batch_no = :rep_value_1
         AND mih_sys_id = mii_mih_sys_id
         AND mih_charge_code = :rep_value_1
         AND mii_acth_code BETWEEN :rep_value_3 AND :rep_value_4
         AND bsl_i_sys_id = mii_sys_id
         AND bsl_h_sys_id = mii_mih_sys_id
         AND mii_item_code = bsl_item_code
         AND bsl_item_code = item_code
         AND bsl_txn_code IN ('SS-MIS', 'SC-MIS')
         AND (   item_ig_code BETWEEN '11' AND '19'
              OR item_ig_code BETWEEN '71' AND '79'
              OR item_ig_code BETWEEN '91' AND '96'
             )
      UNION ALL
      SELECT mii_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
             bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
             SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0, 
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
             item_ig_code, item_anly_code_01, item_anly_code_02, 0 jci_wt_kg,
             bsl_cost_1 jci_cost, 0 jci_val, 0 jcrf_wt_kg, 0 jcrf_val,
             NVL (bsl_qty_bu, 0) / 1000 jcro_wt_kg, bsl_val_1 jcro_val
        FROM os_batch_stk_ledger, om_item, ot_mat_iss_head, ot_mat_iss_item
       WHERE bsl_batch_no = :rep_value_1
         AND mih_sys_id = mii_mih_sys_id
         AND mih_charge_code = :rep_value_1
         AND mii_acth_code BETWEEN :rep_value_3 AND :rep_value_4
         AND bsl_i_sys_id = mii_sys_id
         AND bsl_h_sys_id = mii_mih_sys_id
         AND mii_item_code = bsl_item_code
         AND bsl_item_code = item_code
         AND bsl_txn_code IN ('SS-MIO', 'S-MIOC')
         AND (   item_ig_code BETWEEN '11' AND '19'
              OR item_ig_code BETWEEN '71' AND '79'
              OR item_ig_code BETWEEN '91' AND '96'
             )
      UNION ALL
      SELECT mri_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
             bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
             item_flex_06 item_short_name, item_ig_code, item_anly_code_01,
             item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
             NVL (bsl_qty_bu, 0) / 1000 jcrf_wt_kg, bsl_val_1 jcrf_val,
             0 jcro_wt_kg, 0 jcro_val
        FROM os_batch_stk_ledger, om_item, ot_mat_ret_head, ot_mat_ret_item
       WHERE bsl_batch_no = :rep_value_1
         AND mrh_sys_id = mri_mrh_sys_id
         AND mrh_charge_code = :rep_value_1
         AND mri_acth_code BETWEEN :rep_value_3 AND :rep_value_4
         AND bsl_i_sys_id = mri_sys_id
         AND bsl_h_sys_id = mri_mrh_sys_id
         AND mri_item_code = bsl_item_code
         AND bsl_item_code = item_code
         AND bsl_txn_code IN ('SS-MTR')
         AND (   item_ig_code BETWEEN '11' AND '19'
              OR item_ig_code BETWEEN '71' AND '79'
              OR item_ig_code BETWEEN '91' AND '96'
             )
      UNION ALL
      SELECT adjh_flex_05 jci_str_no, bsl_txn_code jci_mih_txn_code,
             bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
             SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0, 
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name, 
             item_ig_code, item_anly_code_01,
             item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
             NVL (bsl_qty_bu, 0) / 1000 jcrf_wt_kg, bsl_val_1 jcrf_val,
             0 jcro_wt_kg, 0 jcro_val
        FROM os_batch_stk_ledger, om_item, ot_adj_head, ot_adj_item
       WHERE bsl_batch_no = :rep_value_1
         AND adjh_sys_id = adji_adjh_sys_id
         AND adjh_flex_02 = :rep_value_1
         AND adjh_flex_05 BETWEEN :rep_value_3 AND :rep_value_4
         AND bsl_i_sys_id = adji_sys_id
         AND bsl_h_sys_id = adji_adjh_sys_id
         AND adji_item_code = bsl_item_code
         AND bsl_item_code = item_code
         AND bsl_txn_code IN ('SS-MRC')
         AND (   item_ig_code BETWEEN '11' AND '19'
              OR item_ig_code BETWEEN '71' AND '79'
              OR item_ig_code BETWEEN '91' AND '96'
             )
      UNION ALL
      SELECT adjh_flex_05 jci_str_no, bsl_txn_code jci_mih_txn_code,
             bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
             SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0, 
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
             item_ig_code, item_anly_code_01,
             item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
             0 jcrf_wt_kg, 0 jcrf_val, NVL (bsl_qty_bu, 0) / 1000 jcro_wt_kg,
             bsl_val_1 jcro_val
        FROM os_batch_stk_ledger, om_item, ot_adj_head, ot_adj_item
       WHERE bsl_batch_no = :rep_value_1
         AND adjh_sys_id = adji_adjh_sys_id
         AND adjh_flex_02 = :rep_value_1
         AND adjh_flex_05 BETWEEN :rep_value_3 AND :rep_value_4
         AND bsl_i_sys_id = adji_sys_id
         AND bsl_h_sys_id = adji_adjh_sys_id
         AND adji_item_code = bsl_item_code
         AND bsl_item_code = item_code
         AND bsl_txn_code IN ('SSMRO')
         AND (   item_ig_code BETWEEN '11' AND '19'
              OR item_ig_code BETWEEN '71' AND '79'
              OR item_ig_code BETWEEN '91' AND '96'
             );
BEGIN
   FOR i IN c1
   LOOP
      INSERT INTO ow_mat_usage_temp
                  (jci_str_no, jci_mih_txn_code, jci_mih_no,
                   jci_mii_item_code, item_short_name, item_ig_code,
                   item_anly_code_01, item_anly_code_02, jci_wt_kg,
                   jci_cost, jci_val, jcrf_wt_kg, jcrf_val,
                   jcro_wt_kg, jcro_val
                  )
           VALUES (i.jci_str_no, i.jci_mih_txn_code, i.jci_mih_no,
                   i.jci_mii_item_code, i.item_short_name, i.item_ig_code,
                   i.item_anly_code_01, i.item_anly_code_02, i.jci_wt_kg,
                   i.jci_cost, i.jci_val, i.jcrf_wt_kg, i.jcrf_val,
                   i.jcro_wt_kg, i.jcro_val
                  );
   END LOOP;
END;