eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_DIAG_RCV_PO_COMMON dependencies on RCV_SHIPMENT_HEADERS

Line 68: p_sql(11):=' select distinct rsh.* '||'from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||

64: ') and (ili.po_header_id = ph.po_header_id '||' or ili.po_number = '||''''||l_po_number||''''||')';
65: p_sql(10):= ' select distinct ihi.* '||' from ap_invoices_interface ihi , ap_invoice_lines_interface ili , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||
66: ''''||' and ph.org_id = '||l_operating_id||') and (ili.po_header_id = ph.po_header_id '||' or ili.po_number = '||''''||l_po_number||''''||
67: ') and ihi.invoice_id = ili.invoice_id';
68: p_sql(11):=' select distinct rsh.* '||'from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||
69: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
70: l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||' and rsl.shipment_header_id = rsh.shipment_header_id '||
71: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
72: ' and rsl.po_line_id=pl.po_line_id';

Line 95: p_sql(16):=' select distinct rhi.* '||' from rcv_headers_interface rhi'||' where exists (select 1 '||' from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh'||

91: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
92: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rs.po_header_id = ph.po_header_id '||
93: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rs.po_line_location_id'||
94: ' and rs.po_line_id=pl.po_line_id';
95: p_sql(16):=' select distinct rhi.* '||' from rcv_headers_interface rhi'||' where exists (select 1 '||' from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh'||
96: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||'and rsl.po_header_id = ph.po_header_id '||
97: ' and rsl.shipment_header_id = rsh.shipment_header_id '||'and rsh.shipment_header_id = rhi.receipt_header_id)'||
98: ' or exists (select 2 from rcv_transactions_interface rti '||'where rti.document_num = '||''''||l_po_number||''''||' and rhi.header_interface_id = rti.header_interface_id)'||
99: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||

Line 299: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;

295:
296: -- Initialize Local Variables
297: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;
298: l_po_number po_headers_all.segment1%TYPE :=p_po_number;
299: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
300: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
301: l_line_num VARCHAR2(1000) := p_line_num;
302: l_line_loc_num VARCHAR2(1000) := p_line_loc_num;
303:

Line 300: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;

296: -- Initialize Local Variables
297: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;
298: l_po_number po_headers_all.segment1%TYPE :=p_po_number;
299: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
300: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
301: l_line_num VARCHAR2(1000) := p_line_num;
302: l_line_loc_num VARCHAR2(1000) := p_line_loc_num;
303:
304: BEGIN

Line 314: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph, '||' rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

310: IF p_line_loc_num IS NULL THEN
311: l_line_loc_num := ' pll.shipment_num ';
312: END IF;
313:
314: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph, '||' rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
315: ',po_lines_all pl,po_line_locations_all pll'||
316: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||
317: ' and rsh.ship_to_org_id='||l_org_id||' and rsl.po_header_id=ph.po_header_id'||' and ph.segment1 = '||''''||l_po_number||''''||
318: ' and ph.org_id = '||l_operating_id||' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.po_line_id=pl.po_line_id'||

Line 321: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl,po_headers_all ph,po_line_locations_all pll,'||' rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

317: ' and rsh.ship_to_org_id='||l_org_id||' and rsl.po_header_id=ph.po_header_id'||' and ph.segment1 = '||''''||l_po_number||''''||
318: ' and ph.org_id = '||l_operating_id||' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.po_line_id=pl.po_line_id'||
319: ' and pl.po_header_id=ph.po_header_id'||
320: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
321: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl,po_headers_all ph,po_line_locations_all pll,'||' rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
322: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and pl.po_header_id = ph.po_header_id'||
323: ' and pll.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
324: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and rsh.receipt_num='||''''||l_receipt_num||''''||
325: ' and rsh.ship_to_org_id='||l_org_id||

Line 327: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph,rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

323: ' and pll.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
324: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and rsh.receipt_num='||''''||l_receipt_num||''''||
325: ' and rsh.ship_to_org_id='||l_org_id||
326: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
327: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph,rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
328: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||
329: ' and pll.shipment_num ='||l_line_loc_num||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||
330: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
331: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;

Line 333: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

329: ' and pll.shipment_num ='||l_line_loc_num||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||
330: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
331: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
332: p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||
333: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
334: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
335: l_line_num||' and pll.shipment_num ='||l_line_loc_num||
336: ') and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||
337: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||

Line 340: ' po_distributions_all pd,rcv_shipment_lines rsl, rcv_shipment_headers rsh '||' where gcc.summary_flag = ''N'''||

