1 PACKAGE PV_Pg_Memberships_PVT AUTHID CURRENT_USER AS
2 /* $Header: pvxvmems.pls 120.1 2005/10/24 09:36:43 dgottlie noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Pg_Memberships_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19 -- Default number of records fetch per call
20 -- G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
21 --===================================================================
22 -- Start of Comments
23 -- -------------------------------------------------------
24 -- Record name
25 -- memb_rec_type
26 -- -------------------------------------------------------
27 -- Parameters:
28 -- membership_id
29 -- object_version_number
30 -- partner_id
31 -- program_id
32 -- start_date
33 -- original_end_date
34 -- actual_end_date
35 -- membership_status_code
36 -- status_reason_code
37 -- enrl_request_id
38 -- created_by
39 -- creation_date
40 -- last_updated_by
41 -- last_update_date
42 -- last_update_login
43 --
44 -- Required
45 --
46 -- Defaults
47 --
48 -- Note: This is automatic generated record definition, it includes all columns
49 -- defined in the table, developer must manually add or delete some of the attributes.
50 --
51 -- End of Comments
52
53 --===================================================================
54 TYPE memb_rec_type IS RECORD
55 (
56 membership_id NUMBER,
57 object_version_number NUMBER,
58 partner_id NUMBER,
59 program_id NUMBER,
60 start_date DATE,
61 original_end_date DATE,
62 actual_end_date DATE,
63 membership_status_code VARCHAR2(30),
64 status_reason_code VARCHAR2(30),
65 enrl_request_id NUMBER,
66 created_by NUMBER,
67 creation_date DATE,
68 last_updated_by NUMBER,
69 last_update_date DATE,
70 last_update_login NUMBER,
71 attribute1 VARCHAR2(240),
72 attribute2 VARCHAR2(240),
73 attribute3 VARCHAR2(240),
74 attribute4 VARCHAR2(240),
75 attribute5 VARCHAR2(240),
76 attribute6 VARCHAR2(240),
77 attribute7 VARCHAR2(240),
78 attribute8 VARCHAR2(240),
79 attribute9 VARCHAR2(240),
80 attribute10 VARCHAR2(240),
81 attribute11 VARCHAR2(240),
82 attribute12 VARCHAR2(240),
83 attribute13 VARCHAR2(240),
84 attribute14 VARCHAR2(240),
85 attribute15 VARCHAR2(240)
86 );
87
88 g_miss_memb_rec memb_rec_type := NULL;
89 TYPE memb_tbl_type IS TABLE OF memb_rec_type INDEX BY BINARY_INTEGER;
90 g_miss_memb_tbl memb_tbl_type;
91
92 TYPE NUMBER_TABLE IS TABLE OF NUMBER;
93
94 -- ==============================================================================
95 -- Start of Comments
96 -- ==============================================================================
97 -- API Name
98 -- Create_Pg_Memberships
99 -- Type
100 -- Private
101 -- Pre-Req
102 --
103 -- Parameters
104 --
105 -- IN
106 -- p_api_version_number IN NUMBER Required
107 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
108 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
109 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
110 -- p_memb_rec IN memb_rec_type Required
111 --
112 -- OUT
113 -- x_return_status OUT VARCHAR2
114 -- x_msg_count OUT NUMBER
115 -- x_msg_data OUT VARCHAR2
116 -- Version : Current version 1.0
117 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
118 -- and basic operation, developer must manually add parameters and business logic as necessary.
119 --
120 -- History
121 --
122 -- NOTE
123 --
124 -- End of Comments
125 -- ==============================================================================
126
127 PROCEDURE Create_Pg_Memberships(
128 p_api_version_number IN NUMBER,
129 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
130 p_commit IN VARCHAR2 := FND_API.G_FALSE,
131 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
132
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2,
136
137 p_memb_rec IN memb_rec_type := g_miss_memb_rec,
138 x_membership_id OUT NOCOPY NUMBER
139 );
140
141 -- ==============================================================================
142 -- Start of Comments
143 -- ==============================================================================
144 -- API Name
145 -- Update_Pg_Memberships
146 -- Type
147 -- Private
148 -- Pre-Req
149 --
150 -- Parameters
151 --
152 -- IN
153 -- p_api_version_number IN NUMBER Required
154 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
155 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
156 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
157 -- p_memb_rec IN memb_rec_type Required
158 --
159 -- OUT
160 -- x_return_status OUT VARCHAR2
161 -- x_msg_count OUT NUMBER
162 -- x_msg_data OUT VARCHAR2
163 -- Version : Current version 1.0
164 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
165 -- and basic operation, developer must manually add parameters and business logic as necessary.
166 --
167 -- History
168 --
169 -- NOTE
170 --
171 -- End of Comments
172 -- ==============================================================================
173
174 PROCEDURE Update_Pg_Memberships(
175 p_api_version_number IN NUMBER,
176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
177 p_commit IN VARCHAR2 := FND_API.G_FALSE,
178 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
179
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183
184 p_memb_rec IN memb_rec_type
185 );
186
187 -- ==============================================================================
188 -- Start of Comments
189 -- ==============================================================================
190 -- API Name
191 -- Delete_Pg_Memberships
192 -- Type
193 -- Private
194 -- Pre-Req
195 --
196 -- Parameters
197 --
198 -- IN
199 -- p_api_version_number IN NUMBER Required
200 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
201 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
202 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
203 -- p_membership_id IN NUMBER
204 -- p_object_version_number IN NUMBER Optional Default = NULL
205 --
206 -- OUT
207 -- x_return_status OUT VARCHAR2
208 -- x_msg_count OUT NUMBER
209 -- x_msg_data OUT VARCHAR2
210 -- Version : Current version 1.0
211 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
212 -- and basic operation, developer must manually add parameters and business logic as necessary.
213 --
214 -- History
215 --
216 -- NOTE
217 --
218 -- End of Comments
219 -- ==============================================================================
220
221 PROCEDURE Delete_Pg_Memberships(
222 p_api_version_number IN NUMBER,
223 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
224 p_commit IN VARCHAR2 := FND_API.G_FALSE,
225 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 p_membership_id IN NUMBER,
230 p_object_version_number IN NUMBER
231 );
232
233 -- ==============================================================================
234 -- Start of Comments
235 -- ==============================================================================
236 -- API Name
237 -- Lock_Pg_Memberships
238 -- Type
239 -- Private
240 -- Pre-Req
241 --
242 -- Parameters
243 --
244 -- IN
245 -- p_api_version_number IN NUMBER Required
246 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
247 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
248 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
249 -- p_memb_rec IN memb_rec_type Required
250 --
251 -- OUT
252 -- x_return_status OUT VARCHAR2
253 -- x_msg_count OUT NUMBER
254 -- x_msg_data OUT VARCHAR2
255 -- Version : Current version 1.0
256 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
257 -- and basic operation, developer must manually add parameters and business logic as necessary.
258 --
259 -- History
260 --
261 -- NOTE
262 --
263 -- End of Comments
264 -- ==============================================================================
265
266 PROCEDURE Lock_Pg_Memberships(
267 p_api_version_number IN NUMBER,
268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
269
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2,
273
274 p_membership_id IN NUMBER,
275 p_object_version IN NUMBER
276 );
277
278
279 -- ==============================================================================
280 -- Start of Comments
281 -- ==============================================================================
282 -- API Name
283 -- Validate_Pg_Memberships
284 --
285 -- Version : Current version 1.0
286 -- p_validation_mode is a constant defined in PV_UTILITY_PVT package
287 -- For create: G_CREATE, for update: G_UPDATE
288 -- Note: 1. This is automated generated item level validation procedure.
289 -- The actual validation detail is needed to be added.
290 -- 2. We can also validate table instead of record. There will be an option for user to choose.
291 --
292 -- History
293 --
294 -- NOTE
295 --
296 -- End of Comments
297 -- ==============================================================================
298
299
300 PROCEDURE Validate_Pg_Memberships(
301 p_api_version_number IN NUMBER,
302 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
303 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
304 p_memb_rec IN memb_rec_type,
305 p_validation_mode IN VARCHAR2,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2
309 );
310
311 -- ==============================================================================
312 -- Start of Comments
313 -- ==============================================================================
314 -- API Name
315 -- Memb_Items
316 --
317 -- Version : Current version 1.0
318 -- p_validation_mode is a constant defined in PV_UTILITY_PVT package
319 -- For create: G_CREATE, for update: G_UPDATE
320 -- Note: 1. This is automated generated item level validation procedure.
321 -- The actual validation detail is needed to be added.
322 -- 2. Validate the unique keys, lookups here
323 --
324 -- History
325 --
326 -- NOTE
327 --
328 -- End of Comments
329 -- ==============================================================================
330
331
332 PROCEDURE Check_Memb_Items (
333 P_memb_rec IN memb_rec_type,
334 p_validation_mode IN VARCHAR2,
335 x_return_status OUT NOCOPY VARCHAR2
336 );
337
338 -- ==============================================================================
339 -- Start of Comments
340 -- ==============================================================================
341 -- API Name
342 -- Validate_Memb_Rec
343 --
344 -- Version : Current version 1.0
345 -- p_validation_mode is a constant defined in PV_UTILITY_PVT package
346 -- For create: G_CREATE, for update: G_UPDATE
347 -- Note: 1. This is automated generated item level validation procedure.
348 -- The actual validation detail is needed to be added.
349 -- 2. Developer can manually added inter-field level validation.
350 --
351 -- History
352 --
353 -- NOTE
354 --
355 -- End of Comments
356 -- ==============================================================================
357
358
359 PROCEDURE Validate_Memb_Rec (
360 p_api_version_number IN NUMBER,
361 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2,
365 p_memb_rec IN memb_rec_type
366 );
367
368 --------------------------------------------------------------------------
369 -- PROCEDURE
370 -- PV_PG_MEMBERSHIPS_PVT.Terminate_ptr_memberships
371 --
372 -- PURPOSE
373 -- Terminate all memberships for a given partner. If the partner is
374 -- a global partner, terminate all its subsidiary memberships also
375
376 --
377 -- USED BY
378 -- called from change membership type api and can also be called independently
379 -- to terminate all partner memberships.
380 --
381 -- HISTORY
382 -- pukken CREATION
383 --------------------------------------------------------------------------
384
385 PROCEDURE Terminate_ptr_memberships
386 (
387 p_api_version_number IN NUMBER
388 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
389 , p_commit IN VARCHAR2 := FND_API.G_FALSE
390 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
391 , p_partner_id IN NUMBER -- partner id for which all memberships need to be terminated
392 , p_memb_type IN VARCHAR -- if not given, will get from profile, should be 'SUBSIDIARY','GLOBAL','STANDARD'
393 , p_status_reason_code IN VARCHAR2 -- pass 'MEMBER_TYPE_CHANGE' if it is happening because of member type change -- it validates against PV_MEMB_STATUS_REASON_CODE
394 , p_comments IN VARCHAR2 DEFAULT NULL -- pass 'Membership terminated by system as member type is changed' if it is changed because of member type change
395 , x_return_status OUT NOCOPY VARCHAR2
396 , x_msg_count OUT NOCOPY NUMBER
397 , x_msg_data OUT NOCOPY VARCHAR2
398 );
399
400 --------------------------------------------------------------------------
401 -- PROCEDURE
402 -- Terminate_membership
403 --
404 -- PURPOSE
405 -- Terminate a membership for a given partner. If the partner is
406 -- a global partner, terminate its appropraite subsidiary memberships also
407 --
408 -- USED BY
409 --
410 -- HISTORY
411 -- pukken CREATION
412 --------------------------------------------------------------------------
413 PROCEDURE Terminate_membership
414 (
415 p_api_version_number IN NUMBER
416 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
417 , p_commit IN VARCHAR2 := FND_API.G_FALSE
418 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
419 , p_membership_id IN NUMBER
420 , p_event_code IN VARCHAR2
421 , p_memb_type IN VARCHAR
422 , p_status_reason_code IN VARCHAR2
423 , p_comments IN VARCHAR2 DEFAULT NULL
424 , x_return_status OUT NOCOPY VARCHAR2
425 , x_msg_count OUT NOCOPY NUMBER
426 , x_msg_data OUT NOCOPY VARCHAR2
427 );
428
429 --------------------------------------------------------------------------
430 -- PROCEDURE
431 -- Update_memb_end_date
432 --
433 -- PURPOSE
434
435 --
436 -- USED BY
437 --
438 -- HISTORY
439 -- pukken CREATION
440 --------------------------------------------------------------------------
441
442 PROCEDURE Update_membership_end_date
443 (
444 p_api_version_number IN NUMBER
445 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
446 , p_commit IN VARCHAR2 := FND_API.G_FALSE
447 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
448 , p_membership_id IN NUMBER -- membership_id for which end date needs to be updated
449 , p_new_date IN DATE
450 , p_comments IN VARCHAR2 DEFAULT NULL
451 , x_return_status OUT NOCOPY VARCHAR2
452 , x_msg_count OUT NOCOPY NUMBER
453 , x_msg_data OUT NOCOPY VARCHAR2
454 );
455
456 PROCEDURE downgrade_membership
457 (
458 p_api_version_number IN NUMBER
459 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
460 , p_commit IN VARCHAR2 := FND_API.G_FALSE
461 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
462 , p_membership_id IN NUMBER -- membership id of the program that you are dwongrading
463 , p_status_reason_code IN VARCHAR2 -- reason for termoination or downgrade
464 , p_comments IN VARCHAR2 DEFAULT NULL
465 , p_program_id_downgraded_to IN NUMBER --programid into which the partner is downgraded to.
466 , p_requestor_resource_id IN NUMBER --resource_id of the user who's performing the action
467 , x_new_memb_id OUT NOCOPY NUMBER
468 , x_return_status OUT NOCOPY VARCHAR2
469 , x_msg_count OUT NOCOPY NUMBER
470 , x_msg_data OUT NOCOPY VARCHAR2
471 ) ;
472
473 FUNCTION TERMINATE_PTR_MEMBERSHIPS
474 (p_subscription_guid IN RAW,
475 p_event IN OUT NOCOPY WF_EVENT_T)
476 RETURN VARCHAR2;
477
478 END PV_Pg_Memberships_PVT;