[Home] [Help]
PACKAGE BODY: APPS.RMA_UPDATE
Source
1 PACKAGE BODY rma_update AS
2 /* $Header: INVRMAUB.pls 120.2 2005/10/05 06:19:07 amohamme noship $ */
3 Procedure update_rma_receipts(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
4 sql_done boolean:= TRUE;
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(
14 RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
15 RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
16 SELECT
17 mt.transaction_temp_id,mt.trx_source_delivery_id,mt.organization_id,
18 mt.inventory_item_id,
19 decode(sign(mt.department_id + msri.delivered_quantity -
20 msri.received_quantity),-1,
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
30 UPDATE mtl_so_rma_interface msri
31 SET msri.received_quantity = (SELECT
32 decode(sign(sum(mt.department_id) + msri.delivered_quantity -
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
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
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) ;
54
55 success := TRUE;
56 EXCEPTION
57 WHEN NO_DATA_FOUND then
58 success := FALSE;
59 WHEN OTHERS then
60 success := FALSE;
61 end update_rma_receipts;
62
63 Procedure update_rma_receipts_rpc(header_id_value number, trx_rma_id number, success OUT NOCOPY /* file.sql.39 change */ boolean) IS
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,
73 RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
74 SELECT
75 mt.transaction_id,mt.trx_source_delivery_id,mt.organization_id,mt.inventory_item_id,
76 decode(sign(mt.department_id + msri.delivered_quantity -
77 msri.received_quantity),-1,
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
87 SET msri.received_quantity = (SELECT
88 decode(sign(sum(mt.department_id) + msri.delivered_quantity -
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
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) ;
107
108 UPDATE mtl_material_transactions
109 SET department_id = NULL
110 WHERE transaction_set_id = header_id_value ;
111
112 COMMIT;
113 success := TRUE;
114 EXCEPTION
115 WHEN NO_DATA_FOUND then
116 success := FALSE;
117 WHEN OTHERS then
118 success := FALSE;
119
120
121 end update_rma_receipts_rpc;
122
123
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(
133 RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
134 RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
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
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
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) ;
160
161 success := TRUE;
162 EXCEPTION
163 WHEN NO_DATA_FOUND then
164 success := FALSE;
165 WHEN OTHERS then
166 success := FALSE;
167
168 end update_rma_returns;
169
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(
179 RMA_RECEIPT_ID,RMA_INTERFACE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,RECEIVED_QUANTITY,ACCEPTED_QUANTITY,UNIT_CODE,
180 RECEIPT_DATE,RETURN_SUBINVENTORY_NAME,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
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
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
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) ;
205
206
207 UPDATE mtl_material_transactions
208 SET department_id = NULL
209 WHERE transaction_set_id = header_id_value ;
210
211 COMMIT;
212 success := TRUE;
213 EXCEPTION
214 WHEN NO_DATA_FOUND then
215 success := FALSE;
216 WHEN OTHERS then
217 success := FALSE;
218
219
220 end update_rma_returns_rpc;
221
222 Procedure gen_sales_order_id(r_number varchar2, r_type varchar2,
223 r_source_code varchar2, r_id OUT NOCOPY /* file.sql.39 change */ number) IS
224 success boolean := TRUE ;
225 xchar_formatted_date varchar2(200);
226 segs FND_FLEX_EXT.SegmentArray;
227 BEGIN
228
229 /*
230 package is fnd_flex_ext
231 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
232 key_flex_code IN VARCHAR2,
233 structure_number IN NUMBER,
234 validation_date IN DATE,
235 n_segments IN NUMBER,
236 segments IN SegmentArray,
237 combination_id OUT NOCOPY NUMBER) RETURN BOOLEAN;
238 */
239 segs(1) := r_number ;
240 segs(2) := r_type ;
241 segs(3) := r_source_code ;
242 xchar_formatted_date := to_char(Sysdate, 'YYYY/MM/DD');
243 success := fnd_flex_ext.get_combination_id('INV', 'MKTS', 101,
244 TO_DATE(xchar_formatted_date,'YYYY/MM/DD'), 3, segs, r_id) ;
245 if not success then
246 r_id := -9999 ;
247 end if;
248
249 end gen_sales_order_id ;
250
251
252 END rma_update;