[Home] [Help]
PACKAGE BODY: APPS.BOM_DIAGUNITTEST_TSDATA
Source
1 package body BOM_DIAGUNITTEST_TSDATA as
2 /* $Header: BOMDGTSB.pls 120.1.12020000.2 2012/11/07 07:29:22 leizhzha ship $ */
3 PROCEDURE init is
4 BEGIN
5 null;
6 END init;
7
8 PROCEDURE cleanup IS
9 BEGIN
10 -- test writer could insert special cleanup code here
11 NULL;
12 END cleanup;
13
14 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
15 report OUT NOCOPY JTF_DIAG_REPORT,
16 reportClob OUT NOCOPY CLOB) IS
17 reportStr LONG; -- REPORT
18 sqltxt VARCHAR2(9999); -- SQL select statement
19 c_username VARCHAR2(50); -- accept input for username
20 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
21 errStr VARCHAR2(4000); -- error message
22 fixInfo VARCHAR2(4000); -- fix tip
23 isFatal VARCHAR2(50); -- TRUE or FALSE
24 num_rows NUMBER;
25 row_limit NUMBER;
26 l_item_id NUMBER;
27 l_org_id NUMBER;
28 l_count NUMBER;
29 l_table VARCHAR2(30);
30 ln_cursor INTEGER;
31 sql_stmt VARCHAR2(4000);
32 ln_rows_proc INTEGER;
33 l_ret_status BOOLEAN;
34 l_status VARCHAR2 (1);
35 l_industry VARCHAR2 (1);
36 l_oracle_schema VARCHAR2 (30);
37
38 --bug 15835553
39 CURSOR c_varchar_cols_cur(l_table_name varchar2, l_owner VARCHAR2) is
40 select col.column_name from user_synonyms syn, dba_tab_columns col where
41 col.owner=syn.table_owner AND col.table_name = syn.table_name
42 and syn.synonym_name = l_table_name and col.owner = l_owner and col.data_type in ( 'VARCHAR2' , 'CHAR');
43
44 CURSOR c_item_valid (cp_n_item_id IN NUMBER, cp_n_org_id IN NUMBER) IS
45 SELECT count(*)
46 FROM mtl_system_items_b
47 WHERE inventory_item_id = cp_n_item_id
48 AND organization_id = nvl(cp_n_org_id,organization_id);
49
50 BEGIN
51 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
52 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
53 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
54
55 /*Initializing local vars */
56 row_limit :=1000; /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
57 ln_rows_proc :=0;
58
59 -- accept input
60 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
61 l_item_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
62
63 If l_item_id is NULL then
64 JTF_DIAGNOSTIC_COREAPI.errorprint('Input Item Id is mandatory.');
65 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide a valid value for the Item Id.');
66 statusStr := 'FAILURE';
67 isFatal := 'TRUE';
68 fixInfo := ' Please review the error message below and take corrective action. ';
69 errStr := ' Invalid value for input field ItemId. It is a mandatory input.';
70
71 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
72 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
73 Return;
74 End If;
75
76 If l_org_id is NULL then
77 JTF_DIAGNOSTIC_COREAPI.errorprint('Input Organization Id is mandatory.');
78 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide a valid value for the Organization Id.');
79 statusStr := 'FAILURE';
80 isFatal := 'TRUE';
81 fixInfo := ' Please review the error message below and take corrective action. ';
82 errStr := ' Invalid value for input field Organization Id. It is a mandatory input.';
83
84 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
85 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
86 Return;
87 End If;
88
89 If (l_item_id is NOT NULL) AND (l_org_id is NOT NULL) Then
90 OPEN c_item_valid (l_item_id, l_org_id);
91 FETCH c_item_valid INTO l_count;
92 CLOSE c_item_valid;
93
94 IF (l_count IS NULL) OR (l_count = 0) THEN
95 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Item and Organization Combination');
96 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter right combination of Item and Organization ');
97 statusStr := 'FAILURE';
98 errStr := 'Invalid Item and Organization Combination';
99 fixInfo := ' Please review the error message below and take corrective action. ';
100 isFatal := 'TRUE';
101 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
102 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
103 RETURN;
104 END IF;
105 End If;
106
107
108 /* Start of Scripts to check for trailing spaces in Item Tables */
109
110 JTF_DIAGNOSTIC_COREAPI.Line_Out('
111 <BR/> This script will report the records in Items,Bills,
112 <BR/> Routings related tables that contain trailing spaces
113 <BR/> in any of their column values.
114 <BR/><BR/><u>IMPACT:</u>
115 <BR/> If a column contains trailing spaces then,
116 <BR/> FRM-40654 error may occur while trying to
117 <BR/> update those records from the Form.
118 <BR/><BR/><u>ACTION:</u>
119 <BR/> In this script''s output for each table,
120 <BR/> ''Column Name'' stands for the name of the offending column
121 <BR/> ''Column Value'' stands for the value of the offending column.
122 <BR/> The rest of the columns are useful in uniquely identifying
123 <BR/> the specific record.
124 <BR/>
125 <BR/> If records are fetched from any of the tables,
126 <BR/> then the record with the offending column
127 <BR/> needs to be updated so that the columns with
128 <BR/> the trailing spaces are trimmed for this specific record.
129 <BR/>
130 <BR/>(E.g.) If for mtl_system_items_b table,
131 <BR/> a record is fetched with
132 <BR/> Inventory Item Id = 1234
133 <BR/> Organization id = 100
134 <BR/> Column Name = Attribute1
135 <BR/> Column Value = ''column with trailing space ''
136 <BR/>
137 <BR/> Use file "afchrchk.sql" present in $FND_TOP/sql to trim the trailing spaces.
138 <BR/> This script will ask for the following input.
139 <BR/> 1. Table name: Enter the table name fetched above (e.g.)mtl_system_items_b
140 <BR/> 2. Column name: Enter the column name fetched above (e.g.) Attribute1
141 <BR/> 3. Check for newline characters (Y/N)?: Enter as N
142 <BR/> 4. Automatically fix all errors found (Y/N)? Enter as Y
143 <BR/>
144 <BR/><I><u>Important:</u> Try the above action plan on a TEST INSTANCE first.</I>
145 <BR/>');
146
147 /* Note 1: It was observed that for all the tables used in this script, on an average the number of varchar2
148 columns were about 25. So for each column, the number of probelmatic records that get logged have been
149 restricted to 40. So that 40*25 =1000 (row_limt). In future, a more refined approach can be coded if neccessary.
150
151 Note 2: The global temporary table contains four specific columns namely
152 Inventory_item_id, organization_id, Description,Long_Description for storing the item_id,org_id,desc,long_desc
153 of an item respectively. The rest of the problematic varchar2 columns are stored in Char_col2.
154 Always, Char_col1 stores the name of the column with trailing spaces and
155 char_col2 will store its value.
156 */
157
158 /* Start of Scripts for Item tables */
159 /* Get the application installation info. References to Data Dictionary Objects without schema name
160 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). For accessing all_tab_columns
161 in cursor c_varchar_cols_cur we need to pass the schema name*/
162
163 l_ret_status := fnd_installation.get_app_info ('INV'
164 , l_status
165 , l_industry
166 , l_oracle_schema
167 );
168
169 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
170
171 /* Script to verify records in mtl_system_items_b table */
172 Begin
173 delete from bom_diag_temp_tab; -- Clear the temporary tables
174 For l_varchar_col_rec in c_varchar_cols_cur('MTL_SYSTEM_ITEMS_B',l_oracle_schema) Loop
175 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
176
177 sql_stmt := '';
178 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
179 ' char_col1,char_col2) '||
180 ' SELECT msib.inventory_item_id '||
181 ' ,msib.organization_id '||
182 ' ,'''||l_varchar_col_rec.column_name||''' '||
183 ' ,msib.'||l_varchar_col_rec.column_name||' '||
184 ' FROM MTL_SYSTEM_ITEMS_B msib '||
185 ' WHERE msib.organization_id = '||l_org_id||' '||
186 ' AND msib.inventory_item_id ='||l_item_id||' '||
187 ' AND msib.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
188 ' AND rownum < '||(row_limit/25);
189
190 ln_cursor := dbms_sql.open_cursor;
191 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
192 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
193 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
194
195 End Loop;
196
197 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
198 ' Organization_id "Organization id", '||
199 ' Char_Col1 "Column Name", '||
200 ' Char_Col2 "Column Value" '||
201 ' From bom_diag_temp_tab where 1=1 ';
202
203 sqltxt := sqltxt || ' and rownum< '||row_limit;
204 sqltxt :=sqltxt||' order by inventory_item_id, organization_id,char_col1';
205
206 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_system_items_b table ');
207 If (num_rows = row_limit -1 ) Then
208 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
209 End If;
210 statusStr := 'SUCCESS';
211 isFatal := 'FALSE';
212
213 End;
214 /* End of mtl_system_items_b script */
215
216 /* Script to verify records in mtl_system_items_tl table */
217 Begin
218 delete from bom_diag_temp_tab; -- Clear the temporary tables
219 For l_varchar_col_rec in c_varchar_cols_cur('MTL_SYSTEM_ITEMS_TL',l_oracle_schema) Loop
220 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
221
222 sql_stmt := '';
223
224 /* If (upper(l_varchar_col_rec.column_name) NOT IN ('DESCRIPTION','LONG_DESCRIPTION')) Then
225 -- JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> not description, long description');
226 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
227 ' char_col3,char_col1,char_col2) '||
228 ' SELECT msitl.inventory_item_id '||
229 ' ,msitl.organization_id '||
230 ' ,msitl.language '||
231 ' ,'''||l_varchar_col_rec.column_name||''' '||
232 ' ,msitl.'||l_varchar_col_rec.column_name||' '||
233 ' FROM MTL_SYSTEM_ITEMS_TL msitl '||
234 ' WHERE msitl.organization_id = '||l_org_id||' '||
235 ' AND msitl.inventory_item_id ='||l_item_id||' '||
236 ' AND msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
237 ' AND rownum < '||(row_limit/25);
238 */
239 If (upper(l_varchar_col_rec.column_name) IN ('DESCRIPTION','LONG_DESCRIPTION')) Then
240 If (upper(l_varchar_col_rec.column_name)='DESCRIPTION') Then
241 /*JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> description '); */
242 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
243 ' char_col3,char_col1,description) '||
244 ' SELECT msitl.inventory_item_id '||
245 ' ,msitl.organization_id '||
246 ' ,msitl.language '||
247 ' ,''DESCRIPTION'' '||
248 ' ,msitl.'||l_varchar_col_rec.column_name||' '||
249 ' FROM MTL_SYSTEM_ITEMS_TL msitl '||
250 ' WHERE msitl.organization_id = '||l_org_id||' '||
251 ' AND msitl.inventory_item_id ='||l_item_id||' '||
252 ' AND msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
253 ' AND rownum < '||(row_limit/25);
254
255 ElsIf (upper(l_varchar_col_rec.column_name)='LONG_DESCRIPTION') Then
256 /*JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> long description');*/
257 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
258 ' char_col3,char_col1,Long_Description) '||
259 ' SELECT msitl.inventory_item_id '||
260 ' ,msitl.organization_id '||
261 ' ,msitl.language '||
262 ' ,''LONG_DESCRIPTION'' '||
263 ' ,msitl.'||l_varchar_col_rec.column_name||' '||
264 ' FROM MTL_SYSTEM_ITEMS_TL msitl '||
265 ' WHERE msitl.organization_id = '||l_org_id||' '||
266 ' AND msitl.inventory_item_id ='||l_item_id||' '||
267 ' AND msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
268 ' AND rownum < '||(row_limit/25);
269
270 End If;
271
272 ln_cursor := dbms_sql.open_cursor;
273 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
274 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
275 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
276
277 End If; /* l_varchar_col_rec.column_name) IN ('DESCRIPTION','LONG_DESCRIPTION') */
278 End Loop;
279
280 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
281 ' Organization_id "Organization id", '||
282 ' Char_col3 "Language", '||
283 ' Char_Col1 "Column Name", '||
284 ' Description "Description", '||
285 ' Long_Description "Long Description" '||
286 ' From bom_diag_temp_tab where 1=1 ';
287
288 sqltxt := sqltxt || ' and rownum< '||row_limit;
289 sqltxt :=sqltxt||' order by inventory_item_id, organization_id,char_col3,char_col1';
290
291 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_system_items_tl table ');
292 If (num_rows = row_limit -1 ) Then
293 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
294 End If;
295 statusStr := 'SUCCESS';
296 isFatal := 'FALSE';
297
298 End;
299 /* End of mtl_system_items_tl script */
300
301 /* Script to verify records in mtl_item_revisions_b table */
302 Begin
303 delete from bom_diag_temp_tab; -- Clear the temporary tables
304 For l_varchar_col_rec in c_varchar_cols_cur('MTL_ITEM_REVISIONS_B',l_oracle_schema) Loop
305 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
306
307 sql_stmt := '';
308 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
309 ' char_col3,num_col1,char_col1,char_col2) '||
310 ' SELECT mirb.inventory_item_id '||
311 ' ,mirb.organization_id '||
312 ' ,mirb.revision '||
313 ' ,mirb.revision_id '||
314 ' ,'''||l_varchar_col_rec.column_name||''' '||
315 ' ,mirb.'||l_varchar_col_rec.column_name||' '||
316 ' FROM MTL_ITEM_REVISIONS_B mirb '||
317 ' WHERE mirb.organization_id = '||l_org_id||' '||
318 ' AND mirb.inventory_item_id ='||l_item_id||' '||
319 ' AND mirb.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
320 ' AND rownum < '||(row_limit/25);
321
322 ln_cursor := dbms_sql.open_cursor;
323 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
324 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
325 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
326
327 End Loop;
328
329 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
330 ' Organization_id "Organization id", '||
331 ' Char_Col3 "Revision", '||
332 ' Num_Col1 "Revision Id", '||
333 ' Char_Col1 "Column Name", '||
334 ' Char_Col2 "Column Value" '||
335 ' From bom_diag_temp_tab where 1=1 ';
336
337 sqltxt := sqltxt || ' and rownum< '||row_limit;
338 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col1';
339
340 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_item_revisions_b table ');
341 If (num_rows = row_limit -1 ) Then
342 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
343 End If;
344 statusStr := 'SUCCESS';
345 isFatal := 'FALSE';
346
347 End;
348 /* End of mtl_item_revisions_b script */
349
350 /* Script to verify records in mtl_item_revisions_tl table */
351 Begin
352 delete from bom_diag_temp_tab; -- Clear the temporary tables
353 For l_varchar_col_rec in c_varchar_cols_cur('MTL_ITEM_REVISIONS_TL',l_oracle_schema) Loop
354 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
355
356 sql_stmt := '';
357 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
358 ' num_col1,char_col3,char_col1,char_col2) '||
359 ' SELECT mirtl.inventory_item_id '||
360 ' ,mirtl.organization_id '||
364 ' ,mirtl.'||l_varchar_col_rec.column_name||' '||
361 ' ,mirtl.revision_id '||
362 ' ,mirtl.language '||
363 ' ,'''||l_varchar_col_rec.column_name||''' '||
365 ' FROM MTL_ITEM_REVISIONS_TL mirtl '||
366 ' WHERE mirtl.organization_id = '||l_org_id||' '||
367 ' AND mirtl.inventory_item_id ='||l_item_id||' '||
368 ' AND mirtl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
369 ' AND rownum < '||(row_limit/25);
370
371 ln_cursor := dbms_sql.open_cursor;
372 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
373 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
374 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
375
376 End Loop;
377
378 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
379 ' Organization_id "Organization id", '||
380 ' Num_Col1 "Revision Id", '||
381 ' Char_Col3 "Language", '||
382 ' Char_Col1 "Column Name", '||
383 ' Char_Col2 "Column Value" '||
384 ' From bom_diag_temp_tab where 1=1 ';
385
386 sqltxt := sqltxt || ' and rownum< '||row_limit;
387 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, num_col1,char_col3,char_col1';
388
389 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_item_revisions_tl table ');
390 If (num_rows = row_limit -1 ) Then
391 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
392 End If;
393 statusStr := 'SUCCESS';
394 isFatal := 'FALSE';
395
396 End;
397 /* End of mtl_item_revisions_tl script */
398
399 /* Script to verify records in mtl_descr_element_values table */
400 Begin
401 delete from bom_diag_temp_tab; -- Clear the temporary tables
402 For l_varchar_col_rec in c_varchar_cols_cur('MTL_DESCR_ELEMENT_VALUES',l_oracle_schema) Loop
403 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
404
405 sql_stmt := '';
406 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, '||
407 ' char_col3,num_col1,char_col1,char_col2) '||
408 ' SELECT mdev.inventory_item_id '||
409 ' ,mdev.element_name '||
410 ' ,mdev.element_sequence '||
411 ' ,'''||l_varchar_col_rec.column_name||''' '||
412 ' ,mdev.'||l_varchar_col_rec.column_name||' '||
413 ' FROM MTL_DESCR_ELEMENT_VALUES mdev '||
414 ' WHERE mdev.inventory_item_id ='||l_item_id||' '||
415 ' AND mdev.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
416 ' AND rownum < '||(row_limit/25);
417
418 ln_cursor := dbms_sql.open_cursor;
419 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
420 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
421 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
422
423 End Loop;
424
425 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
426 ' Char_Col3 "Element Name", '||
427 ' Num_Col1 "Element Sequence", '||
428 ' Char_Col1 "Column Name", '||
429 ' Char_Col2 "Column Value" '||
430 ' From bom_diag_temp_tab where 1=1 ';
431
432 sqltxt := sqltxt || ' and rownum< '||row_limit;
433 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col1';
434
435 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_descr_element_values table ');
436 If (num_rows = row_limit -1 ) Then
437 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
438 End If;
439 statusStr := 'SUCCESS';
440 isFatal := 'FALSE';
441 End;
442 /* End of mtl_descr_element_values script */
443
444 /* Script to verify records in mtl_pending_item_status table */
445 Begin
446 delete from bom_diag_temp_tab; -- Clear the temporary tables
447 For l_varchar_col_rec in c_varchar_cols_cur('MTL_PENDING_ITEM_STATUS',l_oracle_schema) Loop
448 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
449
450 sql_stmt := '';
451 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
452 ' char_col3,Date_col1,char_col1,char_col2) '||
453 ' SELECT mpis.inventory_item_id '||
454 ' ,mpis.Organization_id '||
455 ' ,mpis.STATUS_CODE '||
456 ' ,mpis.EFFECTIVE_DATE '||
457 ' ,'''||l_varchar_col_rec.column_name||''' '||
458 ' ,mpis.'||l_varchar_col_rec.column_name||' '||
459 ' FROM MTL_PENDING_ITEM_STATUS mpis '||
460 ' WHERE mpis.inventory_item_id ='||l_item_id||' '||
461 ' AND mpis.organization_id = '||l_org_id||' '||
462 ' AND mpis.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
463 ' AND rownum < '||(row_limit/25);
464
465 ln_cursor := dbms_sql.open_cursor;
466 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
467 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
468 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
469
470 End Loop;
471
472 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
473 ' Organization_id "Organization id", '||
474 ' Char_Col3 "Status Code", '||
475 ' to_char(Date_Col1,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
476 ' Char_Col1 "Column Name", '||
477 ' Char_Col2 "Column Value" '||
478 ' From bom_diag_temp_tab where 1=1 ';
479
480 sqltxt := sqltxt || ' and rownum< '||row_limit;
481 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Date_col1,char_col1';
482
486 End If;
483 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_pending_item_status table ');
484 If (num_rows = row_limit -1 ) Then
485 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
487 statusStr := 'SUCCESS';
488 isFatal := 'FALSE';
489 End;
490 /* End of mtl_pending_item_status script */
491
492
493 /* Script to verify records in mtl_cross_references_b table */
494 Begin
495 delete from bom_diag_temp_tab; -- Clear the temporary tables
496 For l_varchar_col_rec in c_varchar_cols_cur('MTL_CROSS_REFERENCES_B',l_oracle_schema) Loop
497 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
498
499 sql_stmt := '';
500 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
501 ' char_col3,char_col4,char_col1,char_col2) '||
502 ' SELECT mcr.inventory_item_id '||
503 ' ,mcr.Organization_id '||
504 ' ,MCR.CROSS_REFERENCE_TYPE '||
505 ' ,MCR.CROSS_REFERENCE '||
506 ' ,'''||l_varchar_col_rec.column_name||''' '||
507 ' ,mcr.'||l_varchar_col_rec.column_name||' '||
508 ' FROM MTL_CROSS_REFERENCES_B mcr '||
509 ' WHERE mcr.inventory_item_id ='||l_item_id||' '||
510 ' AND ( ( mcr.organization_id = '||l_org_id||' and org_independent_flag=''N'') '||
511 ' or ( mcr.organization_id is null and org_independent_flag=''Y'') ) '||
512 ' AND mcr.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
513 ' AND rownum < '||(row_limit/25);
514
515 ln_cursor := dbms_sql.open_cursor;
516 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
517 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
518 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
519
520 End Loop;
521
522 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
523 ' Organization_id "Organization id", '||
524 ' Char_Col3 "Cross Reference Type", '||
525 ' Char_Col4 "Cross Reference", '||
526 ' Char_Col1 "Column Name", '||
527 ' Char_Col2 "Column Value" '||
528 ' From bom_diag_temp_tab where 1=1 ';
529
530 sqltxt := sqltxt || ' and rownum< '||row_limit;
531 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col4,char_col1';
532
533 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_cross_references_b table ');
534 If (num_rows = row_limit -1 ) Then
535 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
536 End If;
537 statusStr := 'SUCCESS';
538 isFatal := 'FALSE';
539 End;
540 /* End of mtl_cross_references_b script */
541
542 /* Script to verify records in mtl_cross_references_tl table */
543 Begin
544 delete from bom_diag_temp_tab; -- Clear the temporary tables
545 For l_varchar_col_rec in c_varchar_cols_cur('MTL_CROSS_REFERENCES_TL',l_oracle_schema) Loop
546 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
547
548 sql_stmt := '';
549 sql_stmt :=' Insert into bom_diag_temp_tab (num_col1, '||
550 ' char_col4,char_col1,char_col2) '||
551 ' SELECT mcrt.cross_reference_id '||
552 ' ,mcrt.language '||
553 ' ,'''||l_varchar_col_rec.column_name||''' '||
554 ' ,mcrt.'||l_varchar_col_rec.column_name||' '||
555 ' FROM MTL_CROSS_REFERENCES_B mcrb, MTL_CROSS_REFERENCES_TL mcrt '||
556 ' WHERE mcrb.inventory_item_id ='||l_item_id||' '||
557 ' AND ( ( mcrb.organization_id = '||l_org_id||' and org_independent_flag=''N'') '||
558 ' or ( mcrb.organization_id is null and org_independent_flag=''Y'') ) '||
559 ' AND mcrb.cross_reference_id = mcrt.cross_reference_id '||
560 ' AND mcrt.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
561 ' AND rownum < '||(row_limit/25);
562
563 ln_cursor := dbms_sql.open_cursor;
564 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
565 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
566 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
567
568 End Loop;
569
570 sqltxt:=' Select num_col1 "Cross Reference Id", '||
571 ' Char_Col4 "Language", '||
572 ' Char_Col1 "Column Name", '||
573 ' Char_Col2 "Column Value" '||
574 ' From bom_diag_temp_tab where 1=1 ';
575
576 sqltxt := sqltxt || ' and rownum< '||row_limit;
577 sqltxt :=sqltxt||' order by num_col1,char_col4,char_col1';
578
579 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_cross_references_tl table ');
580 If (num_rows = row_limit -1 ) Then
581 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
582 End If;
583 statusStr := 'SUCCESS';
584 isFatal := 'FALSE';
585 End;
586 /* End of mtl_cross_references_tl script */
587
588 /* Script to verify records in mtl_customer_item_xrefs table */
589 Begin
590 delete from bom_diag_temp_tab; -- Clear the temporary tables
591 For l_varchar_col_rec in c_varchar_cols_cur('MTL_CUSTOMER_ITEM_XREFS',l_oracle_schema) Loop
592 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
593
594 sql_stmt := '';
595 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
596 ' Num_col1,char_col1,char_col2) '||
597 ' SELECT mcix.inventory_item_id '||
598 ' ,mcix.Master_Organization_id '||
599 ' ,MCIX.CUSTOMER_ITEM_ID '||
603 ' WHERE mcix.inventory_item_id ='||l_item_id||' '||
600 ' ,'''||l_varchar_col_rec.column_name||''' '||
601 ' ,mcix.'||l_varchar_col_rec.column_name||' '||
602 ' FROM MTL_CUSTOMER_ITEM_XREFS mcix '||
604 ' AND mcix.master_organization_id = '||
605 ' (select master_organization_id from mtl_parameters '||
606 ' where organization_id= '||l_org_id||' ) '||
607 ' AND mcix.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
608 ' AND rownum < '||(row_limit/25);
609
610 ln_cursor := dbms_sql.open_cursor;
611 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
612 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
613 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
614
615 End Loop;
616
617 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
618 ' Organization_id "Master Organization id", '||
619 ' Num_Col1 "Customer Item Id", '||
620 ' Char_Col1 "Column Name", '||
621 ' Char_Col2 "Column Value" '||
622 ' From bom_diag_temp_tab where 1=1 ';
623
624 sqltxt := sqltxt || ' and rownum< '||row_limit;
625 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, Num_col1,char_col1';
626
627 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_customer_item_xrefs table ');
628 If (num_rows = row_limit -1 ) Then
629 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
630 End If;
631 statusStr := 'SUCCESS';
632 isFatal := 'FALSE';
633 End;
634 /* End of mtl_customer_item_xrefs script */
635
636 /* Script to verify records in mtl_mfg_part_numbers table */
637 Begin
638 delete from bom_diag_temp_tab; -- Clear the temporary tables
639 For l_varchar_col_rec in c_varchar_cols_cur('MTL_MFG_PART_NUMBERS',l_oracle_schema) Loop
640 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
641
642 sql_stmt := '';
643 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
644 ' Num_col1,char_col3,char_col1,char_col2) '||
645 ' SELECT mmpn.inventory_item_id '||
646 ' ,mmpn.Organization_id '||
647 ' ,MMPN.MANUFACTURER_ID '||
648 ' ,MMPN.MFG_PART_NUM '||
649 ' ,'''||l_varchar_col_rec.column_name||''' '||
650 ' ,mmpn.'||l_varchar_col_rec.column_name||' '||
651 ' FROM MTL_MFG_PART_NUMBERS mmpn '||
652 ' WHERE mmpn.inventory_item_id ='||l_item_id||' '||
653 ' AND mmpn.organization_id = '||
654 ' (select master_organization_id from mtl_parameters '||
655 ' where organization_id= '||l_org_id||' ) '||
656 ' AND mmpn.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
657 ' AND rownum < '||(row_limit/25);
658
659 ln_cursor := dbms_sql.open_cursor;
660 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
661 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
662 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
663
664 End Loop;
665
666 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
667 ' Organization_id "Organization id", '||
668 ' Num_Col1 "Manufacturer Id", '||
669 ' Char_Col3 "Mfg Part Num", '||
670 ' Char_Col1 "Column Name", '||
671 ' Char_Col2 "Column Value" '||
672 ' From bom_diag_temp_tab where 1=1 ';
673
674 sqltxt := sqltxt || ' and rownum< '||row_limit;
675 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, Num_col1,char_col3,char_col1';
676
677 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_mfg_part_numbers table ');
678 If (num_rows = row_limit -1 ) Then
679 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
680 End If;
681 statusStr := 'SUCCESS';
682 isFatal := 'FALSE';
683 End;
684 /* End of mtl_mfg_part_numbers script */
685
686 /* Script to verify records in mtl_rtg_item_revisions table */
687 Begin
688 delete from bom_diag_temp_tab; -- Clear the temporary tables
689 For l_varchar_col_rec in c_varchar_cols_cur('MTL_RTG_ITEM_REVISIONS',l_oracle_schema) Loop
690 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
691
692 sql_stmt := '';
693 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
694 ' char_col3,char_col1,char_col2) '||
695 ' SELECT mrir.inventory_item_id '||
696 ' ,mrir.organization_id '||
697 ' ,MRIR.PROCESS_REVISION '||
698 ' ,'''||l_varchar_col_rec.column_name||''' '||
699 ' ,mrir.'||l_varchar_col_rec.column_name||' '||
700 ' FROM MTL_RTG_ITEM_REVISIONS mrir '||
701 ' WHERE mrir.organization_id = '||l_org_id||' '||
702 ' AND mrir.inventory_item_id ='||l_item_id||' '||
703 ' AND mrir.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
704 ' AND rownum < '||(row_limit/25);
705
706 ln_cursor := dbms_sql.open_cursor;
707 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
708 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
709 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
710
711 End Loop;
712
713 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
714 ' Organization_id "Organization id", '||
715 ' Char_Col3 "Process Revision", '||
716 ' Char_Col1 "Column Name", '||
717 ' Char_Col2 "Column Value" '||
718 ' From bom_diag_temp_tab where 1=1 ';
719
720 sqltxt := sqltxt || ' and rownum< '||row_limit;
724 If (num_rows = row_limit -1 ) Then
721 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col1';
722
723 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_rtg_item_revisions table ');
725 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
726 End If;
727 statusStr := 'SUCCESS';
728 isFatal := 'FALSE';
729
730 End;
731 /* End of mtl_rtg_item_revisions script */
732
733 /* Script to verify records in mtl_related_items table */
734 Begin
735 delete from bom_diag_temp_tab; -- Clear the temporary tables
736 For l_varchar_col_rec in c_varchar_cols_cur('MTL_RELATED_ITEMS',l_oracle_schema) Loop
737 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
738
739 sql_stmt := '';
740 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
741 ' Num_col1,Num_col2,char_col1,char_col2) '||
742 ' SELECT mri.inventory_item_id '||
743 ' ,mri.Organization_id '||
744 ' ,MRI.RELATED_ITEM_ID '||
745 ' ,MRI.RELATIONSHIP_TYPE_ID '||
746 ' ,'''||l_varchar_col_rec.column_name||''' '||
747 ' ,mri.'||l_varchar_col_rec.column_name||' '||
748 ' FROM MTL_RELATED_ITEMS mri '||
749 ' WHERE mri.inventory_item_id ='||l_item_id||' '||
750 ' AND mri.organization_id = '||
751 ' (select master_organization_id from mtl_parameters '||
752 ' where organization_id= '||l_org_id||' ) '||
753 ' AND mri.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
754 ' AND rownum < '||(row_limit/25);
755
756 ln_cursor := dbms_sql.open_cursor;
757 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
758 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
759 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
760
761 End Loop;
762
763 sqltxt:=' Select Inventory_Item_Id "Inventory Item Id", '||
764 ' Organization_id "Organization id", '||
765 ' Num_Col1 "Related Item Id", '||
766 ' Num_Col2 "Relationship Type Id", '||
767 ' Char_Col1 "Column Name", '||
768 ' Char_Col2 "Column Value" '||
769 ' From bom_diag_temp_tab where 1=1 ';
770
771 sqltxt := sqltxt || ' and rownum< '||row_limit;
772 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, Num_col1,Num_col2,char_col1';
773
774 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in mtl_related_items table ');
775 If (num_rows = row_limit -1 ) Then
776 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
777 End If;
778 statusStr := 'SUCCESS';
779 isFatal := 'FALSE';
780 End;
781 /* End of mtl_related_items script */
782
783 /* End of Scripts to check for trailing spaces in Item Tables */
784
785
786 /* Start of Scripts to check for trailing spaces in Bom Tables */
787
788 /* Get the application installation info. References to Data Dictionary Objects without schema name
789 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). For accessing all_tab_columns
790 in cursor c_varchar_cols_cur we need to pass the schema name*/
791
792 l_ret_status := fnd_installation.get_app_info ('BOM'
793 , l_status
794 , l_industry
795 , l_oracle_schema
796 );
797
798 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
799
800 /* Script to verify records in BOM_STRUCTURES_B table */
801 Begin
802 delete from bom_diag_temp_tab; -- Clear the temporary tables
803 For l_varchar_col_rec in c_varchar_cols_cur('BOM_STRUCTURES_B',l_oracle_schema) Loop
804 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
805
806 sql_stmt := '';
807 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
808 ' char_col3,Num_col1,char_col1,char_col2) '||
809 ' SELECT bsb.assembly_item_id '||
810 ' ,bsb.Organization_id '||
811 ' ,bsb.ALTERNATE_BOM_DESIGNATOR '||
812 ' ,bsb.BILL_SEQUENCE_ID '||
813 ' ,'''||l_varchar_col_rec.column_name||''' '||
814 ' ,bsb.'||l_varchar_col_rec.column_name||' '||
815 ' FROM BOM_STRUCTURES_B bsb '||
816 ' WHERE bsb.assembly_item_id ='||l_item_id||' '||
817 ' AND bsb.organization_id = '||l_org_id||' '||
818 ' AND bsb.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
819 ' AND rownum < '||(row_limit/25);
820
821 ln_cursor := dbms_sql.open_cursor;
822 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
823 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
824 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
825
826 End Loop;
827
828 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
829 ' Organization_id "Organization id", '||
830 ' char_Col3 "ALTERNATE BOM DESIGNATOR", '||
831 ' Num_Col1 "BILL SEQUENCE ID", '||
832 ' Char_Col1 "Column Name", '||
833 ' Char_Col2 "Column Value" '||
834 ' From bom_diag_temp_tab where 1=1 ';
835
836 sqltxt := sqltxt || ' and rownum< '||row_limit;
837 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col1,char_col1';
838
839 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_structures_b table ');
840 If (num_rows = row_limit -1 ) Then
841 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
845 End;
842 End If;
843 statusStr := 'SUCCESS';
844 isFatal := 'FALSE';
846 /* End of bom_bill_of_materials script */
847
848 /* Script to verify records in BOM_COMPONENTS_B table */
849 Begin
850 delete from bom_diag_temp_tab; -- Clear the temporary tables
851 For l_varchar_col_rec in c_varchar_cols_cur('BOM_COMPONENTS_B',l_oracle_schema) Loop
852 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
853
854 sql_stmt := '';
855 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
856 ' char_col3,Num_col1,Num_col2,Num_col3,num_col4,char_col1,char_col2)'||
857 ' SELECT bsb.assembly_item_id '||
858 ' ,bsb.Organization_id '||
859 ' ,bsb.ALTERNATE_BOM_DESIGNATOR '||
860 ' ,bsb.BILL_SEQUENCE_ID '||
861 ' ,BCB.OPERATION_SEQ_NUM '||
862 ' ,BCB.COMPONENT_ITEM_ID '||
863 ' ,bcb.component_sequence_id '||
864 ' ,'''||l_varchar_col_rec.column_name||''' '||
865 ' ,bcb.'||l_varchar_col_rec.column_name||' '||
866 ' FROM BOM_COMPONENTS_B bcb , BOM_STRUCTURES_B bsb '||
867 ' WHERE 1=1 '||
868 ' AND bcb.bill_sequence_id = bsb.bill_sequence_id '||
869 ' AND bsb.assembly_item_id ='||l_item_id||' '||
870 ' AND bsb.organization_id = '||l_org_id||' '||
871 ' AND bcb.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
872 ' AND rownum < '||(row_limit/25);
873
874 ln_cursor := dbms_sql.open_cursor;
875 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
876 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
877 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
878
879 End Loop;
880
881 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
882 ' Organization_id "Organization id", '||
883 ' char_Col3 "ALTERNATE BOM DESIGNATOR", '||
884 ' Num_Col1 "BILL SEQUENCE ID", '||
885 ' Num_Col2 "OPERATION SEQ NUM", '||
886 ' Num_Col3 "COMPONENT ITEM ID", '||
887 ' Num_Col4 "COMPONENT SEQUENCE ID", '||
888 ' Char_Col1 "Column Name", '||
889 ' Char_Col2 "Column Value" '||
890 ' From bom_diag_temp_tab where 1=1 ';
891
892 sqltxt := sqltxt || ' and rownum< '||row_limit;
893 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col1,Num_col2,Num_col3,char_col1';
894
895 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_components_b table ');
896 If (num_rows = row_limit -1 ) Then
897 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
898 End If;
899 statusStr := 'SUCCESS';
900 isFatal := 'FALSE';
901 End;
902 /* End of bom_inventory_components script */
903
904 /* Script to verify records in bom_reference_designators table */
905 Begin
906 delete from bom_diag_temp_tab; -- Clear the temporary tables
907 For l_varchar_col_rec in c_varchar_cols_cur('BOM_REFERENCE_DESIGNATORS',l_oracle_schema) Loop
908 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
909
910 sql_stmt := '';
911 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
912 ' char_col3,Num_col1,Num_col2,char_col4,char_col1,char_col2) '||
913 ' SELECT bsb.assembly_item_id '||
914 ' ,bsb.Organization_id '||
915 ' ,bsb.ALTERNATE_BOM_DESIGNATOR '||
916 ' ,bsb.BILL_SEQUENCE_ID '||
917 ' ,bcb.component_sequence_id '||
918 ' ,BRD.COMPONENT_REFERENCE_DESIGNATOR '||
919 ' ,'''||l_varchar_col_rec.column_name||''' '||
920 ' ,brd.'||l_varchar_col_rec.column_name||' '||
921 ' FROM bom_inventory_components bcb, bom_bill_of_materials bsb, bom_reference_designators brd '||
922 ' WHERE 1=1 '||
923 ' AND bcb.bill_sequence_id = bsb.bill_sequence_id '||
924 ' and brd.component_sequence_id=bcb.component_sequence_id '||
925 ' AND bsb.assembly_item_id ='||l_item_id||' '||
926 ' AND bsb.organization_id = '||l_org_id||' '||
927 ' AND brd.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
928 ' AND rownum < '||(row_limit/25);
929
930 ln_cursor := dbms_sql.open_cursor;
931 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
932 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
933 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
934
935 End Loop;
936
937 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
938 ' Organization_id "Organization id", '||
939 ' char_Col3 "ALTERNATE BOM DESIGNATOR", '||
940 ' Num_Col1 "BILL SEQUENCE ID", '||
941 ' Num_Col2 "COMPONENT SEQUENCE ID", '||
942 ' Char_Col4 "COMPONENT REFERENCE DESIGNATOR", '||
943 ' Char_Col1 "Column Name", '||
944 ' Char_Col2 "Column Value" '||
945 ' From bom_diag_temp_tab where 1=1 ';
946
947 sqltxt := sqltxt || ' and rownum< '||row_limit;
948 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col1,Num_col2,char_col1';
949
950 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_reference_designators table ');
951 If (num_rows = row_limit -1 ) Then
952 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
953 End If;
954 statusStr := 'SUCCESS';
955 isFatal := 'FALSE';
956 End;
957 /* End of bom_reference_designators script */
958
959 /* Script to verify records in bom_substitute_components table */
960 Begin
964
961 delete from bom_diag_temp_tab; -- Clear the temporary tables
962 For l_varchar_col_rec in c_varchar_cols_cur('BOM_SUBSTITUTE_COMPONENTS',l_oracle_schema) Loop
963 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
965 sql_stmt := '';
966 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
967 ' char_col3,Num_col1,Num_col2,Num_col3,char_col1,char_col2) '||
968 ' SELECT bsb.assembly_item_id '||
969 ' ,bsb.Organization_id '||
970 ' ,bsb.ALTERNATE_BOM_DESIGNATOR '||
971 ' ,bsb.BILL_SEQUENCE_ID '||
972 ' ,bcb.component_sequence_id '||
973 ' ,BSC.SUBSTITUTE_COMPONENT_ID '||
974 ' ,'''||l_varchar_col_rec.column_name||''' '||
975 ' ,bsc.'||l_varchar_col_rec.column_name||' '||
976 ' FROM bom_inventory_components bcb, bom_bill_of_materials bsb, bom_substitute_components bsc '||
977 ' WHERE 1=1 '||
978 ' AND bcb.bill_sequence_id = bsb.bill_sequence_id '||
979 ' and bsc.component_sequence_id=bcb.component_sequence_id '||
980 ' AND bsb.assembly_item_id ='||l_item_id||' '||
981 ' AND bsb.organization_id = '||l_org_id||' '||
982 ' AND bsc.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
983 ' AND rownum < '||(row_limit/25);
984
985 ln_cursor := dbms_sql.open_cursor;
986 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
987 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
988 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
989
990 End Loop;
991
992 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
993 ' Organization_id "Organization id", '||
994 ' char_Col3 "ALTERNATE BOM DESIGNATOR", '||
995 ' Num_Col1 "BILL SEQUENCE ID", '||
996 ' Num_Col2 "COMPONENT SEQUENCE ID", '||
997 ' Num_col3 "SUBSTITUTE COMPONENT ID", '||
998 ' Char_Col1 "Column Name", '||
999 ' Char_Col2 "Column Value" '||
1000 ' From bom_diag_temp_tab where 1=1 ';
1001
1002 sqltxt := sqltxt || ' and rownum< '||row_limit;
1003 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col1,Num_col2,char_col1';
1004
1005 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_substitute_components table ');
1006 If (num_rows = row_limit -1 ) Then
1007 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1008 End If;
1009 statusStr := 'SUCCESS';
1010 isFatal := 'FALSE';
1011 End;
1012 /* End of bom_substitute_components script */
1013
1014 /* End of Scripts to check for trailing spaces in Bom Tables */
1015
1016 /* Start of Scripts to check for trailing spaces in Rtg Tables */
1017
1018 /* Script to verify records in bom_operational_routings table */
1019 Begin
1020 delete from bom_diag_temp_tab; -- Clear the temporary tables
1021 For l_varchar_col_rec in c_varchar_cols_cur('BOM_OPERATIONAL_ROUTINGS',l_oracle_schema) Loop
1022 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
1023
1024 sql_stmt := '';
1025 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
1026 ' char_col3,Num_col1,char_col1,char_col2) '||
1027 ' SELECT bor.assembly_item_id '||
1028 ' ,bor.Organization_id '||
1029 ' ,BOR.ALTERNATE_ROUTING_DESIGNATOR '||
1030 ' ,BOR.ROUTING_SEQUENCE_ID '||
1031 ' ,'''||l_varchar_col_rec.column_name||''' '||
1032 ' ,bor.'||l_varchar_col_rec.column_name||' '||
1033 ' FROM bom_operational_routings bor '||
1034 ' WHERE 1=1 '||
1035 ' AND bor.assembly_item_id ='||l_item_id||' '||
1036 ' AND bor.organization_id = '||l_org_id||' '||
1037 ' AND bor.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
1038 ' AND rownum < '||(row_limit/25);
1039
1040 ln_cursor := dbms_sql.open_cursor;
1041 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
1042 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
1043 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
1044
1045 End Loop;
1046
1047 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
1048 ' Organization_id "Organization id", '||
1049 ' char_Col3 "ALTERNATE ROUTING DESIGNATOR", '||
1050 ' Num_Col1 "ROUTING SEQUENCE ID", '||
1051 ' Char_Col1 "Column Name", '||
1052 ' Char_Col2 "Column Value" '||
1053 ' From bom_diag_temp_tab where 1=1 ';
1054
1055 sqltxt := sqltxt || ' and rownum< '||row_limit;
1056 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col1';
1057
1058 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_operational_routings table ');
1059 If (num_rows = row_limit -1 ) Then
1060 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1061 End If;
1062 statusStr := 'SUCCESS';
1063 isFatal := 'FALSE';
1064 End;
1065 /* End of bom_operational_routings script */
1066
1067 /* Script to verify records in bom_operation_sequences table */
1068 Begin
1069 delete from bom_diag_temp_tab; -- Clear the temporary tables
1070 For l_varchar_col_rec in c_varchar_cols_cur('BOM_OPERATION_SEQUENCES',l_oracle_schema) Loop
1071 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
1072
1073 sql_stmt := '';
1074 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
1075 ' char_col3,Num_col1,Num_col2,char_col1,char_col2) '||
1076 ' SELECT bor.assembly_item_id '||
1077 ' ,bor.Organization_id '||
1081 ' ,'''||l_varchar_col_rec.column_name||''' '||
1078 ' ,BOR.ALTERNATE_ROUTING_DESIGNATOR '||
1079 ' ,BOR.ROUTING_SEQUENCE_ID '||
1080 ' ,BOS.OPERATION_SEQUENCE_ID '||
1082 ' ,bos.'||l_varchar_col_rec.column_name||' '||
1083 ' FROM bom_operational_routings bor, bom_operation_sequences bos '||
1084 ' WHERE 1=1 '||
1085 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
1086 ' AND bor.assembly_item_id ='||l_item_id||' '||
1087 ' AND bor.organization_id = '||l_org_id||' '||
1088 ' AND bos.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
1089 ' AND rownum < '||(row_limit/25);
1090
1091 ln_cursor := dbms_sql.open_cursor;
1092 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
1093 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
1094 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
1095
1096 End Loop;
1097
1098 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
1099 ' Organization_id "Organization id", '||
1100 ' char_Col3 "ALTERNATE ROUTING DESIGNATOR", '||
1101 ' Num_Col1 "ROUTING SEQUENCE ID", '||
1102 ' Num_Col2 "OPERATION SEQUENCE ID", '||
1103 ' Char_Col1 "Column Name", '||
1104 ' Char_Col2 "Column Value" '||
1105 ' From bom_diag_temp_tab where 1=1 ';
1106
1107 sqltxt := sqltxt || ' and rownum< '||row_limit;
1108 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,char_col1';
1109
1110 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_operation_sequences table ');
1111 If (num_rows = row_limit -1 ) Then
1112 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1113 End If;
1114 statusStr := 'SUCCESS';
1115 isFatal := 'FALSE';
1116 End;
1117 /* End of bom_operation_sequences script */
1118
1119 /* Script to verify records in bom_operation_resources table */
1120 Begin
1121 delete from bom_diag_temp_tab; -- Clear the temporary tables
1122 For l_varchar_col_rec in c_varchar_cols_cur('BOM_OPERATION_RESOURCES',l_oracle_schema) Loop
1123 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
1124
1125 sql_stmt := '';
1126 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
1127 ' char_col3,Num_col1,Num_col2,Num_col3,Num_col4,char_col1,char_col2)'||
1128 ' SELECT bor.assembly_item_id '||
1129 ' ,bor.Organization_id '||
1130 ' ,BOR.ALTERNATE_ROUTING_DESIGNATOR '||
1131 ' ,BOR.ROUTING_SEQUENCE_ID '||
1132 ' ,BOS.OPERATION_SEQUENCE_ID '||
1133 ' ,BORE.RESOURCE_SEQ_NUM '||
1134 ' ,BORE.RESOURCE_ID '||
1135 ' ,'''||l_varchar_col_rec.column_name||''' '||
1136 ' ,bore.'||l_varchar_col_rec.column_name||' '||
1137 ' FROM bom_operational_routings bor, bom_operation_sequences bos,'||
1138 ' bom_operation_resources bore '||
1139 ' WHERE 1=1 '||
1140 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
1141 ' AND bore.operation_sequence_id=bos.operation_sequence_id '||
1142 ' AND bor.assembly_item_id ='||l_item_id||' '||
1143 ' AND bor.organization_id = '||l_org_id||' '||
1144 ' AND bore.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
1145 ' AND rownum < '||(row_limit/25);
1146
1147 ln_cursor := dbms_sql.open_cursor;
1148 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
1149 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
1150 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
1151
1152 End Loop;
1153
1154 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
1155 ' Organization_id "Organization id", '||
1156 ' char_Col3 "ALTERNATE ROUTING DESIGNATOR", '||
1157 ' Num_Col1 "ROUTING SEQUENCE ID", '||
1158 ' Num_Col2 "OPERATION SEQUENCE ID", '||
1159 ' Num_Col3 "RESOURCE SEQ NUM", '||
1160 ' Num_Col4 "RESOURCE ID", '||
1161 ' Char_Col1 "Column Name", '||
1162 ' Char_Col2 "Column Value" '||
1163 ' From bom_diag_temp_tab where 1=1 ';
1164
1165 sqltxt := sqltxt || ' and rownum< '||row_limit;
1166 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col2,Num_col3,char_col1';
1167
1168 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_operation_resources table ');
1169 If (num_rows = row_limit -1 ) Then
1170 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1171 End If;
1172 statusStr := 'SUCCESS';
1173 isFatal := 'FALSE';
1174 End;
1175 /* End of bom_operation_resources script */
1176
1177 /* Script to verify records in bom_sub_operation_resources table */
1178 Begin
1179 delete from bom_diag_temp_tab; -- Clear the temporary tables
1180 For l_varchar_col_rec in c_varchar_cols_cur('BOM_SUB_OPERATION_RESOURCES',l_oracle_schema) Loop
1181 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
1182
1183 sql_stmt := '';
1184 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
1185 ' char_col3,Num_col1,Num_col2,Num_col3,Num_col4,Num_col5,Num_col6,char_col1,char_col2)'||
1186 ' SELECT bor.assembly_item_id '||
1187 ' ,bor.Organization_id '||
1188 ' ,BOR.ALTERNATE_ROUTING_DESIGNATOR '||
1189 ' ,BOR.ROUTING_SEQUENCE_ID '||
1190 ' ,BOS.OPERATION_SEQUENCE_ID '||
1191 ' ,BSOR.SUBSTITUTE_GROUP_NUM '||
1192 ' ,BSOR.RESOURCE_ID '||
1193 ' ,BSOR.SCHEDULE_SEQ_NUM '||
1197 ' FROM bom_operational_routings bor, bom_operation_sequences bos,'||
1194 ' ,BSOR.REPLACEMENT_GROUP_NUM '||
1195 ' ,'''||l_varchar_col_rec.column_name||''' '||
1196 ' ,bsor.'||l_varchar_col_rec.column_name||' '||
1198 ' bom_sub_operation_resources bsor '||
1199 ' WHERE 1=1 '||
1200 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
1201 ' AND bsor.operation_sequence_id=bos.operation_sequence_id '||
1202 ' AND bor.assembly_item_id ='||l_item_id||' '||
1203 ' AND bor.organization_id = '||l_org_id||' '||
1204 ' AND bsor.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
1205 ' AND rownum < '||(row_limit/25);
1206
1207 ln_cursor := dbms_sql.open_cursor;
1208 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
1209 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
1210 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
1211
1212 End Loop;
1213
1214 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
1215 ' Organization_id "Organization id", '||
1216 ' char_Col3 "ALTERNATE ROUTING DESIGNATOR", '||
1217 ' Num_Col1 "ROUTING SEQUENCE ID", '||
1218 ' Num_Col2 "OPERATION SEQUENCE ID", '||
1219 ' Num_Col3 "SUBSTITUTE GROUP NUM", '||
1220 ' Num_Col4 "RESOURCE ID", '||
1221 ' Num_Col5 "SCHEDULE SEQ NUM", '||
1222 ' Num_Col6 "REPLACEMENT GROUP NUM", '||
1223 ' Char_Col1 "Column Name", '||
1224 ' Char_Col2 "Column Value" '||
1225 ' From bom_diag_temp_tab where 1=1 ';
1226
1227 sqltxt := sqltxt || ' and rownum< '||row_limit;
1228 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col2,Num_col3,Num_col6,char_col1';
1229
1230 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_sub_operation_resources table ');
1231 If (num_rows = row_limit -1 ) Then
1232 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1233 End If;
1234 statusStr := 'SUCCESS';
1235 isFatal := 'FALSE';
1236 End;
1237 /* End of bom_sub_operation_resources script */
1238
1239
1240 /* Script to verify records in bom_operation_networks table */
1241 Begin
1242 delete from bom_diag_temp_tab; -- Clear the temporary tables
1243 For l_varchar_col_rec in c_varchar_cols_cur('BOM_OPERATION_NETWORKS',l_oracle_schema) Loop
1244 --JTF_DIAGNOSTIC_COREAPI.Line_Out('Column: '||l_varchar_col_rec.column_name);
1245
1246 sql_stmt := '';
1247 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id, '||
1248 ' char_col3,Num_col1,Num_col2,Num_col3,Num_col4,char_col1,char_col2)'||
1249 ' SELECT bor.assembly_item_id '||
1250 ' ,bor.Organization_id '||
1251 ' ,BOR.ALTERNATE_ROUTING_DESIGNATOR '||
1252 ' ,BOR.ROUTING_SEQUENCE_ID '||
1253 ' ,BOS.OPERATION_SEQUENCE_ID '||
1254 ' ,BON.FROM_OP_SEQ_ID '||
1255 ' ,BON.TO_OP_SEQ_ID '||
1256 ' ,'''||l_varchar_col_rec.column_name||''' '||
1257 ' ,bon.'||l_varchar_col_rec.column_name||' '||
1258 ' FROM bom_operational_routings bor, bom_operation_sequences bos,'||
1259 ' bom_operation_networks bon '||
1260 ' WHERE 1=1 '||
1261 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
1262 ' AND bon.to_op_seq_id=bos.operation_sequence_id '||
1263 ' AND bor.assembly_item_id ='||l_item_id||' '||
1264 ' AND bor.organization_id = '||l_org_id||' '||
1265 ' AND bon.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
1266 ' AND rownum < '||(row_limit/25);
1267
1268 ln_cursor := dbms_sql.open_cursor;
1269 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
1270 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
1271 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
1272
1273 End Loop;
1274
1275 sqltxt:=' Select Inventory_Item_Id "Assembly Item Id", '||
1276 ' Organization_id "Organization id", '||
1277 ' char_Col3 "ALTERNATE ROUTING DESIGNATOR", '||
1278 ' Num_Col1 "ROUTING SEQUENCE ID", '||
1279 ' Num_Col2 "OPERATION SEQUENCE ID", '||
1280 ' Num_Col3 "FROM OP SEQ ID", '||
1281 ' Num_Col4 "TO OP SEQ ID", '||
1282 ' Char_Col1 "Column Name", '||
1283 ' Char_Col2 "Column Value" '||
1284 ' From bom_diag_temp_tab where 1=1 ';
1285
1286 sqltxt := sqltxt || ' and rownum< '||row_limit;
1287 sqltxt :=sqltxt||' order by inventory_item_id, organization_id, char_col3,Num_col2,Num_col3,char_col1';
1288
1289 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Columns containing Trailing Spaces in bom_operation_networks table ');
1290 If (num_rows = row_limit -1 ) Then
1291 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1292 End If;
1293 statusStr := 'SUCCESS';
1294 isFatal := 'FALSE';
1295 End;
1296 /* End of bom_operation_networks script */
1297
1298 /* End of Scripts to check for trailing spaces in Rtg Tables */
1299
1300 <<l_test_end>>
1301 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
1302 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1303 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1304
1305 EXCEPTION
1306 when others then
1307 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
1308 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
1309 statusStr := 'FAILURE';
1310 errStr := sqlerrm ||' occurred in script. ';
1314 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1311 fixInfo := 'Unexpected Exception in BOMDGTSB.pls';
1312 isFatal := 'FALSE';
1313 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1315
1316 END runTest;
1317
1318 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
1319 BEGIN
1320 name := 'Attribute Trailing Spaces';
1321 END getComponentName;
1322
1323 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
1324 BEGIN
1325 descStr := ' This diagnostic test checks Items/Bills/Routing tables for <BR/>
1326 columns holding values with trailing spaces and <BR/>
1327 provides suggestions on how to solve possible issues.<BR/>
1328 Run this health check when experiencing issues with a specific Item.<BR/>
1329 ( E.g. FRM-40654 : Record has been updated.)<BR/>
1330 Inputs for fields OrgId and ItemID are mandatory. ';
1331 END getTestDesc;
1332
1333 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
1334 BEGIN
1335 name := 'Attribute Trailing Spaces';
1336 END getTestName;
1337
1338 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
1339 tempDependencies JTF_DIAG_DEPENDTBL;
1340
1341 BEGIN
1342 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
1343 END getDependencies;
1344
1345 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
1346 BEGIN
1347 str := 'FALSE';
1348 END isDependencyPipelined;
1349
1350
1351 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
1352 tempOutput JTF_DIAG_OUTPUTTBL;
1353 BEGIN
1354 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1355 outputValues := tempOutput;
1356 EXCEPTION
1357 when others then
1358 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1359 END getOutputValues;
1360
1361
1362 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
1363 tempInput JTF_DIAG_INPUTTBL;
1364 BEGIN
1365 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1366 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
1367 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.bom.diag.lov.ItemLov');-- Lov name modified to ItemId for bug 6412260
1368 defaultInputValues := tempInput;
1369 EXCEPTION
1370 when others then
1371 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1372 END getDefaultTestParams;
1373
1374 Function getTestMode return INTEGER IS
1375 BEGIN
1376 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
1377
1378 END getTestMode;
1379
1380 END BOM_DIAGUNITTEST_TSDATA;