APPS.INV_DIAG_RCV_PO_COMMON dependencies on PO_HEADERS_ALL

Line 11: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;

7:
8: PROCEDURE build_po_all_sql(p_operating_id IN NUMBER,p_po_number IN VARCHAR2,p_line_num IN NUMBER,p_line_loc_num IN NUMBER,p_sql IN OUT NOCOPY sqls_list) IS
9:
10: -- Initialize Local Variables.
11: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;
12: l_po_number po_headers_all.segment1%TYPE :=p_po_number;
13: l_line_num VARCHAR2(1000) := p_line_num;
14: l_line_loc_num VARCHAR2(1000) := p_line_loc_num;
15:

Line 12: l_po_number po_headers_all.segment1%TYPE :=p_po_number;

8: PROCEDURE build_po_all_sql(p_operating_id IN NUMBER,p_po_number IN VARCHAR2,p_line_num IN NUMBER,p_line_loc_num IN NUMBER,p_sql IN OUT NOCOPY sqls_list) IS
9:
10: -- Initialize Local Variables.
11: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;
12: l_po_number po_headers_all.segment1%TYPE :=p_po_number;
13: l_line_num VARCHAR2(1000) := p_line_num;
14: l_line_loc_num VARCHAR2(1000) := p_line_loc_num;
15:
16: BEGIN

Line 26: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||

22: IF p_line_loc_num IS NULL THEN
23: l_line_loc_num := ' pll.shipment_num ';
24: END IF;
25:
26: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
27: ' and ph.org_id = '||l_operating_id||')';
28: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
29: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||') and pl.po_header_id = ph.po_header_id';
30: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph where '||

Line 28: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||

24: END IF;
25:
26: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
27: ' and ph.org_id = '||l_operating_id||')';
28: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
29: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||') and pl.po_header_id = ph.po_header_id';
30: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph where '||
31: ' (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||
32: ' and pll.shipment_num ='||l_line_loc_num||

Line 30: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph where '||

26: p_sql(1) := ' select distinct ph.* '||' from po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
27: ' and ph.org_id = '||l_operating_id||')';
28: p_sql(2) := ' select distinct pl.* '||' from po_lines_all pl , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||
29: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||') and pl.po_header_id = ph.po_header_id';
30: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph where '||
31: ' (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||
32: ' and pll.shipment_num ='||l_line_loc_num||
33: ' ) and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id ';
34: p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||

Line 34: p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||

30: p_sql(3) := ' select distinct pll.* '||' from po_line_locations_all pll , po_lines_all pl , '||'po_headers_all ph where '||
31: ' (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||
32: ' and pll.shipment_num ='||l_line_loc_num||
33: ' ) and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id ';
34: p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||
35: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
36: l_line_num||' and pll.shipment_num ='||l_line_loc_num||
37: ') 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';
38: p_sql(5) := ' select distinct gcc.* '||' from gl_code_combinations gcc , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||

Line 38: p_sql(5) := ' select distinct gcc.* '||' from gl_code_combinations gcc , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||

34: p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||
35: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
36: l_line_num||' and pll.shipment_num ='||l_line_loc_num||
37: ') 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';
38: p_sql(5) := ' select distinct gcc.* '||' from gl_code_combinations gcc , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||
39: ' po_distributions_all pd where gcc.summary_flag = ''N'''||' and gcc.template_id is null and '||' (ph.segment1 = '||''''||l_po_number||''''||
40: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
41: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
42: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||' and gcc.code_combination_id in '||'(pd.accrual_account_id , pd.budget_account_id '||

Line 44: p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

40: ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
41: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
42: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||' and gcc.code_combination_id in '||'(pd.accrual_account_id , pd.budget_account_id '||
43: ', pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)';
44: p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
45: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
46: l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||' and rrsl.rcv_transaction_id = rt.transaction_id'||
47: ' 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';
48: /*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 '||

Line 48: /*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 '||

44: p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
45: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
46: l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||' and rrsl.rcv_transaction_id = rt.transaction_id'||
47: ' 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';
48: /*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 '||
49: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
50: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
51: ' 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';*/
52: 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 '||

Line 52: 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 '||

48: /*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 '||
49: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
50: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
51: ' 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';*/
52: 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 '||
53: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
54: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
55: ' 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'||
56: ' and id.po_line_location_id=pll.line_location_id'||' and id.po_line_id=pl.po_line_id'||

