[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_MSN
Source
1 package body INV_DIAG_MSN as
2 /* $Header: INVDS01B.pls 120.0.12000000.1 2007/06/22 01:22:39 musinha noship $ */
3 PROCEDURE init is
4 BEGIN
5 null;
6 END init;
7
8 PROCEDURE cleanup IS
9 BEGIN
10 -- test writer could insert special cleanup code here
11 NULL;
12 END cleanup;
13
14 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
15 report OUT NOCOPY JTF_DIAG_REPORT,
16 reportClob OUT NOCOPY CLOB) IS
17
18 reportStr LONG;
19 counter NUMBER;
20 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
21 c_userid VARCHAR2(50);
22 statusStr VARCHAR2(50);
23 errStr VARCHAR2(4000);
24 fixInfo VARCHAR2(4000);
25 isFatal VARCHAR2(50);
26 dummy_num NUMBER;
27 sqltxt VARCHAR2 (2000);
28 l_sn VARCHAR2(30);
29 l_item_id number;
30 l_org_id NUMBER;
31 l_org_code VARCHAR2(3);
32 l_txn_id NUMBER;
33 row_limit NUMBER;
34
35 BEGIN
36
37 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
38 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
39 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
40
41 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
42 l_sn := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('SerialNum',inputs);
43 l_item_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
44
45 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('OrgID input : '||l_org_id||' Serial '||l_sn||' ItemId : '||l_item_id);
46 row_limit :=INV_DIAG_GRP.g_max_row;
47 test_out :='MSN';
48 reportStr :='';
49
50 if l_sn is not null or l_item_id is not null then
51
52 sqltxt :='SELECT msn.serial_number "Serial|Number" '||
53 ', msn.inventory_item_id "Item ID" '||
54 ', ml.meaning || '' ( '' || msn.current_status || '' )'' "Current Status (Id)" '||
55 ', msn.current_subinventory_code "Current|Subinventory" '||
56 ', msn.current_locator_id "Current|Locator Id" '||
57 ', msn.cost_group_id "Cost Group|Id" '||
58 ', msn.lpn_id "LPN Id" '||
59 ', msn.group_mark_id "Group Mark|Id" '||
60 ', msn.line_mark_id "Line Mark|Id" '||
61 ', msn.lot_line_mark_id "Lot Line Mark|Id" '||
62 ', TO_CHAR( msn.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last|Updated" '||
63 ', msn.last_transaction_id "Last Transaction ID" '||
64 ', msn.wip_entity_id "WIP Entity ID" '||
65 ', msn.original_wip_entity_id "Original|WIP Entity ID" '||
66 'FROM mtl_serial_numbers msn '||
67 ', mfg_lookups ml '||
68 'WHERE 1 = 1 ';
69
70 if l_org_id is not null then
71 sqltxt := sqltxt||' and msn.current_organization_id = '||l_org_id;
72 end if;
73 if l_sn is not null then
74 sqltxt := sqltxt||' AND msn.serial_number = '''||l_sn||'''';
75 end if;
76 if l_item_id is not null then
77 sqltxt := sqltxt||' AND msn.inventory_item_id = '||l_item_id;
78 end if;
79 sqltxt := sqltxt||' AND msn.current_status = ml.lookup_code(+)'||
80 'AND ''SERIAL_NUM_STATUS'' = ml.lookup_type(+)'||
81 'ORDER BY msn.serial_number ';
82
83 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
84
85 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Serial Status');
86
87 delete from inv_diag_msn_temp;
88
89 insert into inv_diag_msn_temp
90 (serial_number
91 ,org_id
92 ,inventory_item_id)
93 select distinct serial_number, current_organization_id,inventory_item_id
94 from mtl_serial_numbers
95 where inventory_item_id = nvl(l_item_id, inventory_item_id)
96 and current_organization_id = nvl(l_org_id, current_organization_id)
97 and serial_number = nvl(l_sn, serial_number);
98
99 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
100 statusStr := 'SUCCESS';
101
102 ELSE
103
104 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Parameter input is requred ');
105 statusStr := 'FAILURE';
106 errStr := 'This test failed as: no input';
107 fixInfo := 'Please enter at least one of the following parameters: Serial or ItemId';
108 isFatal := 'SUCCESS';
109
110 end if;
111
112 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
113 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
114
115 EXCEPTION
116 when others then
117 -- this should never happen
118 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('Exception Occurred In RUNTEST');
119 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
120 raise;
121
122 END runTest;
123
124 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
125 BEGIN
126 name := 'Serial Status';
127 END getComponentName;
128
129 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
130 BEGIN
131 descStr := 'Checks for Serial Status';
132 END getTestDesc;
133
134 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
135 BEGIN
136 name := 'MSN';
137 END getTestName;
138
139 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
140 tempDependencies JTF_DIAG_DEPENDTBL;
141
142 BEGIN
143 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
144 END getDependencies;
145
146 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
147 BEGIN
148 str := 'FALSE';
149 END isDependencyPipelined;
150
151
152 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
153 tempOutput JTF_DIAG_OUTPUTTBL;
154 BEGIN
155 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
156 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.addOutput(tempOutput,'testout', test_out);
157 outputValues := tempOutput;
158 EXCEPTION
159 when others then
160 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
161 END getOutputValues;
162
163
164 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
165 tempInput JTF_DIAG_INPUTTBL;
166 BEGIN
167 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
168 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
169 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
170 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'SerialNum','LOV-oracle.apps.inv.diag.lov.SerialLov');
171 -- tempInput := JTF_DIAGNOSTIC_
172 defaultInputValues := tempInput;
173 EXCEPTION
174 when others then
175 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
176 END getDefaultTestParams;
177
178 Function getTestMode return INTEGER IS
179 BEGIN
180 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
181
182 END getTestMode;
183
184 END;