PACKAGE BODY: APPS.HRI_APL_DGNSTC_LOOKUP

Source

1 PACKAGE BODY hri_apl_dgnstc_lookup AS
2 /* $Header: hriadglk.pkb 120.1 2006/12/05 08:55:38 smohapat noship $ */
3 
4 FUNCTION get_lookup_sql(p_lookup_code  IN VARCHAR2)
5      RETURN VARCHAR2 IS
6 
7   l_sql_stmt  VARCHAR2(32000);
8 
9 BEGIN
10 
11   l_sql_stmt :=
12 'SELECT hr_bis.bis_decode_lookup(''' || p_lookup_code || ''',:p_value)
13  FROM dual';
14 
15   RETURN l_sql_stmt;
16 
17 END get_lookup_sql;
18 
19 FUNCTION get_rate_type
20       RETURN VARCHAR2 IS
21 
22   l_sql_stmt  VARCHAR2(32000);
23 
24 BEGIN
25 
26   l_sql_stmt :=
27 'SELECT user_conversion_type
28 FROM gl_daily_conversion_types
29 WHERE conversion_type = :p_value';
30 
31   RETURN l_sql_stmt;
32 
33 END get_rate_type;
34 
35 FUNCTION get_currency_code
36       RETURN VARCHAR2 IS
37 
38   l_sql_stmt  VARCHAR2(32000);
39 
40 BEGIN
41 
42   l_sql_stmt :=
43 'SELECT name meaning
44 FROM fnd_currencies_active_v
45 WHERE currency_code = :p_value';
46 
47   RETURN l_sql_stmt;
48 
49 END get_currency_code;
50 
51 FUNCTION get_flex_value_set
52       RETURN VARCHAR2 IS
53 
54   l_sql_stmt  VARCHAR2(32000);
55 
56 BEGIN
57 
58   l_sql_stmt :=
59 'SELECT flex_value_set_name
60 FROM fnd_flex_value_sets
61 WHERE flex_value_set_id = :p_value';
62 
63   RETURN l_sql_stmt;
64 
65 END get_flex_value_set;
66 
67 
68 FUNCTION get_org_struct_name
69       RETURN VARCHAR2 IS
70 
71   l_sql_stmt  VARCHAR2(32000);
72 
73 BEGIN
74 
75   l_sql_stmt :=
76 'SELECT name
77 FROM per_organization_structures
78 WHERE organization_structure_id =:p_value';
79 
80   RETURN l_sql_stmt;
81 
82 END get_org_struct_name;
83 
84 
85 FUNCTION validate_bucket_sql(p_bucket_code  IN VARCHAR2)
86        RETURN VARCHAR2 IS
87 
88   l_sql_stmt  VARCHAR2(32000);
89 
90 BEGIN
91 
92   l_sql_stmt :=
93 'SELECT
94  CASE WHEN ((CASE WHEN range1_low  IS NOT NULL OR
95                        range1_high IS NOT NULL
96                   THEN 1 ELSE 0 END) +
97             (CASE WHEN range2_low  IS NOT NULL OR
98                        range2_high IS NOT NULL
99                   THEN 1 ELSE 0 END) +
100             (CASE WHEN range3_low  IS NOT NULL OR
101                        range3_high IS NOT NULL
102                   THEN 1 ELSE 0 END) +
103             (CASE WHEN range4_low  IS NOT NULL OR
104                        range4_high IS NOT NULL
105                  THEN 1 ELSE 0 END) +
106             (CASE WHEN range5_low  IS NOT NULL OR
107                        range5_high IS NOT NULL
108                   THEN 1 ELSE 0 END) +
109             (CASE WHEN range6_low  IS NOT NULL OR
110                        range6_high IS NOT NULL
111                   THEN 1 ELSE 0 END) +
112             (CASE WHEN range7_low  IS NOT NULL OR
113                        range7_high IS NOT NULL
114                  THEN 1 ELSE 0 END) +
115             (CASE WHEN range8_low  IS NOT NULL OR
116                        range8_high IS NOT NULL
117                   THEN 1 ELSE 0 END) +
118             (CASE WHEN range9_low  IS NOT NULL OR
119                        range9_high IS NOT NULL
120                   THEN 1 ELSE 0 END) +
121             (CASE WHEN range10_low  IS NOT NULL OR
122                        range10_high IS NOT NULL
123                   THEN 1 ELSE 0 END)
124           <= 5 )
125       THEN ''Y'' ELSE ''N''
126  END range
127 FROM bis_bucket
128 WHERE short_name = ''' || p_bucket_code || '''';
129 
130   RETURN l_sql_stmt;
131 
132 END validate_bucket_sql;
133 
134 END hri_apl_dgnstc_lookup;