1 PACKAGE CN_PMTPLAN_PUB AUTHID CURRENT_USER as
2 -- $Header: cnppplns.pls 120.4 2005/11/02 05:37:09 sjustina ship $
3
4 TYPE PmtPlan_rec_type IS RECORD
5 ( org_id cn_pmt_plans.org_id%TYPE,
6 name cn_pmt_plans.name%TYPE,
7 minimum_amount cn_pmt_plans.minimum_amount%TYPE,
8 maximum_amount cn_pmt_plans.maximum_amount%TYPE,
9 min_rec_flag cn_pmt_plans.min_rec_flag%TYPE,
10 max_rec_flag cn_pmt_plans.max_rec_flag%TYPE,
11 max_recovery_amount cn_pmt_plans.max_recovery_amount%TYPE,
12 credit_type_name cn_credit_types.name%TYPE,
13 pay_interval_type_name cn_interval_types.name%TYPE,
14 start_date cn_pmt_plans.start_date%TYPE,
15 end_date cn_pmt_plans.end_date%TYPE,
16 object_version_number NUMBER := null,
17 recoverable_interval_type cn_interval_types_all_tl.name%TYPE,
18 pay_against_commission cn_pmt_plans.pay_against_commission%TYPE,
19 attribute_category cn_pmt_plans.attribute_category%TYPE := NULL,
20 attribute1 cn_pmt_plans.attribute1%TYPE := NULL,
21 attribute2 cn_pmt_plans.attribute2%TYPE := NULL,
22 attribute3 cn_pmt_plans.attribute3%TYPE := NULL,
23 attribute4 cn_pmt_plans.attribute4%TYPE := NULL,
24 attribute5 cn_pmt_plans.attribute5%TYPE := NULL,
25 attribute6 cn_pmt_plans.attribute6%TYPE := NULL,
26 attribute7 cn_pmt_plans.attribute7%TYPE := NULL,
27 attribute8 cn_pmt_plans.attribute8%TYPE := NULL,
28 attribute9 cn_pmt_plans.attribute9%TYPE := NULL,
29 attribute10 cn_pmt_plans.attribute10%TYPE := NULL,
30 attribute11 cn_pmt_plans.attribute11%TYPE := NULL,
31 attribute12 cn_pmt_plans.attribute12%TYPE := NULL,
32 attribute13 cn_pmt_plans.attribute13%TYPE := NULL,
33 attribute14 cn_pmt_plans.attribute14%TYPE := NULL,
34 attribute15 cn_pmt_plans.attribute15%TYPE := NULL,
35 payment_group_code cn_pmt_plans.payment_group_code%TYPE := NULL
36 );
37
38
39 /*
40 TYPE PmtPlan_rec_type IS RECORD
41 ( name cn_pmt_plans.name%TYPE := fnd_api.g_miss_char,
42 minimum_amount cn_pmt_plans.minimum_amount%TYPE := fnd_api.g_miss_num,
43 maximum_amount cn_pmt_plans.maximum_amount%TYPE := fnd_api.g_miss_num,
44 min_rec_flag cn_pmt_plans.min_rec_flag%TYPE := fnd_api.g_miss_char,
45 max_rec_flag cn_pmt_plans.max_rec_flag%TYPE := fnd_api.g_miss_char,
46 max_recovery_amount cn_pmt_plans.max_recovery_amount%TYPE := fnd_api.g_miss_num,
47 credit_type_name cn_credit_types.name%TYPE := fnd_api.g_miss_num,
48 pay_interval_type_name cn_interval_types.name%TYPE := fnd_api.g_miss_char,
49 start_date cn_pmt_plans.start_date%TYPE := fnd_api.g_miss_date,
50 end_date cn_pmt_plans.end_date%TYPE := fnd_api.g_miss_date,
51 attribute_category cn_pmt_plans.attribute_category%TYPE := NULL,
52 attribute1 cn_pmt_plans.attribute1%TYPE := NULL,
53 attribute2 cn_pmt_plans.attribute2%TYPE := NULL,
54 attribute3 cn_pmt_plans.attribute3%TYPE := NULL,
55 attribute4 cn_pmt_plans.attribute4%TYPE := NULL,
56 attribute5 cn_pmt_plans.attribute5%TYPE := NULL,
57 attribute6 cn_pmt_plans.attribute6%TYPE := NULL,
58 attribute7 cn_pmt_plans.attribute7%TYPE := NULL,
59 attribute8 cn_pmt_plans.attribute8%TYPE := NULL,
60 attribute9 cn_pmt_plans.attribute9%TYPE := NULL,
61 attribute10 cn_pmt_plans.attribute10%TYPE := NULL,
62 attribute11 cn_pmt_plans.attribute11%TYPE := NULL,
63 attribute12 cn_pmt_plans.attribute12%TYPE := NULL,
64 attribute13 cn_pmt_plans.attribute13%TYPE := NULL,
65 attribute14 cn_pmt_plans.attribute14%TYPE := NULL,
66 attribute15 cn_pmt_plans.attribute15%TYPE := NULL
67 );
68 */
69
70 g_mode VARCHAR2(30); --global to indicate if operation is insert/update
71
72 ------------------------------------------------------------------------------+
73 -- Start of comments
74 -- API name : Create_PmtPlan
75 -- Type : Public.
76 -- Pre-reqs : None.
77 -- Usage : Used to create a new payment plan
78 --
79 -- Desc : This procedure will validate the input for a payment plan
80 -- and create one if all validations are passed.
81 --
82 -- Parameters :
83 -- IN : p_api_version IN NUMBER Required
84 -- p_init_msg_list IN VARCHAR2 Optional
85 -- Default = FND_API.G_FALSE
86 -- p_commit IN VARCHAR2 Optional
87 -- Default = FND_API.G_FALSE
88 -- p_validation_level IN NUMBER Optional
89 -- Default = FND_API.G_VALID_LEVEL_FULL
90 -- OUT : x_return_status OUT VARCHAR2(1)
91 -- x_msg_count OUT NUMBER
92 -- x_msg_data OUT VARCHAR2(2000)
93 -- IN : p_pmt_plan_rec IN PmtPlan_rec_type
94 -- OUT : x_loading_status OUT VARCHAR2(50)
95 -- Detailed error code returned from procedure.
96 --
97 -- OUT : x_status OUT VARCHAR2(50)
98 -- Return Sql Statement Status ( VALID/INVALID)
99 --
100 -- Version : Current version 1.0
101 -- Initial version 1.0
102 --
103 -- Notes : Validations in the API are the following :
104 -- Start date less than end date
105 -- Name not missing or null
109 -- Max_rec_flag should be 'Y', 'N' or null
106 -- Credit type not missing or null
107 -- Start date not null
108 -- Min_rec_flag should be 'Y', 'N' or null
110 -- Payment plan should be unique
111 -- Credit Type should be valid
112 -- Pay intervval should be valid 'PERIOD', 'QUARTER', 'YEAR' or null
113 --
114 -- End of comments
115 --------------------------------------------------------------------------+
116 PROCEDURE Create_PmtPlan
117 ( p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2,
119 p_commit IN VARCHAR2,
120 p_validation_level IN NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_rec_type,
125 x_loading_status OUT NOCOPY VARCHAR2,
126 x_status OUT NOCOPY VARCHAR2
127 );
128 /*
129 PROCEDURE Create_PmtPlan
130 ( p_api_version IN NUMBER,
131 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
132 p_commit IN VARCHAR2 := FND_API.G_FALSE,
133 p_validation_level IN NUMBER :=
134 FND_API.G_VALID_LEVEL_FULL,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2,
138 p_PmtPlan_rec IN PmtPlan_rec_type,
139 x_loading_status OUT NOCOPY VARCHAR2,
140 x_status OUT NOCOPY VARCHAR2
141 );
142 */
143 --------------------------------------------------------------------------------------+
144 --Start of comments
145 -- API name : Update_PmtPlan
146 -- Type : Public.
147 -- Pre-reqs : Payment plan must exist
148 -- Usage : Used to update payment plans
149 -- Desc : Procedure to update payment plans
150 -- Parameters :
151 -- IN : p_api_version IN NUMBER Require
152 -- p_init_msg_list IN VARCHAR2 Optional
153 -- Default = FND_API.G_FALSE
154 -- p_commit IN VARCHAR2 Optional
155 -- Default = FND_API.G_FALSE
156 -- p_validation_level IN NUMBER Optional
157 -- Default = FND_API.G_VALID_LEVEL_FULL
158 -- OUT : x_return_status OUT VARCHAR2(1)
159 -- x_msg_count OUT NUMBER
160 -- x_msg_data OUT VARCHAR2(2000)
161 -- IN : p_old_pmt_plan_rec IN PmtPlan_rec_type
162 -- IN : p_pmt_plan_rec IN PmtPlan_rec_type
163 --
164 -- OUT : x_loading_status OUT
165 -- Detailed Error Message
166 -- OUT : x_status OUT
167 -- RETURN SQL Status
168 -- Version : Current version 1.0
169 -- Initial version 1.0
170 -- Notes : Validations in the api are the following:
171 -- Start date cannot be updated to null
172 -- If end date is specified, it cannot be less than start date
173 -- The payment plan to be updated should already exist
174 -- If pmt plan has been assgnd, then recoverable flags can't be updated
175 -- Start and end dates can't be updated to affect existing assignemnets
176 -- Name and start date are mandatory parameters
177 -- If credit type is provided, it should be valid
178 -- If pay interval is provided, it should be valid
179 --
180 -- End of comments
181 --------------------------------------------------------------------------------------- +
182
183 PROCEDURE Update_PmtPlan
184 ( p_api_version IN NUMBER,
185 p_init_msg_list IN VARCHAR2,
186 p_commit IN VARCHAR2,
187 p_validation_level IN NUMBER,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 p_old_PmtPlan_rec IN PmtPlan_rec_type,
192 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_rec_type,
193 x_status OUT NOCOPY VARCHAR2,
194 x_loading_status OUT NOCOPY VARCHAR2
195 ) ;
196
197 /*
198 PROCEDURE Update_PmtPlan
199 ( p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
201 p_commit IN VARCHAR2 := FND_API.G_FALSE,
202 p_validation_level IN NUMBER :=
203 FND_API.G_VALID_LEVEL_FULL,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_old_PmtPlan_rec IN PmtPlan_rec_type,
208 p_PmtPlan_rec IN PmtPlan_rec_type,
209 x_status OUT NOCOPY VARCHAR2,
210 x_loading_status OUT NOCOPY VARCHAR2
211 ) ;
212 */
213
214 ---------------------------------------------------------------------------------------+
215 -- Start of Comments
216 --
217 -- API name : Delete_PmtPlan
218 -- Type : Public.
219 -- Pre-reqs : None.
220 -- Usage : Delete
221 -- Desc : Procedure to Delete Payment Plans
222 -- Parameters :
223 -- IN : p_api_version IN NUMBER Require
224 -- p_init_msg_list IN VARCHAR2 Optional
225 -- Default = FND_API.G_FALSE
226 -- p_commit IN VARCHAR2 Optional
227 -- Default = FND_API.G_FALSE
228 -- p_validation_level IN NUMBER Optional
229 -- Default = FND_API.G_VALID_LEVEL_FULL
230 -- OUT : x_return_status OUT VARCHAR2(1)
231 -- x_msg_count OUT NUMBER
232 -- x_msg_data OUT VARCHAR2(2000)
233 -- IN : x_PmtPlan_rec IN PmtPlan_rec_type
234 -- OUT : x_loading_status OUT
235 -- Detailed Error Message
236 -- Version : Current version 1.0
237 -- Initial version 1.0
238 -- Notes : The following validations are performed by this API
239 -- Pmt plan should exist
240 -- Pmt plan cannot be deleted if assigned to salesreps
241 --
242 -- End of comments
243 ------------------------------------------------------------------------------------- +
244 PROCEDURE Delete_PmtPlan
245 ( p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2,
247 p_commit IN VARCHAR2,
248 p_validation_level IN NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_PmtPlan_rec IN PmtPlan_rec_type ,
253 x_status OUT NOCOPY VARCHAR2,
254 x_loading_status OUT NOCOPY VARCHAR2
255 ) ;
256 /*
257 PROCEDURE Delete_PmtPlan
258 ( p_api_version IN NUMBER,
259 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
260 p_commit IN VARCHAR2 := FND_API.G_FALSE,
261 p_validation_level IN NUMBER :=
262 FND_API.G_VALID_LEVEL_FULL,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_pmt_plan_id IN NUMBER,
267 x_loading_status OUT NOCOPY VARCHAR2
268 ) ;
269 */
270
271
272 END CN_PmtPlan_PUB ;