[Home] [Help]
PACKAGE BODY: APPS.GML_VALIDATE_PO
Source
1 PACKAGE BODY GML_VALIDATE_PO AS
2 /* $Header: GMLPOVAB.pls 115.7 99/10/26 11:36:32 porting ship $ */
3
4 FUNCTION val_orgn_code (v_orgn_code IN SY_ORGN_MST.ORGN_CODE%TYPE)
5 /*========================================================================
6 | |
7 | FUNCTION NAME val_orgn_code |
8 | |
9 | DESCRIPTION Function for Orgn code validation. |
10 | |
11 | MODIFICATION HISTORY |
12 | |
13 | 11/22/97 Ravi Dasani created |
14 | |
15 ========================================================================*/
16 RETURN BOOLEAN
17 IS
18 err_num NUMBER;
19 err_msg VARCHAR2(100);
20 v_dummy NUMBER;
21
22 CURSOR op_orgn_cur IS
23 SELECT 1
24 FROM sy_orgn_mst
25 WHERE orgn_code = v_orgn_code
26 AND delete_mark = 0;
27
28 BEGIN
29 OPEN op_orgn_cur;
30 FETCH op_orgn_cur INTO v_dummy;
31 IF op_orgn_cur%FOUND THEN
32 CLOSE op_orgn_cur;
33 RETURN TRUE;
34 ELSE
35 CLOSE op_orgn_cur;
36 RETURN FALSE;
37 END IF;
38
39 EXCEPTION
40
41 WHEN OTHERS THEN
42 err_num := SQLCODE;
43 err_msg := SUBSTRB(SQLERRM, 1, 100);
44 raise_application_error(-20000, err_msg);
45
46 END val_orgn_code;
47
48 /*========================================================================
49 | |
50 | FUNCTION NAME val_operator_code |
51 | |
52 | DESCRIPTION Function for operator code validation. |
53 | |
54 | MODIFICATION HISTORY |
55 | |
56 | 10/22/97 Kenny Jiang created |
57 | 05/20/98 T. Ricci commented Function sy_oper_mst not in |
58 | GEMMS 5.0 |
59 | |
60 ========================================================================
61
62 FUNCTION val_operator_code (v_op_code IN sy_oper_mst.op_code%TYPE)
63 RETURN BOOLEAN
64 IS
65 err_num NUMBER;
66 err_msg VARCHAR2(100);
67 v_dummy NUMBER;
68
69 CURSOR op_code_cur IS
70 SELECT 1
71 FROM sy_oper_mst
72 WHERE op_code = v_op_code
73 AND delete_mark = 0;
74
75 BEGIN
76 OPEN op_code_cur;
77 FETCH op_code_cur INTO v_dummy;
78 IF op_code_cur%FOUND THEN
79 CLOSE op_code_cur;
80 RETURN TRUE;
81 ELSE
82 CLOSE op_code_cur;
83 RETURN FALSE;
84 END IF;
85
86 EXCEPTION
87
88 WHEN OTHERS THEN
89 err_num := SQLCODE;
90 err_msg := SUBSTRB(SQLERRM, 1, 100);
91 raise_application_error(-20000, err_msg);
92
93 END val_operator_code;
94
95 ========================================================================
96 | |
97 | FUNCTION NAME val_doc_assign |
98 | |
99 | DESCRIPTION Function for document validation. |
100 | |
101 | MODIFICATION HISTORY |
102 | |
103 | 10/22/97 Kenny Jiang created |
104 | |
105 =========================================================================*/
106
107 FUNCTION val_doc_assign (v_orgn_code IN sy_docs_seq.orgn_code%TYPE)
108 RETURN BOOLEAN
109
110 IS
111 err_num NUMBER;
112 err_msg VARCHAR2(100);
113 v_dummy NUMBER;
114
115 CURSOR doc_cur IS
116 SELECT 1
117 FROM sy_docs_seq
118 WHERE doc_type = 'PORD'
119 AND orgn_code = v_orgn_code
120 AND delete_mark = 0;
121
122 BEGIN
123 OPEN doc_cur;
124 FETCH doc_cur INTO v_dummy;
125 IF doc_cur%FOUND THEN
126 CLOSE doc_cur;
127 RETURN TRUE;
128 ELSE
129 CLOSE doc_cur;
130 RETURN FALSE;
131 END IF;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 err_num := SQLCODE;
136 err_msg := SUBSTRB(SQLERRM, 1, 100);
137 raise_application_error(-20000, err_msg);
138
139 END val_doc_assign;
140
141 /*========================================================================
142 | |
143 | FUNCTION NAME val_warehouse |
144 | |
145 | DESCRIPTION Function for warehouse validation. |
146 | |
147 | MODIFICATION HISTORY |
148 | |
149 | 10/22/97 Kenny Jiang created |
150 | 03/18/98 Ravi Dasani modified to add validation of whse and org|
151 | for the same set of books. |
152 | |
153 =========================================================================*/
154
155 FUNCTION val_warehouse (v_whse_code IN ic_whse_mst.whse_code%TYPE,
156 v_orgn_code IN ic_whse_mst.orgn_code%TYPE)
157 RETURN BOOLEAN
158 IS
159 err_num NUMBER;
160 err_msg VARCHAR2(100);
161 v_dummy NUMBER;
162 v_whse_sob NUMBER;
163 v_org_sob NUMBER;
164
165 CURSOR whse_cur IS
166 SELECT distinct sob_id
167 from gl_plcy_mst
168 where co_code = (select co_code
169 from sy_orgn_mst
170 where orgn_code = (select orgn_code
171 from ic_whse_mst
172 where whse_code=v_whse_code));
173
174 CURSOR org_cur IS
175 SELECT distinct sob_id
176 from gl_plcy_mst
177 where co_code = (select co_code
178 from sy_orgn_mst
179 where orgn_code = v_orgn_code);
180
181 BEGIN
182 OPEN whse_cur;
183 FETCH whse_cur into v_whse_sob;
184 OPEN org_cur;
185 FETCH org_cur into v_org_sob;
186
187 IF whse_cur%NOTFOUND THEN
188 CLOSE whse_cur;
189 CLOSE org_cur;
190 RETURN FALSE;
191 END IF;
192
193 IF (v_whse_sob = v_org_sob) THEN
194 CLOSE whse_cur;
195 CLOSE org_cur;
196 RETURN TRUE;
197 ELSE
198 CLOSE whse_cur;
199 CLOSE org_cur;
200 RETURN FALSE;
201 END IF;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 err_num := SQLCODE;
206 err_msg := SUBSTRB(SQLERRM, 1, 100);
207 raise_application_error(-20000, err_msg);
208
209 END val_warehouse;
210
211 /*========================================================================
212 | |
213 | FUNCTION NAME val_vendor |
214 | |
215 | DESCRIPTION Function for vendor validation. |
216 | |
217 | MODIFICATION HISTORY |
218 | |
219 | 10/22/97 Kenny Jiang created |
220 | |
221 =========================================================================*/
222
223 FUNCTION val_vendor
224 (v_of_vendor_site_id IN po_vend_mst.of_vendor_site_id%TYPE,
225 v_co_code IN po_vend_mst.co_code%TYPE)
226 RETURN BOOLEAN
227 IS
228 err_num NUMBER;
229 err_msg VARCHAR2(100);
230 v_dummy NUMBER;
231
232 CURSOR vendor_cur IS
233 SELECT 1
234 FROM po_vend_mst
235 WHERE of_vendor_site_id = v_of_vendor_site_id
236 AND ship_ind > 0
237 /* B#972240 Begin */
238 /*AND co_code = v_co_code; */
239 AND ((co_code = v_co_code) OR(co_code is NULL));
240 /* B#972240 end */
241
242 BEGIN
243 OPEN vendor_cur;
244 FETCH vendor_cur into v_dummy;
245 IF vendor_cur%FOUND THEN
246 CLOSE vendor_cur;
247 RETURN TRUE;
248 ELSE
249 CLOSE vendor_cur;
250 RETURN FALSE;
251 END IF;
252
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 err_num := SQLCODE;
257 err_msg := SUBSTRB(SQLERRM, 1, 100);
258 raise_application_error(-20000, err_msg);
259
260 END val_vendor;
261
262 /*========================================================================
263 | |
264 | FUNCTION NAME val_item |
265 | |
266 | DESCRIPTION Function for item validation. |
267 | |
268 | MODIFICATION HISTORY |
269 | |
270 | 10/22/97 Kenny Jiang created |
271 | |
272 =========================================================================*/
273
274 FUNCTION val_item (v_item_no IN ic_item_mst.item_no%TYPE)
275 RETURN BOOLEAN
276 IS
277 err_num NUMBER;
278 err_msg VARCHAR2(100);
279 v_dummy NUMBER;
280
281 CURSOR item_cur IS
282 SELECT 1
283 FROM ic_item_mst
284 WHERE item_no = v_item_no;
285
286 BEGIN
287 OPEN item_cur;
288 FETCH item_cur into v_dummy;
289 IF item_cur%FOUND THEN
290 CLOSE item_cur;
291 RETURN TRUE;
292 ELSE
293 CLOSE item_cur;
294 RETURN FALSE;
295 END IF;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 err_num := SQLCODE;
300 err_msg := SUBSTRB(SQLERRM, 1, 100);
301 raise_application_error(-20000, err_msg);
302
303 END val_item;
304
305
306 /*========================================================================
307 | |
308 | FUNCTION NAME val_currency |
309 | |
310 | DESCRIPTION Function for currency validation. |
311 | |
312 | MODIFICATION HISTORY |
313 | |
314 | 10/22/97 Kenny Jiang created |
315 | |
316 =========================================================================*/
317
318 FUNCTION val_currency (v_currency_code IN gl_curr_mst.currency_code%TYPE)
319 RETURN BOOLEAN
320 IS
321 err_num NUMBER;
322 err_msg VARCHAR2(100);
323 v_dummy NUMBER;
324
325 CURSOR currency_cur IS
326 SELECT 1
327 FROM gl_curr_mst
328 WHERE currency_code = v_currency_code;
329
330 BEGIN
331 OPEN currency_cur;
332 FETCH currency_cur into v_dummy;
333 IF currency_cur%FOUND THEN
334 CLOSE currency_cur;
335 RETURN TRUE;
336 ELSE
337 CLOSE currency_cur;
338 RETURN FALSE;
339 END IF;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 err_num := SQLCODE;
344 err_msg := SUBSTRB(SQLERRM, 1, 100);
345 raise_application_error(-20000, err_msg);
346
347 END val_currency;
348
349
350 /*========================================================================
351 | |
352 | FUNCTION NAME val_aqui_cost_code |
353 | |
354 | DESCRIPTION Function for cost code validation. |
355 | |
356 | MODIFICATION HISTORY |
357 | |
358 | 10/22/97 Kenny Jiang created |
359 | |
360 =========================================================================*/
361
362 FUNCTION val_aqui_cost_code
363 (v_aqui_cost_code IN po_cost_mst.aqui_cost_code%TYPE)
364 RETURN BOOLEAN
365 IS
366 err_num NUMBER;
367 err_msg VARCHAR2(100);
368 v_dummy NUMBER;
369
370 CURSOR cost_cur IS
371 SELECT 1
372 FROM po_cost_mst
373 WHERE aqui_cost_code = v_aqui_cost_code;
374
375 BEGIN
376 OPEN cost_cur;
377 FETCH cost_cur into v_dummy;
378 IF cost_cur%FOUND THEN
379 CLOSE cost_cur;
380 RETURN TRUE;
381 ELSE
382 CLOSE cost_cur;
383 RETURN FALSE;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 err_num := SQLCODE;
389 err_msg := SUBSTRB(SQLERRM, 1, 100);
390 raise_application_error(-20000, err_msg);
391
392 END val_aqui_cost_code;
393
394
395 /*========================================================================
396 | |
397 | FUNCTION NAME val_uom |
398 | |
399 | DESCRIPTION Function for unit of measure validation. |
400 | |
401 | MODIFICATION HISTORY |
402 | |
403 | 10/22/97 Kenny Jiang created |
404 | |
405 ========================================================================*/
406
407 FUNCTION val_uom
408 (v_um_code IN sy_uoms_mst.um_code%TYPE)
409 RETURN BOOLEAN
410 IS
411 err_num NUMBER;
412 err_msg VARCHAR2(100);
413 v_dummy NUMBER;
414
415 CURSOR uom_cur IS
416 SELECT 1
417 FROM sy_uoms_mst
418 WHERE um_code = v_um_code;
419
420 BEGIN
421 OPEN uom_cur;
422 FETCH uom_cur INTO v_dummy;
423 IF uom_cur%FOUND THEN
424 CLOSE uom_cur;
425 RETURN TRUE;
426 ELSE
427 CLOSE uom_cur;
428 RETURN FALSE;
429 END IF;
430
431 EXCEPTION
432
433 WHEN OTHERS THEN
434 err_num := SQLCODE;
435 err_msg := SUBSTRB(SQLERRM, 1, 100);
436 raise_application_error(-20000, err_msg);
437
438 END val_uom;
439
440
441 /*========================================================================
442 | |
443 | FUNCTION NAME val_shipper_code |
444 | |
445 | DESCRIPTION Function for shipper code validation. |
446 | |
447 | MODIFICATION HISTORY |
448 | |
449 | 10/22/97 Kenny Jiang created |
450 | |
451 =========================================================================*/
452
453 FUNCTION val_shipper_code
454 (v_shipper_code IN op_ship_mst.shipper_code%TYPE)
455 RETURN BOOLEAN
456 IS
457 err_num NUMBER;
458 err_msg VARCHAR2(100);
459 v_dummy NUMBER;
460
461 CURSOR shipper_cur IS
462 SELECT 1
463 FROM op_ship_mst
464 WHERE shipper_code = v_shipper_code;
465
466 BEGIN
467 OPEN shipper_cur;
468 FETCH shipper_cur INTO v_dummy;
469 IF shipper_cur%FOUND THEN
470 CLOSE shipper_cur;
471 RETURN TRUE;
472 ELSE
473 CLOSE shipper_cur;
474 RETURN FALSE;
475 END IF;
476
477 EXCEPTION
478
479 WHEN OTHERS THEN
480 err_num := SQLCODE;
481 err_msg := SUBSTRB(SQLERRM, 1, 100);
482 raise_application_error(-20000, err_msg);
483
484 END val_shipper_code;
485
486 /*========================================================================
487 | |
488 | FUNCTION NAME val_frtbill_mthd |
489 | |
490 | DESCRIPTION Function for frtbill method validation. |
491 | |
492 | MODIFICATION HISTORY |
493 | |
494 | 10/22/97 Kenny Jiang created |
495 | 11/10/98 Tony Ricci changed to use of_frtbill_mthd |
496 | |
497 =========================================================================*/
498
499 FUNCTION val_frtbill_mthd
500 (v_frtbill_mthd IN op_frgt_mth.of_frtbill_mthd%TYPE)
501 RETURN BOOLEAN
502 IS
503 err_num NUMBER;
504 err_msg VARCHAR2(100);
505 v_dummy NUMBER;
506
507 CURSOR frtbill_cur IS
508 SELECT 1
509 FROM op_frgt_mth
510 WHERE of_frtbill_mthd = v_frtbill_mthd;
511
512 BEGIN
513 OPEN frtbill_cur;
514 FETCH frtbill_cur INTO v_dummy;
515 IF frtbill_cur%FOUND THEN
516 CLOSE frtbill_cur;
517 RETURN TRUE;
518 ELSE
519 CLOSE frtbill_cur;
520 RETURN FALSE;
521 END IF;
522
523 EXCEPTION
524
525 WHEN OTHERS THEN
526 err_num := SQLCODE;
527 err_msg := SUBSTRB(SQLERRM, 1, 100);
528 raise_application_error(-20000, err_msg);
529
530 END val_frtbill_mthd;
531
532 /*========================================================================
533 | |
534 | FUNCTION NAME val_terms_code |
535 | |
536 | DESCRIPTION Function for terms code validation. |
537 | |
538 | MODIFICATION HISTORY |
539 | |
540 | 10/22/97 Kenny Jiang created |
541 | 06/11/99 Tony Ricci use of_terms_code instead of terms_code |
542 | |
543 ========================================================================*/
544
545 FUNCTION val_terms_code
546 (v_of_terms_code IN op_term_mst.of_terms_code%TYPE)
547 RETURN BOOLEAN
548 IS
549 err_num NUMBER;
550 err_msg VARCHAR2(100);
551 v_dummy NUMBER;
552
553 CURSOR terms_cur IS
554 SELECT 1
555 FROM op_term_mst
556 WHERE of_terms_code = v_of_terms_code;
557
558 BEGIN
559 OPEN terms_cur;
560 FETCH terms_cur INTO v_dummy;
561 IF terms_cur%FOUND THEN
562 CLOSE terms_cur;
563 RETURN TRUE;
564 ELSE
565 CLOSE terms_cur;
566 RETURN FALSE;
567 END IF;
568
569 EXCEPTION
570
571 WHEN OTHERS THEN
572 err_num := SQLCODE;
573 err_msg := SUBSTRB(SQLERRM, 1, 100);
574 raise_application_error(-20000, err_msg);
575
576 END val_terms_code;
577
578
579 /*========================================================================
580 | |
581 | FUNCTION NAME val_qc_grade_wanted |
582 | |
583 | DESCRIPTION Function for qc_grade validation. |
584 | |
585 | MODIFICATION HISTORY |
586 | |
587 | 12/11/97 Rajeshwari Chellam created |
588 | |
589 ========================================================================*/
590
591 FUNCTION val_qc_grade_wanted
592 (v_qc_grade_wanted IN qc_grad_mst.qc_grade%TYPE)
593 RETURN BOOLEAN
594 IS
595 err_num NUMBER;
596 err_msg VARCHAR2(100);
597 v_dummy NUMBER;
598
599 CURSOR qc_grade_cur IS
600 SELECT 1
601 FROM qc_grad_mst
602 WHERE qc_grade = v_qc_grade_wanted;
603
604 BEGIN
605 OPEN qc_grade_cur;
606 FETCH qc_grade_cur INTO v_dummy;
607 IF qc_grade_cur%FOUND THEN
608 CLOSE qc_grade_cur;
609 RETURN TRUE;
610 ELSE
611 CLOSE qc_grade_cur;
612 RETURN FALSE;
613 END IF;
614
615 EXCEPTION
616
617 WHEN OTHERS THEN
618 err_num := SQLCODE;
619 err_msg := SUBSTRB(SQLERRM, 1, 100);
620 raise_application_error(-20000, err_msg);
621
622 END val_qc_grade_wanted;
623
624 /*========================================================================
625 | |
626 | PROCEDURE NAME get_gl_source |
627 | |
628 | DESCRIPTION Procedure to get gl source. |
629 | |
630 | MODIFICATION HISTORY |
631 | |
632 | 10/22/97 Kenny Jiang created |
633 | |
634 =========================================================================*/
635
636 PROCEDURE get_gl_source
637 (v_trans_source_type OUT gl_srce_mst.trans_source_type%TYPE)
638 IS
639 err_num NUMBER;
640 err_msg VARCHAR2(100);
641
642 CURSOR gl_cur IS
643 SELECT trans_source_type
644 FROM gl_srce_mst
645 WHERE trans_source_code = 'PO';
646
647 BEGIN
648 OPEN gl_cur;
649 FETCH gl_cur INTO v_trans_source_type;
650 CLOSE gl_cur;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 err_num := SQLCODE;
655 err_msg := SUBSTRB(SQLERRM, 1, 100);
656 raise_application_error(-20000, err_msg);
657 END get_gl_source;
658
659 /*========================================================================
660 | |
661 | PROCEDURE NAME get_base_currency |
662 | |
663 | DESCRIPTION Procedure to get base currency. |
664 | |
665 | MODIFICATION HISTORY |
666 | |
667 | 10/22/97 Kenny Jiang created |
668 | | =========================================================================*/
669
670 PROCEDURE get_base_currency
671 (v_base_currency_code OUT gl_plcy_mst.base_currency_code%TYPE,
672 v_orgn_code IN sy_orgn_mst.orgn_code%TYPE)
673 IS
674 err_num NUMBER;
675 err_msg VARCHAR2(100);
676
677 CURSOR base_currency_cur IS
678 SELECT plcy.base_currency_code
679 FROM sy_orgn_mst orgn,
680 gl_plcy_mst plcy
681 WHERE orgn.orgn_code = v_orgn_code AND
682 orgn.co_code = plcy.co_code;
683
684 BEGIN
685 OPEN base_currency_cur;
686 FETCH base_currency_cur INTO v_base_currency_code;
687 CLOSE base_currency_cur;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691 err_num := SQLCODE;
692 err_msg := SUBSTRB(SQLERRM, 1, 100);
693 raise_application_error(-20000, err_msg);
694
695 END get_base_currency;
696
697 /*========================================================================
698 | |
699 | PROCEDURE NAME get_exchange_rate |
700 | |
701 | DESCRIPTION Procedure to get exchange rate. |
702 | |
703 | MODIFICATION HISTORY |
704 | |
705 | 10/22/97 Kenny Jiang created |
706 | |
707 ========================================================================*/
708
709 PROCEDURE get_exchange_rate
710 (v_exchange_rate OUT gl_xchg_rte.exchange_rate%TYPE,
711 v_mul_div_sign OUT gl_xchg_rte.mul_div_sign%TYPE,
712 v_exchange_rate_date OUT gl_xchg_rte.exchange_rate_date%TYPE,
713 v_to_currency IN gl_xchg_rte.to_currency_code%TYPE,
714 v_from_currency IN gl_xchg_rte.from_currency_code%TYPE,
715 v_trans_source_type IN gl_srce_mst.trans_source_type%TYPE)
716
717 IS
718 err_num NUMBER;
719 err_msg VARCHAR2(100);
720
721 CURSOR exchange_cur IS
722 SELECT ex.exchange_rate,
723 ex.mul_div_sign,
724 ex.exchange_rate_date
725 FROM gl_xchg_rte ex,
726 gl_srce_mst src
727 WHERE ex.to_currency_code = v_to_currency AND
728 ex.from_currency_code = v_from_currency AND
729 ex.exchange_rate_date <= SYSDATE AND
730 ex.rate_type_code = src.rate_type_code AND
731 src.trans_source_type = v_trans_source_type AND
732 ex.delete_mark =0;
733
734 BEGIN
735 OPEN exchange_cur;
736 FETCH exchange_cur INTO v_exchange_rate,
737 v_mul_div_sign,
738 v_exchange_rate_date;
739 CLOSE exchange_cur;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 err_num := SQLCODE;
744 err_msg := SUBSTRB(SQLERRM, 1, 100);
745 raise_application_error(-20000, err_msg);
746
747 END get_exchange_rate;
748
749
750 END GML_VALIDATE_PO;