eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.RMA_UPDATE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9


DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;

Line: 13


INSERT INTO mtl_so_rma_receipts(
RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
SELECT
mt.transaction_temp_id,mt.trx_source_delivery_id,mt.organization_id,
mt.inventory_item_id,
decode(sign(mt.department_id + msri.delivered_quantity -
        	msri.received_quantity),-1,
        	0, 1, mt.department_id +
        	msri.delivered_quantity - msri.received_quantity, 0),
mt.department_id,msri.unit_code,mt.transaction_date,mt.subinventory_code,
mt.last_update_date,mt.last_updated_by,mt.creation_date,mt.created_by
FROM mtl_so_rma_interface msri, mtl_material_transactions_temp mt
WHERE mt.transaction_header_id  = header_id_value
AND mt.trx_source_delivery_id = msri.rma_interface_id ;

Line: 30


UPDATE mtl_so_rma_interface msri
        SET msri.received_quantity =  (SELECT
	     decode(sign(sum(mt.department_id) + msri.delivered_quantity -
             msri.received_quantity),-1,
             msri.received_quantity, 1, sum(mt.department_id) +
             msri.delivered_quantity,
             msri.received_quantity)
             FROM mtl_material_transactions_temp mt
             WHERE mt.transaction_header_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
        msri.delivered_quantity = (SELECT sum(mt.department_id) +
        msri.delivered_quantity
             FROM mtl_material_transactions_temp mt
             WHERE mt.transaction_header_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),

	msri.last_update_date = (SELECT mt.last_update_date FROM
        mtl_material_transactions_temp mt
				WHERE mt.transaction_header_id  = header_id_value AND rownum = 1),
	msri.last_updated_by =  (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
				WHERE mt.transaction_header_id  = header_id_value AND rownum = 1)
	WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id FROM
				mtl_material_transactions_temp mmtt
				WHERE mmtt.transaction_header_id = header_id_value) ;

Line: 61


end update_rma_receipts;

Line: 63


Procedure update_rma_receipts_rpc(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
	sql_done	boolean := TRUE;

Line: 68


DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;

Line: 71


INSERT INTO mtl_so_rma_receipts(
RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
SELECT
mt.transaction_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id,
decode(sign(mt.department_id + msri.delivered_quantity -
        	msri.received_quantity),-1,
        	0, 1, mt.department_id +
        	msri.delivered_quantity - msri.received_quantity, 0),
mt.department_id,msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,mt.last_updated_by,
mt.creation_date,mt.created_by
FROM mtl_so_rma_interface msri, mtl_material_transactions mt
WHERE mt.transaction_set_id  = header_id_value
AND mt.trx_source_delivery_id = msri.rma_interface_id ;

Line: 86


UPDATE mtl_so_rma_interface msri
        SET msri.received_quantity =  (SELECT
	     decode(sign(sum(mt.department_id) + msri.delivered_quantity -
             msri.received_quantity),-1,
             msri.received_quantity, 1, sum(mt.department_id) +
             msri.delivered_quantity,
             msri.received_quantity)
             FROM mtl_material_transactions mt
             WHERE mt.transaction_set_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
        msri.delivered_quantity = (SELECT sum(mt.department_id) + msri.delivered_quantity
             FROM mtl_material_transactions mt
             WHERE mt.transaction_set_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
	msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
				WHERE mt.transaction_set_id  = header_id_value AND rownum = 1),
	msri.last_updated_by =  (SELECT mt.last_updated_by from mtl_material_transactions mt
				WHERE mt.transaction_set_id  = header_id_value AND rownum = 1)
	WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id FROM
				mtl_material_transactions mmt
				WHERE mmt.transaction_set_id = header_id_value) ;

Line: 108


UPDATE mtl_material_transactions
	SET department_id = NULL
	WHERE transaction_set_id = header_id_value ;

Line: 121


end update_rma_receipts_rpc;

Line: 124


Procedure update_rma_returns(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
	sql_done	boolean := TRUE ;

Line: 128


DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;

Line: 132


INSERT INTO mtl_so_rma_receipts(
RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
SELECT
mt.transaction_temp_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id, mt.department_id * -1,
mt.department_id * -1, msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,
mt.last_updated_by,mt.creation_date,mt.created_by
FROM mtl_so_rma_interface msri, mtl_material_transactions_temp mt
WHERE mt.transaction_header_id  = header_id_value
AND mt.trx_source_delivery_id = msri.rma_interface_id ;

Line: 144


UPDATE mtl_so_rma_interface msri
        SET msri.received_quantity = (SELECT msri.received_quantity - sum(mt.department_id)
             FROM  mtl_material_transactions_temp mt
             WHERE mt.transaction_header_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
        msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
             FROM mtl_material_transactions_temp mt
             WHERE mt.transaction_header_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
	msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions_temp mt
				WHERE mt.transaction_header_id  = header_id_value AND rownum = 1),
	msri.last_updated_by =  (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
				WHERE mt.transaction_header_id  = header_id_value AND rownum = 1)
	WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id
	FROM mtl_material_transactions_temp mmtt
	WHERE mmtt.transaction_header_id = header_id_value) ;

Line: 168


end update_rma_returns;

Line: 170


Procedure update_rma_returns_rpc(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
	sql_done	boolean := TRUE ;

Line: 174


DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;

Line: 178


INSERT INTO mtl_so_rma_receipts(
RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
SELECT
mt.transaction_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id, mt.department_id * -1,
mt.department_id * -1, msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,
mt.last_updated_by,mt.creation_date,mt.created_by
FROM mtl_so_rma_interface msri, mtl_material_transactions mt
WHERE mt.transaction_set_id  = header_id_value
AND mt.trx_source_delivery_id = msri.rma_interface_id ;

Line: 189


UPDATE mtl_so_rma_interface msri
        SET msri.received_quantity =   (SELECT msri.received_quantity - sum(mt.department_id)
             FROM  mtl_material_transactions mt
             WHERE mt.transaction_set_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
        msri.delivered_quantity = (select  msri.delivered_quantity - sum(mt.department_id)
             FROM mtl_material_transactions mt
             WHERE mt.transaction_set_id  = header_id_value
	     AND mt.trx_source_delivery_id = msri.rma_interface_id ),
	msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
				WHERE mt.transaction_set_id  = header_id_value AND rownum = 1),
	msri.last_updated_by =  (SELECT mt.last_updated_by from mtl_material_transactions mt
				WHERE mt.transaction_set_id  = header_id_value AND rownum = 1)
	WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id
	FROM mtl_material_transactions mmt
	WHERE mmt.transaction_set_id = header_id_value) ;

Line: 207


UPDATE mtl_material_transactions
	SET department_id = NULL
	WHERE transaction_set_id = header_id_value ;

Line: 220


end update_rma_returns_rpc;