Receive the contract_no and item_no values and search for them in the inventory table. If the product:
Delivered, assigned in arrival_date as the 7 days after today
Ordered. In arrival_date, the value is assigned to the month after today.
If there is no order or delivery, the value in arrival_date is the two months after today,
Add a new order record to the order table.
The values of product_status columns are 'shipped 'and 'ordered'
Inventory:
Product_idnumber (6)
Product_descriptionchar (30)
Product_statuschar (20)
Std_shipping_qtynumber (3)
Contract_item:
Product_id number (6)
Contract_nonumber (12)
Item_nonumber (6)
Arrival_datedate
Order:
Order_idnumber (6)
Product_idnumber (6)
Qtynumber (3)
Code:
Copy codeThe Code is as follows:
Declare
I _product_id inventory. product_id % type;
I _product_description inventory. product_description % type;
I _product_status inventory. product_status % type;
I _std_shipping_qty inventory. std_shipping_qty % type;
Begin
// SQL statement to put the queried value into the defined variable
Select product_id, product_description, product_status, std_shipping_qty
Into I _product_id, I _product_description, I _product_status, I _std_shipping_qty
From inventory where product_id = (
Select product_id from contract_item where contract_no = & contract_no and item_no = & item_no
);
If I _product_status = 'shipped 'then
Update contract_item set arrival_date = sysdate + 7 contract_no = & contract_no and item_no = & item_no;
// Here the elseif is written in connection
Elseif I _product_status = 'ordered' then
Updatecontract_item
Setarrival_date = add_months (sysdate, 1) // Add a month
Whereitem_no = & amp; itemnoandcontract_no = & amp; contractno;
Else
Updatecontract_item
Setarrival_date = add_months (sysdate, 2)
Whereitem_no = & amp; itemnoandcontract_no = & amp; contractno;
Insertintoorders
Valuees (100, I _product_id, I _std_shipping_qty );
End if;
End if;
Commit;
End;