Analysis of Oracle Data allocation problems

It is often encountered that, due to business needs, some summarized data needs to be apportioned to a pile of data according to certain principles.
In fact, if the logic is clear, this type of program is better.
This article focuses on using a simple program to achieve this effect, and it is not easy to share the score.

The following problems must be clarified first:
1. First, determine what and how many items should be allocated to it? For example, a bucket of sand is allocated to some bottles.
2. Principles of allocation. In the above example, a bucket of sand is allocated to some bottles. What is the order in which the bottles are distributed? Which bottle does sand give first? Be sure first.

It seems a little troublesome. Here is an example.
A recent requirement:
When the PO is stored in the database, the batch may be input repeatedly, so after the PO is imported into the database, the inventory has been summarized together. Then, the user consumes the (summarized) Inventory (that is, miscellaneous ).
Now you need to have a report to know that, according to the first-in-first-out principle, the number of warehouse receiving tickets corresponding to the number of consumed items during a period of time.

Note: Assume that the number below is the master unit.

7.1 receiving warehouse receiving ticket R1 item A batch P1 receiving warehouse receiving 400
7.3 receiving warehouse receiving ticket R2 batch A P1 receiving warehouse receiving 300
At this time, the P1 batch inventory totaled 700
------- Consumption (Miscellaneous) Details
7.10 consumption P1 100
7.12 consumption P1 200
8.10 consumption P1 200
8.13 consumption P1 100
8.20 consumption P1 50
9.20 consumption P1 50

If the REPORT query date is 8.1 ~ 8.31
8.1 + 100 = 200
8.1 ~ Total consumption within 8.31: 200 + 100 + 50 = 350

So the core issue is how to share 350 in R1 and R2.
The apportioning effect to be achieved:
The amount consumed during the apportioned period before the total number of warehouse receiving
R1 400 300 100
R2. 300 0 250

Therefore, the result is: 8.1 ~ 8.31
The total consumption is 350, corresponding to the consumption of the warehouse receiving ticket:
R1 consumes 100
R2 consumes 250

Implementation logic:
You can imagine that there are two sandbuckets,
The sand in the red sandbucket is the apportioned amount previously consumed.
The sand in the black sandbucket is the apportioned amount consumed during the period.
Each warehouse receiving ticket is a bottle, so there are two bottles, R1 and R2. Now how can we put red sand and black sand into these two bottles.

Sand loading rules:
1. Sequence of sand use: first use red sand, and then use black sand after use.
2. Sequence of bottle loading: according to the first-in-first-out principle, the first bottle R1 must be installed, and then R2.

 

DECLARE
Rochelle pre_period_qty NUMBER; --- aggregate consumption before the period --- apportioned amount consumed before --- red sand
Rochelle curr_period_qty NUMBER; -- total consumption of the current period -- the apportioned quantity consumed during the period -- Black Sand
---- The result of the installation is saved as the record type, because it will be used later.
TYPE shipment_consume_Rec_Type IS RECORD
(
SHIPMENT_LINE_ID NUMBER
, PRIMARY_QUANTITY NUMBER
, Consume_pre_qty NUMBER
, Consume_curr_qty NUMBER
);
TYPE shipment_consume_Tbl_Type is table of shipment_consume_Rec_Type
Index by BINARY_INTEGER;
Rochelle shipment_consume_tbl shipment_consume_Tbl_Type;
N number;
BEGIN
---- 1 first, calculate the total quantity of red sand and black sand, that is, the amount of sand that can be apportioned.
SELECT nvl (sum (case when Mt. transaction_date <: P_F_START_DATE then
ABS (NVL (MTLN. PRIMARY_QUANTITY, 0 ))
Else
0
End), 0) PRE_PERIOD_QTY,
Nvl (sum (case when Mt. transaction_date> =: P_F_START_DATE then
ABS (NVL (MTLN. PRIMARY_QUANTITY, 0 ))
Else
0
End), 0) CURR_PERIOD_QTY
INTO L_PRE_PERIOD_QTY, L_CURR_PERIOD_QTY
FROM MTL_MATERIAL_TRANSACTIONS Mt
, MTL_TRANSACTION_LOT_NUMBERS MTLN
WHERE Mt. TRANSACTION_ID = MTLN. TRANSACTION_ID
AND Mt. TRANSACTION_TYPE_ID = 74
AND Mt. TRANSACTION_ACTION_ID = 6
AND Mt. OWNING_TP_TYPE = 1 --- ownership transferred (inventory of consignment suppliers)
---
AND Mt. ORGANIZATION_ID = 103
AND Mt. INVENTORY_ITEM_ID = 11783561
And mtln. LOT_NUMBER = 'p0000001'
AND Mt. transaction_date <=: P_F_END_DATE;
DBMS_OUTPUT.PUT_LINE ('L _ PRE_PERIOD_QTY: '| L_PRE_PERIOD_QTY |'-L_CURR_PERIOD_QTY: '| L_CURR_PERIOD_QTY );
N: = 1;

