[Home] [Help]
PACKAGE BODY: APPS.OKC_PO_QA_PVT
Source
1 PACKAGE BODY OKC_PO_QA_PVT AS
2 /* $Header: OKCRPQAB.pls 120.0 2005/05/25 22:43:34 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7
8 ----------------------------------------------------------------------------
9 -- Global Constants--------------------------------------------------------
10 ----------------------------------------------------------------------------
11 -- Standard API Constants
12
13 G_DESCRIPTIVE_FLEXFIELD_NAME CONSTANT VARCHAR2(200) := 'OKC Rule Developer DF';
14 G_DF_COUNT CONSTANT NUMBER(2) := 15;
15 G_PACKAGE Varchar2(33) := ' OKC_PO_QA_PVT.';
16 G_MSG_NAME Varchar2(30);
17 G_LINE Varchar2(4) := 'LINE';
18 G_TOKEN Varchar2(30);
19
20
21
22
23
24 ------------------------------------------------------------------------------
25 ---------------- Procedure: Set_QA_Message -------------------------
26 ------------------------------------------------------------------------------
27 -- Purpose: Serves to set the error messages in the screen
28 --
29 -- Parameters: As specified below.
30 --
31 -- Out Parameters: None
32 --
33 -------------------------------------------------------------------------------
34
35 PROCEDURE Set_QA_Message(p_chr_id IN OKC_K_LINES_V.chr_id%TYPE,
36 p_cle_id IN OKC_K_LINES_V.id%TYPE,
37 p_msg_name IN VARCHAR2,
38 p_token1 IN VARCHAR2 ,
39 p_token1_value IN VARCHAR2 ,
40 p_token2 IN VARCHAR2 ,
41 p_token2_value IN VARCHAR2 ,
42 p_token3 IN VARCHAR2 ,
43 p_token3_value IN VARCHAR2 ,
44 p_token4 IN VARCHAR2 ,
45 p_token4_value IN VARCHAR2 ) IS
46 l_line Varchar2(200);
47 l_token1_value Varchar2(200) := p_token1_value;
48 l_token2_value Varchar2(200) := p_token2_value;
49 l_token3_value Varchar2(200) := p_token3_value;
50 l_token4_value Varchar2(200) := p_token4_value;
51 l_return_status Varchar2(3);
52 BEGIN
53 If p_cle_id Is Not Null Then
54 l_line := okc_contract_pub.get_concat_line_no(p_cle_id,
55 l_return_status);
56 If p_token1 = g_line Then
57 l_token1_value := l_line;
58 Elsif p_token2 = g_line Then
59 l_token2_value := l_line;
60 Elsif p_token3 = g_line Then
61 l_token3_value := l_line;
62 Elsif p_token4 = g_line Then
63 l_token4_value := l_line;
64 End If;
65 End If;
66
67 OKC_API.set_message(
68 p_app_name => G_APP_NAME,
69 p_msg_name => p_msg_name,
70 p_token1 => p_token1,
71 p_token1_value => l_token1_value,
72 p_token2 => p_token2,
73 p_token2_value => l_token2_value,
74 p_token3 => p_token3,
75 p_token3_value => l_token3_value,
76 p_token4 => p_token4,
77 p_token4_value => l_token4_value);
78 END;
79
80
81
82 ----------------------------------------------------------------------------
83 -- Public Procedure -------------------------------------------------------
84 ----------------------------------------------------------------------------
85
86 --------------------------------------------------------------------------------
87 ------------- Procedure: Validate_K_FOR_PO ---------------------
88 --------------------------------------------------------------------------------
89 -- Purpose: to check QA validations required for PO
90 --
91 -- In Parameters: p_chr_id Contract header id
92
93 --
94 -- Out Parameters: x_return_status Standard return status
95 ----------------------------------------------------------------------------------
96
97 PROCEDURE Validate_K_FOR_PO(
98 x_return_status OUT NOCOPY VARCHAR2,
99 p_chr_id IN NUMBER
100 ) IS
101
102 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
103 l_dummy VARCHAR2(1) := '?';
104 l_count NUMBER := 0;
105 l_row_notfound BOOLEAN;
106 l_token VARCHAR2(2000);
107
108
109
110 lcontract_intent OKC_K_HEADERS_B.BUY_OR_SELL%TYPE;
111 l_nlines NUMBER := 0;
112
113
114 CURSOR l_oklb_csr IS
115 SELECT oklb.item_to_price_yn item_priced,
116 oklb.price_level_ind priced,
117 oklb.price_negotiated net_price
118 FROM OKC_K_LINES_b oklb
119 WHERE oklb.dnz_chr_id = p_chr_id
120 AND oklb.cle_id IS NULL;
121 l_oklb_rec l_oklb_csr%ROWTYPE;
122
123
124 CURSOR l_oki_csr IS
125 SELECT oki.number_of_items qty,
126 oki.uom_code UOM_CODE
127 FROM OKC_K_LINES_b oklb,
128 OKC_K_ITEMS oki
129 WHERE oklb.dnz_chr_id = oki.dnz_chr_id
130 AND oklb.cle_id IS NULL
131 AND oklb.dnz_chr_id = p_chr_id;
132 l_oki_rec l_oki_csr%ROWTYPE;
133
134 g_rg_shipping CONSTANT VARCHAR2(12) := 'OKPSHIPPING';
135 g_rg_payment CONSTANT VARCHAR2(12) := 'PAYMENT';
136
137
138 lshipping_gr_count NUMBER := 0;
139 lpayment_gr_count NUMBER := 0;
140
141
142 lno_of_vendors NUMBER := 0;
143 lno_of_customers NUMBER := 0;
144 lbuyer_contact NUMBER := 0;
145
146
147
148 --
149 l_proc varchar2(72) := g_package||'check_required_values';
150 l_line Varchar2(200);
151 --
152 BEGIN
153
154 IF (l_debug = 'Y') THEN
155 okc_debug.Set_Indentation(l_proc);
156 okc_debug.Log('10: Entering ',2);
157 END IF;
158
159 -- initialize return status
160 x_return_status := OKC_API.G_RET_STS_SUCCESS;
161
162
163
164
165 /*********To Check if the intent of the contract is BUY***************/
166
167 SELECT buy_or_sell
168 INTO lcontract_intent
169 FROM OKC_K_HEADERS_b okhb
170 WHERE okhb.id = p_chr_id;
171
172 IF (lcontract_intent <> 'B') THEN
173 OKC_API.set_message(
174 p_app_name => G_APP_NAME,
175 p_msg_name => 'OKC_QA_INTENT_BUY',
176 p_token1 => 'CONTRACT_INTENT',
177 p_token1_value => 'BUY');
178
179 x_return_status := OKC_API.G_RET_STS_ERROR;
180 END IF;
181 /*********CONTRACT SHOULD HAVE ATLEAST ONE LINE***************/
182 BEGIN
183
184 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
185
186 SELECT 1 /*+ FIRST_ROWS */
187 INTO l_nlines
188 FROM OKC_K_LINES_B oklb
189 WHERE oklb.dnz_chr_id = p_chr_id
190 and rownum<2;
191
192 END IF;
193
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 OKC_API.set_message(
197 p_app_name => G_APP_NAME,
198 p_msg_name => 'OKC_QA_K_LINES');
199
200 x_return_status := OKC_API.G_RET_STS_ERROR;
201 NULL;
202
203 END;
204
205 /*********ARE ALL THE TOP LINES HAVING PRICED HAS ITEM_TO_PRICE FLAG CHECKED***************/
206
207 -- fetch the contract header information
208 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
209 OPEN l_oklb_csr;
210 LOOP
211 FETCH l_oklb_csr INTO l_oklb_rec;
212 EXIT WHEN l_oklb_csr%NOTFOUND;
213
214 -- check required data for contract header
215 IF (l_oklb_rec.item_priced = 'N') THEN
216 OKC_API.set_message(
217 p_app_name => G_APP_NAME,
218 p_msg_name => 'OKC_QA_ITEM_PRICED');
219
220 -- notify caller of an error
221 x_return_status := OKC_API.G_RET_STS_ERROR;
222 END IF;
223
224 IF (l_oklb_rec.priced = 'N') THEN
225 OKC_API.set_message(
226 p_app_name => G_APP_NAME,
227 p_msg_name => 'OKC_QA_PRICED_CHECK');
228
229 -- notify caller of an error
230 x_return_status := OKC_API.G_RET_STS_ERROR;
231 END IF;
232
233 IF (l_oklb_rec.net_price < 0 OR l_oklb_rec.net_price IS NULL) THEN
234 OKC_API.set_message(
235 p_app_name => G_APP_NAME,
236 p_msg_name => 'OKC_QA_NETPRICE_CHECK');
237 -- notify caller of an error
238 x_return_status := OKC_API.G_RET_STS_ERROR;
239 END IF;
240 END LOOP;
241 CLOSE l_oklb_csr;
242 END IF;
243
244 /*********DO ALL THE TOP LINES HAVE qty>0, valid UOM CODE, valid net price***************/
245 -- Check that at least 2 different parties have been attached
246 -- to the contract header.
247 -- get party count
248 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
249 OPEN l_oki_csr;
250 LOOP
251 FETCH l_oki_csr INTO l_oki_rec;
252 EXIT WHEN l_oki_csr%NOTFOUND;
253
254
255 IF (l_oki_rec.qty <= 0 OR l_oki_rec.qty IS NULL) THEN
256 OKC_API.set_message(
257 p_app_name => G_APP_NAME,
258 p_msg_name => 'OKC_QA_QTY_CHECK');
259 -- notify caller of an error
260 x_return_status := OKC_API.G_RET_STS_ERROR;
261 END IF;
262 IF (l_oki_rec.UOM_CODE IS NULL) THEN
263 OKC_API.set_message(
264 p_app_name => G_APP_NAME,
265 p_msg_name => 'OKC_QA_UOMCODE_CHECK');
266 -- notify caller of an error
267 x_return_status := OKC_API.G_RET_STS_ERROR;
268 END IF;
269 END LOOP;
270 CLOSE l_oki_csr;
271 END IF;
272 /*******Do the follwong rulegroups exist on the contract header---SHIPPING, PAYMENT***************/
273
274 BEGIN
275 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
276 SELECT 1 /*+ FIRST_ROWS */
277 INTO lshipping_gr_count
278 FROM okc_rule_groups_b rgp
279 WHERE rgp.dnz_chr_id = p_chr_id
280 AND rgp.cle_id IS NULL
281 AND (rgp.rgd_code = g_rg_shipping)
282 and rownum<2;
283
284 END IF;
285
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 OKC_API.set_message(
289 p_app_name => G_APP_NAME,
290 p_msg_name => 'OKC_QA_RG_SHIPPING',
291 p_token1 => 'RULE_GROUP',
292 p_token1_value => 'SHIPPING');
293 -- notify caller of an error
294 x_return_status := OKC_API.G_RET_STS_ERROR;
295 NULL;
296 END;
297
298 BEGIN
299 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
300 SELECT '1' /*+ FIRST_ROWS */
301 INTO lpayment_gr_count
302 FROM okc_rule_groups_b rgp
303 WHERE rgp.dnz_chr_id = p_chr_id
304 AND rgp.cle_id IS NULL
305 AND (rgp.rgd_code = g_rg_payment)
306 and rownum < 2;
307
308
309
310 END IF;
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN
313 OKC_API.set_message(
314 p_app_name => G_APP_NAME,
315 p_msg_name => 'OKC_QA_RG_PAYMENT',
316 p_token1 => 'RULE_GROUP',
317 p_token1_value => 'PAYMENT');
318 -- notify caller of an error
319 x_return_status := OKC_API.G_RET_STS_ERROR;
320 NULL;
321 END;
322
323
324 /******Is there one and only one value for each partyrole - VENDOR/CUSTOMER***********/
325
326 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
327 SELECT count(*)
328 INTO lno_of_vendors
329 FROM okc_k_party_roles_b cpr
330 WHERE cpr.rle_code = 'VENDOR'
331 AND cpr.cle_id IS NULL -- header level vendors only
332 AND cpr.dnz_chr_id = p_chr_id;
333
334 IF (lno_of_vendors <> 1) THEN
335 OKC_API.set_message(
336 p_app_name => G_APP_NAME,
337 p_msg_name => 'OKC_QA_VENDOR_ROLE',
338 p_token1 => 'PARTY_ROLE',
339 p_token1_value => 'VENDOR');
340
341 -- notify caller of an error
342 x_return_status := OKC_API.G_RET_STS_ERROR;
343 END IF;
344
345
346 SELECT count(*)
347 INTO lno_of_customers
348 FROM okc_k_party_roles_b cpr
349 WHERE cpr.rle_code = 'CUSTOMER'
350 AND cpr.cle_id IS NULL -- header level customers only
351 AND cpr.dnz_chr_id = p_chr_id;
352
353 IF (lno_of_customers <> 1) THEN
354 OKC_API.set_message(
355 p_app_name => G_APP_NAME,
356 p_msg_name => 'OKC_QA_CUSTOMER_ROLE',
357 p_token1 => 'PARTY_ROLE',
358 p_token1_value => 'CUSTOMER');
359
360 -- notify caller of an error
361 x_return_status := OKC_API.G_RET_STS_ERROR;
362 END IF;
363 END IF;
364
365 /************DOES THE PARTY ROLE CUSTOMER HAS A CONTACT CALLED BUYER**************************/
366 BEGIN
367 IF (x_return_status<>OKC_API.G_RET_STS_ERROR) THEN
368 SELECT 1 /*+ FIRST_ROWS */
369 INTO lbuyer_contact
370 FROM okc_contacts okcc,
371 okc_k_party_roles_b okpr
372 WHERE okcc.dnz_chr_id = okpr.dnz_chr_id
373 AND okcc.cro_code = 'BUYER'
374 AND okpr.rle_code = 'CUSTOMER'
375 AND okcc.dnz_chr_id = p_chr_id
376 AND rownum<2;
377
378 END IF;
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 OKC_API.set_message(
382 p_app_name => G_APP_NAME,
383 p_msg_name => 'OKC_QA_BUYER_CHECK',
384 p_token1 => 'CONTACT_NAME',
385 p_token1_value => 'BUYER' ,
386 p_token2 => 'PARTY_ROLE',
387 p_token2_value => 'CUSTOMER');
388 -- notify caller of an error
389 x_return_status := OKC_API.G_RET_STS_ERROR;
390 NULL;
391 END;
392
393 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
394 OKC_API.set_message(
395 p_app_name => G_APP_NAME,
396 p_msg_name => G_QA_SUCCESS);
397 END IF;
398
399
400
401 IF (l_debug = 'Y') THEN
402 okc_debug.Log('1000: Leaving ',2);
403 okc_debug.Reset_Indentation;
404 END IF;
405 EXCEPTION
406 WHEN G_EXCEPTION_HALT_VALIDATION THEN
407 IF (l_debug = 'Y') THEN
408 okc_debug.Log('2000: Leaving ',2);
409 okc_debug.Reset_Indentation;
410 END IF;
411 -- no processing necessary; validation can continue with next column
412 NULL;
413 WHEN OTHERS THEN
414 IF (l_debug = 'Y') THEN
415 okc_debug.Log('3000: Leaving ',2);
416 okc_debug.Reset_Indentation;
417 END IF;
418 -- store SQL error message on message stack
419 OKC_API.SET_MESSAGE(
420 p_app_name => G_APP_NAME,
421 p_msg_name => G_UNEXPECTED_ERROR,
422 p_token1 => G_SQLCODE_TOKEN,
423 p_token1_value => SQLCODE,
424 p_token2 => G_SQLERRM_TOKEN,
425 p_token2_value => SQLERRM);
426 -- notify caller of an error as UNEXPETED error
427 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
428 -- verify that cursor was closed
429 IF l_oki_csr%ISOPEN THEN
430 CLOSE l_oki_csr;
431 END IF;
432 IF l_oklb_csr%ISOPEN THEN
433 CLOSE l_oklb_csr;
434 END IF;
435
436 END Validate_K_FOR_PO;
437
438 ----------------------------------------------------------------------------------
439
440 END OKC_PO_QA_PVT;