[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF l_mo_line_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_mo_line_rec.last_updated_by := NULL;
IF l_mo_line_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_mo_line_rec.last_update_date := NULL;
IF l_mo_line_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_mo_line_rec.last_update_login := NULL;
IF l_mo_line_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_mo_line_rec.program_update_date := NULL;
/* Procedure Update_Row */
PROCEDURE Update_Row
( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
)
IS
BEGIN
UPDATE IC_TXN_REQUEST_LINES
SET ATTRIBUTE1 = p_mo_line_rec.attribute1
, ATTRIBUTE10 = p_mo_line_rec.attribute10
, ATTRIBUTE11 = p_mo_line_rec.attribute11
, ATTRIBUTE12 = p_mo_line_rec.attribute12
, ATTRIBUTE13 = p_mo_line_rec.attribute13
, ATTRIBUTE14 = p_mo_line_rec.attribute14
, ATTRIBUTE15 = p_mo_line_rec.attribute15
, ATTRIBUTE2 = p_mo_line_rec.attribute2
, ATTRIBUTE3 = p_mo_line_rec.attribute3
, ATTRIBUTE4 = p_mo_line_rec.attribute4
, ATTRIBUTE5 = p_mo_line_rec.attribute5
, ATTRIBUTE6 = p_mo_line_rec.attribute6
, ATTRIBUTE7 = p_mo_line_rec.attribute7
, ATTRIBUTE8 = p_mo_line_rec.attribute8
, ATTRIBUTE9 = p_mo_line_rec.attribute9
, ATTRIBUTE_CATEGORY = p_mo_line_rec.attribute_category
, CREATED_BY = p_mo_line_rec.created_by
, CREATION_DATE = p_mo_line_rec.creation_date
, DATE_REQUIRED = p_mo_line_rec.date_required
, FROM_LOCATOR_ID = p_mo_line_rec.from_locator_id
, FROM_SUBINVENTORY_CODE = p_mo_line_rec.from_subinventory_code
, FROM_SUBINVENTORY_ID = p_mo_line_rec.from_subinventory_id
, HEADER_ID = p_mo_line_rec.header_id
, INVENTORY_ITEM_ID = p_mo_line_rec.inventory_item_id
, LAST_UPDATED_BY = p_mo_line_rec.last_updated_by
, LAST_UPDATE_DATE = p_mo_line_rec.last_update_date
, LAST_UPDATE_LOGIN = p_mo_line_rec.last_update_login
, LINE_ID = p_mo_line_rec.line_id
, LINE_NUMBER = p_mo_line_rec.line_number
, LINE_STATUS = p_mo_line_rec.line_status
/* , LOT_NUMBER = p_mo_line_rec.lot_number */
, ORGANIZATION_ID = p_mo_line_rec.organization_id
, PROGRAM_APPLICATION_ID = p_mo_line_rec.program_application_id
, PROGRAM_ID = p_mo_line_rec.program_id
, PROGRAM_UPDATE_DATE = p_mo_line_rec.program_update_date
/* , PROJECT_ID = p_mo_line_rec.project_id */
, QUANTITY = p_mo_line_rec.quantity
, QUANTITY_DELIVERED = p_mo_line_rec.quantity_delivered
, QUANTITY_DETAILED = p_mo_line_rec.quantity_detailed
, REASON_ID = p_mo_line_rec.reason_id
, REFERENCE = p_mo_line_rec.reference
, REFERENCE_ID = p_mo_line_rec.reference_id
, REFERENCE_TYPE_CODE = p_mo_line_rec.reference_type_code
, REQUEST_ID = p_mo_line_rec.request_id
/* , REVISION = p_mo_line_rec.revision */
/* , SERIAL_NUMBER_END = p_mo_line_rec.serial_number_end */
/* , SERIAL_NUMBER_START = p_mo_line_rec.serial_number_start */
, STATUS_DATE = p_mo_line_rec.status_date
/* , TASK_ID = p_mo_line_rec.task_id */
, TO_ACCOUNT_ID = p_mo_line_rec.to_account_id
, TO_LOCATOR_ID = p_mo_line_rec.to_locator_id
, TO_SUBINVENTORY_CODE = p_mo_line_rec.to_subinventory_code
, TO_SUBINVENTORY_ID = p_mo_line_rec.to_subinventory_id
, TRANSACTION_HEADER_ID = p_mo_line_rec.transaction_header_id
, UOM_CODE = p_mo_line_rec.uom_code
, TRANSACTION_TYPE_ID = p_mo_line_rec.transaction_type_id
, TRANSACTION_SOURCE_TYPE_ID = p_mo_line_rec.transaction_source_type_id
, TXN_SOURCE_ID = p_mo_line_rec.txn_source_id
, TXN_SOURCE_LINE_ID = p_mo_line_rec.txn_source_line_id
, TXN_SOURCE_LINE_DETAIL_ID = p_mo_line_rec.txn_source_line_detail_id
, TO_ORGANIZATION_ID = p_mo_line_rec.to_organization_id
, PRIMARY_QUANTITY = p_mo_line_rec.primary_quantity
, PICK_STRATEGY_ID = p_mo_line_rec.pick_strategy_id
, PUT_AWAY_STRATEGY_ID = p_mo_line_rec.put_away_strategy_id
, LOT_NO = p_mo_line_rec.lot_no
, SUBLOT_NO = p_mo_line_rec.sublot_no
, QC_GRADE = p_mo_line_rec.qc_grade
, SECONDARY_QUANTITY = p_mo_line_rec.secondary_quantity
, SECONDARY_UOM_CODE = p_mo_line_rec.secondary_uom_code
, SECONDARY_QUANTITY_DELIVERED =p_mo_line_rec.secondary_quantity_delivered
, SECONDARY_QUANTITY_DETAILED = p_mo_line_rec.secondary_quantity_detailed
-- HW BUG#:2296620 added ship_set_id
, SHIP_SET_ID = p_mo_line_rec.ship_set_id
WHERE LINE_ID = p_mo_line_rec.line_id
;
, 'Update_Row'
);
END Update_Row;
/* Procedure Update_Row_Status */
PROCEDURE Update_Row_Status
( p_line_id IN Number,
p_status IN Number
)
IS
l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
l_mo_line_rec.last_update_date := SYSDATE;
l_mo_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_mo_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
GMI_MOVE_ORDER_LINE_UTIL.Update_Row(l_mo_line_rec);
, 'Update_Row_Status'
);
END Update_Row_Status;
/* Procedure Insert_Row */
PROCEDURE Insert_Row
( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
)
IS
err_num NUMBER;
gmi_reservation_util.println('In insert row in move order line');
WSH_UTIL_CORE.Println(' Line Insert DML');
INSERT INTO IC_TXN_REQUEST_LINES
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CREATED_BY
, CREATION_DATE
, DATE_REQUIRED
, FROM_LOCATOR_ID
, FROM_SUBINVENTORY_CODE
, FROM_SUBINVENTORY_ID
, HEADER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, LINE_NUMBER
, LINE_STATUS
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUANTITY
, QUANTITY_DELIVERED
, QUANTITY_DETAILED
, REASON_ID
, REFERENCE
, REFERENCE_ID
, REFERENCE_TYPE_CODE
, REQUEST_ID
, STATUS_DATE
, TO_ACCOUNT_ID
, TO_LOCATOR_ID
, TO_SUBINVENTORY_CODE
, TO_SUBINVENTORY_ID
, TRANSACTION_HEADER_ID
, UOM_CODE
, TRANSACTION_TYPE_ID
, TRANSACTION_SOURCE_TYPE_ID
, TXN_SOURCE_ID
, TXN_SOURCE_LINE_ID
, TXN_SOURCE_LINE_DETAIL_ID
, TO_ORGANIZATION_ID
, PRIMARY_QUANTITY
, PICK_STRATEGY_ID
, PUT_AWAY_STRATEGY_ID
, LOT_NO
, SUBLOT_NO
, QC_GRADE
, SECONDARY_QUANTITY
, SECONDARY_UOM_CODE
, SECONDARY_QUANTITY_DELIVERED
, SECONDARY_QUANTITY_DETAILED
--HW BUG#:2296620 added ship_set_id
, SHIP_SET_ID
)
VALUES
( p_mo_line_rec.attribute1
, p_mo_line_rec.attribute10
, p_mo_line_rec.attribute11
, p_mo_line_rec.attribute12
, p_mo_line_rec.attribute13
, p_mo_line_rec.attribute14
, p_mo_line_rec.attribute15
, p_mo_line_rec.attribute2
, p_mo_line_rec.attribute3
, p_mo_line_rec.attribute4
, p_mo_line_rec.attribute5
, p_mo_line_rec.attribute6
, p_mo_line_rec.attribute7
, p_mo_line_rec.attribute8
, p_mo_line_rec.attribute9
, p_mo_line_rec.attribute_category
, p_mo_line_rec.created_by
, p_mo_line_rec.creation_date
, p_mo_line_rec.date_required
, p_mo_line_rec.from_locator_id
, p_mo_line_rec.from_subinventory_code
, p_mo_line_rec.from_subinventory_id
, p_mo_line_rec.header_id
, p_mo_line_rec.inventory_item_id
, p_mo_line_rec.last_updated_by
, p_mo_line_rec.last_update_date
, p_mo_line_rec.last_update_login
, p_mo_line_rec.line_id
, p_mo_line_rec.line_number
, p_mo_line_rec.line_status
, p_mo_line_rec.organization_id
, p_mo_line_rec.program_application_id
, p_mo_line_rec.program_id
, p_mo_line_rec.program_update_date
, p_mo_line_rec.quantity
, p_mo_line_rec.quantity_delivered
, p_mo_line_rec.quantity_detailed
, p_mo_line_rec.reason_id
, p_mo_line_rec.reference
, p_mo_line_rec.reference_id
, p_mo_line_rec.reference_type_code
, p_mo_line_rec.request_id
, p_mo_line_rec.status_date
, p_mo_line_rec.to_account_id
, p_mo_line_rec.to_locator_id
, p_mo_line_rec.to_subinventory_code
, p_mo_line_rec.to_subinventory_id
, p_mo_line_rec.transaction_header_id
, p_mo_line_rec.uom_code
, p_mo_line_rec.transaction_type_id
, p_mo_line_rec.transaction_source_type_id
, p_mo_line_rec.txn_source_id
, p_mo_line_rec.txn_source_line_id
, p_mo_line_rec.txn_source_line_detail_id
, p_mo_line_rec.to_organization_id
, p_mo_line_rec.primary_quantity
, p_mo_line_rec.pick_strategy_id
, p_mo_line_rec.put_away_strategy_id
, p_mo_line_rec.lot_no
, p_mo_line_rec.sublot_no
, p_mo_line_rec.qc_grade
, p_mo_line_rec.secondary_quantity
, p_mo_line_rec.secondary_uom_code
, p_mo_line_rec.secondary_quantity_delivered
, p_mo_line_rec.secondary_quantity_detailed
-- HW BUG#:2296620 added ship_set_id
, p_mo_line_rec.ship_set_id
);
gmi_reservation_util.println('End of insert row in move order lines');
WSH_UTIL_CORE.Println(' Line Insert Error => ' || err_num || err_msg);
gmi_reservation_util.Println(' Line Insert Error => ' || err_num || err_msg);
, 'Insert_Row'
);
END Insert_Row;
/* Procedure Delete_Row */
PROCEDURE Delete_Row
( p_line_id IN NUMBER
)
IS
BEGIN
DELETE FROM IC_TXN_REQUEST_LINES
WHERE LINE_ID = p_line_id
;
, 'Delete_Row'
);
END Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CREATED_BY
, CREATION_DATE
, DATE_REQUIRED
, FROM_LOCATOR_ID
, FROM_SUBINVENTORY_CODE
, FROM_SUBINVENTORY_ID
, HEADER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, LINE_NUMBER
, LINE_STATUS
/* , LOT_NUMBER */
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
/* , PROJECT_ID */
, QUANTITY
, QUANTITY_DELIVERED
, QUANTITY_DETAILED
, REASON_ID
, REFERENCE
, REFERENCE_ID
, REFERENCE_TYPE_CODE
, REQUEST_ID
/* , REVISION */
/* , SERIAL_NUMBER_END */
/* , SERIAL_NUMBER_START */
, STATUS_DATE
/* , TASK_ID */
, TO_ACCOUNT_ID
, TO_LOCATOR_ID
, TO_SUBINVENTORY_CODE
, TO_SUBINVENTORY_ID
, TRANSACTION_HEADER_ID
, UOM_CODE
, TRANSACTION_TYPE_ID
, TRANSACTION_SOURCE_TYPE_ID
, TXN_SOURCE_ID
, TXN_SOURCE_LINE_ID
, TXN_SOURCE_LINE_DETAIL_ID
, TO_ORGANIZATION_ID
, PRIMARY_QUANTITY
, PICK_STRATEGY_ID
, PUT_AWAY_STRATEGY_ID
, LOT_NO
, SUBLOT_NO
, QC_GRADE
, SECONDARY_QUANTITY
, SECONDARY_UOM_CODE
, SECONDARY_QUANTITY_DELIVERED
, SECONDARY_QUANTITY_DETAILED
-- HW BUG#:2296620 added ship_set_id
, SHIP_SET_ID
FROM IC_TXN_REQUEST_LINES
WHERE ( LINE_ID = p_line_id
)
OR ( HEADER_ID = p_header_id
);
l_mo_line_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_mo_line_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_mo_line_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_mo_line_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CREATED_BY
, CREATION_DATE
, DATE_REQUIRED
, FROM_LOCATOR_ID
, FROM_SUBINVENTORY_CODE
, FROM_SUBINVENTORY_ID
, HEADER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, LINE_NUMBER
, LINE_STATUS
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUANTITY
, QUANTITY_DELIVERED
, QUANTITY_DETAILED
, REASON_ID
, REFERENCE
, REFERENCE_ID
, REFERENCE_TYPE_CODE
, REQUEST_ID
, STATUS_DATE
, TO_ACCOUNT_ID
, TO_LOCATOR_ID
, TO_SUBINVENTORY_CODE
, TO_SUBINVENTORY_ID
, TRANSACTION_HEADER_ID
, UOM_CODE
, TRANSACTION_TYPE_ID
, TRANSACTION_SOURCE_TYPE_ID
, TXN_SOURCE_ID
, TXN_SOURCE_LINE_ID
, TXN_SOURCE_LINE_DETAIL_ID
, TO_ORGANIZATION_ID
, PRIMARY_QUANTITY
, PICK_STRATEGY_ID
, PUT_AWAY_STRATEGY_ID
, LOT_NO
, SUBLOT_NO
, QC_GRADE
, SECONDARY_QUANTITY
, SECONDARY_UOM_CODE
, SECONDARY_QUANTITY_DELIVERED
, SECONDARY_QUANTITY_DETAILED
-- HW BUG#:2296620 added ship_set_id
, SHIP_SET_ID
FROM IC_TXN_REQUEST_LINES
WHERE LINE_ID = mo_line_id
FOR UPDATE NOWAIT;
, l_mo_line_rec.last_updated_by
, l_mo_line_rec.last_update_date
, l_mo_line_rec.last_update_login
, l_mo_line_rec.line_id
, l_mo_line_rec.line_number
, l_mo_line_rec.line_status
/* , l_mo_line_rec.lot_number */
, l_mo_line_rec.organization_id
, l_mo_line_rec.program_application_id
, l_mo_line_rec.program_id
, l_mo_line_rec.program_update_date
/* , l_mo_line_rec.project_id */
, l_mo_line_rec.quantity
, l_mo_line_rec.quantity_delivered
, l_mo_line_rec.quantity_detailed
, l_mo_line_rec.reason_id
, l_mo_line_rec.reference
, l_mo_line_rec.reference_id
, l_mo_line_rec.reference_type_code
, l_mo_line_rec.request_id
/* , l_mo_line_rec.revision */
/* , l_mo_line_rec.serial_number_end */
/* , l_mo_line_rec.serial_number_start */
, l_mo_line_rec.status_date
/* , l_mo_line_rec.task_id */
, l_mo_line_rec.to_account_id
, l_mo_line_rec.to_locator_id
, l_mo_line_rec.to_subinventory_code
, l_mo_line_rec.to_subinventory_id
, l_mo_line_rec.transaction_header_id
, l_mo_line_rec.uom_code
, l_mo_line_rec.transaction_type_id
, l_mo_line_rec.transaction_source_type_id
, l_mo_line_rec.txn_source_id
, l_mo_line_rec.txn_source_line_id
, l_mo_line_rec.txn_source_line_detail_id
, l_mo_line_rec.to_organization_id
, l_mo_line_rec.primary_quantity
, l_mo_line_rec.pick_strategy_id
, l_mo_line_rec.put_away_strategy_id
, l_mo_line_rec.lot_no
, l_mo_line_rec.sublot_no
, l_mo_line_rec.qc_grade
, l_mo_line_rec.secondary_quantity
, l_mo_line_rec.secondary_uom_code
, l_mo_line_rec.secondary_quantity_delivered
, l_mo_line_rec.secondary_quantity_detailed
-- HW BUG#:2296620 added ship_set_id
, l_mo_line_rec.ship_set_id;
GMI_Reservation_Util.PrintLn('(opm_dbg) in Lock_Row : after select.');
FND_MESSAGE.SET_NAME('INV','OE_LOCK_ROW_DELETED');
, p_allow_delete IN VARCHAR2 DEFAULT NULL
, x_number_of_rows OUT NOCOPY NUMBER
, x_qc_grade OUT NOCOPY VARCHAR2
, x_detailed_qty OUT NOCOPY NUMBER
, x_qty_UM OUT NOCOPY VARCHAR2
, x_detailed_qty2 OUT NOCOPY NUMBER
, x_qty_UM2 OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_p_allow_delete VARCHAR2(3);
SELECT NVL(PRINT_PICK_SLIP_MODE, 'E'),
AUTODETAIL_PR_FLAG,
NVL(AUTOCREATE_DELIVERIES_FLAG, 'N'),
NVL(AUTOCREATE_DEL_ORDERS_FLAG, 'Y'),
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
PICK_SEQUENCE_RULE_ID,
PICK_GROUPING_RULE_ID
FROM WSH_SHIPPING_PARAMETERS
WHERE ORGANIZATION_ID = v_org_id;
SELECT DECODE(MO_PICK_CONFIRM_REQUIRED, 2, 'Y', 'N')
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = v_org_id;
select auto_pick_confirm_flag
into l_auto_pick_flag
from wsh_picking_batches
where name = l_request_number;
l_p_allow_delete := p_allow_delete;
GMI_Reservation_Util.PrintLn('(opm_dbg) l_p_allow_delete = ' || l_p_allow_delete) ;
p_allow_delete => l_p_allow_delete,
x_pick_release_status => l_pick_release_status,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
GMI_Reservation_Util.PrintLn('calling delete details');
INV_Replenish_Detail_PUB.Delete_Details(
p_transaction_temp_id => l_mold_tbl_temp(l_index).transaction_temp_id,
p_move_order_line_id => l_mold_tbl_temp(l_index).move_order_line_id,
p_reservation_id => l_mold_tbl_temp(l_index).reservation_id,
p_transaction_quantity => l_mold_tbl_temp(l_index).transaction_quantity,
p_primary_trx_qty => l_mold_tbl_temp(l_index).primary_quantity,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
update mtl_txn_request_lines
set quantity_detailed = quantity_detailed - l_mold_tbl_temp(l_index).transaction_quantity,
quantity_delivered = quantity_delivered - l_mold_tbl_temp(l_index).transaction_quantity
where line_id = l_mold_tbl_temp(l_index).move_order_line_id;
update mtl_txn_request_lines
set line_status = 7
where line_id = p_mo_line_id;
update ic_txn_request_lines
set quantity_detailed = x_detailed_qty
, secondary_quantity_detailed = x_detailed_qty2
where line_id = l_mo_line_tbl(1).line_id;
Select h.order_number, d.line_number, d.schedule_ship_date,h.name
From oe_order_headers_all h
, oe_order_lines_all d
, hr_operating_units h
Where h.header_id = d.header_id
and h.organization_id = d.org_id
and d.line_id = p_source_line_id;
SELECT NVL(SUM(LOCT_ONHAND),0) - NVL(SUM(ABS(COMMIT_QTY)),0)
FROM IC_ITEM_INV_V
WHERE item_id = l_item_id
AND whse_code = l_whse_code;
SELECT i.item_id, i.item_no,i.alloc_class
FROM IC_ITEM_MST i, MTL_SYSTEM_ITEMS m
WHERE m.organization_id = l_org_id
And m.inventory_item_id = l_inv_item_id
AND i.item_no = m.segment1;
SELECT MTL_ORGANIZATION_ID
FROM IC_WHSE_MST
WHERE WHSE_CODE = l_whse_code;
SELECT m.inventory_item_id
FROM IC_ITEM_MST i, MTL_SYSTEM_ITEMS m
WHERE m.organization_id = l_org_id
AND m.segment1 = l_item_no;
SELECT C.OF_SHIP_TO_SITE_USE_ID
FROM OP_CUST_MST C, IC_WHSE_MST I, SY_ORGN_MST S
WHERE S.ORGN_CODE = i.ORGN_CODE
AND S.CO_CODE = C.CO_CODE
AND I.WHSE_CODE = l_whse_code
AND CUST_NO = l_cust_num;
SELECT COUNT(1)
FROM ic_txn_request_lines mo,
oe_order_lines_all l,
oe_order_headers_all h,
oe_transaction_types_all ta,
oe_transaction_types_tl tt
WHERE l.header_id = h.header_id
AND l.line_id = mo.txn_source_line_id
AND tt.transaction_type_id = ta.transaction_type_id
AND ta.transaction_type_id = h.order_type_id
AND ta.org_id = h.org_id
AND mo.line_status <> 5
AND NVL(mo.quantity_delivered,0) < mo.quantity
AND tt.language = userenv('LANG')
AND nvl(l.cancelled_flag,'N') = 'N'
AND l.ship_from_org_id = G_ORG_ID
AND h.order_number between --B2593897
NVL(G_FROM_ORDER_NUM,h.order_number) AND NVL(G_TO_ORDER_NUM,h.order_number)
AND NVL(G_SHIP_TO_ID,l.ship_to_org_id) = l.ship_to_org_id
AND TRUNC(l.schedule_ship_date)
between NVL(G_FROM_SHIP_DATE,TRUNC(l.schedule_ship_date))
AND NVL(G_TO_SHIP_DATE,TRUNC(l.schedule_ship_date))
AND l.inventory_item_id = NVL(G_INV_ITEM_ID, l.inventory_item_id) --B2594191
;
SELECT
h.order_number,
mo.line_id,
mo.organization_id,
mo.txn_source_line_id,
mo.date_required,
mo.quantity,
mo.quantity_detailed,
mo.quantity_delivered,
mo.inventory_item_id
FROM ic_txn_request_lines mo,
oe_order_lines_all l,
oe_order_headers_all h,
oe_transaction_types_all ta,
oe_transaction_types_tl tt
WHERE l.header_id = h.header_id
AND l.line_id = mo.txn_source_line_id
AND tt.transaction_type_id = ta.transaction_type_id
AND ta.transaction_type_id = h.order_type_id
AND ta.org_id = h.org_id
AND mo.line_status <> 5
AND NVL(mo.quantity_delivered,0) < mo.quantity
AND tt.language = userenv('LANG')
AND nvl(l.cancelled_flag,'N') = 'N'
AND l.ship_from_org_id = G_ORG_ID
AND h.order_number between --B2593897
NVL(G_FROM_ORDER_NUM,h.order_number) AND NVL(G_TO_ORDER_NUM,h.order_number)
AND NVL(G_SHIP_TO_ID,l.ship_to_org_id) = l.ship_to_org_id
AND TRUNC(l.schedule_ship_date)
between NVL(G_FROM_SHIP_DATE,TRUNC(l.schedule_ship_date))
AND NVL(G_TO_SHIP_DATE,TRUNC(l.schedule_ship_date))
AND l.inventory_item_id = NVL(G_INV_ITEM_ID, l.inventory_item_id) --B2594191
ORDER BY 1,2;
, p_allow_delete => NULL
, x_number_of_rows => l_number_rows
, x_qc_grade => l_qc_grade
, x_detailed_qty => l_detailed_qty
, x_qty_UM => l_qty_um
, x_detailed_qty2 => l_detailed_qty2
, x_qty_UM2 => l_qty_um2
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
WSH_UTIL_CORE.PRINTMSG(' PICK CONFIRM NOT SELECTED ');
END IF; /* PICK CONFIRM SELECTION */
IF p_delete_reservations: 'Y' The inv transaction is simply
deleted and qtys are returned to the default lot
IF p_delete_reservations: 'N' The inv transaction is kept for the delivery line
as if user has allocated them in order pad
since the move order line is calceled, user has to pick release the line again
in order to transact
OPM does not support WMS yet. So this case is not considered
*/
PROCEDURE Cancel_Move_Order_Line(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_line_id IN NUMBER
,p_delivery_detail_id IN NUMBER
,p_delete_reservations IN VARCHAR2
,p_txn_source_line_id IN NUMBER DEFAULT NULL)
IS
l_quantity NUMBER;
l_deleted_quantity NUMBER;
l_deleted_quantity2 NUMBER;
l_quantity_to_delete NUMBER;
l_quantity2_to_delete NUMBER;
l_delete_reservations VARCHAR2(1);
SELECT quantity
, secondary_quantity
, NVL(quantity_detailed, 0)
, NVL(secondary_quantity_detailed, 0)
, organization_id
FROM ic_txn_request_lines
WHERE line_id = p_line_id
FOR UPDATE;
SELECT source_line_id
, organization_id
FROM wsh_delivery_details
WHERE move_order_line_id IS NOT NULL
AND delivery_detail_id = p_delivery_detail_id
AND move_order_line_id = p_line_id
AND released_status = 'S';
SELECT trans_id
FROM ic_tran_pnd
WHERE line_id = l_txn_source_line_id
AND line_detail_id = p_delivery_detail_id /* should have this filed populated since mo line*/
AND staged_ind = 0
AND delete_mark = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0
AND (lot_id > 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
-- Bug 3962760 - exclude the default transaction.
ORDER BY trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
l_deleted_quantity := 0;
/*IF p_delete_reservations IS NULL OR
p_delete_reservations <> 'Y' THEN
l_delete_reservations := 'N';
gmi_reservation_util.println('L_delete is set to N');
l_delete_reservations := 'Y';
gmi_reservation_util.println('L_delete is set to Y');
/* for OPM delete is always Y*/
--l_delete_reservations := 'Y';
l_delete_reservations := p_delete_reservations;
GMI_Reservation_Util.PrintLn('Delete_rsvs = ' || l_delete_reservations);
/* if the delivery_detail is deleted in shipping, all the rsv with it should be deleted */
IF l_delete_reservations = 'Y'
--AND l_quantity >= l_quantity_detailed
THEN
l_quantity_to_delete := l_quantity_detailed;
l_quantity2_to_delete := l_quantity2_detailed;
GMI_Reservation_Util.PrintLn('Qty to delete = ' || l_quantity_to_delete);
EXIT WHEN l_quantity_to_delete <= 0;
IF abs(l_trans_rec.trans_qty) <= l_quantity_to_delete THEN
-- if so, simply delete the rsv , will balance default later
GMI_Reservation_Util.PrintLn('set delete trans' );
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_trans_rec
, l_trans_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
l_deleted_quantity := l_deleted_quantity + abs(l_trans_rec.trans_qty);
l_deleted_quantity2 := l_deleted_quantity2 + abs(l_trans_rec.trans_qty2);
GMI_Reservation_Util.PrintLn('update ic_tran_pnd ' );
l_trans_rec.trans_qty := -1 * (abs(l_trans_rec.trans_qty) - l_quantity_to_delete);
l_trans_rec.trans_qty2 := -1 * (abs(l_trans_rec.trans_qty2) - l_quantity2_to_delete);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_trans_rec
, l_trans_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
l_deleted_quantity2 := l_deleted_quantity2 + l_quantity2_to_delete;
l_quantity_to_delete := l_quantity_to_delete - abs(l_trans_rec.trans_qty);
l_quantity2_to_delete := l_quantity2_to_delete - abs(l_trans_rec.trans_qty2);
Select ship_from_org_id
Into l_ship_from_org_id
From oe_order_lines_all
Where line_id = l_txn_source_line_id;
Select mtl_organization_id
Into l_organization_id
From ic_whse_mst
Where whse_code = l_trans_rec.whse_code;
/* delete this default lot, and created a new one */
GMI_Reservation_Util.PrintLn('delete trans trans_id'||l_trans_rec.trans_id);
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_trans_rec
, l_trans_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
Select whse_code
Into l_trans_rec.whse_code
From ic_whse_mst
Where mtl_organization_id = l_ship_from_org_id
and rownum = 1; -- just in case, should only have 1 row
If l_deleted_quantity >= l_quantity Then
l_quantity := 0;
l_quantity := l_quantity - l_deleted_quantity;
l_quantity2 := l_quantity2 - l_deleted_quantity2;
UPDATE ic_txn_request_lines
SET quantity = l_quantity
,secondary_quantity = l_quantity2
,line_status = l_line_status
WHERE line_id = p_line_id;
l_deleted_quantity NUMBER;
l_deleted_quantity2 NUMBER;
l_quantity_to_delete NUMBER;
l_quantity2_to_delete NUMBER;
l_prim_quantity_to_delete NUMBER;
SELECT quantity
, secondary_quantity
, NVL(quantity_detailed, 0)
, NVL(secondary_quantity_detailed, 0)
, organization_id
, inventory_item_Id
, uom_code
FROM ic_txn_request_lines
WHERE line_id = p_line_id
FOR UPDATE;
SELECT primary_uom_code
FROM mtl_system_items
WHERE organization_Id = l_organization_id
AND inventory_Item_Id = l_inventory_Item_Id;
SELECT source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id IS NOT NULL
AND move_order_line_id = p_line_id
AND released_status = 'S';
SELECT trans_id
FROM ic_tran_pnd
WHERE line_id = l_txn_source_line_id
AND (line_detail_id = p_delivery_detail_id
or nvl(line_detail_id, -1 ) = -1 )
AND staged_ind = 0
AND delete_mark = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0
AND (lot_id > 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
-- Bug 3962760 - exclude the default transaction.
ORDER BY line_detail_id desc,
trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
SELECT trans_id
FROM ic_tran_pnd
WHERE line_id = l_txn_source_line_id
AND doc_type = 'OMSO'
AND delete_mark = 0
AND (lot_id = 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
l_deleted_quantity := 0;
l_deleted_quantity2 := 0;
l_quantity_to_delete := -l_reduction_quantity + l_remaining_quantity;
l_quantity2_to_delete := -p_reduction_quantity2 + (l_quantity2 - l_quantity2_detailed);
gmi_reservation_util.println('Value of l_quantity_to_delete is '||l_quantity_to_delete);
,p_delete_reservations => 'Y'
,p_txn_source_line_id => p_txn_source_line_id);
/* need to delete or reduce some of the inv transactions*/
IF l_quantity_to_delete > 0 THEN
/* move order uom should be always primary uom */
/*If l_primary_uom_code <> l_mo_uom_code Then
null;
l_prim_quantity_to_delete :=
GMI_Reservation_Util.get_opm_converted_qty(
p_apps_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_apps_from_uom => l_uom_code
p_apps_to_uom => c.requested_quantity_uom,
p_original_qty => p_changed_attributes(l_counter).ordered_quantity);
l_prim_quantity_to_delete := l_quantity_to_delete;
EXIT WHEN l_quantity_to_delete <= 0;
IF abs(l_trans_rec.trans_qty) <= l_quantity_to_delete THEN
-- if so, simply delete the rsv , will balance default later
gmi_reservation_util.println('Going to delete tran in reduceMove');
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_trans_rec
, l_trans_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
l_deleted_quantity := l_deleted_quantity + abs(l_trans_rec.trans_qty);
l_deleted_quantity2:= l_deleted_quantity2 + abs(l_trans_rec.trans_qty2);
gmi_reservation_util.println('In ELSE and will be updateing ic_tran');
l_trans_rec.trans_qty := -1 * (abs(l_trans_rec.trans_qty) - l_quantity_to_delete);
l_trans_rec.trans_qty2:= -1 * (abs(l_trans_rec.trans_qty2) - l_quantity2_to_delete);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( 1
, FND_API.G_FALSE
, FND_API.G_FALSE
, FND_API.G_VALID_LEVEL_FULL
, l_trans_rec
, l_trans_row
, x_return_status
, x_msg_count
, x_msg_data
);
GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
l_deleted_quantity2 := l_deleted_quantity2 + l_quantity2_to_delete;
l_quantity_to_delete := l_quantity_to_delete - l_deleted_quantity;
l_quantity2_to_delete := l_quantity2_to_delete - l_deleted_quantity2;
gmi_reservation_util.println('Value of p_line_id before update is '||p_line_id);
UPDATE ic_txn_request_lines
SET quantity = l_quantity
, primary_quantity = l_quantity
, secondary_quantity = l_quantity2
, quantity_detailed = quantity_detailed - l_deleted_quantity
, secondary_quantity_detailed = secondary_quantity_detailed - l_deleted_quantity2
WHERE line_id = p_line_id;
PROCEDURE update_txn_source_line
( p_line_id IN NUMBER
, p_new_source_line_id IN NUMBER
) IS
BEGIN
UPDATE ic_txn_request_lines
SET txn_source_line_id = p_new_source_line_id
WHERE line_id = p_line_id;
/*UPDATE mtl_material_transactions_temp
SET trx_source_line_id = p_new_source_line_id
WHERE move_order_line_id = p_line_id;*/
END update_txn_source_line;