DBA Data[Home] [Help]

PACKAGE BODY: APPS.HR_PROCESS_ADDRESS_SS

Source


1 PACKAGE BODY hr_process_address_ss AS
2 /* $Header: hraddwrs.pkb 120.7 2011/11/15 12:01:41 sidsaxen ship $*/
3 
4   -- Package scope global variables.
5  l_transaction_table hr_transaction_ss.transaction_table;
6  l_count INTEGER := 0;
7  l_praddr_ovrlap VARCHAR2(2);
8  l_transaction_step_id  hr_api_transaction_steps.transaction_step_id%type;
9  l_trs_object_version_number  hr_api_transaction_steps.object_version_number%type;
10  g_package      varchar2(31)   := 'HR_PROCESS_ADDRESS_SS';
11  g_data_error            exception;
12  l_message_number VARCHAR2(10);
13  p_trans_rec_count integer;
14 
15 
16   /*
17   ||===========================================================================
18   || FUNCTION: is_a_personal_info_flow
19   ||---------------------------------------------------------------------------
20   ||
21   || Description:
22   ||     This function will will check if the current flow is a personal information flow or not.
23   ||
24   || Access Status:
25   ||     Private
26   ||
27   ||===========================================================================
28   */
29 
30 function is_a_personal_info_flow (
31   p_person_id in number
32   , p_effective_date in date )
33   return boolean
34   is
35 
36   CURSOR c_get_current_applicant_flag
37          (p_person_id      in number
38          ,p_eff_date       in date default trunc(sysdate))
39   IS
40   SELECT   per.current_applicant_flag,
41            per.current_employee_flag,
42            per.current_npw_flag
43   FROM     per_all_people_f   per
44   WHERE  per.person_id = p_person_id
45   AND    p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
46 
47   l_current_applicant_flag  per_all_people_f.current_applicant_flag%type;
48   l_current_employee_flag  per_all_people_f.current_employee_flag%type;
49   l_current_npw_flag per_all_people_f.current_npw_flag%type;
50   l_applicant_hire boolean := false ;
51   l_re_hire boolean := false ;
52   p_result  boolean := true;
53   begin
54 
55    if (p_person_id is null or p_person_id < 0 ) then
56         return false;
57    end if;
58 
59    open c_get_current_applicant_flag(p_person_id, p_effective_date);
60    fetch c_get_current_applicant_flag into l_current_applicant_flag, l_current_employee_flag, l_current_npw_flag;
61    close c_get_current_applicant_flag;
62 
63    -- for rehire and applicant
64   if (nvl(l_current_employee_flag, 'N') <>  'Y' AND
65       nvl(l_current_npw_flag, 'N') <> 'Y') then
66      return false;
67   end if;
68 /*
69   -- for applicant hire ---> g_applicant_hire = TRUE
70   if (l_current_applicant_flag = 'Y' AND
71       nvl(l_current_employee_flag, 'N') <>  'Y' AND
72       nvl(l_current_npw_flag, 'N') <> 'Y') then
73     l_applicant_hire := true;
74   end if;
75 
76   if l_applicant_hire = false  then
77     p_result := true;
78   else
79     p_result := false;
80   end if;
81   */
82   return p_result;
83   end is_a_personal_info_flow;
84 
85 
86   /*
87   ||===========================================================================
88   || PROCEDURE: create_person_address
89   ||---------------------------------------------------------------------------
90   ||
91   || Description:
92   ||     This procedure will call the actual API -
93   ||                hr_person_address_api.create_person_address()
94   ||
95   || Access Status:
96   ||     Public.
97   ||
98   ||===========================================================================
99   */
100   PROCEDURE create_person_address
101     (p_validate                      in     number   default 0
102     ,p_effective_date                in     date
103     ,p_pradd_ovlapval_override       in     number   default 0
104     ,p_validate_county               in     number   default 1
105     ,p_person_id                     in     number
106     --
107     -- PB Add :
108     -- The transaction steps have to be created by the login personid.
109     -- In case of adding address for contacts person_id is contact_person_id.
110     -- Login person id is say employee who is adding the address to his contact.
111     --
112     ,p_login_person_id               in     number default null
113     ,p_business_group_id             in     number default null
114     --
115     ,p_primary_flag                  in     varchar2
116     ,p_style                         in     varchar2
117     ,p_date_from                     in     date
118     ,p_date_to                       in     date     default null
119     ,p_address_type                  in     varchar2 default hr_api.g_varchar2
120     ,p_address_type_meaning          in     varchar2 default hr_api.g_varchar2
121     ,p_comments                      in     clob default hr_api.g_varchar2	--Bug#13362792
122     ,p_address_line1                 in     varchar2 default hr_api.g_varchar2
123     ,p_address_line2                 in     varchar2 default hr_api.g_varchar2
124     ,p_address_line3                 in     varchar2 default hr_api.g_varchar2
125     ,p_town_or_city                  in     varchar2 default hr_api.g_varchar2
126     ,p_region_1                      in     varchar2 default hr_api.g_varchar2
127     ,p_region_2                      in     varchar2 default hr_api.g_varchar2
128     ,p_region_3                      in     varchar2 default hr_api.g_varchar2
129     ,p_postal_code                   in     varchar2 default hr_api.g_varchar2
130     ,p_country                       in     varchar2 default hr_api.g_varchar2
131     ,p_country_meaning               in     varchar2 default hr_api.g_varchar2
132     ,p_telephone_number_1            in     varchar2 default hr_api.g_varchar2
133     ,p_telephone_number_2            in     varchar2 default hr_api.g_varchar2
134     ,p_telephone_number_3            in     varchar2 default hr_api.g_varchar2
135     ,p_addr_attribute_category       in     varchar2 default hr_api.g_varchar2
136     ,p_addr_attribute1               in     varchar2 default hr_api.g_varchar2
137     ,p_addr_attribute2               in     varchar2 default hr_api.g_varchar2
138     ,p_addr_attribute3               in     varchar2 default hr_api.g_varchar2
139     ,p_addr_attribute4               in     varchar2 default hr_api.g_varchar2
140     ,p_addr_attribute5               in     varchar2 default hr_api.g_varchar2
141     ,p_addr_attribute6               in     varchar2 default hr_api.g_varchar2
142     ,p_addr_attribute7               in     varchar2 default hr_api.g_varchar2
143     ,p_addr_attribute8               in     varchar2 default hr_api.g_varchar2
144     ,p_addr_attribute9               in     varchar2 default hr_api.g_varchar2
145     ,p_addr_attribute10              in     varchar2 default hr_api.g_varchar2
146     ,p_addr_attribute11              in     varchar2 default hr_api.g_varchar2
147     ,p_addr_attribute12              in     varchar2 default hr_api.g_varchar2
148     ,p_addr_attribute13              in     varchar2 default hr_api.g_varchar2
149     ,p_addr_attribute14              in     varchar2 default hr_api.g_varchar2
150     ,p_addr_attribute15              in     varchar2 default hr_api.g_varchar2
151     ,p_addr_attribute16              in     varchar2 default hr_api.g_varchar2
152     ,p_addr_attribute17              in     varchar2 default hr_api.g_varchar2
153     ,p_addr_attribute18              in     varchar2 default hr_api.g_varchar2
154     ,p_addr_attribute19              in     varchar2 default hr_api.g_varchar2
155     ,p_addr_attribute20              in     varchar2 default hr_api.g_varchar2
156     ,p_add_information13             in     varchar2 default hr_api.g_varchar2
157     ,p_add_information14             in     varchar2 default hr_api.g_varchar2
158     ,p_add_information15             in     varchar2 default hr_api.g_varchar2
159     ,p_add_information16             in     varchar2 default hr_api.g_varchar2
160     ,p_add_information17             in     varchar2 default hr_api.g_varchar2
161     ,p_add_information18             in     varchar2 default hr_api.g_varchar2
162     ,p_add_information19             in     varchar2 default hr_api.g_varchar2
163     ,p_add_information20             in     varchar2 default hr_api.g_varchar2
164     ,p_address_id                       out nocopy number
165     ,p_object_version_number            out nocopy number
166     -- StartRegistration
167     ,p_contact_or_person             in     varchar2 default null
168     -- EndRegistration
169     ,p_item_type                     in     varchar2
170     ,p_item_key                      in     varchar2
171     ,p_activity_id                   in     number
172     ,p_action                        in     varchar2
173     ,p_old_address_id                in     number   default null
174     ,p_old_object_version_number     in     number   default null
175     ,p_save_mode                     in     varchar2 default null
176     ,p_error_message                 out nocopy    long
177     , p_contact_relationship_id       in number           default hr_api.g_number
178   )
179   IS
180 
181   l_proc varchar2(200) := g_package || 'create_person_address';
182   l_review_item_name           varchar2(50);
183   l_date_to                    date;
184   l_date_from                  date;
185   l_transaction_id             number default null;
186   l_result                     varchar2(100) default null;
187   -- PB : Added the variables
188   l_person_id                  number;
189   l_dummy_num  number;
190   l_dummy_date date;
191   l_dummy_char varchar2(1000);
192   l_dummy_bool boolean;
193 
194   -- StartRegistration
195 
196   l_primary_flag varchar2(10);
197     -- StartRegistration
198   l_reg_per_ovn                       number default null;
199   l_reg_employee_number               number default null;
200   l_reg_asg_ovn                       number default null;
201   l_reg_full_name                     per_all_people_f.full_name%type default null;
202   l_reg_assignment_id                 number;
203   l_reg_per_effective_start_date      date;
204   l_reg_per_effective_end_date        date;
205   l_reg_per_comment_id                number;
206   l_reg_assignment_sequence           number;
207   l_reg_assignment_number             varchar2(50);
208   l_reg_name_combination_warning      boolean;
209   l_reg_assign_payroll_warning        boolean;
210   l_reg_orig_hire_warning             boolean;
211   --Startregistration gsheelum
212   l_contact_set                       number;
213   -- EndRegistration
214   l_api_name                          varchar2(100);
215   l_validate_g_per_con_step_id        number;
216   l_old_ovn                           number;
217 
218   BEGIN
219     hr_utility.set_location(' Entering:' || l_proc,5);
220 
221          --bug 5375749
222     --If user enter effective date for which is less than person joining date then error should come.
223     declare
224       error_flag boolean := false;
225       result     boolean := false;
226     begin
227       result := is_a_personal_info_flow (p_person_id, p_effective_date);
228       error_flag := hr_perinfo_util_web.isDateLessThanCreationDate(p_effective_date,p_person_id);
229       if result = true then
230          hr_utility.trace('ORCL : inside the result true ');
231       end if;
232       if error_flag = true then
233          hr_utility.trace('ORCL : inside the error_flag true');
234       end if;
235       if error_flag= true and result= true then
236         fnd_message.set_name('PER', 'HR_PERINFO_INVALID_EFFEC_DATE');
237         fnd_message.raise_error;
238       end if;
239     end ;
240     --bug 5375749
241 
242     -- Call the actual API.
243     --
244     -- PB : In case of adding a contact and address to contact then person_id
245     --      is null or less than 0, so to validate the address data, contact
246     --      have to be created.
247     --
248     l_person_id := p_person_id;
249     --
250     -- StartRegistration
251     l_primary_flag :=p_primary_flag;
252     -- EndRegistration
253     --
254     --startregistration anupam
255     -- If coming from overview page and creating a third address then the
256     -- value of primary flag could be "T". This is required to be stored in
257     -- transaction tables as "T" but the api validation will be as "N" only and
258     -- finally it will go in database as N in process_api
259     if p_primary_flag = 'T' then
260     hr_utility.set_location(l_proc,10);
261     l_primary_flag := 'N';
262     end if;
263     -- endregistration anupam
264 
265     IF (p_save_mode = 'SAVE_FOR_LATER') THEN
266        hr_utility.set_location(l_proc,15);
267        GOTO only_transaction;
268     END IF;
269 
270     savepoint create_address;
271     --
272     if (l_person_id is null or l_person_id < 0 ) then
273        hr_utility.set_location(l_proc,20);
274        --
275        -- Now create a dummy contact to the login person and
276        -- use the out contact person id to validate the phone.
277        --
278        l_primary_flag := 'Y';
279        --
280     -- bug # 2174876
281     if p_contact_or_person = 'CONTACT' or p_contact_or_person = 'EMER_CR_NEW_CONT' or p_contact_or_person = 'EMRG_OVRW_UPD' or p_contact_or_person = 'EMRG_OVRW_DEL' or  p_contact_or_person = 'EMER_CR_NEW_REL'
282           or  p_contact_or_person = 'DPDNT_CR_NEW_CONT' or  p_contact_or_person = 'DPDNT_OVRW_UPD'  or  p_contact_or_person = 'DPDNT_OVRW_DEL' or  p_contact_or_person = 'DPDNT_CR_NEW_REL'
283         or p_contact_or_person = 'COBRA' then
284       declare
285         l_object_version_number number;
286         l_effective_start_date date;
287         l_effective_end_date date;
288         l_full_name varchar2(255);
289         l_comment_id number;
290         l_name_combination_warning boolean;
291         l_orig_hire_warning boolean;
292       begin
293        hr_utility.set_location(l_proc,25);
294        --ignore the dff validations for Dummy Person created
295        hr_person_info_util_ss.create_ignore_df_validation('PER_PEOPLE');
296        hr_person_info_util_ss.create_ignore_df_validation('Person Developer DF');
297         hr_contact_api.create_person
298            (p_start_date => p_effective_date,
299             p_business_group_id =>p_business_group_id,
300             p_last_name => 'RegistrationDummy',
301 	    p_first_name => 'Dummy',
302             p_sex => 'M',
303        --     p_coord_ben_no_cvg_flag => 'Y',
304             p_person_id => l_person_id,
305             p_object_version_number => l_object_version_number,
306             p_effective_start_date => l_effective_start_date,
307             p_effective_end_date => l_effective_end_date,
308             p_full_name => l_full_name,
309             p_comment_id => l_comment_id,
310             p_name_combination_warning => l_name_combination_warning,
311             p_orig_hire_warning => l_orig_hire_warning);
312        hr_person_info_util_ss.remove_ignore_df_validation;
313       end;
314      end if;
315       -- end bug # 2174876
316       --end bug # 2138073/2115552
317      if p_contact_or_person = 'PERSON' then
318        hr_utility.set_location(l_proc,30);
319        -- we need to call only BD step to create the dummy person
320        -- and nothing else, so use process_selected_transaction
321        hr_new_user_reg_ss.process_selected_transaction(
322                   p_item_type => p_item_type,
323                   p_item_key  => p_item_key,
324                   p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
325        l_person_id := to_char(hr_process_person_ss.g_person_id);
326      end if;
327     end if;
328     --
329     -- StartRegistration : Changed the primary flag to validate the secondary
330     -- address. In case of new person, there is no primary address so while
331     -- validating the secondary address create_address errors out, so always
332     -- validate as primary address.
333 
334     -- First end date the Secondary Address, before creating a new one
335     -- and do it in validate false mode, as we have a rollback down.
336 
337     IF UPPER(p_action) = 'CHANGE' THEN
338        IF UPPER(l_primary_flag) like 'N%' THEN
339           hr_utility.set_location(l_proc,35);
340           l_old_ovn := p_old_object_version_number;
341           hr_person_address_api.update_person_address
342                         (p_validate => false
343                         ,p_effective_date => p_effective_date
344                         ,p_address_id => p_old_address_id
345                         ,p_object_version_number => l_old_ovn
346                         ,p_date_to => p_date_to);
347        END IF;
348     END IF;
349 
350     hr_person_address_api.create_person_address
351       (p_validate                      => hr_java_conv_util_ss.get_boolean (
352                                             p_number => p_validate
353                                           )
354       ,p_effective_date                => p_effective_date
355       ,p_pradd_ovlapval_override       => hr_java_conv_util_ss.get_boolean (
356                                            p_number => p_pradd_ovlapval_override
357                                           )
358       ,p_validate_county               => hr_java_conv_util_ss.get_boolean (
359                                            p_number => p_validate_county
360                                           )
361       ,p_person_id                     => l_person_id  -- PB : Modify p_person_id
362       ,p_primary_flag                  => l_primary_flag -- StartRegistration
363       ,p_style                         => p_style
364       ,p_date_from                     => p_effective_date
365       ,p_address_type                  => p_address_type
366       ,p_comments                      => p_comments
367       ,p_address_line1                 => p_address_line1
368       ,p_address_line2                 => p_address_line2
369       ,p_address_line3                 => p_address_line3
370       ,p_town_or_city                  => p_town_or_city
371       ,p_region_1                      => p_region_1
372       ,p_region_2                      => p_region_2
373       ,p_region_3                      => p_region_3
374       ,p_postal_code                   => p_postal_code
375       ,p_country                       => p_country
376       ,p_telephone_number_1            => p_telephone_number_1
377       ,p_telephone_number_2            => p_telephone_number_2
378       ,p_telephone_number_3            => p_telephone_number_3
379       ,p_addr_attribute_category       => p_addr_attribute_category
380       ,p_addr_attribute1               => p_addr_attribute1
381       ,p_addr_attribute2               => p_addr_attribute2
382       ,p_addr_attribute3               => p_addr_attribute3
383       ,p_addr_attribute4               => p_addr_attribute4
384       ,p_addr_attribute5               => p_addr_attribute5
385       ,p_addr_attribute6               => p_addr_attribute6
386       ,p_addr_attribute7               => p_addr_attribute7
387       ,p_addr_attribute8               => p_addr_attribute8
388       ,p_addr_attribute9               => p_addr_attribute9
389       ,p_addr_attribute10              => p_addr_attribute10
390       ,p_addr_attribute11              => p_addr_attribute11
391       ,p_addr_attribute12              => p_addr_attribute12
392       ,p_addr_attribute13              => p_addr_attribute13
393       ,p_addr_attribute14              => p_addr_attribute14
394       ,p_addr_attribute15              => p_addr_attribute15
395       ,p_addr_attribute16              => p_addr_attribute16
396       ,p_addr_attribute17              => p_addr_attribute17
397       ,p_addr_attribute18              => p_addr_attribute18
398       ,p_addr_attribute19              => p_addr_attribute19
399       ,p_addr_attribute20              => p_addr_attribute20
400       ,p_add_information13             => p_add_information13
401       ,p_add_information14             => p_add_information14
402       ,p_add_information15             => p_add_information15
403       ,p_add_information16             => p_add_information16
404       ,p_add_information17             => p_add_information17
405       ,p_add_information18             => p_add_information18
406       ,p_add_information19             => p_add_information19
407       ,p_add_information20             => p_add_information20
408       --
409       -- PB : These out variables should not be written to transaction tables.
410       --
411       ,p_address_id                    => l_dummy_num -- PB : p_address_id
412       ,p_object_version_number         => l_dummy_num -- PB : p_object_version_number
413     );
414  -- PB : Now rollback all the changes which are performed.
415  --
416 
417  ROLLBACK to create_address;
418  --
419  -- -----------------------------------------------------------------------------
420  -- We will write the data to transaction tables.
421  -- Determine if a transaction step exists for this activity
422  -- if a transaction step does exist then the transaction_step_id and
423  -- object_version_number are set (i.e. not null).
424  -- -----------------------------------------------------------------------------
425 
426        <<only_transaction>> -- label for GOTO
427 
428       --
429       -- Setting the following two helps to see the actual effective
430       -- date of the transaction in All Actions Awaiting your attention
431       -- table.
432       -- Say for address user enters new address with an effective date
433       -- this date helps to show as said above and also makes sure
434       -- when SFL'd and started again uses this date.
435       -- If not set, WF on starting sets this dates to sysdate and it
436       -- shows that and also on retrieving SFL it performs as of sysdate
437       -- rather than the actual date of the transaction.
438       --
439       -- We don't support this for contacts as it is always by sysdate
440       -- if we end of setting it for contacts it even affects the start
441       -- date of the dependents created in registration flow see
442       -- bug 3784728 for more details
443       --
444 
445       IF NOT (p_contact_or_person = 'CONTACT' or p_contact_or_person = 'EMER_CR_NEW_CONT' or p_contact_or_person = 'EMRG_OVRW_UPD' or p_contact_or_person = 'EMRG_OVRW_DEL' or  p_contact_or_person = 'EMER_CR_NEW_REL'
446           or  p_contact_or_person = 'DPDNT_CR_NEW_CONT' or  p_contact_or_person = 'DPDNT_OVRW_UPD'  or  p_contact_or_person = 'DPDNT_OVRW_DEL' or  p_contact_or_person = 'DPDNT_CR_NEW_REL') THEN
447 
448        hr_utility.set_location(l_proc,40);
449        wf_engine.setItemAttrText (itemtype => p_item_type,
450                            itemkey  => p_item_key,
451                            aname    => 'P_EFFECTIVE_DATE',
452                            avalue   =>  to_char(p_effective_date,
453                                         g_date_format));
454 
455        wf_engine.setItemAttrDate (itemtype => p_item_type,
456                            itemkey  => p_item_key,
457                            aname    => 'CURRENT_EFFECTIVE_DATE',
458                            avalue   =>  p_effective_date);
459        END IF;
460        --
461        -- First, check if transaction id exists or not
462        --
463        l_transaction_id := hr_transaction_ss.get_transaction_id
464                      (p_item_type   => p_item_type
465                      ,p_item_key    => p_item_key);
466        --
467        IF l_transaction_id is null THEN
468          hr_utility.set_location(l_proc,45);
469 
470         -- Start a Transaction
471 
472         hr_transaction_ss.start_transaction
473            (itemtype   => p_item_type
474            ,itemkey    => p_item_key
475            ,actid      => p_activity_id
476            ,funmode    => 'RUN'
477            ,p_api_addtnl_info => p_contact_or_person
478            ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB : Modification
479            ,result     => l_result);
480 
481         l_transaction_id := hr_transaction_ss.get_transaction_id
482                         (p_item_type   => p_item_type
483                         ,p_item_key    => p_item_key);
484        END IF;
485        --
486        -- Create a transaction step
487        --
488        hr_transaction_api.create_transaction_step
489            (p_validate              => false
490            ,p_creator_person_id     => nvl(p_login_person_id, p_person_id) -- PB : Modification
491            ,p_transaction_id        => l_transaction_id
492            ,p_api_name              => g_package || '.PROCESS_API'
493            ,p_item_type             => p_item_type
494            ,p_item_key              => p_item_key
495            ,p_activity_id           => p_activity_id
496            ,p_transaction_step_id   => l_transaction_step_id
497            ,p_object_version_number => l_trs_object_version_number);
498        --
499 
500 
501 	l_count := 1;
502  	l_transaction_table(l_count).param_name := 'P_PERSON_ID';
503  	l_transaction_table(l_count).param_value := p_person_id;
504  	l_transaction_table(l_count).param_data_type := 'NUMBER';
505 
506 --	IF UPPER(p_action) = 'DELETE' THEN
507                 hr_utility.set_location(l_proc,50);
508 --		l_effective_date := to_char(trunc(sysdate),l_user_date_format);
509 --	ELSE
510                 hr_utility.set_location(l_proc,55);
511 --		l_effective_date := p_effective_date;
512 --	END IF;
513 
514  	l_count := l_count + 1;
515  	l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
516  	l_transaction_table(l_count).param_value := to_char(p_effective_date,
517                                                     hr_transaction_ss.g_date_format);
518  	l_transaction_table(l_count).param_data_type := 'DATE';
519 
520 -- 	l_count := l_count + 1;
521 -- 	l_transaction_table(l_count).param_name := 'P_USER_DATE_FORMAT';
522 -- 	l_transaction_table(l_count).param_value := l_user_date_format;
523 -- 	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
524 
525  	l_count := l_count + 1;
526  	l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
527  	l_transaction_table(l_count).param_value := p_object_version_number;
528  	l_transaction_table(l_count).param_data_type := 'NUMBER';
529 
530 --    old object version number is same as object version number for create address
531 
532  	l_count := l_count + 1;
533  	l_transaction_table(l_count).param_name	:= 'P_OLD_OBJECT_VERSION_NUMBER';
534  	l_transaction_table(l_count).param_value := p_old_object_version_number;
535  	l_transaction_table(l_count).param_data_type := 'NUMBER';
536 
537  	l_count := l_count + 1;
538  	l_transaction_table(l_count).param_name := 'P_ADDRESS_ID';
539  	l_transaction_table(l_count).param_value := p_address_id;
540  	l_transaction_table(l_count).param_data_type := 'NUMBER';
541 
542 --    old address id is same as address id for create address
543 
544  	l_count := l_count + 1;
545  	l_transaction_table(l_count).param_name := 'P_OLD_ADDRESS_ID';
546  	l_transaction_table(l_count).param_value := p_old_address_id;
547  	l_transaction_table(l_count).param_data_type := 'NUMBER';
548 
549  	l_count := l_count + 1;
550  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE1';
551  	l_transaction_table(l_count).param_value := p_address_line1;
552  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
553 
554  	l_count := l_count + 1;
555  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE2';
556  	l_transaction_table(l_count).param_value := p_address_line2;
557  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
558 
559  	l_count := l_count + 1;
560  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE3';
561  	l_transaction_table(l_count).param_value := p_address_line3;
562  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
563 
564  	l_count := l_count + 1;
565  	l_transaction_table(l_count).param_name := 'P_CITY';
566  	l_transaction_table(l_count).param_value := p_town_or_city;
567  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
568 
569  	l_count := l_count + 1;
570  	l_transaction_table(l_count).param_name := 'P_REGION1';
571  	l_transaction_table(l_count).param_value := p_region_1;
572  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
573 
574 -- 	l_count := l_count + 1;
575 -- 	l_transaction_table(l_count).param_name := 'P_STATE';
576 -- 	l_transaction_table(l_count).param_value := p_state;
577 -- 	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
578 
579  	l_count := l_count + 1;
580  	l_transaction_table(l_count).param_name := 'P_REGION2';
581  	l_transaction_table(l_count).param_value := p_region_2;
582  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
583 
584  	l_count := l_count + 1;
585  	l_transaction_table(l_count).param_name := 'P_REGION3';
586  	l_transaction_table(l_count).param_value := p_region_3;
587  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
588 
589  	l_count := l_count + 1;
590  	l_transaction_table(l_count).param_name := 'P_COUNTRY';
591 	l_transaction_table(l_count).param_value := p_country_meaning;
592  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
593 
594  	l_count := l_count + 1;
595  	l_transaction_table(l_count).param_name := 'P_COUNTRY_CODE';
596  	l_transaction_table(l_count).param_value := p_country;
597  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
598 
599  	l_count := l_count + 1;
600  	l_transaction_table(l_count).param_name := 'P_POSTAL_CODE';
601  	l_transaction_table(l_count).param_value := p_postal_code;
602  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
603 
604  	l_count := l_count + 1;
605 	l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE';
606  	l_transaction_table(l_count).param_value := p_address_type_meaning;
607  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
608 
609  	l_count := l_count + 1;
610  	l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE_CODE';
611  	l_transaction_table(l_count).param_value := p_address_type;
612  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
613 
614  	l_count := l_count + 1;
615  	l_transaction_table(l_count).param_name := 'P_PRADD_OVLAPVAL_OVERRIDE';
616         IF (p_pradd_ovlapval_override = 1) THEN
617 	   hr_utility.set_location(l_proc,60);
618  	   l_transaction_table(l_count).param_value := 'Y';
619         ELSIF (p_pradd_ovlapval_override = 0) THEN
620 	   hr_utility.set_location(l_proc,65);
621            l_transaction_table(l_count).param_value := 'N';
622         END IF;
623 
624  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
625 
626         l_count := l_count + 1;
627         l_transaction_table(l_count).param_name := 'P_DATE_FROM';
628         l_transaction_table(l_count).param_value := to_char(p_date_from,
629                                                     hr_transaction_ss.g_date_format);
630         l_transaction_table(l_count).param_data_type := 'DATE';
631 
632  	l_count := l_count + 1;
633  	l_transaction_table(l_count).param_name := 'P_DATE_TO';
634  	l_transaction_table(l_count).param_value := to_char(p_date_to,
635                                                     hr_transaction_ss.g_date_format);
636         l_transaction_table(l_count).param_data_type := 'DATE';
637 
638  	l_count := l_count + 1;
639  	l_transaction_table(l_count).param_name := 'P_STYLE';
640  	l_transaction_table(l_count).param_value := p_style;
641  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
642 
643  	l_count := l_count + 1;
644  	l_transaction_table(l_count).param_name := 'P_PRIMARY_FLAG';
645  	l_transaction_table(l_count).param_value := p_primary_flag;
646  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
647 
648  	l_count := l_count + 1;
649  	l_transaction_table(l_count).param_name := 'P_ACTION';
650  	l_transaction_table(l_count).param_value := UPPER(p_action);
651  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
652 
653         l_count := l_count + 1;
654         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER1';
655         l_transaction_table(l_count).param_value := p_telephone_number_1;
656         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
657 
658         l_count := l_count + 1;
659         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER2';
660         l_transaction_table(l_count).param_value := p_telephone_number_2;
661         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
662 
663         l_count := l_count + 1;
664         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER3';
665         l_transaction_table(l_count).param_value := p_telephone_number_3;
666         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
667 
668 -- Now add all the Descriptive flex fields into transactions tables
669 
670         l_count := l_count + 1; -- CONTEXT
671         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE_CATEGORY';
672         l_transaction_table(l_count).param_value := p_addr_attribute_category;
673         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
674 
675         l_count := l_count + 1;
676         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE1';
677         l_transaction_table(l_count).param_value := p_addr_attribute1;
678         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
679 
680         l_count := l_count + 1;
681         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE2';
682         l_transaction_table(l_count).param_value := p_addr_attribute2;
683         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
684 
685         l_count := l_count + 1;
686         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE3';
687         l_transaction_table(l_count).param_value := p_addr_attribute3;
688         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
689 
690         l_count := l_count + 1;
691         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE4';
692         l_transaction_table(l_count).param_value := p_addr_attribute4;
693         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
694 
695         l_count := l_count + 1;
696         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE5';
697         l_transaction_table(l_count).param_value := p_addr_attribute5;
698         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
699 
700         l_count := l_count + 1;
701         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE6';
702         l_transaction_table(l_count).param_value := p_addr_attribute6;
703         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
704 
705         l_count := l_count + 1;
706         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE7';
707         l_transaction_table(l_count).param_value := p_addr_attribute7;
708         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
709 
710         l_count := l_count + 1;
711         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE8';
712         l_transaction_table(l_count).param_value := p_addr_attribute8;
713         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
714 
715         l_count := l_count + 1;
716         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE9';
717         l_transaction_table(l_count).param_value := p_addr_attribute9;
718         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
719 
720         l_count := l_count + 1;
721         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE10';
722         l_transaction_table(l_count).param_value := p_addr_attribute10;
723         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
724 
725         l_count := l_count + 1;
726         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE11';
727         l_transaction_table(l_count).param_value := p_addr_attribute11;
728         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
729 
730         l_count := l_count + 1;
731         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE12';
732         l_transaction_table(l_count).param_value := p_addr_attribute12;
733         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
734 
735         l_count := l_count + 1;
736         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE13';
737         l_transaction_table(l_count).param_value := p_addr_attribute13;
738         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
739 
740         l_count := l_count + 1;
741         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE14';
742         l_transaction_table(l_count).param_value := p_addr_attribute14;
743         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
744 
745         l_count := l_count + 1;
746         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE15';
747         l_transaction_table(l_count).param_value := p_addr_attribute15;
748         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
749 
750         l_count := l_count + 1;
751         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE16';
752         l_transaction_table(l_count).param_value := p_addr_attribute16;
753         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
754 
755         l_count := l_count + 1;
756         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE17';
757         l_transaction_table(l_count).param_value := p_addr_attribute17;
758         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
759 
760         l_count := l_count + 1;
761         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE18';
762         l_transaction_table(l_count).param_value := p_addr_attribute18;
763         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
764 
765         l_count := l_count + 1;
766         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE19';
767         l_transaction_table(l_count).param_value := p_addr_attribute19;
768         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
769 
770         l_count := l_count + 1;
771         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE20';
772         l_transaction_table(l_count).param_value := p_addr_attribute20;
773         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
774 
775 
776         l_count := l_count + 1;
777         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION13';
778         l_transaction_table(l_count).param_value := p_add_information13;
779         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
780 
781 
782         l_count := l_count + 1;
783         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION14';
784         l_transaction_table(l_count).param_value := p_add_information14;
785         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
786 
787 
788         l_count := l_count + 1;
789         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION15';
790         l_transaction_table(l_count).param_value := p_add_information15;
791         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
792 
793 
794         l_count := l_count + 1;
795         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION16';
796         l_transaction_table(l_count).param_value := p_add_information16;
797         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
798 
799 
800         l_count := l_count + 1;
801         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION17';
802         l_transaction_table(l_count).param_value := p_add_information17;
803         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
804 
805         l_count := l_count + 1;
806         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION18';
807         l_transaction_table(l_count).param_value := p_add_information18;
808         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
809 
810         l_count := l_count + 1;
811         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION19';
812         l_transaction_table(l_count).param_value := p_add_information19;
813         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
814 
815         l_count := l_count + 1;
816         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION20';
817         l_transaction_table(l_count).param_value := p_add_information20;
818         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
819 
820         l_count := l_count + 1;
821         l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
822         l_transaction_table(l_count).param_value := p_activity_id;
823         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
824 
825         BEGIN
826           l_review_item_name := wf_engine.GetActivityAttrText(itemtype  => p_item_type,
827                                                   itemkey   => p_item_key,
828                                                   actid     => p_activity_id,
829                                                   aname     => gv_wf_review_region_item);
830         EXCEPTION
831         WHEN OTHERS THEN
832 	   hr_utility.set_location(l_proc || 'EXCEPTION' ,555);
833            l_review_item_name := 'HrMainAddressReview';
834         END;
835 
836         l_count := l_count + 1;
837         l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
838         l_transaction_table(l_count).param_value := l_review_item_name;
839         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
840 
841         -- StartRegistration gsheelum
842         l_count := l_count + 1;
843         l_transaction_table(l_count).param_name := 'P_CONTACT_OR_PERSON';
844         l_transaction_table(l_count).param_value := P_CONTACT_OR_PERSON;
845         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
846         -- EndRegistration
847         --
848         --  This is a marker for the contact person to be used to identify the Address
849         --  to be retrieved for the contact person in context in review page.
850         --  The HR_LAST_CONTACT_SET is in from the work flow attribute
851         begin
852 	    hr_utility.set_location(l_proc,70);
853             l_contact_set := wf_engine.GetItemAttrNumber(itemtype => p_item_type,
854                                                 itemkey  => p_item_key,
855                                                 aname    => 'HR_CONTACT_SET');
856 
857             exception when others then
858 	        hr_utility.set_location(l_proc || 'EXCEPTION' ,560 );
859                 l_contact_set := 0;
860 
861         end;
862 
863         l_count := l_count + 1;
864         l_transaction_table(l_count).param_name := 'P_CONTACT_SET';
865         l_transaction_table(l_count).param_value := l_contact_set;
866         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
867 
868         if (p_contact_relationship_id > 0) then
869 	   hr_utility.set_location(l_proc,75);
870            l_count := l_count + 1;
871            l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
872            l_transaction_table(l_count).param_value := p_contact_relationship_id;
873            l_transaction_table(l_count).param_data_type := 'NUMBER';
874         end if;
875         --EndRegistration gsheelum
876 	 hr_transaction_ss.save_transaction_step
877        		(p_item_type => p_item_type
878        		,p_item_key => p_item_key
879        		,p_actid => p_activity_id
880        	        ,p_login_person_id => nvl(p_login_person_id, p_person_id) -- PB Modification
881                 ,p_transaction_step_id => l_transaction_step_id
882        		,p_api_name => g_package || '.PROCESS_API'
883        		,p_transaction_data => l_transaction_table);
884 
885 
886   hr_utility.set_location(' Leaving:' || l_proc,80);
887 
888 
889    EXCEPTION
890         WHEN hr_utility.hr_error THEN
891 	 hr_utility.set_location(' Leaving:' || l_proc,565);
892 
893          -- -------------------------------------------
894          -- an application error has been raised so we must
895          -- redisplay the web form to display the error
896          -- --------------------------------------------
897          hr_message.provide_error;
898          l_message_number := hr_message.last_message_number;
899          IF l_message_number = 'APP-7165' OR
900             l_message_number = 'APP-7155' THEN
901    --populate the p_error_message out variable
902           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
903                              p_error_message => p_error_message,
904                              p_attr_name => 'Page',
905                              p_app_short_name => 'PER',
906                              p_message_name => 'HR_UPDATE_NOT_ALLOWED');
907          ELSIF l_message_number = 'APP-51139' THEN
908           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
909                              p_error_message => p_error_message,
910                              p_attr_name => 'AddressType',
911                              p_app_short_name => 'PER',
912                              p_message_name => 'HR_PERINFO_INVALID_ADDR_TYPE');
913          ELSIF l_message_number = 'APP-7952' OR
914                l_message_number = 'APP-7953' OR
915                l_message_number = 'APP-51276' OR
916                l_message_number = 'APP-51282'  THEN
917           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
918                              p_error_message => p_error_message,
919                              p_app_short_name => 'PER',
920                              p_message_name => 'HR_INVALID_CITYSTATEZIPCOUNTY');
921          ELSE
922           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
923                              p_error_message => p_error_message);
924          END IF;
925     WHEN OTHERS THEN
926     p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
927                              p_error_message => p_error_message);
928   END create_person_address;
929 
930   /*
931   ||===========================================================================
932   || PROCEDURE: update_person_address
933   ||---------------------------------------------------------------------------
934   ||
935   || Description:
936   ||     This procedure will call the actual API -
937   ||                hr_person_address_api.update_person_address()
938   ||
939   || Access Status:
940   ||     Public.
941   ||
942   ||===========================================================================
943   */
944 
945 PROCEDURE update_person_address
946   (p_validate                      in     number  default 0
947   ,p_effective_date                in     date
948   ,p_validate_county               in     number  default 1
949   ,p_address_id                    in     number
950   ,p_object_version_number         in out nocopy number
951   ,p_date_from                     in     date     default hr_api.g_date
952   ,p_date_to                       in     date     default hr_api.g_date
953   ,p_address_type                  in     varchar2 default hr_api.g_varchar2
954   ,p_address_type_meaning          in     varchar2 default hr_api.g_varchar2
955   ,p_comments                      in     clob default hr_api.g_varchar2	--Bug#13362792
956   ,p_address_line1                 in     varchar2 default hr_api.g_varchar2
957   ,p_address_line2                 in     varchar2 default hr_api.g_varchar2
958   ,p_address_line3                 in     varchar2 default hr_api.g_varchar2
959   ,p_town_or_city                  in     varchar2 default hr_api.g_varchar2
960   ,p_region_1                      in     varchar2 default hr_api.g_varchar2
961   ,p_region_2                      in     varchar2 default hr_api.g_varchar2
962   ,p_region_3                      in     varchar2 default hr_api.g_varchar2
963   ,p_postal_code                   in     varchar2 default hr_api.g_varchar2
964   ,p_country                       in     varchar2 default hr_api.g_varchar2
965   ,p_country_meaning               in     varchar2 default hr_api.g_varchar2
966   ,p_telephone_number_1            in     varchar2 default hr_api.g_varchar2
967   ,p_telephone_number_2            in     varchar2 default hr_api.g_varchar2
968   ,p_telephone_number_3            in     varchar2 default hr_api.g_varchar2
969   ,p_addr_attribute_category       in     varchar2 default hr_api.g_varchar2
970   ,p_addr_attribute1               in     varchar2 default hr_api.g_varchar2
971   ,p_addr_attribute2               in     varchar2 default hr_api.g_varchar2
972   ,p_addr_attribute3               in     varchar2 default hr_api.g_varchar2
973   ,p_addr_attribute4               in     varchar2 default hr_api.g_varchar2
974   ,p_addr_attribute5               in     varchar2 default hr_api.g_varchar2
975   ,p_addr_attribute6               in     varchar2 default hr_api.g_varchar2
976   ,p_addr_attribute7               in     varchar2 default hr_api.g_varchar2
977   ,p_addr_attribute8               in     varchar2 default hr_api.g_varchar2
978   ,p_addr_attribute9               in     varchar2 default hr_api.g_varchar2
979   ,p_addr_attribute10              in     varchar2 default hr_api.g_varchar2
980   ,p_addr_attribute11              in     varchar2 default hr_api.g_varchar2
981   ,p_addr_attribute12              in     varchar2 default hr_api.g_varchar2
982   ,p_addr_attribute13              in     varchar2 default hr_api.g_varchar2
983   ,p_addr_attribute14              in     varchar2 default hr_api.g_varchar2
984   ,p_addr_attribute15              in     varchar2 default hr_api.g_varchar2
985   ,p_addr_attribute16              in     varchar2 default hr_api.g_varchar2
986   ,p_addr_attribute17              in     varchar2 default hr_api.g_varchar2
987   ,p_addr_attribute18              in     varchar2 default hr_api.g_varchar2
988   ,p_addr_attribute19              in     varchar2 default hr_api.g_varchar2
989   ,p_addr_attribute20              in     varchar2 default hr_api.g_varchar2
990   ,p_add_information13             in     varchar2 default hr_api.g_varchar2
991   ,p_add_information14             in     varchar2 default hr_api.g_varchar2
992   ,p_add_information15             in     varchar2 default hr_api.g_varchar2
993   ,p_add_information16             in     varchar2 default hr_api.g_varchar2
994   ,p_add_information17             in     varchar2 default hr_api.g_varchar2
995   ,p_add_information18             in     varchar2 default hr_api.g_varchar2
996   ,p_add_information19             in     varchar2 default hr_api.g_varchar2
997   ,p_add_information20             in     varchar2 default hr_api.g_varchar2
998   ,p_item_type                     in     varchar2
999   ,p_item_key                      in     varchar2
1000   ,p_activity_id                   in     number
1001   ,p_person_id                     in     number
1002   --
1003   -- PB Add :
1004   -- The transaction steps have to be created by the login personid.
1005   -- In case of adding phones for contacts parent_is is contact_person_id.
1006   -- Login person id is say employee who is adding the phones to his contact.
1007   --
1008   --TEST
1009   ,p_contact_or_person             in     varchar2 default null
1010   ,p_login_person_id               in     number default null
1011   ,p_primary_flag                  in     varchar2
1012   ,p_style                         in     varchar2
1013   ,p_action                        in     varchar2
1014   ,p_save_mode                     in     varchar2 default null
1015   ,p_error_message                 out nocopy    long
1016   , p_contact_relationship_id       in number           default hr_api.g_number
1017 )
1018   IS
1019 
1020   l_proc varchar2(200) := g_package || 'update_person_address';
1021   l_old_ovn            number;
1022   l_old_address_id     per_addresses.address_id%TYPE;
1023     --startregistration anupam
1024   l_primary_flag       per_addresses.primary_flag%TYPE;
1025   --endregistration anupam
1026   l_review_item_name                        varchar2(50);
1027   l_date_to                                 date;
1028   l_date_from                               date;
1029   l_transaction_id             number default null;
1030   l_result                     varchar2(100) default null;
1031 
1032 
1033   BEGIN
1034     hr_utility.set_location(' Entering:' || l_proc,5);
1035  -- save the the old address id and old object version number in temp variables
1036     l_old_ovn := p_object_version_number;
1037     l_old_address_id := p_address_id;
1038     --startregistration anupam
1039     -- If coming from overview page and updating the third address then the
1040     -- value of primary flag could be "T". This is required to be stored in
1041     -- transaction tables as "T" but the api validation will be as "N" only and
1042     -- finally it will go in database as N in process_api
1043     l_primary_flag := p_primary_flag;
1044     if p_primary_flag = 'T' then
1045     hr_utility.set_location( l_proc,10);
1046     l_primary_flag := 'N';
1047     end if;
1048     -- replacing the p_primary_flag with l_primary_flag in api_calls
1049     -- endregistration anupam
1050 
1051     IF (p_save_mode = 'SAVE_FOR_LATER') THEN
1052         hr_utility.set_location( l_proc,15);
1053        GOTO only_transaction;
1054     END IF;
1055 
1056 
1057     -- Call the actual API.
1058 --    savepoint update_address;
1059     IF (UPPER(p_action) = 'DELETE') THEN
1060      hr_utility.set_location( l_proc,20);
1061       hr_person_address_api.update_person_address
1062         (p_validate                      => hr_java_conv_util_ss.get_boolean (
1063                                               p_number => p_validate
1064                                             )
1065         ,p_effective_date                => p_effective_date
1066         ,p_validate_county               => hr_java_conv_util_ss.get_boolean (
1067                                              p_number => p_validate_county
1068                                             )
1069         ,p_address_id                    => p_address_id
1070         ,p_object_version_number         => p_object_version_number
1071         ,p_date_from                     => p_date_from
1072         ,p_date_to                       => p_date_to
1073       );
1074 
1075     ELSE --Bug#3114508 start
1076      hr_utility.set_location( l_proc,25);
1077       hr_person_address_api.update_pers_addr_with_style  --Bug#3114508 end
1078         (p_validate                      => hr_java_conv_util_ss.get_boolean (
1079                                               p_number => p_validate
1080                                             )
1081         ,p_effective_date                => p_effective_date
1082         ,p_validate_county               => hr_java_conv_util_ss.get_boolean (
1083                                              p_number => p_validate_county
1084                                             )
1085         ,p_address_id                    => p_address_id
1086         ,p_object_version_number         => p_object_version_number
1087         ,p_date_from                     => p_date_from
1088         ,p_date_to                       => p_date_to
1089         ,p_address_type                  => p_address_type
1090         ,p_comments                      => p_comments
1091         ,p_address_line1                 => p_address_line1
1092         ,p_address_line2                 => p_address_line2
1093         ,p_address_line3                 => p_address_line3
1094         ,p_town_or_city                  => p_town_or_city
1095         ,p_region_1                      => p_region_1
1096         ,p_region_2                      => p_region_2
1097         ,p_region_3                      => p_region_3
1098         ,p_postal_code                   => p_postal_code
1099         ,p_country                       => p_country  --Bug#3114508 start
1100         ,p_style                         => p_style   --Bug#3114508 end
1101         ,p_telephone_number_1            => p_telephone_number_1
1102         ,p_telephone_number_2            => p_telephone_number_2
1103         ,p_telephone_number_3            => p_telephone_number_3
1104         ,p_addr_attribute_category       => p_addr_attribute_category
1105         ,p_addr_attribute1               => p_addr_attribute1
1106         ,p_addr_attribute2               => p_addr_attribute2
1107         ,p_addr_attribute3               => p_addr_attribute3
1108         ,p_addr_attribute4               => p_addr_attribute4
1109         ,p_addr_attribute5               => p_addr_attribute5
1110         ,p_addr_attribute6               => p_addr_attribute6
1111         ,p_addr_attribute7               => p_addr_attribute7
1112         ,p_addr_attribute8               => p_addr_attribute8
1113         ,p_addr_attribute9               => p_addr_attribute9
1114         ,p_addr_attribute10              => p_addr_attribute10
1115         ,p_addr_attribute11              => p_addr_attribute11
1116         ,p_addr_attribute12              => p_addr_attribute12
1117         ,p_addr_attribute13              => p_addr_attribute13
1118         ,p_addr_attribute14              => p_addr_attribute14
1119         ,p_addr_attribute15              => p_addr_attribute15
1120         ,p_addr_attribute16              => p_addr_attribute16
1121         ,p_addr_attribute17              => p_addr_attribute17
1122         ,p_addr_attribute18              => p_addr_attribute18
1123         ,p_addr_attribute19              => p_addr_attribute19
1124         ,p_addr_attribute20              => p_addr_attribute20
1125         ,p_add_information13             => p_add_information13
1126         ,p_add_information14             => p_add_information14
1127         ,p_add_information15             => p_add_information15
1128         ,p_add_information16             => p_add_information16
1129         ,p_add_information17             => p_add_information17
1130         ,p_add_information18             => p_add_information18
1131         ,p_add_information19             => p_add_information19
1132         ,p_add_information20             => p_add_information20
1133       );
1134     END IF;
1135 
1136  -- -----------------------------------------------------------------------------
1137  -- We will write the data to transaction tables.
1138  -- Determine if a transaction step exists for this activity
1139  -- if a transaction step does exist then the transaction_step_id and
1140  -- object_version_number are set (i.e. not null).
1141  -- -----------------------------------------------------------------------------
1142 
1143     <<only_transaction>> -- label for GOTO
1144     hr_utility.set_location( l_proc,30);
1145 -- Set the P_EFFECTIVE_DATE and CURRENT_EFFECTIVE_DATE in wf item attributes to be retreived
1146 -- in review page
1147 
1148        wf_engine.setItemAttrText (itemtype => p_item_type,
1149                            itemkey  => p_item_key,
1150                            aname    => 'P_EFFECTIVE_DATE',
1151                            avalue   =>  to_char(p_effective_date,
1152                                         g_date_format));
1153 
1154        wf_engine.setItemAttrDate (itemtype => p_item_type,
1155                            itemkey  => p_item_key,
1156                            aname    => 'CURRENT_EFFECTIVE_DATE',
1157                            avalue   =>  p_effective_date);
1158 
1159   -- First, check if transaction id exists or not
1160        l_transaction_id := hr_transaction_ss.get_transaction_id
1161                      (p_item_type   => p_item_type
1162                      ,p_item_key    => p_item_key);
1163   --
1164        IF l_transaction_id is null THEN
1165         hr_utility.set_location( l_proc,35);
1166      -- Start a Transaction
1167         hr_transaction_ss.start_transaction
1168            (itemtype   => p_item_type
1169            ,itemkey    => p_item_key
1170            ,actid      => p_activity_id
1171            ,funmode    => 'RUN'
1172            --TEST
1173            ,p_api_addtnl_info => p_contact_or_person
1174            -- PB : For creating address for a contact use
1175            -- use login person id.
1176            --
1177            ,p_login_person_id => nvl(p_login_person_id, p_person_id)
1178            ,result     => l_result);
1179 
1180         l_transaction_id := hr_transaction_ss.get_transaction_id
1181                         (p_item_type   => p_item_type
1182                         ,p_item_key    => p_item_key);
1183        END IF;
1184   --
1185   -- Create a transaction step
1186   --
1187         hr_transaction_api.create_transaction_step
1188            (p_validate              => false
1189            -- PB : For creating address for a contact use
1190            -- use login person id.
1191            --
1192            ,p_creator_person_id     => nvl(p_login_person_id, p_person_id) -- p_person_id
1193            ,p_transaction_id        => l_transaction_id
1194            ,p_api_name              => g_package || '.PROCESS_API'
1195            ,p_item_type             => p_item_type
1196            ,p_item_key              => p_item_key
1197            ,p_activity_id           => p_activity_id
1198            ,p_transaction_step_id   => l_transaction_step_id
1199            ,p_object_version_number => l_trs_object_version_number);
1200   --
1201 
1202 	l_count := 1;
1203  	l_transaction_table(l_count).param_name := 'P_PERSON_ID';
1204  	l_transaction_table(l_count).param_value := p_person_id;
1205  	l_transaction_table(l_count).param_data_type := 'NUMBER';
1206 
1207 --	IF UPPER(p_action) = 'DELETE' THEN
1208                hr_utility.set_location( l_proc,40);
1209 --		l_effective_date := to_char(trunc(sysdate),l_user_date_format);
1210 --	ELSE
1211                hr_utility.set_location( l_proc,45);
1212 --		l_effective_date := p_effective_date;
1213 --	END IF;
1214 
1215         l_count := l_count + 1;
1216         l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
1217         l_transaction_table(l_count).param_value := to_char(p_effective_date,
1218                                                     hr_transaction_ss.g_date_format);
1219         l_transaction_table(l_count).param_data_type := 'DATE';
1220 
1221 
1222 -- 	l_count := l_count + 1;
1223 -- 	l_transaction_table(l_count).param_name := 'P_USER_DATE_FORMAT';
1224 -- 	l_transaction_table(l_count).param_value := l_user_date_format;
1225 -- 	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1226 
1227  	l_count := l_count + 1;
1228  	l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
1229  	l_transaction_table(l_count).param_value := p_object_version_number;
1230  	l_transaction_table(l_count).param_data_type := 'NUMBER';
1231 
1232  	l_count := l_count + 1;
1233  	l_transaction_table(l_count).param_name
1234 			:= 'P_OLD_OBJECT_VERSION_NUMBER';
1235  	l_transaction_table(l_count).param_value := l_old_ovn;
1236  	l_transaction_table(l_count).param_data_type := 'NUMBER';
1237 
1238  	l_count := l_count + 1;
1239  	l_transaction_table(l_count).param_name := 'P_ADDRESS_ID';
1240  	l_transaction_table(l_count).param_value := p_address_id;
1241  	l_transaction_table(l_count).param_data_type := 'NUMBER';
1242 
1243  	l_count := l_count + 1;
1244  	l_transaction_table(l_count).param_name := 'P_OLD_ADDRESS_ID';
1245  	l_transaction_table(l_count).param_value := l_old_address_id;
1246  	l_transaction_table(l_count).param_data_type := 'NUMBER';
1247 
1248  	l_count := l_count + 1;
1249  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE1';
1250  	l_transaction_table(l_count).param_value := p_address_line1;
1251  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1252 
1253  	l_count := l_count + 1;
1254  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE2';
1255  	l_transaction_table(l_count).param_value := p_address_line2;
1256  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1257 
1258  	l_count := l_count + 1;
1259  	l_transaction_table(l_count).param_name := 'P_ADDRESS_LINE3';
1260  	l_transaction_table(l_count).param_value := p_address_line3;
1261  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1262 
1263  	l_count := l_count + 1;
1264  	l_transaction_table(l_count).param_name := 'P_CITY';
1265  	l_transaction_table(l_count).param_value := p_town_or_city;
1266  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1267 
1268  	l_count := l_count + 1;
1269  	l_transaction_table(l_count).param_name := 'P_REGION1';
1270  	l_transaction_table(l_count).param_value := p_region_1;
1271  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1272 
1273 -- 	l_count := l_count + 1;
1274 -- 	l_transaction_table(l_count).param_name := 'P_STATE';
1275 -- 	l_transaction_table(l_count).param_value := p_state;
1276 -- 	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1277 
1278  	l_count := l_count + 1;
1279  	l_transaction_table(l_count).param_name := 'P_REGION2';
1280  	l_transaction_table(l_count).param_value := p_region_2;
1281  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1282 
1283  	l_count := l_count + 1;
1284  	l_transaction_table(l_count).param_name := 'P_REGION3';
1285  	l_transaction_table(l_count).param_value := p_region_3;
1286  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1287 
1288  	l_count := l_count + 1;
1289  	l_transaction_table(l_count).param_name := 'P_COUNTRY';
1290  	l_transaction_table(l_count).param_value := p_country_meaning;
1291  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1292 
1293  	l_count := l_count + 1;
1294  	l_transaction_table(l_count).param_name := 'P_COUNTRY_CODE';
1295  	l_transaction_table(l_count).param_value := p_country;
1296  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1297 
1298  	l_count := l_count + 1;
1299  	l_transaction_table(l_count).param_name := 'P_POSTAL_CODE';
1300  	l_transaction_table(l_count).param_value := p_postal_code;
1301  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1302 
1303  	l_count := l_count + 1;
1304  	l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE';
1305  	l_transaction_table(l_count).param_value := p_address_type_meaning;
1306  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1307 
1308  	l_count := l_count + 1;
1309  	l_transaction_table(l_count).param_name := 'P_ADDRESS_TYPE_CODE';
1310  	l_transaction_table(l_count).param_value := p_address_type;
1311  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1312 
1313  	l_count := l_count + 1;
1314  	l_transaction_table(l_count).param_name := 'P_PRADD_OVLAPVAL_OVERRIDE';
1315  	l_transaction_table(l_count).param_value := 'N';
1316  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1317 
1318         l_count := l_count + 1;
1319         l_transaction_table(l_count).param_name := 'P_DATE_FROM';
1320         l_transaction_table(l_count).param_value := to_char(p_date_from,
1321                                                     hr_transaction_ss.g_date_format);
1322         l_transaction_table(l_count).param_data_type := 'DATE';
1323 
1324  	l_count := l_count + 1;
1325  	l_transaction_table(l_count).param_name := 'P_DATE_TO';
1326         l_transaction_table(l_count).param_value := to_char(p_date_to,
1327                                                     hr_transaction_ss.g_date_format);
1328         l_transaction_table(l_count).param_data_type := 'DATE';
1329 
1330  	l_count := l_count + 1;
1331  	l_transaction_table(l_count).param_name := 'P_STYLE';
1332  	l_transaction_table(l_count).param_value := p_style;
1333  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1334 
1335  	l_count := l_count + 1;
1336  	l_transaction_table(l_count).param_name := 'P_PRIMARY_FLAG';
1337  	l_transaction_table(l_count).param_value := p_primary_flag;
1338  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1339 
1340  	l_count := l_count + 1;
1341  	l_transaction_table(l_count).param_name := 'P_ACTION';
1342  	l_transaction_table(l_count).param_value := UPPER(p_action);
1343  	l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1344 
1345         l_count := l_count + 1;
1346         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER1';
1347         l_transaction_table(l_count).param_value := p_telephone_number_1;
1348         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1349 
1350         l_count := l_count + 1;
1351         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER2';
1352         l_transaction_table(l_count).param_value := p_telephone_number_2;
1353         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1354 
1355         l_count := l_count + 1;
1356         l_transaction_table(l_count).param_name := 'P_TELEPHONE_NUMBER3';
1357         l_transaction_table(l_count).param_value := p_telephone_number_3;
1358         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1359 
1360 -- Now add all the Descriptive flex fields into transactions tables
1361 
1362         l_count := l_count + 1; -- CONTEXT
1363         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE_CATEGORY';
1364         l_transaction_table(l_count).param_value := p_addr_attribute_category;
1365         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1366 
1367         l_count := l_count + 1;
1368         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE1';
1369         l_transaction_table(l_count).param_value := p_addr_attribute1;
1370         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1371 
1372         l_count := l_count + 1;
1373         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE2';
1374         l_transaction_table(l_count).param_value := p_addr_attribute2;
1375         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1376 
1377         l_count := l_count + 1;
1378         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE3';
1379         l_transaction_table(l_count).param_value := p_addr_attribute3;
1380         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1381 
1382         l_count := l_count + 1;
1383         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE4';
1384         l_transaction_table(l_count).param_value := p_addr_attribute4;
1385         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1386 
1387         l_count := l_count + 1;
1388         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE5';
1389         l_transaction_table(l_count).param_value := p_addr_attribute5;
1390         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1391 
1392         l_count := l_count + 1;
1393         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE6';
1394         l_transaction_table(l_count).param_value := p_addr_attribute6;
1395         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1396 
1397         l_count := l_count + 1;
1398         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE7';
1399         l_transaction_table(l_count).param_value := p_addr_attribute7;
1400         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1401 
1402         l_count := l_count + 1;
1403         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE8';
1404         l_transaction_table(l_count).param_value := p_addr_attribute8;
1405         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1406 
1407         l_count := l_count + 1;
1408         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE9';
1409         l_transaction_table(l_count).param_value := p_addr_attribute9;
1410         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1411 
1412         l_count := l_count + 1;
1413         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE10';
1414         l_transaction_table(l_count).param_value := p_addr_attribute10;
1415         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1416 
1417         l_count := l_count + 1;
1418         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE11';
1419         l_transaction_table(l_count).param_value := p_addr_attribute11;
1420         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1421 
1422         l_count := l_count + 1;
1423         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE12';
1424         l_transaction_table(l_count).param_value := p_addr_attribute12;
1425         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1426 
1427         l_count := l_count + 1;
1428         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE13';
1429         l_transaction_table(l_count).param_value := p_addr_attribute13;
1430         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1431 
1432         l_count := l_count + 1;
1433         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE14';
1434         l_transaction_table(l_count).param_value := p_addr_attribute14;
1435         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1436 
1437         l_count := l_count + 1;
1438         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE15';
1439         l_transaction_table(l_count).param_value := p_addr_attribute15;
1440         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1441 
1442         l_count := l_count + 1;
1443         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE16';
1444         l_transaction_table(l_count).param_value := p_addr_attribute16;
1445         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1446 
1447         l_count := l_count + 1;
1448         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE17';
1449         l_transaction_table(l_count).param_value := p_addr_attribute17;
1450         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1451 
1452         l_count := l_count + 1;
1453         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE18';
1454         l_transaction_table(l_count).param_value := p_addr_attribute18;
1455         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1456 
1457         l_count := l_count + 1;
1458         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE19';
1459         l_transaction_table(l_count).param_value := p_addr_attribute19;
1460         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1461 
1462 
1463         l_count := l_count + 1;
1464         l_transaction_table(l_count).param_name := 'P_ADDR_ATTRIBUTE20';
1465         l_transaction_table(l_count).param_value := p_addr_attribute20;
1466         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1467 
1468 
1469         l_count := l_count + 1;
1470         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION13';
1471         l_transaction_table(l_count).param_value := p_add_information13;
1472         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1473 
1474         l_count := l_count + 1;
1475         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION14';
1476         l_transaction_table(l_count).param_value := p_add_information14;
1477         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1478 
1479 
1480         l_count := l_count + 1;
1481         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION15';
1482         l_transaction_table(l_count).param_value := p_add_information15;
1483         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1484 
1485 
1486         l_count := l_count + 1;
1487         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION16';
1488         l_transaction_table(l_count).param_value := p_add_information16;
1489         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1490 
1491 
1492         l_count := l_count + 1;
1493         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION17';
1494         l_transaction_table(l_count).param_value := p_add_information17;
1495         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1496 
1497         l_count := l_count + 1;
1498         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION18';
1499         l_transaction_table(l_count).param_value := p_add_information18;
1500         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1501 
1502         l_count := l_count + 1;
1503         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION19';
1504         l_transaction_table(l_count).param_value := p_add_information19;
1505         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1506 
1507         l_count := l_count + 1;
1508         l_transaction_table(l_count).param_name := 'P_ADD_INFORMATION20';
1509         l_transaction_table(l_count).param_value := p_add_information20;
1510         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1511 
1512         l_count := l_count + 1;
1513         l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
1514         l_transaction_table(l_count).param_value := p_activity_id;
1515         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1516 
1517         BEGIN
1518 	   hr_utility.set_location( l_proc,50);
1519           l_review_item_name := wf_engine.GetActivityAttrText(itemtype  => p_item_type,
1520                                                   itemkey   => p_item_key,
1521                                                   actid     => p_activity_id,
1522                                                   aname     => gv_wf_review_region_item);
1523         EXCEPTION
1524         WHEN OTHERS THEN
1525 	    hr_utility.set_location( l_proc || 'EXCEPTION' ,555);
1526            l_review_item_name := 'HrMainAddressReview';
1527         END;
1528 
1529         l_count := l_count + 1;
1530         l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
1531         l_transaction_table(l_count).param_value := l_review_item_name;
1532         l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1533         if (p_contact_relationship_id > 0) then
1534 	  hr_utility.set_location( l_proc,55);
1535          l_count := l_count + 1;
1536          l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
1537          l_transaction_table(l_count).param_value := p_contact_relationship_id;
1538          l_transaction_table(l_count).param_data_type := 'NUMBER';
1539         end if;
1540 
1541 
1542 	 hr_transaction_ss.save_transaction_step
1543        		(p_item_type => p_item_type
1544        		,p_item_key => p_item_key
1545        	        ,p_login_person_id => nvl(p_login_person_id, p_person_id	)
1546                 ,p_actid => p_activity_id
1547        		,p_transaction_step_id => l_transaction_step_id
1548        		,p_api_name => g_package || '.PROCESS_API'
1549        		,p_transaction_data => l_transaction_table);
1550 
1551 
1552 hr_utility.set_location(' Leaving:' || l_proc,60);
1553 
1554   EXCEPTION
1555         WHEN hr_utility.hr_error THEN
1556 	 hr_utility.set_location(' Leaving:' || l_proc,560);
1557 
1558          -- -------------------------------------------
1559          -- an application error has been raised so we must
1560          -- redisplay the web form to display the error
1561          -- --------------------------------------------
1562          hr_message.provide_error;
1563          l_message_number := hr_message.last_message_number;
1564          IF l_message_number = 'APP-7165' OR
1565             l_message_number = 'APP-7155' THEN
1566    --populate the p_error_message out variable
1567           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1568                              p_error_message => p_error_message,
1569                              p_attr_name => 'Page',
1570                              p_app_short_name => 'PER',
1571                              p_message_name => 'HR_UPDATE_NOT_ALLOWED');
1572          ELSIF l_message_number = 'APP-51139' THEN
1573           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1574                              p_error_message => p_error_message,
1575                              p_attr_name => 'AddressType',
1576                              p_app_short_name => 'PER',
1577                              p_message_name => 'HR_PERINFO_INVALID_ADDR_TYPE');
1578          ELSIF l_message_number = 'APP-7952' OR
1579                l_message_number = 'APP-7953' OR
1580                l_message_number = 'APP-51276' OR
1581                l_message_number = 'APP-51282'  THEN
1582           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1583                              p_error_message => p_error_message,
1584                              p_app_short_name => 'PER',
1585                              p_message_name => 'HR_INVALID_CITYSTATEZIPCOUNTY');
1586          ELSE
1587           p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1588                              p_error_message => p_error_message);
1589          END IF;
1590     WHEN OTHERS THEN
1591     hr_utility.set_location(' Leaving:' || l_proc,565);
1592 
1593     p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
1594                              p_error_message => p_error_message);
1595   END update_person_address;
1596 
1597 -- ---------------------- < get_address_data_from_tt> -------------------------
1598 -- ---------------------------------------------------------------------------
1599 -- Purpose: This procedure will get transaction data which are pending for
1600 --          approval in workflow for a given person id, workflow process name
1601 --          and workflow activity name.  This is the overloaded version.
1602 -- ---------------------------------------------------------------------------
1603 PROCEDURE get_address_data_from_tt
1604    (p_item_type                       in     varchar2
1605    ,p_process_name                    in     varchar2
1606    ,p_activity_name                   in     varchar2
1607    ,p_current_person_id               in     varchar2
1608    ,p_effective_date                  out nocopy    date
1609    ,p_person_id                       out nocopy number
1610    ,p_address_id                      out nocopy number
1611    ,p_object_version_number           out nocopy number
1612    ,p_primary_flag                    out nocopy varchar2
1613    ,p_style                           out nocopy varchar2
1614    ,p_date_from                       out nocopy date
1615    ,p_date_to                         out nocopy date
1616    ,p_address_type                    out nocopy varchar2
1617    ,p_address_type_meaning            out nocopy varchar2
1618    ,p_comments                        out nocopy varchar2
1619    ,p_address_line1                   out nocopy varchar2
1620    ,p_address_line2                   out nocopy varchar2
1621    ,p_address_line3                   out nocopy varchar2
1622    ,p_town_or_city                    out nocopy varchar2
1623    ,p_region_1                        out nocopy varchar2
1624    ,p_region_2                        out nocopy varchar2
1625    ,p_region_3                        out nocopy varchar2
1626    ,p_postal_code                     out nocopy varchar2
1627    ,p_country                         out nocopy varchar2
1628    ,p_country_meaning                 out nocopy varchar2
1629    ,p_telephone_number_1              out nocopy varchar2
1630    ,p_telephone_number_2              out nocopy varchar2
1631    ,p_telephone_number_3              out nocopy varchar2
1632    ,p_addr_attribute_category         out nocopy varchar2
1633    ,p_addr_attribute1                 out nocopy varchar2
1634    ,p_addr_attribute2                 out nocopy varchar2
1635    ,p_addr_attribute3                 out nocopy varchar2
1636    ,p_addr_attribute4                 out nocopy varchar2
1637    ,p_addr_attribute5                 out nocopy varchar2
1638    ,p_addr_attribute6                 out nocopy varchar2
1639    ,p_addr_attribute7                 out nocopy varchar2
1640    ,p_addr_attribute8                 out nocopy varchar2
1641    ,p_addr_attribute9                 out nocopy varchar2
1642    ,p_addr_attribute10                out nocopy varchar2
1643    ,p_addr_attribute11                out nocopy varchar2
1644    ,p_addr_attribute12                out nocopy varchar2
1645    ,p_addr_attribute13                out nocopy varchar2
1646    ,p_addr_attribute14                out nocopy varchar2
1647    ,p_addr_attribute15                out nocopy varchar2
1648    ,p_addr_attribute16                out nocopy varchar2
1649    ,p_addr_attribute17                out nocopy varchar2
1650    ,p_addr_attribute18                out nocopy varchar2
1651    ,p_addr_attribute19                out nocopy varchar2
1652    ,p_addr_attribute20                out nocopy varchar2
1653    ,p_add_information17               out nocopy varchar2
1654    ,p_add_information18               out nocopy varchar2
1655    ,p_add_information19               out nocopy varchar2
1656    ,p_add_information20               out nocopy varchar2
1657    ,p_action                          out nocopy varchar2
1658    ,p_old_address_id                  out nocopy varchar2
1659    ,p_add_information13               out nocopy varchar2
1660    ,p_add_information14               out nocopy varchar2
1661    ,p_add_information15               out nocopy varchar2
1662    ,p_add_information16               out nocopy varchar2
1663 )is
1664 
1665   l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
1666   l_transaction_id             number;
1667   l_trans_step_id              number;
1668   l_trans_obj_vers_num         number;
1669   l_active_wf_items_tbl        hr_workflow_service.active_wf_items_list;
1670   l_active_item_keys_tbl       hr_workflow_service.active_wf_items_list;
1671   l_count                      integer default 0;
1672 --  l_trans_rec_count      integer default 0;
1673   l_trans_rec_count      number;
1674 begin
1675 
1676   hr_utility.set_location(' Entering:' || l_proc,5);
1677 
1678   -- ------------------------------------------------------------------
1679   -- Check if there are any transactions waiting to be approved.
1680   -----------------------------------------------------------------------------
1681 
1682   -- 1) Find all item keys which have a status of "ACTIVE" for p_process_name
1683   -- 2) Then for those item keys, check if there are any transaction steps
1684   --    exist. There can be defunct workflow processes.  Therefore, we must
1685   --    match active processes with transaction tables.
1686   -- 3) If transaction steps are found, check that if the item key is for the
1687   --    particular activity (derive the activity_id from p_activity_name).
1688   --    If found, then there are pending approval transaction data.
1689   --
1690   -- Following function will return a PL/SQL table which has following
1691   -- fields : Item Key, Activity ID
1692   -----------------------------------------------------------------------------
1693   l_active_wf_items_tbl := hr_workflow_service.check_active_wf_items
1694                           (p_item_type           => p_item_type
1695                           ,p_process_name        => p_process_name
1696                           ,p_current_person_id   => p_current_person_id
1697                           ,p_activity_name       => p_activity_name);
1698 
1699   l_count := l_active_wf_items_tbl.COUNT;
1700 
1701   IF l_count > 0
1702      -- -------------------------------------------------------------------
1703      -- There are some transactions waiting to be approved for the given
1704      -- process, person and activity name.
1705      -- There can only be 1 row returned but not more for
1706      -- 'HR_MAINT_PERSONAL_DETAILS_FRM', 'HR_MAINT_MAIN_ADDRESS_FRM',
1707      -- 'HR_MAINT_SECONDARY_ADDRESS_FRM .  For contacts, there can be ??? rows.
1708      -- -------------------------------------------------------------------
1709   THEN
1710     hr_utility.set_location(l_proc,10);
1711 
1712 
1713      -- For Personal Information, there can only be 1 transaction step per
1714      -- item type and item key, therefore, we don't need to handle the multiple
1715      -- rows situation.
1716 
1717      FOR i in 1..l_count LOOP
1718          hr_transaction_api.get_transaction_step_info
1719           (p_item_type             => p_item_type
1720           ,p_item_key              => l_active_wf_items_tbl(i).active_item_key
1721           ,p_activity_id           => l_active_wf_items_tbl(i).activity_id
1722           ,p_transaction_step_id   => l_trans_step_id
1723           ,p_object_version_number => l_trans_obj_vers_num);
1724      END LOOP;
1725   ELSE
1726      hr_utility.set_location(l_proc,15);
1727      l_trans_rec_count := 0;
1728 --     p_trans_rec_count := to_char(l_trans_rec_count);
1729      p_trans_rec_count := l_trans_rec_count;
1730      RETURN;
1731   END IF;
1732 
1733   -- If we are here, that means we've found a pending wf item and there should
1734   -- be matching transaction records.  If we cannot find any transaction step,
1735   -- that means we have a data integrity error.
1736   IF l_trans_step_id is NULL OR
1737      l_trans_step_id = 0
1738   THEN
1739      hr_utility.set_location(l_proc,20);
1740      RAISE g_data_error;
1741   END IF;
1742 --
1743 -- Now get the transaction data for the given step
1744   get_address_data_from_tt(
1745     p_transaction_step_id            => l_trans_step_id
1746    ,p_effective_date                 => p_effective_date
1747    ,p_person_id                      => p_person_id
1748    ,p_address_id                     => p_address_id
1749    ,p_object_version_number          => p_object_version_number
1750    ,p_primary_flag                   => p_primary_flag
1751    ,p_style                          => p_style
1752    ,p_date_from                      => p_date_from
1753    ,p_date_to                        => p_date_to
1754    ,p_address_type                   => p_address_type
1755    ,p_address_type_meaning           => p_address_type_meaning
1756    ,p_comments                       => p_comments
1757    ,p_address_line1                  => p_address_line1
1758    ,p_address_line2                  => p_address_line2
1759    ,p_address_line3                  => p_address_line3
1760    ,p_town_or_city                   => p_town_or_city
1761    ,p_region_1                       => p_region_1
1762    ,p_region_2                       => p_region_2
1763    ,p_region_3                       => p_region_3
1764    ,p_postal_code                    => p_postal_code
1765    ,p_country                        => p_country
1766    ,p_country_meaning                => p_country_meaning
1767    ,p_telephone_number_1             => p_telephone_number_1
1768    ,p_telephone_number_2             => p_telephone_number_2
1769    ,p_telephone_number_3             => p_telephone_number_3
1770    ,p_addr_attribute_category        => p_addr_attribute_category
1771    ,p_addr_attribute1                => p_addr_attribute1
1772    ,p_addr_attribute2                => p_addr_attribute2
1773    ,p_addr_attribute3                => p_addr_attribute3
1774    ,p_addr_attribute4                => p_addr_attribute4
1775    ,p_addr_attribute5                => p_addr_attribute5
1776    ,p_addr_attribute6                => p_addr_attribute6
1777    ,p_addr_attribute7                => p_addr_attribute7
1778    ,p_addr_attribute8                => p_addr_attribute8
1779    ,p_addr_attribute9                => p_addr_attribute9
1780    ,p_addr_attribute10               => p_addr_attribute10
1781    ,p_addr_attribute11               => p_addr_attribute11
1782    ,p_addr_attribute12               => p_addr_attribute12
1783    ,p_addr_attribute13               => p_addr_attribute13
1784    ,p_addr_attribute14               => p_addr_attribute14
1785    ,p_addr_attribute15               => p_addr_attribute15
1786    ,p_addr_attribute16               => p_addr_attribute16
1787    ,p_addr_attribute17               => p_addr_attribute17
1788    ,p_addr_attribute18               => p_addr_attribute18
1789    ,p_addr_attribute19               => p_addr_attribute19
1790    ,p_addr_attribute20               => p_addr_attribute20
1791    ,p_add_information17              => p_add_information17
1792    ,p_add_information18              => p_add_information18
1793    ,p_add_information19              => p_add_information19
1794    ,p_add_information20              => p_add_information20
1795    ,p_action                         => p_action
1796    ,p_old_address_id                 => p_old_address_id
1797    ,p_add_information13              => p_add_information13
1798    ,p_add_information14              => p_add_information14
1799    ,p_add_information15              => p_add_information15
1800    ,p_add_information16              => p_add_information16
1801 );
1802 
1803  -- p_trans_rec_count := to_char(l_trans_rec_count);
1804   p_trans_rec_count := l_trans_rec_count;
1805 
1806 
1807 hr_utility.set_location(' Leaving:' || l_proc,25);
1808 
1809 EXCEPTION
1810   WHEN g_data_error THEN
1811      hr_utility.set_location(' Leaving:' || l_proc,555);
1812      RAISE;
1813 
1814 END get_address_data_from_tt;
1815 
1816 
1817 -- ---------------------------------------------------------------------------
1818 -- ---------------------- < get_address_data_from_tt> -------------------------
1819 -- ---------------------------------------------------------------------------
1820 -- Purpose: This procedure will get transaction data which are saved earlier
1821 --          in the current transaction.  This is invoked when a user click BACK
1822 --          button to go back from the Review page to Update page to correct
1823 --          typos or make further changes.  Hence, we need to use the item_type
1824 --          item_key passed in to retrieve the transaction record.
1825 --          This is an overloaded version.
1826 -- ---------------------------------------------------------------------------
1827 PROCEDURE get_address_data_from_tt
1828    (p_item_type                       in  varchar2
1829    ,p_item_key                        in  varchar2
1830    ,p_activity_id                     in  varchar2
1831    ,p_trans_rec_count                 out nocopy number
1832    ,p_effective_date                  out nocopy    date
1833    ,p_person_id                       out nocopy number
1834    ,p_address_id                      out nocopy number
1835    ,p_object_version_number           out nocopy number
1836    ,p_primary_flag                    out nocopy varchar2
1837    ,p_style                           out nocopy varchar2
1838    ,p_date_from                       out nocopy date
1839    ,p_date_to                         out nocopy date
1840    ,p_address_type                    out nocopy varchar2
1841    ,p_address_type_meaning            out nocopy varchar2
1842    ,p_comments                        out nocopy varchar2
1843    ,p_address_line1                   out nocopy varchar2
1844    ,p_address_line2                   out nocopy varchar2
1845    ,p_address_line3                   out nocopy varchar2
1846    ,p_town_or_city                    out nocopy varchar2
1847    ,p_region_1                        out nocopy varchar2
1848    ,p_region_2                        out nocopy varchar2
1849    ,p_region_3                        out nocopy varchar2
1850    ,p_postal_code                     out nocopy varchar2
1851    ,p_country                         out nocopy varchar2
1852    ,p_country_meaning                 out nocopy varchar2
1853    ,p_telephone_number_1              out nocopy varchar2
1854    ,p_telephone_number_2              out nocopy varchar2
1855    ,p_telephone_number_3              out nocopy varchar2
1856    ,p_addr_attribute_category         out nocopy varchar2
1857    ,p_addr_attribute1                 out nocopy varchar2
1858    ,p_addr_attribute2                 out nocopy varchar2
1859    ,p_addr_attribute3                 out nocopy varchar2
1860    ,p_addr_attribute4                 out nocopy varchar2
1861    ,p_addr_attribute5                 out nocopy varchar2
1862    ,p_addr_attribute6                 out nocopy varchar2
1863    ,p_addr_attribute7                 out nocopy varchar2
1864    ,p_addr_attribute8                 out nocopy varchar2
1865    ,p_addr_attribute9                 out nocopy varchar2
1866    ,p_addr_attribute10                out nocopy varchar2
1867    ,p_addr_attribute11                out nocopy varchar2
1868    ,p_addr_attribute12                out nocopy varchar2
1869    ,p_addr_attribute13                out nocopy varchar2
1870    ,p_addr_attribute14                out nocopy varchar2
1871    ,p_addr_attribute15                out nocopy varchar2
1872    ,p_addr_attribute16                out nocopy varchar2
1873    ,p_addr_attribute17                out nocopy varchar2
1874    ,p_addr_attribute18                out nocopy varchar2
1875    ,p_addr_attribute19                out nocopy varchar2
1876    ,p_addr_attribute20                out nocopy varchar2
1877    ,p_add_information17               out nocopy varchar2
1878    ,p_add_information18               out nocopy varchar2
1879    ,p_add_information19               out nocopy varchar2
1880    ,p_add_information20               out nocopy varchar2
1881    ,p_action                          out nocopy varchar2
1882    ,p_old_address_id                  out nocopy varchar2
1883    ,p_add_information13               out nocopy varchar2
1884    ,p_add_information14               out nocopy varchar2
1885    ,p_add_information15               out nocopy varchar2
1886    ,p_add_information16               out nocopy varchar2
1887 ) is
1888 
1889    l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
1890    l_trans_step_ids       hr_util_web.g_varchar2_tab_type;
1891    l_api_names  hr_util_web.g_varchar2_tab_type;
1892    l_trans_step_rows                  NUMBER  ;
1893 
1894    l_trans_rec_count                  integer default 0;
1895 
1896  BEGIN
1897 
1898   hr_utility.set_location(' Entering:' || l_proc,5);
1899 
1900   -- ------------------------------------------------------------------
1901   -- Check if there are any transaction rec already saved for the current
1902   -- transaction. This is used for re-display the Update page when a user
1903   -- clicks the Back button on the Review page to go back to the Update page
1904   -- to make further changes or to correct errors.
1905   -----------------------------------------------------------------------------
1906   hr_transaction_api.get_transaction_step_info
1907      (p_item_type              => p_item_type
1908      ,p_item_key               => p_item_key
1909      ,p_activity_id            => p_activity_id
1910      ,p_transaction_step_id    => l_trans_step_ids
1911      ,p_api_name               => l_api_names
1912      ,p_rows                   => l_trans_step_rows);
1913 
1914 
1915   IF l_trans_step_rows IS NOT NULL OR
1916      l_trans_step_rows > 0
1917   THEN
1918      hr_utility.set_location(l_proc,10);
1919      l_trans_rec_count := l_trans_step_rows;
1920   ELSE
1921      hr_utility.set_location(l_proc,15);
1922      l_trans_rec_count := 0;
1923      hr_utility.set_location(' Leaving:' || l_proc,20);
1924 
1925      return;
1926   END IF;
1927   --
1928   -- -------------------------------------------------------------------
1929   -- There are some changes made earlier in the transaction.
1930   -- Retrieve the data and return to caller.
1931   -- -------------------------------------------------------------------
1932   --
1933   -- Now get the transaction data for the given step
1934   -- Need to loop through l_trans_rec_count -1 as the index starts from 0
1935 
1936   FOR i in 0..l_trans_rec_count-1 LOOP
1937    IF(l_api_names(i) = 'HR_PROCESS_ADDRESS_SS.PROCESS_API') THEN
1938       hr_utility.set_location(l_proc  || 'LOOP' ,25);
1939     get_address_data_from_tt(
1940     p_transaction_step_id            => l_trans_step_ids(i)
1941    ,p_effective_date                 => p_effective_date
1942    ,p_person_id                      => p_person_id
1943    ,p_address_id                     => p_address_id
1944    ,p_object_version_number          => p_object_version_number
1945    ,p_primary_flag                   => p_primary_flag
1946    ,p_style                          => p_style
1947    ,p_date_from                      => p_date_from
1948    ,p_date_to                        => p_date_to
1949    ,p_address_type                   => p_address_type
1950    ,p_address_type_meaning           => p_address_type_meaning
1951    ,p_comments                       => p_comments
1952    ,p_address_line1                  => p_address_line1
1953    ,p_address_line2                  => p_address_line2
1954    ,p_address_line3                  => p_address_line3
1955    ,p_town_or_city                   => p_town_or_city
1956    ,p_region_1                       => p_region_1
1957    ,p_region_2                       => p_region_2
1958    ,p_region_3                       => p_region_3
1959    ,p_postal_code                    => p_postal_code
1960    ,p_country                        => p_country
1961    ,p_country_meaning                => p_country_meaning
1962    ,p_telephone_number_1             => p_telephone_number_1
1963    ,p_telephone_number_2             => p_telephone_number_2
1964    ,p_telephone_number_3             => p_telephone_number_3
1965    ,p_addr_attribute_category        => p_addr_attribute_category
1966    ,p_addr_attribute1                => p_addr_attribute1
1967    ,p_addr_attribute2                => p_addr_attribute2
1968    ,p_addr_attribute3                => p_addr_attribute3
1969    ,p_addr_attribute4                => p_addr_attribute4
1970    ,p_addr_attribute5                => p_addr_attribute5
1971    ,p_addr_attribute6                => p_addr_attribute6
1972    ,p_addr_attribute7                => p_addr_attribute7
1973    ,p_addr_attribute8                => p_addr_attribute8
1974    ,p_addr_attribute9                => p_addr_attribute9
1975    ,p_addr_attribute10               => p_addr_attribute10
1976    ,p_addr_attribute11               => p_addr_attribute11
1977    ,p_addr_attribute12               => p_addr_attribute12
1978    ,p_addr_attribute13               => p_addr_attribute13
1979    ,p_addr_attribute14               => p_addr_attribute14
1980    ,p_addr_attribute15               => p_addr_attribute15
1981    ,p_addr_attribute16               => p_addr_attribute16
1982    ,p_addr_attribute17               => p_addr_attribute17
1983    ,p_addr_attribute18               => p_addr_attribute18
1984    ,p_addr_attribute19               => p_addr_attribute19
1985    ,p_addr_attribute20               => p_addr_attribute20
1986    ,p_add_information17              => p_add_information17
1987    ,p_add_information18              => p_add_information18
1988    ,p_add_information19              => p_add_information19
1989    ,p_add_information20              => p_add_information20
1990    ,p_action                         => p_action
1991    ,p_old_address_id                 => p_old_address_id
1992    ,p_add_information13              => p_add_information13
1993    ,p_add_information14              => p_add_information14
1994    ,p_add_information15              => p_add_information15
1995    ,p_add_information16              => p_add_information16
1996    );
1997   END IF;
1998  END LOOP;
1999 
2000 
2001 
2002 --  p_trans_rec_count := to_char(l_trans_rec_count);
2003   p_trans_rec_count := l_trans_rec_count;
2004 
2005 hr_utility.set_location(' Leaving:' || l_proc,30);
2006 
2007 EXCEPTION
2008    WHEN g_data_error THEN
2009    hr_utility.set_location(' Leaving:' || l_proc,555);
2010 
2011       RAISE;
2012 
2013 END get_address_data_from_tt;
2014 
2015 
2016 -- ---------------------------------------------------------------------------
2017 -- ---------------------- < get_address_data_from_tt> -------------------------
2018 -- ---------------------------------------------------------------------------
2019 -- Purpose: This procedure will get transaction data which are pending for
2020 --          approval in workflow for a transaction step id.
2021 -- ---------------------------------------------------------------------------
2022 procedure get_address_data_from_tt
2023    (p_transaction_step_id             in  number
2024    ,p_effective_date                  out nocopy date
2025    ,p_person_id                       out nocopy number
2026    ,p_address_id                      out nocopy number
2027    ,p_object_version_number           out nocopy number
2028    ,p_primary_flag                    out nocopy varchar2
2029    ,p_style                           out nocopy varchar2
2030    ,p_date_from                       out nocopy date
2031    ,p_date_to                         out nocopy date
2032    ,p_address_type                    out nocopy varchar2
2033    ,p_address_type_meaning            out nocopy varchar2
2034    ,p_comments                        out nocopy varchar2
2035    ,p_address_line1                   out nocopy varchar2
2036    ,p_address_line2                   out nocopy varchar2
2037    ,p_address_line3                   out nocopy varchar2
2038    ,p_town_or_city                    out nocopy varchar2
2039    ,p_region_1                        out nocopy varchar2
2040    ,p_region_2                        out nocopy varchar2
2041    ,p_region_3                        out nocopy varchar2
2042    ,p_postal_code                     out nocopy varchar2
2043    ,p_country                         out nocopy varchar2
2044    ,p_country_meaning                 out nocopy varchar2
2045    ,p_telephone_number_1              out nocopy varchar2
2046    ,p_telephone_number_2              out nocopy varchar2
2047    ,p_telephone_number_3              out nocopy varchar2
2048    ,p_addr_attribute_category         out nocopy varchar2
2049    ,p_addr_attribute1                 out nocopy varchar2
2050    ,p_addr_attribute2                 out nocopy varchar2
2051    ,p_addr_attribute3                 out nocopy varchar2
2052    ,p_addr_attribute4                 out nocopy varchar2
2053    ,p_addr_attribute5                 out nocopy varchar2
2054    ,p_addr_attribute6                 out nocopy varchar2
2055    ,p_addr_attribute7                 out nocopy varchar2
2056    ,p_addr_attribute8                 out nocopy varchar2
2057    ,p_addr_attribute9                 out nocopy varchar2
2058    ,p_addr_attribute10                out nocopy varchar2
2059    ,p_addr_attribute11                out nocopy varchar2
2060    ,p_addr_attribute12                out nocopy varchar2
2061    ,p_addr_attribute13                out nocopy varchar2
2062    ,p_addr_attribute14                out nocopy varchar2
2063    ,p_addr_attribute15                out nocopy varchar2
2064    ,p_addr_attribute16                out nocopy varchar2
2065    ,p_addr_attribute17                out nocopy varchar2
2066    ,p_addr_attribute18                out nocopy varchar2
2067    ,p_addr_attribute19                out nocopy varchar2
2068    ,p_addr_attribute20                out nocopy varchar2
2069    ,p_add_information17               out nocopy varchar2
2070    ,p_add_information18               out nocopy varchar2
2071    ,p_add_information19               out nocopy varchar2
2072    ,p_add_information20               out nocopy varchar2
2073    ,p_action                          out nocopy varchar2
2074    ,p_old_address_id                  out nocopy varchar2
2075    ,p_add_information13               out nocopy varchar2
2076    ,p_add_information14               out nocopy varchar2
2077    ,p_add_information15               out nocopy varchar2
2078    ,p_add_information16               out nocopy varchar2
2079 )is
2080 
2081 l_proc varchar2(200) := g_package || 'get_address_data_from_tt';
2082 
2083 begin
2084 
2085   hr_utility.set_location(' Entering:' || l_proc,5);
2086 
2087 --
2088    p_effective_date:= to_date(
2089       hr_transaction_ss.get_wf_effective_date
2090         (p_transaction_step_id => p_transaction_step_id),g_date_format);
2091 --
2092   p_person_id := hr_transaction_api.get_number_value
2093     (p_transaction_step_id => p_transaction_step_id
2094     ,p_name                => 'P_PERSON_ID');
2095 --
2096   p_address_id := hr_transaction_api.get_number_value
2097     (p_transaction_step_id => p_transaction_step_id
2098     ,p_name                => 'P_ADDRESS_ID');
2099 --
2100   p_object_version_number := hr_transaction_api.get_number_value
2101     (p_transaction_step_id =>  p_transaction_step_id
2102     ,p_name                => 'P_OBJECT_VERSION_NUMBER');
2103 --
2104   p_primary_flag := hr_transaction_api.get_varchar2_value
2105     (p_transaction_step_id => p_transaction_step_id
2106     ,p_name                => 'P_PRIMARY_FLAG');
2107 -- startregistration anupam
2108   if p_primary_flag = 'T' then
2109      p_primary_flag :='N';
2110   end if;
2111 -- endregistration anupam
2112 --
2113   p_style := hr_transaction_api.get_varchar2_value
2114     (p_transaction_step_id => p_transaction_step_id
2115     ,p_name                => 'P_STYLE');
2116 
2117 --
2118   p_date_from :=
2119     hr_transaction_api.get_date_value
2120     (p_transaction_step_id => p_transaction_step_id
2121     ,p_name                => 'P_DATE_FROM');
2122 --
2123   p_date_to :=
2124     hr_transaction_api.get_date_value
2125     (p_transaction_step_id => p_transaction_step_id
2126     ,p_name                => 'P_DATE_TO');
2127 --
2128   p_address_type :=
2129     hr_transaction_api.get_varchar2_value
2130     (p_transaction_step_id => p_transaction_step_id
2131     ,p_name                => 'P_ADDRESS_TYPE_CODE');
2132 --
2133   p_address_type_meaning :=
2134     hr_transaction_api.get_varchar2_value
2135     (p_transaction_step_id => p_transaction_step_id
2136     ,p_name                => 'P_ADDRESS_TYPE');
2137 
2138 --
2139 --   p_comments :=
2140 --    hr_transaction_api.get_varchar2_value
2141 --    (p_transaction_step_id => p_transaction_step_id
2142 --    ,p_name                => 'P_COMMENTS');
2143 --
2144   p_address_line1 :=
2145     hr_transaction_api.get_varchar2_value
2146     (p_transaction_step_id => p_transaction_step_id
2147     ,p_name                => 'P_ADDRESS_LINE1');
2148 --
2149   p_address_line2 :=
2150     hr_transaction_api.get_varchar2_value
2151     (p_transaction_step_id => p_transaction_step_id
2152     ,p_name                => 'P_ADDRESS_LINE2');
2153 --
2154   p_address_line3 :=
2155     hr_transaction_api.get_varchar2_value
2156     (p_transaction_step_id => p_transaction_step_id
2157     ,p_name                => 'P_ADDRESS_LINE3');
2158 --
2159   p_town_or_city :=
2160     hr_transaction_api.get_varchar2_value
2161     (p_transaction_step_id => p_transaction_step_id
2162     ,p_name                => 'P_CITY');
2163 --
2164   p_region_1 :=
2165     hr_transaction_api.get_varchar2_value
2166     (p_transaction_step_id => p_transaction_step_id
2167     ,p_name                => 'P_REGION1');
2168 --
2169   p_region_2 :=
2170     hr_transaction_api.get_varchar2_value
2171     (p_transaction_step_id => p_transaction_step_id
2172     ,p_name                => 'P_REGION2');
2173 --
2174   p_region_3 :=
2175     hr_transaction_api.get_varchar2_value
2176     (p_transaction_step_id => p_transaction_step_id
2177     ,p_name                => 'P_REGION3');
2178 --
2179   p_postal_code :=
2180     hr_transaction_api.get_varchar2_value
2181     (p_transaction_step_id => p_transaction_step_id
2182     ,p_name                => 'P_POSTAL_CODE');
2183 --
2184   p_country :=
2185     hr_transaction_api.get_varchar2_value
2186     (p_transaction_step_id => p_transaction_step_id
2187     ,p_name                => 'P_COUNTRY_CODE');
2188 --
2189   p_country_meaning :=
2190     hr_transaction_api.get_varchar2_value
2191     (p_transaction_step_id => p_transaction_step_id
2192     ,p_name                => 'P_COUNTRY');
2193 --
2194   p_telephone_number_1 :=
2195     hr_transaction_api.get_varchar2_value
2196     (p_transaction_step_id => p_transaction_step_id
2197     ,p_name                => 'P_TELEPHONE_NUMBER1');
2198 --
2199   p_telephone_number_2 :=
2200     hr_transaction_api.get_varchar2_value
2201     (p_transaction_step_id => p_transaction_step_id
2202     ,p_name                => 'P_TELEPHONE_NUMBER2');
2203 --
2204   p_telephone_number_3 :=
2205     hr_transaction_api.get_varchar2_value
2206     (p_transaction_step_id => p_transaction_step_id
2207     ,p_name                => 'P_TELEPHONE_NUMBER3');
2208 --
2209   p_addr_attribute_category :=
2210     hr_transaction_api.get_varchar2_value
2211     (p_transaction_step_id => p_transaction_step_id
2212     ,p_name                => 'P_ADDR_ATTRIBUTE_CATEGORY');
2213 --
2214   p_addr_attribute1 :=
2215     hr_transaction_api.get_varchar2_value
2216     (p_transaction_step_id => p_transaction_step_id
2217     ,p_name                => 'P_ADDR_ATTRIBUTE1');
2218 --
2219   p_addr_attribute2 :=
2220     hr_transaction_api.get_varchar2_value
2221     (p_transaction_step_id => p_transaction_step_id
2222     ,p_name                => 'P_ADDR_ATTRIBUTE2');
2223 --
2224   p_addr_attribute3 :=
2225     hr_transaction_api.get_varchar2_value
2226     (p_transaction_step_id => p_transaction_step_id
2227     ,p_name                => 'P_ADDR_ATTRIBUTE3');
2228 --
2229   p_addr_attribute4 :=
2230     hr_transaction_api.get_varchar2_value
2231     (p_transaction_step_id => p_transaction_step_id
2232     ,p_name                => 'P_ADDR_ATTRIBUTE4');
2233 --
2234   p_addr_attribute5 :=
2235     hr_transaction_api.get_varchar2_value
2236     (p_transaction_step_id => p_transaction_step_id
2237     ,p_name                => 'P_ADDR_ATTRIBUTE5');
2238 --
2239   p_addr_attribute6 :=
2240     hr_transaction_api.get_varchar2_value
2241     (p_transaction_step_id => p_transaction_step_id
2242     ,p_name                => 'P_ADDR_ATTRIBUTE6');
2243 --
2244   p_addr_attribute7 :=
2245     hr_transaction_api.get_varchar2_value
2246     (p_transaction_step_id => p_transaction_step_id
2247     ,p_name                => 'P_ADDR_ATTRIBUTE7');
2248 --
2249   p_addr_attribute8 :=
2250     hr_transaction_api.get_varchar2_value
2251     (p_transaction_step_id => p_transaction_step_id
2252     ,p_name                => 'P_ADDR_ATTRIBUTE8');
2253 --
2254   p_addr_attribute9 :=
2255     hr_transaction_api.get_varchar2_value
2256     (p_transaction_step_id => p_transaction_step_id
2257     ,p_name                => 'P_ADDR_ATTRIBUTE9');
2258 --
2259   p_addr_attribute10 :=
2260     hr_transaction_api.get_varchar2_value
2261     (p_transaction_step_id => p_transaction_step_id
2262     ,p_name                => 'P_ADDR_ATTRIBUTE10');
2263 --
2264   p_addr_attribute11 :=
2265     hr_transaction_api.get_varchar2_value
2266     (p_transaction_step_id => p_transaction_step_id
2267     ,p_name                => 'P_ADDR_ATTRIBUTE11');
2268 --
2269   p_addr_attribute12 :=
2270     hr_transaction_api.get_varchar2_value
2271     (p_transaction_step_id => p_transaction_step_id
2272     ,p_name                => 'P_ADDR_ATTRIBUTE12');
2273 --
2274   p_addr_attribute13 :=
2275     hr_transaction_api.get_varchar2_value
2276     (p_transaction_step_id => p_transaction_step_id
2277     ,p_name                => 'P_ADDR_ATTRIBUTE13');
2278 --
2279   p_addr_attribute14 :=
2280     hr_transaction_api.get_varchar2_value
2281     (p_transaction_step_id => p_transaction_step_id
2282     ,p_name                => 'P_ADDR_ATTRIBUTE14');
2283 --
2284   p_addr_attribute15 :=
2285     hr_transaction_api.get_varchar2_value
2286     (p_transaction_step_id => p_transaction_step_id
2287     ,p_name                => 'P_ADDR_ATTRIBUTE15');
2288 --
2289   p_addr_attribute16 :=
2290     hr_transaction_api.get_varchar2_value
2291     (p_transaction_step_id => p_transaction_step_id
2292     ,p_name                => 'P_ADDR_ATTRIBUTE16');
2293 --
2294   p_addr_attribute17 :=
2295     hr_transaction_api.get_varchar2_value
2296     (p_transaction_step_id => p_transaction_step_id
2297     ,p_name                => 'P_ADDR_ATTRIBUTE17');
2298 --
2299   p_addr_attribute18 :=
2300     hr_transaction_api.get_varchar2_value
2301     (p_transaction_step_id => p_transaction_step_id
2302     ,p_name                => 'P_ADDR_ATTRIBUTE18');
2303 --
2304   p_addr_attribute19 :=
2305     hr_transaction_api.get_varchar2_value
2306     (p_transaction_step_id => p_transaction_step_id
2307     ,p_name                => 'P_ADDR_ATTRIBUTE19');
2308 --
2309   p_addr_attribute20 :=
2310     hr_transaction_api.get_varchar2_value
2311     (p_transaction_step_id => p_transaction_step_id
2312     ,p_name                => 'P_ADDR_ATTRIBUTE20');
2313 --
2314 
2315 
2316   p_add_information17 :=
2317     hr_transaction_api.get_varchar2_value
2318     (p_transaction_step_id => p_transaction_step_id
2319     ,p_name                => 'P_ADD_INFORMATION17');
2320 --
2321 
2322 --
2323   p_add_information18 :=
2324     hr_transaction_api.get_varchar2_value
2325     (p_transaction_step_id => p_transaction_step_id
2326     ,p_name                => 'P_ADD_INFORMATION18');
2327 --
2328 --
2329   p_add_information19 :=
2330     hr_transaction_api.get_varchar2_value
2331     (p_transaction_step_id => p_transaction_step_id
2332     ,p_name                => 'P_ADD_INFORMATION19');
2333 --
2334 --
2335   p_add_information20 :=
2336     hr_transaction_api.get_varchar2_value
2337     (p_transaction_step_id => p_transaction_step_id
2338     ,p_name                => 'P_ADD_INFORMATION20');
2339 --
2340 --
2341   p_action :=
2342     hr_transaction_api.get_varchar2_value
2343     (p_transaction_step_id => p_transaction_step_id
2344     ,p_name                => 'P_ACTION');
2345 --
2346   p_old_address_id := hr_transaction_api.get_number_value
2347     (p_transaction_step_id => p_transaction_step_id
2348     ,p_name                => 'P_OLD_ADDRESS_ID');
2349 
2350  p_add_information13 :=
2351     hr_transaction_api.get_varchar2_value
2352     (p_transaction_step_id => p_transaction_step_id
2353     ,p_name                => 'P_ADD_INFORMATION13');
2354 --
2355   p_add_information14 :=
2356     hr_transaction_api.get_varchar2_value
2357     (p_transaction_step_id => p_transaction_step_id
2358     ,p_name                => 'P_ADD_INFORMATION14');
2359 --
2360   p_add_information15 :=
2361     hr_transaction_api.get_varchar2_value
2362     (p_transaction_step_id => p_transaction_step_id
2363     ,p_name                => 'P_ADD_INFORMATION15');
2364 --
2365   p_add_information16 :=
2366     hr_transaction_api.get_varchar2_value
2367     (p_transaction_step_id => p_transaction_step_id
2368     ,p_name                => 'P_ADD_INFORMATION16');
2369 --
2370 
2371 hr_utility.set_location(' Leaving:' || l_proc,10);
2372 
2373 
2374 EXCEPTION
2375    WHEN OTHERS THEN
2376    -- Reset OUT parameters for nocopy.
2377 
2378    p_effective_date                  := NULL;
2379    p_person_id                       := NULL;
2380    p_address_id                      := NULL;
2381    p_object_version_number           := NULL;
2382    p_primary_flag                    := NULL;
2383    p_style                           := NULL;
2384    p_date_from                       := NULL;
2385    p_date_to                         := NULL;
2386    p_address_type                    := NULL;
2387    p_address_type_meaning            := NULL;
2388    p_comments                        := NULL;
2389    p_address_line1                   := NULL;
2390    p_address_line2                   := NULL;
2391    p_address_line3                   := NULL;
2392    p_town_or_city                    := NULL;
2393    p_region_1                        := NULL;
2394    p_region_2                        := NULL;
2395    p_region_3                        := NULL;
2396    p_postal_code                     := NULL;
2397    p_country                         := NULL;
2398    p_country_meaning                 := NULL;
2399    p_telephone_number_1              := NULL;
2400    p_telephone_number_2              := NULL;
2401    p_telephone_number_3              := NULL;
2402    p_addr_attribute_category         := NULL;
2403    p_addr_attribute1                 := NULL;
2404    p_addr_attribute2                 := NULL;
2405    p_addr_attribute3                 := NULL;
2406    p_addr_attribute4                 := NULL;
2407    p_addr_attribute5                 := NULL;
2408    p_addr_attribute6                 := NULL;
2409    p_addr_attribute7                 := NULL;
2410    p_addr_attribute8                 := NULL;
2411    p_addr_attribute9                 := NULL;
2412    p_addr_attribute10                := NULL;
2413    p_addr_attribute11                := NULL;
2414    p_addr_attribute12                := NULL;
2415    p_addr_attribute13                := NULL;
2416    p_addr_attribute14                := NULL;
2417    p_addr_attribute15                := NULL;
2418    p_addr_attribute16                := NULL;
2419    p_addr_attribute17                := NULL;
2420    p_addr_attribute18                := NULL;
2421    p_addr_attribute19                := NULL;
2422    p_addr_attribute20                := NULL;
2423    p_add_information13               := NULL;
2424    p_add_information14               := NULL;
2425    p_add_information15               := NULL;
2426    p_add_information16               := NULL;
2427    p_add_information17               := NULL;
2428    p_add_information18               := NULL;
2429    p_add_information19               := NULL;
2430    p_add_information20               := NULL;
2431    p_action                          := NULL;
2432    p_old_address_id                  := NULL;
2433    hr_utility.set_location(' Leaving:' || l_proc,555);
2434 
2435       RAISE;
2436 
2437 END get_address_data_from_tt;
2438 
2439 /*---------------------------------------------------------------------------+
2440 |                                                                            |
2441 |       Name           : process_api                                         |
2442 |                                                                            |
2443 |       Purpose        : This will procedure is invoked whenever approver    |
2444 |                        approves the address change.                        |
2445 |                                                                            |
2446 +-----------------------------------------------------------------------------*/
2447 PROCEDURE process_api
2448 (p_validate                 in     boolean default false
2449 ,p_transaction_step_id      in     number
2450 ,p_effective_date           in     varchar2 default null
2451 )
2452 IS
2453 
2454 l_proc varchar2(200) := g_package || 'process_api';
2455 l_user_date_format      varchar2(200);
2456 l_validate BOOLEAN := true; l_old_ovn NUMBER; l_ovn NUMBER;
2457 l_old_address_id per_addresses.address_id%TYPE;
2458 l_action VARCHAR2(100); l_effective_date date;
2459 l_address per_addresses%ROWTYPE;
2460 l_pradd_ovrlap BOOLEAN := FALSE;
2461 l_contact_or_person varchar2(100);
2462 l_check_for_sfl varchar2(10);
2463 l_sfl_g_contact_step_id NUMBER;
2464 
2465 BEGIN
2466         hr_utility.set_location(' Entering:' || l_proc,5);
2467         if (p_effective_date is not null) then
2468 	   hr_utility.set_location( l_proc, 10);
2469            l_effective_date:= to_date(p_effective_date,g_date_format);
2470         else
2471 	   hr_utility.set_location( l_proc, 15);
2472            l_effective_date:= to_date(
2473                hr_transaction_ss.get_wf_effective_date
2474                  (p_transaction_step_id => p_transaction_step_id),g_date_format);
2475         end if;
2476 
2477         -- ------------------------------------------------------------
2478         -- get user date format
2479         -- ------------------------------------------------------------
2480         -- This call is causing an implicit commit and not being used at all
2481         -- commenting the method call
2482         -- l_user_date_format := hr_util_misc_web.get_user_date_format;
2483 
2484         l_address.object_version_number :=
2485                 hr_transaction_api.get_number_value
2486                 (p_Transaction_step_id => p_transaction_step_id
2487                 ,p_name => 'P_OBJECT_VERSION_NUMBER');
2488 
2489         l_old_ovn :=
2490                 hr_transaction_api.get_number_value
2491                 (p_Transaction_step_id => p_transaction_step_id
2492                 ,p_name => 'P_OLD_OBJECT_VERSION_NUMBER');
2493 
2494         l_Address.address_id :=
2495                 hr_transaction_api.get_number_value
2496                 (p_Transaction_step_id => p_transaction_step_id
2497                 ,p_name => 'P_ADDRESS_ID');
2498 
2499         l_old_address_id :=
2500                 hr_transaction_api.get_number_value
2501                 (p_Transaction_step_id => p_transaction_step_id
2502                 ,p_name => 'P_OLD_ADDRESS_ID');
2503 
2504         l_action :=
2505                 hr_transaction_api.get_varchar2_value
2506                 (p_Transaction_step_id => p_transaction_step_id
2507                 ,p_name => 'P_ACTION');
2508 
2509         IF      hr_transaction_api.get_varchar2_value
2510                 (p_Transaction_step_id => p_transaction_step_id
2511                 ,p_name => 'P_PRADD_OVLAPVAL_OVERRIDE') = 'Y' THEN
2512 		hr_utility.set_location( l_proc, 20);
2513                 l_pradd_ovrlap := TRUE;
2514         ELSE
2515 	        hr_utility.set_location( l_proc, 25);
2516                 l_pradd_ovrlap := FALSE;
2517         END IF;
2518 
2519 
2520         l_address.date_from :=
2521                 (hr_transaction_api.get_date_value
2522                 (p_Transaction_step_id => p_transaction_step_id
2523                 ,p_name => 'P_DATE_FROM'));
2524 
2525         l_address.date_to :=
2526                 (hr_transaction_api.get_date_value
2527                 (p_Transaction_step_id => p_transaction_step_id
2528                 ,p_name => 'P_DATE_TO'));
2529 
2530         l_address.person_id :=
2531                 hr_transaction_api.get_number_value
2532                 (p_Transaction_step_id =>
2533                         p_transaction_step_id
2534                 ,p_name => 'P_PERSON_ID');
2535         --
2536         -- PB :
2537         --
2538         if l_address.person_id is null or l_address.person_id < 0 then
2539 	   hr_utility.set_location( l_proc, 30);
2540            --
2541            -- This is the case where the contact and address are created.
2542            -- So get contact person id from the global which is set by
2543            -- hrconwrs.pkb.
2544            --
2545            -- StartRegistration
2546            --
2547            l_contact_or_person :=
2548                 hr_transaction_api.get_varchar2_value
2549                 (p_Transaction_step_id => p_transaction_step_id
2550                 ,p_name => 'P_CONTACT_OR_PERSON');
2551            --
2552     if l_contact_or_person = 'CONTACT' or l_contact_or_person = 'EMER_CR_NEW_CONT' or l_contact_or_person = 'EMRG_OVRW_UPD' or l_contact_or_person = 'EMRG_OVRW_DEL' or  l_contact_or_person = 'EMER_CR_NEW_REL'
2553           or  l_contact_or_person = 'DPDNT_CR_NEW_CONT' or  l_contact_or_person = 'DPDNT_OVRW_UPD'  or  l_contact_or_person = 'DPDNT_OVRW_DEL' or  l_contact_or_person = 'DPDNT_CR_NEW_REL'       then
2554 	  hr_utility.set_location( l_proc, 35);
2555               --
2556               l_address.person_id := hr_process_contact_ss.g_contact_person_id;
2557               --
2558            -- In case of SaveForLater run process_create_contact_api
2559            -- in commit mode to get the g_contact_person_id
2560            -- this will be rolled back with the current step
2561            -- after validating the current transaction step
2562 
2563            BEGIN
2564 
2565               select 'NOT_SFL'
2566               into l_check_for_sfl
2567               from per_all_people_f
2568               where person_id =l_address.person_id;
2569 
2570            EXCEPTION
2571               WHEN no_data_found THEN
2572 	        hr_utility.set_location( l_proc || 'EXCEPTION' , 555);
2573                 l_check_for_sfl := 'SFL';
2574            END;
2575 
2576 
2577            IF  l_check_for_sfl = 'SFL' THEN
2578               hr_utility.set_location( l_proc, 40);
2579               hr_utility.set_location('Address: process_api: It is a save for later step' , 1234);
2580               hr_utility.set_location('g_contact_person_id before :'||hr_process_contact_ss.g_contact_person_id , 1234);
2581 
2582               BEGIN
2583                     select nvl(hats1.transaction_step_id,0)
2584                     into   l_sfl_g_contact_step_id
2585                     from   hr_api_transaction_steps hats1
2586                     where  hats1.item_type   = 'HRSSA'
2587                     and    hats1.item_key    =
2588                                (select hats2.item_key
2589                                 from   hr_api_transaction_steps hats2
2590                                 where  hats2.item_type   = 'HRSSA'
2591 	                            and    hats2.transaction_step_id = p_transaction_step_id )
2592                     and    hats1.api_name = 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API';
2593 
2594 
2595 
2596                     HR_PROCESS_CONTACT_SS.process_create_contact_api (
2597                         p_transaction_step_id => l_sfl_g_contact_step_id
2598                         );
2599 
2600                     l_address.person_id := hr_process_contact_ss.g_contact_person_id;
2601                     hr_utility.set_location('g_contact_person_id after :'||hr_process_contact_ss.g_contact_person_id , 1234);
2602               EXCEPTION
2603                     WHEN others THEN
2604 		    hr_utility.set_location( l_proc || 'EXCEPTION' , 565);
2605                     null;
2606               END;
2607            END IF;
2608 
2609            else -- for person PERSON
2610 	      hr_utility.set_location( l_proc, 45);
2611               --
2612               -- StartRegistration : For Create person and address in one
2613               -- transaction get person id from the package global.
2614               --
2615               -- adding the session id check to avoid connection pooling problems.
2616               /* l_address.person_id := hr_process_person_ss.g_person_id; */
2617               if (( hr_process_person_ss.g_person_id is not null) and
2618                               (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) then
2619 			      hr_utility.set_location( l_proc, 50);
2620                l_address.person_id := hr_process_person_ss.g_person_id;
2621               --
2622               end if;
2623               --
2624            end if;
2625            --
2626         end if;
2627         --
2628         l_address.primary_flag :=
2629                 hr_transaction_api.get_varchar2_value
2630                 (p_Transaction_step_id => p_transaction_step_id
2631                 ,p_name => 'P_PRIMARY_FLAG');
2632         -- startregistration anupam
2633         -- For tertiary address from overview page we have T for primary flag
2634         -- it should be changed to N in the database.
2635         if l_address.primary_flag = 'T' then
2636 	    hr_utility.set_location( l_proc, 55);
2637             l_address.primary_flag := 'N';
2638         end if;
2639         -- endregistration anupam
2640 
2641 
2642         l_Address.style :=
2643                 hr_transaction_api.get_varchar2_value
2644                 (p_Transaction_step_id => p_transaction_step_id
2645                 ,p_name => 'P_STYLE');
2646         l_address.address_type :=
2647                 hr_transaction_api.get_varchar2_value
2648                 (p_Transaction_step_id => p_transaction_step_id
2649                 ,p_name => 'P_ADDRESS_TYPE_CODE');
2650         l_Address.address_line1 :=
2651                 hr_transaction_api.get_varchar2_value
2652                 (p_Transaction_step_id => p_transaction_step_id
2653                 ,p_name => 'P_ADDRESS_LINE1');
2654         l_Address.address_line2 :=
2655                 hr_transaction_api.get_varchar2_value
2656                 (p_Transaction_step_id => p_transaction_step_id
2657                 ,p_name => 'P_ADDRESS_LINE2');
2658         l_Address.address_line3 :=
2659                 hr_transaction_api.get_varchar2_value
2660                 (p_Transaction_step_id => p_transaction_step_id
2661                 ,p_name => 'P_ADDRESS_LINE3');
2662         l_address.town_or_city :=
2663                 hr_transaction_api.get_varchar2_value
2664                 (p_Transaction_step_id => p_transaction_step_id
2665                 ,p_name => 'P_CITY');
2666         l_address.region_1 :=
2667                 hr_transaction_api.get_varchar2_value
2668                 (p_Transaction_step_id => p_transaction_step_id
2669                 ,p_name => 'P_REGION1');
2670         l_address.region_2 :=
2671                 hr_transaction_api.get_varchar2_value
2672                 (p_Transaction_step_id => p_transaction_step_id
2673                 ,p_name => 'P_REGION2');
2674         l_address.region_3 :=
2675                 hr_transaction_api.get_varchar2_value
2676                 (p_Transaction_step_id => p_transaction_step_id
2677                 ,p_name => 'P_REGION3');
2678         l_address.postal_code :=
2679                 hr_transaction_api.get_varchar2_value
2680                 (p_Transaction_step_id => p_transaction_step_id
2681                 ,p_name => 'P_POSTAL_CODE');
2682         l_address.country :=
2683                 hr_transaction_api.get_varchar2_value
2684                 (p_Transaction_step_id => p_transaction_step_id
2685                 ,p_name => 'P_COUNTRY_CODE');
2686         l_address.telephone_number_1 :=
2687                 hr_transaction_api.get_varchar2_value
2688                 (p_Transaction_step_id => p_transaction_step_id
2689                 ,p_name => 'P_TELEPHONE_NUMBER1');
2690         l_address.telephone_number_2 :=
2691                 hr_transaction_api.get_varchar2_value
2692                 (p_Transaction_step_id => p_transaction_step_id
2693                 ,p_name => 'P_TELEPHONE_NUMBER2');
2694         l_address.telephone_number_3 :=
2695                 hr_transaction_api.get_varchar2_value
2696                 (p_Transaction_step_id => p_transaction_step_id
2697                 ,p_name => 'P_TELEPHONE_NUMBER3');
2698 
2699 -- Now get all the Descriptive Flex fields
2700         l_address.addr_attribute_category :=
2701                 hr_transaction_api.get_varchar2_value
2702                 (p_Transaction_step_id => p_transaction_step_id
2703                 ,p_name => 'P_ADDR_ATTRIBUTE_CATEGORY');
2704         l_address.addr_attribute1 :=
2705                 hr_transaction_api.get_varchar2_value
2706                 (p_Transaction_step_id => p_transaction_step_id
2707                 ,p_name => 'P_ADDR_ATTRIBUTE1');
2708         l_address.addr_attribute2 :=
2709                 hr_transaction_api.get_varchar2_value
2710                 (p_Transaction_step_id => p_transaction_step_id
2711                 ,p_name => 'P_ADDR_ATTRIBUTE2');
2712         l_address.addr_attribute3 :=
2713                 hr_transaction_api.get_varchar2_value
2714                 (p_Transaction_step_id => p_transaction_step_id
2715                 ,p_name => 'P_ADDR_ATTRIBUTE3');
2716         l_address.addr_attribute4 :=
2717                 hr_transaction_api.get_varchar2_value
2718                 (p_Transaction_step_id => p_transaction_step_id
2719                 ,p_name => 'P_ADDR_ATTRIBUTE4');
2720         l_address.addr_attribute5 :=
2721                 hr_transaction_api.get_varchar2_value
2722                 (p_Transaction_step_id => p_transaction_step_id
2723                 ,p_name => 'P_ADDR_ATTRIBUTE5');
2724         l_address.addr_attribute6 :=
2725                 hr_transaction_api.get_varchar2_value
2726                 (p_Transaction_step_id => p_transaction_step_id
2727                 ,p_name => 'P_ADDR_ATTRIBUTE6');
2728         l_address.addr_attribute7 :=
2729                 hr_transaction_api.get_varchar2_value
2730                 (p_Transaction_step_id => p_transaction_step_id
2731                 ,p_name => 'P_ADDR_ATTRIBUTE7');
2732         l_address.addr_attribute8 :=
2733                 hr_transaction_api.get_varchar2_value
2734                 (p_Transaction_step_id => p_transaction_step_id
2735                 ,p_name => 'P_ADDR_ATTRIBUTE8');
2736         l_address.addr_attribute9 :=
2737                 hr_transaction_api.get_varchar2_value
2738                 (p_Transaction_step_id => p_transaction_step_id
2739                 ,p_name => 'P_ADDR_ATTRIBUTE9');
2740         l_address.addr_attribute10 :=
2741                 hr_transaction_api.get_varchar2_value
2742                 (p_Transaction_step_id => p_transaction_step_id
2743                 ,p_name => 'P_ADDR_ATTRIBUTE10');
2744         l_address.addr_attribute11 :=
2745                 hr_transaction_api.get_varchar2_value
2746                 (p_Transaction_step_id => p_transaction_step_id
2747                 ,p_name => 'P_ADDR_ATTRIBUTE11');
2748         l_address.addr_attribute12 :=
2749                 hr_transaction_api.get_varchar2_value
2750                 (p_Transaction_step_id => p_transaction_step_id
2751                 ,p_name => 'P_ADDR_ATTRIBUTE12');
2752         l_address.addr_attribute13 :=
2753                 hr_transaction_api.get_varchar2_value
2754                 (p_Transaction_step_id => p_transaction_step_id
2755                 ,p_name => 'P_ADDR_ATTRIBUTE13');
2756         l_address.addr_attribute14 :=
2757                 hr_transaction_api.get_varchar2_value
2758                 (p_Transaction_step_id => p_transaction_step_id
2759                 ,p_name => 'P_ADDR_ATTRIBUTE14');
2760         l_address.addr_attribute15 :=
2761                 hr_transaction_api.get_varchar2_value
2762                 (p_Transaction_step_id => p_transaction_step_id
2763                 ,p_name => 'P_ADDR_ATTRIBUTE15');
2764         l_address.addr_attribute16 :=
2765                 hr_transaction_api.get_varchar2_value
2766                 (p_Transaction_step_id => p_transaction_step_id
2767                 ,p_name => 'P_ADDR_ATTRIBUTE16');
2768         l_address.addr_attribute17 :=
2769                 hr_transaction_api.get_varchar2_value
2770                 (p_Transaction_step_id => p_transaction_step_id
2771                 ,p_name => 'P_ADDR_ATTRIBUTE17');
2772         l_address.addr_attribute18 :=
2773                 hr_transaction_api.get_varchar2_value
2774                 (p_Transaction_step_id => p_transaction_step_id
2775                 ,p_name => 'P_ADDR_ATTRIBUTE18');
2776         l_address.addr_attribute19 :=
2777                 hr_transaction_api.get_varchar2_value
2778                 (p_Transaction_step_id => p_transaction_step_id
2779                 ,p_name => 'P_ADDR_ATTRIBUTE19');
2780         l_address.addr_attribute20 :=
2781                 hr_transaction_api.get_varchar2_value
2782                 (p_Transaction_step_id => p_transaction_step_id
2783                 ,p_name => 'P_ADDR_ATTRIBUTE20');
2784         l_address.add_information17 :=
2785                 hr_transaction_api.get_varchar2_value
2786                 (p_Transaction_step_id => p_transaction_step_id
2787                 ,p_name => 'P_ADD_INFORMATION17');
2788         l_address.add_information18 :=
2789                 hr_transaction_api.get_varchar2_value
2790                 (p_Transaction_step_id => p_transaction_step_id
2791                 ,p_name => 'P_ADD_INFORMATION18');
2792         l_address.add_information19 :=
2793                 hr_transaction_api.get_varchar2_value
2794                 (p_Transaction_step_id => p_transaction_step_id
2795                 ,p_name => 'P_ADD_INFORMATION19');
2796         l_address.add_information20 :=
2797                 hr_transaction_api.get_varchar2_value
2798                 (p_Transaction_step_id => p_transaction_step_id
2799                 ,p_name => 'P_ADD_INFORMATION20');
2800         l_address.add_information13 :=
2801                 hr_transaction_api.get_varchar2_value
2802                 (p_Transaction_step_id => p_transaction_step_id
2803                 ,p_name => 'P_ADD_INFORMATION13');
2804         l_address.add_information14 :=
2805                 hr_transaction_api.get_varchar2_value
2806                 (p_Transaction_step_id => p_transaction_step_id
2807                 ,p_name => 'P_ADD_INFORMATION14');
2808         l_address.add_information15 :=
2809                 hr_transaction_api.get_varchar2_value
2810                 (p_Transaction_step_id => p_transaction_step_id
2811                 ,p_name => 'P_ADD_INFORMATION15');
2812         l_address.add_information16 :=
2813                 hr_transaction_api.get_varchar2_value
2814                 (p_Transaction_step_id => p_transaction_step_id
2815                 ,p_name => 'P_ADD_INFORMATION16');
2816 
2817 
2818         IF UPPER(l_action) = 'CHANGE' THEN
2819                 IF UPPER(l_address.primary_flag) like 'N%' THEN
2820 		hr_utility.set_location( l_proc, 60);
2821                 hr_person_address_api.update_person_address
2822                         (p_validate => false
2823                         ,p_effective_date => l_effective_date
2824                         ,p_address_id => l_old_address_id
2825                         ,p_object_version_number => l_old_ovn
2826                         ,p_date_to => l_Address.date_to);
2827                 END IF;
2828                 -- Now enter the new address.
2829                 hr_person_address_api.create_person_address
2830                         (p_validate => false
2831                         ,p_effective_date => l_effective_date
2832                         ,p_person_id => l_address.person_id
2833                         ,p_primary_flag =>  l_address.primary_flag
2834                         ,p_style =>  l_address.style
2835                         ,p_date_from => l_effective_date
2836                         ,p_address_type => l_address.address_type
2837                         ,p_address_line1 => l_address.address_line1
2838                         ,p_address_line2 => l_address.address_line2
2839                         ,p_address_line3 => l_address.address_line3
2840                         ,p_town_or_city =>  l_address.town_or_city
2841                         ,p_region_1 =>  l_address.region_1
2842                         ,p_region_2 =>  l_address.region_2
2843                         ,p_region_3 =>  l_address.region_3
2844                         ,p_postal_code =>  l_Address.postal_code
2845                         ,p_country => l_address.country
2846                         ,p_address_id => l_Address.address_id
2847                         ,p_object_Version_number =>
2848                                 l_address.object_version_number
2849                         ,p_telephone_number_1 => l_address.telephone_number_1
2850                         ,p_telephone_number_2 => l_address.telephone_number_2
2851                         ,p_telephone_number_3 => l_address.telephone_number_3
2852                         ,p_pradd_ovlapval_override => l_pradd_ovrlap
2853                         ,p_addr_attribute_category => l_address.addr_attribute_category
2854                         ,p_addr_attribute1         => l_address.addr_attribute1
2855                         ,p_addr_attribute2         => l_address.addr_attribute2
2856                         ,p_addr_attribute3         => l_address.addr_attribute3
2857                         ,p_addr_attribute4         => l_address.addr_attribute4
2858                         ,p_addr_attribute5         => l_address.addr_attribute5
2859                         ,p_addr_attribute6         => l_address.addr_attribute6
2860                         ,p_addr_attribute7         => l_address.addr_attribute7
2861                         ,p_addr_attribute8         => l_address.addr_attribute8
2862                         ,p_addr_attribute9         => l_address.addr_attribute9
2863                         ,p_addr_attribute10        => l_address.addr_attribute10
2864                         ,p_addr_attribute11        => l_address.addr_attribute11
2865                         ,p_addr_attribute12        => l_address.addr_attribute12
2866                         ,p_addr_attribute13        => l_address.addr_attribute13
2867                         ,p_addr_attribute14        => l_address.addr_attribute14
2868                         ,p_addr_attribute15        => l_address.addr_attribute15
2869                         ,p_addr_attribute16        => l_address.addr_attribute16
2870                         ,p_addr_attribute17        => l_address.addr_attribute17
2871                         ,p_addr_attribute18        => l_address.addr_attribute18
2872                         ,p_addr_attribute19        => l_address.addr_attribute19
2873                         ,p_addr_attribute20        => l_address.addr_attribute20
2874                         ,p_add_information13       => l_address.add_information13
2875                         ,p_add_information14       => l_address.add_information14
2876                         ,p_add_information15       => l_address.add_information15
2877                         ,p_add_information16       => l_address.add_information16
2878                         ,p_add_information17       => l_address.add_information17
2879                         ,p_add_information18       => l_address.add_information18
2880                         ,p_add_information19       => l_address.add_information19
2881                         ,p_add_information20       => l_address.add_information20);
2882         ELSIF UPPER(l_action) = 'CORRECT' THEN --Bug#3114508 start
2883 	hr_utility.set_location( l_proc, 65);
2884            if (hr_process_contact_ss.g_is_address_updated = true) then
2885               l_address.object_version_number := l_address.object_version_number + 1;
2886            end if;
2887                 hr_person_address_api.update_pers_addr_with_style --Bug#3114508 end
2888                         (p_validate => false
2889                         ,p_effective_date => trunc(sysdate)
2890                         ,p_address_type => l_address.address_type
2891                         ,p_address_line1 => l_address.address_line1
2892                         ,p_address_line2 => l_address.address_line2
2893                         ,p_address_line3 => l_address.address_line3
2894                         ,p_town_or_city =>  l_address.town_or_city
2895                         ,p_region_1 =>  l_address.region_1
2896                         ,p_region_2 =>  l_address.region_2
2897                         ,p_region_3 =>  l_address.region_3
2898                         ,p_postal_code =>  l_Address.postal_code
2899                         ,p_country => l_address.country  --Bug#3114508 start
2900                         ,p_style   => l_Address.style --Bug#3114508 end
2901                         ,p_address_id => l_Address.address_id
2902                         ,p_object_version_number =>
2903                                 l_address.object_version_number
2904                         ,p_telephone_number_1 => l_address.telephone_number_1
2905                         ,p_telephone_number_2 => l_address.telephone_number_2
2906                         ,p_telephone_number_3 => l_address.telephone_number_3
2907                         ,p_addr_attribute_category => l_address.addr_attribute_category
2908                         ,p_addr_attribute1         => l_address.addr_attribute1
2909                         ,p_addr_attribute2         => l_address.addr_attribute2
2910                         ,p_addr_attribute3         => l_address.addr_attribute3
2911                         ,p_addr_attribute4         => l_address.addr_attribute4
2912                         ,p_addr_attribute5         => l_address.addr_attribute5
2913                         ,p_addr_attribute6         => l_address.addr_attribute6
2914                         ,p_addr_attribute7         => l_address.addr_attribute7
2915                         ,p_addr_attribute8         => l_address.addr_attribute8
2916                         ,p_addr_attribute9         => l_address.addr_attribute9
2917                         ,p_addr_attribute10        => l_address.addr_attribute10
2918                         ,p_addr_attribute11        => l_address.addr_attribute11
2919                         ,p_addr_attribute12        => l_address.addr_attribute12
2920                         ,p_addr_attribute13        => l_address.addr_attribute13
2921                         ,p_addr_attribute14        => l_address.addr_attribute14
2922                         ,p_addr_attribute15        => l_address.addr_attribute15
2923                         ,p_addr_attribute16        => l_address.addr_attribute16
2924                         ,p_addr_attribute17        => l_address.addr_attribute17
2925                         ,p_addr_attribute18        => l_address.addr_attribute18
2926                         ,p_addr_attribute19        => l_address.addr_attribute19
2927                         ,p_addr_attribute20        => l_address.addr_attribute20
2928                         ,p_add_information13       => l_address.add_information13
2929                         ,p_add_information14       => l_address.add_information14
2930                         ,p_add_information15       => l_address.add_information15
2931                         ,p_add_information16       => l_address.add_information16
2932                         ,p_add_information17       => l_address.add_information17
2933                         ,p_add_information18       => l_address.add_information18
2934                         ,p_add_information19       => l_address.add_information19
2935                         ,p_add_information20       => l_address.add_information20);
2936            hr_process_contact_ss.g_is_address_updated := false;
2937         ELSIF UPPER(l_action) = 'DELETE' THEN
2938 		hr_utility.set_location( l_proc, 70);
2939                 hr_person_address_api.update_person_address
2940                 (p_validate => false
2941                 ,p_address_id => l_address.address_id
2942                 ,p_object_version_number => l_address.object_version_number
2943                 ,p_effective_date => l_effective_date
2944                 ,p_date_from => l_address.date_from
2945                 ,p_date_to => l_address.date_to);
2946         ELSIF UPPER(l_action) = 'NEW' THEN
2947 		hr_utility.set_location( l_proc, 75);
2948                 hr_person_address_api.create_person_address
2949                         (p_validate => false
2950                         ,p_effective_date => l_effective_date
2951                         ,p_person_id => l_address.person_id
2952                         ,p_primary_flag => l_address.primary_flag
2953                         ,p_style => l_address.style
2954                         ,p_date_from => l_effective_date
2955                         ,p_address_type => l_address.address_type
2956                         ,p_address_line1 => l_address.address_line1
2957                         ,p_address_line2 => l_address.address_line2
2958                         ,p_address_line3 => l_address.address_line3
2959                         ,p_town_or_city => l_address.town_or_city
2960                         ,p_region_1 => l_address.region_1
2961                         ,p_region_2 => l_address.region_2
2962                         ,p_region_3 => l_address.region_3
2963                         ,p_postal_code => l_address.postal_code
2964                         ,p_country => l_address.country
2965                         ,p_address_id => l_address.address_id
2966                         ,p_object_Version_number =>
2967                                 l_address.object_version_number
2968                         ,p_telephone_number_1 => l_address.telephone_number_1
2969                         ,p_telephone_number_2 => l_address.telephone_number_2
2970                         ,p_telephone_number_3 => l_address.telephone_number_3
2971                         ,p_addr_attribute_category => l_address.addr_attribute_category
2972                         ,p_addr_attribute1         => l_address.addr_attribute1
2973                         ,p_addr_attribute2         => l_address.addr_attribute2
2974                         ,p_addr_attribute3         => l_address.addr_attribute3
2975                         ,p_addr_attribute4         => l_address.addr_attribute4
2976                         ,p_addr_attribute5         => l_address.addr_attribute5
2977                         ,p_addr_attribute6         => l_address.addr_attribute6
2978                         ,p_addr_attribute7         => l_address.addr_attribute7
2979                         ,p_addr_attribute8         => l_address.addr_attribute8
2980                         ,p_addr_attribute9         => l_address.addr_attribute9
2981                         ,p_addr_attribute10        => l_address.addr_attribute10
2982                         ,p_addr_attribute11        => l_address.addr_attribute11
2983                         ,p_addr_attribute12        => l_address.addr_attribute12
2984                         ,p_addr_attribute13        => l_address.addr_attribute13
2985                         ,p_addr_attribute14        => l_address.addr_attribute14
2986                         ,p_addr_attribute15        => l_address.addr_attribute15
2987                         ,p_addr_attribute16        => l_address.addr_attribute16
2988                         ,p_addr_attribute17        => l_address.addr_attribute17
2989                         ,p_addr_attribute18        => l_address.addr_attribute18
2990                         ,p_addr_attribute19        => l_address.addr_attribute19
2991                         ,p_addr_attribute20        => l_address.addr_attribute20
2992                         ,p_add_information13       => l_address.add_information13
2993                         ,p_add_information14       => l_address.add_information14
2994                         ,p_add_information15       => l_address.add_information15
2995                         ,p_add_information16       => l_address.add_information16
2996                         ,p_add_information17       => l_address.add_information17
2997                         ,p_add_information18       => l_address.add_information18
2998                         ,p_add_information19       => l_address.add_information19
2999                         ,p_add_information20       => l_address.add_information20);
3000         END IF;
3001 
3002 	hr_utility.set_location(' Leaving:' || l_proc,80);
3003 
3004 END process_api;
3005 
3006 END hr_process_address_ss;