1 PACKAGE BODY CSC_PLAN_ASSIGNMENT_PKG as
2 /* $Header: cscvengb.pls 120.2 2006/04/06 22:33:00 vshastry 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 body.
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-16-1999 dejoseph Created.
14 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
15 -- 01-25-2000 dejoseph Added where condition in the NOT EXISTS sub query of the bulk
16 -- insert into CSC_CUST_PLANS. ie cust_account_id and org.
17 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
18 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
19 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
20 -- 03-28-2000 dejoseph Removed references to CUST_ACCOUNT_ID and ORG_ID from all
21 -- 'where' clauses. ie. and nvl(cust_account_org,0) =
22 -- nvl(p_cust_account_org, nvl(cust_account_org,0) )
23 -- Replaced call to HZ_CUST_ACCOUNT_ALL to HZ_CUST_ACCOUNTS.
24 -- 04-10-2000 dejoseph Following TCA changes done:
25 -- - Replaced references to HZ_ tables with JTF_ views.
26 -- - Removed all references to org_id and cust_account_org.
27 --
28 -- 04-28-2000 dejoseph Replaced reference to jtf_cust_accounts_v with jtf_cust_accounts_all_v;
29 -- 05-11-2000 dmontefa Added the 'OUT NOCOPY ' parameters, x_errbuf, x_retcode
30 -- 08-09-2000 dejoseph Modified engine to run for all plans and parties if no parameters
31 -- are specified. Fix to bug # 1372050.
32 -- 09-26-2001 dejoseph Made the following changes for 11.5.6: Ref bug# 1745488.
33 -- - Changed value of COMMIT_TIME from 25 to 1000;
34 -- - Included check to avoid plans being assigned to the wrong level;
35 -- ie. Account level plans being assigned to parties and vice-versa.
36 -- 01-30-2002 dejoseph Included the dbdrv command for DB driver generation.
37 -- Made the following change to clean up the Plans engine to
38 -- 1. Function as intended and 2. Perform efficiently.
39 -- Deleted the following procedures:
40 -- - RUN_PLAN_ENGINE -- overloaded proc. that accepts sql tables
41 -- - VALIDATE_PARAMETERS
42 -- - VALIDATE_PLAN_ID
43 -- - VALIDATE_PARTY_ID
44 -- - VALIDATE_CUST_ID_ORG
45 -- - GET_PARTIES
46 -- - GET_CUSTOMER_ACCOUNTS
47 -- - GET_PLANS_AT_PARTY_LEVEL
48 -- - GET_PLANS_AT_CUST_ACCT_LEVEL
49 -- - UPDATE_CUST_PLANS
50 -- - SELECT_CUST_PLAN_REC_EXISTS
51 -- Introduced the following procedures:
52 -- - RUN_WITH_ACCOUNT_ID
53 -- - RUN_WITH_PLAN_PARTY
54 -- - RUN_WITH_PLAN_ACCOUNT
55 -- - RUN_WITH_CHECK_PARTY
56 -- - RUN_WITH_CHECK_ACCOUNT
57 -- - RUN_WITH_ALL
58 -- Modified the cursor to join the plans header table and the check
59 -- results table, instead of selecting the plans first and then
60 -- selecting the results.
61 -- Ref. Bug #s - 2030164, 1745488
62 -- 03-14-2002 dejoseph - Corrected spelling mistake of the log message. Ref bug# 2232926
63 -- - Introduced new variable 'g_mesg_line' which stores single lines
64 -- of error text that is inserted into the log file as new lines.
65 -- - Generated meaningful error message into the log file when an invalid
66 -- set of parameters is given to the engine. Ref bug# 2250086.
67 -- - Corrected where clause in cursor get_details that had:
68 -- where b.cust_account_id = b.cust_account_id to
69 -- where b.cust_account_id is not null
70 -- 03-15-2002 dejoseph Added the checkfile command
71 -- 11-12-2002 bhroy NOCOPY changes made
72 -- 11-28-2002 bhroy FND_API.G_MISS_XXX defaults removed
73 -- 07-11-2005 tpalaniv Bug 4628149 Changing all the procedures to include
74 -- the bulk limit in the fetch statement
75 -- 07-04-2006 vshastry Bug 5073490 Added loop in all the procedures using bulk collect with limit
76 -- 07-04-2006 vshastry Bug 5073490 closing the cursors out of the loop
77 -- NOTE :
78 -- End of Comments
79 --
80
81 -- **** LIST OF GLOBAL VARIABLE AND CONSTANTS USED IN THE PACKAGE BODY *****
82
83 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_PLAN_ASSIGNMENT_PKG';
84 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvengb.pls';
85 G_COMMIT_TIME CONSTANT NUMBER := 1000;
86 -- no. of recs that are inserted into CSC_CUST_PLANS
87 G_ERRBUF VARCHAR2(2000) := NULL;
88 -- the error text from SQLERRM
89 G_MESG VARCHAR2(2000); -- message into log files.
90 G_MESG_LINE VARCHAR2(2000); -- has single lines of error text
91
92 -- The following tables stores the column values of all the plans that
93 -- a customer satisfies. It has an 'add' in its name to denote that these
94 -- plans need to be added to this customer in the CSC_CUST_PLANS table.
95 G_PLAN_ID_ADD_TBL CSC_PLAN_ID_TBL_TYPE;
96 G_PARTY_ID_ADD_TBL CSC_PARTY_ID_TBL_TYPE;
97 G_CUST_ID_ADD_TBL CSC_CUST_ID_TBL_TYPE;
98 G_START_DATE_ACTIVE_ADD_TBL CSC_DATE_TBL_TYPE;
99 G_END_DATE_ACTIVE_ADD_TBL CSC_DATE_TBL_TYPE;
100 G_ADD_IDX NUMBER := 0; -- index for add tables
101
102 -- The following tables stores plan and customer information, when a
103 -- customer no longer satisfies a plan criteria. It has a 'del' to its
104 -- name to denote that these rows need to be deleted from CSC_CUST_PLANS
105 -- table.
106 G_PLAN_ID_DEL_TBL CSC_PLAN_ID_TBL_TYPE;
107 G_PARTY_ID_DEL_TBL CSC_PARTY_ID_TBL_TYPE;
108 G_CUST_ID_DEL_TBL CSC_CUST_ID_TBL_TYPE;
109 G_DEL_IDX NUMBER := 0; -- index for delete tables
110
111
112
113 -- ********* END DECLARATION OF GLOBAL VARIABLES AND CONSTANTS. **************
114
115 /************* OVERVIEW OF HOW THE PLAN ENGINE CAN BE EXECUTED ****************
116
117 1> Given a list of plan_id(s) this is what should be done:
118 - get the profile variables and plan levels (either party or account level)
119 associated with each of those plans from the plan headers table
120 csc_plan_headers_b.
121 - if the plan is at party level then
122 - for each of those profile variables (check_ids), get the result for all
123 parties (only, not accounts) from the results table.
124 - else if the plan is at account level then
125 - for each of those profile variables (check_ids), get the result for all
126 accounts (only, not parties) from the results table.
127 - compare the result of each party or account, with the plan criteria.
128 - associate the plan if the party/account is eligible for it.
129 2> Given a list of check id(s) this is what should be done:
130 - get the results for each party or account for each of those check_ids from
131 the results table.
132 - get all the plans and their criteria (relational_operator,criteria_value_low
133 and high) that are defined with the given check_ids.
134 - compare the results of each party/account with the plan criteria.
135 - associate the plan if the party/account is eligible for it.
136 3> Given a list of party_id(s) or account(s) this is what should be done:
137 - get the results and check_ids for each of the given parties or accounts from
138 the results table.
139 - get all the plans and their criteria (relational_operator, criteria_value_low
140 and high) for each of the returned back check_ids.
141 - compare the results of each party/account with the plan criteria.
142 - associate the plan if the party/account is elIgible for it.
143
144 ********************************************************************************/
145
146 PROCEDURE RUN_PLAN_ENGINE (
147 X_ERRBUF OUT NOCOPY VARCHAR2,
148 X_RETCODE OUT NOCOPY NUMBER ,
149 P_PLAN_ID IN NUMBER := NULL,
150 P_CHECK_ID IN NUMBER := NULL,
151 P_PARTY_ID IN NUMBER := NULL,
152 P_CUST_ACCOUNT_ID IN NUMBER := NULL )
153 IS
154 l_call NUMBER; -- see comments below
155
156 l_return_status VARCHAR2(1); -- used to return a success of failure
157 -- status
158
159 /*
160 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE := G_EMPTY_PLAN_ID_TBL;
161 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE := G_EMPTY_CHECK_ID_TBL;
162 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE := G_EMPTY_PARTY_ID_TBL;
163 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE := G_EMPTY_CUST_ID_TBL;
164 */
165
166
167 BEGIN
168 -- l_call is used to determine which procedure to call, depending on what
169 -- parameters are passed; The legend for l_call is as follows:
170 -- 1 => Engine called with plan id
171 -- 2 => Engine called with check id
172 -- 3 => Engine called with party id
173 -- 4 => Engine called with account id
174 -- 12 => Engine called with plan id and check id
175 -- 13 => Engine called with plan id and party id
176 -- 14 => Engine called with plan id and account id
177 -- 23 => Engine called with check id and party id
178 -- 24 => Engine called with check id and account id
179 -- 34 => Engine called with party id and account id
180 -- 123 => Engine called with plan id, check id and party id
181 -- 124 => Engine called with plan id, check id and account id
182 -- 134 => Engine called with plan id, party id and account id
183 -- 234 => Engine called with check id, party id and account id
184 --1234 => Engine called with plan id, check id, party id and account id
185 -- 0 => Engine called with no parameters.
186
187 l_call := 0; -- initialize l_call to no parameters passed.
188
189 IF ( p_plan_id IS NOT NULL ) THEN
190 l_call := 1;
191 END IF;
192
193 IF ( p_check_id IS NOT NULL ) THEN
194 if ( l_call = 1 ) then
195 l_call := 12;
196 else
197 l_call := 2;
198 end if;
199 END IF;
200
201 IF ( p_party_id IS NOT NULL ) THEN
202 if ( l_call = 1 ) then
203 l_call := 13;
204 elsif ( l_call = 2 ) then
205 l_call := 23;
206 elsif ( l_call = 12 ) then
207 l_call := 123;
208 else
209 l_call := 3;
210 end if;
211 END IF;
212
213 IF ( p_cust_account_id IS NOT NULL ) THEN
214 if ( l_call = 1 ) then
215 l_call := 14;
216 elsif ( l_call = 2 ) then
217 l_call := 24;
218 elsif ( l_call = 3 ) then
219 l_call := 34;
220 elsif ( l_call = 12 ) then
221 l_call := 124;
222 elsif ( l_call = 13 ) then
223 l_call := 134;
224 elsif ( l_call = 23 ) then
225 l_call := 234;
226 elsif ( l_call = 123 ) then
227 l_call := 1234;
228 else
229 l_call := 4;
230 end if;
231 END IF;
232
233 -- because of the dependency between
234 -- i) checks and plans (a plan can be tied to only one check)
235 -- and ii) party and account (an account can be tied to only one party)
236 -- the call to the required procedure depending on the input parameters are
237 -- sometimes redundant. For eg. the call to run the engine with only the plan id
238 -- parameter will be made for the following cases:
239 -- i) user enters a specific plan id
240 -- ii) user enters a specific plan id and check id
241 -- Similarily there are other cases as well. The following call out logic is based
242 -- on the above example.
243
244 if ( l_call = 1 OR l_call = 12 ) then
245 -- exec. run_with_plan_id with a single plan_id
246 run_with_plan_id(
247 p_plan_id => p_plan_id,
248 x_return_status => l_return_status);
249
250 elsif ( l_call = 2 ) then
251 -- get all the plan details that share this same check_id
252 -- exec. run_with_plan_id with a list of theses plan_ids
253 -- run_with_check_id; -- same proc. as just giving a plan id
254 run_with_check_id (
255 p_check_id => p_check_id,
256 x_return_status => l_return_status);
257
258 elsif ( l_call = 3 ) then
259 -- get all the plan details at party_level only
260 -- get the coresponding results for the single party_id
261 run_with_party_id (
262 p_party_id => p_party_id,
263 x_return_status => l_return_status);
264
265 elsif ( l_call = 4 OR l_call = 34 ) then
266 -- get all the plan details at account level only
267 -- get the results for given account for the given check_id
268 run_with_account_id (
269 p_cust_account_id => p_cust_account_id,
270 x_return_status => l_return_status);
271
272 elsif ( l_call = 13 OR l_call = 123 ) then
273 -- get the plan details for the given plan_id. the plan should
274 -- be at party level, else throw an error message
275 -- get the results for the given party for the given check_id
276 run_with_plan_party (
277 p_plan_id => p_plan_id,
278 p_party_id => p_party_id,
279 x_return_status => l_return_status);
280
281 elsif ( l_call = 14 OR l_call = 124 OR l_call = 134 OR l_call = 1234 ) then
282 -- get the plan details for the given plan_id. the plan should
283 -- be at account level, else throw an error message
284 -- get the results for the given account for the given check_id
285 run_with_plan_account (
286 p_plan_id => p_plan_id,
287 p_cust_account_id => p_cust_account_id,
288 x_return_status => l_return_status);
289
290 elsif ( l_call = 23 ) then
291 -- get all the plans details that share this same check_id at
292 -- party level only
293 -- get the results for the given party id
294 run_with_check_party (
295 p_check_id => p_check_id,
296 p_party_id => p_party_id,
297 x_return_status => l_return_status);
298
299 elsif ( l_call = 24 OR l_call = 234 ) then
300 -- get all the plan details that share this same check_id at
301 -- account level only
302 -- get the results for the given account_id
303 run_with_check_account (
304 p_check_id => p_check_id,
305 p_cust_account_id => p_cust_account_id,
306 x_return_status => l_return_status);
307
308 elsif ( l_call = 0 ) then
309 -- get all plan details
310 -- get all party results
311 run_with_all (
312 x_return_status => l_return_status);
313
314 end if;
315
316 IF (g_errbuf is not null) THEN
317 x_errbuf := G_ERRBUF;
318 x_retcode := 2;
319 ELSE
320 x_errbuf := '';
321 x_retcode := 0;
322 END IF;
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 g_mesg := sqlcode || ' ' || sqlerrm;
327 fnd_file.put_line(fnd_file.log, g_mesg);
328 G_ERRBUF := g_mesg;
329
330 END RUN_PLAN_ENGINE;
331
332 -- The following procedure will be executed when
333 -- 1. User enters a plan_id
334
335 PROCEDURE RUN_WITH_PLAN_ID (
336 P_PLAN_ID IN NUMBER,
337 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
338 IS
339 -- get the plan details and the coresponding check results
340 -- NOTE: Get the check results according to the level of the plan. ie. get
341 -- account level results only if the plan is defined for account level.
342 cursor get_details is
343 select a.plan_id, a.profile_check_id, a.relational_operator,
344 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
345 a.end_date_active, a.use_for_cust_account,
346 b.party_id, b.cust_account_id, b.value
347 from csc_plan_headers_b a,
348 csc_prof_check_results b
349 where a.profile_check_id = b.check_id
350 and a.plan_id = p_plan_id
351 and a.customized_plan = 'N'
352 and ( ( a.use_for_cust_account = 'N'
353 and b.cust_account_id is null )
354 OR ( a.use_for_cust_account = 'Y'
355 and b.cust_account_id is not null ) )
356 and sysdate between nvl(a.start_date_active, sysdate )
357 and nvl(a.end_date_active, sysdate );
358
359 -- local variable declaration
360 l_check_name VARCHAR2(240);
361
362 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
363 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
364 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
365 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
366 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
367 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
368 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
369 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
370
371 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
372 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
373 l_value_tbl CSC_CHAR_TBL_TYPE;
374 BEGIN
375
376 open get_details;
377 Loop
378 fetch get_details
379 bulk collect into
380 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
381 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
382 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
383 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included the limit */
384
385 if ( l_plan_id_tbl.count = 0 ) then
386 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
387 'Please enter a valid set of parameters.';
388 G_ERRBUF := g_mesg;
389 g_mesg_line := 'Causes:';
390 fnd_file.put_line(fnd_file.log, g_mesg_line);
391 g_mesg := g_mesg || g_mesg_line;
392 g_mesg_line := '- Specified plan is either customized or has expired.';
393 fnd_file.put_line(fnd_file.log, g_mesg_line);
394 g_mesg := g_mesg || g_mesg_line;
395 g_mesg_line := '- Profile results for the check associated to the specified plan ' ||
396 'have not been populated.';
397 fnd_file.put_line(fnd_file.log, g_mesg_line);
398 g_mesg := g_mesg || g_mesg_line;
399 g_mesg_line := 'Action';
400 fnd_file.put_line(fnd_file.log, g_mesg_line);
401 g_mesg := g_mesg || g_mesg_line;
402 g_mesg_line := '- Specify plans that are not customized and have not expired.';
403 fnd_file.put_line(fnd_file.log, g_mesg_line);
404 g_mesg := g_mesg || g_mesg_line;
405 g_mesg_line := '- Run the Profile Engine to populate the results for the check ' ||
406 'name associated to the specified plan.';
407 fnd_file.put_line(fnd_file.log, g_mesg_line);
408 g_mesg := g_mesg || g_mesg_line;
409 else
410 -- call the procedure to make/break the customer - plan association
411 add_remove_plan_check (
412 P_PLAN_ID_TBL => l_plan_id_tbl,
413 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
414 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
415 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
416 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
417 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
418 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
419 P_PARTY_ID_TBL => l_party_id_tbl,
420 P_CUST_ID_TBL => l_cust_id_tbl,
421 P_VALUE_TBL => l_value_tbl,
422 X_RETURN_STATUS => x_return_status );
423 end if;
424
425 Exit when get_details%NOTFOUND;
426 End Loop;
427 close get_details;
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 g_mesg := sqlcode || ' ' || sqlerrm;
432 fnd_file.put_line(fnd_file.log, g_mesg);
433 G_ERRBUF := g_mesg;
434
435 END RUN_WITH_PLAN_ID; -- end for main begin of run_with_plan_id
436
437
438 -- The following procedure will be executed when
439 -- 1. User enters a check_id
440
441 PROCEDURE RUN_WITH_CHECK_ID (
442 P_CHECK_ID IN NUMBER,
443 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
444 IS
445 -- get the plan details and the coresponding check results
446 -- NOTE: Get the check results according to the level of the plan. ie. get
447 -- account level results only if the plan is defined for account level.
448 cursor get_details is
449 select a.plan_id, a.profile_check_id, a.relational_operator,
450 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
451 a.end_date_active, a.use_for_cust_account,
452 b.party_id, b.cust_account_id, b.value
453 from csc_plan_headers_b a,
454 csc_prof_check_results b
455 where a.profile_check_id = b.check_id
456 and a.profile_check_id = p_check_id
457 and a.customized_plan = 'N'
458 and ( ( a.use_for_cust_account = 'N'
459 and b.cust_account_id is null )
460 OR ( a.use_for_cust_account = 'Y'
461 and b.cust_account_id is not null ) )
462 and sysdate between nvl(a.start_date_active, sysdate )
463 and nvl(a.end_date_active, sysdate );
464
465 -- local variable declaration
466 l_check_name VARCHAR2(240);
467
468 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
469 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
470 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
471 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
472 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
473 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
474 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
475 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
476
477 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
478 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
479 l_value_tbl CSC_CHAR_TBL_TYPE;
480 BEGIN
481
482 open get_details;
483 Loop
484 fetch get_details
485 bulk collect into
486 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
487 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
488 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
489 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included limit clause */
490
491 if ( l_plan_id_tbl.count = 0 ) then
492 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
493 'Please enter a valid set of parameters.';
494 G_ERRBUF := g_mesg;
495 g_mesg_line := 'Causes:';
496 fnd_file.put_line(fnd_file.log, g_mesg_line);
497 g_mesg := g_mesg || g_mesg_line;
498 g_mesg_line := '- Plans for the specified check are either customized or have expired.';
499 fnd_file.put_line(fnd_file.log, g_mesg_line);
500 g_mesg := g_mesg || g_mesg_line;
501 g_mesg_line := '- Profile results for the specified check have not been populated.';
502 fnd_file.put_line(fnd_file.log, g_mesg_line);
503 g_mesg := g_mesg || g_mesg_line;
504 g_mesg_line := 'Action';
505 fnd_file.put_line(fnd_file.log, g_mesg_line);
506 g_mesg := g_mesg || g_mesg_line;
507 g_mesg_line := '- Specify checks associated to plans that are not customized and have not expired.';
508 fnd_file.put_line(fnd_file.log, g_mesg_line);
509 g_mesg := g_mesg || g_mesg_line;
510 g_mesg_line := '- Run the Profile Engine to populate the results for the specified check.';
511 fnd_file.put_line(fnd_file.log, g_mesg_line);
512 g_mesg := g_mesg || g_mesg_line;
513 else
514 -- call the procedure to make/break the customer - plan association
515 add_remove_plan_check (
516 P_PLAN_ID_TBL => l_plan_id_tbl,
517 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
518 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
519 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
520 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
521 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
522 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
523 P_PARTY_ID_TBL => l_party_id_tbl,
524 P_CUST_ID_TBL => l_cust_id_tbl,
525 P_VALUE_TBL => l_value_tbl,
526 X_RETURN_STATUS => x_return_status );
527 end if;
528
529 Exit when get_details%NOTFOUND;
530 End Loop;
531 close get_details;
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 g_mesg := sqlcode || ' ' || sqlerrm;
536 fnd_file.put_line(fnd_file.log, g_mesg);
537 G_ERRBUF := g_mesg;
538
539 END RUN_WITH_CHECK_ID; -- end for main begin of run_with_plan_id
540
541
542 -- The following procedure will be executed when
543 -- 1. User enters a party_id
544
545 PROCEDURE RUN_WITH_PARTY_ID (
546 P_PARTY_ID IN NUMBER,
547 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
548 IS
549
550 cursor get_details is
551 select a.plan_id, a.profile_check_id, a.relational_operator,
552 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
553 a.end_date_active, a.use_for_cust_account,
554 b.party_id, b.cust_account_id, b.value
555 from csc_plan_headers_b a,
556 csc_prof_check_results b
557 where a.profile_check_id = b.check_id
558 and a.customized_plan = 'N'
559 and b.party_id = p_party_id
560 and b.cust_account_id is NULL
561 and a.use_for_cust_account = 'N'
562 and sysdate between nvl(a.start_date_active, sysdate )
563 and nvl(a.end_date_active, sysdate );
564
565 -- local variable declaration
566 l_check_name VARCHAR2(240);
567
568 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
569 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
570 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
571 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
572 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
573 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
574 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
575 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
576
577 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
578 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
579 l_value_tbl CSC_CHAR_TBL_TYPE;
580 BEGIN
581 open get_details;
582 Loop
583 fetch get_details
584 bulk collect into
585 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
586 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
587 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
588 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148 : Included Limit clause */
589
590 if ( l_plan_id_tbl.count = 0 ) then
591 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
592 'Please enter a valid set of parameters.';
593 G_ERRBUF := g_mesg;
594 g_mesg_line := 'Causes:';
595 fnd_file.put_line(fnd_file.log, g_mesg_line);
596 g_mesg := g_mesg || g_mesg_line;
597 g_mesg_line := '- All plans have expired.';
598 fnd_file.put_line(fnd_file.log, g_mesg_line);
599 g_mesg := g_mesg || g_mesg_line;
600 g_mesg_line := '- Profile results for the specified party have not been populated.';
601 fnd_file.put_line(fnd_file.log, g_mesg_line);
602 g_mesg := g_mesg || g_mesg_line;
603 g_mesg_line := 'Action';
604 fnd_file.put_line(fnd_file.log, g_mesg_line);
605 g_mesg := g_mesg || g_mesg_line;
606 g_mesg_line := '- Verify that there are active plans. (ie. end date of plans are a future date)';
607 fnd_file.put_line(fnd_file.log, g_mesg_line);
608 g_mesg := g_mesg || g_mesg_line;
609 g_mesg_line := '- Run the Profile Engine to populate the results for the specified party.';
610 fnd_file.put_line(fnd_file.log, g_mesg_line);
611 g_mesg := g_mesg || g_mesg_line;
612 else
613 -- call the procedure to make/break the customer - plan association
614 add_remove_plan_check (
615 P_PLAN_ID_TBL => l_plan_id_tbl,
616 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
617 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
618 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
619 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
620 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
621 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
622 P_PARTY_ID_TBL => l_party_id_tbl,
623 P_CUST_ID_TBL => l_cust_id_tbl,
624 P_VALUE_TBL => l_value_tbl,
625 X_RETURN_STATUS => x_return_status );
626 end if;
627
628 Exit when get_details%NOTFOUND;
629 End Loop;
630 close get_details;
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 g_mesg := sqlcode || ' ' || sqlerrm;
635 fnd_file.put_line(fnd_file.log, g_mesg);
636 G_ERRBUF := g_mesg;
637
638 END RUN_WITH_PARTY_ID;
639
640
641 -- The following procedure will be executed when
642 -- 1. User enters an account_id
643
644 PROCEDURE RUN_WITH_ACCOUNT_ID (
645 P_CUST_ACCOUNT_ID IN NUMBER,
646 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
647 IS
648
649 cursor get_details is
650 select a.plan_id, a.profile_check_id, a.relational_operator,
651 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
652 a.end_date_active, a.use_for_cust_account,
653 b.party_id, b.cust_account_id, b.value
654 from csc_plan_headers_b a,
655 csc_prof_check_results b
656 where a.profile_check_id = b.check_id
657 and a.customized_plan = 'N'
658 and b.cust_account_id = p_cust_account_id
659 and a.use_for_cust_account = 'Y'
660 and sysdate between nvl(a.start_date_active, sysdate )
661 and nvl(a.end_date_active, sysdate );
662
663 -- local variable declaration
664 l_check_name VARCHAR2(240);
665
666 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
667 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
668 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
669 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
670 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
671 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
672 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
673 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
674
675 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
676 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
677 l_value_tbl CSC_CHAR_TBL_TYPE;
678 BEGIN
679 open get_details;
680 Loop
681 fetch get_details
682 bulk collect into
683 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
684 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
685 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
686 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included limit clause */
687
688 if ( l_plan_id_tbl.count = 0 ) then
689 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
690 'Please enter a valid set of parameters.';
691 G_ERRBUF := g_mesg;
692 g_mesg_line := 'Causes:';
693 fnd_file.put_line(fnd_file.log, g_mesg_line);
694 g_mesg := g_mesg || g_mesg_line;
695 g_mesg_line := '- All plans have expired.';
696 fnd_file.put_line(fnd_file.log, g_mesg_line);
697 g_mesg := g_mesg || g_mesg_line;
698 g_mesg_line := '- Profile results for the specified account have not been populated.';
699 fnd_file.put_line(fnd_file.log, g_mesg_line);
700 g_mesg := g_mesg || g_mesg_line;
701 g_mesg_line := 'Action';
702 fnd_file.put_line(fnd_file.log, g_mesg_line);
703 g_mesg := g_mesg || g_mesg_line;
704 g_mesg_line := '- Verify that there are active plans. (ie. end date of plans are a future date)';
705 fnd_file.put_line(fnd_file.log, g_mesg_line);
706 g_mesg := g_mesg || g_mesg_line;
707 g_mesg_line := '- Run the Profile Engine to populate the results for the specified account.';
708 fnd_file.put_line(fnd_file.log, g_mesg_line);
709 g_mesg := g_mesg || g_mesg_line;
710 else
711 -- call the procedure to make/break the customer - plan association
712 add_remove_plan_check (
713 P_PLAN_ID_TBL => l_plan_id_tbl,
714 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
715 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
716 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
717 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
718 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
719 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
720 P_PARTY_ID_TBL => l_party_id_tbl,
721 P_CUST_ID_TBL => l_cust_id_tbl,
722 P_VALUE_TBL => l_value_tbl,
723 X_RETURN_STATUS => x_return_status );
724 end if;
725
726 Exit when get_details%NOTFOUND;
727 End Loop;
728 close get_details;
729
730
731 EXCEPTION
732 WHEN OTHERS THEN
733 g_mesg := sqlcode || ' ' || sqlerrm;
734 fnd_file.put_line(fnd_file.log, g_mesg);
735 G_ERRBUF := g_mesg;
736
737 END RUN_WITH_ACCOUNT_ID;
738
739
740
741 -- The following procedure will be executed when
742 -- 1. User enters a plan_id and party_id
743 -- 2. User enters a plan_id, check_id and a party_id
744
745 PROCEDURE RUN_WITH_PLAN_PARTY (
746 P_PLAN_ID IN NUMBER,
747 P_PARTY_ID IN NUMBER,
748 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
749 IS
750
751 cursor get_details is
752 select a.plan_id, a.profile_check_id, a.relational_operator,
753 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
754 a.end_date_active, a.use_for_cust_account,
755 b.party_id, b.cust_account_id, b.value
756 from csc_plan_headers_b a,
757 csc_prof_check_results b
758 where a.profile_check_id = b.check_id
759 and a.plan_id = p_plan_id
760 and a.customized_plan = 'N'
761 and b.party_id = p_party_id
762 and b.cust_account_id is NULL
763 and a.use_for_cust_account = 'N'
764 and sysdate between nvl(a.start_date_active, sysdate )
765 and nvl(a.end_date_active, sysdate ) ;
766
767 -- local variable declaration
768 l_check_name VARCHAR2(240);
769
770 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
771 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
772 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
773 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
774 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
775 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
776 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
777 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
778
779 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
780 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
781 l_value_tbl CSC_CHAR_TBL_TYPE;
782 BEGIN
783
784 open get_details;
785 Loop
786 fetch get_details
787 bulk collect into
788 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
789 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
790 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
791 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included Limit clause */
792
793 if ( l_plan_id_tbl.count = 0 ) then
794 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
795 'Please enter a valid set of parameters.';
796 G_ERRBUF := g_mesg;
797 g_mesg_line := 'Causes:';
798 fnd_file.put_line(fnd_file.log, g_mesg_line);
799 g_mesg := g_mesg || g_mesg_line;
800 g_mesg_line := '- Specified plan is either customized or has expired.';
801 fnd_file.put_line(fnd_file.log, g_mesg_line);
802 g_mesg := g_mesg || g_mesg_line;
803 g_mesg_line := '- Specified plan is an account level plan.';
804 fnd_file.put_line(fnd_file.log, g_mesg_line);
805 g_mesg := g_mesg || g_mesg_line;
806 g_mesg_line := '- Profile results for the specified party and/or check of the ' ||
807 'specified plan have not been populated.';
808 fnd_file.put_line(fnd_file.log, g_mesg_line);
809 g_mesg := g_mesg || g_mesg_line;
810 g_mesg_line := 'Action';
811 fnd_file.put_line(fnd_file.log, g_mesg_line);
812 g_mesg := g_mesg || g_mesg_line;
813 g_mesg_line := '- Specify plans that are not customized and have not expired.';
814 fnd_file.put_line(fnd_file.log, g_mesg_line);
815 g_mesg := g_mesg || g_mesg_line;
816 g_mesg_line := '- Specify plans that are not account level plans when specifing a party together.';
817 fnd_file.put_line(fnd_file.log, g_mesg_line);
818 g_mesg := g_mesg || g_mesg_line;
819 g_mesg_line := '- Run the Profile Engine to populate the results for the specified ' ||
820 'party and/or check of the specified plan.';
821 fnd_file.put_line(fnd_file.log, g_mesg_line);
822 g_mesg := g_mesg || g_mesg_line;
823 else
824 -- call the procedure to make/break the customer - plan association
825 add_remove_plan_check (
826 P_PLAN_ID_TBL => l_plan_id_tbl,
827 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
828 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
829 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
830 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
831 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
832 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
833 P_PARTY_ID_TBL => l_party_id_tbl,
834 P_CUST_ID_TBL => l_cust_id_tbl,
835 P_VALUE_TBL => l_value_tbl,
836 X_RETURN_STATUS => x_return_status );
837 end if;
838
839 Exit when get_details%NOTFOUND;
840 End Loop;
841 close get_details;
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 g_mesg := sqlcode || ' ' || sqlerrm;
846 fnd_file.put_line(fnd_file.log, g_mesg);
847 G_ERRBUF := g_mesg;
848
849 END RUN_WITH_PLAN_PARTY;
850
851 -- The following procedure will be executed when
852 -- 1. User enters a plan_id and account_id
853 -- 2. User enters a plan_id, check_id and account_id
854 -- 3. User enters a plan_id, party_id and account_id
855 -- 4. User enters a plan_id, check_id, party_id and account_id
856
857 PROCEDURE RUN_WITH_PLAN_ACCOUNT (
858 P_PLAN_ID IN NUMBER,
859 P_CUST_ACCOUNT_ID IN NUMBER,
860 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
861 IS
862 cursor get_details is
863 select a.plan_id, a.profile_check_id, a.relational_operator,
864 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
865 a.end_date_active, a.use_for_cust_account,
866 b.party_id, b.cust_account_id, b.value
867 from csc_plan_headers_b a,
868 csc_prof_check_results b
869 where a.profile_check_id = b.check_id
870 and a.plan_id = p_plan_id
871 and a.customized_plan = 'N'
872 and b.cust_account_id = p_cust_account_id
873 and a.use_for_cust_account = 'Y'
874 and sysdate between nvl(a.start_date_active, sysdate )
875 and nvl(a.end_date_active, sysdate ) ;
876
877 -- local variable declaration
878 l_check_name VARCHAR2(240);
879
880 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
881 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
882 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
883 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
884 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
885 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
886 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
887 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
888
889 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
890 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
891 l_value_tbl CSC_CHAR_TBL_TYPE;
892 BEGIN
893
894 open get_details;
895 Loop
896 fetch get_details
897 bulk collect into
898 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
899 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
900 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
901 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included Limit clause */
902
903 if ( l_plan_id_tbl.count = 0 ) then
904 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
905 'Please enter a valid set of parameters.';
906 G_ERRBUF := g_mesg;
907 g_mesg_line := 'Causes:';
908 fnd_file.put_line(fnd_file.log, g_mesg_line);
909 g_mesg := g_mesg || g_mesg_line;
910 g_mesg_line := '- Specified plan is either customized or has expired.';
911 fnd_file.put_line(fnd_file.log, g_mesg_line);
912 g_mesg := g_mesg || g_mesg_line;
913 g_mesg_line := '- Specified plan is not an account level plan.';
914 fnd_file.put_line(fnd_file.log, g_mesg_line);
915 g_mesg := g_mesg || g_mesg_line;
916 g_mesg_line := '- Profile results for the specified account and/or check of the ' ||
917 'specified plan have not been populated.';
918 fnd_file.put_line(fnd_file.log, g_mesg_line);
919 g_mesg := g_mesg || g_mesg_line;
920 g_mesg_line := 'Action';
921 fnd_file.put_line(fnd_file.log, g_mesg_line);
922 g_mesg := g_mesg || g_mesg_line;
923 g_mesg_line := '- Specify plans that are not customized and have not expired.';
924 fnd_file.put_line(fnd_file.log, g_mesg_line);
925 g_mesg := g_mesg || g_mesg_line;
926 g_mesg_line := '- Specify plans that are account level plans when specifing an account together.';
927 fnd_file.put_line(fnd_file.log, g_mesg_line);
928 g_mesg := g_mesg || g_mesg_line;
929 g_mesg_line := '- Run the Profile Engine to populate the results for the ' ||
930 'specified account and/or check of the specified plan.';
931 fnd_file.put_line(fnd_file.log, g_mesg_line);
932 g_mesg := g_mesg || g_mesg_line;
933 else
934 -- call the procedure to make/break the customer - plan association
935 add_remove_plan_check (
936 P_PLAN_ID_TBL => l_plan_id_tbl,
937 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
938 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
939 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
940 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
941 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
942 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
943 P_PARTY_ID_TBL => l_party_id_tbl,
944 P_CUST_ID_TBL => l_cust_id_tbl,
945 P_VALUE_TBL => l_value_tbl,
946 X_RETURN_STATUS => x_return_status );
947 end if;
948
949 Exit when get_details%NOTFOUND;
950 End Loop;
951 close get_details;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 g_mesg := sqlcode || ' ' || sqlerrm;
956 fnd_file.put_line(fnd_file.log, g_mesg);
957 G_ERRBUF := g_mesg;
958
959 END RUN_WITH_PLAN_ACCOUNT;
960
961 -- The following procedure will be executed when
962 -- 1. User enters a check_id and party_id
963
964 PROCEDURE RUN_WITH_CHECK_PARTY (
965 P_CHECK_ID IN NUMBER,
966 P_PARTY_ID IN NUMBER,
967 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
968 IS
969 cursor get_details is
970 select a.plan_id, a.profile_check_id, a.relational_operator,
971 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
972 a.end_date_active, a.use_for_cust_account,
973 b.party_id, b.cust_account_id, b.value
974 from csc_plan_headers_b a,
975 csc_prof_check_results b
976 where a.profile_check_id = b.check_id
977 and a.profile_check_id = p_check_id
978 and a.customized_plan = 'N'
979 and b.party_id = p_party_id
980 and b.cust_account_id is null
981 and a.use_for_cust_account = 'N'
982 and sysdate between nvl(a.start_date_active, sysdate )
983 and nvl(a.end_date_active, sysdate ) ;
984
985 -- local variable declaration
986 l_check_name VARCHAR2(240);
987
988 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
989 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
990 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
991 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
992 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
993 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
994 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
995 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
996
997 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
998 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
999 l_value_tbl CSC_CHAR_TBL_TYPE;
1000 BEGIN
1001
1002 open get_details;
1003 Loop
1004 fetch get_details
1005 bulk collect into
1006 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
1007 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
1008 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
1009 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included Limit clause */
1010
1011 if ( l_plan_id_tbl.count = 0 ) then
1012 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
1013 'Please enter a valid set of parameters.';
1014 G_ERRBUF := g_mesg;
1015 g_mesg_line := 'Causes:';
1016 fnd_file.put_line(fnd_file.log, g_mesg_line);
1017 g_mesg := g_mesg || g_mesg_line;
1018 g_mesg_line := '- Plans for the specified check are either customized or have expired.';
1019 fnd_file.put_line(fnd_file.log, g_mesg_line);
1020 g_mesg := g_mesg || g_mesg_line;
1021 g_mesg_line := '- Plans for the specified check are account level plans.';
1022 fnd_file.put_line(fnd_file.log, g_mesg_line);
1023 g_mesg := g_mesg || g_mesg_line;
1024 g_mesg_line := '- Profile results for the specified check and/or party have not been populated.';
1025 fnd_file.put_line(fnd_file.log, g_mesg_line);
1026 g_mesg := g_mesg || g_mesg_line;
1027 g_mesg_line := 'Action';
1028 fnd_file.put_line(fnd_file.log, g_mesg_line);
1029 g_mesg := g_mesg || g_mesg_line;
1030 g_mesg_line := '- Specify checks associated to plans that are not customized and have not expired.';
1031 fnd_file.put_line(fnd_file.log, g_mesg_line);
1032 g_mesg := g_mesg || g_mesg_line;
1033 g_mesg_line := '- Specify checks associated to plans that are not at account level.';
1034 fnd_file.put_line(fnd_file.log, g_mesg_line);
1035 g_mesg := g_mesg || g_mesg_line;
1036 g_mesg_line := '- Run the Profile Engine to populate the results for the ' ||
1037 'specified party and/or specified check.';
1038 fnd_file.put_line(fnd_file.log, g_mesg_line);
1039 g_mesg := g_mesg || g_mesg_line;
1040 else
1041 -- call the procedure to make/break the customer - plan association
1042 add_remove_plan_check (
1043 P_PLAN_ID_TBL => l_plan_id_tbl,
1044 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
1045 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
1046 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
1047 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
1048 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
1049 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
1050 P_PARTY_ID_TBL => l_party_id_tbl,
1051 P_CUST_ID_TBL => l_cust_id_tbl,
1052 P_VALUE_TBL => l_value_tbl,
1053 X_RETURN_STATUS => x_return_status );
1054 end if;
1055
1056 Exit when get_details%NOTFOUND;
1057 End Loop;
1058 close get_details;
1059
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 g_mesg := sqlcode || ' ' || sqlerrm;
1063 fnd_file.put_line(fnd_file.log, g_mesg);
1064 G_ERRBUF := g_mesg;
1065
1066 END RUN_WITH_CHECK_PARTY;
1067
1068
1069 -- The following procedure will be executed when
1070 -- 1. User enters a check_id and account_id
1071 -- 2. User enters a check_id, party_id and account_id
1072
1073 PROCEDURE RUN_WITH_CHECK_ACCOUNT (
1074 P_CHECK_ID IN NUMBER,
1075 P_CUST_ACCOUNT_ID IN NUMBER,
1076 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1077 IS
1078 cursor get_details is
1079 select a.plan_id, a.profile_check_id, a.relational_operator,
1080 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
1081 a.end_date_active, a.use_for_cust_account,
1082 b.party_id, b.cust_account_id, b.value
1083 from csc_plan_headers_b a,
1084 csc_prof_check_results b
1085 where a.profile_check_id = b.check_id
1086 and a.profile_check_id = p_check_id
1087 and a.customized_plan = 'N'
1088 and b.cust_account_id = p_cust_account_id
1089 and a.use_for_cust_account = 'Y'
1090 and sysdate between nvl(a.start_date_active, sysdate )
1091 and nvl(a.end_date_active, sysdate ) ;
1092
1093 -- local variable declaration
1094 l_check_name VARCHAR2(240);
1095
1096 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
1097 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
1098 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
1099 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
1100 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
1101 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
1102 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
1103 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
1104
1105 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
1106 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
1107 l_value_tbl CSC_CHAR_TBL_TYPE;
1108 BEGIN
1109
1110 open get_details;
1111 Loop
1112 fetch get_details
1113 bulk collect into
1114 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
1115 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
1116 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
1117 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included Limit clause */
1118
1119 if ( l_plan_id_tbl.count = 0 ) then
1120 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
1121 'Please enter a valid set of parameters.';
1122 G_ERRBUF := g_mesg;
1123 g_mesg_line := 'Causes:';
1124 fnd_file.put_line(fnd_file.log, g_mesg_line);
1125 g_mesg := g_mesg || g_mesg_line;
1126 g_mesg_line := '- Plans for the specified check are either customized or have expired.';
1127 fnd_file.put_line(fnd_file.log, g_mesg_line);
1128 g_mesg := g_mesg || g_mesg_line;
1129 g_mesg_line := '- Plans for the specified check are not account level plans.';
1130 fnd_file.put_line(fnd_file.log, g_mesg_line);
1131 g_mesg := g_mesg || g_mesg_line;
1132 g_mesg_line := '- Profile results for the specified check and/or account have not been populated.';
1133 fnd_file.put_line(fnd_file.log, g_mesg_line);
1134 g_mesg := g_mesg || g_mesg_line;
1135 g_mesg_line := 'Action';
1136 fnd_file.put_line(fnd_file.log, g_mesg_line);
1137 g_mesg := g_mesg || g_mesg_line;
1138 g_mesg_line := '- Specify checks associated to plans that are not customized and have not expired.';
1139 fnd_file.put_line(fnd_file.log, g_mesg_line);
1140 g_mesg := g_mesg || g_mesg_line;
1141 g_mesg_line := '- Specify checks associated to plans that are at account level.';
1142 fnd_file.put_line(fnd_file.log, g_mesg_line);
1143 g_mesg := g_mesg || g_mesg_line;
1144 g_mesg_line := '- Run the Profile Engine to populate the results for the ' ||
1145 'specified account and/or specified check.';
1146 fnd_file.put_line(fnd_file.log, g_mesg_line);
1147 g_mesg := g_mesg || g_mesg_line;
1148 else
1149 -- call the procedure to make/break the customer - plan association
1150 add_remove_plan_check (
1151 P_PLAN_ID_TBL => l_plan_id_tbl,
1152 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
1153 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
1154 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
1155 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
1156 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
1157 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
1158 P_PARTY_ID_TBL => l_party_id_tbl,
1159 P_CUST_ID_TBL => l_cust_id_tbl,
1160 P_VALUE_TBL => l_value_tbl,
1161 X_RETURN_STATUS => x_return_status );
1162 end if;
1163
1164 Exit when get_details%NOTFOUND;
1165 End Loop;
1166 close get_details;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 g_mesg := sqlcode || ' ' || sqlerrm;
1171 fnd_file.put_line(fnd_file.log, g_mesg);
1172 G_ERRBUF := g_mesg;
1173
1174 END RUN_WITH_CHECK_ACCOUNT;
1175
1176 -- The following procedure will be executed when
1177 -- 1. No parameters are specified
1178
1179 PROCEDURE RUN_WITH_ALL (
1180 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1181 IS
1182 -- get all plan details; the following logic is followed to get check results
1183 -- > if the plan is at party level, then get only all the party level results for
1184 -- that plan's check id;
1185 -- > if the plan is at account level, then get only all the account level results
1186 -- for that plan's check id;
1187 cursor get_details is
1188 select a.plan_id, a.profile_check_id, a.relational_operator,
1189 a.criteria_value_low, a.criteria_value_high, a.start_date_active,
1190 a.end_date_active, a.use_for_cust_account,
1191 b.party_id, b.cust_account_id, b.value
1192 from csc_plan_headers_b a,
1193 csc_prof_check_results b
1194 where a.profile_check_id = b.check_id
1195 and a.customized_plan = 'N'
1196 and ( ( a.use_for_cust_account = 'N' and
1197 b.cust_account_id is null )
1198 OR ( a.use_for_cust_account = 'Y' and
1199 b.cust_account_id is not null ) )
1200 and sysdate between nvl(a.start_date_active, sysdate )
1201 and nvl(a.end_date_active, sysdate ) ;
1202
1203 -- local variable declaration
1204 l_check_name VARCHAR2(240);
1205
1206 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
1207 l_check_id_tbl CSC_CHECK_ID_TBL_TYPE;
1208 l_relational_operator_tbl CSC_CHAR_TBL_TYPE;
1209 l_criteria_value_low_tbl CSC_CHAR_TBL_TYPE;
1210 l_criteria_value_high_tbl CSC_CHAR_TBL_TYPE;
1211 l_start_date_active_tbl CSC_DATE_TBL_TYPE;
1212 l_end_date_active_tbl CSC_DATE_TBL_TYPE;
1213 l_use_for_cust_account_tbl CSC_CHAR_TBL_TYPE;
1214
1215 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
1216 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
1217 l_value_tbl CSC_CHAR_TBL_TYPE;
1218 BEGIN
1219
1220 open get_details;
1221 Loop
1222 fetch get_details
1223 bulk collect into
1224 l_plan_id_tbl , l_check_id_tbl, l_relational_operator_tbl ,
1225 l_criteria_value_low_tbl , l_criteria_value_high_tbl , l_start_date_active_tbl,
1226 l_end_date_active_tbl , l_use_for_cust_account_tbl, l_party_id_tbl,
1227 l_cust_id_tbl , l_value_tbl LIMIT 2000; /* Bug 4628148: Included Bulk Limit */
1228
1229 if ( l_plan_id_tbl.count = 0 ) then
1230 g_mesg := 'Given request parameters did not retrieve any records to be processed. ' ||
1231 'Please enter a valid set of parameters.';
1232 G_ERRBUF := g_mesg;
1233 g_mesg_line := 'Causes:';
1234 fnd_file.put_line(fnd_file.log, g_mesg_line);
1235 g_mesg := g_mesg || g_mesg_line;
1236 g_mesg_line := '- All plans have expired.';
1237 fnd_file.put_line(fnd_file.log, g_mesg_line);
1238 g_mesg := g_mesg || g_mesg_line;
1239 g_mesg_line := '- Profile results have not been populated.';
1240 fnd_file.put_line(fnd_file.log, g_mesg_line);
1241 g_mesg := g_mesg || g_mesg_line;
1242 g_mesg_line := 'Action';
1243 fnd_file.put_line(fnd_file.log, g_mesg_line);
1244 g_mesg := g_mesg || g_mesg_line;
1245 g_mesg_line := '- Verify that there are active plans. (ie. end date of plans are a future date)';
1246 fnd_file.put_line(fnd_file.log, g_mesg_line);
1247 g_mesg := g_mesg || g_mesg_line;
1248 g_mesg_line := '- Run the Profile Engine to populate all results.';
1249 fnd_file.put_line(fnd_file.log, g_mesg_line);
1250 g_mesg := g_mesg || g_mesg_line;
1251 else
1252 -- call the procedure to make/break the customer - plan association
1253 add_remove_plan_check (
1254 P_PLAN_ID_TBL => l_plan_id_tbl,
1255 P_RELATIONAL_OPERATOR_TBL => l_relational_operator_tbl,
1256 P_CRITERIA_VALUE_LOW_TBL => l_criteria_value_low_tbl,
1257 P_CRITERIA_VALUE_HIGH_TBL => l_criteria_value_high_tbl,
1258 P_START_DATE_ACTIVE_TBL => l_start_date_active_tbl,
1259 P_END_DATE_ACTIVE_TBL => l_end_date_active_tbl,
1260 P_USE_FOR_CUST_ACCOUNT_TBL => l_use_for_cust_account_tbl,
1261 P_PARTY_ID_TBL => l_party_id_tbl,
1262 P_CUST_ID_TBL => l_cust_id_tbl,
1263 P_VALUE_TBL => l_value_tbl,
1264 X_RETURN_STATUS => x_return_status );
1265 end if;
1266
1267 Exit when get_details%NOTFOUND;
1268 End Loop;
1269 close get_details;
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 g_mesg := sqlcode || ' ' || sqlerrm;
1274 fnd_file.put_line(fnd_file.log, g_mesg);
1275 G_ERRBUF := g_mesg;
1276
1277 END RUN_WITH_ALL;
1278
1279
1280 -- The following procedure checks if a party's or account's check results
1281 -- satisfies a plan criteria or not and respectively inserts or deletes
1282 -- the party/account to plan association
1283 PROCEDURE ADD_REMOVE_PLAN_CHECK(
1284 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE,
1285 P_RELATIONAL_OPERATOR_TBL IN CSC_CHAR_TBL_TYPE ,
1286 P_CRITERIA_VALUE_LOW_TBL IN CSC_CHAR_TBL_TYPE ,
1287 P_CRITERIA_VALUE_HIGH_TBL IN CSC_CHAR_TBL_TYPE ,
1288 P_START_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE ,
1289 P_END_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE ,
1290 P_USE_FOR_CUST_ACCOUNT_TBL IN CSC_CHAR_TBL_TYPE ,
1291 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE ,
1292 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE ,
1293 P_VALUE_TBL IN CSC_CHAR_TBL_TYPE ,
1294 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1295 IS
1296 l_assign_plan VARCHAR2(1) := NULL;
1297
1298 -- The following tables stores the column values of all the plans that
1299 -- a customer satisfies. It has an 'add' in its name to denote that these
1300 -- plans need to be added to this customer in the CSC_CUST_PLANS table.
1301 L_PLAN_ID_ADD_TBL CSC_PLAN_ID_TBL_TYPE;
1302 L_PARTY_ID_ADD_TBL CSC_PARTY_ID_TBL_TYPE;
1303 L_CUST_ID_ADD_TBL CSC_CUST_ID_TBL_TYPE;
1304 L_START_DATE_ACTIVE_ADD_TBL CSC_DATE_TBL_TYPE;
1305 L_END_DATE_ACTIVE_ADD_TBL CSC_DATE_TBL_TYPE;
1306 L_ADD_IDX NUMBER := 0; -- index for add tables
1307
1308 -- The following tables stores plan and customer information, when a
1309 -- customer no longer satisfies a plan criteria. It has a 'del' to its
1310 -- name to denote that these rows need to be deleted from CSC_CUST_PLANS
1311 -- table.
1312 L_PLAN_ID_DEL_TBL CSC_PLAN_ID_TBL_TYPE;
1313 L_PARTY_ID_DEL_TBL CSC_PARTY_ID_TBL_TYPE;
1314 L_CUST_ID_DEL_TBL CSC_CUST_ID_TBL_TYPE;
1315 L_DEL_IDX NUMBER := 0; -- index for delete tables
1316
1317 BEGIN
1318 x_return_status := FND_API.G_RET_STS_SUCCESS;
1319
1320 FOR i in 1..p_plan_id_tbl.count
1321 LOOP
1322 validate_compare_arguments (
1323 P_RELATIONAL_OPERATOR => p_relational_operator_tbl(i),
1324 P_CRITERIA_VALUE_LOW => p_criteria_value_low_tbl(i),
1325 P_CRITERIA_VALUE_HIGH => p_criteria_value_high_tbl(i),
1326 X_RETURN_STATUS => x_return_status );
1327
1328 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1329 g_mesg := 'Plan criteria not defined correctly for plan_id = ' || p_plan_id_tbl(i);
1330 fnd_file.put_line(fnd_file.log, g_mesg);
1331 end if;
1332
1333 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1334 if ( p_relational_operator_tbl(i) = '=' ) then
1335 begin
1336 if ( to_number(p_value_tbl(i)) = to_number(p_criteria_value_low_tbl(i)) ) then
1337 l_assign_plan := 'T';
1338 else
1339 l_assign_plan := 'F';
1340 end if;
1341 exception
1342 when others then
1343 if ( p_value_tbl(i) = p_criteria_value_low_tbl(i) ) then
1344 l_assign_plan := 'T';
1345 else
1346 l_assign_plan := 'F';
1347 end if;
1348 end;
1349 elsif ( p_relational_operator_tbl(i) = '<>' ) then
1350 begin
1351 if ( to_number(p_value_tbl(i)) <> to_number(p_criteria_value_low_tbl(i)) ) then
1352 l_assign_plan := 'T';
1353 else
1354 l_assign_plan := 'F';
1355 end if;
1356 exception
1357 when others then
1358 if ( p_value_tbl(i) <> p_criteria_value_low_tbl(i) ) then
1359 l_assign_plan := 'T';
1360 else
1361 l_assign_plan := 'F';
1362 end if;
1363 end;
1364 elsif ( p_relational_operator_tbl(i) = '<' ) then
1365 begin
1366 if ( to_number(p_value_tbl(i)) < to_number(p_criteria_value_low_tbl(i)) ) then
1367 l_assign_plan := 'T';
1368 else
1369 l_assign_plan := 'F';
1370 end if;
1371 exception
1372 when others then
1373 if ( p_value_tbl(i) < p_criteria_value_low_tbl(i) ) then
1374 l_assign_plan := 'T';
1375 else
1376 l_assign_plan := 'F';
1377 end if;
1378 end;
1379 elsif ( p_relational_operator_tbl(i) = '>' ) then
1380 begin
1381 if ( to_number(p_value_tbl(i)) > to_number(p_criteria_value_low_tbl(i)) ) then
1382 l_assign_plan := 'T';
1383 else
1384 l_assign_plan := 'F';
1385 end if;
1386 exception
1387 when others then
1388 if ( p_value_tbl(i) > p_criteria_value_low_tbl(i) ) then
1389 l_assign_plan := 'T';
1390 else
1391 l_assign_plan := 'F';
1392 end if;
1393 end;
1394 elsif ( p_relational_operator_tbl(i) = '<=' ) then
1395 begin
1396 if ( to_number(p_value_tbl(i)) <= to_number(p_criteria_value_low_tbl(i)) ) then
1397 l_assign_plan := 'T';
1398 else
1399 l_assign_plan := 'F';
1400 end if;
1401 exception
1402 when others then
1403 if ( p_value_tbl(i) <= p_criteria_value_low_tbl(i) ) then
1404 l_assign_plan := 'T';
1405 else
1406 l_assign_plan := 'F';
1407 end if;
1408 end;
1409 elsif ( p_relational_operator_tbl(i) = '>=' ) then
1410 begin
1411 if ( to_number(p_value_tbl(i)) >= to_number(p_criteria_value_low_tbl(i)) ) then
1412 l_assign_plan := 'T';
1413 else
1414 l_assign_plan := 'F';
1415 end if;
1416 exception
1417 when others then
1418 if ( p_value_tbl(i) >= p_criteria_value_low_tbl(i) ) then
1419 l_assign_plan := 'T';
1420 else
1421 l_assign_plan := 'F';
1422 end if;
1423 end;
1424 elsif ( p_relational_operator_tbl(i) = 'LIKE' ) then
1425 if ( instr(p_criteria_value_low_tbl(i), p_value_tbl(i)) = 0 ) then
1426 l_assign_plan := 'T';
1427 else
1428 l_assign_plan := 'F';
1429 end if;
1430 elsif ( p_relational_operator_tbl(i) = 'NOT LIKE' ) then
1431 if ( instr(p_criteria_value_low_tbl(i), p_value_tbl(i)) <> 0 ) then
1432 l_assign_plan := 'T';
1433 else
1434 l_assign_plan := 'F';
1435 end if;
1436 elsif ( p_relational_operator_tbl(i) = 'BETWEEN' ) then
1437 if ( p_value_tbl(i) >= p_criteria_value_low_tbl(i) and
1438 p_value_tbl(i) <= p_criteria_value_high_tbl(i) ) then
1439 l_assign_plan := 'T';
1440 else
1441 l_assign_plan := 'F';
1442 end if;
1443 elsif ( p_relational_operator_tbl(i) = 'NOT BETWEEN' ) then
1444 if ( p_value_tbl(i) < p_criteria_value_low_tbl(i) and
1445 p_value_tbl(i) > p_criteria_value_high_tbl(i) ) then
1446 l_assign_plan := 'T';
1447 else
1448 l_assign_plan := 'F';
1449 end if;
1450 elsif ( p_relational_operator_tbl(i) = 'IS NULL' ) then
1451 if ( p_value_tbl(i) IS NULL ) then
1452 l_assign_plan := 'T';
1453 else
1454 l_assign_plan := 'F';
1455 end if;
1456 elsif ( p_relational_operator_tbl(i) = 'IS NOT NULL' ) then
1457 if ( p_value_tbl(i) IS NOT NULL ) then
1458 l_assign_plan := 'T';
1459 else
1460 l_assign_plan := 'F';
1461 end if;
1462 else
1463 -- set message that the relational operator is invalid and raise an error.
1464 l_assign_plan := 'F';
1465 g_mesg := 'Invalid relational operator for plan_id = ' || p_plan_id_tbl(i);
1466 fnd_file.put_line(fnd_file.log, g_mesg);
1467 END IF;
1468
1469 if ( l_assign_plan = 'T' ) then
1470 L_ADD_IDX := L_ADD_IDX + 1;
1471 L_PLAN_ID_ADD_TBL(L_ADD_IDX) := p_plan_id_tbl(i);
1472 L_START_DATE_ACTIVE_ADD_TBL(L_ADD_IDX) := p_start_date_active_tbl(i);
1473 L_END_DATE_ACTIVE_ADD_TBL(L_ADD_IDX) := p_end_date_active_tbl(i);
1474 L_PARTY_ID_ADD_TBL(L_ADD_IDX) := p_party_id_tbl(i);
1475 L_CUST_ID_ADD_TBL(L_ADD_IDX) := p_cust_id_tbl(i);
1476 else
1477 L_DEL_IDX := L_DEL_IDX + 1;
1478 L_PLAN_ID_DEL_TBL(L_DEL_IDX) := p_plan_id_tbl(i);
1479 L_PARTY_ID_DEL_TBL(L_DEL_IDX) := p_party_id_tbl(i);
1480 L_CUST_ID_DEL_TBL(L_DEL_IDX) := p_cust_id_tbl(i);
1481 end if;
1482
1483 IF ( L_DEL_IDX >= G_COMMIT_TIME ) THEN
1484 DELETE_CUST_PLANS (
1485 P_PLAN_ID_TBL => L_PLAN_ID_DEL_TBL,
1486 P_PARTY_ID_TBL => L_PARTY_ID_DEL_TBL,
1487 P_CUST_ID_TBL => L_CUST_ID_DEL_TBL,
1488 X_RETURN_STATUS => x_return_status );
1489
1490 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1491 -- generate message in stack.
1492 -- raise exception
1493 g_mesg := 'Delete of customer to plan association failed.';
1494 fnd_file.put_line(fnd_file.log, g_mesg);
1495 -- RAISE FND_API.G_EXC_ERROR;
1496 end if;
1497
1498 L_DEL_IDX := 0;
1499 L_PLAN_ID_DEL_TBL.DELETE;
1500 L_PARTY_ID_DEL_TBL.DELETE;
1501 L_CUST_ID_DEL_TBL.DELETE;
1502 END IF; -- if L_del_idx = g_commit_time
1503
1504 IF ( L_ADD_IDX >= G_COMMIT_TIME ) THEN
1505 ADD_CUST_PLANS (
1506 p_plan_id_tbl => L_PLAN_ID_ADD_TBL,
1507 p_start_date_active_tbl => L_START_DATE_ACTIVE_ADD_TBL,
1508 p_end_date_active_tbl => L_END_DATE_ACTIVE_ADD_TBL,
1509 p_party_id_tbl => L_PARTY_ID_ADD_TBL,
1510 p_cust_id_tbl => L_CUST_ID_ADD_TBL,
1511 x_return_status => x_return_status );
1512
1513 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1514 -- generate message in stack.
1515 -- raise exception
1516 g_mesg := 'Insert of new customer to plan association failed.';
1517 fnd_file.put_line(fnd_file.log, g_mesg);
1518 -- RAISE FND_API.G_EXC_ERROR;
1519 end if;
1520
1521 L_ADD_IDX := 0;
1522 L_PLAN_ID_ADD_TBL.DELETE;
1523 L_START_DATE_ACTIVE_ADD_TBL.DELETE;
1524 L_END_DATE_ACTIVE_ADD_TBL.DELETE;
1525 L_PARTY_ID_ADD_TBL.DELETE;
1526 L_CUST_ID_ADD_TBL.DELETE;
1527 END IF; -- if L_add_idx >= g_commit_time
1528 END IF; -- if x_return_status = FND_API.G_RET_STS_SUCCESS
1529 END LOOP; -- for i in 1..l_plan_id_sel_tbl.count
1530
1531 IF ( L_DEL_IDX > 0 ) THEN
1532 DELETE_CUST_PLANS (
1533 P_PLAN_ID_TBL => L_PLAN_ID_DEL_TBL,
1534 P_PARTY_ID_TBL => L_PARTY_ID_DEL_TBL,
1535 P_CUST_ID_TBL => L_CUST_ID_DEL_TBL,
1536 X_RETURN_STATUS => x_return_status );
1537
1538 L_DEL_IDX := 0;
1539 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1540 -- generate message in stack.
1541 -- raise exception
1542 g_mesg := 'Delete of customer to plan association failed.';
1543 fnd_file.put_line(fnd_file.log, g_mesg);
1544 -- RAISE FND_API.G_EXC_ERROR;
1545 end if;
1546 END IF; -- if g_del_id > 0
1547
1548 IF ( L_ADD_IDX > 0 ) THEN
1549 ADD_CUST_PLANS (
1550 p_plan_id_tbl => L_PLAN_ID_ADD_TBL,
1551 p_start_date_active_tbl => L_START_DATE_ACTIVE_ADD_TBL,
1552 p_end_date_active_tbl => L_END_DATE_ACTIVE_ADD_TBL,
1553 p_party_id_tbl => L_PARTY_ID_ADD_TBL,
1554 p_cust_id_tbl => L_CUST_ID_ADD_TBL,
1555 x_return_status => x_return_status );
1556 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1557 -- generate message in stack.
1558 -- raise exception
1559 g_mesg := 'Insert of new customer to plan association failed.';
1560 fnd_file.put_line(fnd_file.log, g_mesg);
1561 -- RAISE FND_API.G_EXC_ERROR;
1562 end if;
1563 END IF; -- if L_add_idx > 0
1564
1565 END ADD_REMOVE_PLAN_CHECK;
1566
1567 -- depending on the relational operator, the plan criteria_valu_low and high should or should not be
1568 -- specified. This procedure performs this validation.
1569 PROCEDURE VALIDATE_COMPARE_ARGUMENTS (
1570 P_RELATIONAL_OPERATOR IN VARCHAR2,
1571 P_CRITERIA_VALUE_LOW IN VARCHAR2,
1572 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
1573 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1574 IS
1575 BEGIN
1576 x_return_status := FND_API.G_RET_STS_SUCCESS;
1577 IF ( p_relational_operator = '=' OR
1578 p_relational_operator = '<>' OR
1579 p_relational_operator = '>' OR
1580 p_relational_operator = '<' OR
1581 p_relational_operator = '>=' OR
1582 p_relational_operator = '<=' OR
1583 p_relational_operator = 'IN' OR
1584 p_relational_operator = 'NOT IN' OR
1585 p_relational_operator = 'LIKE' OR
1586 p_relational_operator = 'NOT LIKE' )
1587 THEN
1588 if ( p_criteria_value_low IS NULL OR p_criteria_value_high IS NOT NULL ) then
1589 g_mesg := 'Invalid arguments. Cannot perform comparison between check results and ' ||
1590 'plan criteria. Check the relational operator, low value and high value.';
1591 fnd_file.put_line(fnd_file.log, g_mesg);
1592 x_return_status := FND_API.G_RET_STS_ERROR;
1593 end if;
1594 ELSIF ( p_relational_operator = 'BETWEEN' OR
1595 p_relational_operator = 'NOT BETWEEN' )
1596 THEN
1597 if ( p_criteria_value_low IS NULL OR p_criteria_value_high IS NULL ) then
1598 g_mesg := 'Invalid arguments. Cannot perform comparison between check results and ' ||
1599 'plan criteria. Check the relational operator, low value and high value.';
1600 fnd_file.put_line(fnd_file.log, g_mesg);
1601 x_return_status := FND_API.G_RET_STS_ERROR;
1602 end if;
1603 ELSIF ( p_relational_operator = 'IS NULL' OR
1604 p_relational_operator = 'IS NOT NULL' )
1605 THEN
1606 if ( p_criteria_value_low IS NOT NULL OR p_criteria_value_high IS NOT NULL ) then
1607 g_mesg := 'Invalid arguments. Cannot perform comparison between check results and ' ||
1608 'plan criteria. Check the relational operator, low value and high value.';
1609 fnd_file.put_line(fnd_file.log, g_mesg);
1610 x_return_status := FND_API.G_RET_STS_ERROR;
1611 end if;
1612 END IF;
1613 END VALIDATE_COMPARE_ARGUMENTS;
1614
1615 PROCEDURE ADD_CUST_PLANS (
1616 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE,
1617 P_START_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE,
1618 P_END_DATE_ACTIVE_TBL IN CSC_DATE_TBL_TYPE,
1619 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE,
1620 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE,
1621 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1622 IS
1623 l_api_name CONSTANT VARCHAR2(30) := 'ADD_CUST_PLANS';
1624 l_api_version_number CONSTANT NUMBER := 1.0;
1625
1626 -- Dates to track the start and end time of the bulk insert into CSC_CUST_PLANS.
1627 -- These are then used as a filter in the bulk insert of the AUDIT table.
1628 l_ins_start_date DATE;
1629 l_ins_end_date DATE;
1630
1631 BEGIN
1632 -- Standard Start of API savepoint
1633 SAVEPOINT ADD_CUST_PLANS_PVT;
1634
1635 -- Initialize API return status to SUCCESS
1636 x_return_status := FND_API.G_RET_STS_SUCCESS;
1637
1638 -- ******************************************************************
1639 -- Validate Environment
1640 -- ******************************************************************
1641 IF FND_GLOBAL.User_Id IS NULL THEN
1642 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1643 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'UT_CANNOT_GET_PROFILE_VALUE');
1644 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1645 END IF;
1646 RAISE FND_API.G_EXC_ERROR;
1647 END IF;
1648 select sysdate
1649 into l_ins_start_date
1650 from sys.dual;
1651
1652 FORALL i in 1..p_plan_id_tbl.count
1653 INSERT INTO csc_cust_plans (
1654 CUST_PLAN_ID, PLAN_ID, PARTY_ID,
1655 CUST_ACCOUNT_ID, START_DATE_ACTIVE,
1656 END_DATE_ACTIVE, MANUAL_FLAG, PLAN_STATUS_CODE,
1657 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
1658 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, CREATION_DATE,
1659 LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN,
1660 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
1661 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
1662 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
1663 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1664 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
1665 ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER )
1666 SELECT
1667 CSC_CUST_PLANS_S.NEXTVAL, p_plan_id_tbl(i), p_party_id_tbl(i),
1668 p_cust_id_tbl(i), p_start_date_active_tbl(i),
1669 p_end_date_active_tbl(i), 'N', CSC_CORE_UTILS_PVT.APPLY_PLAN,
1670 NULL, NULL, NULL,
1671 NULL, sysdate, sysdate,
1672 FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
1673 NULL, NULL, NULL,
1674 NULL, NULL, NULL,
1675 NULL, NULL, NULL,
1676 NULL, NULL, NULL,
1677 NULL, NULL, NULL,
1678 NULL, 1
1679 FROM SYS.DUAL
1680 WHERE NOT EXISTS ( select 1
1681 from csc_cust_plans
1682 where plan_id = p_plan_id_tbl(i)
1683 and party_id = p_party_id_tbl(i)
1684 and nvl(cust_account_id, 0) = nvl(p_cust_id_tbl(i), 0)
1685 );
1686
1687 select sysdate
1688 into l_ins_end_date
1689 from sys.dual;
1690
1691 FORALL i in 1..p_party_id_tbl.count
1692 INSERT INTO csc_cust_plans_audit (
1693 PLAN_AUDIT_ID, PLAN_ID, PARTY_ID,
1694 CUST_ACCOUNT_ID, PLAN_STATUS_CODE,
1695 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
1696 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, CREATION_DATE,
1697 LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN,
1698 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
1699 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
1700 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
1701 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1702 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
1703 ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER )
1704 SELECT
1705 CSC_CUST_PLANS_AUDIT_S.NEXTVAL, p_plan_id_tbl(i), p_party_id_tbl(i),
1706 p_cust_id_tbl(i), CSC_CORE_UTILS_PVT.APPLY_PLAN,
1707 NULL, NULL, NULL,
1708 NULL, SYSDATE, SYSDATE,
1709 FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
1710 NULL, NULL, NULL,
1711 NULL, NULL, NULL,
1712 NULL, NULL, NULL,
1713 NULL, NULL, NULL,
1714 NULL, NULL, NULL,
1715 NULL, 1
1716 FROM SYS.DUAL
1717 WHERE EXISTS ( select 1
1718 from csc_cust_plans
1719 where plan_id = p_plan_id_tbl(i)
1720 and party_id = p_party_id_tbl(i)
1721 and creation_date between l_ins_start_date and l_ins_end_date);
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 g_mesg := sqlcode || ' ' || sqlerrm;
1726 fnd_file.put_line(fnd_file.log, g_mesg);
1727 G_ERRBUF := g_mesg;
1728
1729 END ADD_CUST_PLANS;
1730
1731 PROCEDURE DELETE_CUST_PLANS (
1732 P_PLAN_ID_TBL IN CSC_PLAN_ID_TBL_TYPE,
1733 P_PARTY_ID_TBL IN CSC_PARTY_ID_TBL_TYPE,
1734 P_CUST_ID_TBL IN CSC_CUST_ID_TBL_TYPE,
1735 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1736 IS
1737 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CUST_PLANS';
1738 l_api_version_number CONSTANT NUMBER := 1.0;
1739
1740 l_plan_id_tbl CSC_PLAN_ID_TBL_TYPE;
1741 l_party_id_tbl CSC_PARTY_ID_TBL_TYPE;
1742 l_cust_id_tbl CSC_CUST_ID_TBL_TYPE;
1743
1744 BEGIN
1745 -- Standard Start of API savepoint
1746 SAVEPOINT DELETE_CUST_PLANS_PVT;
1747
1748 -- Initialize API return status to SUCCESS
1749 x_return_status := FND_API.G_RET_STS_SUCCESS;
1750
1751 -- ******************************************************************
1752 -- Validate Environment
1753 -- ******************************************************************
1754 IF FND_GLOBAL.User_Id IS NULL THEN
1755 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1756 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'UT_CANNOT_GET_PROFILE_VALUE');
1757 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1758 END IF;
1759 RAISE FND_API.G_EXC_ERROR;
1760 END IF;
1761
1762 FORALL i in 1..P_PLAN_ID_TBL.COUNT
1763 DELETE FROM csc_cust_plans
1764 WHERE plan_id = p_plan_id_tbl(i)
1765 AND party_id = p_party_id_tbl(i)
1766 AND nvl(cust_account_id,0) = nvl(p_cust_id_tbl(i), nvl(cust_account_id,0) )
1767 AND manual_flag = 'N'
1768 RETURNING plan_id, party_id, cust_account_id
1769 BULK COLLECT INTO l_plan_id_tbl, l_party_id_tbl, l_cust_id_tbl;
1770
1771 FORALL i in 1..l_party_id_tbl.count
1772 INSERT INTO csc_cust_plans_audit (
1773 PLAN_AUDIT_ID, PLAN_ID, PARTY_ID,
1774 CUST_ACCOUNT_ID, PLAN_STATUS_CODE,
1775 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
1776 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, CREATION_DATE,
1777 LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN,
1778 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
1779 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
1780 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
1781 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1782 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
1783 ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER )
1784 SELECT
1785 CSC_CUST_PLANS_AUDIT_S.NEXTVAL, l_plan_id_tbl(i), l_party_id_tbl(i),
1786 l_cust_id_tbl(i), CSC_CORE_UTILS_PVT.REMOVE_PLAN,
1787 NULL, NULL, NULL,
1788 NULL, SYSDATE, SYSDATE,
1789 FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID,
1790 NULL, NULL, NULL,
1791 NULL, NULL, NULL,
1792 NULL, NULL, NULL,
1793 NULL, NULL, NULL,
1794 NULL, NULL, NULL,
1795 NULL, 1
1796 FROM SYS.DUAL;
1797
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 g_mesg := sqlcode || ' ' || sqlerrm;
1801 fnd_file.put_line(fnd_file.log, g_mesg);
1802 G_ERRBUF := g_mesg;
1803
1804 END DELETE_CUST_PLANS;
1805
1806
1807 END CSC_PLAN_ASSIGNMENT_PKG;
[Home] [Help]