[Home] [Help]
55: Procedure WriteLog ( p_msg IN VARCHAR2)
56: IS
57: BEGIN
58:
59: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
60: iex_debug_pub.LogMessage (p_msg);
61: END IF;
62:
63: END WriteLog;
182: else
183: l_custom_select := l_custom_select || ' AND del.delinquency_id ';
184: end if;
185:
186: write_log(FND_LOG.LEVEL_STATEMENT,G_PKG_NAME || ' ' || l_api_name || ' - l_custom_select : '||l_custom_select);
187:
188: END custom_where_clause;
189: --End adding by gnramasa for bug 8630852 13-July-09
190:
336: l_return_status := FND_API.G_RET_STS_SUCCESS;
337:
338: /* Check the required profiles for Strategy Concurrent before starting */
339: if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
340: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation aborted. ' );
341: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Disabled by Profile ');
342: return;
343: end if;
344:
337:
338: /* Check the required profiles for Strategy Concurrent before starting */
339: if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
340: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation aborted. ' );
341: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Disabled by Profile ');
342: return;
343: end if;
344:
345: if (l_DefaultStrategyLevel = 50) Then
342: return;
343: end if;
344:
345: if (l_DefaultStrategyLevel = 50) Then
346: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
347: write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Run Level from IEX_APP_PREFERENCES ');
348: b_Skip := 'T';
349: end if;
350:
343: end if;
344:
345: if (l_DefaultStrategyLevel = 50) Then
346: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
347: write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Run Level from IEX_APP_PREFERENCES ');
348: b_Skip := 'T';
349: end if;
350:
351: l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
349: end if;
350:
351: l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
352: if (l_StrategyTempID = 0) Then
353: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
354: write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Template Profile ');
355: b_Skip := 'T';
356: end if;
357:
350:
351: l_StrategyTempID := NVL(to_number(FND_PROFILE.VALUE('IEX_STRATEGY_DEFAULT_TEMPLATE')), 0);
352: if (l_StrategyTempID = 0) Then
353: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
354: write_log(FND_LOG.LEVEL_UNEXPECTED, 'No Default Strategy Template Profile ');
355: b_Skip := 'T';
356: end if;
357:
358: if (l_default_rs_ID = 0) Then
355: b_Skip := 'T';
356: end if;
357:
358: if (l_default_rs_ID = 0) Then
359: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
360: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Default Resource Profile not set. IEX: Strategy Default Resource ');
361: b_Skip := 'T';
362: end if;
363:
356: end if;
357:
358: if (l_default_rs_ID = 0) Then
359: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
360: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Default Resource Profile not set. IEX: Strategy Default Resource ');
361: b_Skip := 'T';
362: end if;
363:
364: if (l_resource_ID = 0) Then
361: b_Skip := 'T';
362: end if;
363:
364: if (l_resource_ID = 0) Then
365: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
366: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Fulfilment Resource Profile not set. ');
367: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Fulfilment Resource should be configured for fulfilment ');
368: b_Skip := 'T';
369: end if;
362: end if;
363:
364: if (l_resource_ID = 0) Then
365: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
366: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Fulfilment Resource Profile not set. ');
367: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Fulfilment Resource should be configured for fulfilment ');
368: b_Skip := 'T';
369: end if;
370:
363:
364: if (l_resource_ID = 0) Then
365: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation stopped. ' );
366: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Fulfilment Resource Profile not set. ');
367: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Fulfilment Resource should be configured for fulfilment ');
368: b_Skip := 'T';
369: end if;
370:
371: if (b_Skip = 'T') then
372: retcode := '2';
373: return;
374: end if;
375:
376: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation started. ' );
377: -- Standard Start of API savepoint
378: SAVEPOINT START_STRY_CONT;
379:
380: begin
384: WHEN OTHERS THEN
385: fnd_file.put_line(FND_FILE.LOG, 'Pre Delinquency flag raised exception; sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
386: end;
387:
388: write_log(FND_LOG.LEVEL_UNEXPECTED, 'pre-delinquency strategy template flag = ' ||pre_delinquency_flag);
389:
390: -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
391: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
392: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
387:
388: write_log(FND_LOG.LEVEL_UNEXPECTED, 'pre-delinquency strategy template flag = ' ||pre_delinquency_flag);
389:
390: -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
391: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
392: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
393: -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
394:
395: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
388: write_log(FND_LOG.LEVEL_UNEXPECTED, 'pre-delinquency strategy template flag = ' ||pre_delinquency_flag);
389:
390: -- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
391: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
392: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
393: -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
394:
395: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
396:
391: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Do not automatically switch strategies flag = ' ||l_ignore_switch);
392: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy Grace Period = ' || l_graceperiod);
393: -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
394:
395: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
396:
397: write_log(FND_LOG.LEVEL_STATEMENT, 'System Strategy Level' || l_system_strategy_level);
398:
399: OPEN pre_del_strategy;
393: -- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
394:
395: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor started ');
396:
397: write_log(FND_LOG.LEVEL_STATEMENT, 'System Strategy Level' || l_system_strategy_level);
398:
399: OPEN pre_del_strategy;
400: FETCH pre_del_strategy INTO l_pre_del_strategy;
401: CLOSE pre_del_strategy;
676: l_stry_cnt_rec.status;
677: /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
678:
679: if c_open_delinquencies%FOUND then
680: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
681: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency_id = '
682: || l_stry_cnt_rec.delinquency_id
683: || ' object Id = ' || l_stry_cnt_rec.object_id
684: || ' object_type = ' || l_stry_cnt_rec.object_type
677: /* ctlee - add status and pass it to GetTemplateId 7/3/2003 */
678:
679: if c_open_delinquencies%FOUND then
680: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
681: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency_id = '
682: || l_stry_cnt_rec.delinquency_id
683: || ' object Id = ' || l_stry_cnt_rec.object_id
684: || ' object_type = ' || l_stry_cnt_rec.object_type
685: || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
706: --
707: -- Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
708: -- fetch c_Score_Exists into l_stry_cnt_rec.score_value;
709: -- Close c_Score_Exists;
710: -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
711: -- write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
712: -- || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
713: -- || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
714: -- || ' Score Value = ' || l_stry_cnt_rec.score_value );
707: -- Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
708: -- fetch c_Score_Exists into l_stry_cnt_rec.score_value;
709: -- Close c_Score_Exists;
710: -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
711: -- write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
712: -- || ' jtf_object Type ' || l_stry_cnt_rec.jtf_object_type
713: -- || ' jtf_object id ' || l_stry_cnt_rec.jtf_object_id
714: -- || ' Score Value = ' || l_stry_cnt_rec.score_value );
715: -- end if;
725: Open c_Score_Exists(l_stry_cnt_rec.jtf_object_id, l_stry_cnt_rec.jtf_object_type);
726: fetch c_Score_Exists into l_stry_cnt_rec.score_value,l_stry_cnt_rec.score_id;
727: Close c_Score_Exists;
728:
729: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
730: write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
731: || '; jtf_object Type= ' || l_stry_cnt_rec.jtf_object_type
732: || '; jtf_object id= ' || l_stry_cnt_rec.jtf_object_id
733: || '; Score Value = ' || l_stry_cnt_rec.score_value );
726: fetch c_Score_Exists into l_stry_cnt_rec.score_value,l_stry_cnt_rec.score_id;
727: Close c_Score_Exists;
728:
729: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
730: write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists '
731: || '; jtf_object Type= ' || l_stry_cnt_rec.jtf_object_type
732: || '; jtf_object id= ' || l_stry_cnt_rec.jtf_object_id
733: || '; Score Value = ' || l_stry_cnt_rec.score_value );
734: end if;
738: -- but if a customer scores the delinquency we should use the delinquency score to set the strategy
739: Open c_score_exists_del(l_stry_cnt_rec.payment_schedule_id, 'IEX_INVOICES', l_stry_cnt_rec.delinquency_id, 'IEX_DELINQUENCY');
740: fetch c_score_exists_del into l_stry_cnt_rec.score_value, l_score_object_id, l_score_object_code,l_stry_cnt_rec.score_id;
741: Close c_score_exists_del;
742: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
743: write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists_del '
744: || '; jtf_object Type= ' || l_score_object_code
745: || '; jtf_object id= ' || l_score_object_id
746: || '; Score Value = ' || l_stry_cnt_rec.score_value );
739: Open c_score_exists_del(l_stry_cnt_rec.payment_schedule_id, 'IEX_INVOICES', l_stry_cnt_rec.delinquency_id, 'IEX_DELINQUENCY');
740: fetch c_score_exists_del into l_stry_cnt_rec.score_value, l_score_object_id, l_score_object_code,l_stry_cnt_rec.score_id;
741: Close c_score_exists_del;
742: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
743: write_log(FND_LOG.LEVEL_STATEMENT, ' Got New Score using c_Score_Exists_del '
744: || '; jtf_object Type= ' || l_score_object_code
745: || '; jtf_object id= ' || l_score_object_id
746: || '; Score Value = ' || l_stry_cnt_rec.score_value );
747: end if;
771: -- Added the if condition for bug 14013369 bibeura
772: -- The IF condition is added to avoid execution of the following piece of code
773: -- when the profile value for grace period is not set which will improve the performance
774: IF NVL(l_gracePeriod,0) <> 0 THEN
775: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_gracePeriod-Bina ' || l_gracePeriod );
777: END IF;
778: IF l_DefaultStrategyLevel = 10 THEN
779: OPEN c_gracePeriod FOR
772: -- The IF condition is added to avoid execution of the following piece of code
773: -- when the profile value for grace period is not set which will improve the performance
774: IF NVL(l_gracePeriod,0) <> 0 THEN
775: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_gracePeriod-Bina ' || l_gracePeriod );
777: END IF;
778: IF l_DefaultStrategyLevel = 10 THEN
779: OPEN c_gracePeriod FOR
780: select c.creation_date from iex_delinquencies_all c
807: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
808: END IF;
809: loop
810: fetch c_gracePeriod into vGracePeriod;
811: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
812: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy grace Period ' || vGracePeriod );
813: end if;
814: if c_gracePeriod%notfound then
815: exit;
808: END IF;
809: loop
810: fetch c_gracePeriod into vGracePeriod;
811: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
812: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy grace Period ' || vGracePeriod );
813: end if;
814: if c_gracePeriod%notfound then
815: exit;
816: end if;
818: end loop;
819: Close c_gracePeriod;
820: END IF;
821: l_turnoff_coll_on_bankru := nvl(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'),'N');
822: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_turnoff_coll_on_bankru: ' || l_turnoff_coll_on_bankru);
823:
824: if l_turnoff_coll_on_bankru = 'Y' then
825: open c_no_of_bankruptcy (l_stry_cnt_rec.PARTY_CUST_ID);
826: fetch c_no_of_bankruptcy into l_no_of_bankruptcy;
825: open c_no_of_bankruptcy (l_stry_cnt_rec.PARTY_CUST_ID);
826: fetch c_no_of_bankruptcy into l_no_of_bankruptcy;
827: close c_no_of_bankruptcy;
828: end if;
829: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_no_of_bankruptcy: ' || l_no_of_bankruptcy);
830:
831: if (l_turnoff_coll_on_bankru = 'Y' and l_no_of_bankruptcy >0) then
832: write_log(FND_LOG.LEVEL_PROCEDURE, 'Profile IEX: Turn Off Collections Activity for Bankruptcy is Yes and bankruptcy record is exist, so will skip assigning strategy');
833: goto nextRec;
828: end if;
829: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_no_of_bankruptcy: ' || l_no_of_bankruptcy);
830:
831: if (l_turnoff_coll_on_bankru = 'Y' and l_no_of_bankruptcy >0) then
832: write_log(FND_LOG.LEVEL_PROCEDURE, 'Profile IEX: Turn Off Collections Activity for Bankruptcy is Yes and bankruptcy record is exist, so will skip assigning strategy');
833: goto nextRec;
834: end if;
835:
836:
892: Close C_Strategy_Exists;
893:
894: vOrginalStrategyStatus :=vStrategyStatus; --Added for bug#5202312 by schekuri on 05-May-2006
895:
896: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900: write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
893:
894: vOrginalStrategyStatus :=vStrategyStatus; --Added for bug#5202312 by schekuri on 05-May-2006
895:
896: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900: write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
901:
894: vOrginalStrategyStatus :=vStrategyStatus; --Added for bug#5202312 by schekuri on 05-May-2006
895:
896: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900: write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
901:
902: if (vStrategyStatus = 'OPEN' or vStrategyStatus = 'PENDING') then
895:
896: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900: write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
901:
902: if (vStrategyStatus = 'OPEN' or vStrategyStatus = 'PENDING') then
903: begin
896: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status = ' || vStrategyStatus );
897: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Score Value = ' || vScoreValue );
898: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Id = ' || vStrategyId );
899: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Template Id = ' || vStrategyTemplateId );
900: write_log(FND_LOG.LEVEL_PROCEDURE, 'score_history/delinquency Score Value = ' || l_stry_cnt_rec.score_value );
901:
902: if (vStrategyStatus = 'OPEN' or vStrategyStatus = 'PENDING') then
903: begin
904: select strategy_rank, decode(score_tolerance, null, 0, score_tolerance), change_strategy_yn
909: vStrategyRank := '0';
910: vScoreTolerance := 0;
911: vChangeStrategy := 'N';
912: end;
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
910: vScoreTolerance := 0;
911: vChangeStrategy := 'N';
912: end;
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918: END IF;
911: vChangeStrategy := 'N';
912: end;
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918: END IF;
919:
912: end;
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918: END IF;
919:
920: -- score in iex_strategies diff from score_histories/iex_delinquencies_all table
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: write_log(FND_LOG.LEVEL_PROCEDURE, 'Get Strategy Template Details of = ' || vStrategyTemplateId || ' of Strategy ID = ' || vStrategyID );
915: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Rank = ' || vStrategyRank );
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918: END IF;
919:
920: -- score in iex_strategies diff from score_histories/iex_delinquencies_all table
921: -- and score history out of the strategy template score tolerance
940:
941: -- cancel strategy
942: -- begin bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
943: -- BEGIN
944: -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
945: -- IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
946: -- strategy_id => vStrategyId,
947: -- status => 'CANCELLED' ) ;
948: -- EXCEPTION
946: -- strategy_id => vStrategyId,
947: -- status => 'CANCELLED' ) ;
948: -- EXCEPTION
949: -- WHEN OTHERS THEN
950: -- write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
951: -- UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;
952: -- END;
953: -- end bug 4944801 ctlee 01/18/2006, cancel strategy only if the new strategy template id different from the old one
954: vStrategyStatus := 'CANCELLED';
968: p_stry_cnt_rec => l_stry_cnt_rec,
969: x_strategy_template_id => l_strategy_template_id
970: );
971: fnd_file.put_line(FND_FILE.LOG, ' Template ID selected ' || l_strategy_template_id);
972: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
973: write_log(FND_LOG.LEVEL_PROCEDURE, 'Delinquency ID ' || l_stry_cnt_rec.delinquency_id ||
974: ' Strategy Template ID selected ' || l_strategy_template_id );
975: end if;
976:
969: x_strategy_template_id => l_strategy_template_id
970: );
971: fnd_file.put_line(FND_FILE.LOG, ' Template ID selected ' || l_strategy_template_id);
972: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
973: write_log(FND_LOG.LEVEL_PROCEDURE, 'Delinquency ID ' || l_stry_cnt_rec.delinquency_id ||
974: ' Strategy Template ID selected ' || l_strategy_template_id );
975: end if;
976:
977: -- start for bug 8708271 multi level strategy
974: ' Strategy Template ID selected ' || l_strategy_template_id );
975: end if;
976:
977: -- start for bug 8708271 multi level strategy
978: --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
979: write_log(FND_LOG.LEVEL_PROCEDURE, 'Cancelling strategies for Party ID ' || l_stry_cnt_rec.party_cust_id ||
980: ' if exists other than level ' || l_DefaultStrategyLevel );
981: --end if;
982: cancel_strategy( l_stry_cnt_rec.party_cust_id, l_DefaultStrategyLevel,p_strategy_mode,
975: end if;
976:
977: -- start for bug 8708271 multi level strategy
978: --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
979: write_log(FND_LOG.LEVEL_PROCEDURE, 'Cancelling strategies for Party ID ' || l_stry_cnt_rec.party_cust_id ||
980: ' if exists other than level ' || l_DefaultStrategyLevel );
981: --end if;
982: cancel_strategy( l_stry_cnt_rec.party_cust_id, l_DefaultStrategyLevel,p_strategy_mode,
983: l_stry_cnt_rec.cust_account_id, l_stry_cnt_rec.customer_site_use_id, l_stry_cnt_rec.delinquency_id, p_show_output);
995: if (l_strategy_template_id = vStrategyTemplateId and vOrginalStrategyStatus <> l_StratStatusClosed and
996: vOrginalStrategyStatus <> l_StratStatusCancelled) then
997: --End bug#5202312 schekuri 05-May-2006
998:
999: write_log(FND_LOG.LEVEL_PROCEDURE, 'same template and continue, strategy template id = ' || vStrategyTemplateId );
1000: goto nextRec; -- continue to the loop for the next record, no need to change strategy
1001:
1002: --Begin Bug#5126770 schekuri 04-Apr-2006
1003: --Added IF condition to the following block of code to avoid junk Cancellation of Strategies
1006: elsif vStrategyId IS NOT NULL AND vStrategyStatus1 = 'CANCELLED' THEN
1007: --Start adding by gnramasa for bug 8630852 13-July-09
1008: if p_strategy_mode = 'FINAL' then
1009: BEGIN
1010: write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy id = ' || vStrategyId );
1011: IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1012: strategy_id => vStrategyId,
1013: status => 'CANCELLED' ) ;
1014: EXCEPTION
1013: status => 'CANCELLED' ) ;
1014: EXCEPTION
1015: WHEN OTHERS THEN
1016: -- Added for bug 5877743 by gnramasa on 28-02-2007
1017: write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1018: UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED',
1019: last_update_date=sysdate --Added for bug#7594370 by PNAVEENK
1020: WHERE STRATEGY_ID = vStrategyId;
1021: END;
1030: WHEN OTHERS THEN
1031: --Begin bug#5126770 schekuri 04-Apr-2006
1032: --Moved the exception handler to the inner block
1033: NULL;
1034: /*write_log(FND_LOG.LEVEL_PROCEDURE, 'cancel strategy exception occurred = ' );
1035: UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;*/
1036: --End bug#5126770 schekuri 04-Apr-2006
1037: END;
1038: -- end bug 4944801 ctlee 01/18/2006
1039:
1040:
1041: --Start bug 6794510 gnramasa 7th feb 2008
1042: if (NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N') = 'Y') then
1043: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1044: IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment Profile value is: Y' );
1045: IEX_DEBUG_PUB.LogMessage( 'l_strategy_template_id: '|| l_strategy_template_id);
1046: IEX_DEBUG_PUB.LogMessage( 'l_StrategyTempID: '|| l_StrategyTempID);
1047: END IF;
1045: IEX_DEBUG_PUB.LogMessage( 'l_strategy_template_id: '|| l_strategy_template_id);
1046: IEX_DEBUG_PUB.LogMessage( 'l_StrategyTempID: '|| l_StrategyTempID);
1047: END IF;
1048: IF l_strategy_template_id = l_StrategyTempID THEN
1049: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1050: IEX_DEBUG_PUB.LogMessage( 'Strategy creation aborted. ' );
1051: IEX_DEBUG_PUB.LogMessage( 'Skip Default Strategy Assignment by Profile ');
1052: END IF;
1053: goto nextRec; -- continue to the loop for the next record, no need to change strategy
1135: group by pd.delinquency_id
1136: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1137:
1138: IF l_unpro_dels <=0 then
1139: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1140: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1141: END IF;
1142: l_strategy_rec.status_code := l_StratStatusOnhold;
1143: ELSE
1136: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1137:
1138: IF l_unpro_dels <=0 then
1139: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1140: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1141: END IF;
1142: l_strategy_rec.status_code := l_StratStatusOnhold;
1143: ELSE
1144: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1140: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1141: END IF;
1142: l_strategy_rec.status_code := l_StratStatusOnhold;
1143: ELSE
1144: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1145: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1146: END IF;
1147: l_strategy_rec.status_code := l_StratStatusOpen;
1148: END IF;
1141: END IF;
1142: l_strategy_rec.status_code := l_StratStatusOnhold;
1143: ELSE
1144: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1145: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for party id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1146: END IF;
1147: l_strategy_rec.status_code := l_StratStatusOpen;
1148: END IF;
1149: elsif l_strategy_rec.strategy_level = 20 then
1167: group by pd.delinquency_id
1168: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1169:
1170: IF l_unpro_dels <=0 then
1171: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1172: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1173: END IF;
1174: l_strategy_rec.status_code := l_StratStatusOnhold;
1175: ELSE
1168: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1169:
1170: IF l_unpro_dels <=0 then
1171: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1172: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1173: END IF;
1174: l_strategy_rec.status_code := l_StratStatusOnhold;
1175: ELSE
1176: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1172: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1173: END IF;
1174: l_strategy_rec.status_code := l_StratStatusOnhold;
1175: ELSE
1176: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1177: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1178: END IF;
1179: l_strategy_rec.status_code := l_StratStatusOpen;
1180: END IF;
1173: END IF;
1174: l_strategy_rec.status_code := l_StratStatusOnhold;
1175: ELSE
1176: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1177: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for account id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1178: END IF;
1179: l_strategy_rec.status_code := l_StratStatusOpen;
1180: END IF;
1181: elsif l_strategy_rec.strategy_level = 30 then
1199: group by pd.delinquency_id
1200: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1201:
1202: IF l_unpro_dels <=0 then
1203: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1204: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1205: END IF;
1206: l_strategy_rec.status_code := l_StratStatusOnhold;
1207: ELSE
1200: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1201:
1202: IF l_unpro_dels <=0 then
1203: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1204: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1205: END IF;
1206: l_strategy_rec.status_code := l_StratStatusOnhold;
1207: ELSE
1208: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1204: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1205: END IF;
1206: l_strategy_rec.status_code := l_StratStatusOnhold;
1207: ELSE
1208: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1209: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1210: END IF;
1211: l_strategy_rec.status_code := l_StratStatusOpen;
1212: END IF;
1205: END IF;
1206: l_strategy_rec.status_code := l_StratStatusOnhold;
1207: ELSE
1208: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1209: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for site use id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1210: END IF;
1211: l_strategy_rec.status_code := l_StratStatusOpen;
1212: END IF;
1213: else
1231: group by pd.delinquency_id
1232: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1233:
1234: IF l_unpro_dels <=0 then
1235: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1236: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1237: END IF;
1238: l_strategy_rec.status_code := l_StratStatusOnhold;
1239: ELSE
1232: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
1233:
1234: IF l_unpro_dels <=0 then
1235: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1236: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1237: END IF;
1238: l_strategy_rec.status_code := l_StratStatusOnhold;
1239: ELSE
1240: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1236: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOnhold);
1237: END IF;
1238: l_strategy_rec.status_code := l_StratStatusOnhold;
1239: ELSE
1240: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1241: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1242: END IF;
1243: l_strategy_rec.status_code := l_StratStatusOpen;
1244: END IF;
1237: END IF;
1238: l_strategy_rec.status_code := l_StratStatusOnhold;
1239: ELSE
1240: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1241: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Create strategy for delinquency id : = ' || l_strategy_rec.object_id || ' with status as :' || l_StratStatusOpen);
1242: END IF;
1243: l_strategy_rec.status_code := l_StratStatusOpen;
1244: END IF;
1245: end if;
1255:
1256: --Start adding by gnramasa for bug 8630852 13-July-09
1257: if p_strategy_mode = 'FINAL' then
1258:
1259: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1260: write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Create strategy for Delinquency ID '
1261: || l_strategy_rec.delinquency_id);
1262: end if;
1263: Begin
1256: --Start adding by gnramasa for bug 8630852 13-July-09
1257: if p_strategy_mode = 'FINAL' then
1258:
1259: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1260: write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Create strategy for Delinquency ID '
1261: || l_strategy_rec.delinquency_id);
1262: end if;
1263: Begin
1264: -- bug 4141678 begin - ctlee
1278: );
1279:
1280: l_strategy_rec.strategy_id := l_strategy_id;
1281: fnd_file.put_line(FND_FILE.LOG, 'Strategy Created . Id = ' || l_strategy_id);
1282: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1283: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1284: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy created. id = ' || l_strategy_id);
1285: end if;
1286: -- bug 4141678 begin - ctlee
1279:
1280: l_strategy_rec.strategy_id := l_strategy_id;
1281: fnd_file.put_line(FND_FILE.LOG, 'Strategy Created . Id = ' || l_strategy_id);
1282: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1283: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1284: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy created. id = ' || l_strategy_id);
1285: end if;
1286: -- bug 4141678 begin - ctlee
1287: -- EXCEPTION
1280: l_strategy_rec.strategy_id := l_strategy_id;
1281: fnd_file.put_line(FND_FILE.LOG, 'Strategy Created . Id = ' || l_strategy_id);
1282: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1283: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1284: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy created. id = ' || l_strategy_id);
1285: end if;
1286: -- bug 4141678 begin - ctlee
1287: -- EXCEPTION
1288: -- WHEN OTHERS THEN
1285: end if;
1286: -- bug 4141678 begin - ctlee
1287: -- EXCEPTION
1288: -- WHEN OTHERS THEN
1289: -- write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy create Return status = ' ||
1290: -- l_return_status || ' ' || sqlerrm );
1291: -- retcode := '2';
1292: -- return;
1293: -- END;
1293: -- END;
1294: -- bug 4141678 end - ctlee
1295:
1296:
1297: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1298: write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Workflow creation for Delinquency ID '
1299: || l_strategy_rec.strategy_id);
1300: end if;
1301:
1294: -- bug 4141678 end - ctlee
1295:
1296:
1297: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1298: write_log(FND_LOG.LEVEL_STATEMENT, 'Calling Workflow creation for Delinquency ID '
1299: || l_strategy_rec.strategy_id);
1300: end if;
1301:
1302: -- BEGIN
1313: x_msg_count=>l_msg_count,
1314: x_msg_data=>l_msg_data
1315: );
1316:
1317: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1318: write_log(FND_LOG.LEVEL_STATEMENT, 'Workflow Launch Return status = '
1319: || l_return_status) ;
1320: end if;
1321: EXCEPTION
1314: x_msg_data=>l_msg_data
1315: );
1316:
1317: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1318: write_log(FND_LOG.LEVEL_STATEMENT, 'Workflow Launch Return status = '
1319: || l_return_status) ;
1320: end if;
1321: EXCEPTION
1322: WHEN OTHERS THEN
1361: -- bug 4141678 end - ctlee
1362: end;
1363: end if; /* if template id is -1 then donot generate streategy */
1364: end if; /* check status */
1365: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1366: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor ends' );
1367: end if;
1368: ELSE -- fetch failed, so exit loop
1369: EXIT;
1362: end;
1363: end if; /* if template id is -1 then donot generate streategy */
1364: end if; /* check status */
1365: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1366: write_log(FND_LOG.LEVEL_STATEMENT, 'Delinquency cursor ends' );
1367: end if;
1368: ELSE -- fetch failed, so exit loop
1369: EXIT;
1370: end if; /* found cursor */
1376: if (l_save_count > 0) then
1377: l_commit_count := l_commit_count + 1;
1378: commit work;
1379: end if;
1380: write_log(FND_LOG.LEVEL_UNEXPECTED, 'commit count = ' || l_commit_count);
1381: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Total number of strategies created in running Operating Unit = '|| l_str_count );
1382: -- bug 4141678 end - ctlee
1383:
1384: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
1377: l_commit_count := l_commit_count + 1;
1378: commit work;
1379: end if;
1380: write_log(FND_LOG.LEVEL_UNEXPECTED, 'commit count = ' || l_commit_count);
1381: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Total number of strategies created in running Operating Unit = '|| l_str_count );
1382: -- bug 4141678 end - ctlee
1383:
1384: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
1385: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation completed ' );
1380: write_log(FND_LOG.LEVEL_UNEXPECTED, 'commit count = ' || l_commit_count);
1381: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Total number of strategies created in running Operating Unit = '|| l_str_count );
1382: -- bug 4141678 end - ctlee
1383:
1384: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
1385: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation completed ' );
1386:
1387: close c_open_delinquencies;
1388:
1381: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Total number of strategies created in running Operating Unit = '|| l_str_count );
1382: -- bug 4141678 end - ctlee
1383:
1384: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Delinquency cursor EXIT ');
1385: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy creation completed ' );
1386:
1387: close c_open_delinquencies;
1388:
1389: EXCEPTION
1446: l_return_status := FND_API.G_RET_STS_SUCCESS;
1447: -- Standard Start of API savepoint
1448: SAVEPOINT CLOSE_STRY_CONT;
1449:
1450: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1451:
1452: FOR f_strategy_rec in C_Open_strategies loop
1453:
1454: /* Create the strategy record */
1456: l_stry_cnt_rec.delinquency_id := f_strategy_rec.delinquency_id;
1457: l_stry_cnt_rec.object_id := f_strategy_rec.object_id;
1458: l_stry_cnt_rec.object_type := f_strategy_rec.object_type;
1459:
1460: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_stry_cnt_rec.strategy_id
1461: || ' Delinquency ID ' || l_stry_cnt_rec.delinquency_id
1462: || ' Object ID ' || l_stry_cnt_rec.object_id
1463: || ' Object Type ' || l_stry_cnt_rec.object_type
1464: || ' Strategy Template ID ' || l_strategy_template_id );
1462: || ' Object ID ' || l_stry_cnt_rec.object_id
1463: || ' Object Type ' || l_stry_cnt_rec.object_type
1464: || ' Strategy Template ID ' || l_strategy_template_id );
1465:
1466: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1467:
1468: l_itemtype := 'IEXSTRY';
1469: l_itemkey := to_char(l_stry_cnt_rec.strategy_id);
1470:
1474: strategy_id => l_itemkey,
1475: status => l_StratStatusClosed
1476: );
1477:
1478: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_stry_cnt_rec.strategy_id);
1479:
1480: EXCEPTION
1481: WHEN OTHERS THEN
1482: fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Raised Exception = ' ||
1482: fnd_file.put_line(FND_FILE.LOG, 'Strategy Closed Raised Exception = ' ||
1483: l_stry_cnt_rec.strategy_id || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1484: END;
1485:
1486: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1487:
1488: END loop;
1489: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1490:
1485:
1486: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1487:
1488: END loop;
1489: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1490:
1491: EXCEPTION
1492: WHEN OTHERS THEN
1493: fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1540: -- Initialize API return status to SUCCESS
1541: l_return_status := FND_API.G_RET_STS_SUCCESS;
1542:
1543: if (NVL(FND_PROFILE.VALUE('IEX_STRATEGY_DISABLED'), 'N') = 'Y') then
1544: write_log(FND_LOG.LEVEL_STATEMENT,' Profile Name IEX: Strategy Disabled (IEX_STRATEGY_DISABLED) set to YES ');
1545: return;
1546: end if;
1547: -- Standard Start of API savepoint
1548: SAVEPOINT CLOSE_STRY_CONT;
1547: -- Standard Start of API savepoint
1548: SAVEPOINT CLOSE_STRY_CONT;
1549:
1550:
1551: --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN -- commented by gnramasa on 29/08/2006 for bug # 5487449
1552: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1553: --end if;
1554:
1555: --Start adding for bug 8756947 gnramasa 3rd Aug 09
1548: SAVEPOINT CLOSE_STRY_CONT;
1549:
1550:
1551: --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN -- commented by gnramasa on 29/08/2006 for bug # 5487449
1552: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor started ');
1553: --end if;
1554:
1555: --Start adding for bug 8756947 gnramasa 3rd Aug 09
1556: IF l_DefaultStrategyLevel = 10 THEN
1657: end if;
1658: END IF;
1659: --End adding for bug 8756947 gnramasa 3rd Aug 09
1660:
1661: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1662: write_log(FND_LOG.LEVEL_PROCEDURE, 'Close Strategies vPLSQL :' || vPLSQL);
1663: END IF;
1664: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Close Strategies vPLSQL :' || vPLSQL);
1665:
1658: END IF;
1659: --End adding for bug 8756947 gnramasa 3rd Aug 09
1660:
1661: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1662: write_log(FND_LOG.LEVEL_PROCEDURE, 'Close Strategies vPLSQL :' || vPLSQL);
1663: END IF;
1664: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Close Strategies vPLSQL :' || vPLSQL);
1665:
1666: OPEN c_open_strategies FOR vPLSQL;
1670: FETCH c_open_strategies INTO l_strategy_id, l_Strategy_template_id, vStrategyStatus ;
1671: if c_open_strategies%FOUND then
1672:
1673:
1674: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1675: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_strategy_id
1676: || ' Strategy Status ' || vStrategyStatus
1677: || ' Strategy Template ID ' || l_strategy_template_id );
1678:
1671: if c_open_strategies%FOUND then
1672:
1673:
1674: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1675: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_strategy_id
1676: || ' Strategy Status ' || vStrategyStatus
1677: || ' Strategy Template ID ' || l_strategy_template_id );
1678:
1679: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1675: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy ID ' || l_strategy_id
1676: || ' Strategy Status ' || vStrategyStatus
1677: || ' Strategy Template ID ' || l_strategy_template_id );
1678:
1679: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Status ' || vStrategyStatus );
1680: end if;
1681:
1682: if p_strategy_mode = 'FINAL' then
1683: l_itemtype := 'IEXSTRY';
1689: strategy_id => l_itemkey,
1690: status => l_StratStatusClosed
1691: );
1692:
1693: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_strategy_id);
1695: end if;
1696:
1697: EXCEPTION
1690: status => l_StratStatusClosed
1691: );
1692:
1693: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy Closed. id = ' || l_strategy_id);
1695: end if;
1696:
1697: EXCEPTION
1698: WHEN OTHERS THEN
1706: IF p_show_output = 'Y' THEN
1707: gen_xml_body_strategy (p_strategy_id => l_strategy_id,
1708: p_strategy_status => 'CLOSE');
1709: END IF;
1710: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1711: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1712: end if;
1713: ELSE -- fetch failed, so exit loop
1714: EXIT;
1707: gen_xml_body_strategy (p_strategy_id => l_strategy_id,
1708: p_strategy_status => 'CLOSE');
1709: END IF;
1710: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1711: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor ends' );
1712: end if;
1713: ELSE -- fetch failed, so exit loop
1714: EXIT;
1715: end if;
1713: ELSE -- fetch failed, so exit loop
1714: EXIT;
1715: end if;
1716: END loop;
1717: write_log(FND_LOG.LEVEL_STATEMENT, 'Close Strategy cursor EXIT ');
1718: close c_open_strategies;
1719: EXCEPTION
1720: WHEN OTHERS THEN
1721: fnd_file.put_line(FND_FILE.LOG, 'Close Strategy raised exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1804: into l_DefaultStrategyLevel,l_StrategyLevelName
1805: from iex_app_preferences_vl
1806: where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y'; */
1807:
1808: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1809: write_log(FND_LOG.LEVEL_STATEMENT, ' Strategy level is: ' || l_DefaultStrategyLevel);
1810: END IF;
1811:
1812: if l_DefaultStrategyLevel = 10 then
1805: from iex_app_preferences_vl
1806: where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y'; */
1807:
1808: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1809: write_log(FND_LOG.LEVEL_STATEMENT, ' Strategy level is: ' || l_DefaultStrategyLevel);
1810: END IF;
1811:
1812: if l_DefaultStrategyLevel = 10 then
1813: vPLSQL := 'select s.strategy_id strategy_id, '||
1832: vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
1833: end if;
1834: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id';
1835:
1836: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1837: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1838: END IF;
1839: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1840:
1833: end if;
1834: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id';
1835:
1836: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1837: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1838: END IF;
1839: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1840:
1841: open c_onhold_strategies for vPLSQL;
1842: loop
1843: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_party_id;
1844:
1845: if c_onhold_strategies%FOUND then
1846: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1847: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for party id : = ' || l_party_id);
1848: END IF;
1849:
1850: if p_strategy_mode = 'FINAL' then
1843: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_party_id;
1844:
1845: if c_onhold_strategies%FOUND then
1846: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1847: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for party id : = ' || l_party_id);
1848: END IF;
1849:
1850: if p_strategy_mode = 'FINAL' then
1851: iex_strategy_pub.set_strategy(
1859: p_DelinquencyID => null,
1860: p_ObjectType => 'PARTY',
1861: p_ObjectID => l_party_id,
1862: p_Status => 'OPEN');
1863: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1864: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1865: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1866: END IF;
1867: end if; --if p_strategy_mode = 'FINAL' then
1860: p_ObjectType => 'PARTY',
1861: p_ObjectID => l_party_id,
1862: p_Status => 'OPEN');
1863: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1864: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1865: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1866: END IF;
1867: end if; --if p_strategy_mode = 'FINAL' then
1868:
1861: p_ObjectID => l_party_id,
1862: p_Status => 'OPEN');
1863: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1864: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1865: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1866: END IF;
1867: end if; --if p_strategy_mode = 'FINAL' then
1868:
1869: --Call gen_xml_body_strategy to insert this record to xml body
1875: EXIT;
1876: end if;
1877: end loop;
1878: close c_onhold_strategies;
1879: write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
1880:
1881: elsif l_DefaultStrategyLevel = 20 then
1882: vPLSQL := 'select s.strategy_id strategy_id, '||
1883: ' s.strategy_template_id strategy_template_id, '||
1900: vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || ' = s.cust_Account_id) ';
1901: end if;
1902: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id';
1903:
1904: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1905: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1906: END IF;
1907: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1908:
1901: end if;
1902: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id';
1903:
1904: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1905: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1906: END IF;
1907: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1908:
1909: open c_onhold_strategies for vPLSQL;
1910: loop
1911: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_account_id;
1912: if c_onhold_strategies%FOUND then
1913:
1914: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1915: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for account id: = ' || l_cust_account_id);
1916: END IF;
1917:
1918: if p_strategy_mode = 'FINAL' then
1911: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_account_id;
1912: if c_onhold_strategies%FOUND then
1913:
1914: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1915: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for account id: = ' || l_cust_account_id);
1916: END IF;
1917:
1918: if p_strategy_mode = 'FINAL' then
1919: iex_strategy_pub.set_strategy(
1927: p_DelinquencyID => null,
1928: p_ObjectType => 'ACCOUNT',
1929: p_ObjectID => l_cust_account_id,
1930: p_Status => 'OPEN');
1931: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1932: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1933: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1934: END IF;
1935: end if; --if p_strategy_mode = 'FINAL' then
1928: p_ObjectType => 'ACCOUNT',
1929: p_ObjectID => l_cust_account_id,
1930: p_Status => 'OPEN');
1931: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1932: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1933: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1934: END IF;
1935: end if; --if p_strategy_mode = 'FINAL' then
1936:
1929: p_ObjectID => l_cust_account_id,
1930: p_Status => 'OPEN');
1931: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1932: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
1933: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
1934: END IF;
1935: end if; --if p_strategy_mode = 'FINAL' then
1936:
1937: --Call gen_xml_body_strategy to insert this record to xml body
1944: EXIT;
1945: end if;
1946: end loop;
1947: close c_onhold_strategies;
1948: write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
1949:
1950: elsif l_DefaultStrategyLevel = 30 then
1951: vPLSQL := 'select s.strategy_id strategy_id, '||
1952: ' s.strategy_template_id strategy_template_id, '||
1970: end if;
1971: --End adding for bug 8756947 gnramasa 3rd Aug 09
1972: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id';
1973:
1974: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1975: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1976: END IF;
1977: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1978:
1971: --End adding for bug 8756947 gnramasa 3rd Aug 09
1972: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id';
1973:
1974: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1975: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
1976: END IF;
1977: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
1978:
1979: open c_onhold_strategies for vPLSQL;
1979: open c_onhold_strategies for vPLSQL;
1980: loop
1981: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_site_use_id;
1982: if c_onhold_strategies%FOUND then
1983: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1984: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for customer site use id : = ' || l_cust_site_use_id);
1985: END IF;
1986:
1987: if p_strategy_mode = 'FINAL' then
1980: loop
1981: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_cust_site_use_id;
1982: if c_onhold_strategies%FOUND then
1983: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1984: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for customer site use id : = ' || l_cust_site_use_id);
1985: END IF;
1986:
1987: if p_strategy_mode = 'FINAL' then
1988: iex_strategy_pub.set_strategy(
1996: p_DelinquencyID => null,
1997: p_ObjectType => 'BILL_TO',
1998: p_ObjectID => l_cust_site_use_id,
1999: p_Status => 'OPEN');
2000: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2001: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2002: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2003: END IF;
2004: end if; --if p_strategy_mode = 'FINAL' then
1997: p_ObjectType => 'BILL_TO',
1998: p_ObjectID => l_cust_site_use_id,
1999: p_Status => 'OPEN');
2000: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2001: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2002: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2003: END IF;
2004: end if; --if p_strategy_mode = 'FINAL' then
2005:
1998: p_ObjectID => l_cust_site_use_id,
1999: p_Status => 'OPEN');
2000: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2001: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2002: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2003: END IF;
2004: end if; --if p_strategy_mode = 'FINAL' then
2005:
2006: --Call gen_xml_body_strategy to insert this record to xml body
2012: EXIT;
2013: end if;
2014: end loop;
2015: close c_onhold_strategies;
2016: write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
2017: else
2018: -- added for bug 11693277 PNAVEENK
2019: vPLSQL := 'select s.strategy_id strategy_id, '||
2020: ' s.strategy_template_id strategy_template_id, '||
2038: end if;
2039: --End adding for bug 8756947 gnramasa 3rd Aug 09
2040: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.delinquency_id';
2041:
2042: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2043: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
2044: END IF;
2045: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
2046:
2039: --End adding for bug 8756947 gnramasa 3rd Aug 09
2040: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.delinquency_id';
2041:
2042: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2043: write_log(FND_LOG.LEVEL_PROCEDURE, 'ON-HOLD vPLSQL :' || vPLSQL);
2044: END IF;
2045: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ON-HOLD vPLSQL :' || vPLSQL);
2046:
2047: open c_onhold_strategies for vPLSQL;
2047: open c_onhold_strategies for vPLSQL;
2048: loop
2049: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_delinquency_id;
2050: if c_onhold_strategies%FOUND then
2051: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2052: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for delinquency id : = ' || l_delinquency_id);
2053: END IF;
2054:
2055: if p_strategy_mode = 'FINAL' then
2048: loop
2049: fetch c_onhold_strategies into l_strategy_id, l_strategy_template_id, l_status_code, l_delinquency_id;
2050: if c_onhold_strategies%FOUND then
2051: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2052: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for delinquency id : = ' || l_delinquency_id);
2053: END IF;
2054:
2055: if p_strategy_mode = 'FINAL' then
2056: iex_strategy_pub.set_strategy(
2064: p_DelinquencyID => null,
2065: p_ObjectType => 'DELINQUENT',
2066: p_ObjectID => l_delinquency_id,
2067: p_Status => 'OPEN');
2068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2069: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2070: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2071: END IF;
2072: end if; --if p_strategy_mode = 'FINAL' then
2065: p_ObjectType => 'DELINQUENT',
2066: p_ObjectID => l_delinquency_id,
2067: p_Status => 'OPEN');
2068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2069: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2070: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2071: END IF;
2072: end if; --if p_strategy_mode = 'FINAL' then
2073:
2066: p_ObjectID => l_delinquency_id,
2067: p_Status => 'OPEN');
2068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2069: write_log(FND_LOG.LEVEL_PROCEDURE, 'Return status = ' || l_return_status);
2070: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
2071: END IF;
2072: end if; --if p_strategy_mode = 'FINAL' then
2073:
2074: --Call gen_xml_body_strategy to insert this record to xml body
2080: EXIT;
2081: end if;
2082: end loop;
2083: close c_onhold_strategies;
2084: write_log(FND_LOG.LEVEL_STATEMENT, 'ONHOLD Strategy cursor EXIT ');
2085:
2086: -- end for bug 11693277
2087: end if;
2088: EXCEPTION
2086: -- end for bug 11693277
2087: end if;
2088: EXCEPTION
2089: when others then
2090: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2091: write_log(FND_LOG.LEVEL_STATEMENT, ' In PROCESS_ONHOLD_STRATEGIES when others exception');
2092: END IF;
2093: END PROCESS_ONHOLD_STRATEGIES;
2094: --End Bug#7248296 28-07-2008 barathsr
2087: end if;
2088: EXCEPTION
2089: when others then
2090: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2091: write_log(FND_LOG.LEVEL_STATEMENT, ' In PROCESS_ONHOLD_STRATEGIES when others exception');
2092: END IF;
2093: END PROCESS_ONHOLD_STRATEGIES;
2094: --End Bug#7248296 28-07-2008 barathsr
2095: --End by gnramasa for bug 8630852 13-July-09
2143: vstr4 := ' where CUST_ACCOUNT_id = :AcctId ';
2144: vstr5 := ' where party_id = :PartyId ';
2145: vstr6 := ' where customer_site_use_id = :CustomerSiteUseId ';
2146:
2147: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2148: write_log(FND_LOG.LEVEL_STATEMENT, 'GetStrategyTempID: Object_Type = '
2149: || p_stry_cnt_rec.object_type || ' Delinquency ID = ' || p_stry_cnt_rec.delinquency_id
2150: || ' Strategy Level ' || l_DefaultStrategyLevel || ' Score ' || p_stry_cnt_rec.score_value
2151: || ' Party ID ' || p_stry_cnt_rec.party_cust_id || ' Account ID ' || p_stry_cnt_rec.cust_account_id
2144: vstr5 := ' where party_id = :PartyId ';
2145: vstr6 := ' where customer_site_use_id = :CustomerSiteUseId ';
2146:
2147: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2148: write_log(FND_LOG.LEVEL_STATEMENT, 'GetStrategyTempID: Object_Type = '
2149: || p_stry_cnt_rec.object_type || ' Delinquency ID = ' || p_stry_cnt_rec.delinquency_id
2150: || ' Strategy Level ' || l_DefaultStrategyLevel || ' Score ' || p_stry_cnt_rec.score_value
2151: || ' Party ID ' || p_stry_cnt_rec.party_cust_id || ' Account ID ' || p_stry_cnt_rec.cust_account_id
2152: || ' CUSTOMER_SITE_USE ID ' || p_stry_cnt_rec.customer_site_use_id );
2240: /* Get the Strategy Template for requested Category Type */
2241: LOOP
2242: FETCH C_StrategyTemp INTO c_rec_Strategy_temp_id, c_Rec_Strategy_Rank, c_Rec_ENTITY_NAME, c_rec_active_flag ;
2243:
2244: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2245: write_log(FND_LOG.LEVEL_STATEMENT,
2246: ' Get Strategy Template: Inside Cursor. Entity Name '
2247: || c_Rec_Entity_Name
2248: || ' Temp ' || c_rec_Strategy_temp_id
2241: LOOP
2242: FETCH C_StrategyTemp INTO c_rec_Strategy_temp_id, c_Rec_Strategy_Rank, c_Rec_ENTITY_NAME, c_rec_active_flag ;
2243:
2244: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2245: write_log(FND_LOG.LEVEL_STATEMENT,
2246: ' Get Strategy Template: Inside Cursor. Entity Name '
2247: || c_Rec_Entity_Name
2248: || ' Temp ' || c_rec_Strategy_temp_id
2249: || ' c_rec_active_flag ' || c_rec_active_flag
2297: ' and rownum < 2 ';
2298: */
2299: end if;
2300:
2301: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2302: write_log(FND_LOG.LEVEL_STATEMENT, ' Dynamic SQL in GetStrategyTemplate '
2303: || c_DynSql );
2304: end if;
2305:
2298: */
2299: end if;
2300:
2301: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2302: write_log(FND_LOG.LEVEL_STATEMENT, ' Dynamic SQL in GetStrategyTemplate '
2303: || c_DynSql );
2304: end if;
2305:
2306: --Execute Immediate c_DynSql into v_Exists;
2307:
2308: EXCEPTION
2309: When no_data_found then
2310: -- fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When No Data Found: ' || c_DynSql );
2311: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When No Data Found: ' || c_DynSql ); -- changed for bug 9039794
2312: v_SkipTemp := 'T';
2313: When Others then
2314: -- fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When Others: ' || c_DynSql );
2315: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When Others: ' || c_DynSql ); -- changed for bug 9039794
2311: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When No Data Found: ' || c_DynSql ); -- changed for bug 9039794
2312: v_SkipTemp := 'T';
2313: When Others then
2314: -- fnd_file.put_line(FND_FILE.LOG, ' Get Strategy Template: When Others: ' || c_DynSql );
2315: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: When Others: ' || c_DynSql ); -- changed for bug 9039794
2316: v_SkipTemp := 'T';
2317: END;
2318: end if;
2319:
2316: v_SkipTemp := 'T';
2317: END;
2318: end if;
2319:
2320: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: ' || c_Rec_Strategy_Temp_id ||
2322: ' Skip Flag ' || v_SkipTemp );
2323: end if;
2324:
2317: END;
2318: end if;
2319:
2320: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321: write_log(FND_LOG.LEVEL_STATEMENT, ' Get Strategy Template: ' || c_Rec_Strategy_Temp_id ||
2322: ' Skip Flag ' || v_SkipTemp );
2323: end if;
2324:
2325: if v_SkipTemp <> 'T' then
2324:
2325: if v_SkipTemp <> 'T' then
2326:
2327: if p_stry_cnt_rec.score_value >= C_Rec_Strategy_Rank then
2328: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2329: write_log(FND_LOG.LEVEL_STATEMENT, ' Found Template: ' || c_Rec_Strategy_Temp_id );
2330: end if;
2331: x_strategy_template_id := c_rec_Strategy_temp_id;
2332: return;
2325: if v_SkipTemp <> 'T' then
2326:
2327: if p_stry_cnt_rec.score_value >= C_Rec_Strategy_Rank then
2328: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2329: write_log(FND_LOG.LEVEL_STATEMENT, ' Found Template: ' || c_Rec_Strategy_Temp_id );
2330: end if;
2331: x_strategy_template_id := c_rec_Strategy_temp_id;
2332: return;
2333: end if;
2622:
2623: select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2624: into l_DefaultStrategyLevel from dual;
2625:
2626: write_log(FND_LOG.LEVEL_STATEMENT, ' Org_id ' || l_org_id || ' Itearation Level ' || l_DefaultStrategyLevel);
2627:
2628: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2629: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2630: writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2673:
2674: select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2675: into l_DefaultStrategyLevel from dual;
2676:
2677: write_log(FND_LOG.LEVEL_STATEMENT, ' Org_id ' || l_org_id || ' Itearation Level ' || l_DefaultStrategyLevel);
2678:
2679: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2680: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2681: writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2720: and org_id is null;
2721:
2722: l_system_strategy_level := l_StrategyLevelName;
2723:
2724: write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2725: write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2726:
2727: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2728: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2721:
2722: l_system_strategy_level := l_StrategyLevelName;
2723:
2724: write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2725: write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2726:
2727: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2728: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2729: writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2790: open c_system_strategy_level;
2791: fetch c_system_strategy_level into l_system_strategy_level;
2792: close c_system_strategy_level;
2793:
2794: write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level' || l_system_strategy_level);
2795: for l_str_levels in c_str_levels loop
2796:
2797: l_StrategyLevelName := l_str_levels.lookup_code;
2798:
2798:
2799: select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2800: into l_DefaultStrategyLevel from dual;
2801:
2802: write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2803: write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2804:
2805: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2806: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2799: select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
2800: into l_DefaultStrategyLevel from dual;
2801:
2802: write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2803: write_log(FND_LOG.LEVEL_STATEMENT, ' System Strategy Level ' || l_system_strategy_level);
2804:
2805: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2806: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2807: writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2843: and org_id is null;
2844:
2845: l_system_strategy_level := l_StrategyLevelName;
2846:
2847: write_log(FND_LOG.LEVEL_STATEMENT, ' Running Strategy Level ' || l_DefaultStrategyLevel);
2848:
2849: if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
2850: p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
2851: writelog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
2961: --Start adding for bug 8761053 gnramasa 18th Aug 09
2962: for l_str_id in c_str_ids(p_party_id, p_str_level) loop
2963:
2964:
2965: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2966: write_log(FND_LOG.LEVEL_STATEMENT, 'In procedure cancel_strategy');
2967: write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy ID ' || l_str_id.strategy_id );
2968: end if;
2969:
2962: for l_str_id in c_str_ids(p_party_id, p_str_level) loop
2963:
2964:
2965: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2966: write_log(FND_LOG.LEVEL_STATEMENT, 'In procedure cancel_strategy');
2967: write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy ID ' || l_str_id.strategy_id );
2968: end if;
2969:
2970: l_itemtype := 'IEXSTRY';
2963:
2964:
2965: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2966: write_log(FND_LOG.LEVEL_STATEMENT, 'In procedure cancel_strategy');
2967: write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy ID ' || l_str_id.strategy_id );
2968: end if;
2969:
2970: l_itemtype := 'IEXSTRY';
2971: l_itemkey := to_char(l_str_id.strategy_id);
2996: strategy_id => l_itemkey,
2997: status => l_StratStatusCancelled
2998: );
2999: fnd_file.put_line(FND_FILE.LOG, 'Strategy Cancelled. id = '|| l_str_id.strategy_id);
3000: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3001: write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy Cancelled. id = ' || l_str_id.strategy_id);
3002: end if;
3003:
3004: EXCEPTION
2997: status => l_StratStatusCancelled
2998: );
2999: fnd_file.put_line(FND_FILE.LOG, 'Strategy Cancelled. id = '|| l_str_id.strategy_id);
3000: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3001: write_log(FND_LOG.LEVEL_STATEMENT, 'Strategy Cancelled. id = ' || l_str_id.strategy_id);
3002: end if;
3003:
3004: EXCEPTION
3005: WHEN OTHERS THEN
3011: end if;
3012:
3013: end loop;
3014:
3015: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3016: write_log(FND_LOG.LEVEL_STATEMENT, 'Cancelled strategies for Party ID ' || p_party_id );
3017: write_log(FND_LOG.LEVEL_STATEMENT, 'End procedure cancel_strategy');
3018: end if;
3019:
3012:
3013: end loop;
3014:
3015: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3016: write_log(FND_LOG.LEVEL_STATEMENT, 'Cancelled strategies for Party ID ' || p_party_id );
3017: write_log(FND_LOG.LEVEL_STATEMENT, 'End procedure cancel_strategy');
3018: end if;
3019:
3020: END;
3013: end loop;
3014:
3015: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3016: write_log(FND_LOG.LEVEL_STATEMENT, 'Cancelled strategies for Party ID ' || p_party_id );
3017: write_log(FND_LOG.LEVEL_STATEMENT, 'End procedure cancel_strategy');
3018: end if;
3019:
3020: END;
3021: --End adding for bug 8761053 gnramasa 18th Aug 09
3030: l_dot := '.';
3031: l_module := 'strategy';
3032: if (mesg_level >= l_msgLevel) then
3033: fnd_file.put_line(FND_FILE.LOG, mesg);
3034: FND_LOG.STRING(mesg_level, l_schema || l_dot || l_module , mesg);
3035: end if;
3036: END;
3037: --Bug# 6870773 Naveen
3038: PROCEDURE update_strat_org
3185: end if;
3186:
3187: EXCEPTION
3188: WHEN OTHERS THEN
3189: write_log(FND_LOG.LEVEL_STATEMENT, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3190: fnd_file.put_line(FND_FILE.LOG, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
3191: rollback;
3192:
3193: END;
3214: /*-----------------------------------------------------------------------+
3215: | Cursor Declarations |
3216: +-----------------------------------------------------------------------*/
3217: BEGIN
3218: -- LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
3219: LOOP
3220: l_end := DBMS_LOB.INSTR (lob_loc => lob_loc, pattern => c_endline, offset => l_start, nth => 1 );
3221: --FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
3222: IF (NVL (l_end, 0) < 1) THEN
4192: l_StratObjectFilterType := 'IEXSTRAT';
4193:
4194: l_enabled := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
4195: if (l_enabled = 'N') then
4196: l_MsgLevel := FND_LOG.LEVEL_UNEXPECTED;
4197: else
4198: l_MsgLevel := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
4199: end if;
4200: -- l_DefaultTempID := GetDefaultStrategyTempID;
4194: l_enabled := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
4195: if (l_enabled = 'N') then
4196: l_MsgLevel := FND_LOG.LEVEL_UNEXPECTED;
4197: else
4198: l_MsgLevel := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
4199: end if;
4200: -- l_DefaultTempID := GetDefaultStrategyTempID;
4201: -- chaged for bug 9795455 pnaveenk
4202: l_DefaultTempID := NVL(TO_NUMBER(fnd_profile.value('IEX_STRATEGY_DEFAULT_TEMPLATE')),0);
4201: -- chaged for bug 9795455 pnaveenk
4202: l_DefaultTempID := NVL(TO_NUMBER(fnd_profile.value('IEX_STRATEGY_DEFAULT_TEMPLATE')),0);
4203:
4204: -- Start for bug # 5487449 on 28/08/2006 by gnramasa
4205: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template ID ' || l_DefaultTempID || ' Profile Name IEX: Strategy Default Template (IEX_STRATEGY_DEFAULT_TEMPLATE)');
4206: begin
4207: select STRATEGY_NAME ,ENABLED_FLAG
4208: into l_DefaultTempName, l_EnabledFlag
4209: from iex_strategy_templates_vl
4208: into l_DefaultTempName, l_EnabledFlag
4209: from iex_strategy_templates_vl
4210: where STRATEGY_TEMP_ID=l_DefaultTempID;
4211:
4212: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Default Template Name :' || l_DefaultTempName || ' , Enabled Flag :' || l_EnabledFlag );
4213: EXCEPTION
4214: WHEN OTHERS THEN
4215: l_DefaultTempName := 'N';
4216: fnd_file.put_line(FND_FILE.LOG, 'Default Template Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
4221: select SOURCE_NAME,USER_NAME
4222: into l_SourceName,l_UserName
4223: from jtf_rs_resource_extns
4224: where RESOURCE_ID=l_default_rs_id;
4225: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Resource Id :' || l_default_rs_id || ' Profile Name IEX: Strategy Assignment Default Resource (IEX_STRY_DEFAULT_RESOURCE) , Resource Name :' || l_SourceName || ' , User Name :' || l_UserName);
4226: EXCEPTION
4227: WHEN OTHERS THEN
4228: fnd_file.put_line(FND_FILE.LOG, 'Resource Name raised Exception ' || SQLCODE || ' ' || SQLERRM);
4229: END;
4229: END;
4230: -- Start for bug # 5877743 on 28/02/2007 by gnramasa
4231: begin
4232: --Begin Bug#7205287 31-Jul-2008 barathsr
4233: write_log(FND_LOG.LEVEL_UNEXPECTED, 'Work Item Assignment Collector level from Profile (IEX: Territory Access Level) :'-- (IEX: Collector Access Level) :'
4234: || NVL(fnd_profile.value('IEX_ACCESS_LEVEL'), 'PARTY'));
4235: --End Bug#7205287 31-Jul-2008 barathsr
4236: EXCEPTION
4237: WHEN OTHERS THEN
4243: select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
4244: into l_DefaultStrategyLevel,l_StrategyLevelName
4245: from iex_app_preferences_vl
4246: where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = l_Yes;
4247: write_log(FND_LOG.LEVEL_STATEMENT, 'Current Strategy Level ' || l_DefaultStrategyLevel || ' , ' || l_StrategyLevelName);
4248:
4249: -- End for bug # 5487449 on 28/08/2006 by gnramasa
4250: EXCEPTION
4251: WHEN OTHERS THEN
4257: select DEFINE_PARTY_RUNNING_LEVEL,DEFINE_OU_RUNNING_LEVEL
4258: into l_party_override,l_org_override
4259: from IEX_QUESTIONNAIRE_ITEMS;
4260:
4261: write_log(FND_LOG.LEVEL_STATEMENT, 'Party Level Strategy Override and Operating Unit Level Override values ' || l_party_override || ' , ' || l_org_override);
4262:
4263: EXCEPTION
4264: WHEN OTHERS THEN
4265: fnd_file.put_line(FND_FILE.LOG, 'Strategy Level Rised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);