1 PACKAGE BODY PAY_NET_CALCULATION_RULES_PKG as
2 /* $Header: pyapncr.pkb 115.1 99/07/17 05:42:06 porting ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
6 X_Net_Calculation_Rule_Id IN OUT NUMBER,
7 X_Accrual_Plan_Id NUMBER,
8 X_Business_Group_Id NUMBER,
9 X_Input_Value_Id NUMBER,
10 X_Add_Or_Subtract VARCHAR2
11 ) IS
12 CURSOR C IS SELECT rowid FROM PAY_NET_CALCULATION_RULES
13
14 WHERE net_calculation_rule_id = X_Net_Calculation_Rule_Id;
15
16
17
18
19
20 CURSOR C2 IS SELECT pay_net_calculation_rules_s.nextval FROM sys.dual;
21 BEGIN
22
23 if (X_Net_Calculation_Rule_Id is NULL) then
24 OPEN C2;
25 FETCH C2 INTO X_Net_Calculation_Rule_Id;
26 CLOSE C2;
27 end if;
28 INSERT INTO PAY_NET_CALCULATION_RULES(
29 net_calculation_rule_id,
30 accrual_plan_id,
31 business_group_id,
32 input_value_id,
33 add_or_subtract
34 ) VALUES (
35 X_Net_Calculation_Rule_Id,
36 X_Accrual_Plan_Id,
37 X_Business_Group_Id,
38 X_Input_Value_Id,
39 X_Add_Or_Subtract
40 );
41
42 OPEN C;
43 FETCH C INTO X_Rowid;
44 if (C%NOTFOUND) then
45 CLOSE C;
46 RAISE NO_DATA_FOUND;
47 end if;
48 CLOSE C;
49 END Insert_Row;
50 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
51 X_Net_Calculation_Rule_Id NUMBER,
52 X_Accrual_Plan_Id NUMBER,
53 X_Business_Group_Id NUMBER,
54 X_Input_Value_Id NUMBER,
55 X_Add_Or_Subtract VARCHAR2
56 ) IS
57 CURSOR C IS
58 SELECT *
59 FROM PAY_NET_CALCULATION_RULES
60 WHERE rowid = X_Rowid
61 FOR UPDATE of Net_Calculation_Rule_Id NOWAIT;
62 Recinfo C%ROWTYPE;
63 BEGIN
64 OPEN C;
65 FETCH C INTO Recinfo;
66 if (C%NOTFOUND) then
67 CLOSE C;
68 RAISE NO_DATA_FOUND;
69 end if;
70 CLOSE C;
71 if (
72 ( (Recinfo.net_calculation_rule_id = X_Net_Calculation_Rule_Id)
73 OR ( (Recinfo.net_calculation_rule_id IS NULL)
74 AND (X_Net_Calculation_Rule_Id IS NULL)))
75 AND ( (Recinfo.accrual_plan_id = X_Accrual_Plan_Id)
76 OR ( (Recinfo.accrual_plan_id IS NULL)
77 AND (X_Accrual_Plan_Id IS NULL)))
78 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
79 OR ( (Recinfo.business_group_id IS NULL)
80 AND (X_Business_Group_Id IS NULL)))
81 AND ( (Recinfo.input_value_id = X_Input_Value_Id)
82 OR ( (Recinfo.input_value_id IS NULL)
83 AND (X_Input_Value_Id IS NULL)))
84 AND ( (Recinfo.add_or_subtract = X_Add_Or_Subtract)
85 OR ( (Recinfo.add_or_subtract IS NULL)
86 AND (X_Add_Or_Subtract IS NULL)))
87 ) then
88 return;
89 else
90 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
91 APP_EXCEPTION.RAISE_EXCEPTION;
92 end if;
93 END Lock_Row;
94
95 PROCEDURE Update_Row(X_Rowid VARCHAR2,
96 X_Net_Calculation_Rule_Id NUMBER,
97 X_Accrual_Plan_Id NUMBER,
98 X_Business_Group_Id NUMBER,
99 X_Input_Value_Id NUMBER,
100 X_Add_Or_Subtract VARCHAR2
101 ) IS
102 BEGIN
103 UPDATE PAY_NET_CALCULATION_RULES
104 SET
105 net_calculation_rule_id = X_Net_Calculation_Rule_Id,
106 accrual_plan_id = X_Accrual_Plan_Id,
107 business_group_id = X_Business_Group_Id,
108 input_value_id = X_Input_Value_Id,
109 add_or_subtract = X_Add_Or_Subtract
110 WHERE rowid = X_rowid;
111
112 if (SQL%NOTFOUND) then
113 RAISE NO_DATA_FOUND;
114 end if;
115
116 END Update_Row;
117
118 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
119 BEGIN
120 DELETE FROM PAY_NET_CALCULATION_RULES
121 WHERE rowid = X_Rowid;
122
123 if (SQL%NOTFOUND) then
124 RAISE NO_DATA_FOUND;
125 end if;
126 END Delete_Row;
127
128 PROCEDURE DUP_INPUT_VALUE(p_accrual_plan_id IN number,
129 p_input_value_id IN number,
130 p_net_calculation_rule_id IN number) IS
131 --
132 l_comb_exists VARCHAR2(2);
133 --
134 CURSOR dup_rec IS
135 select 'Y'
136 from pay_net_calculation_rules
137 where
138 ((p_net_calculation_rule_id is null)
139 or (p_net_calculation_rule_id is not null
140 and net_calculation_rule_id <> p_net_calculation_rule_id))
141 and accrual_plan_id = p_accrual_plan_id
142 and input_value_id = p_input_value_id;
143 --
144 BEGIN
145 --
146 l_comb_exists := 'N';
147 --
148 -- open fetch and close the cursor - if a record is found then the local
149 -- variable will be set to 'Y', otherwise it will remain 'N'
150 --
151 OPEN dup_rec;
152 FETCH dup_rec INTO l_comb_exists;
153 CLOSE dup_rec;
154 --
155 -- go ahead and check the value of the local variable - if it's 'Y' then this
156 -- record is duplicated
157 --
158 IF (l_comb_exists = 'Y') THEN
159 hr_utility.set_message(801, 'HR_13162_PTO_DUP_INP_VAL');
160 hr_utility.raise_error;
161 END IF;
162 --
163 END DUP_INPUT_VALUE;
164
165 END PAY_NET_CALCULATION_RULES_PKG;