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

Source


1 PACKAGE BODY inv_diag_rcv_rma AS
2 /* $Header: INVDR02B.pls 120.2 2008/02/19 06:13:15 srnatara 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 := 'Return Material Authorization';
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 := 'RMA';
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 return material authorization.'||'<BR/>'||
46          'The data collected can aid in the resolution of issues involving return material authorization.'||'<BR/>'||
47          '<b>Parameters</b>'||'<BR/>'||
48          'This script requires either the combination of Rma Number and Operating Unit Id or Receipt Number and '||
49          ' Organization Id as mandatory parameters.'||'<BR/>'||'Rma 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 
62 /* Bug#6828508:
63   * OperatingLov is wrongly referenced as 'OperatingUnit Id'.
64   * So, changed 'OperatingUnit Id' to 'Operating Unit Id' as mentioned
65   * in OperatingLov.
66   */
67   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Operating Unit Id','LOV-oracle.apps.inv.diag.lov.OperatingLov');--Bug#6828508
68   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'RMA Number','LOV-oracle.apps.inv.diag.lov.RMANumberLov');
69   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'RMA Line Number','LOV-oracle.apps.inv.diag.lov.RMAlineLov');
70   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Organization Id','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
71   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Receipt Number','LOV-oracle.apps.inv.diag.lov.RMAReceiptLov');
72   defaultInputValues := tempInput;
73 END getDefaultTestParams;
74 
75 ------------------------------------------------------------
76 -- procedure to report test mode back to the framework
77 ------------------------------------------------------------
78 FUNCTION getTestMode RETURN NUMBER IS
79 BEGIN
80   return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
81 END getTestMode;
82 
83 ------------------------------------------------------------
84 -- procedure to execute the PLSQL test
85 -- the inputs needed for the test are passed in and a report object and CLOB are -- returned.
86 ------------------------------------------------------------
87 PROCEDURE runtest(inputs IN  JTF_DIAG_INPUTTBL,
88                           report OUT NOCOPY JTF_DIAG_REPORT,
89                           reportClob OUT NOCOPY CLOB) IS
90    reportStr   LONG;           -- REPORT
91    c_username  VARCHAR2(50);   -- accept input for username
92    statusStr   VARCHAR2(50);   -- SUCCESS or FAILURE
93    errStr      VARCHAR2(4000); -- error message
94    fixInfo     VARCHAR2(4000); -- fix tip
95    isFatal     VARCHAR2(50);   -- TRUE or FALSE
96    row_limit   NUMBER;
97 
98    sql_text VARCHAR2(6000);
99    skip_execution NUMBER := 0;
100    l_rma_count  number := 0;
101 
102    rma1_sql INV_DIAG_RCV_PO_COMMON.sqls_list;
103 
104    dummy_num        NUMBER;
105    l_operating_id   po_headers_all.org_id%TYPE;
106    l_rma_number      po_headers_all.segment1%TYPE;
107    l_line_num       po_lines_all.line_num%TYPE;
108    l_org_id         rcv_shipment_headers.organization_id%TYPE;
109    l_line_loc_num   po_line_locations_all.shipment_num%TYPE;
110    l_receipt_num    rcv_shipment_headers.receipt_num%TYPE;
111  BEGIN
112   JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
113   JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
114   JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
115   -- accept input
116   l_rma_number := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('RMA Number',inputs));
117   l_operating_id := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Operating Unit Id',inputs));--Bug#6828508
118   l_line_num :=Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('RMA Line Number',inputs));
119   l_receipt_num :=Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Receipt Number',inputs));
120   l_org_id := Trim(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs));
121 
122 IF l_rma_number IS NULL THEN
123 
124    IF (l_receipt_num IS NULL OR l_org_id IS NULL) THEN
125       JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
126       JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
127 test');
128       statusStr  := 'FAILURE';
129       --errStr     := 'Some of the Required inputs are missing';
130       --fixInfo    := 'Please rerun the Diagnostics using either Purchase order number or Receipt number ';
131       --isFatal := 'SUCCESS';
132       report     := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
133       reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
134       skip_execution := 1;
135       RETURN;
136    END IF;
137 ELSIF l_operating_id IS NULL THEN
138 
139   JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
140       JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
141 test');
142       statusStr  := 'FAILURE';
143       --errStr     := 'Some of the Required inputs are missing';
144       --fixInfo    := 'Please rerun the Diagnostics using either Purchase order number or Receipt number ';
145       --isFatal := 'SUCCESS';
146       report     := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
147       reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
148     skip_execution := 1;
149     RETURN;
150 
151 ELSE
152 IF (l_receipt_num IS NOT NULL AND l_org_id IS NULL) THEN
153 JTF_DIAGNOSTIC_COREAPI.ErrorPrint('Some of the required inputs are missing');
154       JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please refer to the parameters section for mandatory parameters for this
155 test');
156       statusStr  := 'FAILURE';
157       --errStr     := 'Some of the Required inputs are missing';
158       --fixInfo    := 'Please rerun the Diagnostics using either Purchase order number or Receipt number ';
159       --isFatal := 'SUCCESS';
160       report     := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
161       reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
162       skip_execution := 1;
163       RETURN;
164 END IF;
165 END IF;
166 
167 -- checking for valid data
168 if skip_execution <> 1 then
169 if l_rma_number is not null and l_operating_id is not null then
170  begin
171    select header_id
172    into l_rma_count
173    from oe_order_headers_all
174    where org_id = l_operating_id
175    and order_number = l_rma_number;
176  EXCEPTION
177   WHEN No_Data_Found THEN
178    statusStr    := 'FAILURE';
179    JTF_DIAGNOSTIC_COREAPI.ErrorPrint('No Data exists for the entered combination.');
180    JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter valid RMA number and Operating unit id');
181    report       := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
182    reportClob   := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
183    RETURN;
184    skip_execution := 1;
185   end;
186 end if;
187 
188 if l_receipt_num is not null and l_org_id is not null then
189  begin
190    select shipment_header_id
191    into l_rma_count
192    from rcv_shipment_headers
193    where receipt_num = l_receipt_num
194    and ship_to_org_id = l_org_id;
195  EXCEPTION
196   WHEN No_Data_Found THEN
197    statusStr    := 'FAILURE';
198    JTF_DIAGNOSTIC_COREAPI.ErrorPrint('No Data exists for the entered combination.');
199    JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter valid Receipt number and Organization id');
200    report       := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
201    reportClob   := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
202    RETURN;
203   skip_execution := 1;
204  end;
205 end if;
206 
207 end if; --skp_execption
208 
209 
210 -------------------------- Fixed Sqls
211 if skip_execution <> 1 then
212 IF l_rma_number IS NOT NULL AND l_operating_id IS NOT NULL AND
213    l_receipt_num IS NULL AND l_org_id IS NULL AND l_line_num IS NULL then
214      rma_diagnostics.rma_sql(l_operating_id,l_rma_number,rma1_sql);
215 END IF;
216 
217 IF l_rma_number IS NOT NULL AND l_operating_id IS NOT NULL AND l_line_num IS NOT NULL
218    AND l_receipt_num IS NULL AND l_org_id IS NULL THEN
219    rma_diagnostics.rma_line_sql(l_operating_id,l_rma_number,l_line_num,rma1_sql);
220  END IF;
221 
222 IF l_rma_number IS NOT NULL AND l_operating_id IS NOT NULL
223    AND l_receipt_num IS NOT NULL AND l_org_id IS NOT NULL THEN
224  rma_diagnostics.rma_receipt_sql(l_operating_id,l_rma_number,l_receipt_num,l_org_id,rma1_sql) ;
225  END IF;
226 
227 IF l_receipt_num IS NOT NULL AND l_org_id IS NOT NULL AND
228    l_rma_number IS NOT NULL AND l_operating_id IS NOT NULL AND l_line_num IS NOT NULL  THEN
229  rma_rcv_diagnostics.rma_line_receipt_sql( l_operating_id,l_rma_number,l_line_num,l_receipt_num,l_org_id,rma1_sql);
230 END IF;
231 
232 IF l_receipt_num IS NOT NULL AND l_org_id IS NOT NULL AND
233    l_rma_number IS NULL AND l_line_num IS NULL THEN
234 rma_rcv_diagnostics.receipt_sql(l_receipt_num,l_org_id,rma1_sql);
235 END IF;
236 
237 INV_DIAG_RCV_RCV_COMMON.build_lookup_codes(rma1_sql);
238 END IF;
239 
240 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
241 
242 JTF_DIAGNOSTIC_COREAPI.insert_html('<table border="1" cellpadding="1" cellspacing="1" bgcolor="#f7f7e7">'||'
243 <tr bgcolor="#cccc99">'||'
244 <th colspan="3">INDEX OF QUERIES</th>'||'
245 <a name="INDEX OF QUERIES"></a></tr>'||'
246 <tr>'||'
247 <td><a href="#OE_ORDER_HEADERS_ALL">OE_ORDER_HEADERS_ALL</a></td>'||'
248 <td><a href="#OE_ORDER_LINES_ALL">OE_ORDER_LINES_ALL</a></td>'||'
249 <td><a href="#MTL_SYSTEM_ITEMS">MTL_SYSTEM_ITEMS</a></td>'||'
250 </tr>'||'
251 <tr>'||'
252 <td><a href="#RCV_SHIPMENT_HEADERS">RCV_SHIPMENT_HEADERS</a></td>'||'
253 <td><a href="#RCV_SHIPMENT_LINES">RCV_SHIPMENT_LINES</a></td>'||'
254 <td><a href="#RCV_TRANSACTIONS">RCV_TRANSACTIONS</a></td>'||'
255 </tr>'||'
256 <tr>'||'
257 <td><a href="#RCV_HEADERS_INTERFACE">RCV_HEADERS_INTERFACE</a></td>'||'
258 <td><a href="#RCV_TRANSACTIONS_INTERFACE">RCV_TRANSACTIONS_INTERFACE</a></td>'||'
259 <td><a href="#PO_INTERFACE_ERRORS">PO_INTERFACE_ERRORS</a></td>'||'
260 </tr>'||'
261 <tr>'||'
262 <td><a href="#ORG_ORG_DEFINITIONS">ORG_ORG_DEFINITIONS</a></td>'||'
263 <td><a href="#MTL_PARAMETERS">MTL_PARAMETERS</a></td>'||'
264 <td><a href="#MTL_MATERIAL_TRANSACTIONS">MTL_MATERIAL_TRANSACTIONS</a></td>'||'
265 </tr>'||'
266 <tr>'||'
267 <td><a href="#MTL_TRANSACTION_TYPES">MTL_TRANSACTION_TYPES</a></td>'||'
268 <td><a href="#MTL_TRANSACTION_REQUEST_LINES">MTL_TRANSACTION_REQUEST_LINES</a></td>'||'
269 <td><a href="#MTL_MATERIAL_TRANSACTIONS_TEMP">MTL_MATERIAL_TRANSACTIONS_TEMP</a></td>'||'
270 </tr>'||'
271 <tr>'||'
272 <td><a href="#OE_LOT_SERIAL_NUMBERS">OE_LOT_SERIAL_NUMBERS</a></td>'||'
273 <td><a href="#MTL_SERIAL_NUMBERS">MTL_SERIAL_NUMBERS</a></td>'||'
274 <td><a href="#MTL_SERIAL_NUMBERS_TEMP">MTL_SERIAL_NUMBERS_TEMP</a></td>'||'
275 </tr>'||'
276 <tr>'||'
277 <td><a href="#MTL_SERIAL_NUMBERS_INTERFACE">MTL_SERIAL_NUMBERS_INTERFACE</a></td>'||'
278 <td><a href="#MTL_UNIT_TRANSACTIONS">MTL_UNIT_TRANSACTIONS</a></td>'||'
279 <td><a href="#RCV_SERIAL_TRANSACTIONS">RCV_SERIAL_TRANSACTIONS</a></td>'||'
280 </tr>'||'
281 <tr>'||'
282 
283 <td><a href="#RCV_SERIAL_INTERFACE">RCV_SERIAL_INTERFACE</a></td>'||'
284 <td><a href="#MTL_PARAMETERS">MTL_PARAMETERS</a></td>'||'
285 <td><a href="#MTL_LOT_NUMBERS">MTL_LOT_NUMBERS</a></td>'||'
286 </tr>'||'
287 <tr>'||'
288 <td><a href="#MTL_TRANSACTION_LOT_NUMBERS">MTL_TRANSACTION_LOT_NUMBERS</a></td>'||'
289 <td><a href="#MTL_TRANSACTION_LOT_INTERFACE">MTL_TRANSACTION_LOT_INTERFACE</a></td>'||'
290 <td><a href="#MTL_TRANSACTION_LOTS_TEMP">MTL_TRANSACTION_LOTS_TEMP</a></td>'||'
291 </tr>'||'
292 <tr>'||'
293 <td><a href="#RCV_LOT_TRANSACTIONS">RCV_LOT_TRANSACTIONS</a></td>'||'
294 <td><a href="#RCV_PARAMETERS">RCV_PARAMETERS</a></td>'||'
295 <td><a href="#PO_SYSTEM_PARAMETERS_ALL">PO_SYSTEM_PARAMETERS_ALL</a></td>'||'
296 </tr>'||'
297 <tr>'||'
298 <td><a href="#FINANCIAL_SYSTEM_PARAMETERS">FINANCIAL_SYSTEM_PARAMETERS</a></td>'||'
299 <td><a href="#LOOKUP CODES">LOOKUP CODES</a></td>'||'
300 </tr>'||'
301 </table>');
302 
303 
304 IF skip_execution <> 1 THEN
305 
306 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>RMA Information</h4>');
307 
308 sql_text := rma1_sql(1);
309 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="OE_ORDER_HEADERS_ALL"></a>');
310 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>OE_ORDER_HEADERS_ALL</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
311 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
312 JTF_DIAGNOSTIC_COREAPI.BRPrint;
313 
314 sql_text := rma1_sql(2);
315 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="OE_ORDER_LINES_ALL"></a>');
316 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>OE_ORDER_LINES_ALL</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
317 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
318 JTF_DIAGNOSTIC_COREAPI.BRPrint;
319 
320 
321 sql_text := rma1_sql(3);
322 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SYSTEM_ITEMS"></a>');
323 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SYSTEM_ITEMS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
324 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
325 JTF_DIAGNOSTIC_COREAPI.BRPrint;
326 
327 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Receipt Information</h4>');
328 
329 sql_text := rma1_sql(4);
330 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_HEADERS"></a>');
331 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_HEADERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
332 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
333 JTF_DIAGNOSTIC_COREAPI.BRPrint;
334 
335 sql_text := rma1_sql(5);
336 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_LINES"></a>');
337 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_LINES</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
338 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
339 JTF_DIAGNOSTIC_COREAPI.BRPrint;
340 
341 sql_text:= rma1_sql(6);
342 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_TRANSACTIONS"></a>');
343 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_TRANSACTIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
344 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
345 JTF_DIAGNOSTIC_COREAPI.BRPrint;
346 
347 sql_text  := rma1_sql(7);
348 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_HEADERS_INTERFACE"></a>');
352 
349 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_HEADERS_INTERFACE</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
350 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
351 JTF_DIAGNOSTIC_COREAPI.BRPrint;
353 sql_text := rma1_sql(8);
354 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_TRANSACTIONS_INTERFACE"></a>');
355 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_TRANSACTIONS_INTERFACE</b>  <a href="#INDEX OF
356 QUERIES">[Top]</a>');
357 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
358 JTF_DIAGNOSTIC_COREAPI.BRPrint;
359 
360 sql_text := rma1_sql(9);
361 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="PO_INTERFACE_ERRORS"></a>');
362 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>PO_INTERFACE_ERRORS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
363 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
364 JTF_DIAGNOSTIC_COREAPI.BRPrint;
365 
366 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Inventory Information</h4>');
367 
368 sql_text:= rma1_sql(10);
369 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="ORG_ORG_DEFINITIONS"></a>');
370 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>ORG_ORG_DEFINITIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
371 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
372 JTF_DIAGNOSTIC_COREAPI.BRPrint;
373 
374 sql_text:= rma1_sql(11);
375 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_PARAMETERS"></a>');
376 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_PARAMETERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
377 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
378 JTF_DIAGNOSTIC_COREAPI.BRPrint;
379 
380 sql_text:= rma1_sql(12);
381 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_MATERIAL_TRANSACTIONS"></a>');
382 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_MATERIAL_TRANSACTIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
383 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
384 JTF_DIAGNOSTIC_COREAPI.BRPrint;
385 
386 sql_text:= rma1_sql(13);
387 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_TYPES"></a>');
388 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_TYPES</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
389 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
390 JTF_DIAGNOSTIC_COREAPI.BRPrint;
391 
392 sql_text:= rma1_sql(14);
393 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_REQUEST_LINES"></a>');
394 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_REQUEST_LINES</b>  <a href="#INDEX OF
395 QUERIES">[Top]</a>');
396 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
397 JTF_DIAGNOSTIC_COREAPI.BRPrint;
398 
399 sql_text:= rma1_sql(15);
400 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_MATERIAL_TRANSACTIONS_TEMP"></a>');
401 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_MATERIAL_TRANSACTIONS_TEMP</b>  <a href="#INDEX OF
402 QUERIES">[Top]</a>');
403 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
404 JTF_DIAGNOSTIC_COREAPI.BRPrint;
405 
406 sql_text:= rma1_sql(16);
407 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="OE_LOT_SERIAL_NUMBERS"></a>');
408 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>OE_LOT_SERIAL_NUMBERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
409 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
410 JTF_DIAGNOSTIC_COREAPI.BRPrint;
411 
412 sql_text:= rma1_sql(17);
413 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS"></a>');
414 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
415 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
416 JTF_DIAGNOSTIC_COREAPI.BRPrint;
417 
418 sql_text:= rma1_sql(18);
419 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS_TEMP"></a>');
420 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS_TEMP</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
421 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
422 JTF_DIAGNOSTIC_COREAPI.BRPrint;
423 
424 sql_text:= rma1_sql(19);
425 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_SERIAL_NUMBERS_INTERFACE"></a>');
426 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_SERIAL_NUMBERS_INTERFACE</b>  <a href="#INDEX OF
427 QUERIES">[Top]</a>');
428 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
429 JTF_DIAGNOSTIC_COREAPI.BRPrint;
430 
431 sql_text:= rma1_sql(20);
432 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_UNIT_TRANSACTIONS"></a>');
433 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_UNIT_TRANSACTIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
434 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
435 JTF_DIAGNOSTIC_COREAPI.BRPrint;
436 
437 sql_text:= rma1_sql(21);
438 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SERIAL_TRANSACTIONS"></a>');
439 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SERIAL_TRANSACTIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
440 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
441 JTF_DIAGNOSTIC_COREAPI.BRPrint;
442 
443 sql_text:= rma1_sql(22);
444 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SERIAL_INTERFACE"></a>');
445 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SERIAL_INTERFACE</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
446 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
447 JTF_DIAGNOSTIC_COREAPI.BRPrint;
448 
449 sql_text:= rma1_sql(23);
450 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_LOT_NUMBERS"></a>');
451 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_LOT_NUMBERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
452 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
453 JTF_DIAGNOSTIC_COREAPI.BRPrint;
454 
455 sql_text:= rma1_sql(24);
456 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOT_NUMBERS"></a>');
457 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOT_NUMBERS</b>  <a href="#INDEX OF
458 QUERIES">[Top]</a>');
459 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
460 JTF_DIAGNOSTIC_COREAPI.BRPrint;
461 
462 sql_text:= rma1_sql(25);
463 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOT_INTERFACE"></a>');
464 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOT_INTERFACE</b>  <a href="#INDEX OF
465 QUERIES">[Top]</a>');
466 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
467 JTF_DIAGNOSTIC_COREAPI.BRPrint;
468 
469 sql_text:= rma1_sql(26);
470 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_TRANSACTION_LOTS_TEMP"></a>');
471 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_TRANSACTION_LOTS_TEMP</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
472 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
473 JTF_DIAGNOSTIC_COREAPI.BRPrint;
474 
475 sql_text:= rma1_sql(27);
476 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_LOT_TRANSACTIONS"></a>');
477 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_LOT_TRANSACTIONS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
478 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
479 JTF_DIAGNOSTIC_COREAPI.BRPrint;
480 
481 sql_text:= rma1_sql(28);
482 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_PARAMETERS"></a>');
483 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_PARAMETERS</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
484 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
485 JTF_DIAGNOSTIC_COREAPI.BRPrint;
486 
487 sql_text:= rma1_sql(29);
488 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="PO_SYSTEM_PARAMETERS_ALL"></a>');
489 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>PO_SYSTEM_PARAMETERS_ALL</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
490 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
491 JTF_DIAGNOSTIC_COREAPI.BRPrint;
492 
493 sql_text:= rma1_sql(30);
494 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="FINANCIAL_SYSTEM_PARAMETERS"></a>');
495 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>FINANCIAL_SYSTEM_PARAMETERS</b>  <a href="#INDEX OF
496 QUERIES">[Top]</a>');
497 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
498 JTF_DIAGNOSTIC_COREAPI.BRPrint;
499 
500 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="LOOKUP CODES"></a>');
501 JTF_DIAGNOSTIC_COREAPI.insert_html('<h4>Lookup Codes</h4>');
502 sql_text:= rma1_sql(100);
503 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Control"></a>');
504 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Control</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:= rma1_sql(101);
509 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Uniqueness"></a>');
510 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Uniqueness</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:= rma1_sql(102);
515 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Generation"></a>');
516 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Generation</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:= rma1_sql(103);
521 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Serial Number Status"></a>');
522 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Serial Number Status</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:= rma1_sql(104);
527 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Lot Control"></a>');
528 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Lot Control</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:= rma1_sql(105);
533 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="Lot Generation"></a>');
534 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>Lot Generation</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
535 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
536 JTF_DIAGNOSTIC_COREAPI.BRPrint;
537 
538 sql_text:= rma1_sql(106);
539 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="LOT Uniqueness"></a>');
540 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>LOT Uniqueness</b>  <a href="#INDEX OF QUERIES">[Top]</a>');
541 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sql_text,'');
542 JTF_DIAGNOSTIC_COREAPI.BRPrint;
543 
544 
545 END IF;
546 
547 ------------------------- Submit the sql to fwk
548 if skip_execution <> 1 then
549 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
550 statusStr := 'SUCCESS';
551 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
552 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
553 end if;
554 
555 EXCEPTION
556   when others then
557     JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
558     JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
559     statusStr := 'FAILURE';
560     errStr := sqlerrm ||' occurred in script Exception handled';
561     isFatal := 'FALSE';
562     report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
563     reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
564 END runTest;
565 
566 
567 END INV_DIAG_RCV_RMA;