Line 58: p_sql(8) := ' select distinct ai.* '||' from ap_invoices_all ai , ap_invoice_distributions_all id , po_line_locations_all pll '||', po_lines_all pl , po_headers_all ph ,'||

54: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
55: ' 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'||
56: ' and id.po_line_location_id=pll.line_location_id'||' and id.po_line_id=pl.po_line_id'||
57: ' and id.po_header_id=ph.po_header_id';
58: p_sql(8) := ' select distinct ai.* '||' from ap_invoices_all ai , ap_invoice_distributions_all id , po_line_locations_all pll '||', po_lines_all pl , po_headers_all ph ,'||
59: ' 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 ='||
60: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
61: ' 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 '||
62: ' and ai.invoice_id = id.invoice_id';

Line 63: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

59: ' 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 ='||
60: l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
61: ' 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 '||
62: ' and ai.invoice_id = id.invoice_id';
63: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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';

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

61: ' 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 '||
62: ' and ai.invoice_id = id.invoice_id';
63: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph '||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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 ='||

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 73: p_sql(12):=' select distinct rsl.* '||' from po_headers_all ph , rcv_shipment_lines rsl,'||'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';
73: p_sql(12):=' select distinct rsl.* '||' from po_headers_all ph , rcv_shipment_lines rsl,'||'po_lines_all pl,po_line_locations_all pll '||
74: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
75: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||
76: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
77: ' and rsl.po_line_id=pl.po_line_id';

Line 78: p_sql(13):=' select distinct rt.* '||' from rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

74: ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
75: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rsl.po_header_id = ph.po_header_id '||
76: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rsl.po_line_location_id'||
77: ' and rsl.po_line_id=pl.po_line_id';
78: p_sql(13):=' select distinct rt.* '||' from rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
79: ' where (ph.segment1 = '||''''||l_po_number||
80: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
81: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||
82: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rt.po_line_location_id'||

Line 84: p_sql(14):=' select distinct ms.* '||' from mtl_supply ms , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

80: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
81: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||
82: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rt.po_line_location_id'||
83: ' and rt.po_line_id=pl.po_line_id';
84: p_sql(14):=' select distinct ms.* '||' from mtl_supply ms , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
85: ' where (ph.segment1 = '||''''||l_po_number||
86: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
87: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and ms.po_header_id = ph.po_header_id '||
88: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=ms.po_line_location_id'||

Line 90: p_sql(15):=' select distinct rs.* '||' from rcv_supply rs , po_headers_all ph ,'||'po_lines_all pl,po_line_locations_all pll '||

86: ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
87: l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and ms.po_header_id = ph.po_header_id '||
88: ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=ms.po_line_location_id'||
89: ' and ms.po_line_id=pl.po_line_id';
90: p_sql(15):=' select distinct rs.* '||' from rcv_supply rs , po_headers_all ph ,'||'po_lines_all pl,po_line_locations_all pll '||
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';

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 99: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||

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||''''||
100: ' and ph.org_id = '||l_operating_id||' and rti.po_header_id = ph.po_header_id '||'and rti.po_header_id is not null '||
101: ' and rhi.header_interface_id = rti.header_interface_id)';
102: p_sql(17):=' select distinct rti.*'||'from rcv_transactions_interface rti where '||'rti.document_num = '||''''||l_po_number||''''||
103: ' or exists (select 1 from po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

Line 103: ' or exists (select 1 from po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

99: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||
100: ' and ph.org_id = '||l_operating_id||' and rti.po_header_id = ph.po_header_id '||'and rti.po_header_id is not null '||
101: ' and rhi.header_interface_id = rti.header_interface_id)';
102: p_sql(17):=' select distinct rti.*'||'from rcv_transactions_interface rti where '||'rti.document_num = '||''''||l_po_number||''''||
103: ' or exists (select 1 from po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
104: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
105: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
106: ' and rti.po_header_id = ph.po_header_id'||
107: ' and pll.po_line_id = pl.po_line_id '||' and pl.po_header_id = ph.po_header_id )';

Line 108: p_sql(18):=' select distinct pie.* '||'from po_interface_errors pie , rcv_transactions_interface rti , rcv_headers_interface rhi '||', po_headers_all poh where '||

104: ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
105: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
106: ' and rti.po_header_id = ph.po_header_id'||
107: ' and pll.po_line_id = pl.po_line_id '||' and pl.po_header_id = ph.po_header_id )';
108: p_sql(18):=' select distinct pie.* '||'from po_interface_errors pie , rcv_transactions_interface rti , rcv_headers_interface rhi '||', po_headers_all poh where '||
109: ' ((table_name = ''RCV_HEADERS_INTERFACE'''||' and rti.header_interface_id = rhi.header_interface_id '||' and pie.interface_header_id = rhi.header_interface_id '||
110: ' and (nvl (rti.po_header_id , -999) = poh.po_header_id '||'or nvl (rti.document_num , ''-9999'') = poh.segment1 ))'||
111: ' or (table_name = ''RCV_TRANSACTIONS_INTERFACE'''||'and pie.interface_line_id = rti.interface_transaction_id'||' and (nvl (rti.po_header_id , -999) = poh.po_header_id'||
112: ' or nvl (rti.document_num ,''-9999'') = poh.segment1)))'||' and poh.segment1 = '||''''||l_po_number||'''';

