[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RCV_IOT
Source
1 PACKAGE BODY inv_diag_rcv_iot AS
2 /* $Header: INVDR04B.pls 120.1 2007/12/19 16:26:59 ssadasiv noship $ */
3
4 ------------------------------------------------------------
5 -- procedure to initialize test datastructures
6 -- executes prior to test run
7 ------------------------------------------------------------
8 PROCEDURE init IS
9 BEGIN
10 -- test writer could insert special setup code here
11 null;
12 END init;
13
14 ------------------------------------------------------------
15 -- procedure to cleanup any test datastructures that were setup in the init
16 -- procedure call executes after test run
17 ------------------------------------------------------------
18 PROCEDURE cleanup IS
19 BEGIN
20 -- test writer could insert special cleanup code here
21 NULL;
22 END cleanup;
23
24 ------------------------------------------------------------
25 -- procedure to report test name back to framework
26 ------------------------------------------------------------
27 PROCEDURE getTestName(str OUT NOCOPY VARCHAR2) IS
28 BEGIN
29 str := 'Inter Org Transfer';
30 END getTestName;
31
32 ------------------------------------------------------------
33 -- procedure to report name back to framework
34 ------------------------------------------------------------
35 PROCEDURE getComponentName(str OUT NOCOPY VARCHAR2) IS
36 BEGIN
37 str := 'IOT';
38 END getComponentName;
39
40 ------------------------------------------------------------
41 -- procedure to report test description back to framework
42 ------------------------------------------------------------
43 PROCEDURE getTestDesc(str OUT NOCOPY VARCHAR2) IS
44 BEGIN
45 str := 'This diagnostic test will collect setup and transactional data for a specific interorg shipment.'||'<BR/>'||
46 'The data collected can aid in the resolution of issues involving interorg shipment.'||'<BR/>'||
47 '<b>Parameters</b>'||'<BR/>'||
48 'This script requires either the combination of Shipment Number and Organization Id or Receipt Number and '||
49 'Organization Id as mandatory parameters.'||'<BR/>'||'Shipment Line Number is optional.'||
50 ' Alternatively all the parameters can be entered at the same time.';
51 END getTestDesc;
52
53
54 ------------------------------------------------------------
55 -- procedure to provide/populate the default parameters for the test case.
56 ------------------------------------------------------------
57 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
58 tempInput JTF_DIAG_INPUTTBL;
59 BEGIN
60 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
61 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Organization Id','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
62 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Shipment Number','LOV-oracle.apps.inv.diag.lov.IOTNumberLov');
63 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Shipment Line Number','LOV-oracle.apps.inv.diag.lov.IOTlineLov');
64 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Receipt Number','LOV-oracle.apps.inv.diag.lov.IOTReceiptLov');
65 defaultInputValues := tempInput;
66 END getDefaultTestParams;
67
68 ------------------------------------------------------------
69 -- procedure to report test mode back to the framework
70 ------------------------------------------------------------
71 FUNCTION getTestMode RETURN NUMBER IS
72 BEGIN
73 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
74 END getTestMode;
75
76 ------------------------------------------------------------
77 -- procedure to execute the PLSQL test
78 -- the inputs needed for the test are passed in and a report object and CLOB are -- returned.
79 ------------------------------------------------------------
80 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
81 report OUT NOCOPY JTF_DIAG_REPORT,
82 reportClob OUT NOCOPY CLOB) IS
83 reportStr LONG; -- REPORT
84 c_username VARCHAR2(50); -- accept input for username
85 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
86 errStr VARCHAR2(4000); -- error message
87 fixInfo VARCHAR2(4000); -- fix tip
88 isFatal VARCHAR2(50); -- TRUE or FALSE
89 row_limit NUMBER;
90
91 sql_text VARCHAR2(6000);
92
93 iot1_sql INV_DIAG_RCV_PO_COMMON.sqls_list;
94
95 dummy_num NUMBER;
96 l_org_id rcv_shipment_headers.organization_id%TYPE;
97 l_receipt_num rcv_shipment_headers.receipt_num%TYPE;
98 l_shipment_num rcv_shipment_headers.shipment_num%TYPE;
99 l_shipment_line_num rcv_shipment_lines.line_num%TYPE;
100 l_makecall NUMBER := 0;
101 l_count NUMBER := 1;
102
103 BEGIN
104 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
105 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
106 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
107
108 -- accept input
109 l_shipment_num := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Shipment Number',inputs));
110 l_org_id := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs));
111 l_shipment_line_num := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Shipment Line Number',inputs));
112 l_receipt_num := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Receipt Number',inputs));
113
114
115 IF (l_shipment_num is NULL AND l_receipt_num is NULL AND l_shipment_line_num IS NULL AND l_org_id is NULL ) THEN
116
117 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
118 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
119 test');
120 statusStr := 'FAILURE';
121 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
122 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
123 l_makecall := 1;
124 RETURN;
125 END IF;
126
127
128 IF (l_shipment_num is NULL AND l_receipt_num is NULL AND l_org_id is NULL) THEN
129
130 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
131 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
132 test');
133 statusStr := 'FAILURE';
134 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
135 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
136 l_makecall := 1;
137 RETURN;
138 END IF;
139
140
141 IF (l_shipment_num is NOT NULL and l_org_id is NULL) THEN
142 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
143 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
144 test');
145 statusStr := 'FAILURE';
146 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
147 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
148 l_makecall := 1;
149 RETURN;
150 END IF;
151
152
153 IF (l_receipt_num is NOT NULL and l_org_id is NULL) THEN
154
155 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
156 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
157 test');
158 statusStr := 'FAILURE';
159 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
160 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
161 l_makecall := 1;
162 RETURN;
163 END IF;
164
165
166 IF (l_receipt_num is NULL and l_shipment_num is NULL and l_org_id is NOT NULL) THEN
167 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
168 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
169 test');
170 statusStr := 'FAILURE';
171 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
172 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
173 l_makecall := 1;
174 RETURN;
175 END IF;
176
177
178 IF (l_receipt_num is NULL and l_shipment_num is NULL and l_shipment_line_num is NOT NULL) THEN
179 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
180 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
181 test');
182 statusStr := 'FAILURE';
183 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
184 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
185 l_makecall := 1;
186 RETURN;
187 END IF;
188
189
190 IF (l_shipment_num is NOT NULL and l_receipt_num is NOT NULL and l_shipment_line_num is NOT NULL and l_org_id is NOT
191 NULL) THEN
192
193 select Count(*)
194 into l_count
195 from rcv_shipment_headers rsh,
196 rcv_shipment_lines rsl
197 where rsh.receipt_num = l_receipt_num
198 AND rsh.shipment_num = l_shipment_num
199 AND rsh.shipment_header_id = rsl.shipment_header_id
200 AND rsl.line_num = l_shipment_line_num
201 and rsh.ship_to_org_id = l_org_id
202 and rsh.receipt_source_code = 'INVENTORY';
203
204 END IF;
205
206
207 IF (l_shipment_num is NOT NULL and l_shipment_line_num is NOT NULL and l_org_id is NOT NULL AND l_count <> 0) THEN
208
209 select Count(*)
210 into l_count
211 from rcv_shipment_headers rsh,
212 rcv_shipment_lines rsl
213 where rsh.shipment_num = l_shipment_num
214 AND rsh.shipment_header_id = rsl.shipment_header_id
215 AND rsl.line_num = l_shipment_line_num
216 and rsh.ship_to_org_id = l_org_id
217 and rsh.receipt_source_code = 'INVENTORY';
218
219 END IF;
220
221 IF (l_receipt_num is NOT NULL and l_shipment_line_num is NOT NULL and l_org_id is NOT NULL AND l_count <> 0) THEN
222
223 select Count(*)
224 into l_count
225 from rcv_shipment_headers rsh,
226 rcv_shipment_lines rsl
227 where rsh.receipt_num = l_receipt_num
228 AND rsh.shipment_header_id = rsl.shipment_header_id
229 AND rsl.line_num = l_shipment_line_num
230 and rsh.ship_to_org_id = l_org_id
231 and rsh.receipt_source_code = 'INVENTORY';
232
233 END IF;
234
235 IF (l_shipment_num is NOT NULL and l_receipt_num is NOT NULL and l_org_id is NOT NULL AND l_count <> 0) THEN
236
237 select Count(*)
238 into l_count
239 from rcv_shipment_headers rsh
240 where rsh.receipt_num = l_receipt_num
241 AND rsh.shipment_num = l_shipment_num
242 and rsh.ship_to_org_id = l_org_id
243 and rsh.receipt_source_code = 'INVENTORY';
244
245 END IF;
246
247 IF (l_shipment_num is NOT NULL and l_org_id is NOT NULL AND l_count <> 0) THEN
248
249 select Count(*)
250 into l_count
251 from rcv_shipment_headers rsh
252 where rsh.shipment_num = l_shipment_num
253 and rsh.ship_to_org_id = l_org_id
254 and rsh.receipt_source_code = 'INVENTORY';
255
256 END IF;
257
258 IF (l_receipt_num is NOT NULL and l_org_id is NOT NULL AND l_count <> 0) THEN
259
260 select Count(*)
261 into l_count
262 from rcv_shipment_headers rsh
263 where rsh.receipt_num = l_receipt_num
264 and rsh.ship_to_org_id = l_org_id
265 and rsh.receipt_source_code = 'INVENTORY';
266
267 END IF;
268
269
270 IF (l_count = 0) THEN
271 statusStr := 'FAILURE';
272 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('No Data exists for the entered combination.');
273 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter a valid combination of data');
274 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
275 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
276 RETURN;
277 END IF;
278
279
280 IF (l_makecall <> 1) THEN
281
282 IF (l_shipment_num is NULL and l_receipt_num is NOT NULL and l_org_id is NOT NULL) THEN
283
284 begin
285
286 select shipment_num
287 into l_shipment_num
288 from rcv_shipment_headers rsh
289 where receipt_num = l_receipt_num
290 and ship_to_org_id = l_org_id
291 and rsh.receipt_source_code = 'INVENTORY';
292
293 exception
294 when no_data_found then
295 l_shipment_num := NULL;
296 end;
297 END IF;
298
299
300 IF (l_shipment_num is NOT NULL and l_receipt_num is NULL and l_org_id is NOT NULL) THEN
301
302 begin
303 select receipt_num
304 into l_receipt_num
305 from rcv_shipment_headers rsh
306 where shipment_num = l_shipment_num
307 and ship_to_org_id = l_org_id
308 and rsh.receipt_source_code = 'INVENTORY';
309
310 exception
311 when no_data_found then
312 l_receipt_num := NULL;
313 end;
314
315 END IF;
316
317 -------------------------- Fixed Sqls
318
319 IF (l_shipment_num is NOT NULL AND l_shipment_line_num is NULL) THEN
320 iot_diagnostics.shipment_num_sql(l_org_id,l_shipment_num,l_receipt_num,iot1_sql);
321 END IF;
322
323
324 IF (l_shipment_num is NOT NULL AND l_shipment_line_num is NOT NULL) THEN
325 iot_diagnostics.shipment_line_num_sql(l_org_id,l_shipment_num,l_shipment_line_num,l_receipt_num,iot1_sql);
326 END IF;
327
328 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
329 JTF_DIAGNOSTIC_COREAPI.insert_html('<table border="1" cellpadding="1" cellspacing="1" bgcolor="#f7f7e7">'||'
330 <tr bgcolor="#cccc99">'||'
331 <th colspan="3">INDEX OF QUERIES</th>'||'
332 <a name="INDEX OF QUERIES"></a></tr>'||'
333 <tr>'||'
334 <td><a href="#RCV_HEADERS_INTERFACE">RCV_HEADERS_INTERFACE</a></td>'||'
335 <td><a href="#RCV_TRANSACTIONS_INTERFACE">RCV_TRANSACTIONS_INTERFACE</a></td>'||'
336 <td><a href="#PO_INTERFACE_ERRORS">PO_INTERFACE_ERRORS</a></td>'||'
337 </tr>'||'
338 <tr>'||'
339 <td><a href="#RCV_SHIPMENT_HEADERS">RCV_SHIPMENT_HEADERS</a></td>'||'
340 <td><a href="#RCV_SHIPMENT_LINES">RCV_SHIPMENT_LINES</a></td>'||'
341 <td><a href="#RCV_TRANSACTIONS">RCV_TRANSACTIONS</a></td>'||'
342 </tr>'||'
343 <tr>'||'
344 <td><a href="#MTL_SUPPLY">MTL_SUPPLY</a></td>'||'
345 <td><a href="#RCV_SUPPLY">RCV_SUPPLY</a></td>'||'
346 <td><a href="#MTL_MATERIAL_TRANSACTIONS_TEMP">MTL_MATERIAL_TRANSACTIONS_TEMP</a></td>'||'
347 </tr>'||'
348 <tr>'||'
349 <td><a href="#MTL_MATERIAL_TRANSACTIONS">MTL_MATERIAL_TRANSACTIONS</a></td>'||'
350 <td><a href="#MTL_LOT_NUMBERSS">MTL_LOT_NUMBERS</a></td>'||'
351 <td><a href="#MTL_TRANSACTION_LOT_NUMBERS">MTL_TRANSACTION_LOT_NUMBERS</a></td>'||'
352 </tr>'||'
353 <tr>'||'
354 <td><a href="#MTL_TRANSACTION_LOTS_INTERFACE">MTL_TRANSACTION_LOTS_INTERFACE</a></td>'||'
355 <td><a href="#MTL_TRANSACTION_LOTS_TEMP">MTL_TRANSACTION_LOTS_TEMP</a></td>'||'
356 <td><a href="#RCV_LOTS_SUPPLY">RCV_LOTS_SUPPLY</a></td>'||'
357 </tr>'||'
358 <tr>'||'
362 </tr>'||'
359 <td><a href="#RCV_LOT_TRANSACTIONS">RCV_LOT_TRANSACTIONS</a></td>'||'
360 <td><a href="#RCV_LOTS_INTERFACE">RCV_LOTS_INTERFACE</a></td>'||'
361 <td><a href="#RCV_SERIALS_SUPPLY">RCV_SERIALS_SUPPLY</a></td>'||'
363 <tr>'||'
364 <td><a href="#RCV_SERIAL_TRANSACTIONS">RCV_SERIAL_TRANSACTIONS</a></td>'||'
365 <td><a href="#RCV_SERIALS_INTERFACE">RCV_SERIALS_INTERFACE</a></td>'||'
366 <td><a href="#MTL_SERIAL_NUMBERS">MTL_SERIAL_NUMBERS</a></td>'||'
367 </tr>'||'
368 <tr>'||'
369
370 <td><a href="#MTL_SERIAL_NUMBERS_TEMPE">MTL_SERIAL_NUMBERS_TEMP</a></td>'||'
371 <td><a href="#MTL_SERIAL_NUMBERS_INTERFACES">MTL_SERIAL_NUMBERS_INTERFACE</a></td>'||'
372 <td><a href="#MTL_UNIT_TRANSACTIONSS">MTL_UNIT_TRANSACTIONS</a></td>'||'
373 </tr>'||'
374 <tr>'||'
375 <td><a href="#MTL_SYSTEM_ITEMS">MTL_SYSTEM_ITEMS</a></td>'||'
376 <td><a href="#MTL_TRANSACTION_TYPES">MTL_TRANSACTION_TYPES</a></td>'||'
377 <td><a href="#ORG_ORG_DEFINIATIONS">ORG_ORG_DEFINIATIONS</a></td>'||'
378 </tr>'||'
379 <tr>'||'
380 <td><a href="#MTL_INTERORG_PARAMETERS">MTL_INTERORG_PARAMETERS</a></td>'||'
381 <td><a href="MTL_PARAMETERS">MTL_PARAMETERS</a></td>'||'
382 <td><a href="#RCV_PARAMETERS">RCV_PARAMETERS</a></td>'||'
383 </tr>'||'
384 <tr>'||'
385 <td><a href="#LOOKUP CODES">LOOKUP CODES</a></td>'||'
386 </tr>'||'
387 </table>');
388
389 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Receipt Information</h4>');
390 sql_text := iot1_sql(1);
391 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_HEADERS_INTERFACE"></a>');
392 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_HEADERS_INTERFACE</b> <a href="#INDEX OF QUERIES">[Top]</a>');
393 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
394 JTF_DIAGNOSTIC_COREAPI.BRPrint;
395
396 sql_text := iot1_sql(2);
397 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_TRANSACTIONS_INTERFACE"></a>');
398 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_TRANSACTIONS_INTERFACE</b> <a href="#INDEX OF
399 QUERIES">[Top]</a>');
400 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
401 JTF_DIAGNOSTIC_COREAPI.BRPrint;
402
403 sql_text := iot1_sql(3);
404 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="PO_INTERFACE_ERRORS"></a>');
405 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>PO_INTERFACE_ERRORS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
406 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
407 JTF_DIAGNOSTIC_COREAPI.BRPrint;
408
409
410 sql_text := iot1_sql(4);
411 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_HEADERS"></a>');
412 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_HEADERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
413 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
414 JTF_DIAGNOSTIC_COREAPI.BRPrint;
415
416
417 sql_text := iot1_sql(5);
418 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_LINES"></a>');
419 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_LINES</b> <a href="#INDEX OF QUERIES">[Top]</a>');
420 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
421 JTF_DIAGNOSTIC_COREAPI.BRPrint;
422
423
424 sql_text:= iot1_sql(6);
425 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_TRANSACTIONS"></a>');
426 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_TRANSACTIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
427 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
428 JTF_DIAGNOSTIC_COREAPI.BRPrint;
429
430 sql_text := iot1_sql(7);
431 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SUPPLY"></a>');
432 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SUPPLY</b> <a href="#INDEX OF QUERIES">[Top]</a>');
433 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
434 JTF_DIAGNOSTIC_COREAPI.BRPrint;
435
436 sql_text := iot1_sql(8);
437 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SUPPLY"></a>');
438 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SUPPLY</b> <a href="#INDEX OF QUERIES">[Top]</a>');
439 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'RCV_SUPPLY','Y','Y');
440 JTF_DIAGNOSTIC_COREAPI.BRPrint;
441
442
443 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Inventory Information</h4>');
444 sql_text := iot1_sql(9);
445 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_MATERIAL_TRANSACTIONS_TEMP"></a>');
446 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_MATERIAL_TRANSACTIONS_TEMP</b> <a href="#INDEX OF
447 QUERIES">[Top]</a>');
448 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
449 JTF_DIAGNOSTIC_COREAPI.BRPrint;
450
451
452 sql_text:= iot1_sql(10);
453 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_MATERIAL_TRANSACTIONS"></a>');
454 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_MATERIAL_TRANSACTIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
455 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
456 JTF_DIAGNOSTIC_COREAPI.BRPrint;
457
458 sql_text:= iot1_sql(11);
459 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_LOT_NUMBERS"></a>');
460 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_LOT_NUMBERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
461 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
462 JTF_DIAGNOSTIC_COREAPI.BRPrint;
463
464 sql_text:= iot1_sql(12);
465 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOT_NUMBERS"></a>');
466 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOT_NUMBERS</b> <a href="#INDEX OF
467 QUERIES">[Top]</a>');
468 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
469 JTF_DIAGNOSTIC_COREAPI.BRPrint;
470
471 sql_text:= iot1_sql(13);
472 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOT_INTERFACE"></a>');
473 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOT_INTERFACE</b> <a href="#INDEX OF
474 QUERIES">[Top]</a>');
475 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
476 JTF_DIAGNOSTIC_COREAPI.BRPrint;
477
478 sql_text:= iot1_sql(14);
482 JTF_DIAGNOSTIC_COREAPI.BRPrint;
479 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOTS_TEMP"></a>');
480 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOTS_TEMP</b> <a href="#INDEX OF QUERIES">[Top]</a>');
481 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
483
484 sql_text:= iot1_sql(15);
485 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_LOTS_SUPPLY"></a>');
486 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_LOTS_SUPPLY</b> <a href="#INDEX OF QUERIES">[Top]</a>');
487 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
488 JTF_DIAGNOSTIC_COREAPI.BRPrint;
489
490 sql_text:= iot1_sql(16);
491 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_LOT_TRANSACTIONS"></a>');
492 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_LOT_TRANSACTIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
493 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
494 JTF_DIAGNOSTIC_COREAPI.BRPrint;
495
496 sql_text:= iot1_sql(17);
497 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_LOTS_INTERFACE"></a>');
498 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_LOTS_INTERFACE</b> <a href="#INDEX OF QUERIES">[Top]</a>');
499 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
500 JTF_DIAGNOSTIC_COREAPI.BRPrint;
501
502 sql_text:= iot1_sql(18);
503 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SERIAL_SUPPLY"></a>');
504 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SERIAL_SUPPLY</b> <a href="#INDEX OF QUERIES">[Top]</a>');
505 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
506 JTF_DIAGNOSTIC_COREAPI.BRPrint;
507
508 sql_text:= iot1_sql(19);
509 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SERIAL_TRANSACTIONS"></a>');
510 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SERIAL_TRANSACTIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
511 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
512 JTF_DIAGNOSTIC_COREAPI.BRPrint;
513
514 sql_text:= iot1_sql(20);
515 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SERIALS_INTERFACE"></a>');
516 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SERIALS_INTERFACE</b> <a href="#INDEX OF QUERIES">[Top]</a>');
517 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
518 JTF_DIAGNOSTIC_COREAPI.BRPrint;
519
520 sql_text:= iot1_sql(21);
521 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS"></a>');
522 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
523 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
524 JTF_DIAGNOSTIC_COREAPI.BRPrint;
525
526 sql_text:= iot1_sql(22);
527 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS_TEMP"></a>');
528 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS_TEMP</b> <a href="#INDEX OF QUERIES">[Top]</a>');
529 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
530 JTF_DIAGNOSTIC_COREAPI.BRPrint;
531
532 sql_text:= iot1_sql(23);
533 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS_INTERFACE"></a>');
534 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS_INTERFACE</b> <a href="#INDEX OF
535 QUERIES">[Top]</a>');
536 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
537 JTF_DIAGNOSTIC_COREAPI.BRPrint;
538
539 sql_text:= iot1_sql(24);
540 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_UNIT_TRANSACTIONS"></a>');
541 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_UNIT_TRANSACTIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
542 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
543 JTF_DIAGNOSTIC_COREAPI.BRPrint;
544
545 sql_text:= iot1_sql(25);
546 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SYSTEM_ITEMS"></a>');
547 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SYSTEM_ITEMS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
548 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
549 JTF_DIAGNOSTIC_COREAPI.BRPrint;
550
551 sql_text:= iot1_sql(26);
552 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_TYPES"></a>');
553 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_TYPES</b> <a href="#INDEX OF QUERIES">[Top]</a>');
554 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
555 JTF_DIAGNOSTIC_COREAPI.BRPrint;
556
557 sql_text:= iot1_sql(27);
558 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="ORG_ORG_DEFINITIONS"></a>');
559 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>ORG_ORG_DEFINITIONS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
560 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
561 JTF_DIAGNOSTIC_COREAPI.BRPrint;
562
563 sql_text:= iot1_sql(28);
564 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_INTERORG_PARAMETERS"></a>');
565 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_INTERORG_PARAMETERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
566 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
567 JTF_DIAGNOSTIC_COREAPI.BRPrint;
568
569 sql_text:= iot1_sql(29);
570 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_PARAMETERS"></a>');
571 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_PARAMETERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
572 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
573 JTF_DIAGNOSTIC_COREAPI.BRPrint;
574
575 sql_text:= iot1_sql(30);
576 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_PARAMETERS"></a>');
577 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_PARAMETERS</b> <a href="#INDEX OF QUERIES">[Top]</a>');
578 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
579 JTF_DIAGNOSTIC_COREAPI.BRPrint;
580
581 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="LOOKUP CODES"></a>');
582 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Lookup Codes</h4>');
583 sql_text:= iot1_sql(31);
584 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Lot Control"></a>');
585 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Lot Control</b> <a href="#INDEX OF QUERIES">[Top]</a>');
586 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
587 JTF_DIAGNOSTIC_COREAPI.BRPrint;
591 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Lot Generation</b> <a href="#INDEX OF QUERIES">[Top]</a>');
588
589 sql_text:= iot1_sql(32);
590 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Lot Generation"></a>');
592 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
593 JTF_DIAGNOSTIC_COREAPI.BRPrint;
594
595 sql_text:= iot1_sql(33);
596 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="LOT Uniqueness"></a>');
597 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>LOT Uniqueness</b> <a href="#INDEX OF QUERIES">[Top]</a>');
598 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
599 JTF_DIAGNOSTIC_COREAPI.BRPrint;
600
601 sql_text:= iot1_sql(34);
602 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Control"></a>');
603 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Control</b> <a href="#INDEX OF QUERIES">[Top]</a>');
604 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
605 JTF_DIAGNOSTIC_COREAPI.BRPrint;
606
607
608 sql_text:= iot1_sql(35);
609 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Uniqueness"></a>');
610 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Uniqueness</b> <a href="#INDEX OF QUERIES">[Top]</a>');
611 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
612 JTF_DIAGNOSTIC_COREAPI.BRPrint;
613
614
615 sql_text:= iot1_sql(36);
616 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Generation"></a>');
617 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Generation</b> <a href="#INDEX OF QUERIES">[Top]</a>');
618 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
619 JTF_DIAGNOSTIC_COREAPI.BRPrint;
620
621 sql_text:= iot1_sql(37);
622 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Number Status"></a>');
623 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Number Status</b> <a href="#INDEX OF QUERIES">[Top]</a>');
624 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
625 JTF_DIAGNOSTIC_COREAPI.BRPrint;
626
627
628 END IF;
629
630 ------------------------- Submit the sql to fwk
631
632 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
633 statusStr := 'SUCCESS';
634 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
635 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
636
637 EXCEPTION
638 when others then
639 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
640 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
641 statusStr := 'FAILURE';
642 errStr := sqlerrm ||' occurred in script Exception handled';
643 fixInfo := 'Unexpected Exception in INVDR01B.pls';
644 isFatal := 'FALSE';
645 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
646 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
647 END runTest;
648
649
650 END INV_DIAG_RCV_IOT;