4 * The procedures in this package can be used to get pay group information, validate the input, create pay groups, update pay groups, and delete pay groups.
1 PACKAGE CN_PAYGROUP_PUB AUTHID CURRENT_USER as
2 -- $Header: cnppgrps.pls 120.8 2005/11/02 22:50:00 sjustina ship $ --+
3 /*#
5 * They are also used to create entry into cn_pay_groups and to update salesrep pay group assignment.
6 * @rep:scope public
7 * @rep:product CN
8 * @rep:displayname Pay Group
9 * @rep:lifecycle active
10 * @rep:compatibility S
11 * @rep:category BUSINESS_ENTITY CN_COMP_PLANS
12 */
13
14 /**Start of MOAC Org Validation change */
15 l_org_id NUMBER;
16 l_status VARCHAR2(1);
17 /**End of MOAC Org Validation change */
18
19 TYPE PayGroup_rec_type IS RECORD
20 ( pay_group_id cn_pay_groups.pay_group_id%TYPE := CN_API.G_MISS_ID,
21 name cn_pay_groups.name%TYPE,
22 period_set_name cn_pay_groups.period_set_name%TYPE,
23 period_type cn_pay_groups.period_type%TYPE,
24 start_date cn_pay_groups.start_date%TYPE,
25 end_date cn_pay_groups.end_date%TYPE,
26 pay_group_description cn_pay_groups.pay_group_description%TYPE := NULL,
27 attribute_category cn_pay_groups.attribute_category%TYPE := NULL,
28 attribute1 cn_pay_groups.attribute1%TYPE := NULL,
29 attribute2 cn_pay_groups.attribute2%TYPE := NULL,
30 attribute3 cn_pay_groups.attribute3%TYPE := NULL,
31 attribute4 cn_pay_groups.attribute4%TYPE := NULL,
32 attribute5 cn_pay_groups.attribute5%TYPE := NULL,
33 attribute6 cn_pay_groups.attribute6%TYPE := NULL,
34 attribute7 cn_pay_groups.attribute7%TYPE := NULL,
35 attribute8 cn_pay_groups.attribute8%TYPE := NULL,
36 attribute9 cn_pay_groups.attribute9%TYPE := NULL,
37 attribute10 cn_pay_groups.attribute10%TYPE := NULL,
38 attribute11 cn_pay_groups.attribute11%TYPE := NULL,
39 attribute12 cn_pay_groups.attribute12%TYPE := NULL,
40 attribute13 cn_pay_groups.attribute13%TYPE := NULL,
41 attribute14 cn_pay_groups.attribute14%TYPE := NULL,
42 attribute15 cn_pay_groups.attribute15%TYPE := NULL,
43 object_version_number cn_pay_groups.object_version_number%TYPE := NULL,
44 org_id cn_pay_groups.org_id%TYPE := NULL
45 );
46
47
48 TYPE PayGroup_tbl_type IS
49 TABLE OF PayGroup_rec_type INDEX BY BINARY_INTEGER ;
50
51
52 G_MISS_PAYGROUP_REC PayGroup_rec_type;
53
54 G_MISS_PAYGROUP_REC_TB PayGroup_tbl_type;
55
56 /*#
57 * This procedure gets the pay group information.
58 * @param p_api_version API Version
59 * @param p_init_msg_list Initialize Message List
60 * @param p_commit Commit after create
61 * @param p_validation_level Validation Level
62 * @param x_return_status Status of the create operation
63 * @param x_msg_count Number of error messages returned
64 * @param x_msg_data Error messages
65 * @param p_start_record return search results starting from this row
66 * @param p_fetch_size number or rows fetched
67 * @param p_search_name pay group name to be searched with
68 * @param p_search_start_date start date to be searched with
69 * @param p_search_end_date end date to be searched with
70 * @param p_search_period_set_name Period set name to be searched with
71 * @param x_pay_group Record of type PayGroup_tbl_type that contains matched pay group information from the search criteria
72 * @param x_total_record total number of rows returned from the search criteria
73 * @param p_org_id Org Identitifier
74 * @rep:scope public
75 * @rep:lifecycle active
79 (p_api_version IN NUMBER,
76 * @rep:displayname Get pay groups
77 */
78 PROCEDURE Get_Pay_Group_Sum
80 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
81 p_commit IN VARCHAR2 := FND_API.G_FALSE,
82 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
83 p_start_record IN NUMBER := -1,
84 p_fetch_size IN NUMBER := -1,
85 p_search_name IN VARCHAR2 := '%',
86 p_search_start_date IN DATE := FND_API.G_MISS_DATE,
87 p_search_end_date IN DATE := FND_API.G_MISS_DATE,
88 p_search_period_set_name IN VARCHAR2 := '%',
89 x_pay_group OUT NOCOPY PayGroup_tbl_type,
90 x_total_record OUT NOCOPY NUMBER,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_org_id IN NUMBER := NULL
95 );
96
97 -------------------------------------------------------------------------------------+
98 -- Start of comments
99 -- API name : Create_PayGroup
100 -- Type : Public.
101 -- Pre-reqs : None.
102 -- Usage : Used to create pay groups
103
104 -- Desc : This procedure will validate the input for a pay group
105 -- and create one if all validations are passed.
106
107 -- Parameters :
108 -- IN : p_api_version IN NUMBER Required
109 -- p_init_msg_list IN VARCHAR2 Optional
110 -- Default = FND_API.G_FALSE
111 -- p_commit IN VARCHAR2 Optional
112 -- Default = FND_API.G_FALSE
113 -- p_validation_level IN NUMBER Optional
114 -- Default = FND_API.G_VALID_LEVEL_FULL
115 -- OUT : x_return_status OUT VARCHAR2(1)
116 -- x_msg_count OUT NUMBER
117 -- x_msg_data OUT VARCHAR2(2000)
118 -- IN : p_PayGroup_rec IN PayGroup_rec
119
120 -- OUT : x_loading_status OUT VARCHAR2(50)
121 -- Detailed error code returned from procedure.
122
123 -- OUT : x_status OUT VARCHAR2(50)
124 -- Return Sql Statement Status ( VALID/INVALID)
125
126 -- Version : Current version 1.0
127 -- Initial version 1.0
128
129 -- Notes : The following validations are performed by this API
130 -- Name should not be null
131 -- Period set name should not be null
132 -- Period Type should not be null
133 -- Start date should not be null
134 -- End date should not be null
135 -- Start date should be less than end date
136 -- Name, start date and end date should be unique
137 -- Period set should be valid
138 -- Period type should be valid
139
140 -- End of comments
141 -------------------------------------------------------------------------------------+
142 /*#
143 * This procedure validates the input for a pay group and creates one if all validations are passed.
144 * @param p_api_version API Version
145 * @param p_init_msg_list Initialize Message List
146 * @param p_commit Commit after create
147 * @param p_validation_level Validation Level
148 * @param x_return_status Status of the create operation
149 * @param x_msg_count Number of error messages returned
150 * @param x_msg_data Error messages
151 * @param x_status Status
152 * @param x_loading_status Status
153 * @param p_PayGroup_rec Record of type PayGroup_rec_type that stores the data associated with pay group
154 * @rep:scope public
155 * @rep:lifecycle active
156 * @rep:displayname Create pay group
157 */
158 PROCEDURE Create_PayGroup
159 ( p_api_version IN NUMBER,
160 p_init_msg_list IN VARCHAR2,
161 p_commit IN VARCHAR2,
162 p_validation_level IN NUMBER,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 p_PayGroup_rec IN OUT NOCOPY PayGroup_rec_type,
167 x_loading_status OUT NOCOPY VARCHAR2,
168 x_status OUT NOCOPY VARCHAR2
169 );
170 ------------------------------------------------------------------------------------+
171 -- Start of comments
172 -- API name : Update_PayGroup
173 -- Type : Public.
174 -- Pre-reqs : None.
175 -- Usage : Used to update pay groups
176 -- Desc : Procedure to update pay groups
177 -- Parameters :
178 -- IN : p_api_version IN NUMBER Require
179 -- p_init_msg_list IN VARCHAR2 Optional
180 -- Default = FND_API.G_FALSE
181 -- p_commit IN VARCHAR2 Optional
182 -- Default = FND_API.G_FALSE
183 -- p_validation_level IN NUMBER Optional
184 -- Default = FND_API.G_VALID_LEVEL_FULL
185 -- OUT : x_return_status OUT VARCHAR2(1)
186 -- x_msg_count OUT NUMBER
187 -- x_msg_data OUT VARCHAR2(2000)
188 -- IN : p_old_PayGroup_rec IN PayGroup_rec
189 -- IN : p_PayGroup_rec IN PayGroup_rec
190
191 -- OUT : x_loading_status OUT
192 -- Detailed Error Message
193 -- OUT : x_status OUT
194 -- RETURN SQL Status
195 -- Version : Current version 1.0
196 -- Initial version 1.0
197 -- Notes : The following are the validations performed by this api
198 -- Start date cannot be updated to null
199 -- End date cannot be updated to null
200 -- Start date should be less than end date
201 -- The pay group to be updated should exist
205 -- If period type is specified, then it should not be null
202 -- Pay group effectivity cannot be shrunk to be less than assignments
203 -- Pay group name, st date and end date cannot be null
204 -- If period_set is specified, then it should not be null
206 -- End of comments
207 -------------------------------------------------------------------------------------+
208 /*#
209 * This API is used to update pay groups.
210 * @param p_api_version API Version
211 * @param p_init_msg_list Initialize Message List
212 * @param p_commit Commit after update
213 * @param p_validation_level Validation Level
214 * @param x_return_status Status of the update operation
215 * @param x_msg_count Number of error messages returned
216 * @param x_msg_data Error messages
217 * @param x_status Status
218 * @param x_loading_status Status
219 * @param p_old_Paygroup_rec Record of type PayGroup_rec_type that stores the old data associated with pay group
220 * @param p_PayGroup_rec Record of type PayGroup_rec_type that stores the updated data associated with pay group
221 * @rep:scope public
222 * @rep:lifecycle active
223 * @rep:displayname Update pay group
224 */
225 PROCEDURE Update_PayGroup
226 ( p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2,
228 p_commit IN VARCHAR2,
229 p_validation_level IN NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 p_old_Paygroup_rec IN PayGroup_rec_type,
234 p_PayGroup_rec IN OUT NOCOPY PayGroup_rec_type,
235 x_status OUT NOCOPY VARCHAR2,
236 x_loading_status OUT NOCOPY VARCHAR2
237 ) ;
238
239 --------------------------------------------------------------------------------------+
240 -- Start of comments
241 -- API name : Delete_PayGroup
242 -- Type : Public.
243 -- Pre-reqs : None.
244 -- Usage : Delete
245 -- Desc : Procedure to Delete Pay Groups
246 -- Parameters :
247 -- IN : p_api_version IN NUMBER Require
248 -- p_init_msg_list IN VARCHAR2 Optional
249 -- Default = FND_API.G_FALSE
250 -- p_commit IN VARCHAR2 Optional
251 -- Default = FND_API.G_FALSE
252 -- p_validation_level IN NUMBER Optional
253 -- Default = FND_API.G_VALID_LEVEL_FULL
254 -- OUT : x_return_status OUT VARCHAR2(1)
255 -- x_msg_count OUT NUMBER
256 -- x_msg_data OUT VARCHAR2(2000)
257 -- IN : p_PayGroup_rec IN PayGroup_rec_type
258 -- OUT : x_loading_status OUT
259 -- Detailed Error Message
260 -- Version : Current version 1.0
261 -- Initial version 1.0
262 -- Notes : The following validations are done by this API
263 -- Pay group to be deleted must exist
264 -- End of comments
265 --------------------------------------------------------------------------------------+
266 /*#
267 * This API is used to delete pay groups.
268 * @param p_api_version API Version
269 * @param p_init_msg_list Initialize Message List
270 * @param p_commit Commit after delete
271 * @param p_validation_level Validation Level
272 * @param x_return_status Status of the delete operation
273 * @param x_msg_count Number of error messages returned
274 * @param x_msg_data Error messages
275 * @param x_status Status
276 * @param x_loading_status Status
277 * @param p_PayGroup_rec Record of type PayGroup_rec_type that stores the data associated with pay group
278 * @rep:scope public
279 * @rep:lifecycle active
280 * @rep:displayname Delete pay group
281 */
282 PROCEDURE Delete_PayGroup
283 ( p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2,
285 p_commit IN VARCHAR2,
286 p_validation_level IN NUMBER,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2,
290 p_PayGroup_rec IN OUT NOCOPY PayGroup_rec_type ,
291 x_status OUT NOCOPY VARCHAR2,
292 x_loading_status OUT NOCOPY VARCHAR2
293 ) ;
294
295
296 TYPE PayGroup_assign_rec IS RECORD
297 ( employee_type cn_salesreps.type%TYPE,
298 employee_number cn_salesreps.employee_number%TYPE,
299 assignment_start_date cn_srp_pay_groups.start_date%TYPE,
300 assignment_end_date cn_srp_pay_groups.end_date%TYPE,
301 attribute_category cn_srp_pay_groups.attribute_category%TYPE := NULL,
302 attribute1 cn_srp_pay_groups.attribute1%TYPE := NULL,
303 attribute2 cn_srp_pay_groups.attribute2%TYPE := NULL,
304 attribute3 cn_srp_pay_groups.attribute3%TYPE := NULL,
305 attribute4 cn_srp_pay_groups.attribute4%TYPE := NULL,
306 attribute5 cn_srp_pay_groups.attribute5%TYPE := NULL,
307 attribute6 cn_srp_pay_groups.attribute6%TYPE := NULL,
308 attribute7 cn_srp_pay_groups.attribute7%TYPE := NULL,
309 attribute8 cn_srp_pay_groups.attribute8%TYPE := NULL,
310 attribute9 cn_srp_pay_groups.attribute9%TYPE := NULL,
311 attribute10 cn_srp_pay_groups.attribute10%TYPE := NULL,
312 attribute11 cn_srp_pay_groups.attribute11%TYPE := NULL,
313 attribute12 cn_srp_pay_groups.attribute12%TYPE := NULL,
314 attribute13 cn_srp_pay_groups.attribute13%TYPE := NULL,
315 attribute14 cn_srp_pay_groups.attribute14%TYPE := NULL,
316 attribute15 cn_srp_pay_groups.attribute15%TYPE := NULL );
317
318 END CN_PAYGROUP_PUB ;
[Home] [Help]