general_init_columns.lwm 1.53 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            declare
			  v_insert_str             varchar2(300);
			  v_Sql_Str                varchar2(300);
			  v_value                  varchar2(300);
			  v_prompt                 varchar2(30);
			  v_sheet_name             varchar2(30);
			begin
			  
			  delete from fnd_transfer_table tt where tt.header_id = ${/parameter/@header_id};
			  for c_sheets in  (select il.source_table from fnd_interface_lines il where il.line_number = 1 and il.header_id = ${/parameter/@header_id}) loop
			    v_sheet_name := c_sheets.source_table;
			  for i in 1..500 loop
			     v_Sql_Str := 'select attribute_'||i||'
			   from fnd_interface_lines fl 
			   where fl.line_number = 1 and fl.header_id =:header_id and fl.source_table = :sheet_name';  
			     execute immediate v_Sql_Str
			       into v_value using ${/parameter/@header_id},v_sheet_name;  
			    if v_value is null then
			      exit;
			    end if;
			    v_prompt := 'attribute_'||i;
			    v_insert_str:='insert into fnd_transfer_table values(:1,:2,:3,:4)';
			   execute  immediate v_insert_str using ${/parameter/@header_id},v_prompt,v_value,v_sheet_name;
			  end loop;   
			  end loop; 
			end;
            ]]></bm:update-sql>
            <!-- <bm:parameters>
                <bm:parameter name="header_id"/>
            </bm:parameters> -->
        </bm:operation>
    </bm:operations>
</bm:model>