[Home] [Help]
PACKAGE BODY: APPS.RMA_RCV_DIAGNOSTICS
Source
1 PACKAGE BODY RMA_RCV_DIAGNOSTICS AS
2 /* $Header: INVDRMA2B.pls 120.0.12000000.1 2007/08/09 06:52:10 ssadasiv noship $ */
3
4 PROCEDURE rma_line_receipt_sql(p_operating_id IN NUMBER,p_rma_number IN VARCHAR2,p_line_num IN NUMBER,
5 p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
6 INV_DIAG_RCV_PO_COMMON.sqls_list) IS
7
8 l_operating_id rcv_shipment_headers.receipt_num%TYPE := p_operating_id;
9 l_rma_number rcv_shipment_headers.organization_id%TYPE := p_rma_number;
10 l_line_num oe_order_lines_all.line_number%TYPE := p_line_num;
11 l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
12 l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
13
14 BEGIN
15
16 p_sql(1) := 'select soh.* ' ||
17 ' from oe_order_headers_all soh,' ||
18 ' oe_order_lines_all sol,' ||
19 ' rcv_shipment_headers rsh,' ||
20 ' rcv_shipment_lines rsl' ||
21 ' where soh.order_number ='||''''||l_rma_number||'''' ||
22 ' and soh.org_id = '||l_operating_id ||
23 ' and soh.header_id = sol.header_id' ||
24 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
25 ' and rsl.oe_order_line_id = sol.line_id' ||
26 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
27 ' and rsh.organization_id = '|| l_organization_id ||
28 ' and exists (' ||
29 ' select 1 ' ||
30 ' from oe_order_lines_all sol ' ||
31 ' where sol.line_category_code = ''RETURN'' ' ||
32 ' and sol.header_id = soh.header_id ) ';
33
34
35
36 p_sql(2) := ' select sol.* ' ||
37 ' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||
38 ' rcv_shipment_lines rsl ' ||
39 ' where soh.order_number ='||''''||l_rma_number||'''' ||
40 ' and sol.line_number = '|| l_line_num ||
41 ' and soh.org_id = '||l_operating_id ||
42 ' and sol.line_category_code = ''RETURN'' ' ||
43 ' and sol.header_id = soh.header_id' ||
44 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
45 ' and rsl.oe_order_line_id = sol.line_id' ||
46 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
47 ' and rsh.organization_id ='|| l_organization_id ;
48
49
50
51 p_sql(3) := ' select distinct msi.* ' ||
52 ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh,
53 rcv_shipment_lines rsl' ||
54 ' where soh.order_number ='||''''||l_rma_number||'''' ||
55 ' and sol.line_number = '|| l_line_num ||
56 ' and soh.org_id = '||l_operating_id ||
57 ' and sol.line_category_code = ''RETURN'' ' ||
58 ' and sol.header_id = soh.header_id ' ||
59 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
60 ' and rsl.shipment_line_id = sol.line_id ' ||
61 ' and msi.inventory_item_id = sol.inventory_item_id ' ||
62 ' and msi.organization_id = sol.ship_from_org_id ' ||
63 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
64 ' and rsh.organization_id ='|| l_organization_id ;
65
66
67
68 p_sql(4) := ' select rsh.* ' ||
69 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
70 ' where soh.order_number ='||''''||l_rma_number||'''' ||
71 ' and sol.line_number = '|| l_line_num ||
72 ' and soh.org_id = '||l_operating_id ||
73 ' and rsl.oe_order_header_id = soh.header_id ' ||
74 ' and rsl.oe_order_line_id = sol.line_id ' ||
75 ' and sol.header_id = soh.header_id ' ||
76 ' and sol.line_category_code = ''RETURN'' ' ||
77 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
78 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
79 ' and rsh.organization_id = '||l_organization_id ;
80
81 p_sql(5) := ' select rsl.* ' ||
82 ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
83 ' where soh.order_number ='||''''||l_rma_number||'''' ||
84 ' and sol.line_number = '|| l_line_num ||
85 ' and soh.org_id = '||l_operating_id ||
86 ' and rsl.oe_order_header_id = soh.header_id ' ||
87 ' and rsl.oe_order_line_id = sol.line_id ' ||
88 ' and sol.header_id = soh.header_id ' ||
89 ' and sol.line_category_code = ''RETURN''' ||
90 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
91 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
92 ' and rsh.organization_id = ' || l_organization_id ;
93
94
95
96 p_sql(6) := ' select rt.* ' ||
97 ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
98 ' where soh.order_number ='||''''||l_rma_number||'''' ||
99 ' and sol.line_number = '|| l_line_num ||
100 ' and soh.org_id = '||l_operating_id ||
101 ' and rt.oe_order_header_id = soh.header_id ' ||
102 ' and rt.oe_order_line_id = sol.line_id ' ||
103 ' and sol.header_id = soh.header_id' ||
104 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
105 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
106 ' and sol.line_category_code = ''RETURN'' ' ||
107 ' and rsh.organization_id = ' || l_organization_id ;
108
109
110
111
112 p_sql(7) := ' select rhi.* ' ||
113 ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
114 ||
115 ' where soh.order_number ='||''''||l_rma_number||'''' ||
116 ' and sol.line_number = '|| l_line_num ||
117 ' and soh.org_id = '||l_operating_id ||
118 ' and (rti.oe_order_header_id = soh.header_id ' ||
119 ' or rti.oe_order_line_id = sol.line_id ) ' ||
120 ' and sol.header_id = soh.header_id ' ||
121 ' and sol.line_category_code = ''RETURN'' ' ||
122 ' and rhi.header_interface_id = rti.header_interface_id ';
123
124
125 p_sql(8) := ' select rti.* ' ||
126 ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
127 ' where soh.order_number ='||''''||l_rma_number||'''' ||
128 ' and sol.line_number = '|| l_line_num ||
129 ' and soh.org_id = '||l_operating_id ||
130 ' and (rti.oe_order_header_id = soh.header_id ' ||
131 ' or rti.oe_order_line_id = sol.line_id ) ' ||
132 ' and sol.header_id = soh.header_id' ||
133 ' and rti.shipment_header_id = rsh.shipment_header_id' ||
134 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
135 ' and sol.line_category_code = ''RETURN'' ' ||
136 ' and rsh.organization_id = ' || l_organization_id ;
137
138
139
140
141 p_sql(9) := ' select pie.* ' ||
142 ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
143 ' where soh.order_number ='||''''||l_rma_number||'''' ||
144 ' and sol.line_number = '|| l_line_num ||
145 ' and soh.org_id = '||l_operating_id ||
146 ' and sol.header_id = soh.header_id ' ||
147 ' and sol.line_category_code = ''RETURN'' ' ||
148 ' and (exists (' ||
149 ' select 1 ' ||
150 ' from rcv_transactions_interface rti ' ||
151 ' where (rti.oe_order_header_id = soh.header_id ' ||
152 ' or rti.oe_order_line_id = sol.line_id ) ' ||
153 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
154 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
155 ' or exists (' ||
156 ' select 2 ' ||
157 ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
158 ' where (rti.oe_order_header_id = soh.header_id ' ||
159 ' or rti.oe_order_line_id = sol.line_id ) ' ||
160 ' and rhi.header_interface_id = rti.header_interface_id ' ||
161 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
162 ' and pie.interface_header_id = rhi.header_interface_id) ) ';
163
164
165 p_sql(10) := ' select distinct ood.* ' ||
166 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
167 ||
168 ' , rcv_shipment_headers rsh' ||
169 ' where soh.order_number ='||''''||l_rma_number||'''' ||
170 ' and sol.line_number = '|| l_line_num ||
171 ' and soh.org_id = '||l_operating_id ||
172 ' and rsl.oe_order_header_id = soh.header_id ' ||
173 ' and rsl.oe_order_line_id = sol.line_id ' ||
174 ' and sol.header_id = soh.header_id ' ||
175 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
176 ' and rsl.oe_order_line_id = sol.line_id ' ||
177 ' and sol.line_category_code = ''RETURN'' ' ||
178 ' and ood.organization_id = rsl.to_organization_id ' ||
179 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
180 ' and rsh.organization_id = ' || l_organization_id ;
181
182
183
184 p_sql(11) := ' select distinct mp.* ' ||
185 ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol,
186 oe_order_headers_all soh ' ||
187 ' where rsl.oe_order_line_id = sol.line_id' ||
188 ' and sol.line_category_code = ''RETURN'' ' ||
189 ' and soh.header_id = sol.header_id ' ||
190 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
191 ' and mp.organization_id = rsl.to_organization_id '||
192 ' where soh.order_number ='||''''||l_rma_number||'''' ||
193 ' and sol.line_number = '|| l_line_num ||
194 ' and soh.org_id = '||l_operating_id ||
195 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
196 ' and rsh.organization_id = ' || l_organization_id ;
197
198
199 p_sql(12) := ' select mmt.* ' ||
200 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
201 rcv_shipment_headers rsh' ||
202 ' where soh.order_number ='||''''||l_rma_number||'''' ||
203 ' and sol.line_number = '|| l_line_num ||
204 ' and soh.org_id = '||l_operating_id ||
205 ' and sol.header_id = soh.header_id ' ||
206 ' and sol.line_category_code = ''RETURN'' ' ||
207 ' and rt.oe_order_header_id = soh.header_id ' ||
208 ' and rt.oe_order_line_id = sol.line_id' ||
209 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
210 ' and rsh.organization_id = '|| l_organization_id ||
211 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
212 ' and mmt.rcv_transaction_id = rt.transaction_id ';
213
214
215 p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'||
216 ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
217 ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
218 ||
219 ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
220 ' where soh.order_number ='||''''||l_rma_number||'''' ||
221 ' and sol.line_number = '|| l_line_num ||
222 ' and soh.org_id = '||l_operating_id ||
223 ' and sol.header_id = soh.header_id ' ||
224 ' and sol.line_category_code = ''RETURN'' ' ||
225 ' and rt.oe_order_header_id = soh.header_id ' ||
226 ' and rt.oe_order_line_id = sol.line_id ' ||
227 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
228 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
229 ' and rsh.organization_id = '|| l_organization_id ||
230 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
231 ' and mtt.transaction_type_id = mmt.transaction_type_id ';
232
233
234
235 /*p_sql(14) := 'select distinct mtrl.* ' ||
236 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
237 rcv_shipment_headers rsh ' ||
238 ' where soh.order_number ='||''''||l_rma_number||'''' ||
239 ' and sol.line_number = '|| l_line_num ||
240 ' and rt.oe_order_header_id = soh.header_id ' ||
241 ' and rt.oe_order_line_id = sol.line_id ' ||
242 ' and sol.header_id = soh.header_id ' ||
243 ' and sol.line_category_code = ''RETURN'' ' ||
244 ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
245 ' and mtrl.reference_id = rt.oe_order_line_id ' ||
246 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
247 ' and rsh.organization_id = '|| l_organization_id ||
248 ' and rt.shipment_header_id = rt.shipment_header_id' ||
249 ' and soh.org_id = 2222 ';*/
250
251 p_sql(14) := 'select distinct mtrl.* ' ||
252 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
253 rcv_shipment_headers rsh ' ||
254 ' where soh.order_number ='||''''||l_rma_number||'''' ||
255 ' and sol.line_number = '|| l_line_num ||
256 ' and rt.oe_order_header_id = soh.header_id ' ||
257 ' and rt.oe_order_line_id = sol.line_id ' ||
258 ' and sol.header_id = soh.header_id ' ||
259 ' and sol.line_category_code = ''RETURN'' ' ||
260 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
261 ' and rsh.organization_id = '|| l_organization_id ||
262 ' and rt.shipment_header_id = rt.shipment_header_id' ||
263 ' and soh.org_id = '||l_operating_id||
264 ' AND mtrl.organization_id=rt.organization_id'||
265 ' AND mtrl.inventory_item_id = sol.inventory_item_id ' ||
266 ' and nvl(mtrl.revision,0)=nvl(sol.item_revision,0) ' ||' and mtrl.line_status=7'||
267 ' and mtrl.transaction_type_id=15';
268
269
270
271 p_sql(15) := ' select mmtt.* ' ||
272 ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh
273 ' ||
274 ' where soh.order_number ='||''''||l_rma_number||'''' ||
275 ' and sol.line_number = '|| l_line_num ||
276 ' and soh.org_id = '||l_operating_id ||
277 ' and sol.header_id = soh.header_id ' ||
278 ' and sol.line_category_code = ''RETURN'' ' ||
279 ' and exists (' ||
280 ' select 1 ' ||
281 ' from rcv_transactions rt ' ||
282 ' where rt.oe_order_header_id = soh.header_id ' ||
283 ' and rt.oe_order_line_id = sol.line_id ' ||
284 ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
285 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
286 ' and rsh.organization_id = '|| l_organization_id ||
287 ' and rt.shipment_header_id = rsh.shipment_header_id) ';
288
289 p_sql(16) := ' select lsn.* ' ||
290 ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
291 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
292 ' where soh.order_number ='||''''||l_rma_number||'''' ||
293 ' and sol.line_number = '|| l_line_num ||
294 ' and soh.org_id = '||l_operating_id ||
295 ' and sol.line_category_code = ''RETURN'' ' ||
296 ' and sol.header_id = soh.header_id ' ||
297 ' and lsn.line_id = sol.line_id ' ||
298 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
299 ' and rsl.oe_order_line_id = sol.line_id' ||
300 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
301 ' and rsh.organization_id = '|| l_organization_id ||
302 ' order by sol.line_id desc , lsn.lot_serial_id ';
303
304
305 p_sql(17) := ' select distinct msn.* ' ||
306 ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
307 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
308 ' where soh.order_number ='||''''||l_rma_number||'''' ||
309 ' and sol.line_number = '|| l_line_num ||
310 ' and soh.org_id = '||l_operating_id ||
311 ' and sol.header_id = soh.header_id ' ||
312 ' and sol.line_category_code = ''RETURN'' ' ||
316 ' and rsh.organization_id = '|| l_organization_id ||
313 ' and rsl.oe_order_line_id = sol.line_id' ||
314 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
315 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
317 ' and (exists (' ||
318 ' select 1 ' ||
319 ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
320 ' where rt.oe_order_header_id = soh.header_id ' ||
321 ' and rt.oe_order_line_id = sol.line_id ' ||
322 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
323 ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
324 ' or exists (' ||
325 ' select 3 ' ||
326 ' from mtl_material_transactions ommt ' ||
327 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
328 ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
329 ' or exists (' ||
330 ' select 2 ' ||
331 ' from oe_lot_serial_numbers lsn ' ||
332 ' where lsn.line_id = sol.line_id ' ||
333 ' and sol.ordered_item_id = msn.inventory_item_id ' ||
334 ' and sol.ship_from_org_id = msn.current_organization_id ' ||
335 ' and msn.serial_number between lsn.from_serial_number and nvl(lsn.to_serial_number ,
336 lsn.from_serial_number)'
337 ||
338 ' ) ) order by msn.inventory_item_id , msn.serial_number ';
339
340
341 p_sql(18) := ' select msnt.* ' ||
342 ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
343 ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
344 ' where soh.order_number ='||''''||l_rma_number||'''' ||
345 ' and sol.line_number = '|| l_line_num ||
346 ' and soh.org_id = '||l_operating_id ||
347 ' and sol.header_id = soh.header_id ' ||
348 ' and sol.line_category_code = ''RETURN'' ' ||
349 ' and rt.oe_order_header_id = soh.header_id ' ||
350 ' and rt.oe_order_line_id = sol.line_id ' ||
351 ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
352 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
353 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
354 ' and rsh.organization_id = '|| l_organization_id ||
355 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
356 ' or exists (' ||
357 ' select 2 ' ||
358 ' from mtl_transaction_lots_temp mtlt ' ||
359 ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
360 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
361
362
363 p_sql(19) := ' select msni.* ' ||
364 ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
365 ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
366 ' rcv_shipment_headers rsh ' ||
367 ' where soh.order_number ='||''''||l_rma_number||'''' ||
368 ' and sol.line_number = '|| l_line_num ||
369 ' and soh.org_id = '||l_operating_id ||
370 ' and sol.header_id = soh.header_id ' ||
371 ' and sol.line_category_code = ''RETURN'' ' ||
372 ' and rt.oe_order_header_id = soh.header_id ' ||
373 ' and rt.oe_order_line_id = sol.line_id ' ||
374 ' and mti.rcv_transaction_id = rt.transaction_id ' ||
375 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
376 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
377 ' and rsh.organization_id = '|| l_organization_id ||
378 ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
379 ' or exists (' ||
380 ' select 3 ' ||
381 ' from mtl_transaction_lots_interface mtln ' ||
382 ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
383 ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
384
385
386 p_sql(20) := ' select mut.* ' ||
387 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
388 ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
389 ' where soh.order_number ='||''''||l_rma_number||'''' ||
390 ' and sol.line_number = '|| l_line_num ||
391 ' and soh.org_id = '||l_operating_id ||
392 ' and sol.header_id = soh.header_id ' ||
393 ' and sol.line_category_code = ''RETURN'' ' ||
394 ' and rt.oe_order_header_id = soh.header_id ' ||
395 ' and rt.oe_order_line_id = sol.line_id ' ||
396 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
397 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
398 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
399 ' and rsh.organization_id = '|| l_organization_id ||
400 ' and (mut.transaction_id = mmt.transaction_id ' ||
401 ' or exists (' ||
402 ' select 1 ' ||
403 ' from mtl_transaction_lot_numbers mtln ' ||
404 ' where mtln.transaction_id = mmt.transaction_id ' ||
405 ' and mut.transaction_id = mtln.serial_transaction_id ) ) ';
406
407
408
409 p_sql(21) := ' select rst.* ' ||
410 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
411 ' rcv_shipment_headers rsh' ||
412 ' where soh.order_number ='||''''||l_rma_number||'''' ||
413 ' and sol.line_number = '|| l_line_num ||
414 ' and soh.org_id = '||l_operating_id ||
415 ' and rsl.oe_order_header_id = soh.header_id ' ||
416 ' and rsl.oe_order_line_id = sol.line_id ' ||
417 ' and sol.header_id = soh.header_id ' ||
418 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
419 ' and rsh.organization_id = '|| l_organization_id ||
420 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
421 ' and sol.line_category_code = ''RETURN'' ' ||
422 ' and rst.shipment_line_id = rsl.shipment_line_id ';
423
424
425 p_sql(22) := ' select distinct rsi.* ' ||
429 ' and sol.line_number = '|| l_line_num ||
426 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
427 ' rcv_shipment_headers rsh' ||
428 ' where soh.order_number ='||''''||l_rma_number||'''' ||
430 ' and soh.org_id = '||l_operating_id ||
431 ' and rsl.oe_order_header_id = soh.header_id ' ||
432 ' and rsl.oe_order_line_id = sol.line_id ' ||
433 ' and sol.header_id = soh.header_id ' ||
434 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
435 ' and rsh.organization_id = '|| l_organization_id ||
436 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
437 ' and sol.line_category_code = ''RETURN'' ' ||
438 ' and rsi.item_id = rsl.item_id ' ||
439 ' and rsi.organization_id = rsl.to_organization_id ';
440
441
442
443 p_sql(23) := ' select distinct mln.* ' ||
444 ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
445 ' where soh.order_number ='||''''||l_rma_number||'''' ||
446 ' and sol.line_number = '|| l_line_num ||
447 ' and soh.org_id = '||l_operating_id ||
448 ' and sol.header_id = soh.header_id ' ||
449 ' and sol.line_category_code = ''RETURN'' ' ||
450 ' and (exists (' ||
451 ' select 1 ' ||
452 ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln, ' ||
453 ' rcv_shipment_headers rsh ' ||
454 ' where rt.oe_order_header_id = soh.header_id ' ||
455 ' and rt.oe_order_line_id = sol.line_id ' ||
456 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
457 ' and mmt.transaction_id = mtln.transaction_id ' ||
458 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
459 ' and mln.organization_id = mmt.organization_id ' ||
460 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
461 ' and rsh.organization_id = '|| l_organization_id ||
462 ' and rsh.shipment_header_id = rt.shipment_header_id ' ||
463 ' and mln.lot_number = mtln.lot_number ) ' ||
464 ' or exists (' ||
465 ' select 2 ' ||
466 ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
467 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
468 ' and ommt.transaction_id = mtln.transaction_id ' ||
469 ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
470 ' and mln.organization_id = ommt.organization_id ' ||
471 ' and mln.lot_number = mtln.lot_number ) ' ||
472 ' or exists (' ||
473 ' select 3 ' ||
474 ' from oe_lot_serial_numbers lsn ' ||
475 ' where lsn.line_id = sol.line_id ' ||
476 ' and sol.ordered_item_id = mln.inventory_item_id ' ||
477 ' and sol.ship_from_org_id = mln.organization_id ' ||
478 ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
479 ' mln.lot_number ';
480
481
482 p_sql(24) := ' select mtln.* ' ||
483 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
484 ||
485 ' soh ' ||
486 ' where soh.order_number ='||''''||l_rma_number||'''' ||
487 ' and sol.line_number = '|| l_line_num ||
488 ' and soh.org_id = '||l_operating_id ||
489 ' and sol.header_id = soh.header_id ' ||
490 ' and sol.line_category_code = ''RETURN'' ' ||
491 ' and mmt.transaction_id = mtln.transaction_id ' ||
492 ' and (exists (' ||
493 ' select 1 ' ||
494 ' from rcv_transactions rt, rcv_shipment_headers rsh ' ||
495 ' where rt.oe_order_line_id = sol.line_id ' ||
496 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
497 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
498 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
499 ' and rsh.organization_id = '|| l_organization_id ||
500 ' ) ' ||
501 ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
502 ,' ||
503 ' mtln.transaction_id ';
504
505
506
507
508 p_sql(25):= ' select mtli.* ' ||
509 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
510 ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
511 ' where soh.order_number ='||''''||l_rma_number||'''' ||
512 ' and sol.line_number = '|| l_line_num ||
513 ' and soh.org_id = '||l_operating_id ||
514 ' and sol.header_id = soh.header_id ' ||
515 ' and sol.line_category_code = ''RETURN'' ' ||
516 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
517 ' and rt.oe_order_header_id = soh.header_id ' ||
518 ' and rt.oe_order_line_id = sol.line_id ' ||
519 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
520 ' and rsh.organization_id = '|| l_organization_id ||
521 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
522 ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
523
524
525 p_sql(26) := ' select mtlt.* ' ||
526 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
527 ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
528 ' where soh.order_number ='||''''||l_rma_number||'''' ||
529 ' and sol.line_number = '|| l_line_num ||
530 ' and soh.org_id = '||l_operating_id ||
531 ' and sol.header_id = soh.header_id ' ||
532 ' and sol.line_category_code = ''RETURN'' ' ||
533 ' and rt.oe_order_header_id = soh.header_id ' ||
534 ' and rt.oe_order_line_id = sol.line_id ' ||
535 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
536 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
540
537 ' and rsh.organization_id = '|| l_organization_id ||
538 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
539 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
541 p_sql(27) := ' select rlt.* ' ||
542 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh, ' ||
543 ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
544 ' where soh.order_number ='||''''||l_rma_number||'''' ||
545 ' and sol.line_number = '|| l_line_num ||
546 ' and soh.org_id = '||l_operating_id ||
547 ' and rsl.oe_order_header_id = soh.header_id ' ||
548 ' and rsl.oe_order_line_id = sol.line_id ' ||
549 ' and sol.header_id = soh.header_id ' ||
550 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
551 ' and rsh.organization_id = '|| l_organization_id ||
552 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
553 ' and sol.line_category_code = ''RETURN'' ' ||
554 ' and rlt.shipment_line_id = rsl.shipment_line_id ' ||
555 ' order by rlt.shipment_line_id , rlt.lot_num ';
556
557 p_sql(28) := ' SELECT DISTINCT rp.* ' ||
558 ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||
559 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
560 ' and rsh.organization_id = '|| l_organization_id ||
561 ' AND rsh.organization_id = rp.organization_id ' ;
562
563
564 p_sql(29) := ' SELECT DISTINCT psp.* ' ||
565 ' FROM po_system_parameters_all psp, oe_order_headers_all soh ' ||
566 ' where soh.order_number ='||''''||l_rma_number||'''' ||
567 ' and soh.org_id = '||l_operating_id ||
568 ' and soh.org_id = psp.org_id ' ;
569
570 p_sql(30) := ' SELECT fsp.* ' ||
571 ' FROM financials_system_params_all fsp, oe_order_headers_all soh ' ||
572 ' where fsp.org_id = soh.org_id ' ||
573 ' where soh.order_number ='||''''||l_rma_number||'''' ||
574 ' and soh.org_id = '||l_operating_id ;
575
576
577
578 RETURN;
579 END;
580
581 PROCEDURE receipt_sql(p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
582 INV_DIAG_RCV_PO_COMMON.sqls_list) IS
583
584 l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
585 l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
586
587 BEGIN
588
589 p_sql(1) := 'select distinct soh.* ' ||
590 ' from oe_order_headers_all soh,' ||
591 ' oe_order_lines_all sol,' ||
592 ' rcv_shipment_headers rsh,' ||
593 ' rcv_shipment_lines rsl' ||
594 ' where soh.header_id = sol.header_id' ||
595 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
596 ' and rsl.oe_order_line_id = sol.line_id' ||
597 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
598 ' and rsh.organization_id = '|| l_organization_id ||
599 ' and exists (' ||
600 ' select 1 ' ||
601 ' from oe_order_lines_all sol ' ||
602 ' where sol.line_category_code = ''RETURN'' ' ||
603 ' and sol.header_id = soh.header_id ) ';
604
605
606
607 p_sql(2) := ' select distinct sol.* ' ||
608 ' from oe_order_lines_all sol , rcv_shipment_headers rsh,' ||
609 ' rcv_shipment_lines rsl ' ||
610 ' where sol.line_category_code = ''RETURN'' ' ||
611 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
612 ' and rsl.oe_order_line_id = sol.line_id' ||
613 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
614 ' and rsh.organization_id ='|| l_organization_id ;
615
616
617
618 p_sql(3) := ' select distinct msi.* ' ||
619 ' from mtl_system_items msi , oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
620 ' where sol.line_category_code = ''RETURN'' ' ||
621 ' and sol.line_id = rsl.oe_order_line_id' ||
622 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
623 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
624 ' and rsh.organization_id ='|| l_organization_id ||
625 ' and msi.inventory_item_id = sol.inventory_item_id ' ||
626 ' and msi.organization_id = sol.ship_from_org_id ';
627
628
629 p_sql(4) := ' select distinct rsh.* ' ||
630 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_lines_all sol ' ||
631 ' where rsl.oe_order_header_id = sol.header_id ' ||
632 ' and rsl.oe_order_line_id = sol.line_id ' ||
633 ' and sol.line_category_code = ''RETURN'' ' ||
634 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
635 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
636 ' and rsh.organization_id = '||l_organization_id ;
637
638 p_sql(5) := ' select distinct rsl.* ' ||
639 ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_lines_all sol ' ||
640 ' where rsl.oe_order_header_id = sol.header_id ' ||
641 ' and rsl.oe_order_line_id = sol.line_id ' ||
642 ' and sol.line_category_code = ''RETURN''' ||
643 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
644 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
645 ' and rsh.organization_id = ' || l_organization_id ;
646
647
648
649 p_sql(6) := ' select rt.* ' ||
650 ' from rcv_transactions rt , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
651 ' where rt.oe_order_line_id = sol.line_id ' ||
652 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
653 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
654 ' and sol.line_category_code = ''RETURN'' ' ||
655 ' and rsh.organization_id = ' || l_organization_id ;
656
657
658 p_sql(7) := ' select distinct rhi.* ' ||
659 ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_lines_all sol ' ||
663 ' and rhi.receipt_num = '||''''||l_receipt_number||'''' ||
660 ' where rti.oe_order_header_id = sol.header_id ' ||
661 ' and rti.oe_order_line_id = sol.line_id ' ||
662 ' and rhi.header_interface_id = rti.header_interface_id ' ||
664 ' and sol.line_category_code = ''RETURN'' ' ||
665 ' and rhi.ship_to_organization_id = ' || l_organization_id ;
666
667
668 p_sql(8) := ' select rti.* ' ||
669 ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
670 ' where rti.oe_order_header_id = soh.header_id ' ||
671 ' and rti.oe_order_line_id = sol.line_id ' ||
672 ' and sol.header_id = soh.header_id' ||
673 ' and rti.shipment_header_id = rsh.shipment_header_id' ||
674 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
675 ' and sol.line_category_code = ''RETURN'' ' ||
676 ' and rsh.organization_id = ' || l_organization_id ;
677
678
679
680 p_sql(9) := ' select distinct pie.* ' ||
681 ' from po_interface_errors pie , oe_order_lines_all sol, rcv_transactions_interface rti, rcv_shipment_headers rsh' ||
682 ' where sol.line_category_code = ''RETURN'' ' ||
683 ' and rsh.shipment_header_id = rti.shipment_header_id' ||
684 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
685 ' and rsh.organization_id = ' || l_organization_id ||
686 ' and pie.interface_line_id = rti.interface_transaction_id' ||
687 ' and rti.oe_order_line_id = sol.line_id' ;
688
689 p_sql(10) := ' select distinct ood.* ' ||
690 ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
691 ' where rsl.oe_order_line_id = sol.line_id ' ||
692 ' and sol.line_category_code = ''RETURN'' ' ||
693 ' and ood.organization_id = rsl.to_organization_id ' ||
694 ' and rsl.shipment_header_id = rsh.shipment_header_id '||
695 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
696 ' and rsh.organization_id = ' || l_organization_id ;
697
698
699
700 p_sql(11) := ' select distinct mp.* ' ||
701 ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol ' ||
702 ' where rsl.oe_order_line_id = sol.line_id' ||
703 ' and sol.line_category_code = ''RETURN'' ' ||
704 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
705 ' and mp.organization_id = rsl.to_organization_id '||
706 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
707 ' and rsh.organization_id = ' || l_organization_id ;
708
709
710 p_sql(12) := ' select mmt.* ' ||
711 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
712 rcv_shipment_headers rsh' ||
713 ' where sol.header_id = soh.header_id ' ||
714 ' and sol.line_category_code = ''RETURN'' ' ||
715 ' and rt.oe_order_header_id = soh.header_id ' ||
716 ' and rt.oe_order_line_id = sol.line_id' ||
717 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
718 ' and rsh.organization_id = '|| l_organization_id ||
719 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
720 ' and mmt.rcv_transaction_id = rt.transaction_id ';
721
722
723 p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'||
724 ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
725 ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
726 ||
727 ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
728 ' where sol.header_id = soh.header_id ' ||
729 ' and sol.line_category_code = ''RETURN'' ' ||
730 ' and rt.oe_order_header_id = soh.header_id ' ||
731 ' and rt.oe_order_line_id = sol.line_id ' ||
732 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
733 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
734 ' and rsh.organization_id = '|| l_organization_id ||
735 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
736 ' and mtt.transaction_type_id = mmt.transaction_type_id ';
737
738
739
740 /*p_sql(14) := 'select distinct mtrl.* ' ||
741 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
742 rcv_shipment_headers rsh ' ||
743 ' where rt.oe_order_header_id = soh.header_id ' ||
744 ' and rt.oe_order_line_id = sol.line_id ' ||
745 ' and sol.header_id = soh.header_id ' ||
746 ' and sol.line_category_code = ''RETURN'' ' ||
747 ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
748 ' and mtrl.reference_id = rt.oe_order_line_id ' ||
749 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
750 ' and rsh.organization_id = '|| l_organization_id ||
751 ' and rt.shipment_header_id = rsh.shipment_header_id' ;*/
752
753 p_sql(14) := 'select distinct mtrl.* ' ||
754 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
755 rcv_shipment_headers rsh ' ||
756 ' where rt.oe_order_header_id = soh.header_id ' ||
757 ' and rt.oe_order_line_id = sol.line_id ' ||
758 ' and sol.header_id = soh.header_id ' ||
759 ' and sol.line_category_code = ''RETURN'' ' ||
760 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
761 ' and rsh.organization_id = '|| l_organization_id ||
762 ' and rt.shipment_header_id = rsh.shipment_header_id'||
763 ' AND mtrl.inventory_item_id = sol.inventory_item_id ' ||
764 ' and nvl(mtrl.revision,0)=nvl(sol.item_revision,0) ' ||' and mtrl.line_status=7'||
765 ' and mtrl.transaction_type_id=15';
766
767 p_sql(15) := 'select mmtt.* '||
768 'from mtl_material_transactions_temp mmtt , rcv_shipment_headers rsh, rcv_transactions rt'||
769 ' WHERE mmtt.rcv_transaction_id = rt.transaction_id ' ||
770 ' and rt.shipment_header_id = rsh.shipment_header_id '||
771 ' AND rt.organization_id = rsh.organization_id '||
775
772 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
773 ' and rsh.organization_id = '|| l_organization_id ;
774
776 p_sql(16) := ' select lsn.* ' ||
777 ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
778 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
779 ' where sol.line_category_code = ''RETURN'' ' ||
780 ' and sol.header_id = soh.header_id ' ||
781 ' and lsn.line_id = sol.line_id ' ||
782 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
783 ' and rsl.oe_order_line_id = sol.line_id' ||
784 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
785 ' and rsh.organization_id = '|| l_organization_id ||
786 ' order by sol.line_id desc , lsn.lot_serial_id ';
787
788
789 p_sql(17) := ' select distinct msn.* ' ||
790 ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
791 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
792 ' where sol.header_id = soh.header_id ' ||
793 ' and sol.line_category_code = ''RETURN'' ' ||
794 ' and rsl.oe_order_line_id = sol.line_id' ||
795 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
796 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
797 ' and rsh.organization_id = '|| l_organization_id ||
798 ' and (exists (' ||
799 ' select 1 ' ||
800 ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
801 ' where rt.oe_order_header_id = soh.header_id ' ||
802 ' and rt.oe_order_line_id = sol.line_id ' ||
803 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
804 ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
805 ' or exists (' ||
806 ' select 3 ' ||
807 ' from mtl_material_transactions ommt ' ||
808 ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
809 ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
810 ' or exists (' ||
811 ' select 2 ' ||
812 ' from oe_lot_serial_numbers lsn ' ||
813 ' where lsn.line_id = sol.line_id ' ||
814 ' and sol.ordered_item_id = msn.inventory_item_id ' ||
815 ' and sol.ship_from_org_id = msn.current_organization_id ' ||
816 ' and msn.serial_number between lsn.from_serial_number and nvl(lsn.to_serial_number ,
817 lsn.from_serial_number)'
818 ||
819 ' ) ) order by msn.inventory_item_id , msn.serial_number ';
820
821
822 p_sql(18) := ' select msnt.* ' ||
823 ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
824 ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
825 ' where sol.header_id = soh.header_id ' ||
826 ' and sol.line_category_code = ''RETURN'' ' ||
827 ' and rt.oe_order_header_id = soh.header_id ' ||
828 ' and rt.oe_order_line_id = sol.line_id ' ||
829 ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
830 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
831 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
832 ' and rsh.organization_id = '|| l_organization_id ||
833 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
834 ' or exists (' ||
835 ' select 2 ' ||
836 ' from mtl_transaction_lots_temp mtlt ' ||
837 ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
838 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
839
840
841 p_sql(19) := ' select msni.* ' ||
842 ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
843 ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
844 ' rcv_shipment_headers rsh ' ||
845 ' where sol.header_id = soh.header_id ' ||
846 ' and sol.line_category_code = ''RETURN'' ' ||
847 ' and rt.oe_order_header_id = soh.header_id ' ||
848 ' and rt.oe_order_line_id = sol.line_id ' ||
849 ' and mti.rcv_transaction_id = rt.transaction_id ' ||
850 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
851 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
852 ' and rsh.organization_id = '|| l_organization_id ||
853 ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
854 ' or exists (' ||
855 ' select 3 ' ||
856 ' from mtl_transaction_lots_interface mtln ' ||
857 ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
858 ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
859
860
861 p_sql(20) := ' select mut.* ' ||
862 ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
863 ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
864 ' where 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 mmt.rcv_transaction_id = rt.transaction_id ' ||
869 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
870 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
871 ' and rsh.organization_id = '|| l_organization_id ||
872 ' and (mut.transaction_id = mmt.transaction_id ' ||
873 ' or exists (' ||
874 ' select 1 ' ||
875 ' from mtl_transaction_lot_numbers mtln ' ||
876 ' where mtln.transaction_id = mmt.transaction_id ' ||
877 ' and mut.transaction_id = mtln.serial_transaction_id ) ) ';
878
879
880
881 p_sql(21) := ' select rst.* ' ||
882 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
883 ' rcv_shipment_headers rsh' ||
884 ' where rsl.oe_order_header_id = soh.header_id ' ||
885 ' and rsl.oe_order_line_id = sol.line_id ' ||
889 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
886 ' and sol.header_id = soh.header_id ' ||
887 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
888 ' and rsh.organization_id = '|| l_organization_id ||
890 ' and sol.line_category_code = ''RETURN'' ' ||
891 ' and rst.shipment_line_id = rsl.shipment_line_id ';
892
893
894 p_sql(22) := ' select distinct rsi.* ' ||
895 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
896 ' rcv_shipment_headers rsh' ||
897 ' where rsl.oe_order_header_id = soh.header_id ' ||
898 ' and rsl.oe_order_line_id = sol.line_id ' ||
899 ' and sol.header_id = soh.header_id ' ||
900 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
901 ' and rsh.organization_id = '|| l_organization_id ||
902 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
903 ' and sol.line_category_code = ''RETURN'' ' ||
904 ' and rsi.item_id = rsl.item_id ' ||
905 ' and rsi.organization_id = rsl.to_organization_id ';
906
907
908
909 p_sql(23) := ' select distinct mln.* ' ||
910 ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol' ||
911 ' where sol.header_id = soh.header_id ' ||
912 ' and sol.line_category_code = ''RETURN'' ' ||
913 ' and (exists (' ||
914 ' select 1 ' ||
915 ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln,
916 rcv_shipment_headers rsh ' ||
917 ' where rt.oe_order_header_id = soh.header_id ' ||
918 ' and rt.oe_order_line_id = sol.line_id ' ||
919 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
920 ' and mmt.transaction_id = mtln.transaction_id ' ||
921 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
922 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
923 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
924 ' and rsh.organization_id = '|| l_organization_id ||
925 ' and mln.organization_id = mmt.organization_id ' ||
926 ' and mln.lot_number = mtln.lot_number ) ' ||
927 ') order by mln.organization_id , mln.inventory_item_id ,' ||
928 ' mln.lot_number ';
929
930
931 p_sql(24) := ' select mtln.* ' ||
932 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt, rcv_transactions rt,
933 rcv_shipment_headers rsh' ||
934 ' where mmt.transaction_id = mtln.transaction_id ' ||
935 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
936 ' AND rt.shipment_header_id = rsh.shipment_header_id' ||
937 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
938 ' and rsh.organization_id = '|| l_organization_id ||
939 ' order by mtln.organization_id , mtln.inventory_item_id , mtln.transaction_id ';
940
941 p_sql(25):= ' select mtli.* ' ||
942 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
943 ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
944 ' where sol.header_id = soh.header_id ' ||
945 ' and sol.line_category_code = ''RETURN'' ' ||
946 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
947 ' and rt.oe_order_header_id = soh.header_id ' ||
948 ' and rt.oe_order_line_id = sol.line_id ' ||
949 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
950 ' and rsh.organization_id = '|| l_organization_id ||
951 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
952 ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
953
954
955 p_sql(26) := ' select mtlt.* ' ||
956 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
957 ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
958 ' where sol.header_id = soh.header_id ' ||
959 ' and sol.line_category_code = ''RETURN'' ' ||
960 ' and rt.oe_order_header_id = soh.header_id ' ||
961 ' and rt.oe_order_line_id = sol.line_id ' ||
962 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
963 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
964 ' and rsh.organization_id = '|| l_organization_id ||
965 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
966 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
967
968 p_sql(27) := ' select rlt.* ' ||
969 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , ' ||
970 ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
971 ' where rsl.oe_order_header_id = soh.header_id ' ||
972 ' and rsl.oe_order_line_id = sol.line_id ' ||
973 ' and sol.header_id = soh.header_id ' ||
974 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
975 ' and rsh.organization_id = '|| l_organization_id ||
976 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
977 ' and sol.line_category_code = ''RETURN'' ' ||
978 ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num ';
979
980 p_sql(28) := ' SELECT DISTINCT rp.* ' ||
981 ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||
982 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
983 ' and rsh.organization_id = '|| l_organization_id ||
984 ' AND rsh.organization_id = rp.organization_id ' ;
985
986
987 p_sql(29) := ' SELECT DISTINCT psp.* ' ||
988 ' FROM po_system_parameters_all psp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||
989 ' rcv_shipment_lines rsl ' ||
990 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
991 ' and rsh.organization_id = '|| l_organization_id ||
992 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
993 ' and rsl.oe_order_header_id = soh.header_id ' ||
994 ' and soh.org_id = psp.org_id ' ;
995
996 p_sql(30) := ' SELECT DISTINCT fsp.* ' ||
997 ' FROM financials_system_params_all fsp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||
998 ' rcv_shipment_lines rsl ' ||
999 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1000 ' and rsh.organization_id = '|| l_organization_id ||
1001 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
1002 ' and rsl.oe_order_header_id = soh.header_id ' ||
1003 ' and soh.org_id = fsp.org_id ' ;
1004
1005
1006
1007 RETURN;
1008 END;
1009
1010 END RMA_RCV_DIAGNOSTICS;