APPS.IO_DIAGNOSTICS1 dependencies on PO_REQUISITION_HEADERS_ALL

Line 5: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;

1: PACKAGE BODY io_diagnostics1 AS
2: /* $Header: INVDIO1B.pls 120.1.12000000.1 2007/08/09 06:43:33 ssadasiv noship $ */
3:
4: PROCEDURE req_num_sql(p_ou_id IN NUMBER , p_req_num IN VARCHAR2, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
5: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
6: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
7: l_line_num po_requisition_lines_all.line_num%TYPE := NULL;
8: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := NULL;
9: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := NULL;

Line 6: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;

2: /* $Header: INVDIO1B.pls 120.1.12000000.1 2007/08/09 06:43:33 ssadasiv noship $ */
3:
4: PROCEDURE req_num_sql(p_ou_id IN NUMBER , p_req_num IN VARCHAR2, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
5: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
6: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
7: l_line_num po_requisition_lines_all.line_num%TYPE := NULL;
8: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := NULL;
9: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := NULL;
10: l_org_id rcv_shipment_headers.organization_id%TYPE := NULL;

Line 15: ' from po_requisition_headers_all prh,' ||

11:
12: BEGIN
13:
14: p_sql(1) := ' select distinct prh.*' ||
15: ' from po_requisition_headers_all prh,' ||
16: ' po_requisition_lines_all prl' ||
17: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
18: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
19: ' and prh.org_id = ' || l_ou_id ||

Line 27: ' po_requisition_headers_all prh ' ||

23:
24:
25: p_sql(2) := ' select distinct prl.*' ||
26: ' from po_requisition_lines_all prl,' ||
27: ' po_requisition_headers_all prh ' ||
28: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
29: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
30: ' and prh.org_id = ' || l_ou_id ||
31: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 40: ' po_requisition_headers_all prh' ||

36:
37: p_sql(3) := ' select distinct prd.*' ||
38: ' from po_req_distributions_all prd ,' ||
39: ' po_requisition_lines_all prl ,' ||
40: ' po_requisition_headers_all prh' ||
41: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
42: ' and prh.requisition_header_id = prl.requisition_header_id' ||
43: ' and prl.requisition_line_id = prd.requisition_line_id' ||
44: ' and prl.source_type_code = ''INVENTORY''' ||

Line 54: ' po_requisition_headers_all prh' ||

50:
51: p_sql(4) := ' select distinct oel.*' ||
52: ' from oe_order_lines_all oel,' ||
53: ' po_requisition_lines_all prl,' ||
54: ' po_requisition_headers_all prh' ||
55: ' where oel.source_document_type_id = 10' ||
56: ' and oel.source_document_line_id = prl.requisition_line_id' ||
57: ' and prl.requisition_header_id = prh.requisition_header_id' ||
58: ' and prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||

Line 67: ' prl , po_requisition_headers_all prh ' ||

63:
64: p_sql(5) := ' select distinct wsh.* ' ||
65: ' from wsh_delivery_details wsh , wsh_delivery_assignments wda , wsh_new_deliveries wnd , oe_order_lines_all sol ,
66: po_requisition_lines_all ' ||
67: ' prl , po_requisition_headers_all prh ' ||
68: ' where wsh.source_line_id = sol.line_id ' ||
69: ' and wsh.delivery_detail_id = wda.delivery_detail_id ' ||
70: ' and wda.delivery_id = wnd.delivery_id ' ||
71: ' and sol.source_document_line_id = prl.requisition_line_id ' ||

Line 79: po_requisition_headers_all prh , ' ||

75: ' and prh.org_id = ' || l_ou_id ||
76: ' union all ' ||
77: ' select distinct wsh.* ' ||
78: ' from wsh_delivery_details wsh , mtl_transactions_interface mti , po_requisition_lines_all prl ,
79: po_requisition_headers_all prh , ' ||
80: ' oe_order_lines_all sol ' ||
81: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
82: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
83: ' and prh.org_id = ' || l_ou_id ||

Line 93: ' po_requisition_headers_all prh , ' ||

89:
90: p_sql(6) := ' select distinct rhi.*' ||
91: ' from rcv_headers_interface rhi, ' ||
92: ' rcv_transactions_interface rti,' ||
93: ' po_requisition_headers_all prh , ' ||
94: ' po_requisition_lines_all prl ' ||
95: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
96: ' and prh.org_id = ' || l_ou_id ||
97: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 109: ' po_requisition_headers_all prh , ' ||

105:
106:
107: p_sql(7) := ' select distinct rti.*' ||
108: ' from rcv_transactions_interface rti , ' ||
109: ' po_requisition_headers_all prh , ' ||
110: ' po_requisition_lines_all prl ' ||
111: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
112: ' and prh.org_id = ' || l_ou_id ||
113: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 123: ' po_requisition_headers_all prh , ' ||

119:
120: p_sql(8) := ' select distinct pie.* ' ||
121: ' from po_interface_errors pie , ' ||
122: ' rcv_transactions_interface rti , ' ||
123: ' po_requisition_headers_all prh , ' ||
124: ' po_requisition_lines_all prl ' ||
125: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
126: ' and prh.org_id = ' || l_ou_id ||
127: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 140: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl

136:
137:
138:
139: p_sql(9) := ' select distinct rsh.* ' ||
140: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl
141: ' ||
142: ' where rsl.shipment_header_id = rsh.shipment_header_id ' ||
143: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
144: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||

Line 152: ' from rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

148: ' and prh.org_id = ' || l_ou_id ;
149:
150:
151: p_sql(10) := ' select distinct rsl.*' ||
152: ' from rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
153: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
154: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
155: ' and rsl.source_document_code = ''REQ'' ' ||
156: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 161: ' from rcv_transactions rt , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

157: ' and prh.org_id = ' || l_ou_id;
158:
159:
160: p_sql(11) := ' select distinct rt.* ' ||
161: ' from rcv_transactions rt , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
162: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
163: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
164: ' and rt.source_document_code = ''REQ'' ' ||
165: ' and rt.requisition_line_id = prl.requisition_line_id ' ||

Line 170: ' from mtl_supply ms , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

166: ' and prh.org_id = ' || l_ou_id ;
167:
168:
169: p_sql(12) := ' select distinct ms.* ' ||
170: ' from mtl_supply ms , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
171: ' where ms.req_line_id = prl.requisition_line_id ' ||
172: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
173: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
174: ' and prh.org_id = ' || l_ou_id ;

Line 178: ' from rcv_supply rs , po_requisition_headers_all prh , po_requisition_lines_all prl' ||

174: ' and prh.org_id = ' || l_ou_id ;
175:
176:
177: p_sql(13) := ' select distinct rs.* ' ||
178: ' from rcv_supply rs , po_requisition_headers_all prh , po_requisition_lines_all prl' ||
179: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
180: ' and prh.org_id = ' || l_ou_id ||
181: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
182: ' and rs.req_line_id = prl.requisition_line_id ';

Line 187: ' po_requisition_headers_all prh,' ||

183:
184: /* p_sql(14) := ' select distinct mtrl.*' ||
185: ' from mtl_txn_request_lines mtrl,' ||
186: ' rcv_shipment_lines rsl,' ||
187: ' po_requisition_headers_all prh,' ||
188: ' po_requisition_lines_all prl' ||
189: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
190: ' and prh.org_id = ' || l_ou_id ||
191: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 199: ' po_requisition_headers_all prh, ' ||

195: ' and mtrl.reference_id = rsl.shipment_line_id ';*/
196:
197: p_sql(14) := ' select distinct mtrl.* ' ||
198: ' from mtl_txn_request_lines mtrl, ' ||
199: ' po_requisition_headers_all prh, ' ||
200: ' po_requisition_lines_all prl ' ||
201: ' where prh.segment1 = '|| '''' || l_req_num || '''' ||
202: ' and prh.org_id = '|| l_ou_id ||
203: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 214: ' po_requisition_headers_all prh,' ||

210:
211: p_sql(15) := ' select distinct mti.*' ||
212: ' from mtl_transactions_interface mti,' ||
213: ' po_requisition_lines_all prl,' ||
214: ' po_requisition_headers_all prh,' ||
215: ' oe_order_lines_all sol' ||
216: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
217: ' and prh.org_id = ' || l_ou_id ||
218: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 229: ' po_requisition_headers_all prh, ' ||

225:
226: p_sql(16) := ' select distinct mmtt.* ' ||
227: ' from mtl_material_transactions_temp mmtt , ' ||
228: ' po_requisition_lines_all prl , ' ||
229: ' po_requisition_headers_all prh, ' ||
230: ' rcv_transactions rt' ||
231: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
232: ' and prh.org_id = ' || l_ou_id ||
233: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 241: ' po_requisition_headers_all prh,' ||

237: ' UNION ALL' ||
238: ' select distinct mmtt.* ' ||
239: ' from mtl_material_transactions_temp mmtt, ' ||
240: ' po_requisition_lines_all prl, ' ||
241: ' po_requisition_headers_all prh,' ||
242: ' oe_order_lines_all sol ' ||
243: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
244: ' and prh.org_id = ' || l_ou_id ||
245: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 256: ' po_requisition_headers_all prh , ' ||

252:
253: p_sql(17) := ' select distinct mmt.* ' ||
254: ' from mtl_material_transactions mmt, ' ||
255: ' po_requisition_lines_all prl, ' ||
256: ' po_requisition_headers_all prh , ' ||
257: ' rcv_transactions rt' ||
258: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
259: ' and prh.org_id = ' || l_ou_id ||
260: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 268: ' po_requisition_headers_all prh,' ||

264: ' UNION ALL' ||
265: ' select distinct mmt.* ' ||
266: ' from mtl_material_transactions mmt, ' ||
267: ' po_requisition_lines_all prl, ' ||
268: ' po_requisition_headers_all prh,' ||
269: ' oe_order_lines_all sol ' ||
270: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
271: ' and prh.org_id = ' || l_ou_id ||
272: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 281: ' from mtl_reservations mr , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||

277: ' and mmt.transaction_action_id=21 ';
278:
279:
280: p_sql(18) := ' select distinct mr.* ' ||
281: ' from mtl_reservations mr , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||
282: ' where sol.source_document_line_id = prl.requisition_line_id ' ||
283: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
284: ' and sol.source_document_type_id = 10 ' ||
285: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 292: ' from mtl_reservations mr , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all

288: ' and mr.demand_source_line_id = sol.line_id ' ||
289: ' and mr.demand_source_type_id = 8 ' ||
290: ' union all ' ||
291: ' select distinct mr.* ' ||
292: ' from mtl_reservations mr , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all
293: prh , ' ||
294: ' oe_order_lines_all sol ' ||
295: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
296: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 307: ' from mtl_demand md , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||

303:
304:
305:
306: p_sql(19) := ' select distinct md.* ' ||
307: ' from mtl_demand md , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||
308: ' where sol.source_document_line_id = prl.requisition_line_id ' ||
309: ' and sol.source_document_type_id = 10 ' ||
310: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
311: ' and prl.source_type_code = ''INVENTORY''' ||

Line 318: ' from mtl_demand md , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all prh ,

314: ' and md.demand_source_line = sol.line_id ' ||
315: ' and md.demand_source_type = 8 ' ||
316: ' union all ' ||
317: ' select distinct md.* ' ||
318: ' from mtl_demand md , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all prh ,
319: oe_order_lines_all ' ||
320: ' sol ' ||
321: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
322: ' and prl.source_type_code = ''INVENTORY''' ||

Line 335: ' po_requisition_headers_all prh ,' ||

331: p_sql(20) := ' select distinct msn.* ' ||
332: ' from mtl_serial_numbers msn , ' ||
333: ' mtl_material_transactions mmt , ' ||
334: ' po_requisition_lines_all prl , ' ||
335: ' po_requisition_headers_all prh ,' ||
336: ' rcv_transactions rt' ||
337: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
338: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
339: ' and prh.org_id = ' || l_ou_id ||

Line 350: ' po_requisition_headers_all prh ,' ||

346: ' select distinct msn.* ' ||
347: ' from mtl_serial_numbers msn , ' ||
348: ' mtl_material_transactions mmt , ' ||
349: ' po_requisition_lines_all prl , ' ||
350: ' po_requisition_headers_all prh ,' ||
351: ' oe_order_lines_all sol ' ||
352: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
353: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
354: ' and prh.org_id = ' || l_ou_id ||

Line 368: ' po_requisition_headers_all prh ,' ||

364:
365:
366: p_sql(21) := ' select DISTINCT msnt.*' ||
367: ' from po_requisition_lines_all prl ,' ||
368: ' po_requisition_headers_all prh ,' ||
369: ' mtl_serial_numbers_temp msnt ,' ||
370: ' mtl_system_items msi,' ||
371: ' rcv_transactions_interface rti' ||
372: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 387: ' po_requisition_headers_all prh ,' ||

383: ' and msi.lot_control_code = 1' ||
384: ' UNION ALL' ||
385: ' select DISTINCT msnt.*' ||
386: ' from po_requisition_lines_all prl ,' ||
387: ' po_requisition_headers_all prh ,' ||
388: ' mtl_serial_numbers_temp msnt ,' ||
389: ' mtl_transaction_lots_temp mtlt,' ||
390: ' mtl_system_items msi,' ||
391: ' rcv_transactions_interface rti' ||

Line 408: ' po_requisition_headers_all prh,' ||

404: ' and msi.lot_control_code <> 1' ||
405: ' UNION ALL' ||
406: ' select DISTINCT msnt.*' ||
407: ' from po_requisition_lines_all prl,' ||
408: ' po_requisition_headers_all prh,' ||
409: ' mtl_serial_numbers_temp msnt,' ||
410: ' mtl_system_items msi,' ||
411: ' oe_order_lines_all sol,' ||
412: ' mtl_material_transactions_temp mmtt' ||

Line 428: ' po_requisition_headers_all prh,' ||

424: ' and msi.lot_control_code = 1' ||
425: ' UNION ALL' ||
426: ' select DISTINCT msnt.*' ||
427: ' from po_requisition_lines_all prl,' ||
428: ' po_requisition_headers_all prh,' ||
429: ' mtl_serial_numbers_temp msnt,' ||
430: ' mtl_transaction_lots_temp mtlt,' ||
431: ' mtl_system_items msi,' ||
432: ' oe_order_lines_all sol,' ||

Line 453: ' po_requisition_headers_all prh , ' ||

449:
450: p_sql(22) := ' select distinct msni.* ' ||
451: ' from rcv_transactions_interface rti ,' ||
452: ' po_requisition_lines_all prl , ' ||
453: ' po_requisition_headers_all prh , ' ||
454: ' mtl_serial_numbers_interface msni ,' ||
455: ' mtl_system_items msi' ||
456: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
457: ' and prh.org_id = ' || l_ou_id ||

Line 472: ' po_requisition_headers_all prh , ' ||

468: ' UNION ALL' ||
469: ' select distinct msni.* ' ||
470: ' from rcv_transactions_interface rti ,' ||
471: ' po_requisition_lines_all prl , ' ||
472: ' po_requisition_headers_all prh , ' ||
473: ' mtl_serial_numbers_interface msni ,' ||
474: ' mtl_transaction_lots_interface mtli,' ||
475: ' mtl_system_items msi' ||
476: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 496: ' po_requisition_headers_all prh , ' ||

492:
493: p_sql(23) := ' select distinct mut.* ' ||
494: ' from mtl_material_transactions mmt , ' ||
495: ' po_requisition_lines_all prl , ' ||
496: ' po_requisition_headers_all prh , ' ||
497: ' mtl_unit_transactions mut , ' ||
498: ' mtl_system_items msi,' ||
499: ' rcv_transactions rt' ||
500: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 515: ' po_requisition_headers_all prh ,' ||

511: ' union all ' ||
512: ' select distinct mut.*' ||
513: ' from mtl_material_transactions mmt ,' ||
514: ' po_requisition_lines_all prl ,' ||
515: ' po_requisition_headers_all prh ,' ||
516: ' mtl_unit_transactions mut , ' ||
517: ' mtl_system_items msi , ' ||
518: ' rcv_transactions rt , ' ||
519: ' mtl_transaction_lot_numbers mtln ' ||

Line 536: ' po_requisition_headers_all prh , ' ||

532: ' union all' ||
533: ' select distinct mut.* ' ||
534: ' from mtl_material_transactions mmt , ' ||
535: ' po_requisition_lines_all prl , ' ||
536: ' po_requisition_headers_all prh , ' ||
537: ' mtl_unit_transactions mut , ' ||
538: ' mtl_system_items msi , ' ||
539: ' oe_order_lines_all sol ' ||
540: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 556: ' po_requisition_headers_all prh , ' ||

552: ' union all' ||
553: ' select distinct mut.*' ||
554: ' from mtl_material_transactions mmt , ' ||
555: ' po_requisition_lines_all prl , ' ||
556: ' po_requisition_headers_all prh , ' ||
557: ' mtl_unit_transactions mut , ' ||
558: ' mtl_system_items msi , ' ||
559: ' oe_order_lines_all sol , ' ||
560: ' mtl_transaction_lot_numbers mtln ' ||

Line 578: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl '

574:
575:
576:
577: p_sql(24) := ' select distinct rss.* ' ||
578: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl '
579: ||
580: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
581: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
582: ' and rss.shipment_line_id = rsl.shipment_line_id ' ||

Line 591: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all

587: ' order by rss.supply_type_code , rss.serial_num ';
588:
589:
590: p_sql(25) := ' select distinct rst.* ' ||
591: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all
592: prl ' ||
593: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
594: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
595: ' and rsl.source_document_code = ''REQ'' ' ||

Line 604: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all

600: ' order by rst.serial_transaction_type , rst.serial_num ';
601:
602:
603: p_sql(26) := ' select distinct rsi.* ' ||
604: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all
605: prl ' ||
606: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
607: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
608: ' and rsl.source_document_code = ''REQ'' ' ||

Line 621: ' po_requisition_headers_all prh , ' ||

617: ' from mtl_lot_numbers mln , ' ||
618: ' mtl_transaction_lot_numbers mtln , ' ||
619: ' mtl_material_transactions mmt , ' ||
620: ' po_requisition_lines_all prl , ' ||
621: ' po_requisition_headers_all prh , ' ||
622: ' rcv_transactions rt' ||
623: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
624: ' and prh.org_id = ' || l_ou_id ||
625: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 638: ' po_requisition_headers_all prh , ' ||

634: ' from mtl_lot_numbers mln , ' ||
635: ' mtl_transaction_lot_numbers mtln , ' ||
636: ' mtl_material_transactions mmt , ' ||
637: ' po_requisition_lines_all prl , ' ||
638: ' po_requisition_headers_all prh , ' ||
639: ' oe_order_lines_all sol' ||
640: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
641: ' and prh.org_id = ' || l_ou_id ||
642: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 657: ' po_requisition_headers_all prh ,' ||

653: p_sql(28) := ' select distinct mtln.* ' ||
654: ' from mtl_transaction_lot_numbers mtln , ' ||
655: ' mtl_material_transactions mmt , ' ||
656: ' po_requisition_lines_all prl , ' ||
657: ' po_requisition_headers_all prh ,' ||
658: ' rcv_transactions rt' ||
659: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
660: ' and prh.org_id = ' || l_ou_id ||
661: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 670: ' po_requisition_headers_all prh ,' ||

666: ' select distinct mtln.* ' ||
667: ' from mtl_transaction_lot_numbers mtln , ' ||
668: ' mtl_material_transactions mmt , ' ||
669: ' po_requisition_lines_all prl , ' ||
670: ' po_requisition_headers_all prh ,' ||
671: ' oe_order_lines_all sol' ||
672: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
673: ' and prh.org_id = ' || l_ou_id ||
674: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 686: ' po_requisition_headers_all prh ' ||

682: p_sql(29) := ' select distinct mtli.* ' ||
683: ' from mtl_transaction_lots_interface mtli , ' ||
684: ' mtl_transactions_interface mti , ' ||
685: ' po_requisition_lines_all prl , ' ||
686: ' po_requisition_headers_all prh ' ||
687: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
688: ' and prh.org_id = ' || l_ou_id ||
689: ' and prl.requisition_header_id = prh.requisition_header_id' ||
690: ' and mti.requisition_line_id = prl.requisition_line_id' ||

Line 697: ' po_requisition_headers_all prh ' ||

693: ' select distinct mtli.* ' ||
694: ' from mtl_transaction_lots_interface mtli , ' ||
695: ' rcv_transactions_interface rti , ' ||
696: ' po_requisition_lines_all prl , ' ||
697: ' po_requisition_headers_all prh ' ||
698: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
699: ' and prh.org_id = ' || l_ou_id ||
700: ' and prl.requisition_header_id = prh.requisition_header_id' ||
701: ' and rti.interface_transaction_id = mtli.product_transaction_id' ||

Line 712: ' po_requisition_headers_all prh ' ||

708: p_sql(30) := ' select distinct mtlt.* ' ||
709: ' from mtl_transaction_lots_temp mtlt ,' ||
710: ' rcv_transactions_interface rti, ' ||
711: ' po_requisition_lines_all prl , ' ||
712: ' po_requisition_headers_all prh ' ||
713: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
714: ' and prh.org_id = ' || l_ou_id ||
715: ' and prl.requisition_header_id = prh.requisition_header_id' ||
716: ' and rti.interface_transaction_id = mtlt.product_transaction_id' ||

Line 726: ' po_requisition_headers_all prh,' ||

722: ' from mtl_transaction_lots_temp mtlt ,' ||
723: ' mtl_material_transactions_temp mmtt,' ||
724: ' rcv_transactions_interface rti, ' ||
725: ' po_requisition_lines_all prl , ' ||
726: ' po_requisition_headers_all prh,' ||
727: ' oe_order_lines_all sol' ||
728: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
729: ' and prh.org_id = ' || l_ou_id ||
730: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 739: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl' ||

735:
736:
737:
738: p_sql(31) := ' select distinct rls.* ' ||
739: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl' ||
740: ' where rsl.shipment_line_id = rls.shipment_line_id ' ||
741: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
742: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
743: ' and rsl.source_document_code = ''REQ'' ' ||

Line 749: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl

745: ' and prh.org_id = ' || l_ou_id ;
746:
747:
748: p_sql(32) := ' select distinct rlt.* ' ||
749: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl
750: ' ||
751: ' where rsl.shipment_line_id = rlt.shipment_line_id ' ||
752: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
753: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 760: ' from rcv_lots_interface rli , rcv_transactions_interface rti , po_requisition_headers_all prh ,

756: ' and prh.org_id = ' || l_ou_id ;
757:
758:
759: p_sql(33) := ' select distinct rli.* ' ||
760: ' from rcv_lots_interface rli , rcv_transactions_interface rti , po_requisition_headers_all prh ,
761: po_requisition_lines_all prl ' ||
762: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
763: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
764: ' and prh.org_id = ' || l_ou_id ||

Line 772: ' po_requisition_headers_all prh,' ||

768:
769:
770: p_sql(34) := ' select distinct msi.*' ||
771: ' from po_requisition_lines_all prl,' ||
772: ' po_requisition_headers_all prh,' ||
773: ' mtl_system_items msi' ||
774: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
775: ' and prh.org_id = ' || l_ou_id ||
776: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 792: ' po_requisition_headers_all prh ,' ||

788: ' mtt.disable_date ' ||
789: ' from mtl_transaction_types mtt , ' ||
790: ' mtl_material_transactions mmt , ' ||
791: ' po_requisition_lines_all prl , ' ||
792: ' po_requisition_headers_all prh ,' ||
793: ' rcv_transactions rt' ||
794: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
795: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
796: ' and prh.org_id = ' || l_ou_id ||

Line 810: ' po_requisition_headers_all prh,' ||

806: ' mtt.disable_date ' ||
807: ' from mtl_transaction_types mtt , ' ||
808: ' mtl_material_transactions mmt , ' ||
809: ' po_requisition_lines_all prl , ' ||
810: ' po_requisition_headers_all prh,' ||
811: ' oe_order_lines_all sol ' ||
812: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
813: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
814: ' and prh.org_id = ' || l_ou_id ||

Line 826: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||

822: p_sql(36) := ' select distinct ood.* ' ||
823: ' from org_organization_definitions ood ' ||
824: ' where exists (' ||
825: ' select 1 ' ||
826: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||
827: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
828: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
829: ' and prh.org_id = ' || l_ou_id ||
830: ' and prh.type_lookup_code in (''INTERNAL'',''PURCHASE'')' ||

Line 839: ' from mtl_parameters mp , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

835:
836:
837:
838: p_sql(37) := ' select distinct mp.* ' ||
839: ' from mtl_parameters mp , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
840: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
841: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
842: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
843: ' and (prl.destination_organization_id = mp.organization_id ' ||

Line 852: ' from po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

848: p_sql(38) := ' select distinct miop.* ' ||
849: ' from mtl_interorg_parameters miop ' ||
850: ' where exists (' ||
851: ' select 1 ' ||
852: ' from po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
853: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
854: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
855: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
856: ' and (prl.destination_organization_id = miop.to_organization_id ' ||

Line 865: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||

861: p_sql(39) := ' select distinct rp.* ' ||
862: ' from rcv_parameters rp ' ||
863: ' where exists (' ||
864: ' select 1 ' ||
865: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||
866: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
867: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
868: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
869: ' and (prl.destination_organization_id = rp.organization_id ' ||

Line 922: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;

918:
919: PROCEDURE req_line_sql(p_ou_id IN NUMBER, p_req_num IN VARCHAR2, p_line_num IN NUMBER, p_sql IN OUT NOCOPY
920: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
921:
922: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
923: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
924: l_line_num po_requisition_lines_all.line_num%TYPE := p_line_num;
925: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := NULL;
926: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := NULL;

Line 923: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;

919: PROCEDURE req_line_sql(p_ou_id IN NUMBER, p_req_num IN VARCHAR2, p_line_num IN NUMBER, p_sql IN OUT NOCOPY
920: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
921:
922: l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
923: l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
924: l_line_num po_requisition_lines_all.line_num%TYPE := p_line_num;
925: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := NULL;
926: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := NULL;
927: l_org_id rcv_shipment_headers.organization_id%TYPE := NULL;

Line 932: ' from po_requisition_headers_all prh,' ||

928:
929: BEGIN
930:
931: p_sql(1) := ' select distinct prh.*' ||
932: ' from po_requisition_headers_all prh,' ||
933: ' po_requisition_lines_all prl' ||
934: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
935: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
936: 'and prh.org_id = ' || l_ou_id ||

Line 943: ' po_requisition_headers_all prh ' ||

939:
940:
941: p_sql(2) := ' select distinct prl.*' ||
942: ' from po_requisition_lines_all prl,' ||
943: ' po_requisition_headers_all prh ' ||
944: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
945: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
946: ' and prh.org_id = ' || l_ou_id ||
947: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 955: ' po_requisition_headers_all prh' ||

951:
952: p_sql(3) := ' select distinct prd.*' ||
953: ' from po_req_distributions_all prd ,' ||
954: ' po_requisition_lines_all prl ,' ||
955: ' po_requisition_headers_all prh' ||
956: ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
957: ' and prh.requisition_header_id = prl.requisition_header_id' ||
958: ' and prl.requisition_line_id = prd.requisition_line_id' ||
959: ' and prl.source_type_code = ''INVENTORY''' ||

Line 969: ' po_requisition_headers_all prh' ||

965:
966: p_sql(4) := ' select distinct oel.*' ||
967: ' from oe_order_lines_all oel,' ||
968: ' po_requisition_lines_all prl,' ||
969: ' po_requisition_headers_all prh' ||
970: ' where oel.source_document_type_id = 10' ||
971: ' and oel.source_document_line_id = prl.requisition_line_id' ||
972: ' and prl.requisition_header_id = prh.requisition_header_id' ||
973: ' and prl.source_type_code = ''INVENTORY''' ||

Line 982: ' prl , po_requisition_headers_all prh ' ||

978:
979: p_sql(5) := ' select distinct wsh.* ' ||
980: ' from wsh_delivery_details wsh , wsh_delivery_assignments wda , wsh_new_deliveries wnd , oe_order_lines_all sol ,
981: po_requisition_lines_all ' ||
982: ' prl , po_requisition_headers_all prh ' ||
983: ' where wsh.source_line_id = sol.line_id ' ||
984: ' and wsh.delivery_detail_id = wda.delivery_detail_id ' ||
985: ' and wda.delivery_id = wnd.delivery_id ' ||
986: ' and sol.source_document_line_id = prl.requisition_line_id ' ||

Line 996: po_requisition_headers_all prh , ' ||

992: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
993: ' union all ' ||
994: ' select distinct wsh.* ' ||
995: ' from wsh_delivery_details wsh , mtl_transactions_interface mti , po_requisition_lines_all prl ,
996: po_requisition_headers_all prh , ' ||
997: ' oe_order_lines_all sol ' ||
998: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
999: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
1000: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1012: ' po_requisition_headers_all prh , ' ||

1008:
1009: p_sql(6) := ' select distinct rhi.*' ||
1010: ' from rcv_headers_interface rhi, ' ||
1011: ' rcv_transactions_interface rti,' ||
1012: ' po_requisition_headers_all prh , ' ||
1013: ' po_requisition_lines_all prl ' ||
1014: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1015: ' and prh.org_id = ' || l_ou_id ||
1016: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 1028: ' po_requisition_headers_all prh , ' ||

1024:
1025:
1026: p_sql(7) := ' select distinct rti.*' ||
1027: ' from rcv_transactions_interface rti , ' ||
1028: ' po_requisition_headers_all prh , ' ||
1029: ' po_requisition_lines_all prl ' ||
1030: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1031: ' and prh.org_id = ' || l_ou_id ||
1032: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1043: ' po_requisition_headers_all prh , ' ||

1039:
1040: p_sql(8) := ' select distinct pie.* ' ||
1041: ' from po_interface_errors pie , ' ||
1042: ' rcv_transactions_interface rti , ' ||
1043: ' po_requisition_headers_all prh , ' ||
1044: ' po_requisition_lines_all prl ' ||
1045: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1046: ' and prh.org_id = ' || l_ou_id ||
1047: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 1059: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl

1055: ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'' ';
1056:
1057:
1058: p_sql(9) := ' select distinct rsh.* ' ||
1059: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl
1060: ' ||
1061: ' where rsl.shipment_header_id = rsh.shipment_header_id ' ||
1062: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1063: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||

Line 1073: ' from rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

1069:
1070:
1071:
1072: p_sql(10) := ' select distinct rsl.*' ||
1073: ' from rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
1074: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1075: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
1076: ' and prl.source_type_code = ''INVENTORY''' ||
1077: ' and rsl.source_document_code = ''REQ'' ' ||

Line 1084: ' from rcv_transactions rt , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

1080: ' and prl.line_num = nvl(' || l_line_num || ',-99)';
1081:
1082:
1083: p_sql(11) := ' select distinct rt.* ' ||
1084: ' from rcv_transactions rt , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
1085: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1086: ' and prl.source_type_code = ''INVENTORY''' ||
1087: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1088: ' and rt.requisition_line_id = prl.requisition_line_id ' ||

Line 1095: ' from mtl_supply ms , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

1091:
1092:
1093:
1094: p_sql(12) := ' select distinct ms.* ' ||
1095: ' from mtl_supply ms , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
1096: ' where ms.req_line_id = prl.requisition_line_id ' ||
1097: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1098: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
1099: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1105: ' from rcv_supply rs , po_requisition_headers_all prh, po_requisition_lines_all prl ' ||

1101: ' and prl.line_num = nvl(' || l_line_num || ',-99)';
1102:
1103:
1104: p_sql(13) := ' select distinct rs.* ' ||
1105: ' from rcv_supply rs , po_requisition_headers_all prh, po_requisition_lines_all prl ' ||
1106: ' where rs.req_line_id = prl.requisition_line_id ' ||
1107: ' and prl.source_type_code = ''INVENTORY''' ||
1108: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1109: ' and prh.org_id = ' || l_ou_id ||

Line 1116: ' po_requisition_headers_all prh, ' ||

1112:
1113:
1114: p_sql(14) := ' select distinct mtrl.* ' ||
1115: ' from mtl_txn_request_lines mtrl, ' ||
1116: ' po_requisition_headers_all prh, ' ||
1117: ' po_requisition_lines_all prl ' ||
1118: ' where prh.segment1 = '|| '''' || l_req_num || '''' ||
1119: ' and prh.org_id = '||l_ou_id ||
1120: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1131: ' po_requisition_headers_all prh,' ||

1127:
1128: /* ' select distinct mtrl.*' ||
1129: ' from mtl_txn_request_lines mtrl,' ||
1130: ' rcv_shipment_lines rsl,' ||
1131: ' po_requisition_headers_all prh,' ||
1132: ' po_requisition_lines_all prl' ||
1133: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1134: ' and prh.org_id = ' || l_ou_id ||
1135: ' and prh.requisition_header_id = prl.requisition_header_id' ||

Line 1146: ' po_requisition_headers_all prh,' ||

1142:
1143: p_sql(15) := ' select distinct mti.*' ||
1144: ' from mtl_transactions_interface mti,' ||
1145: ' po_requisition_lines_all prl,' ||
1146: ' po_requisition_headers_all prh,' ||
1147: ' oe_order_lines_all sol' ||
1148: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1149: ' and prh.org_id = ' || l_ou_id ||
1150: ' and prl.source_type_code = ''INVENTORY''' ||

Line 1162: ' po_requisition_headers_all prh, ' ||

1158:
1159: p_sql(16) := ' select distinct mmtt.* ' ||
1160: ' from mtl_material_transactions_temp mmtt , ' ||
1161: ' po_requisition_lines_all prl , ' ||
1162: ' po_requisition_headers_all prh, ' ||
1163: ' rcv_transactions rt' ||
1164: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1165: ' and prh.org_id = ' || l_ou_id ||
1166: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1175: ' po_requisition_headers_all prh,' ||

1171: ' UNION ALL' ||
1172: ' select distinct mmtt.* ' ||
1173: ' from mtl_material_transactions_temp mmtt, ' ||
1174: ' po_requisition_lines_all prl, ' ||
1175: ' po_requisition_headers_all prh,' ||
1176: ' oe_order_lines_all sol ' ||
1177: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1178: ' and prh.org_id = ' || l_ou_id ||
1179: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1190: ' po_requisition_headers_all prh , ' ||

1186:
1187: p_sql(17) := ' select distinct mmt.* ' ||
1188: ' from mtl_material_transactions mmt, ' ||
1189: ' po_requisition_lines_all prl, ' ||
1190: ' po_requisition_headers_all prh , ' ||
1191: ' rcv_transactions rt' ||
1192: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1193: ' and prh.org_id = ' || l_ou_id ||
1194: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1203: ' po_requisition_headers_all prh,' ||

1199: ' UNION ALL' ||
1200: ' select distinct mmt.* ' ||
1201: ' from mtl_material_transactions mmt, ' ||
1202: ' po_requisition_lines_all prl, ' ||
1203: ' po_requisition_headers_all prh,' ||
1204: ' oe_order_lines_all sol ' ||
1205: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1206: ' and prh.org_id = ' || l_ou_id ||
1207: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1217: ' from mtl_reservations mr , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||

1213: ' and mmt.transaction_action_id=21 ';
1214:
1215:
1216: p_sql(18) := ' select distinct mr.* ' ||
1217: ' from mtl_reservations mr , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||
1218: ' where sol.source_document_line_id = prl.requisition_line_id ' ||
1219: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
1220: ' and prh.type_lookup_code = ''INTERNAL'' ' ||
1221: ' and sol.source_document_type_id = 10 ' ||

Line 1229: ' from mtl_reservations mr , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all

1225: ' and mr.demand_source_line_id = sol.line_id ' ||
1226: ' and mr.demand_source_type_id = 8 ' ||
1227: ' union all ' ||
1228: ' select distinct mr.* ' ||
1229: ' from mtl_reservations mr , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all
1230: prh , ' ||
1231: ' oe_order_lines_all sol ' ||
1232: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1233: ' and prh.type_lookup_code = ''INTERNAL'' ' ||

Line 1243: ' from mtl_demand md , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||

1239: ' and mti.trx_source_line_id = sol.line_id ' ||
1240: ' and mr.demand_source_line_id = mti.trx_source_line_id ';
1241:
1242: p_sql(19) := ' select distinct md.* ' ||
1243: ' from mtl_demand md , oe_order_lines_all sol , po_requisition_lines_all prl , po_requisition_headers_all prh ' ||
1244: ' where sol.source_document_line_id = prl.requisition_line_id ' ||
1245: ' and sol.source_document_type_id = 10 ' ||
1246: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
1247: ' and prh.type_lookup_code = ''INTERNAL'' ' ||

Line 1255: ' from mtl_demand md , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all prh ,

1251: ' and md.demand_source_line = sol.line_id ' ||
1252: ' and md.demand_source_type = 8 ' ||
1253: ' union all ' ||
1254: ' select distinct md.* ' ||
1255: ' from mtl_demand md , mtl_transactions_interface mti , po_requisition_lines_all prl , po_requisition_headers_all prh ,
1256: oe_order_lines_all ' ||
1257: ' sol ' ||
1258: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1259: ' and prh.type_lookup_code = ''INTERNAL'' ' ||

Line 1273: ' po_requisition_headers_all prh ,' ||

1269: p_sql(20) := ' select distinct msn.* ' ||
1270: ' from mtl_serial_numbers msn , ' ||
1271: ' mtl_material_transactions mmt , ' ||
1272: ' po_requisition_lines_all prl , ' ||
1273: ' po_requisition_headers_all prh ,' ||
1274: ' rcv_transactions rt' ||
1275: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1276: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
1277: ' and prh.org_id = ' || l_ou_id ||

Line 1289: ' po_requisition_headers_all prh ,' ||

1285: ' select distinct msn.* ' ||
1286: ' from mtl_serial_numbers msn , ' ||
1287: ' mtl_material_transactions mmt , ' ||
1288: ' po_requisition_lines_all prl , ' ||
1289: ' po_requisition_headers_all prh ,' ||
1290: ' oe_order_lines_all sol ' ||
1291: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1292: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
1293: ' and prh.org_id = ' || l_ou_id ||

Line 1306: ' po_requisition_headers_all prh ,' ||

1302:
1303:
1304: p_sql(21) := ' select DISTINCT msnt.*' ||
1305: ' from po_requisition_lines_all prl ,' ||
1306: ' po_requisition_headers_all prh ,' ||
1307: ' mtl_serial_numbers_temp msnt ,' ||
1308: ' mtl_system_items msi,' ||
1309: ' rcv_transactions_interface rti' ||
1310: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1326: ' po_requisition_headers_all prh ,' ||

1322: ' and msi.lot_control_code = 1' ||
1323: ' UNION ALL' ||
1324: ' select DISTINCT msnt.*' ||
1325: ' from po_requisition_lines_all prl ,' ||
1326: ' po_requisition_headers_all prh ,' ||
1327: ' mtl_serial_numbers_temp msnt ,' ||
1328: ' mtl_transaction_lots_temp mtlt,' ||
1329: ' mtl_system_items msi,' ||
1330: ' rcv_transactions_interface rti' ||

Line 1348: ' po_requisition_headers_all prh,' ||

1344: ' and msi.lot_control_code <> 1' ||
1345: ' UNION ALL' ||
1346: ' select DISTINCT msnt.*' ||
1347: ' from po_requisition_lines_all prl,' ||
1348: ' po_requisition_headers_all prh,' ||
1349: ' mtl_serial_numbers_temp msnt,' ||
1350: ' mtl_system_items msi,' ||
1351: ' oe_order_lines_all sol,' ||
1352: ' mtl_material_transactions_temp mmtt' ||

Line 1369: ' po_requisition_headers_all prh,' ||

1365: ' and msi.lot_control_code = 1' ||
1366: ' UNION ALL' ||
1367: ' select DISTINCT msnt.*' ||
1368: ' from po_requisition_lines_all prl,' ||
1369: ' po_requisition_headers_all prh,' ||
1370: ' mtl_serial_numbers_temp msnt,' ||
1371: ' mtl_transaction_lots_temp mtlt,' ||
1372: ' mtl_system_items msi,' ||
1373: ' oe_order_lines_all sol,' ||

Line 1396: ' po_requisition_headers_all prh , ' ||

1392:
1393: p_sql(22) := ' select distinct msni.* ' ||
1394: ' from rcv_transactions_interface rti ,' ||
1395: ' po_requisition_lines_all prl , ' ||
1396: ' po_requisition_headers_all prh , ' ||
1397: ' mtl_serial_numbers_interface msni ,' ||
1398: ' mtl_system_items msi' ||
1399: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1400: ' and prh.org_id = ' || l_ou_id ||

Line 1416: ' po_requisition_headers_all prh , ' ||

1412: ' UNION ALL' ||
1413: ' select distinct msni.* ' ||
1414: ' from rcv_transactions_interface rti ,' ||
1415: ' po_requisition_lines_all prl , ' ||
1416: ' po_requisition_headers_all prh , ' ||
1417: ' mtl_serial_numbers_interface msni ,' ||
1418: ' mtl_transaction_lots_interface mtli,' ||
1419: ' mtl_system_items msi' ||
1420: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1440: ' po_requisition_headers_all prh , ' ||

1436:
1437: p_sql(23) := ' select distinct mut.* ' ||
1438: ' from mtl_material_transactions mmt , ' ||
1439: ' po_requisition_lines_all prl , ' ||
1440: ' po_requisition_headers_all prh , ' ||
1441: ' mtl_unit_transactions mut , ' ||
1442: ' mtl_system_items msi,' ||
1443: ' rcv_transactions rt' ||
1444: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1460: ' po_requisition_headers_all prh ,' ||

1456: ' union all ' ||
1457: ' select distinct mut.*' ||
1458: ' from mtl_material_transactions mmt ,' ||
1459: ' po_requisition_lines_all prl ,' ||
1460: ' po_requisition_headers_all prh ,' ||
1461: ' mtl_unit_transactions mut , ' ||
1462: ' mtl_system_items msi , ' ||
1463: ' rcv_transactions rt , ' ||
1464: ' mtl_transaction_lot_numbers mtln ' ||

Line 1482: ' po_requisition_headers_all prh , ' ||

1478: ' union all' ||
1479: ' select distinct mut.* ' ||
1480: ' from mtl_material_transactions mmt , ' ||
1481: ' po_requisition_lines_all prl , ' ||
1482: ' po_requisition_headers_all prh , ' ||
1483: ' mtl_unit_transactions mut , ' ||
1484: ' mtl_system_items msi , ' ||
1485: ' oe_order_lines_all sol ' ||
1486: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1503: ' po_requisition_headers_all prh , ' ||

1499: ' union all' ||
1500: ' select distinct mut.*' ||
1501: ' from mtl_material_transactions mmt , ' ||
1502: ' po_requisition_lines_all prl , ' ||
1503: ' po_requisition_headers_all prh , ' ||
1504: ' mtl_unit_transactions mut , ' ||
1505: ' mtl_system_items msi , ' ||
1506: ' oe_order_lines_all sol , ' ||
1507: ' mtl_transaction_lot_numbers mtln ' ||

Line 1526: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl '

1522:
1523:
1524:
1525: p_sql(24) := ' select distinct rss.* ' ||
1526: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl '
1527: ||
1528: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1529: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
1530: ' and rss.shipment_line_id = rsl.shipment_line_id ' ||

Line 1540: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all

1536: ' order by rss.supply_type_code , rss.serial_num ';
1537:
1538:
1539: p_sql(25) := ' select distinct rst.* ' ||
1540: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all
1541: prl ' ||
1542: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1543: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
1544: ' and rsl.source_document_code = ''REQ'' ' ||

Line 1555: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all

1551:
1552:
1553:
1554: p_sql(26) := ' select distinct rsi.* ' ||
1555: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all
1556: prl ' ||
1557: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1558: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
1559: ' and rsl.source_document_code = ''REQ'' ' ||

Line 1573: ' po_requisition_headers_all prh , ' ||

1569: ' from mtl_lot_numbers mln , ' ||
1570: ' mtl_transaction_lot_numbers mtln , ' ||
1571: ' mtl_material_transactions mmt , ' ||
1572: ' po_requisition_lines_all prl , ' ||
1573: ' po_requisition_headers_all prh , ' ||
1574: ' rcv_transactions rt' ||
1575: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1576: ' and prh.org_id = ' || l_ou_id ||
1577: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 1591: ' po_requisition_headers_all prh , ' ||

1587: ' from mtl_lot_numbers mln , ' ||
1588: ' mtl_transaction_lot_numbers mtln , ' ||
1589: ' mtl_material_transactions mmt , ' ||
1590: ' po_requisition_lines_all prl , ' ||
1591: ' po_requisition_headers_all prh , ' ||
1592: ' oe_order_lines_all sol' ||
1593: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1594: ' and prh.org_id = ' || l_ou_id ||
1595: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 1610: ' po_requisition_headers_all prh ,' ||

1606: p_sql(28) := ' select distinct mtln.* ' ||
1607: ' from mtl_transaction_lot_numbers mtln , ' ||
1608: ' mtl_material_transactions mmt , ' ||
1609: ' po_requisition_lines_all prl , ' ||
1610: ' po_requisition_headers_all prh ,' ||
1611: ' rcv_transactions rt' ||
1612: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1613: ' and prh.org_id = ' || l_ou_id ||
1614: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 1624: ' po_requisition_headers_all prh ,' ||

1620: ' select distinct mtln.* ' ||
1621: ' from mtl_transaction_lot_numbers mtln , ' ||
1622: ' mtl_material_transactions mmt , ' ||
1623: ' po_requisition_lines_all prl , ' ||
1624: ' po_requisition_headers_all prh ,' ||
1625: ' oe_order_lines_all sol' ||
1626: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1627: ' and prh.org_id = ' || l_ou_id ||
1628: ' and prl.requisition_header_id = prh.requisition_header_id ' ||

Line 1640: ' po_requisition_headers_all prh ' ||

1636: p_sql(29) := ' select distinct mtli.* ' ||
1637: ' from mtl_transaction_lots_interface mtli , ' ||
1638: ' mtl_transactions_interface mti , ' ||
1639: ' po_requisition_lines_all prl , ' ||
1640: ' po_requisition_headers_all prh ' ||
1641: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1642: ' and prh.org_id = ' || l_ou_id ||
1643: ' and prl.requisition_header_id = prh.requisition_header_id' ||
1644: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||

Line 1652: ' po_requisition_headers_all prh ' ||

1648: ' select distinct mtli.* ' ||
1649: ' from mtl_transaction_lots_interface mtli , ' ||
1650: ' rcv_transactions_interface rti , ' ||
1651: ' po_requisition_lines_all prl , ' ||
1652: ' po_requisition_headers_all prh ' ||
1653: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1654: ' and prh.org_id = ' || l_ou_id ||
1655: ' and prl.requisition_header_id = prh.requisition_header_id' ||
1656: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||

Line 1668: ' po_requisition_headers_all prh ' ||

1664: p_sql(30) := ' select distinct mtlt.* ' ||
1665: ' from mtl_transaction_lots_temp mtlt ,' ||
1666: ' rcv_transactions_interface rti, ' ||
1667: ' po_requisition_lines_all prl , ' ||
1668: ' po_requisition_headers_all prh ' ||
1669: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1670: ' and prh.org_id = ' || l_ou_id ||
1671: ' and prl.requisition_header_id = prh.requisition_header_id' ||
1672: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||

Line 1683: ' po_requisition_headers_all prh,' ||

1679: ' from mtl_transaction_lots_temp mtlt ,' ||
1680: ' mtl_material_transactions_temp mmtt,' ||
1681: ' rcv_transactions_interface rti, ' ||
1682: ' po_requisition_lines_all prl , ' ||
1683: ' po_requisition_headers_all prh,' ||
1684: ' oe_order_lines_all sol' ||
1685: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1686: ' and prh.org_id = ' || l_ou_id ||
1687: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1696: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl' ||

1692: ' and mmtt.trx_source_line_id = sol.line_id ';
1693:
1694:
1695: p_sql(31) := ' select distinct rls.* ' ||
1696: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl' ||
1697: ' where rsl.shipment_line_id = rls.shipment_line_id ' ||
1698: ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1699: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1700: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||

Line 1707: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl

1703: ' and prh.org_id = ' || l_ou_id ;
1704:
1705:
1706: p_sql(32) := ' select distinct rlt.* ' ||
1707: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl
1708: ' ||
1709: ' where rsl.shipment_line_id = rlt.shipment_line_id ' ||
1710: ' and rsl.requisition_line_id = prl.requisition_line_id ' ||
1711: ' and prh.requisition_header_id = prl.requisition_header_id ' ||

Line 1720: ' from rcv_lots_interface rli , rcv_transactions_interface rti , po_requisition_headers_all prh ,

1716:
1717:
1718:
1719: p_sql(33) := ' select distinct rli.* ' ||
1720: ' from rcv_lots_interface rli , rcv_transactions_interface rti , po_requisition_headers_all prh ,
1721: po_requisition_lines_all prl ' ||
1722: ' where prh.requisition_header_id = prl.requisition_header_id ' ||
1723: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1724: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1732: ' from po_requisition_headers_all prh,' ||

1728: ' AND rli.interface_transaction_id = rti.interface_transaction_id ';
1729:
1730:
1731: p_sql(34) := ' select distinct msi.*' ||
1732: ' from po_requisition_headers_all prh,' ||
1733: ' po_requisition_lines_all prl,' ||
1734: ' mtl_system_items msi' ||
1735: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1736: ' and prh.org_id = ' || l_ou_id ||

Line 1753: ' po_requisition_headers_all prh ,' ||

1749: ' mtt.disable_date ' ||
1750: ' from mtl_transaction_types mtt , ' ||
1751: ' mtl_material_transactions mmt , ' ||
1752: ' po_requisition_lines_all prl , ' ||
1753: ' po_requisition_headers_all prh ,' ||
1754: ' rcv_transactions rt' ||
1755: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1756: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1757: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1772: ' po_requisition_headers_all prh,' ||

1768: ' mtt.disable_date ' ||
1769: ' from mtl_transaction_types mtt , ' ||
1770: ' mtl_material_transactions mmt , ' ||
1771: ' po_requisition_lines_all prl , ' ||
1772: ' po_requisition_headers_all prh,' ||
1773: ' oe_order_lines_all sol ' ||
1774: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1775: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1776: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1788: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||

1784: p_sql(36) := ' select distinct ood.* ' ||
1785: ' from org_organization_definitions ood ' ||
1786: ' where exists (' ||
1787: ' select 1 ' ||
1788: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||
1789: ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1790: ' and prh.org_id = ' || l_ou_id ||
1791: ' and prl.requisition_header_id = prh.requisition_header_id ' ||
1792: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||

Line 1801: ' from mtl_parameters mp , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

1797: ' and ood.organization_id = fsp.inventory_organization_id ) ) ) ';
1798:
1799:
1800: p_sql(37) := ' select distinct mp.* ' ||
1801: ' from mtl_parameters mp , po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
1802: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1803: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1804: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1805: ' and prl.source_type_code = ''INVENTORY''' ||

Line 1815: ' from po_requisition_headers_all prh , po_requisition_lines_all prl ' ||

1811: p_sql(38) := ' select distinct miop.* ' ||
1812: ' from mtl_interorg_parameters miop ' ||
1813: ' where exists (' ||
1814: ' select 1 ' ||
1815: ' from po_requisition_headers_all prh , po_requisition_lines_all prl ' ||
1816: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1817: ' and prl.line_num = nvl(' || l_line_num || ',-99)' ||
1818: ' and prl.source_type_code = ''INVENTORY''' ||
1819: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||

Line 1830: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||

1826: p_sql(39) := ' select distinct rp.* ' ||
1827: ' from rcv_parameters rp ' ||
1828: ' where exists (' ||
1829: ' select 1 ' ||
1830: ' from po_requisition_headers_all prh , po_requisition_lines_all prl , financials_system_params_all fsp ' ||
1831: ' where prl.requisition_header_id = prh.requisition_header_id ' ||
1832: ' and prl.source_type_code = ''INVENTORY''' ||
1833: ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1834: ' and prh.org_id = ' || l_ou_id ||