[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_PHY_INV_DUMP
Source
1 package body INV_DIAG_PHY_INV_DUMP as
2 /* $Header: INVDA07B.pls 120.0.12000000.1 2007/06/22 00:44:32 musinha noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 -- test writer
7 null;
8 END init;
9
10 PROCEDURE cleanup IS
11 BEGIN
12 -- test writer could insert special cleanup code here
13 NULL;
14 END cleanup;
15
16 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
17 report OUT NOCOPY JTF_DIAG_REPORT,
18 reportClob OUT NOCOPY CLOB) IS
19
20 reportStr LONG;
21 counter NUMBER;
22 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
23 c_userid VARCHAR2(50);
24 statusStr VARCHAR2(50);
25 errStr VARCHAR2(4000);
26 fixInfo VARCHAR2(4000);
27 isFatal VARCHAR2(50);
28 dummy_num NUMBER;
29 sqltxt VARCHAR2 (9999);
30 l_org_id NUMBER;
31 l_item_id NUMBER;
32 row_limit NUMBER;
33 l_script VARCHAR2(30);
34
35
36 BEGIN
37
38 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
39 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
40 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
41 --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
42
43 row_limit := INV_DIAG_GRP.g_max_row;
44
45 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
46 l_item_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
47 l_script :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ScriptName',inputs);
48
49 IF l_script = 'MPA_DUMP' THEN
50
51 sqltxt := ' SELECT' ||
52 ' MPA.ADJUSTMENT_ID,' ||
53 ' mp.organization_code || ''('' || mpa.organization_id ||'')'' "Organization|Code (Id)", ' ||
54 ' MPA.PHYSICAL_INVENTORY_ID,' ||
55 ' mif.item_number || ''(''|| mif.inventory_item_id || '')'' "Item (Id)", ' ||
56 ' MPA.SUBINVENTORY_NAME,' ||
57 ' MPA.SYSTEM_QUANTITY,' ||
58 ' MPA.LAST_UPDATE_DATE,' ||
59 ' MPA.LAST_UPDATED_BY,' ||
60 ' MPA.CREATION_DATE,' ||
61 ' MPA.CREATED_BY,' ||
62 ' MPA.LAST_UPDATE_LOGIN,' ||
63 ' MPA.COUNT_QUANTITY,' ||
64 ' MPA.ADJUSTMENT_QUANTITY,' ||
65 ' MPA.REVISION,' ||
66 ' MPA.LOCATOR_ID,' ||
67 ' MPA.LOT_NUMBER,' ||
68 ' MPA.LOT_EXPIRATION_DATE,' ||
69 ' MPA.SERIAL_NUMBER,' ||
70 ' MPA.ACTUAL_COST,' ||
71 ' MPA.APPROVAL_STATUS,' ||
72 ' MPA.APPROVED_BY_EMPLOYEE_ID,' ||
73 ' MPA.AUTOMATIC_APPROVAL_CODE,' ||
74 ' MPA.GL_ADJUST_ACCOUNT,' ||
75 ' MPA.REQUEST_ID,' ||
76 ' MPA.PROGRAM_APPLICATION_ID,' ||
77 ' MPA.PROGRAM_ID,' ||
78 ' MPA.PROGRAM_UPDATE_DATE,' ||
79 ' MPA.LOT_SERIAL_CONTROLS,' ||
80 ' MPA.TEMP_APPROVER,' ||
81 ' MPA.PARENT_LPN_ID,' ||
82 ' MPA.OUTERMOST_LPN_ID,' ||
83 ' MPA.COST_GROUP_ID' ||
84 ' FROM MTL_PHYSICAL_ADJUSTMENTS MPA, MTL_PARAMETERS mp, MTL_ITEM_FLEXFIELDS mif ' ||
85 ' WHERE MPA.ORGANIZATION_ID = mp.organization_id ' ||
86 ' AND MPA.INVENTORY_ITEM_ID = mif.inventory_item_id(+) ' ||
87 ' AND MPA.ORGANIZATION_ID = mif.organization_id(+) ' ;
88
89 IF l_org_id IS NOT NULL THEN
90 IF l_item_id IS NOT NULL THEN
91 sqltxt := sqltxt || ' AND mpa.organization_id = ' || l_org_id || ' AND mpa.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
92 ELSE
93 sqltxt := sqltxt || ' AND mpa.organization_id = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
94 END IF;
95
96 ELSE
97 IF l_item_id IS NOT NULL THEN
98 sqltxt := sqltxt || ' AND mpa.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
99 ELSE
100 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
101 END IF;
102
103 END IF;
104 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Dump of Physical Inventory Adjustments');
105
106 ELSIF l_script = 'MPIT_DUMP' THEN
107 sqltxt := ' SELECT' ||
108 ' MPIT.TAG_ID,' ||
109 ' MPIT.PHYSICAL_INVENTORY_ID,' ||
110 ' mp.organization_code || ''('' || mpit.organization_id ||'')'' "Organization|Code (Id)", ' ||
111 ' MPIT.LAST_UPDATE_DATE,' ||
112 ' MPIT.LAST_UPDATED_BY,' ||
113 ' MPIT.CREATION_DATE,' ||
114 ' MPIT.CREATED_BY,' ||
115 ' MPIT.LAST_UPDATE_LOGIN,' ||
116 ' MPIT.VOID_FLAG,' ||
117 ' MPIT.TAG_NUMBER,' ||
118 ' MPIT.ADJUSTMENT_ID,' ||
119 ' mif.item_number || ''(''|| mif.inventory_item_id || '')'' "Item (Id)", ' ||
120 ' MPIT.TAG_QUANTITY,' ||
121 ' MPIT.TAG_UOM,' ||
122 ' MPIT.TAG_QUANTITY_AT_STANDARD_UOM,' ||
123 ' MPIT.STANDARD_UOM,' ||
124 ' MPIT.SUBINVENTORY,' ||
125 ' MPIT.LOCATOR_ID,' ||
126 ' MPIT.LOT_NUMBER,' ||
127 ' MPIT.LOT_EXPIRATION_DATE,' ||
128 ' MPIT.REVISION,' ||
129 ' MPIT.SERIAL_NUM,' ||
130 ' MPIT.COUNTED_BY_EMPLOYEE_ID,' ||
131 ' MPIT.LOT_SERIAL_CONTROLS,' ||
132 ' MPIT.ATTRIBUTE_CATEGORY,' ||
133 ' MPIT.ATTRIBUTE1,' ||
134 ' MPIT.ATTRIBUTE2,' ||
135 ' MPIT.ATTRIBUTE3,' ||
136 ' MPIT.ATTRIBUTE4,' ||
137 ' MPIT.ATTRIBUTE5,' ||
138 ' MPIT.ATTRIBUTE6,' ||
139 ' MPIT.ATTRIBUTE7,' ||
140 ' MPIT.ATTRIBUTE8,' ||
141 ' MPIT.ATTRIBUTE9,' ||
142 ' MPIT.ATTRIBUTE10,' ||
143 ' MPIT.ATTRIBUTE11,' ||
144 ' MPIT.ATTRIBUTE12,' ||
145 ' MPIT.ATTRIBUTE13,' ||
146 ' MPIT.ATTRIBUTE14,' ||
147 ' MPIT.ATTRIBUTE15,' ||
148 ' MPIT.REQUEST_ID,' ||
149 ' MPIT.PROGRAM_APPLICATION_ID,' ||
150 ' MPIT.PROGRAM_ID,' ||
151 ' MPIT.PROGRAM_UPDATE_DATE,' ||
152 ' MPIT.PARENT_LPN_ID,' ||
153 ' MPIT.OUTERMOST_LPN_ID,' ||
154 ' MPIT.COST_GROUP_ID ' ||
155 ' FROM MTL_PHYSICAL_INVENTORY_TAGS MPIT, MTL_PARAMETERS mp, MTL_ITEM_FLEXFIELDS mif ' ||
156 ' WHERE mpit.ORGANIZATION_ID = mp.organization_id ' ||
157 ' AND mpit.INVENTORY_ITEM_ID = mif.inventory_item_id(+) ' ||
158 ' AND mpit.ORGANIZATION_ID = mif.organization_id(+) ' ;
159
160 IF l_org_id IS NOT NULL THEN
161 IF l_item_id IS NOT NULL THEN
162 sqltxt := sqltxt || ' AND mpit.organization_id = ' || l_org_id || ' AND mpit.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
163 ELSE
164 sqltxt := sqltxt || ' AND mpit.organization_id = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
165 END IF;
166
167 ELSE
168 IF l_item_id IS NOT NULL THEN
169 sqltxt := sqltxt || ' AND mpit.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
170 ELSE
171 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
172 END IF;
173
174 END IF;
175
176 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of Physical Inventory tags');
177
178 ELSIF l_script = 'MPI_DUMP' THEN
179 sqltxt := ' SELECT ' ||
180 ' MPI.PHYSICAL_INVENTORY_ID,' ||
181 ' mp.organization_code || ''('' || mpi.organization_id ||'')'' "Organization|Code (Id)", ' ||
182 ' MPI.LAST_UPDATE_DATE,' ||
183 ' MPI.LAST_UPDATED_BY,' ||
184 ' MPI.CREATION_DATE,' ||
185 ' MPI.CREATED_BY,' ||
186 ' MPI.LAST_UPDATE_LOGIN,' ||
187 ' MPI.PHYSICAL_INVENTORY_DATE,' ||
188 ' MPI.LAST_ADJUSTMENT_DATE,' ||
189 ' MPI.TOTAL_ADJUSTMENT_VALUE,' ||
190 ' MPI.DESCRIPTION,' ||
191 ' MPI.FREEZE_DATE,' ||
192 ' MPI.PHYSICAL_INVENTORY_NAME,' ||
193 ' MPI.APPROVAL_REQUIRED,' ||
194 ' MPI.ALL_SUBINVENTORIES_FLAG,' ||
195 ' MPI.NEXT_TAG_NUMBER,' ||
196 ' MPI.TAG_NUMBER_INCREMENTS,' ||
197 ' MPI.DEFAULT_GL_ADJUST_ACCOUNT,' ||
198 ' MPI.REQUEST_ID,' ||
199 ' MPI.PROGRAM_APPLICATION_ID,' ||
200 ' MPI.PROGRAM_ID,' ||
201 ' MPI.PROGRAM_UPDATE_DATE,' ||
202 ' MPI.APPROVAL_TOLERANCE_POS,' ||
203 ' MPI.APPROVAL_TOLERANCE_NEG,' ||
204 ' MPI.COST_VARIANCE_POS,' ||
205 ' MPI.COST_VARIANCE_NEG,' ||
206 ' MPI.NUMBER_OF_SKUS,' ||
207 ' MPI.DYNAMIC_TAG_ENTRY_FLAG,' ||
208 ' MPI.ATTRIBUTE1,' ||
209 ' MPI.ATTRIBUTE2,' ||
210 ' MPI.ATTRIBUTE3,' ||
211 ' MPI.ATTRIBUTE4,' ||
212 ' MPI.ATTRIBUTE5,' ||
213 ' MPI.ATTRIBUTE6,' ||
214 ' MPI.ATTRIBUTE7,' ||
215 ' MPI.ATTRIBUTE8,' ||
216 ' MPI.ATTRIBUTE9,' ||
217 ' MPI.ATTRIBUTE10,' ||
218 ' MPI.ATTRIBUTE11,' ||
219 ' MPI.ATTRIBUTE12,' ||
220 ' MPI.ATTRIBUTE13,' ||
221 ' MPI.ATTRIBUTE14,' ||
222 ' MPI.ATTRIBUTE15,' ||
223 ' MPI.ATTRIBUTE_CATEGORY' ||
224 ' FROM MTL_PHYSICAL_INVENTORIES MPI, MTL_PARAMETERS mp ' ||
225 ' WHERE mpi.ORGANIZATION_ID = mp.organization_id ' ;
226
227 IF l_org_id IS NOT NULL THEN
228 sqltxt := sqltxt || ' AND mpi.organization_id = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
229 ELSE
230 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
231 END IF;
232
233 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of Physical Inventory tags without onhand');
234
235 ELSE
236 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,'Please execute the report with Script Name');
237 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Script Name');
238 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please choose correct Script Name');
239 statusStr := 'FAILURE';
240 errStr := 'Invalid Script Name';
241 fixInfo := 'Please choose correct Script Name';
242 isFatal := 'FALSE';
243 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
244 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
245 RETURN;
246 END IF;
247
248 reportStr := ' Note: Only first 199 rows are returned by this script. The test completed as expected';
249 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
250 statusStr := 'SUCCESS';
251 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
252 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
253
254 EXCEPTION
255 when others then
256 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
257 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
258 statusStr := 'FAILURE';
259 errStr := sqlerrm ||' occurred in script Exception handled';
260 fixInfo := 'Unexpected Exception in INVDA07B.pls';
261 isFatal := 'FALSE';
262 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
263 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
264 END runTest;
265
266
267 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
268 BEGIN
269 name := 'Accuracy';
270 END getComponentName;
271
272 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
273 BEGIN
274 descStr := 'Dump of Physical Inventory Tables';
275 END getTestDesc;
276
277 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
278 BEGIN
279 name := 'Dump of Physical Inventory';
280 END getTestName;
281
282 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
283 tempDependencies JTF_DIAG_DEPENDTBL;
284
285 BEGIN
286 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
287 END getDependencies;
288
289 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
290 BEGIN
291 str := 'FALSE';
292 END isDependencyPipelined;
293
294 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
295 tempOutput JTF_DIAG_OUTPUTTBL;
296 BEGIN
297 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
298 outputValues := tempOutput;
299 EXCEPTION
300 when others then
301 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
302 END getOutputValues;
303
304 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
305 tempInput JTF_DIAG_INPUTTBL;
306 BEGIN
307 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
308 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
309 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
310 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ScriptName','LOV-oracle.apps.inv.diag.lov.PhyInvDumpScriptsLov');
311 defaultInputValues := tempInput;
312 EXCEPTION
313 when others then
314 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
315 END getDefaultTestParams;
316
317 Function getTestMode return INTEGER IS
318 BEGIN
319 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
320 END getTestMode;
321
322 END INV_DIAG_PHY_INV_DUMP;