Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

How to identify available dates

Orcl ApexMay 14 2024

Hi All,

I need to match the quantities based on the date and give the updated dates to our clients. I have arranged a sample data below for your understanding.

Table T1 holds data for sales orders in columns.

sales_order_number, sales_order_line_number, ordered_item, ordered_quantity, request_date, shipping_warehouse (all of which are self-explanatory, I believe), flag (which indicates whether we can ship material from other warehouses or not), and other_warehouses (near_by_locations from which customer orders can be fulfilled).

Table T2 contains data on item availability and available dates.

Location, part_number, available_date, and available_quantity (I presume they are self-explanatory).

We may connect T1 and T2 using t1.ordered_item = t2.part_number and t1.shiiping_warehouse = t2.location.

I want to scan the T1 rows in ascending order based on request_date to determine if the ordered amount is available in table T2. If it is, I will include the availability date in my result, which will be displayed in result table T3. If the ordered quantity is not available, I want to see the flag of table T1, and if it is 'N', I will not apply any further logic; if it is 'Y', I want to search for available quantities in table T2 based on the order present in T1's other warehouse column, and provide the available date in the result. If we can't discover the available quantities or if the available amount is less than the order quantity, we'd want to offer the year end date as a result.

with t1 as (
select 1 sales_order, 1 sales_order_line, 'A' ordered_item, 'W1' shipping_warehouse, 'N' others_can_ship, 'W2, W3, W4, W5, W6, W7' other_warehouses, date '2024-01-01' request_date, 500 ordered_quantity from dual union
select 2 , 1 , 'A' , 'W2' , 'Y' , 'W1, W3, W4, W5, W6, W7' , date '2024-01-02', 100 from dual union
select 3 , 1 , 'A' , 'W3' , 'Y' , 'W1, W2, W4, W5, W6, W7' , date '2024-01-03', 100 from dual union
select 4 , 1 , 'A' , 'W4' , 'Y' , 'W1, W2, W3, W5, W6, W7' , date '2024-01-04', 200 from dual
)
select * from t1;

with t2 as (
select 'W1' location, 'A' part_number, date '2024-02-01' available_date, 500 available_quantity from dual union
select 'W2' location, 'A' part_number, date '2024-02-02' available_date, 200 available_quantity from dual union
select 'W4' location, 'A' part_number, date '2024-02-03' available_date, 500 available_quantity from dual union
select 'W5' location, 'A' part_number, date '2024-02-04' available_date, 100 available_quantity from dual
)
select * from t2;

with result as (
select 1 sales_order, 1 sales_order_line, 'A' ordered_item, 'W1' shipping_warehouse, 'N' others_can_ship, 'W2, W3, W4, W5, W6, W7' other_warehouses, date '2024-01-01' request_date, 500 ordered_quantity, date '2024-02-01' available_date from dual union
select 2 , 1 , 'A' , 'W2' , 'Y' , 'W1, W3, W4, W5, W6, W7' , date '2024-01-02', 500, date '2024-02-02' from dual union
select 3 , 1 , 'A' , 'W2' , 'Y' , 'W1, W2, W4, W5, W6, W7' , date '2024-01-03', 500, date '2024-02-02' from dual union
select 4 , 1 , 'A' , 'W4' , 'Y' , 'W1, W2, W4, W5, W6, W7' , date '2024-01-04', 500, date '2024-12-31' from dual
)
select * from result;

Please let me know whether the explanation or data is sufficient for you to offer comments. whether you have any more questions, I would be delighted to clarify and share further data.

This post has been answered by Barbara Boehmer on May 15 2024
Jump to Answer
Comments
Post Details
Added on May 14 2024
8 comments
154 views