Home » SQL & PL/SQL » SQL & PL/SQL » move data from one table to another table
move data from one table to another table [message #604267] Fri, 27 December 2013 01:51 Go to next message
mist598
Messages: 929
Registered: February 2013
Location: Hyderabad
Senior Member
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


Thanks in advance.
Re: move data from one table to another table [message #604269 is a reply to message #604267] Fri, 27 December 2013 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you already done everything, you just have to run it now.

Re: move data from one table to another table [message #604270 is a reply to message #604269] Fri, 27 December 2013 02:41 Go to previous messageGo to next message
mist598
Messages: 929
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel Cadot,

Please go through 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;



Please suggest me.
Thank You.
Re: move data from one table to another table [message #604271 is a reply to message #604270] Fri, 27 December 2013 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Suggest what?
Note we have not your tables, we have not your data, and so on.

Re: move data from one table to another table [message #604274 is a reply to message #604271] Fri, 27 December 2013 03:31 Go to previous messageGo to next message
mist598
Messages: 929
Registered: February 2013
Location: Hyderabad
Senior Member
Hi

I am sorry i created type type1 is table of xxc_forecast_data%rowtype; (At line no 7 in my procedure & Please go through the below tables.
CREATE TABLE apps.xxc_forecast2 
  ( 
     product_category               VARCHAR2(30 byte), 
     product_sub_category           VARCHAR2(30 byte), 
     product_line                   VARCHAR2(30 byte), 
     product_style                  VARCHAR2(30 byte), 
     item_number                    VARCHAR2(240 byte), 
     item_description               VARCHAR2(240 byte), 
     customer_name                  VARCHAR2(240 byte), 
     customer_number                VARCHAR2(240 byte), 
     sales_channel                  VARCHAR2(240 byte), 
     forecast_designator            VARCHAR2(240 byte), 
     forecast_amount_month1         NUMBER, 
     forecast_cost_month1           NUMBER, 
     forecast_mtl_cost_month1       NUMBER, 
     forecast_mtl_ovhd_cost_month1  NUMBER, 
     forecast_res_cost_month1       NUMBER, 
     forecast_op_cost_month1        NUMBER, 
     forecast_ovhd_month1           NUMBER, 
     forecast_units_month1          NUMBER, 
     forecast_amount_month2         NUMBER, 
     forecast_mtl_cost_month2       NUMBER, 
     forecast_mtl_ovhd_cost_month2  NUMBER, 
     forecast_res_cost_month2       NUMBER, 
     forecast_op_cost_month2        NUMBER, 
     forecast_ovhd_month2           NUMBER, 
     forecast_units_month2          NUMBER, 
     forecast_amount_month3         NUMBER, 
     forecast_mtl_cost_month3       NUMBER, 
     forecast_mtl_ovhd_cost_month3  NUMBER, 
     forecast_res_cost_month3       NUMBER, 
     forecast_op_cost_month3        NUMBER, 
     forecast_ovhd_month3           NUMBER, 
     forecast_units_month3          NUMBER, 
     forecast_amount_month4         NUMBER, 
     forecast_mtl_cost_month4       NUMBER, 
     forecast_mtl_ovhd_cost_month4  NUMBER, 
     forecast_res_cost_month4       NUMBER, 
     forecast_op_cost_month4        NUMBER, 
     forecast_ovhd_month4           NUMBER, 
     forecast_units_month4          NUMBER, 
     forecast_amount_month5         NUMBER, 
     forecast_mtl_cost_month5       NUMBER, 
     forecast_mtl_ovhd_cost_month5  NUMBER, 
     forecast_res_cost_month5       NUMBER, 
     forecast_op_cost_month5        NUMBER, 
     forecast_ovhd_month5           NUMBER, 
     forecast_units_month5          NUMBER, 
     forecast_amount_month6         NUMBER, 
     forecast_mtl_cost_month6       NUMBER, 
     forecast_mtl_ovhd_cost_month6  NUMBER, 
     forecast_res_cost_month6       NUMBER, 
     forecast_op_cost_month6        NUMBER, 
     forecast_ovhd_month6           NUMBER, 
     forecast_units_month6          NUMBER, 
     forecast_amount_month7         NUMBER, 
     forecast_mtl_cost_month7       NUMBER, 
     forecast_mtl_ovhd_cost_month7  NUMBER, 
     forecast_res_cost_month7       NUMBER, 
     forecast_op_cost_month7        NUMBER, 
     forecast_ovhd_month7           NUMBER, 
     forecast_units_month7          NUMBER, 
     forecast_amount_month8         NUMBER, 
     forecast_mtl_cost_month8       NUMBER, 
     forecast_mtl_ovhd_cost_month8  NUMBER, 
     forecast_res_cost_month8       NUMBER, 
     forecast_op_cost_month8        NUMBER, 
     forecast_ovhd_month8           NUMBER, 
     forecast_units_month8          NUMBER, 
     forecast_amount_month9         NUMBER, 
     forecast_mtl_cost_month9       NUMBER, 
     forecast_mtl_ovhd_cost_month9  NUMBER, 
     forecast_res_cost_month9       NUMBER, 
     forecast_op_cost_month9        NUMBER, 
     forecast_ovhd_month9           NUMBER, 
     forecast_units_month9          NUMBER, 
     forecast_amount_month10        NUMBER, 
     forecast_mtl_cost_month10      NUMBER, 
     forecast_mtl_ovhd_cost_month10 NUMBER, 
     forecast_res_cost_month10      NUMBER, 
     forecast_op_cost_month10       NUMBER, 
     forecast_ovhd_month10          NUMBER, 
     forecast_units_month10         NUMBER, 
     forecast_amount_month11        NUMBER, 
     forecast_mtl_cost_month11      NUMBER, 
     forecast_mtl_ovhd_cost_month11 NUMBER, 
     forecast_res_cost_month11      NUMBER, 
     forecast_op_cost_month11       NUMBER, 
     forecast_ovhd_month11          NUMBER, 
     forecast_units_month11         NUMBER, 
     forecast_amount_month12        NUMBER, 
     forecast_mtl_cost_month12      NUMBER, 
     forecast_mtl_ovhd_cost_month12 NUMBER, 
     forecast_res_cost_month12      NUMBER, 
     forecast_op_cost_month12       NUMBER, 
     forecast_ovhd_month12          NUMBER, 
     forecast_units_month12         NUMBER, 
     forecast_cost_month2           NUMBER, 
     forecast_cost_month3           NUMBER, 
     forecast_cost_month4           NUMBER, 
     forecast_cost_month5           NUMBER, 
     forecast_cost_month6           NUMBER, 
     forecast_cost_month7           NUMBER, 
     forecast_cost_month8           NUMBER, 
     forecast_cost_month9           NUMBER, 
     forecast_cost_month10          NUMBER, 
     forecast_cost_month11          NUMBER, 
     forecast_cost_month12          NUMBER, 
     fiscal_year                    NUMBER, 
     budget_entity                  VARCHAR2(30 byte), 
     organization_code              VARCHAR2(30 byte), 
     customer_id                    NUMBER, 
     inventory_item_id              NUMBER, 
     prev_customer_id               NUMBER, 
     prev_inventory_item_id         NUMBER, 
     created_by                     NUMBER, 
     last_updated_by                NUMBER, 
     creation_date                  DATE, 
     last_update_date               DATE, 
     attribute1                     VARCHAR2(150 byte), 
     attribute2                     VARCHAR2(150 byte), 
     attribute3                     VARCHAR2(150 byte), 
     attribute4                     VARCHAR2(150 byte), 
     attribute5                     VARCHAR2(150 byte), 
     attribute6                     VARCHAR2(150 byte), 
     attribute7                     VARCHAR2(150 byte), 
     attribute8                     VARCHAR2(150 byte), 
     attribute9                     VARCHAR2(150 byte), 
     attribute10                    VARCHAR2(150 byte), 
     attribute11                    VARCHAR2(150 byte), 
     attribute12                    VARCHAR2(150 byte), 
     attribute13                    VARCHAR2(150 byte), 
     attribute14                    VARCHAR2(150 byte), 
     attribute15                    VARCHAR2(150 byte) 
  ) 


Insert into the below table.
CREATE TABLE APPS.XXC_FORECAST2
(
  PRODUCT_CATEGORY                VARCHAR2(30 BYTE),
  PRODUCT_SUB_CATEGORY            VARCHAR2(30 BYTE),
  PRODUCT_LINE                    VARCHAR2(30 BYTE),
  PRODUCT_STYLE                   VARCHAR2(30 BYTE),
  ITEM_NUMBER                     VARCHAR2(240 BYTE),
  ITEM_DESCRIPTION                VARCHAR2(240 BYTE),
  CUSTOMER_NAME                   VARCHAR2(240 BYTE),
  CUSTOMER_NUMBER                 VARCHAR2(240 BYTE),
  SALES_CHANNEL                   VARCHAR2(240 BYTE),
  FORECAST_DESIGNATOR             VARCHAR2(240 BYTE),
  FORECAST_AMOUNT_MONTH1          NUMBER,
  FORECAST_COST_MONTH1            NUMBER,
  FORECAST_MTL_COST_MONTH1        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH1   NUMBER,
  FORECAST_RES_COST_MONTH1        NUMBER,
  FORECAST_OP_COST_MONTH1         NUMBER,
  FORECAST_OVHD_MONTH1            NUMBER,
  FORECAST_UNITS_MONTH1           NUMBER,
  FORECAST_AMOUNT_MONTH2          NUMBER,
  FORECAST_MTL_COST_MONTH2        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH2   NUMBER,
  FORECAST_RES_COST_MONTH2        NUMBER,
  FORECAST_OP_COST_MONTH2         NUMBER,
  FORECAST_OVHD_MONTH2            NUMBER,
  FORECAST_UNITS_MONTH2           NUMBER,
  FORECAST_AMOUNT_MONTH3          NUMBER,
  FORECAST_MTL_COST_MONTH3        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH3   NUMBER,
  FORECAST_RES_COST_MONTH3        NUMBER,
  FORECAST_OP_COST_MONTH3         NUMBER,
  FORECAST_OVHD_MONTH3            NUMBER,
  FORECAST_UNITS_MONTH3           NUMBER,
  FORECAST_AMOUNT_MONTH4          NUMBER,
  FORECAST_MTL_COST_MONTH4        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH4   NUMBER,
  FORECAST_RES_COST_MONTH4        NUMBER,
  FORECAST_OP_COST_MONTH4         NUMBER,
  FORECAST_OVHD_MONTH4            NUMBER,
  FORECAST_UNITS_MONTH4           NUMBER,
  FORECAST_AMOUNT_MONTH5          NUMBER,
  FORECAST_MTL_COST_MONTH5        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH5   NUMBER,
  FORECAST_RES_COST_MONTH5        NUMBER,
  FORECAST_OP_COST_MONTH5         NUMBER,
  FORECAST_OVHD_MONTH5            NUMBER,
  FORECAST_UNITS_MONTH5           NUMBER,
  FORECAST_AMOUNT_MONTH6          NUMBER,
  FORECAST_MTL_COST_MONTH6        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH6   NUMBER,
  FORECAST_RES_COST_MONTH6        NUMBER,
  FORECAST_OP_COST_MONTH6         NUMBER,
  FORECAST_OVHD_MONTH6            NUMBER,
  FORECAST_UNITS_MONTH6           NUMBER,
  FORECAST_AMOUNT_MONTH7          NUMBER,
  FORECAST_MTL_COST_MONTH7        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH7   NUMBER,
  FORECAST_RES_COST_MONTH7        NUMBER,
  FORECAST_OP_COST_MONTH7         NUMBER,
  FORECAST_OVHD_MONTH7            NUMBER,
  FORECAST_UNITS_MONTH7           NUMBER,
  FORECAST_AMOUNT_MONTH8          NUMBER,
  FORECAST_MTL_COST_MONTH8        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH8   NUMBER,
  FORECAST_RES_COST_MONTH8        NUMBER,
  FORECAST_OP_COST_MONTH8         NUMBER,
  FORECAST_OVHD_MONTH8            NUMBER,
  FORECAST_UNITS_MONTH8           NUMBER,
  FORECAST_AMOUNT_MONTH9          NUMBER,
  FORECAST_MTL_COST_MONTH9        NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH9   NUMBER,
  FORECAST_RES_COST_MONTH9        NUMBER,
  FORECAST_OP_COST_MONTH9         NUMBER,
  FORECAST_OVHD_MONTH9            NUMBER,
  FORECAST_UNITS_MONTH9           NUMBER,
  FORECAST_AMOUNT_MONTH10         NUMBER,
  FORECAST_MTL_COST_MONTH10       NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH10  NUMBER,
  FORECAST_RES_COST_MONTH10       NUMBER,
  FORECAST_OP_COST_MONTH10        NUMBER,
  FORECAST_OVHD_MONTH10           NUMBER,
  FORECAST_UNITS_MONTH10          NUMBER,
  FORECAST_AMOUNT_MONTH11         NUMBER,
  FORECAST_MTL_COST_MONTH11       NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH11  NUMBER,
  FORECAST_RES_COST_MONTH11       NUMBER,
  FORECAST_OP_COST_MONTH11        NUMBER,
  FORECAST_OVHD_MONTH11           NUMBER,
  FORECAST_UNITS_MONTH11          NUMBER,
  FORECAST_AMOUNT_MONTH12         NUMBER,
  FORECAST_MTL_COST_MONTH12       NUMBER,
  FORECAST_MTL_OVHD_COST_MONTH12  NUMBER,
  FORECAST_RES_COST_MONTH12       NUMBER,
  FORECAST_OP_COST_MONTH12        NUMBER,
  FORECAST_OVHD_MONTH12           NUMBER,
  FORECAST_UNITS_MONTH12          NUMBER,
  FORECAST_COST_MONTH2            NUMBER,
  FORECAST_COST_MONTH3            NUMBER,
  FORECAST_COST_MONTH4            NUMBER,
  FORECAST_COST_MONTH5            NUMBER,
  FORECAST_COST_MONTH6            NUMBER,
  FORECAST_COST_MONTH7            NUMBER,
  FORECAST_COST_MONTH8            NUMBER,
  FORECAST_COST_MONTH9            NUMBER,
  FORECAST_COST_MONTH10           NUMBER,
  FORECAST_COST_MONTH11           NUMBER,
  FORECAST_COST_MONTH12           NUMBER,
  FISCAL_YEAR                     NUMBER,
  BUDGET_ENTITY                   VARCHAR2(30 BYTE),
  ORGANIZATION_CODE               VARCHAR2(30 BYTE),
  CUSTOMER_ID                     NUMBER,
  INVENTORY_ITEM_ID               NUMBER,
  PREV_CUSTOMER_ID                NUMBER,
  PREV_INVENTORY_ITEM_ID          NUMBER,
  CREATED_BY                      NUMBER,
  LAST_UPDATED_BY                 NUMBER,
  CREATION_DATE                   DATE,
  LAST_UPDATE_DATE                DATE,
  ATTRIBUTE1                      VARCHAR2(150 BYTE),
  ATTRIBUTE2                      VARCHAR2(150 BYTE),
  ATTRIBUTE3                      VARCHAR2(150 BYTE),
  ATTRIBUTE4                      VARCHAR2(150 BYTE),
  ATTRIBUTE5                      VARCHAR2(150 BYTE),
  ATTRIBUTE6                      VARCHAR2(150 BYTE),
  ATTRIBUTE7                      VARCHAR2(150 BYTE),
  ATTRIBUTE8                      VARCHAR2(150 BYTE),
  ATTRIBUTE9                      VARCHAR2(150 BYTE),
  ATTRIBUTE10                     VARCHAR2(150 BYTE),
  ATTRIBUTE11                     VARCHAR2(150 BYTE),
  ATTRIBUTE12                     VARCHAR2(150 BYTE),
  ATTRIBUTE13                     VARCHAR2(150 BYTE),
  ATTRIBUTE14                     VARCHAR2(150 BYTE),
  ATTRIBUTE15                     VARCHAR2(150 BYTE)
)



Thank You
Re: move data from one table to another table [message #604275 is a reply to message #604274] Fri, 27 December 2013 03:49 Go to previous messageGo to next message
mist598
Messages: 929
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Michel Cadot,

I have so many rows on my tables.

Thank You
Re: move data from one table to another table [message #604295 is a reply to message #604275] Fri, 27 December 2013 12:47 Go to previous messageGo to next message
pablolee
Messages: 2617
Registered: May 2007
Location: Scotland
Senior Member
You need to do a couple of things here.
1. STOP.
2.do this in Sql.

As far as I can see from your over complex example and description, there is absolutely no need for pl/sql.
Re: move data from one table to another table [message #604360 is a reply to message #604267] Sun, 29 December 2013 06:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2324
Registered: May 2013
Location: World Wide on the Web
Senior Member
mist598 wrote on Fri, 27 December 2013 13:21

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


Well there you go! That's a design flaw. A date is a DATE which has all the parts, a day, month, year...
"Jan-2500"? What's that actually? What is the data type for these months columns? I bet you have declared a varchar2 or number, isn't it? If you declare the DATE column as date data type, you won't face such issues at all.
You have ignored normalization and now you will have to pay for it. Read Normalization

Quote:
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,.....

If you have a DATE data type for your monthly sales data, you could simply retrieve the required month's data using proper date format.

Quote:
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

Ok, I saw your PL/SQL code. As Pablolee already suggested, you need to do all that in plain SQL.

Quote:
well while when i am inserting data am passing year,month as parameters but 23000 data is moving it should get like that.

Again, wrong. Why do you need to pass parameters? Say NO to pl/sql for this. A sql would just require a where clause to filter the data based on dates. A proper date format could simply do the work for you.

Quote:
its urgent

Shocked

[edit : fixed typo]

[Updated on: Sun, 29 December 2013 06:17]

Report message to a moderator

Re: move data from one table to another table [message #604365 is a reply to message #604360] Sun, 29 December 2013 07:57 Go to previous message
John Watson
Messages: 4557
Registered: January 2010
Location: Global Village
Senior Member
Lalit Kumar B wrote
Well there you go! That's a design flaw. A date is a DATE which has all the parts, a day, month, year...
"Jan-2500"? What's that actually? What is the data type for these months columns? I bet you have declared a varchar2 or number, isn't it? If you declare the DATE column as date data type, you won't face such issues at all.
├Łou can't use dates here because (in this case) EBS doesn't use them. If you look up gl.gl_periods you'll see that General Ledger accounting periods are defined as
PERIOD_TYPE 	VARCHAR2 	(15) 	Yes 	Accounting period type
PERIOD_YEAR 	NUMBER 	(15) 	Yes 	Accounting period year
PERIOD_NUM 	NUMBER 	(15) 	Yes 	Accounting period number
So even though OP appears to be using months as his accounting period, he can't actually refer to them by date.

But apart from this, I think there may be some major issues.

@mist598 -
Firstly, you are creating these objects in the APPS schema. This is seriously wrong, and it may mean that you are invalidating your support licence and making future patching and upgrades impossible. You need to create and register your own custom schema. There are supplied procedures for doing this. Have you studied the guidelines for EBS customization?

Secondly, why are you doing this at all? In my experience, EBS is infinitely configurable, and customizations are virtually NEVER needed. The moment an EBS implementer mentions "customization" he is actually saying "I don't know how to do this with the standard facilities.". For example, have you looked at Advanced Planning for your forecasting?

Thirdly, to repeat what others have said, using PL/SL for this is more complicated and astronomically slower to execute than SQL would be.
Previous Topic: UPDATE MULTIPLE ROWS IN ONE UPDATE PASS
Next Topic: SQL Problem
Goto Forum:
  


Current Time: Wed Sep 17 19:22:11 CDT 2014

Total time taken to generate the page: 0.21424 seconds