[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RCV_PO_COMMON
Source
1 PACKAGE BODY INV_DIAG_RCV_PO_COMMON AS
2 /* $Header: INVDPO2B.pls 120.0.12000000.1 2007/08/09 06:49:54 ssadasiv noship $ */
3 ---------------------------------------------------------------
4 -- Package to Build sqls for PO,OU,PLL or POL combination
5 -- This Package is also used for PO,OU,PLL and POL combination
6 ---------------------------------------------------------------
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
16 BEGIN
17
18 -- Build the condition based on the input
19 IF p_line_num IS NULL THEN
20 l_line_num := ' pl.line_num ';
21 END IF;
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 '||
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 ,'||
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 '||
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 '||
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'||
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';
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 ='||
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';
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'||
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'||
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';
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 '||
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||'''';
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||
122 ' 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'||
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||')';
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;*/
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;
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 ';
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;
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;
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||
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'||
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 '||
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'||
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,'||
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 '||
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'||
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||
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'||
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 '||
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'||
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 '||
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||''''||
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||''''||
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 '||
235 l_operating_id||''''||
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 = '||''''||
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 '||
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;
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||
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 '||
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||
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||''''||
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||
283 ' and pl.line_num ='||l_line_num||
284 ' and pll.shipment_num ='||l_line_loc_num||')'||
285 ' or (nvl(rti.document_num ,''-9999'') ='||''''||l_po_number||''''||')';
286 RETURN;
287 END; -- END build_po_all_sql
288
289 ---------------------------------------------------------------------
290 -- Procedure to Build sqls for PO,OU,PLL,POL,RCV and Org combination
291 --
292 ---------------------------------------------------------------------
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;
304 BEGIN
301 l_line_num VARCHAR2(1000) := p_line_num;
302 l_line_loc_num VARCHAR2(1000) := p_line_loc_num;
303
305
306 -- Build the condition based on the input
307 IF p_line_num IS NULL THEN
308 l_line_num := ' pl.line_num ';
309 END IF;
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'||
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||
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;
332 p_sql(4) := ' select distinct pd.* '||' from po_line_locations_all pll , po_lines_all pl , '||' po_headers_all ph , po_distributions_all pd '||
333 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
334 ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
335 l_line_num||' and pll.shipment_num ='||l_line_loc_num||
336 ') and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||
337 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
338 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
339 p_sql(5) := ' select distinct gcc.* '||' from gl_code_combinations gcc , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph ,'||
340 ' po_distributions_all pd,rcv_shipment_lines rsl, rcv_shipment_headers rsh '||' where gcc.summary_flag = ''N'''||
341 ' and gcc.template_id is null and '||' (ph.segment1 = '||''''||l_po_number||''''||
342 ' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
343 ') and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id = pd.line_location_id'||
344 ' and gcc.code_combination_id in '||'(pd.accrual_account_id , pd.budget_account_id '||', pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)'||
345 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
346 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
347 p_sql(6) := ' select distinct rrsl.* '||' from rcv_receiving_sub_ledger rrsl , rcv_transactions rt , po_headers_all ph,'||
348 'po_lines_all pl,po_line_locations_all pll '||',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
349 ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
350 l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||' and rrsl.rcv_transaction_id = rt.transaction_id'||
351 ' and ph.po_header_id=pl.po_header_id '||'and pll.po_line_id=pl.po_line_id'||' and pll.line_location_id=rrsl.reference3'||
352 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
353 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
354 /*p_sql(7) := ' select distinct id.* '||' from ap_invoice_distributions_all id , po_line_locations_all pll , '||'po_lines_all pl , po_headers_all ph , po_distributions_all pd '||
355 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
356 ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||
357 l_line_loc_num||') and pl.po_header_id = ph.po_header_id '||
358 ' and pll.po_line_id = pl.po_line_id '||'and pll.line_location_id = pd.line_location_id '||'and id.po_distribution_id = pd.po_distribution_id'||
359 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
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 '||
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'||
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 '||
366 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||
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'||
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 ='||
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'||
393 ' and rsl.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
394 p_sql(13):=' select distinct rt.* '||' from rcv_transactions rt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
395 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
396 ' where (ph.segment1 = '||''''||l_po_number||
397 ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
398 l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rt.po_header_id = ph.po_header_id '||
399 ' and rsh.shipment_header_id = rt.shipment_header_id'||' and rt.shipment_line_id=rsl.shipment_line_id'||
400 ' and rsl.shipment_header_id=rsh.shipment_header_id'||' and pll.po_line_id = pl.po_line_id '||
401 ' and pll.line_location_id=rt.po_line_location_id'||' and rt.po_line_id=pl.po_line_id'||
402 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
403 p_sql(14):=' select distinct ms.* '||' from mtl_supply ms , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll'||
404 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
405 ' where (ph.segment1 = '||''''||l_po_number||
406 ''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
407 l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and ms.po_header_id = ph.po_header_id '||
408 ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=ms.po_line_location_id'||
409 ' and rsl.shipment_header_id=rsh.shipment_header_id'||' and ms.shipment_line_id=rsl.shipment_line_id'||
410 ' and ms.po_line_id=pl.po_line_id'||' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
411 p_sql(15):=' select distinct rs.* '||' from rcv_supply rs , po_headers_all ph ,'||'po_lines_all pl,po_line_locations_all pll '||
412 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
413 ' where (ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||' and pl.line_num ='||
414 l_line_num||' and pll.shipment_num ='||l_line_loc_num||') and rs.po_header_id = ph.po_header_id '||
415 ' and pll.po_line_id = pl.po_line_id '||' and pll.line_location_id=rs.po_line_location_id'||
416 ' and rs.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rs.shipment_line_id=rsl.shipment_line_id'||
417 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
421 ' 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'||
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 '||
428 ',rcv_shipment_lines rsl, rcv_shipment_headers rsh '||
429 ' where ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
430 ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
431 ' and rti.po_header_id = ph.po_header_id'||' and rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_header_id=ph.po_header_id'||
432 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
433 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
434 ' and pll.po_line_id = pl.po_line_id '||' and pl.po_header_id = ph.po_header_id )';
435
436 p_sql(18) := 'SELECT DISTINCT pie.* '||' FROM po_interface_errors pie , '||
437 ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||
438 ' AND rsh.ship_to_org_id='||l_org_id||' AND ( '||
439 ' EXISTS (SELECT 1'||' FROM rcv_transactions_interface rti'||
440 ' WHERE pie.interface_line_id = rti.interface_transaction_id'||
441 ' AND rsh.shipment_header_id=rti.shipment_header_id )'||
442 ' OR EXISTS '||
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'||
452 ' and rsl.po_line_id=pl.po_line_id'||' and rsl.shipment_header_id=rsh.shipment_header_id'||' and rsl.po_line_location_id=pll.line_location_id'||
453 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id;
454 p_sql(20):=' select distinct mmt.* '||'from mtl_material_transactions mmt , po_headers_all ph,'||'po_lines_all pl,po_line_locations_all pll '||
455 ' ,rcv_transactions rt,rcv_shipment_headers rsh'||
456 ' where mmt.transaction_source_id = ph.po_header_id '||
457 ' and mmt.transaction_source_type_id = 1 and '||'ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
458 ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
459 ' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id = pl.po_line_id'||' and rt.transaction_id=mmt.rcv_transaction_id'||
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||')';
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||
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,'||
480 ' and mol.transaction_type_id=18'||
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'||
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 '||
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;
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;
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||
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'||
515 ' and mmt.transaction_source_type_id = 1 '||'and mut.transaction_id = mmt.transaction_id'||
516 ' and msn.inventory_item_id = mut.inventory_item_id '||' and msn.current_organization_id = mut.organization_id '||
517 ' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id = pl.po_line_id'||' and rt.transaction_id=mmt.rcv_transaction_id'||
518 ' and rt.shipment_header_id=rsh.shipment_header_id'||
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'||
528 ' and msn.current_organization_id = mut.organization_id'||' and msn.serial_number = mut.serial_number '||
529 ' 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'||
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,'||
538 ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
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 '||
539 ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
540 ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
541 ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
542 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')'||
543 ' union all '||
544 ' select msnt.* '||' from mtl_serial_numbers_temp msnt'||', mtl_material_transactions_temp mmtt , po_headers_all ph,'||
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'||
549 ' and ph.segment1 = '||''''||l_po_number||''''||' and ph.org_id = '||l_operating_id||
550 ' and pl.line_num ='||l_line_num||' and pll.shipment_num ='||l_line_loc_num||
551 ' and exists (select 1 from rcv_shipment_headers rsh,rcv_shipment_lines rsl'||
552 ' where rsh.shipment_header_id=rsl.shipment_header_id'||' and rsl.po_line_id=pl.po_line_id'||' and rsl.po_line_location_id=pll.line_location_id'||
553 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||')';
554 p_sql(31):=' select distinct msni.* '||'from mtl_serial_numbers_interface msni , rcv_transactions_interface rti'||',rcv_shipment_headers rsh '||
555 ' where (nvl(rti.document_num , ''-9999'') = '||''''||l_po_number||''''||
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'||
565 ' and rt.shipment_header_id=rsh.shipment_header_id'||
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 '||
575 ' and rt.transaction_id=mmt.rcv_transaction_id'||' and rt.po_line_location_id=pll.line_location_id'||' and rt.po_line_id=pl.po_line_id'||
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'||
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'||
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||
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'||
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'||
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||
602 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
603 ')) and rsi.interface_transaction_id = rti.interface_transaction_id';
604 p_sql(36):=' select distinct mln.* '||'from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln ,'||'po_headers_all ph,'||
605 ' po_lines_all pl , po_line_locations_all pll,'||
606 ' mtl_material_transactions mmt'||',rcv_transactions rt,rcv_shipment_headers rsh'||
607 ' where mmt.transaction_source_id = ph.po_header_id '||' and pl.po_header_id = ph.po_header_id '||' and pll.po_line_id = pl.po_line_id'||
608 ' and mmt.transaction_source_type_id = 1 '||'and mtln.transaction_id = mmt.transaction_id '||
609 ' and mtln.lot_number = mln.lot_number'||' and mtln.inventory_item_id = mln.inventory_item_id '||' and mtln.organization_id = mln.organization_id '||
610 ' and mmt.rcv_transaction_id=rt.transaction_id'||' and rsh.shipment_header_id=rt.shipment_header_id'||
611 ' and rt.po_line_location_id=pll.line_location_id'||
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'||
621 ' and rt.po_line_location_id=pll.line_location_id'||
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'||
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 '||
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'||
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'||
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'||
666 ' and ph.segment1 = '||''''||l_po_number||''''||
667 ' and ph.org_id = '||l_operating_id||
668 ' and pl.line_num ='||l_line_num||
669 ' and pll.shipment_num ='||l_line_loc_num||
670 ' and rsh.receipt_num='||''''||l_receipt_num||''''||' and rsh.ship_to_org_id='||l_org_id||
671 ')'||' or (nvl(rti.document_num ,''-9999'') ='||''''||l_po_number||''''||')';
672 RETURN;
673 END;
674
675 END INV_DIAG_RCV_PO_COMMON;