eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.INV_DIAG_PI_SUBLOC

Source


1 package body INV_DIAG_PI_SUBLOC as
2 /* $Header: INVDA02B.pls 120.0.12000000.1 2007/06/22 00:36:09 musinha 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  l_org_id NUMBER;
27 
28  CURSOR c_inv_loc (cp_n_org_id IN NUMBER) IS
29     select moqd.inventory_item_id,
30            moqd.organization_id,
31            moqd.subinventory_code,
32            moqd.locator_id,
33            moqd.revision,
34            moqd.primary_transaction_quantity,
35            moqd.create_transaction_id,
36 	   moqd.update_transaction_id
37      from mtl_onhand_quantities_detail moqd
38      where moqd.locator_id is not null
39      and   moqd.organization_id = NVL(cp_n_org_id, moqd.organization_id)
40      and   not exists (select 1 from mtl_item_locations mil
41                        where mil.organization_id = moqd.organization_id
42                        and  mil.subinventory_code = moqd.subinventory_code
43                        and  mil.inventory_location_id = moqd.locator_id)
44      and  exists (SELECT 1 FROM MTL_PARAMETERS P,
45                                 MTL_SECONDARY_INVENTORIES S,
46 				MTL_SYSTEM_ITEMS I
47                             WHERE I.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
48                             AND S.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE
49                             AND P.ORGANIZATION_ID = moqd.ORGANIZATION_ID
50                             AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
51                             AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
52                             AND P.ORGANIZATION_ID = I.ORGANIZATION_ID AND P.WMS_ENABLED_FLAG = 'N'
53                             AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,
54 			            4,decode(S.LOCATOR_TYPE,5,
55 					I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE),
56 				P.STOCK_LOCATOR_CONTROL_CODE) IN (2,3) ))
57      ORDER BY  moqd.organization_id, moqd.inventory_item_id, moqd.create_transaction_id, moqd.update_transaction_id, moqd.locator_id;
58 
59   CURSOR c_phy_adj ( cp_n_cre_trx_id IN NUMBER, cp_upd_trx_id IN NUMBER) IS
60     SELECT mmt.physical_adjustment_id
61     FROM   mtl_material_transactions mmt
62     WHERE  (mmt.transaction_id = cp_n_cre_trx_id OR mmt.transaction_id = cp_upd_trx_id)
63     AND    mmt.physical_adjustment_id IS NOT NULL;
64 
65 BEGIN
66   JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
67   JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
68   JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
69 
70   l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
71 
72   sqltxt := ' select mif.item_number||''(''||mif.inventory_item_id||'')'' "Item (Id)"' ||
73             ' , moqd.subinventory_code "Subinv"  ' ||
74             ' , moqd.locator_id "Stock Locator"  ' ||
75             ' , moqd.revision "Rev"  ' ||
76             ' , moqd.primary_transaction_quantity "Prim Qty"  ' ||
77             ' , moqd.create_transaction_id "Create txn_id"' ||
78 	    ' , moqd.update_transaction_id "Update trx_id"' ||
79             ' from mtl_onhand_quantities_detail moqd,' ||
80             ' mtl_item_flexfields mif' ||
81             ' where moqd.locator_id is not null ' ;
82   IF l_org_id IS NOT NULL THEN
83      sqltxt := sqltxt || ' AND  moqd.organization_id = ' || l_org_id ;
84   END IF;
85 
86   sqltxt := sqltxt ||  ' and   moqd.inventory_item_id = mif.inventory_item_id(+) ' ||
87                        ' and   not exists (select 1 from mtl_item_locations mil ' ||
88                                            ' where mil.organization_id = moqd.organization_id ' ||
89                                            ' and  mil.subinventory_code = moqd.subinventory_code ' ||
90                                            ' and  mil.inventory_location_id = moqd.locator_id) ' ||
91 	               ' and  exists (SELECT 1' ||
92                                       ' FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I ' ||
93                                       ' WHERE I.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID ' ||
94                                       ' AND S.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE ' ||
95                                       ' AND P.ORGANIZATION_ID = moqd.ORGANIZATION_ID ' ||
96                                       ' AND I.ORGANIZATION_ID = S.ORGANIZATION_ID ' ||
97                                       ' AND P.ORGANIZATION_ID = S.ORGANIZATION_ID ' ||
98                                       ' AND P.ORGANIZATION_ID = I.ORGANIZATION_ID ' ||
99 				      ' AND P.WMS_ENABLED_FLAG = ''N'' ' ||
100                                       ' AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4,decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE), ' ||
101                                       ' P.STOCK_LOCATOR_CONTROL_CODE) IN (2,3) )) ' ||
102 				      ' ORDER BY  moqd.organization_id, moqd.inventory_item_id, moqd.create_transaction_id, moqd.update_transaction_id, moqd.locator_id ';
103 
104   dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Onhand with invalid locators');
105 
106   FOR rec_inv_loc IN c_inv_loc (l_org_id ) LOOP
107 
108       sqltxt := ' SELECT mmt.transaction_id "Txn Id"  ' ||
109                 ' , mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)"  ' ||
110                 ' , mmt.transaction_date "Txn Date"  ' ||
111                 ' , mmt.transaction_quantity "Txn Qty"  ' ||
112                 ' , mmt.primary_quantity "Prim Qty"  ' ||
113                 ' , mmt.transaction_uom "Uom"  ' ||
114                 ' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)"  ' ||
115                 ' , ml.meaning || '' ('' ||mmt.transaction_action_id|| '')''  ' ||
116                 ' "Txn Action (Id)"  ' ||
117                 ' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)"  ' ||
118                 ' , mmt.subinventory_code "Subinv"  ' ||
119                 ' , mmt.locator_id "Stock Locator"  ' ||
120                 ' , mmt.revision "Rev"   ' ||
121                 ' , mmt.physical_adjustment_id "Physical Adj Id"' ||
122                 ' , mmt.transaction_source_id "Txn Source Id"  ' ||
123                 ' , mmt.transaction_source_name "Txn Source"' ||
124                 ' FROM mtl_material_transactions mmt  ' ||
125                 ' , mtl_item_flexfields mif  ' ||
126                 ' , mtl_transaction_types tt  ' ||
127                 ' , mtl_txn_source_types st  ' ||
128                 ' , mfg_lookups ml  ' ||
129                 ' WHERE mmt.organization_id = ' || rec_inv_loc.organization_id  ||
130                 ' AND mmt.transaction_id = ' || rec_inv_loc.create_transaction_id ||
131                 ' AND mmt.inventory_item_id = mif.inventory_item_id(+)  ' ||
132                 ' AND mmt.organization_id = mif.organization_id(+)  ' ||
133                 ' AND mmt.transaction_type_id = tt.transaction_type_id(+)  ' ||
134                 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+)  ' ||
135                 ' AND mmt.transaction_action_id=ml.lookup_code  ' ||
136                 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION''  ' ||
137                 ' ORDER BY mmt.costed_flag, mmt.transaction_id ';
138 
139          dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transaction with invalid locator');
140 
141 	 FOR rec_phy_adj IN c_phy_adj (rec_inv_loc.create_transaction_id, rec_inv_loc.update_transaction_id) LOOP
142 
143 	     sqltxt := ' SELECT mpi.physical_inventory_name||''(''||mpi.physical_inventory_id||'')'' "Phy inv name(Id)",' ||
144                        ' mpit.adjustment_id "Phy Adj id",' ||
145                        ' mif.item_number||''(''||mif.inventory_item_id||'')'' "Item (Id)",' ||
146                        ' mpit.subinventory "Subinv",' ||
147                        ' mpit.locator_id "Stock locator"' ||
148                        ' FROM mtl_physical_inventory_tags mpit, mtl_physical_inventories mpi, mtl_item_flexfields mif' ||
149                        ' WHERE mpit.physical_inventory_id = mpi.physical_inventory_id' ||
150                        ' and mpit.adjustment_id = ' || rec_phy_adj.physical_adjustment_id ||
151                        ' AND mpit.inventory_item_id = mif.inventory_item_id(+)' ||
152                        ' and mpit.locator_id is not null and not exists' ||
153                        ' (select 1 from mtl_item_locations mil' ||
154                        ' where mil.organization_id = mpit.organization_id' ||
155                        ' and  mil.subinventory_code = mpit.subinventory' ||
156                        ' and  mil.inventory_location_id = mpit.locator_id) ' ||
157 		       ' ORDER BY mpi.physical_inventory_id ';
158 
159 	     dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Details of Physical Inventory');
160 
161              sqltxt :=  ' SELECT ' ||
162                         ' MPA.ADJUSTMENT_ID,' ||
163                         ' MPA.ORGANIZATION_ID,' ||
164                         ' MPA.PHYSICAL_INVENTORY_ID,' ||
165                         ' MPA.INVENTORY_ITEM_ID,' ||
166                         ' MPA.SUBINVENTORY_NAME,' ||
167                         ' MPA.SYSTEM_QUANTITY,' ||
168                         ' MPA.LAST_UPDATE_DATE,' ||
169                         ' MPA.LAST_UPDATED_BY,' ||
170                         ' MPA.CREATION_DATE,' ||
171                         ' MPA.CREATED_BY,' ||
172                         ' MPA.LAST_UPDATE_LOGIN,' ||
173                         ' MPA.COUNT_QUANTITY,' ||
174                         ' MPA.ADJUSTMENT_QUANTITY,' ||
175                         ' MPA.REVISION,' ||
176                         ' MPA.LOCATOR_ID,' ||
177                         ' MPA.LOT_NUMBER,' ||
178                         ' MPA.LOT_EXPIRATION_DATE,' ||
179                         ' MPA.SERIAL_NUMBER,' ||
180                         ' MPA.ACTUAL_COST,' ||
181                         ' MPA.APPROVAL_STATUS,' ||
182                         ' MPA.APPROVED_BY_EMPLOYEE_ID,' ||
183                         ' MPA.AUTOMATIC_APPROVAL_CODE,' ||
184                         ' MPA.GL_ADJUST_ACCOUNT,' ||
185                         ' MPA.REQUEST_ID,' ||
186                         ' MPA.PROGRAM_APPLICATION_ID,' ||
187                         ' MPA.PROGRAM_ID,' ||
188                         ' MPA.PROGRAM_UPDATE_DATE,' ||
189                         ' MPA.LOT_SERIAL_CONTROLS,' ||
190                         ' MPA.TEMP_APPROVER,' ||
191                         ' MPA.PARENT_LPN_ID,' ||
192                         ' MPA.OUTERMOST_LPN_ID,' ||
193                         ' MPA.COST_GROUP_ID' ||
194                         ' FROM mtl_physical_adjustments mpa' ||
195                         ' WHERE mpa.adjustment_id = ' || rec_phy_adj.physical_adjustment_id ||
196                         ' and  locator_id is not null and not exists' ||
197                         ' (select 1 from mtl_item_locations mil' ||
198                         ' where mil.organization_id = mpa.organization_id' ||
199                         ' and  mil.subinventory_code = mpa.subinventory_name' ||
200                         ' and  mil.inventory_location_id = mpa.locator_id)' ||
201                         ' ORDER BY mpa.physical_inventory_id ' ;
202 
203 	     dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Details of Physical Inventory Adjustments');
204 
205 	 END LOOP;
206 
207   END LOOP;
208 
209 
210   reportStr := 'The test completed as expected';
211   JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
212   statusStr := 'SUCCESS';
213   report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
214   reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
215 
216 EXCEPTION
217   when others then
218     JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
219     JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
220     statusStr := 'FAILURE';
221     errStr := sqlerrm ||' occurred in script Exception handled';
222     fixInfo := 'Unexpected Exception in INVDA02B.pls';
223     isFatal := 'FALSE';
224     report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
225     reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
226 END runTest;
227 
228 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
229 BEGIN
230 name := 'Accuracy';
231 END getComponentName;
232 
233 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
234 BEGIN
235 descStr := 'PI tags SubLoc mismtach';
236 END getTestDesc;
237 
238 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
239 BEGIN
240 name := 'PI tags SubLoc mismtach';
241 END getTestName;
242 
243 PROCEDURE getDependencies (package_names OUT NOCOPY  JTF_DIAG_DEPENDTBL) IS
244 tempDependencies JTF_DIAG_DEPENDTBL;
245 
246 BEGIN
247     package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
248 END getDependencies;
249 
250 PROCEDURE isDependencyPipelined (str OUT NOCOPY  VARCHAR2) IS
251 BEGIN
252   str := 'FALSE';
253 END isDependencyPipelined;
254 
255 
256 PROCEDURE getOutputValues(outputValues OUT NOCOPY  JTF_DIAG_OUTPUTTBL) IS
257   tempOutput JTF_DIAG_OUTPUTTBL;
258 BEGIN
259   tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
260   outputValues := tempOutput;
261 EXCEPTION
262  when others then
263  outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
264 END getOutputValues;
265 
266 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY  JTF_DIAG_INPUTTBL) IS
267 tempInput JTF_DIAG_INPUTTBL;
268 BEGIN
269 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
270 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
271 defaultInputValues := tempInput;
272 EXCEPTION
273 when others then
274 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
275 END getDefaultTestParams;
276 
277 Function getTestMode return INTEGER IS
278 BEGIN
279  return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
280 
281 END getTestMode;
282 
283 END INV_DIAG_PI_SUBLOC;