1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

move data from one table to another table

Discussion in 'Oracle Financials' started by mist, Dec 27, 2013.

  1. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi all,

    I had a custom table called sales_data in that table there are columns like JAn,FEB,upto DEC including other columns so in each month there is some data total data is 23000 count but each month has has specific data like JAn-2500,FEB-2000 like that it has total 23000 records

    My Requirement is i have to move data from one table to another table that too if i will pass jan only jan data should move like that feb,march,.....
    in my table there is no month column i had get it from another table called gl_periods and by using cursor and case function i have written the code
    well while when i am inserting data am passing year,month as parameters but 23000 data is moving it should get like that.

    Please suggest me.its urgent


    Thank You
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    table gl_periods is partitioning table?
     
  3. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi Sergey Krasnoslobodtsev ,

    Thank You for reply.Please see the below procedure.

    CREATE OR REPLACE PROCEDURE APPS.copy_sales_to_forecast(p_fiscal_year varchar2,p_month number)
    IS
    CURSOR C1 IS select period_year,period_num,start_date,end_date from apps.gl_periods
    where period_set_name='Accounting'
    and period_year=p_fiscal_year
    and period_num<=p_month;
    type type1 is table of xxc_forecast2%rowtype;
    t1 type1;
    BEGIN
    FOR CREC IN C1 LOOP
    BEGIN
    DELETE FROM xxc_forecast2
    where fiscal_year = crec.period_year
    and attribute1='Copied From Sales to Forecast Table of Month '||crec.period_num;
    END;
    SELECT
    product_category ,
    product_sub_category ,
    product_line ,
    product_style ,
    item_number ,
    item_description ,
    customer_name ,
    customer_number ,
    sales_channel ,
    null ,
    CASE
    WHEN crec.period_num=1 THEN sales_amount_month1
    ELSE 0
    END Transaction_quantity_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_cost_month1
    ELSE 0
    END item_cogs_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_mtl_cost_month1
    ELSE 0
    END item_material_cogs_period1 ,
    CASE
    WHEN crec.period_num=1 THEN sales_mtl_ovhd_cost_month1
    ELSE 0
    END item_mtl_ovhd_cogs_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_res_cost_month1
    ELSE 0
    END item_resource_cogs_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_op_cost_month1
    ELSE 0
    END item_op_cogs_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_ovhd_month1
    ELSE 0
    END item_ovhd_cogs_period1,
    CASE
    WHEN crec.period_num=1 THEN sales_units_month1
    ELSE 0
    END extended_amount_us_period1,
    CASE
    WHEN crec.period_num=2 THEN sales_amount_month2
    ELSE 0
    END Transaction_quantity_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_mtl_cost_month2
    ELSE 0
    END item_material_cogs_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_mtl_ovhd_cost_month2
    ELSE 0
    END item_mtl_ovhd_cogs_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_res_cost_month2
    ELSE 0
    END item_resource_cogs_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_op_cost_month2
    ELSE 0
    END item_op_cogs_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_ovhd_month2
    ELSE 0
    END item_ovhd_cogs_period2,
    CASE
    WHEN crec.period_num=2 THEN sales_units_month2
    ELSE 0
    END extended_amount_us_period2,
    CASE
    WHEN crec.period_num=3 THEN sales_amount_month3
    ELSE 0
    END Transaction_quantity_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_mtl_cost_month3
    ELSE 0
    END item_material_cogs_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_mtl_ovhd_cost_month3
    ELSE 0
    END item_mtl_ovhd_cogs_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_res_cost_month3
    ELSE 0
    END item_resource_cogs_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_op_cost_month3
    ELSE 0
    END item_op_cogs_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_ovhd_month3
    ELSE 0
    END item_ovhd_cogs_period3,
    CASE
    WHEN crec.period_num=3 THEN sales_units_month3
    ELSE 0
    END extended_amount_us_period3,
    CASE
    WHEN crec.period_num=4 THEN sales_amount_month4
    ELSE 0
    END Transaction_quantity_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_mtl_cost_month4
    ELSE 0
    END item_material_cogs_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_mtl_ovhd_cost_month4
    ELSE 0
    END item_mtl_ovhd_cogs_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_res_cost_month4
    ELSE 0
    END item_resource_cogs_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_op_cost_month4
    ELSE 0
    END item_op_cogs_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_ovhd_month4
    ELSE 0
    END item_ovhd_cogs_period4,
    CASE
    WHEN crec.period_num=4 THEN sales_units_month4
    ELSE 0
    END extended_amount_us_period4,
    CASE
    WHEN crec.period_num=5 THEN sales_amount_month5
    ELSE 0
    END Transaction_quantity_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_mtl_cost_month5
    ELSE 0
    END item_material_cogs_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_mtl_ovhd_cost_month5
    ELSE 0
    END item_mtl_ovhd_cogs_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_res_cost_month5
    ELSE 0
    END item_resource_cogs_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_op_cost_month5
    ELSE 0
    END item_op_cogs_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_ovhd_month5
    ELSE 0
    END item_ovhd_cogs_period5,
    CASE
    WHEN crec.period_num=5 THEN sales_units_month5
    ELSE 0
    END extended_amount_us_period5,
    CASE
    WHEN crec.period_num=6 THEN sales_amount_month6
    ELSE 0
    END Transaction_quantity_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_mtl_cost_month6
    ELSE 0
    END item_material_cogs_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_mtl_ovhd_cost_month6
    ELSE 0
    END item_mtl_ovhd_cogs_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_res_cost_month6
    ELSE 0
    END item_resource_cogs_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_op_cost_month6
    ELSE 0
    END item_op_cogs_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_ovhd_month6
    ELSE 0
    END item_ovhd_cogs_period6,
    CASE
    WHEN crec.period_num=6 THEN sales_units_month6
    ELSE 0
    END extended_amount_us_period6,
    CASE
    WHEN crec.period_num=7 THEN sales_amount_month7
    ELSE 0
    END Transaction_quantity_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_mtl_cost_month7
    ELSE 0
    END item_material_cogs_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_mtl_ovhd_cost_month7
    ELSE 0
    END item_mtl_ovhd_cogs_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_res_cost_month7
    ELSE 0
    END item_resource_cogs_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_op_cost_month7
    ELSE 0
    END item_op_cogs_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_ovhd_month7
    ELSE 0
    END item_ovhd_cogs_period7,
    CASE
    WHEN crec.period_num=7 THEN sales_units_month7
    ELSE 0
    END extended_amount_us_period7,
    CASE
    WHEN crec.period_num=8 THEN sales_amount_month8
    ELSE 0
    END Transaction_quantity_period8,
    CASE
    WHEN crec.period_num=8 THEN sales_mtl_cost_month8
    ELSE 0
    END item_material_cogs_period8,
    CASE
    WHEN crec.period_num=8 THEN sales_mtl_ovhd_cost_month8
    ELSE 0
    END item_mtl_ovhd_cogs_period8,
    CASE
    WHEN crec.period_num=8 THEN sales_res_cost_month8
    ELSE 0
    END item_resource_cogs_period7,
    CASE
    WHEN crec.period_num=8 THEN sales_op_cost_month8
    ELSE 0
    END item_op_cogs_period8,
    CASE
    WHEN crec.period_num=8 THEN sales_ovhd_month8
    ELSE 0
    END item_ovhd_cogs_period8,
    CASE
    WHEN crec.period_num=8 THEN sales_units_month8
    ELSE 0
    END extended_amount_us_period8,
    CASE
    WHEN crec.period_num=9 THEN sales_amount_month9
    ELSE 0
    END Transaction_quantity_period9,
    CASE
    WHEN crec.period_num=9 THEN sales_mtl_cost_month9
    ELSE 0
    END item_material_cogs_period9,
    CASE
    WHEN crec.period_num=9 THEN sales_mtl_ovhd_cost_month9
    ELSE 0
    END item_mtl_ovhd_cogs_period9,
    CASE
    WHEN crec.period_num=9 THEN sales_res_cost_month9
    ELSE 0
    END item_resource_cogs_period7,
    CASE
    WHEN crec.period_num=9 THEN sales_op_cost_month9
    ELSE 0
    END item_op_cogs_period9,
    CASE
    WHEN crec.period_num=9 THEN sales_ovhd_month9
    ELSE 0
    END item_ovhd_cogs_period9,
    CASE
    WHEN crec.period_num=9 THEN sales_units_month9
    ELSE 0
    END extended_amount_us_period9,
    CASE
    WHEN crec.period_num=10 THEN sales_amount_month10
    ELSE 0
    END Transaction_quantity_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_mtl_cost_month10
    ELSE 0
    END item_material_cogs_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_mtl_ovhd_cost_month10
    ELSE 0
    END item_mtl_ovhd_cogs_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_res_cost_month10
    ELSE 0
    END item_resource_cogs_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_op_cost_month10
    ELSE 0
    END item_op_cogs_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_ovhd_month10
    ELSE 0
    END item_ovhd_cogs_period10,
    CASE
    WHEN crec.period_num=10 THEN sales_units_month10
    ELSE 0
    END extended_amount_us_period10,
    CASE
    WHEN crec.period_num=11 THEN sales_amount_month11
    ELSE 0
    END Transaction_quantity_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_mtl_cost_month11
    ELSE 0
    END item_material_cogs_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_mtl_ovhd_cost_month11
    ELSE 0
    END item_mtl_ovhd_cogs_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_res_cost_month11
    ELSE 0
    END item_resource_cogs_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_op_cost_month11
    ELSE 0
    END item_op_cogs_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_ovhd_month11
    ELSE 0
    END item_ovhd_cogs_period11,
    CASE
    WHEN crec.period_num=11 THEN sales_units_month11
    ELSE 0
    END extended_amount_us_period11,
    CASE
    WHEN crec.period_num=12 THEN sales_amount_month12
    ELSE 0
    END Transaction_quantity_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_mtl_cost_month12
    ELSE 0
    END item_material_cogs_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_mtl_ovhd_cost_month12
    ELSE 0
    END item_mtl_ovhd_cogs_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_res_cost_month12
    ELSE 0
    END item_resource_cogs_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_op_cost_month12
    ELSE 0
    END item_op_cogs_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_ovhd_month12
    ELSE 0
    END item_ovhd_cogs_period12,
    CASE
    WHEN crec.period_num=12 THEN sales_units_month12
    ELSE 0
    END extended_amount_us_period12,
    CASE
    WHEN crec.period_num=2 THEN sales_cost_month2
    ELSE 0
    END item_cogs_period2,
    CASE
    WHEN crec.period_num=3 THEN sales_cost_month3
    ELSE 0
    END item_cogs_period3,
    CASE
    WHEN crec.period_num=4 THEN sales_cost_month4
    ELSE 0
    END item_cogs_period4,
    CASE
    WHEN crec.period_num=5 THEN sales_cost_month5
    ELSE 0
    END item_cogs_period5,
    CASE
    WHEN crec.period_num=6 THEN sales_cost_month6
    ELSE 0
    END item_cogs_period6,
    CASE
    WHEN crec.period_num=7 THEN sales_cost_month7
    ELSE 0
    END item_cogs_period7,
    CASE
    WHEN crec.period_num=8 THEN sales_cost_month8
    ELSE 0
    END item_cogs_period8,
    CASE
    WHEN crec.period_num=9 THEN sales_cost_month9
    ELSE 0
    END item_cogs_period9,
    CASE
    WHEN crec.period_num=10 THEN sales_cost_month10
    ELSE 0
    END item_cogs_period10,
    CASE
    WHEN crec.period_num=11 THEN sales_cost_month11
    ELSE 0
    END item_cogs_period11,
    CASE
    WHEN crec.period_num=12 THEN sales_cost_month12
    ELSE 0
    END item_cogs_period12,
    a.fiscal_year ,
    a.budget_entity ,
    a.organization_code,
    a.customer_id ,
    a.inventory_item_id ,
    NULL,
    NULL,
    a.created_by ,
    a.last_updated_by ,
    a.creation_date ,
    a.last_update_date ,
    'Copied From Sales to Forecast Table of Month '||crec.period_num,
    a.attribute2,
    a.attribute3 ,
    a.attribute4 ,
    a.attribute5 ,
    a.attribute6 ,
    a.attribute7 ,
    a.attribute8 ,
    a.attribute9 ,
    a.attribute10,
    a.attribute11,
    a.attribute12,
    a.attribute13,
    a.attribute14,
    a.attribute15
    bulk collect into t1
    FROM xxc_sales_data a
    where a.fiscal_year = crec.period_year
    FORALL i IN t1.first .. t1.last
    INSERT INTO xxc_forecast2 VALUES t1(i); -->insert
    commit;
    END LOOP;
    END;
    /


    Thank You
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    your procedure works optimally on time?

    line 439: error .. absent ;

    line 440 : why you use forall here if it is possible to use simply the operator of Insert?

    your problem simply isn't solved...
    optimization code of your procedure for the current structures of tables this simplest decision..