[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* This Procedure is used to insert values into table mtl_replenish_lines.
* @param x_return_status Return Status
* @param x_msg_count Message Count
* @param x_msg_data Message Data
* @param p_organization_id Organization Id
* @param p_replenish_header_id Replenishment Count Header Id
* @param p_locator_id Locator Id
* @param p_item_id Item ID
* @param p_count_type_code Count Type Code
* @param p_count_quantity Count Quantity
* @param p_count_uom_code Count Uom Code
* @param p_primary_uom_code Primary Uom Code
* @param p_count_secondary_uom_code Secondary Uom Code
* @param p_count_secondary_quantity Secondary Quantity
**/
PROCEDURE insert_row(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_replenish_header_id IN NUMBER
, p_locator_id IN NUMBER
, p_item_id IN NUMBER
, p_count_type_code IN NUMBER
, p_count_quantity IN NUMBER
, p_count_uom_code IN VARCHAR2
, p_primary_uom_code IN VARCHAR2
, p_count_secondary_uom_code IN VARCHAR2 -- INVCONV, NSRIVAST
, p_count_secondary_quantity IN NUMBER -- INVCONV, NSRIVAST
) IS
l_proc CONSTANT VARCHAR2(30) := 'INSERT_ROW';
INSERT INTO mtl_replenish_lines
(
replenishment_line_id
, replenishment_header_id
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, locator_id
, inventory_item_id
, count_type_code
, count_quantity
, count_uom_code
, supply_quantity
, source_type
, source_organization_id
, source_subinventory
, reorder_quantity
, expense_account
, encumbrance_account
, REFERENCE
, error_flag
, primary_uom_count_quantity
, primary_uom_code
-- INCVONV, NSRIVAST
, secondary_uom_code
, secondary_uom_count_quantity
-- INCVONV, NSRIVAST
)
VALUES (
mtl_replenish_lines_s.NEXTVAL
, p_replenish_header_id
, p_organization_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, p_locator_id
, p_item_id
, p_count_type_code
, p_count_quantity
, p_count_uom_code
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, fnd_message.get
, NULL
, inv_convert.inv_um_convert(p_item_id, 6, p_count_quantity, p_count_uom_code, p_primary_uom_code, NULL, NULL)
, p_primary_uom_code
-- INCVONV, NSRIVAST
, p_count_secondary_uom_code
, p_count_secondary_quantity
-- INCVONV, NSRIVAST
);
END insert_row;
* This Procedure is used to update table mtl_replenish_lines.
* @param x_return_status Return Status
* @param x_msg_count Message Count
* @param x_msg_data Message Data
* @param p_item_id Item ID
* @param p_replenish_header_id Replenishment Count Header Id
* @param p_replenish_line_id Replenishment Count Line Id
* @param p_count_quantity Count Quantity
* @param p_primary_uom_code Primary Uom Code
* @param p_count_secondary_quantity Secondary Quantity
**/
PROCEDURE update_row(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_item_id IN NUMBER
, p_replenish_header_id IN NUMBER
, p_replenish_line_id IN NUMBER
, p_count_quantity IN NUMBER
, p_count_uom_code IN VARCHAR2
, p_count_secondary_quantity IN NUMBER -- INVCONV, NSRIVAST
) IS
l_proc CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
UPDATE mtl_replenish_lines
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, last_update_login = fnd_global.login_id
, count_quantity = p_count_quantity
, count_uom_code = p_count_uom_code
, primary_uom_count_quantity =
inv_convert.inv_um_convert(p_item_id, 6, p_count_quantity, p_count_uom_code, primary_uom_code, NULL, NULL)
, secondary_uom_count_quantity = p_count_secondary_quantity -- INVCONV, NSRIVAST
WHERE replenishment_header_id = p_replenish_header_id
AND replenishment_line_id = p_replenish_line_id;
END update_row;
SELECT msiv.inventory_item_id item_id
, msiv.concatenated_segments item
, mil.inventory_location_id locator_id
, inv_project.get_locsegs(mil.inventory_location_id, mil.organization_id) LOCATOR
, mrl.count_type_code count_type_code
, ml.meaning count_type
, mrl.replenishment_line_id replenishment_line_id
, msiv.description item_description
, msiv.primary_uom_code primary_uom_code
FROM mtl_item_locations mil, mtl_system_items_vl msiv, mtl_secondary_locators msl, mtl_replenish_lines mrl, mfg_lookups ml
WHERE msl.inventory_item_id = msiv.inventory_item_id
AND msl.organization_id = msiv.organization_id
AND msl.secondary_locator = mil.inventory_location_id
AND msl.organization_id = mil.organization_id
AND msl.organization_id = p_organization_id
AND msl.subinventory_code = p_subinventory_code
AND mrl.replenishment_header_id(+) = p_replenish_header_id
AND ml.lookup_type(+) = 'MTL_COUNT_TYPES'
AND ml.lookup_code(+) = mrl.count_type_code
AND mrl.organization_id(+) = msl.organization_id
AND mrl.inventory_item_id(+) = msl.inventory_item_id
-- AND mil.inventory_location_id = NVL(mil.physical_location_id, mil.inventory_location_id) -- Commented for Bug 6798138
AND mrl.locator_id(+) = msl.secondary_locator
AND(
mrl.count_type_code IS NULL
OR mrl.count_type_code = 2
OR(mrl.count_type_code = 1
AND p_quantity_tracked = 2
AND msl.maximum_quantity IS NOT NULL)
)
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
ORDER BY DECODE(p_use_loc_pick_seq, 'YES', mil.picking_order, replenishment_line_id), item;
SELECT mis.inventory_item_id item_id
, msiv.concatenated_segments item
, TO_NUMBER(NULL) locator_id
, NULL LOCATOR
, mrl.count_type_code count_type_code
, ml.meaning count_type
, mrl.replenishment_line_id replenishment_line_id
, msiv.description item_description
, msiv.primary_uom_code primary_uom_code
FROM mtl_system_items_vl msiv, mtl_item_sub_inventories mis, mtl_replenish_lines mrl, mfg_lookups ml
WHERE mis.inventory_item_id = msiv.inventory_item_id
AND mis.organization_id = msiv.organization_id
AND mis.organization_id = p_organization_id
AND mis.secondary_inventory = p_subinventory_code
AND mrl.replenishment_header_id(+) = p_replenish_header_id
AND ml.lookup_type(+) = 'MTL_COUNT_TYPES'
AND ml.lookup_code(+) = mrl.count_type_code
AND mrl.organization_id(+) = mis.organization_id
AND mrl.inventory_item_id(+) = mis.inventory_item_id
AND(
mrl.count_type_code IS NULL
OR mrl.count_type_code = 2
OR(mrl.count_type_code = 1
AND p_quantity_tracked = 2
AND mis.inventory_planning_code = 2)
)
AND mrl.count_quantity IS NULL
AND mrl.error_flag IS NULL
ORDER BY replenishment_line_id;
SELECT replenishment_count_name
INTO x_replenish_count_name
FROM mtl_replenish_headers mrh
WHERE mrh.organization_id = p_organization_id
AND mrh.subinventory_code = p_subinventory_code
AND mrh.process_status = 1
AND mrh.count_mode = 1
AND(
(
p_planning_level = 1
AND NOT EXISTS(SELECT 1
FROM mtl_replenish_lines mrl
WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
AND mrl.locator_id IS NULL)
)
OR(
p_planning_level = 2
AND NOT EXISTS(SELECT 1
FROM mtl_replenish_lines mrl
WHERE mrl.replenishment_header_id = mrh.replenishment_header_id
AND mrl.locator_id IS NOT NULL)
)
);
SELECT 1
INTO l_record_exists
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_replenish_lines
WHERE replenishment_header_id = p_replenish_header_id
AND count_quantity IS NULL
AND error_flag IS NULL);
SELECT 1
INTO l_error_record_exists
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_replenish_lines mrl
WHERE replenishment_header_id = p_replenish_header_id
AND(
(count_type_code = 1
AND p_quantity_tracked = 1)
OR(
p_planning_level = 1
AND(
locator_id IS NULL
OR count_type_code = 3
OR NOT EXISTS(
SELECT maximum_quantity
FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
WHERE msl.inventory_item_id = mrl.inventory_item_id
AND msl.secondary_locator = mrl.locator_id
AND msl.organization_id = mrl.organization_id
AND msi.inventory_item_id = msl.inventory_item_id
AND msi.organization_id = msl.organization_id
AND mil.inventory_location_id = msl.secondary_locator
AND mil.organization_id = msl.organization_id
AND msi.inventory_item_flag = 'Y'
AND msi.stock_enabled_flag = 'Y'
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE))
OR(
EXISTS(
SELECT maximum_quantity
FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
WHERE msl.inventory_item_id = mrl.inventory_item_id
AND msl.secondary_locator = mrl.locator_id
AND msl.organization_id = mrl.organization_id
AND msi.inventory_item_id = msl.inventory_item_id
AND msi.organization_id = msl.organization_id
AND mil.inventory_location_id = msl.secondary_locator
AND mil.organization_id = msl.organization_id
AND msi.inventory_item_flag = 'Y'
AND msi.stock_enabled_flag = 'Y'
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND maximum_quantity IS NULL)
AND count_type_code IN(1, 4)
)
)
)
OR(
p_planning_level <> 1
AND(
locator_id IS NOT NULL
OR count_type_code = 4
OR(
NOT EXISTS(
SELECT mis.inventory_planning_code
FROM mtl_item_sub_inventories mis, mtl_system_items msi
WHERE mis.inventory_item_id = mrl.inventory_item_id
AND mis.secondary_inventory = p_subinventory_code
AND mis.organization_id = mrl.organization_id
AND msi.inventory_item_id = mis.inventory_item_id
AND msi.organization_id = mis.organization_id
AND msi.inventory_item_flag = 'Y'
AND msi.stock_enabled_flag = 'Y'
AND mis.inventory_planning_code IN(2, 6))
)
OR(
EXISTS(
SELECT mis.inventory_planning_code
FROM mtl_item_sub_inventories mis, mtl_system_items msi
WHERE mis.inventory_item_id = mrl.inventory_item_id
AND mis.secondary_inventory = p_subinventory_code
AND mis.organization_id = mrl.organization_id
AND msi.inventory_item_id = mis.inventory_item_id
AND msi.organization_id = mis.organization_id
AND msi.inventory_item_flag = 'Y'
AND msi.stock_enabled_flag = 'Y'
AND mis.inventory_planning_code = 6)
AND count_type_code IN(1, 3)
)
)
)
));
SELECT 2
INTO l_count_valid
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_replenish_lines
WHERE replenishment_header_id = p_replenish_header_id
AND((p_planning_level = 1
AND locator_id IS NULL)
OR(p_planning_level = 2
AND locator_id IS NOT NULL)));
UPDATE mtl_replenish_headers
SET process_status = 2
WHERE replenishment_header_id = p_replenish_header_id;