1 PACKAGE BODY PAY_GARN_EXEMPTION_RULES_PKG as
2 /* $Header: pyger01t.pkb 115.0 99/07/17 06:08:28 porting ship $ */
3 --
4 PROCEDURE pre_insert (x_exemption_rule_id IN OUT NUMBER)
5 IS
6 CURSOR C2 IS SELECT pay_us_garn_exemption_rules_s.nextval FROM sys.dual;
7 BEGIN
8 if (X_Exemption_Rule_Id is NULL) then
9 OPEN C2;
10 FETCH C2 INTO X_Exemption_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_calc_rule VARCHAR2,
18 x_dependents_calc_rule VARCHAR2,
19 x_state_name IN OUT VARCHAR2,
20 x_garn_category_name IN OUT VARCHAR2,
21 x_d_calc_rule IN OUT VARCHAR2,
22 x_d_dependents_calc_rule IN OUT VARCHAR2
23 ) IS
24 l_state_name pay_state_rules.name%TYPE;
25 l_lookup_type hr_lookups.lookup_type%TYPE;
26 l_lookup_code hr_lookups.lookup_code%TYPE;
27 l_meaning hr_lookups.meaning%TYPE;
28
29 CURSOR state_c IS
30 SELECT name
31 FROM pay_state_rules
32 WHERE substr(jurisdiction_code, 1, 2) = x_state_code;
33
34 CURSOR lookup_c IS
35 SELECT meaning
36 FROM hr_lookups
37 WHERE lookup_type = l_lookup_type
38 AND lookup_code = l_lookup_code;
39
40 BEGIN
41 IF x_state_code IS NOT NULL THEN
42 OPEN state_c;
43 FETCH state_c INTO l_state_name;
44 IF state_c%NOTFOUND THEN
45 CLOSE state_c;
46 RAISE no_data_found;
47 ELSE
48 x_state_name := l_state_name;
49 CLOSE state_c;
50 END IF;
51 END IF;
52 IF x_garn_category IS NOT NULL THEN
53 l_lookup_type := 'US_GARN_EXMPT_CAT';
54 l_lookup_code := x_garn_category;
55 OPEN lookup_c;
56 FETCH lookup_c INTO l_meaning;
57 IF lookup_c%NOTFOUND THEN
58 CLOSE lookup_c;
59 RAISE no_data_found;
60 ELSE
61 x_garn_category_name := l_meaning;
62 CLOSE lookup_c;
63 END IF;
64 END IF;
65 IF x_calc_rule IS NOT NULL THEN
66 l_lookup_type := 'US_GARN_CHK_CALRULE';
67 l_lookup_code := x_calc_rule;
68 OPEN lookup_c;
69 FETCH lookup_c INTO l_meaning;
70 IF lookup_c%NOTFOUND THEN
71 CLOSE lookup_c;
72 RAISE no_data_found;
73 ELSE
74 x_d_calc_rule := l_meaning;
75 CLOSE lookup_c;
76 END IF;
77 END IF;
78 IF x_dependents_calc_rule IS NOT NULL THEN
79 l_lookup_type := 'US_GARN_DEP_CALRULE';
80 l_lookup_code := x_dependents_calc_rule;
81 OPEN lookup_c;
82 FETCH lookup_c INTO l_meaning;
83 IF lookup_c%NOTFOUND THEN
84 CLOSE lookup_c;
85 RAISE no_data_found;
86 ELSE
87 x_d_dependents_calc_rule := l_meaning;
88 CLOSE lookup_c;
89 END IF;
90 END IF;
91 EXCEPTION WHEN no_data_found THEN
92 APP_EXCEPTION.RAISE_EXCEPTION;
93 END post_query;
94
95 PROCEDURE Check_Unique( X_State_Code VARCHAR2,
96 X_Garn_Category VARCHAR2
97 ) IS
98 esd DATE;
99 BEGIN
100 SELECT MIN(effective_start_date)
101 INTO esd
102 FROM pay_us_garn_exemption_rules_f
103 WHERE state_code = X_State_Code
104 AND GARN_CATEGORY = X_Garn_Category;
105 IF (esd IS NOT NULL) then
106 hr_utility.set_message(801, 'PAY_51780_GER_CHK_UNI_W_DATE');
107 fnd_message.set_token('1',esd);
108 hr_utility.raise_error;
109 END IF;
110 END check_unique;
111 END;
[Home] [Help]