1 PACKAGE CSC_PLAN_ASSIGNMENT_PKG AUTHID CURRENT_USER as
2 /* $Header: cscvengs.pls 120.0 2005/05/30 15:52:54 appldev noship $ */
3 -- Start of Comments
4 -- Package name : CSC_PLAN_ASSIGNMENT_PKG
5 -- Purpose : Plan defnitions by itself are dummy entities in the system, and
6 -- means nothing until it is associated to customer(s). This association
7 -- is done by the procedures and function defined in this package spec.
8 -- These procedures evaluate the results of the customer profile checks
9 -- for each customer and its account, compares it with the plan criteria,
10 -- and if met, stores the association of the plan and the customer.
11 -- History :
12 -- MM-DD-YYYY NAME MODIFICATIONS
13 -- 12-09-1999 dejoseph Created.
14 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
15 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
16 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
17 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
18 -- 04-10-2000 dejoseph Removed reference to cust_account_org in lieu of TCA's
19 -- decision to drop column org_id from hz_cust_accounts.
20 --
21 -- 04-28-2000 dejoseph Replace reference to jtf_cust_accounts_v with jtf_cust_accounts_all_v.
22 -- 05-11-2000 dmontefa Added the 'OUT NOCOPY ' parameters, x_errbuf, x_retcode
23 -- 08-09-2000 dejoseph Included a parameter in procedure add_remove_plan_check. Generally
24 -- modified engine to run for all plans and parties if no parameters
25 -- are specified. Fix to bug # 1372050.
26 -- 01-30-2002 dejoseph Included the dbdrv command for DB driver generation.
27 -- Made the following change to clean up the Plans engine to
28 -- 1. Function as intended and 2. Perform efficiently.
29 -- Ref. Bug #s - 2030164, 1745488
30 -- 03-15-2002 dejoseph Added the checkfile command
31 -- 11-12-2002 bhroy NOCOPY changes made
32 -- 11-28-2002 bhroy FND_API.G_MISS_XXX defaults removed
33 --
34 -- NOTE :
35 -- End of Comments
36 --
37
38 -- Default number of records fetch per call
39 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
40
41 /**************************** TYPE DEFNITIONS ****************************/
42
43 TYPE CSC_PARTY_ACCT_REC_TYPE IS RECORD (
44 PARTY_ID NUMBER,
45 CUST_ACCOUNT_ID NUMBER);
46
47 TYPE CSC_PARTY_ACCT_TBL_TYPE IS TABLE OF CSC_PARTY_ACCT_REC_TYPE
48 INDEX BY BINARY_INTEGER;
49 G_MISS_PARTY_ACCT_TBL CSC_PARTY_ACCT_TBL_TYPE;
50
51 TYPE CSC_NUM_TBL_TYPE IS TABLE OF NUMBER
52 INDEX BY BINARY_INTEGER;
53 G_EMPTY_NUM_TBL CSC_NUM_TBL_TYPE;
54
55 TYPE CSC_CHAR_TBL_TYPE IS TABLE OF VARCHAR2(240)
56 INDEX BY BINARY_INTEGER;
57 G_EMPTY_CHAR_TBL CSC_CHAR_TBL_TYPE;
58
59 TYPE CSC_PARTY_ID_TBL_TYPE IS TABLE OF NUMBER
60 INDEX BY BINARY_INTEGER;
61 G_EMPTY_PARTY_ID_TBL CSC_PARTY_ID_TBL_TYPE;
62
63 TYPE CSC_PLAN_ID_TBL_TYPE IS TABLE OF NUMBER
64 INDEX BY BINARY_INTEGER;
65 G_EMPTY_PLAN_ID_TBL CSC_PLAN_ID_TBL_TYPE;
66
67 TYPE CSC_CUST_ID_TBL_TYPE IS TABLE OF NUMBER
68 INDEX BY BINARY_INTEGER;
69 G_EMPTY_CUST_ID_TBL CSC_CUST_ID_TBL_TYPE;
70
71 TYPE CSC_DATE_TBL_TYPE IS TABLE OF DATE
72 INDEX BY BINARY_INTEGER;
73 G_EMPTY_DATE_TBL CSC_DATE_TBL_TYPE;
74
75 TYPE CSC_CHECK_ID_TBL_TYPE IS TABLE OF NUMBER
76 INDEX BY BINARY_INTEGER;
77 G_EMPTY_CHECK_ID_TBL CSC_CHECK_ID_TBL_TYPE;
78
79 /************************** END TYPE DEFNITIONS ***************************/
80
81 -- *******************************************************
82 -- Start of Comments
83 -- *******************************************************
84 -- API Name: RUN_PLAN_ENGINE
85 -- Type : Private
86 -- Pre-Req :
87 -- Function: Invokes the plan engine. The plan engine can be invoked
88 -- when any of the three scenarios occur.
89 -- 1> Invoked from the customer profile engine.(when profile engine is
90 -- executed).
91 -- 2> Invoked when plan defnitions change;
92 -- 3> Invoked when a party or party account is added/deleted
93 -- Depending on the passed in parameters the appropiate procedure is
94 -- executed to make/break the customer to plan association.
95 -- Parameters:
96 -- IN
97 -- p_plan_id IN NUMBER Optional Default = NULL
98 -- p_check_id IN NUMBER Optional Default = NULL
99 -- p_party_id IN NUMBER Optional Default = NULL
100 -- p_cust_account_id IN NUMBER Optional Default = NULL
101 -- x_errbuf OUT NOCOPY VARCHAR2
102 -- x_retcode OUT NOCOPY NUMBER
103 --
104 -- Version : Current version 1.0
105 --
106 -- End of Comments
107 --
108 PROCEDURE RUN_PLAN_ENGINE (
109 X_ERRBUF OUT NOCOPY VARCHAR2,
110 X_RETCODE OUT NOCOPY NUMBER,
111 P_PLAN_ID IN NUMBER := NULL,
112 P_CHECK_ID IN NUMBER := NULL,
113 P_PARTY_ID IN NUMBER := NULL,
114 P_CUST_ACCOUNT_ID IN NUMBER := NULL );
115
116 -- *******************************************************
117 -- Start of Comments
118 -- *******************************************************
119 -- API Name: RUN_WITH_PLAN_ID
120 -- Type : Private
121 -- Pre-Req :
122 -- Function: Calls add_remove_plan_check with passed in plan_id.
123 -- Parameters:
124 -- IN
125 -- P_PLAN_ID IN NUMBER
126 --
127 -- OUT NOCOPY
128 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
129 -- Version : Current version 1.0
130 --
131 -- End of Comments
132 --
133 PROCEDURE RUN_WITH_PLAN_ID (
134 P_PLAN_ID IN NUMBER,
135 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
136
137 -- *******************************************************
138 -- Start of Comments
139 -- *******************************************************
140 -- API Name: RUN_WITH_CHECK_ID
141 -- Type : Private
142 -- Pre-Req :
143 -- Function: Calls add_remove_plan_check with passed in check_id.
144 -- Parameters:
145 -- IN
146 -- P_CHECK_ID IN NUMBER
147 --
148 --
149 -- OUT NOCOPY
150 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
151 -- Version : Current version 1.0
152 --
153 -- End of Comments
154 --
155 PROCEDURE RUN_WITH_CHECK_ID (
156 P_CHECK_ID IN NUMBER,
157 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
158
159 -- *******************************************************
160 -- Start of Comments
161 -- *******************************************************
162 -- API Name: RUN_WITH_PARTY_ID
163 -- Type : Private
164 -- Pre-Req :
165 -- Function: Calls add_remove_plan_check with passed in party_id.
166 -- Parameters:
167 -- IN
168 -- P_PARTY_ID IN NUMBER
169 --
170 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
171 -- Version : Current version 1.0
172 --
173 -- End of Comments
174 --
175 PROCEDURE RUN_WITH_PARTY_ID (
176 P_PARTY_ID IN NUMBER,
177 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
178
179 -- *******************************************************
180 -- Start of Comments
181 -- *******************************************************
182 -- API Name: RUN_WITH_ACCOUNT_ID
183 -- Type : Private
184 -- Pre-Req :
185 -- Function: Calls add_remove_plan_check with passed in account_id.
186 -- Parameters:
187 -- IN
188 -- P_CUST_ACCOUNT_ID IN NUMBER
189 --
190 -- OUT NOCOPY
191 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
192 -- Version : Current version 1.0
193 --
194 -- End of Comments
195 --
196 PROCEDURE RUN_WITH_ACCOUNT_ID (
197 P_CUST_ACCOUNT_ID IN NUMBER,
198 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
199
200 -- *******************************************************
201 -- Start of Comments
202 -- *******************************************************
203 -- API Name: RUN_WITH_PLAN_PARTY
204 -- Type : Private
205 -- Pre-Req :
206 -- Function: Calls add_remove_plan_check with passed in plan_id and
207 -- party_id.
208 -- Parameters:
209 -- IN
210 -- P_PLAN_ID IN NUMBER
211 -- P_PARTY_ID IN NUMBER
212 --
213 -- OUT NOCOPY
214 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
215 -- Version : Current version 1.0
216 --
217 -- End of Comments
218 --
219 PROCEDURE RUN_WITH_PLAN_PARTY (
220 P_PLAN_ID IN NUMBER,
221 P_PARTY_ID IN NUMBER,
222 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
223
224 -- *******************************************************
225 -- Start of Comments
226 -- *******************************************************
227 -- API Name: RUN_WITH_PLAN_ACCOUNT
228 -- Type : Private
229 -- Pre-Req :
230 -- Function: Calls add_remove_plan_check with passed in plan_id and
231 -- account_id.
232 -- Parameters:
233 -- IN
234 -- P_PLAN_ID IN NUMBER
235 -- P_CUST_ACCOUNT_ID IN NUMBER
236 --
237 -- OUT NOCOPY
238 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
239 -- Version : Current version 1.0
240 --
241 -- End of Comments
242 --
243 PROCEDURE RUN_WITH_PLAN_ACCOUNT (
244 P_PLAN_ID IN NUMBER,
245 P_CUST_ACCOUNT_ID IN NUMBER,
246 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
247
248 -- *******************************************************
249 -- Start of Comments
250 -- *******************************************************
251 -- API Name: RUN_WITH_CHECK_PARTY
252 -- Type : Private
253 -- Pre-Req :
254 -- Function: Calls add_remove_plan_check with passed in check_id and
255 -- party_id.
256 -- Parameters:
257 -- IN
258 -- P_CHECK_ID IN NUMBER
259 -- P_PARTY_ID IN NUMBER
260 --
261 -- OUT NOCOPY
262 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
263 -- Version : Current version 1.0
264 --
265 -- End of Comments
266 --
267 PROCEDURE RUN_WITH_CHECK_PARTY (
268 P_CHECK_ID IN NUMBER,
269 P_PARTY_ID IN NUMBER,
270 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
271
272 -- *******************************************************
273 -- Start of Comments
274 -- *******************************************************
275 -- API Name: RUN_WITH_CHECK_ACCOUNT
276 -- Type : Private
277 -- Pre-Req :
278 -- Function: Calls add_remove_plan_check with passed in check_id and
279 -- account_id.
280 -- Parameters:
281 -- IN
282 -- P_CHECK_ID IN NUMBER
283 -- P_CUST_ACCOUNT_ID IN NUMBER
284 --
285 -- OUT NOCOPY
286 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
287 -- Version : Current version 1.0
288 --
289 -- End of Comments
290 --
291 PROCEDURE RUN_WITH_CHECK_ACCOUNT (
292 P_CHECK_ID IN NUMBER,
293 P_CUST_ACCOUNT_ID IN NUMBER,
294 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
295
296 -- *******************************************************
297 -- Start of Comments
298 -- *******************************************************
299 -- API Name: RUN_WITH_ALL
300 -- Type : Private
301 -- Pre-Req :
302 -- Function: Calls add_remove_plan_check with all plans and their
303 -- coresponding check results for all parties and accoutns.
304 -- Parameters:
305 -- IN
306 -- NONE
307 -- OUT NOCOPY
308 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
309 -- Version : Current version 1.0
310 --
311 -- End of Comments
312 --
313 PROCEDURE RUN_WITH_ALL (
314 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
315
316 -- *******************************************************
317 -- Start of Comments
318 -- *******************************************************
319 -- API Name: ADD_REMOVE_PLAN_CHECK
320 -- Type : Private
321 -- Pre-Req :
322 -- Function: Given the plan criteria, and the profile check result for each
323 -- customer, this procedure determines whether the customer is
324 -- eligible for the plan or not. If eligible, the customer is
325 -- assigned the plan throught the ADD_CUST_PLANS procedure, else
326 -- the cust.- plan association is delete by the DELETE_CUST_PLANS
327 -- procedure.
328 -- Parameters:
329 -- IN
330 -- P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE Required
331 -- P_RELATIONAL_OPERATOR_TBL IN CSC_CHAR_TBL_TYPE Required
332 -- P_CRITERIA_VALUE_LOW_TBL IN CSC_CHAR_TBL_TYPE Required
333 -- P_CRITERIA_VALUE_HIGH_TBL IN CSC_CHAR_TBL_TYPE Required
334 -- P_START_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE Required
335 -- P_END_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE Required
336 -- P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE Required
337 -- P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE Required
338 -- P_VALUE_TBL IN CSC_CHAR_TBL_TYPE Required
339 --
340 -- OUT NOCOPY
341 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
342 -- Version : Current version 1.0
343 --
344 -- End of Comments
345 --
346 PROCEDURE ADD_REMOVE_PLAN_CHECK (
350 G_EMPTY_CHAR_TBL,
347 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE :=
348 G_EMPTY_PLAN_ID_TBL,
349 P_RELATIONAL_OPERATOR_TBL IN CSC_CHAR_TBL_TYPE :=
351 P_CRITERIA_VALUE_LOW_TBL IN CSC_CHAR_TBL_TYPE :=
352 G_EMPTY_CHAR_TBL,
353 P_CRITERIA_VALUE_HIGH_TBL IN CSC_CHAR_TBL_TYPE :=
354 G_EMPTY_CHAR_TBL,
355 P_START_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE :=
356 G_EMPTY_DATE_TBL,
357 P_END_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE :=
358 G_EMPTY_DATE_TBL,
359 P_USE_FOR_CUST_ACCOUNT_TBL IN CSC_CHAR_TBL_TYPE :=
360 G_EMPTY_CHAR_TBL,
361 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE :=
362 G_EMPTY_PARTY_ID_TBL,
363 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE :=
364 G_EMPTY_CUST_ID_TBL,
365 P_VALUE_TBL IN CSC_CHAR_TBL_TYPE :=
366 G_EMPTY_CHAR_TBL,
367 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
368
369 -- *******************************************************
370 -- Start of Comments
371 -- *******************************************************
372 -- API Name: Add_Cust_Plans
373 -- Type : Private
374 -- Pre-Req :
375 -- Function: Creates record(s) in CSC_CUST_PLANS for those customers who are
376 -- eligible for the plan.
377 -- Note : Use BULK INSERTS rather that individual inserts through the API.
378 -- Parameters:
379 -- IN
380 -- p_plan_id_tbl IN CSC_PLAN_ID_TBL_TYPE
381 -- p_start_date_active_tbl IN CSC_DATE_TBL_TYPE
382 -- p_end_date_active_tbl IN CSC_DATE_TBL_TYPE
383 -- p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE
384 -- p_cust_id_tbl IN CSC_CUST_ID_TBL_TYPE
385 --
386 -- OUT NOCOPY :
387 -- x_return_status OUT NOCOPY VARCHAR2
388 -- Version : Current version 1.0
389 --
390 -- End of Comments
391 --
392
393 PROCEDURE ADD_CUST_PLANS (
394 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE,
395 P_START_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE,
396 P_END_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE,
397 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE,
398 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE,
399 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
400
401 -- *******************************************************
402 -- Start of Comments
403 -- *******************************************************
404 -- API Name: Delete_Cust_Plans
405 -- Type : Private
406 -- Pre-Req :
407 -- Function: Deletes records from CSC_CUST_PLANS, if customers are no more
408 -- eligible for that plan.
409 -- Note : Use BULK DELETES rather that individual deletes through the API.
410 -- Parameters:
411 -- IN
412 -- p_plan_id_tbl IN CSC_PLAN_ID_TBL_TYPE
413 -- p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE
414 -- p_cust_id_tbl IN CSC_CUST_ID_TBL_TYPE
415 --
416 -- OUT NOCOPY :
417 -- x_return_status OUT NOCOPY VARCHAR2
418 -- Version : Current version 1.0
419 --
420 -- End of Comments
421 --
422 PROCEDURE DELETE_CUST_PLANS (
423 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE,
424 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE,
425 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE,
426 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
427
428 -- *******************************************************
429 -- Start of Comments
430 -- *******************************************************
431 -- API Name: VALIDATE_COMPARE_ARGUMENTS
432 -- Type : Private
433 -- Pre-Req :
434 -- Function: The relational operator forms the basis of the check to be
435 -- performed between the profile result and the plan criteria
436 -- values. The relational operator can have the following
437 -- valid values:
438 -- =,<>,>, <, >=, <=, IN, NOT IN, LIKE, NOT LIKE, BETWEEN,
439 -- NOT BETWEEN, IS NULL and IS NOT NULL.
440 -- For the following relational operators it is required that
441 -- BOTH, criteria value low and high ARE SPECIFIED. ie. BETWEEN
442 -- and NOT BETWEEN.
443 -- For the following relational operators, BOTH the criteria
444 -- values, high and low SHOULD NOT be specified. ie. IS NULL,
445 -- IS NOT NULL.
446 -- For all other operators, ie. =, <>, >, <, >=, <=, IN, NOT IN,
447 -- LIKE and NOT LIKE, ONLY criteria value low should be
448 -- specified and criteria value high should be NULL.
449 -- This procedure does these validations.
450 -- Note :
451 -- Parameters:
452 -- IN
453 -- P_RELATIONAL_OPERATOR IN VARCHAR2
454 -- P_CRITERIA_VALUE_LOW IN VARCHAR2
455 -- P_CRITERIA_VALUE_HIGH IN VARCHAR2
456 --
457 -- OUT NOCOPY :
458 -- X_RETURN_STATUS OUT NOCOPY VARCHAR2
459 -- Version : Current version 1.0
460 --
461 -- End of Comments
462 --
463 PROCEDURE VALIDATE_COMPARE_ARGUMENTS (
464 P_RELATIONAL_OPERATOR IN VARCHAR2,
465 P_CRITERIA_VALUE_LOW IN VARCHAR2,
466 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
467 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
468
469 END CSC_PLAN_ASSIGNMENT_PKG;
[Home] [Help]