336: ') and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||
337: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
338: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
339: p_sql(5) := ' select distinct gcc.* '||' from gl_code_combinations gcc , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||
340: ' po_distributions_all pd,rcv_shipment_lines rsl, rcv_shipment_headers rsh '||' where gcc.summary_flag = ''N'''||
341: ' and gcc.template_id is null and '||' (ph.segment1 = '||''''||l_po_number||''''||
342: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
343: ') and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||
344: ' and gcc.code_combination_id in '||'(pd.accrual_account_id , pd.budget_account_id '||', pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)'||

Line 348: 'po_lines_all pl,po_line_locations_all pll '||',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

344: ' and gcc.code_combination_id in '||'(pd.accrual_account_id , pd.budget_account_id '||', pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)'||
345: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
346: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
347: p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||
348: 'po_lines_all pl,po_line_locations_all pll '||',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
349: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
350: l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||' and rrsl.rcv_transaction_id = rt.transaction_id'||
351: ' and ph.po_header_id=pl.po_header_id '||'and pll.po_line_id=pl.po_line_id'||' and pll.line_location_id=rrsl.reference3'||
352: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||

Line 355: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

351: ' and ph.po_header_id=pl.po_header_id '||'and pll.po_line_id=pl.po_line_id'||' and pll.line_location_id=rrsl.reference3'||
352: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
353: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
354: /*p_sql(7) := ' select distinct id.* '||' from ap_invoice_distributions_all id , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , po_distributions_all pd '||
355: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
356: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
357: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
358: ' and pll.po_line_id = pl.po_line_id '||'and pll.line_location_id = pd.line_location_id '||'and id.po_distribution_id = pd.po_distribution_id'||
359: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||

Line 362: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh,'||' rcv_transactions rt'||

358: ' and pll.po_line_id = pl.po_line_id '||'and pll.line_location_id = pd.line_location_id '||'and id.po_distribution_id = pd.po_distribution_id'||
359: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
360: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;*/
361: p_sql(7) := ' select distinct id.* '||' from ap_invoice_lines_all id , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , po_distributions_all pd '||
362: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh,'||' rcv_transactions rt'||
363: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
364: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
365: ' and pll.po_line_id = pl.po_line_id '||'and pll.line_location_id = pd.line_location_id '||'and id.po_distribution_id = pd.po_distribution_id'||
366: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||

Line 371: ', po_lines_all pl , po_headers_all ph ,'||'rcv_shipment_lines rsl, rcv_shipment_headers rsh ,'||

367: ' and id.po_line_location_id=pll.line_location_id'||' and rt.po_line_location_id=pll.line_location_id'||
368: ' and rt.transaction_id=id.rcv_transaction_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||
369: ' and rsh.ship_to_org_id='||l_org_id;
370: p_sql(8) := ' select distinct ai.* '||' from ap_invoices_all ai , ap_invoice_distributions_all id , po_line_locations_all pll '||
371: ', po_lines_all pl , po_headers_all ph ,'||'rcv_shipment_lines rsl, rcv_shipment_headers rsh ,'||
372: ' po_distributions_all pd where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
373: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
374: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id '||' and id.po_distribution_id = pd.po_distribution_id '||
375: ' and ai.invoice_id = id.invoice_id'||

Line 383: p_sql(11):=' select distinct rsh.* '||'from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||

