PACKAGE BODY: APPS.HRI_APL_DGNSTC_CORE

Source

1 PACKAGE BODY hri_apl_dgnstc_core AS
2 /* $Header: hriadgcr.pkb 120.2 2005/11/24 05:22:35 jtitmas noship $ */
3 
4 FUNCTION get_ff_check_sql
5      RETURN VARCHAR2 IS
6 
7   l_sql_stmt  VARCHAR2(32000);
8 
9 BEGIN
10 
11   l_sql_stmt :=
12 'SELECT
13  bg.name               bg_name
14 ,bg.business_group_id  bg_id
15 ,''FFP'' || ff.formula_id || ''_'' || TO_CHAR(ff.effective_start_date, ''DDMMYYYY'')
16                        ff_name
17 FROM
18  ff_formulas_f        ff
19 ,per_business_groups  bg
20 WHERE ff.formula_name = :p_obj_nm
21 AND ff.business_group_id = bg.business_group_id
22 AND TRUNC(SYSDATE) BETWEEN ff.effective_start_date
23                    AND ff.effective_end_date
24 ORDER BY bg.name';
25 
26   RETURN l_sql_stmt;
27 
28 END get_ff_check_sql;
29 
30 FUNCTION get_ff_check_all_sql
31      RETURN VARCHAR2 IS
32 
33   l_sql_stmt  VARCHAR2(32000);
34 
35 BEGIN
36 
37   l_sql_stmt :=
38 'SELECT
39  bg.name               bg_name
40 ,bg.business_group_id  bg_id
41 ,''FFP''||ff.formula_id||''_''||TO_CHAR(ff.effective_start_date, ''DDMMYYYY'')
42                        ff_name
43 FROM
44  ff_formulas_f         ff
45 ,per_business_groups  bg
46 WHERE ff.formula_name (+) = :p_obj_nm
47 AND ff.business_group_id (+) = bg.business_group_id
48 AND trunc(SYSDATE) BETWEEN ff.effective_start_date (+)
49                    AND ff.effective_end_date (+)
50 AND EXISTS
51  (SELECT NULL
52   FROM per_all_assignments_f asg
53   WHERE asg.assignment_type IN (''E'',''C'')
54   AND trunc(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
55   AND asg.business_group_id = bg.business_group_id)
56 ORDER BY bg.name';
57 
58   RETURN l_sql_stmt;
59 
60 END get_ff_check_all_sql;
61 
62 FUNCTION get_alert_sql
63      RETURN VARCHAR2 IS
64 
65 l_sql_stmt  VARCHAR2(32000);
66 
67 BEGIN
68 
69   l_sql_stmt :=
70 'SELECT /*+ INDEX(mthd hri_adm_mthd_actions_u1) */
71  log.note note,
72  hr_bis.bis_decode_lookup(''HRI_PROCESS_STATUS'',log.msg_type) status,
73  bg.name business_group_name,
74  log.effective_date,
75  log.person_id
76 FROM
77  hri_adm_msg_log log,
78  hri_adm_mthd_actions mthd,
79  hr_all_organization_units_tl bg,
80  per_all_people_f pn
81 WHERE log.mthd_action_id = mthd.mthd_action_id
82 AND log.person_id = pn.person_id
83 AND log.effective_date BETWEEN pn.effective_start_date
84                        AND pn.effective_end_date
85 AND bg.organization_id = pn.business_group_id
86 AND bg.language = USERENV(''LANG'')
87 AND log.mthd_action_id =
88  (SELECT max(mthd_action_id)
89   FROM hri_adm_msg_log log1
90   WHERE log1.msg_group = :p_obj_name
91   AND log1.msg_type in (''ERROR'',''WARNING''))
92 AND log.msg_group = :p_obj_name
93 AND NOT EXISTS
94  (SELECT null
95   FROM hri_adm_mthd_actions mthd1
96   WHERE mthd1.process_name = mthd.process_name
97   AND mthd1.mthd_action_id > mthd.mthd_action_id)';
98 
99   RETURN l_sql_stmt;
100 
101 END get_alert_sql;
102 
103 END hri_apl_dgnstc_core;