Line 113: p_sql(19):=' select distinct msi.* '||' from mtl_system_items msi , po_line_locations_all pll , po_lines_all pl , po_headers_all ph '||

109: ' ((table_name = ''RCV_HEADERS_INTERFACE'''||' and rti.header_interface_id = rhi.header_interface_id '||' and pie.interface_header_id = rhi.header_interface_id '||
110: ' and (nvl (rti.po_header_id , -999) = poh.po_header_id '||'or nvl (rti.document_num , ''-9999'') = poh.segment1 ))'||
111: ' or (table_name = ''RCV_TRANSACTIONS_INTERFACE'''||'and pie.interface_line_id = rti.interface_transaction_id'||' and (nvl (rti.po_header_id , -999) = poh.po_header_id'||
112: ' or nvl (rti.document_num ,''-9999'') = poh.segment1)))'||' and poh.segment1 = '||''''||l_po_number||'''';
113: p_sql(19):=' select distinct msi.* '||' from mtl_system_items msi , po_line_locations_all pll , po_lines_all pl , po_headers_all ph '||
114: ' where pl.po_header_id = ph.po_header_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
115: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
116: ' 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';
117: p_sql(20):=' select distinct mmt.* '||'from mtl_material_transactions mmt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

Line 117: p_sql(20):=' select distinct mmt.* '||'from mtl_material_transactions mmt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

113: p_sql(19):=' select distinct msi.* '||' from mtl_system_items msi , po_line_locations_all pll , po_lines_all pl , po_headers_all ph '||
114: ' where pl.po_header_id = ph.po_header_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
115: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
116: ' 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';
117: p_sql(20):=' select distinct mmt.* '||'from mtl_material_transactions mmt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
118: ' ,rcv_transactions rt'||
119: ' where mmt.transaction_source_id = ph.po_header_id '||
120: ' and mmt.transaction_source_type_id = 1 and '||'ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
121: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||

Line 127: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||

123: ' and pll.po_line_id = pl.po_line_id '||
124: ' and pl.po_header_id = ph.po_header_id';
125: p_sql(21):=' select distinct mtt.transaction_type_id , mtt.transaction_type_name , '||'mtt.transaction_source_type_id , mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date'||
126: ' from mtl_transaction_types mtt where'||
127: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||
128: ' 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'||
129: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')'||
130: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||
131: ' and mmtt.transaction_type_id = mtt.transaction_type_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')';

Line 130: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||

126: ' from mtl_transaction_types mtt where'||
127: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||
128: ' 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'||
129: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')'||
130: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||
131: ' and mmtt.transaction_type_id = mtt.transaction_type_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')';
132: /*p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol , rcv_transactions rt ,'||' rcv_shipment_lines rsl , po_headers_all ph '||
133: ' where mol.reference_id = decode(mol.reference ,'||'''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,'||'''PO_LINE_LOCATION_ID'' , rt.po_line_location_id ,'||
134: ' ''ORDER_LINE_ID'' , rt.oe_order_line_id)'||' and rt.shipment_line_id = rsl.shipment_line_id '||'and mol.organization_id = rt.organization_id '||

Line 132: /*p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol , rcv_transactions rt ,'||' rcv_shipment_lines rsl , po_headers_all ph '||

128: ' 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'||
129: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')'||
130: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||
131: ' and mmtt.transaction_type_id = mtt.transaction_type_id '||'and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||')';
132: /*p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol , rcv_transactions rt ,'||' rcv_shipment_lines rsl , po_headers_all ph '||
133: ' where mol.reference_id = decode(mol.reference ,'||'''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,'||'''PO_LINE_LOCATION_ID'' , rt.po_line_location_id ,'||
134: ' ''ORDER_LINE_ID'' , rt.oe_order_line_id)'||' and rt.shipment_line_id = rsl.shipment_line_id '||'and mol.organization_id = rt.organization_id '||
135: ' and mol.inventory_item_id = rsl.item_id'||' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||
136: ' and ph.org_id = '||l_operating_id;*/

