[Home] [Help]
PACKAGE BODY: APPS.PO_CLM_COLS_SUB_CHECK
Source
1 PACKAGE BODY PO_CLM_COLS_SUB_CHECK AS
2 /* $Header: PO_CLM_COLS_SUB_CHECK.plb 120.30.12020000.3 2013/04/29 10:33:54 mabaig ship $ */
3
4
5 /* The below procedure checks
6 * Order total amount should be between the defined Minimum & Maximum Order Amount on the IDV.
7 */
8 PROCEDURE CLM_ORDER_AMT_WITHIN_MAX_MIN
9 (
10 p_document_id IN NUMBER
11 ,p_draft_id IN NUMBER
12 ,p_online_report_id IN NUMBER
13 ,p_login_id IN NUMBER
14 ,p_user_id IN NUMBER
15 ,p_sequence IN NUMBER
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,x_errorcode OUT NOCOPY NUMBER
18 ,x_msg_count OUT NOCOPY NUMBER
19 ,x_msg_data OUT NOCOPY VARCHAR2
20 )
21 IS
22 d_progress NUMBER := 0;
23 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
24 l_api_name CONSTANT VARCHAR2(100) := 'CLM_ORDER_AMT_WITHIN_MAX_MIN';
25
26 l_idv_min_ord_amt NUMBER;
27 l_idv_max_ord_amt NUMBER;
28 l_document_type_code VARCHAR2(100);
29 l_from_header_id NUMBER;
30 l_total_amt_ordered NUMBER;
31
32 CURSOR c_get_po_lines(p_document_id NUMBER) IS
33 SELECT FROM_HEADER_ID IDV_HEADER_ID, LINE_NUM,
34 FROM_LINE_ID IDV_LINE_ID, PO_LINE_ID
35 FROM po_lines_gt
36 WHERE PO_HEADER_ID = p_document_id
37 AND CLM_INFO_FLAG <> 'Y';
38
39 BEGIN
40 d_progress := 10;
41 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_ORDER_AMT_WITHIN_MAX_MIN with parameters' , d_progress);
42 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
43 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
44
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46 BEGIN
47 d_progress := 20;
48 SELECT TYPE_LOOKUP_CODE
49 INTO l_document_type_code
50 FROM po_headers_gt
51 WHERE PO_HEADER_ID = p_document_id;
52
53 d_progress := 30;
54 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
55 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_from_header_id , d_progress);
56 EXCEPTION
57 WHEN OTHERS THEN
58 d_progress := 40;
59 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, FROM_HEADER_ID' , d_progress);
60 RAISE;
61 END;
62
63 d_progress := 50;
64 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
65 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
66 IF c_get_po_lines_rec.IDV_HEADER_ID IS NOT NULL
67 AND c_get_po_lines_rec.IDV_LINE_ID IS NOT NULL
68 THEN
69 d_progress := 60;
70 BEGIN
71 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID :' || c_get_po_lines_rec.IDV_HEADER_ID , d_progress);
72 PO_UDA_DEFAULTING_PKG.DEBUG('PO_LINE_ID :' || c_get_po_lines_rec.IDV_LINE_ID , d_progress);
73 d_progress := 70;
74
75 l_total_amt_ordered := getAmountOrderedForIDV
76 (
77 p_level => 'HEADER'
78 ,p_doc_level_id => p_document_id
79 ,p_idv_header_id => c_get_po_lines_rec.IDV_HEADER_ID
80 ,p_idv_line_id => c_get_po_lines_rec.IDV_LINE_ID
81 );
82
83 SELECT NVL(CLM_MAX_ORDER_AMOUNT, l_total_amt_ordered + 1),
84 NVL(CLM_MIN_ORDER_AMOUNT, l_total_amt_ordered - 1)
85 INTO l_idv_max_ord_amt,
86 l_idv_min_ord_amt
87 FROM po_lines_all
88 WHERE PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
89 AND PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
90
91 d_progress := 80;
92
93 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_max_ord_amt :' || l_idv_max_ord_amt , d_progress);
94 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_min_ord_amt :' || l_idv_min_ord_amt , d_progress);
95 EXCEPTION
96 WHEN OTHERS THEN
97 d_progress := 90;
98 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_MAX_ORDER_AMOUNT, CLM_MIN_ORDER_AMOUNT' , d_progress);
99 RAISE;
100 END;
101
102 d_progress := 100;
103 IF l_total_amt_ordered BETWEEN l_idv_min_ord_amt AND l_idv_max_ord_amt THEN
104 d_progress := 110;
105 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORDER_AMT_WITHIN_MAX_MIN' , d_progress);
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107 ELSE
108 d_progress := 120;
109 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORDER_AMT_WITHIN_MAX_MIN' , d_progress);
110
111 x_return_status := FND_API.G_RET_STS_ERROR;
112
113 LOG_ERR_CLM_NMD_COLS_SUB_CHK
114 (
115 p_online_report_id => p_online_report_id
116 ,p_login_id => p_login_id
117 ,p_user_id => p_user_id
118 ,p_line_num => c_get_po_lines_rec.LINE_NUM
119 ,p_sequence => p_sequence
120 ,p_return_message => 'PO_CLM_ORD_AMT_IN_MAX_MIN'
121 );
122
123 END IF;
124 END IF;
125 END LOOP;
126 ELSE
127 d_progress := 130;
128 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130 x_msg_data := 'Not a Standard PO';
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_ORDER_AMT_WITHIN_MAX_MIN' , d_progress);
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 END CLM_ORDER_AMT_WITHIN_MAX_MIN;
137
138 /* The below procedure checks
139 * Maximum Order amount should be less than or equal to Sum of all lines amount & amount limit
140 */
141 PROCEDURE CLM_MAX_ORD_AMT_WITIN_SUM_AMT
142 (
143 p_document_id IN NUMBER
144 ,p_draft_id IN NUMBER
145 ,p_online_report_id IN NUMBER
146 ,p_login_id IN NUMBER
147 ,p_user_id IN NUMBER
148 ,p_sequence IN NUMBER
149 ,x_return_status OUT NOCOPY VARCHAR2
150 ,x_errorcode OUT NOCOPY NUMBER
151 ,x_msg_count OUT NOCOPY NUMBER
152 ,x_msg_data OUT NOCOPY VARCHAR2
153 )
154 IS
155
156 d_progress NUMBER := 0;
157 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
158 l_api_name CONSTANT VARCHAR2(100) := 'CLM_MAX_ORD_AMT_WITIN_SUM_AMT';
159
160 l_amt_limit NUMBER;
161 l_idv_max_ord_amt NUMBER;
162 l_total_line_amt NUMBER;
163 l_document_type_code VARCHAR2(100);
164 l_total_line_amt_ordered NUMBER;
165 l_total_amt_ordered NUMBER := 0;
166
167 CURSOR c_get_idv_lines(p_document_id NUMBER) IS
168 SELECT LINE_NUM,
169 PO_LINE_ID
170 FROM po_lines_gt
171 WHERE PO_HEADER_ID = p_document_id;
172
173 BEGIN
174 d_progress := 10;
175 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_MAX_ORD_AMT_WITIN_SUM_AMT with parameters' , d_progress);
176 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
177 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
178
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180 BEGIN
181 d_progress := 20;
182 SELECT TYPE_LOOKUP_CODE
183 INTO l_document_type_code
184 FROM po_headers_gt
185 WHERE PO_HEADER_ID = p_document_id;
186
187 d_progress := 30;
188 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
189 EXCEPTION
190 WHEN OTHERS THEN
191 d_progress := 40;
192 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE' , d_progress);
193 RAISE;
194 END;
195
196 d_progress := 50;
197 IF l_document_type_code = 'BLANKET' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
198 d_progress := 60;
199 BEGIN
200 SELECT CLM_MAX_ORDER_AMOUNT, AMOUNT_LIMIT
201 INTO l_idv_max_ord_amt, l_amt_limit
202 FROM po_headers_gt
203 WHERE PO_HEADER_ID = p_document_id;
204
205 d_progress := 70;
206 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_max_ord_amt :' || l_idv_max_ord_amt , d_progress);
207 PO_UDA_DEFAULTING_PKG.DEBUG('l_amt_limit :' || l_amt_limit , d_progress);
208 EXCEPTION
209 WHEN OTHERS THEN
210 d_progress := 80;
211 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_MAX_ORDER_AMOUNT, AMOUNT_LIMIT' , d_progress);
212 RAISE;
213 END;
214
215 d_progress := 90;
216 BEGIN
217 FOR c_get_idv_lines_rec IN c_get_idv_lines(p_document_id) LOOP
218
219 l_total_line_amt_ordered := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered
220 (
221 p_doc_level => 'LINE'
222 ,p_doc_level_id => c_get_idv_lines_rec.PO_LINE_ID
223 ,p_data_source => PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION
224 ,p_draft_id => p_draft_id
225 );
226 l_total_amt_ordered := l_total_amt_ordered + l_total_line_amt_ordered;
227
228 END LOOP;
229 d_progress := 100;
230 PO_UDA_DEFAULTING_PKG.DEBUG('l_total_amt_ordered :' || l_total_amt_ordered , d_progress);
231 EXCEPTION
232 WHEN OTHERS THEN
233 d_progress := 110;
234 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch AMOUNT' , d_progress);
235 RAISE;
236 END;
237
238 d_progress := 120;
239 IF ((l_idv_max_ord_amt <= l_amt_limit)
240 OR l_amt_limit IS NULL
241 OR l_idv_max_ord_amt IS NULL)
242 AND((l_idv_max_ord_amt <= l_total_amt_ordered)
243 OR l_idv_max_ord_amt IS NULL
244 OR l_total_amt_ordered IS NULL)
245 THEN
246 d_progress := 130;
247 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_MAX_ORD_AMT_WITIN_SUM_AMT' , d_progress);
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249 ELSE
250 d_progress := 140;
251 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_MAX_ORD_AMT_WITIN_SUM_AMT' , d_progress);
252 x_return_status := FND_API.G_RET_STS_ERROR;
253
254 LOG_ERR_CLM_NMD_COLS_SUB_CHK
255 (
256 p_online_report_id => p_online_report_id
257 ,p_login_id => p_login_id
258 ,p_user_id => p_user_id
259 ,p_line_num => 0
260 ,p_sequence => p_sequence
261 ,p_return_message => 'PO_CLM_MAX_ORD_AMT_IN_SUM_AMT'
262 );
263 END IF;
264 d_progress := 150;
265 ELSE
266 d_progress := 160;
267 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269 x_msg_data := 'Not a Agreement';
270 END IF;
271 d_progress := 170;
272 EXCEPTION
273 WHEN OTHERS THEN
274 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_MAX_ORD_AMT_WITIN_SUM_AMT' , d_progress);
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 END CLM_MAX_ORD_AMT_WITIN_SUM_AMT;
277
278 /* The below procedure checks
279 * Orders should not be created for the given IDV line within the specified Order Start & End Date
280 */
281 PROCEDURE CLM_ORD_DT_WITIN_ST_END_DT
282 (
283 p_document_id IN NUMBER
284 ,p_draft_id IN NUMBER
285 ,p_online_report_id IN NUMBER
286 ,p_login_id IN NUMBER
287 ,p_user_id IN NUMBER
288 ,p_sequence IN NUMBER
289 ,x_return_status OUT NOCOPY VARCHAR2
290 ,x_errorcode OUT NOCOPY NUMBER
291 ,x_msg_count OUT NOCOPY NUMBER
292 ,x_msg_data OUT NOCOPY VARCHAR2
293 )
294 IS
295 CURSOR c_get_po_lines(p_document_id NUMBER) IS
296 SELECT FROM_LINE_ID, LINE_NUM
297 FROM po_lines_gt
298 WHERE PO_HEADER_ID = p_document_id
299 AND CLM_INFO_FLAG <> 'Y';
300
301 l_ord_st_dt DATE;
302 l_ord_end_dt DATE;
303 l_effective_date DATE;
304 l_document_type_code VARCHAR2(100);
305 l_from_header_id NUMBER;
306
307 d_progress NUMBER := 0;
308 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
309 l_api_name CONSTANT VARCHAR2(100) := 'CLM_ORD_DT_WITIN_ST_END_DT';
310
311
312 BEGIN
313 d_progress := 10;
314 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_WITIN_ST_END_DT with parameters' , d_progress);
315 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
316 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
317
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319 BEGIN
320 d_progress := 20;
321 SELECT TYPE_LOOKUP_CODE
322 INTO l_document_type_code
323 FROM po_headers_gt
324 WHERE PO_HEADER_ID = p_document_id;
325
326 d_progress := 30;
327 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
328 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_from_header_id , d_progress);
329 EXCEPTION
330 WHEN OTHERS THEN
331 d_progress := 40;
332 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, FROM_HEADER_ID' , d_progress);
333 RAISE;
334 END;
335
336 d_progress := 50;
337 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
338 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
339 IF c_get_po_lines_rec.FROM_LINE_ID IS NOT NULL THEN
340
341 d_progress := 60;
342 BEGIN
343 PO_UDA_DEFAULTING_PKG.DEBUG('FROM_LINE_ID :' || c_get_po_lines_rec.FROM_LINE_ID , d_progress);
344 SELECT CLM_ORDER_START_DATE,
345 CLM_ORDER_END_DATE
346 INTO l_ord_st_dt,
347 l_ord_end_dt
348 FROM PO_LINES_ALL
349 WHERE PO_LINE_ID = c_get_po_lines_rec.FROM_LINE_ID;
350
351 SELECT CLM_EFFECTIVE_DATE
352 INTO l_effective_date
353 FROM po_headers_gt
354 WHERE PO_HEADER_ID = p_document_id;
355
356 d_progress := 70;
357 PO_UDA_DEFAULTING_PKG.DEBUG('l_ord_st_dt :' || l_ord_st_dt , d_progress);
358 PO_UDA_DEFAULTING_PKG.DEBUG('l_ord_end_dt :' || l_ord_end_dt , d_progress);
359 EXCEPTION
360 WHEN OTHERS THEN
361 d_progress := 80;
362 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_ORDER_START_DATE, CLM_ORDER_END_DATE' , d_progress);
363 RAISE;
364 END;
365
366 d_progress := 90;
367 IF l_ord_st_dt IS NULL OR l_ord_end_dt IS NULL THEN
368 d_progress := 95;
369 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_DT_WITIN_ST_END_DT' , d_progress);
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371 ELSIF l_effective_date IS NULL THEN
372 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORD_DT_WITIN_ST_END_DT' , d_progress);
373 x_return_status := FND_API.G_RET_STS_ERROR;
374
375 LOG_ERR_CLM_NMD_COLS_SUB_CHK
376 (
377 p_online_report_id => p_online_report_id
378 ,p_login_id => p_login_id
379 ,p_user_id => p_user_id
380 ,p_line_num => 0
381 ,p_sequence => p_sequence
382 ,p_return_message => 'PO_CLM_ORD_DT_IN_ST_END_DT'
383 );
384
385 ELSIF l_effective_date BETWEEN l_ord_st_dt AND l_ord_end_dt THEN
386 d_progress := 100;
387 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_DT_WITIN_ST_END_DT' , d_progress);
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389 ELSE
390 d_progress := 110;
391 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORD_DT_WITIN_ST_END_DT' , d_progress);
392 x_return_status := FND_API.G_RET_STS_ERROR;
393
394 LOG_ERR_CLM_NMD_COLS_SUB_CHK
395 (
396 p_online_report_id => p_online_report_id
397 ,p_login_id => p_login_id
398 ,p_user_id => p_user_id
399 ,p_line_num => 0
400 ,p_sequence => p_sequence
401 ,p_return_message => 'PO_CLM_ORD_DT_IN_ST_END_DT'
402 );
403 END IF;
404 d_progress := 120;
405 END IF;
406 END LOOP;
407 d_progress := 130;
408 ELSE
409 d_progress := 140;
410 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
411 x_return_status := FND_API.G_RET_STS_SUCCESS;
412 x_msg_data := 'Not a Standard PO';
413 END IF;
414 d_progress := 150;
415 EXCEPTION
416 WHEN OTHERS THEN
417 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_ORD_DT_WITIN_ST_END_DT' , d_progress);
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 END CLM_ORD_DT_WITIN_ST_END_DT;
420
421 /* The below procedure checks
422 * The Order Start and End Date at the line level must be within the order period defined at the header level.
423 */
424 PROCEDURE CLM_ORD_DT_LINE_WITIN_DT_HEAD
425 (
426 p_document_id IN NUMBER
427 ,p_draft_id IN NUMBER
428 ,p_online_report_id IN NUMBER
429 ,p_login_id IN NUMBER
430 ,p_user_id IN NUMBER
431 ,p_sequence IN NUMBER
432 ,x_return_status OUT NOCOPY VARCHAR2
433 ,x_errorcode OUT NOCOPY NUMBER
434 ,x_msg_count OUT NOCOPY NUMBER
435 ,x_msg_data OUT NOCOPY VARCHAR2
436 )
437 IS
438 l_line_min_start_dt DATE;
439 l_line_max_end_dt DATE;
440 l_head_st_dt DATE;
441 l_head_end_dt DATE;
442 l_document_type_code VARCHAR2(100);
443
444 d_progress NUMBER := 0;
445 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
446 l_api_name CONSTANT VARCHAR2(100) := 'CLM_ORD_DT_LINE_WITIN_DT_HEAD';
447
448 CURSOR c_get_po_lines(p_document_id NUMBER) IS
449 SELECT CLM_ORDER_START_DATE, CLM_ORDER_END_DATE, LINE_NUM
450 FROM po_lines_gt
451 WHERE PO_HEADER_ID = p_document_id;
452
453 BEGIN
454 d_progress := 10;
455 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_LINE_WITIN_DT_HEAD with parameters' , d_progress);
456 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
457 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
458
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460 BEGIN
461 d_progress := 20;
462 SELECT TYPE_LOOKUP_CODE
463 INTO l_document_type_code
464 FROM po_headers_gt
465 WHERE PO_HEADER_ID = p_document_id;
466
467 d_progress := 30;
468 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
469 EXCEPTION
470 WHEN OTHERS THEN
471 d_progress := 40;
472 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE' , d_progress);
473 RAISE;
474 END;
475
476 d_progress := 50;
477 IF l_document_type_code = 'BLANKET' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
478 d_progress := 60;
479 BEGIN
480
481 SELECT START_DATE, END_DATE
482 INTO l_head_st_dt, l_head_end_dt
483 FROM po_headers_gt
484 WHERE PO_HEADER_ID = p_document_id;
485
486 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
487
488 l_line_min_start_dt := c_get_po_lines_rec.CLM_ORDER_START_DATE;
489 l_line_max_end_dt := c_get_po_lines_rec.CLM_ORDER_END_DATE;
490
491 IF l_line_min_start_dt IS NULL
492 OR l_line_max_end_dt IS NULL
493 OR l_head_st_dt IS NULL
494 OR l_head_end_dt IS NULL THEN
495 d_progress := 160;
496 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_DT_LINE_WITIN_DT_HEAD' , d_progress);
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498 ELSIF l_line_min_start_dt < l_head_st_dt OR l_line_max_end_dt > l_head_end_dt THEN
499 d_progress := 150;
500 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORD_DT_LINE_WITIN_DT_HEAD' , d_progress);
501 x_return_status := FND_API.G_RET_STS_ERROR;
502
503 LOG_ERR_CLM_NMD_COLS_SUB_CHK
504 (
505 p_online_report_id => p_online_report_id
506 ,p_login_id => p_login_id
507 ,p_user_id => p_user_id
508 ,p_line_num => c_get_po_lines_rec.LINE_NUM
509 ,p_sequence => p_sequence
510 ,p_return_message => 'PO_CLM_ORD_DT_LINE_IN_DT_HEAD'
511 );
512 ELSE
513 d_progress := 160;
514 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_DT_LINE_WITIN_DT_HEAD' , d_progress);
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516 END IF;
517 d_progress := 170;
518
519 END LOOP;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 d_progress := 80;
524 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_ORDER_START_DATE, CLM_ORDER_END_DATE' , d_progress);
525 RAISE;
526 END;
527 d_progress := 140;
528 ELSE
529 d_progress := 180;
530 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532 x_msg_data := 'Not a Blanket';
533 END IF;
534 d_progress := 190;
535 EXCEPTION
536 WHEN OTHERS THEN
537 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_ORD_DT_LINE_WITIN_DT_HEAD' , d_progress);
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 END CLM_ORD_DT_LINE_WITIN_DT_HEAD;
540
541 /* The below procedure checks
542 * Effective Date is a past date or null
543 */
544 PROCEDURE CLM_EFF_DT_HEAD_CHECK
545 (
546 p_draft_id IN NUMBER
547 ,p_document_id IN NUMBER
548 ,p_online_report_id IN NUMBER
549 ,p_login_id IN NUMBER
550 ,p_user_id IN NUMBER
551 ,p_sequence IN NUMBER
552 ,x_return_status OUT NOCOPY VARCHAR2
553 )
554 AS
555
556 d_progress NUMBER := 0;
557 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
558 l_api_name CONSTANT VARCHAR2(100) := 'CLM_EFF_DT_HEAD_CHECK';
559 l_line_num NUMBER := 0;
560 l_ship_num NUMBER := 0;
561 l_document_type_code VARCHAR2(100);
562 l_effect_dt_null_msg VARCHAR2(100);
563
564 BEGIN
565 d_progress := 10;
566 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_EFF_DT_HEAD_CHECK with parameters' , d_progress);
567 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
568
569 BEGIN
570 d_progress := 20;
571 SELECT TYPE_LOOKUP_CODE
572 INTO l_document_type_code
573 FROM po_headers_gt
574 WHERE PO_HEADER_ID = p_document_id;
575
576 d_progress := 30;
577 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
578 EXCEPTION
579 WHEN OTHERS THEN
580 d_progress := 40;
581 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE' , d_progress);
582 RAISE;
583 END;
584
585 IF l_document_type_code = 'BLANKET' OR l_document_type_code = 'CONTRACT' THEN
586 l_effect_dt_null_msg := 'PO_EFFECT_DT_NULL_IDV';
587 ELSIF l_document_type_code = 'STANDARD' THEN
588 l_effect_dt_null_msg := 'PO_EFFECT_DT_NULL_PAST';
589 END IF;
590
591 -- 16720438 - There should not be any validation if Effecctive date is given. Ref Bug: 13970257
592 -- Check for clm_effective_date being given as NULL
593 INSERT INTO po_online_report_text_gt
594 (
595 online_report_id,
596 last_update_login,
597 last_updated_by,
598 last_update_date,
599 created_by,
600 creation_date,
601 line_num,
602 shipment_num,
603 distribution_num,
604 sequence,
605 text_line,
606 message_name,
607 message_type
608 )
609 SELECT
610 p_online_report_id,
611 p_login_id,
612 p_user_id,
613 SYSDATE,
614 p_user_id,
615 SYSDATE,
616 null,
617 0,
618 0,
619 p_sequence,
620 FND_MESSAGE.GET_STRING('PO',l_effect_dt_null_msg),
621 'Effective date Invalid',
622 'W'
623 FROM PO_HEADERS_GT POH
624 WHERE POH.PO_HEADER_ID = p_document_id
625 AND POH.CLM_EFFECTIVE_DATE IS NULL ;
626
627 IF SQL%ROWCOUNT > 0 THEN
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 ELSE
630 x_return_status := FND_API.G_RET_STS_SUCCESS;
631 END IF ;
632
633 -- Effective date should be less than the need by date
634 -- of all the shipments
635 INSERT INTO po_online_report_text_gt
636 (
637 online_report_id,
638 last_update_login,
639 last_updated_by,
640 last_update_date,
641 created_by,
642 creation_date,
643 line_num,
644 shipment_num,
645 distribution_num,
646 sequence,
647 text_line,
648 message_name,
649 message_type
650 )
651 SELECT
652 p_online_report_id,
653 p_login_id,
654 p_user_id,
655 SYSDATE,
656 p_user_id,
657 SYSDATE,
658 POL.LINE_NUM,
659 PLL.SHIPMENT_NUM,
660 0,
661 p_sequence,
662 FND_MESSAGE.GET_STRING('PO','PO_EFFECT_DT_INVALID') || ' for Line '||POL.LINE_NUM_DISPLAY,
663 'Effective date Invalid',
664 'E'
665 FROM PO_HEADERS_GT POH,PO_LINES_GT POL,PO_LINE_LOCATIONS_GT PLL
666 WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
667 AND POL.PO_LINE_ID=PLL.PO_LINE_ID
668 -- AND POH.draft_id = p_draft_id
669 AND POH.PO_HEADER_ID = p_document_id
670 AND POH.CLM_EFFECTIVE_DATE > Nvl(PLL.CLM_PERIOD_PERF_START_DATE,Nvl(PLL.NEED_BY_DATE, POH.CLM_EFFECTIVE_DATE + 1 ))
671 ORDER BY POL.LINE_NUM;
672
673 IF SQL%ROWCOUNT > 0 THEN
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 ELSE
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677 END IF ;
678
679
680 EXCEPTION
681
682 WHEN No_Data_Found THEN
683 null;
684 WHEN OTHERS THEN
685 d_progress := 40;
686 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_EFF_DT_HEAD_CHECK' , d_progress);
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 END CLM_EFF_DT_HEAD_CHECK;
689
690
691 /* The below procedure checks
692 * The Quantity field should be entered when Min Order Qty, Max Order Qty, Min Total Qty or Max Total Qty is entered
693 */
694 PROCEDURE CLM_QTY_ORD_QTY_CHECK
695 (
696 p_document_id IN NUMBER
697 ,p_draft_id IN NUMBER
698 ,p_online_report_id IN NUMBER
699 ,p_login_id IN NUMBER
700 ,p_user_id IN NUMBER
701 ,p_sequence IN NUMBER
702 ,x_return_status OUT NOCOPY VARCHAR2
703 ,x_errorcode OUT NOCOPY NUMBER
704 ,x_msg_count OUT NOCOPY NUMBER
705 ,x_msg_data OUT NOCOPY VARCHAR2
706 )
707 IS
708
709 CURSOR c_get_po_lines(p_document_id NUMBER) IS
710 SELECT CLM_MIN_ORDER_QUANTITY,
711 CLM_MAX_ORDER_QUANTITY,
712 CLM_MAX_TOTAL_QUANTITY,
713 CLM_MIN_TOTAL_QUANTITY,
714 QUANTITY QUANTITY,
715 MATCHING_BASIS, LINE_NUM
716 FROM po_lines_gt
717 WHERE PO_HEADER_ID = p_document_id
718 AND CLM_INFO_FLAG <> 'Y';
719
720 l_document_type_code VARCHAR2(100);
721
722 d_progress NUMBER := 0;
723 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
724 l_api_name CONSTANT VARCHAR2(100) := 'CLM_QTY_ORD_QTY_CHECK';
725
726 BEGIN
727 d_progress := 10;
728 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_QTY_ORD_QTY_CHECK with parameters' , d_progress);
729 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
730 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
731
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733 BEGIN
734 d_progress := 20;
735 SELECT TYPE_LOOKUP_CODE
736 INTO l_document_type_code
737 FROM po_headers_gt
738 WHERE PO_HEADER_ID = p_document_id;
739
740 d_progress := 30;
741 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
742 EXCEPTION
743 WHEN OTHERS THEN
744 d_progress := 40;
745 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE' , d_progress);
746 RAISE;
747 END;
748
749 d_progress := 50;
750 IF l_document_type_code = 'BLANKET' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
751 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
752 d_progress := 60;
753 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MIN_ORDER_QUANTITY :' || c_get_po_lines_rec.CLM_MIN_ORDER_QUANTITY , d_progress);
754 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MAX_ORDER_QUANTITY :' || c_get_po_lines_rec.CLM_MAX_ORDER_QUANTITY , d_progress);
755 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MAX_TOTAL_QUANTITY :' || c_get_po_lines_rec.CLM_MAX_TOTAL_QUANTITY , d_progress);
756 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MIN_TOTAL_QUANTITY :' || c_get_po_lines_rec.CLM_MIN_TOTAL_QUANTITY , d_progress);
757
758 d_progress := 70;
759 IF (c_get_po_lines_rec.CLM_MIN_ORDER_QUANTITY IS NOT NULL
760 OR c_get_po_lines_rec.CLM_MAX_ORDER_QUANTITY IS NOT NULL
761 OR c_get_po_lines_rec.CLM_MAX_TOTAL_QUANTITY IS NOT NULL
762 OR c_get_po_lines_rec.CLM_MIN_TOTAL_QUANTITY IS NOT NULL)
763 AND c_get_po_lines_rec.MATCHING_BASIS = 'QUANTITY'
764 THEN
765 d_progress := 80;
766 IF c_get_po_lines_rec.QUANTITY IS NULL THEN
767 d_progress := 90;
768 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_QTY_ORD_QTY_CHECK' , d_progress);
769 x_return_status := FND_API.G_RET_STS_ERROR;
770
771 LOG_ERR_CLM_NMD_COLS_SUB_CHK
772 (
773 p_online_report_id => p_online_report_id
774 ,p_login_id => p_login_id
775 ,p_user_id => p_user_id
776 ,p_line_num => c_get_po_lines_rec.LINE_NUM
777 ,p_sequence => p_sequence
778 ,p_return_message => 'PO_CLM_QTY_ORD_QTY_CHECK'
779 );
780
781 ELSE
782 d_progress := 100;
783 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_QTY_ORD_QTY_CHECK' , d_progress);
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785 END IF;
786 d_progress := 110;
787 END IF;
788 END LOOP;
789 ELSE
790 d_progress := 120;
791 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793 x_msg_data := 'Not a Blanket';
794 END IF;
795 d_progress := 130;
796 EXCEPTION
797 WHEN OTHERS THEN
798 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_QTY_ORD_QTY_CHECK' , d_progress);
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 END CLM_QTY_ORD_QTY_CHECK;
801
802 /* The below procedure checks
803 * The Amount field should be entered when Min Order Amt, Max Order Amt, Min Total Amt or Max Total Amt is entered
804 */
805 PROCEDURE CLM_AMT_ORD_AMT_CHECK
806 (
807 p_document_id IN NUMBER
808 ,p_draft_id IN NUMBER
809 ,p_online_report_id IN NUMBER
810 ,p_login_id IN NUMBER
811 ,p_user_id IN NUMBER
812 ,p_sequence IN NUMBER
813 ,x_return_status OUT NOCOPY VARCHAR2
814 ,x_errorcode OUT NOCOPY NUMBER
815 ,x_msg_count OUT NOCOPY NUMBER
816 ,x_msg_data OUT NOCOPY VARCHAR2
817 )
818 IS
819
820 CURSOR c_get_po_lines(p_document_id NUMBER) IS
821 SELECT CLM_MIN_ORDER_AMOUNT,
822 CLM_MAX_ORDER_AMOUNT,
823 CLM_MAX_TOTAL_AMOUNT,
824 CLM_MIN_TOTAL_AMOUNT,
825 MATCHING_BASIS,
826 AMOUNT AMOUNT,
827 QUANTITY QUANTITY,
828 NVL(UNIT_PRICE, 0) UNIT_PRICE, LINE_NUM
829 FROM po_lines_gt
830 WHERE PO_HEADER_ID = p_document_id
831 AND CLM_INFO_FLAG <> 'Y';
832
833 l_document_type_code VARCHAR2(100);
834
835 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
836 l_api_name CONSTANT VARCHAR2(100) := 'CLM_AMT_ORD_AMT_CHECK';
837
838 d_progress NUMBER := 0;
839
840 BEGIN
841 d_progress := 10;
842 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_AMT_ORD_AMT_CHECK with parameters' , d_progress);
843 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
844 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
845
846 x_return_status := FND_API.G_RET_STS_SUCCESS;
847 BEGIN
848 d_progress := 20;
849 SELECT TYPE_LOOKUP_CODE
850 INTO l_document_type_code
851 FROM po_headers_gt
852 WHERE PO_HEADER_ID = p_document_id;
853
854 d_progress := 30;
855 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
856 EXCEPTION
857 WHEN OTHERS THEN
858 d_progress := 40;
859 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE' , d_progress);
860 RAISE;
861 END;
862
863 d_progress := 50;
864 IF l_document_type_code = 'BLANKET' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
865 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
866 d_progress := 60;
867 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MIN_ORDER_AMOUNT :' || c_get_po_lines_rec.CLM_MIN_ORDER_AMOUNT , d_progress);
868 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MAX_ORDER_AMOUNT :' || c_get_po_lines_rec.CLM_MAX_ORDER_AMOUNT , d_progress);
869 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MAX_TOTAL_AMOUNT :' || c_get_po_lines_rec.CLM_MAX_TOTAL_AMOUNT , d_progress);
870 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_MIN_TOTAL_AMOUNT :' || c_get_po_lines_rec.CLM_MIN_TOTAL_AMOUNT , d_progress);
871
872 d_progress := 70;
873 IF c_get_po_lines_rec.CLM_MIN_ORDER_AMOUNT IS NOT NULL
874 OR c_get_po_lines_rec.CLM_MAX_ORDER_AMOUNT IS NOT NULL
875 OR c_get_po_lines_rec.CLM_MAX_TOTAL_AMOUNT IS NOT NULL
876 OR c_get_po_lines_rec.CLM_MIN_TOTAL_AMOUNT IS NOT NULL
877 THEN
878 IF c_get_po_lines_rec.MATCHING_BASIS = 'AMOUNT' THEN
879 d_progress := 80;
880 IF c_get_po_lines_rec.AMOUNT IS NULL THEN
881 d_progress := 90;
882 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_AMT_ORD_AMT_CHECK' , d_progress);
883 x_return_status := FND_API.G_RET_STS_ERROR;
884
885 LOG_ERR_CLM_NMD_COLS_SUB_CHK
886 (
887 p_online_report_id => p_online_report_id
888 ,p_login_id => p_login_id
889 ,p_user_id => p_user_id
890 ,p_line_num => c_get_po_lines_rec.LINE_NUM
891 ,p_sequence => p_sequence
892 ,p_return_message => 'PO_CLM_AMT_ORD_AMT_CHECK'
893 );
894 ELSE
895 d_progress := 100;
896 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_AMT_ORD_AMT_CHECK' , d_progress);
897 x_return_status := FND_API.G_RET_STS_SUCCESS;
898 END IF;
899 ELSIF c_get_po_lines_rec.MATCHING_BASIS = 'QUANTITY' THEN
900 d_progress := 110;
901 IF c_get_po_lines_rec.QUANTITY IS NULL THEN
902 d_progress := 120;
903 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_AMT_ORD_AMT_CHECK' , d_progress);
904 x_return_status := FND_API.G_RET_STS_ERROR;
905
906 LOG_ERR_CLM_NMD_COLS_SUB_CHK
907 (
908 p_online_report_id => p_online_report_id
909 ,p_login_id => p_login_id
910 ,p_user_id => p_user_id
911 ,p_line_num => c_get_po_lines_rec.LINE_NUM
912 ,p_sequence => p_sequence
913 ,p_return_message => 'PO_CLM_AMT_ORD_AMT_CHECK'
914 );
915 ELSE
916 d_progress := 130;
917 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_AMT_ORD_AMT_CHECK' , d_progress);
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919 END IF;
920 ELSE
921 d_progress := 140;
922 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_AMT_ORD_AMT_CHECK' , d_progress);
923 x_return_status := FND_API.G_RET_STS_SUCCESS;
924 END IF;
925 d_progress := 150;
926 END IF;
927 END LOOP;
928 ELSE
929 d_progress := 160;
930 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932 x_msg_data := 'Not a Blanket';
933 END IF;
934 d_progress := 170;
935
936 EXCEPTION
937 WHEN OTHERS THEN
938 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_AMT_ORD_AMT_CHECK' , d_progress);
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 END CLM_AMT_ORD_AMT_CHECK;
941
942 /* The below procedure checks
943 * Order line's quantity should be between the defined Minimum & Maximum Per-Order Quantity
944 */
945 PROCEDURE CLM_TOT_QTY_WIT_MAX_MIN_CHECK
946 (
947 p_document_id IN NUMBER
948 ,p_draft_id IN NUMBER
949 ,p_online_report_id IN NUMBER
950 ,p_login_id IN NUMBER
951 ,p_user_id IN NUMBER
952 ,p_sequence IN NUMBER
953 ,x_return_status OUT NOCOPY VARCHAR2
954 ,x_errorcode OUT NOCOPY NUMBER
955 ,x_msg_count OUT NOCOPY NUMBER
956 ,x_msg_data OUT NOCOPY VARCHAR2
957 )
958 IS
959
960 d_progress NUMBER := 0;
961 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
962 l_api_name CONSTANT VARCHAR2(100) := 'CLM_TOT_QTY_WIT_MAX_MIN_CHECK';
963
964 l_idv_min_ord_qty NUMBER;
965 l_idv_max_ord_qty NUMBER;
966 l_document_type_code VARCHAR2(100);
967 l_from_header_id NUMBER;
968 l_line_num NUMBER;
969
970 CURSOR c_get_po_lines(p_document_id NUMBER) IS
971 SELECT NVL(SUM(QUANTITY), 0) TOTAL_QTY,
972 FROM_HEADER_ID IDV_HEADER_ID,
973 FROM_LINE_ID IDV_LINE_ID,
974 MATCHING_BASIS
975 FROM po_lines_gt
976 WHERE PO_HEADER_ID = p_document_id
977 AND CLM_INFO_FLAG <> 'Y'
978 AND MATCHING_BASIS = 'QUANTITY'
979 GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;
980
981 BEGIN
982 d_progress := 10;
983 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_TOT_QTY_WIT_MAX_MIN_CHECK with parameters' , d_progress);
984 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
985 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987
988 d_progress := 20;
989 BEGIN
990 SELECT TYPE_LOOKUP_CODE
991 INTO l_document_type_code
992 FROM po_headers_gt
993 WHERE PO_HEADER_ID = p_document_id;
994
995 d_progress := 30;
996 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
997 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_from_header_id , d_progress);
998 EXCEPTION
999 WHEN OTHERS THEN
1000 d_progress := 40;
1001 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, FROM_HEADER_ID with PO_HEADER_ID (' || p_document_id || ') and DRAFT_ID (' || p_draft_id || ')' , d_progress);
1002 RAISE;
1003 END;
1004
1005 d_progress := 50;
1006 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1007 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
1008 IF c_get_po_lines_rec.IDV_LINE_ID IS NOT NULL THEN
1009 d_progress := 60;
1010 BEGIN
1011 SELECT NVL(CLM_MIN_ORDER_QUANTITY, c_get_po_lines_rec.TOTAL_QTY - 1), NVL(CLM_MAX_ORDER_QUANTITY, c_get_po_lines_rec.TOTAL_QTY + 1)
1012 INTO l_idv_min_ord_qty, l_idv_max_ord_qty
1013 FROM PO_LINES_ALL
1014 WHERE PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1015 AND PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
1016
1017 d_progress := 70;
1018 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_min_ord_qty :' || l_idv_min_ord_qty , d_progress);
1019 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_max_ord_qty :' || l_idv_max_ord_qty , d_progress);
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 d_progress := 80;
1023 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_MIN_ORDER_QUANTITY, CLM_MAX_ORDER_QUANTITY ', d_progress);
1024 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || c_get_po_lines_rec.IDV_HEADER_ID || ') and PO_LINE_ID (' || c_get_po_lines_rec.IDV_LINE_ID || ')' , d_progress);
1025 RAISE;
1026 END;
1027 d_progress := 90;
1028
1029 IF c_get_po_lines_rec.TOTAL_QTY BETWEEN l_idv_min_ord_qty AND l_idv_max_ord_qty THEN
1030 d_progress := 100;
1031 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_TOT_QTY_WIT_MAX_MIN_CHECK' , d_progress);
1032 x_return_status := FND_API.G_RET_STS_SUCCESS;
1033 ELSE
1034 d_progress := 110;
1035 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_TOT_QTY_WIT_MAX_MIN_CHECK' , d_progress);
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037
1038 SELECT MIN(LINE_NUM)
1039 INTO l_line_num
1040 FROM po_lines_gt
1041 WHERE PO_HEADER_ID = p_document_id
1042 AND CLM_INFO_FLAG <> 'Y'
1043 AND MATCHING_BASIS = 'QUANTITY'
1044 AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1045 AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
1046
1047 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1048 (
1049 p_online_report_id => p_online_report_id
1050 ,p_login_id => p_login_id
1051 ,p_user_id => p_user_id
1052 ,p_line_num => l_line_num
1053 ,p_sequence => p_sequence
1054 ,p_return_message => 'PO_CLM_TQTY_IN_MAX_MIN_CHK'
1055 );
1056 END IF;
1057 END IF;
1058 d_progress := 120;
1059 END LOOP;
1060 ELSE
1061 d_progress := 130;
1062 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1063 x_return_status := FND_API.G_RET_STS_SUCCESS;
1064 x_msg_data := 'Not a Blanket';
1065 END IF;
1066 d_progress := 140;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_TOT_QTY_WIT_MAX_MIN_CHECK' , d_progress);
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071 END CLM_TOT_QTY_WIT_MAX_MIN_CHECK;
1072
1073
1074
1075 /* The below procedure checks
1076 * Total quantity of all order lines should not exceed defined Maximum Total Quantity
1077 */
1078 PROCEDURE CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK
1079 (
1080 p_document_id IN NUMBER
1081 ,p_draft_id IN NUMBER
1082 ,p_online_report_id IN NUMBER
1083 ,p_login_id IN NUMBER
1084 ,p_user_id IN NUMBER
1085 ,p_sequence IN NUMBER
1086 ,x_return_status OUT NOCOPY VARCHAR2
1087 ,x_errorcode OUT NOCOPY NUMBER
1088 ,x_msg_count OUT NOCOPY NUMBER
1089 ,x_msg_data OUT NOCOPY VARCHAR2
1090 )
1091 IS
1092
1093 d_progress NUMBER := 0;
1094 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1095 l_api_name CONSTANT VARCHAR2(100) := 'CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK';
1096
1097 l_idv_max_qty NUMBER;
1098 l_document_type_code VARCHAR2(100);
1099 l_from_header_id NUMBER;
1100 l_tot_qty_ordered NUMBER;
1101 l_line_num NUMBER;
1102 l_line_base_qty NUMBER := 0;
1103 l_line_mod_qty NUMBER := 0;
1104 l_diff_qty NUMBER := 0;
1105
1106 CURSOR c_get_po_lines(p_document_id NUMBER) IS
1107 SELECT NVL(SUM(QUANTITY), 0) TOTAL_QTY,
1108 FROM_HEADER_ID IDV_HEADER_ID,
1109 FROM_LINE_ID IDV_LINE_ID,
1110 MATCHING_BASIS
1111 FROM po_lines_gt
1112 WHERE PO_HEADER_ID = p_document_id
1113 AND CLM_INFO_FLAG <> 'Y'
1114 AND MATCHING_BASIS = 'QUANTITY'
1115 GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;
1116
1117 BEGIN
1118 d_progress := 10;
1119 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK with parameters' , d_progress);
1120 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1121 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1122 x_return_status := FND_API.G_RET_STS_SUCCESS;
1123
1124 d_progress := 20;
1125 BEGIN
1126 SELECT TYPE_LOOKUP_CODE
1127 INTO l_document_type_code
1128 FROM po_headers_gt
1129 WHERE PO_HEADER_ID = p_document_id;
1130
1131 d_progress := 30;
1132 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1133 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_from_header_id , d_progress);
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, FROM_HEADER_ID', d_progress);
1137 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || p_document_id || ') and DRAFT_ID (' || p_draft_id || ')' , d_progress);
1138 d_progress := 40;
1139 RAISE;
1140 END;
1141
1142 d_progress := 50;
1143 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1144 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
1145 IF c_get_po_lines_rec.IDV_LINE_ID IS NOT NULL THEN
1146 d_progress := 60;
1147 BEGIN
1148 SELECT CLM_MAX_TOTAL_QUANTITY, CLM_TOTAL_QUANTITY_ORDERED
1149 INTO l_idv_max_qty, l_tot_qty_ordered
1150 FROM PO_LINES_ALL
1151 WHERE PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1152 AND PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
1153
1154 d_progress := 70;
1155 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_max_qty :' || l_idv_max_qty , d_progress);
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 d_progress := 80;
1159 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_MAX_TOTAL_QUANTITY', d_progress);
1160 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || c_get_po_lines_rec.IDV_HEADER_ID || ') and PO_LINE_ID (' || c_get_po_lines_rec.IDV_LINE_ID || ')' , d_progress);
1161 RAISE;
1162 END;
1163
1164 IF po_encumbrance_preprocessing.g_call_from_encumbrance <> 'Y' THEN
1165 BEGIN
1166 SELECT SUM(Nvl(QUANTITY,0))
1167 INTO l_line_base_qty
1168 FROM PO_LINES_MERGE_V
1169 WHERE PO_HEADER_ID = p_document_id
1170 AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1171 AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID
1172 AND DRAFT_ID = -1
1173 GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;
1174 EXCEPTION
1175 WHEN No_Data_Found THEN
1176 l_line_base_qty := 0;
1177 END;
1178
1179 d_progress := 85;
1180
1181
1182 IF p_draft_id <> -1 THEN
1183 BEGIN
1184 SELECT SUM(Nvl(QUANTITY,0))
1185 INTO l_line_mod_qty
1186 FROM PO_LINES_MERGE_V
1187 WHERE PO_HEADER_ID = p_document_id
1188 AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1189 AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID
1190 AND DRAFT_ID = p_draft_id
1191 GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;
1192 EXCEPTION
1193 WHEN No_Data_Found THEN
1194 l_line_mod_qty := 0;
1195 END;
1196 END IF;
1197 END IF;
1198
1199 IF p_draft_id <> -1 THEN
1200 l_diff_qty := l_line_mod_qty - l_line_base_qty;
1201 ELSE
1202 l_diff_qty := l_line_base_qty;
1203 END IF;
1204
1205 d_progress := 90;
1206 IF l_diff_qty + nvl(l_tot_qty_ordered, 0) <= NVL(l_idv_max_qty, l_diff_qty + nvl(l_tot_qty_ordered, 0)) THEN
1207 d_progress := 100;
1208 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK' , d_progress);
1209 x_return_status := FND_API.G_RET_STS_SUCCESS;
1210 ELSE
1211 d_progress := 110;
1212 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK' , d_progress);
1213 x_return_status := FND_API.G_RET_STS_ERROR;
1214
1215 SELECT MIN(LINE_NUM)
1216 INTO l_line_num
1217 FROM po_lines_gt
1218 WHERE PO_HEADER_ID = p_document_id
1219 AND CLM_INFO_FLAG <> 'Y'
1220 AND MATCHING_BASIS = 'QUANTITY'
1221 AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1222 AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
1223
1224 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1225 (
1226 p_online_report_id => p_online_report_id
1227 ,p_login_id => p_login_id
1228 ,p_user_id => p_user_id
1229 ,p_line_num => l_line_num
1230 ,p_sequence => p_sequence
1231 ,p_return_message => 'PO_CLM_ORD_TQTY_LE_MAX_QTY_CHK'
1232 );
1233 END IF;
1234 END IF;
1235 END LOOP;
1236 ELSE
1237 d_progress := 120;
1238 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240 x_msg_data := 'Not a Blanket';
1241 END IF;
1242 d_progress := 130;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK' , d_progress);
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247 END CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK;
1248
1249 /* The below procedure checks
1250 * Total amount of all order lines released against the IDV should not exceed the Maximum Total Amount
1251 */
1252 PROCEDURE CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK
1253 (
1254 p_document_id IN NUMBER
1255 ,p_draft_id IN NUMBER
1256 ,p_online_report_id IN NUMBER
1257 ,p_login_id IN NUMBER
1258 ,p_user_id IN NUMBER
1259 ,p_sequence IN NUMBER
1260 ,x_return_status OUT NOCOPY VARCHAR2
1261 ,x_errorcode OUT NOCOPY NUMBER
1262 ,x_msg_count OUT NOCOPY NUMBER
1263 ,x_msg_data OUT NOCOPY VARCHAR2
1264 )
1265 IS
1266
1267 d_progress NUMBER := 0;
1268 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1269 l_api_name CONSTANT VARCHAR2(100) := 'CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK';
1270
1271 l_idv_min_ord_amt NUMBER;
1272 l_idv_max_ord_amt NUMBER;
1273 l_ord_type VARCHAR2(200);
1274 l_document_type_code VARCHAR2(100);
1275 l_total_amt NUMBER := 0;
1276 l_from_header_id NUMBER;
1277 l_line_base_amt NUMBER := 0;
1278 l_line_mod_amt NUMBER := 0;
1279 l_diff_amt NUMBER := 0;
1280
1281 CURSOR c_get_po_lines(p_document_id NUMBER) IS
1282 SELECT FROM_HEADER_ID IDV_HEADER_ID, PO_LINE_ID ,
1283 FROM_LINE_ID IDV_LINE_ID, LINE_NUM
1284 FROM po_lines_gt
1285 WHERE PO_HEADER_ID = p_document_id
1286 AND CLM_INFO_FLAG <> 'Y';
1287
1288 l_total_line_amt_ordered NUMBER;
1289 l_tot_amt_ordered NUMBER;
1290 l_idv_max_amt NUMBER;
1291
1292 BEGIN
1293 d_progress := 10;
1294 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK with parameters' , d_progress);
1295 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1296 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1297 x_return_status := FND_API.G_RET_STS_SUCCESS;
1298
1299 d_progress := 20;
1300 BEGIN
1301 SELECT TYPE_LOOKUP_CODE
1302 INTO l_document_type_code
1303 FROM po_headers_gt
1304 WHERE PO_HEADER_ID = p_document_id;
1305
1306 d_progress := 30;
1307 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1308 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_from_header_id , d_progress);
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, FROM_HEADER_ID', d_progress);
1312 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || p_document_id || ') and DRAFT_ID (' || p_draft_id || ')' , d_progress);
1313 d_progress := 40;
1314 RAISE;
1315 END;
1316
1317 d_progress := 50;
1318 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1319 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
1320 IF c_get_po_lines_rec.IDV_LINE_ID IS NOT NULL THEN
1321 d_progress := 60;
1322 BEGIN
1323 SELECT CLM_MAX_TOTAL_AMOUNT, NVL(CLM_TOTAL_AMOUNT_ORDERED, 0)
1324 INTO l_idv_max_amt, l_tot_amt_ordered
1325 FROM PO_LINES_ALL
1326 WHERE PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
1327 AND PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;
1328
1329 d_progress := 70;
1330 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_min_ord_amt :' || l_idv_min_ord_amt , d_progress);
1331 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_max_ord_amt :' || l_idv_max_ord_amt , d_progress);
1332 PO_UDA_DEFAULTING_PKG.DEBUG('l_ord_type :' || l_ord_type , d_progress);
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 d_progress := 80;
1336 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch CLM_MAX_TOTAL_AMOUNT, CLM_TOTAL_AMOUNT_ORDERED', d_progress);
1337 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || c_get_po_lines_rec.IDV_HEADER_ID || ') and PO_LINE_ID (' || c_get_po_lines_rec.IDV_LINE_ID || ')' , d_progress);
1338 RAISE;
1339 END;
1340
1341 IF po_encumbrance_preprocessing.g_call_from_encumbrance <> 'Y' THEN
1342 l_line_base_amt := getAmountOrdered(p_header_id => p_document_id
1343 , p_idv_header_id => c_get_po_lines_rec.IDV_HEADER_ID
1344 , p_idv_line_id => c_get_po_lines_rec.IDV_LINE_ID
1345 , p_draft_id => -1
1346 , p_level => 'LINE');
1347 d_progress := 90;
1348
1349 IF p_draft_id <> -1 THEN
1350 l_line_mod_amt := getAmountOrdered(p_header_id => p_document_id
1351 , p_idv_header_id => c_get_po_lines_rec.IDV_HEADER_ID
1352 , p_idv_line_id => c_get_po_lines_rec.IDV_LINE_ID
1353 , p_draft_id => p_draft_id
1354 , p_level => 'LINE');
1355 END IF;
1356 END IF;
1357
1358 d_progress := 100;
1359
1360 IF p_draft_id <> -1 THEN
1361 l_diff_amt := l_line_mod_amt - l_line_base_amt;
1362 ELSE
1363 l_diff_amt := l_line_base_amt;
1364 END IF;
1365 d_progress := 130;
1366
1367 IF l_diff_amt + l_tot_amt_ordered <= NVL(l_idv_max_amt, l_diff_amt + l_tot_amt_ordered) THEN
1368 d_progress := 140;
1369 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK' , d_progress);
1370 x_return_status := FND_API.G_RET_STS_SUCCESS;
1371 ELSE
1372 d_progress := 150;
1373 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK' , d_progress);
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375
1376 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1377 (
1378 p_online_report_id => p_online_report_id
1379 ,p_login_id => p_login_id
1380 ,p_user_id => p_user_id
1381 ,p_line_num => c_get_po_lines_rec.LINE_NUM
1382 ,p_sequence => p_sequence
1383 ,p_return_message => 'PO_CLM_ORD_TAMT_LE_MAX_AMT_CHK'
1384 );
1385
1386 END IF;
1387 END IF;
1388 END LOOP;
1389 ELSE
1390 d_progress := 160;
1391 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393 x_msg_data := 'Not a Blanket';
1394 END IF;
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK' , d_progress);
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399 END CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK;
1400
1401 /* The below procedure checks
1402 * The IDV Amount limit >= Total of all order line amount
1403 */
1404 PROCEDURE CLM_AMT_GE_LINE_AMT
1405 (
1406 p_document_id IN NUMBER
1407 ,p_draft_id IN NUMBER
1408 ,p_online_report_id IN NUMBER
1409 ,p_login_id IN NUMBER
1410 ,p_user_id IN NUMBER
1411 ,p_sequence IN NUMBER
1412 ,x_return_status OUT NOCOPY VARCHAR2
1413 ,x_errorcode OUT NOCOPY NUMBER
1414 ,x_msg_count OUT NOCOPY NUMBER
1415 ,x_msg_data OUT NOCOPY VARCHAR2
1416 )
1417 IS
1418 d_progress NUMBER := 0;
1419 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1420 l_api_name CONSTANT VARCHAR2(100) := 'CLM_AMT_GE_LINE_AMT';
1421 l_document_type_code VARCHAR2(100);
1422 l_idv_header_id NUMBER;
1423 l_clm_amt_released NUMBER := 0;
1424 l_amount_limit NUMBER := 0;
1425 l_base_amt NUMBER := 0;
1426 l_mod_amt NUMBER := 0;
1427 l_diff_amt NUMBER :=0;
1428
1429 BEGIN
1430 d_progress := 10;
1431 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_AMT_GE_LINE_AMT with parameters' , d_progress);
1432 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1433 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1434 x_return_status := FND_API.G_RET_STS_SUCCESS;
1435
1436 d_progress := 20;
1437 BEGIN
1438 SELECT TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
1439 INTO l_document_type_code, l_idv_header_id
1440 FROM po_headers_gt
1441 WHERE PO_HEADER_ID = p_document_id;
1442
1443 d_progress := 30;
1444 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1445 PO_UDA_DEFAULTING_PKG.DEBUG('l_from_header_id :' || l_idv_header_id , d_progress);
1446 EXCEPTION
1447 WHEN OTHERS THEN
1448 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID', d_progress);
1449 d_progress := 40;
1450 RAISE;
1451 END;
1452
1453 d_progress := 50;
1454 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1455
1456 SELECT NVL(clm_amount_released, 0), amount_limit
1457 INTO l_clm_amt_released, l_amount_limit
1458 FROM PO_HEADERS_ALL
1459 WHERE PO_HEADER_ID = l_idv_header_id;
1460
1461 d_progress := 70;
1462 IF po_encumbrance_preprocessing.g_call_from_encumbrance <> 'Y' THEN
1463 l_base_amt := getAmountOrdered(p_header_id => p_document_id
1464 , p_idv_header_id => l_idv_header_id
1465 , p_draft_id => -1
1466 , p_level => 'HEADER');
1467
1468 d_progress := 80;
1469
1470 IF p_draft_id <> -1 THEN
1471 l_mod_amt := getAmountOrdered(p_header_id => p_document_id
1472 , p_idv_header_id => l_idv_header_id
1473 , p_draft_id => p_draft_id
1474 , p_level => 'HEADER');
1475 END IF;
1476 END IF;
1477 d_progress := 90;
1478
1479 IF p_draft_id <> -1 THEN
1480 l_diff_amt := l_mod_amt - l_base_amt;
1481 ELSE
1482 l_diff_amt := l_base_amt;
1483 END IF;
1484
1485 d_progress := 100;
1486
1487 IF l_amount_limit IS NOT NULL THEN
1488 IF l_amount_limit >= l_diff_amt + l_clm_amt_released THEN
1489 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : CLM_AMT_GE_LINE_AMT' , d_progress);
1490 x_return_status := FND_API.G_RET_STS_SUCCESS;
1491 ELSE
1492 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : CLM_AMT_GE_LINE_AMT' , d_progress);
1493 x_return_status := FND_API.G_RET_STS_ERROR;
1494 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1495 (
1496 p_online_report_id => p_online_report_id
1497 ,p_login_id => p_login_id
1498 ,p_user_id => p_user_id
1499 ,p_line_num => 0
1500 ,p_sequence => p_sequence
1501 ,p_return_message => 'PO_CLM_AMT_GE_LINE_AMT'
1502 );
1503 END IF;
1504 END IF;
1505 ELSE
1506 d_progress := 160;
1507 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1508 x_return_status := FND_API.G_RET_STS_SUCCESS;
1509 x_msg_data := 'Not a PO';
1510 END IF;
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_AMT_GE_LINE_AMT' , d_progress);
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1515 END CLM_AMT_GE_LINE_AMT;
1516
1517
1518 /* The below procedure checks
1519 * The Total of all order line amount should be withing the min max order amount on the IDV header
1520 */
1521 PROCEDURE CLM_AMT_WITHIN_MAX_MIN_ORD_AMT
1522 (
1523 p_document_id IN NUMBER
1524 ,p_draft_id IN NUMBER
1525 ,p_online_report_id IN NUMBER
1526 ,p_login_id IN NUMBER
1527 ,p_user_id IN NUMBER
1528 ,p_sequence IN NUMBER
1529 ,x_return_status OUT NOCOPY VARCHAR2
1530 ,x_errorcode OUT NOCOPY NUMBER
1531 ,x_msg_count OUT NOCOPY NUMBER
1532 ,x_msg_data OUT NOCOPY VARCHAR2
1533 )
1534 IS
1535 d_progress NUMBER := 0;
1536 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1537 l_api_name CONSTANT VARCHAR2(100) := 'CLM_AMT_WITHIN_MAX_MIN_ORD_AMT';
1538 l_document_type_code VARCHAR2(100);
1539 l_idv_header_id NUMBER;
1540 l_total_amt NUMBER := 0;
1541 l_max_ord_amt NUMBER := 0;
1542 l_min_ord_amt NUMBER := 0;
1543 BEGIN
1544 d_progress := 10;
1545 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_AMT_WITHIN_MAX_MIN_ORD_AMT with parameters' , d_progress);
1546 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1547 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1548 x_return_status := FND_API.G_RET_STS_SUCCESS;
1549
1550 d_progress := 20;
1551 BEGIN
1552 SELECT TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
1553 INTO l_document_type_code, l_idv_header_id
1554 FROM po_headers_gt
1555 WHERE PO_HEADER_ID = p_document_id;
1556
1557 d_progress := 30;
1558 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1559 PO_UDA_DEFAULTING_PKG.DEBUG('l_idv_header_id :' || l_idv_header_id , d_progress);
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID', d_progress);
1563 d_progress := 40;
1564 RAISE;
1565 END;
1566
1567 d_progress := 50;
1568 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1569 l_total_amt := getAmountOrderedForIDV
1570 (
1571 p_level => 'HEADER'
1572 ,p_doc_level_id => p_document_id
1573 ,p_idv_header_id => l_idv_header_id
1574 );
1575
1576 SELECT clm_max_order_amount, clm_min_order_amount
1577 INTO l_max_ord_amt, l_min_ord_amt
1578 FROM PO_HEADERS_ALL
1579 WHERE PO_HEADER_ID = l_idv_header_id;
1580
1581 /*
1582 Bug 11676275:
1583 Ordering constraint of max_amount_per_order was not working. This was because of following
1584 if asserts whether l_max_ord_amt and l_min_ord_amt should not be NULL. But, one of the values
1585 can be NULL, and in that case constraint validation should be done with respect to that value.
1586 Hence added two ELSIFs after this IF condition.
1587 */
1588 IF l_max_ord_amt IS NOT NULL AND l_min_ord_amt IS NOT NULL THEN
1589 IF l_total_amt BETWEEN l_min_ord_amt AND l_max_ord_amt THEN
1590 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1591 x_return_status := FND_API.G_RET_STS_SUCCESS;
1592 ELSE
1593 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1594 x_return_status := FND_API.G_RET_STS_ERROR;
1595 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1596 (
1597 p_online_report_id => p_online_report_id
1598 ,p_login_id => p_login_id
1599 ,p_user_id => p_user_id
1600 ,p_line_num => 0
1601 ,p_sequence => p_sequence
1602 ,p_return_message => 'PO_CLM_AMT_IN_MAX_MIN_ORD_AMT'
1603 );
1604 END IF;
1605 ELSIF l_max_ord_amt IS NOT NULL THEN
1606 IF l_total_amt <= l_max_ord_amt THEN
1607 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1608 x_return_status := FND_API.G_RET_STS_SUCCESS;
1609 ELSE
1610 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1611 x_return_status := FND_API.G_RET_STS_ERROR;
1612 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1613 (
1614 p_online_report_id => p_online_report_id
1615 ,p_login_id => p_login_id
1616 ,p_user_id => p_user_id
1617 ,p_line_num => 0
1618 ,p_sequence => p_sequence
1619 ,p_return_message => 'PO_CLM_AMT_IN_MAX_MIN_ORD_AMT'
1620 );
1621 END IF;
1622 ELSIF l_min_ord_amt IS NOT NULL THEN
1623 IF l_total_amt >= l_min_ord_amt THEN
1624 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Passed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1625 x_return_status := FND_API.G_RET_STS_SUCCESS;
1626 ELSE
1627 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Failed : PO_CLM_AMT_IN_MAX_MIN_ORD_AMT' , d_progress);
1628 x_return_status := FND_API.G_RET_STS_ERROR;
1629 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1630 (
1631 p_online_report_id => p_online_report_id
1632 ,p_login_id => p_login_id
1633 ,p_user_id => p_user_id
1634 ,p_line_num => 0
1635 ,p_sequence => p_sequence
1636 ,p_return_message => 'PO_CLM_AMT_IN_MAX_MIN_ORD_AMT'
1637 );
1638 END IF;
1639 END IF;
1640 ELSE
1641 d_progress := 160;
1642 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1643 x_return_status := FND_API.G_RET_STS_SUCCESS;
1644 x_msg_data := 'Not a PO';
1645 END IF;
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_AMT_WITHIN_MAX_MIN_ORD_AMT' , d_progress);
1649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1650 END CLM_AMT_WITHIN_MAX_MIN_ORD_AMT;
1651
1652 /* The below procedure checks
1653 * The Source document should be same for the Header and all the Lines
1654 */
1655 PROCEDURE CLM_SRC_DOC_CHK
1656 (
1657 p_document_id IN NUMBER
1658 ,p_draft_id IN NUMBER
1659 ,p_online_report_id IN NUMBER
1660 ,p_login_id IN NUMBER
1661 ,p_user_id IN NUMBER
1662 ,p_sequence IN NUMBER
1663 ,x_return_status OUT NOCOPY VARCHAR2
1664 ,x_errorcode OUT NOCOPY NUMBER
1665 ,x_msg_count OUT NOCOPY NUMBER
1666 ,x_msg_data OUT NOCOPY VARCHAR2
1667 )
1668 IS
1669 d_progress NUMBER := 0;
1670 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1671 l_api_name CONSTANT VARCHAR2(100) := 'CLM_SRC_DOC_CHK';
1672
1673 l_document_type_code VARCHAR2(100);
1674 l_count NUMBER;
1675 l_source_id NUMBER;
1676 l_doc_type VARCHAR2(100);
1677
1678 BEGIN
1679 d_progress := 10;
1680 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_CHK with parameters' , d_progress);
1681 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1682 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1683 x_return_status := FND_API.G_RET_STS_SUCCESS;
1684 d_progress := 20;
1685
1686 BEGIN
1687 SELECT TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
1688 INTO l_document_type_code, l_source_id
1689 FROM po_headers_gt
1690 WHERE PO_HEADER_ID = p_document_id;
1691
1692 d_progress := 30;
1693 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE', d_progress);
1697 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || p_document_id || ') and DRAFT_ID (' || p_draft_id || ')' , d_progress);
1698 d_progress := 40;
1699 RAISE;
1700 END;
1701
1702 d_progress := 50;
1703 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1704 d_progress := 60;
1705 BEGIN
1706
1707 IF l_source_id IS NOT NULL THEN
1708
1709 SELECT TYPE_LOOKUP_CODE
1710 INTO l_doc_type
1711 FROM PO_HEADERS_ALL
1712 WHERE PO_HEADER_ID = l_source_id;
1713
1714 IF l_doc_type = 'BLANKET' THEN
1715
1716 d_progress := 70;
1717 SELECT 1
1718 INTO l_count
1719 FROM po_headers_gt h, po_lines_gt l
1720 WHERE h.PO_HEADER_ID = p_document_id
1721 AND h.CLM_AWARD_TYPE IS NOT NULL
1722 AND h.PO_HEADER_ID = l.PO_HEADER_ID
1723 AND (l.FROM_HEADER_ID IS NOT NULL OR h.CLM_SOURCE_DOCUMENT_ID IS NOT NULL)
1724 AND Nvl(l.FROM_HEADER_ID, -999) <> Nvl(h.CLM_SOURCE_DOCUMENT_ID, -999)
1725 AND ROWNUM = 1;
1726
1727 ELSIF l_doc_type = 'CONTRACT' THEN
1728
1729 d_progress := 70;
1730 SELECT 1
1731 INTO l_count
1732 FROM po_headers_gt h, po_lines_gt l
1733 WHERE h.PO_HEADER_ID = p_document_id
1734 AND h.CLM_AWARD_TYPE IS NOT NULL
1735 AND h.PO_HEADER_ID = l.PO_HEADER_ID
1736 AND (l.CONTRACT_ID IS NOT NULL OR h.CLM_SOURCE_DOCUMENT_ID IS NOT NULL)
1737 AND Nvl(l.CONTRACT_ID, -999) <> Nvl(h.CLM_SOURCE_DOCUMENT_ID, -999)
1738 AND ROWNUM = 1;
1739
1740 END IF;
1741
1742 x_return_status := FND_API.G_RET_STS_ERROR;
1743
1744 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1745 (
1746 p_online_report_id => p_online_report_id
1747 ,p_login_id => p_login_id
1748 ,p_user_id => p_user_id
1749 ,p_line_num => 0
1750 ,p_sequence => p_sequence
1751 ,p_return_message => 'PO_CLM_SRC_DOC_CHK'
1752 );
1753 d_progress := 80;
1754
1755 END IF;
1756
1757 EXCEPTION
1758 WHEN No_Data_Found THEN
1759 d_progress := 90;
1760 x_return_status := FND_API.G_RET_STS_SUCCESS;
1761 x_msg_data := 'Valid Document';
1762 WHEN OTHERS THEN
1763 d_progress := 100;
1764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1765 RAISE;
1766 END;
1767 d_progress := 110;
1768 ELSE
1769 d_progress := 120;
1770 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1771 x_return_status := FND_API.G_RET_STS_SUCCESS;
1772 x_msg_data := 'Not a Blanket';
1773 END IF;
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_SRC_DOC_CHK' , d_progress);
1778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1779 END CLM_SRC_DOC_CHK;
1780
1781 /* The below procedure checks
1782 * If the line is referring a BPA type IDV,
1783 * then source line reference is mandatory
1784 */
1785 PROCEDURE CLM_SRC_DOC_LINE_CHK
1786 (
1787 p_document_id IN NUMBER
1788 ,p_draft_id IN NUMBER
1789 ,p_online_report_id IN NUMBER
1790 ,p_login_id IN NUMBER
1791 ,p_user_id IN NUMBER
1792 ,p_sequence IN NUMBER
1793 ,x_return_status OUT NOCOPY VARCHAR2
1794 ,x_errorcode OUT NOCOPY NUMBER
1795 ,x_msg_count OUT NOCOPY NUMBER
1796 ,x_msg_data OUT NOCOPY VARCHAR2
1797 )
1798 IS
1799 d_progress NUMBER := 0;
1800 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1801 l_api_name CONSTANT VARCHAR2(100) := 'CLM_SRC_DOC_LINE_CHK';
1802
1803 l_document_type_code VARCHAR2(100);
1804 l_count NUMBER;
1805 l_source_id NUMBER;
1806 l_doc_type VARCHAR2(100);
1807
1808 CURSOR c_get_po_lines(p_document_id NUMBER) IS
1809 SELECT LINE_NUM
1810 FROM po_lines_gt l
1811 WHERE l.PO_HEADER_ID = p_document_id
1812 AND l.FROM_LINE_ID IS NULL
1813 and nvl(l.CLM_INFO_FLAG,'N') = 'N' ; --Bug 12669019
1814 /*Bug 12669019 : If the Line is an Info line then The
1815 src_doc_line need not be populated. Submission check for
1816 source_doc_line needs to be done only in case of price lines */
1817
1818
1819 BEGIN
1820 d_progress := 10;
1821 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_LINE_CHK with parameters' , d_progress);
1822 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1823 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1824 x_return_status := FND_API.G_RET_STS_SUCCESS;
1825 d_progress := 20;
1826
1827 BEGIN
1828 SELECT TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
1829 INTO l_document_type_code, l_source_id
1830 FROM po_headers_gt
1831 WHERE PO_HEADER_ID = p_document_id;
1832
1833 d_progress := 30;
1834 PO_UDA_DEFAULTING_PKG.DEBUG('l_document_type_code :' || l_document_type_code , d_progress);
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 PO_UDA_DEFAULTING_PKG.DEBUG('Exception while trying to fetch TYPE_LOOKUP_CODE', d_progress);
1838 PO_UDA_DEFAULTING_PKG.DEBUG('PO_HEADER_ID (' || p_document_id || ') and DRAFT_ID (' || p_draft_id || ')' , d_progress);
1839 d_progress := 40;
1840 RAISE;
1841 END;
1842
1843 d_progress := 50;
1844 IF l_document_type_code = 'STANDARD' AND PO_CLM_CLO_UTIL.isCLMInstalled THEN
1845 d_progress := 60;
1846 BEGIN
1847
1848 IF l_source_id IS NOT NULL THEN
1849
1850 SELECT TYPE_LOOKUP_CODE
1851 INTO l_doc_type
1852 FROM PO_HEADERS_ALL
1853 WHERE PO_HEADER_ID = l_source_id;
1854
1855 IF l_doc_type = 'BLANKET' THEN
1856 FOR c_get_po_lines_rec IN c_get_po_lines(p_document_id) LOOP
1857 x_return_status := FND_API.G_RET_STS_ERROR;
1858 LOG_ERR_CLM_NMD_COLS_SUB_CHK
1859 (
1860 p_online_report_id => p_online_report_id
1861 ,p_login_id => p_login_id
1862 ,p_user_id => p_user_id
1863 ,p_line_num => c_get_po_lines_rec.LINE_NUM
1864 ,p_sequence => p_sequence
1865 ,p_return_message => 'PO_CLM_SRC_DOC_LINE_CHK'
1866 );
1867 d_progress := 80;
1868 END LOOP;
1869 END IF;
1870
1871 END IF;
1872
1873 EXCEPTION
1874 WHEN No_Data_Found THEN
1875 d_progress := 90;
1876 x_return_status := FND_API.G_RET_STS_SUCCESS;
1877 x_msg_data := 'Valid Document';
1878 WHEN OTHERS THEN
1879 d_progress := 100;
1880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1881 RAISE;
1882 END;
1883 d_progress := 110;
1884 ELSE
1885 d_progress := 120;
1886 PO_UDA_DEFAULTING_PKG.DEBUG('Constraint Not Applicable : Not a valid document' , d_progress);
1887 x_return_status := FND_API.G_RET_STS_SUCCESS;
1888 x_msg_data := 'Not a Blanket';
1889 END IF;
1890
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in CLM_SRC_DOC_LINE_CHK' , d_progress);
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1895 END CLM_SRC_DOC_LINE_CHK;
1896
1897 /* The below procedure checks
1898 * Main Procedure to check the IDV constraints
1899 */
1900 PROCEDURE CLM_NAMED_COLS_SUB_CHK
1901 (
1902 p_document_id IN NUMBER
1903 ,p_draft_id IN NUMBER
1904 ,p_online_report_id IN NUMBER
1905 ,p_login_id IN NUMBER
1906 ,p_user_id IN NUMBER
1907 ,p_sequence IN NUMBER
1908 ,x_return_message OUT NOCOPY VARCHAR2
1909 ,x_return_status OUT NOCOPY VARCHAR2
1910 ,x_errorcode OUT NOCOPY NUMBER
1911 ,x_msg_count OUT NOCOPY NUMBER
1912 ,x_msg_data OUT NOCOPY VARCHAR2
1913 )
1914 IS
1915
1916 d_progress NUMBER := 0;
1917 l_log_head CONSTANT VARCHAR2(100) := 'PO_CLM_COLS_SUB_CHECK';
1918 l_api_name CONSTANT VARCHAR2(100) := 'CLM_NAMED_COLS_SUB_CHK';
1919 g_ret_status VARCHAR2(3);
1920
1921 BEGIN
1922 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.CLM_NAMED_COLS_SUB_CHK with parameters' , d_progress);
1923 PO_UDA_DEFAULTING_PKG.DEBUG('p_document_id :' || p_document_id , d_progress);
1924 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id :' || p_draft_id , d_progress);
1925 PO_UDA_DEFAULTING_PKG.DEBUG('p_online_report_id :' || p_online_report_id , d_progress);
1926 PO_UDA_DEFAULTING_PKG.DEBUG('p_user_id :' || p_user_id , d_progress);
1927 PO_UDA_DEFAULTING_PKG.DEBUG('p_sequence :' || p_sequence , d_progress);
1928
1929 x_return_status := FND_API.G_RET_STS_SUCCESS;
1930 g_ret_status := FND_API.G_RET_STS_SUCCESS;
1931
1932 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_ORDER_AMT_WITHIN_MAX_MIN : ' || x_return_status, d_progress);
1933 PO_CLM_COLS_SUB_CHECK.CLM_ORDER_AMT_WITHIN_MAX_MIN
1934 (
1935 p_document_id => p_document_id
1936 ,p_draft_id => p_draft_id
1937 ,p_online_report_id => p_online_report_id
1938 ,p_login_id => p_login_id
1939 ,p_user_id => p_user_id
1940 ,p_sequence => p_sequence
1941 ,x_return_status => x_return_status
1942 ,x_errorcode => x_errorcode
1943 ,x_msg_count => x_msg_count
1944 ,x_msg_data => x_msg_data
1945 );
1946
1947 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_ORDER_AMT_WITHIN_MAX_MIN : ' || x_return_status, d_progress);
1948 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1949 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1950 g_ret_status := x_return_status;
1951 END IF;
1952 END IF;
1953
1954 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_MAX_ORD_AMT_WITIN_SUM_AMT : ' || x_return_status, d_progress);
1955 PO_CLM_COLS_SUB_CHECK.CLM_MAX_ORD_AMT_WITIN_SUM_AMT
1956 (
1957 p_document_id => p_document_id
1958 ,p_draft_id => p_draft_id
1959 ,p_online_report_id => p_online_report_id
1960 ,p_login_id => p_login_id
1961 ,p_user_id => p_user_id
1962 ,p_sequence => p_sequence
1963 ,x_return_status => x_return_status
1964 ,x_errorcode => x_errorcode
1965 ,x_msg_count => x_msg_count
1966 ,x_msg_data => x_msg_data
1967 );
1968
1969 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_MAX_ORD_AMT_WITIN_SUM_AMT : ' || x_return_status, d_progress);
1970 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1971 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1972 g_ret_status := x_return_status;
1973 END IF;
1974 END IF;
1975
1976 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_WITIN_ST_END_DT : ' || x_return_status, d_progress);
1977 PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_WITIN_ST_END_DT
1978 (
1979 p_document_id => p_document_id
1980 ,p_draft_id => p_draft_id
1981 ,p_online_report_id => p_online_report_id
1982 ,p_login_id => p_login_id
1983 ,p_user_id => p_user_id
1984 ,p_sequence => p_sequence
1985 ,x_return_status => x_return_status
1986 ,x_errorcode => x_errorcode
1987 ,x_msg_count => x_msg_count
1988 ,x_msg_data => x_msg_data
1989 );
1990
1991 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_WITIN_ST_END_DT : ' || x_return_status, d_progress);
1992 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1993 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1994 g_ret_status := x_return_status;
1995 END IF;
1996 END IF;
1997
1998 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_LINE_WITIN_DT_HEAD : ' || x_return_status, d_progress);
1999 PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_LINE_WITIN_DT_HEAD
2000 (
2001 p_document_id => p_document_id
2002 ,p_draft_id => p_draft_id
2003 ,p_online_report_id => p_online_report_id
2004 ,p_login_id => p_login_id
2005 ,p_user_id => p_user_id
2006 ,p_sequence => p_sequence
2007 ,x_return_status => x_return_status
2008 ,x_errorcode => x_errorcode
2009 ,x_msg_count => x_msg_count
2010 ,x_msg_data => x_msg_data
2011 );
2012
2013 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_DT_LINE_WITIN_DT_HEAD : ' || x_return_status, d_progress);
2014 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2015 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2016 g_ret_status := x_return_status;
2017 END IF;
2018 END IF;
2019
2020 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_EFF_DT_HEAD_CHECK : ' || x_return_status, d_progress);
2021
2022 --Bug 13478326 Effective date need not be checked for mods.
2023
2024 IF(p_draft_id = -1 OR p_draft_id IS NULL) THEN
2025
2026 PO_CLM_COLS_SUB_CHECK.CLM_EFF_DT_HEAD_CHECK
2027 (
2028 p_draft_id => p_draft_id
2029 ,p_document_id => p_document_id
2030 ,p_online_report_id => p_online_report_id
2031 ,p_login_id => p_login_id
2032 ,p_user_id => p_user_id
2033 ,p_sequence => p_sequence
2034 ,x_return_status => x_return_status
2035 );
2036
2037 END IF;
2038
2039 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_EFF_DT_HEAD_CHECK : ' || x_return_status, d_progress);
2040 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2041 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2042 g_ret_status := x_return_status;
2043 END IF;
2044 END IF;
2045
2046 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_QTY_ORD_QTY_CHECK : ' || x_return_status, d_progress);
2047 PO_CLM_COLS_SUB_CHECK.CLM_QTY_ORD_QTY_CHECK
2048 (
2049 p_document_id => p_document_id
2050 ,p_draft_id => p_draft_id
2051 ,p_online_report_id => p_online_report_id
2052 ,p_login_id => p_login_id
2053 ,p_user_id => p_user_id
2054 ,p_sequence => p_sequence
2055 ,x_return_status => x_return_status
2056 ,x_errorcode => x_errorcode
2057 ,x_msg_count => x_msg_count
2058 ,x_msg_data => x_msg_data
2059 );
2060
2061 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_QTY_ORD_QTY_CHECK : ' || x_return_status, d_progress);
2062 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2063 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2064 g_ret_status := x_return_status;
2065 END IF;
2066 END IF;
2067
2068 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_ORD_AMT_CHECK : ' || x_return_status, d_progress);
2069 PO_CLM_COLS_SUB_CHECK.CLM_AMT_ORD_AMT_CHECK
2070 (
2071 p_document_id => p_document_id
2072 ,p_draft_id => p_draft_id
2073 ,p_online_report_id => p_online_report_id
2074 ,p_login_id => p_login_id
2075 ,p_user_id => p_user_id
2076 ,p_sequence => p_sequence
2077 ,x_return_status => x_return_status
2078 ,x_errorcode => x_errorcode
2079 ,x_msg_count => x_msg_count
2080 ,x_msg_data => x_msg_data
2081 );
2082
2083 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_ORD_AMT_CHECK : ' || x_return_status, d_progress);
2084 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2085 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2086 g_ret_status := x_return_status;
2087 END IF;
2088 END IF;
2089
2090 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_TOT_QTY_WIT_MAX_MIN_CHECK : ' || x_return_status, d_progress);
2091 PO_CLM_COLS_SUB_CHECK.CLM_TOT_QTY_WIT_MAX_MIN_CHECK
2092 (
2093 p_document_id => p_document_id
2094 ,p_draft_id => p_draft_id
2095 ,p_online_report_id => p_online_report_id
2096 ,p_login_id => p_login_id
2097 ,p_user_id => p_user_id
2098 ,p_sequence => p_sequence
2099 ,x_return_status => x_return_status
2100 ,x_errorcode => x_errorcode
2101 ,x_msg_count => x_msg_count
2102 ,x_msg_data => x_msg_data
2103 );
2104
2105 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_TOT_QTY_WIT_MAX_MIN_CHECK : ' || x_return_status, d_progress);
2106 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2107 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2108 g_ret_status := x_return_status;
2109 END IF;
2110 END IF;
2111
2112
2113 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK : ' || x_return_status, d_progress);
2114 PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK
2115 (
2116 p_document_id => p_document_id
2117 ,p_draft_id => p_draft_id
2118 ,p_online_report_id => p_online_report_id
2119 ,p_login_id => p_login_id
2120 ,p_user_id => p_user_id
2121 ,p_sequence => p_sequence
2122 ,x_return_status => x_return_status
2123 ,x_errorcode => x_errorcode
2124 ,x_msg_count => x_msg_count
2125 ,x_msg_data => x_msg_data
2126 );
2127
2128 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_QTY_LE_MAX_QTY_CHK : ' || x_return_status, d_progress);
2129 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2130 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2131 g_ret_status := x_return_status;
2132 END IF;
2133 END IF;
2134
2135 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK : ' || x_return_status, d_progress);
2136 PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK
2137 (
2138 p_document_id => p_document_id
2139 ,p_draft_id => p_draft_id
2140 ,p_online_report_id => p_online_report_id
2141 ,p_login_id => p_login_id
2142 ,p_user_id => p_user_id
2143 ,p_sequence => p_sequence
2144 ,x_return_status => x_return_status
2145 ,x_errorcode => x_errorcode
2146 ,x_msg_count => x_msg_count
2147 ,x_msg_data => x_msg_data
2148 );
2149
2150 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_ORD_TOT_AMT_LE_MAX_AMT_CHK : ' || x_return_status, d_progress);
2151 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2152 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2153 g_ret_status := x_return_status;
2154 END IF;
2155
2156 END IF;
2157
2158 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_CHK : ' || x_return_status, d_progress);
2159 PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_CHK
2160 (
2161 p_document_id => p_document_id
2162 ,p_draft_id => p_draft_id
2163 ,p_online_report_id => p_online_report_id
2164 ,p_login_id => p_login_id
2165 ,p_user_id => p_user_id
2166 ,p_sequence => p_sequence
2167 ,x_return_status => x_return_status
2168 ,x_errorcode => x_errorcode
2169 ,x_msg_count => x_msg_count
2170 ,x_msg_data => x_msg_data
2171 );
2172
2173 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_CHK : ' || x_return_status, d_progress);
2174 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2175 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2176 g_ret_status := x_return_status;
2177 END IF;
2178 END IF;
2179
2180 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_LINE_CHK : ' || x_return_status, d_progress);
2181 PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_LINE_CHK
2182 (
2183 p_document_id => p_document_id
2184 ,p_draft_id => p_draft_id
2185 ,p_online_report_id => p_online_report_id
2186 ,p_login_id => p_login_id
2187 ,p_user_id => p_user_id
2188 ,p_sequence => p_sequence
2189 ,x_return_status => x_return_status
2190 ,x_errorcode => x_errorcode
2191 ,x_msg_count => x_msg_count
2192 ,x_msg_data => x_msg_data
2193 );
2194
2195 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_SRC_DOC_LINE_CHK : ' || x_return_status, d_progress);
2196 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2198 g_ret_status := x_return_status;
2199 END IF;
2200 END IF;
2201
2202 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_GE_LINE_AMT : ' || x_return_status, d_progress);
2203 PO_CLM_COLS_SUB_CHECK.CLM_AMT_GE_LINE_AMT
2204 (
2205 p_document_id => p_document_id
2206 ,p_draft_id => p_draft_id
2207 ,p_online_report_id => p_online_report_id
2208 ,p_login_id => p_login_id
2209 ,p_user_id => p_user_id
2210 ,p_sequence => p_sequence
2211 ,x_return_status => x_return_status
2212 ,x_errorcode => x_errorcode
2213 ,x_msg_count => x_msg_count
2214 ,x_msg_data => x_msg_data
2215 );
2216
2217 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_GE_LINE_AMT : ' || x_return_status, d_progress);
2218 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2219 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2220 g_ret_status := x_return_status;
2221 END IF;
2222 END IF;
2223
2224 PO_UDA_DEFAULTING_PKG.DEBUG('Before call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_WITHIN_MAX_MIN_ORD_AMT : ' || x_return_status, d_progress);
2225 PO_CLM_COLS_SUB_CHECK.CLM_AMT_WITHIN_MAX_MIN_ORD_AMT
2226 (
2227 p_document_id => p_document_id
2228 ,p_draft_id => p_draft_id
2229 ,p_online_report_id => p_online_report_id
2230 ,p_login_id => p_login_id
2231 ,p_user_id => p_user_id
2232 ,p_sequence => p_sequence
2233 ,x_return_status => x_return_status
2234 ,x_errorcode => x_errorcode
2235 ,x_msg_count => x_msg_count
2236 ,x_msg_data => x_msg_data
2237 );
2238
2239 PO_UDA_DEFAULTING_PKG.DEBUG('After call to PO_CLM_COLS_SUB_CHECK.CLM_AMT_WITHIN_MAX_MIN_ORD_AMT : ' || x_return_status, d_progress);
2240 IF g_ret_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
2241 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2242 g_ret_status := x_return_status;
2243 END IF;
2244 END IF;
2245
2246 x_return_status := g_ret_status;
2247
2248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2249 x_return_status := FND_API.G_RET_STS_ERROR;
2250 END IF;
2251
2252 PO_UDA_DEFAULTING_PKG.DEBUG('CLM_NAMED_COLS_SUB_CHK is completed with : ' || x_return_status, d_progress);
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 x_return_status := FND_API.G_RET_STS_ERROR;
2257 END CLM_NAMED_COLS_SUB_CHK;
2258
2259 /* The below procedure checks
2260 * Procedure to log the submission check errors
2261 */
2262 PROCEDURE LOG_ERR_CLM_NMD_COLS_SUB_CHK
2263 (
2264 p_online_report_id IN NUMBER
2265 ,p_login_id IN NUMBER
2266 ,p_user_id IN NUMBER
2267 ,p_line_num IN NUMBER DEFAULT 0
2268 ,p_sequence IN NUMBER
2269 ,p_return_message IN VARCHAR2
2270 )IS
2271 l_ret_clm_err_msg_txt VARCHAR2(3000);
2272 d_progress NUMBER := 0;
2273 BEGIN
2274 d_progress := 10;
2275 PO_UDA_DEFAULTING_PKG.DEBUG('Starting PO_CLM_COLS_SUB_CHECK.LOG_ERR_CLM_NMD_COLS_SUB_CHK with parameters' , d_progress);
2276 PO_UDA_DEFAULTING_PKG.DEBUG('p_online_report_id :' || p_online_report_id , d_progress);
2277 PO_UDA_DEFAULTING_PKG.DEBUG('p_user_id :' || p_user_id , d_progress);
2278 PO_UDA_DEFAULTING_PKG.DEBUG('p_sequence :' || p_sequence , d_progress);
2279 PO_UDA_DEFAULTING_PKG.DEBUG('p_return_message :' || p_return_message , d_progress);
2280
2281 d_progress := 20;
2282 PO_UDA_DEFAULTING_PKG.DEBUG('Before fetching the message text' , d_progress);
2283 FND_MESSAGE.set_name('PO', p_return_message);
2284 l_ret_clm_err_msg_txt := FND_MESSAGE.GET;
2285 d_progress := 30;
2286 PO_UDA_DEFAULTING_PKG.DEBUG('After fetching the message text :' || l_ret_clm_err_msg_txt, d_progress);
2287
2288 INSERT INTO po_online_report_text_gt
2289 (
2290 online_report_id,
2291 last_update_login,
2292 last_updated_by,
2293 last_update_date,
2294 created_by,
2295 creation_date,
2296 line_num,
2297 shipment_num,
2298 distribution_num,
2299 sequence,
2300 text_line,
2301 message_name
2302 )
2303 VALUES
2304 (
2305 p_online_report_id,
2306 p_login_id,
2307 p_user_id,
2308 SYSDATE,
2309 p_user_id,
2310 SYSDATE,
2311 p_line_num,
2312 0,
2313 0,
2314 p_sequence,
2315 l_ret_clm_err_msg_txt,
2316 p_return_message
2317 );
2318 d_progress := 40;
2319 EXCEPTION
2320 WHEN OTHERS THEN
2321 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in LOG_ERR_CLM_NMD_COLS_SUB_CHK' , d_progress);
2322 RAISE;
2323 END LOG_ERR_CLM_NMD_COLS_SUB_CHK;
2324
2325 -------------------------------------------------------------------------------
2326 --Start of Comments
2327 --Name: getAmountOrderedForIDV
2328 --Function:
2329 -- Calculates the Amount for a given PO with the given IDV ID
2330 --Parameters:
2331 --IN:
2332 -- p_doc_level_id
2333 -- The ID of the PO for which to calculate the amount
2334 -- p_idv_header_id
2335 -- The ID of the IDV for which to PO is refered
2336 --RETURNS
2337 -- NUMBER (The sum of amount of all the lines in the po with the specified
2338 -- IDV reference
2339 --End of Comments
2340 -------------------------------------------------------------------------------
2341
2342 FUNCTION getAmountOrderedForIDV
2343 (
2344 p_level IN VARCHAR2
2345 , p_doc_level_id IN NUMBER
2346 , p_idv_header_id IN NUMBER
2347 , p_idv_line_id IN NUMBER DEFAULT NULL
2348 ) RETURN NUMBER
2349 IS
2350 d_position NUMBER := 0;
2351 l_return_val NUMBER := 0;
2352 l_precision GL_CURRENCIES.precision%TYPE;
2353 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
2354 l_org_id PO_HEADERS_ALL.org_id%type;
2355 l_po_currency PO_HEADERS_ALL.currency_code%type;
2356 l_header_id NUMBER := NULL;
2357
2358 BEGIN
2359
2360 PO_UDA_DEFAULTING_PKG.DEBUG('Startig function getAmountOrderedForIDV with parameters', d_position);
2361 PO_UDA_DEFAULTING_PKG.DEBUG('p_doc_level_id ' || p_doc_level_id, d_position);
2362 PO_UDA_DEFAULTING_PKG.DEBUG('p_idv_header_id ' || p_idv_header_id, d_position);
2363
2364 d_position := 10;
2365
2366 IF p_level = 'LINE' AND p_idv_line_id IS NOT NULL THEN
2367 SELECT pol.po_header_id
2368 INTO l_header_id
2369 FROM po_lines_all pol
2370 WHERE pol.po_line_id = p_doc_level_id;
2371 ELSIF p_level = 'HEADER' THEN
2372 l_header_id := p_doc_level_id;
2373 ELSE
2374 PO_UDA_DEFAULTING_PKG.DEBUG('Incorrect level', d_position);
2375 END IF;
2376
2377 IF l_header_id IS NOT NULL THEN
2378
2379 SELECT poh.currency_code, poh.org_id
2380 INTO l_po_currency, l_org_id
2381 FROM po_headers_gt poh
2382 WHERE poh.po_header_id = l_header_id;
2383
2384 d_position := 20;
2385
2386 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2387
2388 d_position := 30;
2389
2390 PO_CORE_S2.get_currency_info
2391 (
2392 x_currency_code => l_po_currency
2393 , x_precision => l_precision
2394 , x_min_unit => l_mau
2395 );
2396
2397 d_position := 40;
2398
2399 PO_UDA_DEFAULTING_PKG.DEBUG('l_precision ' || l_precision, d_position);
2400 PO_UDA_DEFAULTING_PKG.DEBUG('l_mau ' || l_mau, d_position);
2401
2402 d_position := 50;
2403
2404 BEGIN
2405 IF p_level = 'HEADER' THEN
2406 SELECT SUM
2407 (
2408 DECODE
2409 (
2410 POL.matching_basis
2411 ,'AMOUNT', pol.amount
2412 ,nvl2
2413 (
2414 l_mau
2415 ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
2416 ,round((pol.quantity * pol.unit_price), l_precision)
2417 )
2418 )
2419 )
2420 INTO l_return_val
2421 FROM po_lines_gt pol
2422 WHERE pol.po_header_id = p_doc_level_id
2423 AND (
2424 (pol.from_header_id = p_idv_header_id AND pol.FROM_LINE_ID = NVL(p_idv_line_id, pol.FROM_LINE_ID))
2425 OR
2426 (pol.contract_id = p_idv_header_id)
2427 );
2428 ELSIF p_level = 'LINE' THEN
2429 SELECT DECODE
2430 (
2431 POL.matching_basis
2432 ,'AMOUNT', pol.amount
2433 ,nvl2
2434 (
2435 l_mau
2436 ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
2437 ,round((pol.quantity * pol.unit_price), l_precision)
2438 )
2439 )
2440 INTO l_return_val
2441 FROM po_lines_gt pol
2442 WHERE pol.po_line_id = p_doc_level_id
2443 AND (
2444 (pol.from_header_id = p_idv_header_id AND pol.FROM_LINE_ID = p_idv_line_id)
2445 OR
2446 (pol.contract_id = p_idv_header_id)
2447 );
2448 END IF;
2449 EXCEPTION
2450 WHEN No_Data_Found THEN
2451 RETURN 0;
2452 WHEN OTHERS THEN
2453 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in getAmountOrderedForIDV', d_position);
2454 RETURN 0;
2455 END;
2456
2457 d_position := 60;
2458
2459 PO_UDA_DEFAULTING_PKG.DEBUG('l_return_val ' || l_return_val, d_position);
2460
2461 d_position := 70;
2462
2463 END IF;
2464
2465 RETURN l_return_val;
2466
2467 EXCEPTION
2468 WHEN OTHERS THEN
2469 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in getAmountOrderedForIDV', d_position);
2470 RETURN 0;
2471 END getAmountOrderedForIDV;
2472
2473 ----------------------------------------------------------------------------
2474 --Start of Comments
2475 --Name: getAmountOrdered
2476 --Function:
2477 -- Calculates the Amount on the given IDV Header/Line based on if
2478 -- doc is award or mod
2479 --Parameters:
2480 --IN:
2481 -- p_line_id
2482 -- p_header_id
2483 -- p_idv_header_id
2484 -- p_idv_line_id
2485 -- p_draft_id
2486 --RETURNS
2487 -- NUMBER
2488 --End of Comments
2489 ----------------------------------------------------------------------------
2490
2491 FUNCTION getAmountOrdered
2492 ( p_level IN VARCHAR2
2493 , p_header_id IN NUMBER
2494 , p_idv_header_id IN NUMBER
2495 , p_idv_line_id IN NUMBER DEFAULT NULL
2496 , p_draft_id IN NUMBER
2497 ) RETURN NUMBER
2498 IS
2499 d_position NUMBER := 0;
2500 l_return_val NUMBER := 0;
2501 l_precision GL_CURRENCIES.precision%TYPE;
2502 l_mau GL_CURRENCIES.minimum_accountable_unit%TYPE;
2503 l_org_id PO_HEADERS_ALL.org_id%type;
2504 l_po_currency PO_HEADERS_ALL.currency_code%type;
2505 l_header_id NUMBER := NULL;
2506
2507 BEGIN
2508
2509 PO_UDA_DEFAULTING_PKG.DEBUG('Startig function getAmountOrdered with parameters', d_position);
2510 PO_UDA_DEFAULTING_PKG.DEBUG('p_draft_id ' || p_draft_id, d_position);
2511 PO_UDA_DEFAULTING_PKG.DEBUG('p_header_id ' || p_header_id, d_position);
2512 PO_UDA_DEFAULTING_PKG.DEBUG('p_idv_header_id ' || p_idv_header_id, d_position);
2513 PO_UDA_DEFAULTING_PKG.DEBUG('p_idv_line_id ' || p_idv_line_id, d_position);
2514
2515 d_position := 10;
2516
2517 SELECT poh.currency_code, poh.org_id
2518 INTO l_po_currency, l_org_id
2519 FROM po_headers_gt poh
2520 WHERE poh.po_header_id = p_header_id;
2521
2522 d_position := 20;
2523
2524 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2525
2526 d_position := 30;
2527
2528 PO_CORE_S2.get_currency_info
2529 (
2530 x_currency_code => l_po_currency
2531 , x_precision => l_precision
2532 , x_min_unit => l_mau
2533 );
2534
2535 d_position := 40;
2536
2537 BEGIN
2538 IF p_level = 'HEADER' THEN
2539
2540 SELECT Sum(
2541 DECODE
2542 (
2543 POL.matching_basis
2544 ,'AMOUNT', pol.amount
2545 ,nvl2
2546 (
2547 l_mau
2548 ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
2549 ,round((pol.quantity * pol.unit_price), l_precision)
2550 )
2551 )
2552 )
2553 INTO l_return_val
2554 FROM po_lines_merge_v pol
2555 WHERE pol.po_header_id = p_header_id
2556 AND pol.draft_id = p_draft_id
2557 AND (pol.from_header_id = p_idv_header_id OR
2558 pol.contract_id = p_idv_header_id
2559 );
2560
2561 ELSIF p_level = 'LINE' THEN
2562
2563 SELECT Sum(
2564 DECODE
2565 (
2566 POL.matching_basis
2567 ,'AMOUNT', pol.amount
2568 ,nvl2
2569 (
2570 l_mau
2571 ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
2572 ,round((pol.quantity * pol.unit_price), l_precision)
2573 )
2574 )
2575 )
2576 INTO l_return_val
2577 FROM po_lines_merge_v pol
2578 WHERE pol.po_header_id = p_header_id
2579 AND pol.draft_id = p_draft_id
2580 AND pol.from_header_id = p_idv_header_id
2581 AND pol.FROM_LINE_ID = p_idv_line_id;
2582 END IF;
2583
2584
2585 EXCEPTION
2586 WHEN No_Data_Found THEN
2587 RETURN 0;
2588 WHEN OTHERS THEN
2589 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in getAmountOrdered', d_position);
2590 RETURN 0;
2591 END;
2592 d_position := 60;
2593 PO_UDA_DEFAULTING_PKG.DEBUG('l_return_val ' || l_return_val, d_position);
2594
2595 RETURN l_return_val;
2596
2597 EXCEPTION
2598 WHEN OTHERS THEN
2599 PO_UDA_DEFAULTING_PKG.DEBUG('Exception in getAmountOrdered', d_position);
2600 RETURN 0;
2601 END getAmountOrdered;
2602
2603 END;