[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

PL/SQL LOOP CURSOR

I'm new to Oracle PL/SQL and need assistance with the following.  I have a table that is a sales order line table.  The first 2 line for each sales order is use for quote (labor and material).  I want to read that table and take the other lines in that's not quote and subtract from the first 2 lines depending on if it's material or labor.

The image below is the select statement to pull from the CUST_ORDER_DETAIL table.  In the description, it is listed as LABOR for labor quote and MATERIAL for material quote.  This sales order, we'll keep adding additional labor and material lines as we progress.  Example, sales order 12121 has 5 lines, but we can be adding more lines going forward.  If it's labor, we populate the "SERVICE_ID" field.  If it's material we will populate "DESCRIPTION", but will not put in keyword "LABOR" or "MATERIAL" since that is reserve for quote.
cust_order_detail table
My output result is below.  As we continue to add in additional lines, it should look at the "USER_AMT" field and take that away from the total lines that's not quote for that same sales order.
Result
Here's my attempt at it, but don't think it is right.  Can someone take a look and see what I did wrong?  I do not know if I need 2 cursors. Basically request is to make sure that the USER_AMT is populated so I can use that as a reference point of the quoted amount since "UNIT_PRICE" will be changing as we add more lines.  Then second request is to take total material or labor (excluding quote from line 1 and 2) and subtract from "USER_AMT" and then update "UNIT_PRICE" with the difference for the "MATERIAL" or "LABOR" quote line.

DECLARE 

  V_LABOR_AMT NUMBER(8,4);
  V_MATERIAL_AMT NUMBER(8,4);

CURSOR C1 IS
SELECT  COD.ORDER_ID, COD.LINE, COD.UNIT_PRICE, COD.SERVICE_ID, COD.DESCRIPTION, COD.USER_AMT
FROM    CUST_ORDER_DETAIL COD
WHERE   COD.ORDER_ID IN ('12121', '11233')
ORDER BY COD.ORDER_ID, COD.LINE;

CURSOR C2 IS
SELECT  COD.ORDER_ID, COD.LINE, COD.UNIT_PRICE, COD.SERVICE_ID, COD.DESCRIPTION, COD.USER_AMT
FROM    CUST_ORDER_LINE COD
WHERE   COD.ORDER_ID IN ('12121', '11233')
ORDER BY COD.ORDER_ID, COD.LINE;

BEGIN

V_LABOR_AMT := 0; 
V_MATERIAL_AMT := 0;


    FOR R IN C1
    LOOP
      --LABOR
      IF R.DESCRIPTION = 'LABOR' AND R.USER_AMT IS NULL  THEN
       
        --IF USER_AMT IS NULL THEN UPDATE UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    USER_AMT = R.UNIT_PRICE
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'LABOR';
          COMMIT;
      --MATERIAL
      ELSIF R.DESCRIPTION = 'MATERIAL' AND R.USER_AMT IS NULL THEN

         --IF USER_AMT IS NULL THEN UPDATE WITH UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    USER_AMT = R.UNIT_PRICE
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'MATERIAL';
          COMMIT;
      END IF;
    END LOOP;
      
    FOR I IN C2
    LOOP
      --LABOR
      IF I.DESCRIPTION NOT IN ('LABOR', 'MATERIAL') AND R.SERVICE_ID IS NOT NULL  THEN
        V_LABOR_AMT := V_LABOR_AMT + I.UNIT_PRICE
        
        --IF USER_AMT IS NULL THEN UPDATE UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    UNIT_PRICE = R.USER_AMT - SUM(V_LABOR_AMT)
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'LABOR';
          COMMIT;
      --MATERIAL
      ELSIF R.DESCRIPTION NOT IN ('LABOR', 'MATERIAL') AND R.SERVICE_ID IS NULL THEN
        V_MATERIAL-AMT := V_MATERIAL_AMT + I.UNIT_PRICE
        
         --IF USER_AMT IS NULL THEN UPDATE WITH UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    UNIT_PRICE = R.UNIT_AMT - SUM(V_MATERIAL_AMT)
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'MATERIAL';
          COMMIT;
      END IF;
    END LOOP;
    
END;

Open in new window

0
holemania
Asked:
holemania
1 Solution
 
Geert GruwezOracle dbaCommented:
why commit inside the procedure ?
and why so many times ?

do you now the "case" clause  ?
0
 
slightwv (䄆 Netminder) Commented:
Please post your sample data as text so we can copy and paste it.

No, you don't need two cursors.  Just like your previous question, I'm also not convinced you need the loop.

The cursor is selecting from CUST_ORDER_DETAIL and you are updating CUST_ORDER_LINE.

The first image shows CUST_ORDER_DETAIL.  I'm assuming the second image is an updated CUST_ORDER_DETAIL.

I don't see that in your coded attempt.

Anyway, I can take the data from the first image and generate the data in the second image from a single SELECT statement.

I don't see where the CUST_ORDER_LINE table is part of this?

Here is my test setup and single SELECT that produces the second image from the first:
create table CUST_ORDER_DETAIL (
		ORDER_ID VARCHAR2(6),
		LINE number,
		UNIT_PRICE number,
		service_id VARCHAR2(10),
		description VARCHAR2(15),
		user_amt number
);

insert into cust_order_detail values('12121',1,3000,null,'LABOR',null);
insert into cust_order_detail values('12121',2,2000,null,'MATERIAL',null);
insert into cust_order_detail values('12121',3,120,'INSTALL',null,null);
insert into cust_order_detail values('12121',4,100,null,'ALUM. SHEET',null);
insert into cust_order_detail values('12121',5,5,null,'2X3 BOLT',null);

insert into cust_order_detail values('11233',1,1000,null,'LABOR',null);
insert into cust_order_detail values('11233',2,500,null,'MATERIAL',null);
insert into cust_order_detail values('11233',3,120,'INSTALL',null,null);
insert into cust_order_detail values('11233',4,5,null,'2X3 BOLT',null);
commit;

select order_id,
	line,
	case description
		when 'LABOR' then user_amt-total_labor
		when 'MATERIAL' then user_amt-total_material
		else unit_price end unit_price,
	service_id,
	description,
	user_amt
from (
	select
	order_id, line, unit_price, service_id, description,
		case when line in (1,2) then unit_price else user_amt end  user_amt,
		sum(case when line>3 then unit_price end) over(partition by order_id) total_material,
		sum(case when service_id='INSTALL' then unit_price end) over(partition by order_id) total_labor
	from cust_order_detail
)
order by order_id, line
/

Open in new window

0
 
holemaniaAuthor Commented:
Thank you this helps a lot.  With the cursor/loop, I am doing other things within it, but if i can incorporate more into straight query, that would be even better.  Thanks again.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now