Line 137: p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol,'||' rcv_shipment_lines rsl , po_headers_all ph '||

133: ' where mol.reference_id = decode(mol.reference ,'||'''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,'||'''PO_LINE_LOCATION_ID'' , rt.po_line_location_id ,'||
134: ' ''ORDER_LINE_ID'' , rt.oe_order_line_id)'||' and rt.shipment_line_id = rsl.shipment_line_id '||'and mol.organization_id = rt.organization_id '||
135: ' and mol.inventory_item_id = rsl.item_id'||' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||
136: ' and ph.org_id = '||l_operating_id;*/
137: p_sql(22):=' select distinct mol.* '||'from mtl_txn_request_lines mol,'||' rcv_shipment_lines rsl , po_headers_all ph '||
138: ' where mol.organization_id = rsl.to_organization_id '||
139: ' and mol.inventory_item_id = rsl.item_id'||' and nvl(mol.revision,0)=nvl(rsl.item_revision,0) ' ||' and mol.line_status=7'||
140: ' and mol.transaction_type_id=18'||
141: ' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;

Line 142: p_sql(23):=' select distinct mmtt.* '||' from mtl_material_transactions_temp mmtt , po_headers_all ph,'||

138: ' where mol.organization_id = rsl.to_organization_id '||
139: ' and mol.inventory_item_id = rsl.item_id'||' and nvl(mol.revision,0)=nvl(rsl.item_revision,0) ' ||' and mol.line_status=7'||
140: ' and mol.transaction_type_id=18'||
141: ' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
142: p_sql(23):=' select distinct mmtt.* '||' from mtl_material_transactions_temp mmtt , po_headers_all ph,'||
143: ' po_lines_all pl,po_line_locations_all pll '||' where mmtt.transaction_source_id = ph.po_header_id'||
144: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
145: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
146: ' and pll.po_line_id = pl.po_line_id ';

Line 147: p_sql(24):=' select distinct ood.* '||' from org_organization_definitions ood , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||

143: ' po_lines_all pl,po_line_locations_all pll '||' where mmtt.transaction_source_id = ph.po_header_id'||
144: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
145: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
146: ' and pll.po_line_id = pl.po_line_id ';
147: p_sql(24):=' select distinct ood.* '||' from org_organization_definitions ood , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||
148: ' financials_system_params_all fsp '||' where pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
149: ' and fsp.org_id = ph.org_id'||' and ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
150: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
151: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;

Line 152: p_sql(25):=' select distinct mp.* '||' from mtl_parameters mp , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , '||

148: ' financials_system_params_all fsp '||' where pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
149: ' and fsp.org_id = ph.org_id'||' and ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
150: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
151: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
152: p_sql(25):=' select distinct mp.* '||' from mtl_parameters mp , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , '||
153: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||'and pll.po_line_id = pl.po_line_id '||
154: ' and fsp.org_id = ph.org_id'||' and mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
155: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
156: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;

Line 157: p_sql(26):=' select distinct rp.* '||'from rcv_parameters rp , po_line_locations_all pll ,'||'po_lines_all pl , po_headers_all ph,'||

153: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||'and pll.po_line_id = pl.po_line_id '||
154: ' and fsp.org_id = ph.org_id'||' and mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
155: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
156: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
157: p_sql(26):=' select distinct rp.* '||'from rcv_parameters rp , po_line_locations_all pll ,'||'po_lines_all pl , po_headers_all ph,'||
158: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||
159: ' and fsp.org_id = ph.org_id '||'and (rp.organization_id = fsp.inventory_organization_id '||
160: ' or rp.organization_id = pll.ship_to_organization_id)'||
161: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

Line 163: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||

159: ' and fsp.org_id = ph.org_id '||'and (rp.organization_id = fsp.inventory_organization_id '||
160: ' or rp.organization_id = pll.ship_to_organization_id)'||
161: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
162: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
163: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
164: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
165: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||
166: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
167: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||

Line 165: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||

161: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
162: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
163: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
164: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
165: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||
166: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
167: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||
168: ' ,po_lines_all pl , po_line_locations_all pll,'||' mtl_material_transactions mmt '||',rcv_transactions rt'||
169: ' 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'||

Line 167: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||

163: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
164: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
165: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||
166: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id;
167: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||
168: ' ,po_lines_all pl , po_line_locations_all pll,'||' mtl_material_transactions mmt '||',rcv_transactions rt'||
169: ' 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'||
170: ' and mmt.transaction_source_type_id = 1 '||'and mut.transaction_id = mmt.transaction_id'||
171: ' and msn.inventory_item_id = mut.inventory_item_id '||' and msn.current_organization_id = mut.organization_id '||

Line 176: ' 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,'||

172: ' 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'||
173: ' and msn.serial_number = mut.serial_number'||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
174: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
175: ' union all '||
176: ' 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,'||
177: ' mtl_material_transactions mmt'||', mtl_transaction_lot_numbers mtln '||',rcv_transactions rt'||
178: ' 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'||
179: ' and mmt.transaction_source_type_id = 1 '||
180: ' and mtln.transaction_id = mmt.transaction_id '||'and mut.transaction_id = mtln.serial_transaction_id '||'and msn.inventory_item_id = mut.inventory_item_id'||

Line 185: p_sql(30):=' select distinct msnt.* '||'from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt'||', po_headers_all ph,'||

181: ' and msn.current_organization_id = mut.organization_id'||' and msn.serial_number = mut.serial_number '||
182: ' 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'||
183: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
184: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
185: p_sql(30):=' select distinct msnt.* '||'from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt'||', po_headers_all ph,'||
186: ' po_lines_all pl , po_line_locations_all pll'||' where '||
187: ' 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'||
188: ' and msnt.transaction_temp_id = mmtt.transaction_temp_id '||
189: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

Line 192: ' select msnt.* '||' from mtl_serial_numbers_temp msnt'||', mtl_material_transactions_temp mmtt , po_headers_all ph,'||

188: ' and msnt.transaction_temp_id = mmtt.transaction_temp_id '||
189: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
190: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
191: ' union all '||
192: ' select msnt.* '||' from mtl_serial_numbers_temp msnt'||', mtl_material_transactions_temp mmtt , po_headers_all ph,'||
193: ' po_lines_all pl , po_line_locations_all pll,'||'mtl_transaction_lots_temp mtln where '||'mmtt.transaction_source_id = ph.po_header_id'||
194: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
195: ' and mtln.transaction_temp_id = mmtt.transaction_temp_id'||
196: ' and msnt.transaction_temp_id = mtln.serial_transaction_temp_id'||

Line 201: ' or exists (select 1 from po_headers_all ph '||' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

197: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
198: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
199: p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti '||
200: ' where (nvl(rti.document_num , ''-9999'') = '||''''||l_po_number||''''||
201: ' or exists (select 1 from po_headers_all ph '||' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
202: ' and rti.po_header_id = ph.po_header_id ))'||' and msni.product_transaction_id = rti.interface_transaction_id';
203: p_sql(32):=' select distinct mut.* '||'from mtl_unit_transactions mut , po_headers_all ph , mtl_material_transactions mmt,'||
204: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt'||
205: ' where mmt.transaction_source_id = ph.po_header_id'||' and mmt.transaction_source_type_id = 1 '||

Line 203: p_sql(32):=' select distinct mut.* '||'from mtl_unit_transactions mut , po_headers_all ph , mtl_material_transactions mmt,'||

199: p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti '||
200: ' where (nvl(rti.document_num , ''-9999'') = '||''''||l_po_number||''''||
201: ' or exists (select 1 from po_headers_all ph '||' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
202: ' and rti.po_header_id = ph.po_header_id ))'||' and msni.product_transaction_id = rti.interface_transaction_id';
203: p_sql(32):=' select distinct mut.* '||'from mtl_unit_transactions mut , po_headers_all ph , mtl_material_transactions mmt,'||
204: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt'||
205: ' where mmt.transaction_source_id = ph.po_header_id'||' and mmt.transaction_source_type_id = 1 '||
206: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
207: ' 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 211: ' select mut.* '||' from mtl_unit_transactions mut , po_headers_all ph , '||'mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln,'||

207: ' 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'||
208: ' and mut.transaction_id = mmt.transaction_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
209: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
210: ' union all '||
211: ' select mut.* '||' from mtl_unit_transactions mut , po_headers_all ph , '||'mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln,'||
212: ' po_lines_all pl , po_line_locations_all pll'||',rcv_transactions rt'||
213: ' where mmt.transaction_source_id = ph.po_header_id'||' and mmt.transaction_source_type_id = 1'||
214: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
215: ' and mtln.transaction_id = mmt.transaction_id '||'and mut.transaction_id = mtln.serial_transaction_id '||

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

215: ' and mtln.transaction_id = mmt.transaction_id '||'and mut.transaction_id = mtln.serial_transaction_id '||
216: ' 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'||
217: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
218: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
219: p_sql(33):=' select distinct rss.* '||' from rcv_serials_supply rss , rcv_shipment_lines rsl , po_headers_all ph ,'||
220: ' po_lines_all pl , po_line_locations_all pll'||' where rsl.po_header_id = ph.po_header_id '||
221: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
222: ' 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'||
223: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||

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

221: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
222: ' 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'||
223: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
224: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
225: p_sql(34):=' select distinct rst.* '||'from rcv_serial_transactions rst , rcv_shipment_lines rsl , po_headers_all ph,'||
226: ' po_lines_all pl , po_line_locations_all pll'||' where rsl.po_header_id = ph.po_header_id '||
227: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
228: ' 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'||
229: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||

Line 232: ' from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||' where rti.po_header_id = ph.po_header_id '||

228: ' 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'||
229: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
230: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
231: p_sql(35):=' select distinct rsi.* '||'from rcv_serials_interface rsi , rcv_transactions_interface rti '||' where (exists (select 1 '||
232: ' from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||' where rti.po_header_id = ph.po_header_id '||
233: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
234: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||
235: l_operating_id||''''||
236: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||

Line 238: p_sql(36):=' select distinct mln.* '||'from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln ,'||'po_headers_all ph,'||

234: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||
235: l_operating_id||''''||
236: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
237: ')) and rsi.interface_transaction_id = rti.interface_transaction_id';
238: p_sql(36):=' select distinct mln.* '||'from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln ,'||'po_headers_all ph,'||
239: ' po_lines_all pl , po_line_locations_all pll,'||
240: ' mtl_material_transactions mmt'||
241: ' 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'||
242: ' and mmt.transaction_source_type_id = 1 '||'and mtln.transaction_id = mmt.transaction_id '||

Line 246: 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,'||

242: ' and mmt.transaction_source_type_id = 1 '||'and mtln.transaction_id = mmt.transaction_id '||
243: ' and mtln.lot_number = mln.lot_number'||' and mtln.inventory_item_id = mln.inventory_item_id '||' and mtln.organization_id = mln.organization_id '||
244: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
245: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
246: 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,'||
247: ' mtl_material_transactions mmt '||' where mmt.transaction_source_id = ph.po_header_id '||' and mmt.transaction_source_type_id = 1 '||
248: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
249: ' and mtln.transaction_id = mmt.transaction_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
250: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;

