1 PACKAGE pay_sg_deductions AUTHID CURRENT_USER AS
2 /* $Header: pysgdedn.pkh 120.2 2010/11/25 06:36:18 jalin noship $
3 **
4 ** Copyright (c) 2002 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Procedures and functions used in SG deduction formula
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** =========== ======== ========= =====================
14 ** 26 Jun 2000 makelly 115.0 Initial
15 ** 23 Mar 2002 Ragovind 115.1 Added sg_get_prorator declaration
16 ** 28 Jun 2002 SRussell 115.3 Add functions for CPF Retropay
17 ** 02 Sep 2002 Ragovind 115.4 Added get_prev_year_ord_ytd declaration
18 ** 11 Dec 2002 Apunekar 115.5 Added nocopy to out or in out parameters
19 ** 20 Dec 2002 Ragovind 115.6 Added CPF Report Coding. Bug#2475324
20 ** 11 Feb 2003 Ragovind 115.7 Modified the CPF Report code for Correct CPF for Terminated Employees.Bug#2796093
21 ** 21 Jan 2004 agore 115.8 Added new function get_cur_year_ord_ytd ( )
22 ** 18 May 2004 Nanuradh 115.9 Added new function spl_amount( ) to calculate S Pass Levy.
23 ** 22 Feb 2008 Jalin 115.10 Removed parameter ass_act_id from get_retro_earnings function
24 ** 25 Nov 2010 jalin 115.11 Added new parameter p_cpf_pro_method to
25 ** sg_get_prorator function, bug 9112449
26 ** ============== Formula Fuctions ====================
27 ** Package containing addition processing required by
28 ** formula in SG localisation
29 */
30
31 /* Global Values used for the CPF Report bugno:2475324*/
32 -- variable to decide whether formula SG_STAT is called from the
33 -- report PAYSGCPF or from from the payroll.This is defaulted to Payroll
34 g_sgstat_called_from varchar2(7) ;
35
36 -- If formula SG_STAT is called from the report then
37 -- below variable will have value as last date of the
38 -- called year
39 g_year_end_date_for_cpf_report date;
40
41 -- inputs for the SG_STAT formula
42 g_inputs ff_exec.inputs_t;
43
44 -- outputs for the SG_STAT formula
45 g_outputs ff_exec.outputs_t;
46
47 /* Sturucture to declare the cpf_calc_inputs */
48 type cpf_calc_inputs is record
49 (
50 person_id number, -- stores person_id
51 cpf_diff number -- difference between cpf paid (values of the balances) and calculate cpf via SG_STAT with SAEOY
52 );
53 type cpf_inputs_table is table of cpf_calc_inputs index by binary_integer;
54
55 cpf_inputs_t cpf_inputs_table;
56
57 /* declaration for bugno:2475324 ends */
58
59 /*
60 ** wp_days_in_month - returns the number of days in a month
61 ** that an employee has a valid work permit
62 */
63
64 function fwl_amount ( p_business_group_id in number
65 , p_date_earned in date
66 , p_assignment_id in number
67 , p_start_date in date
68 , p_end_date in date )
69 return number;
70
71 /* Bug: 3595103 - New function to calculate S Pass Levy */
72 function spl_amount ( p_business_group_id in number
73 , p_date_earned in date
74 , p_assignment_id in number
75 , p_start_date in date
76 , p_end_date in date )
77 return number;
78
79 function sg_get_prorator ( p_assignment_id in number
80 , p_date_earned in date
81 , p_pay_proc_start_date in date
82 , p_pay_proc_end_date in date
83 , p_wac in varchar2
84 , p_cpf_pro_method in varchar2
85 , p_cpf_calc_type out nocopy varchar2
86 )
87 return number ;
88
89 function check_if_retro
90 (
91 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
92 p_date_earned in pay_payroll_actions.date_earned%TYPE
93 ) return varchar2;
94
95 function which_retro_method
96 (
97 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
98 p_date_earned in pay_payroll_actions.date_earned%TYPE,
99 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE
100 ) return varchar2;
101
102 function earnings_type
103 (
104 p_element_type_id in pay_element_types_f.element_type_id%TYPE
105 ) return varchar2;
106
107 function get_prev_year_ord_ytd
108 (
109 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
110 p_date_earned in pay_payroll_actions.date_earned%TYPE
111 ) return number;
112 -----------------------------------------
113 -- Added for Bug# 3279235
114 -----------------------------------------
115 function get_cur_year_ord_ytd
116 (
117 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
118 p_date_earned in pay_payroll_actions.date_earned%TYPE
119 )
120 return number;
121
122 function get_retro_earnings( p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
123 p_date_earned in date )
124 return number;
125
126 /* declarations for PAYSGCPF report bugno:2475324*/
127
128 /* Below procedure will initialize all the contexts required for SG_STAT*/
129
130 Procedure init_formula (p_formula_name in varchar2, p_effective_date in date);
131
132 /* Below function will calculate CPF aditional earnings YTD for the given assignment
133 using SAEOY as cpf calculation method*/
134
135 function calc_cpf_add_YTD (p_date_earned in date
136 ,p_assignment_id in number
137 ,p_process_type in varchar2
138 ,p_tax_unit_id in number
139 ,p_asg_action_id in number
140 ,p_business_group_id in number
141 ,p_payroll_action_id in number
142 ,p_payroll_id in number
143 ,p_balance_date in date
144 )return number;
145
146 /* Returns whether the SG_STAT is called from the REPORT or PAYROLL Run*/
147
148 function get_SG_STAT_CALLED_FROM return varchar2;
149
150 /* In the before report trigger of PAYSGCPF the global g_sgstat_called_from
151 is set to REPORT*/
152 procedure set_SG_STAT_CALLED_FROM (p_running in varchar2);
153
154 /* Populates the pl/sql table with assignment id and difference of CPF paid (values of the balances)
155 and calculated CPF from SG_STAT with SAEOY*/
156 procedure populate_cpf_table (p_person_id in number,
157 p_cpf_diff number );
158
159 /* If the assignment exists in the cpf pl/sql table (populated by populate_cpf_table)
160 ,return 1 else 0. Used in the where clause of the report query*/
161 function get_assignment_from_cpf_table(p_person_id in number) return number;
162
163 /* Get the overpaid value for the assignment passed from the pl/sql table populated by
164 populate_cpf_table above*/
165 function get_cpf_difference(p_person_id in number) return number;
166
167
168 /* set the g_year_end_date_for_cpf_report as the last date of the year*/
169 procedure set_year_end_date(p_year_end_date in date);
170
171 /* Return last date of the year (stored in g_year_end_date_for_cpf_report)
172 , used in the SG_STAT*/
173 function GET_YEAR_END_DATE return date;
174
175 end pay_sg_deductions;
[Home] [Help]