1 PACKAGE BODY INV_DIAG_SER_NOT_MRK AS
2 /* $Header: INVDP05B.pls 120.0.12000000.1 2007/06/22 01:18:20 musinha noship $ */
3
4 ------------------------------------------------------------
5 -- procedure to initialize test datastructures
6 -- executed prior to test run leave body as null otherwize
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 leave body as null otherwize
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 execute the PLSQL test
26 -- the inputs needed for the test are passed in and a report object and CLOB are
27 -- returned.
28 -- note the way that support API writes to the report CLOB.
29 ------------------------------------------------------------
30 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
31 report OUT NOCOPY JTF_DIAG_REPORT,
32 reportClob OUT NOCOPY CLOB) IS
33 reportStr LONG;
34 counter NUMBER;
35 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
36 c_userid VARCHAR2(50);
37 statusStr VARCHAR2(50);
38 errStr VARCHAR2(4000);
39 fixInfo VARCHAR2(4000);
40 isFatal VARCHAR2(50);
41 dummy_num NUMBER;
42 sqltxt VARCHAR2 (2000);
43 l_org_id NUMBER;
44 l_item_id NUMBER;
45 l_resp fnd_responsibility_tl.Responsibility_Name%type :='Inventory';
46
47 BEGIN
48 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
49 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
50 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
51 --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
52
53 /*
54 -- check whether user has 'Inventory' responsibilty to execute diagnostics script.
55 IF NOT INV_DIAG_GRP.check_responsibility(p_responsibility_name => l_resp) THEN -- l_resp = 'Inventory'
56 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' You do not have the privilege to run this Diagnostics.');
57 statusStr := 'FAILURE';
58 errStr := 'This test requires Inventory Responsibility Role';
59 fixInfo := 'Please contact your sysadmin to get Inventory Responsibility';
60 isFatal := 'FALSE';
61 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
62 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
63 RETURN;
64 END IF;
65
66 */
67
68 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
69 l_item_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
70
71
72 sqltxt := ' SELECT count(*) ' ||
73 ' FROM' ||
74 ' mtl_serial_numbers msn ,' ||
75 ' mtl_system_items msi,' ||
76 ' wsh_delivery_details wdd' ||
77 ' where msn.inventory_item_id=msi.inventory_item_id' ||
78 ' and msi.organization_id=msn.current_organization_id' ||
79 ' and msn.current_status =3' ||
80 ' and msi.reservable_type=1' ||
81 ' and msi.serial_number_control_code not in (1,6)' ||
82 ' and (msn.group_mark_id is NULL or msn.group_mark_id = -1 )' ||
83 ' and msn.current_organization_id = wdd.organization_id ' ||
84 ' and msn.inventory_item_id = wdd.inventory_item_id' ||
85 ' and msn.serial_number = wdd.serial_number' ||
86 ' and wdd.transaction_temp_id is null' ||
87 ' and wdd.released_status in (''C'',''Y'') ' ||
88 ' and wdd.inv_interfaced_flag in (''N'',''P'')' ;
89
90 IF l_org_id IS NOT NULL THEN
91 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
92 END IF;
93
94 IF l_item_id iS NOT NULL THEN
95 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
96 END IF;
97
98 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Total number of staged serial and not marked, serial in delivery details');
99
100 sqltxt := ' SELECT count(*) ' ||
101 ' FROM' ||
102 ' mtl_serial_numbers msn,' ||
103 ' mtl_system_items msi,' ||
104 ' wsh_delivery_details wdd,' ||
105 ' mtl_serial_numbers_temp msnt' ||
106 ' where msn.inventory_item_id=msi.inventory_item_id' ||
107 ' and msi.organization_id=msn.current_organization_id' ||
108 ' and msn.current_status =3' ||
109 ' and msi.reservable_type=1' ||
110 ' and msi.serial_number_control_code not in (1,6)' ||
111 ' and ( group_mark_id is NULL or group_mark_id =-1 )' ||
112 ' and msn.current_organization_id = wdd.organization_id ' ||
113 ' and msn.inventory_item_id = wdd.inventory_item_id' ||
114 ' and wdd.released_status in (''C'',''Y'') ' ||
115 ' and wdd.inv_interfaced_flag in (''N'',''P'')' ||
116 ' and wdd.transaction_temp_id = msnt.transaction_temp_id' ||
117 ' and msn.serial_number BETWEEN msnt.fm_serial_number and msnt.to_serial_number ';
118
119 IF l_org_id IS NOT NULL THEN
120 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
121 END IF;
122
123 IF l_item_id iS NOT NULL THEN
124 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
125 END IF;
126
127 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Total number of staged serial and not marked, serial in pending serials');
128
129 sqltxt := ' SELECT count(*) ' ||
130 ' FROM' ||
131 ' mtl_serial_numbers msn ,' ||
132 ' mtl_system_items msi,' ||
133 ' wsh_delivery_details wdd,' ||
134 ' wsh_serial_numbers wsn' ||
135 ' where msn.inventory_item_id=msi.inventory_item_id' ||
136 ' and msi.organization_id=msn.current_organization_id' ||
137 ' and msn.current_status =3' ||
138 ' and msi.reservable_type=1' ||
139 ' and msi.serial_number_control_code not in (1,6)' ||
140 ' and ( group_mark_id is NULL or group_mark_id =-1 )' ||
141 ' and msn.current_organization_id = wdd.organization_id ' ||
142 ' and msn.inventory_item_id = wdd.inventory_item_id' ||
143 ' and wdd.transaction_temp_id is not null' ||
144 ' and wdd.released_status in (''C'',''Y'') ' ||
145 ' and wdd.inv_interfaced_flag in (''N'',''P'')' ||
146 ' and wdd.delivery_detail_id = wsn.delivery_detail_id' ||
147 ' and msn.serial_number BETWEEN wsn.fm_serial_number and wsn.to_serial_number ';
148
149 IF l_org_id IS NOT NULL THEN
150 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
151 END IF;
152
153 IF l_item_id iS NOT NULL THEN
154 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
155 END IF;
156
157 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Total number of staged serial and not marked, serial in deliveried serials');
158
159 sqltxt := 'select mp.organization_code || '' ('' || msn.current_organization_id ||'')'' "Organization|Code (Id)" ,' ||
160 ' msi.item_number || '' ('' || msn.inventory_item_id || '')'' "Item (Id)" ,' ||
161 ' msn.serial_number "Serial Number",' ||
162 ' msn.group_mark_id "Group Mark Id",' ||
163 ' wdd.source_header_number "Order number",' ||
164 ' wdd.source_line_id "Order line id",' ||
165 ' wdd.delivery_Detail_id "Delivery detail Id"' ||
166 ' from ' ||
167 ' mtl_serial_numbers msn ,mtl_item_flexfields msi,' ||
168 ' mtl_parameters mp, wsh_delivery_Details wdd' ||
169 ' where mp.organization_id = msn.current_organization_id ' ||
170 ' and msn.inventory_item_id=msi.inventory_item_id' ||
171 ' and msi.organization_id=msn.current_organization_id' ||
172 ' and msn.current_status =3 ' ||
173 ' and msi.reservable_type=1' ||
174 ' and msi.serial_number_control_code not in (1,6)' ||
175 ' and (group_mark_id is NULL or group_mark_id =-1 ) ' ||
176 ' and msn.serial_number = wdd.serial_number ' ||
177 ' and wdd.released_status in (''C'',''Y'') ' ||
178 ' and wdd.inv_interfaced_flag in (''N'',''P'')' ||
179 ' and wdd.organization_id=msn.current_organization_id' ||
180 ' and wdd.inventory_item_id=msn.inventory_item_id' ||
181 ' and wdd.serial_number=msn.serial_number' ||
182 ' and wdd.transaction_temp_id is null ';
183
184 IF l_org_id IS NOT NULL THEN
185 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
186 END IF;
187
188 IF l_item_id iS NOT NULL THEN
189 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
190 END IF;
191
192 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of staged / shipped (and not inventory interfaced) serials, serials in deliver details');
193
194 sqltxt := ' select mp.organization_code || ''('' || msn.current_organization_id || '')'' "Organization|Code (Id)" ,' ||
195 ' msi.item_number || ''('' || msn.inventory_item_id || '')'' "Item (Id)" ,' ||
196 ' msn.serial_number "Serial Number",' ||
197 ' msn.group_mark_id "Group Mark Id",' ||
198 ' wdd.source_header_number "Order number",' ||
199 ' wdd.source_line_id "Order line id",' ||
200 ' wdd.delivery_Detail_id "Delivery detail Id"' ||
201 ' from mtl_serial_numbers msn ,' ||
202 ' mtl_item_flexfields msi,' ||
203 ' mtl_parameters mp, ' ||
204 ' wsh_delivery_details wdd,' ||
205 ' mtl_serial_numbers_temp msnt' ||
206 ' where mp.organization_id = msn.current_organization_id' ||
207 ' and msn.inventory_item_id=msi.inventory_item_id' ||
208 ' and msi.organization_id=msn.current_organization_id' ||
209 ' and msn.current_status =3' ||
210 ' and msi.reservable_type=1' ||
211 ' and msi.serial_number_control_code not in (1,6)' ||
212 ' and (group_mark_id is NULL or group_mark_id =-1 )' ||
213 ' and msn.current_organization_id = wdd.organization_id ' ||
214 ' and msn.inventory_item_id = wdd.inventory_item_id' ||
215 ' and wdd.released_status in (''C'',''Y'') ' ||
216 ' and wdd.inv_interfaced_flag in (''N'',''P'')' ||
217 ' and wdd.transaction_temp_id = msnt.transaction_temp_id' ||
218 ' and msn.serial_number BETWEEN msnt.fm_serial_number and msnt.to_serial_number ';
219
220 IF l_org_id IS NOT NULL THEN
221 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
222 END IF;
223
224 IF l_item_id iS NOT NULL THEN
225 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
226 END IF;
227
228 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of staged / shipped (and not inventory interfaced) serials, serial in pending serials');
229
230 sqltxt := 'select mp.organization_code || '' ('' || msn.current_organization_id ||'')'' "Organization|Code (Id)" ,' ||
231 ' msi.item_number || '' ('' || msn.inventory_item_id || '')'' "Item (Id)" ,' ||
232 ' msn.serial_number "Serial Number",' ||
233 ' msn.group_mark_id "Group Mark Id",' ||
234 ' wdd.source_header_number "Order number",' ||
235 ' wdd.source_line_id "Order line id",' ||
236 ' wdd.delivery_Detail_id "Delivery detail Id"' ||
237 ' from' ||
238 ' mtl_serial_numbers msn ,mtl_item_flexfields msi,' ||
239 ' mtl_parameters mp, wsh_serial_numbers wsn ,wsh_delivery_details wdd' ||
240 ' where mp.organization_id = msn.current_organization_id ' ||
241 ' and msn.inventory_item_id=msi.inventory_item_id' ||
242 ' and msi.organization_id=msn.current_organization_id' ||
243 ' and msn.current_status =3 ' ||
244 ' and msi.reservable_type=1' ||
245 ' and msi.serial_number_control_code not in (1,6)' ||
246 ' and ( group_mark_id is NULL or group_mark_id =-1) ' ||
247 ' and wdd.transaction_temp_id is not NULL' ||
248 ' and wsn.delivery_detail_id=wdd.delivery_detail_id' ||
249 ' and wdd.organization_id=msn.current_organization_id' ||
250 ' and wdd.inventory_item_id=msn.inventory_item_id' ||
251 ' and wsn.fm_serial_number=msn.serial_number' ||
252 ' and wsn.fm_serial_number=wsn.to_serial_number' ||
253 ' and wdd.released_status in (''C'',''Y'') ' ||
254 ' and wdd.inv_interfaced_flag in (''N'',''P'') ' ;
255
256 IF l_org_id IS NOT NULL THEN
257 sqltxt := sqltxt || ' and msi.organization_id = ' || l_org_id ;
258 END IF;
259
260 IF l_item_id iS NOT NULL THEN
261 sqltxt := sqltxt || ' and msi.inventory_item_id = ' || l_item_id ;
262 END IF;
263
264 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of staged / shipped (and not inventory interfaced) serials, serials in delivered serials');
265
266 reportStr := 'The test completed as expected';
267 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
268 statusStr := 'SUCCESS';
269 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
270 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
275 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
276 statusStr := 'FAILURE';
277 errStr := sqlerrm ||' occurred in script Exception handled';
278 fixInfo := 'Unexpected Exception in INVDP05B.pls';
279 isFatal := 'FALSE';
280 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
281 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
282 END runTest;
283
284 ------------------------------------------------------------
285 -- procedure to report name back to framework
286 ------------------------------------------------------------
287 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
288 BEGIN
289 name := 'Pick Release and Reservation';
290 END getComponentName;
291
292 ------------------------------------------------------------
293 -- procedure to report test description back to framework
294 ------------------------------------------------------------
295 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
296 BEGIN
297 descStr := 'Details of staged / shipped (and not inventory interfaced) serials not marked';
298 END getTestDesc;
299
300 ------------------------------------------------------------
301 -- procedure to report test name back to framework
302 ------------------------------------------------------------
303 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
304 BEGIN
305 name := 'Staged Serial Not Marked';
306 END getTestName;
307
308 ------------------------------------------------------------
309 -- procedure to provide the default parameters for the test case.
310 -- please note the paramters have to be registered through the UI
311 -- before basic tests can be run.
312 --
313 ------------------------------------------------------------
314 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
315 tempInput JTF_DIAG_INPUTTBL;
316 BEGIN
317
318 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
319 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
320 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
321 defaultInputValues := tempInput;
322
323 EXCEPTION
324 when others then
325 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
326 END getDefaultTestParams;
327 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
328 tempDependencies JTF_DIAG_DEPENDTBL;
329
330 BEGIN
331 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
332 END getDependencies;
333
334 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
335 BEGIN
336 str := 'FALSE';
337 END isDependencyPipelined;
338
339 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
340 tempOutput JTF_DIAG_OUTPUTTBL;
341 BEGIN
342 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
343 outputValues := tempOutput;
344 EXCEPTION
345 when others then
346 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
347 END getOutputValues;
348
349 Function getTestMode return INTEGER IS
350 BEGIN
351 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
352
353 END getTestMode;
354
355 END INV_DIAG_SER_NOT_MRK;
[Home] [Help]