1 PACKAGE BODY pay_ip_route_support AS
2 /* $Header: pyiprous.pkb 115.1 2002/09/18 12:28:44 jahobbs noship $ */
3 --
4 --
5 -- --------------------------------------------------------------------------
6 -- This returns the start date of the tax year relative to the effective
7 -- date.
8 -- --------------------------------------------------------------------------
9 --
10 FUNCTION tax_year
11 (p_business_group_id NUMBER
12 ,p_effective_date DATE) RETURN DATE IS
13 --
14 -- Get the start date of the tax year in the same year as the effective
15 -- date NB. the start date for the tax year is held in a legislation rule
16 -- ('L') in the format DD/MM.
17 --
18 CURSOR csr_base_tax_year(p_business_group_id NUMBER, p_effective_date DATE) IS
19 SELECT TO_DATE(ru.rule_mode || '/' || TO_CHAR(p_effective_date, 'YYYY'), 'DD/MM/YYYY')
20 FROM per_business_groups bg
21 ,pay_legislation_rules ru
22 WHERE bg.business_group_id = p_business_group_id
23 AND ru.legislation_code = bg.legislation_code
24 AND ru.rule_type = 'L';
25 --
26 --
27 -- Local variables.
28 --
29 l_tax_yr DATE;
30 BEGIN
31 --
32 --
33 -- Fetch the start date of the tax year.
34 --
35 OPEN csr_base_tax_year(p_business_group_id, p_effective_date);
36 FETCH csr_base_tax_year INTO l_tax_yr;
37 CLOSE csr_base_tax_year;
38 --
39 --
40 -- The effective date is AFTER the tax year start date so it is valid.
41 --
42 IF p_effective_date >= l_tax_yr THEN
43 RETURN l_tax_yr;
44 --
45 --
46 -- The effective date is BEFORE the tax year start date so go back to the
47 -- previous tax year start date.
48 --
49 ELSE
50 RETURN ADD_MONTHS(l_tax_yr, -12);
51 END IF;
52 END tax_year;
53 --
54 --
55 -- --------------------------------------------------------------------------
56 -- This returns the start date of the tax quarter relative to the effective
57 -- date.
58 -- --------------------------------------------------------------------------
59 --
60 FUNCTION tax_quarter
61 (p_business_group_id NUMBER
62 ,p_effective_date DATE) RETURN DATE IS
63 --
64 --
65 -- Local variables.
66 --
67 l_tax_yr DATE;
68 BEGIN
69 --
70 --
71 -- Find the start of the tax year.
72 --
73 l_tax_yr := tax_year(p_business_group_id, p_effective_date);
74 --
75 --
76 -- Find the closest tax quarter start date to the effective date.
77 --
78 RETURN ADD_MONTHS(l_tax_yr, FLOOR(MONTHS_BETWEEN(p_effective_date, l_tax_yr) / 3) * 3);
79 END tax_quarter;
80 --
81 --
82 -- --------------------------------------------------------------------------
83 -- This returns the start date of the fiscal year relative to the effective
84 -- date.
85 -- --------------------------------------------------------------------------
86 --
87 FUNCTION fiscal_year
88 (p_business_group_id NUMBER
89 ,p_effective_date DATE) RETURN DATE IS
90 --
91 -- Get the start date of the fiscal year in the same year as the effective
92 -- date NB. the start date for the fiscal year is held against the business
93 -- group in canonical format.
94 --
95 CURSOR csr_base_fiscal_year(p_business_group_id NUMBER, p_effective_date DATE) IS
96 SELECT TO_DATE(TO_CHAR(fnd_date.canonical_to_date(hoi.org_information11), 'DD/MM/')
97 || TO_CHAR(p_effective_date, 'YYYY'), 'DD/MM/YYYY')
98 FROM hr_organization_information HOI
99 WHERE UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
100 AND hoi.organization_id = p_business_group_id;
101 --
102 --
103 -- Local variables.
104 --
105 l_fis_yr DATE;
106 BEGIN
107 --
108 --
109 -- Fetch the start date of the fiscal year.
110 --
111 OPEN csr_base_fiscal_year(p_business_group_id, p_effective_date);
112 FETCH csr_base_fiscal_year INTO l_fis_yr;
113 CLOSE csr_base_fiscal_year;
114 --
115 --
116 -- The effective date is AFTER the fiscal year start date so it is valid.
117 --
118 IF p_effective_date >= l_fis_yr THEN
119 RETURN l_fis_yr;
120 --
121 --
122 -- The effective date is BEFORE the fiscal year start date so go back to the
123 -- previous fis year start date.
124 --
125 ELSE
126 RETURN ADD_MONTHS(l_fis_yr, -12);
127 END IF;
128 END fiscal_year;
129 --
130 --
131 -- --------------------------------------------------------------------------
132 -- This returns the start date of the fiscal quarter relative to the effective
133 -- date.
134 -- --------------------------------------------------------------------------
135 --
136 FUNCTION fiscal_quarter
137 (p_business_group_id NUMBER
138 ,p_effective_date DATE) RETURN DATE is
139 --
140 --
141 -- Local variables.
142 --
143 l_fis_yr DATE;
144 BEGIN
145 --
146 --
147 -- Find the start of the fiscal year.
148 --
149 l_fis_yr := fiscal_year(p_business_group_id, p_effective_date);
150 --
151 --
152 -- Find the closest fiscal quarter start date to the effective date.
153 --
154 RETURN ADD_MONTHS(l_fis_yr, FLOOR(MONTHS_BETWEEN(p_effective_date, l_fis_yr) / 3) * 3);
155 END fiscal_quarter;
156 END pay_ip_route_support;
[Home] [Help]