[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_COMINGLING
Source
1 package body INV_DIAG_OH_COMINGLING as
2 /* $Header: INVDOH4B.pls 120.1 2011/03/23 13:26:37 rkatoori noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 null;
7 END init;
8
9 PROCEDURE cleanup IS
10 BEGIN
11 -- test writer could insert special cleanup code here
12 NULL;
13 END cleanup;
14
15 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
16 report OUT NOCOPY JTF_DIAG_REPORT,
17 reportClob OUT NOCOPY CLOB) IS
18 reportStr LONG; -- REPORT
19 sqltxt VARCHAR2(9999); -- SQL select statement
20 c_username VARCHAR2(50); -- accept input for username
21 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
22 errStr VARCHAR2(4000); -- error message
23 fixInfo VARCHAR2(4000); -- fix tip
24 isFatal VARCHAR2(50); -- TRUE or FALSE
25 dummy_num NUMBER;
26 row_limit NUMBER;
27 l_org_id NUMBER;
28
29 BEGIN
30 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
31 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
32 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
33
34 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
35
36 sqltxt := ' SELECT mp.organization_code' ||
37 ' || '' (''' ||
38 ' ||x.organization_id' ||
39 ' ||'')'' "Organization|Code (Id)" ,' ||
40 ' mif.item_number' ||
41 ' || '' (''' ||
42 ' ||x.inventory_item_id' ||
43 ' ||'')'' "Item (Id)" ,' ||
44 ' x.subinventory_code "Subinv" ,' ||
45 ' x.locator_id "Loc id" ,' ||
46 ' x.lot_number "Lot Number" ,' ||
47 ' mtsv.status_code' ||
48 ' ||'' (''' ||
49 ' ||x.status_id' ||
50 ' ||'')'' "StatusCode (Id)"' ||
51 ' FROM' ||
52 ' ( SELECT organization_id ,' ||
53 ' inventory_item_id,' ||
54 ' subinventory_code,' ||
55 ' locator_id ,' ||
56 ' lot_number ,' ||
57 ' status_id ,' ||
58 ' COUNT(*) C1' ||
59 ' FROM mtl_onhand_quantities_detail moqd1' ||
60 ' WHERE lpn_id is null ' ||
61 ' GROUP BY organization_id ,' ||
62 ' inventory_item_id ,' ||
63 ' subinventory_code ,' ||
64 ' locator_id ,' ||
65 ' lot_number ,' ||
66 ' status_id' ||
67 ' ) X ,' ||
68 ' (SELECT organization_id ,' ||
69 ' inventory_item_id,' ||
70 ' subinventory_code,' ||
71 ' locator_id ,' ||
72 ' lot_number ,' ||
73 ' COUNT(*) C2' ||
74 ' FROM mtl_onhand_quantities_detail moqd2' ||
75 ' WHERE lpn_id is null ' ||
76 ' GROUP BY organization_id ,' ||
77 ' inventory_item_id ,' ||
78 ' subinventory_code ,' ||
79 ' locator_id ,' ||
80 ' lot_number' ||
81 ' ) Y ,' ||
82 ' mtl_parameters mp ,' ||
83 ' mtl_item_flexfields mif ,' ||
84 ' mtl_material_statuses_vl mtsv' ||
85 ' WHERE mp.organization_id = x.organization_id' ||
86 ' AND x.organization_id = mif.organization_id' ||
87 ' AND x.inventory_item_id = mif.inventory_item_id' ||
88 ' AND x.organization_id = y.organization_id' ||
89 ' AND x.inventory_item_id = y.inventory_item_id' ||
90 ' AND x.subinventory_code = y.subinventory_code' ||
91 ' AND NVL(x.locator_id,-9999) = NVL(y.locator_id,-9999)' ||
92 ' AND NVL(x.lot_number,''@@@@'') = NVL(y.lot_number,''@@@@'')' ||
93 ' AND x.C1 <> y.C2' ||
94 ' AND mtsv.status_id = x.status_id' ||
95 ' AND mp.default_status_id IS NOT NULL' ||
96 ' AND mif.serial_number_control_code IN (1,6) ';
97
98 IF l_org_id IS NOT NULL THEN
99 sqltxt := sqltxt || ' AND x.organization_id = ' || l_org_id ;
100 END IF;
101 sqltxt := sqltxt ||
102 ' ORDER BY x.organization_id, x.inventory_item_id,'||
103 ' x.subinventory_code, x.locator_id, x.lot_number';
104
105 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand Records for which commingling of the status exists');
106
107 reportStr := 'The test completed as expected';
108 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
109 statusStr := 'SUCCESS';
110 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
111 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
112
113 EXCEPTION
114 when others then
115 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
116 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
117 statusStr := 'FAILURE';
118 errStr := sqlerrm ||' occurred in script Exception handled';
119 fixInfo := 'Unexpected Exception in INVDOH4B.pls';
120 isFatal := 'FALSE';
121 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
122 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
123 END runTest;
124
125 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
126 BEGIN
127 name := 'MATSTATUS';
128 END getComponentName;
129
130 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
131 BEGIN
132 descStr := 'Onhand Records for which commingling of the status exist';
133 END getTestDesc;
134
135 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
136 BEGIN
137 name := 'commingling of Status in Onhand';
138 END getTestName;
139
140
141 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
142 tempDependencies JTF_DIAG_DEPENDTBL;
143
144 BEGIN
145 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
146 END getDependencies;
147
148 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
149 BEGIN
150 str := 'FALSE';
151 END isDependencyPipelined;
152
153 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
154 tempOutput JTF_DIAG_OUTPUTTBL;
155 BEGIN
156 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
157 outputValues := tempOutput;
158 EXCEPTION
159 when others then
160 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
161 END getOutputValues;
162
163
164 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
165 tempInput JTF_DIAG_INPUTTBL;
166 BEGIN
167 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
168 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
169 defaultInputValues := tempInput;
170
171 EXCEPTION
172 when others then
173 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
174 END getDefaultTestParams;
175
176 Function getTestMode return INTEGER IS
177 BEGIN
178 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
179 END getTestMode;
180 END INV_DIAG_OH_COMINGLING;