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_OH_NOLOC_SKULOC

Source


1 package body INV_DIAG_OH_NOLOC_SKULOC as
2 /* $Header: INVDO06B.pls 120.0.12000000.1 2007/06/22 01:04:39 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 BEGIN
32 
33    JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
34    JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
35    JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
36    --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
37 
38    l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
39 
40 
41    IF l_org_id IS NOT NULL THEN
42 
43       sqltxt := 'select  mp.organization_code|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)"' ||
44                 ' ,mif.item_number|| '' (''||moqd.inventory_item_id||'')'' "Item (Id)" ' ||
45                 ' ,moqd.subinventory_code "Subinv", moqd.locator_id "Loc id",moqd.lot_number "Lot number",moqd.revision "Rev" ' ||
46                 ' from mtl_onhand_quantities_detail moqd,' ||
47                 ' mtl_parameters mp,mtl_item_flexfields mif' ||
48                 ' where moqd.organization_id = mp.organization_id' ||
49                 ' and moqd.inventory_item_id = mif.inventory_item_id' ||
50                 ' and moqd.organization_id = mif.organization_id' ||
51                 ' and moqd.locator_id is null ' ||
52                 ' and moqd.organization_id = ' || l_org_id ||
53                 ' and EXISTS (SELECT 1' ||
54                              ' FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I' ||
55                              ' WHERE I.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID' ||
56                              ' AND S.SECONDARY_INVENTORY_NAME = moqd.SUBINVENTORY_CODE' ||
57                              ' AND P.ORGANIZATION_ID = moqd.ORGANIZATION_ID' ||
58                              ' AND I.ORGANIZATION_ID = S.ORGANIZATION_ID' ||
59                              ' AND P.ORGANIZATION_ID = S.ORGANIZATION_ID' ||
60                              ' AND P.ORGANIZATION_ID = I.ORGANIZATION_ID' ||
61                              ' AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4,decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE),' ||
62                              ' P.STOCK_LOCATOR_CONTROL_CODE) <> 1))' ||
63                 ' group  by ' ||
64                 ' mp.organization_code || '' ('' || moqd.organization_id  || '')'',' ||
65                 ' mif.item_number|| '' (''||moqd.inventory_item_id||'')'',' ||
66                 ' moqd.inventory_item_id, moqd.subinventory_code, moqd.locator_id, moqd.lot_number, moqd.revision' ;
67 
68       dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand without locator for SKU not locator controlled');
69 
70    ELSE
71 
72       sqltxt := ' select mp.organization_code|| '' (''||t.organization_id ||'')'' "Organization|Code (Id)"' ||
73              ' ,mif.item_number|| '' (''|| t.inventory_item_id||'')'' "Item (Id)" ,' ||
74              ' t.subinventory_code "Subinv", t.locator_id "Locator_id", t.lot_number "Lot num", t.revision "Rev"' ||
75              ' from   temp_disc_inv_cg_loose t,  mtl_parameters mp, mtl_item_flexfields mif' ||
76              ' where  ' ||
77              ' t.organization_id = mp.organization_id(+)' ||
78              ' and t.inventory_item_id = mif.inventory_item_id(+)' ||
79              ' and t.organization_id = mif.organization_id(+)' ||
80              ' and t.locator_id is null ' ||
81              ' and EXISTS (SELECT 1' ||
82                            ' FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I' ||
83                            ' WHERE I.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID' ||
84                            ' AND S.SECONDARY_INVENTORY_NAME = t.SUBINVENTORY_CODE' ||
85                            ' AND P.ORGANIZATION_ID = t.ORGANIZATION_ID' ||
86                            ' AND I.ORGANIZATION_ID = S.ORGANIZATION_ID' ||
87                            ' AND P.ORGANIZATION_ID = S.ORGANIZATION_ID' ||
88                            ' AND P.ORGANIZATION_ID = I.ORGANIZATION_ID' ||
89                            ' AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4,decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE),' ||
90                            ' P.STOCK_LOCATOR_CONTROL_CODE) <> 1))' ||
91              ' group  by ' ||
92 	     ' mp.organization_code || '' ('' || t.organization_id  || '')'',' ||
93 	     ' mif.item_number|| '' (''||t.inventory_item_id||'')'',' ||
94 	     ' t.subinventory_code, t.locator_id, t.lot_number, t.revision' ;
95 
96       dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,' Onhand without locator for SKU not locator controlled.');
97       JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' Please execute with organization identifier to identify the locator mismatch for an organization.');
98       JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' Alternatively user can execute the mismatch script mentioned in Note 279205.1  and then this script without organization identifier to identify locator mismatches across organization.');
99 
100    END IF;
101 
102    reportStr := 'The test completed as expected';
103    JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
104    statusStr := 'SUCCESS';
105    report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
106    reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
107 
108 EXCEPTION
109   when others then
110     JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
111     JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
112     statusStr := 'FAILURE';
113     errStr := sqlerrm ||' occurred in script Exception handled';
114     fixInfo := 'Unexpected Exception in INVDO06B.pls';
115     isFatal := 'FALSE';
116     report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
117     reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
118 END runTest;
119 
120 
121 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
122 BEGIN
123 name := 'Onhand';
124 END getComponentName;
125 
126 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
127 BEGIN
128 descStr := 'Onhand without locator when SKU is locator controlled';
129 END getTestDesc;
130 
131 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
132 BEGIN
133 name := 'Onhand NoLoc SKU Loc';
134 END getTestName;
135 
136 PROCEDURE getDependencies (package_names OUT NOCOPY  JTF_DIAG_DEPENDTBL) IS
137 tempDependencies JTF_DIAG_DEPENDTBL;
138 
139 BEGIN
140     package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
141 END getDependencies;
142 
143 PROCEDURE isDependencyPipelined (str OUT NOCOPY  VARCHAR2) IS
144 BEGIN
145   str := 'FALSE';
146 END isDependencyPipelined;
147 
148 
149 PROCEDURE getOutputValues(outputValues OUT NOCOPY  JTF_DIAG_OUTPUTTBL) IS
150   tempOutput JTF_DIAG_OUTPUTTBL;
151 BEGIN
152   tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
153   outputValues := tempOutput;
154 EXCEPTION
155  when others then
156  outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
157 END getOutputValues;
158 
159 
160 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
161 tempInput JTF_DIAG_INPUTTBL;
162 BEGIN
163   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
164   tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
165   defaultInputValues := tempInput;
166 EXCEPTION
167 when others then
168   defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
169 END getDefaultTestParams;
170 
171 Function getTestMode return INTEGER IS
172 BEGIN
173  return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
174 
175 END getTestMode;
176 
177 END INV_DIAG_OH_NOLOC_SKULOC;