APPS.RMA_DIAGNOSTICS dependencies on OE_ORDER_LINES_ALL

Line 12: ' from oe_order_headers_all soh , oe_order_lines_all sol ' ||

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'' ' ;

Line 20: ' from oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ;

Line 28: ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ' ||

Line 38: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 55: ' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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'' ' ||

Line 67: ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 78: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '

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 ) ' ||

Line 90: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 101: ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ||

Line 123: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '

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 ' ||

Line 135: ' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 146: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||

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'' ' ||

Line 159: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'

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 ||

Line 173: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 184: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 197: ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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'' ' ||

Line 210: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ' ||

Line 219: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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'' ' ||

Line 247: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||

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 ' ||

Line 264: ' from mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh ,' ||

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 ' ||

Line 281: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||

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 ' ||

Line 300: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 311: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 323: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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'' ' ||

Line 357: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'

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 ||

Line 377: ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||

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 ' ||

Line 390: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||

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 ' ||

Line 402: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 412: ' FROM rcv_parameters rp, oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ;

Line 439: l_line_num oe_order_lines_all.line_number%TYPE := p_line_num ;

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.* ' ||

Line 449: ' from oe_order_lines_all sol ' ||

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:

Line 455: ' from oe_order_lines_all sol , oe_order_headers_all soh ' ||

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''' ||

Line 464: ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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'' ' ||

Line 476: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ||

Line 487: 'from rcv_shipment_headers rsh, oe_order_headers_all soh , oe_order_lines_all sol,rcv_transactions_interface rti '||

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 '||
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'||
491: ' and rti.oe_order_line_id = sol.line_id' ||

Line 498: ' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 509: ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 522: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '

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 ||

Line 535: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 546: ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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' ||

Line 569: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '

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 ||

Line 582: ' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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' ||

Line 595: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||

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 ||

Line 610: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'

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 ,'
611: ||
612: ' rcv_transactions rt ' ||
613: ' where soh.order_number ='||''''||l_rma_number||'''' ||
614: ' and soh.org_id = '||l_operating_id ||

Line 625: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 637: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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' ||

Line 651: ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ||

Line 665: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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' ||

Line 675: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ||

Line 706: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||

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' ||

Line 724: mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh , mtl_serial_numbers_interface' || '

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||'''' ||
727: ' and soh.org_id = '||l_operating_id ||
728: ' and sol.header_id = soh.header_id ' ||

Line 742: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||

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 ' ||

Line 763: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 776: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 789: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 824: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'

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 ||

Line 845: ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||

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 ,' ||
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 ||
849: ' and sol.line_number = '|| l_line_num ||

Line 859: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||

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 ||

Line 872: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 883: ' FROM rcv_parameters rp, oe_order_headers_all soh, oe_order_lines_all sol ' ||

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 ' ;

Line 916: ' from oe_order_headers_all soh, oe_order_lines_all sol' ||

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 ' ||

Line 921: ' from oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||

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' ||

Line 934: ' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||

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'' ' ||

Line 948: ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||

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 ' ||

Line 958: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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 ' ||

Line 970: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

966: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
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 ' ||

Line 984: ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||

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 ' ||

Line 999: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '

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 ' ||

Line 1011: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||

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 ) ' ||

Line 1026: ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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'' ' ||

Line 1048: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '

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 ' ||

Line 1060: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '

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 ' ||

Line 1072: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,

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 ' ||

Line 1088: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'

1084:
1085:
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 ||

Line 1106: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

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 ' ||

Line 1121: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

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 ' ||

Line 1140: ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh

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 ' ||

Line 1157: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||

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'' ' ||

Line 1172: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||

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 ' ||

Line 1207: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

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 ' ||

Line 1228: ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||

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 ||

Line 1250: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||

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 ' ||

Line 1272: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

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 ' ||

Line 1287: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

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 ' ||

Line 1304: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||

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'' ' ||

Line 1338: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'

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 ||

Line 1364: ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||

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'' ' ||

Line 1379: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

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 ' ||

Line 1395: ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||

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 ' ||

Line 1408: ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh, oe_order_lines_all sol

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 ' ;