Allocation Engine Logic

(from Traction article afsismanual3857)

CREATED 30-DEC-2016 DCombest

ALLOCATION ENGINE CODING LOGIC DESCRIPTION

The Inventory Allocation Engine (AE) runs are requested by launching an

inventory allocation engine request set from Oracle Applications. The
request set is comprised of a group of individual applications that are
run one after the other. If one of the requests fail, the remaining
requests will also fail. The request set runs the following programs:

•Pending Credit Approval Holds

•Release IMEX holds
•apply IMEX Holds
•Release Credit Deny Holds
•Allocation Engine
•IMEX Lot Holds
•Simi Pick Release
•Credit Eligible Hold

AE PROGRAM PARAMETERS

The AE uses 7 parameters in its determination of which order lines get

stock. All of these parameters can be set at runtime or left at their
default values, with the exception of the allocation engine time fence.
Concurrent Program Parameters include:

allocation_base_date DATE; -- ARGUMENT1

allocation_time_fence NUMBER; -- ARGUMENT2
reservation_time_fence NUMBER; -- ARGUMENT3
lot_expiration_time_fence NUMBER; -- ARGUMENT4
minimum_release_percentage NUMBER; -- ARGUMENT5
organization_id NUMBER; -- ARGUMENT6
receipt_buffer_days NUMBER; -- ARGUMENT7

Notes and Rules About This Document

The coding logic as noted below is intended as a guide for creating a

business logic document that could be distributed to users. It is also
intended to assist a developer in making changes to specific areas of the
AE when used in conjunction with the code base.

The ordering below is in the same order as it occurs in the code so do not
re-arrange the order in this document.

The numbers below correspond to the code base that contains actual AE code
and can be cross-referenced for more details so do not rearrange or change
any of the numbering below. This system is especially useful for a

Intern
al Use
 developer who may need to do further research on any specific pieces of
the logic.

AE CODE LOGIC DESCRIPTION, in order of execution

1. Set the scheduled ship date to a default date of 01-JAN-2000 for any new /
never looked at order lines where header/line is open, status is ‘BOOKED’
or ‘AWAITING SHIPPING’ and customer.attribute1 = ‘N’.

2. If a sales order line has hard reserved stock, (mtl_demand table

reservation type = 2) then update the order line and delivery table to set
the scheduled ship date to today. This step is logged:
LOG: '*(RESERVED ORDERS) Set scheduled date: '||sysdate||' For Order: '||
RESERVED_ORDER_TO_RELEASE.order_number||' Line: ||
RESERVED_ORDER_TO_RELEASE.line_number

3. Initial stock and lot quantities are calculated for items that appear on
orders and have quantity available to ship. These values are stored in a
temporary table (FFD.IAE_AVAILABLE_STOCK).

4. Total Demand for all items is loaded into an AE table

(FFD.IAE_TOTAL_DEMAND) with the following ordering values set:
a. Generic_set_aside demand_order = 4 and contract_priority = NULL
b. Non-Boeing Contracts demand_order = 5 and contract_priority =
apps_agreement_table.attribute6
c. All other demands have demand_order = 3 and contract_priority =
(max_contract_priority_in_table)+1

5. PO Receipts are calculated and loaded into an iae table, though this will
not be used. (FFD.IAE_PO_RECIEPTS_AVAILABLE)

6. Print to the log the total number of working reservations. LOG: ' No of
rows in WorkingReservations is ' || V_WORKING_RESERVATIONS_COUNT

7. The processing of the AE is ordered by stock item and subinventory, where

there is at least one demand for the item. The process will select an
item+subinventory that has QOH and then process through the demand table
looking to see if it can allocate the stock.

For the item+subinventory, get the available on-hand quantity.

This value is logged: LOG: 'Loading Current Stock for ' ||P_INVENTORY_ITEM_ID || '('
|| stock_part_no || ')' ||' Subinv ' ||P_SUBINVENTORY ||' Quantity ' ||
V_CURRENT_STOCK_QUANTITY

8. GENERIC SET ASIDE: This is the highest level of stock protection. It is

intended to be used for the protection of inventory for use on a contract.
In order to make use of this type of protection, there must be an entry in
the ffdem_stock_setasides table and an oracle sales order agreement in the
apps.so_agreements table. The AE will force allocation of these generic
set-asides for the item/subinventory being processed. It does not appear that
this feature is being used. As of December 2016, there are no rows in the
setasides table. This step is logged: LOG, 'Generic Set Aside Order '||
SCSA_ORDER.order_number||' gets '||SCSA_ORDER.order_quantity

Intern
al Use
9. CONTRACT STOCK: This is the next level of protection. It is the same as a
generic set aside but it is for stock associated with a specific customer.
The customer is defined in the apps agreement. Entries are required in
the ffdm_stock_setasides table and the apps.so_agreements table. These
order lines are given priority by modifying the contract_priority and
attribute3 values in the iae_total_demand table. It does not appear that
this feature is being used. As of December 2016, there are no rows in the
setasides table. This step is logged: LOG: 'Contract Order '||
CONTRACT_ORDER.order_number||' gets '||CONTRACT_ORDER.order_quantity

10. The AE will consider items that are on an order line or part of a kit
and also meet all of the following rules:

a) The item type is STANDARD or the ITEM is part of a kit

b) The customer is not a contract customer
c) There is a balance to ship that has not already been scheduled
d) The line is open
e) The line is booked
f) The item is shippable
g) The workflow step for the line is AWAITING SHIPPING or if the item
is a KIT, the workflow step is either AWAITING SHIPPING or BOOKED

The demand table is queried to get the first order line with demand for
the item+subinventory. The order line is chosen using the following
criteria, in the order listed, using ascending order:
I. Base Id - order_line.attribute3
II. Demand Type – where backorder=0 and all others=1
III. Demand Order – this is 3 for all orders, so irrelevant
IV. Contract Priority – this is the same for all orders, so
irrelevant
V. Unq_Id – order_line.attribute17

This step is logged: fnd_file.put_line (fnd_file.LOG,' ***Processing for Order Number '
|| order_for_stock.order_number|| ' base-id:'||order_for_stock.attribute3 || '***');

11. For the order in step 10, referred to going forward as “the order”,
check if there are header or line holds on the order. This step is
logged: LOG: ' *Order has header hold.' or LOG:' *Order has line hold.'

12. For the order, check to see if the customer has a reservation time fence
set. These steps are logged along with the final reservation time fence that
will be used for this order. LOG:' Customer Release Time Fence (CRTF):' ||
v_customer_release_time_fence|| '. From cust site use attribute_3.' LOG:' Reservation Time
Fence is the greater of CRTF and reservation time fence (v_reservation_time_fence). LOG:'
Using Reservation Time Fence: [TODAY] + ' || v_line_reservation_time_fence || ' day(s)'

13. For the order item, get the qty on hand. This step is logged. LOG: '
*(STOCK ASSIGNMENT) CurrentStockQty: ' || V_CURRENT_STOCK_QUANTITY

14. For the order, check that the date requested is within the reservation
time fence. Each of the values checked is logged. LOG: '*(STOCK ASSIGNMENT)
Date Requested: ' || P_DATE_REQUESTED || ' sysdate:' || V_ALLOCATION_BASE_DATE
LOG:'*(STOCK ASSIGNMENT) Reservation Time Fence: ' ||
V_RESERVATION_TIME_FENCE_DAYS

Intern
al Use
 The results of the validation is also logged, so you will either see this
entry: LOG: *(STOCK ASSIGNMENT) Date Requested <= ' || V_ALLOCATION_BASE_DATE
|| ' +res_time_fence. *(STOCK ASSIGNMENT) Try to satisfy from onhand. '
OR you will see this: LOG:*(STOCK ASSIGNMENT) Date Requested is NOT <=
allocation_base_date+res_time_fence.

