CREATE OR REPLACE FUNCTION CMP_RATE (P_START IN DATE DEFAULT TRUNC(SYSDATE-1), P_END IN DATE DEFAULT TRUNC(SYSDATE-1))
 
  RETURN cmp_rate_ct PIPELINED IS
 ---return type cmp_rate_ct as table of cmp_rate_t? ---
 ----aenderung  result_rec cmp_rate_ct := cmp_rate_t--war falsch!--

--declare rec?---
 --- declare rec;
  result_rec cmp_rate_t := cmp_rate_t (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
   
  BEGIN


                                  FOR rec IN (WITH masterdata AS (SELECT DISTINCT c.facility,
                                                             c.work_center,
                                                             c.operation,
                                                             m.route,
                                                             m.sps_number,
                                                             m.sps_id,
                                                             m.sps_sequence_number,
                                                             CASE WHEN m.sps_sequence_number = '3' THEN 'PROD'
                                                                                      ELSE 'REWORK'
                                                                                 END AS PROD_CATEGORY
                                                                                 ---Fehler view does not exist--auf dwh_admin.* geändert!--
                                                      FROM dwh_admin.dwh_plan_workcenter_oper c JOIN dwh_admin.dwh_route_operation_sps m ON c.facility = m.facility
                                                                                                                       AND c.operation = m.operation
                                                      WHERE c.datasource = 'Workbench200'
                                                      AND c.work_center IN ('CMP012','CMP064_HIT', 'CMP064_POLY', 'CMP013','CMP064_PSG/', 'CMP017', 'CMPP64_BPSG', 'CMPP64_POLY', 'CMPP64_W')
                                                      AND m.sps_sequence_number IN ('3', '7')),
                                       moves AS (SELECT t.facility,
                                                        t.route,
                                                        t.basic_type,
                                                        t.process_group,
                                                        t.operation,
                                                        t.work_center,
                                                        t.equipment,
                                                        t.lot,
                                                        t.sps_number,
                                                        CASE WHEN m.PROD_CATEGORY = 'PROD' THEN COALESCE(t.qty_out_1,0) ELSE 0 END AS qty_out_prod,
                                                        CASE WHEN m.PROD_CATEGORY = 'PROD' THEN COALESCE(t.quantity_exec, t.qty_out_1) ELSE 0 END AS qty_exec_prod,
                                                        CASE WHEN m.PROD_CATEGORY = 'REWORK' THEN COALESCE(t.qty_out_1,0) ELSE 0 END AS qty_out_rework,
                                                        CASE WHEN m.PROD_CATEGORY = 'REWORK' THEN COALESCE(t.quantity_exec, t.qty_out_1) ELSE 0 END AS qty_exec_rework,
                                                        m.PROD_CATEGORY,
                                                        m.sps_sequence_number,
                                                        t.TIME_STAMP
                                                   FROM GLB_RBL.DWH_WIP_DATA_ALL_TRANSACTIONS t JOIN masterdata m ON t.facility = m.facility
                                                                                                                 AND t.operation = m.operation
                                                                                                                 AND t.sps_sid = m.sps_id
                                                                                                                 AND t.work_center = m.work_center
                                                 WHERE t.date_stamp >= P_START
                                                     AND t.date_stamp < P_END
                                                     
                                                    AND t.transcode IN ('MVNS', 'MVOU', 'RVLT', 'RWLT', 'SHLT')
                                                    AND t.owner NOT IN ('ANKO'))
                                  SELECT trunc(time_stamp) AS date_stamp,
                                         work_center,
                                         route,
                                         basic_type,
                                         process_group,
                                         facility,
                                         sps_number,
                                         sum(qty_exec_rework) AS qty_exec_rework,
                                         sum(qty_exec_prod) AS qty_exec_prod
                                  FROM moves
                                  GROUP BY trunc(time_stamp),
                                         work_center,
                                         route,
                                         basic_type,
                                         process_group,
                                         facility,
                                         sps_number)
               
    LOOP
---Fehler korrigiert! result_rec.
       result_rec.TIME_STAMP   := rec.TIME_STAMP;
       ---result_rec.  := rec. fuer alle? nein!---
       -- result_rec.work_center   := rec.work_center;
        --result_rec.route   := rec.route;
        --result_rec.basic_type   := rec.basic_type;
        --result_rec.process_group   := rec.process_group;
        --result_rec.facility   := rec.facility;
        --result_rec.sps_number   := rec.sps_number;
        
        
        pipe row(result_rec);
     --pipe row (cmp_rate_t);

    END LOOP;

    RETURN;
  END CMP_RATE;