[Home] [Help]
PACKAGE BODY: APPS.GMD_CUSTOMER_TESTS_GRP
Source
1 PACKAGE BODY GMD_CUSTOMER_TESTS_GRP as
2 /* $Header: GMDGTCUB.pls 115.4 2004/04/07 16:31:44 pupakare noship $*/
3
4 /*===========================================================================
5 PROCEDURE NAME: Check_exists
6
7 DESCRIPTION: This Functions Detemines if Unqiue Record Exists.
8
9
10 CHANGE HISTORY: Created 29-SEP-02 HVERDDIN
11 ===========================================================================*/
12 FUNCTION CHECK_EXISTS( p_test_id IN NUMBER,
13 p_cust_id IN NUMBER)
14 RETURN BOOLEAN IS
15
16 l_progress VARCHAR2(3);
17 l_exists VARCHAR2(1);
18 BEGIN
19 l_progress := '010';
20
21 IF p_test_id IS NULL THEN
22 GMD_API_PUB.log_message('GMD','GMD_TEST_ID_CODE_NULL');
23 RETURN FALSE;
24 END IF;
25
26 IF p_cust_id is NULL THEN
27 GMD_API_PUB.log_message('GMD_API_CUST_ID_NULL');
28 RETURN FALSE;
29 END IF;
30
31 l_progress := '020';
32
33 BEGIN
34 SELECT 'X' INTO l_exists
35 FROM GMD_CUSTOMER_TESTS
36 WHERE test_id = p_test_id
37 AND cust_id = p_cust_id;
38
39 RETURN TRUE;
40
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RETURN FALSE;
44 END;
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 RETURN FALSE;
49 END CHECK_EXISTS;
50
51
52 /*===========================================================================
53 PROCEDURE NAME: validate_before_insert
54
55 DESCRIPTION: This procedure validates customer values before insert.
56
57
58 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
59 ===========================================================================*/
60 PROCEDURE VALIDATE_BEFORE_INSERT(
61 p_init_msg_list IN VARCHAR2 ,
62 p_customer_tests_rec IN GMD_CUSTOMER_TESTS%ROWTYPE,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_message_data OUT NOCOPY VARCHAR2) IS
65
66 l_progress VARCHAR2(3);
67 l_dummy NUMBER;
68 l_delete_mark NUMBER;
69 l_display_precision NUMBER;
70 l_test_type VARCHAR2(1);
71
72 CURSOR c_check_test (p_test_id IN NUMBER)
73 IS
74 SELECT display_precision,test_type ,delete_mark
75 FROM GMD_QC_TESTS
76 WHERE test_id = p_test_id
77 AND delete_mark = 0;
78
79 CURSOR c_check_cust ( p_cust_id IN NUMBER)
80 IS
81 SELECT 1
82 FROM hz_cust_accounts_all a,
83 hz_parties p,
84 hz_cust_acct_sites_all s,
85 gl_plcy_mst g
86 WHERE a.cust_account_id = s.cust_account_id
87 AND p.party_id = a.party_id
88 AND g.org_id = s.org_id
89 AND a.cust_account_id = p_cust_id;
90
91
92 BEGIN
93 l_progress := '010';
94
95 x_return_status := FND_API.G_RET_STS_SUCCESS ;
96 IF FND_API.to_Boolean( p_init_msg_list ) THEN
97 FND_MSG_PUB.initialize; -- clear the message stack.
98 END IF;
99
100 -- Validate that required Fields are populated.
101
102 IF p_customer_tests_rec.test_id is NULL THEN
103 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
104 RAISE FND_API.G_EXC_ERROR;
105 ELSE
106 -- Check Test is Valid
107 OPEN c_check_test (p_customer_tests_rec.test_id);
108 FETCH c_check_test INTO l_display_precision,
109 l_test_type,l_delete_mark;
110 IF c_check_test%NOTFOUND THEN
111 CLOSE c_check_test;
112 GMD_API_PUB.log_message('GMD_INVALID_TEST_ID',
113 'TEST_ID',p_customer_tests_rec.test_id);
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116 CLOSE c_check_test;
117 END IF;
118
119
120 IF p_customer_tests_rec.cust_id is NULL THEN
121 GMD_API_PUB.log_message('GMD_API_CUST_ID_NULL');
122 RAISE FND_API.G_EXC_ERROR;
123 ELSE
124 -- Check Customer is Valid
125 OPEN c_check_cust (p_customer_tests_rec.cust_id);
126 FETCH c_check_cust INTO l_dummy;
127 IF c_check_cust%NOTFOUND THEN
128 CLOSE c_check_cust;
129 GMD_API_PUB.log_message('GMD_INVALID_CUST_ID',
130 'CUST_ID',p_customer_tests_rec.cust_id);
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133 CLOSE c_check_cust;
134 END IF;
135
136
137 -- Validate that header record is not delete Marked.
138
139 IF l_delete_mark = 1 THEN
140 GMD_API_PUB.log_message('GMD_TEST_DELETED',
141 'TEST',p_customer_tests_rec.test_id);
142 RAISE FND_API.G_EXC_ERROR;
143 END IF;
144
145 -- Validate that record does not exist
146
147 l_progress := '020';
148
149 IF CHECK_EXISTS ( p_test_id => p_customer_tests_rec.test_id,
150 p_cust_id => p_customer_tests_rec.cust_id) THEN
151
152 GMD_API_PUB.log_message('GMD_CUST_TESTS_EXISTS',
153 'CUST_ID',p_customer_tests_rec.cust_id,
154 'TEST_ID',p_customer_tests_rec.test_id);
155 RAISE FND_API.G_EXC_ERROR;
156 END IF;
157
158
159 -- Validate Report Precison and Display
160
161 IF p_customer_tests_rec.cust_test_display IS NULL AND
162 p_customer_tests_rec.report_precision IS NULL THEN
163 GMD_API_PUB.log_message('GMD','GMD_CUST_TEST_REQ');
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167 l_progress := '030';
168
169 -- Validate Business Rules For Precision and Display
170
171 IF l_test_type in ('L','N','E') THEN
172
173 IF p_customer_tests_rec.report_precision is NOT NULL AND
174 p_customer_tests_rec.report_precision > l_display_precision THEN
175 GMD_API_PUB.log_message('GMD_REP_GRTR_DIS_PRCSN',
176 'DISPLAY_PRECISION', l_display_precision);
177 RAISE FND_API.G_EXC_ERROR;
178
179 END IF;
180 ELSE
181
182 IF p_customer_tests_rec.report_precision is NOT NULL THEN
183 GMD_API_PUB.log_message('GMD_REP_PRCSN_INVALID',
184 'TEST_TYPE',l_test_type);
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 IF p_customer_tests_rec.cust_test_display IS NULL THEN
189 GMD_API_PUB.log_message('GMD_CUST_DISPLAY_REQ');
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192
193 END IF;
194
195 EXCEPTION
196
197 WHEN FND_API.G_EXC_ERROR THEN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 WHEN OTHERS THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 GMD_API_PUB.log_message('GMD_API_ERROR',
202 'PACKAGE' , 'GMD_CUSTOMER_TESTS_GRP',
203 'ERROR' , substr(sqlerrm,1,100),
204 'POSITION' , l_progress);
205
206 END VALIDATE_BEFORE_INSERT;
207
208 PROCEDURE VALIDATE_BEFORE_DELETE(
209 p_init_msg_list IN VARCHAR2 ,
210 p_test_id IN NUMBER,
211 p_cust_id IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_message_data OUT NOCOPY VARCHAR2) IS
214
215 l_progress VARCHAR2(3);
216 l_delete_mark NUMBER;
217
218 CURSOR c_check_deleted ( p_test_id NUMBER)
219 IS
220 SELECT delete_mark
221 FROM GMD_QC_TESTS
222 WHERE test_id = p_test_id;
223
224 BEGIN
225 l_progress := '010';
226
227 x_return_status := FND_API.G_RET_STS_SUCCESS ;
228 IF FND_API.to_Boolean( p_init_msg_list ) THEN
229 FND_MSG_PUB.initialize; -- clear the message stack.
230 END IF;
231
232 -- Validate that test_id and cust_id are populated.
233
234 IF p_test_id is NULL THEN
235 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238
239 IF p_cust_id is NULL THEN
240 GMD_API_PUB.log_message('GMD_API_CUST_ID_NULL');
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243
244 -- Validate that record Exists
245
246 IF NOT CHECK_EXISTS ( p_test_id => p_test_id,
247 p_cust_id => p_cust_id) THEN
248
249 GMD_API_PUB.log_message('GMD_CUST_TESTS_NOTEXISTS',
250 'CUST_ID',p_cust_id,
251 'TEST_ID',p_test_id);
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254
255 l_progress := '020';
256
257 -- Validate that the test_header row has been marked for Purge.
258
259 OPEN c_check_deleted (p_test_id);
260 FETCH c_check_deleted into l_delete_mark;
261 CLOSE c_check_deleted;
262
263 -- BUG 3554590 Check if the TEST is already deleted!
264 IF l_delete_mark = 1 THEN
265 GMD_API_PUB.log_message('GMD_TEST_DELETED',
266 'TEST',p_test_id);
267 RAISE FND_API.G_EXC_ERROR;
268 END IF;
269
270
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 x_return_status := FND_API.G_RET_STS_ERROR ;
274 WHEN OTHERS THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 GMD_API_PUB.log_message('GMD_API_ERROR',
277 'PACKAGE' , 'GMD_CUSTOMER_TESTS_GRP',
278 'ERROR' , substr(sqlerrm,1,100),
279 'POSITION' , l_progress);
280
281 END VALIDATE_BEFORE_DELETE;
282
283 END GMD_CUSTOMER_TESTS_GRP ;