1 PACKAGE BODY paywsfgt_pkg AS
2 -- $Header: pyfgtpkg.pkb 115.4 2002/12/10 18:44:45 dsaxby noship $
3 --
4 -- +---------------------------------------------------------------------------+
5 -- | Global Constants |
6 -- +---------------------------------------------------------------------------+
7 -- The end-of-line character to use in generated PL/SQL
8 g_eol CONSTANT VARCHAR2(10) := fnd_global.newline;
9 --
10 -- +---------------------------------------------------------------------------+
11 -- | NAME : write_fgt_check |
12 -- | SCOPE : PUBLIC |
13 -- | DESCRIPTION: See header |
14 -- +---------------------------------------------------------------------------+
15 PROCEDURE write_fgt_check(
16 p_id IN NUMBER,
17 p_sql IN OUT NOCOPY VARCHAR2,
18 p_has_bus IN BOOLEAN,
19 p_has_pay IN BOOLEAN
20 ) IS
21 BEGIN
22 p_sql := p_sql||' /* Is the trigger in an enabled functional area */';
23 p_sql := p_sql||g_eol;
24 --
25 p_sql := p_sql||' IF paywsfgt_pkg.trigger_is_not_enabled('||g_eol;
26 --
27 p_sql := p_sql||' p_event_id => '||TO_CHAR(p_id)||','||g_eol;
28 --
29 p_sql := p_sql||' p_legislation_code => ';
30 IF p_has_bus THEN
31 p_sql := p_sql||'l_legislation_code,'||g_eol;
32 ELSE
33 p_sql := p_sql||'NULL,'||g_eol;
34 END IF;
35 --
36 p_sql := p_sql||' p_business_group_id => ';
37 IF p_has_bus THEN
38 p_sql := p_sql||'l_business_group_id,'||g_eol;
39 ELSE
40 p_sql := p_sql||'NULL,'||g_eol;
41 END IF;
42 --
43 p_sql := p_sql||' p_payroll_id => ';
44 IF p_has_pay THEN
45 p_sql := p_sql||'l_payroll_id'||g_eol;
46 ELSE
47 p_sql := p_sql||'NULL'||g_eol;
48 END IF;
49 --
50 p_sql := p_sql||' ) THEN'||g_eol;
51 p_sql := p_sql||' RETURN;'||g_eol;
52 p_sql := p_sql||' END IF;'||g_eol;
53 p_sql := p_sql||' --'||g_eol;
54 --
55 END write_fgt_check;
56 --
57 -- +---------------------------------------------------------------------------+
58 -- | NAME : trigger_is_not_enabled |
59 -- | SCOPE : PUBLIC |
60 -- | DESCRIPTION: See header |
61 -- +---------------------------------------------------------------------------+
62 FUNCTION trigger_is_not_enabled(
63 p_event_id IN NUMBER,
64 p_legislation_code IN VARCHAR2 DEFAULT NULL,
65 p_business_group_id IN NUMBER DEFAULT NULL,
66 p_payroll_id IN NUMBER DEFAULT NULL
67 ) RETURN BOOLEAN IS
68 --
69 CURSOR csr_in_area(cp_id IN NUMBER) IS
70 SELECT 'Y'
71 FROM pay_functional_triggers
72 WHERE event_id = cp_id;
73 --
74 CURSOR csr_leg_enb(
75 cp_id IN NUMBER,
76 cp_leg IN VARCHAR2
77 ) IS
78 SELECT 'Y'
79 FROM pay_functional_usages pfu,
80 pay_functional_areas pfa,
81 pay_functional_triggers pft
82 WHERE pfu.legislation_code IN (cp_leg,'-1')
83 AND pfu.area_id = pfa.area_id
84 AND pfa.area_id = pft.area_id
85 AND pft.event_id = cp_id;
86 --
87 CURSOR csr_bus_enb(
88 cp_id IN NUMBER,
89 cp_bus IN NUMBER
90 ) IS
91 SELECT 'Y'
92 FROM pay_functional_usages pfu,
93 pay_functional_areas pfa,
94 pay_functional_triggers pft
95 WHERE pfu.business_group_id IN (cp_bus,-1)
96 AND pfu.area_id = pfa.area_id
97 AND pfa.area_id = pft.area_id
98 AND pft.event_id = cp_id;
99 --
100 CURSOR csr_pay_enb(
101 cp_id IN NUMBER,
102 cp_pay IN VARCHAR2
103 ) IS
104 SELECT 'Y'
105 FROM pay_functional_usages pfu,
106 pay_functional_areas pfa,
107 pay_functional_triggers pft
108 WHERE pfu.payroll_id IN (cp_pay,-1)
109 AND pfu.area_id = pfa.area_id
110 AND pfa.area_id = pft.area_id
111 AND pft.event_id = cp_id;
112 --
113 l_execute BOOLEAN;
114 l_exists VARCHAR2(1);
115 --
116 BEGIN
117 --
118 -- If the trigger is not in any functional area then execute the code
119 --
120 OPEN csr_in_area(p_event_id);
121 FETCH csr_in_area INTO l_exists;
122 IF csr_in_area%NOTFOUND THEN
123 l_exists := 'N';
124 END IF;
125 CLOSE csr_in_area;
126 IF l_exists = 'N' THEN
127 hr_utility.trace('Trigger not in any area, execute code');
128 l_execute := TRUE;
129 ELSE
130 --
131 -- Otherwise do not execute the trigger code by default
132 --
133 l_execute := FALSE;
134 --
135 -- If the trigger is firing for a specific legislation and belongs to a functional
136 -- area that is enabled for all legislations or the current specific legislation
137 -- then the code should be executed
138 --
139 IF p_legislation_code IS NOT NULL THEN
140 OPEN csr_leg_enb(p_event_id,p_legislation_code);
141 FETCH csr_leg_enb INTO l_exists;
142 IF csr_leg_enb%NOTFOUND THEN
143 l_exists := 'N';
144 END IF;
145 CLOSE csr_leg_enb;
146 IF l_exists = 'Y' THEN
147 hr_utility.trace('Trigger enabled by legislation, execute code');
148 l_execute := TRUE;
149 END IF;
150 END IF;
151 --
152 -- If we have not already decided to execute the trigger code and
153 -- the trigger is firing for a specific business group and belongs to a functional
154 -- area that is enabled for all business groups or the current specific business group
155 -- then the code should be executed
156 --
157 IF l_execute = FALSE AND p_business_group_id IS NOT NULL THEN
158 OPEN csr_bus_enb(p_event_id,p_business_group_id);
159 FETCH csr_bus_enb INTO l_exists;
160 IF csr_bus_enb%NOTFOUND THEN
161 l_exists := 'N';
162 END IF;
163 CLOSE csr_bus_enb;
164 IF l_exists = 'Y' THEN
165 hr_utility.trace('Trigger enabled by business group, execute code');
166 l_execute := TRUE;
167 END IF;
168 END IF;
169 --
170 -- If we have not already decided to execute the trigger code and
171 -- the trigger is firing for a specific payroll and belongs to a functional
172 -- area that is enabled for all payrolls or the current specific payroll
173 -- then the code should be executed
174 --
175 IF l_execute = FALSE AND p_payroll_id IS NOT NULL THEN
176 OPEN csr_pay_enb(p_event_id,p_payroll_id);
177 FETCH csr_pay_enb INTO l_exists;
178 IF csr_pay_enb%NOTFOUND THEN
179 l_exists := 'N';
180 END IF;
181 CLOSE csr_pay_enb;
182 IF l_exists = 'Y' THEN
183 hr_utility.trace('Trigger enabled by payroll, execute code');
184 l_execute := TRUE;
185 END IF;
186 END IF;
187 --
188 END IF;
189 --
190 -- Logic works out if trigger _SHOULD_ execute, so reverse it before returning
191 --
192 RETURN (l_execute = FALSE);
193 --
194 END trigger_is_not_enabled;
195 --
196 END paywsfgt_pkg;