Remove Orphan Transactions/Move Orders

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.

/