379: ') and (ili.po_header_id = ph.po_header_id '||' or ili.po_number = '||''''||l_po_number||''''||')';
380: p_sql(10):= ' select distinct ihi.* '||' from ap_invoices_interface ihi , ap_invoice_lines_interface ili , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||
381: ''''||' and ph.org_id = '||l_operating_id||') and (ili.po_header_id = ph.po_header_id '||' or ili.po_number = '||''''||l_po_number||''''||
382: ') and ihi.invoice_id = ili.invoice_id';
383: p_sql(11):=' select distinct rsh.* '||'from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||
384: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
385: l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||' and rsl.shipment_header_id = rsh.shipment_header_id '||
386: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
387: ' and rsl.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;

Line 388: p_sql(12):=' select distinct rsl.* '||' from po_headers_all ph , rcv_shipment_lines rsl,rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||

384: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
385: l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||' and rsl.shipment_header_id = rsh.shipment_header_id '||
386: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
387: ' and rsl.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
388: p_sql(12):=' select distinct rsl.* '||' from po_headers_all ph , rcv_shipment_lines rsl,rcv_shipment_headers rsh,'||'po_lines_all pl,po_line_locations_all pll '||
389: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
390: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||
391: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
392: ' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 395: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

391: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
392: ' and rsl.shipment_header_id=rsh.shipment_header_id'||
393: ' and rsl.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
394: p_sql(13):=' select distinct rt.* '||' from rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
395: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
396: ' where (ph.segment1 = '||''''||l_po_number||
397: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
398: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||
399: ' and rsh.shipment_header_id = rt.shipment_header_id'||' and rt.shipment_line_id=rsl.shipment_line_id'||

Line 404: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

