Procedure to create Trip in Oracle WMS.
CREATE OR REPLACE PROCEDURE xx_om_autocreate_trip (
p_master_trip IN VARCHAR2,
p_trip_no OUT VARCHAR2,
p_trip_name OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
IS
lv_return_status VARCHAR2 (1);
ln_msg_count NUMBER;
lv_msg_data VARCHAR2 (400);
ln_trip_id VARCHAR2 (20);
lv_trip_name VARCHAR2 (30);
l_line_tbl wsh_util_core.id_tab_type;
l_del_rows_tbl wsh_util_core.id_tab_type;
ln_line_no NUMBER := 0;
l_msg_index_out NUMBER;
CURSOR cur_trip
IS
SELECT DISTINCT wdv.delivery_detail_id
FROM oe_order_headers_v oeh,
oe_order_lines_v ool,
xx_om_mtrip_mastertrip_v xxo,
wsh_deliverables_v wdv,
xx_om_mtrip_lines_v xxl
WHERE oeh.header_id = ool.header_id
AND oeh.ship_from = xxo.warehouse_code
AND oeh.shipping_method_code = xxo.shipping_method_code
AND TRIM (ool.schedule_ship_date)
BETWEEN NVL (xxo.ss_date_from,
TRIM (ool.schedule_ship_date)
)
AND NVL (xxo.ss_date_to,
TRIM (ool.schedule_ship_date)
)
AND UPPER (ool.flow_status_code) = 'AWAITING_SHIPPING'
AND wdv.source_header_id = ool.header_id
AND wdv.source_line_id = ool.line_id
AND xxo.master_trip_no = p_master_trip
AND TO_CHAR (xxl.sales_order) = TO_CHAR (oeh.order_number)
AND NOT EXISTS (
SELECT sales_order
FROM xx_om_mtrip_lines_v xxo
WHERE xxo.sales_order = oeh.order_number
AND xxo.add_to_trip = 'Y')
AND NOT EXISTS (
SELECT 1
FROM oe_holds_history_v ohh
WHERE ohh.header_id = oeh.header_id
AND UPPER (hold_name) <> 'HOLD ORDER - WAREHOUSE'
AND released_flag = 'N')
ORDER BY 1;
BEGIN
FOR i IN cur_trip
LOOP
ln_line_no := NVL (ln_line_no, 0) + 1;
l_line_tbl (ln_line_no) := i.delivery_detail_id;
DBMS_OUTPUT.put_line (l_line_tbl (ln_line_no));
END LOOP;
wsh_delivery_details_pub.autocreate_del_trip
(p_api_version_number => 1.0 ---IN NUMBER
,
p_init_msg_list => fnd_api.g_false
---IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
p_commit => fnd_api.g_false
--IN VARCHAR2 DEFAULT Fnd_Api.G_FALSE
,
x_return_status => lv_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lv_msg_data,
p_line_rows => l_line_tbl,
x_del_rows => l_del_rows_tbl,
x_trip_id => ln_trip_id,
x_trip_name => lv_trip_name
);
COMMIT;
IF NVL (ln_msg_count, 0) > 0
THEN
FOR j IN 1 .. ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F',
p_data => lv_msg_data,
p_msg_index_out => l_msg_index_out
);
END LOOP;
END IF;
IF NVL (lv_return_status, 'X') = 'S'
THEN
p_trip_name := lv_trip_name;
p_trip_no := ln_trip_id;
UPDATE xx_om_confirm_trip_v
SET trip_created_flag = 'Y',
oracle_trip_id = ln_trip_id,
oracle_trip_name = lv_trip_name
WHERE master_trip_no = p_master_trip;
ELSIF NVL (lv_return_status, 'X') = 'F'
THEN
p_err_msg := lv_msg_data;
END IF;
COMMIT;
END;
/
Tuesday, December 7, 2010
Monday, December 6, 2010
Register a Report with Oracle Applications R12
To register a Report with Oracle Applications in order to use it in Apps.
1. Develop the report (.rdf) file as per client requirement by using reports 10g.
2. move the .rdf file from local machine to server into the respective path CUS_TOP\12.0.0\Reports\US\ .rdf PO_TOP\12.0.0\Reports\US\ .rdf1. Connect to the application and select system administrator create Executable
2. Executable Name (Any User defined Name)
3. Application Name(Where the .rdf file located)
4. Execution Method (Oracle Reports for reports)
5. Execution File (.rdf file Name)
6. Create Concurrent program and attach 1)Executable
7. Parameters
8. Incompatible Programs
9. create Request Group attach Concurrent Program
10. Create responsibility attach
1. Request Group
2. Menu
3. Data Group
11. Create User Attach Responsibility to the User
12. User will submit the request from SRS (Standard Request Submission) Window SELECT user_id UserID, user_name UserName, creation_date Cdate FROM fnd_user
Executable = System Administrator =>
Concurrent = > PRogram =>Executable Concurrent Program = => Concurrent = >
Program => Define Request Group = => Security = >Responsibility => Request responsibility = =>
Security = >responsibility => define user = =>Security =>User =>Define SRS Window =
View=>Requests=>Submit New Request => OK =>Select Program Name =>
Click Submit Button Press NO =>Select find Button =>13. Click View Log button to open the log file.
14. Click View output button to open the output file.
1. Develop the report (.rdf) file as per client requirement by using reports 10g.
2. move the .rdf file from local machine to server into the respective path CUS_TOP\12.0.0\Reports\US\ .rdf PO_TOP\12.0.0\Reports\US\ .rdf1. Connect to the application and select system administrator create Executable
2. Executable Name (Any User defined Name)
3. Application Name(Where the .rdf file located)
4. Execution Method (Oracle Reports for reports)
5. Execution File (.rdf file Name)
6. Create Concurrent program and attach 1)Executable
7. Parameters
8. Incompatible Programs
9. create Request Group attach Concurrent Program
10. Create responsibility attach
1. Request Group
2. Menu
3. Data Group
11. Create User Attach Responsibility to the User
12. User will submit the request from SRS (Standard Request Submission) Window SELECT user_id UserID, user_name UserName, creation_date Cdate FROM fnd_user
Executable = System Administrator =>
Concurrent = > PRogram =>Executable Concurrent Program = => Concurrent = >
Program => Define Request Group = => Security = >Responsibility => Request responsibility = =>
Security = >responsibility => define user = =>Security =>User =>Define SRS Window =
View=>Requests=>Submit New Request => OK =>Select Program Name =>
Click Submit Button Press NO =>Select find Button =>13. Click View Log button to open the log file.
14. Click View output button to open the output file.
Sunday, December 5, 2010
Check Printing in Oracle R12
Login to XML Publisher Administrator, download below template and start customizing report. IBYDC_STD1.rtf file is the initial starting point for Check printing report customization in R12
Template = Standard Check Format
Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Code = IBY_FD_INSTRUCTION_1_0
File = IBYDC_STD1.rtf
There are always lot of challenges to design check printing report as per clients requirement. I tried to cover most of them below:
1) Below link specifies all steps required for MICR Font Setup on our desktop and application server
http://www.centroid.com/Blog/oracle/check-printing-using-micr-fonts-in-r12/
For more details, refer to Metalink Note- 312353.1. This document describes how to set up the MICR font used in check printing.
2) Perform below steps to add signature in your RTF Template
a) Insert Picture tag in your template and click on Tab 'Web'
b) Insert url in that tab (Example given in separate article)
c) Finally copy your signature file Sample_Sig.gif in $OA_MEDIA top at Application server
You can refer separate article on this RTF Template- Signature Printing
3) In order to fix total number of rows on each page and then do pagebreak, go thru below link
http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary
There are always lot of challenges to design check printing report as per clients requirement. I tried to cover most of them below:
1) Below link specifies all steps required for MICR Font Setup on our desktop and application server
http://www.centroid.com/Blog/oracle/check-printing-using-micr-fonts-in-r12/
For more details, refer to Metalink Note- 312353.1. This document describes how to set up the MICR font used in check printing.
2) Perform below steps to add signature in your RTF Template
a) Insert Picture tag in your template and click on Tab 'Web'
b) Insert url in that tab (Example given in separate article)
c) Finally copy your signature file Sample_Sig.gif in $OA_MEDIA top at Application server
You can refer separate article on this RTF Template- Signature Printing
3) In order to fix total number of rows on each page and then do pagebreak, go thru below link
http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary
Set Environment for Oracle Forms development
1. Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to C:\DevSuiteHome_1\forms directory copy all .pll files from /Applvis/visappl/au/12.0.0/resource to C:\DevSuiteHome_1\forms using ftp
2. Open Template.fmb in Oracle Forms Builder 10g. It should open without giving any error
3. Now environment is set to do further development
Resolving records stuck in the Receiving Transactions Interface
Reprocessing Errored Rows
-------------------------
On occassion it may be necessary to reprocess an errored or stuck record in the interface. The following steps can be used to reprocess a stuck record.
Navigate to the Transaction Status Summary screen in the application from either Receiving or Inventory responsibility (Nav > Receiving > Transaction Status Summary). Use the Find form to query up the transaction in question and then set focus to the transaction.
You then select the Delete function from the menu by pressing on the red x icon button to delete the record.
You can then reattempt to receive the record. If it fails again you must engage Oracle Support Services for further disposition.
Technical Details:
-----------------
When a row has been succesfully processed it will be deleted from the table. When a row has been processed for an inventory item, the rcv_transactions stable will be updated and a row written to the mtl_transactions_interface table, waiting to be picked up by the Inventory Transaction Processor. Once this record has been processed succesfully, the on hand quantity level will be updated.
Receiving statuses and errors can subsequently be viewed in the Receiving Transaction Status Summary form in Purchasing / Inventory. On occassion the records cannot be viewed in this form and they have to be queried using sqlplus.
To query stuck rows, run the following sql statement which will yield errored records for the past 7 days (if you want to change the time
period just change the sysdate - 7 criteria to a larger duration for example for the past 30 days it would be: "and transaction_date >= sysdate - 30")
Here is the query:
SQL> select interface_transaction_id, processing_status_code, processing_mode_code, transaction_status_code, transaction_type,
transaction_date
from rcv_transactions_interface
where (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR') and transaction_date >= sysdate - 7;
The processing_mode_code determines whether the record was received in Online, Immediate or Batch mode. This is the column that would
need to be updated to 'BATCH' to reprocess an online record.
Troubleshooting
----------------
If the receipt has gone through okay but the stock levels have not been updated then it is worth checking if the inventory transaction manager is running and if there are any records pending or in error in the inventory transactions interface. These can be queried via the Inventory Transactions Interface Inquiry screen.
If the Purchase Order shows that the shipment has been received and delivered and inventory levels show the stock levels updated to reflect the delivery, then the records can be deleted using the Transaction Status Summary form (Ref Note 303544.1 - How To Remove Pending and Error Transactions from the Transaction Status Summary) by selecting the record and then picking the Delete red X icon on the toolbar in the menu. In this case there is no need to reprocess or re-enter these transactions.
Note: If a transaction is accessible via the Transaction Status Summary form and is in error it can be deleted.
Generic script to delete RTI records which are not appearing in Transaction Status Summary form for deletion. Please ensure the scripts are ran on TEST instance first and tested for data
correctness thoroughly. After the scripts are ran, please check the data and only the correct records are updated before committing. If all goes well, the script can be promoted to the PRODUCTION instance.
--back up date in rti, rli, mtlt, rsi, msnt
create table rti_bak as select * from rcv_transactions_interface
where interface_transaction_id in (&interface_transaction_ids);
create table rli_bak as select * from rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table mtlt_bak as
select * from mtl_transaction_lots_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
create table rsi_bak as
select * from rcv_serials_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table msnt_bak as select * from mtl_serial_numbers_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
--delete data in rti, rli, mtlt, rsi, msnt
delete rcv_transactions_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_transaction_lots_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
delete rcv_serials_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_serial_numbers_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
References :
Resolving records stuck in the Receiving Transactions Interface
Doc ID 50903.1
Transaction Status Summary: How To Remove Pending and Error Receiving Transactions [ID 303544.1]
-------------------------
On occassion it may be necessary to reprocess an errored or stuck record in the interface. The following steps can be used to reprocess a stuck record.
Navigate to the Transaction Status Summary screen in the application from either Receiving or Inventory responsibility (Nav > Receiving > Transaction Status Summary). Use the Find form to query up the transaction in question and then set focus to the transaction.
You then select the Delete function from the menu by pressing on the red x icon button to delete the record.
You can then reattempt to receive the record. If it fails again you must engage Oracle Support Services for further disposition.
Technical Details:
-----------------
When a row has been succesfully processed it will be deleted from the table. When a row has been processed for an inventory item, the rcv_transactions stable will be updated and a row written to the mtl_transactions_interface table, waiting to be picked up by the Inventory Transaction Processor. Once this record has been processed succesfully, the on hand quantity level will be updated.
Receiving statuses and errors can subsequently be viewed in the Receiving Transaction Status Summary form in Purchasing / Inventory. On occassion the records cannot be viewed in this form and they have to be queried using sqlplus.
To query stuck rows, run the following sql statement which will yield errored records for the past 7 days (if you want to change the time
period just change the sysdate - 7 criteria to a larger duration for example for the past 30 days it would be: "and transaction_date >= sysdate - 30")
Here is the query:
SQL> select interface_transaction_id, processing_status_code, processing_mode_code, transaction_status_code, transaction_type,
transaction_date
from rcv_transactions_interface
where (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR') and transaction_date >= sysdate - 7;
The processing_mode_code determines whether the record was received in Online, Immediate or Batch mode. This is the column that would
need to be updated to 'BATCH' to reprocess an online record.
Troubleshooting
----------------
If the receipt has gone through okay but the stock levels have not been updated then it is worth checking if the inventory transaction manager is running and if there are any records pending or in error in the inventory transactions interface. These can be queried via the Inventory Transactions Interface Inquiry screen.
If the Purchase Order shows that the shipment has been received and delivered and inventory levels show the stock levels updated to reflect the delivery, then the records can be deleted using the Transaction Status Summary form (Ref Note 303544.1 - How To Remove Pending and Error Transactions from the Transaction Status Summary) by selecting the record and then picking the Delete red X icon on the toolbar in the menu. In this case there is no need to reprocess or re-enter these transactions.
Note: If a transaction is accessible via the Transaction Status Summary form and is in error it can be deleted.
Generic script to delete RTI records which are not appearing in Transaction Status Summary form for deletion. Please ensure the scripts are ran on TEST instance first and tested for data
correctness thoroughly. After the scripts are ran, please check the data and only the correct records are updated before committing. If all goes well, the script can be promoted to the PRODUCTION instance.
--back up date in rti, rli, mtlt, rsi, msnt
create table rti_bak as select * from rcv_transactions_interface
where interface_transaction_id in (&interface_transaction_ids);
create table rli_bak as select * from rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table mtlt_bak as
select * from mtl_transaction_lots_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
create table rsi_bak as
select * from rcv_serials_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
create table msnt_bak as select * from mtl_serial_numbers_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
--delete data in rti, rli, mtlt, rsi, msnt
delete rcv_transactions_interface where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete rcv_lots_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_transaction_lots_temp
where product_code = 'RCV' and product_transaction_id in (select interface_transaction_id
from rti_bak);
delete rcv_serials_interface
where interface_transaction_id in (select interface_transaction_id from rti_bak);
delete mtl_serial_numbers_temp
where product_code = 'RCV'
and product_transaction_id in (select interface_transaction_id from rti_bak);
References :
Resolving records stuck in the Receiving Transactions Interface
Doc ID 50903.1
Transaction Status Summary: How To Remove Pending and Error Receiving Transactions [ID 303544.1]
Saturday, December 4, 2010
Items not Costed – Oracle inventory
select * from mtl_material transaction where costed_flag is not null
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.
/
Subscribe to:
Posts (Atom)