1 PACKAGE BODY PAY_GARN_LIMIT_RULES_PKG as
2 /* $Header: pyglr01t.pkb 115.0 99/07/17 06:08:42 porting ship $ */
3
4 PROCEDURE pre_insert (x_limit_rule_id IN OUT NUMBER)
5 IS
6 CURSOR C2 IS SELECT pay_us_garn_limit_rules_s.nextval FROM sys.dual;
7 BEGIN
8 if (X_Limit_Rule_Id is NULL) then
9 OPEN C2;
10 FETCH C2 INTO X_Limit_Rule_Id;
11 CLOSE C2;
12 end if;
13 END pre_insert;
14
15 PROCEDURE post_query( x_state_code VARCHAR2,
16 x_garn_category VARCHAR2,
17 x_state_name IN OUT VARCHAR2,
18 x_garn_category_name IN OUT VARCHAR2
19 ) IS
20 l_state_name pay_state_rules.name%TYPE;
21 l_lookup_type hr_lookups.lookup_type%TYPE;
22 l_lookup_code hr_lookups.lookup_code%TYPE;
23 l_meaning hr_lookups.meaning%TYPE;
24
25 CURSOR state_c IS
26 SELECT name
27 FROM pay_state_rules
28 WHERE substr(jurisdiction_code, 1, 2) = x_state_code;
29
30 CURSOR lookup_c IS
31 SELECT meaning
32 FROM hr_lookups
33 WHERE lookup_type = l_lookup_type
34 AND lookup_code = l_lookup_code;
35
36 BEGIN
37 IF x_state_code IS NOT NULL THEN
38 OPEN state_c;
39 FETCH state_c INTO l_state_name;
40 IF state_c%NOTFOUND THEN
41 CLOSE state_c;
42 RAISE no_data_found;
43 ELSE
44 x_state_name := l_state_name;
45 CLOSE state_c;
46 END IF;
47 END IF;
48 IF x_garn_category IS NOT NULL THEN
49 l_lookup_type := 'US_GARN_EXMPT_CAT';
50 l_lookup_code := x_garn_category;
51 OPEN lookup_c;
52 FETCH lookup_c INTO l_meaning;
53 IF lookup_c%NOTFOUND THEN
54 CLOSE lookup_c;
55 RAISE no_data_found;
56 ELSE
57 x_garn_category_name := l_meaning;
58 CLOSE lookup_c;
59 END IF;
60 END IF;
61 EXCEPTION WHEN no_data_found THEN
62 APP_EXCEPTION.RAISE_EXCEPTION;
63 END post_query;
64
65 PROCEDURE Check_Unique( X_State_Code VARCHAR2,
66 X_Garn_Category VARCHAR2
67 ) IS
68 esd DATE;
69 BEGIN
70 SELECT MIN(effective_start_date)
71 INTO esd
72 FROM pay_us_garn_limit_rules_f
73 WHERE state_code = X_State_Code
74 AND GARN_CATEGORY = X_Garn_Category;
75 IF (esd IS NOT NULL) then
76 hr_utility.set_message(801, 'PAY_51780_GER_CHK_UNI_W_DATE');
77 fnd_message.set_token('1',esd);
78 hr_utility.raise_error;
79 END IF;
80 END check_unique;
81
82 END;
[Home] [Help]