[Home] [Help]
PACKAGE BODY: APPS.PAY_US_GARN_FEE_RULES_F_PKG
Source
1 PACKAGE BODY PAY_US_GARN_FEE_RULES_F_PKG as
2 /* $Header: pygfr01t.pkb 115.1.1150.1 2000/02/10 15:53:37 pkm ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Fee_Rule_Id IN OUT NUMBER,
6 X_Effective_Start_Date DATE,
7 X_Effective_End_Date DATE,
8 X_Garn_Category VARCHAR2,
9 X_State_Code VARCHAR2,
10 X_Addl_Garn_Fee_Amount NUMBER,
11 X_Correspondence_Fee NUMBER,
12 X_Creator_Type VARCHAR2,
13 X_Fee_Amount NUMBER,
14 X_Fee_Rule VARCHAR2,
15 X_Max_Fee_Amount NUMBER,
16 X_Pct_Current NUMBER,
17 X_Take_Fee_On_Proration VARCHAR2,
18 X_Last_Update_Date DATE,
19 X_Last_Updated_By NUMBER,
20 X_Last_Update_Login NUMBER,
21 X_Created_By NUMBER,
22 X_Creation_Date DATE
23 ) IS
24 CURSOR C IS SELECT rowid FROM PAY_US_GARN_FEE_RULES_F
25 WHERE fee_rule_id = X_Fee_Rule_Id;
26 CURSOR C2 IS SELECT pay_us_garn_fee_rules_s.nextval FROM sys.dual;
27 BEGIN
28 if (X_Fee_Rule_Id is NULL) then
29 OPEN C2;
30 FETCH C2 INTO X_Fee_Rule_Id;
31 CLOSE C2;
32 end if;
33
34 INSERT INTO PAY_US_GARN_FEE_RULES_F(
35
36 fee_rule_id,
37 effective_start_date,
38 effective_end_date,
39 garn_category,
40 state_code,
41 addl_garn_fee_amount,
42 correspondence_fee,
43 creator_type,
44 fee_amount,
45 fee_rule,
46 max_fee_amount,
47 pct_current,
48 take_fee_on_proration,
49 last_update_date,
50 last_updated_by,
51 last_update_login,
52 created_by,
53 creation_date
54 ) VALUES (
55
56 X_Fee_Rule_Id,
57 X_Effective_Start_Date,
58 X_Effective_End_Date,
59 X_Garn_Category,
60 X_State_Code,
61 X_Addl_Garn_Fee_Amount,
62 X_Correspondence_Fee,
63 X_Creator_Type,
64 X_Fee_Amount,
65 X_Fee_Rule,
66 X_Max_Fee_Amount,
67 X_Pct_Current,
68 X_Take_Fee_On_Proration,
69 X_Last_Update_Date,
70 X_Last_Updated_By,
71 X_Last_Update_Login,
72 X_Created_By,
73 X_Creation_Date
74
75 );
76
77 OPEN C;
78 FETCH C INTO X_Rowid;
79 if (C%NOTFOUND) then
80 CLOSE C;
81 Raise NO_DATA_FOUND;
82 end if;
83 CLOSE C;
84 END Insert_Row;
85
86
87 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
88 X_Fee_Rule_Id NUMBER,
89 X_Effective_Start_Date DATE,
90 X_Effective_End_Date DATE,
91 X_Garn_Category VARCHAR2,
92 X_State_Code VARCHAR2,
93 X_Addl_Garn_Fee_Amount NUMBER,
94 X_Correspondence_Fee NUMBER,
95 X_Creator_Type VARCHAR2,
96 X_Fee_Amount NUMBER,
97 X_Fee_Rule VARCHAR2,
98 X_Max_Fee_Amount NUMBER,
99 X_Pct_Current NUMBER,
100 X_Take_Fee_On_Proration VARCHAR2
101 ) IS
102 CURSOR C IS
103 SELECT *
104 FROM PAY_US_GARN_FEE_RULES_F
105 WHERE rowid = X_Rowid
106 FOR UPDATE of Fee_Rule_Id NOWAIT;
107 Recinfo C%ROWTYPE;
108
109
110 BEGIN
111 OPEN C;
112 FETCH C INTO Recinfo;
113 if (C%NOTFOUND) then
114 CLOSE C;
115 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
116 APP_EXCEPTION.Raise_Exception;
117 end if;
118 CLOSE C;
119 if (
120
121 (Recinfo.fee_rule_id = X_Fee_Rule_Id)
122 AND (Recinfo.effective_start_date = X_Effective_Start_Date)
123 AND (Recinfo.effective_end_date = X_Effective_End_Date)
124 AND (Recinfo.garn_category = X_Garn_Category)
125 AND (Recinfo.state_code = X_State_Code)
126 AND ( (Recinfo.addl_garn_fee_amount = X_Addl_Garn_Fee_Amount)
127 OR ( (Recinfo.addl_garn_fee_amount IS NULL)
128 AND (X_Addl_Garn_Fee_Amount IS NULL)))
129 AND ( (Recinfo.correspondence_fee = X_Correspondence_Fee)
130 OR ( (Recinfo.correspondence_fee IS NULL)
131 AND (X_Correspondence_Fee IS NULL)))
132 AND ( (Recinfo.creator_type = X_Creator_Type)
133 OR ( (Recinfo.creator_type IS NULL)
134 AND (X_Creator_Type IS NULL)))
135 AND ( (Recinfo.fee_amount = X_Fee_Amount)
136 OR ( (Recinfo.fee_amount IS NULL)
137 AND (X_Fee_Amount IS NULL)))
138 AND ( (Recinfo.fee_rule = X_Fee_Rule)
139 OR ( (Recinfo.fee_rule IS NULL)
140 AND (X_Fee_Rule IS NULL)))
141 AND ( (Recinfo.max_fee_amount = X_Max_Fee_Amount)
142 OR ( (Recinfo.max_fee_amount IS NULL)
143 AND (X_Max_Fee_Amount IS NULL)))
144 AND ( (Recinfo.pct_current = X_Pct_Current)
145 OR ( (Recinfo.pct_current IS NULL)
146 AND (X_Pct_Current IS NULL)))
147 AND ( (Recinfo.take_fee_on_proration = X_Take_Fee_On_Proration)
148 OR ( (Recinfo.take_fee_on_proration IS NULL)
149 AND (X_take_fee_on_proration IS NULL)))
150 ) then
151 return;
152 else
153 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
154 APP_EXCEPTION.Raise_Exception;
155 end if;
156 END Lock_Row;
157
158
159
160 PROCEDURE Update_Row(X_Rowid VARCHAR2,
161 X_Fee_Rule_Id NUMBER,
162 X_Effective_Start_Date DATE,
163 X_Effective_End_Date DATE,
164 X_Garn_Category VARCHAR2,
165 X_State_Code VARCHAR2,
166 X_Addl_Garn_Fee_Amount NUMBER,
167 X_Correspondence_Fee NUMBER,
168 X_Creator_Type VARCHAR2,
169 X_Fee_Amount NUMBER,
170 X_Fee_Rule VARCHAR2,
171 X_Max_Fee_Amount NUMBER,
172 X_Pct_Current NUMBER,
173 X_Take_Fee_On_Proration VARCHAR2,
174 X_Last_Update_Date DATE,
175 X_Last_Updated_By NUMBER,
176 X_Last_Update_Login NUMBER
177
178 ) IS
179 BEGIN
180 UPDATE PAY_US_GARN_FEE_RULES_F
181 SET
182 fee_rule_id = X_Fee_Rule_Id,
183 effective_start_date = X_Effective_Start_Date,
184 effective_end_date = X_Effective_End_Date,
185 garn_category = X_Garn_Category,
186 state_code = X_State_Code,
187 addl_garn_fee_amount = X_Addl_Garn_Fee_Amount,
188 correspondence_fee = X_Correspondence_Fee,
189 creator_type = X_Creator_Type,
190 fee_amount = X_Fee_Amount,
191 fee_rule = X_Fee_Rule,
192 max_fee_amount = X_Max_Fee_Amount,
193 pct_current = X_Pct_Current,
194 take_fee_on_proration = X_Take_Fee_On_Proration,
195 last_update_date = X_Last_Update_Date,
196 last_updated_by = X_Last_Updated_By,
197 last_update_login = X_Last_Update_Login
198 WHERE rowid = X_Rowid;
199
200 if (SQL%NOTFOUND) then
201 Raise NO_DATA_FOUND;
202 end if;
203 END Update_Row;
204 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
205 BEGIN
206 DELETE FROM PAY_US_GARN_FEE_RULES_F
207 WHERE rowid = X_Rowid;
208
209 if (SQL%NOTFOUND) then
210 Raise NO_DATA_FOUND;
211 end if;
212 END Delete_Row;
213
214 PROCEDURE Check_Unique( X_State_Code VARCHAR2,
215 X_Garn_Category VARCHAR2
216 ) IS
217 DUMMY NUMBER;
218
219 BEGIN
220
221 SELECT count(1)
222 INTO DUMMY
223 FROM pay_us_garn_fee_rules_f
224 WHERE state_code = X_State_Code
225 AND GARN_CATEGORY = X_Garn_Category;
226
227 IF (DUMMY >= 1) then
228 hr_utility.set_message(801, 'PAY_51332_GFR_CHECK_UNIQUE');
229 hr_utility.raise_error;
230 END IF;
231
232 END check_unique;
233
234
235 END PAY_US_GARN_FEE_RULES_F_PKG;