[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RSV_MS_DUMP
Source
1 package body INV_DIAG_RSV_MS_DUMP as
2 /* $Header: INVDP08B.pls 120.0.12000000.1 2007/06/22 01:19:52 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 = 'MR_DUMP' THEN
50
51 sqltxt := 'SELECT ' ||
52 ' MR.RESERVATION_ID "RESERVATION_ID",' ||
53 ' MR.REQUIREMENT_DATE "REQUIREMENT_DATE",' ||
54 ' mif.item_number||''(''||mif.inventory_item_id||'')'' "Item (Id)",' ||
55 ' mp.organization_code|| '' (''||mr.organization_id ||'')'' "Organization|Code (Id)",' ||
56 ' MR.DEMAND_SOURCE_TYPE_ID "DEMAND_SOURCE_TYPE_ID",' ||
57 ' MR.DEMAND_SOURCE_NAME "DEMAND_SOURCE_NAME",' ||
58 ' MR.DEMAND_SOURCE_HEADER_ID "DEMAND_SOURCE_HEADER_ID",' ||
59 ' MR.DEMAND_SOURCE_LINE_ID "DEMAND_SOURCE_LINE_ID",' ||
60 ' MR.DEMAND_SOURCE_DELIVERY "DEMAND_SOURCE_DELIVERY",' ||
61 ' MR.PRIMARY_UOM_CODE "PRIMARY_UOM_CODE",' ||
62 ' MR.PRIMARY_UOM_ID "PRIMARY_UOM_ID",' ||
63 ' MR.RESERVATION_UOM_CODE "RESERVATION_UOM_CODE",' ||
64 ' MR.RESERVATION_UOM_ID "RESERVATION_UOM_ID",' ||
65 ' MR.RESERVATION_QUANTITY "RESERVATION_QUANTITY",' ||
66 ' MR.PRIMARY_RESERVATION_QUANTITY "PRIMARY_RESERVATION_QUANTITY",' ||
67 ' MR.AUTODETAIL_GROUP_ID "AUTODETAIL_GROUP_ID",' ||
68 ' MR.EXTERNAL_SOURCE_CODE "EXTERNAL_SOURCE_CODE",' ||
69 ' MR.EXTERNAL_SOURCE_LINE_ID "EXTERNAL_SOURCE_LINE_ID",' ||
70 ' MR.SUPPLY_SOURCE_TYPE_ID "SUPPLY_SOURCE_TYPE_ID",' ||
71 ' MR.SUPPLY_SOURCE_HEADER_ID "SUPPLY_SOURCE_HEADER_ID",' ||
72 ' MR.SUPPLY_SOURCE_LINE_ID "SUPPLY_SOURCE_LINE_ID",' ||
73 ' MR.SUPPLY_SOURCE_LINE_DETAIL "SUPPLY_SOURCE_LINE_DETAIL",' ||
74 ' MR.SUPPLY_SOURCE_NAME "SUPPLY_SOURCE_NAME",' ||
75 ' MR.REVISION "REVISION",' ||
76 ' MR.SUBINVENTORY_CODE "SUBINVENTORY_CODE",' ||
77 ' MR.SUBINVENTORY_ID "SUBINVENTORY_ID",' ||
78 ' MR.LOCATOR_ID "LOCATOR_ID",' ||
79 ' MR.LOT_NUMBER "LOT_NUMBER",' ||
80 ' MR.LOT_NUMBER_ID "LOT_NUMBER_ID",' ||
81 ' MR.SERIAL_NUMBER "SERIAL_NUMBER",' ||
82 ' MR.SERIAL_NUMBER_ID "SERIAL_NUMBER_ID",' ||
83 ' MR.PARTIAL_QUANTITIES_ALLOWED "PARTIAL_QUANTITIES_ALLOWED",' ||
84 ' MR.AUTO_DETAILED "AUTO_DETAILED",' ||
85 ' MR.PICK_SLIP_NUMBER "PICK_SLIP_NUMBER",' ||
86 ' MR.LPN_ID "LPN_ID",' ||
87 ' MR.LAST_UPDATE_DATE "LAST_UPDATE_DATE",' ||
88 ' MR.LAST_UPDATED_BY "LAST_UPDATED_BY",' ||
89 ' MR.CREATION_DATE "CREATION_DATE",' ||
90 ' MR.CREATED_BY "CREATED_BY",' ||
91 ' MR.LAST_UPDATE_LOGIN "LAST_UPDATE_LOGIN",' ||
92 ' MR.REQUEST_ID "REQUEST_ID",' ||
93 ' MR.PROGRAM_APPLICATION_ID "PROGRAM_APPLICATION_ID",' ||
94 ' MR.PROGRAM_ID "PROGRAM_ID",' ||
95 ' MR.PROGRAM_UPDATE_DATE "PROGRAM_UPDATE_DATE",' ||
96 ' MR.ATTRIBUTE_CATEGORY "ATTRIBUTE_CATEGORY",' ||
97 ' MR.ATTRIBUTE1 "ATTRIBUTE1",' ||
98 ' MR.ATTRIBUTE2 "ATTRIBUTE2",' ||
99 ' MR.ATTRIBUTE3 "ATTRIBUTE3",' ||
100 ' MR.ATTRIBUTE4 "ATTRIBUTE4",' ||
101 ' MR.ATTRIBUTE5 "ATTRIBUTE5",' ||
102 ' MR.ATTRIBUTE6 "ATTRIBUTE6",' ||
103 ' MR.ATTRIBUTE7 "ATTRIBUTE7",' ||
104 ' MR.ATTRIBUTE8 "ATTRIBUTE8",' ||
105 ' MR.ATTRIBUTE9 "ATTRIBUTE9",' ||
106 ' MR.ATTRIBUTE10 "ATTRIBUTE10",' ||
107 ' MR.ATTRIBUTE11 "ATTRIBUTE11",' ||
108 ' MR.ATTRIBUTE12 "ATTRIBUTE12",' ||
109 ' MR.ATTRIBUTE13 "ATTRIBUTE13",' ||
110 ' MR.ATTRIBUTE14 "ATTRIBUTE14",' ||
111 ' MR.ATTRIBUTE15 "ATTRIBUTE15",' ||
112 ' MR.SHIP_READY_FLAG "SHIP_READY_FLAG",' ||
113 ' MR.N_COLUMN1 "N_COLUMN1",' ||
114 ' MR.DETAILED_QUANTITY "DETAILED_QUANTITY",' ||
115 ' MR.COST_GROUP_ID "COST_GROUP_ID",' ||
116 ' MR.CONTAINER_LPN_ID "CONTAINER_LPN_ID",' ||
117 ' MR.STAGED_FLAG "STAGED_FLAG "' ||
118 ' FROM MTL_RESERVATIONS MR, MTL_PARAMETERS mp, MTL_ITEM_FLEXFIELDS mif' ||
119 ' WHERE MR.organization_id = mp.organization_id' ||
120 ' and MR.inventory_item_id = mif.inventory_item_id(+)' ||
121 ' and MR.organization_id = mif.organization_id(+) ' ;
122
123 IF l_org_id IS NOT NULL THEN
124
125 IF l_item_id IS NOT NULL THEN
126 sqltxt := sqltxt || ' AND MR.ORGANIZATION_ID = ' || l_org_id || ' AND MR.INVENTORY_ITEM_ID = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
127 ELSE
128 sqltxt := sqltxt || ' AND MR.ORGANIZATION_ID = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
129 END IF;
130
131 ELSE
132 IF l_item_id IS NOT NULL THEN
133 sqltxt := sqltxt || ' AND MR.INVENTORY_ITEM_ID = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
134 ELSE
135 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit;
136 END IF;
137
138 END IF;
139
140 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Dump of Reservations');
141
142 ELSIF l_script = 'MS_DUMP' THEN
143 sqltxt := 'SELECT ' ||
144 ' MS.SUPPLY_TYPE_CODE,' ||
145 ' MS.SUPPLY_SOURCE_ID,' ||
146 ' MS.LAST_UPDATED_BY,' ||
147 ' MS.LAST_UPDATE_DATE,' ||
148 ' MS.LAST_UPDATE_LOGIN,' ||
149 ' MS.CREATED_BY,' ||
150 ' MS.CREATION_DATE,' ||
151 ' MS.REQUEST_ID,' ||
152 ' MS.PROGRAM_APPLICATION_ID,' ||
153 ' MS.PROGRAM_ID,' ||
154 ' MS.PROGRAM_UPDATE_DATE,' ||
155 ' MS.REQ_HEADER_ID,' ||
156 ' MS.REQ_LINE_ID,' ||
157 ' MS.PO_HEADER_ID,' ||
158 ' MS.PO_RELEASE_ID,' ||
159 ' MS.PO_LINE_ID,' ||
160 ' MS.PO_LINE_LOCATION_ID,' ||
161 ' MS.PO_DISTRIBUTION_ID,' ||
162 ' MS.SHIPMENT_HEADER_ID,' ||
163 ' MS.SHIPMENT_LINE_ID,' ||
164 ' MS.RCV_TRANSACTION_ID,' ||
165 ' MS.ITEM_ID,' ||
166 ' mif.item_number || ''(''|| mif.inventory_item_id || '')'' "Item (Id)", ' ||
167 ' MS.ITEM_REVISION,' ||
168 ' MS.CATEGORY_ID,' ||
169 ' MS.QUANTITY,' ||
170 ' MS.UNIT_OF_MEASURE,' ||
171 ' MS.TO_ORG_PRIMARY_QUANTITY,' ||
172 ' MS.TO_ORG_PRIMARY_UOM,' ||
173 ' MS.RECEIPT_DATE,' ||
174 ' MS.NEED_BY_DATE,' ||
175 ' MS.EXPECTED_DELIVERY_DATE,' ||
176 ' MS.DESTINATION_TYPE_CODE,' ||
177 ' MS.LOCATION_ID,' ||
178 ' mp.organization_code || ''('' || ms.from_organization_id ||'')'' "From Organization|Code (Id)", ' ||
179 ' MS.FROM_SUBINVENTORY,' ||
180 ' mpTo.organization_code || ''('' || ms.to_organization_id ||'')'' "To Organization|Code (Id)", ' ||
181 ' MS.TO_SUBINVENTORY,' ||
182 ' MS.INTRANSIT_OWNING_ORG_ID,' ||
183 ' MS.MRP_PRIMARY_QUANTITY,' ||
184 ' MS.MRP_PRIMARY_UOM,' ||
185 ' MS.MRP_EXPECTED_DELIVERY_DATE,' ||
186 ' MS.MRP_DESTINATION_TYPE_CODE,' ||
187 ' MS.MRP_TO_ORGANIZATION_ID,' ||
188 ' MS.MRP_TO_SUBINVENTORY,' ||
189 ' MS.CHANGE_FLAG,' ||
190 ' MS.CHANGE_TYPE,' ||
191 ' MS.COST_GROUP_ID ' ||
192 ' FROM MTL_SUPPLY MS, MTL_PARAMETERS mp, MTL_ITEM_FLEXFIELDS mif, MTL_PARAMETERS mpTo ' ||
193 ' WHERE ms.from_organization_id = mp.organization_id ' ||
194 ' AND ms.item_id = mif.inventory_item_id(+) ' ||
195 ' AND ms.from_organization_id = mif.organization_id(+) ' ||
196 ' AND ms.to_organization_id = mpTo.organization_id ' ;
197
198 IF l_org_id IS NOT NULL THEN
199 IF l_item_id IS NOT NULL THEN
200 sqltxt := sqltxt || ' AND MS.FROM_ORGANIZATION_ID = ' || l_org_id || ' AND MS.ITEM_ID = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
201 ELSE
202 sqltxt := sqltxt || ' AND MS.FROM_ORGANIZATION_ID = ' || l_org_id || ' AND ROWNUM < ' || row_limit ;
203 END IF;
204
205 ELSE
206 IF l_item_id IS NOT NULL THEN
207 sqltxt := sqltxt || ' AND MS.ITEM_ID = ' || l_item_id || ' AND ROWNUM < ' || row_limit ;
208 ELSE
209 sqltxt := sqltxt || ' AND ROWNUM < ' || row_limit ;
210 END IF;
211
212 END IF;
213
214
215 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Dump of MTL_SUPPLY');
216
217 ELSE
218 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,'Please execute the report with Script Name');
219 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Script Name');
220 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please choose correct Script Name');
221 statusStr := 'FAILURE';
222 errStr := 'Invalid Script Name';
223 fixInfo := 'Please choose correct Script Name';
224 isFatal := 'FALSE';
225 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
226 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
227 RETURN;
228 END IF;
229
230 reportStr := ' Note: Only first 199 rows are returned by this script. The test completed as expected';
231 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
232 statusStr := 'SUCCESS';
233 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
234 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
235
236 EXCEPTION
237 when others then
238 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
239 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
240 statusStr := 'FAILURE';
241 errStr := sqlerrm ||' occurred in script Exception handled';
242 fixInfo := 'Unexpected Exception in INVDP08B.pls';
243 isFatal := 'FALSE';
244 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
245 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
246 END runTest;
247
248
249 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
250 BEGIN
251 name := 'Pick Release and Reservation';
252 END getComponentName;
253
254 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
255 BEGIN
256 descStr := 'Dump of Reservation / Supply';
257 END getTestDesc;
258
259 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
260 BEGIN
261 name := 'Dump of Reservation / Supply';
262 END getTestName;
263
264 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
265 tempDependencies JTF_DIAG_DEPENDTBL;
266
267 BEGIN
268 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
269 END getDependencies;
270
271 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
272 BEGIN
273 str := 'FALSE';
274 END isDependencyPipelined;
275
276 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
277 tempOutput JTF_DIAG_OUTPUTTBL;
278 BEGIN
279 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
280 outputValues := tempOutput;
281 EXCEPTION
282 when others then
283 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
284 END getOutputValues;
285
286 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
287 tempInput JTF_DIAG_INPUTTBL;
288 BEGIN
289 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
290 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
291 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
292 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ScriptName','LOV-oracle.apps.inv.diag.lov.PickRelRsvDiagScriptsLov');
293 defaultInputValues := tempInput;
294 EXCEPTION
295 when others then
296 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
297 END getDefaultTestParams;
298
299 Function getTestMode return INTEGER IS
300 BEGIN
301 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
302 END getTestMode;
303
304 END INV_DIAG_RSV_MS_DUMP;