[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_LOC_SKUNOLOC
Source
1 package body INV_DIAG_OH_LOC_SKUNOLOC as
2 /* $Header: INVDO05B.pls 120.0.12000000.1 2007/06/22 01:03:19 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 not null and moqd.locator_id <> -1' ||
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 with 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 not null and t.locator_id <> -1 ' ||
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 with 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 INVDO05B.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 with locator when SKU is not locator controlled';
129 END getTestDesc;
130
131 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
132 BEGIN
133 name := 'Onhand Loc SKU NoLoc';
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 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
149 tempOutput JTF_DIAG_OUTPUTTBL;
150 BEGIN
151 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
152 outputValues := tempOutput;
153 EXCEPTION
154 when others then
155 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
156 END getOutputValues;
157
158 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
159 tempInput JTF_DIAG_INPUTTBL;
160 BEGIN
161 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
162 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
163 defaultInputValues := tempInput;
164 EXCEPTION
165 when others then
166 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
167 END getDefaultTestParams;
168
169 Function getTestMode return INTEGER IS
170 BEGIN
171 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
172
173 END getTestMode;
174
175 END INV_DIAG_OH_LOC_SKUNOLOC;