1 PACKAGE PQP_GB_PSI_ADDDRESS AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbpsadd.pkh 120.2.12010000.1 2008/07/28 11:16:53 appldev ship $ */
3 --
4 -- Debug Variables.
5 --
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_package VARCHAR2(30) := 'PQP_GB_PSI_ADDDRESS'; -- package name
8 c_application_id CONSTANT NUMBER := 8303;-- application id
9 c_configuration_type CONSTANT VARCHAR2(30) := 'PQP_GB_PENSERVER_ADDRESS_MAP';
10 -- address mapping configuration name
11 c_highest_date CONSTANT DATE := to_date('31/12/4712','DD/MM/YYYY');
12 -- highest date
13
14 g_person_id per_all_people_f.person_id%type;
15 g_assignment_id NUMBER;
16 g_business_group_id per_addresses.business_group_id%TYPE;
17 g_legislation_code VARCHAR2(2);
18 g_effective_date DATE;
19 g_current_run VARCHAR2(20);
20 g_current_layout VARCHAR2(20);
21
22 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
23
24 g_office_address_type per_addresses.address_type%TYPE;
25 g_home_address_type per_addresses.address_type%TYPE;
26 g_office_address_id per_addresses.address_id%TYPE;
27 g_home_address_id per_addresses.address_id%TYPE;
28
29 g_country VARCHAR2(30);
30
31 g_office_address_changed VARCHAR2(1);-- this is used by the data elements functions
32 -- to check if there is a change in office address
33 g_home_address_changed VARCHAR2(1);-- this is used by the data elements functions
34 -- to check if there is a change in home address
35
36 g_email_address per_all_people_f.email_address%TYPE;
37 g_curr_person_dtls per_all_people_f%ROWTYPE;-- current perons details fetched from the
38 -- shared globals
39 g_curr_assg_dtls per_all_assignments_f%ROWTYPE;-- current assignment details fetched from the
40 -- shared globals
41 g_paypoint VARCHAR2(30);-- paypoint value fetched from
42 g_cutover_date DATE;
43 g_ext_dfn_id NUMBER;
44
45 g_include_home_address VARCHAR2(1);-- this value will be set to 'N' when
46 -- an DE error is raised
47 g_include_office_address VARCHAR2(1);-- this value will be set to 'N' when
48 -- an DE error is raised
49 g_report_non_pen_address BOOLEAN := false; -- this will be set to true if the person details should be
50 -- reported for a non-penserver address(added in version 115.4)
51
52 g_office_address_reported BOOLEAN := FALSE;
53 --Cursor to fetch all the addresses for a person of the types specified
54 -- in configuration value.
55 -- Bug Fix - 5104319.
56 CURSOR csr_get_addr_dtls
57 IS
58 SELECT pa.address_id
59 ,pa.address_line1
60 ,pa.address_line2
61 ,pa.address_line3
62 ,pa.town_or_city address_line4
63 ,pa.address_type address_type
64 ,hrl.MEANING address_meaning
65 ,fcl.meaning address_line5
66 ,ftt.territory_short_name country
67 ,pa.date_from
68 ,pa.date_to
69 ,pa.postal_code
70 ,pa.telephone_number_1
71 ,pa.telephone_number_2
72 ,ppf.email_address
73 ,ppf.mailstop
74 FROM per_addresses pa
75 ,per_all_people_f ppf
76 ,fnd_territories_tl ftt
77 ,fnd_common_lookups fcl
78 ,HR_LOOKUPS hrl
79 WHERE pa.person_id = ppf.person_id
80 AND pa.person_id = g_person_id
81 AND pa.business_group_id = g_business_group_id
82 AND pa.country = ftt.territory_code (+)
83 AND 'GB_COUNTY' = fcl.lookup_type (+)
84 AND pa.region_1 = fcl.lookup_code (+)
85 AND 'ADDRESS_TYPE' = hrl.LOOKUP_TYPE (+)
86 AND pa.address_type = hrl.LOOKUP_CODE (+)
87 AND userenv('LANG')= ftt.language -- added as bugfix : 5525631
88 AND g_effective_date BETWEEN ppf.effective_start_date
89 AND ppf.effective_end_date
90 ORDER BY date_from,address_id DESC;
91
92 TYPE t_person_addresses IS TABLE OF csr_get_addr_dtls%ROWTYPE
93 INDEX BY BINARY_INTEGER;
94 -- global table to store the addresses for periodic changes extract
95 g_person_addresses t_person_addresses;
96 -- global table to store the addresses for cutover extract
97 g_person_cutover_addresses t_person_addresses;
98
99
100
101 CURSOR csr_get_email_mailstop
102 IS
103 SELECT email_address
104 ,mailstop
105 FROM per_all_people_f
106 WHERE person_id = g_person_id
107 AND g_effective_date BETWEEN effective_start_date
108 AND effective_end_date;
109
110
111 -- Debug
112 PROCEDURE DEBUG (
113 p_trace_message IN VARCHAR2
114 ,p_trace_location IN NUMBER DEFAULT NULL
115 );
116
117 -- Debug_Enter
118 PROCEDURE debug_enter (
119 p_proc_name IN VARCHAR2
120 ,p_trace_on IN VARCHAR2 DEFAULT NULL
121 );
122
123 -- Debug_Exit
124 PROCEDURE debug_exit (
125 p_proc_name IN VARCHAR2
126 ,p_trace_off IN VARCHAR2 DEFAULT NULL
127 );
128
129 -- Debug Others
130 PROCEDURE debug_others (
131 p_proc_name IN VARCHAR2
132 ,p_proc_step IN NUMBER DEFAULT NULL
133 );
134 ---
135
136 -- ----------------------------------------------------------------------------
137 -- |---------------------< address_cutover_ext_criteria >---------------------|
138 -- Description: Cutover extract criteria.
139 -- ----------------------------------------------------------------------------
140 FUNCTION address_cutover_ext_criteria
141 (
142 p_business_group_id NUMBER
143 ,p_assignment_id NUMBER
144 ,p_effective_date DATE
145 )RETURN VARCHAR2;
146 --------
147
148 -- ----------------------------------------------------------------------------
149 -- |--------------------< address_periodic_ext_criteria >---------------------|
150 -- Description: Address Periodic extract Criteria.
151 -- ----------------------------------------------------------------------------
152 FUNCTION address_periodic_ext_criteria
153 (
154 p_business_group_id NUMBER
155 ,p_assignment_id NUMBER
156 ,p_effective_date DATE
157 )RETURN VARCHAR2;
158 ---------
159
160 -- ----------------------------------------------------------------------------
161 -- |----------------------< set_address_extract_globals >---------------------|
162 -- Description: This procedure is to obtain set the extract level globals.
163 -- ----------------------------------------------------------------------------
164 PROCEDURE set_address_extract_globals
165 (
166 p_business_group_id IN NUMBER
167 ,p_assignment_id IN NUMBER
168 );
169 ---------
170
171 -- ----------------------------------------------------------------------------
172 -- |----------------------< set_assignment_globals >--------------------------|
173 -- Description: This procedure is to set the assignment level globals.
174 -- ----------------------------------------------------------------------------
175 PROCEDURE set_assignment_globals
176 (
177 p_assignment_id IN NUMBER
178 ,p_effective_date IN DATE
179 );
180 -- ----------------------------------------------------------------------------
181 -- |---------------------< is_home_address_changed >------------------------|
182 -- Description: This process will return the value of the global variable
183 -- g_include_home_address, which indicates whether the home
184 -- address is to be picked or not. The value of this is checked
185 -- in the extra conditions on the home address record.
186 -- g_include_home_address is set to 'N' if there are any data
187 -- errors raised.
188 -- ----------------------------------------------------------------------------
189 FUNCTION is_home_address_changed RETURN VARCHAR2;
190 --------
191
192 -- ----------------------------------------------------------------------------
193 -- |---------------------< is_office_address_changed >------------------------|
194 -- Description: This process will return the value of the global variable
195 -- g_include_office_address, which indicates whether the office
196 -- address is to be picked or not. The value of this is checked
197 -- in the extra conditions on the office address record.
198 -- g_include_office_address is set to 'N' if there are any data
199 -- errors raised.
200 -- ----------------------------------------------------------------------------
201 FUNCTION is_office_address_changed RETURN VARCHAR2;
202 ----------
203
204 -- ----------------------------------------------------------------------------
205 -- |---------------------< address_data_element_value >-----------------------|
206 -- Description: This is a common function used by all the data elements to fetch
207 -- thier respective values. Depending the parameter p_ext_user_value
208 -- this procedure decides which value to be returned.
209 -- ----------------------------------------------------------------------------
210 FUNCTION address_data_element_value
211 (
212 p_ext_user_value IN VARCHAR2
213 ,p_output_value OUT NOCOPY VARCHAR2
214 )
215 RETURN NUMBER;
216 ----------
217
218 -- ----------------------------------------------------------------------------
219 -- |----------------------< address_post_processing >--------------------------|
220 -- Description: This is the post-processing rule for the address layout.
221 -- ----------------------------------------------------------------------------
222 FUNCTION address_post_processing RETURN VARCHAR2;
223 ------
224
225 -- ----------------------------------------------------------------------------
226 -- |----------------------< chk_pen_addresses_exist >--------------------------|
227 -- Description: This function is used to check if there are any perserver addresses
228 -- active on a particular date.
229 -- ----------------------------------------------------------------------------
230 FUNCTION chk_pen_addresses_exist
231 (
232 p_effective_date DATE
233 ) RETURN BOOLEAN;
234 ---------
235 END PQP_GB_PSI_ADDDRESS;
[Home] [Help]