Search This Blog

Saturday, December 4, 2010

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.

/

12 comments:

  1. Dear Niranjan,

    Please let me know if this is useful to you. Get it checked from Jegan before implementing.

    Regards,
    Sandip

    ReplyDelete
  2. Useful information. Fortunate me I found your site by accident, and I'm stunned why this twist of fate didn't
    took place in advance! I bookmarked it.
    Also visit my website Satellite Television

    ReplyDelete
  3. I was recommended this blog by means of my cousin.
    I'm not positive whether or not this submit is written through him as nobody else understand such unique approximately my problem. You're amazing!
    Thanks!

    Check out my homepage :: BMW Z4 Windscreen

    ReplyDelete
  4. I used to be suggested this website by my cousin. I'm now not certain whether this submit is written by way of him as nobody else recognize such designated approximately my problem. You are amazing! Thank you!

    Also visit my blog post diet that works

    ReplyDelete
  5. At this time it seems like Movable Type is the best blogging platform available right now.
    (from what I've read) Is that what you are using on your blog?

    Feel free to visit my site: beautiful

    ReplyDelete
  6. I like it when people come together and share thoughts.
    Great site, stick with it!

    Feel free to surf to my web site ... Consultant Appointments Manchester

    ReplyDelete
  7. Right now it looks like Expression Engine is the
    preferred blogging platform out there right now. (from what I've read) Is that what you are using on your blog?

    My web site ... healthy diet Plans

    ReplyDelete
  8. I like reading a post that will make men and women think.

    Also, thank you for allowing me to comment!

    Also visit my web page www.wildpartygirls.org

    ReplyDelete
  9. Someone essentially lend a hand to make critically posts I would state.
    This is the very first time I frequented your web
    page and thus far? I surprised with the research you made to make
    this actual submit incredible. Fantastic job!



    Feel free to visit my web page - Where can I buy Enduros

    ReplyDelete
  10. I'm not sure exactly why but this web site is loading very slow for me. Is anyone else having this problem or is it a problem on my end? I'll check
    back later and see if the problem still exists.

    Feel free to surf to my web page; xxx-fuck.net

    ReplyDelete
  11. Calfre is the local search engine where you can find different training institutes around Hyderabad that provide training for oracle Fusion hcm through online.

    Oracle Fusion Training Institute

    ReplyDelete
  12. Oracle fusion HCM Training from ERPTREE gives you the best results to learn your dream course and maintains sufficient knowledge on oracle. It provides training by self-paced videos which are very helpful for the users to watch at any time according to their schedule. It is globally accepted and having many users undergoing training every day.


    Oracle fusion HCM Online Training

    Oracle Fusion HCM Training

    ReplyDelete