5:
6:
7: BEGIN
8:
9: DELETE FROM mtl_material_transactions_temp
10: WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;
11:
12:
13: INSERT INTO mtl_so_rma_receipts(
21: 0, 1, mt.department_id +
22: msri.delivered_quantity - msri.received_quantity, 0),
23: mt.department_id,msri.unit_code,mt.transaction_date,mt.subinventory_code,
24: mt.last_update_date,mt.last_updated_by,mt.creation_date,mt.created_by
25: FROM mtl_so_rma_interface msri, mtl_material_transactions_temp mt
26: WHERE mt.transaction_header_id = header_id_value
27: AND mt.trx_source_delivery_id = msri.rma_interface_id ;
28:
29:
33: msri.received_quantity),-1,
34: msri.received_quantity, 1, sum(mt.department_id) +
35: msri.delivered_quantity,
36: msri.received_quantity)
37: FROM mtl_material_transactions_temp mt
38: WHERE mt.transaction_header_id = header_id_value
39: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
40: msri.delivered_quantity = (SELECT sum(mt.department_id) +
41: msri.delivered_quantity
38: WHERE mt.transaction_header_id = header_id_value
39: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
40: msri.delivered_quantity = (SELECT sum(mt.department_id) +
41: msri.delivered_quantity
42: FROM mtl_material_transactions_temp mt
43: WHERE mt.transaction_header_id = header_id_value
44: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
45:
46: msri.last_update_date = (SELECT mt.last_update_date FROM
43: WHERE mt.transaction_header_id = header_id_value
44: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
45:
46: msri.last_update_date = (SELECT mt.last_update_date FROM
47: mtl_material_transactions_temp mt
48: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
49: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
50: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
51: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id FROM
45:
46: msri.last_update_date = (SELECT mt.last_update_date FROM
47: mtl_material_transactions_temp mt
48: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
49: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
50: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
51: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id FROM
52: mtl_material_transactions_temp mmtt
53: WHERE mmtt.transaction_header_id = header_id_value) ;
48: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
49: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
50: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
51: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id FROM
52: mtl_material_transactions_temp mmtt
53: WHERE mmtt.transaction_header_id = header_id_value) ;
54:
55: success := TRUE;
56: EXCEPTION
64: sql_done boolean := TRUE;
65:
66: BEGIN
67:
68: DELETE FROM mtl_material_transactions_temp
69: WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;
70:
71: INSERT INTO mtl_so_rma_receipts(
72: RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
78: 0, 1, mt.department_id +
79: msri.delivered_quantity - msri.received_quantity, 0),
80: mt.department_id,msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,mt.last_updated_by,
81: mt.creation_date,mt.created_by
82: FROM mtl_so_rma_interface msri, mtl_material_transactions mt
83: WHERE mt.transaction_set_id = header_id_value
84: AND mt.trx_source_delivery_id = msri.rma_interface_id ;
85:
86: UPDATE mtl_so_rma_interface msri
89: msri.received_quantity),-1,
90: msri.received_quantity, 1, sum(mt.department_id) +
91: msri.delivered_quantity,
92: msri.received_quantity)
93: FROM mtl_material_transactions mt
94: WHERE mt.transaction_set_id = header_id_value
95: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
96: msri.delivered_quantity = (SELECT sum(mt.department_id) + msri.delivered_quantity
97: FROM mtl_material_transactions mt
93: FROM mtl_material_transactions mt
94: WHERE mt.transaction_set_id = header_id_value
95: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
96: msri.delivered_quantity = (SELECT sum(mt.department_id) + msri.delivered_quantity
97: FROM mtl_material_transactions mt
98: WHERE mt.transaction_set_id = header_id_value
99: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
100: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
101: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
96: msri.delivered_quantity = (SELECT sum(mt.department_id) + msri.delivered_quantity
97: FROM mtl_material_transactions mt
98: WHERE mt.transaction_set_id = header_id_value
99: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
100: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
101: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
102: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
103: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
104: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id FROM
98: WHERE mt.transaction_set_id = header_id_value
99: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
100: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
101: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
102: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
103: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
104: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id FROM
105: mtl_material_transactions mmt
106: WHERE mmt.transaction_set_id = header_id_value) ;
101: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
102: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
103: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
104: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id FROM
105: mtl_material_transactions mmt
106: WHERE mmt.transaction_set_id = header_id_value) ;
107:
108: UPDATE mtl_material_transactions
109: SET department_id = NULL
104: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id FROM
105: mtl_material_transactions mmt
106: WHERE mmt.transaction_set_id = header_id_value) ;
107:
108: UPDATE mtl_material_transactions
109: SET department_id = NULL
110: WHERE transaction_set_id = header_id_value ;
111:
112: COMMIT;
124: Procedure update_rma_returns(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
125: sql_done boolean := TRUE ;
126: BEGIN
127:
128: DELETE FROM mtl_material_transactions_temp
129: WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;
130:
131:
132: INSERT INTO mtl_so_rma_receipts(
135: SELECT
136: mt.transaction_temp_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id, mt.department_id * -1,
137: mt.department_id * -1, msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,
138: mt.last_updated_by,mt.creation_date,mt.created_by
139: FROM mtl_so_rma_interface msri, mtl_material_transactions_temp mt
140: WHERE mt.transaction_header_id = header_id_value
141: AND mt.trx_source_delivery_id = msri.rma_interface_id ;
142:
143:
142:
143:
144: UPDATE mtl_so_rma_interface msri
145: SET msri.received_quantity = (SELECT msri.received_quantity - sum(mt.department_id)
146: FROM mtl_material_transactions_temp mt
147: WHERE mt.transaction_header_id = header_id_value
148: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
149: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
150: FROM mtl_material_transactions_temp mt
146: FROM mtl_material_transactions_temp mt
147: WHERE mt.transaction_header_id = header_id_value
148: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
149: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
150: FROM mtl_material_transactions_temp mt
151: WHERE mt.transaction_header_id = header_id_value
152: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
153: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions_temp mt
154: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
149: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
150: FROM mtl_material_transactions_temp mt
151: WHERE mt.transaction_header_id = header_id_value
152: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
153: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions_temp mt
154: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
155: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
156: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
157: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id
151: WHERE mt.transaction_header_id = header_id_value
152: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
153: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions_temp mt
154: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
155: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
156: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
157: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id
158: FROM mtl_material_transactions_temp mmtt
159: WHERE mmtt.transaction_header_id = header_id_value) ;
154: WHERE mt.transaction_header_id = header_id_value AND rownum = 1),
155: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions_temp mt
156: WHERE mt.transaction_header_id = header_id_value AND rownum = 1)
157: WHERE msri.rma_interface_id IN (SELECT mmtt.trx_source_delivery_id
158: FROM mtl_material_transactions_temp mmtt
159: WHERE mmtt.transaction_header_id = header_id_value) ;
160:
161: success := TRUE;
162: EXCEPTION
170: Procedure update_rma_returns_rpc(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
171: sql_done boolean := TRUE ;
172: BEGIN
173:
174: DELETE FROM mtl_material_transactions_temp
175: WHERE transaction_header_id = header_id_value AND process_flag = 'N' ;
176:
177:
178: INSERT INTO mtl_so_rma_receipts(
181: SELECT
182: mt.transaction_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id, mt.department_id * -1,
183: mt.department_id * -1, msri.unit_code,mt.transaction_date,mt.subinventory_code,mt.last_update_date,
184: mt.last_updated_by,mt.creation_date,mt.created_by
185: FROM mtl_so_rma_interface msri, mtl_material_transactions mt
186: WHERE mt.transaction_set_id = header_id_value
187: AND mt.trx_source_delivery_id = msri.rma_interface_id ;
188:
189: UPDATE mtl_so_rma_interface msri
187: AND mt.trx_source_delivery_id = msri.rma_interface_id ;
188:
189: UPDATE mtl_so_rma_interface msri
190: SET msri.received_quantity = (SELECT msri.received_quantity - sum(mt.department_id)
191: FROM mtl_material_transactions mt
192: WHERE mt.transaction_set_id = header_id_value
193: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
194: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
195: FROM mtl_material_transactions mt
191: FROM mtl_material_transactions mt
192: WHERE mt.transaction_set_id = header_id_value
193: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
194: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
195: FROM mtl_material_transactions mt
196: WHERE mt.transaction_set_id = header_id_value
197: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
198: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
199: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
194: msri.delivered_quantity = (select msri.delivered_quantity - sum(mt.department_id)
195: FROM mtl_material_transactions mt
196: WHERE mt.transaction_set_id = header_id_value
197: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
198: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
199: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
200: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
201: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
202: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id
196: WHERE mt.transaction_set_id = header_id_value
197: AND mt.trx_source_delivery_id = msri.rma_interface_id ),
198: msri.last_update_date = (SELECT mt.last_update_date FROM mtl_material_transactions mt
199: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
200: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
201: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
202: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id
203: FROM mtl_material_transactions mmt
204: WHERE mmt.transaction_set_id = header_id_value) ;
199: WHERE mt.transaction_set_id = header_id_value AND rownum = 1),
200: msri.last_updated_by = (SELECT mt.last_updated_by from mtl_material_transactions mt
201: WHERE mt.transaction_set_id = header_id_value AND rownum = 1)
202: WHERE msri.rma_interface_id IN (SELECT mmt.trx_source_delivery_id
203: FROM mtl_material_transactions mmt
204: WHERE mmt.transaction_set_id = header_id_value) ;
205:
206:
207: UPDATE mtl_material_transactions
203: FROM mtl_material_transactions mmt
204: WHERE mmt.transaction_set_id = header_id_value) ;
205:
206:
207: UPDATE mtl_material_transactions
208: SET department_id = NULL
209: WHERE transaction_set_id = header_id_value ;
210:
211: COMMIT;