----- 2-minute primary logic.
FOR REC_SHIPMENT_LINE IN (
----- Cursor of the bottle (warehouse receiving ticket)
SELECT Mt. ORGANIZATION_ID
, MT. INVENTORY_ITEM_ID
, MTLN. LOT_NUMBER
, MTLN. TRANSACTION_DATE
, RT. SHIPMENT_HEADER_ID
, RT. SHIPMENT_LINE_ID
, MTLN. PRIMARY_QUANTITY
FROM MTL_TRANSACTION_LOT_NUMBERS MTLN, MTL_MATERIAL_TRANSACTIONS Mt, RCV_TRANSACTIONS RT
Where mtln. TRANSACTION_ID = Mt. TRANSACTION_ID
And rt. TRANSACTION_ID = Mt. RCV_TRANSACTION_ID
AND Mt. TRANSACTION_TYPE_ID = 18
AND Mt. TRANSACTION_SOURCE_TYPE_ID = 1
AND XYG_PO_PKG.CHECK_PO_LINE_CONSIGN (RT. PO_LINE_ID) = 'y'
AND Mt. ORGANIZATION_ID = 103
AND Mt. INVENTORY_ITEM_ID = 11783561
And mtln. LOT_NUMBER = 'p0000001'
Order by mtln. TRANSACTION_DATE, MT. TRANSACTION_ID) LOOP
--- 2.1 preferentially consumes the amount of consumed materials before the beginning of the period, that is, the amount of sand in red first.
IF L_PRE_PERIOD_QTY> = REC_SHIPMENT_LINE.PRIMARY_QUANTITY THEN --- when the amount of red sand exceeds the bottle capacity.
Rochelle shipment_consume_tbl (N). SHIPMENT_LINE_ID: = REC_SHIPMENT_LINE.SHIPMENT_LINE_ID;
Rochelle shipment_consume_tbl (N). PRIMARY_QUANTITY: = REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
---- The amount of red sand consumed is the bottle capacity.
Rochelle shipment_consume_tbl (N). consume_pre_qty: = REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
Rochelle shipment_consume_tbl (N). consume_curr_qty: = 0;
---- The starting quantity is the remaining quantity to be allocated. Because the red sand has been burned out.
Rochelle pre_period_qty: = L_PRE_PERIOD_QTY-REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
ELSE --- when the amount of red sand is smaller than the bottle capacity
Rochelle shipment_consume_tbl (N). SHIPMENT_LINE_ID: = REC_SHIPMENT_LINE.SHIPMENT_LINE_ID;
Rochelle shipment_consume_tbl (N). PRIMARY_QUANTITY: = REC_SHIPMENT_LINE.PRIMARY_QUANTITY;
---- The quantity of red sand in the bottle is the quantity of red sand.
Rochelle shipment_consume_tbl (N). consume_pre_qty: = L_PRE_PERIOD_QTY;

---- Now the red sand is used up, and black sand is used up -----
--- 2.2 When the quantity of Black Sand exceeds the available capacity of the bottle.
IF L_CURR_PERIOD_QTY> (REC_SHIPMENT_LINE.PRIMARY_QUANTITY-L_PRE_PERIOD_QTY) THEN
--- The quantity of black sand in the bottle is the available capacity of the bottle.
Rochelle shipment_consume_tbl (N). consume_curr_qty: = REC_SHIPMENT_LINE.PRIMARY_QUANTITY-L_PRE_PERIOD_QTY;
--- Determine the remaining amount of Black Sand to be apportioned by the next ticket number.
L_CURR_PERIOD_QTY: = L_CURR_PERIOD_QTY-L_shipment_consume_Tbl (N). consume_curr_qty;
ELSE
---- When the quantity of black sand is smaller than or equal to the available capacity of the bottle
----- The quantity of black sand in the bottle is the available capacity of the bottle.
Rochelle shipment_consume_tbl (N). consume_curr_qty: = L_CURR_PERIOD_QTY;
----- Black sand used up !~~ You must assign a value of 0, because you can determine whether to exit the bottle Loop Based on the use of black sand.
L_CURR_PERIOD_QTY: = 0;
End if;
Rochelle pre_period_qty: = 0;
End if;
N: = N + 1;
---- When black sand is used up, exit the loop. Because the number of sand may be small, but there are many bottles... There is no need to recycle it.
IF L_CURR_PERIOD_QTY <= 0 THEN
EXIT;
End if;
End loop;

--- Display the installation result.
For I IN 1 .. L_shipment_consume_Tbl.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (L_shipment_consume_Tbl (I). SHIPMENT_LINE_ID
| '-' | L_shipment_consume_Tbl (I). PRIMARY_QUANTITY
| '-' | L_shipment_consume_Tbl (I). consume_pre_qty
| '-' | L_shipment_consume_Tbl (I). consume_curr_qty
);

End loop;
END;

/*
--- Example:
L_PRE_PERIOD_QTY: 0-L_CURR_PERIOD_QTY: 2020.2
18467366-1605.5-0-1605.5
18633076-5014.7-0-414.7

*/