[Home] [Help]
PACKAGE BODY: APPS.BOM_DIAGUNITTEST_EXPDATA
Source
1 package body BOM_DIAGUNITTEST_EXPDATA as
2 /* $Header: BOMDGEXB.pls 120.1 2007/12/26 09:49:25 vggarg noship $ */
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_ret_status BOOLEAN;
30 l_status VARCHAR2 (1);
31 l_industry VARCHAR2 (1);
32 l_oracle_schema VARCHAR2 (30);
33
34 CURSOR c_item_valid (cp_n_item_id IN NUMBER, cp_n_org_id IN NUMBER) IS
35 SELECT count(*)
36 FROM mtl_system_items_b
37 WHERE inventory_item_id = cp_n_item_id
38 AND organization_id = nvl(cp_n_org_id,organization_id);
39
40 BEGIN
41 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
42 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
43 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
44
45 /*Initializing local vars */
46 row_limit :=1000; /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
47 l_count := 0;
48
49 -- accept input
50 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
51 l_item_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
52
53 If l_item_id is NULL then
54 JTF_DIAGNOSTIC_COREAPI.errorprint('Input Item Id is mandatory.');
55 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide a valid value for the Item Id.');
56 statusStr := 'FAILURE';
57 isFatal := 'TRUE';
58 fixInfo := ' Please review the error message below and take corrective action. ';
59 errStr := ' Invalid value for input field Item Id. It is a mandatory input.';
60
61 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
62 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
63 Return;
64 Else /* l_item_id is not null */
65 OPEN c_item_valid (l_item_id, l_org_id);
66 FETCH c_item_valid INTO l_count;
67 CLOSE c_item_valid;
68
69 IF (l_count IS NULL) OR (l_count = 0) THEN
70 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Item and Organization Combination');
71 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter right combination of Item and Organization ');
72 statusStr := 'FAILURE';
73 errStr := 'Invalid Item and Organization Combination';
74 fixInfo := ' Please review the error message below and take corrective action. ';
75 isFatal := 'TRUE';
76 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
77 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
78 RETURN;
79 END IF;
80 End If; /* l_item_id is null */
81
82 /* Start of the diagnostic test script */
83 /* Get the application installation info. References to Data Dictionary Objects without schema name
84 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
85 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
86
87 l_ret_status := fnd_installation.get_app_info ('BOM'
88 , l_status
89 , l_industry
90 , l_oracle_schema
91 );
92
93 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
94
95 sqltxt := ' Select '||
96 ' BE.TOP_BILL_SEQUENCE_ID "TOP BILL SEQUENCE ID", '||
97 ' BE.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
98 ' BE.ORGANIZATION_ID "ORGANIZATION ID", '||
99 ' BE.EXPLOSION_TYPE "EXPLOSION TYPE", '||
100 ' BE.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID", '||
101 ' BE.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
102 ' BE.PLAN_LEVEL "PLAN LEVEL", '||
103 ' BE.EXTENDED_QUANTITY "EXTENDED QUANTITY", '||
104 ' BE.SORT_ORDER "SORT ORDER", '||
105 ' to_char(BE.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
106 ' BE.CREATED_BY "CREATED BY", '||
107 ' to_char(BE.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
108 ' BE.LAST_UPDATED_BY "LAST UPDATED BY", '||
109 ' BE.TOP_ITEM_ID "TOP ITEM ID", '||
110 ' BE.CONTEXT "CONTEXT", '||
111 ' BE.ATTRIBUTE1 "ATTRIBUTE1", '||
112 ' BE.ATTRIBUTE2 "ATTRIBUTE2", '||
113 ' BE.ATTRIBUTE3 "ATTRIBUTE3", '||
114 ' BE.ATTRIBUTE4 "ATTRIBUTE4", '||
115 ' BE.ATTRIBUTE5 "ATTRIBUTE5", '||
116 ' BE.ATTRIBUTE6 "ATTRIBUTE6", '||
117 ' BE.ATTRIBUTE7 "ATTRIBUTE7", '||
118 ' BE.ATTRIBUTE8 "ATTRIBUTE8", '||
119 ' BE.ATTRIBUTE9 "ATTRIBUTE9", '||
120 ' BE.ATTRIBUTE10 "ATTRIBUTE10", '||
121 ' BE.ATTRIBUTE11 "ATTRIBUTE11", '||
122 ' BE.ATTRIBUTE12 "ATTRIBUTE12", '||
123 ' BE.ATTRIBUTE13 "ATTRIBUTE13", '||
124 ' BE.ATTRIBUTE14 "ATTRIBUTE14", '||
125 ' BE.ATTRIBUTE15 "ATTRIBUTE15", '||
126 ' BE.COMPONENT_QUANTITY "COMPONENT QUANTITY", '||
127 ' BE.SO_BASIS "SO BASIS", '||
128 ' BE.OPTIONAL "OPTIONAL", '||
129 ' BE.MUTUALLY_EXCLUSIVE_OPTIONS "MUTUALLY EXCLUSIVE OPTIONS", '||
130 ' BE.CHECK_ATP "CHECK ATP", '||
131 ' BE.SHIPPING_ALLOWED "SHIPPING ALLOWED", '||
132 ' BE.REQUIRED_TO_SHIP "REQUIRED TO SHIP", '||
133 ' BE.REQUIRED_FOR_REVENUE "REQUIRED FOR REVENUE", '||
134 ' BE.INCLUDE_ON_SHIP_DOCS "INCLUDE ON SHIP DOCS", '||
135 ' BE.INCLUDE_ON_BILL_DOCS "INCLUDE ON BILL DOCS", '||
136 ' BE.LOW_QUANTITY "LOW QUANTITY", '||
137 ' BE.HIGH_QUANTITY "HIGH QUANTITY", '||
138 ' BE.PICK_COMPONENTS "PICK COMPONENTS", '||
139 ' BE.PRIMARY_UOM_CODE "PRIMARY UOM CODE", '||
140 ' BE.PRIMARY_UNIT_OF_MEASURE "PRIMARY UNIT OF MEASURE", '||
141 ' BE.BASE_ITEM_ID "BASE ITEM ID", '||
142 ' BE.ATP_COMPONENTS_FLAG "ATP COMPONENTS FLAG", '||
143 ' BE.ATP_FLAG "ATP FLAG", '||
144 ' BE.BOM_ITEM_TYPE "BOM ITEM TYPE", '||
145 ' BE.PICK_COMPONENTS_FLAG "PICK COMPONENTS FLAG", '||
146 ' BE.REPLENISH_TO_ORDER_FLAG "REPLENISH TO ORDER FLAG", '||
147 ' BE.SHIPPABLE_ITEM_FLAG "SHIPPABLE ITEM FLAG", '||
148 ' BE.CUSTOMER_ORDER_FLAG "CUSTOMER ORDER FLAG", '||
149 ' BE.INTERNAL_ORDER_FLAG "INTERNAL ORDER FLAG", '||
150 ' BE.CUSTOMER_ORDER_ENABLED_FLAG "CUSTOMER ORDER ENABLED FLAG", '||
151 ' BE.INTERNAL_ORDER_ENABLED_FLAG "INTERNAL ORDER ENABLED FLAG", '||
152 ' BE.SO_TRANSACTIONS_FLAG "SO TRANSACTIONS FLAG", '||
153 ' BE.DESCRIPTION "DESCRIPTION", '||
154 ' BE.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
155 ' BE.COMPONENT_CODE "COMPONENT CODE", '||
156 ' BE.LOOP_FLAG "LOOP FLAG", '||
157 ' BE.PARENT_BOM_ITEM_TYPE "PARENT BOM ITEM TYPE", '||
158 ' BE.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
159 ' BE.ITEM_NUM "ITEM NUM", '||
160 ' to_char(BE.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE",'||
161 ' to_char(BE.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE", '||
162 ' to_char(BE.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
163 ' BE.REXPLODE_FLAG "REXPLODE FLAG", '||
164 ' BE.COMMON_BILL_SEQUENCE_ID "COMMON BILL SEQUENCE ID", '||
165 ' BE.COMP_BILL_SEQ_ID "COMP BILL SEQ ID", '||
166 ' BE.COMP_COMMON_BILL_SEQ_ID "COMP COMMON BILL SEQ ID", '||
167 ' BE.EXPLODE_GROUP_ID "EXPLODE GROUP ID", '||
168 ' BE.NUM_COL1 "NUM COL1", '||
169 ' BE.NUM_COL2 "NUM COL2", '||
170 ' BE.NUM_COL3 "NUM COL3", '||
171 ' to_char(BE.DATE_COL1,''DD-MON-YYYY HH24:MI:SS'') "DATE COL1", '||
172 ' to_char(BE.DATE_COL2,''DD-MON-YYYY HH24:MI:SS'') "DATE COL2", '||
173 ' to_char(BE.DATE_COL3,''DD-MON-YYYY HH24:MI:SS'') "DATE COL3", '||
174 ' BE.CHAR_COL1 "CHAR COL1", '||
175 ' BE.CHAR_COL2 "CHAR COL2", '||
176 ' BE.CHAR_COL3 "CHAR COL3", '||
177 ' BE.AUTO_REQUEST_MATERIAL "AUTO REQUEST MATERIAL", '||
178 ' BE.INCLUDE_IN_COST_ROLLUP "INCLUDE IN COST ROLLUP", '||
179 ' BE.COMPONENT_YIELD_FACTOR "COMPONENT YIELD FACTOR", '||
180 ' BE.PLANNING_FACTOR "PLANNING FACTOR", '||
181 ' BE.CHANGE_NOTICE "CHANGE NOTICE", '||
182 ' BE.PARENT_SORT_ORDER "PARENT SORT ORDER" '||
183 ' ,BE.SUGGESTED_VENDOR_NAME "SUGGESTED VENDOR NAME" '||
184 ' ,BE.VENDOR_ID "VENDOR ID" '||
185 ' ,BE.UNIT_PRICE "UNIT PRICE" '||
186 ' ,BE.REQUEST_ID "REQUEST ID" '||
187 ' ,BE.BASIS_TYPE "BASIS TYPE" '||
188 ' ,BE.SOURCE_BILL_SEQUENCE_ID "SOURCE BILL SEQUENCE ID" '||
189 ' ,BE.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
190 ' ,BE.COMP_SOURCE_BILL_SEQ_ID "COMP SOURCE BILL SEQ ID" '||
191 ' FROM bom_explosions be WHERE 1=1 ';
192
193 if l_org_id is not null then
194 sqltxt :=sqltxt||' and be.organization_id = '||l_org_id;
195 end if;
196
197 if l_item_id is not null then
198 sqltxt :=sqltxt||' and be.top_item_id = '||l_item_id;
199 end if;
200
201 sqltxt := sqltxt || ' and rownum< '||row_limit;
202 sqltxt := sqltxt || ' order by be.organization_id,be.sort_order ';
203 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Bom Explosions Data ');
204 If (num_rows = row_limit -1 ) Then
205 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/>');
206 End If;
207 statusStr := 'SUCCESS';
208 isFatal := 'FALSE';
209
210 <<l_test_end>>
211 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
212 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
213 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
214
215 EXCEPTION
216 when others then
217 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
218 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
219 statusStr := 'FAILURE';
220 errStr := sqlerrm ||' occurred in script. ';
221 fixInfo := 'Unexpected Exception in BOMDGEXB.pls';
222 isFatal := 'FALSE';
223 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
224 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
225 END runTest;
226
227 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
228 BEGIN
229 name := 'Bom Explosions Data Collection';
230 END getComponentName;
231
232 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
233 BEGIN
234 descStr := ' This data collection script collects data about Bom Explosions Details. <BR/>
235 Input for field ItemId is mandatory. ';
236 END getTestDesc;
237
238 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
239 BEGIN
240 name := 'Bom Explosions Data Collection';
241 END getTestName;
242
243 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
244 tempDependencies JTF_DIAG_DEPENDTBL;
245
246 BEGIN
247 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
248 END getDependencies;
249
250 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
251 BEGIN
252 str := 'FALSE';
253 END isDependencyPipelined;
254
255
256 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
257 tempOutput JTF_DIAG_OUTPUTTBL;
258 BEGIN
259 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
260 outputValues := tempOutput;
261 EXCEPTION
262 when others then
263 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
264 END getOutputValues;
265
266
267 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
268 tempInput JTF_DIAG_INPUTTBL;
269 BEGIN
270 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
271 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
272 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.bom.diag.lov.ItemLov'); -- Lov name modified to ItemId for bug 6412260
273 defaultInputValues := tempInput;
274 EXCEPTION
275 when others then
276 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
277 END getDefaultTestParams;
278
279 Function getTestMode return INTEGER IS
280 BEGIN
284
281 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
282
283 END getTestMode;
285 END BOM_DIAGUNITTEST_EXPDATA;