400: ' and rsl.shipment_header_id=rsh.shipment_header_id'||' and pll.po_line_id = pl.po_line_id '||
401: ' and pll.line_location_id=rt.po_line_location_id'||' and rt.po_line_id=pl.po_line_id'||
402: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
403: p_sql(14):=' select distinct ms.* '||' from mtl_supply ms , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll'||
404: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
405: ' where (ph.segment1 = '||''''||l_po_number||
406: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
407: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and ms.po_header_id = ph.po_header_id '||
408: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=ms.po_line_location_id'||

Line 412: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

408: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=ms.po_line_location_id'||
409: ' and rsl.shipment_header_id=rsh.shipment_header_id'||' and ms.shipment_line_id=rsl.shipment_line_id'||
410: ' and ms.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
411: p_sql(15):=' select distinct rs.* '||' from rcv_supply rs , po_headers_all ph ,'||'po_lines_all pl,po_line_locations_all pll '||
412: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
413: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
414: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rs.po_header_id = ph.po_header_id '||
415: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rs.po_line_location_id'||
416: ' and rs.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rs.shipment_line_id=rsl.shipment_line_id'||

Line 418: p_sql(16):=' select distinct rhi.* '||' from rcv_headers_interface rhi'||' where exists (select 1 '||' from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh'||

414: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rs.po_header_id = ph.po_header_id '||
415: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rs.po_line_location_id'||
416: ' and rs.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rs.shipment_line_id=rsl.shipment_line_id'||
417: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
418: p_sql(16):=' select distinct rhi.* '||' from rcv_headers_interface rhi'||' where exists (select 1 '||' from po_headers_all ph , rcv_shipment_lines rsl , rcv_shipment_headers rsh'||
419: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||'and rsl.po_header_id = ph.po_header_id '||
420: ' and rsl.shipment_header_id = rsh.shipment_header_id '||'and rsh.shipment_header_id = rhi.receipt_header_id'||
421: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')'||
422: ' or exists (select 2 from rcv_transactions_interface rti '||'where rti.document_num = '||''''||l_po_number||''''||' and rhi.header_interface_id = rti.header_interface_id)'||

Line 428: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

424: ' and ph.org_id = '||l_operating_id||' and rti.po_header_id = ph.po_header_id '||'and rti.po_header_id is not null '||
425: ' and rhi.header_interface_id = rti.header_interface_id)';
426: p_sql(17):=' select distinct rti.*'||'from rcv_transactions_interface rti where '||'rti.document_num = '||''''||l_po_number||''''||
427: ' or exists (select 1 from po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
428: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
429: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
430: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
431: ' and rti.po_header_id = ph.po_header_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_header_id=ph.po_header_id'||
432: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||

Line 437: ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||

433: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
434: ' and pll.po_line_id = pl.po_line_id '||' and pl.po_header_id = ph.po_header_id )';
435:
436: p_sql(18) := 'SELECT DISTINCT pie.* '||' FROM po_interface_errors pie , '||
437: ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||
438: ' AND rsh.ship_to_org_id='||l_org_id||' AND ( '||
439: ' EXISTS (SELECT 1'||' FROM rcv_transactions_interface rti'||
440: ' WHERE pie.interface_line_id = rti.interface_transaction_id'||
441: ' AND rsh.shipment_header_id=rti.shipment_header_id )'||

Line 448: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||

444: ' WHERE pie.interface_header_id = rhi.header_interface_id '||
445: ' AND rsh.shipment_header_id = rhi.header_interface_id))';
446:
447: p_sql(19):=' select distinct msi.* '||' from mtl_system_items msi , po_line_locations_all pll , po_lines_all pl , po_headers_all ph '||
448: ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
449: ' where pl.po_header_id = ph.po_header_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
450: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
451: ' and pll.po_line_id = pl.po_line_id '||'and msi.inventory_item_id = pl.item_id '||'and msi.organization_id = pll.ship_to_organization_id'||
452: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rsl.po_line_location_id=pll.line_location_id'||

Line 455: ' ,rcv_transactions rt,rcv_shipment_headers rsh'||

451: ' and pll.po_line_id = pl.po_line_id '||'and msi.inventory_item_id = pl.item_id '||'and msi.organization_id = pll.ship_to_organization_id'||
452: ' and rsl.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rsl.po_line_location_id=pll.line_location_id'||
453: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
454: p_sql(20):=' select distinct mmt.* '||'from mtl_material_transactions mmt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
455: ' ,rcv_transactions rt,rcv_shipment_headers rsh'||
456: ' where mmt.transaction_source_id = ph.po_header_id '||
457: ' and mmt.transaction_source_type_id = 1 and '||'ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
458: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
459: ' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id = pl.po_line_id'||' and rt.transaction_id=mmt.rcv_transaction_id'||

Line 469: /p_sql(22):=' select distinct mol. '||'from mtl_txn_request_lines mol , rcv_transactions rt ,'||' rcv_shipment_lines rsl ,rcv_shipment_headers rsh,'||

465: ' where mmt.transaction_source_id = ph.po_header_id '||' and mmt.transaction_source_type_id = 1 '||' and mtt.transaction_type_id = mmt.transaction_type_id'||
466: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')'||
467: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||
468: ' and mmtt.transaction_type_id = mtt.transaction_type_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')';
469: /*p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol , rcv_transactions rt ,'||' rcv_shipment_lines rsl ,rcv_shipment_headers rsh,'||
470: ' po_headers_all ph '||
471: ' where mol.reference_id = decode(mol.reference ,'||'''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,'||'''PO_LINE_LOCATION_ID'' , rt.po_line_location_id ,'||
472: ' ''ORDER_LINE_ID'' , rt.oe_order_line_id)'||' and rt.shipment_line_id = rsl.shipment_line_id '||' and rsl.shipment_header_id=rsh.shipment_header_id'||
473: ' and mol.organization_id = rt.organization_id '||' and mol.inventory_item_id = rsl.item_id'||' and rsl.po_header_id = ph.po_header_id '||

Line 476: p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol ,'||' rcv_shipment_lines rsl ,rcv_shipment_headers rsh,'||

472: ' ''ORDER_LINE_ID'' , rt.oe_order_line_id)'||' and rt.shipment_line_id = rsl.shipment_line_id '||' and rsl.shipment_header_id=rsh.shipment_header_id'||
473: ' and mol.organization_id = rt.organization_id '||' and mol.inventory_item_id = rsl.item_id'||' and rsl.po_header_id = ph.po_header_id '||
474: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
475: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;*/
476: p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol ,'||' rcv_shipment_lines rsl ,rcv_shipment_headers rsh,'||
477: ' po_headers_all ph '||' where rsl.shipment_header_id=rsh.shipment_header_id'||
478: ' and mol.organization_id = rsl.to_organization_id '||
479: ' and mol.inventory_item_id = rsl.item_id'||' and nvl(mol.revision,0)=nvl(rsl.item_revision,0) ' ||' and mol.line_status=7'||
480: ' and mol.transaction_type_id=18'||

