[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dispense_LoV to select LPN number |
|17-Jun-11 Apeksha Mishra Bug 12562057 |
| Remove explicit parameter names as it does |
| not compile on versions below 11g |
+===========================================================================*/
--- For GTIN support
g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
SELECT NVL(mr.revision,' '),
NVL(mr.lot_number,' '),
NVL(mr.subinventory_code,' '),
NVL(lo.concatenated_segments,' ') locator,
mr.reservation_quantity,
mr.reservation_uom_code,
TO_CHAR(mr.requirement_date,l_date_format),
NVL(RTRIM(lpn.LICENSE_PLATE_NUMBER),' '), /*Bug9483781*/
nvl(mr.secondary_reservation_quantity,0), /*Bug7041074*/
mr.reservation_id,
lo.inventory_location_id
FROM mtl_reservations mr,
wms_item_locations_kfv lo,
wms_license_plate_numbers lpn
WHERE lo.inventory_location_id(+) = mr.locator_id
AND mr.organization_id = p_org_id
AND mr.demand_source_type_id = 5
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_material_detail_id
AND LPN.lpn_id(+) = mr.lpn_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date, mr.reservation_id;
SELECT NVL(revision,' '),
NVL(d.lot_number,' '),
NVL(d.subinventory_code,' '),
NVL(lo.concatenated_segments,' ') locator,
d.dispensed_qty,
d.dispense_uom,
TO_CHAR(SYSDATE,l_date_format),
NULL, /*Bug9483781*/
d.secondary_dispensed_qty,
d.dispense_id,
lo.inventory_location_id
FROM gme_material_dispensing_gtmp d,
wms_item_locations_kfv lo
WHERE lo.inventory_location_id(+) = d.locator_id
AND lo.subinventory_code(+) = d.subinventory_code
ORDER BY d.dispense_id;
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status in (1,2)
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
ORDER BY batch_no DESC;
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status = 2
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
ORDER BY batch_no DESC;
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status in (2,3)
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
ORDER BY batch_no DESC;
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status in (2,3)
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND parentline_id IS NULL
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
ORDER BY batch_no DESC;
ELSIF p_statuses = 'UPDATE_RSRC_USAGE' THEN
OPEN x_batch_cursor FOR
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status in (2,3)
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND routing_id IS NOT NULL
AND routing_id > 0
AND automatic_step_calculation = 0
AND automatic_step_calculation IN (0,1)
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
AND EXISTS
( SELECT 1
FROM gme_batch_steps
WHERE batch_id = b.batch_id
AND step_status in (2,3)
)
UNION
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header h,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status = 3
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND routing_id IS NOT NULL
AND routing_id > 0
AND automatic_step_calculation = 1
AND lkup.lookup_type = 'BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(h.update_inventory_ind,'N') = 'Y' --Bug#5763793
AND EXISTS
( SELECT 1
FROM gme_batch_steps
WHERE batch_id = h.batch_id
AND step_status = 3
)
ORDER BY batch_no DESC;
SELECT batch_no
,meaning
,batch_status
,batch_id
,NVL(formula_id,0)
,NVL(routing_id,0)
FROM gme_batch_header b,
gem_lookups lkup
WHERE organization_id = p_org_id
AND batch_type= 0
AND batch_status in (1,2)
--- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
AND batch_no LIKE p_batch_no
AND delete_mark = 0
AND routing_id IS NOT NULL
AND lkup.lookup_type = 'GME_BATCH_STATUS'
AND lkup.lookup_code = batch_status
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
ORDER BY batch_no DESC;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.line_type = -1
AND d.phantom_type = 0
AND i.concatenated_segments = NVL(p_item_no, i.concatenated_segments)
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.reservable_type = 1
AND d.line_no LIKE (p_line_no)
AND NOT EXISTS
(SELECT 1
FROM gme_batch_step_items i2,
gme_batch_steps s
WHERE i2.batch_id = d.batch_id
AND s.batch_id = d.batch_id
AND i2.material_detail_id = d.material_detail_id
AND i2.batchstep_id = s.batchstep_id
AND s.step_status IN (3,4,5))
ORDER BY d.line_no;
SELECT DISTINCT
d.line_no,
i.concatenated_segments,
i.description,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
AND i.concatenated_segments = NVL(p_item_no,i.concatenated_segments)
--- For GTIN support
UNION
SELECT DISTINCT
d.line_no,
i.concatenated_segments,
i.description,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id OR
mcr.org_independent_flag = 'Y')
ORDER BY line_no;
SELECT DISTINCT
d.line_no,
i.concatenated_segments,
i.description,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
AND i.concatenated_segments = NVL(p_item_no,i.concatenated_segments)
--- For GTIN support
UNION
SELECT DISTINCT
d.line_no,
i.concatenated_segments,
i.description,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id OR mcr.org_independent_flag = 'Y')
ORDER BY line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.lot_control_code = 2
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.lot_control_code = 2
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.release_type IN (1,2) --- Manual/Incremental
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT d.line_no,
i.concatenated_segments,
i.inventory_item_id,
d.material_detail_id
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE
d.batch_id = p_batch_id
AND d.line_type = p_line_type
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.release_type IN (1,2) --- Manual/Incremental
AND d.line_no LIKE (p_line_no)
ORDER BY d.line_no;
SELECT DISTINCT
s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_steps s,
gme_batch_step_items i,
gmd_operations o
WHERE
i.batch_id = p_batch_id
AND s.batch_id = i.batch_id
AND i.batchstep_id = s.batchstep_id
AND s.step_status <> 4
AND s.batchstep_no LIKE (p_step_no)
AND s.oprn_id = o.oprn_id
ORDER BY 1;
SELECT DISTINCT
s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_steps s,
gme_batch_step_items i,
gmd_operations o,
gme_material_details d
WHERE
i.batch_id = p_batch_id
AND s.batch_id = i.batch_id
AND i.batchstep_id = s.batchstep_id
AND i.material_detail_id = d.material_detail_id
AND d.line_type IN (1,2)
AND s.step_status <> 4
AND s.batchstep_no LIKE (p_step_no)
AND s.oprn_id = o.oprn_id
ORDER BY 1;
SELECT DISTINCT
s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_steps s,
gme_batch_step_items i,
gmd_operations o,
gme_material_details d
WHERE
i.batch_id = p_batch_id
AND s.batch_id = i.batch_id
AND i.batchstep_id = s.batchstep_id
AND i.material_detail_id = d.material_detail_id
AND d.line_type IN (1,2)
AND s.step_status IN (1,2)
AND s.batchstep_no LIKE (p_step_no)
AND s.oprn_id = o.oprn_id
ORDER BY 1;
SELECT DISTINCT
s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_steps s,
gme_batch_step_items i,
gmd_operations o,
gme_material_details d
WHERE
i.batch_id = p_batch_id
AND s.batch_id = i.batch_id
AND i.batchstep_id = s.batchstep_id
AND i.material_detail_id = d.material_detail_id
AND s.step_status IN (1,2,3)
AND s.batchstep_no LIKE (p_step_no)
AND s.oprn_id = o.oprn_id
ORDER BY 1;
SELECT reason_name, description, reason_id
FROM mtl_transaction_reasons
WHERE reason_name like (p_reason_name)
AND NVL(disable_date, SYSDATE+1) > SYSDATE
ORDER BY reason_name;
SELECT a.activity,
a.batchstep_activity_id
FROM gme_batch_header h,
gme_batch_steps s,
gme_batch_step_activities a
WHERE h.organization_id = p_organization_id
AND h.batch_id = p_batch_id
AND h.batch_type = 0
AND h.batch_id = s.batch_id
AND s.batchstep_no = p_step_no
AND s.batchstep_id = a.batchstep_id
AND h.batch_id = a.batch_id
--- Bug 5236930 AND a.activity LIKE LTRIM(RTRIM(p_activity||'%'))
AND a.activity LIKE p_activity
ORDER BY a.activity;
SELECT r.resources,
r.batchstep_resource_id
FROM gme_batch_header h,
gme_batch_steps s,
gme_batch_step_activities a,
gme_batch_step_resources r
WHERE h.organization_id = p_org_id
AND h.batch_id = p_batch_id
AND h.batch_type = 0
AND h.batch_id = s.batch_id
AND s.batchstep_no = p_step_no
AND s.batchstep_id = a.batchstep_id
AND h.batch_id = a.batch_id
AND r.batch_id = h.batch_id
AND r.batchstep_id = s.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND r.batchstep_activity_id = p_activity_id
--- Bug 5236930 AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
AND r.resources LIKE p_resource
ORDER BY r.resources;
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.reservable_type = 1
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.inventory_item_id = mcr.inventory_item_id
AND i.reservable_type = 1
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND d.phantom_type = 0
AND i.reservable_type = 1
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND d.phantom_type = 0
AND i.inventory_item_id = mcr.inventory_item_id
AND i.reservable_type = 1
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = -1
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY line_no;
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y')
ORDER BY line_no;
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 2),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.lot_control_code = 2
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 2),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.lot_control_code = 2
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 2),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.lot_control_code = 2
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 2),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.lot_control_code = 2
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.release_type IN (1,2) --- Manual/Incremental
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND i.mtl_transactions_enabled_flag = 'Y'
AND d.release_type IN (1,2) --- Manual/Incremental
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'N', --- gtin_entered_ind
NULL, --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND d.release_type IN (1,2) --- Manual/Incremental
AND i.mtl_transactions_enabled_flag = 'Y'
AND i.concatenated_segments LIKE (p_item_no)
--- For GTIN support
UNION
SELECT DISTINCT
i.concatenated_segments,
i.description,
d.line_no,
i.inventory_item_id,
d.material_detail_id,
NVL(i.lot_control_code, 1),
NVL(i.location_control_code, 1),
'Y', --- gtin_entered_ind
NVL(uom_code, ' '), --- GTIN UOM code
NVL(i.restrict_locators_code, 2),
NVL(i.grade_control_flag, 'N'),
NVL(i.lot_status_enabled, 'N'),
NVL(i.lot_divisible_flag, 'N')
FROM
gme_material_details d,
mtl_cross_references mcr,
gme_batch_step_items si,
gme_batch_steps s,
mtl_system_items_kfv i
WHERE d.batch_id = p_batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND d.line_no = NVL(p_line_no, d.line_no)
AND d.line_type = p_line_type
AND i.inventory_item_id = mcr.inventory_item_id
AND d.release_type IN (1,2) --- Manual/Incremental
AND i.mtl_transactions_enabled_flag = 'Y'
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND mcr.cross_reference LIKE l_cross_ref
AND (mcr.organization_id = i.organization_id
OR
mcr.org_independent_flag = 'Y');
SELECT s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_header h,
gme_batch_steps s,
gmd_operations o
WHERE h.batch_id = p_batch_id
AND h.batch_type = 0
AND h.batch_id = s.batch_id
AND automatic_step_calculation = 0
--- Bug 5236930 AND to_char(s.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
AND to_char(s.batchstep_no) LIKE p_step_no
AND s.oprn_id = o.oprn_id
AND s.step_status in (2,3)
UNION
SELECT s.batchstep_no,
o.oprn_desc,
s.batchstep_id
FROM gme_batch_header h,
gme_batch_steps s,
gmd_operations o
WHERE h.batch_id = p_batch_id
AND h.batch_type = 0
AND h.batch_id = s.batch_id
AND automatic_step_calculation = 1
--- Bug 5236930 AND to_char(s.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
AND to_char(s.batchstep_no) LIKE p_step_no
AND s.oprn_id = o.oprn_id
AND s.step_status = 3
ORDER BY 1;
SELECT s.secondary_inventory_name
, s.description
, 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_item_id
AND NVL(s.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND s.secondary_inventory_name LIKE (p_subinv_code)
AND s.quantity_tracked = 1
ORDER BY s.secondary_inventory_name;
SELECT secondary_inventory_name
, description
, NVL(locator_type, 1)
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND secondary_inventory_name LIKE (p_subinv_code)
AND quantity_tracked = 1
ORDER BY secondary_inventory_name;
SELECT subinv_loc_ind
FROM gme_parameters
WHERE organization_id = p_organization_id;
SELECT DISTINCT sub.secondary_inventory_name, sub.description,
NVL(sub.locator_type, 1)
FROM mtl_secondary_inventories sub, mtl_onhand_sub_v onh
WHERE sub.organization_id = onh.organization_id
AND sub.secondary_inventory_name = onh.subinventory_code
AND NVL(sub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND sub.quantity_tracked = 1
AND sub.organization_id = p_organization_id
AND onh.inventory_item_id = p_inventory_item_id
AND (p_revision IS NULL OR onh.revision = p_revision)
AND secondary_inventory_name LIKE (p_subinventory_code)
AND total_qoh > 0;
SELECT DISTINCT s.secondary_inventory_name
, s.description
, NVL(s.locator_type, 1)
, NVL (b.concatenated_segments, ' ')
---,rtrim(substr(INV_UTILITIES.get_conc_segments(p_organization_id, d.locator_id),1,255)) locator
,d.lot_number
,d.dispensed_qty
,NVL(d.secondary_dispensed_qty,0)
,d.dispense_uom
, dispense_id
,d.revision
FROM mtl_secondary_inventories s,
wms_item_locations_kfv b,
gme_material_dispensing_gtmp d
WHERE
d.subinventory_code LIKE (p_subinv_code)
AND s.secondary_inventory_name = d.subinventory_code
AND s.organization_id = p_organization_id
AND d.locator_id = b.inventory_location_id(+)
AND b.organization_id (+) = p_organization_id
ORDER BY s.secondary_inventory_name;
SELECT DISTINCT msi.secondary_inventory_name
, msi.description
, NVL(msi.locator_type, 1)
FROM mtl_secondary_inventories msi,
mtl_reservations mr
WHERE mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_item_id
AND NVL(mr.lot_number,0) = NVL(NVL(p_lot_number, mr.lot_number),0)
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = p_batch_id
AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
AND subinventory_code LIKE (p_subinventory_code)
AND NVL(mr.locator_id, -1) = NVL(NVL(p_locator_id, mr.locator_id), -1)
AND subinventory_code = msi.secondary_inventory_name
AND msi.organization_id = p_organization_id;
SELECT DISTINCT msi.secondary_inventory_name
, msi.description
, NVL(msi.locator_type, 1)
, mr.revision
FROM mtl_secondary_inventories msi,
mtl_reservations mr
WHERE mr.subinventory_code = msi.secondary_inventory_name
AND mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_item_id
AND demand_source_header_id = p_batch_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_line_id = p_material_detail_id
AND subinventory_code LIKE (p_subinventory_code)
AND msi.organization_id = p_organization_id;
SELECT t.subinventory_code,
msi.description,
NVL(msi.locator_type, 1),
t.atr
FROM mtl_secondary_inventories msi,
mtl_rsv_quantities_temp t
WHERE node_level = 4
AND t.organization_id = p_organization_id
AND t.inventory_item_id = p_item_id
AND nvl(t.revision,1) = nvl(p_revision, 1)
AND nvl(t.lot_number,0) = nvl(p_lot_number, 0)
AND t.subinventory_code LIKE (p_subinv_code)
AND t.subinventory_code = msi.secondary_inventory_name
AND msi.organization_id = p_organization_id
AND msi.quantity_tracked = 1
AND msi.reservable_type=1
ORDER BY t.subinventory_code, msi.description;
sqlstmt := ' SELECT a.concatenated_segments, '
||' a.description, '
||' 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, trunc(SYSDATE+1)) > trunc(SYSDATE) '
||' AND a.concatenated_segments LIKE (:p_concatenated_segments ) '
||' ORDER BY 1 ';
sqlstmt := ' SELECT concatenated_segments, '
||' description, '
||' inventory_location_id '
||' FROM wms_item_locations_kfv '
||' WHERE organization_id = :p_organization_id '
||' AND subinventory_code = :p_subinventory_code '
||' AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) '
||' AND concatenated_segments LIKE (:p_concatenated_segments) '
||' ORDER BY 1 ';
SELECT subinv_loc_ind
FROM gme_parameters
WHERE organization_id = p_organization_id;
SELECT DISTINCT loc.concatenated_segments locator, loc.description,
onh.locator_id
FROM wms_item_locations_kfv loc, mtl_onhand_locator_v onh
WHERE loc.organization_id = onh.organization_id
AND loc.inventory_location_id = onh.locator_id
AND NVL(loc.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND loc.organization_id = p_organization_id
AND onh.inventory_item_id = p_inventory_item_id
AND (p_revision IS NULL OR onh.revision = p_revision)
AND onh.subinventory_code = p_subinventory_code
AND concatenated_segments LIKE (p_locator)
AND onh.total_qoh > 0 ;
SELECT a.concatenated_segments,
a.description,
a.inventory_location_id
FROM wms_item_locations_kfv a,
mtl_reservations mr
WHERE mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_item_id
AND NVL(mr.lot_number,0) = NVL(NVL(p_lot_number, mr.lot_number),0)
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
AND mr.subinventory_code = p_subinventory_code
AND a.concatenated_segments LIKE (p_locator)
AND mr.locator_id = a.inventory_location_id
ORDER BY 1;
* from DLR of selected material line.
*/
PROCEDURE Ing_Locator_LoV_Rsrv(
x_locators OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_batch_id IN NUMBER
, p_material_detail_id IN NUMBER
, p_item_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_locator IN VARCHAR2
) IS
BEGIN
OPEN x_Locators FOR
SELECT a.concatenated_segments,
a.description,
a.inventory_location_id
FROM wms_item_locations_kfv a,
mtl_reservations mr
WHERE mr.locator_id = a.inventory_location_id
AND mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_item_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND demand_source_line_id = p_material_detail_id
AND mr.subinventory_code = p_subinventory_code
AND a.concatenated_segments LIKE (p_locator)
ORDER BY 1;
sqlstmt := ' SELECT a.concatenated_segments '
||' ,a.description '
||' ,a.inventory_location_id '
||' ,t.lot_number '
||' ,t.dispensed_qty '
||' ,NVL(t.secondary_dispensed_qty, 0) '
||' ,dispense_uom '
||' ,dispense_id '
||' ,t.revision '
||' FROM wms_item_locations_kfv a, '
||' gme_material_dispensing_gtmp t '
||' WHERE '
||' t.subinventory_code = :p_subinv_code '
||' AND a.subinventory_code = t.subinventory_code '
||' AND a.concatenated_segments LIKE (:p_locator_code) '
||' AND a.inventory_location_id = t.locator_id ';
SELECT a.concatenated_segments,
a.description,
a.inventory_location_id,
ROUND(SUM(atr),4) atr
FROM wms_item_locations_kfv a,
mtl_rsv_quantities_temp t
WHERE ((t.node_level = 4
AND t.subinventory_code not in (select subinventory_code from
mtl_rsv_quantities_temp where node_level = 5)) OR
(t.node_level = 5))
AND t.organization_id = p_organization_id
AND t.inventory_item_id = p_item_id
AND nvl(t.revision, 1) = nvl(p_revision, 1)
AND t.subinventory_code = p_subinv_code
AND a.concatenated_segments LIKE (p_locator_code)
AND a.inventory_location_id = locator_id
AND nvl(t.lot_number,0) = nvl(p_lot_number, 0)
GROUP BY a.concatenated_segments,
a.description,
a.inventory_location_id;
SELECT DISTINCT mln.lot_number
i, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_reservations mr,
mtl_lot_numbers mln
WHERE mr.organization_id = p_organization_id
AND mr.organization_id = mln.organization_id
AND mr.inventory_item_id = p_item_id
AND mr.inventory_item_id = mln.inventory_item_id
AND mr.lot_number LIKE (p_lot_number)
AND mr.lot_number = mln.lot_number
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = p_batch_id
AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
AND subinventory_code = p_subinventory_code
AND NVL(mr.locator_id, -1) = NVL(NVL(p_locator_id, mr.locator_id), -1)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id);
SELECT DISTINCT mln.lot_number
i, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_reservations mr,
mtl_lot_numbers mln
WHERE mr.organization_id = p_organization_id
AND mr.organization_id = mln.organization_id
AND mr.inventory_item_id = p_item_id
AND mr.inventory_item_id = mln.inventory_item_id
AND mr.lot_number LIKE (p_lot_number)
AND mr.lot_number = mln.lot_number
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = p_batch_id
AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id);
SELECT l.lot_num, ABS(l.txn_qty),ABS(l.txn_sec_qty)
FROM
(SELECT m2.revision,
l2.lot_number lot_num,
SUM(l2.primary_quantity) txn_qty,
SUM(NVL(l2.secondary_transaction_quantity,0)) txn_sec_qty
FROM mtl_material_transactions m2,
mtl_transaction_lot_numbers l2
WHERE l2.transaction_id = m2.transaction_id
AND l2.lot_number LIKE (p_lot_number)
AND m2.organization_id = p_organization_id
AND m2.transaction_source_id = p_batch_id
AND m2.trx_source_line_id = p_material_detail_id
AND m2.transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND (p_revision IS NULL OR m2.revision = p_revision)
GROUP BY m2.revision, l2.lot_number) l
WHERE (p_line_type = -1 and l.txn_qty < 0) OR
(p_line_type IN (1,2) and l.txn_qty > 0);
SELECT DISTINCT mln.lot_number
, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id);
SELECT DISTINCT mln.lot_number
, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND moqd.subinventory_code = p_subinventory_code
AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1));
SELECT DISTINCT mln.lot_number
, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND NVL(mln.expiration_date,SYSDATE+1) > SYSDATE --Bug#5092198
/*Bug#8937132 including the check for the lot status allows the transaction or not*/
AND (inv_material_status_grp.is_status_applicable(
NULL
,NULL
,p_transaction_type
,NULL
,NULL
,p_organization_id
,p_item_id
,NULL
,NULL
,mln.lot_number
,NULL
,'O') = 'Y' )
AND EXISTS (SELECT '1'
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND (p_revision IS NULL OR revision = p_revision)); --Bug#5867209
SELECT DISTINCT mln.lot_number
, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND NVL(mln.expiration_date,SYSDATE+1) > SYSDATE --Bug#5092198
AND (inv_material_status_grp.is_status_applicable(
NULL
,NULL
,p_transaction_type
,NULL
,NULL
,p_organization_id
,p_item_id
,p_subinventory_code
,p_locator_id
,mln.lot_number
,NULL
,'A') = 'Y')
AND EXISTS (SELECT '1'
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND moqd.subinventory_code = p_subinventory_code
AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1)
AND (p_revision IS NULL OR revision = p_revision)); --Bug#5867209
SELECT DISTINCT mln.lot_number
, TO_CHAR(mln.expiration_date, l_date_format)
, mln.grade_code
, mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND NVl(mln.expiration_date,SYSDATE+1) > SYSDATE; --Bug#5092198
SELECT lot_number,
dispensed_qty,
NVL(secondary_dispensed_qty,0),
dispense_uom,
dispense_id,
revision
FROM
GME_MATERIAL_DISPENSING_GTMP
WHERE subinventory_code = NVL(p_subinv_code, subinventory_code)
AND NVL(locator_id,-1) = NVL(p_locator_id, -1)
AND lot_number LIKE (p_lot_number)
ORDER BY lot_number;
SELECT lot_number,
---subinventory_code,
---decode(t.node_level,4,'',5,rtrim(substr(INV_UTILITIES.get_conc_segments(organization_id, locator_id),1,255)),'') locator,
ROUND(SUM(atr),4) atr,
grade_code
---locator_id
FROM mtl_rsv_quantities_temp t
WHERE ((t.node_level = 4
AND subinventory_code not in (select subinventory_code from
mtl_rsv_quantities_temp where node_level = 5)) OR
(t.node_level = 5))
AND t.organization_id = p_organization_id
AND t.inventory_item_id = p_item_id
AND nvl(t.revision, 1) = nvl(p_revision, 1)
---AND t.revision = nvl(p_revision, t.revision)
---AND subinventory_code = NVL(p_subinv_code, subinventory_code)
---AND locator_id = NVL(p_locator_id, locator_id)
AND lot_number LIKE (p_lot_number)
GROUP BY lot_number, grade_code
ORDER BY lot_number;
* for a selected material line. Used for Use Pnd Lot field in mobile
*/
PROCEDURE PndLot_LoV(
x_lot_num_lov OUT NOCOPY t_genref
, p_org_id IN NUMBER
, p_batch_id IN NUMBER
, p_material_detail_id IN NUMBER
) IS
BEGIN
OPEN x_lot_num_lov FOR
SELECT lo.sequence,
NVL(lo.revision,' '),
NVL(m.subinventory,' '),
NVl(loc.concatenated_segments,' ') locator,
NVL(mln.parent_lot_number,' '),
lo.lot_number,
NVL(quantity, 0),
lo.pending_product_lot_id,
NVL(m.locator_id,-1)
FROM gme_material_details m,
gme_pending_product_lots lo,
wms_item_locations_kfv loc,
mtl_lot_numbers mln
WHERE m.batch_id = lo.batch_id
AND m.material_detail_id = lo.material_detail_id
AND m.locator_id = loc.inventory_location_id(+)
AND m.inventory_item_id = mln.inventory_item_id
AND lo.lot_number = mln.lot_number
AND mln.organization_id = p_org_id
AND m.batch_id = p_batch_id
AND m.material_detail_id = p_material_detail_id
ORDER BY sequence;
SELECT DISTINCT lot_number
FROM
GME_PENDING_PRODUCT_LOTS
WHERE
batch_id = p_batch_id AND
material_detail_id = NVL(p_material_detail_id, material_detail_id) AND
lot_number LIKE (p_lot_number)
ORDER BY 1;
SELECT DISTINCT lot_number, quantity, NVL(secondary_quantity,0),
pending_product_lot_id
FROM
GME_PENDING_PRODUCT_LOTS
WHERE
batch_id = p_batch_id AND
material_detail_id = NVL(p_material_detail_id, material_detail_id) AND
lot_number LIKE (p_lot_number) AND
(p_revision IS NULL OR revision = p_revision)
ORDER BY 1;
SELECT lot_number
FROM mtl_lot_numbers l, gme_material_details d
WHERE l.inventory_item_id = d.inventory_item_id
AND l.organization_id = d.organization_id
AND d.material_detail_id = p_material_detail_id
AND l.lot_number LIKE (p_lot_number)
UNION
SELECT parent_lot_number
FROM mtl_lot_numbers l, gme_material_details d
WHERE l.inventory_item_id = d.inventory_item_id
AND l.organization_id = d.organization_id
AND d.material_detail_id = p_material_detail_id
AND l.parent_lot_number LIKE (p_lot_number)
ORDER BY 1;
sqlstmt := ' SELECT DISTINCT mtluom2.uom_code, '
||' mtluom2.unit_of_measure '
||' FROM mtl_system_items_b mtlitm1, '
||' mtl_units_of_measure_tl mtluom2, '
||' mtl_uom_conversions mtlucv '
||' WHERE mtlitm1.inventory_item_id = :p_item_id '
||' AND mtlitm1.organization_id = :p_org_id '
||' AND mtluom2.uom_code = mtlucv.uom_code '
||' AND ( mtlucv.inventory_item_id = :p_item_id OR mtlucv.inventory_item_id = 0) '
||' AND mtluom2.language = USERENV('||''''||'LANG'||''''||') '
||' AND ( ( mtlitm1.allowed_units_lookup_code IN (1, 3) '
||' AND mtlucv.inventory_item_id = mtlitm1.inventory_item_id '
||' OR ( mtlucv.inventory_item_id = 0 '
||' AND mtluom2.base_uom_flag = '||''''||'Y'||''''
||' AND mtluom2.uom_class = mtlucv.uom_class '
||' AND mtlucv.uom_class IN ( '
||' SELECT mtlpri1.uom_class '
||' FROM mtl_units_of_measure mtlpri1 '
||' WHERE mtlpri1.uom_code = mtlitm1.primary_uom_code) '
||' ) '
||' OR ( mtlucv.inventory_item_id = 0 '
||' AND mtlucv.uom_code IN ( '
||' SELECT mtlucc1.to_uom_code '
||' FROM mtl_uom_class_conversions mtlucc1 '
||' WHERE mtlucc1.inventory_item_id = mtlitm1.inventory_item_id '
||' AND NVL (mtlucc1.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) '
||' ) '
||' ) '
||' OR ( mtlitm1.allowed_units_lookup_code IN (2, 3) '
||' AND mtlucv.inventory_item_id = 0 '
||' AND ( mtlucv.uom_class IN ( '
||' SELECT mtlucc.to_uom_class '
||' FROM mtl_uom_class_conversions mtlucc '
||' WHERE mtlucc.inventory_item_id = mtlitm1.inventory_item_id '
||' AND NVL (mtlucc.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) '
||' OR mtlucv.uom_class = '
||' (SELECT mtlpri.uom_class '
||' FROM mtl_units_of_measure mtlpri '
||' WHERE mtlpri.uom_code = mtlitm1.primary_uom_code) '
||' ) '
||' ) '
||' ) '
||' AND NVL (mtlucv.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE) '
||' AND NVL (mtluom2.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE) '
||' AND mtluom2.uom_code like (:p_uom_code) '
||' ORDER BY 1 ';
SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
meaning,
d.line_type
FROM gme_batch_header h,
gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
gem_lookups lkup
WHERE h.organization_id = p_organization_id
AND h.batch_id = p_batch_id
AND h.batch_id = d.batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND h.batch_type = 0
AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lkup.lookup_code = d.line_type
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND d.line_type LIKE (l_line_type)
ORDER BY 1;
SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
meaning,
d.line_type
FROM gme_batch_header h,
gme_material_details d,
gem_lookups lkup
WHERE h.organization_id = p_organization_id
AND h.batch_id = p_batch_id
AND h.batch_id = d.batch_id
AND h.batch_type = 0
AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lkup.lookup_code = d.line_type
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND d.line_type LIKE (l_line_type)
ORDER BY 1;
SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
meaning,
d.line_type
FROM gme_batch_header h,
gme_material_details d,
gme_batch_step_items si,
gme_batch_steps s,
gem_lookups lkup
WHERE h.organization_id = p_organization_id
AND h.batch_id = p_batch_id
AND h.batch_id = d.batch_id
AND d.batch_id = si.batch_id
AND si.material_detail_id = d.material_detail_id
AND s.batchstep_no = p_step_no
AND si.batchstep_id = s.batchstep_id
AND h.batch_type = 0
AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lkup.lookup_code = d.line_type
AND d.line_type IN (1,2)
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND d.line_type LIKE (l_line_type)
ORDER BY 1;
SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
meaning,
d.line_type
FROM gme_batch_header h,
gme_material_details d,
gem_lookups lkup
WHERE h.organization_id = p_organization_id
AND h.batch_id = p_batch_id
AND h.batch_id = d.batch_id
AND h.batch_type = 0
AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lkup.lookup_code = d.line_type
AND d.line_type IN (1,2)
AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
AND NVL(lkup.end_date_active, sysdate)
AND lkup.enabled_flag = 'Y'
AND d.line_type LIKE (l_line_type)
ORDER BY 1;
SELECT revision
FROM mtl_item_revisions
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND implementation_date IS NOT NULL
AND revision like p_revision
ORDER BY revision;
SELECT DISTINCT revision
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 LIKE (p_revision)
GROUP BY revision
HAVING (p_line_type = -1 AND SUM(transaction_quantity) < 0) OR
(p_line_type IN (1,2) AND SUM(transaction_quantity) > 0)--rework
ORDER BY revision;
SELECT DISTINCT revision
FROM mtl_reservations
WHERE organization_id = p_org_id
AND demand_source_header_id = p_batch_id
AND demand_source_line_id = p_material_detail_id
AND demand_source_type_id = 5
AND revision LIKE (p_revision)
ORDER BY revision;
SELECT DISTINCT revision
FROM gme_pending_product_lots
WHERE batch_id = p_batch_id
AND material_detail_id = p_material_detail_id
AND revision LIKE (p_revision)
ORDER BY revision;
SELECT i.instance_number, i.instance_id
FROM gmp_resource_instances i, cr_rsrc_dtl r
WHERE r.resource_id = i.resource_id
AND r.resources = p_resource
AND r.organization_id = p_organization_id
AND i.instance_number LIKE (p_instance);
SELECT i.instance_number, i.instance_id
FROM gmp_resource_instances i, gme_resource_txns_gtmp t
WHERE t.doc_id = p_batch_id
AND t.line_id = p_batchstep_resource_id
AND t.start_date = t.end_date
AND action_code NOT IN ('REVS', 'REVL')
AND t.resource_usage = 0
AND t.completed_ind = 1
AND t.delete_mark = 0
AND t.instance_id = i.instance_id
AND i.instance_number LIKE (p_instance);
SELECT gbs.batchstep_no, glk.meaning,gbs.batchstep_id, gmo.oprn_no, gmo.oprn_vers, gbs.plan_step_qty,
TO_NUMBER(NULL) act_step_qty, gbs.step_qty_um,
TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING),
' ' act_start_date,
TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING) plan_comlt_date
FROM gme_batch_header gbh, gme_batch_steps gbs, gmd_operations gmo, gem_lookups glk
WHERE gbh.batch_id = p_batch_id
AND gbh.batch_type = 0
AND gbh.batch_id = gbs.batch_id
AND gbs.step_status = 1
AND glk.lookup_type = 'GME_STEP_STATUS'
AND glk.lookup_code = TO_CHAR(gbs.step_status)
AND TO_CHAR(gbs.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
AND gbs.oprn_id = gmo.oprn_id
ORDER BY 1;
SELECT gbs.batchstep_no, glk.meaning,gbs.batchstep_id, gmo.oprn_no, gmo.oprn_vers, gbs.plan_step_qty,
TO_NUMBER(NULL) act_step_qty, gbs.step_qty_um,
TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING),
DECODE(gbs.actual_start_date,NULL,' ',TO_CHAR(gbs.actual_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING)) act_start_date,
TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING) plan_comlt_date
FROM gme_batch_header gbh, gme_batch_steps gbs, gmd_operations gmo, gem_lookups glk
WHERE gbh.batch_id = p_batch_id
AND gbh.batch_type = 0
AND gbh.batch_id = gbs.batch_id
AND gbs.step_status IN (1,2)
AND glk.lookup_type = 'GME_STEP_STATUS'
AND glk.lookup_code = TO_CHAR(gbs.step_status)
AND TO_CHAR(gbs.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
AND gbs.oprn_id = gmo.oprn_id
ORDER BY 1;
SELECT
r.resources
, r.batchstep_resource_id
, DECODE(i.instance_number, null, ' ')
, NVL (i.instance_id,-1)
, TO_CHAR(r.actual_start_date,p_date_format)
, TO_CHAR(r.actual_cmplt_date,p_date_format)
, TO_CHAR(DECODE(s.step_status, 2, r.plan_start_date, 3, r.actual_start_date), p_date_format)
, u.user_name
, ROUND(DECODE(s.step_status, 2, r.plan_rsrc_qty, 3, r.actual_rsrc_qty),2)
, r.resource_qty_um
, ROUND(DECODE(s.step_status, 2, r.plan_rsrc_usage, 3, r.actual_rsrc_usage),2)
, r.usage_um
FROM gme_batch_header h,
gme_batch_steps s,
gme_batch_step_activities a,
gme_batch_step_resources r,
gmp_resource_instances i,
fnd_user u
WHERE h.batch_id = p_batch_id
AND h.batch_id = s.batch_id
AND s.batchstep_no = p_step_no
AND s.batchstep_id = a.batchstep_id
AND h.batch_id = a.batch_id
AND r.batch_id = h.batch_id
AND r.batchstep_id = s.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND r.batchstep_activity_id = p_activity_id
AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
AND r.batchstep_resource_id = i.resource_id(+)
AND u.user_id = r.last_updated_by
ORDER BY r.resources;
SELECT
r.resources
, r.batchstep_resource_id
, DECODE(t.instance_id, NULL, ' ', i.instance_number)
, NVL (t.instance_id,-1)
, TO_CHAR(r.actual_start_date,p_date_format)
, TO_CHAR(r.actual_cmplt_date,p_date_format)
, TO_CHAR(t.start_date, p_date_format)
, u.user_name
, ROUND(DECODE(s.step_status, 2, r.plan_rsrc_qty, 3, r.actual_rsrc_qty),2)
, r.resource_qty_um
, ROUND(DECODE(s.step_status, 2, r.plan_rsrc_usage, 3, r.actual_rsrc_usage),2)
, r.usage_um
FROM gme_batch_steps s,
gme_batch_step_activities a,
gme_batch_step_resources r,
gmp_resource_instances i,
gme_resource_txns_gtmp t,
gme_resource_txns rt,
fnd_user u
WHERE
t.doc_id = p_batch_id
AND t.line_id = r.batchstep_resource_id
AND t.start_date = t.end_date
AND t.resource_usage = 0
AND t.completed_ind = 1
AND t.action_code NOT IN ('REVS', 'REVL')
AND t.delete_mark = 0
AND t.instance_id = i.instance_id(+)
AND s.batchstep_no = p_step_no
AND s.batchstep_id = a.batchstep_id
AND a.batch_id = t.doc_id
AND r.batch_id = a.batch_id
AND r.batchstep_id = s.batchstep_id
AND r.batchstep_activity_id = a.batchstep_activity_id
AND r.batchstep_activity_id = p_activity_id
AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
AND rt.poc_trans_id = t.poc_trans_id
AND u.user_id = rt.last_updated_by
ORDER BY r.resources;