1 PACKAGE pay_ip_employee_balances AUTHID CURRENT_USER AS
2 /* $Header: payipempbalance.pkh 120.2.12020000.1 2012/08/29 05:48:13 sjawid noship $ */
3
4 /******************************************************************************
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_ip_employee_balances
22
23 Description : The package is used by the Employee Balances SS Page
24 to fetch the earnings and deduction balances.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ ------- --------------------------
30 31-Jan-2012 vvijayku 120.0 Created.
31 15-Mar-2012 vvijayku 120.1 13827477 Added a new Function definition
32 for validate_assignment.
33 02-Apr-2012 vvijayku 120.2 13902938 Removed unnecessary columns in the
34 pl/sql tables.
35 ******************************************************************************/
36
37 /******************************************************************************
38 * This package is used by the Employee Balances form to get the value of
39 * balances for different earnings and Non-Tax deduction elements.The
40 * procedure defined inside this package fetches the balance values and
41 * populates a PL/SQL table.The PL/SQL tables are then passed to the form and
42 * the values in these tables are displayed in the form.
43 ******************************************************************************/
44
45 -- Record to store earning elements
46 TYPE earn_rec IS RECORD
47 (element_name pay_element_types_f.element_name%TYPE
48 ,element_type_id pay_element_types_f.element_type_id%TYPE
49 ,classification_id pay_element_types_f.classification_id%TYPE
50 ,element_information10 pay_element_types_f.element_information10%TYPE);
51
52 -- Record to store deduction elements
53 TYPE dedn_rec IS RECORD
54 (element_name pay_element_types_f.element_name%TYPE
55 ,element_type_id pay_element_types_f.element_type_id%TYPE
56 ,classification_id pay_element_types_f.classification_id%TYPE
57 ,element_information10 pay_element_types_f.element_information10%TYPE
58 ,element_information11 pay_element_types_f.element_information11%TYPE
59 ,element_information13 pay_element_types_f.element_information13%TYPE);
60
61
62 -- Declare tables for records
63 TYPE earn_tbl IS TABLE OF
64 earn_rec INDEX BY BINARY_INTEGER;
65
66 TYPE dedn_tbl IS TABLE OF
67 dedn_rec INDEX BY BINARY_INTEGER;
68
69 TYPE rec_entity_details IS RECORD
70 ( ele_name pay_element_types_f.element_name%TYPE,
71 ele_type_id pay_element_types_f.element_type_id%TYPE,
72 def_bal_id pay_defined_balances.defined_balance_id%TYPE,
73 bal_value NUMBER,
74 bal_type_id pay_balance_types.balance_type_id%TYPE,
75 bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
76 defbal_type VARCHAR2(20));
77
78 TYPE entity_details_tab IS TABLE OF rec_entity_details INDEX BY BINARY_INTEGER;
79 t_defbal_details_tab entity_details_tab;
80
81 TYPE ele_bal_details_rec IS RECORD
82 ( ele_name pay_element_types_f.element_name%TYPE,
83 ele_type_id pay_element_types_f.element_type_id%TYPE,
84 bal_type_id pay_balance_types.balance_type_id%TYPE,
85 ptd_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
86 ptd_bal_value NUMBER,
87 mtd_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
88 mtd_bal_value NUMBER,
89 qtd_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
90 qtd_bal_value NUMBER,
91 ytd_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
92 ytd_bal_value NUMBER,
93 arr_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
94 arr_bal_value NUMBER,
95 acc_bal_dim_id pay_balance_dimensions.balance_dimension_id%TYPE,
96 acc_bal_value NUMBER);
97
98 TYPE ele_bal_details_tab IS TABLE OF ele_bal_details_rec INDEX BY BINARY_INTEGER;
99 t_ele_bal_horizontal_tab ele_bal_details_tab;
100
101 /******************************************************************************
102 Name : purge_old_data
103 Purpose : This procedure will purge the old balance data from the table
104 pay_us_rpt_totals
105 ******************************************************************************/
106 PROCEDURE purge_old_data (p_purge varchar2);
107
108 /******************************************************************************
109 * Name : validate_assignment
110 * Purpose : This function is used to check whether the assignment is
111 * valid as on the effective date passed.
112 ******************************************************************************/
113 FUNCTION validate_assignment(p_assignment_id number, p_effective_date varchar2, p_business_group_id number)
114 RETURN varchar2 ;
115
116 /******************************************************************************
117 * Name : populate_element_info
118 * Purpose : This procedure fetches the elements for which the balances are to
119 * be retrieved.It then finds out the corresponding balance values and
120 * stores them in a PL/SQL table which is then inserted into the table
121 * pay_us_rpt_totals. The View Employee Balance SS Page then fetches
122 * and displays the balances values from pay_us_rpt_totals
123 ******************************************************************************/
124 PROCEDURE populate_element_info( p_assignment_id in number,
125 p_assignment_action_id in number,
126 p_classification_name in pay_element_classifications.classification_name%TYPE,
127 p_session_date in pay_element_types_f.effective_start_date%TYPE,
128 p_tax_unit_id in number,
129 p_per_month in number,
130 p_per_qtd in number,
131 p_per_ytd in number,
132 p_asg_ptd in number,
133 p_asg_month in number,
134 p_asg_qtd in number,
135 p_asg_ytd in number,
136 p_asg_arr_itd in number,
137 p_asg_acc_itd in number,
138 p_business_group_id in pay_element_types_f.business_group_id%TYPE,
139 p_balance_level in varchar2
140 );
141
142 END pay_ip_employee_balances;
143
[Home] [Help]