[Home] [Help]
PACKAGE BODY: APPS.CSC_RELATIONSHIP_PLANS_PUB
Source
1 PACKAGE BODY CSC_RELATIONSHIP_PLANS_PUB as
2 /* $Header: cscprlpb.pls 115.15 2002/11/28 04:25:58 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_RELATIONSHIP_PLANS_PUB
5 -- Purpose : This package body contains all procedures and functions that are required
6 -- to create and modify plan headers and disable plans.
7 -- History
8 -- MM-DD-YYYY NAME MODIFICATIONS
9 -- 10-08-1999 dejoseph Created.
10 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
11 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
12 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
13 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
14 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
15 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
16 -- 04-10-2000 dejoseph Removed reference to cust_account_org in lieu of TCA's
17 -- decision to drop column org_id from hz_cust_accounts.
18 -- 02-18-2002 dejoseph Added changes to uptake new functionality for 11.5.8.
19 -- Ct. / Agent facing application
20 -- - Added new IN parameter END_USER_TYPE to procedures:
21 -- convert_columns_to_rec_type
22 -- create_plan_header
23 -- update_plan_header
24 -- Added the dbdrv command.
25 -- 05-23-2002 dejoseph Added checkfile syntax.
26 -- 11-12-2002 bhroy NOCOPY changes made
27 -- 11-27-2002 bhroy All the default values have been removed, added WHENEVER OSERROR EXIT FAILURE ROLLBACK
28 --
29 -- End of Comments
30
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_RELATIONSHIP_PLANS_PUB';
32 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscprlpb.pls';
33
34 -- Start of Comments
35 -- ***************** Private Conversion Routines Values -> Ids **************
36 -- Purpose
37 --
38 -- This procedure takes a public CSC_RELATIONSHIP_PLANS record as input. It may contain
39 -- values or ids. All values are then converted into ids and a
40 -- private CSC_RELATIONSHIP_PLANS record is returned for the private
41 -- API call.
42 --
43 -- Conversions:
44 --
45 -- Notes
46 --
47 -- 1. IDs take precedence over values. If both are present for a field, ID is used,
48 -- the value based parameter is ignored and a warning message is created.
49 -- 2. This is automatically generated procedure, it converts public record type to
50 -- private record type for all attributes.
51 -- Developer must manually add conversion logic to the attributes.
52 --
53 -- End of Comments
54
55 PROCEDURE CONVERT_CSC_PLAN_HEADERS_B(
56 P_CSC_PLAN_HEADERS_B_REC IN CSC_relationship_plans_PUB.CSC_PLAN_HEADERS_B_REC_TYPE,
57 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
58 x_pvt_CSC_PLAN_HEADERS_B_REC OUT NOCOPY CSC_relationship_plans_PVT.CSC_PLAN_HEADERS_B_REC_TYPE,
59 x_pvt_CSC_PARTY_ID_TBL OUT NOCOPY CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE
60 )
61 IS
62
63 CURSOR C_Get_Lookup_Code(X_Lookup_Type VARCHAR2, X_Meaning VARCHAR2) IS
64 SELECT lookup_code
65 FROM fnd_lookup_values
66 WHERE lookup_type = X_Lookup_Type
67 AND nls_upper(meaning) = nls_upper(X_Meaning);
68
69 l_any_errors BOOLEAN := FALSE;
70 BEGIN
71 -- As of now there are no columns that need to be changed to ids.Just convert the public
72 -- rec type into a pvt. rec type. If in future there is a need to convert a column value
73 -- into an id, put in the logic to do so in this proc. DJ
74
75 if ( p_party_id_tbl.count > 0 ) then
76 for i in 1..p_party_id_tbl.count
77 loop
78 x_pvt_csc_party_id_tbl(i).party_id := p_party_id_tbl(i).party_id;
79 x_pvt_csc_party_id_tbl(i).cust_account_id := p_party_id_tbl(i).cust_account_id;
80 end loop;
81 end if;
82
83 x_pvt_CSC_PLAN_HEADERS_B_REC.PLAN_ID := P_CSC_PLAN_HEADERS_B_REC.PLAN_ID;
84 x_pvt_CSC_PLAN_HEADERS_B_REC.ORIGINAL_PLAN_ID := P_CSC_PLAN_HEADERS_B_REC.ORIGINAL_PLAN_ID;
85 x_pvt_CSC_PLAN_HEADERS_B_REC.PLAN_GROUP_CODE := P_CSC_PLAN_HEADERS_B_REC.PLAN_GROUP_CODE;
86 x_pvt_CSC_PLAN_HEADERS_B_REC.START_DATE_ACTIVE := P_CSC_PLAN_HEADERS_B_REC.START_DATE_ACTIVE;
87 x_pvt_CSC_PLAN_HEADERS_B_REC.END_DATE_ACTIVE := P_CSC_PLAN_HEADERS_B_REC.END_DATE_ACTIVE;
88 x_pvt_CSC_PLAN_HEADERS_B_REC.USE_FOR_CUST_ACCOUNT := P_CSC_PLAN_HEADERS_B_REC.USE_FOR_CUST_ACCOUNT;
89 x_pvt_CSC_PLAN_HEADERS_B_REC.END_USER_TYPE := P_CSC_PLAN_HEADERS_B_REC.END_USER_TYPE;
90 x_pvt_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN := P_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN;
91 x_pvt_CSC_PLAN_HEADERS_B_REC.PROFILE_CHECK_ID := P_CSC_PLAN_HEADERS_B_REC.PROFILE_CHECK_ID;
92 x_pvt_CSC_PLAN_HEADERS_B_REC.RELATIONAL_OPERATOR := P_CSC_PLAN_HEADERS_B_REC.RELATIONAL_OPERATOR;
93 x_pvt_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_HIGH := P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_HIGH;
94 x_pvt_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_LOW := P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_LOW;
95 x_pvt_CSC_PLAN_HEADERS_B_REC.CREATION_DATE := P_CSC_PLAN_HEADERS_B_REC.CREATION_DATE;
96 x_pvt_CSC_PLAN_HEADERS_B_REC.LAST_UPDATE_DATE := P_CSC_PLAN_HEADERS_B_REC.LAST_UPDATE_DATE;
97 x_pvt_CSC_PLAN_HEADERS_B_REC.CREATED_BY := P_CSC_PLAN_HEADERS_B_REC.CREATED_BY;
98 x_pvt_CSC_PLAN_HEADERS_B_REC.LAST_UPDATED_BY := P_CSC_PLAN_HEADERS_B_REC.LAST_UPDATED_BY;
99 x_pvt_CSC_PLAN_HEADERS_B_REC.LAST_UPDATE_LOGIN := P_CSC_PLAN_HEADERS_B_REC.LAST_UPDATE_LOGIN;
100 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE1 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE1;
101 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE2 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE2;
102 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE3 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE3;
103 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE4 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE4;
104 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE5 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE5;
105 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE6 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE6;
106 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE7 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE7;
107 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE8 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE8;
108 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE9 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE9;
109 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE10 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE10;
110 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE11 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE11;
111 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE12 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE12;
112 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE13 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE13;
113 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE14 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE14;
114 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE15 := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE15;
115 x_pvt_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE_CATEGORY := P_CSC_PLAN_HEADERS_B_REC.ATTRIBUTE_CATEGORY;
116 x_pvt_CSC_PLAN_HEADERS_B_REC.OBJECT_VERSION_NUMBER:= P_CSC_PLAN_HEADERS_B_REC.OBJECT_VERSION_NUMBER;
117
118 END CONVERT_CSC_PLAN_HEADERS_B;
119
120
121 /*** PROCEDURE THAT CONVERTS INDIVIDUAL COLUMN PARAMETERS INTO RECORD TYPE FOR
122 PROCEDURE OVERLOADING ***/
123
124 PROCEDURE convert_columns_to_rec_type(
125 P_ROW_ID IN ROWID ,
126 P_PLAN_ID IN NUMBER,
127 P_ORIGINAL_PLAN_ID IN NUMBER,
128 P_PLAN_GROUP_CODE IN VARCHAR2,
129 P_START_DATE_ACTIVE IN DATE ,
130 P_END_DATE_ACTIVE IN DATE,
131 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
132 P_END_USER_TYPE IN VARCHAR2,
133 P_CUSTOMIZED_PLAN IN VARCHAR2,
134 P_PROFILE_CHECK_ID IN NUMBER ,
135 P_RELATIONAL_OPERATOR IN VARCHAR2,
136 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
137 P_CRITERIA_VALUE_LOW IN VARCHAR2,
138 P_CREATION_DATE IN DATE ,
139 P_LAST_UPDATE_DATE IN DATE,
140 P_CREATED_BY IN NUMBER,
141 P_LAST_UPDATED_BY IN NUMBER,
142 P_LAST_UPDATE_LOGIN IN NUMBER,
143 P_ATTRIBUTE1 IN VARCHAR2 ,
144 P_ATTRIBUTE2 IN VARCHAR2,
145 P_ATTRIBUTE3 IN VARCHAR2,
146 P_ATTRIBUTE4 IN VARCHAR2,
147 P_ATTRIBUTE5 IN VARCHAR2,
148 P_ATTRIBUTE6 IN VARCHAR2,
149 P_ATTRIBUTE7 IN VARCHAR2,
150 P_ATTRIBUTE8 IN VARCHAR2,
151 P_ATTRIBUTE9 IN VARCHAR2,
152 P_ATTRIBUTE10 IN VARCHAR2,
153 P_ATTRIBUTE11 IN VARCHAR2,
154 P_ATTRIBUTE12 IN VARCHAR2,
155 P_ATTRIBUTE13 IN VARCHAR2,
156 P_ATTRIBUTE14 IN VARCHAR2,
157 P_ATTRIBUTE15 IN VARCHAR2,
158 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
159 P_OBJECT_VERSION_NUMBER IN NUMBER ,
160 X_PUB_CSC_PLAN_HEADERS_B_REC OUT NOCOPY CSC_PLAN_HEADERS_B_REC_TYPE )
161 IS
162
163 BEGIN
164 x_pub_csc_plan_headers_b_rec.ROW_ID := p_row_id;
165 x_pub_csc_plan_headers_b_rec.PLAN_ID := p_plan_id;
166 x_pub_csc_plan_headers_b_rec.ORIGINAL_PLAN_ID := p_original_plan_id;
167 x_pub_csc_plan_headers_b_rec.PLAN_GROUP_CODE := p_plan_group_code;
168 x_pub_csc_plan_headers_b_rec.START_DATE_ACTIVE := p_start_date_active;
169 x_pub_csc_plan_headers_b_rec.END_DATE_ACTIVE := p_end_date_active;
170 x_pub_csc_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT := p_use_for_cust_account;
171 x_pub_csc_plan_headers_b_rec.END_USER_TYPE := p_end_user_type;
172 x_pub_csc_plan_headers_b_rec.CUSTOMIZED_PLAN := p_customized_plan;
173 x_pub_csc_plan_headers_b_rec.PROFILE_CHECK_ID := p_profile_check_id;
174 x_pub_csc_plan_headers_b_rec.RELATIONAL_OPERATOR := p_relational_operator;
175 x_pub_csc_plan_headers_b_rec.CRITERIA_VALUE_HIGH := p_criteria_value_high;
176 x_pub_csc_plan_headers_b_rec.CRITERIA_VALUE_LOW := p_criteria_value_low;
177 x_pub_csc_plan_headers_b_rec.CREATION_DATE := p_creation_date;
178 x_pub_csc_plan_headers_b_rec.LAST_UPDATE_DATE := p_last_update_date;
179 x_pub_csc_plan_headers_b_rec.CREATED_BY := p_created_by;
180 x_pub_csc_plan_headers_b_rec.LAST_UPDATED_BY := p_last_updated_by;
181 x_pub_csc_plan_headers_b_rec.LAST_UPDATE_LOGIN := p_last_update_login;
182 x_pub_csc_plan_headers_b_rec.ATTRIBUTE1 := p_attribute1;
183 x_pub_csc_plan_headers_b_rec.ATTRIBUTE2 := p_attribute2;
184 x_pub_csc_plan_headers_b_rec.ATTRIBUTE3 := p_attribute3;
185 x_pub_csc_plan_headers_b_rec.ATTRIBUTE4 := p_attribute4;
186 x_pub_csc_plan_headers_b_rec.ATTRIBUTE5 := p_attribute5;
187 x_pub_csc_plan_headers_b_rec.ATTRIBUTE6 := p_attribute6;
188 x_pub_csc_plan_headers_b_rec.ATTRIBUTE7 := p_attribute7;
189 x_pub_csc_plan_headers_b_rec.ATTRIBUTE8 := p_attribute8;
190 x_pub_csc_plan_headers_b_rec.ATTRIBUTE9 := p_attribute9;
191 x_pub_csc_plan_headers_b_rec.ATTRIBUTE10 := p_attribute10;
192 x_pub_csc_plan_headers_b_rec.ATTRIBUTE11 := p_attribute11;
193 x_pub_csc_plan_headers_b_rec.ATTRIBUTE12 := p_attribute12;
194 x_pub_csc_plan_headers_b_rec.ATTRIBUTE13 := p_attribute13;
195 x_pub_csc_plan_headers_b_rec.ATTRIBUTE14 := p_attribute14;
196 x_pub_csc_plan_headers_b_rec.ATTRIBUTE15 := p_attribute15;
197 x_pub_csc_plan_headers_b_rec.ATTRIBUTE_CATEGORY := p_attribute_category;
198 x_pub_csc_plan_headers_b_rec.OBJECT_VERSION_NUMBER:= p_object_version_number;
199
200 END convert_columns_to_rec_type;
201
202
203
204 /*********** OVERLOADED PROCEDURE TO TAKE COLUMNS INSTEAD OF RECORD TYPE *******/
205
206 PROCEDURE create_plan_header(
207 P_Api_Version_Number IN NUMBER,
208 P_Init_Msg_List IN VARCHAR2,
209 P_Commit IN VARCHAR2,
210 P_ROW_ID IN ROWID,
211 P_PLAN_ID IN NUMBER,
212 P_ORIGINAL_PLAN_ID IN NUMBER,
213 P_PLAN_GROUP_CODE IN VARCHAR2,
214 P_START_DATE_ACTIVE IN DATE,
215 P_END_DATE_ACTIVE IN DATE,
216 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
217 P_END_USER_TYPE IN VARCHAR2,
218 P_CUSTOMIZED_PLAN IN VARCHAR2,
219 P_PROFILE_CHECK_ID IN NUMBER,
220 P_RELATIONAL_OPERATOR IN VARCHAR2,
221 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
222 P_CRITERIA_VALUE_LOW IN VARCHAR2,
223 P_CREATION_DATE IN DATE,
224 P_LAST_UPDATE_DATE IN DATE,
225 P_CREATED_BY IN NUMBER,
226 P_LAST_UPDATED_BY IN NUMBER,
227 P_LAST_UPDATE_LOGIN IN NUMBER,
228 P_ATTRIBUTE1 IN VARCHAR2,
229 P_ATTRIBUTE2 IN VARCHAR2,
230 P_ATTRIBUTE3 IN VARCHAR2,
231 P_ATTRIBUTE4 IN VARCHAR2,
232 P_ATTRIBUTE5 IN VARCHAR2,
233 P_ATTRIBUTE6 IN VARCHAR2,
234 P_ATTRIBUTE7 IN VARCHAR2,
235 P_ATTRIBUTE8 IN VARCHAR2,
236 P_ATTRIBUTE9 IN VARCHAR2,
237 P_ATTRIBUTE10 IN VARCHAR2,
238 P_ATTRIBUTE11 IN VARCHAR2,
239 P_ATTRIBUTE12 IN VARCHAR2,
240 P_ATTRIBUTE13 IN VARCHAR2,
241 P_ATTRIBUTE14 IN VARCHAR2,
242 P_ATTRIBUTE15 IN VARCHAR2,
243 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
244 P_OBJECT_VERSION_NUMBER IN NUMBER ,
245 P_DESCRIPTION IN VARCHAR2,
246 P_NAME IN VARCHAR2,
247 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
248 X_PLAN_ID OUT NOCOPY NUMBER,
249 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
250 X_Return_Status OUT NOCOPY VARCHAR2,
251 X_Msg_Count OUT NOCOPY NUMBER,
252 X_Msg_Data OUT NOCOPY VARCHAR2 )
253 IS
254 l_pub_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
255
256 BEGIN
257
258 convert_columns_to_rec_type(
259 P_ROW_ID => p_row_id,
260 P_PLAN_ID => p_plan_id,
261 P_ORIGINAL_PLAN_ID => p_original_plan_id,
262 P_PLAN_GROUP_CODE => p_plan_group_code,
263 P_START_DATE_ACTIVE => p_start_date_active,
264 P_END_DATE_ACTIVE => p_end_date_active ,
265 P_USE_FOR_CUST_ACCOUNT => P_use_for_cust_account,
266 P_END_USER_TYPE => P_end_user_type,
267 P_CUSTOMIZED_PLAN => p_customized_plan,
268 P_PROFILE_CHECK_ID => p_profile_check_id,
269 P_RELATIONAL_OPERATOR => p_relational_operator,
270 P_CRITERIA_VALUE_HIGH => p_criteria_value_high,
271 P_CRITERIA_VALUE_LOW => p_criteria_value_low,
272 P_CREATION_DATE => p_creation_date,
273 P_LAST_UPDATE_DATE => p_last_update_date,
277 P_ATTRIBUTE1 => p_attribute1,
274 P_CREATED_BY => p_created_by,
275 P_LAST_UPDATED_BY => p_last_updated_by,
276 P_LAST_UPDATE_LOGIN => p_last_update_login,
278 P_ATTRIBUTE2 => p_attribute2,
279 P_ATTRIBUTE3 => p_attribute3,
280 P_ATTRIBUTE4 => p_attribute4,
281 P_ATTRIBUTE5 => p_attribute5,
282 P_ATTRIBUTE6 => p_attribute6,
283 P_ATTRIBUTE7 => p_attribute7,
284 P_ATTRIBUTE8 => p_attribute8,
285 P_ATTRIBUTE9 => p_attribute9,
286 P_ATTRIBUTE10 => p_attribute10,
287 P_ATTRIBUTE11 => p_attribute11,
288 P_ATTRIBUTE12 => p_attribute12,
289 P_ATTRIBUTE13 => p_attribute13,
290 P_ATTRIBUTE14 => p_attribute14,
291 P_ATTRIBUTE15 => p_attribute15,
292 P_ATTRIBUTE_CATEGORY => p_attribute_category,
293 P_OBJECT_VERSION_NUMBER => p_object_version_number,
294 X_PUB_CSC_PLAN_HEADERS_B_REC => l_pub_csc_plan_headers_b_rec );
295
296 /*** CALL PROCEDURE WITH RECORD TYPE STRUCTURE ***/
297
298 create_plan_header(
299 p_api_version_number => p_api_version_number,
300 p_init_msg_list => p_init_msg_list,
301 p_commit => p_commit,
302 p_csc_plan_headers_b_rec => l_pub_csc_plan_headers_b_rec,
303 p_description => p_description,
304 p_name => p_name,
305 p_party_id_tbl => p_party_id_tbl,
306 x_plan_id => x_plan_id,
307 x_object_version_number => x_object_version_number,
308 x_return_status => x_return_status,
309 x_msg_count => x_msg_count,
310 x_msg_data => x_msg_data );
311
312 END create_plan_header;
313
314
315 PROCEDURE create_plan_header(
316 P_Api_Version_Number IN NUMBER,
317 P_Init_Msg_List IN VARCHAR2,
318 P_Commit IN VARCHAR2,
319 P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
320 P_DESCRIPTION IN VARCHAR2,
321 P_NAME IN VARCHAR2,
322 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
323 X_PLAN_ID OUT NOCOPY NUMBER,
324 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
325 X_Return_Status OUT NOCOPY VARCHAR2,
326 X_Msg_Count OUT NOCOPY NUMBER,
327 X_Msg_Data OUT NOCOPY VARCHAR2
328 )
329 IS
330 l_api_name CONSTANT VARCHAR2(30) := 'create_plan_header';
331 l_api_version_number CONSTANT NUMBER := 1.0;
332 l_pvt_CSC_PLAN_HEADERS_B_REC CSC_RELATIONSHIP_PLANS_PVT.CSC_PLAN_HEADERS_B_REC_TYPE :=
333 CSC_RELATIONSHIP_PLANS_PVT.G_MISS_CSC_PLAN_HEADERS_B_REC;
334 l_pvt_CSC_PARTY_ID_TBL CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE :=
335 CSC_CUST_PLANS_PVT.G_MISS_PARTY_ID_TBL;
336 BEGIN
337 -- Standard Start of API savepoint
338 SAVEPOINT create_plan_header_pub;
339
340 -- Standard call to check for call compatibility.
341 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
342 p_api_version_number,
343 l_api_name,
344 G_PKG_NAME)
345 THEN
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END IF;
348
349 -- Initialize message list if p_init_msg_list is set to TRUE.
350 IF FND_API.to_Boolean( p_init_msg_list ) THEN
351 FND_MSG_PUB.initialize;
352 END IF;
353
354 -- Initialize API return status to SUCCESS
355 x_return_status := FND_API.G_RET_STS_SUCCESS;
356
357 -- Convert the values to ids. As of now there are no values to be converted into ids. But call
358 -- the proc. to keep with the standards and to get back a pvt. rec type to pass to the pvt.
359 -- package. A table type conversion is also added in this procedure to collect information about
360 -- the parties when a 'CUSTOMIZED' plan is created. DJ
361 --
362 CONVERT_CSC_PLAN_HEADERS_B (
363 P_CSC_PLAN_HEADERS_B_REC => P_CSC_PLAN_HEADERS_B_REC,
364 P_PARTY_ID_TBL => P_PARTY_ID_TBL,
365 x_pvt_CSC_PLAN_HEADERS_B_REC => l_pvt_CSC_PLAN_HEADERS_B_REC,
366 x_pvt_CSC_PARTY_ID_TBL => l_pvt_CSC_PARTY_ID_TBL);
367
368 -- Calling Private package : Create_PLAN_HEADERS_B
369 -- Hint : Primary key needs to be returned
370 CSC_relationship_plans_PVT.Create_plan_header(
371 P_Api_Version_Number => 1.0,
372 P_Init_Msg_List => FND_API.G_FALSE,
373 P_Commit => FND_API.G_FALSE,
374 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
375 P_CSC_PLAN_HEADERS_B_REC => l_pvt_CSC_PLAN_HEADERS_B_REC ,
376 P_DESCRIPTION => p_description,
377 P_NAME => p_name,
378 P_PARTY_ID_TBL => l_pvt_CSC_PARTY_ID_TBL,
379 X_PLAN_ID => x_PLAN_ID,
380 X_OBJECT_VERSION_NUMBER => x_object_version_number,
381 X_Return_Status => x_return_status,
382 X_Msg_Count => x_msg_count,
386 -- Check return status from the above procedure call
383 X_Msg_Data => x_msg_data);
384
385
387 IF x_return_status = FND_API.G_RET_STS_ERROR then
388 raise FND_API.G_EXC_ERROR;
389 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
390 raise FND_API.G_EXC_UNEXPECTED_ERROR;
391 END IF;
392
393 -- Standard check for p_commit
394 IF FND_API.to_Boolean( p_commit )
395 THEN
396 COMMIT WORK;
397 END IF;
398
399 -- Standard call to get message count and if count is 1, get message info.
400 FND_MSG_PUB.Count_And_Get
401 ( p_encoded => FND_API.G_FALSE,
402 p_count => x_msg_count,
403 p_data => x_msg_data );
404
405 EXCEPTION
406 WHEN FND_API.G_EXC_ERROR THEN
407 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
408 P_API_NAME => L_API_NAME,
409 P_PKG_NAME => G_PKG_NAME,
410 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
411 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
412 X_MSG_COUNT => X_MSG_COUNT,
413 X_MSG_DATA => X_MSG_DATA,
414 X_RETURN_STATUS => X_RETURN_STATUS);
415 APP_EXCEPTION.RAISE_EXCEPTION;
416
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
419 P_API_NAME => L_API_NAME,
420 P_PKG_NAME => G_PKG_NAME,
421 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
422 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
423 X_MSG_COUNT => X_MSG_COUNT,
424 X_MSG_DATA => X_MSG_DATA,
425 X_RETURN_STATUS => X_RETURN_STATUS);
426 APP_EXCEPTION.RAISE_EXCEPTION;
427
428 WHEN OTHERS THEN
429 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
430 P_API_NAME => L_API_NAME,
431 P_PKG_NAME => G_PKG_NAME,
432 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
433 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
434 X_MSG_COUNT => X_MSG_COUNT,
435 X_MSG_DATA => X_MSG_DATA,
436 X_RETURN_STATUS => X_RETURN_STATUS);
437 APP_EXCEPTION.RAISE_EXCEPTION;
438
439 End create_plan_header;
440
441
442 /*********** OVERLOADED PROCEDURE TO TAKE COLUMNS INSTEAD OF RECORD TYPE *******/
443
444 PROCEDURE update_plan_header(
445 P_Api_Version_Number IN NUMBER,
446 P_Init_Msg_List IN VARCHAR2,
447 P_Commit IN VARCHAR2,
448 P_ROW_ID IN ROWID,
449 P_PLAN_ID IN NUMBER,
450 P_ORIGINAL_PLAN_ID IN NUMBER,
451 P_PLAN_GROUP_CODE IN VARCHAR2,
452 P_START_DATE_ACTIVE IN DATE,
453 P_END_DATE_ACTIVE IN DATE,
454 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
455 P_END_USER_TYPE IN VARCHAR2,
456 P_CUSTOMIZED_PLAN IN VARCHAR2,
457 P_PROFILE_CHECK_ID IN NUMBER,
458 P_RELATIONAL_OPERATOR IN VARCHAR2,
459 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
460 P_CRITERIA_VALUE_LOW IN VARCHAR2,
461 P_CREATION_DATE IN DATE,
462 P_LAST_UPDATE_DATE IN DATE,
463 P_CREATED_BY IN NUMBER,
464 P_LAST_UPDATED_BY IN NUMBER,
465 P_LAST_UPDATE_LOGIN IN NUMBER,
466 P_ATTRIBUTE1 IN VARCHAR2 ,
467 P_ATTRIBUTE2 IN VARCHAR2,
468 P_ATTRIBUTE3 IN VARCHAR2,
469 P_ATTRIBUTE4 IN VARCHAR2,
470 P_ATTRIBUTE5 IN VARCHAR2,
471 P_ATTRIBUTE6 IN VARCHAR2,
472 P_ATTRIBUTE7 IN VARCHAR2,
473 P_ATTRIBUTE8 IN VARCHAR2,
474 P_ATTRIBUTE9 IN VARCHAR2,
475 P_ATTRIBUTE10 IN VARCHAR2,
476 P_ATTRIBUTE11 IN VARCHAR2,
477 P_ATTRIBUTE12 IN VARCHAR2,
478 P_ATTRIBUTE13 IN VARCHAR2,
479 P_ATTRIBUTE14 IN VARCHAR2,
480 P_ATTRIBUTE15 IN VARCHAR2,
481 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
482 P_OBJECT_VERSION_NUMBER IN NUMBER ,
483 P_DESCRIPTION IN VARCHAR2,
484 P_NAME IN VARCHAR2,
485 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
486 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
487 X_Return_Status OUT NOCOPY VARCHAR2,
488 X_Msg_Count OUT NOCOPY NUMBER,
489 X_Msg_Data OUT NOCOPY VARCHAR2
490 )
491 IS
492 l_pub_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
493
494 BEGIN
495
496 convert_columns_to_rec_type(
497 P_ROW_ID => p_row_id,
498 P_PLAN_ID => p_plan_id,
499 P_ORIGINAL_PLAN_ID => p_original_plan_id,
500 P_PLAN_GROUP_CODE => p_plan_group_code,
501 P_START_DATE_ACTIVE => p_start_date_active,
502 P_END_DATE_ACTIVE => p_end_date_active ,
503 P_USE_FOR_CUST_ACCOUNT => P_use_for_cust_account,
504 P_END_USER_TYPE => P_end_user_type,
505 P_CUSTOMIZED_PLAN => p_customized_plan,
506 P_PROFILE_CHECK_ID => p_profile_check_id,
510 P_CREATION_DATE => p_creation_date,
507 P_RELATIONAL_OPERATOR => p_relational_operator,
508 P_CRITERIA_VALUE_HIGH => p_criteria_value_high,
509 P_CRITERIA_VALUE_LOW => p_criteria_value_low,
511 P_LAST_UPDATE_DATE => p_last_update_date,
512 P_CREATED_BY => p_created_by,
513 P_LAST_UPDATED_BY => p_last_updated_by,
514 P_LAST_UPDATE_LOGIN => p_last_update_login,
515 P_ATTRIBUTE1 => p_attribute1,
516 P_ATTRIBUTE2 => p_attribute2,
517 P_ATTRIBUTE3 => p_attribute3,
518 P_ATTRIBUTE4 => p_attribute4,
519 P_ATTRIBUTE5 => p_attribute5,
520 P_ATTRIBUTE6 => p_attribute6,
521 P_ATTRIBUTE7 => p_attribute7,
522 P_ATTRIBUTE8 => p_attribute8,
523 P_ATTRIBUTE9 => p_attribute9,
524 P_ATTRIBUTE10 => p_attribute10,
525 P_ATTRIBUTE11 => p_attribute11,
526 P_ATTRIBUTE12 => p_attribute12,
527 P_ATTRIBUTE13 => p_attribute13,
528 P_ATTRIBUTE14 => p_attribute14,
529 P_ATTRIBUTE15 => p_attribute15,
530 P_ATTRIBUTE_CATEGORY => p_attribute_category,
531 P_OBJECT_VERSION_NUMBER => p_object_version_number,
532 X_PUB_CSC_PLAN_HEADERS_B_REC => l_pub_csc_plan_headers_b_rec );
533
534 /*** CALL PROCEDURE WITH RECORD TYPE STRUCTURE ***/
535 update_plan_header(
536 P_Api_Version_Number => p_api_version_number,
537 P_Init_Msg_List => p_init_msg_list,
538 P_Commit => p_commit,
539 P_CSC_PLAN_HEADERS_B_REC => l_pub_csc_plan_headers_b_rec,
540 P_DESCRIPTION => p_description,
541 P_NAME => p_name,
542 P_PARTY_ID_TBL => p_party_id_tbl,
543 X_Object_Version_Number => x_object_version_number,
544 X_Return_Status => x_return_status,
545 X_Msg_Count => x_msg_count,
546 X_Msg_Data => x_msg_data );
547
548 END update_plan_header;
549
550
551 PROCEDURE update_plan_header(
552 P_Api_Version_Number IN NUMBER,
553 P_Init_Msg_List IN VARCHAR2,
554 P_Commit IN VARCHAR2,
555 P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
556 P_DESCRIPTION IN VARCHAR2,
557 P_NAME IN VARCHAR2,
558 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
559 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
560 X_Return_Status OUT NOCOPY VARCHAR2,
561 X_Msg_Count OUT NOCOPY NUMBER,
562 X_Msg_Data OUT NOCOPY VARCHAR2
563 )
564 IS
565 l_api_name CONSTANT VARCHAR2(30) := 'update_plan_header';
566 l_api_version_number CONSTANT NUMBER := 1.0;
567 l_pvt_CSC_PLAN_HEADERS_B_REC CSC_RELATIONSHIP_PLANS_PVT.CSC_PLAN_HEADERS_B_REC_TYPE :=
568 CSC_RELATIONSHIP_PLANS_PVT.G_MISS_CSC_PLAN_HEADERS_B_REC;
569 l_pvt_CSC_PARTY_ID_TBL CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE :=
570 CSC_CUST_PLANS_PVT.G_MISS_PARTY_ID_TBL;
571
572 BEGIN
573 -- Standard Start of API savepoint
574 SAVEPOINT update_plan_header_pub;
575
576 -- Standard call to check for call compatibility.
577 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
578 p_api_version_number,
579 l_api_name,
580 G_PKG_NAME)
581 THEN
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583 END IF;
584
585 -- Initialize message list if p_init_msg_list is set to TRUE.
586 IF FND_API.to_Boolean( p_init_msg_list ) THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590 -- Initialize API return status to SUCCESS
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592
593 -- Convert the values to ids. As of now there are no values to be converted into ids. But call
594 -- the proc. to keep with the standards and to get back a pvt. rec type to pass to the pvt.
595 -- package. DJ
596 --
597 CONVERT_CSC_PLAN_HEADERS_B (
598 P_CSC_PLAN_HEADERS_B_REC => P_CSC_PLAN_HEADERS_B_REC,
599 P_PARTY_ID_TBL => P_PARTY_ID_TBL,
600 x_pvt_CSC_PLAN_HEADERS_B_REC => l_pvt_CSC_PLAN_HEADERS_B_REC,
601 x_pvt_CSC_PARTY_ID_TBL => l_pvt_CSC_PARTY_ID_TBL);
602
603 CSC_relationship_plans_pvt.update_plan_header(
604 P_Api_Version_Number => 1.0,
605 P_Init_Msg_List => FND_API.G_FALSE,
606 P_Commit => p_commit,
607 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
608 P_CSC_PLAN_HEADERS_B_REC => l_pvt_CSC_PLAN_HEADERS_B_REC ,
609 P_DESCRIPTION => p_description,
610 P_NAME => p_name,
611 P_PARTY_ID_TBL => l_pvt_CSC_PARTY_ID_TBL,
612 X_Object_Version_Number => x_object_version_number,
613 X_Return_Status => x_return_status,
614 X_Msg_Count => x_msg_count,
615 X_Msg_Data => x_msg_data);
616
617 -- Check return status from the above procedure call
618 IF x_return_status = FND_API.G_RET_STS_ERROR then
622 END IF;
619 raise FND_API.G_EXC_ERROR;
620 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
621 raise FND_API.G_EXC_UNEXPECTED_ERROR;
623
624 -- Standard check for p_commit
625 IF FND_API.to_Boolean( p_commit )
626 THEN
627 COMMIT WORK;
628 END IF;
629
630 -- Standard call to get message count and if count is 1, get message info.
631 FND_MSG_PUB.Count_And_Get
632 ( p_encoded => FND_API.G_FALSE,
633 p_count => x_msg_count,
634 p_data => x_msg_data );
635
636 EXCEPTION
637 WHEN FND_API.G_EXC_ERROR THEN
638 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
639 P_API_NAME => L_API_NAME,
640 P_PKG_NAME => G_PKG_NAME,
641 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
642 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
643 X_MSG_COUNT => X_MSG_COUNT,
644 X_MSG_DATA => X_MSG_DATA,
645 X_RETURN_STATUS => X_RETURN_STATUS);
646 APP_EXCEPTION.RAISE_EXCEPTION;
647
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
650 P_API_NAME => L_API_NAME,
651 P_PKG_NAME => G_PKG_NAME,
652 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
653 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
654 X_MSG_COUNT => X_MSG_COUNT,
655 X_MSG_DATA => X_MSG_DATA,
656 X_RETURN_STATUS => X_RETURN_STATUS);
657 APP_EXCEPTION.RAISE_EXCEPTION;
658
659 WHEN OTHERS THEN
660 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
661 P_API_NAME => L_API_NAME,
662 P_PKG_NAME => G_PKG_NAME,
663 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
664 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
665 X_MSG_COUNT => X_MSG_COUNT,
666 X_MSG_DATA => X_MSG_DATA,
667 X_RETURN_STATUS => X_RETURN_STATUS);
668 APP_EXCEPTION.RAISE_EXCEPTION;
669
670 End update_plan_header;
671
672
673 PROCEDURE Disable_plan(
674 P_Api_Version_Number IN NUMBER,
675 P_Init_Msg_List IN VARCHAR2,
676 P_Commit IN VARCHAR2,
677 P_Plan_Id IN NUMBER,
678 X_Return_Status OUT NOCOPY VARCHAR2,
679 X_Msg_Count OUT NOCOPY NUMBER,
680 X_Msg_Data OUT NOCOPY VARCHAR2 )
681 IS
682 l_api_name CONSTANT VARCHAR2(30) := 'Disable_Plan';
683 l_api_version_number CONSTANT NUMBER := 1.0;
684 l_pvt_CSC_PLAN_HEADERS_B_REC CSC_RELATIONSHIP_PLANS_PVT.CSC_PLAN_HEADERS_B_REC_TYPE;
685 BEGIN
686 -- Standard Start of API savepoint
687 SAVEPOINT DISABLE_PLAN_PUB;
688
689 -- Standard call to check for call compatibility.
690 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
691 p_api_version_number,
692 l_api_name,
693 G_PKG_NAME)
694 THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697
698 -- Initialize message list if p_init_msg_list is set to TRUE.
699 IF FND_API.to_Boolean( p_init_msg_list ) THEN
700 FND_MSG_PUB.initialize;
701 END IF;
702
703 -- Initialize API return status to SUCCESS
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705
706 CSC_relationship_plans_PVT.disable_plan(
707 P_Api_Version_Number => 1.0,
708 P_Init_Msg_List => FND_API.G_FALSE,
709 P_Commit => p_commit,
710 P_plan_id => p_plan_id,
711 X_Return_Status => x_return_status,
712 X_Msg_Count => x_msg_count,
713 X_Msg_Data => x_msg_data);
714
715 -- Check return status from the above procedure call
716 IF x_return_status = FND_API.G_RET_STS_ERROR then
717 raise FND_API.G_EXC_ERROR;
718 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
719 raise FND_API.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721
722 -- Standard check for p_commit
723 IF FND_API.to_Boolean( p_commit )
724 THEN
725 COMMIT WORK;
726 END IF;
727
728 -- Standard call to get message count and if count is 1, get message info.
729 FND_MSG_PUB.Count_And_Get
730 ( p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data );
733
734 EXCEPTION
735 WHEN FND_API.G_EXC_ERROR THEN
736 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
737 P_API_NAME => L_API_NAME,
738 P_PKG_NAME => G_PKG_NAME,
739 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
740 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
741 X_MSG_COUNT => X_MSG_COUNT,
742 X_MSG_DATA => X_MSG_DATA,
743 X_RETURN_STATUS => X_RETURN_STATUS);
744
745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
746 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
747 P_API_NAME => L_API_NAME,
748 P_PKG_NAME => G_PKG_NAME,
749 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
750 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
751 X_MSG_COUNT => X_MSG_COUNT,
752 X_MSG_DATA => X_MSG_DATA,
753 X_RETURN_STATUS => X_RETURN_STATUS);
754
755 WHEN OTHERS THEN
756 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
757 P_API_NAME => L_API_NAME,
758 P_PKG_NAME => G_PKG_NAME,
759 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
760 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PUB,
761 X_MSG_COUNT => X_MSG_COUNT,
762 X_MSG_DATA => X_MSG_DATA,
763 X_RETURN_STATUS => X_RETURN_STATUS);
764
765 End Disable_plan;
766
767 End CSC_RELATIONSHIP_PLANS_PUB;