This is script to clear Open move order lines which are not linked to Delivery details, clean Orphan transactions in Mtl_material_transactions_temp and remove reservations link to Mtl_material_transactions_temp if reservations do not exist.
Below is the script:
REM $Header: INVCLRMO.sql 115.5 2002/03/09 16:23:25 kadavi noship $
REM
REM (c) Copyright Oracle Corporation 2001
REM All Rights Reserved
REM
REM HISTORY
REM This is script to clear Open move order lines which
REM are not linked to Delivery details ,clean Orphan suggestions
REM in Mtl_material_transactions_temp and remove reservations
REM link to Mtl_material_transactions_temp if reservations are
REM not existing.
REM
REM Also this script creates and drop temp tables so Ct
REM need to manually run this script.
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single phase=dat \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER SQLERROR CONTINUE ROLLBACK; –when an error occurred during the sql executing, rollback
prompt dropping tables --showing “droping tables”
DROP TABLE mtl_mmtt_backup; –clean/drop the previous backup table
DROP TABLE mtl_mtrl_backup;
DROP TABLE mtl_msnt_backup;
DROP TABLE mtl_mtlt_backup;
prompt create table for MMTT backup –start to create backup tables
CREATE TABLE mtl_mmtt_backup AS
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mmtt.move_order_line_id IS NOT NULL
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status = 7 –preapproved
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 3 –pick-wave
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status = ‘S’ –released to warehouse
)
)
/
–select to backup the lines whose linked move order line status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt select allocation records for closed move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status = 5
)
/ –select to backup the lines whose move order line status is closed
prompt select allocation records with missing move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt
WHERE move_order_line_id IS NOT NULL
AND NOT EXISTS
(SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = mmtt.move_order_line_id
)
)
/ –select to backup the lines who has a move order link, but corresponding move order is not existing now.
prompt create backup table for move order lines
CREATE TABLE mtl_mtrl_backup AS
(SELECT mtrl.*
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status = 7
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 3
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status = ‘S’
)
)
/
–select to backup the move order lines whose status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt create backup table for serial number allocations
CREATE TABLE mtl_msnt_backup AS
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
)
)
/
–backup all of the series records under the backuped mtl_material_transactions_temp
prompt create backup table for lot number allocations
CREATE TABLE mtl_mtlt_backup AS
(SELECT mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id IN
(SELECT transaction_temp_Id FROM mtl_mmtt_backup
)
)
/
–backup all of the lots records under the backuped mtl_material_transactions_temp
prompt select serial number allocations for lot controlled items
INSERT
INTO mtl_msnt_backup
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT serial_transaction_temp_id FROM mtl_mtlt_backup
)
)
/
–backup all of the serials records for the lot and serial control items
prompt delete serial number allocations
DELETE
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_msnt_backup
) /
–delete all of the serials records which has been backuped
prompt delete lot number allocations
DELETE
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mtlt_backup
) /
–delete all of the lot records which has been backuped
prompt delete allocations
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
) /
–delete all of the allocations (records in mmtt table)which has been backuped
prompt close move order lines
UPDATE mtl_txn_request_lines
SET quantity = NVL(quantity_detailed, 0) ,
line_status = 5
WHERE line_id IN
(SELECT line_id FROM mtl_mtrl_backup
) /
–close all of the move order which has been backuped in mtl_mtrl_backup
prompt update transaction source on the move order line
UPDATE mtl_txn_request_lines mtrl
SET mtrl.txn_source_line_id =
(SELECT DISTINCT(source_line_id)
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status = ‘S’
)
WHERE mtrl.line_status = 7
AND EXISTS
(SELECT delivery_detail_id
FROM wsh_delivery_details wdd
WHERE move_order_line_Id = mtrl.line_Id
AND wdd.source_line_id <> mtrl.txn_source_line_id
AND wdd.source_line_id > 0
AND wdd.released_status = ‘S’
) /
–The move order has linked with delivery details and delivery details has been released to warehouse
–In the delivery details, there’s a source line id but the source line id is different with the one in move order
–And the move order is in the status of pre-approved.
–If above is true, change the source line id in move order line to the one in delivery details
prompt update transaction source on the allocation
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.trx_source_line_id =
(SELECT txn_source_line_id
FROM mtl_txn_request_lines
WHERE line_id = mmtt.move_order_line_id
)
WHERE mmtt.transaction_type_id IN (52, 53)
AND mmtt.move_order_line_id IS NOT NULL
AND EXISTS
(SELECT line_id
FROM mtl_txn_request_lines
WHERE line_status = 7
AND line_id = mmtt.move_order_line_id
AND txn_source_line_id <> mmtt.trx_source_line_id
) /
–pending transaction is from internal order pick and sales order pick
–pending transaction is linked with move order, move order is in Pre-approved
–source line id in pending transaction is different with the one in move order
–If above is true, change the source line id in pending transaction to the one in move order
prompt update allocations for missing reservations
UPDATE mtl_material_transactions_temp mmtt
SET reservation_id = NULL
WHERE mmtt.reservation_id IS NOT NULL
AND NOT EXISTS
(SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE reservation_id = mmtt.reservation_id –there’s a reservation id in pending transaction,
)
/
COMMIT / EXIT; –but there’s no in reservation record; If above is true, change it to null.
/
Dear Niranjan,
ReplyDeletePlease let me know if this is useful to you. Get it checked from Jegan before implementing.
Regards,
Sandip