[Home] [Help]
23: REM | table. |
24: REM | JAN-2002 sashaik Made changes w.r.t the following procs for 1740789:|
25: REM | Retrieve_Org_level_value, Is_Previous_Time_Period |
26: REM | Is_Current_Time_Period, Get_Time_From, Get_Time_To|
27: REM | 17-JUN-2002 rchandra added bis_utilities_pub.encode | --
28: REM | for encoding for bug 2418741 |
29: REM | NOV-15 sashaik Unsubscribe alerts 1898436
30: REM | DEC-12 sashaik 2684836
31: REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
117:
118: --FUNCTION Get_Display_Unit(p_unit_code IN VARCHAR2) RETURN VARCHAR2;
119:
120: FUNCTION getAdHocRole
121: ( p_Alert_recipients_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL )RETURN VARCHAR2;
122: FUNCTION Format_Message(p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL)
123: RETURN VARCHAR2;
124:
125: FUNCTION Generate_parameter_string
118: --FUNCTION Get_Display_Unit(p_unit_code IN VARCHAR2) RETURN VARCHAR2;
119:
120: FUNCTION getAdHocRole
121: ( p_Alert_recipients_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL )RETURN VARCHAR2;
122: FUNCTION Format_Message(p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL)
123: RETURN VARCHAR2;
124:
125: FUNCTION Generate_parameter_string
126: ( p_target_id IN VARCHAR2
138: Procedure Get_Performance_Measure_Msg
139: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
140: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
141: , p_comparison_result IN VARCHAR2
142: , x_message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
143: );
144:
145: Procedure Get_Message_Banner
146: ( p_Sent_Date IN VARCHAR2
145: Procedure Get_Message_Banner
146: ( p_Sent_Date IN VARCHAR2
147: , p_Item IN VARCHAR2
148: , p_to IN VARCHAR2
149: , x_Message_Banner_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
150: );
151:
152: Procedure Get_Message_Intro
153: ( p_Sent_Date IN VARCHAR2
150: );
151:
152: Procedure Get_Message_Intro
153: ( p_Sent_Date IN VARCHAR2
154: , x_Message_Intro_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
155: );
156:
157: Procedure Get_Message_Body
158: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
157: Procedure Get_Message_Body
158: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
159: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
160: , p_comparison_result IN VARCHAR2
161: , x_message_body_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
162: );
163:
164: Procedure Get_Alert_Information
165: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
163:
164: Procedure Get_Alert_Information
165: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
166: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
167: , x_Alert_Information_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
168: );
169:
170: Procedure Get_Related_Links
171: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
169:
170: Procedure Get_Related_Links
171: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
172: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
173: , x_Related_Links_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
174: );
175:
176: Procedure Get_Report_Attachement
177: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
175:
176: Procedure Get_Report_Attachement
177: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
178: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
179: , x_attachement_url_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
180: );
181:
182: Procedure Get_Alert_Recipients
183: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
182: Procedure Get_Alert_Recipients
183: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
184: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
185: , p_comparison_result IN VARCHAR2
186: , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
187: , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
188: , x_number_out_of_range OUT NOCOPY NUMBER
189: );
190:
183: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
184: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
185: , p_comparison_result IN VARCHAR2
186: , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
187: , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
188: , x_number_out_of_range OUT NOCOPY NUMBER
189: );
190:
191: Procedure Get_Alert_Message
190:
191: Procedure Get_Alert_Message
192: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
193: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
194: , x_Alert_Message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
195: );
196:
197: Procedure Set_Message
198: ( p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
194: , x_Alert_Message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
195: );
196:
197: Procedure Set_Message
198: ( p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
199: , x_return_status OUT NOCOPY VARCHAR2
200: );
201:
202: Function Get_Role
207:
208: PROCEDURE Get_Result_Owners
209: ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
210: , p_comparison_result IN VARCHAR2
211: , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
212: , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
213: , x_number_out_of_range OUT NOCOPY NUMBER
214: );
215:
208: PROCEDURE Get_Result_Owners
209: ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
210: , p_comparison_result IN VARCHAR2
211: , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
212: , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
213: , x_number_out_of_range OUT NOCOPY NUMBER
214: );
215:
216: Procedure Get_Workflow_Info
231: IS
232:
233: l_item_type VARCHAR2(32000);
234: l_process VARCHAR2(32000);
235: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
236: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
237: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
238:
239: l_message VARCHAR2(32000);
232:
233: l_item_type VARCHAR2(32000);
234: l_process VARCHAR2(32000);
235: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
236: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
237: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
238:
239: l_message VARCHAR2(32000);
240: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
236: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
237: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
238:
239: l_message VARCHAR2(32000);
240: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
241: l_schedule_date VARCHAR2(32000);
242: l_schedule_time VARCHAR2(32000);
243: l_schedule_unit VARCHAR2(32000);
244: l_schedule_freq VARCHAR2(32000);
252: l_return_status VARCHAR2(32000);
253:
254: BEGIN
255:
256: --BIS_UTILITIES_PUB.put_line(p_text =>'Starting corrective action workflow');
257: Get_Request_Info
258: ( p_measure_instance => p_measure_instance
259: , x_schedule_date => l_schedule_date
260: , x_schedule_time => l_schedule_time
293: );
294:
295: l_message := Format_Message(p_message_tbl => l_message_tbl);
296:
297: --BIS_UTILITIES_PUB.put_line(p_text =>'Corrective action message: '||l_message);
298:
299: FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
300: BIS_UTIL.Start_Workflow_Engine
301: ( p_exception_message => l_message
296:
297: --BIS_UTILITIES_PUB.put_line(p_text =>'Corrective action message: '||l_message);
298:
299: FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
300: BIS_UTIL.Start_Workflow_Engine
301: ( p_exception_message => l_message
302: , p_msg_subject => l_description
303: , p_exception_date => l_date
304: , p_item_type => l_item_type
306: , p_notify_resp_name => l_Alert_recipients_sh_nm_tbl(i) -- 2628529
307: , x_return_status => l_return_status
308: );
309:
310: BIS_UTILITIES_PUB.put_line(p_text =>'Started workflow Process '||i||'. item type: '||l_item_type
311: ||', notified: '||l_Alert_recipients_tbl(i) || ' Status ' || nvl(l_return_status, 'x') );
312:
313: commit;
314: END LOOP;
332: := BIS_CORRECTIVE_ACTION_PUB.G_BIS_GEN_WORKFLOW_ITEM_TYPE;
333: l_process VARCHAR2(32000)
334: := BIS_CORRECTIVE_ACTION_PUB.G_BIS_ALR_WORKFLOW_PROCESS;
335:
336: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
337: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
338:
339: l_document_type_value VARCHAR2(32000);
340: l_message varchar2(32000);
333: l_process VARCHAR2(32000)
334: := BIS_CORRECTIVE_ACTION_PUB.G_BIS_ALR_WORKFLOW_PROCESS;
335:
336: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
337: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
338:
339: l_document_type_value VARCHAR2(32000);
340: l_message varchar2(32000);
341: l_document_type varchar2(32000);
339: l_document_type_value VARCHAR2(32000);
340: l_message varchar2(32000);
341: l_document_type varchar2(32000);
342: l_return_status varchar2(32000);
343: l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
344: l_date VARCHAR2(32000);
345: l_role VARCHAR2(32000);
346: l_schedule_date VARCHAR2(32000);
347: l_schedule_time VARCHAR2(32000);
449:
450: BEGIN
451:
452:
453: BIS_UTILITIES_PUB.put_line(p_text =>' ........... START: Notification email ........... ');
454:
455: Get_Request_Info
456: ( p_measure_instance => p_measure_instance
457: , x_schedule_date => l_schedule_date
479: , x_number_out_of_range => l_number_out_of_range
480: );
481:
482: /*
483: BIS_UTILITIES_PUB.put_line(p_text => ' recip count = ' || l_Alert_recipients_tbl.count );
484: BIS_UTILITIES_PUB.put_line(p_text => ' Recipt count = ' || l_Alert_recipients_sh_nm_tbl.count );
485:
486: for i in 1..l_Alert_recipients_tbl.count loop
487: BIS_UTILITIES_PUB.put_line(p_text => ' alert recip i = ' || i || ' val = ' || l_Alert_recipients_tbl(i) );
480: );
481:
482: /*
483: BIS_UTILITIES_PUB.put_line(p_text => ' recip count = ' || l_Alert_recipients_tbl.count );
484: BIS_UTILITIES_PUB.put_line(p_text => ' Recipt count = ' || l_Alert_recipients_sh_nm_tbl.count );
485:
486: for i in 1..l_Alert_recipients_tbl.count loop
487: BIS_UTILITIES_PUB.put_line(p_text => ' alert recip i = ' || i || ' val = ' || l_Alert_recipients_tbl(i) );
488: end loop;
483: BIS_UTILITIES_PUB.put_line(p_text => ' recip count = ' || l_Alert_recipients_tbl.count );
484: BIS_UTILITIES_PUB.put_line(p_text => ' Recipt count = ' || l_Alert_recipients_sh_nm_tbl.count );
485:
486: for i in 1..l_Alert_recipients_tbl.count loop
487: BIS_UTILITIES_PUB.put_line(p_text => ' alert recip i = ' || i || ' val = ' || l_Alert_recipients_tbl(i) );
488: end loop;
489:
490: for i in 1..l_Alert_recipients_sh_nm_tbl.count loop
491: BIS_UTILITIES_PUB.put_line(p_text => ' Alert recipt i = ' || i || ' val = ' || l_Alert_recipients_sh_nm_tbl(i) );
487: BIS_UTILITIES_PUB.put_line(p_text => ' alert recip i = ' || i || ' val = ' || l_Alert_recipients_tbl(i) );
488: end loop;
489:
490: for i in 1..l_Alert_recipients_sh_nm_tbl.count loop
491: BIS_UTILITIES_PUB.put_line(p_text => ' Alert recipt i = ' || i || ' val = ' || l_Alert_recipients_sh_nm_tbl(i) );
492: end loop;
493: */
494:
495: l_measure_rec.measure_id := p_measure_instance.measure_id;
502: , x_return_status => l_return_status
503: , x_error_tbl => l_error_tbl
504: );
505:
506: --BIS_UTILITIES_PUB.put_line(p_text =>'Performance Measure: '||l_measure_rec.measure_short_name);
507: BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target
508: ( p_measure_instance => p_measure_instance
509: , p_dim_level_value_tbl => p_dim_level_value_tbl
510: , x_target_rec => l_target_rec
509: , p_dim_level_value_tbl => p_dim_level_value_tbl
510: , x_target_rec => l_target_rec
511: );
512:
513: --BIS_UTILITIES_PUB.put_line(p_text =>'Retrive_Target: '||l_target_rec.Target_Id);
514:
515: l_target_level_rec.target_level_id := l_target_rec.target_level_id;
516:
517: l_target_level_rec.measure_id := l_measure_rec.measure_id;
613: , p_dim_level_value_tbl => l_dim_level_value_tbl
614: , x_actual_rec => l_actual_rec
615: );
616:
617: BIS_UTILITIES_PUB.put_line(p_text =>'Retrieve_Actual: '||l_actual_rec.Actual);
618:
619:
620:
621: begin -- Begin addition 2164190 sashaik
637: l_link := l_actual_rec1.Report_URL;
638:
639: exception
640: when others then
641: BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure BISVIEWER.GET_NOTIFY_RPT_URL : '||sqlerrm);
642:
643: end; -- End addition 2164190 sashaik
644:
645: BIS_UTILITIES_PUB.put_line(p_text =>'Link: '||l_link);
641: BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure BISVIEWER.GET_NOTIFY_RPT_URL : '||sqlerrm);
642:
643: end; -- End addition 2164190 sashaik
644:
645: BIS_UTILITIES_PUB.put_line(p_text =>'Link: '||l_link);
646:
647:
648:
649: l_role := getAdHocRole(l_Alert_recipients_tbl);
668: -- , document => l_message
669: -- , document_type => l_document_type
670: -- );
671:
672: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters length: '||length(l_document_type_value));
673: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters: '||substr(l_document_type_value,0,200));
674: -- BIS_UTILITIES_PUB.put_line(p_text =>'generated message: '||substr(l_message,0,200));
675:
676: FOR i IN 1..l_Alert_recipients_tbl.COUNT LOOP
669: -- , document_type => l_document_type
670: -- );
671:
672: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters length: '||length(l_document_type_value));
673: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters: '||substr(l_document_type_value,0,200));
674: -- BIS_UTILITIES_PUB.put_line(p_text =>'generated message: '||substr(l_message,0,200));
675:
676: FOR i IN 1..l_Alert_recipients_tbl.COUNT LOOP
677:
670: -- );
671:
672: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters length: '||length(l_document_type_value));
673: -- BIS_UTILITIES_PUB.put_line(p_text =>'Passing parameters: '||substr(l_document_type_value,0,200));
674: -- BIS_UTILITIES_PUB.put_line(p_text =>'generated message: '||substr(l_message,0,200));
675:
676: FOR i IN 1..l_Alert_recipients_tbl.COUNT LOOP
677:
678: BIS_UTILITIES_PUB.put_line(p_text =>'Starting item type: '||l_item_type||', process: '||l_process);
674: -- BIS_UTILITIES_PUB.put_line(p_text =>'generated message: '||substr(l_message,0,200));
675:
676: FOR i IN 1..l_Alert_recipients_tbl.COUNT LOOP
677:
678: BIS_UTILITIES_PUB.put_line(p_text =>'Starting item type: '||l_item_type||', process: '||l_process);
679:
680: SELECT bis_excpt_wf_s.nextval
681: INTO l_wf_item_key
682: FROM dual;
680: SELECT bis_excpt_wf_s.nextval
681: INTO l_wf_item_key
682: FROM dual;
683:
684: BIS_UTILITIES_PUB.put_line(p_text =>'wf_item_key: '||l_wf_item_key);
685: --BIS_UTILITIES_PUB.put_line(p_text =>'document_type_value: '||l_document_type_value);
686:
687: -- create a new workflow process
688: --
681: INTO l_wf_item_key
682: FROM dual;
683:
684: BIS_UTILITIES_PUB.put_line(p_text =>'wf_item_key: '||l_wf_item_key);
685: --BIS_UTILITIES_PUB.put_line(p_text =>'document_type_value: '||l_document_type_value);
686:
687: -- create a new workflow process
688: --
689:
698:
699:
700: if l_time_level_short_name is not NULL then
701:
702: l_is_rolling_period := bis_utilities_pvt.Is_Rolling_Period_Level
703: ( p_level_short_name => l_time_level_short_name );
704:
705: IF ( l_is_rolling_period = 1 ) THEN
706:
711: , avalue => l_measure_rec.Measure_Name||' '||v_dim_value_level(l_sequence_no)
712: );
713:
714: l_rolling_period_end_date := sysdate;
715: l_rolling_period_start_date := bis_utilities_pvt.get_Roll_Period_Start_Date
716: ( p_level_short_name => l_time_level_short_name
717: , p_end_date => l_rolling_period_end_date
718: );
719:
716: ( p_level_short_name => l_time_level_short_name
717: , p_end_date => l_rolling_period_end_date
718: );
719:
720: l_start_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_START');
721: l_end_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_END');
722:
723: ELSE
724:
717: , p_end_date => l_rolling_period_end_date
718: );
719:
720: l_start_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_START');
721: l_end_label := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ROLLING_END');
722:
723: ELSE
724:
725: WF_ENGINE.SetItemAttrText
743:
744:
745:
746:
747: l_label := BIS_UTILITIES_PVT.getPrompt
748: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SUBJECT')
749: ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Subject'||l_label);
750:
751: WF_ENGINE.SetItemAttrText
745:
746:
747: l_label := BIS_UTILITIES_PVT.getPrompt
748: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SUBJECT')
749: ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Subject'||l_label);
750:
751: WF_ENGINE.SetItemAttrText
752: ( itemtype => l_item_type
753: , itemkey => l_wf_item_key
752: ( itemtype => l_item_type
753: , itemkey => l_wf_item_key
754: , aname => 'L_SUBJECT_LABEL'
755: , avalue => l_label
756: ); -- BIS_UTILITIES_PUB.put_line(p_text =>'subject'||l_label );
757:
758:
759: -- set the workflow attributes
760: --
757:
758:
759: -- set the workflow attributes
760: --
761: l_label := BIS_UTILITIES_PVT.getPrompt
762: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_DETAILS')
763: ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Details'||l_label);
764:
765: WF_ENGINE.SetItemAttrText
759: -- set the workflow attributes
760: --
761: l_label := BIS_UTILITIES_PVT.getPrompt
762: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_DETAILS')
763: ); -- BIS_UTILITIES_PUB.put_line(p_text =>'Details'||l_label);
764:
765: WF_ENGINE.SetItemAttrText
766: ( itemtype => l_item_type
767: , itemkey => l_wf_item_key
768: , aname => 'L_ALERT_DETAILS_LABEL'
769: , avalue => l_label
770: );
771:
772: l_label := BIS_UTILITIES_PVT.getPrompt
773: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_MSG')
774: );
775: -- BIS_UTILITIES_PUB.put_line(p_text =>'Alert Msg'||l_label);
776: WF_ENGINE.SetItemAttrText
771:
772: l_label := BIS_UTILITIES_PVT.getPrompt
773: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_MSG')
774: );
775: -- BIS_UTILITIES_PUB.put_line(p_text =>'Alert Msg'||l_label);
776: WF_ENGINE.SetItemAttrText
777: ( itemtype => l_item_type
778: , itemkey => l_wf_item_key
779: , aname => 'L_ALERT_MSG'
779: , aname => 'L_ALERT_MSG'
780: , avalue => l_label
781: );
782:
783: l_label := BIS_UTILITIES_PVT.getPrompt
784: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RUN_DATE')
785: );
786: -- BIS_UTILITIES_PUB.put_line(p_text =>'Run Date'||l_label);
787: WF_ENGINE.SetItemAttrText
782:
783: l_label := BIS_UTILITIES_PVT.getPrompt
784: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RUN_DATE')
785: );
786: -- BIS_UTILITIES_PUB.put_line(p_text =>'Run Date'||l_label);
787: WF_ENGINE.SetItemAttrText
788: ( itemtype => l_item_type
789: , itemkey => l_wf_item_key
790: , aname => 'L_RUN_DATE_LABEL'
814: l_alert_details1 := l_alert_details1 || v_dim_value_level(counter) || ' ' || l_start_label || ' : ' || l_rolling_period_start_date || l_NL || v_dim_value_level(counter) || ' ' || l_end_label || ' : ' || l_rolling_period_end_date || l_NL;
815: ELSE
816:
817: if (v_dim_value(counter) IS NOT NULL ) and(v_dim_value_level(counter) IS NOT NULL) then
818: -- BIS_UTILITIES_PUB.put_line(p_text =>'Level: '||v_dim_value_level(counter)||' '||v_dim_value(Counter));
819: -- rolling change 2 if time level and if rolling period.
820:
821: l_alert_details_label := l_alert_details_label||v_dim_value_level(counter)||'
';
822: l_alert_details := l_alert_details||v_dim_value(counter)||'
';
853: , avalue => l_alert_details1
854: );
855: l_alert_details1 := null;
856:
857: BIS_UTILITIES_PUB.put_line(p_text =>'Dimension: '||l_alert_details);
858:
859: l_label := BIS_UTILITIES_PVT.getPrompt
860: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_PERF_MEASURE')
861: );
855: l_alert_details1 := null;
856:
857: BIS_UTILITIES_PUB.put_line(p_text =>'Dimension: '||l_alert_details);
858:
859: l_label := BIS_UTILITIES_PVT.getPrompt
860: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_PERF_MEASURE')
861: );
862: BIS_UTILITIES_PUB.put_line(p_text =>'Perf Measure'||l_label);
863: wf_engine.SetItemAttrText
858:
859: l_label := BIS_UTILITIES_PVT.getPrompt
860: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_PERF_MEASURE')
861: );
862: BIS_UTILITIES_PUB.put_line(p_text =>'Perf Measure'||l_label);
863: wf_engine.SetItemAttrText
864: ( itemtype => l_item_type
865: , itemkey => l_wf_item_key
866: , aname => 'L_PERFORMANCE_MEASURE_LABEL'
872: , aname => 'L_PERFORMANCE_MEASURE'
873: , avalue => l_measure_rec.Measure_Name
874: );
875:
876: l_label := BIS_UTILITIES_PVT.getPrompt
877: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_UNIT_MEASURE')
878: );
879: BIS_UTILITIES_PUB.put_line(p_text =>'Unit Of Measure'||l_label);
880: wf_engine.SetItemAttrText
875:
876: l_label := BIS_UTILITIES_PVT.getPrompt
877: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_UNIT_MEASURE')
878: );
879: BIS_UTILITIES_PUB.put_line(p_text =>'Unit Of Measure'||l_label);
880: wf_engine.SetItemAttrText
881: ( itemtype => l_item_type
882: , itemkey => l_wf_item_key
883: , aname => 'L_UNITOFMEASURE_LABEL'
889: , aname => 'L_UNITOFMEASURE'
890: , avalue => l_measure_rec.Unit_Of_Measure_Class
891: );
892:
893: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||p_measure_instance.Measure_Name);
894:
895: l_label := BIS_UTILITIES_PVT.getPrompt
896: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TARGET')
897: );
891: );
892:
893: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||p_measure_instance.Measure_Name);
894:
895: l_label := BIS_UTILITIES_PVT.getPrompt
896: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TARGET')
897: );
898: BIS_UTILITIES_PUB.put_line(p_text =>'Target'||l_label);
899: wf_engine.SetItemAttrText
894:
895: l_label := BIS_UTILITIES_PVT.getPrompt
896: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TARGET')
897: );
898: BIS_UTILITIES_PUB.put_line(p_text =>'Target'||l_label);
899: wf_engine.SetItemAttrText
900: ( itemtype => l_item_type
901: , itemkey => l_wf_item_key
902: , aname => 'L_TARGET_LABEL'
908: , aname => 'L_TARGET'
909: , avalue => l_target_rec.Target
910: );
911:
912: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': ' ||l_target_rec.Target);
913:
914: if (l_number_out_of_range >= 100) then
915:
916: l_label := BIS_UTILITIES_PVT.getPrompt
912: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': ' ||l_target_rec.Target);
913:
914: if (l_number_out_of_range >= 100) then
915:
916: l_label := BIS_UTILITIES_PVT.getPrompt
917: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE1')
918: );
919: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 1'||l_label);
920:
915:
916: l_label := BIS_UTILITIES_PVT.getPrompt
917: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE1')
918: );
919: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 1'||l_label);
920:
921: wf_engine.SetItemAttrText
922: ( itemtype => l_item_type
923: , itemkey => l_wf_item_key
930: , aname => 'L_TOLERANCE_RANGE_1'
931: , avalue => l_target_rec.Range1_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range1_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp1_Name); -- 1880142
932: --1880142 , avalue => l_target_rec.Range1_low || ' - ' || l_target_rec.Range1_high||' - '||l_target_rec.Notify_Resp1_Name);
933:
934: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range1_low || ' - ' || l_target_rec.Range1_high);
935:
936: end if;
937:
938: if ( mod(l_number_out_of_range, 100) >= 10 ) then
936: end if;
937:
938: if ( mod(l_number_out_of_range, 100) >= 10 ) then
939:
940: l_label := BIS_UTILITIES_PVT.getPrompt
941: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE2')
942: );
943: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 2'||l_label);
944:
939:
940: l_label := BIS_UTILITIES_PVT.getPrompt
941: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE2')
942: );
943: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 2'||l_label);
944:
945: wf_engine.SetItemAttrText
946: ( itemtype => l_item_type
947: , itemkey => l_wf_item_key
954: , aname => 'L_TOLERANCE_RANGE_2'
955: , avalue => l_target_rec.Range2_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range2_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp2_Name); -- 1880142
956: -- 1880142, avalue => l_target_rec.Range2_low || ' - ' || l_target_rec.Range2_high||' - '||l_target_rec.Notify_Resp2_Name);
957:
958: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range2_low || ' - ' || l_target_rec.Range2_high);
959:
960: end if;
961:
962: if ( mod(l_number_out_of_range, 100) = 1 ) or ( mod(l_number_out_of_range, 10) = 1 ) then
960: end if;
961:
962: if ( mod(l_number_out_of_range, 100) = 1 ) or ( mod(l_number_out_of_range, 10) = 1 ) then
963:
964: l_label := BIS_UTILITIES_PVT.getPrompt
965: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE3')
966: );
967:
968: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 3'||l_label);
964: l_label := BIS_UTILITIES_PVT.getPrompt
965: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_TOLERANCE_RANGE3')
966: );
967:
968: BIS_UTILITIES_PUB.put_line(p_text =>'Tolerance Range 3'||l_label);
969:
970: wf_engine.SetItemAttrText
971: ( itemtype => l_item_type
972: , itemkey => l_wf_item_key
979: , aname => 'L_TOLERANCE_RANGE_3'
980: , avalue => l_target_rec.Range3_low || fnd_message.get_string('BIS', 'BIS_ALERT_BELOW') || ' '||l_target_rec.Range3_high||fnd_message.get_string('BIS', 'BIS_ALERT_ABOVE')||' '||l_target_rec.Notify_Resp3_Name); -- 1880142
981: --1880142 , avalue => l_target_rec.Range3_low || ' - ' || l_target_rec.Range3_high||' - '||l_target_rec.Notify_Resp3_Name);
982:
983: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_target_rec.Range3_low || ' - ' || l_target_rec.Range3_high);
984:
985: end if;
986:
987:
984:
985: end if;
986:
987:
988: l_label := BIS_UTILITIES_PVT.getPrompt
989: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_ACTUAL')
990: );
991: BIS_UTILITIES_PUB.put_line(p_text =>'Actual'||l_label);
992: wf_engine.SetItemAttrText
987:
988: l_label := BIS_UTILITIES_PVT.getPrompt
989: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_ACTUAL')
990: );
991: BIS_UTILITIES_PUB.put_line(p_text =>'Actual'||l_label);
992: wf_engine.SetItemAttrText
993: ( itemtype => l_item_type
994: , itemkey => l_wf_item_key
995: , aname => 'L_ACTUAL_LABEL'
1001: , aname => 'L_ACTUAL'
1002: , avalue => l_formatted_actual
1003: );
1004:
1005: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_actual_rec.Actual);
1006:
1007: l_label := BIS_UTILITIES_PVT.getPrompt
1008: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO')
1009: );
1003: );
1004:
1005: --BIS_UTILITIES_PUB.put_line(p_text =>l_label||': '||l_actual_rec.Actual);
1006:
1007: l_label := BIS_UTILITIES_PVT.getPrompt
1008: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO')
1009: );
1010: BIS_UTILITIES_PUB.put_line(p_text =>'info'||l_label);
1011: wf_engine.SetItemAttrText
1006:
1007: l_label := BIS_UTILITIES_PVT.getPrompt
1008: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO')
1009: );
1010: BIS_UTILITIES_PUB.put_line(p_text =>'info'||l_label);
1011: wf_engine.SetItemAttrText
1012: ( itemtype => l_item_type
1013: , itemkey => l_wf_item_key
1014: , aname => 'L_RELATED_INFO_LABEL'
1015: , avalue => l_label
1016: );
1017:
1018: If ( l_link is null ) THEN -- UPPER(l_link) = G_RPT_ERROR then
1019: l_label := BIS_UTILITIES_PVT.getPrompt
1020: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_NORPT1')
1021: );
1022:
1023: wf_engine.SetItemAttrText
1025: , itemkey => l_wf_item_key
1026: , aname => 'L_RELATE_INFO_LABEL'
1027: , avalue => l_label
1028: );
1029: l_label := BIS_UTILITIES_PVT.getPrompt
1030: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_NORPT2')
1031: );
1032:
1033: wf_engine.SetItemAttrText
1046:
1047: Else
1048:
1049:
1050: l_label := BIS_UTILITIES_PVT.getPrompt
1051: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_INFO_MSG')
1052: );
1053:
1054: wf_engine.SetItemAttrText
1073: );
1074:
1075: End If;
1076:
1077: l_label := BIS_UTILITIES_PVT.getPrompt
1078: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RECIPIENTS')
1079: );
1080: BIS_UTILITIES_PUB.put_line(p_text =>'Recipients'||l_label);
1081: wf_engine.SetItemAttrText
1076:
1077: l_label := BIS_UTILITIES_PVT.getPrompt
1078: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_RECIPIENTS')
1079: );
1080: BIS_UTILITIES_PUB.put_line(p_text =>'Recipients'||l_label);
1081: wf_engine.SetItemAttrText
1082: ( itemtype => l_item_type
1083: , itemkey => l_wf_item_key
1084: , aname => 'L_ALERT_RECIPIENTS_LABEL'
1084: , aname => 'L_ALERT_RECIPIENTS_LABEL'
1085: , avalue => l_label
1086: );
1087:
1088: --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1089: l_label := BIS_UTILITIES_PVT.getPrompt
1090: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_REC_MSG')
1091: );
1092: BIS_UTILITIES_PUB.put_line(p_text =>'Rec Msg'||l_label);
1085: , avalue => l_label
1086: );
1087:
1088: --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1089: l_label := BIS_UTILITIES_PVT.getPrompt
1090: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_REC_MSG')
1091: );
1092: BIS_UTILITIES_PUB.put_line(p_text =>'Rec Msg'||l_label);
1093: wf_engine.SetItemAttrText
1088: --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1089: l_label := BIS_UTILITIES_PVT.getPrompt
1090: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_REC_MSG')
1091: );
1092: BIS_UTILITIES_PUB.put_line(p_text =>'Rec Msg'||l_label);
1093: wf_engine.SetItemAttrText
1094: ( itemtype => l_item_type
1095: , itemkey => l_wf_item_key
1096: , aname => 'L_ALERT_RECIPIENTS_MSG'
1095: , itemkey => l_wf_item_key
1096: , aname => 'L_ALERT_RECIPIENTS_MSG'
1097: , avalue => l_label
1098: );
1099: --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1100:
1101: wf_engine.SetItemAttrText
1102: ( itemtype => l_item_type
1103: , itemkey => l_wf_item_key
1104: , aname => 'L_ROLE_NAME'
1105: , avalue => l_Alert_recipients_sh_nm_tbl(i)
1106: );
1107:
1108: --BIS_UTILITIES_PUB.put_line(p_text =>'Role: '||l_Alert_recipients_tbl(i));
1109: l_msg := null;
1110:
1111: FOR j IN 1..l_Alert_recipients_tbl.COUNT LOOP -- 2684836
1112:
1141: , itemkey => l_wf_item_key
1142: , aname => 'L_RECEPIENT_TXT'
1143: , avalue => l_msg1
1144: );
1145: l_label := BIS_UTILITIES_PVT.getPrompt
1146: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SCHEDULE')
1147: );
1148:
1149: wf_engine.SetItemAttrText
1151: , itemkey => l_wf_item_key
1152: , aname => 'L_ALERT_SCHEDULE'
1153: , avalue => l_label
1154: );
1155: --BIS_UTILITIES_PUB.put_line(p_text =>'SS');
1156: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||l_schedule_date);
1157: BIS_UTILITIES_PUB.put_line(p_text =>'Next Run Date: '||l_next_run_date);
1158:
1159: l_msg := null;
1152: , aname => 'L_ALERT_SCHEDULE'
1153: , avalue => l_label
1154: );
1155: --BIS_UTILITIES_PUB.put_line(p_text =>'SS');
1156: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||l_schedule_date);
1157: BIS_UTILITIES_PUB.put_line(p_text =>'Next Run Date: '||l_next_run_date);
1158:
1159: l_msg := null;
1160: l_label := null; -- 1880142
1153: , avalue => l_label
1154: );
1155: --BIS_UTILITIES_PUB.put_line(p_text =>'SS');
1156: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||l_schedule_date);
1157: BIS_UTILITIES_PUB.put_line(p_text =>'Next Run Date: '||l_next_run_date);
1158:
1159: l_msg := null;
1160: l_label := null; -- 1880142
1161: if UPPER(l_schedule_unit) <> 'ONCE' then
1165: , itemkey => l_wf_item_key
1166: , aname => 'NEXT_RUN_DATE'
1167: , avalue => l_next_run_date
1168: );
1169: l_label := BIS_UTILITIES_PVT.getPrompt
1170: (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1171: p_message_name => 'BIS_ALERT_SCHEDULE_MSG1'
1172: , p_msg_param1 => 'NEXT_RUN_DATE'
1173: , p_msg_param1_val => l_next_run_date
1166: , aname => 'NEXT_RUN_DATE'
1167: , avalue => l_next_run_date
1168: );
1169: l_label := BIS_UTILITIES_PVT.getPrompt
1170: (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1171: p_message_name => 'BIS_ALERT_SCHEDULE_MSG1'
1172: , p_msg_param1 => 'NEXT_RUN_DATE'
1173: , p_msg_param1_val => l_next_run_date
1174: )
1183: -- 1880142, avalue => l_schedule_freq ||' '||l_schedule_freq_unit
1184: , avalue => l_schedule_unit -- 1880142
1185: );
1186:
1187: l_label := l_label || ' ' || BIS_UTILITIES_PVT.getPrompt
1188: (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1189: p_message_name => 'BIS_ALERT_SCHEDULE_MSG2'
1190: , p_msg_param1 => 'SCHEDULE_INFO'
1191: , p_msg_param1_val => l_schedule_unit
1184: , avalue => l_schedule_unit -- 1880142
1185: );
1186:
1187: l_label := l_label || ' ' || BIS_UTILITIES_PVT.getPrompt
1188: (p_attribute_code => BIS_UTILITIES_PVT.Get_FND_Message(
1189: p_message_name => 'BIS_ALERT_SCHEDULE_MSG2'
1190: , p_msg_param1 => 'SCHEDULE_INFO'
1191: , p_msg_param1_val => l_schedule_unit
1192: )
1195: end if;
1196:
1197: else
1198:
1199: l_label := BIS_UTILITIES_PVT.getPrompt(p_attribute_code=> fnd_message.get_string('BIS', 'BIS_ALERT_NOT_REPEAT'));
1200:
1201: end if;
1202:
1203: --l_msg := l_msg||'You will only receive an alert if the actual performane measure is outside of the tolerance range.';
1200:
1201: end if;
1202:
1203: --l_msg := l_msg||'You will only receive an alert if the actual performane measure is outside of the tolerance range.';
1204: l_label := l_label || ' ' || BIS_UTILITIES_PVT.getPrompt
1205: (p_attribute_code => fnd_message.get_string('BIS', 'BIS_ALERT_SCHEDULE_MSG3')
1206: );
1207:
1208:
1254:
1255: -- End addition 1898436
1256:
1257:
1258: BIS_UTILITIES_PUB.put_line(p_text =>'schedule msg'||l_label);
1259: wf_engine.SetItemAttrText
1260: ( itemtype => l_item_type
1261: , itemkey => l_wf_item_key
1262: , aname => 'L_ALERT_SCHEDULE_MSG'
1262: , aname => 'L_ALERT_SCHEDULE_MSG'
1263: , avalue => l_label
1264: );
1265:
1266: --BIS_UTILITIES_PUB.put_line(p_text =>l_label);
1267: wf_engine.SetItemAttrText
1268: ( itemtype => l_item_type
1269: , itemkey => l_wf_item_key
1270: , aname => 'L_ALERT_SCHEDULE_MSG'
1289: , itemkey => l_wf_item_key
1290: );
1291:
1292: /*
1293: BIS_UTILITIES_PUB.put_line(p_text =>'Started workflow '||i||'. item type: '||l_item_type
1294: ||', process: '||l_process
1295: ||', item key: '||l_wf_item_key
1296: ||', notified: '||l_Alert_recipients_tbl(i));
1297: */
1299: commit;
1300:
1301: END LOOP;
1302:
1303: BIS_UTILITIES_PUB.put_line(p_text =>' ........... END : Notification email ........... ');
1304:
1305: EXCEPTION
1306: when FND_API.G_EXC_ERROR then
1307: BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 at Send_Alert: '||sqlerrm);
1303: BIS_UTILITIES_PUB.put_line(p_text =>' ........... END : Notification email ........... ');
1304:
1305: EXCEPTION
1306: when FND_API.G_EXC_ERROR then
1307: BIS_UTILITIES_PUB.put_line(p_text =>'exception 1 at Send_Alert: '||sqlerrm);
1308: wf_core.context
1309: ( 'BIS_CORRECTIVE_ACTION_PVT'
1310: , 'Send_Alert'
1311: , l_item_type
1312: , l_wf_item_key
1313: );
1314: RETURN;
1315: when FND_API.G_EXC_UNEXPECTED_ERROR then
1316: BIS_UTILITIES_PUB.put_line(p_text =>'exception 2 at Send_Alert: '||sqlerrm);
1317: wf_core.context
1318: ( 'BIS_CORRECTIVE_ACTION_PVT'
1319: , 'Send_Alert'
1320: , l_item_type
1321: , l_wf_item_key
1322: );
1323: RETURN;
1324: when others then
1325: BIS_UTILITIES_PUB.put_line(p_text =>'exception 3 at Send_Alert: '||sqlerrm);
1326: wf_core.context
1327: ( 'BIS_CORRECTIVE_ACTION_PVT'
1328: , 'Send_Alert'
1329: , l_item_type
1348:
1349: BEGIN
1350:
1351: IF (
1352: ( BIS_UTILITIES_PUB.Value_Missing(p_recipient_short_name) = FND_API.G_TRUE ) OR
1353: ( BIS_UTILITIES_PUB.Value_Null(p_recipient_short_name) = FND_API.G_TRUE )
1354: ) THEN
1355: x_return_status := FND_API.G_RET_STS_ERROR;
1356: x_return_msg := 'Recipient short name is null or missing.';
1349: BEGIN
1350:
1351: IF (
1352: ( BIS_UTILITIES_PUB.Value_Missing(p_recipient_short_name) = FND_API.G_TRUE ) OR
1353: ( BIS_UTILITIES_PUB.Value_Null(p_recipient_short_name) = FND_API.G_TRUE )
1354: ) THEN
1355: x_return_status := FND_API.G_RET_STS_ERROR;
1356: x_return_msg := 'Recipient short name is null or missing.';
1357: RETURN;
1385: Procedure Get_Alert_Recipients
1386: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1387: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1388: , p_comparison_result IN VARCHAR2
1389: , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1390: , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1391: , x_number_out_of_range OUT NOCOPY NUMBER
1392: )
1393: IS
1386: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1387: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1388: , p_comparison_result IN VARCHAR2
1389: , x_Alert_recipients_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1390: , x_Alert_recipients_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1391: , x_number_out_of_range OUT NOCOPY NUMBER
1392: )
1393: IS
1394:
1394:
1395: l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1396: l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1397: l_Target_owners_rec BIS_TARGET_PUB.Target_Owners_Rec_Type;
1398: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1399: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1400: l_notifiers_code VARCHAR2(32000);
1401: l_user_tbl wf_directory.UserTable;
1402: l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1395: l_Param_Set_Rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
1396: l_Param_Set_Tbl BIS_PMF_ALERT_REG_PUB.parameter_set_tbl_type;
1397: l_Target_owners_rec BIS_TARGET_PUB.Target_Owners_Rec_Type;
1398: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1399: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1400: l_notifiers_code VARCHAR2(32000);
1401: l_user_tbl wf_directory.UserTable;
1402: l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1403: l_return_status VARCHAR2(32000);
1398: l_Alert_recipients_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1399: l_Alert_recipients_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1400: l_notifiers_code VARCHAR2(32000);
1401: l_user_tbl wf_directory.UserTable;
1402: l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1403: l_return_status VARCHAR2(32000);
1404:
1405: BEGIN
1406:
1410: , x_Param_Set_Rec => l_Param_Set_Rec
1411: );
1412:
1413: /*
1414: BIS_UTILITIES_PUB.put_line(p_text =>'Formed Param set ');
1415: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1411: );
1412:
1413: /*
1414: BIS_UTILITIES_PUB.put_line(p_text =>'Formed Param set ');
1415: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1412:
1413: /*
1414: BIS_UTILITIES_PUB.put_line(p_text =>'Formed Param set ');
1415: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1414: BIS_UTILITIES_PUB.put_line(p_text =>'Formed Param set ');
1415: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1415: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_rec.REGISTRATION_ID);
1416: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1417: ||l_param_set_rec.PERFORMANCE_MEASURE_ID );
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1418: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_rec.TARGET_LEVEL_ID);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1419: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1420: ||l_param_set_rec.TIME_DIMENSION_LEVEL_ID);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1421: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_rec.PLAN_ID);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1422: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_rec.NOTIFIERS_CODE);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1423: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_rec.PARAMETER1_VALUE);
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1431: */
1424: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_rec.PARAMETER2_VALUE);
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1431: */
1432:
1425: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_rec.PARAMETER3_VALUE);
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1431: */
1432:
1433: BIS_PMF_ALERT_REG_PVT.Retrieve_Parameter_set
1426: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_rec.PARAMETER4_VALUE);
1427: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_rec.PARAMETER5_VALUE);
1428: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_rec.PARAMETER6_VALUE);
1429: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_rec.PARAMETER7_VALUE);
1430: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_rec.NOTIFY_OWNER_FLAG);
1431: */
1432:
1433: BIS_PMF_ALERT_REG_PVT.Retrieve_Parameter_set
1434: ( p_api_version => 1.0
1438: , x_error_Tbl => l_error_Tbl
1439: );
1440: /*
1441: FOR i IN l_param_set_tbl.FIRST..l_param_set_tbl.LAST LOOP
1442: BIS_UTILITIES_PUB.put_line(p_text =>' Param set '||i);
1443: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1439: );
1440: /*
1441: FOR i IN l_param_set_tbl.FIRST..l_param_set_tbl.LAST LOOP
1442: BIS_UTILITIES_PUB.put_line(p_text =>' Param set '||i);
1443: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1440: /*
1441: FOR i IN l_param_set_tbl.FIRST..l_param_set_tbl.LAST LOOP
1442: BIS_UTILITIES_PUB.put_line(p_text =>' Param set '||i);
1443: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1442: BIS_UTILITIES_PUB.put_line(p_text =>' Param set '||i);
1443: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1443: BIS_UTILITIES_PUB.put_line(p_text =>'REGISTRATION_ID : '||l_param_set_tbl(i).REGISTRATION_ID);
1444: BIS_UTILITIES_PUB.put_line(p_text =>'PERFORMANCE_MEASURE_ID: '
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1445: ||l_param_set_tbl(i).PERFORMANCE_MEASURE_ID );
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1446: BIS_UTILITIES_PUB.put_line(p_text =>'TARGET_LEVEL_ID : '||l_param_set_tbl(i).TARGET_LEVEL_ID);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1447: BIS_UTILITIES_PUB.put_line(p_text =>'TIME_DIMENSION_LEVEL_ID: '
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1448: ||l_param_set_tbl(i).TIME_DIMENSION_LEVEL_ID);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1449: BIS_UTILITIES_PUB.put_line(p_text =>'PLAN_ID : '||l_param_set_tbl(i).PLAN_ID);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1450: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFIERS_CODE : '||l_param_set_tbl(i).NOTIFIERS_CODE);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1451: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER1_VALUE : '||l_param_set_tbl(i).PARAMETER1_VALUE);
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1459: END LOOP;
1452: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER2_VALUE : '||l_param_set_tbl(i).PARAMETER2_VALUE);
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1459: END LOOP;
1460: */
1453: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER3_VALUE : '||l_param_set_tbl(i).PARAMETER3_VALUE);
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1459: END LOOP;
1460: */
1461:
1454: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER4_VALUE : '||l_param_set_tbl(i).PARAMETER4_VALUE);
1455: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER5_VALUE : '||l_param_set_tbl(i).PARAMETER5_VALUE);
1456: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER6_VALUE : '||l_param_set_tbl(i).PARAMETER6_VALUE);
1457: BIS_UTILITIES_PUB.put_line(p_text =>'PARAMETER7_VALUE : '||l_param_set_tbl(i).PARAMETER7_VALUE);
1458: BIS_UTILITIES_PUB.put_line(p_text =>'NOTIFY_OWNER_FLAG: '||l_param_set_tbl(i).NOTIFY_OWNER_FLAG);
1459: END LOOP;
1460: */
1461:
1462: IF l_param_set_tbl.COUNT <> 1 THEN
1459: END LOOP;
1460: */
1461:
1462: IF l_param_set_tbl.COUNT <> 1 THEN
1463: BIS_UTILITIES_PUB.put_line(p_text =>'odd number of param set found in Get_Alert_Recipients: '
1464: ||l_param_set_tbl.count);
1465: return;
1466: ELSE
1467: BIS_UTILITIES_PUB.put_line(p_text =>'Getting Target owners');
1463: BIS_UTILITIES_PUB.put_line(p_text =>'odd number of param set found in Get_Alert_Recipients: '
1464: ||l_param_set_tbl.count);
1465: return;
1466: ELSE
1467: BIS_UTILITIES_PUB.put_line(p_text =>'Getting Target owners');
1468: BIS_UTILITIES_PUB.put_line(p_text =>'Check: '||p_measure_instance.target_level_id);
1469: BIS_UTILITIES_PUB.put_line(p_text =>'Check2: '||p_measure_instance.range1_owner_short_name);
1470: BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Owners
1471: ( p_measure_instance => p_measure_instance
1464: ||l_param_set_tbl.count);
1465: return;
1466: ELSE
1467: BIS_UTILITIES_PUB.put_line(p_text =>'Getting Target owners');
1468: BIS_UTILITIES_PUB.put_line(p_text =>'Check: '||p_measure_instance.target_level_id);
1469: BIS_UTILITIES_PUB.put_line(p_text =>'Check2: '||p_measure_instance.range1_owner_short_name);
1470: BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Owners
1471: ( p_measure_instance => p_measure_instance
1472: , p_dim_level_value_tbl => p_dim_level_value_tbl
1465: return;
1466: ELSE
1467: BIS_UTILITIES_PUB.put_line(p_text =>'Getting Target owners');
1468: BIS_UTILITIES_PUB.put_line(p_text =>'Check: '||p_measure_instance.target_level_id);
1469: BIS_UTILITIES_PUB.put_line(p_text =>'Check2: '||p_measure_instance.range1_owner_short_name);
1470: BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Owners
1471: ( p_measure_instance => p_measure_instance
1472: , p_dim_level_value_tbl => p_dim_level_value_tbl
1473: , p_all_info => FND_API.G_FALSE
1480: , x_owners_sh_nm_tbl => l_Alert_recipients_sh_nm_tbl
1481: , x_number_out_of_range => x_number_out_of_range
1482: );
1483:
1484: BIS_UTILITIES_PUB.put_line(p_text =>'total number of owners: '||l_Alert_recipients_tbl.count);
1485:
1486: l_notifiers_code := l_param_set_tbl(l_param_set_tbl.FIRST).NOTIFIERS_CODE;
1487: wf_directory.GetRoleUsers(l_notifiers_code,l_user_tbl);
1488: IF l_user_tbl.COUNT > 0 THEN
1487: wf_directory.GetRoleUsers(l_notifiers_code,l_user_tbl);
1488: IF l_user_tbl.COUNT > 0 THEN
1489: l_Alert_recipients_tbl(l_Alert_recipients_tbl.COUNT+1) := l_notifiers_code;
1490: l_Alert_recipients_sh_nm_tbl(l_Alert_recipients_sh_nm_tbl.COUNT+1) := l_notifiers_code;
1491: BIS_UTILITIES_PUB.put_line(p_text =>'Recipients include '||l_user_tbl.COUNT||' subscriber(s)');
1492: /*
1493: FOR i IN 1..l_user_tbl.COUNT LOOP
1494: BIS_UTILITIES_PUB.put_line(p_text =>i||')'||l_user_tbl(i));
1495: END LOOP;
1490: l_Alert_recipients_sh_nm_tbl(l_Alert_recipients_sh_nm_tbl.COUNT+1) := l_notifiers_code;
1491: BIS_UTILITIES_PUB.put_line(p_text =>'Recipients include '||l_user_tbl.COUNT||' subscriber(s)');
1492: /*
1493: FOR i IN 1..l_user_tbl.COUNT LOOP
1494: BIS_UTILITIES_PUB.put_line(p_text =>i||')'||l_user_tbl(i));
1495: END LOOP;
1496: */
1497: ELSE
1498: BIS_UTILITIES_PUB.put_line(p_text =>'No subscribers for this alert.');
1494: BIS_UTILITIES_PUB.put_line(p_text =>i||')'||l_user_tbl(i));
1495: END LOOP;
1496: */
1497: ELSE
1498: BIS_UTILITIES_PUB.put_line(p_text =>'No subscribers for this alert.');
1499: null;
1500: END IF;
1501: END IF;
1502:
1499: null;
1500: END IF;
1501: END IF;
1502:
1503: --BIS_UTILITIES_PUB.put_line(p_text =>'get alert recipients returned: '||l_Alert_recipients_tbl.COUNT);
1504: FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
1505: BIS_UTILITIES_PUB.put_line(p_text =>'Notification will be sent to: '|| l_Alert_recipients_sh_nm_tbl(i));
1506: END LOOP;
1507:
1501: END IF;
1502:
1503: --BIS_UTILITIES_PUB.put_line(p_text =>'get alert recipients returned: '||l_Alert_recipients_tbl.COUNT);
1504: FOR i IN 1..l_Alert_recipients_sh_nm_tbl.COUNT LOOP
1505: BIS_UTILITIES_PUB.put_line(p_text =>'Notification will be sent to: '|| l_Alert_recipients_sh_nm_tbl(i));
1506: END LOOP;
1507:
1508: x_Alert_recipients_tbl := l_Alert_recipients_tbl;
1509: x_Alert_recipients_sh_nm_tbl := l_Alert_recipients_sh_nm_tbl;
1509: x_Alert_recipients_sh_nm_tbl := l_Alert_recipients_sh_nm_tbl;
1510:
1511: EXCEPTION
1512: WHEN OTHERS THEN
1513: BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_Alert_Recipients: '||sqlerrm);
1514: END Get_Alert_Recipients;
1515:
1516: PROCEDURE Get_Result_Owners
1517: ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
1515:
1516: PROCEDURE Get_Result_Owners
1517: ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
1518: , p_comparison_result IN VARCHAR2
1519: , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1520: , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1521: , x_number_out_of_range OUT NOCOPY NUMBER
1522: )
1523: IS
1516: PROCEDURE Get_Result_Owners
1517: ( p_target_Owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
1518: , p_comparison_result IN VARCHAR2
1519: , x_owners_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1520: , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1521: , x_number_out_of_range OUT NOCOPY NUMBER
1522: )
1523: IS
1524: l_owners_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1520: , x_owners_sh_nm_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1521: , x_number_out_of_range OUT NOCOPY NUMBER
1522: )
1523: IS
1524: l_owners_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1525: l_owners_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1526:
1527: l_number_out_of_range NUMBER := 0;
1528: l_first_out_of_range NUMBER := 0;
1521: , x_number_out_of_range OUT NOCOPY NUMBER
1522: )
1523: IS
1524: l_owners_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1525: l_owners_sh_nm_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1526:
1527: l_number_out_of_range NUMBER := 0;
1528: l_first_out_of_range NUMBER := 0;
1529: l_second_out_of_range NUMBER := 0;
1531:
1532: BEGIN
1533:
1534: /*
1535: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1532: BEGIN
1533:
1534: /*
1535: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1533:
1534: /*
1535: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1541: */
1534: /*
1535: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1541: */
1542:
1535: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 name = ' || p_target_Owners_rec.Range1_Owner_Name ) ;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1541: */
1542:
1543: l_owners_tbl := x_owners_tbl;
1536: BIS_UTILITIES_PUB.put_line(p_text => ' owner 1 short name = ' || p_target_Owners_rec.Range1_Owner_short_Name ) ;
1537: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 name = ' || p_target_Owners_rec.Range2_Owner_Name ) ;
1538: BIS_UTILITIES_PUB.put_line(p_text => ' owner 2 short name = ' || p_target_Owners_rec.Range2_Owner_short_Name ) ;
1539: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 name = ' || p_target_Owners_rec.Range3_Owner_Name ) ;
1540: BIS_UTILITIES_PUB.put_line(p_text => ' owner 3 short name = ' || p_target_Owners_rec.Range3_Owner_short_Name ) ;
1541: */
1542:
1543: l_owners_tbl := x_owners_tbl;
1544:
1571:
1572: x_owners_tbl := l_owners_tbl;
1573: x_owners_sh_nm_tbl := l_owners_sh_nm_tbl;
1574:
1575: -- BIS_UTILITIES_PUB.put_line(p_text => ' get result owners owners count ' || l_owners_tbl.COUNT || ' sh nm dt ' || l_owners_sh_nm_tbl.COUNT ) ;
1576:
1577: l_number_out_of_range := 100*l_first_out_of_range + 10*l_second_out_of_range + l_third_out_of_range ;
1578: x_number_out_of_range := l_number_out_of_range;
1579:
1578: x_number_out_of_range := l_number_out_of_range;
1579:
1580: EXCEPTION
1581: WHEN OTHERS THEN
1582: BIS_UTILITIES_PUB.put_line(p_text =>'exception at Get_Result_Owners: '||sqlerrm);
1583:
1584: END Get_Result_Owners;
1585:
1586: Procedure Get_Performance_Measure_Msg
1586: Procedure Get_Performance_Measure_Msg
1587: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1588: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1589: , p_comparison_result IN VARCHAR2
1590: , x_message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1591: )
1592: IS
1593:
1594: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1593:
1594: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1595: l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
1596:
1597: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1598: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1599: l_Message_Intro_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1600: l_Message_body_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1601: l_notify VARCHAR2(32000);
1594: l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1595: l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
1596:
1597: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1598: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1599: l_Message_Intro_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1600: l_Message_body_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1601: l_notify VARCHAR2(32000);
1602: l_sent_date VARCHAR2(32000);
1595: l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
1596:
1597: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1598: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1599: l_Message_Intro_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1600: l_Message_body_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1601: l_notify VARCHAR2(32000);
1602: l_sent_date VARCHAR2(32000);
1603:
1596:
1597: l_message_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1598: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1599: l_Message_Intro_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1600: l_Message_body_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1601: l_notify VARCHAR2(32000);
1602: l_sent_date VARCHAR2(32000);
1603:
1604: BEGIN
1669: x_message_tbl := l_message_tbl;
1670:
1671: EXCEPTION
1672: WHEN OTHERS THEN
1673: BIS_UTILITIES_PUB.put_line(p_text =>'error in Get_Performance_Measure_Msg: '||sqlerrm);
1674:
1675: END Get_Performance_Measure_Msg;
1676:
1677: Procedure Get_Message_Banner
1677: Procedure Get_Message_Banner
1678: ( p_Sent_Date IN VARCHAR2
1679: , p_Item IN VARCHAR2
1680: , p_to IN VARCHAR2
1681: , x_Message_Banner_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1682: )
1683: IS
1684:
1685: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1681: , x_Message_Banner_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1682: )
1683: IS
1684:
1685: l_Message_Banner_tbl BIS_UTILITIES_PUB.BIS_VARCHAR_TBL;
1686:
1687: BEGIN
1688:
1689: x_message_banner_tbl := l_message_banner_tbl;
1691: END Get_Message_Banner;
1692:
1693: Procedure Get_Message_Intro
1694: ( p_Sent_Date IN VARCHAR2
1695: , x_Message_Intro_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1696: )
1697: IS
1698:
1699: BEGIN
1703: Procedure Get_Message_Body
1704: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1705: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1706: , p_comparison_result IN VARCHAR2
1707: , x_message_body_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1708: )
1709: IS
1710:
1711: BEGIN
1714:
1715: Procedure Get_Alert_Information
1716: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1717: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1718: , x_Alert_Information_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1719: )
1720: IS
1721:
1722: BEGIN
1725:
1726: Procedure Get_Related_Links
1727: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1728: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1729: , x_Related_Links_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1730: )
1731: IS
1732:
1733: BEGIN
1736:
1737: Procedure Get_Report_Attachement
1738: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1739: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1740: , x_attachement_url_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1741: )
1742: IS
1743:
1744: BEGIN
1750: RETURN;
1751: when FND_API.G_EXC_UNEXPECTED_ERROR then
1752: RETURN;
1753: when others then
1754: BIS_UTILITIES_PVT.Add_Error_Message
1755: ( p_error_msg_id => SQLCODE
1756: , p_error_description => SQLERRM
1757: , p_error_proc_name => G_PKG_NAME||'.Get_Report_Attachement'
1758: );
1763:
1764: Procedure Get_Alert_Message
1765: ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
1766: , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
1767: , x_Alert_Message_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1768: )
1769: IS
1770:
1771: BEGIN
1772: null;
1773: END Get_Alert_Message;
1774:
1775: Procedure Set_Message
1776: ( p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1777: , x_return_status OUT NOCOPY VARCHAR2
1778: )
1779: IS
1780:
1798: l_document varchar2(32000);
1799: l_amp varchar2(10) := '&';
1800: l_target_id varchar2(32000);
1801: l_wait_msg varchar2(32000) :=
1802: BIS_UTILITIES_PVT.Get_FND_Message
1803: ( p_message_name => 'BIS_ALERT_RETRIEVING_MSG' );
1804:
1805: BEGIN
1806:
1816: ||'?dbc='||FND_WEB_CONFIG.DATABASE_ID
1817: ||l_amp||'sessionid='||l_encrypted_session_id
1818: ||document_id
1819: ||'">'
1820: ||bis_utilities_pub.encode(l_wait_msg) -- 2418741
1821: ||' ';
1822:
1823: l_message := l_redirect;
1824: --BIS_UTILITIES_PUB.put_line(p_text =>'notification redirect: '||l_message);
1820: ||bis_utilities_pub.encode(l_wait_msg) -- 2418741
1821: ||' ';
1822:
1823: l_message := l_redirect;
1824: --BIS_UTILITIES_PUB.put_line(p_text =>'notification redirect: '||l_message);
1825:
1826: document := l_message;
1827: document_type := 'text/html';
1828: return;
1828: return;
1829:
1830: EXCEPTION
1831: when others then
1832: BIS_UTILITIES_PUB.put_line(p_text =>'exception at Generate_Alert_Message;: '||sqlerrm);
1833: RETURN;
1834:
1835: END Generate_Alert_Message;
1836:
1848: RETURN l_servlet_agent;
1849:
1850: EXCEPTION
1851: WHEN NO_DATA_FOUND THEN
1852: BIS_UTILITIES_PUB.put_line(p_text =>'No such agent defined at site level.');
1853:
1854: WHEN OTHERS THEN
1855: BIS_UTILITIES_PUB.put_line(p_text =>'General exception while getting servlet agent: '||sqlerrm);
1856:
1851: WHEN NO_DATA_FOUND THEN
1852: BIS_UTILITIES_PUB.put_line(p_text =>'No such agent defined at site level.');
1853:
1854: WHEN OTHERS THEN
1855: BIS_UTILITIES_PUB.put_line(p_text =>'General exception while getting servlet agent: '||sqlerrm);
1856:
1857: End Get_Servlet_Agent;
1858:
1859: Procedure Get_Request_Info
1877: , x_Param_Set_Rec => l_Param_Set_Rec
1878: );
1879:
1880: /*
1881: BIS_UTILITIES_PUB.put_line(p_text =>'Getting request information. measure id: '
1882: ||l_Param_Set_Rec.performance_measure_id||', target level: '
1883: ||l_Param_Set_Rec.target_level_id);
1884: */
1885:
1897: , x_description => x_description
1898: , x_return_status => x_return_status
1899: );
1900:
1901: BIS_UTILITIES_PUB.put_line(p_text =>'Request description: '||x_description);
1902: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||x_schedule_date);
1903:
1904: EXCEPTION
1905: WHEN OTHERS THEN
1898: , x_return_status => x_return_status
1899: );
1900:
1901: BIS_UTILITIES_PUB.put_line(p_text =>'Request description: '||x_description);
1902: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||x_schedule_date);
1903:
1904: EXCEPTION
1905: WHEN OTHERS THEN
1906: BIS_UTILITIES_PUB.put_line(p_text =>'error at Get_Request_Info: '||sqlerrm);
1902: BIS_UTILITIES_PUB.put_line(p_text =>'Schedule Date: '||x_schedule_date);
1903:
1904: EXCEPTION
1905: WHEN OTHERS THEN
1906: BIS_UTILITIES_PUB.put_line(p_text =>'error at Get_Request_Info: '||sqlerrm);
1907:
1908: END Get_Request_Info;
1909:
1910: FUNCTION getDate
1930:
1931: END getDate;
1932:
1933: FUNCTION getAdHocRole
1934: ( p_Alert_recipients_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL )
1935: RETURN VARCHAR2
1936: IS
1937: l_role VARCHAR2(32000);
1938: l_bis_alert VARCHAR2(32000);
1944: l_bis_alert := substr(p_Alert_recipients_tbl(i),0,colon-1);
1945:
1946: IF (l_bis_alert = BIS_PMF_ALERT_REG_PVT.G_BIS_ALERT_ROLE) THEN
1947: l_role := p_Alert_recipients_tbl(i);
1948: BIS_UTILITIES_PUB.put_line(p_text =>'Got AddHocRole: '||l_role);
1949: END IF;
1950: END LOOP;
1951:
1952: return l_role;
1957: RETURN l_role;
1958:
1959: END getAdHocRole;
1960:
1961: FUNCTION Format_Message(p_message_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL)
1962: RETURN VARCHAR2
1963: IS
1964:
1965: msg_len NUMBER;
1984: Return l_message;
1985:
1986: EXCEPTION
1987: WHEN OTHERS THEN
1988: BIS_UTILITIES_PUB.put_line(p_text =>'Exception while formatting message: '||sqlerrm);
1989:
1990: END Format_Message;
1991:
1992: Procedure Get_Workflow_Info
2012: x_process := l_target_level_rec.Workflow_Process_Short_Name;
2013:
2014: EXCEPTION
2015: WHEN OTHERS THEN
2016: BIS_UTILITIES_PUB.put_line(p_text =>'Exception while getting workflow info: '||sqlerrm);
2017:
2018: END Get_Workflow_Info;
2019:
2020: FUNCTION Generate_parameter_string
2057: l_next_run_date := wfa_html.conv_special_url_chars(p_next_run_date);
2058: l_next_run_time := wfa_html.conv_special_url_chars(p_next_run_time);
2059: l_description := wfa_html.conv_special_url_chars(p_description);
2060: */
2061: l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2058: l_next_run_time := wfa_html.conv_special_url_chars(p_next_run_time);
2059: l_description := wfa_html.conv_special_url_chars(p_description);
2060: */
2061: l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2059: l_description := wfa_html.conv_special_url_chars(p_description);
2060: */
2061: l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2060: */
2061: l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2061: l_target_id := BIS_UTILITIES_PUB.encode(p_target_id);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2062: l_comparison_result := BIS_UTILITIES_PUB.encode(p_comparison_result);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070: l_description := BIS_UTILITIES_PUB.encode(p_description);
2063: l_role := BIS_UTILITIES_PUB.encode(p_role);
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070: l_description := BIS_UTILITIES_PUB.encode(p_description);
2071:
2064: l_date := BIS_UTILITIES_PUB.encode(p_date);
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070: l_description := BIS_UTILITIES_PUB.encode(p_description);
2071:
2072: -- 2280993 ends
2065: l_schedule_date := BIS_UTILITIES_PUB.encode(p_schedule_date);
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070: l_description := BIS_UTILITIES_PUB.encode(p_description);
2071:
2072: -- 2280993 ends
2073:
2066: L_schedule_time := BIS_UTILITIES_PUB.encode(P_schedule_time);
2067: l_schedule_freq_unit := BIS_UTILITIES_PUB.encode(p_schedule_freq_unit);
2068: l_next_run_date := BIS_UTILITIES_PUB.encode(p_next_run_date);
2069: l_next_run_time := BIS_UTILITIES_PUB.encode(p_next_run_time);
2070: l_description := BIS_UTILITIES_PUB.encode(p_description);
2071:
2072: -- 2280993 ends
2073:
2074: l_parameters
2086: return l_parameters;
2087:
2088: EXCEPTION
2089: WHEN OTHERS THEN
2090: BIS_UTILITIES_PUB.put_line(p_text =>'Exception while generating parameter string: '||sqlerrm);
2091:
2092: END Generate_parameter_string;
2093:
2094:
2121: IF icx_sec.validateSession THEN
2122:
2123: l_jsp_name := 'bisunsub.jsp';
2124:
2125: l_bis_url := bis_utilities_pvt.get_bis_jsp_path;
2126:
2127: l_parameter := 'measureId='||pMeasureId -- 487
2128: || '&' || 'targetLevelId=' || pTargetLevelId -- 1964
2129: || '&' || 'targetId=' || pTargetId -- 602
2182: l_return_status VARCHAR2(100);
2183: l_users_table Wf_Directory.UserTable;
2184: l_Param_Set_rec BIS_PMF_ALERT_REG_PUB.parameter_set_rec_type;
2185: l_user_in_role BOOLEAN := FALSE;
2186: l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2187: i NUMBER;
2188: l_user_tbl wf_directory.UserTable;
2189: l_role VARCHAR2(300) := NULL; -- 'BIS_ALERT880';
2190: l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2186: l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2187: i NUMBER;
2188: l_user_tbl wf_directory.UserTable;
2189: l_role VARCHAR2(300) := NULL; -- 'BIS_ALERT880';
2190: l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2191:
2192:
2193: BEGIN
2194:
2248: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2249: x_msg_count := 1;
2250: x_msg_data := 'Error in BISVCACB.pls in unSubscribeFromAlerts' || sqlerrm;
2251: l_error_tbl_p := l_error_tbl;
2252: BIS_UTILITIES_PVT.Add_Error_Message
2253: ( p_error_msg_id => SQLCODE
2254: , p_error_description => SQLERRM
2255: , p_error_proc_name => G_PKG_NAME||'.unSubscribeFromAlerts'
2256: , p_error_table => l_error_tbl_p
2372: ) ;
2373:
2374: l_next_run_date := to_char(l_client_date, l_date_format);
2375:
2376: l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2377: p_message_name => 'BIS_ALERT_SCHEDULE_MSG1'
2378: ,p_msg_param1 => 'NEXT_RUN_DATE'
2379: ,p_msg_param1_val => l_next_run_date
2380: );
2380: );
2381: END IF;
2382:
2383: IF (l_freq IS NOT NULL) THEN
2384: l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2385: p_message_name => 'BIS_ALERT_SCHEDULE_MSG2'
2386: ,p_msg_param1 => 'SCHEDULE_INFO'
2387: ,p_msg_param1_val => l_freq
2388: );
2387: ,p_msg_param1_val => l_freq
2388: );
2389: END IF;
2390: ELSE
2391: l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2392: p_message_name => 'BIS_ALERT_NOT_REPEAT'
2393: );
2394: END IF;
2395:
2392: p_message_name => 'BIS_ALERT_NOT_REPEAT'
2393: );
2394: END IF;
2395:
2396: l_alert_message := l_alert_message || BIS_UTILITIES_PVT.Get_FND_Message(
2397: p_message_name => 'BIS_ALERT_SCHEDULE_MSG3'
2398: );
2399:
2400: l_hdr_support := wf_core.translate('WF_HEADER_ATTR');
3086: l_sql_stmt VARCHAR2(400);
3087: l_client_date DATE;
3088:
3089: BEGIN
3090: l_db_ver := BIS_UTILITIES_PUB.Get_DB_Version;
3091: l_client_date := p_date_time;
3092:
3093: l_sql_stmt := 'begin :1 := fnd_timezones_pvt.adjust_datetime(:2, :3, :4); end;';
3094: