[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RCV_IPROC_COMMON
Source
1 PACKAGE BODY inv_diag_rcv_iproc_common AS
2 /* $Header: INVREQ2B.pls 120.2 2008/03/20 11:41:03 srnatara noship $ */
3 --------------------------------------------------
4 -- Package to Build sqls for Req and OU combination
5 --------------------------------------------------
6 PROCEDURE build_req_sql(p_operating_id IN NUMBER,
7 p_req_number IN VARCHAR2,
8 p_line_num IN NUMBER,
9 p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list)
10 IS
11 -- Initialize Local Variables.
12 l_operating_id po_requisition_headers_all.org_id%TYPE := p_operating_id;
13 l_req_number po_requisition_headers_all.segment1%TYPE := p_req_number;
14 l_line_num VARCHAR2(1000) := p_line_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 := ' prl.line_num ';
21 END IF;
22
23 p_sql(1) := ' select distinct prh.*' ||
24 ' from po_requisition_headers_all prh,' ||
25 ' po_requisition_lines_all prl' ||
26 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
27 ' and prh.segment1 = '||''''||l_req_number||''''||
28 ' and prh.org_id = '||l_operating_id||
29 ' and prl.line_num='||l_line_num||
30 ' and prl.requisition_header_id = prh.requisition_header_id' ||
31 ' and prl.source_type_code = ''VENDOR'' ';
32
33 p_sql(2) := ' select distinct prl.*' ||
34 ' from po_requisition_lines_all prl,' ||
35 ' po_requisition_headers_all prh ' ||
36 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
37 ' and prh.segment1 = '||''''||l_req_number||''''||
38 ' and prh.org_id = '||l_operating_id||
39 ' and prl.line_num='||l_line_num||
40 ' and prh.requisition_header_id = prl.requisition_header_id' ||
41 ' and prl.source_type_code = ''VENDOR''' ||
42 ' order by prl.requisition_line_id ';
43
44 p_sql(3) := ' select distinct prd.*' ||
45 ' from po_req_distributions_all prd ,' ||
46 ' po_requisition_lines_all prl ,' ||
47 ' po_requisition_headers_all prh' ||
48 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
49 ' and prh.requisition_header_id = prl.requisition_header_id' ||
50 ' and prl.requisition_line_id = prd.requisition_line_id' ||
51 ' and prl.source_type_code = ''VENDOR''' ||
52 ' and prh.segment1 = '||''''||l_req_number||''''||
53 ' and prh.org_id = '||l_operating_id||
54 ' and prl.line_num='||l_line_num||
55 ' order by prd.distribution_id ';
56
57 p_sql(4) := ' SELECT distinct ph.* ' ||
58 ' from po_headers_all ph,' ||
59 ' po_distributions_all pd,' ||
60 ' po_req_distributions_all prd ,' ||
61 ' po_requisition_lines_all prl ,' ||
62 ' po_requisition_headers_all prh' ||
63 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
64 ' and prh.requisition_header_id = prl.requisition_header_id' ||
65 ' and prl.requisition_line_id = prd.requisition_line_id' ||
66 ' and prl.source_type_code = ''VENDOR''' ||
67 ' and pd.req_distribution_id = prd.distribution_id' ||
68 ' and pd.po_header_id=ph.po_header_id' ||
69 ' and prh.segment1 = '||''''||l_req_number||''''||
70 ' and prl.line_num='||l_line_num||
71 ' and prh.org_id = '||l_operating_id;
72
73
74 p_sql(5) := ' SELECT distinct pl.* ' ||
75 ' from po_lines_all pl , ' ||
76 ' po_headers_all ph,' ||
77 ' po_distributions_all pd,' ||
78 ' po_req_distributions_all prd ,' ||
79 ' po_requisition_lines_all prl ,' ||
80 ' po_requisition_headers_all prh' ||
81 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
82 ' and prh.requisition_header_id = prl.requisition_header_id' ||
83 ' and prl.requisition_line_id = prd.requisition_line_id' ||
84 ' and prl.source_type_code = ''VENDOR''' ||
85 ' and pd.req_distribution_id = prd.distribution_id' ||
86 ' and pd.po_header_id=ph.po_header_id' ||
87 ' AND pl.po_header_id = ph.po_header_id' ||
88 ' AND pd.po_line_id=pl.po_line_id'||
89 ' and prh.segment1 = '||''''||l_req_number||''''||
90 ' and prl.line_num='||l_line_num||
91 ' and prh.org_id = '||l_operating_id;
92
93
94 /* Bug#6882986
95 * Due to missing join conditions, queries were fetching data
96 * not related to the req line number provided by the user.
97 * Added required join conditions to fetch only records pertaining
98 * to the req line number entered by the user.
99 */
100 p_sql(6) := ' SELECT distinct pll.* ' ||
101 ' from po_line_locations_all pll , ' ||
102 ' po_lines_all pl , ' ||
103 ' po_headers_all ph,' ||
104 ' po_distributions_all pd,' ||
105 ' po_req_distributions_all prd ,' ||
106 ' po_requisition_lines_all prl ,' ||
107 ' po_requisition_headers_all prh' ||
108 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
109 ' and prh.requisition_header_id = prl.requisition_header_id' ||
110 ' and prl.requisition_line_id = prd.requisition_line_id' ||
111 ' and prl.source_type_code = ''VENDOR''' ||
112 ' and pd.req_distribution_id = prd.distribution_id' ||
113 ' and pd.po_header_id=ph.po_header_id' ||
114 ' AND pl.po_header_id = ph.po_header_id' ||
115 ' AND pd.po_line_id=pl.po_line_id'||
116 ' AND pll.line_location_id = pd.line_location_id ' || --Bug#6882986
117 ' AND pll.po_line_id = pl.po_line_id ' ||
118 ' and prh.segment1 = '||''''||l_req_number||''''||
119 ' and prl.line_num='||l_line_num||
120 ' and prh.org_id = '||l_operating_id;
121
122
123 p_sql(7) := ' SELECT distinct pd.* ' ||
124 ' from po_line_locations_all pll , ' ||
125 ' po_lines_all pl , ' ||
126 ' po_headers_all ph , ' ||
127 ' po_distributions_all pd,' ||
128 ' po_req_distributions_all prd ,' ||
129 ' po_requisition_lines_all prl ,' ||
130 ' po_requisition_headers_all prh ' ||
131 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
132 ' and prh.requisition_header_id = prl.requisition_header_id' ||
133 ' and prl.requisition_line_id = prd.requisition_line_id' ||
134 ' and prl.source_type_code = ''VENDOR''' ||
135 ' and pd.req_distribution_id = prd.distribution_id' ||
136 ' and pd.po_header_id=ph.po_header_id' ||
137 ' AND pl.po_header_id = ph.po_header_id' ||
138 ' AND pll.po_line_id = pl.po_line_id ' ||
139 ' AND pll.line_location_id = pd.line_location_id' ||
140 ' and prh.segment1 = '||''''||l_req_number||''''||
141 ' and prl.line_num='||l_line_num||
142 ' and prh.org_id = '||l_operating_id;
143
144
145 p_sql(8) := ' SELECT distinct gcc.* ' ||
146 ' from gl_code_combinations gcc , ' ||
147 ' po_line_locations_all pll , ' ||
148 ' po_lines_all pl , ' ||
149 ' po_headers_all ph , ' ||
150 ' po_distributions_all pd,' ||
151 ' po_req_distributions_all prd ,' ||
152 ' po_requisition_lines_all prl ,' ||
153 ' po_requisition_headers_all prh' ||
154 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
155 ' and prh.requisition_header_id = prl.requisition_header_id' ||
156 ' and prl.requisition_line_id = prd.requisition_line_id' ||
157 ' and prl.source_type_code = ''VENDOR''' ||
158 ' and gcc.summary_flag = ''N'' ' ||
159 ' and pd.req_distribution_id = prd.distribution_id' ||
160 ' and pd.po_header_id=ph.po_header_id' ||
161 ' AND pl.po_header_id = ph.po_header_id' ||
162 ' AND pd.po_line_id=pl.po_line_id'||
163 ' AND pll.po_line_id = pl.po_line_id ' ||
164 ' AND pll.line_location_id = pd.line_location_id' ||
165 ' AND gcc.template_id is null ' ||
166 ' AND gcc.code_combination_id in (pd.accrual_account_id , pd.budget_account_id , pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)' ||
167 ' and prh.segment1 = '||''''||l_req_number||''''||
168 ' and prl.line_num='||l_line_num||
169 ' and prh.org_id = '||l_operating_id;
170
171 p_sql(9) := ' SELECT distinct rrsl.* ' ||
172 ' from rcv_receiving_sub_ledger rrsl , ' ||
173 ' rcv_transactions rt , ' ||
174 ' po_headers_all ph,' ||
175 ' po_line_locations_all pll , ' ||--Bug#6882986
176 ' po_distributions_all pd,' ||
177 ' po_req_distributions_all prd ,' ||
178 ' po_requisition_lines_all prl ,' ||
179 ' po_requisition_headers_all prh' ||
180 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
181 ' and prh.requisition_header_id = prl.requisition_header_id' ||
182 ' and prl.requisition_line_id = prd.requisition_line_id' ||
183 ' and prl.source_type_code = ''VENDOR''' ||
184 ' and pd.req_distribution_id = prd.distribution_id' ||
185 ' and pd.po_header_id=ph.po_header_id' ||
186 ' and pll.po_header_id=ph.po_header_id' ||--Bug#6882986
187 ' and pll.line_location_id=pd.line_location_id' ||--Bug#6882986
188 ' and pll.line_location_id=rt.po_line_location_id' ||--Bug#6882986
189 ' AND rt.po_header_id = ph.po_header_id ' ||
190 ' AND rrsl.rcv_transaction_id = rt.transaction_id' ||
191 ' and prh.segment1 = '||''''||l_req_number||''''||
192 ' and prl.line_num='||l_line_num||
193 ' and prh.org_id = '||l_operating_id;
194
195 /*p_sql(10) := ' SELECT distinct id.* ' ||
196 ' from ap_invoice_distributions_all id , ' ||
197 ' po_line_locations_all pll , ' ||
198 ' po_lines_all pl , ' ||
199 ' po_headers_all ph , ' ||
200 ' po_distributions_all pd,' ||
201 ' po_req_distributions_all prd ,' ||
202 ' po_requisition_lines_all prl ,' ||
203 ' po_requisition_headers_all prh ' ||
204 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
205 ' and prh.requisition_header_id = prl.requisition_header_id' ||
206 ' and prl.requisition_line_id = prd.requisition_line_id' ||
207 ' and prl.source_type_code = ''VENDOR''' ||
208 ' and pd.req_distribution_id = prd.distribution_id' ||
209 ' and pd.po_header_id=ph.po_header_id' ||
210 ' AND pl.po_header_id = ph.po_header_id' ||
211 ' AND pd.po_line_id=pl.po_line_id'||
212 ' AND pll.po_line_id = pl.po_line_id ' ||
213 ' AND pll.line_location_id = pd.line_location_id' ||
214 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
215 ' and prh.segment1 = '||''''||l_req_number||''''||
216 ' and prl.line_num='||l_line_num||
217 ' and prh.org_id = '||l_operating_id;*/
218
219 p_sql(10) := ' SELECT distinct id.* ' ||
220 ' from ap_invoice_lines_all id , ' ||
221 ' po_line_locations_all pll , ' ||
222 ' po_lines_all pl , ' ||
223 ' po_headers_all ph , ' ||
224 ' po_distributions_all pd,' ||
225 ' po_req_distributions_all prd ,' ||
226 ' po_requisition_lines_all prl ,' ||
227 ' po_requisition_headers_all prh ' ||
228 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
229 ' and prh.requisition_header_id = prl.requisition_header_id' ||
230 ' and prl.requisition_line_id = prd.requisition_line_id' ||
231 ' and prl.source_type_code = ''VENDOR''' ||
232 ' and pd.req_distribution_id = prd.distribution_id' ||
233 ' and pd.po_header_id=ph.po_header_id' ||
234 ' AND pl.po_header_id = ph.po_header_id' ||
235 ' AND pd.po_line_id=pl.po_line_id'||
236 ' AND pll.po_line_id = pl.po_line_id ' ||
237 ' AND pll.line_location_id = pd.line_location_id' ||
238 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
239 ' and id.po_line_location_id=pll.line_location_id'||' and id.po_line_id=pl.po_line_id'||
240 ' and prh.segment1 = '||''''||l_req_number||''''||
241 ' and prl.line_num='||l_line_num||
242 ' and prh.org_id = '||l_operating_id;
243
244 p_sql(11) := ' SELECT distinct ai.* ' ||
245 ' from ap_invoices_all ai , ' ||
246 ' ap_invoice_distributions_all id , ' ||
247 ' po_line_locations_all pll , ' ||
248 ' po_lines_all pl , ' ||
249 ' po_headers_all ph , ' ||
250 ' po_distributions_all pd,' ||
251 ' po_req_distributions_all prd ,' ||
252 ' po_requisition_lines_all prl ,' ||
253 ' po_requisition_headers_all prh ' ||
254 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
255 ' and prh.requisition_header_id = prl.requisition_header_id' ||
256 ' and prl.requisition_line_id = prd.requisition_line_id' ||
257 ' and prl.source_type_code = ''VENDOR''' ||
258 ' and pd.req_distribution_id = prd.distribution_id' ||
259 ' AND pd.po_line_id=pl.po_line_id'||
260 ' and pd.po_header_id=ph.po_header_id' ||
261 ' AND pl.po_header_id = ph.po_header_id' ||
262 ' AND pll.po_line_id = pl.po_line_id ' ||
263 ' AND pll.line_location_id = pd.line_location_id' ||
264 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
265 ' AND ai.invoice_id = id.invoice_id' ||
266 ' and prh.segment1 = '||''''||l_req_number||''''||
267 ' and prl.line_num='||l_line_num||
268 ' and prh.org_id = '||l_operating_id;
269
270 p_sql(12) := ' SELECT distinct ili.* ' ||
271 ' from ap_invoice_lines_interface ili , ' ||
272 ' po_line_locations_all pll , ' ||
273 ' po_lines_all pl , ' ||
274 ' po_headers_all ph , ' ||
275 ' po_distributions_all pd,' ||
276 ' po_req_distributions_all prd ,' ||
277 ' po_requisition_lines_all prl ,' ||
278 ' po_requisition_headers_all prh ' ||
279 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
280 ' and prh.requisition_header_id = prl.requisition_header_id' ||
281 ' and prl.requisition_line_id = prd.requisition_line_id' ||
282 ' and prl.source_type_code = ''VENDOR''' ||
283 ' and pd.req_distribution_id = prd.distribution_id' ||
284 ' and pd.po_header_id=ph.po_header_id' ||
285 ' AND pl.po_header_id = ph.po_header_id' ||
286 ' AND pll.po_line_id = pl.po_line_id ' ||
287 ' AND pd.po_line_id=pl.po_line_id'||
288 ' AND pll.line_location_id = pd.line_location_id' ||
289 ' and ili.po_header_id = ph.po_header_id ' ||
290 ' and prh.segment1 = '||''''||l_req_number||''''||
291 ' and prl.line_num='||l_line_num||
292 ' and prh.org_id = '||l_operating_id;
293
294
295 p_sql(13) := ' SELECT distinct ihi.* ' ||
296 ' from ap_invoices_interface ihi , ' ||
297 ' ap_invoice_lines_interface ili , ' ||
298 ' po_line_locations_all pll , ' ||
299 ' po_lines_all pl , ' ||
300 ' po_headers_all ph , ' ||
301 ' po_distributions_all pd,' ||
302 ' po_req_distributions_all prd ,' ||
303 ' po_requisition_lines_all prl ,' ||
304 ' po_requisition_headers_all prh ' ||
305 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
306 ' and prh.requisition_header_id = prl.requisition_header_id' ||
307 ' and prl.requisition_line_id = prd.requisition_line_id' ||
308 ' and prl.source_type_code = ''VENDOR''' ||
309 ' and pd.req_distribution_id = prd.distribution_id' ||
310 ' and pd.po_header_id=ph.po_header_id' ||
311 ' AND pl.po_header_id = ph.po_header_id' ||
312 ' AND pll.po_line_id = pl.po_line_id ' ||
313 ' AND pll.line_location_id = pd.line_location_id' ||
314 ' AND pd.po_line_id=pl.po_line_id'||
315 ' and ili.po_header_id = ph.po_header_id ' ||
316 ' AND ihi.invoice_id = ili.invoice_id ' ||
317 ' and prh.segment1 = '||''''||l_req_number||''''||
318 ' and prl.line_num='||l_line_num||
319 ' and prh.org_id = '||l_operating_id;
320
321 p_sql(14) := ' SELECT DISTINCT rsh.* ' ||
322 ' from rcv_shipment_lines rsl , ' ||
323 ' rcv_shipment_headers rsh, ' ||
324 ' po_line_locations_all pll , ' ||
325 ' po_distributions_all pd,' ||
326 ' po_req_distributions_all prd ,' ||
327 ' po_requisition_lines_all prl ,' ||
328 ' po_requisition_headers_all prh ' ||
329 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
330 ' and prh.requisition_header_id = prl.requisition_header_id' ||
331 ' and prl.requisition_line_id = prd.requisition_line_id' ||
332 ' and prl.source_type_code = ''VENDOR''' ||
333 ' and pd.req_distribution_id = prd.distribution_id' ||
334 ' and rsl.po_line_location_id=pll.line_location_id' ||
335 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
336 ' AND pll.line_location_id = pd.line_location_id' ||
337 ' and prh.segment1 = '||''''||l_req_number||''''||
338 ' and prl.line_num='||l_line_num||
339 ' and prh.org_id = '||l_operating_id;
340
341 p_sql(15) := ' SELECT DISTINCT rsl.* ' ||
342 ' from rcv_shipment_lines rsl , ' ||
343 ' rcv_shipment_headers rsh, ' ||
344 ' po_line_locations_all pll , ' ||
345 ' po_distributions_all pd,' ||
346 ' po_req_distributions_all prd ,' ||
347 ' po_requisition_lines_all prl ,' ||
351 ' and prl.requisition_line_id = prd.requisition_line_id' ||
348 ' po_requisition_headers_all prh ' ||
349 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
350 ' and prh.requisition_header_id = prl.requisition_header_id' ||
352 ' and prl.source_type_code = ''VENDOR''' ||
353 ' and pd.req_distribution_id = prd.distribution_id' ||
354 ' and rsl.po_line_location_id=pll.line_location_id' ||
355 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
356 ' AND pll.line_location_id = pd.line_location_id' ||
357 ' and prh.segment1 = '||''''||l_req_number||''''||
358 ' and prl.line_num='||l_line_num||
359 ' and prh.org_id = '||l_operating_id;
360
361 p_sql(16) := ' SELECT DISTINCT rt.* ' ||
362 ' from rcv_transactions rt , ' ||
363 ' rcv_shipment_headers rsh, ' ||
364 ' po_line_locations_all pll , ' ||
365 ' po_distributions_all pd,' ||
366 ' po_req_distributions_all prd ,' ||
367 ' po_requisition_lines_all prl ,' ||
368 ' po_requisition_headers_all prh ' ||
369 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
370 ' and prh.requisition_header_id = prl.requisition_header_id' ||
371 ' and prl.requisition_line_id = prd.requisition_line_id' ||
372 ' and prl.source_type_code = ''VENDOR''' ||
373 ' and pd.req_distribution_id = prd.distribution_id' ||
374 ' and rt.po_line_location_id=pll.line_location_id' ||
375 ' AND rt.shipment_header_id = rsh.shipment_header_id ' ||
376 ' AND pll.line_location_id = pd.line_location_id' ||
377 ' and prh.segment1 = '||''''||l_req_number||''''||
378 ' and prl.line_num='||l_line_num||
379 ' and prh.org_id = '||l_operating_id;
380
381
382 p_sql(17) := ' SELECT DISTINCT ms.* ' ||
383 ' from mtl_supply ms , ' ||
384 ' po_line_locations_all pll , ' ||
385 ' po_distributions_all pd,' ||
386 ' po_req_distributions_all prd ,' ||
387 ' po_requisition_lines_all prl ,' ||
388 ' po_requisition_headers_all prh ' ||
389 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
390 ' and prh.requisition_header_id = prl.requisition_header_id' ||
391 ' and prl.requisition_line_id = prd.requisition_line_id' ||
392 ' and prl.source_type_code = ''VENDOR''' ||
393 ' and pd.req_distribution_id = prd.distribution_id' ||
394 ' and ms.po_line_location_id=pll.line_location_id' ||
395 ' AND pll.line_location_id = pd.line_location_id' ||
396 ' and prh.segment1 = '||''''||l_req_number||''''||
397 ' and prl.line_num='||l_line_num||
398 ' and prh.org_id = '||l_operating_id;
399
400 p_sql(18) := ' SELECT DISTINCT rs.* ' ||
401 ' from rcv_supply rs , ' ||
402 ' po_line_locations_all pll , ' ||
403 ' po_distributions_all pd,' ||
404 ' po_req_distributions_all prd ,' ||
405 ' po_requisition_lines_all prl ,' ||
406 ' po_requisition_headers_all prh ' ||
407 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
408 ' and prh.requisition_header_id = prl.requisition_header_id' ||
409 ' and prl.requisition_line_id = prd.requisition_line_id' ||
410 ' and prl.source_type_code = ''VENDOR''' ||
411 ' and pd.req_distribution_id = prd.distribution_id' ||
412 ' and rs.po_line_location_id=pll.line_location_id' ||
413 ' AND pll.line_location_id = pd.line_location_id' ||
414 ' and prh.segment1 = '||''''||l_req_number||''''||
415 ' and prl.line_num='||l_line_num||
416 ' and prh.org_id = '||l_operating_id;
417
418 p_sql(19) := ' SELECT DISTINCT rhi.* ' ||
419 ' from rcv_headers_interface rhi ' ||
420 ' WHERE exists ' ||
421 ' (SELECT 1 ' ||
422 ' from rcv_shipment_lines rsl , ' ||
423 ' rcv_shipment_headers rsh, ' ||
424 ' po_line_locations_all pll , ' ||
425 ' po_distributions_all pd,' ||
426 ' po_req_distributions_all prd ,' ||
427 ' po_requisition_lines_all prl ,' ||
428 ' po_requisition_headers_all prh ' ||
429 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
430 ' and prh.requisition_header_id = prl.requisition_header_id' ||
431 ' and prl.requisition_line_id = prd.requisition_line_id' ||
432 ' and prl.source_type_code = ''VENDOR''' ||
433 ' and pd.req_distribution_id = prd.distribution_id' ||
434 ' AND pll.line_location_id = pd.line_location_id' ||
435 ' and rsl.po_line_location_id = pll.line_location_id'||
436 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
437 ' AND rsh.shipment_header_id = rhi.receipt_header_id' ||
438 ' and prh.segment1 = '||''''||l_req_number||''''||
439 ' and prl.line_num='||l_line_num||
440 ' and prh.org_id = '||l_operating_id ||
441 ' ) ' ||
442 'union'||
443 ' SELECT DISTINCT rhi.* ' ||
444 ' from rcv_headers_interface rhi ' ||
445 ' WHERE exists ' ||
446 ' (SELECT 3 ' ||
447 ' from rcv_transactions_interface rti , ' ||
448 ' po_line_locations_all pll , ' ||
449 ' po_lines_all pl , ' ||
450 ' po_headers_all ph , ' ||
451 ' po_distributions_all pd,' ||
452 ' po_req_distributions_all prd ,' ||
453 ' po_requisition_lines_all prl ,' ||
454 ' po_requisition_headers_all prh ' ||
455 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
456 ' and prh.requisition_header_id = prl.requisition_header_id' ||
460 ' and pd.po_header_id=ph.po_header_id' ||
457 ' and prl.requisition_line_id = prd.requisition_line_id' ||
458 ' and prl.source_type_code = ''VENDOR''' ||
459 ' and pd.req_distribution_id = prd.distribution_id' ||
461 ' AND pl.po_header_id = ph.po_header_id' ||
462 ' AND pll.po_line_id = pl.po_line_id ' ||
463 ' AND pll.line_location_id = pd.line_location_id' ||
464 ' AND pd.po_line_id=pl.po_line_id'||
465 ' AND rti.po_header_id = ph.po_header_id ' ||
466 ' AND rti.po_line_location_id = pll.line_location_id ' ||--Bug#6882986
467 ' AND rti.po_header_id is not null ' ||
468 ' AND rhi.header_interface_id = rti.header_interface_id ' ||
469 ' and prh.segment1 = '||''''||l_req_number||''''||
470 ' and prl.line_num='||l_line_num||
471 ' and prh.org_id = '||l_operating_id||
472 ' ) ';
473
474 p_sql(20) := ' SELECT DISTINCT rti.*' ||
475 ' from rcv_transactions_interface rti ' ||
476 ' WHERE exists ' ||
477 ' (SELECT 1 ' ||
478 ' from po_line_locations_all pll , ' ||
479 ' po_lines_all pl , ' ||
480 ' po_headers_all ph , ' ||
481 ' po_distributions_all pd,' ||
482 ' po_req_distributions_all prd ,' ||
483 ' po_requisition_lines_all prl ,' ||
484 ' po_requisition_headers_all prh ' ||
485 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
486 ' and prh.requisition_header_id = prl.requisition_header_id' ||
487 ' and prl.requisition_line_id = prd.requisition_line_id' ||
488 ' and prl.source_type_code = ''VENDOR''' ||
489 ' and pd.req_distribution_id = prd.distribution_id' ||
490 ' and pd.po_header_id=ph.po_header_id' ||
491 ' AND pl.po_header_id = ph.po_header_id' ||
492 ' AND pd.po_line_id=pl.po_line_id'||
493 ' AND pll.po_line_id = pl.po_line_id ' ||
494 ' AND pll.line_location_id = pd.line_location_id' ||
495 ' AND rti.po_header_id = ph.po_header_id' ||
496 ' AND rti.po_line_location_id = pll.line_location_id ' ||--Bug#6882986
497 ' and prh.segment1 = '||''''||l_req_number||''''||
498 ' and prl.line_num='||l_line_num||
499 ' and prh.org_id = '||l_operating_id||
500 ' ) ';
501
502 p_sql(21) := ' SELECT DISTINCT pie.* ' ||
503 ' from po_interface_errors pie , ' ||
504 ' rcv_transactions_interface rti , ' ||
505 ' po_line_locations_all pll , ' ||
506 ' po_distributions_all pd,' ||
507 ' po_req_distributions_all prd ,' ||
508 ' po_requisition_lines_all prl ,' ||
509 ' po_requisition_headers_all prh ' ||
510 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
511 ' and prh.requisition_header_id = prl.requisition_header_id' ||
512 ' and prl.requisition_line_id = prd.requisition_line_id' ||
513 ' and prl.source_type_code = ''VENDOR''' ||
514 ' and pd.req_distribution_id = prd.distribution_id' ||
515 ' AND pll.line_location_id = pd.line_location_id' ||
516 ' and prh.segment1 = '||''''||l_req_number||''''||
517 ' and prl.line_num='||l_line_num||
518 ' and prh.org_id = '||l_operating_id||
519 ' AND rti.po_header_id=pll.po_header_id'||
520 ' AND rti.po_line_location_id = pll.line_location_id ' ||--Bug#6882986
521 ' AND (pie.interface_transaction_id=rti.interface_transaction_id OR '||
522 'pie.interface_line_id = rti.interface_transaction_id)';
523
524 p_sql(22) := ' select distinct msi.*' ||
525 ' from po_requisition_lines_all prl,' ||
526 ' po_requisition_headers_all prh,' ||
527 ' mtl_system_items msi' ||
528 ' where prh.segment1 = '||''''||l_req_number||''''||
529 ' and prh.org_id = '||l_operating_id||
530 ' and prl.line_num='||l_line_num||
531 ' and prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
532 ' and prh.requisition_header_id = prl.requisition_header_id' ||
533 ' and prl.source_type_code = ''VENDOR''' ||
534 ' and prl.item_id = msi.inventory_item_id' ||
535 ' and prl.destination_organization_id = msi.organization_id ';
536
537 p_sql(23) := ' SELECT distinct mmt.* ' ||
538 ' from mtl_material_transactions mmt , ' ||
539 ' po_line_locations_all pll , ' ||
540 ' po_lines_all pl , ' ||
541 ' po_headers_all ph , ' ||
542 ' po_distributions_all pd,' ||
543 ' rcv_transactions rt,' ||--Bug#6882986
544 ' po_req_distributions_all prd ,' ||
545 ' po_requisition_lines_all prl ,' ||
546 ' po_requisition_headers_all prh ' ||
547 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
548 ' and prh.requisition_header_id = prl.requisition_header_id' ||
549 ' and prl.requisition_line_id = prd.requisition_line_id' ||
550 ' and prl.source_type_code = ''VENDOR''' ||
551 ' and pd.req_distribution_id = prd.distribution_id' ||
552 ' and pd.po_header_id=ph.po_header_id' ||
553 ' AND pl.po_header_id = ph.po_header_id' ||
554 ' AND pd.po_line_id=pl.po_line_id'||
555 ' AND pll.po_line_id = pl.po_line_id ' ||
556 ' AND pll.line_location_id = pd.line_location_id' ||
557 ' AND pll.line_location_id = rt.po_line_location_id' ||--Bug#6882986
558 ' AND ph.po_header_id = rt.po_header_id' ||--Bug#6882986
559 ' AND pl.po_line_id = rt.po_line_id' ||--Bug#6882986
560 ' AND mmt.rcv_transaction_id = rt.transaction_id' ||--Bug#6882986
561 ' and prh.segment1 = '||''''||l_req_number||''''||
562 ' and prl.line_num='||l_line_num||
563 ' and prh.org_id = '||l_operating_id;
564
565 p_sql(24) := ' SELECT distinct mtt.transaction_type_id , ' ||
569 ' mtt.user_defined_flag , ' ||
566 ' mtt.transaction_type_name , ' ||
567 ' mtt.transaction_source_type_id , ' ||
568 ' mtt.transaction_action_id , ' ||
570 ' mtt.disable_date ' ||
571 ' from mtl_transaction_types mtt ' ||
572 ' WHERE exists ' ||
573 ' (SELECT 1 ' ||
574 ' from mtl_material_transactions mmt , ' ||
575 ' po_line_locations_all pll , ' ||
576 ' po_lines_all pl , ' ||
577 ' po_headers_all ph , ' ||
578 ' po_distributions_all pd,' ||
579 ' rcv_transactions rt,' ||--Bug#6882986
580 ' po_req_distributions_all prd ,' ||
581 ' po_requisition_lines_all prl ,' ||
582 ' po_requisition_headers_all prh ' ||
583 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
584 ' and prh.requisition_header_id = prl.requisition_header_id' ||
585 ' and prl.requisition_line_id = prd.requisition_line_id' ||
586 ' and prl.source_type_code = ''VENDOR''' ||
587 ' and pd.req_distribution_id = prd.distribution_id' ||
588 ' and pd.po_header_id=ph.po_header_id' ||
589 ' AND pl.po_header_id = ph.po_header_id' ||
590 ' AND pd.po_line_id=pl.po_line_id'||
591 ' AND pll.po_line_id = pl.po_line_id ' ||
592 ' AND pll.line_location_id = pd.line_location_id' ||
593 ' AND pll.line_location_id = rt.po_line_location_id' ||--Bug#6882986
594 ' AND ph.po_header_id = rt.po_header_id' ||--Bug#6882986
595 ' AND pl.po_line_id = rt.po_line_id' ||--Bug#6882986
596 ' AND mmt.rcv_transaction_id = rt.transaction_id' ||--Bug#6882986
597 ' AND mtt.transaction_type_id = mmt.transaction_type_id ' ||
598 ' and prh.segment1 = '||''''||l_req_number||''''||
599 ' and prl.line_num='||l_line_num||
600 ' and prh.org_id = '||l_operating_id||
601 ' ) ' ||
602 ' OR exists ' ||
603 ' (SELECT 2 ' ||
604 ' from mtl_material_transactions_temp mmtt , ' ||
605 ' po_line_locations_all pll , ' ||
606 ' po_lines_all pl , ' ||
607 ' po_headers_all ph , ' ||
608 ' po_distributions_all pd,' ||
609 ' rcv_transactions rt,' ||--Bug#6882986
610 ' po_req_distributions_all prd ,' ||
611 ' po_requisition_lines_all prl ,' ||
612 ' po_requisition_headers_all prh ' ||
613 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
614 ' and prh.requisition_header_id = prl.requisition_header_id' ||
615 ' and prl.requisition_line_id = prd.requisition_line_id' ||
616 ' and prl.source_type_code = ''VENDOR''' ||
617 ' and pd.req_distribution_id = prd.distribution_id' ||
618 ' and pd.po_header_id=ph.po_header_id' ||
619 ' AND pl.po_header_id = ph.po_header_id' ||
620 ' AND pd.po_line_id=pl.po_line_id'||
621 ' AND pll.po_line_id = pl.po_line_id ' ||
622 ' AND pll.line_location_id = pd.line_location_id' ||
623 ' AND pll.line_location_id = rt.po_line_location_id' ||--Bug#6882986
624 ' AND ph.po_header_id = rt.po_header_id' ||--Bug#6882986
625 ' AND pl.po_line_id = rt.po_line_id' ||--Bug#6882986
626 ' AND mmtt.rcv_transaction_id = rt.transaction_id' ||--Bug#6882986
627 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
628 ' AND mtt.transaction_type_id = mmtt.transaction_type_id ' ||
629 ' and prh.segment1 = '||''''||l_req_number||''''||
630 ' and prl.line_num='||l_line_num||
631 ' and prh.org_id = '||l_operating_id ||
632 ' ) ';
633
634 /* p_sql(25) := ' SELECT DISTINCT mol.* ' ||
635 ' from mtl_txn_request_lines mol , ' ||
636 ' rcv_transactions rt , ' ||
637 ' rcv_shipment_lines rsl , ' ||
638 ' po_line_locations_all pll , ' ||
639 ' po_distributions_all pd,' ||
640 ' po_req_distributions_all prd ,' ||
641 ' po_requisition_lines_all prl ,' ||
642 ' po_requisition_headers_all prh ' ||
643 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
644 ' and prh.requisition_header_id = prl.requisition_header_id' ||
645 ' and prl.requisition_line_id = prd.requisition_line_id' ||
646 ' and prl.source_type_code = ''VENDOR''' ||
647 ' and pd.req_distribution_id = prd.distribution_id' ||
648 ' and rsl.po_line_location_id=pll.line_location_id' ||
649 ' and mol.reference_id = decode(mol.reference ,''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,''PO_LINE_LOCATION_ID'' , rt.po_line_location_id , ''ORDER_LINE_ID'' , rt.oe_order_line_id) ' ||
650 ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
651 ' AND mol.organization_id = rt.organization_id ' ||
652 ' AND mol.inventory_item_id = rsl.item_id' ||
653 ' AND pll.line_location_id = pd.line_location_id' ||
654 ' and prh.segment1 = '||''''||l_req_number||''''||
655 ' and prl.line_num='||l_line_num||
656 ' and prh.org_id = '||l_operating_id;*/
657
658 p_sql(25) := ' SELECT DISTINCT mol.* ' ||
659 ' from mtl_txn_request_lines mol , ' ||
660 ' rcv_transactions rt , ' ||
661 ' rcv_shipment_lines rsl , ' ||
662 ' po_line_locations_all pll , ' ||
663 ' po_distributions_all pd,' ||
664 ' po_req_distributions_all prd ,' ||
665 ' po_requisition_lines_all prl ,' ||
666 ' po_requisition_headers_all prh ' ||
667 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
668 ' and prh.requisition_header_id = prl.requisition_header_id' ||
669 ' and prl.requisition_line_id = prd.requisition_line_id' ||
670 ' and prl.source_type_code = ''VENDOR''' ||
671 ' and pd.req_distribution_id = prd.distribution_id' ||
672 ' and rsl.po_line_location_id=pll.line_location_id' ||
676 ' and Nvl(mol.revision,0)=Nvl(rsl.item_revision,0) ' ||
673 ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
674 ' AND mol.organization_id = rt.organization_id ' ||
675 ' AND mol.inventory_item_id = rsl.item_id' ||
677 ' and mol.line_status = 7'||
678 ' and mol.transaction_type_id=18'||
679 ' AND pll.line_location_id = pd.line_location_id' ||
680 ' and prh.segment1 = '||''''||l_req_number||''''||
681 ' and prl.line_num='||l_line_num||
682 ' and prh.org_id = '||l_operating_id;
683
684 p_sql(26) := ' SELECT DISTINCT mmtt.* ' ||
685 ' from mtl_material_transactions_temp mmtt, ' ||
686 ' po_line_locations_all pll , ' ||
687 ' po_lines_all pl , ' ||
688 ' po_headers_all ph , ' ||
689 ' po_distributions_all pd,' ||
690 ' rcv_transactions rt,' ||--Bug#6882986
691 ' po_req_distributions_all prd ,' ||
692 ' po_requisition_lines_all prl ,' ||
693 ' po_requisition_headers_all prh ' ||
694 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
695 ' and prh.requisition_header_id = prl.requisition_header_id' ||
696 ' and prl.requisition_line_id = prd.requisition_line_id' ||
697 ' and prl.source_type_code = ''VENDOR''' ||
698 ' and pd.req_distribution_id = prd.distribution_id' ||
699 ' and pd.po_header_id=ph.po_header_id' ||
700 ' AND pl.po_header_id = ph.po_header_id' ||
701 ' AND pd.po_line_id=pl.po_line_id'||
702 ' AND pll.po_line_id = pl.po_line_id' ||
703 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
704 ' AND pll.line_location_id = pd.line_location_id' ||
705 ' AND pll.line_location_id = rt.po_line_location_id' ||--Bug#6882986
706 ' AND ph.po_header_id = rt.po_header_id' ||--Bug#6882986
707 ' AND pl.po_line_id = rt.po_line_id' ||--Bug#6882986
708 ' AND mmtt.rcv_transaction_id = rt.transaction_id' ||--Bug#6882986
709 ' and prh.segment1 = '||''''||l_req_number||''''||
710 ' and prl.line_num='||l_line_num||
711 ' and prh.org_id = '||l_operating_id;
712
713
714 p_sql(27) := ' SELECT DISTINCT ood.* ' ||
715 ' from org_organization_definitions ood, ' ||
716 ' financials_system_params_all fsp, ' ||
717 ' po_line_locations_all pll , ' ||
718 ' po_lines_all pl , ' ||
719 ' po_headers_all ph , ' ||
720 ' po_distributions_all pd,' ||
721 ' po_req_distributions_all prd ,' ||
722 ' po_requisition_lines_all prl ,' ||
723 ' po_requisition_headers_all prh ' ||
724 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
725 ' and prh.requisition_header_id = prl.requisition_header_id' ||
726 ' and prl.requisition_line_id = prd.requisition_line_id' ||
727 ' and prl.source_type_code = ''VENDOR''' ||
728 ' and pd.req_distribution_id = prd.distribution_id' ||
729 ' and pd.po_header_id=ph.po_header_id' ||
730 ' AND pl.po_header_id = ph.po_header_id' ||
731 ' AND pll.po_line_id = pl.po_line_id ' ||
732 ' AND pd.po_line_id=pl.po_line_id'||
733 ' AND pll.line_location_id = pd.line_location_id' ||
734 ' AND fsp.org_id = ph.org_id ' ||
735 ' AND ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id) ' ||
736 ' and prh.segment1 = '||''''||l_req_number||''''||
737 ' and prl.line_num='||l_line_num||
738 ' and prh.org_id = '||l_operating_id;
739
740
741 p_sql(28) := ' SELECT DISTINCT mp.* ' ||
742 ' from mtl_parameters mp ,' ||
743 ' financials_system_params_all fsp,' ||
744 ' po_requisition_lines_all prl ,' ||
745 ' po_requisition_headers_all prh ' ||
746 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
747 ' and prh.requisition_header_id = prl.requisition_header_id' ||
748 ' and prl.source_type_code = ''VENDOR''' ||
749 ' AND fsp.org_id = prh.org_id ' ||
750 ' AND mp.organization_id in (fsp.inventory_organization_id , prl.destination_organization_id) ' ||
751 ' and prh.segment1 = '||''''||l_req_number||''''||
752 ' and prl.line_num='||l_line_num||
753 ' and prh.org_id = '||l_operating_id;
754
755
756 p_sql(29) := ' SELECT DISTINCT rp.* ' ||
757 ' from rcv_parameters rp , ' ||
758 ' financials_system_params_all fsp, ' ||
759 ' po_requisition_lines_all prl ,' ||
760 ' po_requisition_headers_all prh ' ||
761 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
762 ' and prh.requisition_header_id = prl.requisition_header_id' ||
763 ' and prl.source_type_code = ''VENDOR''' ||
764 ' AND fsp.org_id = prh.org_id ' ||
765 ' AND (rp.organization_id = fsp.inventory_organization_id ' ||
766 ' OR rp.organization_id = prl.destination_organization_id) ' ||
767 ' and prh.segment1 = '||''''||l_req_number||''''||
768 ' and prl.line_num='||l_line_num||
769 ' and prh.org_id = '||l_operating_id;
770
771
772 p_sql(30) := ' SELECT DISTINCT psp.* ' ||
773 ' from po_system_parameters_all psp, ' ||
774 ' po_requisition_lines_all prl ,' ||
775 ' po_requisition_headers_all prh ' ||
776 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
777 ' and prh.requisition_header_id = prl.requisition_header_id' ||
778 ' and prl.source_type_code = ''VENDOR''' ||
779 ' and psp.org_id = prh.org_id ' ||
780 ' and prh.segment1 = '||''''||l_req_number||''''||
781 ' and prl.line_num='||l_line_num||
782 ' and prh.org_id = '||l_operating_id;
783
784
785 p_sql(31) := ' SELECT DISTINCT fsp.* ' ||
786 ' from financials_system_params_all fsp, ' ||
790 ' and prh.requisition_header_id = prl.requisition_header_id' ||
787 ' po_requisition_lines_all prl ,' ||
788 ' po_requisition_headers_all prh ' ||
789 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
791 ' and prl.source_type_code = ''VENDOR''' ||
792 ' and fsp.org_id = prh.org_id ' ||
793 ' and prh.segment1 = '||''''||l_req_number||''''||
794 ' and prl.line_num='||l_line_num||
795 ' and prh.org_id = '||l_operating_id;
796
797 p_sql(32) := ' SELECT distinct msn.* ' ||
798 ' from mtl_serial_numbers msn , ' ||
799 ' mtl_unit_transactions mut , ' ||
800 ' mtl_material_transactions mmt, ' ||
801 ' po_line_locations_all pll , ' ||
802 ' po_lines_all pl , ' ||
803 ' po_headers_all ph , ' ||
804 ' po_distributions_all pd,' ||
805 ' po_req_distributions_all prd ,' ||
806 ' po_requisition_lines_all prl ,' ||
807 ' po_requisition_headers_all prh,' ||
808 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
809 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
810 ' and prh.requisition_header_id = prl.requisition_header_id' ||
811 ' and prl.requisition_line_id = prd.requisition_line_id' ||
812 ' and prl.source_type_code = ''VENDOR''' ||
813 ' and pd.req_distribution_id = prd.distribution_id' ||
814 ' and pd.po_header_id=ph.po_header_id' ||
815 ' AND pl.po_header_id = ph.po_header_id' ||
816 ' AND pll.po_line_id = pl.po_line_id ' ||
817 ' AND pll.line_location_id = pd.line_location_id' ||
818 ' and mmt.transaction_source_id = ph.po_header_id ' ||
819 ' AND mmt.transaction_source_type_id = 1 ' ||
820 ' AND mut.transaction_id = mmt.transaction_id ' ||
821 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
822 ' AND msn.current_organization_id = mut.organization_id ' ||
823 ' AND msn.serial_number = mut.serial_number ' ||
824 ' and rsh.shipment_header_id = rt.shipment_header_id '||
825 ' and rt.transaction_id = mmt.rcv_transaction_id '||
826 ' and rt.po_line_location_id=pll.line_location_id'||
827 ' and prh.segment1 = '||''''||l_req_number||''''||
828 ' and prl.line_num='||l_line_num||
829 ' and prh.org_id = '||l_operating_id ||
830 ' UNION ALL ' ||
831 ' SELECT distinct msn.* ' ||
832 ' from mtl_serial_numbers msn , ' ||
833 ' mtl_unit_transactions mut , ' ||
834 ' mtl_material_transactions mmt, ' ||
835 ' mtl_transaction_lot_numbers mtln, ' ||
836 ' po_line_locations_all pll , ' ||
837 ' po_lines_all pl , ' ||
838 ' po_headers_all ph , ' ||
839 ' po_distributions_all pd,' ||
840 ' po_req_distributions_all prd ,' ||
841 ' po_requisition_lines_all prl ,' ||
842 ' po_requisition_headers_all prh, ' ||
843 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
844 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
845 ' and prh.requisition_header_id = prl.requisition_header_id' ||
846 ' and prl.requisition_line_id = prd.requisition_line_id' ||
847 ' and prl.source_type_code = ''VENDOR''' ||
848 ' and pd.req_distribution_id = prd.distribution_id' ||
849 ' and pd.po_header_id=ph.po_header_id' ||
850 ' AND pl.po_header_id = ph.po_header_id' ||
851 ' AND pll.po_line_id = pl.po_line_id ' ||
852 ' AND pll.line_location_id = pd.line_location_id' ||
853 ' and mmt.transaction_source_id = ph.po_header_id ' ||
854 ' AND mmt.transaction_source_type_id = 1 ' ||
855 ' AND mtln.transaction_id = mmt.transaction_id ' ||
856 ' AND mut.transaction_id = mtln.serial_transaction_id '||
857 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
858 ' AND msn.current_organization_id = mut.organization_id ' ||
859 ' AND msn.serial_number = mut.serial_number ' ||
860 ' and rsh.shipment_header_id = rt.shipment_header_id '||
861 ' and rt.po_line_location_id=pll.line_location_id'||
862 ' and rt.transaction_id = mmt.rcv_transaction_id '||
863 ' and prh.segment1 = '||''''||l_req_number||''''||
864 ' and prl.line_num='||l_line_num||
865 ' and prh.org_id = '||l_operating_id;
866
867 p_sql(33) := ' SELECT DISTINCT msnt.* ' ||
868 ' from mtl_serial_numbers_temp msnt , ' ||
869 ' mtl_material_transactions_temp mmtt, ' ||
870 ' po_line_locations_all pll , ' ||
871 ' po_lines_all pl , ' ||
872 ' po_headers_all ph , ' ||
873 ' po_distributions_all pd,' ||
874 ' po_req_distributions_all prd ,' ||
875 ' po_requisition_lines_all prl ,' ||
876 ' po_requisition_headers_all prh ' ||
877 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
878 ' and prh.requisition_header_id = prl.requisition_header_id' ||
879 ' and prl.requisition_line_id = prd.requisition_line_id' ||
880 ' and prl.source_type_code = ''VENDOR''' ||
881 ' and pd.req_distribution_id = prd.distribution_id' ||
882 ' and pd.po_header_id=ph.po_header_id' ||
883 ' AND pl.po_header_id = ph.po_header_id' ||
884 ' AND pd.po_line_id=pl.po_line_id'||
885 ' AND pll.po_line_id = pl.po_line_id ' ||
886 ' AND pll.line_location_id = pd.line_location_id' ||
887 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
888 ' AND msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
889 ' and prh.segment1 = '||''''||l_req_number||''''||
890 ' and prl.line_num='||l_line_num||
891 ' and prh.org_id = '||l_operating_id||
892 ' UNION ALL ' ||
893 ' SELECT DISTINCT msnt.* ' ||
897 ' po_line_locations_all pll , ' ||
894 ' from mtl_serial_numbers_temp msnt, ' ||
895 ' mtl_material_transactions_temp mmtt, ' ||
896 ' mtl_transaction_lots_temp mtln, ' ||
898 ' po_lines_all pl , ' ||
899 ' po_headers_all ph , ' ||
900 ' po_distributions_all pd,' ||
901 ' po_req_distributions_all prd ,' ||
902 ' po_requisition_lines_all prl ,' ||
903 ' po_requisition_headers_all prh ' ||
904 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
905 ' and prh.requisition_header_id = prl.requisition_header_id' ||
906 ' and prl.requisition_line_id = prd.requisition_line_id' ||
907 ' and prl.source_type_code = ''VENDOR''' ||
908 ' and pd.req_distribution_id = prd.distribution_id' ||
909 ' and pd.po_header_id=ph.po_header_id' ||
910 ' AND pl.po_header_id = ph.po_header_id' ||
911 ' AND pd.po_line_id=pl.po_line_id'||
912 ' AND pll.po_line_id = pl.po_line_id ' ||
913 ' AND pll.line_location_id = pd.line_location_id' ||
914 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
915 ' AND mtln.transaction_temp_id = mmtt.transaction_temp_id ' ||
916 ' AND msnt.transaction_temp_id = mtln.serial_transaction_temp_id ' ||
917 ' and prh.segment1 = '||''''||l_req_number||''''||
918 ' and prl.line_num='||l_line_num||
919 ' and prh.org_id = '||l_operating_id;
920
921 p_sql(34) := ' SELECT DISTINCT msni.* ' ||
922 ' from mtl_serial_numbers_interface msni , ' ||
923 ' rcv_transactions_interface rti ' ||
924 ' WHERE exists ' ||
925 ' (SELECT 1 ' ||
926 ' from po_line_locations_all pll , ' ||
927 ' po_lines_all pl , ' ||
928 ' po_headers_all ph , ' ||
929 ' po_distributions_all pd,' ||
930 ' po_req_distributions_all prd ,' ||
931 ' po_requisition_lines_all prl ,' ||
932 ' po_requisition_headers_all prh ' ||
933 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
934 ' and prh.requisition_header_id = prl.requisition_header_id' ||
935 ' and prl.requisition_line_id = prd.requisition_line_id' ||
936 ' and prl.source_type_code = ''VENDOR''' ||
937 ' and pd.req_distribution_id = prd.distribution_id' ||
938 ' and pd.po_header_id=ph.po_header_id' ||
939 ' AND pl.po_header_id = ph.po_header_id' ||
940 ' AND pd.po_line_id=pl.po_line_id'||
941 ' AND pll.po_line_id = pl.po_line_id ' ||
942 ' AND pll.line_location_id = pd.line_location_id' ||
943 ' AND rti.po_header_id = ph.po_header_id' ||
944 ' and prh.segment1 = '||''''||l_req_number||''''||
945 ' and prl.line_num='||l_line_num||
946 ' and prh.org_id = '||l_operating_id ||
947 ' ) ' ||
948 ' AND msni.product_transaction_id = rti.interface_transaction_id ';
949
950
951 p_sql(35) := ' SELECT distinct mut.* ' ||
952 ' from mtl_unit_transactions mut , ' ||
953 ' mtl_material_transactions mmt, ' ||
954 ' po_line_locations_all pll , ' ||
955 ' po_lines_all pl , ' ||
956 ' po_headers_all ph , ' ||
957 ' po_distributions_all pd,' ||
958 ' po_req_distributions_all prd ,' ||
959 ' po_requisition_lines_all prl ,' ||
960 ' po_requisition_headers_all prh, ' ||
961 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
962 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
963 ' and prh.requisition_header_id = prl.requisition_header_id' ||
964 ' and prl.requisition_line_id = prd.requisition_line_id' ||
965 ' and prl.source_type_code = ''VENDOR''' ||
966 ' and pd.req_distribution_id = prd.distribution_id' ||
967 ' and pd.po_header_id=ph.po_header_id' ||
968 ' AND pl.po_header_id = ph.po_header_id' ||
969 ' AND pll.po_line_id = pl.po_line_id ' ||
970 ' AND pll.line_location_id = pd.line_location_id' ||
971 ' and mmt.transaction_source_id = ph.po_header_id ' ||
972 ' AND mmt.transaction_source_type_id = 1 ' ||
973 ' AND mut.transaction_id = mmt.transaction_id ' ||
974 ' and rsh.shipment_header_id = rt.shipment_header_id '||
975 ' and rt.po_line_location_id=pll.line_location_id'||
976 ' and rt.transaction_id = mmt.rcv_transaction_id '||
977 ' and prh.segment1 = '||''''||l_req_number||''''||
978 ' and prl.line_num='||l_line_num||
979 ' and prh.org_id = '||l_operating_id||
980 ' UNION ALL ' ||
981 ' SELECT distinct mut.* ' ||
982 ' from mtl_unit_transactions mut , ' ||
983 ' mtl_material_transactions mmt , ' ||
984 ' mtl_transaction_lot_numbers mtln, ' ||
985 ' po_line_locations_all pll , ' ||
986 ' po_lines_all pl , ' ||
987 ' po_headers_all ph , ' ||
988 ' po_distributions_all pd,' ||
989 ' po_req_distributions_all prd ,' ||
990 ' po_requisition_lines_all prl ,' ||
991 ' po_requisition_headers_all prh, ' ||
992 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
993 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
994 ' and prh.requisition_header_id = prl.requisition_header_id' ||
995 ' and prl.requisition_line_id = prd.requisition_line_id' ||
996 ' and prl.source_type_code = ''VENDOR''' ||
997 ' and pd.req_distribution_id = prd.distribution_id' ||
998 ' and pd.po_header_id=ph.po_header_id' ||
999 ' AND pl.po_header_id = ph.po_header_id' ||
1000 ' AND pll.po_line_id = pl.po_line_id ' ||
1001 ' AND pll.line_location_id = pd.line_location_id' ||
1002 ' and mmt.transaction_source_id = ph.po_header_id ' ||
1003 ' AND mmt.transaction_source_type_id = 1 ' ||
1007 ' and rt.po_line_location_id=pll.line_location_id'||
1004 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1005 ' AND mut.transaction_id = mtln.serial_transaction_id '||
1006 ' and rsh.shipment_header_id = rt.shipment_header_id '||
1008 ' and rt.transaction_id = mmt.rcv_transaction_id '||
1009 ' and prh.segment1 = '||''''||l_req_number||''''||
1010 ' and prl.line_num='||l_line_num||
1011 ' and prh.org_id = '||l_operating_id;
1012
1013 p_sql(36) := ' SELECT distinct rss.* ' ||
1014 ' from rcv_serials_supply rss , ' ||
1015 ' rcv_shipment_lines rsl , ' ||
1016 ' po_line_locations_all pll , ' ||
1017 ' po_lines_all pl , ' ||
1018 ' po_headers_all ph , ' ||
1019 ' po_distributions_all pd,' ||
1020 ' po_req_distributions_all prd ,' ||
1021 ' po_requisition_lines_all prl ,' ||
1022 ' po_requisition_headers_all prh, ' ||
1023 ' rcv_shipment_headers rsh ' ||
1024 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1025 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1026 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1027 ' and prl.source_type_code = ''VENDOR''' ||
1028 ' and pd.req_distribution_id = prd.distribution_id' ||
1029 ' and pd.po_header_id=ph.po_header_id' ||
1030 ' AND pl.po_header_id = ph.po_header_id' ||
1031 ' AND pll.po_line_id = pl.po_line_id ' ||
1032 ' AND pll.line_location_id = pd.line_location_id' ||
1033 ' and rsl.po_header_id = ph.po_header_id ' ||
1034 ' AND rss.shipment_line_id = rsl.shipment_line_id ' ||
1035 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
1036 ' and prh.segment1 = '||''''||l_req_number||''''||
1037 ' and prl.line_num='||l_line_num||
1038 ' and prh.org_id = '||l_operating_id;
1039
1040 p_sql(37) := ' SELECT distinct rst.* ' ||
1041 ' from rcv_serial_transactions rst , ' ||
1042 ' rcv_shipment_lines rsl , ' ||
1043 ' po_line_locations_all pll , ' ||
1044 ' po_lines_all pl , ' ||
1045 ' po_headers_all ph , ' ||
1046 ' po_distributions_all pd,' ||
1047 ' po_req_distributions_all prd ,' ||
1048 ' po_requisition_lines_all prl ,' ||
1049 ' po_requisition_headers_all prh, ' ||
1050 ' rcv_shipment_headers rsh ' ||
1051 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1052 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1053 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1054 ' and prl.source_type_code = ''VENDOR''' ||
1055 ' and pd.req_distribution_id = prd.distribution_id' ||
1056 ' and pd.po_header_id=ph.po_header_id' ||
1057 ' AND pl.po_header_id = ph.po_header_id' ||
1058 ' AND pll.po_line_id = pl.po_line_id ' ||
1059 ' AND pll.line_location_id = pd.line_location_id' ||
1060 ' and rsl.po_header_id = ph.po_header_id ' ||
1061 ' AND rst.shipment_line_id = rsl.shipment_line_id ' ||
1062 ' and rsl.po_line_location_id=pll.line_location_id'||
1063 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
1064 ' and prh.segment1 = '||''''||l_req_number||''''||
1065 ' and prl.line_num='||l_line_num||
1066 ' and prh.org_id = '||l_operating_id;
1067
1068 p_sql(38) := ' SELECT distinct rsi.* ' ||
1069 ' from rcv_serials_interface rsi , ' ||
1070 ' rcv_transactions_interface rti ' ||
1071 ' WHERE exists ' ||
1072 ' (SELECT 1 ' ||
1073 ' from po_line_locations_all pll , ' ||
1074 ' po_lines_all pl , ' ||
1075 ' po_headers_all ph , ' ||
1076 ' po_distributions_all pd,' ||
1077 ' po_req_distributions_all prd ,' ||
1078 ' po_requisition_lines_all prl ,' ||
1079 ' po_requisition_headers_all prh, ' ||
1080 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
1081 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1082 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1083 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1084 ' and prl.source_type_code = ''VENDOR''' ||
1085 ' and pd.req_distribution_id = prd.distribution_id' ||
1086 ' and pd.po_header_id=ph.po_header_id' ||
1087 ' AND pl.po_header_id = ph.po_header_id' ||
1088 ' AND pll.po_line_id = pl.po_line_id ' ||
1089 ' AND pll.line_location_id = pd.line_location_id' ||
1090 ' and rti.po_header_id = ph.po_header_id ' ||
1091 ' and rsl.shipment_header_id = rsh.shipment_header_id '||
1092 ' and rsl.po_line_location_id = pll.line_location_id ' ||
1093 ' and prh.segment1 = '||''''||l_req_number||''''||
1094 ' and prl.line_num='||l_line_num||
1095 ' and prh.org_id = '||l_operating_id||
1096 ' ) ' ||
1097 ' AND rsi.interface_transaction_id =
1098 rti.interface_transaction_id ';
1099
1100 p_sql(39) := ' SELECT distinct mln.* ' ||
1101 ' from mtl_lot_numbers mln , ' ||
1102 ' mtl_transaction_lot_numbers mtln ,' ||
1103 ' mtl_material_transactions mmt, ' ||
1104 ' po_line_locations_all pll , ' ||
1105 ' po_lines_all pl , ' ||
1106 ' po_headers_all ph , ' ||
1107 ' po_distributions_all pd,' ||
1108 ' po_req_distributions_all prd ,' ||
1109 ' po_requisition_lines_all prl ,' ||
1110 ' po_requisition_headers_all prh, ' ||
1111 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
1112 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1113 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1114 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1118 ' AND pl.po_header_id = ph.po_header_id' ||
1115 ' and prl.source_type_code = ''VENDOR''' ||
1116 ' and pd.req_distribution_id = prd.distribution_id' ||
1117 ' and pd.po_header_id=ph.po_header_id' ||
1119 ' AND pll.po_line_id = pl.po_line_id ' ||
1120 ' AND pll.line_location_id = pd.line_location_id' ||
1121 ' and mmt.transaction_source_id = ph.po_header_id ' ||
1122 ' AND mmt.transaction_source_type_id = 1 ' ||
1123 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1124 ' AND mtln.lot_number = mln.lot_number ' ||
1125 ' AND mtln.inventory_item_id = mln.inventory_item_id ' ||
1126 ' AND mtln.organization_id = mln.organization_id ' ||
1127 ' and rsh.shipment_header_id = rt.shipment_header_id '||
1128 ' and rt.po_line_location_id=pll.line_location_id'||
1129 ' and rt.transaction_id = mmt.rcv_transaction_id '||
1130 ' and prh.segment1 = '||''''||l_req_number||''''||
1131 ' and prl.line_num='||l_line_num||
1132 ' and prh.org_id = '||l_operating_id;
1133
1134
1135 p_sql(40) := ' SELECT distinct mtln.* ' ||
1136 ' from mtl_transaction_lot_numbers mtln , ' ||
1137 ' mtl_material_transactions mmt , ' ||
1138 ' po_line_locations_all pll , ' ||
1139 ' po_lines_all pl , ' ||
1140 ' po_headers_all ph , ' ||
1141 ' po_distributions_all pd,' ||
1142 ' po_req_distributions_all prd ,' ||
1143 ' po_requisition_lines_all prl ,' ||
1144 ' po_requisition_headers_all prh, ' ||
1145 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
1146 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1147 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1148 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1149 ' and prl.source_type_code = ''VENDOR''' ||
1150 ' and pd.req_distribution_id = prd.distribution_id' ||
1151 ' and pd.po_header_id=ph.po_header_id' ||
1152 ' AND pl.po_header_id = ph.po_header_id' ||
1153 ' AND pll.po_line_id = pl.po_line_id ' ||
1154 ' AND pll.line_location_id = pd.line_location_id' ||
1155 ' and mmt.transaction_source_id = ph.po_header_id ' ||
1156 ' AND mmt.transaction_source_type_id = 1 ' ||
1157 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1158 ' and rsh.shipment_header_id = rt.shipment_header_id '||
1159 ' and rt.po_line_location_id=pll.line_location_id'||
1160 ' and rt.transaction_id = mmt.rcv_transaction_id '||
1161 ' and prh.segment1 = '||''''||l_req_number||''''||
1162 ' and prl.line_num='||l_line_num||
1163 ' and prh.org_id = '||l_operating_id;
1164
1165
1166 p_sql(41) := ' SELECT distinct mtli.* ' ||
1167 ' from mtl_transaction_lots_interface mtli , ' ||
1168 ' rcv_transactions_interface rti ' ||
1169 ' WHERE exists ' ||
1170 ' (SELECT 1 ' ||
1171 ' from po_line_locations_all pll , ' ||
1172 ' po_lines_all pl , ' ||
1173 ' po_headers_all ph , ' ||
1174 ' po_distributions_all pd,' ||
1175 ' po_req_distributions_all prd ,' ||
1176 ' po_requisition_lines_all prl ,' ||
1177 ' po_requisition_headers_all prh ' ||
1178 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1179 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1180 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1181 ' and prl.source_type_code = ''VENDOR''' ||
1182 ' and pd.req_distribution_id = prd.distribution_id' ||
1183 ' and pd.po_header_id=ph.po_header_id' ||
1184 ' AND pl.po_header_id = ph.po_header_id' ||
1185 ' AND pll.po_line_id = pl.po_line_id ' ||
1186 ' AND pll.line_location_id = pd.line_location_id' ||
1187 ' and rti.po_header_id = ph.po_header_id ' ||
1188 ' and prh.segment1 = '||''''||l_req_number||''''||
1189 ' and prl.line_num='||l_line_num||
1190 ' and prh.org_id = '||l_operating_id ||
1191 ' ) ' ||
1192 ' AND mtli.product_transaction_id = RTI.interface_transaction_id ';
1193
1194
1195 p_sql(42) := ' SELECT distinct mtlt.* ' ||
1196 ' from mtl_transaction_lots_temp mtlt , ' ||
1197 ' mtl_material_transactions_temp mmtt , ' ||
1198 ' po_line_locations_all pll , ' ||
1199 ' po_lines_all pl , ' ||
1200 ' po_headers_all ph , ' ||
1201 ' po_distributions_all pd,' ||
1202 ' po_req_distributions_all prd ,' ||
1203 ' po_requisition_lines_all prl ,' ||
1204 ' po_requisition_headers_all prh, ' ||
1205 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
1206 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1207 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1208 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1209 ' and prl.source_type_code = ''VENDOR''' ||
1210 ' and pd.req_distribution_id = prd.distribution_id' ||
1211 ' and pd.po_header_id=ph.po_header_id' ||
1212 ' AND pl.po_header_id = ph.po_header_id' ||
1213 ' AND pll.po_line_id = pl.po_line_id ' ||
1214 ' AND pll.line_location_id = pd.line_location_id' ||
1215 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
1216 ' AND mmtt.transaction_source_type_id = 1 ' ||
1217 ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ' ||
1218 ' and rsh.shipment_header_id = rt.shipment_header_id '||
1219 ' and rt.po_line_location_id=pll.line_location_id'||
1220 ' and rt.transaction_id = mmtt.rcv_transaction_id '||
1221 ' and prh.segment1 = '||''''||l_req_number||''''||
1222 ' and prl.line_num='||l_line_num||
1223 ' and prh.org_id = '||l_operating_id;
1224
1228 ' po_line_locations_all pll , ' ||
1225 p_sql(43) := ' SELECT distinct rls.* ' ||
1226 ' from rcv_lots_supply rls , ' ||
1227 ' rcv_shipment_lines rsl, ' ||
1229 ' po_lines_all pl , ' ||
1230 ' po_headers_all ph , ' ||
1231 ' po_distributions_all pd,' ||
1232 ' po_req_distributions_all prd ,' ||
1233 ' po_requisition_lines_all prl ,' ||
1234 ' po_requisition_headers_all prh, ' ||
1235 ' rcv_shipment_headers rsh ' ||
1236 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1237 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1238 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1239 ' and prl.source_type_code = ''VENDOR''' ||
1240 ' and pd.req_distribution_id = prd.distribution_id' ||
1241 ' and pd.po_header_id=ph.po_header_id' ||
1242 ' AND pl.po_header_id = ph.po_header_id' ||
1243 ' AND pll.po_line_id = pl.po_line_id ' ||
1244 ' AND pll.line_location_id = pd.line_location_id' ||
1245 ' and rsl.shipment_line_id = rls.shipment_line_id ' ||
1246 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
1247 ' and rsl.po_line_location_id=pll.line_location_id' ||
1248 ' AND rsl.po_header_id = ph.po_header_id ' ||
1249 ' and prh.segment1 = '||''''||l_req_number||''''||
1250 ' and prl.line_num='||l_line_num||
1251 ' and prh.org_id = '||l_operating_id;
1252
1253
1254 p_sql(44) := ' SELECT distinct rlt.* ' ||
1255 ' from rcv_lot_transactions rlt , ' ||
1256 ' rcv_shipment_lines rsl , ' ||
1257 ' po_line_locations_all pll , ' ||
1258 ' po_lines_all pl , ' ||
1259 ' po_headers_all ph , ' ||
1260 ' po_distributions_all pd,' ||
1261 ' po_req_distributions_all prd ,' ||
1262 ' po_requisition_lines_all prl ,' ||
1263 ' po_requisition_headers_all prh, ' ||
1264 ' rcv_shipment_headers rsh ' ||
1265 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1266 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1267 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1268 ' and prl.source_type_code = ''VENDOR''' ||
1269 ' and pd.req_distribution_id = prd.distribution_id' ||
1270 ' and pd.po_header_id=ph.po_header_id' ||
1271 ' AND pl.po_header_id = ph.po_header_id' ||
1272 ' AND pll.po_line_id = pl.po_line_id ' ||
1273 ' AND pll.line_location_id = pd.line_location_id' ||
1274 ' and rsl.po_header_id = ph.po_header_id ' ||
1275 ' AND rsl.shipment_line_id = rlt.shipment_line_id ' ||
1276 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
1277 ' and rsl.po_line_location_id=pll.line_location_id' ||
1278 ' and prh.segment1 = '||''''||l_req_number||''''||
1279 ' and prl.line_num='||l_line_num||
1280 ' and prh.org_id = '||l_operating_id;
1281
1282 p_sql(45) := ' SELECT distinct rli.* ' ||
1283 ' from rcv_lots_interface rli , ' ||
1284 ' rcv_transactions_interface rti ' ||
1285 ' WHERE rti.interface_transaction_id =
1286 rli.interface_transaction_id ' ||
1287 ' AND exists ' ||
1288 ' (SELECT 1 ' ||
1289 ' from po_line_locations_all pll , ' ||
1290 ' po_lines_all pl , ' ||
1291 ' po_headers_all ph , ' ||
1292 ' po_distributions_all pd,' ||
1293 ' po_req_distributions_all prd ,' ||
1294 ' po_requisition_lines_all prl ,' ||
1295 ' po_requisition_headers_all prh ' ||
1296 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1297 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1298 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1299 ' and prl.source_type_code = ''VENDOR''' ||
1300 ' and pd.req_distribution_id = prd.distribution_id' ||
1301 ' and pd.po_header_id=ph.po_header_id' ||
1302 ' AND pl.po_header_id = ph.po_header_id' ||
1303 ' AND pll.po_line_id = pl.po_line_id ' ||
1304 ' AND pll.line_location_id = pd.line_location_id' ||
1305 ' and rti.po_header_id = ph.po_header_id ' ||
1306 ' and prh.segment1 = '||''''||l_req_number||''''||
1307 ' and prl.line_num='||l_line_num||
1308 ' and prh.org_id = '||l_operating_id||
1309 ' ) ';
1310
1311
1312 RETURN;
1313 END;
1314
1315 -------------------------------------------------------
1316 -- Package to Build sqls for Receipt and OU combination
1317 -------------------------------------------------------
1318 PROCEDURE build_req_rcv_sql(p_receipt_num IN VARCHAR2,p_org_id IN NUMBER,p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
1319
1320 -- Initialize Local Variables.
1321 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
1322 l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
1323
1324 BEGIN
1325
1326 p_sql(1) := ' select distinct prh.*' ||
1327 ' from po_requisition_headers_all prh,' ||
1328 ' po_requisition_lines_all prl,' ||
1329 ' po_req_distributions_all prd,'||
1330 ' po_line_locations_all pll,po_distributions_all pd,'||
1331 ' rcv_shipment_lines rsl,rcv_shipment_headers rsh'||
1332 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1333 ' and prl.requisition_header_id = prh.requisition_header_id' ||
1334 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1335 ' and prl.source_type_code = ''VENDOR''' ||
1336 ' and pd.req_distribution_id = prd.distribution_id' ||
1337 ' and pll.line_location_id = pd.line_location_id'||
1341 ' AND rsh.ship_to_org_id ='||l_org_id;
1338 ' and rsl.po_line_location_id = pll.line_location_id'||
1339 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
1340 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1342
1343 p_sql(2) := ' select distinct prl.*' ||
1344 ' from po_requisition_headers_all prh,' ||
1345 ' po_requisition_lines_all prl,' ||
1346 ' po_req_distributions_all prd,'||
1347 ' po_line_locations_all pll,po_distributions_all pd,'||
1348 ' rcv_shipment_lines rsl,rcv_shipment_headers rsh'||
1349 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1350 ' and prl.requisition_header_id = prh.requisition_header_id' ||
1351 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1352 ' and prl.source_type_code = ''VENDOR''' ||
1353 ' and pd.req_distribution_id = prd.distribution_id' ||
1354 ' and pll.line_location_id = pd.line_location_id'||
1355 ' and rsl.po_line_location_id = pll.line_location_id'||
1356 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
1357 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1358 ' AND rsh.ship_to_org_id ='||l_org_id;
1359
1360 p_sql(3) := ' select distinct prd.*' ||
1361 ' from po_requisition_headers_all prh,' ||
1362 ' po_requisition_lines_all prl,' ||
1363 ' po_req_distributions_all prd,'||
1364 ' po_line_locations_all pll,po_distributions_all pd,'||
1365 ' rcv_shipment_lines rsl,rcv_shipment_headers rsh'||
1366 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1367 ' and prl.requisition_header_id = prh.requisition_header_id' ||
1368 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1369 ' and prl.source_type_code = ''VENDOR''' ||
1370 ' and pd.req_distribution_id = prd.distribution_id' ||
1371 ' and pll.line_location_id = pd.line_location_id'||
1372 ' and rsl.po_line_location_id = pll.line_location_id'||
1373 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
1374 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1375 ' AND rsh.ship_to_org_id ='||l_org_id;
1376
1377
1378 p_sql(4) := ' SELECT distinct ph.* ' ||' from po_headers_all ph,rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||
1379 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id' ||
1380 ' and rsl.po_header_id=ph.po_header_id' ||
1381 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1382 ' AND rsh.ship_to_org_id ='||l_org_id;
1383
1384 p_sql(5) := ' SELECT distinct pl.* ' ||' from po_lines_all pl,rcv_shipment_lines rsl, ' ||
1385 ' rcv_shipment_headers rsh ' ||' WHERE pl.po_line_id=rsl.po_line_id' ||
1386 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||' AND rsh.receipt_num='||''''||l_receipt_num||'''' ||
1387 ' AND rsh.ship_to_org_id ='||l_org_id;
1388
1389 p_sql(6) := ' SELECT distinct pll.* ' ||' from po_line_locations_all pll , ' ||
1390 ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh' ||
1391 ' WHERE rsl.po_line_location_id= pll.line_location_id' ||
1392 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1393 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1394 ' AND rsh.ship_to_org_id ='||l_org_id;
1395
1396 p_sql(7) := ' SELECT distinct pd.* ' ||' from po_line_locations_all pll , ' ||
1397 ' po_distributions_all pd,' ||' rcv_shipment_lines rsl, ' ||
1398 ' rcv_shipment_headers rsh ' ||' WHERE pll.line_location_id = pd.line_location_id' ||
1399 ' and rsl.po_line_location_id=pll.line_location_id' ||
1400 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1401 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||' AND rsh.ship_to_org_id ='||l_org_id;
1402
1403 p_sql(8) := ' SELECT distinct gcc.* ' ||' from gl_code_combinations gcc , ' ||
1404 ' po_line_locations_all pll , ' ||' po_distributions_all pd ,' ||
1405 ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh' ||
1406 ' WHERE gcc.summary_flag = ''N'' ' ||' AND gcc.template_id is null ' ||
1407 ' AND pll.line_location_id = pd.line_location_id' ||
1408 ' AND pll.line_location_id = rsl.po_line_location_id ' ||
1409 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1410 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1411 ' AND rsh.ship_to_org_id ='||l_org_id ||
1412 ' and gcc.code_combination_id in (pd.accrual_account_id '||
1413 ', pd.budget_account_id , pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id) ';
1414
1415 p_sql(9) := ' SELECT distinct rrsl.* ' ||' from rcv_receiving_sub_ledger rrsl , ' ||
1416 ' rcv_transactions rt , ' ||' rcv_shipment_headers rsh ' ||
1417 ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||' AND rsh.ship_to_org_id ='||l_org_id ||
1418 ' AND rt.shipment_header_id = rsh.shipment_header_id ' ||
1419 ' AND rrsl.rcv_transaction_id = rt.transaction_id ';
1420
1421 /*p_sql(10) := ' SELECT distinct id.* ' ||' from ap_invoice_distributions_all id , ' ||
1422 ' po_line_locations_all pll , ' ||' po_distributions_all pd ,' ||
1423 ' rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||
1424 ' WHERE pll.line_location_id = pd.line_location_id' ||
1425 ' and pll.line_location_id = rsl.po_line_location_id' ||
1426 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1427 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1428 ' AND rsh.ship_to_org_id ='||l_org_id ||
1429 ' AND id.po_distribution_id = pd.po_distribution_id ';*/
1430
1434 ' WHERE pll.line_location_id = rt.po_line_location_id' ||
1431 p_sql(10) := ' SELECT distinct id.* ' ||' from ap_invoice_lines_all id , ' ||
1432 ' po_line_locations_all pll ,'||
1433 ' rcv_transactions rt,rcv_shipment_headers rsh ' ||
1435 ' and rsh.shipment_header_id=rt.shipment_header_id ' ||
1436 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1437 ' AND rsh.ship_to_org_id ='||l_org_id ||
1438 ' and id.rcv_transaction_id=rt.transaction_id';
1439
1440 p_sql(11) := ' SELECT distinct ai.* ' ||' from ap_invoices_all ai , ' ||
1441 ' ap_invoice_distributions_all id , ' ||' po_line_locations_all pll , ' ||
1442 ' po_distributions_all pd ,' ||' rcv_shipment_lines rsl, ' ||
1443 ' rcv_shipment_headers rsh' ||' WHERE pll.line_location_id = pd.line_location_id' ||
1444 ' and pll.line_location_id = rsl.po_line_location_id' ||
1445 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1446 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1447 ' AND rsh.ship_to_org_id ='||l_org_id ||
1448 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
1449 ' AND ai.invoice_id = id.invoice_id ';
1450
1451 p_sql(12) := ' SELECT distinct ili.* ' ||' from ap_invoice_lines_interface ili , ' ||
1452 ' po_headers_all ph,' ||' rcv_shipment_lines rsl, ' ||
1453 ' rcv_shipment_headers rsh ' ||' WHERE ph.po_header_id = rsl.po_header_id' ||
1454 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1455 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1456 ' AND rsh.ship_to_org_id ='||l_org_id ||
1457 ' AND ili.po_header_id = ph.po_header_id ';
1458
1459 p_sql(13) := ' SELECT distinct ihi.* ' ||' from ap_invoices_interface ihi , ' ||
1460 ' ap_invoice_lines_interface ili , ' ||' po_headers_all ph,' ||
1461 ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh ' ||
1462 ' WHERE ph.po_header_id = rsl.po_header_id' ||
1463 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1464 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1465 ' AND rsh.ship_to_org_id ='||l_org_id ||
1466 ' AND ili.po_header_id = ph.po_header_id ' ||
1467 ' AND ihi.invoice_id = ili.invoice_id ';
1468
1469 p_sql(14) := ' SELECT DISTINCT rsh.* ' ||' from rcv_shipment_lines rsl , ' ||
1470 ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||
1471 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1472 ' AND rsh.ship_to_org_id ='||l_org_id ||
1473 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
1474 ' ORDER BY rsh.shipment_header_id ';
1475
1476 p_sql(15) := ' SELECT DISTINCT rsl.* ' ||' from rcv_shipment_lines rsl , ' ||
1477 ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||
1478 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1479 ' AND rsh.ship_to_org_id ='||l_org_id ||
1480 ' AND rsl.shipment_header_id = rsh.shipment_header_id ';
1481
1482 p_sql(16) := ' SELECT distinct rt.* ' ||' from rcv_transactions rt , ' ||
1483 ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1484 ' AND rsh.ship_to_org_id ='||l_org_id ||
1485 ' AND rt.shipment_header_id=rsh.shipment_header_id ';
1486
1487 p_sql(17) := ' SELECT distinct ms.* ' ||' from mtl_supply ms , ' ||
1488 ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1489 ' AND rsh.ship_to_org_id ='||l_org_id ||' AND ms.shipment_header_id=rsh.shipment_header_id ';
1490
1491 p_sql(18) := ' SELECT distinct rs.* ' ||' from rcv_supply rs , ' ||
1492 ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1493 ' AND rsh.ship_to_org_id ='||l_org_id ||' AND rs.shipment_header_id=rsh.shipment_header_id ';
1494
1495 p_sql(19) := ' SELECT distinct rhi.* ' ||' from rcv_headers_interface rhi ' ||
1496 ' WHERE receipt_num= '||''''||l_receipt_num||'''' ||' OR exists ' ||
1497 ' (SELECT 1'||
1498 ' from rcv_shipment_lines rsl , ' ||
1499 ' rcv_shipment_headers rsh ' ||
1500 ' WHERE rsh.receipt_num = '||''''||l_receipt_num||'''' ||
1501 ' AND rsh.ship_to_org_id ='||l_org_id ||
1502 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
1503 ' AND rsh.shipment_header_id = rhi.receipt_header_id' ||
1504 ' ) ' ||
1505 ' OR exists ' ||
1506 ' (SELECT 2 ' ||
1507 ' from rcv_transactions_interface rti , ' ||
1508 ' rcv_shipment_headers rsh ' ||
1509 ' WHERE rsh.shipment_header_id =rti.shipment_header_id ' ||
1510 ' AND rsh.receipt_num = '||''''||l_receipt_num||'''' ||
1511 ' AND rsh.ship_to_org_id ='||l_org_id ||
1512 ' AND rhi.header_interface_id = rti.header_interface_id' ||
1513 ' ) ';
1514
1515 p_sql(20) := ' SELECT DISTINCT rti.* ' ||' from rcv_transactions_interface rti ' ||
1516 ' WHERE exists ' ||' (SELECT 1'||
1517 ' from rcv_shipment_headers rsh ' ||
1518 ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1519 ' AND rsh.ship_to_org_id ='||l_org_id ||
1520 ' AND rti.shipment_header_id = rsh.shipment_header_id' ||
1521 ' ) ';
1522
1523 p_sql(21) := 'SELECT DISTINCT pie.* '||' from po_interface_errors pie , '||
1524 ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||
1525 ' AND rsh.ship_to_org_id='||l_org_id||' AND ( '||
1529 ' OR EXISTS '||
1526 ' EXISTS (SELECT 1'||' from rcv_transactions_interface rti'||
1527 ' WHERE pie.interface_line_id = rti.interface_transaction_id'||
1528 ' AND rsh.shipment_header_id=rti.shipment_header_id )'||
1530 ' (SELECT 2 from rcv_headers_interface rhi'||
1531 ' WHERE pie.interface_header_id = rhi.header_interface_id '||
1532 ' AND rsh.shipment_header_id = rhi.header_interface_id))';
1533
1534 p_sql(22) := ' SELECT DISTINCT msi.* ' ||' from mtl_system_items msi , ' ||
1535 ' rcv_shipment_headers rsh,' ||' rcv_shipment_lines rsl ' ||
1536 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
1537 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1538 ' AND rsh.ship_to_org_id ='||l_org_id||'and msi.inventory_item_id = rsl.item_id ' ||
1539 ' AND msi.organization_id = rsl.to_organization_id ';
1540
1541 p_sql(23) := ' SELECT distinct mmt.* ' ||
1542 ' from mtl_material_transactions mmt ,rcv_transactions rt,rcv_shipment_headers rsh ,' ||
1543 ' po_headers_all ph ' ||' WHERE mmt.transaction_source_id = ph.po_header_id ' ||
1544 ' AND mmt.transaction_source_type_id = 1'||
1545 ' and rsh.shipment_header_id=rt.shipment_header_id ' ||
1546 ' and rt.transaction_id=mmt.rcv_transaction_id' ||
1547 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1548 ' AND rsh.ship_to_org_id ='||l_org_id;
1549
1550 p_sql(24) := ' SELECT distinct mtt.transaction_type_id , ' ||' mtt.transaction_type_name , ' ||
1551 ' mtt.transaction_source_type_id , ' ||
1552 ' mtt.transaction_action_id , ' ||
1553 ' mtt.user_defined_flag , ' ||
1554 ' mtt.disable_date ' ||
1555 ' from mtl_transaction_types mtt ' ||
1556 ' WHERE exists ' ||
1557 ' (SELECT 1'||
1558 ' from mtl_material_transactions mmt , ' ||
1559 ' rcv_transactions rt,' ||
1560 ' rcv_shipment_headers rsh ' ||
1561 ' WHERE mmt.rcv_transaction_id =rt.transaction_id ' ||
1562 ' AND rt.shipment_header_id =rsh.shipment_header_id ' ||
1563 ' AND mmt.transaction_source_type_id = 1'||
1564 ' AND mtt.transaction_type_id = mmt.transaction_type_id ' ||
1565 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1566 ' AND rsh.ship_to_org_id ='||l_org_id ||
1567 ' ) ' ||
1568 ' OR exists ' ||
1569 ' (SELECT 2 ' ||
1570 ' from mtl_material_transactions_temp mmtt , ' ||
1571 ' po_headers_all ph ' ||
1572 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
1573 ' AND mmtt.transaction_type_id = mtt.transaction_type_id ' ||
1574 ' AND (ph.po_header_id in ' ||
1575 ' (SELECT DISTINCT po_header_id ' ||
1576 ' from rcv_shipment_lines rsl, ' ||
1577 ' rcv_shipment_headers rsh ' ||
1578 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
1579 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1580 ' AND rsh.ship_to_org_id ='||l_org_id ||' ))' ||' ) ';
1581
1582 /*p_sql(25) := ' SELECT DISTINCT mol.* ' ||' from mtl_txn_request_lines mol , ' ||
1583 ' rcv_transactions rt , ' ||
1584 ' rcv_shipment_lines rsl , ' ||
1585 ' rcv_shipment_headers rsh ' ||
1586 ' WHERE mol.reference_id = decode(mol.reference ,''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,''PO_LINE_LOCATION_ID'' , rt.po_line_location_id , ''ORDER_LINE_ID'' , rt.oe_order_line_id) ' ||
1587 ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
1588 ' AND mol.organization_id = rt.organization_id ' ||
1589 ' AND mol.inventory_item_id = rsl.item_id ' ||
1590 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
1591 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1592 ' AND rsh.ship_to_org_id ='||l_org_id;*/
1593
1594 p_sql(25) := ' SELECT DISTINCT mol.* ' ||' from mtl_txn_request_lines mol , ' ||
1595 ' rcv_transactions rt , ' ||
1596 ' rcv_shipment_lines rsl , ' ||
1597 ' rcv_shipment_headers rsh ' ||
1598 ' WHERE rt.shipment_line_id = rsl.shipment_line_id ' ||
1599 ' AND mol.organization_id = rt.organization_id ' ||
1600 ' AND mol.inventory_item_id = rsl.item_id ' ||
1601 ' and Nvl(mol.revision,0)=Nvl(rsl.item_revision,0) ' ||
1602 ' and mol.line_status = 7'||
1603 ' and mol.transaction_type_id=18'||
1604 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
1605 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1606 ' AND rsh.ship_to_org_id ='||l_org_id;
1607
1608 p_sql(26) := ' SELECT distinct mmtt.* ' ||
1609 ' from mtl_material_transactions_temp mmtt , ' ||
1610 ' po_headers_all ph ' ||
1611 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
1612 ' AND (ph.po_header_id in ' ||
1613 ' (SELECT DISTINCT po_header_id ' ||
1614 ' from rcv_shipment_lines rsl, ' ||
1615 ' rcv_shipment_headers rsh ' ||
1616 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
1617 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1618 ' AND rsh.ship_to_org_id ='||l_org_id ||
1619 ' )) ';
1620
1621 p_sql(27) := ' SELECT DISTINCT ood.* ' ||
1622 ' from org_organization_definitions ood , ' ||
1623 ' po_line_locations_all pll , ' ||
1624 ' po_headers_all ph , ' ||
1628 ' WHERE pll.po_header_id = ph.po_header_id ' ||
1625 ' financials_system_params_all fsp,' ||
1626 ' rcv_shipment_lines rsl, ' ||
1627 ' rcv_shipment_headers rsh' ||
1629 ' AND fsp.org_id = ph.org_id ' ||
1630 ' AND ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id) ' ||
1631 ' AND pll.line_location_id = rsl.po_line_location_id' ||
1632 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1633 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1634 ' AND rsh.ship_to_org_id ='||l_org_id;
1635
1636 p_sql(28) := ' SELECT DISTINCT mp.* ' ||
1637 ' from mtl_parameters mp , ' ||
1638 ' po_line_locations_all pll , ' ||
1639 ' po_headers_all ph , ' ||
1640 ' financials_system_params_all fsp,' ||
1641 ' rcv_shipment_lines rsl, ' ||
1642 ' rcv_shipment_headers rsh ' ||
1643 ' WHERE pll.po_header_id = ph.po_header_id ' ||
1644 ' AND fsp.org_id = ph.org_id ' ||
1645 ' AND mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id) ' ||
1646 ' AND pll.line_location_id = rsl.po_line_location_id' ||
1647 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1648 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1649 ' AND rsh.ship_to_org_id ='||l_org_id;
1650
1651
1652 p_sql(29) := ' SELECT DISTINCT rp.* ' ||
1653 ' from rcv_parameters rp , ' ||
1654 ' po_line_locations_all pll ,' ||
1655 ' po_lines_all pl , ' ||
1656 ' po_headers_all ph, ' ||
1657 ' financials_system_params_all fsp ' ||
1658 ' WHERE pl.po_header_id = ph.po_header_id ' ||
1659 ' AND pll.po_line_id = pl.po_line_id ' ||
1660 ' AND fsp.org_id = ph.org_id ' ||
1661 ' AND (rp.organization_id = fsp.inventory_organization_id ' ||
1662 ' OR rp.organization_id = pll.ship_to_organization_id) ' ||
1663 ' AND (pll.line_location_id in ' ||
1664 ' (SELECT DISTINCT rsl.po_line_location_id ' ||
1665 ' from rcv_shipment_lines rsl, ' ||
1666 ' rcv_shipment_headers rsh ' ||
1667 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
1668 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1669 ' AND rsh.ship_to_org_id ='||l_org_id ||
1670 ' ))';
1671
1672
1673 p_sql(30):= ' SELECT distinct psp.* ' ||
1674 ' from po_system_parameters_all psp , ' ||
1675 ' po_headers_all ph,' ||
1676 ' rcv_shipment_lines rsl, ' ||
1677 ' rcv_shipment_headers rsh' ||
1678 ' WHERE psp.org_id = ph.org_id ' ||
1679 ' AND ph.po_header_id = rsl.po_header_id' ||
1680 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1681 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1682 ' AND rsh.ship_to_org_id ='||l_org_id;
1683
1684 p_sql(31) := ' SELECT distinct fsp.* ' ||
1685 ' from financials_system_params_all fsp , ' ||
1686 ' po_headers_all ph, ' ||
1687 ' rcv_shipment_lines rsl, ' ||
1688 ' rcv_shipment_headers rsh' ||
1689 ' WHERE fsp.org_id = ph.org_id ' ||
1690 ' and ph.po_header_id = rsl.po_header_id' ||
1691 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1692 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1693 ' AND rsh.ship_to_org_id ='||l_org_id;
1694
1695 p_sql(32) := ' SELECT distinct msn.* ' ||
1696 ' from mtl_serial_numbers msn , ' ||
1697 ' mtl_unit_transactions mut , ' ||
1698 ' rcv_transactions rt ,' ||
1699 ' rcv_shipment_headers rsh, ' ||
1700 ' mtl_material_transactions mmt ' ||
1701 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1702 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1703 ' AND mmt.transaction_source_type_id = 1'||
1704 ' AND mut.transaction_id = mmt.transaction_id ' ||
1705 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
1706 ' AND msn.current_organization_id = mut.organization_id ' ||
1707 ' AND msn.serial_number = mut.serial_number ' ||
1708 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1709 ' AND rsh.ship_to_org_id ='||l_org_id ||
1710 ' UNION ALL ' ||
1711 ' SELECT distinct msn.* ' ||
1712 ' from mtl_serial_numbers msn , ' ||
1713 ' mtl_unit_transactions mut , ' ||
1714 ' rcv_transactions rt ,' ||
1715 ' rcv_shipment_headers rsh, ' ||
1716 ' mtl_material_transactions mmt, ' ||
1717 ' mtl_transaction_lot_numbers mtln ' ||
1718 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1719 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1720 ' AND mmt.transaction_source_type_id = 1'||
1721 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1722 ' AND mut.transaction_id = mtln.serial_transaction_id ' ||
1723 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
1724 ' AND msn.current_organization_id = mut.organization_id ' ||
1725 ' AND msn.serial_number = mut.serial_number ' ||
1726 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1727 ' AND rsh.ship_to_org_id ='||l_org_id;
1728
1729 p_sql(33):=' SELECT distinct msnt.* ' ||
1730 ' from mtl_serial_numbers_temp msnt , ' ||
1731 ' mtl_material_transactions_temp mmtt, ' ||
1732 ' po_headers_all ph,' ||
1733 ' rcv_shipment_lines rsl,' ||
1734 ' rcv_shipment_headers rsh' ||
1735 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
1739 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1736 ' AND msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
1737 ' AND rsl.po_header_id=ph.po_header_id' ||
1738 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1740 ' AND rsh.ship_to_org_id ='||l_org_id ||
1741 ' UNION ALL ' ||
1742 ' SELECT msnt.* ' ||
1743 ' from mtl_serial_numbers_temp msnt, ' ||
1744 ' mtl_material_transactions_temp mmtt , ' ||
1745 ' po_headers_all ph , ' ||
1746 ' mtl_transaction_lots_temp mtln,' ||
1747 ' rcv_shipment_lines rsl,' ||
1748 ' rcv_shipment_headers rsh ' ||
1749 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
1750 ' AND mtln.transaction_temp_id = mmtt.transaction_temp_id ' ||
1751 ' AND msnt.transaction_temp_id = mtln.serial_transaction_temp_id ' ||
1752 ' AND ph.po_header_id = rsl.po_header_id' ||
1753 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
1754 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1755 ' AND rsh.ship_to_org_id ='||l_org_id;
1756
1757 p_sql(34) := ' SELECT distinct msni.* ' ||
1758 ' from mtl_serial_numbers_interface msni , ' ||
1759 ' rcv_transactions_interface rti ,' ||
1760 ' rcv_shipment_headers rsh ' ||
1761 ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1762 ' AND rsh.ship_to_org_id ='||l_org_id ||
1763 ' AND rti.shipment_header_id =rsh.shipment_header_id ' ||
1764 ' AND msni.product_transaction_id = rti.interface_transaction_id';
1765
1766 p_sql(35):=' SELECT distinct mut.* ' ||
1767 ' from mtl_unit_transactions mut , ' ||
1768 ' rcv_transactions rt ,' ||
1769 ' rcv_shipment_headers rsh, ' ||
1770 ' mtl_material_transactions mmt ' ||
1771 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1772 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1773 ' AND mmt.transaction_source_type_id = 1'||
1774 ' AND mut.transaction_id = mmt.transaction_id ' ||
1775 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1776 ' AND rsh.ship_to_org_id ='||l_org_id ||
1777 ' UNION ALL ' ||
1778 ' SELECT mut.* ' ||
1779 ' from mtl_unit_transactions mut, ' ||
1780 ' rcv_transactions rt ,' ||
1781 ' rcv_shipment_headers rsh, ' ||
1782 ' mtl_material_transactions mmt , ' ||
1783 ' mtl_transaction_lot_numbers mtln ' ||
1784 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1785 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1786 ' AND mmt.transaction_source_type_id = 1'||
1787 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1788 ' AND mut.transaction_id = mtln.serial_transaction_id ' ||
1789 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1790 ' AND rsh.ship_to_org_id ='||l_org_id ;
1791
1792
1793 p_sql(36):=' SELECT distinct rss.* ' ||
1794 ' from rcv_serials_supply rss , ' ||
1795 ' rcv_shipment_lines rsl, ' ||
1796 ' rcv_shipment_headers rsh ' ||
1797 ' WHERE rss.shipment_line_id = rsl.shipment_line_id ' ||
1798 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1799 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1800 ' AND rsh.ship_to_org_id ='||l_org_id;
1801
1802 p_sql(37):=' SELECT distinct rst.* ' ||
1803 ' from rcv_serial_transactions rst , ' ||
1804 ' rcv_shipment_lines rsl , ' ||
1805 ' rcv_shipment_headers rsh ' ||
1806 ' WHERE rst.shipment_line_id = rsl.shipment_line_id ' ||
1807 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1808 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1809 ' AND rsh.ship_to_org_id ='||l_org_id;
1810
1811 p_sql(38):=' SELECT distinct rsi.* ' ||
1812 ' from rcv_serials_interface rsi , ' ||
1813 ' rcv_transactions_interface rti , ' ||
1814 ' rcv_shipment_headers rsh ' ||
1815 ' WHERE rti.shipment_header_id = rsh.shipment_header_id ' ||
1816 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1817 ' AND rsh.ship_to_org_id ='||l_org_id ||
1818 ' AND rsi.interface_transaction_id = rti.interface_transaction_id ';
1819
1820 p_sql(39):=' SELECT distinct mln.* ' ||
1821 ' from mtl_lot_numbers mln , ' ||
1822 ' mtl_transaction_lot_numbers mtln , ' ||
1823 ' rcv_transactions rt ,' ||
1824 ' rcv_shipment_headers rsh, ' ||
1825 ' mtl_material_transactions mmt ' ||
1826 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1827 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1828 ' AND mmt.transaction_source_type_id = 1'||
1829 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1830 ' AND mtln.lot_number = mln.lot_number ' ||
1831 ' AND mtln.inventory_item_id = mln.inventory_item_id ' ||
1832 ' AND mtln.organization_id = mln.organization_id ' ||
1833 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1834 ' AND rsh.ship_to_org_id ='||l_org_id;
1835
1836 p_sql(40):=' SELECT distinct mtln.* ' ||
1837 ' from mtl_transaction_lot_numbers mtln , ' ||
1838 ' rcv_transactions rt ,' ||
1839 ' rcv_shipment_headers rsh, ' ||
1840 ' mtl_material_transactions mmt ' ||
1841 ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
1842 ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
1843 ' AND mmt.transaction_source_type_id = 1'||
1847
1844 ' AND mtln.transaction_id = mmt.transaction_id ' ||
1845 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1846 ' AND rsh.ship_to_org_id ='||l_org_id;
1848 p_sql(41):=' SELECT distinct mtli.* ' ||
1849 ' from mtl_transaction_lots_interface mtli , ' ||
1850 ' rcv_transactions_interface rti ,' ||
1851 ' rcv_shipment_headers rsh ' ||
1852 ' WHERE rti.shipment_header_id = rsh.shipment_header_id ' ||
1853 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1854 ' AND rsh.ship_to_org_id ='||l_org_id ||
1855 ' AND mtli.product_transaction_id = RTI.interface_transaction_id';
1856
1857 p_sql(42):=' SELECT distinct mtlt.* ' ||
1858 ' from mtl_transaction_lots_temp mtlt , ' ||
1859 ' mtl_material_transactions_temp mmtt ,' ||
1860 ' po_headers_all ph ' ||
1861 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
1862 ' AND mmtt.transaction_source_type_id = 1 ' ||
1863 ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ' ||
1864 ' AND (ph.po_header_id in ' ||
1865 ' (SELECT DISTINCT po_header_id ' ||
1866 ' from rcv_shipment_lines rsl, ' ||
1867 ' rcv_shipment_headers rsh ' ||
1868 ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
1869 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1870 ' AND rsh.ship_to_org_id ='||l_org_id ||' ))';
1871
1872 p_sql(43):=' SELECT distinct rls.* ' ||
1873 ' from rcv_lots_supply rls , ' ||
1874 ' rcv_shipment_lines rsl , ' ||
1875 ' rcv_shipment_headers rsh ' ||
1876 ' WHERE rsl.shipment_line_id = rls.shipment_line_id ' ||
1877 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1878 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1879 ' AND rsh.ship_to_org_id ='||l_org_id;
1880
1881 p_sql(44):=' SELECT distinct rlt.* ' ||
1882 ' from rcv_lot_transactions rlt , ' ||
1883 ' rcv_shipment_lines rsl , ' ||
1884 ' rcv_shipment_headers rsh ' ||
1885 ' WHERE rsl.shipment_line_id = rlt.shipment_line_id ' ||
1886 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
1887 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1888 ' AND rsh.ship_to_org_id ='||l_org_id;
1889
1890 p_sql(45):=' SELECT distinct rli.* ' ||
1891 ' from rcv_lots_interface rli , ' ||
1892 ' rcv_transactions_interface rti,' ||
1893 ' rcv_shipment_headers rsh ' ||
1894 ' WHERE rti.interface_transaction_id = rli.interface_transaction_id ' ||
1895 ' AND rti.shipment_header_id =rsh.shipment_header_id ' ||
1896 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
1897 ' AND rsh.ship_to_org_id ='||l_org_id;
1898
1899 RETURN;
1900 END; -- END build_req_rcv_sql
1901
1902 ----------------------------------------------------------------
1903 -- Package to Build sqls for Receipt,Org,Req and OU combination
1904 ----------------------------------------------------------------
1905 PROCEDURE build_req_all_sql(p_operating_id IN NUMBER,
1906 p_req_number IN VARCHAR2,
1907 p_line_num IN NUMBER,
1908 p_receipt_number IN VARCHAR2,
1909 p_org_id IN NUMBER,
1910 p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list)
1911 IS
1912
1913 -- Initialize Local Variables.
1914 l_operating_id po_requisition_headers_all.org_id%TYPE := p_operating_id;
1915 l_req_number po_requisition_headers_all.segment1%TYPE := p_req_number;
1916 l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
1917 l_line_num VARCHAR2(1000) := p_line_num;
1918 l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
1919
1920 BEGIN
1921
1922 -- Build the condition based on the input
1923 IF p_line_num IS NULL THEN
1924 l_line_num := ' prl.line_num ';
1925 END IF;
1926
1927 p_sql(1) := ' select distinct prh.*' ||
1928 ' from po_requisition_headers_all prh,' ||
1929 ' po_requisition_lines_all prl' ||
1930 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1931 ' and prh.segment1 = '||''''||l_req_number||''''||
1932 ' and prh.org_id = '||l_operating_id||
1933 ' and prl.line_num='||l_line_num||
1934 ' and prl.requisition_header_id = prh.requisition_header_id' ||
1935 ' and prl.source_type_code = ''VENDOR'' ';
1936
1937 p_sql(2) := ' select distinct prl.*' ||
1938 ' from po_requisition_lines_all prl,' ||
1939 ' po_requisition_headers_all prh, ' ||
1940 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl,
1941 po_line_locations_all pll ' ||
1942 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1943 ' and pll.line_location_id = prl.line_location_id ' ||
1944 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
1945 ' and rsl.po_line_location_id = pll.line_location_id ' ||
1946 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1947 ' and rsh.ship_to_org_id = '|| l_org_id ||
1948 ' and prh.segment1 = '||''''||l_req_number||''''||
1949 ' and prh.org_id = '||l_operating_id||
1950 ' and prl.line_num='||l_line_num||
1951 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1952 ' and prl.source_type_code = ''VENDOR''' ||
1953 ' order by prl.requisition_line_id ';
1954
1955 p_sql(3) := ' select distinct prd.*' ||
1956 ' from po_req_distributions_all prd ,' ||
1960 po_line_locations_all pll ' ||
1957 ' po_requisition_lines_all prl ,' ||
1958 ' po_requisition_headers_all prh,' ||
1959 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl,
1961 ' where prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1962 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1963 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1964 ' and prl.source_type_code = ''VENDOR''' ||
1965 'and pll.line_location_id = prl.line_location_id ' ||
1966 'and rsh.shipment_header_id = rsl.shipment_header_id ' ||
1967 'and rsl.po_line_location_id = pll.line_location_id ' ||
1968 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1969 ' and rsh.ship_to_org_id = '|| l_org_id ||
1970 ' and prh.segment1 = '||''''||l_req_number||''''||
1971 ' and prh.org_id = '||l_operating_id||
1972 ' and prl.line_num='||l_line_num||
1973 ' order by prd.distribution_id ';
1974
1975 p_sql(4) := ' SELECT distinct ph.* ' ||
1976 ' from po_headers_all ph,' ||
1977 ' po_distributions_all pd,' ||
1978 ' po_req_distributions_all prd ,' ||
1979 ' po_requisition_lines_all prl ,' ||
1980 ' po_requisition_headers_all prh,' ||
1981 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
1982 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
1983 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1984 ' and prl.requisition_line_id = prd.requisition_line_id' ||
1985 ' and prl.source_type_code = ''VENDOR''' ||
1986 ' and pd.req_distribution_id = prd.distribution_id' ||
1987 ' and pd.po_header_id=ph.po_header_id' ||
1988 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
1989 'and rsl.po_header_id = ph.po_header_id ' ||
1990 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1991 ' and rsh.ship_to_org_id = '|| l_org_id ||
1992 ' and prh.segment1 = '||''''||l_req_number||''''||
1993 ' and prl.line_num='||l_line_num||
1994 ' and prh.org_id = '||l_operating_id;
1995
1996
1997 p_sql(5) := ' SELECT distinct pl.* ' ||
1998 ' from po_lines_all pl , ' ||
1999 ' po_headers_all ph,' ||
2000 ' po_distributions_all pd,' ||
2001 ' po_req_distributions_all prd ,' ||
2002 ' po_requisition_lines_all prl ,' ||
2003 ' po_requisition_headers_all prh,' ||
2004 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
2005 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2006 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2007 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2008 ' and prl.source_type_code = ''VENDOR''' ||
2009 ' and pd.req_distribution_id = prd.distribution_id' ||
2010 ' and pd.po_header_id=ph.po_header_id' ||
2011 ' AND pl.po_header_id = ph.po_header_id' ||
2012 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
2013 ' and rsl.po_line_id = pl.po_line_id ' ||
2014 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2015 ' and rsh.ship_to_org_id = '|| l_org_id ||
2016 ' and prh.segment1 = '||''''||l_req_number||''''||
2017 ' and prl.line_num='||l_line_num||
2018 ' and prh.org_id = '||l_operating_id;
2019
2020
2021 p_sql(6) := ' SELECT distinct pll.* ' ||
2022 ' from po_line_locations_all pll , ' ||
2023 ' po_lines_all pl , ' ||
2024 ' po_headers_all ph,' ||
2025 ' po_distributions_all pd,' ||
2026 ' po_req_distributions_all prd ,' ||
2027 ' po_requisition_lines_all prl ,' ||
2028 ' po_requisition_headers_all prh,' ||
2029 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
2030 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2031 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2032 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2033 ' and prl.source_type_code = ''VENDOR''' ||
2034 ' and pd.req_distribution_id = prd.distribution_id ' ||
2035 ' and pd.po_header_id=ph.po_header_id' ||
2036 ' AND pl.po_header_id = ph.po_header_id' ||
2037 ' AND pll.po_line_id = pl.po_line_id ' ||
2038 ' AND pll.line_location_id = pd.line_location_id ' || --Bug#6882986
2039 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
2040 ' and rsl.po_line_location_id = pll.line_location_id ' ||
2041 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2042 ' and rsh.ship_to_org_id = '|| l_org_id ||
2043 ' and prh.segment1 = '||''''||l_req_number||''''||
2044 ' and prl.line_num='||l_line_num||
2045 ' and prh.org_id = '||l_operating_id;
2046
2047
2048 p_sql(7) := ' SELECT distinct pd.* ' ||
2049 ' from po_line_locations_all pll , ' ||
2050 ' po_lines_all pl , ' ||
2051 ' po_headers_all ph , ' ||
2052 ' po_distributions_all pd,' ||
2053 ' po_req_distributions_all prd ,' ||
2054 ' po_requisition_lines_all prl ,' ||
2055 ' po_requisition_headers_all prh ,' ||
2056 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
2057 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2058 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2059 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2060 ' and prl.source_type_code = ''VENDOR''' ||
2061 ' and pd.req_distribution_id = prd.distribution_id' ||
2062 ' and pd.po_header_id=ph.po_header_id' ||
2063 ' AND pl.po_header_id = ph.po_header_id' ||
2064 ' AND pll.po_line_id = pl.po_line_id ' ||
2065 ' AND pll.line_location_id = pd.line_location_id ' ||
2069 ' and rsh.ship_to_org_id = '|| l_org_id ||
2066 'and rsh.shipment_header_id = rsl.shipment_header_id '||
2067 'and rsl.po_line_location_id = pll.line_location_id ' ||
2068 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2070 ' and prh.segment1 = '||''''||l_req_number||''''||
2071 ' and prl.line_num='||l_line_num||
2072 ' and prh.org_id = '||l_operating_id;
2073
2074
2075 p_sql(8) := ' SELECT distinct gcc.* ' ||
2076 ' from gl_code_combinations gcc , ' ||
2077 ' po_line_locations_all pll , ' ||
2078 ' po_lines_all pl , ' ||
2079 ' po_headers_all ph , ' ||
2080 ' po_distributions_all pd,' ||
2081 ' po_req_distributions_all prd ,' ||
2082 ' po_requisition_lines_all prl ,' ||
2083 ' po_requisition_headers_all prh' ||
2084 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2085 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2086 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2087 ' and prl.source_type_code = ''VENDOR''' ||
2088 ' and gcc.summary_flag = ''N'' ' ||
2089 ' and pd.req_distribution_id = prd.distribution_id' ||
2090 ' and pd.po_header_id=ph.po_header_id' ||
2091 ' AND pl.po_header_id = ph.po_header_id' ||
2092 ' AND pll.po_line_id = pl.po_line_id ' ||
2093 ' AND pll.line_location_id = pd.line_location_id' ||
2094 ' AND gcc.template_id is null ' ||
2095 ' AND gcc.code_combination_id in (pd.accrual_account_id ,
2096 pd.budget_account_id , pd.VARIANCE_ACCOUNT_ID ,
2097 pd.code_combination_id)' ||
2098 ' and prh.segment1 = '||''''||l_req_number||''''||
2099 ' and prl.line_num='||l_line_num||
2100 ' and prh.org_id = '||l_operating_id;
2101
2102 p_sql(9) := ' SELECT distinct rrsl.* ' ||
2103 ' from rcv_receiving_sub_ledger rrsl , ' ||
2104 ' rcv_transactions rt , ' ||
2105 ' po_headers_all ph,' ||
2106 ' po_line_locations_all pll , ' ||--Bug#6882986
2107 ' po_distributions_all pd,' ||
2108 ' po_req_distributions_all prd ,' ||
2109 ' po_requisition_lines_all prl ,' ||
2110 ' po_requisition_headers_all prh, ' ||
2111 ' rcv_shipment_headers rsh ' ||
2112 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2113 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2114 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2115 ' and prl.source_type_code = ''VENDOR''' ||
2116 ' and pd.req_distribution_id = prd.distribution_id' ||
2117 ' and pd.po_header_id=ph.po_header_id' ||
2118 ' and pll.po_header_id=ph.po_header_id' ||--Bug#6882986
2119 ' and pll.line_location_id=pd.line_location_id' ||--Bug#6882986
2120 ' and pll.line_location_id=rt.po_line_location_id' ||--Bug#6882986
2121 ' AND rt.po_header_id = ph.po_header_id ' ||
2122 ' AND rrsl.rcv_transaction_id = rt.transaction_id' ||
2123 ' and rsh.shipment_header_id = rt.shipment_header_id ' ||
2124 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2125 ' and rsh.ship_to_org_id = '|| l_org_id ||
2126 ' and prh.segment1 = '||''''||l_req_number||''''||
2127 ' and prl.line_num='||l_line_num||
2128 ' and prh.org_id = '||l_operating_id;
2129
2130 /*p_sql(10) := ' SELECT distinct id.* ' ||
2131 ' from ap_invoice_distributions_all id , ' ||
2132 ' po_line_locations_all pll , ' ||
2133 ' po_lines_all pl , ' ||
2134 ' po_headers_all ph , ' ||
2135 ' po_distributions_all pd,' ||
2136 ' po_req_distributions_all prd ,' ||
2137 ' po_requisition_lines_all prl ,' ||
2138 ' po_requisition_headers_all prh, ' ||
2139 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
2140 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2141 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2142 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2143 ' and prl.source_type_code = ''VENDOR''' ||
2144 ' and pd.req_distribution_id = prd.distribution_id' ||
2145 ' and pd.po_header_id=ph.po_header_id' ||
2146 ' AND pl.po_header_id = ph.po_header_id' ||
2147 ' AND pll.po_line_id = pl.po_line_id ' ||
2148 ' AND pll.line_location_id = pd.line_location_id' ||
2149 ' and pll.line_location_id = rsl.po_line_location_id ' ||
2150 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
2151 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
2152 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2153 ' and rsh.ship_to_org_id = '|| l_org_id ||
2154 ' and prh.segment1 = '||''''||l_req_number||''''||
2155 ' and prl.line_num='||l_line_num||
2156 ' and prh.org_id = '||l_operating_id;*/
2157
2158 p_sql(10) := ' SELECT distinct id.* ' ||
2159 ' from ap_invoice_lines_all id , ' ||
2160 ' po_line_locations_all pll , ' ||
2161 ' po_lines_all pl , ' ||
2162 ' po_headers_all ph , ' ||
2163 ' po_distributions_all pd,' ||
2164 ' po_req_distributions_all prd ,' ||
2165 ' po_requisition_lines_all prl ,' ||
2166 ' po_requisition_headers_all prh, ' ||
2167 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
2168 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2169 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2170 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2171 ' and prl.source_type_code = ''VENDOR''' ||
2172 ' and pd.req_distribution_id = prd.distribution_id' ||
2173 ' and pd.po_header_id=ph.po_header_id' ||
2174 ' AND pl.po_header_id = ph.po_header_id' ||
2178 ' and rt.shipment_header_id = rsh.shipment_header_id ' ||
2175 ' AND pll.po_line_id = pl.po_line_id ' ||
2176 ' AND pll.line_location_id = pd.line_location_id' ||
2177 ' and pll.line_location_id = rt.po_line_location_id ' ||
2179 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
2180 ' and id.po_line_location_id=pll.line_location_id'||' and id.po_line_id=pl.po_line_id'||
2181 ' and id.po_header_id=ph.po_header_id'||' and id.rcv_transaction_id=rt.transaction_id'||
2182 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2183 ' and rsh.ship_to_org_id = '|| l_org_id ||
2184 ' and prh.segment1 = '||''''||l_req_number||''''||
2185 ' and prl.line_num='||l_line_num||
2186 ' and prh.org_id = '||l_operating_id;
2187
2188 p_sql(11) := ' SELECT distinct ai.* ' ||
2189 ' from ap_invoices_all ai , ' ||
2190 ' ap_invoice_distributions_all id , ' ||
2191 ' po_line_locations_all pll , ' ||
2192 ' po_lines_all pl , ' ||
2193 ' po_headers_all ph , ' ||
2194 ' po_distributions_all pd,' ||
2195 ' po_req_distributions_all prd ,' ||
2196 ' po_requisition_lines_all prl ,' ||
2197 ' po_requisition_headers_all prh, ' ||
2198 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
2199 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2200 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2201 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2202 ' and prl.source_type_code = ''VENDOR''' ||
2203 ' and pd.req_distribution_id = prd.distribution_id' ||
2204 ' and pd.po_header_id=ph.po_header_id' ||
2205 ' AND pl.po_header_id = ph.po_header_id' ||
2206 ' AND pll.po_line_id = pl.po_line_id ' ||
2207 ' AND pll.line_location_id = pd.line_location_id' ||
2208 ' AND id.po_distribution_id = pd.po_distribution_id ' ||
2209 ' and pll.line_location_id = rsl.po_line_location_id ' ||
2210 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
2211 ' AND ai.invoice_id = id.invoice_id' ||
2212 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2213 ' and rsh.ship_to_org_id = '|| l_org_id ||
2214 ' and prh.segment1 = '||''''||l_req_number||''''||
2215 ' and prl.line_num='||l_line_num||
2216 ' and prh.org_id = '||l_operating_id;
2217
2218 p_sql(12) := ' SELECT distinct ili.* ' ||
2219 ' from ap_invoice_lines_interface ili , ' ||
2220 ' po_line_locations_all pll , ' ||
2221 ' po_lines_all pl , ' ||
2222 ' po_headers_all ph , ' ||
2223 ' po_distributions_all pd,' ||
2224 ' po_req_distributions_all prd ,' ||
2225 ' po_requisition_lines_all prl ,' ||
2226 ' po_requisition_headers_all prh, ' ||
2227 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
2228 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2229 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2230 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2231 ' and prl.source_type_code = ''VENDOR''' ||
2232 ' and pd.req_distribution_id = prd.distribution_id' ||
2233 ' and pd.po_header_id=ph.po_header_id' ||
2234 ' AND pl.po_header_id = ph.po_header_id' ||
2235 ' AND pll.po_line_id = pl.po_line_id ' ||
2236 ' AND pll.line_location_id = pd.line_location_id' ||
2237 ' and ili.po_header_id = ph.po_header_id ' ||
2238 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2239 ' and rsh.ship_to_org_id = '|| l_org_id ||
2240 ' and prh.segment1 = '||''''||l_req_number||''''||
2241 ' and prl.line_num='||l_line_num||
2242 ' and prh.org_id = '||l_operating_id;
2243
2244
2245 p_sql(13) := ' SELECT distinct ihi.* ' ||
2246 ' from ap_invoices_interface ihi , ' ||
2247 ' ap_invoice_lines_interface ili , ' ||
2248 ' po_line_locations_all pll , ' ||
2249 ' po_lines_all pl , ' ||
2250 ' po_headers_all ph , ' ||
2251 ' po_distributions_all pd,' ||
2252 ' po_req_distributions_all prd ,' ||
2253 ' po_requisition_lines_all prl ,' ||
2254 ' po_requisition_headers_all prh, ' ||
2255 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
2256 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2257 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2258 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2259 ' and prl.source_type_code = ''VENDOR''' ||
2260 ' and pd.req_distribution_id = prd.distribution_id' ||
2261 ' and pd.po_header_id=ph.po_header_id' ||
2262 ' AND pl.po_header_id = ph.po_header_id' ||
2263 ' AND pll.po_line_id = pl.po_line_id ' ||
2264 ' AND pll.line_location_id = pd.line_location_id' ||
2265 ' and ili.po_header_id = ph.po_header_id ' ||
2266 ' AND ihi.invoice_id = ili.invoice_id ' ||
2267 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
2268 ' and rsl.po_line_location_id = pll.line_location_id ' ||
2269 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2270 ' and rsh.ship_to_org_id = '|| l_org_id ||
2271 ' and prh.segment1 = '||''''||l_req_number||''''||
2272 ' and prl.line_num='||l_line_num||
2273 ' and prh.org_id = '||l_operating_id;
2274
2275 p_sql(14) := ' SELECT DISTINCT rsh.* ' ||
2276 ' from rcv_shipment_lines rsl , ' ||
2277 ' rcv_shipment_headers rsh, ' ||
2278 ' po_line_locations_all pll , ' ||
2279 ' po_distributions_all pd,' ||
2280 ' po_req_distributions_all prd ,' ||
2281 ' po_requisition_lines_all prl ,' ||
2282 ' po_requisition_headers_all prh ' ||
2286 ' and prl.source_type_code = ''VENDOR''' ||
2283 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2284 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2285 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2287 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2288 ' and rsh.ship_to_org_id = '|| l_org_id ||
2289 ' and pd.req_distribution_id = prd.distribution_id' ||
2290 ' and rsl.po_line_location_id=pll.line_location_id' ||
2291 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
2292 ' AND pll.line_location_id = pd.line_location_id' ||
2293 ' and prh.segment1 = '||''''||l_req_number||''''||
2294 ' and prl.line_num='||l_line_num||
2295 ' and prh.org_id = '||l_operating_id;
2296
2297 p_sql(15) := ' SELECT DISTINCT rsl.* ' ||
2298 ' from rcv_shipment_lines rsl , ' ||
2299 ' rcv_shipment_headers rsh, ' ||
2300 ' po_line_locations_all pll , ' ||
2301 ' po_distributions_all pd,' ||
2302 ' po_req_distributions_all prd ,' ||
2303 ' po_requisition_lines_all prl ,' ||
2304 ' po_requisition_headers_all prh ' ||
2305 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2306 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2307 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2308 ' and prl.source_type_code = ''VENDOR''' ||
2309 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2310 ' and rsh.ship_to_org_id = '|| l_org_id ||
2311 ' and pd.req_distribution_id = prd.distribution_id' ||
2312 ' and rsl.po_line_location_id=pll.line_location_id' ||
2313 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
2314 ' AND pll.line_location_id = pd.line_location_id' ||
2315 ' and prh.segment1 = '||''''||l_req_number||''''||
2316 ' and prl.line_num='||l_line_num||
2317 ' and prh.org_id = '||l_operating_id;
2318
2319 p_sql(16) := ' SELECT DISTINCT rt.* ' ||
2320 ' from rcv_transactions rt , ' ||
2321 ' rcv_shipment_headers rsh, ' ||
2322 ' po_line_locations_all pll , ' ||
2323 ' po_distributions_all pd,' ||
2324 ' po_req_distributions_all prd ,' ||
2325 ' po_requisition_lines_all prl ,' ||
2326 ' po_requisition_headers_all prh ' ||
2327 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2328 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2329 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2330 ' and rsh.ship_to_org_id = '|| l_org_id ||
2331 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2332 ' and prl.source_type_code = ''VENDOR''' ||
2333 ' and pd.req_distribution_id = prd.distribution_id' ||
2334 ' and rt.po_line_location_id=pll.line_location_id' ||
2335 ' AND rt.shipment_header_id = rsh.shipment_header_id ' ||
2336 ' AND pll.line_location_id = pd.line_location_id' ||
2337 ' and prh.segment1 = '||''''||l_req_number||''''||
2338 ' and prl.line_num='||l_line_num||
2339 ' and prh.org_id = '||l_operating_id;
2340
2341
2342 p_sql(17) := ' SELECT ms.* ' ||
2343 ' from mtl_supply ms , ' ||
2344 ' po_line_locations_all pll , ' ||
2345 ' po_distributions_all pd,' ||
2346 ' po_req_distributions_all prd ,' ||
2347 ' po_requisition_lines_all prl ,' ||
2348 ' po_requisition_headers_all prh ,' ||
2349 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl'||
2350 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2351 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2352 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2353 ' and prl.source_type_code = ''VENDOR''' ||
2354 ' and pd.req_distribution_id = prd.distribution_id' ||
2355 ' and ms.po_line_location_id=pll.line_location_id' ||
2356 ' AND pll.line_location_id = pd.line_location_id' ||
2357 ' and rsl.shipment_header_id = rsh.shipment_header_id'||
2358 ' and rsl.po_line_location_id = ms.po_line_location_id'||
2359 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2360 ' and rsh.ship_to_org_id = '|| l_org_id ||
2361 ' and prh.segment1 = '||''''||l_req_number||''''||
2362 ' and prl.line_num='||l_line_num||
2363 ' and prh.org_id = '||l_operating_id;
2364
2365 p_sql(18) := ' SELECT rs.* ' ||
2366 ' from rcv_supply rs , ' ||
2367 ' po_line_locations_all pll , ' ||
2368 ' po_distributions_all pd,' ||
2369 ' po_req_distributions_all prd ,' ||
2370 ' po_requisition_lines_all prl ,' ||
2371 ' po_requisition_headers_all prh , rcv_shipment_headers rsh,
2372 rcv_shipment_lines rsl ' ||
2373 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2374 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2375 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2376 ' and prl.source_type_code = ''VENDOR''' ||
2377 ' and pd.req_distribution_id = prd.distribution_id' ||
2378 ' and rs.po_line_location_id=pll.line_location_id' ||
2379 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
2380 ' and rsl.po_line_location_id = rs.po_line_location_id' ||
2381 ' AND pll.line_location_id = pd.line_location_id' ||
2382 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2383 ' and rsh.ship_to_org_id = '|| l_org_id ||
2384 ' and prh.segment1 = '||''''||l_req_number||''''||
2385 ' and prl.line_num='||l_line_num||
2386 ' and prh.org_id = '||l_operating_id;
2387
2388 p_sql(19) := ' SELECT rhi.* ' ||
2389 ' from rcv_headers_interface rhi ' ||
2390 ' WHERE exists ' ||
2391 ' (SELECT 1 ' ||
2395 ' po_lines_all pl , ' ||
2392 ' from rcv_shipment_lines rsl , ' ||
2393 ' rcv_shipment_headers rsh, ' ||
2394 ' po_line_locations_all pll , ' ||
2396 ' po_headers_all ph , ' ||
2397 ' po_distributions_all pd,' ||
2398 ' po_req_distributions_all prd ,' ||
2399 ' po_requisition_lines_all prl ,' ||
2400 ' po_requisition_headers_all prh ' ||
2401 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2402 ' and prh.requisition_header_id =
2403 prl.requisition_header_id' ||
2404 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2405 ' and prl.source_type_code = ''VENDOR''' ||
2406 ' and pd.req_distribution_id = prd.distribution_id' ||
2407 ' and pd.po_header_id=ph.po_header_id' ||
2408 ' AND pl.po_header_id = ph.po_header_id' ||
2409 ' AND pll.po_line_id = pl.po_line_id ' ||
2410 ' AND pll.line_location_id = pd.line_location_id' ||
2411 ' and rsl.po_header_id = ph.po_header_id ' ||
2412 ' and rsl.po_line_location_id = pll.line_location_id'||--Bug#6882986
2413 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
2414 ' AND rsh.shipment_header_id = rhi.receipt_header_id' ||
2415 ' and prh.segment1 = '||''''||l_req_number||''''||
2416 ' and prl.line_num='||l_line_num||
2417 ' and prh.org_id = '||l_operating_id ||
2418 ' ) ' ||
2419 'union'||
2420 ' SELECT DISTINCT rhi.* ' ||
2421 ' from rcv_headers_interface rhi ' ||
2422 ' WHERE exists ' ||
2423 ' (SELECT 3 ' ||
2424 ' from rcv_transactions_interface rti , ' ||
2425 ' po_line_locations_all pll , ' ||
2426 ' po_lines_all pl , ' ||
2427 ' po_headers_all ph , ' ||
2428 ' po_distributions_all pd,' ||
2429 ' po_req_distributions_all prd ,' ||
2430 ' po_requisition_lines_all prl ,' ||
2431 ' po_requisition_headers_all prh ' ||
2432 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2433 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2434 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2435 ' and prl.source_type_code = ''VENDOR''' ||
2436 ' and pd.req_distribution_id = prd.distribution_id' ||
2437 ' and pd.po_header_id=ph.po_header_id' ||
2438 ' AND pl.po_header_id = ph.po_header_id' ||
2439 ' AND pll.po_line_id = pl.po_line_id ' ||
2440 ' AND pll.line_location_id = pd.line_location_id' ||
2441 ' AND rti.po_header_id = ph.po_header_id ' ||
2442 ' AND rti.po_line_location_id = pll.line_location_id ' ||----Bug#6882986
2443 ' AND rti.po_header_id is not null ' ||
2444 ' AND rhi.header_interface_id =
2445 rti.header_interface_id ' ||
2446 ' and prh.segment1 = '||''''||l_req_number||''''||
2447 ' and prl.line_num='||l_line_num||
2448 ' and prh.org_id = '||l_operating_id||
2449 ' ) ';
2450
2451 p_sql(20) := ' SELECT DISTINCT rti.*' ||
2452 ' from rcv_transactions_interface rti ' ||
2453 ' WHERE exists ' ||
2454 ' (SELECT 1 ' ||
2455 ' from po_line_locations_all pll , ' ||
2456 ' po_lines_all pl , ' ||
2457 ' po_headers_all ph , ' ||
2458 ' po_distributions_all pd,' ||
2459 ' po_req_distributions_all prd ,' ||
2460 ' po_requisition_lines_all prl ,' ||
2461 ' po_requisition_headers_all prh ' ||
2462 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2463 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2464 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2465 ' and prl.source_type_code = ''VENDOR''' ||
2466 ' and pd.req_distribution_id = prd.distribution_id' ||
2467 ' and pd.po_header_id=ph.po_header_id' ||
2468 ' AND pl.po_header_id = ph.po_header_id' ||
2469 ' AND pll.po_line_id = pl.po_line_id ' ||
2470 ' AND pll.line_location_id = pd.line_location_id' ||
2471 ' AND rti.po_header_id = ph.po_header_id' ||
2472 ' AND rti.po_line_location_id = pll.line_location_id ' ||--Bug#6882986
2473 ' and prh.segment1 = '||''''||l_req_number||''''||
2474 ' and prl.line_num='||l_line_num||
2475 ' and prh.org_id = '||l_operating_id||
2476 ' ) ' ||
2477 'union' ||
2478 ' select rti.*' ||
2479 ' from rcv_transactions_interface rti, rcv_shipment_headers rsh' ||
2480 ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2481 ' and rsh.ship_to_org_id = '|| l_org_id ||
2482 ' and rti.shipment_header_id = rsh.shipment_header_id ' ;
2483
2484 p_sql(21) := ' SELECT DISTINCT pie.* ' ||
2485 ' from po_interface_errors pie , ' ||
2486 ' rcv_transactions_interface rti , ' ||
2487 ' po_line_locations_all pll , ' ||
2488 ' po_distributions_all pd,' ||
2489 ' po_req_distributions_all prd ,' ||
2490 ' po_requisition_lines_all prl ,' ||
2491 ' po_requisition_headers_all prh ' ||
2492 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2493 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2494 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2495 ' and prl.source_type_code = ''VENDOR''' ||
2496 ' and pd.req_distribution_id = prd.distribution_id' ||
2497 ' AND pll.line_location_id = pd.line_location_id' ||
2501 ' and prh.org_id = '||l_operating_id||
2498 ' AND rti.po_line_location_id = pll.line_location_id ' ||--Bug#6882986
2499 ' and prh.segment1 = '||''''||l_req_number||''''||
2500 ' and prl.line_num='||l_line_num||
2502 ' AND rti.po_header_id=pll.po_header_id'||
2503 ' AND (pie.interface_transaction_id=rti.interface_transaction_id OR '||
2504 'pie.interface_line_id = rti.interface_transaction_id)';
2505
2506 p_sql(22) := ' select distinct msi.*' ||
2507 ' from po_requisition_lines_all prl,' ||
2508 ' po_requisition_headers_all prh,' ||
2509 ' mtl_system_items msi , po_line_locations_all pll, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
2510 ' where prh.segment1 = '||''''||l_req_number||''''||
2511 ' and prh.org_id = '||l_operating_id||
2512 ' and prl.line_num='||l_line_num||
2513 ' and prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2514 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2515 ' and prl.source_type_code = ''VENDOR''' ||
2516 ' and prl.item_id = msi.inventory_item_id' ||
2517 ' and prl.line_location_id = pll.line_location_id ' ||
2518 ' and pll.line_location_id = rsl.po_line_location_id ' ||
2519 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
2520 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2521 ' and rsh.ship_to_org_id = '|| l_org_id ||
2522 ' and prl.destination_organization_id = msi.organization_id ';
2523
2524 p_sql(23) := ' SELECT mmt.* ' ||
2525 ' from mtl_material_transactions mmt , ' ||
2526 ' po_line_locations_all pll , ' ||
2527 ' po_lines_all pl , ' ||
2528 ' po_headers_all ph , ' ||
2529 ' po_distributions_all pd,' ||
2530 ' po_req_distributions_all prd ,' ||
2531 ' po_requisition_lines_all prl ,' ||
2532 ' po_requisition_headers_all prh, ' ||
2533 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
2534 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2535 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2536 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2537 ' and prl.source_type_code = ''VENDOR''' ||
2538 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2539 ' and rsh.ship_to_org_id = '|| l_org_id ||
2540 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
2541 ' and rt.transaction_id = mmt.rcv_transaction_id '||
2542 ' AND rt.po_line_location_id = pll.line_location_id' ||--Bug#6882986
2543 ' and pd.req_distribution_id = prd.distribution_id' ||
2544 ' and pd.po_header_id=ph.po_header_id' ||
2545 ' AND pl.po_header_id = ph.po_header_id' ||
2546 ' AND pll.po_line_id = pl.po_line_id ' ||
2547 ' AND pll.line_location_id = pd.line_location_id' ||
2548 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2549 ' AND mmt.transaction_source_type_id = 1 ' ||
2550 ' and prh.segment1 = '||''''||l_req_number||''''||
2551 ' and prl.line_num='||l_line_num||
2552 ' and prh.org_id = '||l_operating_id;
2553
2554 p_sql(24) := ' SELECT mtt.transaction_type_id , ' ||
2555 ' mtt.transaction_type_name , ' ||
2556 ' mtt.transaction_source_type_id , ' ||
2557 ' mtt.transaction_action_id , ' ||
2558 ' mtt.user_defined_flag , ' ||
2559 ' mtt.disable_date ' ||
2560 ' from mtl_transaction_types mtt ' ||
2561 ' WHERE exists ' ||
2562 ' (SELECT 1 ' ||
2563 ' from mtl_material_transactions mmt , ' ||
2564 ' po_line_locations_all pll , ' ||
2565 ' po_lines_all pl , ' ||
2566 ' po_headers_all ph , ' ||
2567 ' po_distributions_all pd,' ||
2568 ' po_req_distributions_all prd ,' ||
2569 ' po_requisition_lines_all prl ,' ||
2570 ' po_requisition_headers_all prh, ' ||
2571 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
2572 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2573 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2574 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2575 ' and prl.source_type_code = ''VENDOR''' ||
2576 ' and pd.req_distribution_id = prd.distribution_id' ||
2577 ' and pd.po_header_id=ph.po_header_id' ||
2578 ' AND pl.po_header_id = ph.po_header_id' ||
2579 ' AND pll.po_line_id = pl.po_line_id ' ||
2580 ' AND pll.line_location_id = pd.line_location_id' ||
2581 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2582 ' AND mmt.transaction_source_type_id = 1 ' ||
2583 ' AND mtt.transaction_type_id = mmt.transaction_type_id ' ||
2584 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
2585 ' and rt.transaction_id = mmt.rcv_transaction_id '||
2586 ' AND rt.po_line_location_id = pll.line_location_id' ||--Bug#6882986
2587 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2588 ' and rsh.ship_to_org_id = '|| l_org_id ||
2589 ' and prh.segment1 = '||''''||l_req_number||''''||
2590 ' and prl.line_num='||l_line_num||
2591 ' and prh.org_id = '||l_operating_id||
2592 ' ) ' ||
2593 ' OR exists ' ||
2594 ' (SELECT 2 ' ||
2595 ' from mtl_material_transactions_temp mmtt , ' ||
2596 ' mtl_material_transactions mmt ,'||
2597 ' po_line_locations_all pll , ' ||
2598 ' po_lines_all pl , ' ||
2599 ' po_headers_all ph , ' ||
2600 ' po_distributions_all pd,' ||
2601 ' po_req_distributions_all prd ,' ||
2602 ' po_requisition_lines_all prl ,' ||
2603 ' po_requisition_headers_all prh, ' ||
2604 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
2608 ' and prl.source_type_code = ''VENDOR''' ||
2605 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2606 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2607 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2609 ' and pd.req_distribution_id = prd.distribution_id' ||
2610 ' and pd.po_header_id=ph.po_header_id' ||
2611 ' AND pl.po_header_id = ph.po_header_id' ||
2612 ' AND pll.po_line_id = pl.po_line_id ' ||
2613 ' AND pll.line_location_id = pd.line_location_id' ||
2614 ' AND rt.po_line_location_id = pll.line_location_id' ||--Bug#6882986
2615 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2616 ' AND mmt.transaction_source_type_id = 1 ' ||
2617 ' AND mtt.transaction_type_id = mmtt.transaction_type_id ' ||
2618 ' and rsh.shipment_header_id = rt.shipment_header_id' ||
2619 ' and rt.transaction_id = mmtt.rcv_transaction_id ' ||
2620 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2621 ' and rsh.ship_to_org_id = '|| l_org_id ||
2622 ' and prh.segment1 = '||''''||l_req_number||''''||
2623 ' and prl.line_num='||l_line_num||
2624 ' and prh.org_id = '||l_operating_id ||
2625 ' ) ';
2626
2627 /* p_sql(25) := ' SELECT DISTINCT mol.* ' ||
2628 ' from mtl_txn_request_lines mol , ' ||
2629 ' rcv_transactions rt , ' ||
2630 ' rcv_shipment_lines rsl , ' ||
2631 ' po_line_locations_all pll , ' ||
2632 ' po_distributions_all pd,' ||
2633 ' po_req_distributions_all prd ,' ||
2634 ' po_requisition_lines_all prl ,' ||
2635 ' po_requisition_headers_all prh, ' ||
2636 ' rcv_shipment_headers rsh '||
2637 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2638 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2639 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2640 ' and prl.source_type_code = ''VENDOR''' ||
2641 ' and pd.req_distribution_id = prd.distribution_id' ||
2642 ' and rsl.po_line_location_id=pll.line_location_id' ||
2643 ' and mol.reference_id = decode(mol.reference
2644 ,''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,''PO_LINE_LOCATION_ID'' ,
2645 rt.po_line_location_id , ''ORDER_LINE_ID'' , rt.oe_order_line_id) ' ||
2646 ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
2647 ' AND mol.organization_id = rt.organization_id ' ||
2648 ' AND mol.inventory_item_id = rsl.item_id' ||
2649 ' AND pll.line_location_id = pd.line_location_id' ||
2650 ' and rt.shipment_header_id = rsh.shipment_header_id '||
2651 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2652 ' and rsh.ship_to_org_id = '|| l_org_id ||
2653 ' and prh.segment1 = '||''''||l_req_number||''''||
2654 ' and prl.line_num='||l_line_num||
2655 ' and prh.org_id = '||l_operating_id; */
2656
2657 p_sql(25) := ' SELECT DISTINCT mol.* ' ||
2658 ' from mtl_txn_request_lines mol , ' ||
2659 ' rcv_transactions rt , ' ||
2660 ' rcv_shipment_lines rsl , ' ||
2661 ' po_line_locations_all pll , ' ||
2662 ' po_distributions_all pd,' ||
2663 ' po_req_distributions_all prd ,' ||
2664 ' po_requisition_lines_all prl ,' ||
2665 ' po_requisition_headers_all prh, ' ||
2666 ' rcv_shipment_headers rsh '||
2667 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2668 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2669 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2670 ' and prl.source_type_code = ''VENDOR''' ||
2671 ' and pd.req_distribution_id = prd.distribution_id' ||
2672 ' and rsl.po_line_location_id=pll.line_location_id' ||
2673 ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
2674 ' AND mol.organization_id = rt.organization_id ' ||
2675 ' AND mol.inventory_item_id = rsl.item_id' ||
2676 ' and Nvl(mol.revision,0)=Nvl(rsl.item_revision,0) ' ||
2677 ' and mol.line_status = 7'||
2678 ' and mol.transaction_type_id=18'||
2679 ' AND pll.line_location_id = pd.line_location_id' ||
2680 ' and rt.shipment_header_id = rsh.shipment_header_id '||
2681 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2682 ' and rsh.ship_to_org_id = '|| l_org_id ||
2683 ' and prh.segment1 = '||''''||l_req_number||''''||
2684 ' and prl.line_num='||l_line_num||
2685 ' and prh.org_id = '||l_operating_id;
2686
2687 p_sql(26) := ' SELECT mmtt.* ' ||
2688 ' from mtl_material_transactions_temp mmtt, ' ||
2689 ' po_line_locations_all pll , ' ||
2690 ' po_lines_all pl , ' ||
2691 ' po_headers_all ph , ' ||
2692 ' po_distributions_all pd,' ||
2693 ' po_req_distributions_all prd ,' ||
2694 ' po_requisition_lines_all prl ,' ||
2695 ' po_requisition_headers_all prh, ' ||
2696 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
2697 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2698 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2699 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2700 ' and prl.source_type_code = ''VENDOR''' ||
2701 ' and pd.req_distribution_id = prd.distribution_id' ||
2702 ' and pd.po_header_id=ph.po_header_id' ||
2703 ' AND pl.po_header_id = ph.po_header_id' ||
2704 ' AND pll.po_line_id = pl.po_line_id' ||
2705 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
2706 ' AND pll.line_location_id = pd.line_location_id' ||
2707 ' AND rt.po_line_location_id = pll.line_location_id' ||--Bug#6882986
2708 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2712 ' and prh.segment1 = '||''''||l_req_number||''''||
2709 ' and rt.transaction_id = mmtt.rcv_transaction_id '||
2710 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2711 ' and rsh.ship_to_org_id = '|| l_org_id ||
2713 ' and prl.line_num='||l_line_num||
2714 ' and prh.org_id = '||l_operating_id;
2715
2716
2717 p_sql(27) := ' SELECT DISTINCT ood.* ' ||
2718 ' from org_organization_definitions ood, ' ||
2719 ' financials_system_params_all fsp, ' ||
2720 ' po_line_locations_all pll , ' ||
2721 ' po_lines_all pl , ' ||
2722 ' po_headers_all ph , ' ||
2723 ' po_distributions_all pd,' ||
2724 ' po_req_distributions_all prd ,' ||
2725 ' po_requisition_lines_all prl ,' ||
2726 ' po_requisition_headers_all prh ' ||
2727 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2728 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2729 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2730 ' and prl.source_type_code = ''VENDOR''' ||
2731 ' and pd.req_distribution_id = prd.distribution_id' ||
2732 ' and pd.po_header_id=ph.po_header_id' ||
2733 ' AND pl.po_header_id = ph.po_header_id' ||
2734 ' AND pll.po_line_id = pl.po_line_id ' ||
2735 ' AND pll.line_location_id = pd.line_location_id' ||
2736 ' AND fsp.org_id = ph.org_id ' ||
2737 ' AND ood.organization_id in (fsp.inventory_organization_id ,
2738 pll.ship_to_organization_id) ' ||
2739 ' and prh.segment1 = '||''''||l_req_number||''''||
2740 ' and prl.line_num='||l_line_num||
2741 ' and prh.org_id = '||l_operating_id;
2742
2743
2744 p_sql(28) := ' SELECT DISTINCT mp.* ' ||
2745 ' from mtl_parameters mp ,' ||
2746 ' financials_system_params_all fsp,' ||
2747 ' po_requisition_lines_all prl ,' ||
2748 ' po_requisition_headers_all prh ' ||
2749 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2750 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2751 ' and prl.source_type_code = ''VENDOR''' ||
2752 ' AND fsp.org_id = prh.org_id ' ||
2753 ' AND mp.organization_id in (fsp.inventory_organization_id ,
2754 prl.destination_organization_id) ' ||
2755 ' and prh.segment1 = '||''''||l_req_number||''''||
2756 ' and prl.line_num='||l_line_num||
2757 ' and prh.org_id = '||l_operating_id;
2758
2759
2760 p_sql(29) := ' SELECT DISTINCT rp.* ' ||
2761 ' from rcv_parameters rp , ' ||
2762 ' financials_system_params_all fsp, ' ||
2763 ' po_requisition_lines_all prl ,' ||
2764 ' po_requisition_headers_all prh ' ||
2765 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2766 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2767 ' and prl.source_type_code = ''VENDOR''' ||
2768 ' AND fsp.org_id = prh.org_id ' ||
2769 ' AND (rp.organization_id = fsp.inventory_organization_id ' ||
2770 ' OR rp.organization_id = prl.destination_organization_id) ' ||
2771 ' and prh.segment1 = '||''''||l_req_number||''''||
2772 ' and prl.line_num='||l_line_num||
2773 ' and prh.org_id = '||l_operating_id;
2774
2775
2776 p_sql(30) := ' SELECT psp.* ' ||
2777 ' from po_system_parameters_all psp, ' ||
2778 ' po_requisition_lines_all prl ,' ||
2779 ' po_requisition_headers_all prh ' ||
2780 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2781 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2782 ' and prl.source_type_code = ''VENDOR''' ||
2783 ' and psp.org_id = prh.org_id ' ||
2784 ' and prh.segment1 = '||''''||l_req_number||''''||
2785 ' and prl.line_num='||l_line_num||
2786 ' and prh.org_id = '||l_operating_id;
2787
2788
2789 p_sql(31) := ' SELECT fsp.* ' ||
2790 ' from financials_system_params_all fsp, ' ||
2791 ' po_requisition_lines_all prl ,' ||
2792 ' po_requisition_headers_all prh ' ||
2793 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2794 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2795 ' and prl.source_type_code = ''VENDOR''' ||
2796 ' and fsp.org_id = prh.org_id ' ||
2797 ' and prh.segment1 = '||''''||l_req_number||''''||
2798 ' and prl.line_num='||l_line_num||
2799 ' and prh.org_id = '||l_operating_id;
2800
2801 p_sql(32) := ' SELECT msn.* ' ||
2802 ' from mtl_serial_numbers msn , ' ||
2803 ' mtl_unit_transactions mut , ' ||
2804 ' mtl_material_transactions mmt, ' ||
2805 ' po_line_locations_all pll , ' ||
2806 ' po_lines_all pl , ' ||
2807 ' po_headers_all ph , ' ||
2808 ' po_distributions_all pd,' ||
2809 ' po_req_distributions_all prd ,' ||
2810 ' po_requisition_lines_all prl ,' ||
2811 ' po_requisition_headers_all prh,' ||
2812 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
2813 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2814 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2815 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2816 ' and prl.source_type_code = ''VENDOR''' ||
2817 ' and pd.req_distribution_id = prd.distribution_id' ||
2818 ' and pd.po_header_id=ph.po_header_id' ||
2819 ' AND pl.po_header_id = ph.po_header_id' ||
2820 ' AND pll.po_line_id = pl.po_line_id ' ||
2821 ' AND pll.line_location_id = pd.line_location_id' ||
2822 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2823 ' AND mmt.transaction_source_type_id = 1 ' ||
2827 ' AND msn.serial_number = mut.serial_number ' ||
2824 ' AND mut.transaction_id = mmt.transaction_id ' ||
2825 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
2826 ' AND msn.current_organization_id = mut.organization_id ' ||
2828 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2829 ' and rt.transaction_id = mmt.rcv_transaction_id '||
2830 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2831 ' and rsh.ship_to_org_id = '|| l_org_id ||
2832 ' and prh.segment1 = '||''''||l_req_number||''''||
2833 ' and prl.line_num='||l_line_num||
2834 ' and prh.org_id = '||l_operating_id ||
2835 ' UNION ALL ' ||
2836 ' SELECT msn.* ' ||
2837 ' from mtl_serial_numbers msn , ' ||
2838 ' mtl_unit_transactions mut , ' ||
2839 ' mtl_material_transactions mmt, ' ||
2840 ' mtl_transaction_lot_numbers mtln, ' ||
2841 ' po_line_locations_all pll , ' ||
2842 ' po_lines_all pl , ' ||
2843 ' po_headers_all ph , ' ||
2844 ' po_distributions_all pd,' ||
2845 ' po_req_distributions_all prd ,' ||
2846 ' po_requisition_lines_all prl ,' ||
2847 ' po_requisition_headers_all prh, ' ||
2848 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
2849 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2850 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2851 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2852 ' and prl.source_type_code = ''VENDOR''' ||
2853 ' and pd.req_distribution_id = prd.distribution_id' ||
2854 ' and pd.po_header_id=ph.po_header_id' ||
2855 ' AND pl.po_header_id = ph.po_header_id' ||
2856 ' AND pll.po_line_id = pl.po_line_id ' ||
2857 ' AND pll.line_location_id = pd.line_location_id' ||
2858 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2859 ' AND mmt.transaction_source_type_id = 1 ' ||
2860 ' AND mtln.transaction_id = mmt.transaction_id ' ||
2861 ' AND mut.transaction_id = mtln.serial_transaction_id '||
2862 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
2863 ' AND msn.current_organization_id = mut.organization_id ' ||
2864 ' AND msn.serial_number = mut.serial_number ' ||
2865 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2866 ' and rt.transaction_id = mmt.rcv_transaction_id '||
2867 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2868 ' and rsh.ship_to_org_id = '|| l_org_id ||
2869 ' and prh.segment1 = '||''''||l_req_number||''''||
2870 ' and prl.line_num='||l_line_num||
2871 ' and prh.org_id = '||l_operating_id;
2872
2873
2874 p_sql(33) := ' SELECT msnt.* ' ||
2875 ' from mtl_serial_numbers_temp msnt , ' ||
2876 ' mtl_material_transactions_temp mmtt, ' ||
2877 ' po_line_locations_all pll , ' ||
2878 ' po_lines_all pl , ' ||
2879 ' po_headers_all ph , ' ||
2880 ' po_distributions_all pd,' ||
2881 ' po_req_distributions_all prd ,' ||
2882 ' po_requisition_lines_all prl ,' ||
2883 ' po_requisition_headers_all prh, ' ||
2884 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
2885 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2886 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2887 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2888 ' and prl.source_type_code = ''VENDOR''' ||
2889 ' and pd.req_distribution_id = prd.distribution_id' ||
2890 ' and pd.po_header_id=ph.po_header_id' ||
2891 ' AND pl.po_header_id = ph.po_header_id' ||
2892 ' AND pll.po_line_id = pl.po_line_id ' ||
2893 ' AND pll.line_location_id = pd.line_location_id' ||
2894 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
2895 ' AND msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
2896 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2897 ' and rt.transaction_id = mmtt.rcv_transaction_id '||
2898 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2899 ' and rsh.ship_to_org_id = '|| l_org_id ||
2900 ' and prh.segment1 = '||''''||l_req_number||''''||
2901 ' and prl.line_num='||l_line_num||
2902 ' and prh.org_id = '||l_operating_id||
2903 ' UNION ALL ' ||
2904 ' SELECT msnt.* ' ||
2905 ' from mtl_serial_numbers_temp msnt, ' ||
2906 ' mtl_material_transactions_temp mmtt, ' ||
2907 ' mtl_transaction_lots_temp mtln, ' ||
2908 ' po_line_locations_all pll , ' ||
2909 ' po_lines_all pl , ' ||
2910 ' po_headers_all ph , ' ||
2911 ' po_distributions_all pd,' ||
2912 ' po_req_distributions_all prd ,' ||
2913 ' po_requisition_lines_all prl ,' ||
2914 ' po_requisition_headers_all prh ,' ||
2915 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
2916 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2917 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2918 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2919 ' and prl.source_type_code = ''VENDOR''' ||
2920 ' and pd.req_distribution_id = prd.distribution_id' ||
2921 ' and pd.po_header_id=ph.po_header_id' ||
2922 ' AND pl.po_header_id = ph.po_header_id' ||
2923 ' AND pll.po_line_id = pl.po_line_id ' ||
2924 ' AND pll.line_location_id = pd.line_location_id' ||
2925 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
2926 ' AND mtln.transaction_temp_id = mmtt.transaction_temp_id ' ||
2927 ' AND msnt.transaction_temp_id = mtln.serial_transaction_temp_id
2928 ' ||
2929 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2930 ' and rt.transaction_id = mmtt.rcv_transaction_id '||
2931 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2932 ' and rsh.ship_to_org_id = '|| l_org_id ||
2936
2933 ' and prh.segment1 = '||''''||l_req_number||''''||
2934 ' and prl.line_num='||l_line_num||
2935 ' and prh.org_id = '||l_operating_id;
2937 p_sql(34) := ' SELECT msni.* ' ||
2938 ' from mtl_serial_numbers_interface msni , ' ||
2939 ' rcv_transactions_interface rti ' ||
2940 ' WHERE exists ' ||
2941 ' (SELECT 1 ' ||
2942 ' from po_line_locations_all pll , ' ||
2943 ' po_lines_all pl , ' ||
2944 ' po_headers_all ph , ' ||
2945 ' po_distributions_all pd,' ||
2946 ' po_req_distributions_all prd ,' ||
2947 ' po_requisition_lines_all prl ,' ||
2948 ' po_requisition_headers_all prh, ' ||
2949 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
2950 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2951 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2952 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2953 ' and prl.source_type_code = ''VENDOR''' ||
2954 ' and pd.req_distribution_id = prd.distribution_id' ||
2955 ' and pd.po_header_id=ph.po_header_id' ||
2956 ' AND pl.po_header_id = ph.po_header_id' ||
2957 ' AND pll.po_line_id = pl.po_line_id ' ||
2958 ' AND pll.line_location_id = pd.line_location_id' ||
2959 ' AND rti.po_header_id = ph.po_header_id' ||
2960 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
2961 ' and rsl.po_line_location_id = pll.line_location_id '||
2962 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2963 ' and rsh.ship_to_org_id = '|| l_org_id ||
2964 ' and prh.segment1 = '||''''||l_req_number||''''||
2965 ' and prl.line_num='||l_line_num||
2966 ' and prh.org_id = '||l_operating_id ||
2967 ' ) ' ||
2968 ' AND msni.product_transaction_id = rti.interface_transaction_id ';
2969
2970
2971 p_sql(35) := ' SELECT mut.* ' ||
2972 ' from mtl_unit_transactions mut , ' ||
2973 ' mtl_material_transactions mmt, ' ||
2974 ' po_line_locations_all pll , ' ||
2975 ' po_lines_all pl , ' ||
2976 ' po_headers_all ph , ' ||
2977 ' po_distributions_all pd,' ||
2978 ' po_req_distributions_all prd ,' ||
2979 ' po_requisition_lines_all prl ,' ||
2980 ' po_requisition_headers_all prh, ' ||
2981 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
2982 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
2983 ' and prh.requisition_header_id = prl.requisition_header_id' ||
2984 ' and prl.requisition_line_id = prd.requisition_line_id' ||
2985 ' and prl.source_type_code = ''VENDOR''' ||
2986 ' and pd.req_distribution_id = prd.distribution_id' ||
2987 ' and pd.po_header_id=ph.po_header_id' ||
2988 ' AND pl.po_header_id = ph.po_header_id' ||
2989 ' AND pll.po_line_id = pl.po_line_id ' ||
2990 ' AND pll.line_location_id = pd.line_location_id' ||
2991 ' and mmt.transaction_source_id = ph.po_header_id ' ||
2992 ' AND mmt.transaction_source_type_id = 1 ' ||
2993 ' AND mut.transaction_id = mmt.transaction_id ' ||
2994 ' and rsh.shipment_header_id = rt.shipment_header_id '||
2995 ' and rt.transaction_id = mmt.rcv_transaction_id '||
2996 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
2997 ' and rsh.ship_to_org_id = '|| l_org_id ||
2998 ' and prh.segment1 = '||''''||l_req_number||''''||
2999 ' and prl.line_num='||l_line_num||
3000 ' and prh.org_id = '||l_operating_id||
3001 ' UNION ALL ' ||
3002 ' SELECT mut.* ' ||
3003 ' from mtl_unit_transactions mut , ' ||
3004 ' mtl_material_transactions mmt , ' ||
3005 ' mtl_transaction_lot_numbers mtln, ' ||
3006 ' po_line_locations_all pll , ' ||
3007 ' po_lines_all pl , ' ||
3008 ' po_headers_all ph , ' ||
3009 ' po_distributions_all pd,' ||
3010 ' po_req_distributions_all prd ,' ||
3011 ' po_requisition_lines_all prl ,' ||
3012 ' po_requisition_headers_all prh, ' ||
3013 ' rcv_shipment_headers rsh, rcv_transactions rt ' ||
3014 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3015 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3016 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3017 ' and prl.source_type_code = ''VENDOR''' ||
3018 ' and pd.req_distribution_id = prd.distribution_id' ||
3019 ' and pd.po_header_id=ph.po_header_id' ||
3020 ' AND pl.po_header_id = ph.po_header_id' ||
3021 ' AND pll.po_line_id = pl.po_line_id ' ||
3022 ' AND pll.line_location_id = pd.line_location_id' ||
3023 ' and mmt.transaction_source_id = ph.po_header_id ' ||
3024 ' AND mmt.transaction_source_type_id = 1 ' ||
3025 ' AND mtln.transaction_id = mmt.transaction_id ' ||
3026 ' AND mut.transaction_id = mtln.serial_transaction_id '||
3027 ' and rsh.shipment_header_id = rt.shipment_header_id '||
3028 ' and rt.transaction_id = mmt.rcv_transaction_id '||
3029 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3030 ' and rsh.ship_to_org_id = '|| l_org_id ||
3031 ' and prh.segment1 = '||''''||l_req_number||''''||
3032 ' and prl.line_num='||l_line_num||
3033 ' and prh.org_id = '||l_operating_id;
3034
3035 p_sql(36) := ' SELECT rss.* ' ||
3036 ' from rcv_serials_supply rss , ' ||
3037 ' rcv_shipment_lines rsl , ' ||
3038 ' po_line_locations_all pll , ' ||
3039 ' po_lines_all pl , ' ||
3040 ' po_headers_all ph , ' ||
3041 ' po_distributions_all pd,' ||
3042 ' po_req_distributions_all prd ,' ||
3043 ' po_requisition_lines_all prl ,' ||
3044 ' po_requisition_headers_all prh, ' ||
3048 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3045 ' rcv_shipment_headers rsh ' ||
3046 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3047 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3049 ' and prl.source_type_code = ''VENDOR''' ||
3050 ' and pd.req_distribution_id = prd.distribution_id' ||
3051 ' and pd.po_header_id=ph.po_header_id' ||
3052 ' AND pl.po_header_id = ph.po_header_id' ||
3053 ' AND pll.po_line_id = pl.po_line_id ' ||
3054 ' AND pll.line_location_id = pd.line_location_id' ||
3055 ' and rsl.po_header_id = ph.po_header_id ' ||
3056 ' AND rss.shipment_line_id = rsl.shipment_line_id ' ||
3057 ' and rsh.shipment_header_id = rsl.shipment_header_id '||
3058 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3059 ' and rsh.ship_to_org_id = '|| l_org_id ||
3060 ' and prh.segment1 = '||''''||l_req_number||''''||
3061 ' and prl.line_num='||l_line_num||
3062 ' and prh.org_id = '||l_operating_id;
3063
3064 p_sql(37) := ' SELECT rst.* ' ||
3065 ' from rcv_serial_transactions rst , ' ||
3066 ' rcv_shipment_lines rsl , ' ||
3067 ' po_line_locations_all pll , ' ||
3068 ' po_lines_all pl , ' ||
3069 ' po_headers_all ph , ' ||
3070 ' po_distributions_all pd,' ||
3071 ' po_req_distributions_all prd ,' ||
3072 ' po_requisition_lines_all prl ,' ||
3073 ' po_requisition_headers_all prh, ' ||
3074 ' rcv_shipment_headers rsh ' ||
3075 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3076 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3077 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3078 ' and prl.source_type_code = ''VENDOR''' ||
3079 ' and pd.req_distribution_id = prd.distribution_id' ||
3080 ' and pd.po_header_id=ph.po_header_id' ||
3081 ' AND pl.po_header_id = ph.po_header_id' ||
3082 ' AND pll.po_line_id = pl.po_line_id ' ||
3083 ' AND pll.line_location_id = pd.line_location_id' ||
3084 ' and rsl.po_header_id = ph.po_header_id ' ||
3085 ' AND rst.shipment_line_id = rsl.shipment_line_id ' ||
3086 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
3087 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3088 ' and rsh.ship_to_org_id = '|| l_org_id ||
3089 ' and prh.segment1 = '||''''||l_req_number||''''||
3090 ' and prl.line_num='||l_line_num||
3091 ' and prh.org_id = '||l_operating_id;
3092
3093 p_sql(38) := ' SELECT rsi.* ' ||
3094 ' from rcv_serials_interface rsi , ' ||
3095 ' rcv_transactions_interface rti ' ||
3096 ' WHERE exists ' ||
3097 ' (SELECT 1 ' ||
3098 ' from po_line_locations_all pll , ' ||
3099 ' po_lines_all pl , ' ||
3100 ' po_headers_all ph , ' ||
3101 ' po_distributions_all pd,' ||
3102 ' po_req_distributions_all prd ,' ||
3103 ' po_requisition_lines_all prl ,' ||
3104 ' po_requisition_headers_all prh, ' ||
3105 ' rcv_shipment_headers rsh, rcv_shipment_lines rsl '||
3106 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3107 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3108 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3109 ' and prl.source_type_code = ''VENDOR''' ||
3110 ' and pd.req_distribution_id = prd.distribution_id' ||
3111 ' and pd.po_header_id=ph.po_header_id' ||
3112 ' AND pl.po_header_id = ph.po_header_id' ||
3113 ' AND pll.po_line_id = pl.po_line_id ' ||
3114 ' AND pll.line_location_id = pd.line_location_id' ||
3115 ' and rti.po_header_id = ph.po_header_id ' ||
3116 ' and rsl.shipment_header_id = rsh.shipment_header_id '||
3117 ' and rsl.po_line_location_id = pll.line_location_id ' ||
3118 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3119 ' and rsh.ship_to_org_id = '|| l_org_id ||
3120 ' and prh.segment1 = '||''''||l_req_number||''''||
3121 ' and prl.line_num='||l_line_num||
3122 ' and prh.org_id = '||l_operating_id||
3123 ' ) ' ||
3124 ' AND rsi.interface_transaction_id =
3125 rti.interface_transaction_id ';
3126
3127 p_sql(39) := ' SELECT mln.* ' ||
3128 ' from mtl_lot_numbers mln , ' ||
3129 ' mtl_transaction_lot_numbers mtln ,' ||
3130 ' mtl_material_transactions mmt, ' ||
3131 ' po_line_locations_all pll , ' ||
3132 ' po_lines_all pl , ' ||
3133 ' po_headers_all ph , ' ||
3134 ' po_distributions_all pd,' ||
3135 ' po_req_distributions_all prd ,' ||
3136 ' po_requisition_lines_all prl ,' ||
3137 ' po_requisition_headers_all prh, ' ||
3138 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
3139 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3140 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3141 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3142 ' and prl.source_type_code = ''VENDOR''' ||
3143 ' and pd.req_distribution_id = prd.distribution_id' ||
3144 ' and pd.po_header_id=ph.po_header_id' ||
3145 ' AND pl.po_header_id = ph.po_header_id' ||
3146 ' AND pll.po_line_id = pl.po_line_id ' ||
3147 ' AND pll.line_location_id = pd.line_location_id' ||
3148 ' and mmt.transaction_source_id = ph.po_header_id ' ||
3149 ' AND mmt.transaction_source_type_id = 1 ' ||
3150 ' AND mtln.transaction_id = mmt.transaction_id ' ||
3151 ' AND mtln.lot_number = mln.lot_number ' ||
3152 ' AND mtln.inventory_item_id = mln.inventory_item_id ' ||
3153 ' AND mtln.organization_id = mln.organization_id ' ||
3154 ' and rsh.shipment_header_id = rt.shipment_header_id '||
3155 ' and rt.transaction_id = mmt.rcv_transaction_id '||
3156 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3157 ' and rsh.ship_to_org_id = '|| l_org_id ||
3158 ' and prh.segment1 = '||''''||l_req_number||''''||
3159 ' and prl.line_num='||l_line_num||
3160 ' and prh.org_id = '||l_operating_id;
3161
3162
3163 p_sql(40) := ' SELECT mtln.* ' ||
3164 ' from mtl_transaction_lot_numbers mtln , ' ||
3165 ' mtl_material_transactions mmt , ' ||
3166 ' po_line_locations_all pll , ' ||
3167 ' po_lines_all pl , ' ||
3168 ' po_headers_all ph , ' ||
3169 ' po_distributions_all pd,' ||
3170 ' po_req_distributions_all prd ,' ||
3171 ' po_requisition_lines_all prl ,' ||
3172 ' po_requisition_headers_all prh, ' ||
3173 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
3174 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3175 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3176 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3177 ' and prl.source_type_code = ''VENDOR''' ||
3178 ' and pd.req_distribution_id = prd.distribution_id' ||
3179 ' and pd.po_header_id=ph.po_header_id' ||
3180 ' AND pl.po_header_id = ph.po_header_id' ||
3181 ' AND pll.po_line_id = pl.po_line_id ' ||
3182 ' AND pll.line_location_id = pd.line_location_id' ||
3183 ' and mmt.transaction_source_id = ph.po_header_id ' ||
3184 ' AND mmt.transaction_source_type_id = 1 ' ||
3185 ' AND mtln.transaction_id = mmt.transaction_id ' ||
3186 ' and rsh.shipment_header_id = rt.shipment_header_id '||
3187 ' and rt.transaction_id = mmt.rcv_transaction_id '||
3188 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3189 ' and rsh.ship_to_org_id = '|| l_org_id ||
3190 ' and prh.segment1 = '||''''||l_req_number||''''||
3191 ' and prl.line_num='||l_line_num||
3192 ' and prh.org_id = '||l_operating_id;
3193
3194
3195 p_sql(41) := ' SELECT mtli.* ' ||
3196 ' from mtl_transaction_lots_interface mtli , ' ||
3197 ' rcv_transactions_interface rti ' ||
3198 ' WHERE exists ' ||
3199 ' (SELECT 1 ' ||
3200 ' from po_line_locations_all pll , ' ||
3201 ' po_lines_all pl , ' ||
3202 ' po_headers_all ph , ' ||
3203 ' po_distributions_all pd,' ||
3204 ' po_req_distributions_all prd ,' ||
3205 ' po_requisition_lines_all prl ,' ||
3206 ' po_requisition_headers_all prh ' ||
3207 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3208 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3209 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3210 ' and prl.source_type_code = ''VENDOR''' ||
3211 ' and pd.req_distribution_id = prd.distribution_id' ||
3212 ' and pd.po_header_id=ph.po_header_id' ||
3213 ' AND pl.po_header_id = ph.po_header_id' ||
3214 ' AND pll.po_line_id = pl.po_line_id ' ||
3215 ' AND pll.line_location_id = pd.line_location_id' ||
3216 ' and rti.po_header_id = ph.po_header_id ' ||
3217 ' and prh.segment1 = '||''''||l_req_number||''''||
3218 ' and prl.line_num='||l_line_num||
3219 ' and prh.org_id = '||l_operating_id ||
3220 ' ) ' ||
3221 ' AND mtli.product_transaction_id = RTI.interface_transaction_id ';
3222
3223
3224 p_sql(42) := ' SELECT mtlt.* ' ||
3225 ' from mtl_transaction_lots_temp mtlt , ' ||
3226 ' mtl_material_transactions_temp mmtt , ' ||
3227 ' po_line_locations_all pll , ' ||
3228 ' po_lines_all pl , ' ||
3229 ' po_headers_all ph , ' ||
3230 ' po_distributions_all pd,' ||
3231 ' po_req_distributions_all prd ,' ||
3232 ' po_requisition_lines_all prl ,' ||
3233 ' po_requisition_headers_all prh, ' ||
3234 ' rcv_shipment_headers rsh, rcv_transactions rt' ||
3235 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3236 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3237 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3238 ' and prl.source_type_code = ''VENDOR''' ||
3239 ' and pd.req_distribution_id = prd.distribution_id' ||
3240 ' and pd.po_header_id=ph.po_header_id' ||
3241 ' AND pl.po_header_id = ph.po_header_id' ||
3242 ' AND pll.po_line_id = pl.po_line_id ' ||
3243 ' AND pll.line_location_id = pd.line_location_id' ||
3244 ' and mmtt.transaction_source_id = ph.po_header_id ' ||
3245 ' AND mmtt.transaction_source_type_id = 1 ' ||
3246 ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ' ||
3247 ' and rsh.shipment_header_id = rt.shipment_header_id '||
3248 ' and rt.transaction_id = mmtt.rcv_transaction_id '||
3249 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3250 ' and rsh.ship_to_org_id = '|| l_org_id ||
3251 ' and prh.segment1 = '||''''||l_req_number||''''||
3252 ' and prl.line_num='||l_line_num||
3253 ' and prh.org_id = '||l_operating_id;
3254
3255 p_sql(43) := ' SELECT rls.* ' ||
3256 ' from rcv_lots_supply rls , ' ||
3257 ' rcv_shipment_lines rsl, ' ||
3258 ' po_line_locations_all pll , ' ||
3259 ' po_lines_all pl , ' ||
3260 ' po_headers_all ph , ' ||
3261 ' po_distributions_all pd,' ||
3262 ' po_req_distributions_all prd ,' ||
3263 ' po_requisition_lines_all prl ,' ||
3264 ' po_requisition_headers_all prh, ' ||
3265 ' rcv_shipment_headers rsh ' ||
3266 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3267 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3268 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3269 ' and prl.source_type_code = ''VENDOR''' ||
3270 ' and pd.req_distribution_id = prd.distribution_id' ||
3271 ' and pd.po_header_id=ph.po_header_id' ||
3272 ' AND pl.po_header_id = ph.po_header_id' ||
3273 ' AND pll.po_line_id = pl.po_line_id ' ||
3274 ' AND pll.line_location_id = pd.line_location_id' ||
3275 ' and rsl.shipment_line_id = rls.shipment_line_id ' ||
3276 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
3277 ' and rsl.po_line_location_id=pll.line_location_id' ||
3278 ' AND rsl.po_header_id = ph.po_header_id ' ||
3279 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3280 ' and rsh.ship_to_org_id = '|| l_org_id ||
3281 ' and prh.segment1 = '||''''||l_req_number||''''||
3282 ' and prl.line_num='||l_line_num||
3283 ' and prh.org_id = '||l_operating_id;
3284
3285
3286 p_sql(44) := ' SELECT rlt.* ' ||
3287 ' from rcv_lot_transactions rlt , ' ||
3288 ' rcv_shipment_lines rsl , ' ||
3289 ' po_line_locations_all pll , ' ||
3290 ' po_lines_all pl , ' ||
3291 ' po_headers_all ph , ' ||
3292 ' po_distributions_all pd,' ||
3293 ' po_req_distributions_all prd ,' ||
3294 ' po_requisition_lines_all prl ,' ||
3295 ' po_requisition_headers_all prh, ' ||
3296 ' rcv_shipment_headers rsh ' ||
3297 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3298 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3299 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3300 ' and prl.source_type_code = ''VENDOR''' ||
3301 ' and pd.req_distribution_id = prd.distribution_id' ||
3302 ' and pd.po_header_id=ph.po_header_id' ||
3303 ' AND pl.po_header_id = ph.po_header_id' ||
3304 ' AND pll.po_line_id = pl.po_line_id ' ||
3305 ' AND pll.line_location_id = pd.line_location_id' ||
3306 ' and rsl.po_header_id = ph.po_header_id ' ||
3307 ' AND rsl.shipment_line_id = rlt.shipment_line_id ' ||
3308 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
3309 ' and rsl.po_line_location_id=pll.line_location_id' ||
3310 ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
3311 ' and rsh.ship_to_org_id = '|| l_org_id ||
3312 ' and prh.segment1 = '||''''||l_req_number||''''||
3313 ' and prl.line_num='||l_line_num||
3314 ' and prh.org_id = '||l_operating_id;
3315
3316 p_sql(45) := ' SELECT rli.* ' ||
3317 ' from rcv_lots_interface rli , ' ||
3318 ' rcv_transactions_interface rti ' ||
3319 ' WHERE rti.interface_transaction_id =
3320 rli.interface_transaction_id ' ||
3321 ' AND exists ' ||
3322 ' (SELECT 1 ' ||
3323 ' from po_line_locations_all pll , ' ||
3324 ' po_lines_all pl , ' ||
3325 ' po_headers_all ph , ' ||
3326 ' po_distributions_all pd,' ||
3327 ' po_req_distributions_all prd ,' ||
3328 ' po_requisition_lines_all prl ,' ||
3329 ' po_requisition_headers_all prh ' ||
3330 ' WHERE prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'')' ||
3331 ' and prh.requisition_header_id = prl.requisition_header_id' ||
3332 ' and prl.requisition_line_id = prd.requisition_line_id' ||
3333 ' and prl.source_type_code = ''VENDOR''' ||
3334 ' and pd.req_distribution_id = prd.distribution_id' ||
3335 ' and pd.po_header_id=ph.po_header_id' ||
3336 ' AND pl.po_header_id = ph.po_header_id' ||
3337 ' AND pll.po_line_id = pl.po_line_id ' ||
3338 ' AND pll.line_location_id = pd.line_location_id' ||
3339 ' and rti.po_header_id = ph.po_header_id ' ||
3340 ' and prh.segment1 = '||''''||l_req_number||''''||
3341 ' and prl.line_num='||l_line_num||
3342 ' and prh.org_id = '||l_operating_id||
3343 ' ) ';
3344
3345 RETURN;
3346 END;
3347
3348 END INV_DIAG_RCV_IPROC_COMMON;