Line 252: ''''||l_po_number||''''||'or exists (select 1 from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||

248: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
249: ' and mtln.transaction_id = mmt.transaction_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
250: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
251: p_sql(38):=' select distinct mtli.* '||'from mtl_transaction_lots_interface mtli , rcv_transactions_interface rti '||' where (nvl(rti.document_num ,''-9999'') = '||
252: ''''||l_po_number||''''||'or exists (select 1 from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||
253: ' where rti.po_header_id = ph.po_header_id '||
254: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
255: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
256: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||

Line 258: p_sql(39):=' select distinct mtlt.* '||'from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ,'||'po_headers_all ph,'||

254: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
255: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
256: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
257: ')) and mtli.product_transaction_id = RTI.interface_transaction_id';
258: p_sql(39):=' select distinct mtlt.* '||'from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ,'||'po_headers_all ph,'||
259: ' po_lines_all pl , po_line_locations_all pll where '||
260: ' mmtt.transaction_source_id = ph.po_header_id '||' and mmtt.transaction_source_type_id = 1 '||
261: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
262: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id '||

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

261: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
262: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id '||
263: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
264: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
265: p_sql(40):=' select distinct rls.* '||'from rcv_lots_supply rls , rcv_shipment_lines rsl , po_headers_all ph ,'||
266: ' po_lines_all pl , po_line_locations_all pll where '||' rsl.shipment_line_id = rls.shipment_line_id '||
267: ' 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'||
268: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
269: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

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

267: ' 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'||
268: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
269: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
270: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
271: p_sql(41):=' select distinct rlt.* '||'from rcv_lot_transactions rlt , rcv_shipment_lines rsl , po_headers_all ph ,'||
272: ' po_lines_all pl , po_line_locations_all pll where '||' rsl.po_header_id = ph.po_header_id '||
273: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
274: ' 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'||
275: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||

Line 278: ' and exists (select 1 from po_headers_all ph,'||

274: ' 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'||
275: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
276: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
277: p_sql(42):=' select distinct rli.* '||'from rcv_lots_interface rli , rcv_transactions_interface rti'||' where rti.interface_transaction_id = rli.interface_transaction_id '||
278: ' and exists (select 1 from po_headers_all ph,'||
279: ' po_lines_all pl , po_line_locations_all pll where rti.po_header_id = ph.po_header_id '||
280: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
281: ' and ph.segment1 = '||''''||l_po_number||''''||
282: ' and ph.org_id = '||l_operating_id||

Line 297: l_operating_id po_headers_all.org_id%TYPE := p_operating_id;

293: PROCEDURE build_all_sql(p_operating_id IN NUMBER,p_po_number IN VARCHAR2,p_line_num IN NUMBER,p_line_loc_num IN NUMBER,
294: p_org_id IN NUMBER,p_receipt_num IN VARCHAR2,p_sql IN OUT NOCOPY sqls_list) IS
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;

Line 298: l_po_number po_headers_all.segment1%TYPE :=p_po_number;

294: p_org_id IN NUMBER,p_receipt_num IN VARCHAR2,p_sql IN OUT NOCOPY sqls_list) IS
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;

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

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

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

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

Line 347: p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||

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

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

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

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

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

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 378: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph'||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_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'||
376: ' 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'||
377: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
378: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph'||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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';

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

376: ' 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'||
377: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
378: p_sql(9) := ' select distinct ili.* '||' from ap_invoice_lines_interface ili , po_headers_all ph'||' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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 ='||

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 394: p_sql(13):=' select distinct rt.* '||' from rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

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

Line 403: p_sql(14):=' select distinct ms.* '||' from mtl_supply ms , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll'||

399: ' and rsh.shipment_header_id = rt.shipment_header_id'||' and rt.shipment_line_id=rsl.shipment_line_id'||
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 '||

Line 411: p_sql(15):=' select distinct rs.* '||' from rcv_supply rs , po_headers_all ph ,'||'po_lines_all pl,po_line_locations_all pll '||

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

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 423: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||

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)'||
423: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||
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 '||

Line 427: ' or exists (select 1 from po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||

423: ' or exists (select 3 from rcv_transactions_interface rti , po_headers_all ph '||'where ph.segment1 = '||''''||l_po_number||''''||
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'||

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

443: ' (SELECT 2 FROM rcv_headers_interface rhi'||
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'||

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

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

Line 464: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||

460: ' and rt.shipment_header_id=rsh.shipment_header_id'||' and pll.po_line_id = pl.po_line_id '||' and pl.po_header_id = ph.po_header_id'||
461: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
462: p_sql(21):=' select mtt.transaction_type_id , mtt.transaction_type_name , '||'mtt.transaction_source_type_id , mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date'||
463: ' from mtl_transaction_types mtt where'||
464: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||
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||')';

Line 467: ' or exists (select 2 from mtl_material_transactions_temp mmtt , po_headers_all ph '||' where mmtt.transaction_source_id = ph.po_header_id '||

463: ' from mtl_transaction_types mtt where'||
464: ' exists ('||' select 1 from mtl_material_transactions mmt , po_headers_all ph '||
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 ,'||

Line 470: ' po_headers_all ph '||

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 '||
474: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

Line 477: ' po_headers_all ph '||' where 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'||
481: ' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||

Line 484: p_sql(23):=' select distinct mmtt.* '||' from mtl_material_transactions_temp mmtt , po_headers_all ph,'||

480: ' and mol.transaction_type_id=18'||
481: ' and rsl.po_header_id = ph.po_header_id '||' and ph.segment1 = '||''''||l_po_number||''''||
482: ' and ph.org_id = '||l_operating_id||' and rsh.receipt_num='||''''||l_receipt_num||''''||
483: ' and rsh.ship_to_org_id='||l_org_id;
484: p_sql(23):=' select distinct mmtt.* '||' from mtl_material_transactions_temp mmtt , po_headers_all ph,'||
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 '||

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

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'||
494: ' and fsp.org_id = ph.org_id'||' and ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
495: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
496: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;