15. For the order, determine qty to allocate. These steps are logged.
Entire demand fulfilled: LOG: *(STOCK ASSIGNMENT) OnHandQty: ' ||
V_CURRENT_ONHAND_QUANTITY || ' >= QtyRequested: ' || V_QUANTITY_REQUESTED
*(STOCK ASSIGNMENT) We can satisfy all the quantity requested from the on-hand
inventory.
Partial demand fulfilled: LOG: *(STOCK ASSIGNMENT) OnHandQty: ' ||
V_CURRENT_ONHAND_QUANTITY || ' < (QtyRequested: ' || V_QUANTITY_REQUESTED)

For a partial fulfillment, we need to also check if a minimum release quantity

is required. Failure at this step will be logged: LOG, 'FAILED check for
Minimum Release Percentage: ' || v_min_percent || '%'

If there is no failure at the minimum release step we will see the final log
entry: LOG: *(STOCK ASSIGNMENT) We can only partly satisfy all the quantity
requested from the oh hand - reservation qty.'

16. For the order, log the quantity that is available. LOG:' Stock Available for
this order: ' v_p2_current_stock_qty

17. For the order, verify that the request date is valid and log results:
LOG: Request Date: ' order_for_stock.date_requested ' is within the reservation time fence.'
OR LOG: **This line is not eligible for stock because request date is NOT in the reservation
time fence.'

18. Place the order into the working_reservations table.

19. For the order, log ship set:LOG: *Order line has ship set
number:R1A.ship_set_number
20. For the order, log partial if found: LOG: *Order does not allow partial

21. Log Stock Qty info: [no stock left] LOG: This order has no Stock available to it
for item: ' v_ofs_inv_item_id '(' my_stock_part_no ') qty_reserved set to: 0 - FIFO_RAN_OUT
- Order: ' order_for_stock.order_number *Stock Balance* for item ' v_ofs_inv_item_id ' ('
my_stock_part_no ') : ' current_stock_quantity *Stock Balance* for item ' v_ofs_inv_item_id
' (my_stock_part_no) : ' current_stock_quantity

22. Log Stock Qty info: [stock available] LOG: qty_reserved set to: ' v_my_int ' -
FIFO_ALLOCATED - Order: ' order_number *Stock Balance* for item ' v_ofs_inv_item_id '
('my_stock_part_no ') : ' current_stock_quantity

23. Log Stock Qty info: [partial stock available] LOG: Checking Partial
Allocation for item: ' v_ofs_inv_item_id ' (my_stock_part_no)

[IF MINIMUM_RELEASE_OK] then LOG,' qty_reserved set to: ' v_partial_amount ' -
FIFO_PARTIAL_ALLOCATION - Order: ' order_number
[IF MINIMUM_RELEASE_NOT OK] then LOG: qty_reserved set to: 0 -
FAILED_MINIMUM_RELEASE - Order: ' order_number

LOG: *Stock Balance* for item ' v_ofs_inv_item_id ' (my_stock_part_no) : '
current_stock_quantity
Intern
al Use
 24. Update po receipts available tables. These tables are not used.

25. This is the end of the order processing. The AE will move on to the
next order in line for this item+subinventory, if there is still QOH.
This process will return to step 10 and repeat until there are no more
orders or no more stock.

26. LOG: Current Reservations to Log

LOG: No of rows in WorkingReservations is 'V_WORKING_RESERVATIONS_COUNT

27. Update reserved qty to zero for lines where the ship set number is not
null. The outcome of any particular order is not logged here. LOG: Process
Ship Sets' SYSDATE, 'DD/MON/YYYY HH24:MI:SS

28. Update reserved qty to zero for lines where Kit is a problem. LOG: Process
Kits SYSDATE

29. Update reserved qty to zero for lines where ship complete is indicated and
not met. LOG: Process Ship Complete SYSDATE The failures are logged for this
step. LOG: 'FAILED_SHIP_COMPLETE on LineID:' || FAILED_SHIP_COMPLETE.line_id

30. Update reserved qty to zero for lines that fail allow partial check.
LOG: Process Allow Partial ' SYSDATE

31. Update reserved qty to zero for lines that have holds on them.
LOG: Check Holds ' SYSDATE

32. Log of the final update for the AE. LOG: 'Starting Final Update -
IAE_ALLOCATION_DETAIL' || SYSDATE

33. Log completion of the AE: LOG: 'Finished Final Update - IAE_ALLOCATION_DETAIL'
|| SYSDATE

ALLOCATION ENGINE CODE, pseudo-coded and pulled from various packages

The code snippets below are based off the main program that’s called for
the allocation engine, ffd.inv_allocation_engine.main_inv_engine.

Each number below will correspond to one of the numbers above that
explains the business rules being followed.

Do not rearrange the code below or renumber anything unless you do it in

both places. Note that this document has automated numbering and one
carless carriage return could make the entire document a mess, well, more
than it already is.

--PACKAGE FFD.inv_allocation_engine
PROCEDURE main_inv_engine

USE PARMS:

Intern
al Use
 v_allocation_base_date DATE; -- Concurrent Program Parameter ARGUMENT1
v_allocation_time_fence NUMBER; -- Concurrent Program Parameter
ARGUMENT2
v_reservation_time_fence NUMBER; -- Concurrent Program Parameter
ARGUMENT3
v_lot_expiration_time_fence NUMBER; -- Concurrent Program Parameter
ARGUMENT4
v_minimum_release_percentage NUMBER; -- Concurrent Program Parameter
ARGUMENT5
v_receipt_buffer_days NUMBER; -- Concurrent Program Parameter
ARGUMENT7
v_mc_allocation_fence_date DATE;

