[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Update_Product_Pending_Lot |
| 21-Jun-06 Shrikant Nene Bug#5263908. Added revision in the proc |
| Populate_Dispensing_Table. |
| 11-Jul-06 Shrikant Nene Bug#5331639. Changed procedure |
| Validate_Item_For_IB |
| 26-Jan-07 Archana Mundhe Bug 4774944. Modified release_step and |
| complete_step procedure. Added call to |
| validate step for release and complete. |
| 28-Feb-07 Archana Mundhe Bug 4774944. REWORK Modified release_step |
| and complete_step procedure. Added code to|
| check for parameter step controls batch |
| status. |
| |
+===========================================================================*/
g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
fnd_msg_pub.delete_msg;
SELECT transaction_type_name
INTO l_type
FROM mtl_transaction_types
WHERE transaction_type_id = p_transaction_type_id;
SELECT substr(concatenated_segments,1,100)
INTO l_item
FROM mtl_system_items_kfv
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT substr(concatenated_segments,1,100)
INTO l_locator
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND subinventory_code = p_sub_code
AND inventory_location_id = p_locator_id;
SELECT ABS(SUM (NVL(primary_quantity,0))),
ABS(SUM (NVL(secondary_transaction_quantity,0)))
FROM mtl_material_transactions
WHERE organization_id = p_org_id
AND transaction_source_id = p_batch_id
AND trx_source_line_id = p_material_detail_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type;
SELECT m.revision,lot_number,
ABS(SUM (NVL(l.primary_quantity,0))),
ABS(SUM (NVL(l.secondary_transaction_quantity,0)))
FROM mtl_material_transactions m,
mtl_transaction_lot_numbers l
WHERE l.transaction_id = m.transaction_id
AND lot_number = NVL(p_lot_number, l.lot_number)
AND m.organization_id = p_org_id
AND m.transaction_source_id = p_batch_id
AND m.trx_source_line_id = p_material_detail_id
AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
GROUP BY m.revision,l.lot_number;
SELECT ABS(SUM (NVL(primary_quantity,0))),
ABS(SUM (NVL(secondary_transaction_quantity,0)))
FROM mtl_material_transactions
WHERE organization_id = p_org_id
AND transaction_source_id = p_batch_id
AND trx_source_line_id = p_material_detail_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND revision = p_revision
GROUP BY revision;
SELECT
m.transaction_id,
m.transaction_quantity*(-1),
m.primary_quantity *(-1),
m.secondary_transaction_quantity *(-1),
m.transaction_uom,
m.subinventory_code,
m.locator_id,
m.reason_id,
l.concatenated_segments,
TO_CHAR(transaction_date, l_date_format),
revision
FROM mtl_material_transactions m,
wms_item_locations_kfv l
WHERE trx_source_line_id = NVL(p_material_detail_id, trx_source_line_id)
AND transaction_source_id = p_batch_id
AND transaction_type_id = l_txn_type_id
AND l.inventory_location_id = m.locator_id(+);
SELECT
lot_number,
transaction_quantity *(-1),
primary_quantity *(-1),
secondary_transaction_quantity *(-1)
FROM mtl_transaction_lot_numbers
WHERE transaction_id = p_transaction_id AND
lot_number = NVL(p_lot_number, lot_number);
SELECT 1
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number ;
SELECT b.parentline_id
FROM gme_batch_header_vw b
WHERE batch_type = 0
AND organization_id = p_organization_id
AND batch_id = p_batch_id;
SELECT s.batchstep_no
FROM gme_batch_steps s,
gme_batch_step_items i,
gme_material_details m
WHERE m.batch_id = p_batch_id
AND s.batch_id = p_batch_id
AND i.batch_id = p_batch_id
AND m.release_type = 2
AND s.batchstep_id = i.batchstep_id
AND i.material_detail_id = m.material_detail_id
AND s.step_status = 4;
SELECT release_type,
-- Bug#5331639. Compare wip_plan_qty instead of plan_qty
NVL(wip_plan_qty, 0) wip_planned_qty,
NVL(phantom_type, 0) phantom_ind,
phantom_line_id
FROM gme_material_details
WHERE material_detail_id = p_material_detail_id
AND organization_id = p_organization_id
AND batch_id = p_batch_id;
l_input_rec.last_update_date := SYSDATE;
l_input_rec.last_updated_by := p_uid;
GME_API_PUB.Update_Batchstep_Resource
( p_api_version => 2.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, p_batchstep_resource_rec => l_input_rec
, x_batchstep_resource_rec => l_output_rec
, x_message_count => l_msg_count
, x_message_list => l_msg_list
, x_return_status => l_return_status
);
SELECT
l.pending_product_lot_id,
l.lot_number,
n.parent_lot_number, --nsinghi bug#5236906. Add this column
l.revision,
l.sequence,
l.quantity,
NVL(l.secondary_quantity, 0),
NVL (l.reason_id, -1),
reason_name,
TO_CHAR(l.last_update_date, 'MM/DD/YYYY HH24:MI:SS')
FROM
gme_pending_product_lots l,
mtl_transaction_reasons r,
gme_material_details m, -- nsinghi bug#5236906. Add join to gme_material_details and MLN
mtl_lot_numbers n
WHERE
l.batch_id = p_batch_id AND
l.material_detail_id = p_material_detail_id AND
l.lot_number = NVL(p_lot_number, l.lot_number) AND
m.material_detail_id = l.material_detail_id AND -- nsinghi bug#5236906. Added where conditions
m.inventory_item_id = n.inventory_item_id AND
m.organization_id = n.organization_id AND
l.lot_number = n.lot_number AND
l.reason_id = r.reason_id(+);
SELECT
l.pending_product_lot_id,
l.lot_number,
n.parent_lot_number,
l.revision,
l.sequence,
l.quantity,
NVL(l.secondary_quantity, 0),
NVL (l.reason_id, -1),
reason_name,
TO_CHAR(l.last_update_date, 'MM/DD/YYYY HH24:MI:SS')
FROM
gme_pending_product_lots l,
mtl_transaction_reasons r,
gme_material_details m,
mtl_lot_numbers n
WHERE
l.batch_id = p_batch_id AND
l.material_detail_id = p_material_detail_id AND
l.lot_number = NVL(p_lot_number, l.lot_number) AND
(p_rev_control = 0 OR (p_rev_control = 1 AND l.revision IS NOT NULL)) AND
m.material_detail_id = l.material_detail_id AND
m.inventory_item_id = n.inventory_item_id AND
m.organization_id = n.organization_id AND
l.lot_number = n.lot_number AND
l.reason_id = r.reason_id(+);
SELECT NVL(tracking_quantity_ind, 'P')
FROM mtl_system_items_b msi, gme_material_details gmd
WHERE gmd.inventory_item_id = msi.inventory_item_id AND
gmd.organization_id = msi.organization_id AND
gmd.material_detail_id = p_material_detail_id;
| Update_Product_Pending_Lot
|
| USAGE
|
| ARGUMENTS
|
| RETURNS
|
| HISTORY
| Created 26-Apr-05 Eddie Oumerretane
| Bug#5236906. 09-Jun-06 Namit S. Send Lot Number too when updating Pending Lot.
|
+========================================================================+*/
PROCEDURE Update_Product_Pending_Lot(p_batch_id IN NUMBER,
p_material_detail_id IN NUMBER,
p_lot_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_sequence IN NUMBER,
p_qty IN NUMBER,
p_sec_qty IN NUMBER,
p_reason_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_org_code IN VARCHAR2,
p_last_update_date IN VARCHAR2,
p_is_seq_changed IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2)
IS
l_pending_product_lots_rec gme_pending_product_lots%ROWTYPE;
SELECT NVL(tracking_quantity_ind, 'P')
FROM mtl_system_items_b msi, gme_material_details gmd
WHERE gmd.inventory_item_id = msi.inventory_item_id AND
gmd.organization_id = msi.organization_id AND
gmd.material_detail_id = p_material_detail_id;
gme_debug.log_initialize ('MobileUpdatePendingLot');
l_pending_product_lots_rec.last_update_date := to_date(p_last_update_date, 'MM/DD/YYYY HH24:MI:SS');
gme_debug.put_line('Update Pending Lot: ');
gme_debug.put_line('Last Upd Date = '|| to_char(l_pending_product_lots_rec.last_update_date, 'MM/DD/YYYY HH24:MI:SS'));
GME_API_PUB.update_pending_product_lot
(p_api_version => 2.0
,p_validation_level => gme_common_pvt.g_max_errors
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_true
,x_message_count => l_message_count
,x_message_list => l_message_list
,x_return_status => x_return_status
,p_batch_header_rec => l_batch_header_rec
,p_org_code => p_org_code
,p_material_detail_rec => l_material_detail_rec
,p_pending_product_lots_rec => l_pending_product_lots_rec
,x_pending_product_lots_rec => l_out_pending_product_lots_rec);
gme_debug.put_line('When others exception in Update Pending Lots');
fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','update_pending_product_lots');
END Update_Product_Pending_Lot;
DELETE FROM GME_MATERIAL_DISPENSING_GTMP;
gme_debug.put_line('Inserting Dispensed record ');
INSERT INTO GME_MATERIAL_DISPENSING_GTMP
(
DISPENSE_ID
,SUBINVENTORY_CODE
,LOCATOR_ID
,DISPENSE_UOM
,DISPENSED_QTY
,SECONDARY_DISPENSED_QTY
,LOT_NUMBER
,REVISION
)
VALUES
(
l_rsrv_tab(l_index).external_source_line_id
,l_rsrv_tab(l_index).subinventory_code
,l_rsrv_tab(l_index).locator_id
,l_rsrv_tab(l_index).reservation_uom_code
,l_rsrv_tab(l_index).reservation_quantity
,l_rsrv_tab(l_index).secondary_reservation_quantity
,l_rsrv_tab(l_index).lot_number
,l_rsrv_tab(l_index).revision
);
| Delete_Dispensing_Record
|
| USAGE
|
| ARGUMENTS
|
| RETURNS
|
| HISTORY
| Created 26-Apr-05 Eddie Oumerretane
|
+========================================================================+*/
PROCEDURE Delete_Dispensing_Record(
p_dispense_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2)
IS
BEGIN
IF (g_debug IS NOT NULL) THEN
gme_debug.log_initialize ('MobileDelDispRec');
DELETE FROM GME_MATERIAL_DISPENSING_GTMP
WHERE dispense_id = p_dispense_id;
gme_debug.put_line('When others exception in Delete_Dispensing_Record');
fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Delete_Dispensing_Record');
END Delete_Dispensing_Record;
SELECT count(*)
FROM GME_MATERIAL_DISPENSING_GTMP
WHERE subinventory_code = p_subinv_code AND
NVL(locator_id, -1) = NVL(p_locator_id, -1);
| Update_Qty_Tree
|
| USAGE
|
| ARGUMENTS
|
| RETURNS
|
| HISTORY
| Created 26-Apr-05 Eddie Oumerretane
|
+========================================================================+*/
PROCEDURE Update_Qty_Tree ( p_tree_id IN NUMBER,
p_revision IN VARCHAR2,
p_subinventory_code IN VARCHAR2,
p_locator_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_primary_qty IN NUMBER,
p_secondary_qty IN NUMBER,
p_quantity_type IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_msg_count NUMBER(10);
UPDATE_TREE_ERROR EXCEPTION;
gme_debug.log_initialize ('MobileUpdateQtyTree');
INV_Quantity_Tree_Grp.Update_Quantities(
p_api_version_number => 1.0,
p_init_msg_lst => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => x_error_msg,
p_tree_id => p_tree_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_primary_quantity => p_primary_qty,
p_quantity_type => p_quantity_type,
p_secondary_quantity => p_secondary_qty,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr,
x_sqoh => l_sqoh,
x_srqoh => l_srqoh,
x_sqr => l_sqr,
x_sqs => l_sqs,
x_satt => l_satt,
x_satr => l_satr,
p_containerized => 0,
p_lpn_id => NULL);
RAISE UPDATE_TREE_ERROR;
WHEN UPDATE_TREE_ERROR THEN
IF g_debug <= gme_debug.g_log_unexpected THEN
gme_debug.put_line('Update Qty Tree exception');
gme_debug.put_line('When others exception in Update Qty Tree');
fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','Update_Qty_Tree');
END Update_Qty_Tree;
SELECT
NVL(lot_divisible_flag, 'N'),
NVL(lot_control_code, 1)
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT release_type,
NVL(phantom_type,0),
phantom_line_id
FROM gme_material_details
WHERE material_detail_id = p_material_detail_id;
SELECT count(*)
FROM gme_pending_product_lots
WHERE batch_id = p_batch_id;
SELECT NVL(dispense_ind,'N')
FROM gme_material_details
WHERE material_detail_id = p_material_detail_id;
SELECT release_type
FROM gme_material_details
WHERE material_detail_id = p_material_detail_id;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT DISTINCT
m.transaction_id,
m.transaction_quantity*(-1),
m.primary_quantity *(-1),
m.secondary_transaction_quantity *(-1),
m.transaction_uom,
m.subinventory_code,
m.locator_id,
m.reason_id,
lc.concatenated_segments,
TO_CHAR(m.transaction_date, l_date_format),
revision
FROM mtl_material_transactions m,
mtl_transaction_lot_numbers l,
wms_item_locations_kfv lc
WHERE l.transaction_id = m.transaction_id
AND l.lot_number = NVL(p_lot_number, l.lot_number)
AND m.organization_id = p_organization_id
AND m.transaction_source_id = p_batch_id
AND m.trx_source_line_id = p_material_detail_id
AND m.transaction_type_id = GME_COMMON_PVT.g_ing_issue
AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND lc.inventory_location_id(+) = m.locator_id;
SELECT DISTINCT
m.transaction_id,
m.transaction_quantity*(-1),
m.primary_quantity *(-1),
m.secondary_transaction_quantity *(-1),
m.transaction_uom,
m.subinventory_code,
m.locator_id,
m.reason_id,
lc.concatenated_segments,
TO_CHAR(m.transaction_date, l_date_format),
revision
FROM mtl_material_transactions m,
mtl_transaction_lot_numbers l,
wms_item_locations_kfv lc
WHERE l.transaction_id = m.transaction_id
AND l.lot_number = NVL(p_lot_number, l.lot_number)
AND m.organization_id = p_organization_id
AND m.transaction_source_id = p_batch_id
AND m.trx_source_line_id = p_material_detail_id
AND m.transaction_type_id = l_txn_type_id
AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND lc.inventory_location_id(+) = m.locator_id;
SELECT *
FROM mtl_transactions_interface mmti
WHERE transaction_interface_id = v_transaction_id;
SELECT *
FROM mtl_transaction_lots_interface
WHERE transaction_interface_id = v_transaction_id;
SELECT actual_step_qty INTO l_step_qty
FROM gme_batch_steps
WHERE batch_id = p_batch_id
AND batchstep_id = p_step_id;
IF l_step_qty <> p_act_step_qty THEN /* Update Act Step Qty */
UPDATE gme_batch_steps
SET actual_step_qty = p_act_step_qty
WHERE batch_id = p_batch_id
AND batchstep_id = p_step_id;
SELECT NVL(automatic_step_calculation,0) INTO x_ASQC_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT TO_CHAR(sysdate, p_date_format||HOUR_MIN_SEC_FORMAT_STRING)
INTO x_sys_date
FROM sys.DUAL;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT actual_start_date
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT NVL(locator_type, 1)
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, SYSDATE + 1) > SYSDATE
AND secondary_inventory_name = p_subinventory_code
AND quantity_tracked = 1;
SELECT NVL(s.locator_type, 1)
FROM mtl_secondary_inventories s,
mtl_item_sub_inventories i
WHERE s.secondary_inventory_name = i.secondary_inventory
AND s.organization_id = i.organization_id
AND s.organization_id = p_organization_id
AND i.inventory_item_id = p_inventory_item_id
AND NVL(s.disable_date, SYSDATE + 1) > SYSDATE
AND secondary_inventory_name = p_subinventory_code
AND s.quantity_tracked = 1;
SELECT inventory_location_id
FROM wms_item_locations_kfv
WHERE organization_id = p_organization_id
AND NVL(disable_date, SYSDATE + 1) > SYSDATE
AND subinventory_code = p_subinventory_code
AND concatenated_segments = p_locator_code;
SELECT a.inventory_location_id
FROM wms_item_locations_kfv a,
mtl_secondary_locators b
WHERE b.organization_id = p_organization_Id
AND b.inventory_item_id = p_Inventory_Item_Id
AND b.subinventory_code = p_Subinventory_Code
AND a.inventory_location_id = b.secondary_locator
AND NVL(a.disable_date, SYSDATE+1) > SYSDATE
AND a.concatenated_segments = p_locator_code;
SELECT subinventory, loc.concatenated_segments,
m.locator_id
FROM gme_material_details m, wms_item_locations_kfv loc
WHERE m.subinventory = loc.subinventory_code (+)
AND m.locator_id = loc.inventory_location_id (+)
AND m.batch_id = p_batch_id
AND m.material_Detail_id = p_material_detail_id;