How to check with table for cursor..?

10-11  Source: Network gathering  Views:0 

Advertisement
How to check with table for cursor..?
Here I have Table temp_final_plan
Here i want to update if already exit...below is the procedure....
CREATE OR REPLACE PROCEDURE spu_final_profit_plan
AS
-- Constant declarations
  ln_errnum number := 0;
-- Variable declarations
   ls_errmsg app_errors.err_msg%TYPE;
   ls_appmsg app_errors.app_msg%TYPE;
   ls_appid  app_errors.app_id%TYPE;
-- Cursor declaration for final_update_el
CURSOR cur_final_update_el IS
    select '910' ent,
           '9127316' center,
           post_acct,
           sum(avg_mtd_01) sum_avg_mtd_01,
           sum(avg_mtd_02) sum_avg_mtd_02,
           sum(avg_ytd_01) sum_avg_ytd_01,
           sum(avg_ytd_02) sum_avg_ytd_02
      from mon_act_cypy
     where rec_type = 'A'
       and sum_flag = 'D'
       and yr = '2008'
       and substr(ctr_or_hier, 1, 2) = 'el'
       and ent || sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999100')
     group by post_acct
    having sum(avg_mtd_01) <> 0
        or sum(avg_mtd_02) <> 0
        or sum(avg_ytd_01) <> 0
        or sum(avg_ytd_02) <> 0;
-- Cursor declaration for final_update
CURSOR cur_final_update IS
    select b.plan_ent b_plan_ent,
           b.plan_ctr b_plan_ctr,
           a.post_acct a_post_acct,
           sum(a.avg_mtd_01) sum_avg_mtd_01,
           sum(a.avg_mtd_02) sum_avg_mtd_02,
           sum(a.avg_ytd_01) sum_ytd_mtd_01,
           sum(a.avg_ytd_02) sum_ytd_mtd_02
      from mon_act_cypy a,
           plan_unit_tbl b
     where a.ent || a.ctr_or_hier = b.ent || b.ctr_or_hier
       and a.rec_type = 'A'
       and a.sum_flag = 'D'
       and a.yr = '2008'
       and b.hier_tbl_num = '001'
       and a.ent || a.sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999100')
     group by b.plan_ent, b.plan_ctr, a.post_acct
    having sum(a.avg_mtd_01) <> 0
        or sum(a.avg_mtd_02) <> 0
        or sum(a.avg_ytd_01) <> 0
        or sum(a.avg_ytd_02) <> 0;
-- Begin the procedure body
   BEGIN
-- Insert / Update final profit plan for final_update query using cursor
   FOR rec_final_update_el IN cur_final_update_el
   LOOP
   EXIT WHEN rec_final_update_el%NOTFOUND;
   IF rec_final_update_el. THEN
      UPDATE temp_final_plan
         SET sum_avg_mtd_01 = rec_final_update_el.sum_avg_mtd_01,
             sum_avg_mtd_02 = rec_final_update_el.sum_avg_mtd_02,       
             sum_avg_ytd_01 = rec_final_update_el.sum_avg_ytd_01,       
             sum_avg_ytd_02 = rec_final_update_el.sum_avg_ytd_02,       
       WHERE ent = rec_final_update_el.ent
         AND center = rec_final_update_el.center
         AND post_acct = rec_final_update_el.post_acct;
   ELSE
      INSERT INTO temp_final_plan VALUES(rec_final_update_el.ent,
                                         rec_final_update_el.center,
                                         rec_final_update_el.post_acct,
                                         rec_final_update_el.sum_avg_mtd_01,
                                         rec_final_update_el.sum_avg_mtd_02,
                                         rec_final_update_el.sum_avg_ytd_01,
                                         rec_final_update_el.sum_avg_ytd_02);
   END IF;
   END LOOP;
-- Insert / Update final profit plan for final_update query using cursor
   FOR rec_final_update IN cur_final_update
   LOOP
   EXIT WHEN rec_final_update%NOTFOUND;
   IF rec_final_update. THEN
      UPDATE temp_final_plan
         SET sum_avg_mtd_01 = rec_final_update.sum_avg_mtd_01,
             sum_avg_mtd_02 = rec_final_update.sum_avg_mtd_02,       
             sum_avg_ytd_01 = rec_final_update.sum_avg_ytd_01,       
             sum_avg_ytd_02 = rec_final_update.sum_avg_ytd_02,       
       WHERE ent = rec_final_update.b_plan_ent
         AND center = rec_final_update.b_plan_ctr
         AND post_acct = rec_final_update.a_post_acct;
   ELSE
      INSERT INTO temp_final_plan VALUES(rec_final_update.b_plan_ent,
                                         rec_final_update.b_plan_ctr,
                                         rec_final_update.a_post_acct,
                                         rec_final_update.sum_avg_mtd_01,
                                         rec_final_update.sum_avg_mtd_02,
                                         rec_final_update.sum_avg_ytd_01,
                                         rec_final_update.sum_avg_ytd_02);
   END IF;
   END LOOP;
