1 package pay_au_sgc_pkg as
2 /* $Header: pyausgc.pkh 120.2 2006/06/05 08:46:21 abhargav ship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** Australia Superannuation Contribution Compliance Report
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 05-DEC-2000 RSINGHAL N/A Created
16 ** 10-JAN-2001 RSINGHAL bug# 1574764 Modifications for Performance issue.
17 ** 07-FEB-2001 RSINGHAL bug# 1560081 Changes in the sql for selecting
18 ** working hours.
19 ** 04-FEB-2002 VGSRINIV Bug# 2197813 Added dbdrv command
20 ** 03-DEC-2002 RAGOVIND Bug# 2689226 Added NOCOPY for the function employee_super_details.
21 ** 29-DEC-2003 APUNEKAR Bug# 2920725 Corrected base tables to support security model
22 ** 09-AUG-2004 ABHKUMAR Bug# 2610141 Legal Employer enhancement changes
23 ** 12-AUG-2004 ABHKUMAR Bug# 2610141 Introduce cursor bal_id_qtd to get the defined balance id of _ASG_LE_QTD
24 ** 05-MAY-2006 KSINGLA Bug# 5143056 Modified check for legislation code in cursors bal_id_mtd and bal_id_qtd
25 ** 05-Jun-2006 abhargav Bug# 5244510 Modified cursor bal and assign_work_hrs so that report would process
26 terminated employee also which was terminated mid of the month.
27 */
28
29 -------------------------------------------------------------------------------
30
31 g_monthly_threshold number;
32 g_age number;
33 g_age_min number;
34 g_min_hrs_worked number;
35 g_qtd_threshold number;
36 g_super_guarantee_bal_id_mtd pay_defined_balances.defined_balance_id%type; --2610141
37 g_end_date hr_organization_information.org_information10%type;
38
39
40 ---------------------Cursor to select balance id and superable salary ----------------
41 /*Bug 2610141 modfied to return on the defined balance id*/
42
43 cursor bal (c_business_group_id per_all_people_f.business_group_id%type,
44 c_assignment_id per_all_assignments_f.assignment_id%type,
45 c_effective_date date) is
46 select hoi.org_information8
47 from
48 hr_organization_information hoi,
49 hr_organization_units hou,
50 pay_personal_payment_methods_f pppm
51 where
52 hoi.org_information_context='AU_SUPER_FUND'
53 and hoi.organization_id=hou.organization_id
54 and pppm.payee_id=hoi.organization_id
55 and c_effective_date between pppm.effective_start_date and last_day(pppm.effective_end_date) /* Bug#5244510 */
56 and (c_effective_date between to_date(hoi.org_information9,'yyyy/mm/dd hh24:mi:ss') and
57 nvl(to_date(hoi.org_information10,'yyyy/mm/dd hh24:mi:ss'),
58 to_date(g_end_date,'yyyy/mm/dd hh24:mi:ss')))
59 and hou.business_group_id=c_business_group_id
60 and pppm.assignment_id=c_assignment_id
61 group by hoi.org_information8
62 order by 1 ;
63
64
65 /*Bug 2610141 Cursor to get the balance value for a particular defined balance id*/
66 /* Bug 5143056 Modified check for legislation code from pay_balance_types put nvl*/
67
68 cursor bal_id_mtd(c_balance_type_id pay_balance_types.balance_type_id%type,
69 c_legislation_code pay_balance_types.legislation_code%type)
70 is
71 select pdb.defined_balance_id
72 FROM pay_balance_types pbt,
73 pay_balance_dimensions pbd,
74 pay_defined_balances pdb
75 where pbt.balance_type_id=c_balance_type_id
76 AND nvl(pbt.legislation_code,'AU')=c_legislation_code
77 AND pbd.database_item_suffix = '_ASG_LE_MTD'
78 AND pbt.balance_type_id = pdb.balance_type_id
79 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
80
81
82 /*Bug 2610141 - Portion added to get the defined balance id for _ASG_LE_QTD*/
83 /* Bug 5143056 Modified check for legislation code from pay_balance_types put nvl*/
84 cursor bal_id_qtd(c_balance_type_id pay_balance_types.balance_type_id%type,
85 c_legislation_code pay_balance_types.legislation_code%type)
86 is
87 select pdb.defined_balance_id
88 FROM pay_balance_types pbt,
89 pay_balance_dimensions pbd,
90 pay_defined_balances pdb
91 where pbt.balance_type_id=c_balance_type_id
92 AND nvl(pbt.legislation_code,'AU')=c_legislation_code
93 AND pbd.database_item_suffix = '_ASG_LE_QTD'
94 AND pbt.balance_type_id = pdb.balance_type_id
95 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
96
97
98 ---------------cursor to select working hours ------------------------
99 /* obsolete as per bug 1560081
100 Cursor assign_work_hrs (c_effective_date date,
101 c_assignment_id per_all_assignments_f.assignment_id%type)
102 is
103 select assign.normal_hours from
104 per_assignments_f assign,
105 hr_lookups hr3
106 where c_effective_date between assign.effective_start_date and
107 assign.effective_end_date and
108 assign.assignment_id = c_assignment_id and
109 hr3.application_id (+)= 800 and
110 hr3.lookup_code (+)= assign.frequency and
111 hr3.lookup_type (+)= 'FREQUENCY';
112 */
113
114 /* new one defined to check first at SCL level,
115 ** then at Assignment level,
116 ** at BG level,
117 ** if value is null at all the above three then default it to super_min_hrs +1
118 */
119 /*Bug2920725 Corrected base tables to support security model*/
120
121 CURSOR assign_work_hrs
122 (c_effective_date date,
123 c_assignment_id per_assignments_f.assignment_id%TYPE,
124 c_business_group_id per_all_assignments_f.business_group_id%TYPE,
125 c_registered_employer NUMBER -- 2610141
126 )
127 is
128 select nvl(nvl(hsck.segment4,nvl(
129 decode(paa.frequency,'W',paa.normal_hours,null),
130 decode(hoi.org_information4,'W',hoi.org_information3,
131 null)
132 )),g_min_hrs_worked + 1)
133 from
134 per_assignments_f paa,
135 hr_soft_coding_keyflex hsck,
136 hr_organization_information hoi,
137 hr_organization_units hou
138 where
139 hoi.org_information_context='Work Day Information' and
140 hoi.organization_id=hou.organization_id and
141 paa.business_group_id=hou.business_group_id and
142 paa.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
143 and hsck.segment1 = c_registered_employer -- 2610141
144 and c_effective_date between paa.effective_start_date and
145 last_day(paa.effective_end_date) /* Bug#5244510 */
146 and paa.assignment_id=c_assignment_id
147 and paa.business_group_id=c_business_group_id;
148
149 --------------------------procedure global_super_values-----------------------------
150
151 procedure global_super_values(
152 p_effective_date in date,
153 p_legislation_code in pay_balance_types.legislation_code%type
154 );
155
156
157 -------------------------------procedure employee_super_details------------------------
158
159 procedure employee_super_details
160 (p_assignment_id in per_all_assignments_f.assignment_id%type,
161 p_registered_employer NUMBER, -- 2610141
162 p_employee_age in number,
163 p_effective_date in date,
164 p_sgc_rate in number,
165 p_business_group_id in per_all_people_f.business_group_id%type,
166 p_legislation_code in pay_balance_types.legislation_code%type,--2610141
167 p_superable_sal out NOCOPY number,
168 p_sgc_contribution out NOCOPY number,
169 p_compliance_mesg out NOCOPY varchar2,
170 p_warning_mesg out NOCOPY varchar2
171 );
172
173 ----------------------------function compliance_mesg-----------------------------------
174
175 function compliance_mesg(
176 p_assignment_id in per_all_assignments_f.assignment_id%type,
177 p_employee_age in number,
178 p_effective_date in date,
179 p_sgc_rate in number,
180 p_business_group_id in per_all_people_f.business_group_id%type,
181 p_registered_employer NUMBER, -- 2610141
182 p_legislation_code in pay_balance_types.legislation_code%type--2610141
183 )
184 return varchar2;
185
186 END pay_au_sgc_pkg ;
[Home] [Help]