1 PACKAGE CN_ROLE_PMT_PLANS_PVT AUTHID CURRENT_USER AS
2 /* $Header: cnprptps.pls 120.7 2006/08/23 10:29:30 sjustina noship $ */
3
4 --
5 -- User defined Record Type
6 --
7
8 TYPE role_pmt_plan_rec_type IS RECORD
9 (
10 org_id cn_role_pmt_plans.org_id%TYPE,
11 role_pmt_plan_id cn_role_pmt_plans.role_pmt_plan_id%TYPE,
12 role_name cn_roles.name%TYPE := cn_api.G_MISS_CHAR,
13 pmt_plan_name cn_pmt_plans.name%TYPE := cn_api.G_MISS_CHAR,
14 pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE,
15 start_date cn_role_pmt_plans.start_date%TYPE := cn_api.G_MISS_DATE,
16 end_date cn_role_pmt_plans.end_date%TYPE := cn_api.G_MISS_DATE,
17 object_version_number NUMBER := null,
18 attribute_category cn_role_pmt_plans.attribute_category%TYPE := cn_api.G_MISS_CHAR,
19 attribute1 cn_role_pmt_plans.attribute1%TYPE := cn_api.G_MISS_CHAR,
20 attribute2 cn_role_pmt_plans.attribute2%TYPE := cn_api.G_MISS_CHAR,
21 attribute3 cn_role_pmt_plans.attribute3%TYPE := cn_api.G_MISS_CHAR,
22 attribute4 cn_role_pmt_plans.attribute4%TYPE := cn_api.G_MISS_CHAR,
23 attribute5 cn_role_pmt_plans.attribute5%TYPE := cn_api.G_MISS_CHAR,
24 attribute6 cn_role_pmt_plans.attribute6%TYPE := cn_api.G_MISS_CHAR,
25 attribute7 cn_role_pmt_plans.attribute7%TYPE := cn_api.G_MISS_CHAR,
26 attribute8 cn_role_pmt_plans.attribute8%TYPE := cn_api.G_MISS_CHAR,
27 attribute9 cn_role_pmt_plans.attribute9%TYPE := cn_api.G_MISS_CHAR,
28 attribute10 cn_role_pmt_plans.attribute10%TYPE := cn_api.G_MISS_CHAR,
29 attribute11 cn_role_pmt_plans.attribute11%TYPE := cn_api.G_MISS_CHAR,
30 attribute12 cn_role_pmt_plans.attribute12%TYPE := cn_api.G_MISS_CHAR,
31 attribute13 cn_role_pmt_plans.attribute13%TYPE := cn_api.G_MISS_CHAR,
32 attribute14 cn_role_pmt_plans.attribute14%TYPE := cn_api.G_MISS_CHAR,
33 attribute15 cn_role_pmt_plans.attribute15%TYPE := cn_api.G_MISS_CHAR
34 );
35
36 --
37 -- User defined Record Table Type
38 --
39 TYPE role_pmt_plan_rec_tbl_type IS TABLE OF role_pmt_plan_rec_type
40 INDEX BY BINARY_INTEGER;
41
42 --
43 -- Global variable that represent missing values.
44 --
45 G_MISS_ROLE_PMT_PLAN_REC role_pmt_plan_rec_type;
46 G_MISS_ROLE_PMT_PLAN_TBL role_pmt_plan_rec_tbl_type;
47 G_ROLE_NAME CONSTANT VARCHAR2(80)
48 := cn_api.get_lkup_meaning('ROLE_NAME','ROLE_OBJECT_TYPE');
49
50 G_PP_NAME CONSTANT VARCHAR2(80)
51 := cn_api.get_lkup_meaning('PP_NAME','PP_OBJECT_TYPE');
52
53 G_START_DATE CONSTANT VARCHAR2(80)
54 := cn_api.get_lkup_meaning('START_DATE','CN_OBJECT_TYPE');
55
56 -- Start of Comments
57 -- API name : Create_Role_Pmt_Plan
58 -- Type : Public.
59 -- Pre-reqs : None.
60 -- Usage : Procedure to create a sales role and pmt plan assignment.
61 -- Parameters :
62 -- IN : p_api_version IN NUMBER Require
63 -- : p_init_msg_list IN VARCHAR2 Optional
64 -- Default = FND_API.G_FALSE
65 -- : p_commit IN VARCHAR2 Optional
66 -- Default = FND_API.G_FALSE
67 -- : p_validation_level IN NUMBER Optional
68 -- Default = FND_API.G_VALID_LEVEL_FULL
69 -- : p_role_pmt_plan_rec IN ROLE_PMT_PLAN_REC_TYPE
70 -- OUT : x_return_status OUT VARCHAR2(1)
71 -- : x_msg_count OUT NUMBER
72 -- : x_msg_data OUT VARCHAR2(2000)
73 -- : x_loading_status OUT VARCHAR2
74 --
75 --
76 -- Version : Current version 1.0
77 -- Initial version 1.0
78 --
79 -- Description : This procedure is used to create a sales role and pmt plan assignment.
80 -- Notes : 1. Role name can not be missing or null.
81 -- 2. Pmt plan name can not be missing or null.
82 -- 3. Start_date can not be missing or null.
83 -- 4. Start_date <= end_date, if end_date is not null.
84 -- 5. Role name must exist in cn_roles already.
85 -- 6. Pmt_plan_name must exist in cn_pmt_plans already.
86 -- 7. Date range (start_date, en_date) of the assignment must be
87 -- within the date range (start_date, end_date) of the pmt plan.
88 -- 8. No pmt plan with the same payment_group_code overlap for any same sales role.
89 -- In other words, you can not have more than one pmt plan for
90 -- the same role with the same payment group code at the same time.
91 -- 9. Gap between two payment plans for the same role is allowed.
92 -- End of comments
93
94
95 PROCEDURE Create_Role_Pmt_Plan
96 ( p_api_version IN NUMBER ,
97 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
98 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
99 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
100 x_return_status OUT NOCOPY VARCHAR2 ,
101 x_loading_status OUT NOCOPY VARCHAR2 ,
102 x_msg_count OUT NOCOPY NUMBER ,
103 x_msg_data OUT NOCOPY VARCHAR2 ,
104 p_role_pmt_plan_rec IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
105 );
106
107 -- Start of Comments
108 -- API name : Update_Role_Pmt_Plan
109 -- Type : Public.
110 -- Pre-reqs : None.
111 -- Usage : Procedure to update a sales role and pmt plan assignment.
112 -- Parameters :
113 -- IN : p_api_version IN NUMBER Require
114 -- : p_init_msg_list IN VARCHAR2 Optional
115 -- Default = FND_API.G_FALSE
116 -- : p_commit IN VARCHAR2 Optional
117 -- Default = FND_API.G_FALSE
118 -- : p_validation_level IN NUMBER Optional
119 -- Default = FND_API.G_VALID_LEVEL_FULL
120 -- : p_role_pmt_plan_rec_old IN ROLE_PMT_PLAN_REC_TYPE
121 -- : p_role_pmt_plan_rec_new IN ROLE_PMT_PLAN_REC_TYPE
122 -- OUT : x_return_status OUT VARCHAR2(1)
123 -- : x_msg_count OUT NUMBER
124 -- : x_msg_data OUT VARCHAR2(2000)
125 -- : x_loading_status OUT VARCHAR2
126 --
127 --
128 -- Version : Current version 1.0
129 -- Initial version 1.0
130 --
131 -- Description : This procedure is used to update a sales role and pmt plan assignment.
132 -- Notes : 1. Old role_pmt_plan_id must be found in cn_role_pmt_plans.
133 -- 2. New role name can not be null.
134 -- 3. New pmt plan name can not be null.
135 -- 4. New start date can not be null.
136 -- 5. New start date <= new end date if new end date is not null.
137 -- 6. New role_name must exist in cn_roles already.
138 -- 8. New pmt plan name must exist in cn_pmt_plans already.
139 -- 9. The new date range (start_date, end_date) of the assignment must be
140 -- within the date range (start_date, end_date) of the pmt plan.
141 -- 10. No pmt plan overlap with same payment group code for any same sales role.
142 -- In other words, you can not have more than one pmt plan with the same payment group code for
143 -- the same role at the same time.
144 -- 11. Gap between two pmt plans for the same role is allowed.
145 -- End of comments
146
147
148 PROCEDURE Update_Role_Pmt_Plan
149 ( p_api_version IN NUMBER ,
150 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
151 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
152 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
153 x_return_status OUT NOCOPY VARCHAR2 ,
154 x_loading_status OUT NOCOPY VARCHAR2 ,
155 x_msg_count OUT NOCOPY NUMBER ,
156 x_msg_data OUT NOCOPY VARCHAR2 ,
157 p_role_pmt_plan_rec_old IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC,
158 p_ovn IN cn_role_pmt_plans.object_version_number%TYPE,
159 p_role_pmt_plan_rec_new IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
160 );
161
162
163
164 -- Start of Comments
165 -- API name : Delete_Role_Pmt_Plan
166 -- Type : Public.
167 -- Pre-reqs : None.
168 -- Usage : Procedure to delete a sales role and pmt plan assignment.
169 -- Parameters :
170 -- IN : p_api_version IN NUMBER Require
171 -- : p_init_msg_list IN VARCHAR2 Optional
172 -- Default = FND_API.G_FALSE
173 -- : p_commit IN VARCHAR2 Optional
174 -- Default = FND_API.G_FALSE
175 -- : p_validation_level IN NUMBER Optional
176 -- Default = FND_API.G_VALID_LEVEL_FULL
177 -- p_role_pmt_plan_rec IN ROLE_PMT_PLAN_REC_TYPE
178 -- OUT : x_return_status OUT VARCHAR2(1)
179 -- : x_msg_count OUT NUMBER
180 -- : x_msg_data OUT VARCHAR2(2000)
181 -- : x_loading_status OUT VARCHAR2
182 --
183 --
184 -- Version : Current version 1.0
185 -- Initial version 1.0
186 --
187 -- Description : This procedure is used to delete a sales role and comp plan assignment.
188 -- Notes : 1. the old p_role_plan_id must be found based on the
189 -- parameters passed in.
190 -- End of comments
191
192
193 PROCEDURE Delete_Role_Pmt_Plan
194 ( p_api_version IN NUMBER ,
195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
197 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
198 x_return_status OUT NOCOPY VARCHAR2 ,
199 x_loading_status OUT NOCOPY VARCHAR2 ,
200 x_msg_count OUT NOCOPY NUMBER ,
201 x_msg_data OUT NOCOPY VARCHAR2 ,
202 p_role_pmt_plan_rec IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
203 );
204
205 FUNCTION date_range_overlap
206 (
207 a_start_date DATE,
208 a_end_date DATE,
209 b_start_date DATE,
210 b_end_date DATE
211 ) RETURN NUMBER;
212
213 FUNCTION date_range_diff_present
214 (
215 a_start_date DATE,
216 a_end_date DATE,
217 b_start_date DATE,
218 b_end_date DATE
219 ) RETURN NUMBER;
220
221 FUNCTION date_range_intersect
222 (
223 a_start_date DATE,
224 a_end_date DATE,
225 b_start_date DATE,
226 b_end_date DATE
227 ) RETURN NUMBER;
228
229 END CN_ROLE_PMT_PLANS_PVT;
[Home] [Help]