[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_MO_DUMP
Source
1 package body INV_DIAG_MO_DUMP as
2 /* $Header: INVDM05B.pls 120.0.12000000.1 2007/06/22 00:56:12 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 = 'MTRH_DUMP' THEN
50
51 sqltxt := ' SELECT ' ||
52 ' MTRH.HEADER_ID,' ||
53 ' MTRH.REQUEST_NUMBER,' ||
54 ' MTRH.TRANSACTION_TYPE_ID,' ||
55 ' MTRH.MOVE_ORDER_TYPE,' ||
56 ' mp.organization_code || ''('' || MTRH.ORGANIZATION_ID ||'')'' "Organization|Code (Id)", ' ||
57 ' MTRH.ORGANIZATION_ID,' ||
58 ' MTRH.DESCRIPTION,' ||
59 ' MTRH.DATE_REQUIRED,' ||
60 ' MTRH.FROM_SUBINVENTORY_CODE,' ||
61 ' MTRH.TO_SUBINVENTORY_CODE,' ||
62 ' MTRH.TO_ACCOUNT_ID,' ||
63 ' MTRH.HEADER_STATUS,' ||
64 ' MTRH.STATUS_DATE,' ||
65 ' MTRH.LAST_UPDATED_BY,' ||
66 ' MTRH.LAST_UPDATE_LOGIN,' ||
67 ' MTRH.LAST_UPDATE_DATE,' ||
68 ' MTRH.CREATED_BY,' ||
69 ' MTRH.CREATION_DATE,' ||
70 ' MTRH.REQUEST_ID,' ||
71 ' MTRH.PROGRAM_APPLICATION_ID,' ||
72 ' MTRH.PROGRAM_ID,' ||
73 ' MTRH.PROGRAM_UPDATE_DATE,' ||
74 ' MTRH.GROUPING_RULE_ID,' ||
75 ' MTRH.ATTRIBUTE1,' ||
76 ' MTRH.ATTRIBUTE2,' ||
77 ' MTRH.ATTRIBUTE3,' ||
78 ' MTRH.ATTRIBUTE4,' ||
79 ' MTRH.ATTRIBUTE5,' ||
80 ' MTRH.ATTRIBUTE6,' ||
81 ' MTRH.ATTRIBUTE7,' ||
82 ' MTRH.ATTRIBUTE8,' ||
83 ' MTRH.ATTRIBUTE9,' ||
84 ' MTRH.ATTRIBUTE10,' ||
85 ' MTRH.ATTRIBUTE11,' ||
86 ' MTRH.ATTRIBUTE12,' ||
87 ' MTRH.ATTRIBUTE13,' ||
88 ' MTRH.ATTRIBUTE14,' ||
89 ' MTRH.ATTRIBUTE15,' ||
90 ' MTRH.ATTRIBUTE_CATEGORY,' ||
91 ' MTRH.SHIP_TO_LOCATION_ID,' ||
92 ' MTRH.FREIGHT_CODE,' ||
93 ' MTRH.SHIPMENT_METHOD,' ||
94 ' MTRH.AUTO_RECEIPT_FLAG,' ||
95 ' MTRH.REFERENCE_ID,' ||
96 ' MTRH.REFERENCE_DETAIL_ID,' ||
97 ' MTRH.ASSIGNMENT_ID ' ||
98 ' FROM MTL_TXN_REQUEST_HEADERS MTRH, MTL_PARAMETERS mp ' ;
99
100 IF l_item_id IS NOT NULL THEN
101 sqltxt := sqltxt || ', MTL_TXN_REQUEST_LINES mtrl WHERE mtrh.organization_id = mp.organization_id AND mtrh.header_id = mtrl.header_id AND mtrl.inventory_item_id = ' || l_item_id ;
102 ELSE
103 sqltxt := sqltxt || ' WHERE mtrh.organization_id = mp.organization_id ' ;
104 END IF;
105
106 IF l_org_id IS NOT NULL THEN
107 sqltxt := sqltxt || ' AND mtrh.organization_id = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
108 ELSE
109 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
110 END IF;
111
112 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Dump of Move order headers (MTL_TXN_REQUEST_HEADERS)');
113
114 ELSIF l_script = 'MTRL_DUMP' THEN
115 sqltxt := ' SELECT '||
116 ' MTRL.LINE_ID,' ||
117 ' MTRL.HEADER_ID,' ||
118 ' MTRL.LINE_NUMBER,' ||
119 ' mp.organization_code || ''('' || MTRL.ORGANIZATION_ID ||'')'' "Organization|Code (Id)", ' ||
120 ' mif.item_number || ''(''|| mif.inventory_item_id || '')'' "Item (Id)", ' ||
121 ' MTRL.REVISION,' ||
122 ' MTRL.FROM_SUBINVENTORY_CODE,' ||
123 ' MTRL.FROM_LOCATOR_ID,' ||
124 ' MTRL.TO_SUBINVENTORY_CODE,' ||
125 ' MTRL.TO_LOCATOR_ID,' ||
126 ' MTRL.TO_ACCOUNT_ID,' ||
127 ' MTRL.LOT_NUMBER,' ||
128 ' MTRL.SERIAL_NUMBER_START,' ||
129 ' MTRL.SERIAL_NUMBER_END,' ||
130 ' MTRL.UOM_CODE,' ||
131 ' MTRL.QUANTITY,' ||
132 ' MTRL.QUANTITY_DELIVERED,' ||
133 ' MTRL.QUANTITY_DETAILED,' ||
134 ' MTRL.DATE_REQUIRED,' ||
135 ' MTRL.REASON_ID,' ||
136 ' MTRL.REFERENCE,' ||
137 ' MTRL.REFERENCE_TYPE_CODE,' ||
138 ' MTRL.REFERENCE_ID,' ||
139 ' MTRL.PROJECT_ID,' ||
140 ' MTRL.TASK_ID,' ||
141 ' MTRL.TRANSACTION_HEADER_ID,' ||
142 ' MTRL.LINE_STATUS,' ||
143 ' MTRL.STATUS_DATE,' ||
144 ' MTRL.LAST_UPDATED_BY,' ||
145 ' MTRL.LAST_UPDATE_LOGIN,' ||
146 ' MTRL.LAST_UPDATE_DATE,' ||
147 ' MTRL.CREATED_BY,' ||
148 ' MTRL.CREATION_DATE,' ||
149 ' MTRL.REQUEST_ID,' ||
150 ' MTRL.PROGRAM_APPLICATION_ID,' ||
151 ' MTRL.PROGRAM_ID,' ||
152 ' MTRL.PROGRAM_UPDATE_DATE,' ||
153 ' MTRL.ATTRIBUTE1,' ||
154 ' MTRL.ATTRIBUTE2,' ||
155 ' MTRL.ATTRIBUTE3,' ||
156 ' MTRL.ATTRIBUTE4,' ||
157 ' MTRL.ATTRIBUTE5,' ||
158 ' MTRL.ATTRIBUTE6,' ||
159 ' MTRL.ATTRIBUTE7,' ||
160 ' MTRL.ATTRIBUTE8,' ||
161 ' MTRL.ATTRIBUTE9,' ||
162 ' MTRL.ATTRIBUTE10,' ||
163 ' MTRL.ATTRIBUTE11,' ||
164 ' MTRL.ATTRIBUTE12,' ||
165 ' MTRL.ATTRIBUTE13,' ||
166 ' MTRL.ATTRIBUTE14,' ||
167 ' MTRL.ATTRIBUTE15,' ||
168 ' MTRL.ATTRIBUTE_CATEGORY,' ||
169 ' MTRL.TXN_SOURCE_ID,' ||
170 ' MTRL.TXN_SOURCE_LINE_ID,' ||
171 ' MTRL.TXN_SOURCE_LINE_DETAIL_ID,' ||
172 ' MTRL.TRANSACTION_TYPE_ID,' ||
173 ' MTRL.TRANSACTION_SOURCE_TYPE_ID,' ||
174 ' MTRL.PRIMARY_QUANTITY,' ||
175 ' MTRL.TO_ORGANIZATION_ID,' ||
176 ' MTRL.PUT_AWAY_STRATEGY_ID,' ||
177 ' MTRL.PICK_STRATEGY_ID,' ||
178 ' MTRL.SHIP_TO_LOCATION_ID,' ||
179 ' MTRL.UNIT_NUMBER,' ||
180 ' MTRL.REFERENCE_DETAIL_ID,' ||
181 ' MTRL.ASSIGNMENT_ID,' ||
182 ' MTRL.FROM_COST_GROUP_ID,' ||
183 ' MTRL.TO_COST_GROUP_ID,' ||
184 ' MTRL.LPN_ID,' ||
185 ' MTRL.TO_LPN_ID,' ||
186 ' MTRL.PICK_SLIP_NUMBER,' ||
187 ' MTRL.PICK_SLIP_DATE,' ||
188 ' MTRL.INSPECTION_STATUS,' ||
189 ' MTRL.PICK_METHODOLOGY_ID,' ||
190 ' MTRL.CONTAINER_ITEM_ID,' ||
191 ' MTRL.CARTON_GROUPING_ID,' ||
192 ' MTRL.BACKORDER_DELIVERY_DETAIL_ID,' ||
193 ' MTRL.WMS_PROCESS_FLAG,' ||
194 ' MTRL.SHIP_SET_ID,' ||
195 ' MTRL.SHIP_MODEL_ID,' ||
196 ' MTRL.MODEL_QUANTITY,' ||
197 ' MTRL.FROM_SUBINVENTORY_ID,' ||
198 ' MTRL.TO_SUBINVENTORY_ID,' ||
199 ' MTRL.CROSSDOCK_TYPE,' ||
200 ' MTRL.REQUIRED_QUANTITY' ||
201 ' FROM MTL_TXN_REQUEST_LINES MTRL, MTL_PARAMETERS mp, MTL_ITEM_FLEXFIELDS mif ' ||
202 ' WHERE MTRL.organization_id = mp.organization_id ' ||
203 ' AND MTRL.inventory_item_id = mif.inventory_item_id(+) ' ||
204 ' AND MTRL.organization_id = mif.organization_id(+) ' ;
205
206 IF l_org_id IS NOT NULL THEN
207 IF l_item_id IS NOT NULL THEN
208 sqltxt := sqltxt || ' AND MTRL.organization_id = ' || l_org_id || ' AND MTRL.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
209 ELSE
210 sqltxt := sqltxt || ' AND MTRL.organization_id = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
211 END IF;
212
213 ELSE
214 IF l_item_id IS NOT NULL THEN
215 sqltxt := sqltxt || ' AND MTRL.inventory_item_id = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
216 ELSE
217 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
218 END IF;
219
220 END IF;
221
222 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Dump of Move Order Lines');
223
224 ELSE
225 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,'Please execute the report with Script Name');
226 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Script Name');
227 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please choose correct Script Name');
228 statusStr := 'FAILURE';
229 errStr := 'Invalid Script Name';
230 fixInfo := 'Please choose correct Script Name';
231 isFatal := 'FALSE';
232 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
233 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
234 RETURN;
235 END IF;
236
237 reportStr := ' Note: Only first 199 rows are returned by this script. The test completed as expected';
238 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
239 statusStr := 'SUCCESS';
240 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
241 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
242
243 EXCEPTION
244 when others then
245 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
246 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
247 statusStr := 'FAILURE';
248 errStr := sqlerrm ||' occurred in script Exception handled';
249 fixInfo := 'Unexpected Exception in INVDM05B.pls';
250 isFatal := 'FALSE';
251 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
252 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
253 END runTest;
254
255
256 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
257 BEGIN
258 name := 'Move Orders';
259 END getComponentName;
260
261 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
262 BEGIN
263 descStr := 'Dump of Move Order Tables';
264 END getTestDesc;
265
266 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
267 BEGIN
268 name := 'Dump of Move Order';
269 END getTestName;
270
271 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
272 tempDependencies JTF_DIAG_DEPENDTBL;
273
274 BEGIN
275 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
276 END getDependencies;
277
278 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
279 BEGIN
280 str := 'FALSE';
281 END isDependencyPipelined;
282
283 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
284 tempOutput JTF_DIAG_OUTPUTTBL;
285 BEGIN
286 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
287 outputValues := tempOutput;
288 EXCEPTION
289 when others then
290 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
291 END getOutputValues;
292
293 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
294 tempInput JTF_DIAG_INPUTTBL;
295 BEGIN
296 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
297 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
298 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
299 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ScriptName','LOV-oracle.apps.inv.diag.lov.MODumpScriptsLov');
300 defaultInputValues := tempInput;
301 EXCEPTION
302 when others then
303 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
304 END getDefaultTestParams;
305
306 Function getTestMode return INTEGER IS
307 BEGIN
308 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
309 END getTestMode;
310
311 END INV_DIAG_MO_DUMP;