[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| in GMI_APPLY_BACKORDER_UPDATES |
| B1854224 06/27/01 HW close the comment for bug 1826752 in |
| GMI_APPLY_BACKORDER_UPDATES |
| B2547509 12/06/01 Uday Phadtare Adding the message returned by |
| Inventory Engine in the log file after calling |
| UPDATE_PENDING_TO_COMPLETED. |
| Also added code to change the trans_date to sysdate |
| if the date is in closed period and also undelete |
| the transaction if it is deleted by mistake. |
| V. Ajay Kumar 09-JAN-2003 BUG#2736088 |
| Removed the reference to "apps". |
| B2775197 01/29/03 Uday Phadtare Do not complete the transaction if the |
| inventory is going negative and the profile, allow |
| neg inv is not equal to 1. |
| |
| Hasan Wahdani 10/2003 3206991 Added an overloading procedure with a |
| different parameters. WSH module passes a table of |
| trips stops in WSH.J and prior to that, only stop_id |
| was being passed. |
| |
| Hasan Wahdani 12/2003 Removed the Overloading procedure and replaced it |
| with GMI_UPDATE_ORDER.process_order (GMIUSITB.pls) |
| due to compilation issues |
| |
| Hasan Wahdani 02/2004 BUG: 3434884 GSCC issue to get proper Schema |
| Names. |
| Hasan Wahdani 02/2004 BUG: 3385851 Added for Pushkar |
| Hasan Wahdani 02/2004 BUG: HW 3388186 Added a new procedure |
| UPDATE_NEW_LINE_DETAIL_ID. See comments |
| by procedure |
+=========================================================================+
*/
PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
( p_shipping_line IN wsh_delivery_details%ROWTYPE
, p_actual_ship_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
SELECT trans_id, staged_ind
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = 0
AND location = l_location
AND completed_ind = 0
AND delete_mark = 0
ORDER BY staged_ind;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = l_lot_id
AND location = l_location
AND completed_ind = 0
AND staged_ind = 1
AND delete_mark = 0
ORDER BY trans_id;
SELECT iim.item_id INTO l_item_id
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = p_shipping_line.inventory_item_id
AND msi.organization_id = p_shipping_line.organization_id
AND msi.segment1 = iim.item_no;
SELECT location INTO l_location
FROM ic_loct_mst
WHERE inventory_location_id = p_shipping_line.locator_id;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_shipping_line.lot_number
AND sublot_no IS NULL ;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_shipping_line.lot_number
AND sublot_no = p_shipping_line.sublot_number;
/* Could Select More than one Line With Matching Keys */
/* We do not care which matching record we select */
/* since we are ordering by trans_id, therefore */
/* Exit after First Select. */
GMI_RESERVATION_UTIL.println('LINE_ID => ' || p_shipping_line.source_line_id);
EXIT; /* Exit after First Select */
UPDATE ic_tran_pnd
SET staged_ind = 0,
trans_qty = -1 * p_shipping_line.cycle_count_quantity,
trans_qty2 = -1 * p_shipping_line.cycle_count_quantity2
WHERE trans_id = l_old_transaction_rec.trans_id;
FND_MESSAGE.Set_Token('BY_PROC', 'Update default staged indicator');
/* Delete Original Tranaction */
GMI_RESERVATION_UTIL.println('Delete Original Transaction');
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
PRINT_DEBUG (l_new_transaction_rec,' Update Default Transaction');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
PRINT_DEBUG (l_old_transaction_rec,'Update Default Back Order');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
/* this unreserve would delete the trans for this trans_id
and balancing the default lot*/
PROCEDURE unreserve_inv
( p_trans_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_transaction_row ic_tran_pnd%ROWTYPE;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = l_transaction_rec.line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = 0
AND location = l_location
AND completed_ind = 0
AND delete_mark = 0
ORDER BY staged_ind;
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_transaction_rec
, l_transaction_row
, x_return_status
, l_msg_count
, l_msg_data
);
GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
PROCEDURE GMI_UPDATE_SHIPMENT_TXN
( p_shipping_line IN wsh_delivery_details%ROWTYPE
, p_actual_ship_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = 0
AND location = l_location
AND completed_ind = 0
AND delete_mark = 0;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = l_lot_id
AND location = l_location
AND completed_ind = 0
AND staged_ind = 1
AND delete_mark = 0
/* temporary fix for 1794681 */
/* Have to back this temp fix out because staging is not working. If split shipping line 3 and 7 for
the original qty of 10, there is no way that the invnetory could be found Since this issue is an
internal issue, before the complete fix for delivery detail in ic_tran_pnd, leave the issue
as it is for now, this is a bigger issue for corrugated */
--AND trans_qty >= -1 * ABS(p_shipping_line.requested_quantity + 0.000005)
--AND trans_qty <= -1 * ABS(p_shipping_line.requested_quantity - 0.000005)
ORDER BY trans_id;
SELECT count(*)
FROM ic_tran_pnd
WHERE doc_type = 'OMSO'
AND line_id = p_shipping_line.source_line_id
-- AND item_id = l_item_id -- REMOVED for bug 3403418
AND lot_id = l_lot_id
AND location = l_location
AND completed_ind = 0
AND staged_ind = 1
AND delete_mark = 0;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 0
ORDER BY trans_id;
SELECT count(*)
FROM ic_tran_pnd itp
WHERE doc_type ='OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 1
AND exists (select 1
from wsh_delivery_Details
where line_id = p_shipping_line.source_line_id
and delivery_detail_id = p_shipping_line.delivery_detail_id
and shipped_quantity = (-1)*itp.trans_qty);
Select loct_ctl
From ic_whse_mst
Where mtl_organization_id = p_shipping_line.organization_id;
Select object_name
From all_objects
Where object_name = 'WSH_USA_INV_PVT'
AND object_type = 'PACKAGE BODY'
AND OWNER = l_schema;
select oracle_username
into l_schema
from fnd_oracle_userid
where read_only_flag = 'U';
GMI_RESERVATION_UTIL.println('calling GMI_UPDATE_SHIPMENT_TXN_NEW');
GMI_UPDATE_SHIPMENT_TXN_NEW
( p_shipping_line => p_shipping_line
, p_actual_ship_date => p_actual_ship_date
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
GMI_RESERVATION_UTIL.println('Finished calling GMI_Shipping_Util.GMI_UPDATE _SHIPMENT_TXN');
SELECT iim.item_id, iim.lot_ctl, iim.loct_ctl INTO l_item_id,l_lot_ctl, l_loct_ctl
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = p_shipping_line.inventory_item_id
AND msi.organization_id = p_shipping_line.organization_id
AND msi.segment1 = iim.item_no;
SELECT location INTO l_location
FROM ic_loct_mst
WHERE inventory_location_id = p_shipping_line.locator_id;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_shipping_line.lot_number
AND sublot_no IS NULL ;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_shipping_line.lot_number
AND sublot_no = p_shipping_line.sublot_number;
/* Check if this transaction is deleted by chance */
OPEN get_opm_transaction_cnt;
GMI_RESERVATION_UTIL.println('Multiple deleted transactions found - Manual updates are necessary');
/* update the transaction's delete mark to 0 and proceed */
UPDATE ic_tran_pnd
SET delete_mark = 0
WHERE doc_type = 'OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 1
returning trans_id into l_old_transaction_rec.trans_id;
GMI_RESERVATION_UTIL.println('Undeleted transaction '||to_char(l_old_transaction_rec.trans_id));
EXIT; /* Exit after First Select */
/* existing transaction, insert a new transaction for the */
/* shipped amount and then complete it. If the quantity exactly */
/* matches the transaction quantity, simply complete the one */
/* which already exists. */
/* Before Completing any transactions lets Lock The Rows. */
/* Calling OPM Lock Inventory Routine. */
-- PK Bug 3527599 Moving Lock_Inventory call from here to just before
-- UPDATE_PENDING_TO_COMPLETED call.
GMI_RESERVATION_UTIL.println('Correct Transaction Found');
/* Need To Update The Actual Shipment Date */
/* l_old_transaction_rec.trans_date := p_actual_ship_date; */
FND_MESSAGE.Set_Token('WHERE', 'GMI_UPDATE_SHIPMENT_TXN');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Delete Original Transaction');
PRINT_DEBUG (l_old_transaction_rec, 'DELETE RECORD');
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
/* Need To Update The Actual Shipment Date */
/* l_new_transaction_rec.trans_date := p_actual_ship_date; */
GMI_RESERVATION_UTIL.println('Update New Transaction => Completed');
PRINT_DEBUG (l_new_transaction_rec,'UPDATE TO COMPLETE');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
GMI_RESERVATION_UTIL.println('Delete Original Transaction');
PRINT_DEBUG (l_old_transaction_rec,'Delete Original');
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
/* Need To Update The Actual Shipment Date */
/* l_new_transaction_rec.trans_date := p_actual_ship_date; */
GMI_RESERVATION_UTIL.println('Update New Transaction to Completed');
PRINT_DEBUG (l_new_transaction_rec,'UPDATE NEW');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Update Default For Staged');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
END GMI_UPDATE_SHIPMENT_TXN;
PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
( p_shipping_line IN wsh_delivery_details%ROWTYPE
, p_actual_ship_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
SELECT trans_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 0
ORDER BY trans_id;
SELECT count(*)
FROM ic_tran_pnd itp
WHERE doc_type ='OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 1
AND exists (select 1
from wsh_delivery_Details
where line_id = p_shipping_line.source_line_id
and delivery_detail_id = p_shipping_line.delivery_detail_id
and shipped_quantity = (-1)*itp.trans_qty);
/* Check if this transaction is deleted by chance */
OPEN get_opm_transaction_cnt;
GMI_RESERVATION_UTIL.println('Multiple deleted transactions found - Manual updates are necessary');
/* update the transaction's delete mark to 0 and proceed */
UPDATE ic_tran_pnd
SET delete_mark = 0
WHERE doc_type = 'OMSO'
AND line_id = p_shipping_line.source_line_id
AND line_detail_id = p_shipping_line.delivery_detail_id
AND delete_mark = 1
returning trans_id into l_old_transaction_rec.trans_id;
GMI_RESERVATION_UTIL.println('Undeleted transaction '||to_char(l_old_transaction_rec.trans_id));
FND_MESSAGE.Set_Token('WHERE', 'GMI_UPDATE_SHIPMENT_TXN');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_new_transaction_rec
, l_new_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
END GMI_UPDATE_SHIPMENT_TXN_new;
PROCEDURE GMI_APPLY_BACKORDER_UPDATES
( p_original_source_line_id IN NUMBER
, p_source_line_id IN NUMBER
, p_action_flag IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- HW BUG#: 1826752
l_delivery_detail_id NUMBER;
Select delivery_detail_id , source_line_id,
released_status,lot_number,sublot_number
From wsh_delivery_details
Where source_line_id = p_source_line_id
AND released_status NOT IN ('C','Y');
SELECT move_order_line_id, released_status
FROM wsh_delivery_details
WHERE source_line_id = p_source_line_id
AND released_status = 'S';
/* Let's First Update all the move order lines ( backordered and */
/* Not Pick Confirmed). */
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
GMI_RESERVATION_UTIL.println('In GMI BACKORDER UPDATE');
Update IC_TXN_REQUEST_LINES
SET TXN_SOURCE_LINE_ID = p_source_line_id
WHERE line_id = l_move_order.move_order_line_id;
/* the outstanding pending transactions must be updated to point */
/* to the new line_id. */
/* The shipping line must be updated to align with backorder */
/* default lot transactions (cycle count quantity) */
/* B1504749, 5-Dec-2000 odaboval : added released_status clause */
-- HW BUG#:1854224 closed the comment properly
/* HW BUG#:1826752 exclude shipped (C) and staged (Y) */
UPDATE wsh_delivery_details
SET locator_id = NULL,
lot_number = NULL,
sublot_number = NULL
WHERE source_line_id = p_source_line_id
AND released_status NOT IN ('Y','C')
AND p_action_flag = 'B';
GMI_RESERVATION_UTIL.println('No Update in wsh_delivery_details, line_id='||p_source_line_id);
UPDATE ic_tran_pnd
SET line_id = p_source_line_id ,
staged_ind = 0
WHERE line_id = p_original_source_line_id and
doc_type = 'OMSO' and
completed_ind = 0 and
delete_mark = 0;
UPDATE ic_tran_pnd
SET line_id = p_source_line_id
WHERE line_id = p_original_source_line_id and
doc_type = 'OMSO' and
completed_ind = 0 and
delete_mark = 0;
GMI_RESERVATION_UTIL.println('No Update in ic_tran_pnd, line_id='||p_source_line_id);
GMI_RESERVATION_UTIL.println('At the end of GMI_Apply_BacKOrder_Updated, No Error.');
END GMI_APPLY_BACKORDER_UPDATES;
PROCEDURE UPDATE_OPM_TRANSACTION
( p_old_delivery_detail_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_sublot_number IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_old_source_line_id IN NUMBER,
p_locator_id IN NUMBER,
p_new_delivery_detail_id IN NUMBER,
p_old_req_quantity IN NUMBER,
p_old_req_quantity2 IN NUMBER,
p_req_quantity IN NUMBER,
p_req_quantity2 IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
Select opm.lot_ctl
, opm.loct_ctl
, whse.loct_ctl
From ic_item_mst opm
, mtl_system_items mtl
, ic_whse_mst whse
Where mtl.inventory_item_id = p_inventory_item_id
and mtl.organization_id = p_organization_id
and mtl.segment1 = opm.item_no
and whse.mtl_organization_id = p_organization_id; */
Select noninv_ind
From ic_item_mst opm,mtl_system_items mtl
Where mtl.inventory_item_id = p_inventory_item_id
and mtl.organization_id = p_organization_id
and mtl.segment1 = opm.item_no;
SELECT trans_id from
ic_tran_pnd
WHERE line_id = p_old_source_line_id
AND line_detail_id = p_old_delivery_detail_id
AND completed_ind = 0
AND delete_mark = 0
AND staged_ind = 1;
SAVEPOINT update_txn;
SELECT iim.item_id INTO l_item_id
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id
AND msi.segment1 = iim.item_no;
SELECT location INTO l_location
FROM ic_loct_mst
WHERE inventory_location_id = p_locator_id;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_lot_number
AND sublot_no IS NULL ;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = p_lot_number
AND sublot_no = p_sublot_number;
GMI_RESERVATION_UTIL.println('Retrieving OLD TRX in UPDATE_OPM_TRANSACTION ');
GMI_RESERVATION_UTIL.println('Transaction Not Found in UPDATE_OPM_TRXS');
GMI_reservation_Util.printLn('OPM Transaction found in OPM_UPDATE_TRXS');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_old_transaction_rec
, l_old_transaction_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
rollback to update_txn;
GMI_RESERVATION_UTIL.Println('Raised When No Data Found in UPDATE_OPM_TRANSACTION');
rollback to update_txn;
GMI_RESERVATION_UTIL.Println('Raised When Others in UPDATE_OPM_TRANSACTION');
END UPDATE_OPM_TRANSACTION;
PROCEDURE UPDATE_OPM_IC_TRAN_PND
( p_delivery_detail_id IN NUMBER,
p_trans_id IN NUMBER,
p_staged_flag IN NUMBER) IS
BEGIN
IF ( p_delivery_detail_id IS NOT NULL AND p_trans_id is not NULL) THEN
IF(p_staged_flag = 0) THEN
UPDATE ic_tran_pnd
SET line_detail_id = p_delivery_detail_id
WHERE trans_id = p_trans_id ;
UPDATE ic_tran_pnd
SET line_detail_id = p_delivery_detail_id,
staged_ind = 1
WHERE trans_id = p_trans_id ;
GMI_RESERVATION_UTIL.println('*** In update_opm_ic_tran_pnd');
END UPDATE_OPM_IC_TRAN_PND;
SELECT source_document_type_id
, source_document_id
, source_document_line_id
from oe_order_lines_all
where line_id = c_order_line_id;
SELECT a.delivery_id
, d.waybill
, d.ultimate_dropoff_date
from wsh_delivery_assignments a,
wsh_delivery_details dd,
wsh_new_deliveries d
where a.delivery_detail_id = dd.delivery_detail_id
and d.delivery_id = a.delivery_id
and dd.delivery_detail_id = p_shipping_line.delivery_detail_id
and NVL(dd.container_flag, 'N') = 'N';
SELECT destination_type_code,
destination_subinventory,
source_organization_id,
destination_organization_id,
deliver_to_location_id,
pl.requisition_line_id,
pd.distribution_id
from po_requisition_lines_all pl,
po_req_distributions_all pd
where pl.requisition_line_id = c_po_line_id
and pl.requisition_header_id = c_source_document_id
and pl.requisition_line_id = pd.requisition_line_id;
Select description
From mtl_system_items
Where inventory_item_id = p_shipping_line.inventory_item_id
and organization_id = p_shipping_line.organization_id;
SELECT intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = c_from_organization_id AND
to_organization_id = c_to_organization_id;
Select locator_id
, subinventory_code
From MTL_ITEM_LOC_DEFAULTS
Where inventory_item_id = p_shipping_line.inventory_item_id
and organization_id = p_org_id;
Select subinventory_code
From MTL_ITEM_SUB_DEFAULTS
Where inventory_item_id = p_shipping_line.inventory_item_id
and organization_id = p_org_id;
/* Bug #3415847 punkumar,commeting out the exception so that it does not stop insertion of record in RTI
RAISE FND_API.G_EXC_ERROR;
/* Bug #3415847 punkumar,commeting out the exception so that it does not stop insertion of record in RTI
RAISE FND_API.G_EXC_ERROR;
GMI_RESERVATION_UTIL.println('Inserting Detail ' || l_detail_rec.delivery_detail_id || ' into RCV_TRANSACTIONS_INTERFACE ');
select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL into l_transaction_id from dual;
select UNIT_OF_MEASURE
into l_unit_of_measure
from mtl_units_of_measure
where uom_code =l_detail_rec.requested_quantity_uom;
select UNIT_OF_MEASURE
into l_secondary_unit_of_measure
from mtl_units_of_measure
where uom_code =l_detail_rec.requested_quantity_uom2 ;
GMI_RESERVATION_UTIL.println('last_update_date ' || sysdate);
GMI_RESERVATION_UTIL.println('last_updated_by ' || FND_GLOBAL.user_id);
/* Bug # 3363725 , punkumar , modified insert sql to insert delivery_detail_id in comments column */
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(
INTERFACE_TRANSACTION_ID,
GROUP_ID,
INTERFACE_SOURCE_CODE,
INTERFACE_SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ITEM_ID,
ITEM_REVISION,
FROM_ORGANIZATION_ID,
TO_ORGANIZATION_ID,
INTRANSIT_OWNING_ORG_ID,
QUANTITY,
UNIT_OF_MEASURE,
UOM_CODE,
SECONDARY_QUANTITY,
SECONDARY_UOM_CODE,
SECONDARY_UNIT_OF_MEASURE,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
TRANSACTION_TYPE,
TRANSACTION_DATE,
SHIPMENT_NUM,
FREIGHT_CARRIER_CODE,
TRANSFER_COST,
TRANSPORTATION_COST,
TRANSPORTATION_ACCOUNT_ID,
NUM_OF_CONTAINERS,
WAYBILL_AIRBILL_NUM,
INV_TRANSACTION_ID,
DESTINATION_TYPE_CODE,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
PROCESSING_STATUS_CODE,
TRANSACTION_STATUS_CODE,
PROCESSING_MODE_CODE,
FROM_SUBINVENTORY,
SUBINVENTORY,
LOCATOR_ID,
CATEGORY_ID,
EXPECTED_RECEIPT_DATE,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
USSGL_TRANSACTION_CODE,
SHIP_TO_LOCATION_ID,
REQUISITION_LINE_ID,
REQ_DISTRIBUTION_ID,
ITEM_DESCRIPTION,
SHIPPED_DATE,
ROUTING_HEADER_ID,
REASON_ID,
MOVEMENT_ID,
TRANSFER_PERCENTAGE,
CHARGE_ACCOUNT_ID,
DOCUMENT_SHIPMENT_LINE_NUM ,
COMMENTS
)
VALUES
(
l_transaction_id, /* interface_transaction_id*/
l_group_id, /* group_id*/
l_source_code, /* interface_source_code */
l_detail_rec.source_line_id, /* interface_source_line_id */
sysdate, /* last_update_date */
FND_GLOBAL.user_id, /* last_updated_by */
sysdate, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
l_detail_rec.inventory_item_id, /* item_id */
l_detail_rec.revision, /* item_revision */
l_detail_rec.organization_id, /* from_organization_id */
l_transfer_organization, /* to_organization_id */
l_transfer_organization, /* intransit_owning_org_id */
l_del_ship_qty, /* quantity */
l_unit_of_measure, /* unit_of_measure */
l_del_ship_qty_uom, /* uom_code */
l_detail_rec.shipped_quantity2, /* secondary_quantity */
l_detail_rec.requested_quantity_uom2, /* secondary_uom_code */
l_secondary_unit_of_measure, /* secondary_unit_of_measure */
null, /* primary qty*/
null, /* primary uom*/
'SHIP', /* transaction_type ?*/
l_trip_stop_rec.actual_departure_date, /* transaction_date */
l_delivery_id, /* shipment_num */
null, /* freight_carrier_code */
null, /* transfer_cost */
null, /* transportation_cost */
null, /* transportation_account_id */
null, /* number_of_containers */
l_waybill, /* waybill_airbill_number*/
null, /* inventory_transaction_id */
l_po_info.destination_type_code, /* destination_type_code */
decode(l_trx_action_id,1, 'DELIVER', 2,'DELIVER',3,'DELIVER','SHIP'), /*auto_transact_code*/
'INTERNAL ORDER', /* Receipt_source_code*/
'REQ', /* source_document_code*/
'RUNNING', /* processing_status_code */
'PENDING', /* transaction_status_code */
'ONLINE', /* processing_code_mode */
l_detail_rec.subinventory, /* from_subinventory*/
decode(l_trx_action_id, 3, l_subinventory,l_transfer_subinventory), /* subinventory*/
decode(l_trx_action_id, 3, l_locator_id, l_detail_rec.locator_id) , /* locator_id*/
null, /* category_id*/
l_ultimate_dropoff_date, /* expected_receipt_date */
null, /* currency_code */
null, /* currency_convertion_rate */
SYSDATE, /* currency_convertion_date */
null, /* currency_convertion_type */
null, /* ussgl_transaction_code */
l_ship_to_location_id, /* ship_to_location_id */
l_requisition_line_id, /* requisition_line_id */
l_req_distribution_id, /* req_distribution_id */
l_item_desc, /* item_description */
l_trip_stop_rec.actual_departure_date, /* shipped_date */
null, /* routing_header_id */
null, /* reason_id */
null, /* movement_id */
null, /* transfer_percentage */
l_charge_account_id, /* charge_account_id */
l_detail_rec.delivery_detail_id , /*DOCUMENT_SHIPMENT_LINE_NUM*/
'OPM WDD:' || to_char(l_detail_rec.delivery_detail_id )
);
'in create_rcv_transaction: for inserting in RCV_TRANSACTIONS_INTERFACE, sqlcode is '||SQLCODE||'.');
'in create_rcv_transaction: for inserting in RCV_TRANSACTIONS_INTERFACE, sqlcode is '||SQLCODE||'.');
SELECT dg.delivery_id , st.transaction_header_id
FROM wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id AND
st.stop_id = p_stop_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id ;
SELECT dd.delivery_detail_id, dd.source_line_id
FROM wsh_delivery_details dd, wsh_delivery_assignments da
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = l_delivery_id
AND NVL(dd.inv_interfaced_flag , 'N') = 'Y'
AND dd.container_flag = 'N'
AND dd.source_code = 'OE'
AND NOT EXISTS(select 1
from rcv_shipment_lines rsl, oe_order_lines_all oel
where oel.line_id = dd.source_line_id
and rsl.requisition_line_id = oel.source_document_line_id
and rsl.comments = 'OPM WDD:'||to_char(dd.delivery_detail_id));
SELECT source_document_type_id
, source_document_id
, source_document_line_id
, ship_from_org_id
from oe_order_lines_all
where line_id = c_order_line_id;
SELECT * from wsh_delivery_details
where delivery_detail_id = p_del_detail_id
and container_flag = 'N';
SELECT * FROM WSH_TRIP_STOPS
WHERE STOP_ID = c_trip_stop_id;
select RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_group_id FROM DUAL;
SELECT
mp1.organization_code ORG, mp2.organization_code WHSE
, soh.order_number SO_NUMBER
, sol.line_number LINE_NO
, sol.line_id LINE_ID
, sol.ordered_quantity ORDER_QTY
, wdd.inventory_item_id ITEM_ID
, wdd.organization_id SHIP_ORG
, wdd.delivery_detail_id SHIP_ID
, wdd.SHIPPED_QUANTITY SHIP_QTY
, wdd.locator_id LOCATOR_ID
, wdd.lot_number
, wdd.sublot_number
, wdd.released_status SHIP_STATUS
FROM wsh_delivery_details wdd
, oe_order_headers_all soh
, oe_order_lines_all sol
, mtl_parameters mp1
, mtl_parameters mp2
WHERE wdd.source_line_id = sol.line_id
AND mp1.organization_id = sol.org_id
AND mp2.organization_id = sol.ship_from_org_id
AND sol.flow_status_code = 'AWAITING_SHIPPING'
AND wdd.released_status IN ('C','Y')
AND soh.header_id = sol.header_id
AND NVL(wdd.oe_interfaced_flag,'N') <> 'Y'
-- AND NVL(wdd.inv_interfaced_flag,'N') = 'N'
-- AND NVL(wts.PENDING_INTERFACE_FLAG,'N') <> 'Y'
ORDER BY 1, 2 ,3,4;
IS SELECT trans_id, line_detail_id
FROM ic_tran_pnd
WHERE doc_type='OMSO'
AND line_id = p_line
AND item_id = p_item_id
AND lot_id = p_lot_id
AND location = p_location
AND staged_ind = 1
AND line_detail_id is null
AND delete_mark = 0;
SELECT iim.item_id
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = pitem_id
AND msi.organization_id = porg_id
AND msi.segment1 = iim.item_no;
SELECT
mp1.organization_code ORG, mp2.organization_code WHSE
, soh.order_number SO_NUMBER
, sol.line_number LINE_NO
, sol.line_id LINE_ID
, sol.ordered_quantity ORDER_QTY
, wdd.inventory_item_id ITEM_ID
, wdd.organization_id SHIP_ORG
, wdd.delivery_detail_id SHIP_ID
, wdd.SHIPPED_QUANTITY SHIP_QTY
FROM ic_txn_request_lines mo
, wsh_delivery_details wdd
, oe_order_headers_all soh
, oe_order_lines_all sol
, mtl_parameters mp1
, mtl_parameters mp2
Where soh.header_id = sol.header_id
and sol.line_id = mo.txn_source_line_id
AND sol.flow_status_code = 'AWAITING_SHIPPING'
AND mp1.organization_id = sol.org_id
AND mp2.organization_id = sol.ship_from_org_id
and mo.line_status in (3,7)
and mo.line_id = wdd.move_order_line_id
and wdd.released_status = 'S'
;
SELECT
count(*)
From ic_tran_pnd
Where line_id = p_line_id
And doc_type = 'OMSO'
And line_detail_id is null
And delete_mark = 0
And staged_ind = 0
And completed_ind = 0
And (lot_id <> 0 or location <> l_default_location ) -- NON default
;
Select count(*)
FROM ic_txn_request_lines mo
, wsh_delivery_details wdd
Where mo.txn_source_line_id = p_line_id
and mo.line_status in (3,7)
and mo.line_id = wdd.move_order_line_id
and mo.txn_source_line_id = wdd.source_line_id
and wdd.released_status = 'S'
;
l_updates NUMBER;
l_trans_updated NUMBER;
SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO l_time
FROM DUAL;
select name into l_db_name
from v$database;
l_trans_updated :=0;
SELECT location INTO l_location
FROM ic_loct_mst
WHERE inventory_location_id = lines.locator_id;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = lines.lot_number
AND sublot_no IS NULL ;
SELECT lot_id INTO l_lot_id
FROM ic_lots_mst
WHERE item_id = l_item_id
AND lot_no = lines.lot_number
AND sublot_no = lines.sublot_number;
UPDATE IC_TRAN_PND
SET LINE_DETAIL_ID = lines.SHIP_ID
WHERE TRANS_ID = l_trans_id;
l_trans_updated := l_trans_updated +1;
EXIT; /* Exit after First Select */
Select wdd.delivery_detail_id
Into l_line_detail_id
FROM ic_txn_request_lines mo
, wsh_delivery_details wdd
Where mo.txn_source_line_id = lines.line_id
and mo.line_status in (3,7)
and mo.line_id = wdd.move_order_line_id
and mo.txn_source_line_id = wdd.source_line_id
and wdd.released_status = 'S';
/* find out all the trans and update them to the wdd dd_id */
Update ic_tran_pnd
Set line_detail_id = l_line_detail_id
Where line_id = lines.line_id
And doc_type = 'OMSO'
And line_detail_id is null
And delete_mark = 0
And staged_ind = 0
And completed_ind = 0
And (lot_id <> 0 or location <> l_default_location ) -- NON default
;
UTL_FILE.putf(v_outputfile,'\n TOTAL TRANS UPDATED => %s\n',l_trans_updated);
if the line is staged, it would call update_opm_transactions
if not it would call split_trans
*/
/* NOTE : NC - 11/2/01 commented the first parameter and redeclared this
record type in the spec(GMIUSHPS.pls). This needs to be uncommented
and the record type needs to be deleted from the spec when OM changes
are incorporated */
PROCEDURE split_opm_trans
( p_old_delivery_detail_id IN NUMBER,
p_released_status IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_sublot_number IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_old_source_line_id IN NUMBER,
p_locator_id IN NUMBER,
p_old_req_quantity IN NUMBER,
p_old_req_quantity2 IN NUMBER,
p_new_delivery_detail_id IN NUMBER,
p_qty_to_split IN NUMBER,
p_qty2_to_split IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_new_delivery_detail_id NUMBER;
oe_debug_pub.add('Calling update_opm_transaction.',2);
GMI_RESERVATION_UTIL.println('splitting the OPM inv, update_opm_transaction', 'opm.log');
GMI_SHIPPING_UTIL.update_opm_transaction(
p_old_delivery_detail_id => p_old_delivery_detail_id,
p_lot_number => p_lot_number,
p_sublot_number => p_sublot_number,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_old_source_line_id => p_old_source_line_id,
p_locator_id => p_locator_id,
p_new_delivery_detail_id => l_new_delivery_detail_id,
p_old_req_quantity => p_old_req_quantity,
p_old_req_quantity2 => p_old_req_quantity2,
p_req_quantity => p_qty_to_split,
p_req_quantity2 => NVL(p_qty2_to_split,0),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
oe_debug_pub.add('Done calling update_opm_transaction',2);
rollback to update_txn;
/* this procedure fulfills the trans for the old dd and updates the rest of
trans for the new dd
in the process, split the trans if neccessary
*/
PROCEDURE split_trans
( p_old_delivery_detail_id IN NUMBER,
p_new_delivery_detail_id IN NUMBER,
p_old_source_line_id IN NUMBER,
p_new_source_line_id IN NUMBER,
p_qty_to_split IN NUMBER,
p_qty2_to_split IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
SELECT trans_id, doc_id
FROM ic_tran_pnd
WHERE line_id = p_old_source_line_id
AND line_detail_id = p_old_delivery_detail_id
AND delete_mark = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0
ORDER BY staged_ind desc
,trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
Select requested_quantity
, requested_quantity2
, released_status
, source_line_id
, inventory_item_id
, organization_id
From wsh_delivery_details
Where delivery_detail_id = p_old_delivery_detail_id;
Select ic.item_id
, ic.lot_ctl
, ic.loct_ctl
, ic.noninv_ind /* Bug 2901317 */
From ic_item_mst ic
, mtl_system_items mtl
Where ic.item_no = mtl.segment1
and mtl.inventory_item_id = l_inventory_item_id
and mtl.organization_id = l_organization_id;
SELECT trans_id
FROM ic_tran_pnd
WHERE line_id = p_old_source_line_id
AND line_detail_id = p_old_delivery_detail_id
AND delete_mark = 0
AND doc_type = 'OMSO'
AND staged_ind = 1
AND trans_qty <> 0;
Select loct_ctl
Into l_whse_ctl
From ic_whse_mst
Where mtl_organization_id = l_organization_id;
Update ic_tran_pnd
Set line_id = p_new_source_line_id
Where trans_id = l_trans_id;
/* update the default lot to the new line_id */
oe_debug_pub.add('Going to update ic_tranPnd with new line_id :'||p_new_source_line_id
||' for trans_id '||l_trans_id,2);
Update ic_tran_pnd
Set line_id = p_new_source_line_id
Where trans_id = l_trans_id;
Update ic_tran_pnd
Set line_id = p_new_source_line_id
Where line_id = p_old_source_line_id
and line_detail_id = p_new_delivery_detail_id;
GMI_RESERVATION_UTIL.PrintLn('Updated Here');
update ic_tran_pnd
set trans_qty = -1 * l_qty_to_fulfil
, trans_qty2 = -1 * l_qty2_to_fulfil
Where trans_id = l_trans_id;
/* simply update the rest with the new wdd id and new line_id */
update ic_tran_pnd
set line_detail_id = p_new_delivery_detail_id
, line_id = p_new_source_line_id
Where trans_id = l_trans_id;
Select source_line_id
, organization_id
, inventory_item_id
From wsh_delivery_details
Where delivery_detail_id = p_delivery_detail_id;
SELECT trans_id
FROM ic_tran_pnd
WHERE line_id = l_source_line_id
AND line_detail_id = p_delivery_detail_id
AND delete_mark = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0;
Select ic.noninv_ind
From ic_item_mst ic
, mtl_system_items mtl
Where ic.item_no = mtl.segment1
and mtl.inventory_item_id = l_inventory_item_id
and mtl.organization_id = l_organization_id;
update ic_tran_pnd
set delete_mark=1
Where trans_id=l_trans_id;
GMI_RESERVATION_UTIL.println('will update ictranpndNON ctl item','opm.log');
update ic_tran_pnd
set trans_qty = l_old_transaction_rec.trans_qty
, trans_qty2 = l_old_transaction_rec.trans_qty2
, line_detail_id = p_delivery_detail_id
, staged_ind = 1
Where trans_id = l_trans_id;
update ic_tran_pnd
set trans_qty = -1 * p_shipped_quantity
, trans_qty2 = -1 * p_shipped_quantity2
, line_detail_id = p_delivery_detail_id
, staged_ind = 1
Where trans_id = l_trans_id;
Select trans_id
, trans_qty
, trans_qty2
From ic_tran_pnd
Where line_detail_id = p_delivery_detail_id
--and line_id = p_source_line_id
and doc_type='OMSO'
and delete_mark = 0;
GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
GMI_RESERVATION_UTIL.println('update the ic_tran_pnd');
Update ic_tran_pnd
Set trans_qty = -1 * (abs(l_trans_qty) - l_quantity_to_unreserve)
, trans_qty2 = -1 * (abs(l_trans_qty2) - l_quantity_to_unreserve2)
Where trans_id = l_trans_id;
update ic_tran_pnd
set staged_ind = 0
Where trans_id = l_trans_id;
SELECT noninv_ind
FROM ic_item_mst
WHERE item_id = p_tran_rec.item_id;
SELECT loct_onhand as qty
FROM ic_loct_inv
WHERE item_id = p_tran_rec.item_id
AND whse_code = p_tran_rec.whse_code
AND lot_id = p_tran_rec.lot_id
AND location = p_tran_rec.location;
SELECT iim.item_id, iim.loct_ctl
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_mtl_organization_id
AND msi.segment1 = iim.item_no;
Select loct_ctl
Into l_whse_ctl
From ic_whse_mst
Where mtl_organization_id = p_mtl_organization_id;
PROCEDURE UPDATE_NEW_LINE_DETAIL_ID
( p_cons_dd_id IN NUMBER
, p_old_dd_ids IN WSH_UTIL_CORE.Id_Tab_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Get the eligible records from inventory
CURSOR GET_IC_RECORDS (l_source_line_id IN NUMBER
, l_dd_id IN NUMBER)IS
SELECT line_id,line_detail_id
FROM IC_TRAN_PND IC
WHERE IC.line_id = l_source_line_id
AND ic.line_detail_id = l_dd_id
AND IC.staged_ind = 0
AND IC.trans_qty <> 0
AND IC.doc_type='OMSO'
AND IC.delete_mark = 0 ;
SELECT WDD.source_line_id
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.delivery_detail_id = l_dd_id
AND WDD.container_flag = 'N'
AND WDD.source_code = 'OE'
AND WDD.released_status='B';
gmi_reservation_util.println('In GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
gmi_reservation_util.println('Update ic_tran_pnd with new line_detail_id '||p_cons_dd_id);
UPDATE IC_TRAN_PND IC
SET IC.line_detail_id = p_cons_dd_id
WHERE IC.line_detail_id = l_ic_dd
AND IC.line_id = l_line_id ;
gmi_reservation_util.println('Done calling GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
GMI_RESERVATION_UTIL.Println('Raised When No Data Found in GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
END UPDATE_NEW_LINE_DETAIL_ID;