-- EXCEPTION handling section
   EXCEPTION
-- Fire OTHERS Exception case by default
   WHEN OTHERS THEN
-- ROLL BACK Transaction, if any failure
   ROLLBACK;
   ln_errnum := SQLCODE;
   ls_errmsg := SUBSTR(SQLERRM, 1, 100);
-- Log the ERRORS into APP_ERRORS table using SPU_LOG_ERRORS procedure
   spu_log_errors(ln_errnum, ls_errmsg, ls_appid, ls_appmsg);
-- End of the stored procedure
END spu_final_profit_plan;
[\pre]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
I'm not sure what you mean by, 'How to check with table for cursor..?' but I'll offer a comment on your Code Snippet. I think you want to know how to check if a record exists so you know if you need to perform an INSERT or an UPDATE.
Here is a snippet of your code. I'll put my comments in "Comment" style in your code.
-- Insert / Update final profit plan for final_update query using cursor
   FOR rec_final_update_el IN cur_final_update_el
   LOOP
/* There is no need to test for %NOTFOUND since you are using Cursor FOR Loop! 
** This construct automatically exits when the last record is processed. */
   EXIT WHEN rec_final_update_el%NOTFOUND;
/* Is this where you would like to know how to Check if the record already exist??
** I asked this because, 'rec_final_update_el.' is not valid syntax.  Are you looking for
** an Cursor Attribute or Method you can check here? 
** I would suggest a Primary Key or Unique Index on ENT, CENTER, and POST_ACCT
** on the TEMP_FINAL_PLAN table. Then simply perform an INSERT and code an
** Exception to UPDATE when you get a DUP_VAL_ON_INDEX exception.  Otherwise,
** you will need to simply run an Implicit or Explicit Cursor to test if the row exists and
** use this return value to determine if you should INSERT or UPDATE.  */
   IF rec_final_update_el. THEN
      UPDATE temp_final_plan
         SET sum_avg_mtd_01 = rec_final_update_el.sum_avg_mtd_01,
             sum_avg_mtd_02 = rec_final_update_el.sum_avg_mtd_02,       
             sum_avg_ytd_01 = rec_final_update_el.sum_avg_ytd_01,       
             sum_avg_ytd_02 = rec_final_update_el.sum_avg_ytd_02,       
       WHERE ent = rec_final_update_el.ent
         AND center = rec_final_update_el.center
         AND post_acct = rec_final_update_el.post_acct;
   ELSE
      INSERT INTO temp_final_plan VALUES(rec_final_update_el.ent,
                                         rec_final_update_el.center,
                                         rec_final_update_el.post_acct,
                                         rec_final_update_el.sum_avg_mtd_01,
                                         rec_final_update_el.sum_avg_mtd_02,
                                         rec_final_update_el.sum_avg_ytd_01,
                                         rec_final_update_el.sum_avg_ytd_02);
   END IF;
   END LOOP;I hope I've answered your question, but if I haven't please provide more details so we can better understand your request.
Craig...
Related articles
  • How to check with table for cursor..? 10-11

    How to check with table for cursor..? Here I have Table temp_final_plan Here i want to update if already exit...below is the procedure.... CREATE OR REPLACE PROCEDURE spu_final_profit_plan AS -- Constant declarations   ln_errnum number := 0; -- Varia

  • How to check the tables 11-30

    Dear experts! Thank you for your attention! I have studied the SAP SD for half a year, though I still don't know how to check certain table like: KNVS---customer master shipping data KNBK---customer master (bank detail) ADR---address table ect. could

  • How to check PSA Table Structure ? 11-30

    Hi Friends,    Can you pls any one tell me how to find out PSA Table and how to check PSA Table Structure? .Thanks in advance. Regards, CSHi Sri, Goto RSA1 trans> select your DS> right clcik> Manage> you will get a Pop-up with all the PSA requ

  • How to check small table scan full table scan if we  will use index  in where clause. 11-30

    How to check small table scan full table scan if i will use index column in where clause. Is there example link there i can  test small table scan full table  if index is used in where clause.Use explain plan on your statement or set autotrace traceo

  • How to know the tables for a datasource like 2lis_17_order 11-30

    Hi, How to know the tables for a data source like 2lis_17_order, i have checked in Extract structure of that data source, only fields are visible but how will i know that these fields are getting extracted from so and so tables? pls respond. thanks i

  • How's this partition table for my server? 11-30

    How's this partition table for my server?  Web/DNS/DHCP/Mail Server. hdd1: ~8gb total /boot - 100mb swap - 1gb / - rest of drive hdd2: ~155gb total /var - whole drive This is a Compaq ProLiant 5500 server with 10 drives.  Will most things be installe

  • How to create internal table for a structure in BSP 10-11

    hi , I have created a Structure in BSP.I want to create an internal table for that Structure. But in my coding ie. <% data: begin of itab_1 .                  include type zuvendstr.                  data:end of itab_1.                  data wa_str l

  • How to check the tables we have created in JDBC?? 10-11

    I have created a table called COFFEE2 in a datasource called temp which is in SQL server...How to check the table values?? i went into administrative tools and odbc cource then i could see the data source i have created but how to see the table?? Can

  • Is there a solution for iphone 4 too like iphone5, It doesnt have tab Cellular. How to check call duration for total dialled calls (after resting) on a particular day? 11-30

    How to check call duration for total dialled calls (after resting) on a particular day in iphone 4? Is there a solution like iphone 5, i.e.Settings > Cellular.if the device is unlocked cellular is called mobileRead other 2 answers

  • How to work with otool for pre check app befor submit to app 10-11

    any body  work with otool for pre check app befor submit to app.Searching in Google, I found these explanations : Timmy OTool is a graphical front end for the command line tool "OTool". It can be used to examine frameworks, applications, static

  • How to check in Cube for Non cumulative calculation? 10-11

    Hi All/surendra (Inventory Specialist)   I am now supporting the Inventory cube. I never worked in inventory. I have read all the documents like a. How to Inventory b. Surendra's Inventory with 3parts c. Non cumulative.. But none of the above docs ga

  • How to Fill Setup tables for MAP (IS- Retail) 11-30

    Hello, I am working on MAP scenario, I found the following data sources as a part of it. Can anybody please tell me how to fill the setup tables for these and what are the base tables for it?? 2LIS_40_REVAL 2LIS_40_S202 2LIS_40_S207 2LIS_40_S208 Your

  • How ias integrate with Snacktory for getting main text from an html page 10-11

    Hi All, i am new to endeca and ias, i have an requirement, need to get main text from whole html page before ias save text to Endeca_Document_Text property, as ias save all text in page to endeca_document_text property, it is not ok for reading when

  • How to deal with credentials for external applications using a Java Client/ 10-11

    Hi Guys, This is the case. I am integrating an external application with an ADF Application. I have implemented some programmatic ViewObjects that are being filled up by a REST Java Client Wrapper. Everything is working fine but the issue is that the

  • How to set BDCDATA table for a SELECT-OPTIONS object? 11-30

    Dear experts, I have a transaction with PARAMETERS and SELECT-OPTIONS objects. This transaction will be called by another program by using CALL TRANSACTION 'mytransaction' USING mybdcdata statement. Below is some of the called transaction's source co

  • How to check which table is used 11-30

    After creating RFQ , i want to check which table is used  in creating RFQ. how can i see that data has gone or not into the database table.Hi See the Table EKKO and EKPO only with EKKO-BSTYP = A Regards AnjiRead other 5 answers

  • How to check a value for paramter query 11-30

    I am passing a paramter value from my text box to my sql statement and I would like to check if the value is entered into a text box. If you have any idea please share it with me, i have been struggling with this for many days.Code below is how i try

  • How to deal with file(for example .xml)? what format of dir should be? 10-11

    I'd like to operate the file in disk, and want to use relative directory? How to deal with file dir? what format of dir should be?Hi Kamlesh, Thanks for your response. Actually, In the "Process External Bank Statement" window, i see that there a

  • How to check Business Partner for archiving ? 11-30

    Hi friends, I want to check the BP for Archiving. So I am trying to set the Archiving Flag in BP Status Tab But the Archiving Falg Check box is never in Display Mode and so I am not able to check the BOx. What is the use of that box if it can't be ac

  • How to check silent switches for adobe reader installation ? 11-30

    Hi All, We are working on one project where we have to install adobe remotely and silently on around 5K machines. I am wondering how to check all adobe reader silent installation switches. Any help would be great Thanks And Regards Vaibhav Singh [per