[Home] [Help]
PACKAGE BODY: APPS.RMA_DIAGNOSTICS
Source
1 PACKAGE BODY RMA_DIAGNOSTICS AS
2 /* $Header: INVDRMA1B.pls 120.0.12000000.1 2007/08/09 06:51:43 ssadasiv noship $ */
3
4 PROCEDURE rma_sql(p_operating_id IN NUMBER, p_rma_number IN NUMBER, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list
5 ) IS
6 l_rma_number rcv_shipment_headers.receipt_num%TYPE := p_rma_number;
7 l_operating_id rcv_shipment_headers.organization_id%TYPE := p_operating_id;
8
9 BEGIN
10
11 p_sql(1) := ' select distinct soh.* ' ||
12 ' from oe_order_headers_all soh , oe_order_lines_all sol ' ||
13 ' where order_number = '||''''||l_rma_number||'''' ||
14 ' and soh.org_id ='|| l_operating_id ||
15 ' and soh.header_id = sol.header_id ' ||
16 ' and sol.line_category_code = ''RETURN'' ' ;
17
18
19 p_sql(2) := ' select sol.* ' ||
20 ' from oe_order_lines_all sol , oe_order_headers_all soh ' ||
21 ' where order_number = '||''''||l_rma_number||'''' ||
22 ' and sol.line_category_code = ''RETURN'' ' ||
23 ' and sol.header_id = soh.header_id ' ||
24 ' and soh.org_id ='|| l_operating_id ;
25
26
27 p_sql(3) := ' select distinct msi.* ' ||
28 ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
29 ' where order_number = '||''''||l_rma_number||'''' ||
30 ' and sol.line_category_code = ''RETURN'' ' ||
31 ' and sol.header_id = soh.header_id ' ||
32 ' and msi.inventory_item_id = sol.inventory_item_id ' ||
33 ' and msi.organization_id = sol.ship_from_org_id ' ||
34 ' and soh.org_id ='|| l_operating_id ;
35
36
37 p_sql(4) := ' select rsh.* ' ||
38 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
39 ' where soh.order_number = '||''''||l_rma_number||'''' ||
40 ' and rsl.oe_order_header_id = soh.header_id ' ||
41 ' and rsl.oe_order_line_id = sol.line_id ' ||
42 ' and sol.header_id = soh.header_id ' ||
43 ' and sol.line_category_code = ''RETURN'' ' ||
44 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
45 ' and soh.org_id ='|| l_operating_id ||
46 ' union all' ||
47 ' select rsh.* '||
48 'from rcv_shipment_headers rsh, oe_order_headers_all soh , rcv_transactions_interface rti '||
49 ' WHERE rsh.shipment_header_id = rti.shipment_header_id'||
50 ' AND rti.oe_order_header_id = soh.header_id' ||
51 ' and soh.order_number = '||''''||l_rma_number||'''' ||
52 ' and soh.org_id ='|| l_operating_id ;
53
54 p_sql(5) := ' select rsl.* ' ||
55 ' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
56 ' where order_number = '||''''||l_rma_number||'''' ||
57 ' and rsl.oe_order_header_id = soh.header_id ' ||
58 ' and sol.header_id = soh.header_id ' ||
59 ' and sol.line_category_code = ''RETURN'' ' ||
60 ' and rsl.oe_order_line_id = sol.line_id ' ||
61 ' and sol.header_id = soh.header_id ' ||
62 ' and sol.line_category_code = ''RETURN'' ' ||
63 ' and soh.org_id ='|| l_operating_id ;
64
65
66 p_sql(6) := ' select rt.* ' ||
67 ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
68 ' where order_number = '||''''||l_rma_number||'''' ||
69 ' and rt.oe_order_header_id = soh.header_id ' ||
70 ' and rt.oe_order_line_id = sol.line_id ' ||
71 ' and sol.header_id = soh.header_id ' ||
72 ' and sol.line_category_code = ''RETURN'' '||
73 ' and soh.org_id ='|| l_operating_id ;
74
75
76
77 p_sql(7) := ' select rhi.* ' ||
78 ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
79 ||
80 ' where order_number = '||''''||l_rma_number||'''' ||
81 ' and (rti.oe_order_header_id = soh.header_id ' ||
82 ' or rti.oe_order_line_id = sol.line_id ) ' ||
83 ' and sol.header_id = soh.header_id ' ||
84 ' and sol.line_category_code = ''RETURN'' ' ||
85 ' and rhi.header_interface_id = rti.header_interface_id ' ||
86 ' and soh.org_id ='|| l_operating_id ;
87
88
89 p_sql(8) := ' select rti.* ' ||
90 ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||
91 ' where order_number = '||''''||l_rma_number||'''' ||
92 ' and (rti.oe_order_header_id = soh.header_id ' ||
93 ' or rti.oe_order_line_id = sol.line_id ) ' ||
94 ' and sol.header_id = soh.header_id ' ||
95 ' and sol.line_category_code = ''RETURN'' '||
96 ' and soh.org_id ='|| l_operating_id ;
97
98
99
100 p_sql(9) := ' select pie.* ' ||
101 ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
102 ' where order_number = '||''''||l_rma_number||'''' ||
103 ' and sol.header_id = soh.header_id ' ||
104 ' and sol.line_category_code = ''RETURN'' ' ||
105 ' and soh.org_id ='|| l_operating_id ||
106 ' and (exists (' ||
107 ' select 1 ' ||
108 ' from rcv_transactions_interface rti ' ||
109 ' where (rti.oe_order_header_id = soh.header_id ' ||
110 ' or rti.oe_order_line_id = sol.line_id ) ' ||
111 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
112 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
113 ' or exists (' ||
114 ' select 2 ' ||
115 ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
116 ' where (rti.oe_order_header_id = soh.header_id ' ||
117 ' or rti.oe_order_line_id = sol.line_id ) ' ||
118 ' and rhi.header_interface_id = rti.header_interface_id ' ||
119 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
120 ' and pie.interface_header_id = rhi.header_interface_id) ) ';
121
122 p_sql(10) := ' select distinct ood.* ' ||
123 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
124 ||
125 ' where order_number = '||''''||l_rma_number||'''' ||
126 ' and soh.org_id ='|| l_operating_id ||
127 ' and rsl.oe_order_header_id = soh.header_id ' ||
128 ' and rsl.oe_order_line_id = sol.line_id ' ||
129 ' and sol.header_id = soh.header_id ' ||
130 ' and sol.line_category_code = ''RETURN'' ' ||
131 ' and ood.organization_id = rsl.to_organization_id ';
132
133
134 p_sql(11) := ' select distinct mp.* ' ||
135 ' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
136 ' where order_number = '||''''||l_rma_number||'''' ||
137 ' and soh.org_id ='|| l_operating_id ||
138 ' and rsl.oe_order_header_id = soh.header_id ' ||
139 ' and rsl.oe_order_line_id = sol.line_id ' ||
140 ' and sol.header_id = soh.header_id ' ||
141 ' and sol.line_category_code = ''RETURN'' ' ||
142 ' and mp.organization_id = rsl.to_organization_id ';
143
144
145 p_sql(12) := ' select mmt.* ' ||
146 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||
147 ' where order_number = '||''''||l_rma_number||'''' ||
148 ' and soh.org_id ='|| l_operating_id ||
149 ' and sol.header_id = soh.header_id ' ||
150 ' and sol.line_category_code = ''RETURN'' ' ||
151 ' and rt.oe_order_header_id = soh.header_id ' ||
152 ' and rt.oe_order_line_id = sol.line_id ' ||
153 ' and mmt.rcv_transaction_id = rt.transaction_id ';
154
155
156 p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'
157 ||
158 ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
159 ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
160 ||
161 ' rcv_transactions rt ' ||
162 ' where order_number = '||''''||l_rma_number||'''' ||
163 ' and soh.org_id ='|| l_operating_id ||
164 ' and sol.header_id = soh.header_id ' ||
165 ' and sol.line_category_code = ''RETURN'' ' ||
166 ' and rt.oe_order_header_id = soh.header_id ' ||
167 ' and rt.oe_order_line_id = sol.line_id ' ||
168 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
169 ' and mtt.transaction_type_id = mmt.transaction_type_id ';
170
171
172 /*p_sql(14) := ' select distinct mtrl.* ' ||
173 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
174 ' where order_number = '||''''||l_rma_number||'''' ||
175 ' and rt.oe_order_header_id = soh.header_id ' ||
176 ' and rt.oe_order_line_id = sol.line_id ' ||
177 ' and sol.header_id = soh.header_id ' ||
178 ' and sol.line_category_code = ''RETURN'' ' ||
179 ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
180 ' and mtrl.reference_id = rt.oe_order_line_id ' ||
181 ' and soh.org_id ='|| l_operating_id;*/
182
183 p_sql(14) := ' select distinct mtrl.* ' ||
184 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
185 ' where order_number = '||''''||l_rma_number||'''' ||
186 ' and rt.oe_order_header_id = soh.header_id ' ||
187 ' and rt.oe_order_line_id = sol.line_id ' ||
188 ' and sol.header_id = soh.header_id ' ||
189 ' and sol.line_category_code = ''RETURN'' ' ||
190 ' and soh.org_id ='|| l_operating_id||
191 ' AND mtrl.inventory_item_id = sol.inventory_item_id'||
192 ' AND mtrl.organization_id=rt.organization_id ' ||
193 ' and nvl(mtrl.revision,0)=nvl(sol.item_revision,0) ' ||' and mtrl.line_status=7'||
194 ' and mtrl.transaction_type_id=15';
195
196 p_sql(15) := ' select mmtt.* ' ||
197 ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||
198 ' where order_number = '||''''||l_rma_number||'''' ||
199 ' and soh.org_id ='|| l_operating_id ||
200 ' and sol.header_id = soh.header_id ' ||
201 ' and sol.line_category_code = ''RETURN'' ' ||
202 ' and exists (' ||
203 ' select 1 ' ||
204 ' from rcv_transactions rt ' ||
205 ' where rt.oe_order_header_id = soh.header_id ' ||
206 ' and rt.oe_order_line_id = sol.line_id ' ||
207 ' and mmtt.rcv_transaction_id = rt.transaction_id) ';
208
209 p_sql(16) := ' select lsn.* ' ||
210 ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
211 ' where order_number = '||''''||l_rma_number||'''' ||
212 ' and soh.org_id ='|| l_operating_id ||
213 ' and sol.line_category_code = ''RETURN'' ' ||
214 ' and sol.header_id = soh.header_id ' ||
215 ' and lsn.line_id = sol.line_id order by sol.line_id desc , lsn.lot_serial_id ';
216
217
218 p_sql(17) := ' select distinct msn.* ' ||
219 ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
220 ' where order_number = '||''''||l_rma_number||'''' ||
221 ' and soh.org_id ='|| l_operating_id ||
222 ' and sol.header_id = soh.header_id ' ||
223 ' and sol.line_category_code = ''RETURN'' ' ||
224 ' and (exists (' ||
225 ' select 1 ' ||
226 ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
227 ' where rt.oe_order_header_id = soh.header_id ' ||
228 ' and rt.oe_order_line_id = sol.line_id ' ||
229 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
230 ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
231 ' or exists (' ||
232 ' select 3 ' ||
233 ' from mtl_material_transactions ommt ' ||
234 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
235 ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
236 ' or exists (' ||
237 ' select 2 ' ||
238 ' from oe_lot_serial_numbers lsn ' ||
239 ' where lsn.line_id = sol.line_id ' ||
240 ' and sol.ordered_item_id = msn.inventory_item_id ' ||
241 ' and sol.ship_from_org_id = msn.current_organization_id ' ||
242 ' and msn.serial_number between lsn.from_serial_number and nvl(lsn.to_serial_number ,' ||
243 ' lsn.from_serial_number) ) ) order by msn.inventory_item_id , msn.serial_number ';
244
245
246 p_sql(18) := ' select msnt.* ' ||
247 ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
248 ' oe_order_headers_all soh , rcv_transactions rt ' ||
249 ' where order_number = '||''''||l_rma_number||'''' ||
250 ' and soh.org_id ='|| l_operating_id ||
251 ' and sol.header_id = soh.header_id ' ||
252 ' and sol.line_category_code = ''RETURN'' ' ||
253 ' and rt.oe_order_header_id = soh.header_id ' ||
257 ' or exists (' ||
254 ' and rt.oe_order_line_id = sol.line_id ' ||
255 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
256 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
258 ' select 2 ' ||
259 ' from mtl_transaction_lots_temp mtlt ' ||
260 ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
261 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
262
263 p_sql(19) := ' select msni.* ' ||
264 ' from mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
265 ' mtl_serial_numbers_interface msni , rcv_transactions rt ' ||
266 ' where soh.order_number ='||''''||l_rma_number||'''' ||
267 ' and soh.org_id = '||l_operating_id ||
268 ' and sol.header_id = soh.header_id ' ||
269 ' and sol.line_category_code = ''RETURN'' ' ||
270 ' and rt.oe_order_header_id = soh.header_id ' ||
271 ' and rt.oe_order_line_id = sol.line_id ' ||
272 ' and mti.rcv_transaction_id = rt.transaction_id ' ||
273 ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
274 ' or exists (' ||
275 ' select 3 ' ||
276 ' from mtl_transaction_lots_interface mtln ' ||
277 ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
278 ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) )' ;
279
280 p_sql(20) := ' select mut.* ' ||
281 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||
282 ' mtl_unit_transactions mut ' ||
283 ' where soh.order_number ='||''''||l_rma_number||'''' ||
284 ' and soh.org_id = '||l_operating_id ||
285 ' and sol.header_id = soh.header_id ' ||
286 ' and sol.line_category_code = ''RETURN'' ' ||
287 ' and rt.oe_order_header_id = soh.header_id ' ||
288 ' and rt.oe_order_line_id = sol.line_id ' ||
289 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
290 ' and (mut.transaction_id = mmt.transaction_id ' ||
291 ' or exists (' ||
292 ' select 1 ' ||
293 ' from mtl_transaction_lot_numbers mtln ' ||
294 ' where mtln.transaction_id = mmt.transaction_id ' ||
295 ' and mut.transaction_id = mtln.serial_transaction_id ) ) ';
296
297
298
299 p_sql(21) := ' select rst.* ' ||
300 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
301 ' where soh.order_number ='||''''||l_rma_number||'''' ||
302 ' and soh.org_id = '||l_operating_id ||
303 ' and rsl.oe_order_header_id = soh.header_id ' ||
304 ' and rsl.oe_order_line_id = sol.line_id ' ||
305 ' and sol.header_id = soh.header_id ' ||
306 ' and sol.line_category_code = ''RETURN'' ' ||
307 ' and rst.shipment_line_id = rsl.shipment_line_id ';
308
309
310 p_sql(22) := ' select distinct rsi.* ' ||
311 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
312 ' where soh.order_number ='||''''||l_rma_number||'''' ||
313 ' and soh.org_id = '||l_operating_id ||
314 ' and rsl.oe_order_header_id = soh.header_id ' ||
315 ' and rsl.oe_order_line_id = sol.line_id ' ||
316 ' and sol.header_id = soh.header_id ' ||
317 ' and sol.line_category_code = ''RETURN'' ' ||
318 ' and rsi.item_id = rsl.item_id ' ||
319 ' and rsi.organization_id = rsl.to_organization_id ';
320
321
322 p_sql(23) := ' select distinct mln.* ' ||
323 ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
324 ' where soh.order_number ='||''''||l_rma_number||'''' ||
325 ' and soh.org_id = '||l_operating_id ||
326 ' and sol.header_id = soh.header_id ' ||
327 ' and sol.line_category_code = ''RETURN'' ' ||
328 ' and (exists (' ||
329 ' select 1 ' ||
330 ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
331 ' where rt.oe_order_header_id = soh.header_id ' ||
332 ' and rt.oe_order_line_id = sol.line_id ' ||
333 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
334 ' and mmt.transaction_id = mtln.transaction_id ' ||
335 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
336 ' and mln.organization_id = mmt.organization_id ' ||
337 ' and mln.lot_number = mtln.lot_number ) ' ||
338 ' or exists (' ||
339 ' select 2 ' ||
340 ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
341 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
342 ' and ommt.transaction_id = mtln.transaction_id ' ||
343 ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
344 ' and mln.organization_id = ommt.organization_id ' ||
345 ' and mln.lot_number = mtln.lot_number ) ' ||
346 ' or exists (' ||
347 ' select 3 ' ||
348 ' from oe_lot_serial_numbers lsn ' ||
349 ' where lsn.line_id = sol.line_id ' ||
350 ' and sol.ordered_item_id = mln.inventory_item_id ' ||
351 ' and sol.ship_from_org_id = mln.organization_id ' ||
352 ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
353 ' mln.lot_number ';
354
355
356 p_sql(24) := ' select mtln.* ' ||
357 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
358 ||
359 ' soh ' ||
360 ' where soh.order_number ='||''''||l_rma_number||'''' ||
361 ' and soh.org_id = '||l_operating_id ||
362 ' and sol.header_id = soh.header_id ' ||
363 ' and sol.line_category_code = ''RETURN'' ' ||
364 ' and mmt.transaction_id = mtln.transaction_id ' ||
365 ' and (exists (' ||
366 ' select 1 ' ||
367 ' from rcv_transactions rt ' ||
371 ,' ||
368 ' where rt.oe_order_line_id = sol.line_id ' ||
369 ' and mmt.rcv_transaction_id = rt.transaction_id ) ' ||
370 ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
372 ' mtln.transaction_id ';
373
374
375
376 p_sql(25):= ' select mtli.* ' ||
377 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||
378 ' oe_order_headers_all soh , rcv_transactions rt ' ||
379 ' where soh.order_number = '||''''||l_rma_number||'''' ||
380 ' and soh.org_id = '||l_operating_id ||
381 ' and sol.header_id = soh.header_id ' ||
382 ' and sol.line_category_code = ''RETURN'' ' ||
383 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
384 ' and rt.oe_order_header_id = soh.header_id ' ||
385 ' and rt.oe_order_line_id = sol.line_id ' ||
386 ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
387
388
389 p_sql(26) := ' select mtlt.* ' ||
390 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
391 ' oe_order_headers_all soh , rcv_transactions rt ' ||
392 ' where soh.order_number = '||''''||l_rma_number||'''' ||
393 ' and soh.org_id = '||l_operating_id ||
394 ' and sol.header_id = soh.header_id ' ||
395 ' and sol.line_category_code = ''RETURN'' ' ||
396 ' and rt.oe_order_header_id = soh.header_id ' ||
397 ' and rt.oe_order_line_id = sol.line_id ' ||
398 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
399 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
400
401 p_sql(27) := ' select rlt.* ' ||
402 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
403 ' where soh.order_number ='||''''||l_rma_number||'''' ||
404 ' and soh.org_id = '||l_operating_id ||
405 ' and rsl.oe_order_header_id = soh.header_id ' ||
406 ' and rsl.oe_order_line_id = sol.line_id ' ||
407 ' and sol.header_id = soh.header_id ' ||
408 ' and sol.line_category_code = ''RETURN'' ' ||
409 ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num ';
410
411 p_sql(28) := ' SELECT DISTINCT rp.* ' ||
412 ' FROM rcv_parameters rp, oe_order_headers_all soh , oe_order_lines_all sol ' ||
413 ' where soh.order_number ='||''''||l_rma_number||'''' ||
414 ' and soh.org_id = '||l_operating_id ||
415 ' and soh.header_id = sol.header_id ' ||
416 ' and sol.ship_from_org_id = rp.organization_id ' ;
417
418 p_sql(29) := ' SELECT DISTINCT psp.* ' ||
419 ' FROM po_system_parameters_all psp, oe_order_headers_all soh ' ||
420 ' where soh.order_number ='||''''||l_rma_number||'''' ||
421 ' and soh.org_id = '||l_operating_id ||
422 ' and soh.org_id = psp.org_id ' ;
423
424 p_sql(30) := ' SELECT fsp.* ' ||
425 ' FROM financials_system_params_all fsp, oe_order_headers_all soh ' ||
426 ' where fsp.org_id = soh.org_id ' ||
427 ' and soh.order_number ='||''''||l_rma_number||'''' ||
428 ' and soh.org_id = '||l_operating_id ;
429
430
431 RETURN;
432 END;
433
434 PROCEDURE rma_line_sql(p_operating_id IN NUMBER,p_rma_number IN VARCHAR2,
435 p_line_num IN NUMBER, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
436
437 l_operating_id rcv_shipment_headers.receipt_num%TYPE := p_operating_id;
438 l_rma_number rcv_shipment_headers.organization_id%TYPE := p_rma_number;
439 l_line_num oe_order_lines_all.line_number%TYPE := p_line_num ;
440
441 BEGIN
442
443 p_sql(1) := ' select soh.* ' ||
444 ' from oe_order_headers_all soh ' ||
445 ' where soh.order_number ='||''''||l_rma_number||'''' ||
446 ' and soh.org_id = '||l_operating_id ||
447 ' and exists (' ||
448 ' select 1 ' ||
449 ' from oe_order_lines_all sol ' ||
450 ' where sol.line_category_code = ''RETURN'' ' ||
451 ' and sol.header_id = soh.header_id ) ';
452
453
454 p_sql(2) := ' select sol.* ' ||
455 ' from oe_order_lines_all sol , oe_order_headers_all soh ' ||
456 ' where soh.order_number ='||''''||l_rma_number||'''' ||
457 ' and soh.org_id = '||l_operating_id ||
458 ' and sol.line_number = '|| l_line_num ||
459 ' and sol.line_category_code = ''RETURN''' ||
460 ' and sol.header_id = soh.header_id ';
461
462
463 p_sql(3) := ' select distinct msi.* ' ||
464 ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
465 ' where soh.order_number ='||''''||l_rma_number||'''' ||
466 ' and soh.org_id = '||l_operating_id ||
467 ' and sol.line_number = '|| l_line_num ||
468 ' and sol.line_category_code = ''RETURN'' ' ||
469 ' and sol.header_id = soh.header_id ' ||
470 ' and msi.inventory_item_id = sol.inventory_item_id ' ||
471 ' and msi.organization_id = sol.ship_from_org_id ';
472
473
474
475 p_sql(4) := ' select rsh.* ' ||
476 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
477 ' where soh.order_number = '||''''||l_rma_number||'''' ||
478 ' and rsl.oe_order_header_id = soh.header_id ' ||
479 ' and rsl.oe_order_line_id = sol.line_id ' ||
480 ' and sol.line_number = '|| l_line_num ||
481 ' and sol.header_id = soh.header_id ' ||
482 ' and sol.line_category_code = ''RETURN'' ' ||
483 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
484 ' and soh.org_id ='|| l_operating_id ||
485 ' union all' ||
486 ' select rsh.* '||
487 'from rcv_shipment_headers rsh, oe_order_headers_all soh , oe_order_lines_all sol,rcv_transactions_interface rti '||
491 ' and rti.oe_order_line_id = sol.line_id' ||
488 ' WHERE rsh.shipment_header_id = rti.shipment_header_id'||
489 ' AND rti.oe_order_header_id = soh.header_id' ||
490 ' and soh.header_id = sol.header_id'||
492 ' and soh.order_number = '||''''||l_rma_number||'''' ||
493 ' and sol.line_number = '|| l_line_num ||
494 ' and soh.org_id ='|| l_operating_id ;
495
496
497 p_sql(5) := ' select rsl.* ' ||
498 ' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
499 ' where soh.order_number ='||''''||l_rma_number||'''' ||
500 ' and soh.org_id = '||l_operating_id ||
501 ' and sol.line_number = '|| l_line_num ||
502 ' and rsl.oe_order_header_id = soh.header_id ' ||
503 ' and rsl.oe_order_line_id = sol.line_id' ||
504 ' and sol.header_id = soh.header_id ' ||
505 ' and sol.line_category_code = ''RETURN'' ';
506
507
508 p_sql(6) := ' select rt.* ' ||
509 ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
510 ' where soh.order_number ='||''''||l_rma_number||'''' ||
511 ' and soh.org_id = '||l_operating_id ||
512 ' and sol.line_number = '|| l_line_num ||
513 ' and rt.oe_order_header_id = soh.header_id ' ||
514 ' and rt.oe_order_line_id = sol.line_id ' ||
515 ' and sol.header_id = soh.header_id ' ||
516 ' and sol.line_category_code = ''RETURN'' ';
517
518
519
520
521 p_sql(7) := ' select rhi.* ' ||
522 ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
523 ||
524 ' where soh.order_number ='||''''||l_rma_number||'''' ||
525 ' and soh.org_id = '||l_operating_id ||
526 ' and sol.line_number = '|| l_line_num ||
527 ' and (rti.oe_order_header_id = soh.header_id ' ||
528 ' or rti.oe_order_line_id = sol.line_id ) ' ||
529 ' and sol.header_id = soh.header_id ' ||
530 ' and sol.line_category_code = ''RETURN'' ' ||
531 ' and rhi.header_interface_id = rti.header_interface_id ';
532
533
534 p_sql(8) := ' select rti.* ' ||
535 ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||
536 ' where soh.order_number ='||''''||l_rma_number||'''' ||
537 ' and soh.org_id = '||l_operating_id ||
538 ' and sol.line_number = '|| l_line_num ||
539 ' and (rti.oe_order_header_id = soh.header_id ' ||
540 ' or rti.oe_order_line_id = sol.line_id ) ' ||
541 ' and sol.header_id = soh.header_id' ||
542 ' and sol.line_category_code = ''RETURN'' ';
543
544
545 p_sql(9) := ' select pie.* ' ||
546 ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
547 ' where soh.order_number ='||''''||l_rma_number||'''' ||
548 ' and soh.org_id = '||l_operating_id ||
549 ' and sol.line_number = '|| l_line_num ||
550 ' and sol.header_id = soh.header_id' ||
551 ' and sol.line_category_code = ''RETURN'' ' ||
552 ' and (exists (' ||
553 ' select 1 ' ||
554 ' from rcv_transactions_interface rti ' ||
555 ' where (rti.oe_order_header_id = soh.header_id ' ||
556 ' or rti.oe_order_line_id = sol.line_id ) ' ||
557 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
558 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
559 ' or exists (' ||
560 ' select 2 ' ||
561 ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
562 ' where (rti.oe_order_header_id = soh.header_id ' ||
563 ' or rti.oe_order_line_id = sol.line_id ) ' ||
564 ' and rhi.header_interface_id = rti.header_interface_id ' ||
565 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
566 ' and pie.interface_header_id = rhi.header_interface_id) ) ';
567
568 p_sql(10) := ' select distinct ood.* ' ||
569 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
570 ||
571 ' where soh.order_number ='||''''||l_rma_number||'''' ||
572 ' and soh.org_id = '||l_operating_id ||
573 ' and sol.line_number = '|| l_line_num ||
574 ' and rsl.oe_order_header_id = soh.header_id ' ||
575 ' and rsl.oe_order_line_id = sol.line_id ' ||
576 ' and sol.header_id = soh.header_id' ||
577 ' and sol.line_category_code = ''RETURN'' ' ||
578 ' and ood.organization_id = rsl.to_organization_id ';
579
580
581 p_sql(11) := ' select distinct mp.* ' ||
582 ' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
583 ' where soh.order_number ='||''''||l_rma_number||'''' ||
584 ' and soh.org_id = '||l_operating_id ||
585 ' and rsl.oe_order_header_id = soh.header_id ' ||
586 ' and rsl.oe_order_line_id = sol.line_id' ||
587 ' and sol.line_number = '|| l_line_num ||
588 ' and sol.header_id = soh.header_id ' ||
589 ' and sol.line_category_code = ''RETURN'' ' ||
590 ' and mp.organization_id = rsl.to_organization_id ';
591
592
593
594 p_sql(12) := ' select mmt.* ' ||
595 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||
596 ' where soh.order_number ='||''''||l_rma_number||'''' ||
597 ' and soh.org_id = '||l_operating_id ||
598 ' and sol.header_id = soh.header_id' ||
599 ' and sol.line_number = '|| l_line_num ||
600 ' and sol.line_category_code = ''RETURN'' ' ||
601 ' and rt.oe_order_header_id = soh.header_id ' ||
602 ' and rt.oe_order_line_id = sol.line_id ' ||
603 ' and mmt.rcv_transaction_id = rt.transaction_id ';
604
605
606
607 p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'
608 ||
609 ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
610 ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
614 ' and soh.org_id = '||l_operating_id ||
611 ||
612 ' rcv_transactions rt ' ||
613 ' where soh.order_number ='||''''||l_rma_number||'''' ||
615 ' and sol.header_id = soh.header_id ' ||
616 ' and sol.line_category_code = ''RETURN'' ' ||
617 ' and rt.oe_order_header_id = soh.header_id ' ||
618 ' and rt.oe_order_line_id = sol.line_id' ||
619 ' and sol.line_number = '|| l_line_num ||
620 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
621 ' and mtt.transaction_type_id = mmt.transaction_type_id ';
622
623
624 /*p_sql(14) := ' select distinct mtrl.* ' ||
625 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
626 ' where soh.order_number ='||''''||l_rma_number||'''' ||
627 ' and soh.org_id = '||l_operating_id ||
628 ' and rt.oe_order_header_id = soh.header_id ' ||
629 ' and rt.oe_order_line_id = sol.line_id ' ||
630 ' and sol.header_id = soh.header_id' ||
631 ' and sol.line_number = '|| l_line_num ||
632 ' and sol.line_category_code = ''RETURN'' ' ||
633 ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
634 ' and mtrl.reference_id = rt.oe_order_line_id ' ;*/
635
636 p_sql(14) := ' select distinct mtrl.* ' ||
637 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
638 ' where soh.order_number ='||''''||l_rma_number||'''' ||
639 ' and rt.oe_order_header_id = soh.header_id ' ||
640 ' and rt.oe_order_line_id = sol.line_id ' ||
641 ' and sol.header_id = soh.header_id' ||
642 ' and sol.line_number = '|| l_line_num ||
643 ' and sol.line_category_code = ''RETURN'' ' ||
644 ' and soh.org_id ='|| l_operating_id||
645 ' AND mtrl.organization_id=rt.organization_id'||
646 ' AND mtrl.inventory_item_id = sol.inventory_item_id ' ||
647 ' and Nvl(mtrl.revision,0)=Nvl(sol.item_revision,0) '||'and mtrl.line_status=7'||
648 ' and mtrl.transaction_type_id=15';
649
650 p_sql(15) := ' select mmtt.* ' ||
651 ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||
652 ' where soh.order_number ='||''''||l_rma_number||'''' ||
653 ' and soh.org_id = '||l_operating_id ||
654 ' and sol.header_id = soh.header_id' ||
655 ' and sol.line_number = '|| l_line_num ||
656 ' and sol.line_category_code = ''RETURN'' ' ||
657 ' and exists (' ||
658 ' select 1 ' ||
659 ' from rcv_transactions rt ' ||
660 ' where rt.oe_order_header_id = soh.header_id ' ||
661 ' and rt.oe_order_line_id = sol.line_id ' ||
662 ' and mmtt.rcv_transaction_id = rt.transaction_id) ';
663
664 p_sql(16) := ' select lsn.* ' ||
665 ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
666 ' where soh.order_number ='||''''||l_rma_number||'''' ||
667 ' and soh.org_id = '||l_operating_id ||
668 ' and sol.line_category_code = ''RETURN'' ' ||
669 ' and sol.header_id = soh.header_id' ||
670 ' and sol.line_number = '|| l_line_num ||
671 ' and lsn.line_id = sol.line_id order by sol.line_id desc , lsn.lot_serial_id ';
672
673
674 p_sql(17) := ' select distinct msn.* ' ||
675 ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
676 ' where soh.order_number ='||''''||l_rma_number||'''' ||
677 ' and soh.org_id = '||l_operating_id ||
678 ' and sol.header_id = soh.header_id' ||
679 ' and sol.line_number = '|| l_line_num ||
680 ' and sol.line_category_code = ''RETURN'' ' ||
681 ' and (exists (' ||
682 ' select 1 ' ||
683 ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
684 ' where rt.oe_order_header_id = soh.header_id ' ||
685 ' and rt.oe_order_line_id = sol.line_id ' ||
686 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
687 ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
688 ' or exists (' ||
689 ' select 3 ' ||
690 ' from mtl_material_transactions ommt ' ||
691 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
692 ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
693 ' or exists (' ||
694 ' select 2 ' ||
695 ' from oe_lot_serial_numbers lsn ' ||
696 ' where lsn.line_id = sol.line_id ' ||
697 ' and sol.ordered_item_id = msn.inventory_item_id ' ||
698 ' and sol.ship_from_org_id = msn.current_organization_id ' ||
699 ' and msn.serial_number between lsn.from_serial_number and nvl(lsn.to_serial_number ,
700 lsn.from_serial_number)' ||
701 ' ) ) order by msn.inventory_item_id , msn.serial_number ';
702
703
704
705 p_sql(18) := ' select msnt.* ' ||
706 ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
707 ' oe_order_headers_all soh , rcv_transactions rt ' ||
708 ' where soh.order_number ='||''''||l_rma_number||'''' ||
709 ' and soh.org_id = '||l_operating_id ||
710 ' and sol.header_id = soh.header_id' ||
711 ' and sol.line_number = '|| l_line_num ||
712 ' and sol.line_category_code = ''RETURN'' ' ||
713 ' and rt.oe_order_header_id = soh.header_id ' ||
714 ' and rt.oe_order_line_id = sol.line_id ' ||
715 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
716 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
717 ' or exists (' ||
718 ' select 2 ' ||
719 ' from mtl_transaction_lots_temp mtlt ' ||
720 ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
721 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
722
723 p_sql(19) := ' select msni.* ' || ' from
724 mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh , mtl_serial_numbers_interface' || '
725 msni , rcv_transactions rt ' ||
726 ' where soh.order_number ='||''''||l_rma_number||'''' ||
730 ' and rt.oe_order_header_id = soh.header_id ' ||
727 ' and soh.org_id = '||l_operating_id ||
728 ' and sol.header_id = soh.header_id ' ||
729 ' and sol.line_category_code = ''RETURN'' ' ||
731 ' and rt.oe_order_line_id = sol.line_id ' ||
732 ' and sol.line_number = '|| l_line_num ||
733 ' and mti.rcv_transaction_id = rt.transaction_id ' ||
734 ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
735 ' or exists (' ||
736 ' select 3 ' ||
737 ' from mtl_transaction_lots_interface mtln ' ||
738 ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
739 ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
740
741 p_sql(20) := ' select mut.* ' ||
742 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||
743 ' mtl_unit_transactions mut ' ||
744 ' where soh.order_number ='||''''||l_rma_number||'''' ||
745 ' and soh.org_id = '||l_operating_id ||
746 ' and sol.header_id = soh.header_id ' ||
747 ' and sol.line_category_code = ''RETURN'' ' ||
748 ' and rt.oe_order_header_id = soh.header_id ' ||
749 ' and rt.oe_order_line_id = sol.line_id' ||
750 ' and sol.line_number = '|| l_line_num ||
751 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
752 ' and (mut.transaction_id = mmt.transaction_id ' ||
753 ' or exists (' ||
754 ' select 1 ' ||
755 ' from mtl_transaction_lot_numbers mtln ' ||
756 ' where mtln.transaction_id = mmt.transaction_id ' ||
757 ' and mut.transaction_id = mtln.serial_transaction_id ) ) ';
758
759
760
761
762 p_sql(21) := ' select rst.* ' ||
763 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
764 ' where soh.order_number ='||''''||l_rma_number||'''' ||
765 ' and soh.org_id = '||l_operating_id ||
766 ' and rsl.oe_order_header_id = soh.header_id ' ||
767 ' and rsl.oe_order_line_id = sol.line_id ' ||
768 ' and sol.header_id = soh.header_id ' ||
769 ' and sol.line_number = '|| l_line_num ||
770 ' and sol.line_category_code = ''RETURN'' ' ||
771 ' and rst.shipment_line_id = rsl.shipment_line_id ';
772
773
774
775 p_sql(22) := ' select distinct rsi.* ' ||
776 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
777 ' where soh.order_number ='||''''||l_rma_number||'''' ||
778 ' and soh.org_id = '||l_operating_id ||
779 ' and rsl.oe_order_header_id = soh.header_id ' ||
780 ' and rsl.oe_order_line_id = sol.line_id ' ||
781 ' and sol.header_id = soh.header_id' ||
782 ' and sol.line_number = '|| l_line_num ||
783 ' and sol.line_category_code = ''RETURN'' ' ||
784 ' and rsi.item_id = rsl.item_id ' ||
785 ' and rsi.organization_id = rsl.to_organization_id ';
786
787
788 p_sql(23) := ' select distinct mln.* ' ||
789 ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
790 ' where soh.order_number ='||''''||l_rma_number||'''' ||
791 ' and soh.org_id = '||l_operating_id ||
792 ' and sol.line_number = '|| l_line_num ||
793 ' and sol.header_id = soh.header_id ' ||
794 ' and sol.line_category_code = ''RETURN'' ' ||
795 ' and (exists (' ||
796 ' select 1 ' ||
797 ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
798 ' where rt.oe_order_header_id = soh.header_id ' ||
799 ' and rt.oe_order_line_id = sol.line_id ' ||
800 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
801 ' and mmt.transaction_id = mtln.transaction_id ' ||
802 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
803 ' and mln.organization_id = mmt.organization_id ' ||
804 ' and mln.lot_number = mtln.lot_number ) ' ||
805 ' or exists (' ||
806 ' select 2 ' ||
807 ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
808 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
809 ' and ommt.transaction_id = mtln.transaction_id ' ||
810 ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
811 ' and mln.organization_id = ommt.organization_id ' ||
812 ' and mln.lot_number = mtln.lot_number ) ' ||
813 ' or exists (' ||
814 ' select 3 ' ||
815 ' from oe_lot_serial_numbers lsn ' ||
816 ' where lsn.line_id = sol.line_id ' ||
817 ' and sol.ordered_item_id = mln.inventory_item_id ' ||
818 ' and sol.ship_from_org_id = mln.organization_id ' ||
819 ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
820 ' mln.lot_number ';
821
822
823 p_sql(24) := ' select mtln.* ' ||
824 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
825 ||
826 ' soh ' ||
827 ' where soh.order_number ='||''''||l_rma_number||'''' ||
828 ' and soh.org_id = '||l_operating_id ||
829 ' and sol.line_number = '|| l_line_num ||
830 ' and sol.header_id = soh.header_id ' ||
831 ' and sol.line_category_code = ''RETURN'' ' ||
832 ' and mmt.transaction_id = mtln.transaction_id ' ||
833 ' and (exists (' ||
834 ' select 1 ' ||
835 ' from rcv_transactions rt ' ||
836 ' where rt.oe_order_line_id = sol.line_id ' ||
837 ' and mmt.rcv_transaction_id = rt.transaction_id ) ' ||
838 ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
839 ,' ||
840 ' mtln.transaction_id ';
841
842
843
844 p_sql(25):= ' select mtli.* ' ||
845 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||
849 ' and sol.line_number = '|| l_line_num ||
846 ' oe_order_headers_all soh , rcv_transactions rt ' ||
847 ' where soh.order_number = '||''''||l_rma_number||'''' ||
848 ' and soh.org_id = '||l_operating_id ||
850 ' and sol.header_id = soh.header_id ' ||
851 ' and sol.line_category_code = ''RETURN'' ' ||
852 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
853 ' and rt.oe_order_header_id = soh.header_id ' ||
854 ' and rt.oe_order_line_id = sol.line_id ' ||
855 ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
856
857
858 p_sql(26) := ' select mtlt.* ' ||
859 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
860 ' oe_order_headers_all soh , rcv_transactions rt ' ||
861 ' where soh.order_number = '||''''||l_rma_number||'''' ||
862 ' and soh.org_id = '||l_operating_id ||
863 ' and sol.line_number = '|| l_line_num ||
864 ' and sol.header_id = soh.header_id ' ||
865 ' and sol.line_category_code = ''RETURN'' ' ||
866 ' and rt.oe_order_header_id = soh.header_id ' ||
867 ' and rt.oe_order_line_id = sol.line_id ' ||
868 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
869 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
870
871 p_sql(27) := ' select rlt.* ' ||
872 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
873 ' where soh.order_number ='||''''||l_rma_number||'''' ||
874 ' and soh.org_id = '||l_operating_id ||
875 ' and sol.line_number = '|| l_line_num ||
876 ' and rsl.oe_order_header_id = soh.header_id ' ||
877 ' and rsl.oe_order_line_id = sol.line_id ' ||
878 ' and sol.header_id = soh.header_id ' ||
879 ' and sol.line_category_code = ''RETURN'' ' ||
880 ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num ';
881
882 p_sql(28) := ' SELECT DISTINCT rp.* ' ||
883 ' FROM rcv_parameters rp, oe_order_headers_all soh, oe_order_lines_all sol ' ||
884 ' where soh.order_number ='||''''||l_rma_number||'''' ||
885 ' and soh.org_id = '||l_operating_id ||
886 ' and soh.header_id = sol.header_id ' ||
887 ' and sol.ship_from_org_id = rp.organization_id ' ;
888
889 p_sql(29) := ' SELECT DISTINCT psp.* ' ||
890 ' FROM po_system_parameters_all psp, oe_order_headers_all soh ' ||
891 ' where soh.order_number ='||''''||l_rma_number||'''' ||
892 ' and soh.org_id = '||l_operating_id ||
893 ' and soh.org_id = psp.org_id ' ;
894
895 p_sql(30) := ' SELECT fsp.* ' ||
896 ' FROM financials_system_params_all fsp, oe_order_headers_all soh ' ||
897 ' where fsp.org_id = soh.org_id ' ||
898 ' and soh.order_number ='||''''||l_rma_number||'''' ||
899 ' and soh.org_id = '||l_operating_id ;
900
901 RETURN;
902 END;
903
904 PROCEDURE rma_receipt_sql(p_operating_id IN NUMBER,p_rma_number IN VARCHAR2,
905 p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
906 INV_DIAG_RCV_PO_COMMON.sqls_list) IS
907
908 l_operating_id rcv_shipment_headers.receipt_num%TYPE := p_operating_id;
909 l_rma_number rcv_shipment_headers.organization_id%TYPE := p_rma_number;
910 l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
911 l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
912
913 BEGIN
914
915 p_sql(1) := 'select distinct soh.* ' ||
916 ' from oe_order_headers_all soh, oe_order_lines_all sol' ||
917 ' where soh.order_number ='||''''||l_rma_number||'''' ||
918 ' and soh.org_id = '||l_operating_id ||
919 ' and exists (' ||
920 ' select 1 ' ||
921 ' from oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
922 ' where sol.line_category_code = ''RETURN'' ' ||
923 ' and sol.header_id = soh.header_id '||
924 ' AND sol.line_id = rsl.oe_order_line_id' ||
925 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
926 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
927 ' and rsh.organization_id ='|| l_organization_id ||
928 ')' ;
929
930
931
932
933 p_sql(2) := ' select sol.* ' ||
934 ' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||
935 ' rcv_shipment_lines rsl ' ||
936 ' where soh.order_number ='||''''||l_rma_number||'''' ||
937 ' and soh.org_id = '||l_operating_id ||
938 ' and sol.line_category_code = ''RETURN'' ' ||
939 ' and sol.header_id = soh.header_id' ||
940 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
941 ' and rsl.oe_order_line_id = sol.line_id' ||
942 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
943 ' and rsh.organization_id ='|| l_organization_id ;
944
945
946
947 p_sql(3) := ' select distinct msi.* ' ||
948 ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
949 ' where soh.order_number ='||''''||l_rma_number||'''' ||
950 ' and soh.org_id = '||l_operating_id ||
951 ' and sol.line_category_code = ''RETURN'' ' ||
952 ' and sol.header_id = soh.header_id ' ||
953 ' and msi.inventory_item_id = sol.inventory_item_id ' ||
954 ' and msi.organization_id = sol.ship_from_org_id ';
955
956
957 p_sql(4) := ' select rsh.* ' ||
958 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
959 ' where soh.order_number ='||''''||l_rma_number||'''' ||
960 ' and soh.org_id = '||l_operating_id ||
961 ' and rsl.oe_order_header_id = soh.header_id ' ||
962 ' and rsl.oe_order_line_id = sol.line_id ' ||
966 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
963 ' and sol.header_id = soh.header_id ' ||
964 ' and sol.line_category_code = ''RETURN'' ' ||
965 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
967 ' and rsh.organization_id = '||l_organization_id ;
968
969 p_sql(5) := ' select rsl.* ' ||
970 ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
971 ' where soh.order_number ='||''''||l_rma_number||'''' ||
972 ' and soh.org_id = '||l_operating_id ||
973 ' and rsl.oe_order_header_id = soh.header_id ' ||
974 ' and rsl.oe_order_line_id = sol.line_id ' ||
975 ' and sol.header_id = soh.header_id ' ||
976 ' and sol.line_category_code = ''RETURN''' ||
977 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
978 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
979 ' and rsh.organization_id = ' || l_organization_id ;
980
981
982
983 p_sql(6) := ' select rt.* ' ||
984 ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
985 ' where soh.order_number ='||''''||l_rma_number||'''' ||
986 ' and soh.org_id = '||l_operating_id ||
987 ' and rt.oe_order_header_id = soh.header_id ' ||
988 ' and rt.oe_order_line_id = sol.line_id ' ||
989 ' and sol.header_id = soh.header_id' ||
990 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
991 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
992 ' and sol.line_category_code = ''RETURN'' ' ||
993 ' and rsh.organization_id = ' || l_organization_id ;
994
995
996
997
998 p_sql(7) := ' select rhi.* ' ||
999 ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
1000 ||
1001 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1002 ' and soh.org_id = '||l_operating_id ||
1003 ' and (rti.oe_order_header_id = soh.header_id ' ||
1004 ' or rti.oe_order_line_id = sol.line_id ) ' ||
1005 ' and sol.header_id = soh.header_id ' ||
1006 ' and sol.line_category_code = ''RETURN'' ' ||
1007 ' and rhi.header_interface_id = rti.header_interface_id ';
1008
1009
1010 p_sql(8) := ' select rti.* ' ||
1011 ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
1012 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1013 ' and soh.org_id = '||l_operating_id ||
1014 ' and (rti.oe_order_header_id = soh.header_id ' ||
1015 ' or rti.oe_order_line_id = sol.line_id ) ' ||
1016 ' and sol.header_id = soh.header_id' ||
1017 ' and rti.shipment_header_id = rsh.shipment_header_id' ||
1018 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1019 ' and sol.line_category_code = ''RETURN'' ' ||
1020 ' and rsh.organization_id = ' || l_organization_id ;
1021
1022
1023
1024
1025 p_sql(9) := ' select pie.* ' ||
1026 ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
1027 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1028 ' and soh.org_id = '||l_operating_id ||
1029 ' and sol.header_id = soh.header_id ' ||
1030 ' and sol.line_category_code = ''RETURN'' ' ||
1031 ' and (exists (' ||
1032 ' select 1 ' ||
1033 ' from rcv_transactions_interface rti ' ||
1034 ' where (rti.oe_order_header_id = soh.header_id ' ||
1035 ' or rti.oe_order_line_id = sol.line_id ) ' ||
1036 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
1037 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
1038 ' or exists (' ||
1039 ' select 2 ' ||
1040 ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
1041 ' where (rti.oe_order_header_id = soh.header_id ' ||
1042 ' or rti.oe_order_line_id = sol.line_id ) ' ||
1043 ' and rhi.header_interface_id = rti.header_interface_id ' ||
1044 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
1045 ' and pie.interface_header_id = rhi.header_interface_id) ) ';
1046
1047 p_sql(10) := ' select distinct ood.* ' ||
1048 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
1049 ||
1050 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1051 ' and soh.org_id = '||l_operating_id ||
1052 ' and rsl.oe_order_header_id = soh.header_id ' ||
1053 ' and rsl.oe_order_line_id = sol.line_id ' ||
1054 ' and sol.header_id = soh.header_id ' ||
1055 ' and sol.line_category_code = ''RETURN'' ' ||
1056 ' and ood.organization_id = rsl.to_organization_id ';
1057
1058
1059 p_sql(11) := ' select distinct ood.* ' ||
1060 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
1061 ||
1062 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1063 ' and soh.org_id = '||l_operating_id ||
1064 ' and rsl.oe_order_header_id = soh.header_id ' ||
1065 ' and rsl.oe_order_line_id = sol.line_id ' ||
1066 ' and sol.header_id = soh.header_id ' ||
1067 ' and sol.line_category_code = ''RETURN'' ' ||
1068 ' and ood.organization_id = rsl.to_organization_id ';
1069
1070
1071 p_sql(12) := ' select mmt.* ' ||
1072 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
1073 rcv_shipment_headers rsh' ||
1074 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1075 ' and soh.org_id = '||l_operating_id ||
1076 ' and sol.header_id = soh.header_id ' ||
1077 ' and sol.line_category_code = ''RETURN'' ' ||
1078 ' and rt.oe_order_header_id = soh.header_id ' ||
1079 ' and rt.oe_order_line_id = sol.line_id' ||
1080 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1081 ' and rsh.organization_id = '|| l_organization_id ||
1085
1082 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
1083 ' and mmt.rcv_transaction_id = rt.transaction_id ';
1084
1086 p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'||
1087 ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
1088 ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
1089 ||
1090 ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
1091 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1092 ' and soh.org_id = '||l_operating_id ||
1093 ' and sol.header_id = soh.header_id ' ||
1094 ' and sol.line_category_code = ''RETURN'' ' ||
1095 ' and rt.oe_order_header_id = soh.header_id ' ||
1096 ' and rt.oe_order_line_id = sol.line_id ' ||
1097 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
1098 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1099 ' and rsh.organization_id = '|| l_organization_id ||
1100 ' and rt.shipment_header_id = rt.shipment_header_id' ||
1101 ' and mtt.transaction_type_id = mmt.transaction_type_id ';
1102
1103
1104
1105 /*p_sql(14) := 'select distinct mtrl.* ' ||
1106 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
1107 rcv_shipment_headers rsh ' ||
1108 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1109 ' and rt.oe_order_header_id = soh.header_id ' ||
1110 ' and rt.oe_order_line_id = sol.line_id ' ||
1111 ' and sol.header_id = soh.header_id ' ||
1112 ' and sol.line_category_code = ''RETURN'' ' ||
1113 ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
1114 ' and mtrl.reference_id = rt.oe_order_line_id ' ||
1115 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1116 ' and rsh.organization_id = '|| l_organization_id ||
1117 ' and rt.shipment_header_id = rt.shipment_header_id' ||
1118 ' and soh.org_id = 2222 ';*/
1119
1120 p_sql(14) := 'select distinct mtrl.* ' ||
1121 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
1122 rcv_shipment_headers rsh ' ||
1123 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1124 ' and rt.oe_order_header_id = soh.header_id ' ||
1125 ' and rt.oe_order_line_id = sol.line_id ' ||
1126 ' and sol.header_id = soh.header_id ' ||
1127 ' and sol.line_category_code = ''RETURN'' ' ||
1128 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1129 ' and rsh.organization_id = '|| l_organization_id ||
1130 ' and rt.shipment_header_id = rt.shipment_header_id' ||
1131 ' and soh.org_id ='|| l_operating_id||
1132 ' AND mtrl.organization_id=rt.organization_id'||
1133 ' AND mtrl.inventory_item_id = sol.inventory_item_id ' ||
1134 ' and mtrl.revision=sol.item_revision ' ||' and mtrl.line_status=7'||
1135 ' and mtrl.transaction_type_id=15';
1136
1137
1138
1139 p_sql(15) := ' select mmtt.* ' ||
1140 ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh
1141 ' ||
1142 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1143 ' and soh.org_id = '||l_operating_id ||
1144 ' and sol.header_id = soh.header_id ' ||
1145 ' and sol.line_category_code = ''RETURN'' ' ||
1146 ' and exists (' ||
1147 ' select 1 ' ||
1148 ' from rcv_transactions rt ' ||
1149 ' where rt.oe_order_header_id = soh.header_id ' ||
1150 ' and rt.oe_order_line_id = sol.line_id ' ||
1151 ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
1152 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1153 ' and rsh.organization_id = '|| l_organization_id ||
1154 ' and rt.shipment_header_id = rsh.shipment_header_id) ';
1155
1156 p_sql(16) := ' select lsn.* ' ||
1157 ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
1158 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
1159 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1160 ' and soh.org_id = '||l_operating_id ||
1161 ' and sol.line_category_code = ''RETURN'' ' ||
1162 ' and sol.header_id = soh.header_id ' ||
1163 ' and lsn.line_id = sol.line_id ' ||
1164 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
1165 ' and rsl.oe_order_line_id = sol.line_id' ||
1166 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1167 ' and rsh.organization_id = '|| l_organization_id ||
1168 ' order by sol.line_id desc , lsn.lot_serial_id ';
1169
1170
1171 p_sql(17) := ' select distinct msn.* ' ||
1172 ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
1173 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
1174 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1175 ' and soh.org_id = '||l_operating_id ||
1176 ' and sol.header_id = soh.header_id ' ||
1177 ' and sol.line_category_code = ''RETURN'' ' ||
1178 ' and rsl.oe_order_line_id = sol.line_id' ||
1179 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
1180 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1181 ' and rsh.organization_id = '|| l_organization_id ||
1182 ' and (exists (' ||
1183 ' select 1 ' ||
1184 ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
1185 ' where rt.oe_order_header_id = soh.header_id ' ||
1186 ' and rt.oe_order_line_id = sol.line_id ' ||
1187 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1188 ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
1189 ' or exists (' ||
1190 ' select 3 ' ||
1191 ' from mtl_material_transactions ommt ' ||
1192 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
1193 ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
1194 ' or exists (' ||
1195 ' select 2 ' ||
1196 ' from oe_lot_serial_numbers lsn ' ||
1200 ' and msn.serial_number between lsn.from_serial_number and nvl(lsn.to_serial_number ,
1197 ' where lsn.line_id = sol.line_id ' ||
1198 ' and sol.ordered_item_id = msn.inventory_item_id ' ||
1199 ' and sol.ship_from_org_id = msn.current_organization_id ' ||
1201 lsn.from_serial_number)'
1202 ||
1203 ' ) ) order by msn.inventory_item_id , msn.serial_number ';
1204
1205
1206 p_sql(18) := ' select msnt.* ' ||
1207 ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
1208 ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
1209 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1210 ' and soh.org_id = '||l_operating_id ||
1211 ' and sol.header_id = soh.header_id ' ||
1212 ' and sol.line_category_code = ''RETURN'' ' ||
1213 ' and rt.oe_order_header_id = soh.header_id ' ||
1214 ' and rt.oe_order_line_id = sol.line_id ' ||
1215 ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
1216 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
1217 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1218 ' and rsh.organization_id = '|| l_organization_id ||
1219 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
1220 ' or exists (' ||
1221 ' select 2 ' ||
1222 ' from mtl_transaction_lots_temp mtlt ' ||
1223 ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
1224 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
1225
1226
1227 p_sql(19) := ' select msni.* ' ||
1228 ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
1229 ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
1230 ' rcv_shipment_headers rsh ' ||
1231 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1232 ' and soh.org_id = '||l_operating_id ||
1233 ' and sol.header_id = soh.header_id ' ||
1234 ' and sol.line_category_code = ''RETURN'' ' ||
1235 ' and rt.oe_order_header_id = soh.header_id ' ||
1236 ' and rt.oe_order_line_id = sol.line_id ' ||
1237 ' and mti.rcv_transaction_id = rt.transaction_id ' ||
1238 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
1239 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1240 ' and rsh.organization_id = '|| l_organization_id ||
1241 ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
1242 ' or exists (' ||
1243 ' select 3 ' ||
1244 ' from mtl_transaction_lots_interface mtln ' ||
1245 ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
1246 ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
1247
1248
1249 p_sql(20) := ' select mut.* ' ||
1250 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
1251 ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
1252 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1253 ' and soh.org_id = '||l_operating_id ||
1254 ' and sol.header_id = soh.header_id ' ||
1255 ' and sol.line_category_code = ''RETURN'' ' ||
1256 ' and rt.oe_order_header_id = soh.header_id ' ||
1257 ' and rt.oe_order_line_id = sol.line_id ' ||
1258 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1259 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
1260 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1261 ' and rsh.organization_id = '|| l_organization_id ||
1262 ' and (mut.transaction_id = mmt.transaction_id ' ||
1263 ' or exists (' ||
1264 ' select 1 ' ||
1265 ' from mtl_transaction_lot_numbers mtln ' ||
1266 ' where mtln.transaction_id = mmt.transaction_id ' ||
1267 ' and mut.transaction_id = mtln.serial_transaction_id ) ) ';
1268
1269
1270
1271 p_sql(21) := ' select rst.* ' ||
1272 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
1273 ' rcv_shipment_headers rsh' ||
1274 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1275 ' and soh.org_id = '||l_operating_id ||
1276 ' and rsl.oe_order_header_id = soh.header_id ' ||
1277 ' and rsl.oe_order_line_id = sol.line_id ' ||
1278 ' and sol.header_id = soh.header_id ' ||
1279 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1280 ' and rsh.organization_id = '|| l_organization_id ||
1281 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
1282 ' and sol.line_category_code = ''RETURN'' ' ||
1283 ' and rst.shipment_line_id = rsl.shipment_line_id ';
1284
1285
1286 p_sql(22) := ' select distinct rsi.* ' ||
1287 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
1288 ' rcv_shipment_headers rsh' ||
1289 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1290 ' and soh.org_id = '||l_operating_id ||
1291 ' and rsl.oe_order_header_id = soh.header_id ' ||
1292 ' and rsl.oe_order_line_id = sol.line_id ' ||
1293 ' and sol.header_id = soh.header_id ' ||
1294 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1295 ' and rsh.organization_id = '|| l_organization_id ||
1296 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
1297 ' and sol.line_category_code = ''RETURN'' ' ||
1298 ' and rsi.item_id = rsl.item_id ' ||
1299 ' and rsi.organization_id = rsl.to_organization_id ';
1300
1301
1302
1303 p_sql(23) := ' select distinct mln.* ' ||
1304 ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
1305 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1306 ' and soh.org_id = '||l_operating_id ||
1307 ' and sol.header_id = soh.header_id ' ||
1308 ' and sol.line_category_code = ''RETURN'' ' ||
1309 ' and (exists (' ||
1310 ' select 1 ' ||
1314 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1311 ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
1312 ' where rt.oe_order_header_id = soh.header_id ' ||
1313 ' and rt.oe_order_line_id = sol.line_id ' ||
1315 ' and mmt.transaction_id = mtln.transaction_id ' ||
1316 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
1317 ' and mln.organization_id = mmt.organization_id ' ||
1318 ' and mln.lot_number = mtln.lot_number ) ' ||
1319 ' or exists (' ||
1320 ' select 2 ' ||
1321 ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
1322 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
1323 ' and ommt.transaction_id = mtln.transaction_id ' ||
1324 ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
1325 ' and mln.organization_id = ommt.organization_id ' ||
1326 ' and mln.lot_number = mtln.lot_number ) ' ||
1327 ' or exists (' ||
1328 ' select 3 ' ||
1329 ' from oe_lot_serial_numbers lsn ' ||
1330 ' where lsn.line_id = sol.line_id ' ||
1331 ' and sol.ordered_item_id = mln.inventory_item_id ' ||
1332 ' and sol.ship_from_org_id = mln.organization_id ' ||
1333 ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
1334 ' mln.lot_number ';
1335
1336
1337 p_sql(24) := ' select mtln.* ' ||
1338 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
1339 ||
1340 ' soh ' ||
1341 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1342 ' and soh.org_id = '||l_operating_id ||
1343 ' and sol.header_id = soh.header_id ' ||
1344 ' and sol.line_category_code = ''RETURN'' ' ||
1345 ' and mmt.transaction_id = mtln.transaction_id ' ||
1346 ' and (exists (' ||
1347 ' select 1 ' ||
1348 ' from rcv_transactions rt, rcv_shipment_headers rsh ' ||
1349 ' where rt.oe_order_line_id = sol.line_id ' ||
1350 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
1351 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
1352 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1353 ' and rsh.organization_id = '|| l_organization_id ||
1354 ' ) ' ||
1355 ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
1356 ,' ||
1357 ' mtln.transaction_id ';
1358
1359
1360
1361
1362 p_sql(25):= ' select mtli.* ' ||
1363 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
1364 ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
1365 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1366 ' and soh.org_id = '||l_operating_id ||
1367 ' and sol.header_id = soh.header_id ' ||
1368 ' and sol.line_category_code = ''RETURN'' ' ||
1369 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
1370 ' and rt.oe_order_header_id = soh.header_id ' ||
1371 ' and rt.oe_order_line_id = sol.line_id ' ||
1372 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1373 ' and rsh.organization_id = '|| l_organization_id ||
1374 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
1375 ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
1376
1377
1378 p_sql(26) := ' select mtlt.* ' ||
1379 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
1380 ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
1381 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1382 ' and soh.org_id = '||l_operating_id ||
1383 ' and sol.header_id = soh.header_id ' ||
1384 ' and sol.line_category_code = ''RETURN'' ' ||
1385 ' and rt.oe_order_header_id = soh.header_id ' ||
1386 ' and rt.oe_order_line_id = sol.line_id ' ||
1387 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
1388 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1389 ' and rsh.organization_id = '|| l_organization_id ||
1390 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
1391 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
1392
1393 p_sql(27) := ' select rlt.* ' ||
1394 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , ' ||
1395 ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
1396 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1397 ' and soh.org_id = '||l_operating_id ||
1398 ' and rsl.oe_order_header_id = soh.header_id ' ||
1399 ' and rsl.oe_order_line_id = sol.line_id ' ||
1400 ' and sol.header_id = soh.header_id ' ||
1401 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1402 ' and rsh.organization_id = '|| l_organization_id ||
1403 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
1404 ' and sol.line_category_code = ''RETURN'' ' ||
1405 ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num ';
1406
1407 p_sql(28) := ' SELECT DISTINCT rp.* ' ||
1408 ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh, oe_order_lines_all sol
1409 ' ||
1410 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1411 ' and rsh.organization_id = '|| l_organization_id ||
1412 ' AND rsh.organization_id = rp.organization_id ' ;
1413
1414 p_sql(29) := ' SELECT DISTINCT psp.* ' ||
1415 ' FROM po_system_parameters_all psp, oe_order_headers_all soh ' ||
1416 ' where soh.order_number ='||''''||l_rma_number||'''' ||
1417 ' and soh.org_id = '||l_operating_id ||
1418 ' and soh.org_id = psp.org_id ' ;
1419
1420 p_sql(30) := ' SELECT fsp.* ' ||
1421 ' FROM financials_system_params_all fsp, oe_order_headers_all soh ' ||
1422 ' where fsp.org_id = soh.org_id ' ||
1423 ' and soh.order_number ='||''''||l_rma_number||'''' ||
1424 ' and soh.org_id = '||l_operating_id ;
1425
1426
1427 RETURN;
1428 END;
1429
1430 END RMA_DIAGNOSTICS ;