[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. |
| |
23-Feb-09 G. Muratore Bug 8234700. pass in false to process transactions
and commit after. This more closely mimics how save is done on the form and
commits all work together. GMF layers are not lost anymore.
| 09-jun-2009 Srinivasulu Puri backport of Bug 6925025
| Added parameters subinventory_code and locator_id.
| Pass these parameters to build_txn_inter_lot call.
|
| 30-Mar-10 APMISHRA Bug 9367054. Added a new procedure |
| print_label to print labels for product |
| transactions |
| 15-Apr-10 APMISHRA Bug 9483781. Modified the procedure |
| Create_material_txn to process the LPN |
| details existing for a reservation |
| 16-may-12 APMISHRA Bug 13986776 Added Code to call save batch |
| |
| 08-Aug-12 APMISHRA Bug 14376915 Modified |
| Validate_Step_Completion_Date to compare the input dates |
| after converting them to the correct format |
+===========================================================================*/
g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
SELECT reservation_id from gmo_material_dispenses
WHERE dispense_id = p_rsrv_pndlot_id;
fnd_msg_pub.delete_msg;
Select lpn_id from mtl_reservations
where reservation_id = p_reservation_id;
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;
SELECT
msi.concatenated_segments item,
bh.batch_no batch_no,
mmt.transaction_id,
mmt.created_by,
mmt.transaction_type_id
FROM gme_batch_header_vw bh,
mtl_parameters mp,
gme_material_details bl,
mtl_system_items_vl msi,
mtl_material_transactions mmt
where mmt.transaction_set_id = p_txn_header_id AND
bh.batch_id = mmt.TRANSACTION_SOURCE_ID AND
bl.material_detail_id = mmt.TRX_SOURCE_LINE_ID AND
mmt.organization_id = mp.organization_id AND
bh.batch_id = bl.batch_id AND
bl.organization_id = msi.organization_id AND
bl.inventory_item_id = msi.inventory_item_id;