Line 497: p_sql(25):=' select distinct mp.* '||' from mtl_parameters mp , 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'||
494: ' and fsp.org_id = ph.org_id'||' and ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
495: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
496: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
497: p_sql(25):=' select distinct mp.* '||' from mtl_parameters mp , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , '||
498: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||'and pll.po_line_id = pl.po_line_id '||
499: ' and fsp.org_id = ph.org_id'||' and mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
500: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
501: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;

Line 502: p_sql(26):=' select distinct rp.* '||'from rcv_parameters rp , po_line_locations_all pll ,'||'po_lines_all pl , po_headers_all ph,'||

498: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||'and pll.po_line_id = pl.po_line_id '||
499: ' and fsp.org_id = ph.org_id'||' and mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)'||
500: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
501: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
502: p_sql(26):=' select distinct rp.* '||'from rcv_parameters rp , po_line_locations_all pll ,'||'po_lines_all pl , po_headers_all ph,'||
503: ' financials_system_params_all fsp where '||'pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||
504: ' and fsp.org_id = ph.org_id '||'and (rp.organization_id = fsp.inventory_organization_id '||
505: ' or rp.organization_id = pll.ship_to_organization_id)'||
506: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

Line 508: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||

504: ' and fsp.org_id = ph.org_id '||'and (rp.organization_id = fsp.inventory_organization_id '||
505: ' or rp.organization_id = pll.ship_to_organization_id)'||
506: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
507: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
508: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
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'||

Line 510: p_sql(28):=' select distinct fsp.* '||' from financials_system_params_all fsp , po_headers_all ph '||' where fsp.org_id = ph.org_id '||

506: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
507: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num;
508: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
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'||

Line 512: p_sql(29):=' select distinct msn.* '||' from mtl_serial_numbers msn , mtl_unit_transactions mut , po_headers_all ph'||

508: p_sql(27):=' select distinct psp.* '||' from po_system_parameters_all psp , po_headers_all ph '||' where psp.org_id = ph.org_id '||
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 '||

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

519: ' and msn.serial_number = mut.serial_number'||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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'||

Line 534: p_sql(30):=' select distinct msnt.* '||'from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt'||', po_headers_all ph,'||

530: ' and rt.shipment_header_id=rsh.shipment_header_id'||
531: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
532: ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
533: ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
534: p_sql(30):=' select distinct msnt.* '||'from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt'||', po_headers_all ph,'||
535: ' po_lines_all pl , po_line_locations_all pll'||' where '||
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||

Line 544: ' select msnt.* '||' from mtl_serial_numbers_temp msnt'||', mtl_material_transactions_temp mmtt , po_headers_all ph,'||

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,'||
545: ' po_lines_all pl , po_line_locations_all pll,'||'mtl_transaction_lots_temp mtln where '||'mmtt.transaction_source_id = ph.po_header_id'||
546: ' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
547: ' and mtln.transaction_temp_id = mmtt.transaction_temp_id'||
548: ' and msnt.transaction_temp_id = mtln.serial_transaction_temp_id'||

Line 556: ' or exists (select 1 from po_headers_all ph '||' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||

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

Line 560: p_sql(32):=' select distinct mut.* '||'from mtl_unit_transactions mut , po_headers_all ph , mtl_material_transactions mmt,'||

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

Line 570: ' select mut.* '||' from mtl_unit_transactions mut , po_headers_all ph , '||'mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln,'||

566: ' and mut.transaction_id = mmt.transaction_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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 '||

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 604: p_sql(36):=' select distinct mln.* '||'from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln ,'||'po_headers_all ph,'||

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

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

612: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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'||

Line 626: ''''||l_po_number||''''||'or exists (select 1 from po_headers_all ph,'||' po_lines_all pl , po_line_locations_all pll'||

622: ' and mtln.transaction_id = mmt.transaction_id '||' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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'||

Line 635: p_sql(39):=' select distinct mtlt.* '||'from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ,'||'po_headers_all ph,'||

631: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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 '||

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 661: ' and exists (select 1 from po_headers_all ph,'||

657: ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||''''||l_operating_id||''''||
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'||