Line 489: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

485: ' po_lines_all pl,po_line_locations_all pll'||' where mmtt.transaction_source_id = ph.po_header_id'||
486: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
487: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
488: ' and pll.po_line_id = pl.po_line_id '||
489: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
490: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
491: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')';
492: p_sql(24):=' select distinct ood.* '||' from org_organization_definitions ood , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||
493: ' financials_system_params_all fsp '||' where pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||

Line 513: ' ,po_lines_all pl , po_line_locations_all pll,'||' mtl_material_transactions mmt '||',rcv_transactions rt,rcv_shipment_headers rsh'||

509: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
510: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||
511: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
512: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||
513: ' ,po_lines_all pl , po_line_locations_all pll,'||' mtl_material_transactions mmt '||',rcv_transactions rt,rcv_shipment_headers rsh'||
514: ' where mmt.transaction_source_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
515: ' and mmt.transaction_source_type_id = 1 '||'and mut.transaction_id = mmt.transaction_id'||
516: ' and msn.inventory_item_id = mut.inventory_item_id '||' and msn.current_organization_id = mut.organization_id '||
517: ' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id = pl.po_line_id'||' and rt.transaction_id=mmt.rcv_transaction_id'||

Line 524: ' mtl_material_transactions mmt'||', mtl_transaction_lot_numbers mtln '||',rcv_transactions rt,rcv_shipment_headers rsh'||

520: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
521: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
522: ' union all '||
523: ' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut '||', po_headers_all ph'||',po_lines_all pl , po_line_locations_all pll,'||
524: ' mtl_material_transactions mmt'||', mtl_transaction_lot_numbers mtln '||',rcv_transactions rt,rcv_shipment_headers rsh'||
525: ' where mmt.transaction_source_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
526: ' and mmt.transaction_source_type_id = 1 '||
527: ' and mtln.transaction_id = mmt.transaction_id '||'and mut.transaction_id = mtln.serial_transaction_id '||'and msn.inventory_item_id = mut.inventory_item_id'||
528: ' and msn.current_organization_id = mut.organization_id'||' and msn.serial_number = mut.serial_number '||

Line 540: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

536: ' mmtt.transaction_source_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
537: ' and msnt.transaction_temp_id = mmtt.transaction_temp_id '||
538: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
539: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
540: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
541: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
542: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')'||
543: ' union all '||
544: ' select msnt.* '||' from mtl_serial_numbers_temp msnt'||', mtl_material_transactions_temp mmtt , po_headers_all ph,'||

Line 551: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

547: ' and mtln.transaction_temp_id = mmtt.transaction_temp_id'||
548: ' and msnt.transaction_temp_id = mtln.serial_transaction_temp_id'||
549: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
550: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
551: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
552: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
553: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')';
554: p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti'||',rcv_shipment_headers rsh '||
555: ' where (nvl(rti.document_num , ''-9999'') = '||''''||l_po_number||''''||

Line 554: p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti'||',rcv_shipment_headers rsh '||

550: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
551: ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
552: ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
553: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')';
554: p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti'||',rcv_shipment_headers rsh '||
555: ' where (nvl(rti.document_num , ''-9999'') = '||''''||l_po_number||''''||
556: ' or exists (select 1 from po_headers_all ph '||' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
557: ' and rti.po_header_id = ph.po_header_id ))'||' and msni.product_transaction_id = rti.interface_transaction_id'||
558: ' and rsh.shipment_header_id=rti.shipment_header_id'||

Line 561: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt,rcv_shipment_headers rsh'||

