1 PACKAGE BODY PAY_BACKPAY_RULES_PKG AS
2 /* $Header: pybkr01t.pkb 115.0 99/07/17 05:45:41 porting ship $ */
3 -----------------------------------------------------------------------------
4 -----------------------------------------------------------------------------
5 --
6 -- Standard Insert procedure
7 --
8 procedure insert_row(
9 p_row_id IN OUT varchar2,
10 p_defined_balance_id number,
11 p_input_value_id number,
12 p_backpay_set_id number) is
13 cursor c1 is
14 select rowid
15 from pay_backpay_rules
16 where backpay_set_id = P_BACKPAY_SET_ID
17 and defined_balance_id = P_DEFINED_BALANCE_ID
18 and input_value_id = P_INPUT_VALUE_ID;
19 begin
20 begin
21 insert into pay_backpay_rules(
22 defined_balance_id,
23 input_value_id,
24 backpay_set_id)
25 values (p_defined_balance_id,
26 p_input_value_id,
27 p_backpay_set_id);
28 end;
29 --
30 open c1;
31 fetch c1 into P_ROW_ID;
32 close c1;
33 --
34 end insert_row;
35 -----------------------------------------------------------------------------
36 --
37 -- Standard delete procedure
38 --
39 procedure delete_row(p_row_id varchar2) is
40 begin
41 delete from pay_backpay_rules
42 where rowid = chartorowid(P_ROW_ID);
43 end delete_row;
44 -----------------------------------------------------------------------------
45 --
46 -- Standard lock procedure
47 --
48 procedure lock_row(
49 p_row_id varchar2,
50 p_defined_balance_id number,
51 p_input_value_id number,
52 p_backpay_set_id number) is
53 --
54 cursor CUR is
55 select *
56 from pay_backpay_rules
57 where rowid = chartorowid(P_ROW_ID)
58 FOR UPDATE OF BACKPAY_SET_ID NOWAIT;
59 --
60 rule_rec CUR%rowtype;
61 --
62 begin
63 --
64 open CUR;
65 --
66 fetch CUR into RULE_REC;
67 --
68 close CUR;
69 --
70 if ((rule_rec.defined_balance_id = p_defined_balance_id)
71 or (rule_rec.defined_balance_id is null
72 and (p_defined_balance_id is null)))
73 and ((rule_rec.input_value_id = p_input_value_id)
74 or (rule_rec.input_value_id is null
75 and (p_input_value_id is null)))
76 and ((rule_rec.backpay_set_id = p_backpay_set_id)
77 or (rule_rec.backpay_set_id is null
78 and (p_backpay_set_id is null))) then
79 return; -- Row successfully locked, no changes.
80 end if;
81 --
82 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
83 app_exception.raise_exception;
84 --
85 end lock_row;
86 -----------------------------------------------------------------------------
87 --
88 -- Standard update procedure
89 --
90 -- NB Current business rules dictate that updates are not permitted in
91 -- PAYWSDBS but this is included here for completeness and possible future
92 -- changes to this rule.
93 --
94 procedure update_row(
95 p_row_id varchar2,
96 p_defined_balance_id number,
97 p_input_value_id number,
98 p_backpay_set_id number) is
99 begin
100 update pay_backpay_rules
101 set defined_balance_id = P_DEFINED_BALANCE_ID,
102 input_value_id = P_INPUT_VALUE_ID,
103 backpay_set_id = P_BACKPAY_SET_ID
104 where rowid = chartorowid(P_ROW_ID);
105 --
106 end update_row;
107 -----------------------------------------------------------------------------
108 procedure std_insert_checks(
109 p_backpay_set_id number,
110 p_balance_type_id number,
111 p_input_value_id number) is
112 l_null varchar2(1);
113 begin
114 --
115 -- Cannot have duplicate rows.
116 --
117 begin
118 select null
119 into l_null
120 from sys.dual
121 where not exists (
122 select null
123 from pay_backpay_rules br,
124 pay_defined_balances db
125 where br.backpay_set_id = P_BACKPAY_SET_ID
126 and br.defined_balance_id = db.defined_balance_id
127 and db.balance_type_id = P_BALANCE_TYPE_ID
128 and br.input_value_id = P_INPUT_VALUE_ID);
129 exception
130 when NO_DATA_FOUND then
131 fnd_message.set_name('PAY', 'HR_7036_BACK_RULE_DUP');
132 fnd_message.raise_error;
133 end;
134 --
135 end std_insert_checks;
136 -----------------------------------------------------------------------------
137 procedure chk_overlap_bal_feeds(
138 p_backpay_set_id number) is
139 l_null varchar2(1);
140 begin
141 --
142 -- Commit rule to db then perform this check.
143 -- Check to see if there are any input values feeding this balance and balances
144 -- fed by this input value. Then check to see if there are now duplicates of
145 -- this causing backpay to process a change more than once. The insert should
146 -- thus be disallowed if this is the case.
147 --
148 -- We have basically:
149 -- *
150 -- BAL1 -> IV1 -> BAL2
151 -- _/ \
152 -- IV2 _ --> BAL5
153 -- \ * /
154 -- BAL3 -> IV3 -> BAL4
155 -- (:BT_ID) (:IV_ID)
156 --
157 -- BAL3 is the balance type of the rule we are inserting (:BT_ID) and IV3 is the
158 -- input value of this rule.
159 -- We have IV2 feeding BAL3 and IV3 feeding BAL4.
160 -- The arrows with *'s indicate backpay rules for the current set.
161 -- By inserting our new backpay rule (BAL3 -> IV3) we have effectively added 2
162 -- new "indirect feeds" of IV2 -> BAL4 and IV2 -> BAL5. The former adds a new
163 -- "feed" to backpay and thus is no problem however the latter is a duplicate of
164 -- the other backpay rule in this set. The BAL5 balance would therefore be
165 -- incremented twice given just one input value. The new backpay rule is therefore
166 -- disallowed.
167 --
168 begin
169 select null
170 into l_null
171 from sys.dual
172 where not exists (
173 select bf1.input_value_id, bf2.balance_type_id
174 from pay_backpay_rules br,
175 pay_defined_balances db,
176 pay_balance_feeds bf1,
177 pay_balance_feeds bf2
178 where bf1.balance_type_id = db.balance_type_id
179 and db.defined_balance_id = br.defined_balance_id
180 and br.input_value_id = bf2.input_value_id
181 and br.backpay_set_id = P_BACKPAY_SET_ID
182 group by bf1.input_value_id, bf2.balance_type_id
183 having count(0) > 1);
184 exception
185 when NO_DATA_FOUND then
186 fnd_message.set_name('PAY', 'HR_7037_BACK_RULES_DUP_BAL');
187 fnd_message.raise_error;
188 end;
189 --
190 end chk_overlap_bal_feeds;
191 -----------------------------------------------------------------------------
192 END PAY_BACKPAY_RULES_PKG;
[Home] [Help]