[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- Basically to eliminate already processed records from getting selected again and again into
c_move_order_lines CURSOR the request_id has be introduced in this procedure.
- Once the number of records processed becomes equal to the value specified in 'INV: Pick Slip Batch Size'
profile (10) then a COMMIT is issued and the c_move_order_lines is CLOSED and OPENED once again
(to eliminate the 'ORA-01002: fetch out of sequence' Error). When the c_move_order_lines CURSOR
is OPENED for the second time then the CURSOR MAY fetch the already processed records. But the
idea is to process only unprocessed records and after CLOSING the CURSOR there was no pointer to
find out that which all records have been already processed. So to identify the processed records
we are updating the request_id column in mtl_txn_request_lines along with quantity_detailed. And
the same (request_id) column is now being used to identify unprocessed records.
*****************************************************************************/
l_api_name VARCHAR2(30);
SELECT mtrh.header_id
, mtrh.move_order_type
, mtrl.line_id
, mtrl.inventory_item_id
, mtrl.to_account_id
, mtrl.project_id
, mtrl.task_id
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrl.line_status IN (3, 7)
AND mtrh.organization_id = p_org_id
AND (p_move_order_from IS NULL OR mtrh.request_number >= p_move_order_from)
AND (p_move_order_to IS NULL OR mtrh.request_number <= p_move_order_to)
AND ( (p_move_order_type = 99 AND mtrh.move_order_type IN (1,2,3,5))
OR (p_move_order_type = 1 AND mtrh.move_order_type = 3)
OR (p_move_order_type = 2 AND mtrh.move_order_type = 5)
OR (p_move_order_type = 4 AND mtrh.move_order_type IN (1,2))
)
AND mtrl.header_id = mtrh.header_id
AND mtrl.organization_id = p_org_id
AND mtrl.quantity > NVL(mtrl.quantity_detailed, 0)
AND (p_requested_by IS NULL OR mtrl.created_by = p_requested_by)
AND (p_source_subinv IS NULL OR mtrl.from_subinventory_code = p_source_subinv)
AND (p_source_locator_id IS NULL OR mtrl.from_locator_id = p_source_locator_id)
AND (p_dest_subinv IS NULL OR mtrl.to_subinventory_code = p_dest_subinv)
AND (p_dest_locator_id IS NULL OR mtrl.to_locator_id = p_dest_locator_id)
AND ((p_sales_order_from IS NULL AND p_sales_order_to IS NULL AND p_customer_id IS NULL AND p_freight_code IS NULL)
OR EXISTS (SELECT 1
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = p_org_id
AND wdd.move_order_line_id = mtrl.line_id
AND (p_sales_order_from IS NULL OR wdd.source_header_number >= p_sales_order_from)
AND (p_sales_order_to IS NULL OR wdd.source_header_number <= p_sales_order_to)
AND (p_customer_id IS NULL OR wdd.customer_id = p_customer_id)
AND (p_freight_code IS NULL OR wdd.ship_method_code = p_freight_code))
)
ORDER BY mtrl.header_id; Commented for bug 3772012 */
SELECT l_header_id header_id
, l_move_order_type move_order_type
, mtrl.line_id
, mtrl.inventory_item_id
, mtrl.to_account_id
, mtrl.project_id
, mtrl.task_id
, mtrl.quantity_detailed
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_status IN (3, 7)
AND mtrl.organization_id = p_org_id
AND mtrl.header_id = l_header_id
AND mtrl.quantity > NVL(mtrl.quantity_detailed, 0)
AND (p_requested_by IS NULL OR mtrl.created_by = p_requested_by)
AND (p_source_subinv IS NULL OR mtrl.from_subinventory_code = p_source_subinv)
AND (p_source_locator_id IS NULL OR mtrl.from_locator_id = p_source_locator_id)
AND (p_dest_subinv IS NULL OR mtrl.to_subinventory_code = p_dest_subinv)
AND (p_dest_locator_id IS NULL OR mtrl.to_locator_id = p_dest_locator_id)
AND (p_date_reqd_from IS NULL OR mtrl.date_required >= p_date_reqd_from) /* Added to fix Bug# 4078103 */
--bug 6850379
AND (p_date_reqd_to IS NULL OR (mtrl.date_required <= trunc(p_date_reqd_to+1)-0.00001)) /* Added to fix Bug# 4078103 */
--bug 6850379
AND ((p_sales_order_from IS NULL AND p_sales_order_to IS NULL AND p_customer_id IS NULL AND p_freight_code IS NULL)
OR EXISTS (SELECT 1
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = p_org_id
AND wdd.move_order_line_id = mtrl.line_id
AND (p_sales_order_from IS NULL OR wdd.source_header_number >= p_sales_order_from)
AND (p_sales_order_to IS NULL OR wdd.source_header_number <= p_sales_order_to)
AND (p_customer_id IS NULL OR wdd.customer_id = p_customer_id)
AND (p_freight_code IS NULL OR wdd.ship_method_code = p_freight_code))
)
AND NVL(mtrl.request_id, 0) < p_request_id
/* Added to fix Bug# 4003379; If the record does not have any request_id or
processed by this request so select that record for processing. */
AND rownum < l_profile_value +1
/* Added to fix Bug# 4003379; ROWNUM is introduced to fetch and lock only
FOR UPDATE OF mtrl.quantity_detailed NOWAIT; -- Added 3772012
SELECT mtrh.header_id
, mtrh.move_order_type
FROM mtl_txn_request_headers mtrh
WHERE mtrh.organization_id = p_org_id
AND (p_move_order_from IS NULL OR mtrh.request_number >= p_move_order_from)
AND (p_move_order_to IS NULL OR mtrh.request_number <= p_move_order_to)
AND ( (p_move_order_type = 99 AND mtrh.move_order_type IN (1,2,3,5))
OR (p_move_order_type = 1 AND mtrh.move_order_type = 3)
OR (p_move_order_type = 2 AND mtrh.move_order_type = 5)
OR (p_move_order_type = 3 AND mtrh.move_order_type = 5) --Bug #4700988 MFG Pick
OR (p_move_order_type = 4 AND mtrh.move_order_type IN (1,2))
);
SELECT transaction_temp_id
, subinventory_code
, locator_id
, transfer_subinventory
, transfer_to_location
, revision
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_mo_line_id
AND pick_slip_number IS NULL;
UPDATE mtl_txn_request_headers
SET grouping_rule_id = p_pick_slip_group_rule_id
WHERE header_id = v_mo_line_rec.header_id;
SELECT decode(serial_number_control_code, 1, 'F', 'T') INTO l_serial_flag
FROM mtl_system_items
WHERE inventory_item_id = v_mo_line_rec.inventory_item_id
AND organization_id = p_org_id;
SELECT mtl_material_transactions_s.NEXTVAL INTO l_txn_header_id FROM DUAL;
UPDATE mtl_txn_request_lines
SET quantity_detailed = (NVL(quantity_delivered, 0) + l_detailed_qty),
secondary_quantity_detailed = decode(l_secondary_detailed_qty, 0, NULL, l_secondary_detailed_qty), --INVCONV
request_id = p_request_id /* Added the updation of request_id to fix Bug# 4003379 */
WHERE line_id = v_mo_line_rec.line_id
AND organization_id = p_org_id;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = l_pick_slip_no
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET distribution_account_id = v_mo_line_rec.to_account_id
WHERE move_order_line_id = v_mo_line_rec.line_id;