v_dummy_schedule_date DATE := TO_DATE ('01-JAN-2000', 'DD-MON-

YYYY');
-------------------------------------------------
LOG START
v_process_id := ffd.process_manager.log_process_start
('IAE_ALLOCATION_ENGINE','FFD',NULL);
-------------------------------------------------
GET/SET PARMS AND VARIABLES
/* Get the Alocation Base Date */ v_allocation_base_date := TO_DATE (argument1,
'YYYY/MM/DD HH24:MI:SS');
/* Get the Allocation Time Fence */ v_allocation_time_fence := argument2;
/* Get the Reservation Time Fence Value */ v_reservation_time_fence := argument3;
/* Get the Lot Expiration Time Fence Value */ v_lot_expiration_time_fence :=
argument4;
/* Get the Fulfillment Percentage */ v_minimum_release_percentage := argument5;
/* Get the Inv Organization Id */ v_organization_id := argument6;
/* Get the Receipt Buffer Days */ v_receipt_buffer_days := argument7;
/* Intermediate variable */ v_mc_allocation_fence_date := v_allocation_base_date +
v_allocation_time_fence;
ffd.process_manager.log_info (v_process_id,'IAE_ALLOCATION_ENGINE',0,'Begin process
C3_OPEN_PO_UPDATE',9);
write the varialbles to the control table and put them in the jobs log file:
INSERT INTO iae_requests_control
VALUES (v_iae_request_id, v_user_id, SYSDATE, NULL, NULL, SYSDATE, v_organization_id,
v_allocation_base_date, v_allocation_time_fence,
v_reservation_time_fence, v_lot_expiration_time_fence, v_minimum_release_percentage,
NULL, NULL, v_receipt_buffer_days );
---LOGG IT
fnd_file.put_line (fnd_file.LOG,'Variables Initialised. Request ID:'||
TO_CHAr(v_iae_request_id)||' '||TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.LOG,'** Allocation Time Fence: '||
TO_CHAr(v_allocation_time_fence )||' Reservation Time Fence: '||TO_CHAR
(v_reservation_time_fence));
fnd_file.put_line (fnd_file.LOG,'** Lot Expiration Time Fence: '||
TO_CHAr( v_lot_expiration_time_fence)||' Min Release Percentage: '||TO_CHAR
(v_minimum_release_percentage));
fnd_file.put_line (fnd_file.LOG,'** Receipt Buffer Days: '||
TO_CHAr(v_receipt_buffer_days)); --15561
--------------------item 1-----------------------------
Update the schedule_ship_date in the order lines table for all eligible lines as
defined in cursor c_line_upd
cursor c_line_upd (p_dummy_date date) is SELECT line_id
FROM apps.ra_customers cust, ont.oe_order_headers_all oha,
ont.oe_order_lines_all ola
WHERE cust.customer_id = oha.sold_to_org_id AND NVL(cust.attribute1,
'N') = 'N'
AND nvl(ola.schedule_ship_date,SYSDATE) <> p_dummy_date AND
ola.flow_status_code in ('BOOKED','AWAITING_SHIPPING')

Intern
al Use
 AND ola.header_id = oha.header_id AND oha.open_flag = 'Y' AND
ola.open_flag ='Y';
for line_rec in c_line_upd (v_dummy_schedule_date )
loop begin UPDATE ont.oe_order_lines_all ola SET schedule_ship_date =
v_dummy_schedule_date WHERE line_id = line_rec.line_id;
Update the date_scheduled in the delivery details table for all eligible lines as
defined in cursor c_line_upd
UPDATE wsh.wsh_delivery_details wdd SET date_scheduled = v_dummy_schedule_date
WHERE source_line_id = line_rec.line_id;
--------------------item 2-----------------------------
/* Set Schedule Date for Reserved Lines for Early Release - /
/* This procuedure is to support the early release of picking notes for customers
for whom it takes longer to provide all the documentation that must accompany the
order
when it goes out. This procedure considers only previously reserved stock.*/

iae_procedures_2.reserved_stock_v_release_time(v_iae_request_id, v_organization_id,
v_user_id, v_allocation_base_date, v_reservation_time_fence, v_dummy_schedule_date);
FND_FILE.PUT_LINE(FND_FILE.LOG, '*(RESERVED ORDERS) Set scheduled date: '||
sysdate||' For Order: '||RESERVED_ORDER_TO_RELEASE.order_number||' Line: '||
RESERVED_ORDER_TO_RELEASE.line_number);
FND_FILE.PUT_LINE(FND_FILE.LOG, '*(RESERVED ORDERS) Set
MTL_DEMAND.requirement_date to: '||sysdate);

fnd_file.put_line (fnd_file.LOG,'Hard Reserved Stock Variable Release Completed' ||' '

|| TO_CHAR (TO_DATE (SYSDATE, 'DD/MON/YYYY HH24:MI:SS')) );

--dc are there any like this right now? 19 are set to ship today, 1 row is not
select *
from inv.mtl_demand dem, apps.ra_customers cust, ont.oe_order_headers_all
oha,ont.oe_order_lines_all ola, apps.ra_site_uses_all rsu, wsh.wsh_delivery_details d
--rm 02/01/2010
WHERE cust.customer_id = oha.sold_to_org_id and NVL(cust.attribute1, 'N') =
'N' AND ola.header_id = oha.header_id
AND oha.OPEN_FLAG = 'Y' AND ola.open_flag ='Y' and ola.ship_to_org_id =
rsu.site_use_id -- rm 4/27/2010
-- and ola.request_date <= GREATEST((P_ALLOCATION_BASE_DATE +
P_RESERVATION_TIME_FENCE), (sysdate + to_number(nvl(rsu.attribute3,0))))-- rm
4/27/2010
AND dem.demand_source_line = ola.line_id AND dem.organization_id = 15
AND dem.demand_source_type = 2 -- Sales Order
AND dem.reservation_type = 2 -- Hard Reservation
AND dem.primary_uom_quantity - nvl(dem.completed_quantity,0) > 0 and
ola.line_id = d.SOURCE_LINE_ID and d.released_status not in ( 'Y','C');

CURSOR RESERVED_ORDERS_TO_RELEASE IS
select order_number, ola.line_number, ola.line_id, dem.DEMAND_ID --, sld.delivery
delivery
from inv.mtl_demand dem, apps.ra_customers cust, ont.oe_order_headers_all
oha,ont.oe_order_lines_all ola, apps.ra_site_uses_all rsu, wsh.wsh_delivery_details d
--rm 02/01/2010
WHERE cust.customer_id = oha.sold_to_org_id and NVL(cust.attribute1, 'N') =
'N' AND ola.header_id = oha.header_id
AND oha.OPEN_FLAG = 'Y' AND ola.open_flag ='Y' and ola.ship_to_org_id =
rsu.site_use_id -- rm 4/27/2010
and ola.request_date <= GREATEST((P_ALLOCATION_BASE_DATE +
P_RESERVATION_TIME_FENCE), (sysdate + to_number(nvl(rsu.attribute3,0))))-- rm
4/27/2010
AND dem.demand_source_line = ola.line_id AND dem.organization_id = 15
AND dem.demand_source_type = 2 -- Sales Order
AND dem.reservation_type = 2 -- Hard Reservation

Intern
al Use
 AND dem.primary_uom_quantity - nvl(dem.completed_quantity,0) > 0 and
ola.line_id = d.SOURCE_LINE_ID and d.released_status not in ( 'Y','C');

FOR RESERVED_ORDER_TO_RELEASE IN RESERVED_ORDERS_TO_RELEASE LOOP

UPDATE ont.oe_order_lines_all ola SET ola.schedule_ship_date =

trunc(sysdate)..... WHERE ola.line_id = RESERVED_ORDER_TO_RELEASE.line_id;
UPDATE wsh.wsh_delivery_details SET date_scheduled = trunc(sysdate)....
FND_FILE.PUT_LINE(FND_FILE.LOG, '*(RESERVED ORDERS) Set scheduled date: '||
sysdate||' For Order: '||RESERVED_ORDER_TO_RELEASE.order_number||' Line: '||
RESERVED_ORDER_TO_RELEASE.line_number);
UPDATE inv.mtl_reservations mr SET mr.requirement_date = TRUNC(SYSDATE) WHERE
mr.demand_source_line_id = reserved_order_to_release.line_id;
UPDATE inv.mtl_demand md SET requirement_date = TRUNC(SYSDATE) WHERE
primary_uom_quantity - completed_quantity > 0 and demand_source_line =
reserved_order_to_release.line_id; --8068
FND_FILE.PUT_LINE(FND_FILE.LOG, '*(RESERVED ORDERS) Set
MTL_DEMAND.requirement_date to: '||sysdate);
-------------------------------------------------
--------------------item 3-----------------------------
iae_procedures.initial_stock_and_lot
/* Populate Tables for Available Stock, Demand , and Reservations */
iae_procedures.initial_stock_and_lot (v_iae_request_id, v_organization_id);
INSERT INTO FFD.IAE_AVAILABLE_STOCK...
--------------------item 4-----------------------------
iae_procedures.initial_stock_and_lot
INSERT INTO
IAE_TOTAL_DEMAND( demand_type,inventory_item_id,subinventory,attribute3,org,qty,date_r
equested,shipment_priority_code,ship_model_complete_flag,

ship_set_number,iae_request_id,so_line_id,so_line_detail_id,demand_creation_date,custom
er_id,customer_name,....
1. 'Generic Set Aside' demand_type, WHERE data is in ffdem_stock_setasides (WHICH
IS EMPTY) with
4= demand_order - now transferred to attribute3 AND NULL =
contract_priority
2. 'Non-Boeing Contract' demand_type, WHERE data is in ffdem_stock_setasides
(WHICH IS EMPTY) with
5= demand_order - now transferred to attribute3 AND
apps.OE_AGREEMENTS_115_VL.attribute6 = contract_priority
SELECT MAX(contract_priority) INTO V_MAX_CONTRACT_PRIORITY FROM
IAE_TOTAL_DEMAND WHERE iae_request_id = P_IAE_REQUEST_ID;
V_MAX_CONTRACT_PRIORITY := V_MAX_CONTRACT_PRIORITY + 1;
3. everything else:
3, -- redundant demand_order
V_MAX_CONTRACT_PRIORITY contract_priority
INSERT INTO IAE_ALLOCATION_DETAIL
--------------------item 5-----------------------------
iae_procedures.initial_stock_and_lot
INSERT INTO IAE_PO_RECEIPTS_AVAILABLE
-------------------------------------------------
--------------------item not recorded----------------------------
/* Build Temp Tables to hold dynamic quantities */
INSERT INTO iae_stock_quant_available (available_stock_id,
available_stock_quantity,subinventory)
SELECT inventory_item_id, quantity, subinventory
FROM iae_available_stock WHERE iae_request_id = v_iae_request_id
AND organization_id = v_organization_id;
/* This procedure computes the initial stock and lot quantities available to the
Allocation */
/* Engine to allocate to orders within the Reservation Time Fence */

Intern
al Use
 /* Available Quantities are only calculated for those stock items that appear on
orders and */
/* have some stock available This procedure also loads the iae_total_demand table.
This gives details of demand from */
/* BackOrders, Standard Orders and Contract (Boeing) Orders */
-------------------------------------------------
--------------------item 6-----------------------------
/* Record the lines in Working Reservations before we start processing */
iae_procedures.current_reservations_to_log (v_iae_request_id);
this just logs the count -- record the lines in Working Reservations before we start
processing */
iae_procedures.current_reservations_to_log (v_iae_request_id);
also writes it to the log file
FND_FILE.PUT_LINE(FND_FILE.LOG, ' No of rows in WorkingReservations is '||
V_WORKING_RESERVATIONS_COUNT);
-------------------------------------------------
-- RESERVATION LOOP
-------------------------------------------------

--------------------------------------------------------------------------------------
------------------
------------LOOP for each stock item

--------------------------------------------------------------------------------------
------------------
FOR dis_items_on_e_order IN dis_items_on_e_orders LOOP
CURSOR dis_items_on_e_orders IS SELECT DISTINCT inventory_item_id,
subinventory FROM iae_eligible_items WHERE organization_id = v_organization_id;
v_ofs_inv_item_id := dis_items_on_e_order.inventory_item_id;
v_ofs_subinventory := dis_items_on_e_order.subinventory;
--------------------------GET STOCK QTY FOR THE ITEM
--------------------item 7-----------------------------
iae_procedures.current_stock_quantity_test(v_ofs_inv_item_id,
v_ofs_subinventory);

SELECT NVL(available_stock_quantity,0) FROM

IAE_STOCK_QUANT_AVAILABLE WHERE available_stock_id = P_INVENTORY_ITEM_ID AND
subinventory = P_SUBINVENTORY;
---RECORD THE QTY IT IN THE LOG FILE
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Loading Current Stock for ' ||
P_INVENTORY_ITEM_ID || '(' || stock_part_no || ')' ||' Subinv '
||P_SUBINVENTORY ||' Quantity ' ||
V_CURRENT_STOCK_QUANTITY);

--------------------------Call procedure to force allcation of small

customer set asides first
--------------------item 8-----------------------------
iae_procedures_2.generic_set_aside (v_iae_request_id,
v_organization_id,v_ofs_inv_item_id, v_ofs_subinventory,v_reservation_time_fence );
A Set aside contract is set up for each part included in this (in
FFDEM_SET_ASIDES). Eligible
small customers then have an special agreement (SO_AGREEMENTS),
that is attached to their orders manually,
to enable them to take advantage of the set aside for that part,
using a DFF on Detail Lines.
/* Find the generic set asides associate with this inventory_item_id */
cursor SCSAS is
select fss.quantity quantity, fss.base_id attribute3, fss.unique_id unique_id,
sa.agreement_id agreement_id
from ffdem_stock_setasides fss, apps.so_agreements sa
where sa.agreement_id = fss.agreement_id and sa.attribute1 is null

Intern
al Use
 and fss.inventory_item_id = P_INVENTORY_ITEM_ID and
to_number(sa.attribute2) = P_ORGANIZATION_ID
and sa.attribute7 = P_SUBINVENTORY and fss.enabled_flag ='Y' order
by fss.agreement_id;
---DC NOTE, the table for setasides is empty, and only 7 boeing records are
in the apps table
-- This is logged, however, we did not see any examples during testing
update iae_total_demand set attribute3 = SCSA.attribute3 /*demand_order =
0*/ /*contract_priority = SCSA.contract_priority*/
where demand_id = SCSA_ORDER.demand_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Generic Set Aside Order '||
SCSA_ORDER.order_number||' gets '||SCSA_ORDER.order_quantity);

--------------------------Call procedure to force allocation of contract stock

second
--------------------item 9-----------------------------
iae_procedures_2.contract_stock ( v_iae_request_id, v_organization_id,
v_ofs_inv_item_id, v_ofs_subinventory, v_reservation_time_fence );
cursor CONTRACTS is select sa.name contract_name, fss.quantity quantity,
to_number(sa.attribute1) ship_to_site_use_id, to_number(sa.attribute6)
contract_priority,
sa.agreement_id agreement_id, fss.base_id attribute3, fss.unique_id
unique_id from ffdem_stock_setasides fss, apps.so_agreements sa
where sa.agreement_id = fss.agreement_id and sa.attribute1 is not null and
fss.inventory_item_id = P_INVENTORY_ITEM_ID
and to_number(sa.attribute2) = P_ORGANIZATION_ID and sa.attribute7 =
P_SUBINVENTORY and fss.enabled_flag ='Y' order by to_number(sa.attribute6),
fss.agreement_id;
/* This procedure alters the position of Contract related orders in the Order
For Stock
cursor by manipulating the values in the demand_order and contract_priority
columns */
USES the Set Aside table above
This is logged but we had no instances of it during testing
/* Update demand attributes in iae_total_demand to force allocation order */
update iae_total_demand set /*demand_order = 0*/
attribute3 = CONTRACT.attribute3, contract_priority =
CONTRACT.contract_priority where demand_id = CONTRACT_ORDER.demand_id;
/* Reduce contract qty available for next order */
V_CURR_CONTRACT_QTY := V_CURR_CONTRACT_QTY - CONTRACT_ORDER.order_quantity;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract Order '||
CONTRACT_ORDER.order_number||' gets '||CONTRACT_ORDER.order_quantity);

-------------------------- Now allocate the stock in the Reservation Order

--------------------item 10-----------------------------
FOR order_for_stock IN orders_for_stocks (v_ofs_inv_item_id, v_iae_request_id,
v_organization_id, v_ofs_subinventory)
SELECT * FROM iae_total_demand WHERE inventory_item_id =
p_ofs_inv_item_id AND iae_request_id = p_iae_request_id
AND organization_id = p_organization_id AND
subinventory = p_ofs_subinventory
ORDER BY TO_NUMBER(attribute3), --
oline.attribute16, --base id
decode (demand_type,'Backorder',0,1),
demand_order,
--this is 3 for all values, so irrelevant
contract_priority,
--same for all values, so irrelevant
TO_NUMBER (attribute7);
--oline.attribute17, -- unq_id

Intern
al Use
--------------------------------------------------------------------------------------
----------------------------------------------------
LOOP /* THROUGH ALL THE Orders for THE Stock ITEM */

--------------------------------------------------------------------------------------
----------------------------------------------------
LOG EACH ORDER AS WE PROCESS THE LINE
fnd_file.put_line (fnd_file.LOG,' ***Processing for Order Number ' ||
order_for_stock.order_number|| ' base-id:'||order_for_stock.attribute3 || '***');
--------------------item 11-----------------------------
-----------------CHECK HEADER AND LINE HOLDS
SELECT 1 into my_val FROM ONT.OE_ORDER_HOLDS_ALL soha,
ONT.OE_HOLD_SOURCES_ALL shsa, ont.oe_order_headers_all oha
WHERE oha.order_number = order_for_stock.order_number AND
soha.header_id = oha.header_id AND shsa.hold_source_id = soha.hold_source_id
AND soha.hold_release_id IS NULL AND soha.line_id IS NULL
AND shsa.hold_id > 1;
if found then log it
fnd_file.put_line (fnd_file.LOG,' *Order has header hold.');
SELECT 1 into my_val FROM ONT.OE_ORDER_HOLDS_ALL soha,
ont.oe_order_headers_all oha
WHERE oha.order_number = order_for_stock.order_number AND
soha.header_id = oha.header_id
AND soha.hold_release_id IS NULL AND soha.line_id =
order_for_stock.so_line_id;
if found then log it
fnd_file.put_line (fnd_file.LOG,' *Order has line hold.');
--------------------item 12-----------------------------
-----------------Evaluate if a new reservation time fence should be used
for this line\
-----------------check to see if this customer has a higher reservation
time fece set at the ship to site level
SELECT NVL (rsu.attribute3, 0) INTO v_customer_release_time_fence
FROM ont.oe_order_lines_all sl, ar.hz_cust_site_uses_all rsu
WHERE rsu.site_use_id = sl.ship_to_org_id AND rsu.attribute3 IS
NOT NULL AND sl.line_id = order_for_stock.so_line_id;
all calculations are logged
fnd_file.put_line (fnd_file.LOG,' Customer Release Time Fence (CRTF):' ||
v_customer_release_time_fence|| '. From cust site use attribute_3.');
fnd_file.put_line (fnd_file.LOG,' Reservation Time Fence is the greater
of CRTF and reservation time fence (' || v_reservation_time_fence || ').');
SELECT GREATEST (v_reservation_time_fence, v_customer_release_time_fence)
INTO v_line_reservation_time_fence FROM DUAL;
SELECT sysdate + v_line_reservation_time_fence INTO res_time_fence_date
FROM DUAL;
the reservation time fence that is used is also logged
fnd_file.put_line (fnd_file.LOG,' Using Reservation Time Fence: [TODAY]
+ ' || v_line_reservation_time_fence || ' day(s)' );

-----------------Determine if Stock Available for this order

--------------------------------------
--------------------item 13-----------------------------
iae_procedures.current_stock_quantity_p2:
iae_procedures.current_stock_quantity_p2( v_organization_id,
v_iae_request_id, v_ofs_subinventory, v_ofs_inv_item_id,
order_for_stock.date_requested,
order_for_stock.demand_id, order_for_stock.quantity,
v_allocation_base_date, v_line_reservation_time_fence, v_user_id,
v_p2_current_stock_qty);
------------------------
iae_procedures.current_stock_quantity_p2:
Intern
al Use
 SELECT NVL(available_stock_quantity,0) INTO
V_CURRENT_ONHAND_QUANTITY
FROM IAE_STOCK_QUANT_AVAILABLE WHERE available_stock_id =
P_INVENTORY_ITEM_ID AND subinventory = P_SUBINVENTORY;
LOG THE RESULTING QTY
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
CurrentStockQty: ' || V_CURRENT_STOCK_QUANTITY);

--------------------item 14-----------------------------
iae_procedures.current_stock_quantity_p2:
/* Make sure the demand line is within the Release Time Fence
*/
LOG THE INFO WE ARE USING AND THE STATUS
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT) Date
Requested: ' || P_DATE_REQUESTED || ' sysdate:' || V_ALLOCATION_BASE_DATE);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
Reservation Time Fence: ' || V_RESERVATION_TIME_FENCE_DAYS);
IF P_DATE_REQUESTED <= V_ALLOCATION_BASE_DATE +
V_RESERVATION_TIME_FENCE_DAYS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
Date Requested <= ' || V_ALLOCATION_BASE_DATE || ' +res_time_fence.');
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
Try to satisfy from onhand. ');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
Date Requested is NOT <= allocation_base_date+res_time_fence.');
END IF;

--------------------item 15-----------------------------
iae_procedures.current_stock_quantity_p2:
--------------CAN WE SATISFY THE ENTIRE DEMANDED QTY?
IF V_CURRENT_ONHAND_QUANTITY >= (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND) THEN
LOG WHAT WE FOUND
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
OnHandQty: ' || V_CURRENT_ONHAND_QUANTITY || ' >= QtyRequested: ' ||
V_QUANTITY_REQUESTED);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
We can satisfy all the quantity requested from the on-hand inventory.');
-- We can satisfy all the quantity requested from the oh
hand - reservation qty
UPDATE IAE_STOCK_QUANT_AVAILABLE SET
available_stock_quantity = (available_stock_quantity - (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND))
WHERE available_stock_id = P_INVENTORY_ITEM_ID AND
subinventory = P_SUBINVENTORY;
INSERT INTO IAE_ALLOCATIONS VALUES
(...ORG_ID,INV_ITEM_ID,DEMAND_ID, 'ONHAND', (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND),SUBINV);
V_QUANTITY_FOUND := (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND);
V_ONHAND_QUANTITY_FOUND := (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND);
END IF;
---otherwise can se do a partial fulfillment
IF V_CURRENT_ONHAND_QUANTITY < (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND) and v_current_onhand_quantity > 0 THEN
LOG WHAT WE FOUND
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK ASSIGNMENT)
OnHandQty: ' || V_CURRENT_ONHAND_QUANTITY || ' < (QtyRequested: ' ||
V_QUANTITY_REQUESTED);
-- check if min percent release is ok
Intern
al Use
 if min_release_ok (P_IAE_REQUEST_ID, (V_QUANTITY_REQUESTED
- V_QUANTITY_FOUND),V_CURRENT_ONHAND_QUANTITY) then
FND_FILE.PUT_LINE(FND_FILE.LOG, ' *(STOCK
ASSIGNMENT) We can only partly satisfy all the quantity requested from the oh hand -
reservation qty.');
-- We can only partly satisfy all the quantity
requested from the oh hand - reservation qty
UPDATE IAE_STOCK_QUANT_AVAILABLE SET
available_stock_quantity = available_stock_quantity - V_CURRENT_ONHAND_QUANTITY
WHERE available_stock_id = P_INVENTORY_ITEM_ID AND
subinventory = P_SUBINVENTORY;
INSERT INTO IAE_ALLOCATIONS VALUES
(...ORG_ID,INV_ITEM_ID,DEMAND_ID, 'ONHAND', (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND),SUBINV);
V_QUANTITY_FOUND := (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND);
V_ONHAND_QUANTITY_FOUND := (V_QUANTITY_REQUESTED -
V_QUANTITY_FOUND);
END IF;
RETURN THE QTY TO THE CALLING PROGRAM
<<return_quantity>>
S_QUANTITY := V_QUANTITY_FOUND;
---end---------------------
iae_procedures.current_stock_quantity_p2:
--------------------item 16-----------------------------
LOG THE QTY RETURNED FROM THE PROCEDURE
fnd_file.put_line (fnd_file.LOG,' Stock Available for this order: ' ||
v_p2_current_stock_qty);
--------------------item 17-----------------------------
--CHECKING TO ENSURE THE REQUEST DATE IS OKAY
IF order_for_stock.date_requested <= SYSDATE +
v_line_reservation_time_fence THEN
LOG THE RESULTS
fnd_file.put_line (fnd_file.LOG,' Request Date: ' ||
order_for_stock.date_requested || ' is within the reservation time fence.' );

--------------------item 18-----------------------------
--CHECKING TO ENSURE THE ORDER TYPE IS CORRECT (IN TESTING NO ORDERS WERE
NOT THE CORRECT TYPE)
IF order_for_stock.demand_type in ('Order Line','Backorder') THEN --8096
---PLACE THE RESERVATION INTO THE AE RESERVATIONS TABLE
INSERT INTO iae_working_reservations(.....
--------------------item 19-----------------------------
---USERS WANTED TO SEE SHIP SET IN THE LOG
IF (R1A.ship_set_number IS NOT NULL) THEN
fnd_file.put_line (fnd_file.LOG,' *Order line has ship set number:' ||
R1A.ship_set_number);
--------------------item 20-----------------------------
---USERS WANTED TO SEE SHIP PARTIAL IN THE LOG HERE EVEN THOUGH IT'S
CHECKED LATER
select 1 FROM ont.oe_order_headers_all WHERE order_number
=R1A.order_number
AND nvl(customer_preference_set_code,'x') = 'SHIP' ;
fnd_file.put_line (fnd_file.LOG,' *Order does not allow partial.');
---CHECK DEMAND TYPE
IF order_for_stock.demand_type <> 'Small Customer Set Aside' AND
order_for_stock.demand_type <> 'Non-Boeing Contract' THEN

--------------------item 21-----------------------------
---IF NO STOCK LEFT THEN LOG IT
IF v_p2_current_stock_qty = 0 THEN /* If there is no Stock
Left */
Intern
al Use
 fnd_file.put_line (fnd_file.LOG,' This order has no Stock
available to it for item: ' || v_ofs_inv_item_id || '(' || my_stock_part_no || ')');
fnd_file.put_line (fnd_file.LOG,' qty_reserved set
to: 0 - FIFO_RAN_OUT - Order: ' ||order_for_stock.order_number);
--ENTER THE REASON INTO THE RESERVATION TABLE
UPDATE iae_working_reservations SET qty_reserved = 0,
reason_message_code = 'FIFO_RAN_OUT'
WHERE working_reservation_id = v_working_reservation_id
AND iae_request_id = v_iae_request_id;
---LOG THE STATUS FOR THE USER
fnd_file.put_line (fnd_file.LOG,' *Stock Balance* for
item ' || v_ofs_inv_item_id || ' (' || my_stock_part_no || ') : ' ||
iae_functions.current_stock_quantity (v_ofs_inv_item_id, v_ofs_subinventory) );
END IF;
--------------------item 22-----------------------------
---IF STOCK IS AVAILABLE THEN LOG IT
IF v_p2_current_stock_qty = order_for_stock.quantity AND
v_p2_current_stock_qty != 0 THEN /* Reduce Quantity Available */
--ENTER THE REASON INTO THE RESERVATION TABLE
UPDATE iae_working_reservations SET qty_reserved =
qty_to_be_reserved, reason_message_code = 'FIFO_ALLOCATED'
WHERE working_reservation_id = v_working_reservation_id
AND iae_request_id = v_iae_request_id;
SELECT qty_reserved INTO v_my_int FROM
iae_working_reservations
WHERE working_reservation_id = v_working_reservation_id
AND iae_request_id = v_iae_request_id;
---LOG THE STATUS FOR THE USER
fnd_file.put_line (fnd_file.LOG,' qty_reserved set to:
' || v_my_int || ' - FIFO_ALLOCATED - Order: ' ||order_for_stock.order_number);
fnd_file.put_line (fnd_file.LOG,' *Stock Balance* for
item ' || v_ofs_inv_item_id || ' (' || my_stock_part_no || ') : ' ||
iae_functions.current_stock_quantity (v_ofs_inv_item_id, v_ofs_subinventory) );
END IF;
--------------------item 23-----------------------------
---IF PARTIAL STOCK IS AVAILABLE THEN CHECK IF PARTIAL IS OKAY AND LOG
IF v_p2_current_stock_qty < order_for_stock.quantity AND
v_p2_current_stock_qty != 0 THEN
v_partial_amount := v_p2_current_stock_qty;
---LOG THE STATUS FOR THE USER
fnd_file.put_line (fnd_file.LOG,' Checking Partial
Allocation for item: ' || v_ofs_inv_item_id || ' (' || my_stock_part_no || ')' );
if
iae_procedures.min_release_ok(v_iae_request_id,order_for_stock.quantity,v_p2_current_st
ock_qty ) then --8096
UPDATE iae_working_reservations SET
qty_reserved = v_partial_amount, reason_message_code = 'FIFO_PARTIAL_ALLOCATION'
WHERE working_reservation_id =
v_working_reservation_id AND iae_request_id = v_iae_request_id;
---LOG THE RESERVATION QTY FOR THE USER
fnd_file.put_line (fnd_file.LOG,'
qty_reserved set to: ' || v_partial_amount || ' - FIFO_PARTIAL_ALLOCATION - Order: '
||order_for_stock.order_number);
else
UPDATE IAE_WORKING_RESERVATIONS SET
reason_message_code = 'FAILED_MINIMUM_RELEASE', qty_reserved = 0
WHERE working_reservation_id =
v_working_reservation_id AND iae_request_id = v_iae_request_id;
---LOG THE ZERO RESERVATION QTY FOR THE USER
fnd_file.put_line (fnd_file.LOG,'
qty_reserved set to: 0 - FAILED_MINIMUM_RELEASE - Order: ' ||
order_for_stock.order_number);
Intern
al Use
 end if;
---LOG THE FINAL BALANCE OFTHE STOCK FOR THE ITEM
fnd_file.put_line (fnd_file.LOG,' *Stock Balance* for item ' ||
v_ofs_inv_item_id || ' (' || my_stock_part_no || ') : ' ||
iae_functions.current_stock_quantity (v_ofs_inv_item_id, v_ofs_subinventory) );
---IF THE DEMAND TYPE IS NOT ELIGIBLE FOR STOCK RELEASE THEN LOG THE
STATUS
ELSE --IF order_for_stock.demand_type in ('Order Line','Backorder')
THEN --8096
fnd_file.put_line (fnd_file.LOG,' **This line is not eligible for
stock because the demand type is NOT Order Line OR Backorder.' ); --15661
fnd_file.put_line (fnd_file.LOG,'The demand type is: ' ||
order_for_stock.demand_type); --15661
END IF; -- Regular order
---IF THE REQUESTED DATE IS NOT IN THE TIMEFENCE THEN LOG THE STATUS

ELSE --IF order_for_stock.date_requested <= SYSDATE +

v_line_reservation_time_fence THEN
fnd_file.put_line (fnd_file.LOG,' **This line is not eligible for
stock because request date is NOT in the reservation time fence.' ); --15661
fnd_file.put_line (fnd_file.LOG,' Request Date: ' ||
order_for_stock.date_requested || ' is NOT <= ' || res_time_fence_date); --15661
END IF; /* Reservation Time Fence */

--------------------------------------------------------------------------------------
----------------------------------------------------
end LOOP /* STOCK ITEM ON ORDERS */

--------------------------------------------------------------------------------------
----------------------------------------------------

---Call ALLOCATE_RECEIPTS_DUE procedure to attempt to allocate remaning PO amounts in

recipts due order to demand lines that are not fully satisfied
---THIS HAS NO EFFECT ON ANYTHING (RIGHT?), THE STOCK IS ALREADY ALLOCATED,
IT'S JUST LEFTOVER CODE THAT WAS NEVER DISABLED
-------- iae_procedures_2.allocate_receipts_due(V_IAE_REQUEST_ID,V_ORGANIZATION_ID,
dis_items_on_e_order.inventory_item_id, dis_items_on_e_order.subinventory, V_USER_ID);

--------------------item 24-----------------------------
IF RECEIPT_DUE.qty_unreserved >= V_UNSATISFIED_DEMAND_QTY THEN
UPDATE ffd.iae_po_receipts_available SET qty_unreserved = qty_unreserved -
V_UNSATISFIED_DEMAND_QTY WHERE available_receipt_id =
RECEIPT_DUE.available_receipt_id;
INSERT into iae_allocations...
IF RECEIPT_DUE.qty_unreserved < V_UNSATISFIED_DEMAND_QTY THEN /* Add row to
iae_allocations_table for RECEIPT_DUE.qty_unreserved, update qty_unreserved to zero */
UPDATE iae_po_receipts_available SET qty_unreserved = 0 WHERE available_receipt_id
= RECEIPT_DUE.available_receipt_id;
INSERT into iae_allocations...
-------------------------------------------------
-------- iae_procedures.reservation_order (v_ofs_inv_item_id, v_iae_request_id);
UPDATE IAE_WORKING_RESERVATIONS
SET reservation_order = V_RESERVATION_ORDER
WHERE working_reservation_id = FIFO_RESERVATION_ORDER.working_reservation_id;
--------------------item 25-----------------------------

--------------------------------------------------------------------------------------
----------------------------------------------------
END LOOP; /* FOR EACH STOCK ITEM *//* DIS_ITEMS_ON_E_ORDERS */

--------------------------------------------------------------------------------------
----------------------------------------------------
Intern
al Use
 -------------------------------------------------
--------------------item 26-----------------------------
/* Do final review of orders accross all orders */
fnd_file.put_line (fnd_file.LOG, 'Current Reservations to Log');
-------- iae_procedures.current_reservations_to_log (v_iae_request_id) --only does log
write:
FND_FILE.PUT_LINE(FND_FILE.LOG, ' No of rows in WorkingReservations is '||
V_WORKING_RESERVATIONS_COUNT);

-------------------------------------------------
--------------------item 27-----------------------------
fnd_file.put_line (fnd_file.LOG,'Process Ship Sets' ||' ' ||TO_CHAR (TO_DATE
(SYSDATE, 'DD/MON/YYYY HH24:MI:SS')));
-------- iae_procedures.ship_set (v_iae_request_id);
------UPDATE RESERVED QTY TO ZERO WHERE SHIP SET IS BAD
CURSOR FAILED_SHIP_SETS IS SELECT order_id, ship_set_number, order_number
FROM IAE_WORKING_RESERVATIONS WHERE iae_request_id = P_IAE_REQUEST_ID
AND ship_set_number IS NOT NULL GROUP BY order_id, ship_set_number, order_number
HAVING SUM(qty_to_be_reserved) - SUM(qty_reserved) <> 0;
FOR FAILED_SHIP_SET IN FAILED_SHIP_SETS lOOP
UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code = 'FAILED_SHIP_SET',
qty_reserved = 0
WHERE order_id = FAILED_SHIP_SET.order_id AND ship_set_number =
FAILED_SHIP_SET.ship_set_number AND iae_request_id = P_IAE_REQUEST_ID;

-------------------------------------------------
--------------------item 28-----------------------------
fnd_file.put_line (fnd_file.LOG,'Process Kits '||TO_CHAR (TO_DATE (SYSDATE,
'DD/MON/YYYY HH24:MI:SS')));
-------- iae_procedures.kits (v_iae_request_id);
--SELECT * FROM FFD.IAE_WORKING_RESERVATIONS WHERE item_type_code = 'KIT';
--WE DON'T HAVE KITS ANYMORE
/* If not all the components of a kit can be fulfilled, reduce the number until */
/* the proportion in the bill of materials can be satisfied (and qtys reserved are
/* all integers). Let the demand interace */
/* the place the reservation */

-------------------------------------------------
--------------------item 29-----------------------------
fnd_file.put_line (fnd_file.LOG,'Process Ship Complete '||TO_CHAR (TO_DATE
(SYSDATE, 'DD/MON/YYYY HH24:MI:SS')));
-------- iae_procedures.ship_complete (v_iae_request_id);
------UPDATE RESERVED QTY TO ZERO WHERE oline.ship_model_complete_flag = 'Y' AND NOT
COMPLETE
CURSOR FAILED_SHIP_COMPLETES IS
SELECT oline.line_id, oline.inventory_item_id, oline.ship_model_complete_flag
FROM IAE_WORKING_RESERVATIONS iwr, ont.oe_order_lines_all oline
WHERE oline.line_id = iwr.order_line_id AND oline.ship_model_complete_flag = 'Y'
AND iwr.item_type_code != 'KIT'
AND iwr.iae_request_id = P_IAE_REQUEST_ID GROUP BY oline.line_id,
oline.inventory_item_id, oline.ship_model_complete_flag
HAVING SUM(qty_to_be_reserved) - SUM(qty_reserved) <> 0;

FOR FAILED_SHIP_COMPLETE IN FAILED_SHIP_COMPLETES LOOP

FND_FILE.PUT_LINE(FND_FILE.LOG, 'FAILED_SHIP_COMPLETE on LineID:' ||
FAILED_SHIP_COMPLETE.line_id );

UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code =

'FAILED_SHIP_COMPLETE',qty_reserved = 0
WHERE order_line_id = FAILED_SHIP_COMPLETE.line_id AND iae_request_id =
P_IAE_REQUEST_ID;
-------------------------------------------------
Intern
al Use
--------------------item 30-----------------------------
fnd_file.put_line (fnd_file.LOG,'Process Allow Partial '||TO_CHAR (TO_DATE
(SYSDATE, 'DD/MON/YYYY HH24:MI:SS')));
-------- iae_procedures.allow_partial (v_iae_request_id);
/* This procedure is to exculde orders from allocation where the Allow Partial check
box on the */
/* is unchecked (the default is checked).
/* If the Allow Partial check box on the order header is unchecked then all of the
order lines */
/* on an order must be fully satisfed for the order to ship at all */
/* so_headers_all.ship_partial_flag = 'N' is unchecked. */
/* so_headers_all.ship_partial_flag = 'Y' is checked. */
/* so_headers_all.ship_partial_flag = 'null its an RMA */

--------------------------------------------------------------------------------------
-
-- in 11.5.10 version, Line Set field is use to flag partial shipment . rm,
1/20/2009,
-- value is saved in OE_ORDER_HEADERS_ALL.CUSTOMER_PREFERENCE_SET_CODE
-- SHIP = ship complete, null= allow partial

--------------------------------------------------------------------------------------
-
CURSOR FAIL_ALLOW_PARTIALS IS SELECT ohead.header_id
FROM ont.oe_order_headers_all ohead, IAE_WORKING_RESERVATIONS iwr
WHERE iwr.order_number = ohead.order_number and
nvl(ohead.customer_preference_set_code,'x') = 'SHIP' -- rm 1/20/2009,
AND iwr.iae_request_id = P_IAE_REQUEST_ID GROUP BY ohead.header_id
HAVING SUM(qty_to_be_reserved) != SUM(qty_reserved);

FOR FAIL_ALLOW_PARTIAL IN FAIL_ALLOW_PARTIALS LOOP

UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code =
'FAILED_ALLOW_PARTIAL', qty_reserved = 0
WHERE order_id = FAIL_ALLOW_PARTIAL.header_id AND iae_request_id =
P_IAE_REQUEST_ID;

-------------------------------------------------
--------------------item 31-----------------------------
fnd_file.put_line (fnd_file.LOG,'Check Holds '||TO_CHAR (TO_DATE (SYSDATE,
'DD/MON/YYYY HH24:MI:SS')));
-------- iae_procedures.check_holds (v_iae_request_id);
/* This procedure simply checks to see if there are any unreleased holds against an
order or */
/* order line - The result (if there is a hold ) is an updated reason message code
('ORDER_HEADER_HOLD_EXISTS') ('ORDER_LINE_HOLD_EXISTS') */
/* If a hold exists at the header level only then hold all the lines on the order If
a hold exists at the line level hold only that line and credit holds at customer Level
- maybe */
CURSOR HEADER_HELD_ORDERS IS /* Enhaced to Exclude Auto Credit Check Holds
(aren't at line level) */
SELECT header_id
FROM ONT.OE_ORDER_HOLDS_ALL soha, ONT.OE_HOLD_SOURCES_ALL shsa
WHERE shsa.hold_source_id = soha.hold_source_id AND soha.hold_release_id IS NULL AND
soha.line_id IS NULL AND shsa.hold_id > 1;
FOR HEADER_HELD_ORDER IN HEADER_HELD_ORDERS LOOP
UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code =
'ORDER_HEADER_HOLD_EXISTS', qty_reserved = 0
WHERE order_id = HEADER_HELD_ORDER.header_id AND iae_request_id =
P_IAE_REQUEST_ID;

Intern
al Use
 CURSOR LINE_HELD_ORDERS IS SELECT line_id FROM ont.oe_order_holds_all WHERE
hold_release_id IS NULL AND line_id IS NOT NULL;
FOR LINE_HELD_ORDER IN LINE_HELD_ORDERS LOOP
UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code =
'ORDER_LINE_HOLD_EXISTS', qty_reserved = 0
WHERE order_line_id = LINE_HELD_ORDER.line_id AND iae_request_id =
P_IAE_REQUEST_ID;

CURSOR CUSTOMER_HOLDS IS SELECT DISTINCT sh.header_id

FROM ont.oe_hold_sources_all shsa, ont.oe_hold_definitions sha,
ont.oe_order_holds_all soha, ont.oe_order_headers_all sh
WHERE shsa.hold_source_id = soha.hold_source_id AND sha.hold_id = shsa.hold_id
AND shsa.hold_entity_id = sh.sold_to_org_id
AND shsa.hold_entity_code = 'C' AND soha.hold_release_id IS NULL AND shsa.hold_id
> 1;
---no code to use this exists in the procedure, IT'S COMMENTED OUT----

CURSOR KIT_COMPONENTS_HELD IS SELECT order_line_id

FROM IAE_WORKING_RESERVATIONS WHERE reason_message_code = 'ITEM_HOLD_EXISTS' AND
item_type_code = 'KIT' AND iae_request_id = P_IAE_REQUEST_ID;
FOR KIT_COMPONENT_HELD IN KIT_COMPONENTS_HELD LOOP
UPDATE IAE_WORKING_RESERVATIONS SET reason_message_code =
'KIT_COMPONENT_ITEM_HOLD_EXISTS', qty_reserved = 0
WHERE order_line_id = KIT_COMPONENT_HELD.order_line_id AND iae_request_id =
P_IAE_REQUEST_ID;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Finished Processing Check Holds');
-------------------------------------------------
--------------------item 32-----------------------------
---- iae_procedures.final_iae_allocation_update (v_iae_request_id,v_organization_id);
CURSOR LOAD_DIS_ITEMS_ON_E_ORDERS IS
SELECT DISTINCT inventory_item_id, subinventory FROM iae_eligible_items WHERE
organization_id = P_ORGANIZATION_ID;
fnd_file.put_line ( fnd_file.LOG, 'Starting Final Update - IAE_ALLOCATION_DETAIL' ||
TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'))

FOR LOAD_DIS_ITEMS_ON_E_ORDER IN LOAD_DIS_ITEMS_ON_E_ORDERS LOOP

UPDATE IAE_ALLOCATION_DETAIL iad
SET iad.allocated = (SELECT NVL(SUM(iwr.qty_reserved),0) FROM
IAE_WORKING_RESERVATIONS iwr
WHERE iwr.inv_item_id = iad.inventory_item_id AND
iwr.organization_id = iad.organization_id AND iwr.order_subinventory =
iad.subinventory
AND iwr.iae_request_id = iad.iae_request_id AND
iwr.reason_message_code IN ('FIFO_ALLOCATED','FIFO_PARTIAL_ALLOCATION'))
WHERE iad.organization_id = P_ORGANIZATION_ID AND iad.iae_request_id =
P_IAE_REQUEST_ID
AND iad.inventory_item_id = LOAD_DIS_ITEMS_ON_E_ORDER.inventory_item_id AND
iad.subinventory = LOAD_DIS_ITEMS_ON_E_ORDER.subinventory;
fnd_file.put_line fnd_file.LOG, 'Finished Final Update - IAE_ALLOCATION_DETAIL' ||
TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS'));

--------------------------------- ffd.IAE 866----------------

--dc just looking....
SELECT itd.*, ola.schedule_ship_date FROM ffd.iae_total_demand itd,
ont.oe_order_lines_all ola
WHERE itd.so_line_id= ola.line_id ; -- AND demand_type = 'Order Line' AND AND
ola.schedule_ship_date != v_dummy_schedule_date;
select distinct demand_type from ffd.iae_total_demand; --Backorder and Order Line
/* For rows that have appeared in iae_total_demand set the schedule date to the
*/
/* Dummy Schedule Date (V_DUMMY_SCHEDULE_DATE), for all the rows */
CURSOR reset_allocations (p_iae_request_id IN NUMBER) IS
Intern
al Use
 SELECT itd.demand_type demand_type, itd.so_line_id so_line_id, 0 delivery_detail_id
FROM iae_total_demand itd, ont.oe_order_lines_all ola
WHERE iae_request_id = p_iae_request_id AND demand_type = 'Order Line' AND
itd.so_line_id= ola.line_id AND ola.schedule_ship_date != v_dummy_schedule_date;
/* 4.3.0 Reset Schedule Date for all Demand Rows */
FOR reset_allocation IN reset_allocations (v_iae_request_id) LOOP
IF reset_allocation.demand_type = 'Order Line' THEN
BEGIN /* select for update no wait */
OPEN lock_order_lines (reset_allocation.so_line_id);
CURSOR lock_order_lines (p_line_id IN NUMBER) IS SELECT * FROM
ont.oe_order_lines_all WHERE line_id = p_line_id FOR UPDATE NOWAIT;
FETCH lock_order_lines INTO order_line_locked_row;
/* do the update */
UPDATE ont.oe_order_lines_all SET schedule_ship_date =
v_dummy_schedule_date WHERE line_id = reset_allocation.so_line_id;
UPDATE wsh.wsh_delivery_details SET date_scheduled =
v_dummy_schedule_date WHERE source_line_id = reset_allocation.so_line_id;
-------------------------------------------------
SELECT COUNT (*) INTO v_allocations_ok FROM iae_working_reservations WHERE
qty_reserved != 0 OR (qty_reserved = 0 AND reason_message_code = 'KIT_REDUCED')
AND iae_request_id = v_iae_request_id;
IF (v_allocations_ok > 0) THEN
/* Perform write back of schedule_status_code and schedule_date to
OE_ORDER_LINES_ALL */
/* Only where reservation qty != 0 ie don't write back 0 allocations */
FOR allocation_to_place IN allocations_to_place (v_iae_request_id) LOOP
CURSOR allocations_to_place (p_iae_request_id IN NUMBER) IS SELECT * FROM
iae_working_reservations
WHERE ( qty_reserved != 0 OR ( qty_reserved = 0 AND reason_message_code =
'KIT_REDUCED')) AND iae_request_id = p_iae_request_id
ORDER BY reservation_order, working_reservation_id;
/* Below this 'clause' beginning at 'For STANDARD ALLOCATIONS' is the code
that has been moved to IAE_BASE_UPDATE */
v_working_reservation_row := allocation_to_place;
iae_base_update.modify_line ('ENGINE', v_working_reservation_row, ...
update ont.oe_order_lines_all SET schedule_ship_date =
TRUNC (SYSDATE),....
update wsh.wsh_delivery_details SET date_scheduled =
TRUNC (SYSDATE),.....
UPDATE iae_working_reservations SET oe_write_back_ok = 'Y'...
IF NECESSARY SPLIT THE LINE
fnd_file.put_line (fnd_file.LOG,'DONE WITH IAE_BASE_UPDATE.MODIFY_LINE-in IAE');
-------------------------------------------------
--CHECK FOR WRITE BACK ERRORS
UPDATE iae_working_reservations SET reason_message_code = 'OE_WRITE_BACK_FAILED' WHERE
oe_write_back_ok = 'N' AND qty_reserved != 0AND iae_request_id = v_iae_request_id;
IF v_failed_write_back > 0 THEN fnd_file.put_line (fnd_file.LOG,'Some OE Write Backs
Failed - Please call Support');
-------------------------------------------------
fnd_file.put_line (fnd_file.output,' ' ||placed_allocation.qty_reserved ||' ' ||'EA'
|| /* to be upgraded */
' of Item ' ||v_rp_inv_item ||' - ' ||v_rp_inv_description ||' for ' ||
v_rp_cust_name ||' Order Number ' ||v_rp_order_number ||' from Warehouse ' ||
v_rp_warehouse ||
' Subinventory ' ||placed_allocation.subinventory || ' Lot Number ' ||
placed_allocation.lot_number || ' Locator ' ||placed_allocation.locator_id);
-------------------------------------------------
----- iae_procedures.unplaced_to_log (v_iae_request_id);
CURSOR UNPLACED_RESERVATIONS IS SELECT * FROM IAE_WORKING_RESERVATIONS WHERE
qty_reserved = 0 AND iae_request_id = P_IAE_REQUEST_ID;
BEGIN

Intern
al Use
 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Reservations Not Placed by Demand Interface :
Request ID : '||P_IAE_REQUEST_ID);
FOR UNPLACED_RESERVATION IN UNPLACED_RESERVATIONS LOOP
IF UNPLACED_RESERVATIONS%ROWCOUNT = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' No Unplaced Reservations');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||UNPLACED_RESERVATION.qty_to_be_reserved||....
-------------------------------------------------
/* Report Engine finish */
UPDATE iae_requests_control SET request_run_end_date = SYSDATE WHERE iae_request_id
= v_iae_request_id;
fnd_file.put_line (fnd_file.LOG,'Engine Completed - Wrote back End Date and Time');
FFD.PROCESS_MANAGER.LOG_PROCESS_END (v_process_id);
-----DONE---------------------------------------------

Intern
al Use