1 package body pay_payslip_util as
2 /* $Header: paypaysliputil.pkb 120.1.12000000.1 2007/01/17 14:29:24 appldev noship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description: This package is used for all functions and procedures
26 for Online Payslip for all legislations.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ----- ------- -----------------------------------
32 02-FEB-2004 vpandya 115.0 Created.
33 04-FEB-2004 vpandya 115.1 Changed get_id_for_employer_address
34 26-JUN-2006 sodhingr 115.2 5033776 added new function, get_leg_rule_state
35 */
36 --
37
38 /*********************************************************************
39 Name : get_legislation_code
40 Purpose : This function returns the legislation code for a given
41 Business Group ID.
42 Arguments : IN
43 p_business_group_id number
44 Notes :
45 *********************************************************************/
46
47 FUNCTION get_legislation_code( p_business_group_id in number )
48 RETURN VARCHAR2
49 IS
50
51 cursor get_legi_cd(cp_business_group_id number) is
52 select org_information9
53 from hr_organization_information
54 where organization_id = cp_business_group_id
55 and org_information_context = 'Business Group Information';
56
57 lv_legislation_code varchar2(30) := NULL;
58
59 BEGIN
60
61 open get_legi_cd(p_business_group_id);
62 fetch get_legi_cd into lv_legislation_code;
63 close get_legi_cd;
64
65 return(lv_legislation_code);
66
67 END get_legislation_code;
68
69 /************************************************************************
70 Name : get_employer_addr
71 Purpose : This functtion is being called by PAY_EMPLOYEE_ACTION_INFO_V
72 view using argument mentioned below.
73
74 This function returns Organization ID or Tax Unit ID (GRE ID)
75 based on entry in pay_legislative_field_info table.
76
77 Arguments : IN
78 p_business_group_id number
79 p_tax_unit_id number
80 p_action_info2 varchar2
81 Notes : This would be defaulted to Organization ID.
82 ************************************************************************/
83
84 FUNCTION get_id_for_employer_address( p_business_group_id in number
85 ,p_tax_unit_id in number
86 ,p_organization_id in number )
87 RETURN NUMBER IS
88
89 cursor get_legi_rule(cp_legislation_code varchar2) is
90 select rule_mode
91 from pay_legislative_field_info
92 where legislation_code = cp_legislation_code
93 and field_name = 'CHOOSE_PAYSLIP'
94 and validation_name = 'ITEM_PROPERTY'
95 and validation_type = 'DISPLAY'
96 and target_location = 'PAY_PAYSLIP'
97 and rule_type = 'PAYSLIP_EMPLYR_ADDR';
98
99 BEGIN
100
101 IF p_tax_unit_id IS NULL and p_organization_id IS NOT NULL THEN
102 return (p_organization_id);
103 ELSIF p_tax_unit_id IS NOT NULL and p_organization_id IS NULL THEN
104 return(p_tax_unit_id);
105 END IF;
106
107 IF gv_employer_addr_cd IS NULL THEN
108
109 IF gv_legislation_code IS NULL THEN
110 gv_legislation_code := get_legislation_code(p_business_group_id);
111 END IF;
112
113 gv_employer_addr_cd := 'ORG';
114
115 OPEN get_legi_rule(gv_legislation_code);
116 FETCH get_legi_rule into gv_employer_addr_cd;
117 CLOSE get_legi_rule;
118
119 END IF;
120
121 IF gv_employer_addr_cd = 'GRE' THEN
122 return(p_tax_unit_id);
123 ELSE
124 return(p_organization_id);
125 END IF;
126
127 END get_id_for_employer_address;
128
129
130 FUNCTION get_leg_rule_state(p_business_group_id in number)
131 RETURN VARCHAR2 IS
132
133 cursor get_legislative_rule(cp_legislation_code varchar2) is
134 select rule_mode
135 from pay_legislative_field_info
136 where legislation_code = gv_legislation_code
137 and field_name = 'ACTION_INFO_STATE'
138 and validation_name = 'ITEM_PROPERTY'
139 and validation_type = 'DISPLAY'
140 and target_location = 'PAY_PAYSLIP'
141 and rule_type = 'ACTION_INFO_STATE';
142
143 l_rule_mode VARCHAR2(50);
144 lv_legislation_code varchar2(3);
145
146 BEGIN
147 --hr_utility.trace_on(null,'payslip_util');
148 hr_utility.trace(gv_legislation_code);
149
150 lv_legislation_code := get_legislation_code(p_business_group_id);
151
152 hr_utility.trace('lv_legislation_code ' ||lv_legislation_code);
153
154 OPEN get_legislative_rule(lv_legislation_code);
155 FETCH get_legislative_rule into l_rule_mode;
156 CLOSE get_legislative_rule;
157
158
159 hr_utility.trace('l_rule_mode '||l_rule_mode);
160 return l_rule_mode;
161
162 END get_leg_rule_state;
163
164 END pay_payslip_util;