Skip to Main Content

using a cursor to calculate order logic

User_IAP38Aug 26 2022

Hello,
Need assistance implementing logic for the needs listed below using a cursor
Implementing the logic by using cursors to retrieve data from source tables store_info and order_info (as mentioned scenario) The display as a screenshot as an output format.
Business rule: s1_qty more than the total of need_qty or need_qty, then status(need qty -1) and de_status(1) for least need_qty
scenario 1 : s1 qty > sum of need qty, status(need qty-1), and **de_**status (1)
ex:13456 s1_qty(50) >sumof need_qty(30) then status(29) and de_status(1)
scenaro 2: s1_qty >sumof need_qty then status(need_query-1) and de_status(1) for least need_qty
ex:17890 s1_qty(50) >sumof need_qty(40) then status(40) and and de_status(0)
17890 s1_qty(50) >sumof need_qty(10) then status(10) and and de_status(0)
17890 s1_qty(50) >sumof need_qty(10) then status(9) and and de_status(1)
scenario 3: s1 qty <sum of need qty, status(s1_qty), and **de_**status (0)
ex:12345 s1_qty(20) <sumof need_qty(30) then status(20) and de_status(0)
scenario 4: s1 qty < sum of need qty, status(s1_qty), and **de_**status (0)
ex:14567 s1_qty(50) <sumof need_qty(40) then status(40) and de_status(0)
ex:14567 s1_qty(50) <sumof need_qty(30) then status(10) and de_status(0)

the source table and output format are shown below.
with store_info as
(select 12345 item,9999 det_num,20 s1_qty from dual union all
select 13456 item,9999 det_num,50 s1_qty from dual union all
select 14567 item,9999 det_num,50 s1_qty from dual union all
select 17890 item,9999 det_num,50 s1_qty from dual union all
select 18900 item,9999 det_num,50 s1_qty from dual )
select * from store_info
with order_info as
(select 12345 item,9999 det_num,30 need_qty from dual union all
select 13456 item,9999 det_num,30 need_qty from dual union all
select 14567 item,9999 det_num,40 need_qty from dual union all
select 14567 item,9999 det_num,30 need_qty from dual union all
select 17890 item,9999 det_num,40 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual
) select * from order_info;
output
output.JPG

Comments
Post Details
Added on Aug 26 2022
9 comments
133 views