[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_DIAG_LCM_02
Source
1 PACKAGE BODY INV_RCV_DIAG_LCM_02 AS
2 /* $Header: INVRCV2B.pls 120.1 2009/03/26 21:13:48 vthevark 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
31 BEGIN
32
33 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
34 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
35 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
36
37 -- Printing RCV_SHIPMENT_HEADERS Data
38 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_HEADERS"></a>');
39 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_HEADERS</b><a href="#INDEX OF QUERIES">[Top]</a>');
40
41 sqltxt := ' select rsh.* ' ||
42 ' FROM rcv_shipment_lines rsl,' ||
43 ' rcv_shipment_headers rsh ' ||
44 ' WHERE rsh.shipment_header_id = rsl.shipment_header_id' ||
45 ' AND lcm_shipment_line_id in ' ||
46 ' ( SELECT lcm_shipment_line_id' ||
47 ' FROM (SELECT lcm_shipment_line_id, count(shipment_line_id)' ||
48 ' FROM rcv_shipment_lines' ||
49 ' WHERE lcm_shipment_line_id is not null' ||
50 ' GROUP BY lcm_shipment_line_id' ||
51 ' HAVING COUNT(shipment_line_id) > 1))' ||
52 ' ORDER BY lcm_shipment_line_id, shipment_line_id';
53
54 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
55 JTF_DIAGNOSTIC_COREAPI.BRPrint;
56
57 -- Printing RCV_SHIPMENT_LINES Data
58 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_SHIPMENT_LINES"></a>');
59 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_SHIPMENT_LINES</b><a href="#INDEX OF QUERIES">[Top]</a>');
60
61 sqltxt := ' select * ' ||
62 ' FROM rcv_shipment_lines' ||
63 ' WHERE lcm_shipment_line_id in ' ||
64 ' ( SELECT lcm_shipment_line_id' ||
65 ' FROM (SELECT lcm_shipment_line_id, count(shipment_line_id)' ||
66 ' FROM rcv_shipment_lines' ||
67 ' WHERE lcm_shipment_line_id is not null' ||
68 ' GROUP BY lcm_shipment_line_id' ||
69 ' HAVING COUNT(shipment_line_id) > 1))' ||
70 ' ORDER BY lcm_shipment_line_id, shipment_line_id';
71 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
72 JTF_DIAGNOSTIC_COREAPI.BRPrint;
73
74 -- Printing PO_LINE_LOCATIONS_ALL Data
75 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="PO_LINE_LOCATIONS_ALL"></a>');
76 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>PO_LINE_LOCATIONS_ALL</b><a href="#INDEX OF QUERIES">[Top]</a>');
77
78 sqltxt := ' select pll.* ' ||
79 ' FROM rcv_shipment_lines rsl,' ||
80 ' po_line_locations_all pll ' ||
81 ' WHERE pll.line_location_id = rsl.po_line_location_id' ||
82 ' AND lcm_shipment_line_id in ' ||
83 ' ( SELECT lcm_shipment_line_id' ||
84 ' FROM (SELECT lcm_shipment_line_id, count(shipment_line_id)' ||
85 ' FROM rcv_shipment_lines' ||
86 ' WHERE lcm_shipment_line_id is not null' ||
87 ' GROUP BY lcm_shipment_line_id' ||
88 ' HAVING COUNT(shipment_line_id) > 1))' ||
89 ' ORDER BY lcm_shipment_line_id, shipment_line_id';
90
91 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
92 JTF_DIAGNOSTIC_COREAPI.BRPrint;
93
94 -- Printing MTL_PARAMETERS Data
95 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="MTL_PARAMETERS"></a>');
96 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>MTL_PARAMETERS</b><a href="#INDEX OF QUERIES">[Top]</a>');
97
98 sqltxt := ' select mp.* ' ||
99 ' FROM mtl_parameters mp ' ||
100 ' WHERE mp.organization_id in ' ||
101 ' (SELECT distinct rsl.to_organization_id ' ||
102 ' FROM rcv_shipment_lines rsl,' ||
103 ' mtl_parameters mp ' ||
104 ' WHERE rsl.to_organization_id = mp.organization_id' ||
105 ' AND lcm_shipment_line_id in ' ||
106 ' ( SELECT lcm_shipment_line_id' ||
107 ' FROM (SELECT lcm_shipment_line_id, count(shipment_line_id)' ||
108 ' FROM rcv_shipment_lines' ||
109 ' WHERE lcm_shipment_line_id is not null' ||
110 ' GROUP BY lcm_shipment_line_id' ||
111 ' HAVING COUNT(shipment_line_id) > 1)))';
112
113 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
114 JTF_DIAGNOSTIC_COREAPI.BRPrint;
115
116 -- Printing RCV_PARAMETERS Data
117 JTF_DIAGNOSTIC_COREAPI.insert_html('<a name="RCV_PARAMETERS"></a>');
118 JTF_DIAGNOSTIC_COREAPI.insert_html('<b>RCV_PARAMETERS</b><a href="#INDEX OF QUERIES">[Top]</a>');
119
120 sqltxt := ' select rp.* ' ||
121 ' FROM rcv_parameters rp ' ||
122 ' WHERE rp.organization_id in ' ||
123 ' (SELECT distinct rsl.to_organization_id ' ||
124 ' FROM rcv_shipment_lines rsl,' ||
125 ' mtl_parameters mp ' ||
129 ' FROM (SELECT lcm_shipment_line_id, count(shipment_line_id)' ||
126 ' WHERE rsl.to_organization_id = mp.organization_id' ||
127 ' AND lcm_shipment_line_id in ' ||
128 ' ( SELECT lcm_shipment_line_id' ||
130 ' FROM rcv_shipment_lines' ||
131 ' WHERE lcm_shipment_line_id is not null' ||
132 ' GROUP BY lcm_shipment_line_id' ||
133 ' HAVING COUNT(shipment_line_id) > 1)))';
134
135 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
136 JTF_DIAGNOSTIC_COREAPI.BRPrint;
137
138 -- Test Completed successfully.
139 statusStr := 'SUCCESS';
140 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
141 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
142
143 EXCEPTION
144 when others then
145 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
146 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
147 statusStr := 'FAILURE';
148 errStr := sqlerrm ||' occurred in script Exception handled';
149 fixInfo := 'Unexpected Exception in INVDP08B.pls';
150 isFatal := 'FALSE';
151 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
152 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
153 END runTest;
154
155
156 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
157 BEGIN
158 name := 'Duplicate lcm_shipment_id in rcv_shipment_lines';
159 END getComponentName;
160
161 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
162 BEGIN
163 descStr := 'Duplicate lcm_shipment_id in rcv_shipment_lines';
164 END getTestDesc;
165
166 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
167 BEGIN
168 name := 'Duplicate lcm shipment lines in rcv_shipment_lines';
169 END getTestName;
170
171 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
172 tempDependencies JTF_DIAG_DEPENDTBL;
173
174 BEGIN
175 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
176 END getDependencies;
177
178 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
179 BEGIN
180 str := 'FALSE';
181 END isDependencyPipelined;
182
183 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
184 tempOutput JTF_DIAG_OUTPUTTBL;
185 BEGIN
186 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
187 outputValues := tempOutput;
188 EXCEPTION
189 when others then
190 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
191 END getOutputValues;
192
193 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
194 tempInput JTF_DIAG_INPUTTBL;
195 BEGIN
196 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
197 defaultInputValues := tempInput;
198 EXCEPTION
199 when others then
200 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
201 END getDefaultTestParams;
202
203 Function getTestMode return INTEGER IS
204 BEGIN
205 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
206 END getTestMode;
207
208 END INV_RCV_DIAG_LCM_02;