[Home] [Help]
PACKAGE BODY: APPS.PAY_DB_BALANCES_PKG
Source
1 package body pay_db_balances_pkg as
2 /* $Header: pybaldim.pkb 115.8 2003/05/26 03:44:47 sdhole ship $ */
3 -- Date Person Description.
4 -- ------------ ------------- --------------------------------------
5 -- 25-May-03 sdhole Support new two columns START_DATE_CODE,
6 -- SAVE_RUN_BALANCE_ENABLED to the function
7 -- create_balance_dimension for 2898483.
8 -- 10-OCT-2002 nbristow New attribution on dimenion routes.
9 -- 07-OCT-2002 nbristow Support new balance dimension
10 -- functionality.
11 -- 16-JUL-2002 nbristow Now using SQL%count.
12 -- 31-AUG-2001 dsaxby Support new balance dimension columns.
13 -- 28-FEB-1995 dsaxby Altered to support 'R' dimension type.
14 --
15 -- Building Block for BALANCE DIMENSION
16 --
17 function create_balance_dimension
18 (p_business_group_id number default null,
19 p_legislation_code varchar2 default null,
20 p_route_id number,
21 p_database_item_suffix varchar2,
22 p_dimension_name varchar2,
23 p_dimension_type varchar2 default 'N',
24 p_description varchar2 default null,
25 p_feed_checking_type varchar2 default null,
26 p_feed_checking_code varchar2 default null,
27 p_payments_flag varchar2 default 'N',
28 p_expiry_checking_code varchar2 default null,
29 p_expiry_checking_level varchar2 default null,
30 p_dimension_level varchar2 default null,
31 p_period_type varchar2 default null,
32 p_asg_bal_dim number default null,
33 p_dbi_function varchar2 default null,
34 p_save_run_balance_enabled varchar2 default null,
35 p_start_date_code varchar2 default null)
36 return number is
37 x number;
38 begin
39 -- check correct use of expiry checking and feed checking attributes
40 hr_utility.set_location('pay_db_balances_pkg.create_balance_dimension',10);
41 if (p_dimension_type = 'N'
42 and (p_feed_checking_code is not null
43 or p_feed_checking_type is not null
44 or p_expiry_checking_code is not null
45 or p_expiry_checking_level is not null))
46
47 or (p_feed_checking_type = 'P' and p_feed_checking_code is null)
48
49 or (p_dimension_type in ('F', 'R')
50 and (p_expiry_checking_code is not null
51 or p_expiry_checking_level is not null))
52
53 or (p_dimension_type in ('A', 'P')
54 and ((p_expiry_checking_code is null and p_expiry_checking_level <> 'N')
55 or p_expiry_checking_level is null)) then
56
57 hr_utility.set_message(801, 'HR_BAD_BALDIM_DATA');
58 hr_utility.raise_error;
59 end if;
60 --
61 -- check the database item suffix: it must only contain valid
62 -- characters for a DB Item name
63 -- !!!! hr_checkformat etc.
64 --
65 If hr_api.not_exists_in_hr_lookups
66 (null
67 ,'YES_NO'
68 ,nvl(p_save_run_balance_enabled,'N')) Then
69 --
70 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
71 fnd_message.set_token('COLUMN','SAVE_RUN_BALANCE_ENABLED');
72 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
73 fnd_message.raise_error;
74 --
75 End If;
76
77 -- do the insert
78
79 hr_utility.set_location('pay_db_balances_pkg.create_balance_dimension',20);
80 insert into pay_balance_dimensions
81 (balance_dimension_id,
82 business_group_id,
83 legislation_code,
84 route_id,
85 payments_flag,
86 database_item_suffix,
87 dimension_name,
88 dimension_type,
89 description,
90 feed_checking_code,
91 feed_checking_type,
92 expiry_checking_code,
93 expiry_checking_level,
94 dimension_level,
95 period_type,
96 asg_action_balance_dim_id,
97 database_item_function,
98 save_run_balance_enabled,
99 start_date_code)
100 select pay_balance_dimensions_s.nextval,
101 p_business_group_id,
102 p_legislation_code,
103 p_route_id,
104 p_payments_flag,
105 p_database_item_suffix,
106 p_dimension_name,
107 p_dimension_type,
108 p_description,
109 p_feed_checking_code,
110 p_feed_checking_type,
111 p_expiry_checking_code,
112 p_expiry_checking_level,
113 p_dimension_level,
114 p_period_type,
115 p_asg_bal_dim,
116 p_dbi_function,
117 p_save_run_balance_enabled,
118 p_start_date_code
119 from dual
120 where not exists (select ''
121 from pay_balance_dimensions
122 where dimension_name = p_dimension_name
123 and nvl(business_group_id, -999) =
124 nvl(p_business_group_id, -999)
125 and nvl(legislation_code, 'NULL') =
126 nvl(p_legislation_code, 'NULL')
127 );
128 --
129 if (SQL%rowcount > 0) then
130 hr_utility.set_location('pay_db_balances_pkg.create_balance_dimension',30);
131 select pay_balance_dimensions_s.currval
132 into x from dual;
133 return x;
134 else
135 return null;
136 end if;
137 --
138 end create_balance_dimension;
139 --
140 --------------------------------------------------------------------------
141 -- Procedure create_dimension_route
142 --
143 -- Description
144 -- Inserts dimension routes relationshipts ofr run_balances
145 --------------------------------------------------------------------------
146 procedure create_dimension_route(
147 p_dim_id in number,
148 p_route_id in number,
149 p_route_type in varchar2,
150 p_run_dim_id in number,
151 p_priority in number,
152 p_baltyp_col in varchar2 default null,
153 p_dec_required in varchar2 default null)
154 is
155 l_dim_id number;
156 l_run_dim_id number;
157 l_route_id number;
158 begin
159 if (not p_route_type in ('RR', 'SRB')) then
160 hr_utility.set_message(801, 'HR_BAD_BALDIM_DATA');
161 hr_utility.raise_error;
162 end if;
163 insert into pay_dimension_routes
164 (balance_dimension_id,
165 route_id,
166 route_type,
167 run_dimension_id,
168 priority,
169 balance_type_column,
170 decode_required
171 )
172 select p_dim_id,
173 p_route_id,
174 p_route_type,
175 p_run_dim_id,
176 p_priority,
177 p_baltyp_col,
178 p_dec_required
179 from sys.dual
180 where not exists (select ''
181 from pay_dimension_routes
182 where balance_dimension_id = p_dim_id
183 and route_type = p_route_type
184 and priority = p_priority);
185 end create_dimension_route;
186 --
187 end pay_db_balances_pkg;