[Home] [Help]
PACKAGE BODY: APPS.PV_REFERRAL_COMP_PUB
Source
1 package body PV_REFERRAL_COMP_PUB as
2 /* $Header: pvxvrfcb.pls 120.0 2005/05/27 15:53:55 appldev noship $*/
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variable Declaration */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 g_log_to_file VARCHAR2(5) := 'N';
14 g_pkg_name VARCHAR2(30) := 'PV_REFERRAL_COMP_PUB';
15 g_api_name VARCHAR2(30);
16 g_RETCODE VARCHAR2(10) := '0';
17 g_module_name VARCHAR2(48);
18
19 PV_DEBUG_HIGH_ON boolean :=
20 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
21 PV_DEBUG_LOW_ON boolean :=
22 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23 PV_DEBUG_MEDIUM_ON boolean :=
24 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25 PV_DEBUG_ERROR_ON boolean :=
26 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
27
28
29 /*************************************************************************************/
30 /* */
31 /* */
32 /* */
33 /* private procedure declaration */
34 /* */
35 /* */
36 /* */
37 /*************************************************************************************/
38 PROCEDURE Debug(
39 p_msg_string IN VARCHAR2,
40 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE',
41 p_token_type IN VARCHAR2 := 'TEXT',
42 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
43 );
44
45 PROCEDURE Set_Message(
46 p_msg_name IN VARCHAR2,
47 p_token1 IN VARCHAR2 := NULL,
48 p_token1_value IN VARCHAR2 := NULL,
49 p_token2 IN VARCHAR2 := NULL,
50 p_token2_value IN VARCHAR2 := NULL,
51 p_token3 IN VARCHAR2 := NULL,
52 p_token3_value IN VARCHAR2 := NULL,
53 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
54 );
55
56 PROCEDURE write_conc_log;
57
58
59 -- -----------------------------------------------------------------------------
60 -- This may not be needed anymore.
61 -- -----------------------------------------------------------------------------
62 FUNCTION Get_Partner_Account (
63 p_partner_id IN NUMBER
64 )
65 RETURN NUMBER
66 ;
67
68
69
70 --=============================================================================+
71 --| Public Procedure |
72 --| Get_Beneficiary |
73 --| |
74 --| Parameters |
75 --| IN |
76 --| OUT |
77 --| |
78 --| |
79 --| NOTES |
80 --| |
81 --| HISTORY |
82 --| |
83 --==============================================================================
84 PROCEDURE Get_Beneficiary (
85 p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
87 p_commit IN VARCHAR2 := FND_API.g_false,
88 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
89 p_order_header_id IN NUMBER,
90 p_order_line_id IN NUMBER,
91 p_offer_id IN NUMBER,
92 x_beneficiary_id OUT NOCOPY NUMBER,
93 x_referral_id OUT NOCOPY NUMBER,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2
97 )
98 IS
99 l_api_version NUMBER := 1;
100 l_returned_order_flag BOOLEAN := FALSE;
101
102
103 TYPE t_ref_cursor IS REF CURSOR;
104 l_ref_cursor t_ref_cursor;
105
106
107 CURSOR c_returned_order IS
108 SELECT line_id
109 FROM oe_order_lines_all
110 WHERE flow_status_code = 'RETURNED' AND
111 header_id = p_order_header_id AND
112 line_id = p_order_line_id;
113
114 /*
115 CURSOR c_matching_referral IS
116 SELECT referral_id, partner_id,
117 COUNT(*) OVER (PARTITION BY counter) outer_counter
118 FROM (SELECT referral_id, partner_id, 'x' counter
119 FROM pv_referrals_b
120 WHERE order_id = p_order_header_id);
121 */
122
123 -- -------------------------------------------------------------------
124 -- Given an order, find out if there are any referrals linked with
125 -- this order. The SQL returns the oldest referrals first.
126 -- Note that we don't need to check the referral status for this
127 -- because a referral that is associated with an order cannot be
128 -- expired, manually closed, etc.
129 -- -------------------------------------------------------------------
130 CURSOR c_matching_referral IS
131 SELECT referral_id, partner_id, referral_status, claim_id,
132 partner_cust_account_id
133 FROM pv_referrals_b
134 WHERE order_id = p_order_header_id AND
135 claim_id IS NULL
136 ORDER by creation_date ASC;
137
138 -- -------------------------------------------------------------------
139 -- Given an order and a referral, find out if there are any matching
140 -- products.
141 --
142 -- Here a referral can only be considered when its status is either
143 -- 'APPROVED', 'MANUAL_EXTEND', and 'CLOSED_OPPTY_WON'.
144 -- -------------------------------------------------------------------
145 CURSOR c_matching_line (pc_referral_id NUMBER) IS
146 SELECT LINE.inventory_item_id
147 FROM pv_referred_products PROD,
148 pv_referrals_b REF,
149 oe_order_lines_all LINE,
150 mtl_item_categories MIC,
151 eni_prod_denorm_hrchy_v DENORM,
152 pv_ge_benefits_vl BENFT
153 WHERE REF.referral_id = pc_referral_id AND
154 REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
155 PROD.referral_id = REF.referral_id AND
156 LINE.header_id = p_order_header_id AND
157 LINE.line_id = p_order_line_id AND
158 LINE.inventory_item_id = MIC.inventory_item_id AND
159 MIC.category_set_id = DENORM.category_set_id AND
160 MIC.category_id = DENORM.child_id AND
161 PROD.product_category_set_id = DENORM.category_set_id AND
162 PROD.product_category_id = DENORM.parent_id AND
163 REF.benefit_id = BENFT.benefit_id AND
164 BENFT.additional_info_1 = p_offer_id;
165
166
167 -- --------------------------------------------------------------------------
168 -- We only want to consider active referrals. An active referral is defined
169 -- as one whose referral status is either 'APPROVED', 'MANUAL_EXTEND' or
170 -- 'CLOSED_OPPTY_WON'.
171 -- There is a concurrent program that will update referrals to appropriate
172 -- status (e.g. 'EXPIRED', 'CLOSED_DEAD_LEAD', 'CLOSED_LOST_OPPTY', etc.)
173 -- --------------------------------------------------------------------------
174 CURSOR c_matching_referral_2 IS
175 SELECT *
176 FROM (
177 SELECT REF.referral_id, REF.partner_id, REF.partner_cust_account_id
178 FROM pv_referrals_b REF,
179 pv_referred_products PROD,
180 pv_ge_benefits_b BENFT,
181 oe_order_headers_all HEADER,
182 oe_order_lines_all LINE,
183 hz_cust_accounts ACCOUNT,
184 mtl_item_categories MIC,
185 eni_prod_denorm_hrchy_v DENORM
186 WHERE BENFT.additional_info_1 = p_offer_id AND
187 BENFT.benefit_id = REF.benefit_id AND
188 REF.referral_id = PROD.referral_id AND
189 REF.order_id IS NULL AND
190 REF.claim_id IS NULL AND
191 REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
192 HEADER.header_id = p_order_header_id AND
193 LINE.line_id = p_order_line_id AND
194 HEADER.header_id = LINE.header_id AND
195 LINE.flow_status_code <> 'CANCELLED' AND
196 LINE.inventory_item_id = MIC.inventory_item_id AND
197 MIC.category_set_id = DENORM.category_set_id AND
198 MIC.category_id = DENORM.child_id AND
199 PROD.product_category_set_id = DENORM.category_set_id AND
200 PROD.product_category_id = DENORM.parent_id AND
201 HEADER.sold_to_org_id = ACCOUNT.cust_account_id AND
202 ACCOUNT.party_id = REF.customer_party_id
203 ORDER BY REF.creation_date ASC
204 )
205 WHERE ROWNUM = 1;
206
207 -- --------------------------------------------------------------------------
208 -- Template Cursor
209 -- --------------------------------------------------------------------------
210 CURSOR c_template IS
211 SELECT referral_id, partner_cust_account_id, claim_id, 1 outer_counter
212 FROM pv_referrals_b
213 WHERE referral_id = 1;
214
215 lc_template c_template%ROWTYPE;
216
217 -- --------------------------------------------------------------------------
218 -- Returned Order.
219 -- Use dynamic SQL because Oracle 8i PL/SQL does not support using OVER...
220 -- PARTITION in a static SQL.
221 -- --------------------------------------------------------------------------
222 l_returned_order_match_sql VARCHAR2(32000) :=
223 'SELECT referral_id,
224 partner_cust_account_id,
225 claim_id,
226 COUNT(*) OVER (PARTITION BY counter) outer_counter
227 FROM (
228 SELECT REF.referral_id,
229 REF.partner_cust_account_id,
230 REF.claim_id,
231 1 counter
232 FROM pv_referrals_b REF,
233 pv_referred_products PROD,
234 pv_ge_benefits_b BENFT,
235 oe_order_headers_all HEADER,
236 oe_order_lines_all LINE,
237 hz_cust_accounts ACCOUNT,
238 mtl_item_categories MIC,
239 eni_prod_denorm_hrchy_v DENORM
240 WHERE BENFT.additional_info_1 = :p_offer_id AND
241 BENFT.benefit_id = REF.benefit_id AND
242 REF.referral_id = PROD.referral_id AND
243 REF.order_id IS NOT NULL AND
244 HEADER.header_id = :p_order_header_id AND
245 LINE.line_id = :p_order_line_id AND
246 HEADER.header_id = LINE.header_id AND
247 LINE.inventory_item_id = MIC.inventory_item_id AND
248 MIC.category_set_id = DENORM.category_set_id AND
249 MIC.category_id = DENORM.child_id AND
250 PROD.product_category_set_id = DENORM.category_set_id AND
251 PROD.product_category_id = DENORM.parent_id AND
252 HEADER.sold_to_org_id = ACCOUNT.cust_account_id AND
253 ACCOUNT.party_id = REF.customer_party_id AND
254 REF.creation_date < LINE.creation_date
255 )';
256
257 BEGIN
258 g_api_name := 'Get_Beneficiary';
259
260 Debug('Calling ' || g_pkg_name || '.' || g_api_name);
261 Debug('order_header_id = ' || p_order_header_id);
262 Debug('order_line_id = ' || p_order_line_id);
263 Debug('offer_id = ' || p_offer_id);
264
265 -------------------- initialize -------------------------
266 IF FND_API.to_boolean(p_init_msg_list) THEN
267 FND_MSG_PUB.initialize;
268 END IF;
269
270 IF NOT FND_API.compatible_api_call(
271 l_api_version,
272 p_api_version,
273 g_api_name,
274 g_pkg_name
275 ) THEN
276 RAISE FND_API.g_exc_unexpected_error;
277 END IF;
278
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280
281
282 ---------------------- Source code -----------------------
283
284 -- --------------------------------------------------------------------
285 -- Check if this is a returned order.
286 -- --------------------------------------------------------------------
287 FOR x IN c_returned_order LOOP
288 l_returned_order_flag := TRUE;
289 END LOOP;
290
291
292 -- ====================================================================
293 -- ====================================================================
294 -- Process "normal" orders.
295 -- ====================================================================
296 -- ====================================================================
297 IF (NOT l_returned_order_flag) THEN
298 -- --------------------------------------------------------------------
299 -- If there is one or more referrals associated with this order,
300 -- go through all the matching referrals, starting with the oldest one
301 -- (--> ORDER by creation_date ASC),
302 -- to find out if the product (item) matches between the referral and
303 -- and the order line. If a matche is found, get the beneficiary_id
304 -- and referral_id and all's done.
305 -- --------------------------------------------------------------------
306 FOR x IN c_matching_referral LOOP
307 FOR y IN c_matching_line(x.referral_id) LOOP
308 x_beneficiary_id := x.partner_cust_account_id;
309 x_referral_id := x.referral_id;
310 Debug('Beneficiary Found: ' || x_beneficiary_id);
311 Write_Conc_Log;
312 RETURN;
313 END LOOP;
314 END LOOP;
315
316
317 -- --------------------------------------------------------------------
318 -- If it comes to this part of code, it means one of the following
319 -- conditions is true:
320 -- (1) There are currently no referrals associated with this order.
321 -- (2) There is at least one referral associated with this order, but
322 -- the logic above cannot find a matching product within those
323 -- referrals.
324 --
325 -- In any case, we will search for all the active and unassigned
326 -- referrals for a product/offer/customer match.
327 -- --------------------------------------------------------------------
328 FOR x IN c_matching_referral_2 LOOP
329 x_beneficiary_id := x.partner_cust_account_id;
330 x_referral_id := x.referral_id;
331
332 -- -----------------------------------------------------------------
333 -- Associate the order with this referral.
334 -- -----------------------------------------------------------------
335 UPDATE pv_referrals_b
336 SET order_id = p_order_header_id
337 WHERE referral_id = x.referral_id AND
338 order_id IS NULL;
339
340 Debug('Beneficiary Found: ' || x_beneficiary_id);
341 Write_Conc_Log;
342 END LOOP;
343
344
345
346 -- ====================================================================
347 -- ====================================================================
348 -- Process returned orders.
349 -- ====================================================================
350 -- ====================================================================
351
352 -- --------------------------------------------------------------------
353 -- Find a matching referral for the returned order.
354 --
355 -- A returned order has to be unambiguously matched to a referral
356 -- for the negative accrual to be made on it (In the FOR LOOP below,
357 -- x.count has to be 1).
358 --
359 -- Note that a matching referral is a referral that already has an
360 -- order tied to it, but does not yet have a claim created for it,
361 -- and has a referral creation date that is earlier than the order
362 -- creation date. However, it is irrespective of the referral expiration
363 -- date since an returned order can happen at any time.
364 -- --------------------------------------------------------------------
365 ELSE
366 OPEN l_ref_cursor FOR l_returned_order_match_sql
367 USING p_offer_id, p_order_header_id, p_order_line_id;
368
369 LOOP
370 FETCH l_ref_cursor INTO lc_template;
371 EXIT WHEN (l_ref_cursor%NOTFOUND OR lc_template.outer_counter <> 1);
372
373 IF (lc_template.claim_id IS NULL AND lc_template.outer_counter = 1) THEN
374 x_beneficiary_id := lc_template.partner_cust_account_id;
375 x_referral_id := lc_template.referral_id;
376
377 Debug('Beneficiary Found: ' || x_beneficiary_id);
378 Write_Conc_Log;
379 END IF;
380 END LOOP;
381
382 CLOSE l_ref_cursor;
383 END IF;
384
385
386 Write_Conc_Log;
387
388
389 -------------------- Exception --------------------------
390 EXCEPTION
391 WHEN FND_API.G_EXC_ERROR THEN
392 x_return_status := FND_API.G_RET_STS_ERROR;
393 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
394 p_count => x_msg_count,
395 p_data => x_msg_data);
396
397 Write_Conc_Log;
398
399 WHEN FND_API.g_exc_unexpected_error THEN
400 x_return_status := FND_API.g_ret_sts_unexp_error;
401 FND_MSG_PUB.count_and_get(
402 p_encoded => FND_API.g_false,
403 p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406
407 Write_Conc_Log;
408
409 WHEN OTHERS THEN
410 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
411 FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
412 END IF;
413
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 FND_MSG_PUB.count_and_get(
416 p_encoded => FND_API.g_false,
417 p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420
421 Write_Conc_Log;
422 END Get_Beneficiary;
423 -- ========================End of Get_Beneficiary==============================
424
425
426
427 --=============================================================================+
428 --| Public Procedure |
429 --| Check_Order_Completion |
430 --| |
431 --| Parameters |
432 --| IN |
433 --| OUT |
434 --| |
435 --| |
436 --| NOTES |
437 --| |
438 --| HISTORY |
439 --| |
440 --==============================================================================
441 PROCEDURE Check_Order_Completion (
442 ERRBUF OUT NOCOPY VARCHAR2,
443 RETCODE OUT NOCOPY VARCHAR2,
444 p_log_to_file IN VARCHAR2 := 'Y'
445 )
446 IS
447 i NUMBER;
448 l_incomplete_order BOOLEAN;
449 l_return_status VARCHAR2(100);
450 l_msg_count NUMBER;
451 l_msg_data VARCHAR2(500);
452 l_claim_rec ozf_partner_claim_grp.claim_rec_type;
453 l_empty_claim_rec ozf_partner_claim_grp.claim_rec_type;
454 l_promotion_activity_rec ozf_partner_claim_grp.promotion_activity_rec_type;
455 l_empty_promo_act_rec ozf_partner_claim_grp.promotion_activity_rec_type;
456 l_claim_id NUMBER;
457 l_claim_number VARCHAR2(30);
458 l_claim_amount NUMBER;
459 l_total_start NUMBER;
460 l_elapsed_time NUMBER;
461 l_referral_counter NUMBER := 1;
462 l_operating_unit VARCHAR2(240);
463
464 -- -------------------------------------------------------------------------
465 -- The SQL finds all the referrals in the system that has an order
466 -- associated with it but does not have a claim created for it yet.
467 --
468 -- QUESTION: can a referral be "manually closed" when it has an order
469 -- associated with it? Why not? --> David.
470 -- -------------------------------------------------------------------------
471 CURSOR c_incomplete_orders IS
472 SELECT REF.referral_name,
473 REF.referral_id,
474 REF.referral_code,
475 REF.currency_code,
476 REF.partner_cust_account_id,
477 REF.org_id,
478 REF.partner_id,
479 BENFT.additional_info_1 offer_id,
480 BENFT.benefit_id
481 FROM pv_referrals_vl REF,
482 pv_ge_benefits_b BENFT
483 WHERE BENFT.benefit_id = REF.benefit_id AND
484 REF.order_id > 0 AND
485 REF.claim_id IS NULL;
486
487 -- -------------------------------------------------------------------------
488 -- The SQL/cursor below checks for order completion of a referral.
489 --
490 -- Note that under referral, C in C1, C2, and C3 stands for category_id
491 -- for Single Product Hierarhcy. A category_id can contain one or more
492 -- inventory items as is in the case of C2 --> P2, P3.
493 --
494 -- The following is an example of an order that is not complete since for
495 -- order line #3 (L3), there is no accrual made for that line.
496 -- In case (2), the order is complete since there is at least one accrual
497 -- made for every matching line between the referral and the order.
498 --
499 -- (1)
500 -- Referral Order Accrual
501 -- -------- ----- --------
502 -- C1 -----> L1 P1 --------> A1 A2 A3
503 -- C2 -----> L2 P2 --------> A4
504 -- C2 -----> L3 P2 --------> null
505 -- C2 -----> L4 P3 --------> A5
506 -- C3 L5 P4
507 -- C5 L6 P6
508 --
509 -- (2)
510 -- Referral Order Accrual
511 -- -------- ----- --------
512 -- C1 -----> L1 P1 --------> A1 A2 A3
513 -- C2 -----> L2 P2 --------> A4
514 -- C2 -----> L3 P2 --------> A6 A7
515 -- C2 -----> L4 P3 --------> A5
516 -- C3 L5 P4
517 -- C5 L6 P6
518 --
519 -- Note that in the SQL below, x stands for the join between Referral
520 -- and Order, which UTL stands for the "Accrual" in the diagram above.
521 --
522 -- -------------------------------------------------------------------------
523 CURSOR c_ref_order_line_match (pc_referral_id NUMBER) IS
524 SELECT DISTINCT x.order_id, x.line_id, UTL.utilization_id,
525 UTL.org_id, UTL.exchange_rate_type, UTL.exchange_rate_date,
526 UTL.exchange_rate, UTL.currency_code
527 FROM
528 (SELECT ACCRUAL.plan_type,
529 ACCRUAL.plan_id,
530 ACCRUAL.utilization_id,
531 ACCRUAL.utilization_type,
532 ACCRUAL.reference_type,
533 ACCRUAL.reference_id,
534 ACCRUAL.org_id,
535 ACCRUAL.cust_account_id,
536 ACCRUAL.object_id order_header_id,
537 ACCRUAL.order_line_id,
538 ACCRUAL.currency_code,
539 ACCRUAL.exchange_rate_type,
540 ACCRUAL.exchange_rate_date,
541 ACCRUAL.exchange_rate
542 FROM ozf_funds_utilized_all_b ACCRUAL
543 WHERE ACCRUAL.object_type = 'ORDER'
544 ) UTL,
545 (SELECT PROD.product_category_id,
546 REF.order_id,
547 LINE.line_id,
548 MIC.inventory_item_id,
549 LINE.flow_status_code,
550 OFFER.qp_list_header_id,
551 REF.referral_id,
552 REF.partner_cust_account_id
553 FROM pv_referrals_b REF,
554 pv_referred_products PROD,
555 pv_ge_benefits_b BENFT,
556 oe_order_headers_all HEADER,
557 oe_order_lines_all LINE,
558 mtl_item_categories MIC,
559 eni_prod_denorm_hrchy_v DENORM,
560 ozf_offers OFFER
561 WHERE REF.referral_id = pc_referral_id AND
562 BENFT.benefit_id = REF.benefit_id AND
563 REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
564 REF.referral_id = PROD.referral_id AND
565 REF.order_id = HEADER.header_id AND
566 HEADER.header_id = LINE.header_id AND
567 LINE.flow_status_code <> 'CANCELLED' AND
568 LINE.inventory_item_id = MIC.inventory_item_id AND
569 MIC.category_set_id = DENORM.category_set_id AND
570 MIC.category_id = DENORM.child_id AND
571 PROD.product_category_set_id = DENORM.category_set_id AND
572 PROD.product_category_id = DENORM.parent_id AND
573 BENFT.additional_info_1 = OFFER.offer_id
574 ) x
575 WHERE UTL.plan_type (+) = 'OFFR' AND
576 UTL.plan_id (+) = x.qp_list_header_id AND -- not offer_id!
577 UTL.utilization_type (+) = 'LEAD_ACCRUAL' AND
578 UTL.reference_type (+) = 'LEAD_REFERRAL' AND
579 UTL.reference_id (+) = x.referral_id AND
580 UTL.cust_account_id (+) = x.partner_cust_account_id AND
581 UTL.order_header_id (+) = x.order_id AND
582 UTL.order_line_id (+) = x.line_id;
583
584 BEGIN
585 g_api_name := 'Check_Order_Completion';
586
587 -- -----------------------------------------------------------------------
588 -- Set variables.
589 -- -----------------------------------------------------------------------
590 l_total_start := dbms_utility.get_time;
591
592 g_module_name := 'Referral Compensation: Order Completion CC';
593
594 dbms_application_info.set_module(
595 module_name => g_module_name,
596 action_name => 'STARTUP'
597 );
598
599 IF (p_log_to_file <> 'Y') THEN
600 g_log_to_file := 'N';
601 ELSE
602 g_log_to_file := 'Y';
603 END IF;
604
605
606 -- -----------------------------------------------------------------------
607 -- Start time message...
608 -- -----------------------------------------------------------------------
609 Debug(p_msg_string => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
610 p_msg_type => 'PV_ORDER_COMPLETE_START_TIME',
611 p_token_type => 'P_DATE_TIME',
612 p_statement_level => FND_LOG.LEVEL_EVENT
613 );
614
615 -- --------------------------------------------------------------------------
616 -- Get all (active) referrals that has an order associated with it, but no
617 -- claims created for it.
618 -- --------------------------------------------------------------------------
619 FOR x IN c_incomplete_orders LOOP
620 l_incomplete_order := FALSE;
621 i := 0;
622
623 dbms_application_info.set_module(
624 module_name => g_module_name,
625 action_name => 'Checking Referral #' || l_referral_counter
626 );
627
628 Debug('Referral #' || l_referral_counter);
629 Debug('Checking "' || x.referral_name || '" (referral_id = ' ||
630 x.referral_id || ') for order completion...');
631
632 l_referral_counter := l_referral_counter + 1;
633
634 -- -----------------------------------------------------------------
635 -- Set up claim parameters
636 -- -----------------------------------------------------------------
637 l_claim_id := null;
638 l_claim_number := null;
639 l_claim_amount := null;
640 l_claim_rec := l_empty_claim_rec;
641 l_promotion_activity_rec := l_empty_promo_act_rec;
642
643
644 -- -----------------------------------------------------------------------
645 -- For each of the referrals retrieved above, check if the order is
646 -- complete.
647 -- -----------------------------------------------------------------------
648 FOR y IN c_ref_order_line_match(x.referral_id) LOOP
649 i := i + 1;
650
651 IF (y.utilization_id IS NULL) THEN
652 l_incomplete_order := TRUE;
653 EXIT;
654 END IF;
655
656 -- ---------------------------------------------------------------------
657 -- Set the ord_id and currency info for the claim.
658 -- These values should be derived from the accrual, whose values are same
659 -- as that of the order. They should not be derived from partner's
660 -- responsibility.
661 -- ---------------------------------------------------------------------
662 l_claim_rec.org_id := y.org_id;
663 l_claim_rec.currency_code := y.currency_code;
664 l_claim_rec.exchange_rate_type := y.exchange_rate_type;
665 l_claim_rec.exchange_rate_date := y.exchange_rate_date;
666 l_claim_rec.exchange_rate := y.exchange_rate;
667 END LOOP;
668
669
670 IF (l_incomplete_order OR i = 0) THEN
671 Set_Message(
672 p_msg_name => 'PV_REFERRAL_ORDER_NOT_COMPLETE'
673 );
674
675 Debug('-----------------------------------------------------------------------------');
676 END IF;
677
678 -- -----------------------------------------------------------------------
679 -- If the order is "complete", create a claim for this referral.
680 -- If creating claim fails for this referral, still need to go on to
681 -- the next referral. That's why there is a BEGIN-END block here.
682 -- -----------------------------------------------------------------------
683 IF ((NOT l_incomplete_order) AND i > 0) THEN
684 Set_Message(
685 p_msg_name => 'PV_REFERRAL_ORDER_COMPLETE'
686 );
687
688 BEGIN
689 -- -----------------------------------------------------------------
690 -- Set up claim parameters
691 -- -----------------------------------------------------------------
692 l_claim_rec.source_object_id := x.referral_id;
693 l_claim_rec.source_object_class := 'REFERRAL';
694 l_claim_rec.source_object_number := SUBSTR(x.referral_code, 1, 30);
695 l_claim_rec.cust_account_id := x.partner_cust_account_id;
696 l_claim_rec.pay_to_cust_account_id := x.partner_cust_account_id;
697
698 FOR z IN (SELECT qp_list_header_id FROM ozf_offers WHERE offer_id = x.offer_id) LOOP
699 l_promotion_activity_rec.offer_id := z.qp_list_header_id;
700 END LOOP;
701
702 l_promotion_activity_rec.reference_type := 'LEAD_REFERRAL';
703 l_promotion_activity_rec.reference_id := l_claim_rec.source_object_id;
704
705
706 -- -----------------------------------------------------------------
707 -- Create claim
708 -- -----------------------------------------------------------------
709 ozf_partner_claim_grp.Create_Claim(
710 p_api_version_number => 1.0,
711 p_claim_rec => l_claim_rec,
712 p_promotion_activity_rec => l_promotion_activity_rec,
713 x_claim_id => l_claim_id,
714 x_claim_number => l_claim_number,
715 x_claim_amount => l_claim_amount,
716 x_return_status => l_return_status,
717 x_msg_count => l_msg_count,
718 x_msg_data => l_msg_data
719 );
720
721 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
722 RAISE FND_API.G_EXC_ERROR;
723
724 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
725 RAISE FND_API.g_exc_unexpected_error;
726 END IF;
727
728
729 -- -----------------------------------------------------------------
730 -- Link the claim to the referral and update referral status.
731 -- -----------------------------------------------------------------
732 UPDATE pv_referrals_b
733 SET claim_id = l_claim_id,
734 claim_number = l_claim_number,
735 actual_compensation_amt = l_claim_amount,
736 actual_currency_code = l_claim_rec.currency_code,
737 referral_status = 'COMP_INITIATED'
738 WHERE referral_id = x.referral_id;
739
740
741 -- -------------------------------------------------
742 -- Raise business event
743 -- oracle.apps.pv.benefit.referral.statusChange
744 -- -------------------------------------------------
745 pv_benft_status_change.status_change_raise(
746 p_api_version_number => 1.0,
747 p_init_msg_list => FND_API.G_FALSE,
748 p_commit => FND_API.G_FALSE,
749 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
750 p_benefit_id => x.benefit_id,
751 p_entity_id => x.referral_id,
752 p_status_code => 'COMP_INITIATED',
753 p_partner_id => x.partner_id,
754 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
755 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
756 x_return_status => l_return_status,
757 x_msg_count => l_msg_count,
758 x_msg_data => l_msg_data);
759
760 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
761 raise FND_API.G_EXC_ERROR;
762 end if;
763
764 -- -------------------------------------------------
765 -- Log the event.
766 -- -------------------------------------------------
767 pv_benft_status_change.STATUS_CHANGE_LOGGING(
768 p_api_version_number => 1.0,
769 p_init_msg_list => FND_API.G_FALSE,
770 p_commit => FND_API.G_FALSE,
771 p_benefit_id => x.benefit_id,
772 P_STATUS => 'COMP_INITIATED',
773 p_entity_id => x.referral_id,
774 p_partner_id => x.partner_id,
775 x_return_status => l_return_status,
776 x_msg_count => l_msg_count,
777 x_msg_data => l_msg_data
778 );
779
780 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
781 raise FND_API.G_EXC_ERROR;
782 end if;
783
784 -- -------------------------------------------------
785 -- Commit the changes.
786 -- -------------------------------------------------
787 COMMIT;
788
789
790 Set_Message(
791 p_msg_name => 'PV_REFERRAL_CLAIM_CREATED',
792 p_token1 => 'CLAIM_ID',
793 p_token1_value => l_claim_id,
794 p_token2 => 'CLAIM_NUMBER',
795 p_token2_value => l_claim_number,
796 p_token3 => 'CLAIM_AMOUNT',
797 p_token3_value => l_claim_amount
798 );
799
800 -- -----------------------------------------------------------------
801 -- Display the org_id and the operating unit that the claim is
802 -- created in.
803 -- -----------------------------------------------------------------
804 FOR z IN (SELECT name
805 FROM hr_organization_units
806 WHERE organization_id = l_claim_rec.org_id)
807 LOOP
808 l_operating_unit := z.name;
809 END LOOP;
810
811 Debug('org_id = ' || l_claim_rec.org_id);
812 Debug('Organization = ' || l_operating_unit);
813 Debug('-----------------------------------------------------------------------------');
814
815
816 --------------------------- Exception ---------------------------------
817 EXCEPTION
818 WHEN FND_API.G_EXC_ERROR THEN
819 Debug('Creating claim exception ........................................');
820 l_return_status := FND_API.G_RET_STS_ERROR;
821 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
822 p_count => l_msg_count,
823 p_data => l_msg_data);
824
825 Write_Conc_Log;
826
827 g_RETCODE := '1';
828 Debug('-----------------------------------------------------------------------------');
829
830 WHEN FND_API.g_exc_unexpected_error THEN
831 Debug('Creating claim exception ........................................');
832 l_return_status := FND_API.g_ret_sts_unexp_error;
833 FND_MSG_PUB.count_and_get(
834 p_encoded => FND_API.g_false,
835 p_count => l_msg_count,
836 p_data => l_msg_data
837 );
838
839 Write_Conc_Log;
840
841 g_RETCODE := '1';
842 Debug('-----------------------------------------------------------------------------');
843
844 WHEN OTHERS THEN
845 Debug('Creating claim exception ........................................');
846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
847 FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
848 END IF;
849
850 l_return_status := FND_API.G_RET_STS_ERROR;
851 FND_MSG_PUB.count_and_get(
852 p_encoded => FND_API.g_false,
853 p_count => l_msg_count,
854 p_data => l_msg_data
855 );
856
857 Write_Conc_Log;
858
859 g_RETCODE := '1';
860 Debug('-----------------------------------------------------------------------------');
861
862 END;
863 END IF;
864 END LOOP;
865
866
867 -- -------------------------------------------------------------------------
868 -- Display End Time Message.
869 -- -------------------------------------------------------------------------
870 Debug(p_msg_string => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
871 p_msg_type => 'PV_ORDER_COMPLETION_END_TIME',
872 p_token_type => 'P_DATE_TIME',
873 p_statement_level =>FND_LOG.LEVEL_EVENT
874 );
875
876
877 l_elapsed_time := DBMS_UTILITY.get_time - l_total_start;
878 Debug('=====================================================================');
879 Debug('Total Elapsed Time: ' || l_elapsed_time || ' hsec' || ' = ' ||
880 ROUND((l_elapsed_time/6000), 2) || ' minutes');
881 Debug('=====================================================================');
882
883
884 END Check_Order_Completion;
885 -- ======================End of Check_Order_Completion===========================
886
887
888
889
890 --=============================================================================+
891 --| Public Procedure |
892 --| Update_Referral_Status |
893 --| |
894 --| Parameters |
895 --| IN |
896 --| OUT |
897 --| |
898 --| |
899 --| NOTES |
900 --| |
901 --| HISTORY |
902 --| |
903 --==============================================================================
904 PROCEDURE Update_Referral_Status (
905 p_api_version IN NUMBER,
906 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
907 p_commit IN VARCHAR2 := FND_API.g_false,
908 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
909 p_offer_id IN NUMBER,
910 p_pass_validation_flag IN VARCHAR2,
911 x_return_status OUT NOCOPY VARCHAR2,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2
914 )
915 IS
916 l_benefit_status_code VARCHAR2(50);
917 l_api_version NUMBER := 1;
918
919 BEGIN
920 g_api_name := 'Update_Referral_Status';
921
922 -------------------- initialize -------------------------
923 IF FND_API.to_boolean(p_init_msg_list) THEN
924 FND_MSG_PUB.initialize;
925 END IF;
926
927 IF NOT FND_API.compatible_api_call(
928 l_api_version,
929 p_api_version,
930 g_api_name,
931 g_pkg_name
932 ) THEN
933 RAISE FND_API.g_exc_unexpected_error;
934 END IF;
935
936 x_return_status := FND_API.G_RET_STS_SUCCESS;
937
938
939 ---------------------- Source code -----------------------
940 IF (UPPER(p_pass_validation_flag) NOT IN ('Y', 'N')) THEN
941 Debug('p_pass_validation_flag can only be either ''Y'' or ''N''');
942 RAISE FND_API.G_EXC_ERROR;
943 END IF;
944
945 -- ------------------------------------------------------------
946 -- Benefit status code lookup is: pv_benefit_status.
947 --
948 -- If the budget validation is successful, update the benefit
949 -- status to ACTIVE. Otherwise, update it to 'FAILED_VALIDATION'
950 -- ------------------------------------------------------------
951 IF (UPPER(p_pass_validation_flag) = 'Y') THEN
952 l_benefit_status_code := 'ACTIVE';
953
954 ELSE
955 l_benefit_status_code := 'FAILED_VALIDATION';
956 END IF;
957
958 UPDATE pv_ge_benefits_b
959 SET benefit_status_code = l_benefit_status_code
960 WHERE additional_info_1 = p_offer_id;
961
962 -------------------- Exception --------------------------
963 EXCEPTION
964 WHEN FND_API.G_EXC_ERROR THEN
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
967 p_count => x_msg_count,
968 p_data => x_msg_data);
969
970 WHEN FND_API.g_exc_unexpected_error THEN
971 x_return_status := FND_API.g_ret_sts_unexp_error;
972 FND_MSG_PUB.count_and_get(
973 p_encoded => FND_API.g_false,
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977
978 WHEN OTHERS THEN
979 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
980 FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
981 END IF;
982
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 FND_MSG_PUB.count_and_get(
985 p_encoded => FND_API.g_false,
986 p_count => x_msg_count,
987 p_data => x_msg_data
988 );
989
990 END Update_Referral_Status;
991 -- ======================End of Update_Referral_Status===========================
992
993
994 --=============================================================================+
995 --| Private Function |
996 --| |
997 --| Get_Partner_Account |
998 --| |
999 --| Parameters |
1000 --| IN |
1001 --| OUT |
1002 --| |
1003 --| |
1004 --| NOTES: |
1005 --| |
1006 --| HISTORY |
1007 --| |
1008 --==============================================================================
1009 FUNCTION Get_Partner_Account (
1010 p_partner_id IN NUMBER
1011 )
1012 RETURN NUMBER
1013 IS
1014 -- --------------------------------------------------------------------------
1015 -- A partner can have multiple customer_account_id's. For now, we will just
1016 -- pick the one with the lowest cust_account_id.
1017 --
1018 -- Note that in 11.5.10, a partner will always have a cust_account_id.
1019 -- The following query will always return something.
1020 -- --------------------------------------------------------------------------
1021 CURSOR c IS
1022 SELECT MIN(cust_account_id) cust_account_id
1023 FROM pv_partner_profiles a,
1024 hz_cust_accounts b
1025 WHERE a.partner_id = p_partner_id AND
1026 a.partner_party_id = b.party_id;
1027
1028 l_partner_account_id NUMBER;
1029
1030 BEGIN
1031 FOR x IN c LOOP
1032 l_partner_account_id := x.cust_account_id;
1033 END LOOP;
1034
1035 RETURN l_partner_account_id;
1036 END Get_Partner_Account;
1037 -- ===========================End of Get_Partner_Account========================
1038
1039
1040
1041 --=============================================================================+
1042 --| Private Procedure |
1043 --| |
1044 --| Write_Conc_Log |
1045 --| |
1046 --| Parameters |
1047 --| IN |
1048 --| OUT |
1049 --| |
1050 --| |
1051 --| NOTES: |
1052 --| |
1053 --| HISTORY |
1054 --| |
1055 --==============================================================================
1056 PROCEDURE Write_Conc_Log IS
1057 l_count NUMBER;
1058 l_msg VARCHAR2(2000);
1059 l_cnt NUMBER ;
1060
1061 BEGIN
1062 l_count := FND_MSG_PUB.count_msg;
1063
1064 FOR l_cnt IN 1 .. l_count
1065 LOOP
1066 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1067 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1068 END LOOP;
1069 END Write_Conc_Log;
1070 -- =============================End of Write_Conc_Log===========================
1071
1072
1073
1074 --=============================================================================+
1075 --| Private Procedure |
1076 --| |
1077 --| Debug |
1078 --| |
1079 --| Parameters |
1080 --| IN |
1081 --| OUT |
1082 --| |
1083 --| |
1084 --| NOTES: |
1085 --| |
1086 --| HISTORY |
1087 --| |
1088 --==============================================================================
1089 PROCEDURE Debug(
1090 p_msg_string IN VARCHAR2,
1091 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE',
1092 p_token_type IN VARCHAR2 := 'TEXT',
1093 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
1094 )
1095 IS
1096 BEGIN
1097 FND_MESSAGE.Set_Name('PV', p_msg_type);
1098 FND_MESSAGE.Set_Token(p_token_type, p_msg_string);
1099
1100 IF (g_log_to_file = 'N') THEN
1101 FND_MSG_PUB.Add;
1102
1103 ELSIF (g_log_to_file = 'Y') THEN
1104 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1105 END IF;
1106
1107 IF (p_statement_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1108 FND_LOG.STRING(p_statement_level,
1109 'pv.plsql.' || g_pkg_name || '.' || g_api_name,
1110 p_msg_string
1111 );
1112 END IF;
1113 END Debug;
1114 -- =================================End of Debug================================
1115
1116
1117 --=============================================================================+
1118 --| Public Procedure |
1119 --| |
1120 --| Set_Message |
1121 --| |
1122 --| Parameters |
1123 --| IN |
1124 --| OUT |
1125 --| |
1126 --| |
1127 --| NOTES: |
1128 --| |
1129 --| HISTORY |
1130 --| |
1131 --==============================================================================
1132 PROCEDURE Set_Message(
1133 p_msg_name IN VARCHAR2,
1134 p_token1 IN VARCHAR2 := NULL,
1135 p_token1_value IN VARCHAR2 := NULL,
1136 p_token2 IN VARCHAR2 := NULL,
1137 p_token2_value IN VARCHAR2 := NULL,
1138 p_token3 IN VARCHAR2 := NULL,
1139 p_token3_value IN VARCHAR2 := NULL,
1140 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
1141 )
1142 IS
1143 BEGIN
1144 FND_MESSAGE.Set_Name('PV', p_msg_name);
1145
1146 IF (p_token1 IS NOT NULL) THEN
1147 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
1148 END IF;
1149
1150 IF (p_token2 IS NOT NULL) THEN
1151 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
1152 END IF;
1153
1154 IF (p_token3 IS NOT NULL) THEN
1155 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
1156 END IF;
1157
1158 IF (p_statement_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1159 FND_LOG.MESSAGE(
1160 p_statement_level,
1161 'pv.plsql.' || g_pkg_name || '.' || g_api_name,
1162 FALSE
1163 );
1164 END IF;
1165
1166 IF (g_log_to_file = 'N') THEN
1167 FND_MSG_PUB.Add;
1168
1169 ELSIF (g_log_to_file = 'Y') THEN
1170 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get);
1171 END IF;
1172
1173 END Set_Message;
1174 -- ==============================End of Set_Message==============================
1175
1176 END PV_REFERRAL_COMP_PUB;