557: ' and rti.po_header_id = ph.po_header_id ))'||' and msni.product_transaction_id = rti.interface_transaction_id'||
558: ' and rsh.shipment_header_id=rti.shipment_header_id'||
559: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
560: p_sql(32):=' select distinct mut.* '||'from mtl_unit_transactions mut , po_headers_all ph , mtl_material_transactions mmt,'||
561: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt,rcv_shipment_headers rsh'||
562: ' where mmt.transaction_source_id = ph.po_header_id'||' and mmt.transaction_source_type_id = 1 '||
563: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
564: ' and rt.transaction_id=mmt.rcv_transaction_id'||' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id=pl.po_line_id'||
565: ' and rt.shipment_header_id=rsh.shipment_header_id'||

Line 571: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt,rcv_shipment_headers rsh'||

567: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
568: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
569: ' union all '||
570: ' select mut.* '||' from mtl_unit_transactions mut , po_headers_all ph , '||'mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln,'||
571: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt,rcv_shipment_headers rsh'||
572: ' where mmt.transaction_source_id = ph.po_header_id'||' and mmt.transaction_source_type_id = 1'||
573: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
574: ' and mtln.transaction_id = mmt.transaction_id '||'and mut.transaction_id = mtln.serial_transaction_id '||
575: ' and rt.transaction_id=mmt.rcv_transaction_id'||' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id=pl.po_line_id'||

Line 580: p_sql(33):=' select distinct rss.* '||' from rcv_serials_supply rss , rcv_shipment_lines rsl,rcv_shipment_headers rsh'||', po_headers_all ph ,'||

576: ' and rt.shipment_header_id=rsh.shipment_header_id'||
577: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
578: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
579: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
580: p_sql(33):=' select distinct rss.* '||' from rcv_serials_supply rss , rcv_shipment_lines rsl,rcv_shipment_headers rsh'||', po_headers_all ph ,'||
581: ' po_lines_all pl , po_line_locations_all pll'||' where rsl.po_header_id = ph.po_header_id '||
582: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
583: ' and rss.shipment_line_id = rsl.shipment_line_id '||' and rsl.po_line_id = pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
584: ' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 588: p_sql(34):=' select distinct rst.* '||'from rcv_serial_transactions rst , rcv_shipment_lines rsl,rcv_shipment_headers rsh'||' , po_headers_all ph,'||

584: ' and rsl.shipment_header_id=rsh.shipment_header_id'||
585: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
586: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
587: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
588: p_sql(34):=' select distinct rst.* '||'from rcv_serial_transactions rst , rcv_shipment_lines rsl,rcv_shipment_headers rsh'||' , po_headers_all ph,'||
589: ' po_lines_all pl , po_line_locations_all pll'||' where rsl.po_header_id = ph.po_header_id '||
590: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
591: ' and rst.shipment_line_id = rsl.shipment_line_id' ||' and rsl.po_line_id = pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
592: ' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 597: ' from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||' ,rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

593: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
594: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
595: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
596: p_sql(35):=' select distinct rsi.* '||'from rcv_serials_interface rsi , rcv_transactions_interface rti '||' where (exists (select 1 '||
597: ' from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||' ,rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
598: ' where rti.po_header_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
599: ' and rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.shipment_line_id=rti.shipment_line_id'||
600: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
601: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||

Line 606: ' mtl_material_transactions mmt'||',rcv_transactions rt,rcv_shipment_headers rsh'||

602: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
603: ')) and rsi.interface_transaction_id = rti.interface_transaction_id';
604: p_sql(36):=' select distinct mln.* '||'from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln ,'||'po_headers_all ph,'||
605: ' po_lines_all pl , po_line_locations_all pll,'||
606: ' mtl_material_transactions mmt'||',rcv_transactions rt,rcv_shipment_headers rsh'||
607: ' where mmt.transaction_source_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
608: ' and mmt.transaction_source_type_id = 1 '||'and mtln.transaction_id = mmt.transaction_id '||
609: ' and mtln.lot_number = mln.lot_number'||' and mtln.inventory_item_id = mln.inventory_item_id '||' and mtln.organization_id = mln.organization_id '||
610: ' and mmt.rcv_transaction_id=rt.transaction_id'||' and rsh.shipment_header_id=rt.shipment_header_id'||

Line 617: ' mtl_material_transactions mmt '||',rcv_transactions rt,rcv_shipment_headers rsh'||

