[Home] [Help]
5725: l_approver_notify boolean;
5726: l_employee_notify boolean;
5727: l_init_notify boolean;
5728: l_frp_notify boolean;
5729: t_wf_roles t_varchar2_400_type;
5730: t_person_id t_num_15_type;
5731: l_template_name varchar2(240);
5732: l_distribution_source varchar2(260);
5733: l_time_period_name varchar2(100);
5750:
5751: cursor get_frp_wf_names is
5752: select distinct wf.name,
5753: wf.orig_system_id
5754: from wf_roles wf,
5755: psp_report_templates_h h,
5756: psp_report_template_details_h dtl
5757: where wf.orig_system = 'PER'
5758: and to_char(wf.orig_system_id) = dtl.criteria_value1
5766:
5767: cursor get_init_wf_names is
5768: select distinct wf.name,
5769: wf.orig_system_id
5770: from wf_roles wf,
5771: psp_report_templates_h h
5772: where wf.orig_system = 'PER'
5773: and wf.orig_system_id = h.initiator_person_id
5774: and h.request_id in ( select -person_id
5780:
5781: cursor get_per_wf_names is
5782: select distinct wf.name,
5783: er.person_id
5784: from wf_roles wf,
5785: psp_eff_reports er,
5786: psp_report_templates_h h
5787: where wf.orig_system = 'PER'
5788: and wf.orig_system_id = er.person_id
5990: close get_superceding_source;
5991:
5992: if l_init_notify then
5993: open get_init_wf_names;
5994: fetch get_init_wf_names bulk collect into t_wf_roles, t_person_id;
5995: close get_init_wf_names;
5996:
5997: for i in 1..t_wf_roles.count
5998: loop
5993: open get_init_wf_names;
5994: fetch get_init_wf_names bulk collect into t_wf_roles, t_person_id;
5995: close get_init_wf_names;
5996:
5997: for i in 1..t_wf_roles.count
5998: loop
5999: CALL_WF(p_supercede_reqid_str,
6000: t_wf_roles(i),
6001: p_SandT_reqid,
5996:
5997: for i in 1..t_wf_roles.count
5998: loop
5999: CALL_WF(p_supercede_reqid_str,
6000: t_wf_roles(i),
6001: p_SandT_reqid,
6002: t_person_id(i),
6003: 'INIT',
6004: l_template_name,
6012: end if;
6013:
6014: if l_frp_notify then
6015: open get_frp_wf_names;
6016: fetch get_frp_wf_names bulk collect into t_wf_roles, t_person_id;
6017: close get_frp_wf_names;
6018:
6019: for i in 1..t_wf_roles.count
6020: loop
6015: open get_frp_wf_names;
6016: fetch get_frp_wf_names bulk collect into t_wf_roles, t_person_id;
6017: close get_frp_wf_names;
6018:
6019: for i in 1..t_wf_roles.count
6020: loop
6021: CALL_WF(p_supercede_reqid_str,
6022: t_wf_roles(i),
6023: p_SandT_reqid,
6018:
6019: for i in 1..t_wf_roles.count
6020: loop
6021: CALL_WF(p_supercede_reqid_str,
6022: t_wf_roles(i),
6023: p_SandT_reqid,
6024: t_person_id(i),
6025: 'FRP',
6026: l_template_name,
6035:
6036:
6037: if l_employee_notify then
6038: open get_per_wf_names;
6039: fetch get_per_wf_names bulk collect into t_wf_roles, t_person_id;
6040: close get_per_wf_names;
6041:
6042: for i in 1..t_wf_roles.count
6043: loop
6038: open get_per_wf_names;
6039: fetch get_per_wf_names bulk collect into t_wf_roles, t_person_id;
6040: close get_per_wf_names;
6041:
6042: for i in 1..t_wf_roles.count
6043: loop
6044: CALL_WF(p_supercede_reqid_str,
6045: t_wf_roles(i),
6046: p_SandT_reqid,
6041:
6042: for i in 1..t_wf_roles.count
6043: loop
6044: CALL_WF(p_supercede_reqid_str,
6045: t_wf_roles(i),
6046: p_SandT_reqid,
6047: t_person_id(i),
6048: 'EMP',
6049: l_template_name,
6058:
6059: hr_utility.trace( 'SandT_Supercede-->send_notifications--> has dynamic part for approver start');
6060: if l_approver_notify then
6061: open get_app_wf_names;
6062: fetch get_app_wf_names bulk collect into t_wf_roles;
6063: close get_app_wf_names;
6064: for i in 1..t_wf_roles.count
6065: loop
6066: CALL_WF(p_supercede_reqid_str,
6060: if l_approver_notify then
6061: open get_app_wf_names;
6062: fetch get_app_wf_names bulk collect into t_wf_roles;
6063: close get_app_wf_names;
6064: for i in 1..t_wf_roles.count
6065: loop
6066: CALL_WF(p_supercede_reqid_str,
6067: t_wf_roles(i),
6068: p_SandT_reqid,
6063: close get_app_wf_names;
6064: for i in 1..t_wf_roles.count
6065: loop
6066: CALL_WF(p_supercede_reqid_str,
6067: t_wf_roles(i),
6068: p_SandT_reqid,
6069: null,
6070: 'APP',
6071: l_template_name,