Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

Oracle procedure help

I have attached a procedure

There is a part of the code

ELSIF v_itm_type in ('S','A') then            

             
        
for serv_prc_rec in serv_prc_cur(get_ship_interface_rec.item_id
                                          ,get_ship_interface_rec.price_code) loop
                                                             
                                                        
               v_price := serv_prc_rec.price;
If the value is not found here then I have to go to

   
         If v_itm_type = 'A' then
               for get_valid_attrib_price_rec in get_valid_attrib_price(get_ship_interface_rec.item_id) loop
                  
                    v_new_order_qty := get_ship_interface_rec.order_qty;
              v_price := get_valid_attrib_price_rec.price;
               End loop;
         Elsif v_itm_type = 'S' then
                for get_vserv_charge_codes_rec in get_vserv_charge_codes(get_ship_interface_rec.item_id) loop
                  
                    v_new_order_qty := get_ship_interface_rec.order_qty;
                v_price := get_vserv_charge_codes_rec.charge;
                End loop;                
         End if;
      
How will I add that? I mean if the for loop is null then go to the if condition
WRITE-DB-DTL-PRC1.sql
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>If the value is not found here then I have to go to

Do you mean returns a null value or there are no rows in the loop?

If the first one:

v_price := serv_prc_rec.price;

If v_price is null then

         If v_itm_type = 'A' then
...
end if;

If the second, I would create a loop counter variable and check it for 0 after the loop is completed.
Avatar of anumoses

ASKER

can you tell me how for the second one?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Its inserting the wrong data. I will create some test sample by tomorrow and then post it.
i put some DBMS messages. Its not going to the loop counter. Its only going to

 If  v_itm_type = 'P' then

Not going to  If  v_itm_type = 'S' then
I'm not sure sample data will help.

You have a lot of cursors that we would not have the table definitions for and your code calls a lot of custom functions like " get_ship_interface_rec.order_qty".

I would add debug statements using dbms_output.put_line and test the procedure from sqlplus.  then you can try to get a handle on where the logic is breaking.
>> If  v_itm_type = 'P' then

Then all the types must be a 'P'?
Yes. It its a P(Product) then then only this cursor

Cursor prod_prc_cur(cp_prod_typ in Varchar
                    ,cp_prc_cde  in Varchar) is
  Select price
    from product_prices
   where inv_prod_type = cp_prod_typ
     and price_code = cp_prc_cde;
Else S or A (S- Service) (A-Attribute)
>>Yes. It its a P(Product) then then only this cursor

I'm not understanding the problem.

From the code you posted, if it get's a 'P', then it should not ever get to the 'S','A' check since that is an ELSIF.
IF v_itm_type = 'P'  then


              for prod_prc_rec in prod_prc_cur(v_prd_type
                                          ,v_price_code) loop
              v_price := prod_prc_rec.price;
                  v_new_order_qty := get_ship_interface_rec.order_qty;
                                
        end loop;      
        
ELSIF v_itm_type in ('S','A') then      

       select count(*) into v_price_count from serv_attr_prices
      where item_id =  get_ship_interface_rec.item_id
            and price_code = get_ship_interface_rec.price_code;
             
        loop_counter := 0;

              for serv_prc_rec in serv_prc_cur(get_ship_interface_rec.item_id
                                          ,get_ship_interface_rec.price_code) loop
                                                             
                                                        
               v_price := serv_prc_rec.price;
               loop_counter := loop_counter + 1;  


                  DBMS_OUTPUT.PUT_LINE('counter: ' || loop_counter );       
      if loop_counter > 0 then        

               
         If v_itm_type = 'A' then
               for get_valid_attrib_price_rec in get_valid_attrib_price(get_ship_interface_rec.item_id) loop
                  -- Calculating negative quantity
                    v_new_order_qty := get_ship_interface_rec.order_qty;
              v_price := get_valid_attrib_price_rec.price;

               End loop;
         Elsif v_itm_type = 'S' then
                for get_vserv_charge_codes_rec in get_vserv_charge_codes(get_ship_interface_rec.item_id) loop
                  -- Calculating negative quantity
                    v_new_order_qty := get_ship_interface_rec.order_qty;
                v_price := get_vserv_charge_codes_rec.charge;

                End loop;                
         End if;
      
    End if;

 End loop;
                                
 
End If;
OK, what is that supposed to show me?

You have an if-then-else-if that says if v_itm_type = 'P'  then only do what follows.  If it isn't a 'P's then and onyl them check to see if it is either an 'S' or an 'A'.

You said you retrieved a 'P' so it will never fall into the 'S' or 'A' check.

I'm not understanding what you are wanting to do.  It appears you have a flaw in the logic of your code and we really cannot help with that since we do not understand your data or business requirements.
I have explained with data. Hope this helps you to guide me.
problem.sql
thanks