613: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
614: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
615:
616: p_sql(37):=' select distinct mtln.* '||'from mtl_transaction_lot_numbers mtln , po_headers_all ph ,'||' po_lines_all pl , po_line_locations_all pll,'||
617: ' mtl_material_transactions mmt '||',rcv_transactions rt,rcv_shipment_headers rsh'||
618: ' where mmt.transaction_source_id = ph.po_header_id '||' and mmt.transaction_source_type_id = 1 '||
619: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
620: ' and mmt.rcv_transaction_id=rt.transaction_id'||' and rsh.shipment_header_id=rt.shipment_header_id'||
621: ' and rt.po_line_location_id=pll.line_location_id'||

Line 627: ',rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

623: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
624: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
625: p_sql(38):=' select distinct mtli.* '||'from mtl_transaction_lots_interface mtli , rcv_transactions_interface rti '||' where (nvl(rti.document_num ,''-9999'') = '||
626: ''''||l_po_number||''''||'or exists (select 1 from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||
627: ',rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
628: ' where rti.po_header_id = ph.po_header_id '||
629: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
630: ' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||
631: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

Line 636: ' po_lines_all pl , po_line_locations_all pll,'||'rcv_shipment_headers rsh,rcv_shipment_lines rsl'||' where '||

632: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
633: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
634: ')) and mtli.product_transaction_id = RTI.interface_transaction_id';
635: p_sql(39):=' select distinct mtlt.* '||'from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ,'||'po_headers_all ph,'||
636: ' po_lines_all pl , po_line_locations_all pll,'||'rcv_shipment_headers rsh,rcv_shipment_lines rsl'||' where '||
637: ' mmtt.transaction_source_id = ph.po_header_id '||' and mmtt.transaction_source_type_id = 1 '||
638: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
639: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id '||
640: ' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 644: p_sql(40):=' select distinct rls.* '||'from rcv_lots_supply rls , rcv_shipment_lines rsl,'||'rcv_shipment_headers rsh , po_headers_all ph ,'||

640: ' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||
641: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
642: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
643: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
644: p_sql(40):=' select distinct rls.* '||'from rcv_lots_supply rls , rcv_shipment_lines rsl,'||'rcv_shipment_headers rsh , po_headers_all ph ,'||
645: ' po_lines_all pl , po_line_locations_all pll where '||' rsl.shipment_line_id = rls.shipment_line_id '||
646: ' and rsl.po_header_id = ph.po_header_id '||' and rsl.po_line_id = pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
647: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
648: ' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 652: p_sql(41):=' select distinct rlt.* '||'from rcv_lot_transactions rlt , rcv_shipment_lines rsl,'||'rcv_shipment_headers rsh, po_headers_all ph ,'||

648: ' and rsl.shipment_header_id=rsh.shipment_header_id'||
649: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
650: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
651: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
652: p_sql(41):=' select distinct rlt.* '||'from rcv_lot_transactions rlt , rcv_shipment_lines rsl,'||'rcv_shipment_headers rsh, po_headers_all ph ,'||
653: ' po_lines_all pl , po_line_locations_all pll where '||' rsl.po_header_id = ph.po_header_id '||
654: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
655: ' and rlt.shipment_line_id = rsl.shipment_line_id' ||' and rsl.po_line_id = pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
656: ' and rsl.shipment_header_id=rsh.shipment_header_id'||

Line 662: ' po_lines_all pl , po_line_locations_all pll,'||'rcv_shipment_headers rsh,rcv_shipment_lines rsl'||

658: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
659: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
660: p_sql(42):=' select distinct rli.* '||'from rcv_lots_interface rli , rcv_transactions_interface rti'||' where rti.interface_transaction_id = rli.interface_transaction_id '||
661: ' and exists (select 1 from po_headers_all ph,'||
662: ' po_lines_all pl , po_line_locations_all pll,'||'rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
663: ' where rti.po_header_id = ph.po_header_id '||
664: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
665: ' and rsl.po_line_location_id=pll.line_location_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||
666: ' and ph.segment1 = '||''''||l_po_number||''''||