1 PACKAGE pay_sg_deductions AS
2 /* $Header: pysgdedn.pkh 120.0.12010000.2 2008/08/06 08:22:03 ubhat ship $
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 ** ============== Formula Fuctions ====================
25 ** Package containing addition processing required by
26 ** formula in SG localisation
27 */
28
29 /* Global Values used for the CPF Report bugno:2475324*/
30 -- variable to decide whether formula SG_STAT is called from the
31 -- report PAYSGCPF or from from the payroll.This is defaulted to Payroll
32 g_sgstat_called_from varchar2(7) ;
33
34 -- If formula SG_STAT is called from the report then
35 -- below variable will have value as last date of the
36 -- called year
37 g_year_end_date_for_cpf_report date;
38
39 -- inputs for the SG_STAT formula
40 g_inputs ff_exec.inputs_t;
41
42 -- outputs for the SG_STAT formula
43 g_outputs ff_exec.outputs_t;
44
45 /* Sturucture to declare the cpf_calc_inputs */
46 type cpf_calc_inputs is record
47 (
48 person_id number, -- stores person_id
49 cpf_diff number -- difference between cpf paid (values of the balances) and calculate cpf via SG_STAT with SAEOY
50 );
51 type cpf_inputs_table is table of cpf_calc_inputs index by binary_integer;
52
53 cpf_inputs_t cpf_inputs_table;
54
55 /* declaration for bugno:2475324 ends */
56
57 /*
58 ** wp_days_in_month - returns the number of days in a month
59 ** that an employee has a valid work permit
60 */
61
62 function fwl_amount ( p_business_group_id in number
63 , p_date_earned in date
64 , p_assignment_id in number
65 , p_start_date in date
66 , p_end_date in date )
67 return number;
68
69 /* Bug: 3595103 - New function to calculate S Pass Levy */
70 function spl_amount ( p_business_group_id in number
71 , p_date_earned in date
72 , p_assignment_id in number
73 , p_start_date in date
74 , p_end_date in date )
75 return number;
76
77 function sg_get_prorator ( p_assignment_id in number,
78 p_date_earned in date,
79 p_pay_proc_start_date in date,
80 p_pay_proc_end_date in date,
81 p_wac in varchar2,
82 p_cpf_calc_type out nocopy varchar2
83 )
84 return number ;
85
86 function check_if_retro
87 (
88 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
89 p_date_earned in pay_payroll_actions.date_earned%TYPE
90 ) return varchar2;
91
92 function which_retro_method
93 (
94 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
95 p_date_earned in pay_payroll_actions.date_earned%TYPE,
96 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE
97 ) return varchar2;
98
99 function earnings_type
100 (
101 p_element_type_id in pay_element_types_f.element_type_id%TYPE
102 ) return varchar2;
103
104 function get_prev_year_ord_ytd
105 (
106 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
107 p_date_earned in pay_payroll_actions.date_earned%TYPE
108 ) return number;
109 -----------------------------------------
110 -- Added for Bug# 3279235
111 -----------------------------------------
112 function get_cur_year_ord_ytd
113 (
114 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
115 p_date_earned in pay_payroll_actions.date_earned%TYPE
116 )
117 return number;
118
119 function get_retro_earnings( p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
120 p_date_earned in date )
121 return number;
122
123 /* declarations for PAYSGCPF report bugno:2475324*/
124
125 /* Below procedure will initialize all the contexts required for SG_STAT*/
126
127 Procedure init_formula (p_formula_name in varchar2, p_effective_date in date);
128
129 /* Below function will calculate CPF aditional earnings YTD for the given assignment
130 using SAEOY as cpf calculation method*/
131
132 function calc_cpf_add_YTD (p_date_earned in date
133 ,p_assignment_id in number
134 ,p_process_type in varchar2
135 ,p_tax_unit_id in number
136 ,p_asg_action_id in number
137 ,p_business_group_id in number
138 ,p_payroll_action_id in number
139 ,p_payroll_id in number
140 ,p_balance_date in date
141 )return number;
142
143 /* Returns whether the SG_STAT is called from the REPORT or PAYROLL Run*/
144
145 function get_SG_STAT_CALLED_FROM return varchar2;
146
147 /* In the before report trigger of PAYSGCPF the global g_sgstat_called_from
148 is set to REPORT*/
149 procedure set_SG_STAT_CALLED_FROM (p_running in varchar2);
150
151 /* Populates the pl/sql table with assignment id and difference of CPF paid (values of the balances)
152 and calculated CPF from SG_STAT with SAEOY*/
153 procedure populate_cpf_table (p_person_id in number,
154 p_cpf_diff number );
155
156 /* If the assignment exists in the cpf pl/sql table (populated by populate_cpf_table)
157 ,return 1 else 0. Used in the where clause of the report query*/
158 function get_assignment_from_cpf_table(p_person_id in number) return number;
159
160 /* Get the overpaid value for the assignment passed from the pl/sql table populated by
161 populate_cpf_table above*/
162 function get_cpf_difference(p_person_id in number) return number;
163
164
165 /* set the g_year_end_date_for_cpf_report as the last date of the year*/
166 procedure set_year_end_date(p_year_end_date in date);
167
168 /* Return last date of the year (stored in g_year_end_date_for_cpf_report)
169 , used in the SG_STAT*/
170 function GET_YEAR_END_DATE return date;
171
172
173 end pay_sg_deductions;
[Home] [Help]