CREATE OR REPLACE procedure sort_proc IS

TYPE cur_typ IS REF CURSOR;
 c           cur_typ;

v_sqlquery         varchar2(400);
v_entity_name      varchar2(400);
v_sqlquery1        varchar2(1400);
 v_sql_sort        varchar2(400);
g_errm             varchar2(400);

 V_PLN_DMND_NAME        varchar2(200);     
 V_PLN_CAT              varchar2(200); 
 V_DMND_SHIP_NAME       varchar2(200);
 V_DUE_DATE             varchar2(200);
 V_ADJ_DUE_DATE_TIME    varchar2(200);
 V_DMND_LINE_ITEM_NAME  varchar2(200);
 V_REQ_ITEM             varchar2(200);
 V_REQ_QTY              varchar2(200);
 V_DC                   varchar2(200);              
 V_BA_LIMIT             varchar2(200);
 V_BL_LIMIT             varchar2(200);
 V_SORT_KEY             varchar2(200);
 V_CUST_NO              varchar2(200);
 V_DOMN_NAME            varchar2(200);
 V_SEQ_CTR              varchar2(200);
 V_STYLE_COLOR          varchar2(200);
 V_Coalition            varchar2(200);
 V_SBU_CODE             varchar2(200);
 V_SUB_SBU_CODE         varchar2(200);
 V_PROD_LINE_CODE       varchar2(200);
 V_PLAN_UNIT_CODE       varchar2(200);
 V_MKTG_UNIT_CODE       varchar2(200);
 V_sort_due_date        varchar2(200);
 V_sort_dmd_type        varchar2(200); 
 V_sort_mfg_routes      varchar2(200);
 V_sort_adj_due_date    varchar2(200);
 V_sort_dmd_seq         varchar2(200);
 V_sort_dmd_qty         varchar2(200);
 

cursor c1 is 
select entity_name,MAP_DEMND,
 (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order1) as srt_order1,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order2) as srt_order2,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order3) as srt_order3,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order4) as srt_order4,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order5) as srt_order5,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order6) as srt_order6,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order7) as srt_order7
from (select s.* ,m.priority,m.MAP_DEMND
from  map_sort_entity m ,sort_sequence s
where m.entity_type=s.entity_type
and s.IS_DEFAULT='N'
order by m.priority)
where rownum <2;


BEGIN
----------------
         for r_c1 in  c1 loop
           execute immediate 'truncate table temp_sort';
           begin
           v_sqlquery:=' select  * from demand where ' ||  r_c1.MAP_DEMND ||'=:PLAN_UNIT_CODE' ;
             ---------------------------------------------          
             OPEN c FOR v_sqlquery USING r_c1.entity_name;
             LOOP
             BEGIN
             FETCH c INTO  V_PLN_DMND_NAME ,     V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
                                    V_DMND_LINE_ITEM_NAME   ,V_REQ_ITEM,V_REQ_QTY, V_DC ,              
                                    V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
                                    V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
                                    V_SUB_SBU_CODE          ,
                                    V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
                                    V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq  ,
                                    V_sort_dmd_qty   ;
             EXIT WHEN c%NOTFOUND;
       
             insert into temp_sort (PLN_DMND_NAME ,       PLN_CAT , DMND_SHIP_NAME ,DUE_DATE ,ADJ_DUE_DATE_TIME,
                                    DMND_LINE_ITEM_NAME   ,REQ_ITEM,REQ_QTY, DC ,              
                                    BA_LIMIT ,BL_LIMIT ,SORT_KEY,CUST_NO ,DOMN_NAME,
                                    SEQ_CTR,STYLE_COLOR,Coalition,SBU_CODE,
                                    SUB_SBU_CODE          ,
                                    PROD_LINE_CODE,PLAN_UNIT_CODE,MKTG_UNIT_CODE ,sort_due_date,
                                    sort_dmd_type, sort_mfg_routes,sort_adj_due_date,sort_dmd_seq  ,
                                    sort_dmd_qty   )  
                            values
                                  ( V_PLN_DMND_NAME ,     V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
                                    V_DMND_LINE_ITEM_NAME   ,V_REQ_ITEM,V_REQ_QTY, V_DC ,              
                                    V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
                                    V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
                                    V_SUB_SBU_CODE          ,
                                    V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
                                    V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq  ,
                                    V_sort_dmd_qty   ); 
             
             exception
             WHEN OTHERS THEN
             g_errm := SUBSTR(SQLERRM, 1 , 64);
             dbms_output.put_line(g_errm);
             END;                   
             END LOOP;
             commit;
             CLOSE c;
              
              --------------------------
              --Following delte is not working
              --------------------------
              
              
               --dbms_output.put_line(  r_c1.MAP_DEMND||'--->>'||r_c1.entity_name);
               EXECUTE IMMEDIATE 'DELETE FROM demand WHERE' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
               USING r_c1.entity_name;
               commit;

           exception
           WHEN OTHERS THEN
           g_errm := SUBSTR(SQLERRM, 1 , 64);  
                    
          end;
         end loop;

---------------


 
END;
/
show errors