[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_VALUE_VALIDATION
Source
1 PACKAGE BODY pay_ca_value_validation AS
2 /* $Header: pycavvfn.pkb 120.0 2005/05/29 03:53:53 appldev noship $ */
3 -----------------------------------------------------------------------
4 -- FUNCTION VALIDATE_WCB_ACCOUNT_NUMBER
5 -----------------------------------------------------------------------
6 FUNCTION validate_wcb_account_number(p_business_group_id in number
7 ,p_account_number in varchar2)
8 RETURN VARCHAR2 IS
9 --
10 l_valid_value varchar2(1);
11 --
12 -- Cursor to validate WCB Account Number
13 --
14 CURSOR validate_wcb_account_number(p_bus_group_id number
15 ,p_act_number varchar2) IS
16 SELECT 'S'
17 from pay_wci_accounts
18 where account_number = p_act_number
19 and business_group_id = p_bus_group_id;
20 --
21 BEGIN
22 hr_utility.set_location('validate_wcb_account_number', 10);
23 --
24 OPEN validate_wcb_account_number(p_business_group_id, p_account_number);
25 FETCH validate_wcb_account_number INTO l_valid_value;
26 IF validate_wcb_account_number%NOTFOUND THEN
27 hr_utility.set_location('validate_wcb_account_number', 20);
28 l_valid_value := 'E';
29 END IF;
30 CLOSE validate_wcb_account_number;
31 --
32 RETURN l_valid_value;
33 --
34 END validate_wcb_account_number;
35 -----------------------------------------------------------------------
36 -- FUNCTION VALIDATE_WCB_RATE_CODE
37 -----------------------------------------------------------------------
38 FUNCTION validate_wcb_rate_code(p_business_group_id in number
39 ,p_rate_code in varchar2)
40 RETURN VARCHAR2 is
41 --
42 l_valid_value varchar2(1);
43 --
44 -- Cursor to validate WCB Rate Code
45 --
46 CURSOR validate_wcb_rate_code(p_bus_group_id number
47 ,p_code varchar2) IS
48 SELECT 'S'
49 from pay_wci_rates
50 where code = p_code
51 and business_group_id = p_bus_group_id;
52 --
53 BEGIN
54 hr_utility.set_location('validate_wcb_rate_code', 10);
55 --
56 OPEN validate_wcb_rate_code(p_business_group_id, p_rate_code);
57 FETCH validate_wcb_rate_code INTO l_valid_value;
58 IF validate_wcb_rate_code%NOTFOUND THEN
59 hr_utility.set_location('validate_wcb_rate_code', 20);
60 l_valid_value := 'E';
61 END IF;
62 CLOSE validate_wcb_rate_code;
63 --
64 RETURN l_valid_value;
65 --
66 END validate_wcb_rate_code;
67 ----------------------------------------------------------------------
68 -- FUNCTION VALIDATE_PMED_ACCOUNT_NUMBER
69 ----------------------------------------------------------------------
70 FUNCTION validate_pmed_account_number(p_business_group_id in number
71 ,p_account_number in varchar2)
72 RETURN VARCHAR2 IS
73 --
74 l_valid_value varchar2(1);
75 --
76 -- Cursor to validate PMED Account Number
77 --
78 CURSOR validate_pmed_account_number(p_bus_group_id number
79 ,p_act_number varchar2) IS
80 SELECT 'S'
81 from pay_ca_pmed_accounts
82 where account_number = p_act_number
83 and business_group_id = p_bus_group_id;
84 --
85 BEGIN
86 hr_utility.set_location('validate_pmed_account_number', 10);
87 --
88 OPEN validate_pmed_account_number(p_business_group_id, p_account_number);
89 FETCH validate_pmed_account_number INTO l_valid_value;
90 IF validate_pmed_account_number%NOTFOUND THEN
91 hr_utility.set_location('validate_pmed_account_number', 20);
92 l_valid_value := 'E';
93 END IF;
94 CLOSE validate_pmed_account_number;
95 --
96 RETURN l_valid_value;
97 --
98 END validate_pmed_account_number;
99 --------------------------------------------------------------------
100 -- FUNCTION VALIDATE_USER_TABLE_NAME
101 --------------------------------------------------------------------
102 FUNCTION validate_user_table_name(p_business_group_id in number
103 ,p_user_table_name in varchar2)
104 RETURN VARCHAR2 IS
105 --
106 l_valid_value varchar2(1); -- return variable
107 --
108 -- Cursor to validate user table name
109 --
110 CURSOR validate_user_table_name(p_bus_group_id number
111 ,p_user_tab_name varchar2) IS
112 SELECT 'S'
113 from pay_user_tables put
114 where put.user_table_name = p_user_tab_name
115 and (
116 (put.business_group_id = p_bus_group_id
117 and put.legislation_code is null
118 )
119 or
120 (put.business_group_id is null
121 and put.legislation_code ='CA'
122 )
123 );
124 --
125 BEGIN
126 hr_utility.set_location('validate_user_table_name', 10);
127 --
128 OPEN validate_user_table_name(p_business_group_id, p_user_table_name);
129 FETCH validate_user_table_name INTO l_valid_value;
130 IF validate_user_table_name%NOTFOUND THEN
131 hr_utility.set_location('validate_user_table_name',20);
132 l_valid_value := 'E';
133 END IF;
134 CLOSE validate_user_table_name;
135 --
136 RETURN l_valid_value;
137 --
138 END validate_user_table_name;
139 --------------------------------------------------------------------
140 -- VALIDATE_USER_TABLE_COLUMN
141 --------------------------------------------------------------------
142 FUNCTION validate_user_table_column(p_business_group_id in number
143 ,p_user_table_column in varchar2)
144 RETURN VARCHAR2 IS
145 --
146 l_valid_value varchar2(1);
147 --
148 -- Cursor to validate user table column
149 --
150 CURSOR validate_user_table_column(p_bus_group_id number
151 ,p_user_tab_col varchar2) IS
152 SELECT 'S'
153 from pay_user_columns puc
154 where puc.user_column_name = p_user_tab_col
155 and (
156 (puc.business_group_id = p_bus_group_id
157 and puc.legislation_code is null
158 )
159 or
160 (puc.business_group_id is null
161 and puc.legislation_code ='CA'
162 )
163 );
164 --
165 BEGIN
166 hr_utility.set_location('validate_user_table_column', 10);
167 --
168 OPEN validate_user_table_column(p_business_group_id, p_user_table_column);
169 FETCH validate_user_table_column INTO l_valid_value;
170 IF validate_user_table_column%NOTFOUND THEN
171 hr_utility.set_location('validate_user_table_column', 20);
172 l_valid_value := 'E';
173 END IF;
174 CLOSE validate_user_table_column;
175 --
176 RETURN l_valid_value;
177 --
178 END validate_user_table_column;
179 --------------------------------------------------------------------
180 END pay_ca_value_validation;