1 Package PQP_HRTCA_Integration AUTHID CURRENT_USER AS
2 /* $Header: pqphrtcaintg.pkh 120.0 2005/05/29 02:22:00 appldev noship $ */
3
4
5 TYPE t_per_addr IS TABLE OF per_addresses%ROWTYPE INDEX BY Binary_Integer;
6 g_per_addr_rec t_per_addr;
7
8 TYPE oss_pp_rec IS RECORD
9 (pp_rowid Rowid
10 ,passport_id Number(15)
11 ,passport_cntry_code Varchar2(30)
12 ,passport_number Varchar2(150)
13 ,passport_expiry_date Date
14 ,attribute_category Varchar2(150)
15 ,attribute1 Varchar2(150)
16 ,attribute2 Varchar2(150)
17 ,attribute3 Varchar2(150)
18 ,attribute4 Varchar2(150)
19 ,attribute5 Varchar2(150)
20 ,attribute6 Varchar2(150)
21 ,attribute7 Varchar2(150)
22 ,attribute8 Varchar2(150)
23 ,attribute9 Varchar2(150)
24 ,attribute10 Varchar2(150)
25 ,attribute11 Varchar2(150)
26 ,attribute12 Varchar2(150)
27 ,attribute13 Varchar2(150)
28 ,attribute14 Varchar2(150)
29 ,attribute15 Varchar2(150)
30 ,attribute16 Varchar2(150)
31 ,attribute17 Varchar2(150)
32 ,attribute18 Varchar2(150)
33 ,attribute19 Varchar2(150)
34 ,attribute20 Varchar2(150)
35 );
36 TYPE oss_visa_rec IS RECORD
37 (visa_rowid ROWID
38 ,visa_id Number(15)
39 ,visa_type Varchar2(30)
40 ,visa_number Varchar2(30)
41 ,visa_issue_date Date
42 ,visa_expiry_date Date
43 ,visa_category Varchar2(30)
44 ,visa_issuing_post Varchar2(30)
45 ,passport_id Number(15)
46 ,agent_org_unit_cd Varchar2(30)
47 ,agent_person_id Number(15)
48 ,agent_contact_name Varchar2(80)
49 ,visa_issuing_country Varchar2(30)
50 ,attribute_category Varchar2(30)
51 ,attribute1 Varchar2(150)
52 ,attribute2 Varchar2(150)
53 ,attribute3 Varchar2(150)
54 ,attribute4 Varchar2(150)
55 ,attribute5 Varchar2(150)
56 ,attribute6 Varchar2(150)
57 ,attribute7 Varchar2(150)
58 ,attribute8 Varchar2(150)
59 ,attribute9 Varchar2(150)
60 ,attribute10 Varchar2(150)
61 ,attribute11 Varchar2(150)
62 ,attribute12 Varchar2(150)
63 ,attribute13 Varchar2(150)
64 ,attribute14 Varchar2(150)
65 ,attribute15 Varchar2(150)
66 ,attribute16 Varchar2(150)
67 ,attribute17 Varchar2(150)
68 ,attribute18 Varchar2(150)
69 ,attribute19 Varchar2(150)
70 ,attribute20 Varchar2(150)
71 );
72 TYPE Visit_Hist_Rec IS RECORD
73 (row_id ROWID
74 ,port_of_entry Varchar2(150)
75 ,port_of_entry_m Varchar2(150)
76 ,cntry_entry_form_num Varchar2(150)
77 ,visa_id Number(10)
78 ,visa_type Varchar2(150)
79 ,visa_number Varchar2(150)
80 ,visa_issue_date Date
81 ,visa_expiry_date Date
82 ,visa_category Varchar2(150)
83 ,visa_issuing_post Varchar2(150)
84 ,passport_id Number(10)
85 ,visit_start_date Date
86 ,visit_end_date Date
87 );
88
89 -- =============================================================================
90 -- ~ InsUpd_InHR_PassPort:
91 -- =============================================================================
92 -- {Start Of Comments}
93 --
94 -- Description: Use this API to create the passport information for a given OSS
95 -- person based on the party_id in per_all_people_f.
96 --
97 -- Prerequisites:
98 -- Person must exits
99 -- Person Information Type must already exist
100 --
101 -- In Parameters:
102 -- Name Reqd Type Description
103 -- p_business_group_id Yes Number Business Group Id
104 -- p_person_id Yes Number per_all_people_f.person_id
105 -- p_party_id Yes Number per_all_people_f.party_id
106 -- p_effective_date Yes Date Effective Date for the EIT
107 -- p_pp_error_code Yes Varchar2 returns the error code
108 -- p_passport_warning Yes Boolean set to true when error occurs
109 -- Post Success:
110 -- The person extra info is created based on the passport information present
111 -- in OSS igs_pe_passport table.
112 --
113 -- Post Failure:
114 -- The API does not create the person extra info and sets the error code
115 -- and warning flag to TRUE.
116 -- Access Status:
117 -- Private.
118 --
119 -- {End Of Comments}
120 -- =============================================================================
121 PROCEDURE InsUpd_InHR_PassPort
122 (p_business_group_id IN Number
123 ,p_person_id IN Number
124 ,p_party_id IN Number
125 ,p_effective_date IN Date
126 ,p_pp_error_code OUT NOCOPY Varchar2
127 ,p_passport_warning OUT NOCOPY Boolean
128 );
129 -- =============================================================================
130 -- InsUpd_InHR_Visa:
131 -- =============================================================================
132 -- {Start Of Comments}
133 --
134 -- Description: Use this API to create the Visa information for a given OSS
135 -- person based on the party_id in per_all_people_f.
136 --
137 -- Prerequisites:
138 -- Person must exits
139 -- Person Information Type must already exist
140 --
141 -- In Parameters:
142 -- Name Reqd Type Description
143 -- p_business_group_id Yes Number Business Group Id
144 -- p_person_id Yes Number per_all_people_f.person_id
145 -- p_party_id Yes Number per_all_people_f.party_id
146 -- p_effective_date Yes Date Effective Date for the EIT
147 -- p_visa_error_code Yes Varchar2 Returns the error code
148 -- p_visa_warning Yes Boolean Set to true when error occurs
149 -- Post Success:
150 -- The person extra info is created based on the Visa information present
151 -- in OSS igs_pe_visa table.
152 --
153 -- Post Failure:
154 -- The API does not create the person extra info and sets the error code
155 -- and warning flag to TRUE.
156 -- Access Status:
157 -- Private.
158 --
159 -- {End Of Comments}
160 -- =============================================================================
161 PROCEDURE InsUpd_InHR_Visa
162 (p_business_group_id IN Number
163 ,p_person_id IN Number
164 ,p_party_id IN Number
165 ,p_effective_date IN Date
166 ,p_visa_error_code OUT NOCOPY Varchar2
167 ,p_visa_warning OUT NOCOPY Boolean
168 );
169 -- =============================================================================
170 -- InsUpd_InHR_Visit:
171 -- =============================================================================
172 -- {Start Of Comments}
173 --
174 -- Description: Use this API to create the Visa Visit history for a given OSS
175 -- person based on the party_id in per_all_people_f.
176 --
177 -- Prerequisites:
178 -- Person must exits
179 -- Person Information Type must already exist
180 --
181 -- In Parameters:
182 -- Name Reqd Type Description
183 -- p_business_group_id Yes Number Business Group Id
184 -- p_person_id Yes Number per_all_people_f.person_id
185 -- p_party_id Yes Number per_all_people_f.party_id
186 -- p_effective_date Yes Date Effective Date for the EIT
187 -- p_visit_error_code Yes Varchar2 Returns the error code
188 -- p_visit_warning Yes Boolean Set to true when error occurs
189 -- Post Success:
190 -- The person extra info is created based on the Visa Visit history present
191 -- in OSS igs_pe_visit_histry_v view.
192 --
193 -- Post Failure:
194 -- The API does not create the person extra info and sets the error code
195 -- and warning flag to TRUE.
196 -- Access Status:
197 -- Private.
198 --
199 -- {End Of Comments}
200 -- =============================================================================
201 PROCEDURE InsUpd_InHR_Visit
202 (p_business_group_id IN Number
203 ,p_person_id IN Number
204 ,p_party_id IN Number
205 ,p_effective_date IN Date
206 ,p_visit_error_code OUT NOCOPY Varchar2
207 ,p_visit_warning OUT NOCOPY Boolean
208 );
209 -- =============================================================================
210 -- InsUpd_InHR_OSSPerDtls:
211 -- =============================================================================
212 -- {Start Of Comments}
213 --
214 -- Description: Use this API to create the OSS Person details for a given OSS
215 -- person based on the party_id in per_all_people_f.
216 --
217 -- Prerequisites:
218 -- Person must exits
219 -- Person Information Type must already exist
220 --
221 -- In Parameters:
222 -- Name Reqd Type Description
223 -- p_business_group_id Yes Number Business Group Id
224 -- p_person_id Yes Number per_all_people_f.person_id
225 -- p_party_id Yes Number per_all_people_f.party_id
226 -- p_effective_date Yes Date Effective Date for the EIT
227 -- p_oss_error_code Yes Varchar2 Returns the error code
228 -- p_ossDtls_warning Yes Boolean Set to true when error occurs
229 -- Post Success:
230 -- The person extra info is created based on the OSS Person details
231 -- in HZ_PARTIES, HZ_PERSON_PROFILES.
232 --
233 -- Post Failure:
234 -- The API does not create the person extra info and sets the error code
235 -- and warning flag to TRUE.
236 -- Access Status:
237 -- Private.
238 --
239 -- {End Of Comments}
240 -- =============================================================================
241 PROCEDURE InsUpd_InHR_OSSPerDtls
242 (p_business_group_id IN Number
243 ,p_person_id IN Number
244 ,p_party_id IN Number
245 ,p_effective_date IN Date
246 ,p_oss_error_code OUT NOCOPY Varchar2
247 ,p_ossDtls_warning OUT NOCOPY Boolean
248 );
249 -- =============================================================================
250 -- ~ InsUpd_OSS_PassPort: Insert or update Passport details in OSS
251 -- =============================================================================
252 -- {Start Of Comments}
253 --
254 -- Description: Use this API to create/update the Passport details in OSS when
255 -- a Passport info. is being created/updated in HRMS for a person who is also a
256 -- OSS Person, based on the Person EIT PQP_OSS_PERSON_DETAILS and
257 -- Synchronize OSS Data, must equal to Y. This API is called from the Row-Handler
258 -- hook for the table per_people_extra_info.
259 --
260 -- Prerequisites:
261 -- Person must exits
262 -- Person Information Type must already exist
263 --
264 -- In Parameters:
265 -- Name Reqd Type Description
266 -- p_person_id Yes Number per_all_people_f.person_id
267 -- p_party_id Yes Number per_all_people_f.party_id
268 -- p_action Yes Date Effective Date for the EIT
269 -- p_pei_info_rec_old Yes per_people_extra_info%ROWTYPE
270 -- p_pei_info_rec_new Yes per_people_extra_info%ROWTYPE
271 -- Post Success:
272 -- The passport details for the OSS person is created/updated based on the
273 -- old and new value for the Person EIT in HRMS
274 --
275 -- Post Failure:
276 -- The API does not create the person passport details and will raise the
277 -- corresponding OSS error to the user.
278 -- Access Status:
279 -- Private.
280 --
281 -- {End Of Comments}
282 -- =============================================================================
283 PROCEDURE InsUpd_OSS_PassPort
284 (p_person_id IN Number
285 ,p_party_id IN Number
286 ,p_action IN Varchar2
287 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
288 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
289 );
290 -- ===========================================================================
291 -- ~ InsUpd_OSS_Visa: Insert or update Visa details in OSS
292 -- ===========================================================================
293 -- {Start Of Comments}
294 --
295 -- Description: Use this API to create/update the Visa details in OSS when
296 -- a Visit info. is being created/updated in HRMS for a person who is also a
297 -- OSS Person, based on the Person EIT PQP_OSS_PERSON_DETAILS and
298 -- Synchronize OSS Data, must equal to Y. This API is called from the Row-Handler
299 -- hook for the table per_people_extra_info.
300 --
301 -- Prerequisites:
302 -- Person must exits
303 -- Person Information Type must already exist
304 --
305 -- In Parameters:
306 -- Name Reqd Type Description
307 -- p_person_id Yes Number per_all_people_f.person_id
308 -- p_party_id Yes Number per_all_people_f.party_id
309 -- p_action Yes Date Effective Date for the EIT
310 -- p_pei_info_rec_old Yes per_people_extra_info%ROWTYPE
311 -- p_pei_info_rec_new Yes per_people_extra_info%ROWTYPE
312 -- Post Success:
313 -- The Visa details for the OSS person is created/updated based on the
314 -- old and new value for the Person Visa EIT in HRMS
315 --
316 -- Post Failure:
317 -- The API does not create the person Visa details and will raise the
318 -- corresponding OSS error to the user.
319 -- Access Status:
320 -- Private.
321 --
322 -- {End Of Comments}
323 -- =============================================================================
324 PROCEDURE InsUpd_OSS_Visa
325 (p_person_id IN Number
326 ,p_party_id IN Number
327 ,p_action IN Varchar2
328 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
329 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
330 );
331 -- =============================================================================
332 -- ~ InsUpd_OSS_VisitHstry: Insert or update Visa details in OSS
333 -- =============================================================================
334 -- {Start Of Comments}
335 --
336 -- Description: Use this API to create/update the Visa Visit history in OSS when
337 -- a Visit history info. is being created/updated in HRMS for a person who is
338 -- also an OSS Person, based on the Person EIT PQP_OSS_PERSON_DETAILS and
339 -- Synchronize OSS Data, must equal to Y. This API is called from the Row-Handler
340 -- hook for the table per_people_extra_info.
341 --
342 -- Prerequisites:
343 -- Person must exits
344 -- Person Information Type must already exist
345 --
346 -- In Parameters:
347 -- Name Reqd Type Description
348 -- p_person_id Yes Number per_all_people_f.person_id
349 -- p_party_id Yes Number per_all_people_f.party_id
350 -- p_action Yes Date Effective Date for the EIT
351 -- p_pei_info_rec_old Yes per_people_extra_info%ROWTYPE
352 -- p_pei_info_rec_new Yes per_people_extra_info%ROWTYPE
353 -- Post Success:
354 -- The Visa Visit history details for the OSS person is created/updated based
355 -- on the old and new value for the Person Visa EIT in HRMS
356 --
357 -- Post Failure:
358 -- The API does not create the person Visa Visit history details and will
359 -- raise the corresponding OSS error to the user.
360 -- Access Status:
361 -- Private.
362 --
363 -- {End Of Comments}
364 -- =============================================================================
365 PROCEDURE InsUpd_OSS_VisitHistory
366 (p_person_id IN Number
367 ,p_party_id IN Number
368 ,p_action IN Varchar2
369 ,p_pei_info_rec_old IN per_people_extra_info%ROWTYPE
370 ,p_pei_info_rec_new IN per_people_extra_info%ROWTYPE
371 );
372 -- =============================================================================
373 -- ~ InsUpd_Asg_Extra_info: Insert, Update or Delete Assignment Extra Info.
374 -- =============================================================================
375 -- {Start Of Comments}
376 --
377 -- Description: Use this API to create/update/delete the Assignment Extra Info.
378 -- for a given assignment.
379 --
380 -- Prerequisites:
381 -- Person must a valid assignment.
382 -- Assignment Extra Information Type must be a valid one.
383 --
384 -- In Parameters:
385 -- Name Reqd Type Description
386 -- p_assignment_id Yes Number per_all_assignments_f.assignment_id
387 -- p_business_group_id Yes Number per_all_assignments_f.business_group_id
388 -- p_validate Yes Boolean
389 -- p_action Yes Varchar2
390 -- p_extra_info_rec Yes per_assignment_extra_info%ROWTYPE
391 --
392 -- Post Success:
393 -- The Assignment Extra Information for the person is created/updated based
394 -- on the value of p_action(INSERT,UPDATE,DELETE) and the values must be
395 -- populated for the record structure.
396 --
397 -- Post Failure:
398 -- The API does not create/update/delete the assignment extra info type and
399 -- raises the corresponding error to the user.
400 --
401 -- Access Status:
402 -- Private.
403 -- =============================================================================
404 PROCEDURE InsUpd_Asg_Extra_info
405 (p_assignment_id IN Number
406 ,p_business_group_id IN Number
407 ,p_validate IN Boolean DEFAULT FALSE
408 ,p_action IN Varchar2
409 ,p_extra_info_rec IN OUT NOCOPY per_assignment_extra_info%ROWTYPE
410 );
411 -- =============================================================================
412 -- ~ InsUpd_Per_Extra_info: Insert, Update or Delete Person Extra Information
413 -- =============================================================================
414 -- {Start Of Comments}
415 --
416 -- Description: Use this API to create/update/delete the Person Extra Info.
417 -- for a given person id.
418 --
419 -- Prerequisites:
420 -- Person must a valid assignment.
421 -- Assignment Extra Information Type must be a valid one.
422 --
423 -- In Parameters:
424 -- Name Reqd Type Description
425 -- p_person_id Yes Number per_all_people_f.person_id
426 -- p_business_group_id Yes Number per_all_people_f.business_group_id
427 -- p_validate Yes Boolean Default false
428 -- p_action Yes Varchar2 INSERT, UPDATE, DELETE
429 -- p_extra_info_rec Yes per_people_extra_info%ROWTYPE
430 -- Post Success:
431 -- The Person Extra Information for the person is created/updated/deleted
432 -- based on the value of p_action(INSERT,UPDATE,DELETE) and the values must be
433 -- populated for the record structure.
434 --
435 -- Post Failure:
436 -- The API does not create/update/delete the Person extra info type and
437 -- raises the corresponding error to the user.
438 -- Access Status:
439 -- Private.
440 -- =============================================================================
441 PROCEDURE InsUpd_Per_Extra_info
442 (p_person_id IN Number
443 ,p_business_group_id IN Number
444 ,p_validate IN Boolean DEFAULT FALSE
445 ,p_action IN Varchar2
446 ,p_extra_info_rec IN OUT NOCOPY per_people_extra_info%ROWTYPE
447 );
448 -- =============================================================================
449 -- ~ InsUpd_SIT_info:
450 -- =============================================================================
451 PROCEDURE InsUpd_SIT_info
452 (p_person_id IN Number
453 ,p_business_group_id IN Number
454 ,p_validate IN Boolean DEFAULT FALSE
455 ,p_effective_date IN Date
456 ,p_action IN Varchar2
457 ,p_analysis_criteria_rec IN OUT NOCOPY per_analysis_criteria%ROWTYPE
458 ,p_analyses_rec IN OUT NOCOPY per_person_analyses%ROWTYPE
459 );
460
461 -- =============================================================================
462 -- ~ Chk_Address_Style: Returns the address style to use based on the bus. group
463 -- ==================== legislation code and address country code of the TCA
464 -- ~ hz_locations.country. The [XX]_GLB address is used when a particular
465 -- ~ localization is not installed. In case the Address DDF does not have that
466 -- ~ country as context, then the GENERIC address style is returned.
467 -- =============================================================================
468 FUNCTION Chk_Address_Style
469 (p_leg_code IN Varchar2
470 ,p_HZ_country IN Varchar2
471 ) RETURN Varchar2;
472
473 FUNCTION Chk_Address_Style
474 (p_party_id IN Number
475 ,p_bus_grp_id IN Number
476 ) RETURN Varchar2;
477
478 FUNCTION Chk_Address_Style
479 (p_leg_code IN Varchar2
480 ,p_HZ_country IN Varchar2
481 ,p_location_id IN Number
482 ,p_party_id IN Number DEFAULT NULL
483 ,p_effective_date IN Date DEFAULT NULL
484 ,p_business_group_id IN Number DEFAULT NULL
485 ,p_primary_flag IN Varchar2 DEFAULT NULL
486 ,p_party_site_id IN Number DEFAULT NULL
487 ) RETURN Varchar2;
488
489 -- =============================================================================
490 -- ~ Get_Concat_HR_Address: Returns valid HR Address ina concat. string
491 -- =============================================================================
492 FUNCTION Get_Concat_HR_Address
493 (p_location_id IN Number
494 ,p_party_id IN Number DEFAULT NULL
495 ,p_effective_date IN Date DEFAULT NULL
496 ,p_business_group_id IN Number DEFAULT NULL
497 ,p_primary_flag IN Varchar2 DEFAULT NULL
498 ,p_party_site_id IN Number DEFAULT NULL
499 ) RETURN Varchar2;
500
501 FUNCTION Get_Segment
502 (p_hzlocation_id IN Number
503 ,p_party_id IN Number
504 ,p_business_group_id IN Number
505 ,p_seg_name IN Varchar2
506 ) RETURN Varchar2;
507
508 -- =============================================================================
509 -- ~ Create_Address_TCA_To_HR:
510 -- =============================================================================
511 PROCEDURE Create_Address_TCA_To_HR
512 (p_validate IN Boolean DEFAULT FALSE
513 ,p_effective_date IN Date
514 ,p_party_id IN Number
515 ,p_business_group_id IN Number DEFAULT NULL
516 ,p_party_site_id IN Number DEFAULT NULL
517 ,p_style IN Varchar2 DEFAULT NULL
518 ,p_location_id IN Number DEFAULT NULL
519 ,p_pradd_ovlapval_override IN Boolean DEFAULT FALSE
520 ,p_validate_county IN Boolean DEFAULT TRUE
521 ,p_primary_flag IN Varchar2 DEFAULT 'Y'
522 ,p_address_type IN Varchar2 DEFAULT NULL
523 ,p_overide_TCA_Mapping IN Varchar2 DEFAULT 'N'
524 --,p_HZ_Location_Rec IN Hz_Location_V2pub.Location_Rec_Type DEFAULT NULL
525 -- Out Variable from HR
526 ,p_HR_address_id OUT NOCOPY Number
527 ,p_HR_object_version_number OUT NOCOPY Number
528 );
529 -- =============================================================================
530 -- ~ Update_Address_TCA_To_HR
531 -- =============================================================================
532 PROCEDURE Update_Address_TCA_To_HR
533 (p_validate IN Boolean DEFAULT FALSE
534 ,p_effective_date IN Date
535 ,p_party_id IN Number
536 ,p_business_group_id IN Number DEFAULT NULL
537 ,p_party_site_id IN Number DEFAULT NULL
538 ,p_style IN Varchar2 DEFAULT NULL
539 ,p_location_id IN Number DEFAULT NULL
540 ,p_pradd_ovlapval_override IN Boolean DEFAULT FALSE
541 ,p_validate_county IN Boolean DEFAULT TRUE
542 ,p_primary_flag IN Varchar2 DEFAULT 'Y'
543 ,p_address_type IN Varchar2 DEFAULT NULL
544 ,p_overide_TCA_Mapping IN Varchar2 DEFAULT 'N'
545 --,p_HZ_Location_Rec IN Hz_Location_V2pub.Location_Rec_Type DEFAULT NULL
546 -- Out Variable from HR
547 ,p_HR_object_version_number IN OUT NOCOPY Number
548 );
549 -- =============================================================================
550 -- Create_Address_HR_To_TCA: The API needs to be called only when a student
551 -- ======================== exists in HR as an employee and his address from
552 -- TCA needs to be created in HR.
553 -- Parameters
554 -- ==========
555 -- (a) IN
556 -- ======
557 -- (1) p_person_id : Person_id from per_addresses table
558 -- (2) p_effective_date : Effective date is the session date when the
559 -- Address migration is to be done.
560 -- (3) p_party_id : The Student party_id from hz_parties
561 -- (4) p_party_type : Deafult to PERSON for Students
562 -- (5) p_action : Default to INSERT i.e. creates a new record
563 -- (6) p_status : Defalut to A = Active
564 -- (7) p_start_date : Start Date of the address record. This is only
565 -- specific to OSS and stores in the table
566 -- igs_pe_hz_pty_sites.
567 -- (8) p_start_date : End Date of the address record. This is only
568 -- specific to OSS and stores in the table
569 -- igs_pe_hz_pty_sites.
570 -- (9) p_country : Two Char country code as in fnd_territories
571 -- (10) p_address_style : Address Style if different from the country cd
572 -- (11) p_primary_add_flag : Indicates the address is primary one.
573 -- (12) p_overide_HR_Address : If set to Y it would make use of records
574 -- p_HZ_Location_Rec, p_HZ_Party_Site_Rec to
575 -- Create the address for the party in TCA.
576 -- (b) In Out Variables
577 -- ====================
578 -- (1) p_location_id : hz_locations.location_id
579 -- (2) p_party_site_id : hz_party_sites.party_site_id
580 -- (3) p_last_update_date : hz_locations.last_update_date
581 -- (4) p_party_site_ovn : hz_party_sites.object_version_number
582 -- (5) p_location_ovn : hz_locations.object_version_number
583 -- (6) p_rowid : hz_locations.row_id
584 -- (c) Out Variables
585 -- =================
586 -- (1) p_return_status : Return Status
587 -- (2) p_msg_data : Message text in case of single error.
588 --
589 -- =============================================================================
590 PROCEDURE Create_Address_HR_To_TCA
591 (p_business_group_id IN Number
592 ,p_person_id IN Number
593 ,p_party_id IN Number
594 ,p_address_id IN Number
595 ,p_effective_date IN Date
596 ,p_per_addr_rec_new IN per_addresses%ROWTYPE
597 -- TCA
598 ,p_party_type IN Varchar2
599 ,p_action IN Varchar2
600 ,p_status IN hz_party_sites.status%TYPE
601 -- In Out Variables
602 ,p_location_id IN OUT NOCOPY Number
603 ,p_party_site_id IN OUT NOCOPY Number
604 ,p_last_update_date IN OUT NOCOPY Date
605 ,p_party_site_ovn IN OUT NOCOPY Number
606 ,p_location_ovn IN OUT NOCOPY Number
607 ,p_rowid IN OUT NOCOPY Varchar2
608 -- Out Variables
609 ,p_return_status OUT NOCOPY Varchar2
610 ,p_msg_data OUT NOCOPY Varchar2
611 );
612
613 -- =============================================================================
614 -- Update_Address_HR_To_TCA: The API needs to be called only when a student
615 -- ======================== exists in HR as an employee and his address from
616 -- TCA needs to be created in HR.
617 -- Parameters
618 -- ==========
619 -- (a) IN
620 -- ======
621 -- (1) p_person_id : Person_id from per_addresses table
622 -- (2) p_effective_date : Effective date is the session date when the
623 -- Address migration is to be done.
624 -- (3) p_party_id : The Student party_id from hz_parties
625 -- (4) p_party_type : Deafult to PERSON for Students
626 -- (5) p_action : Default to UPDATE i.e. updates existing record
627 -- (6) p_status : Defalut to A = Active
628 -- (7) p_start_date : Start Date of the address record. This is only
629 -- specific to OSS and stores in the table
630 -- igs_pe_hz_pty_sites.
631 -- (8) p_start_date : End Date of the address record. This is only
632 -- specific to OSS and stores in the table
633 -- igs_pe_hz_pty_sites.
634 -- (9) p_country : Two Char country code as in fnd_territories
635 -- (10) p_address_style : Address Style if different from the country cd
636 -- (11) p_primary_add_flag : Indicates the address is primary one.
637 -- (12) p_overide_HR_Address : If set to Y it would make use of records
638 -- p_HZ_Location_Rec, p_HZ_Party_Site_Rec to
639 -- Create the address for the party in TCA.
640 -- (b) In Out Variables
641 -- ====================
642 -- (1) p_location_id : hz_locations.location_id
643 -- (2) p_party_site_id : hz_party_sites.party_site_id
644 -- (3) p_last_update_date : hz_locations.last_update_date
645 -- (4) p_party_site_ovn : hz_party_sites.object_version_number
646 -- (5) p_location_ovn : hz_locations.object_version_number
647 -- (6) p_rowid : hz_locations.row_id
648 -- (c) Out Variables
649 -- =================
650 -- (1) p_return_status : Return Status
651 -- (2) p_msg_data : Message text in case of single error.
652 --
653 -- =============================================================================
654 PROCEDURE Update_Address_HR_To_TCA
655 (p_business_group_id IN Number
656 ,p_person_id IN Number
657 ,p_party_id IN Number
658 ,p_address_id IN Number
659 ,p_effective_date IN Date
660 ,p_per_addr_rec_new IN per_addresses%ROWTYPE
661 ,p_per_addr_rec_old IN per_addresses%ROWTYPE
662 -- TCA
663 ,p_party_type IN Varchar2 DEFAULT 'PERSON'
664 ,p_action IN Varchar2 DEFAULT 'UPDATE'
665 ,p_status IN Varchar2 DEFAULT 'A'
666 -- In Out Variables
667 ,p_location_id IN OUT NOCOPY Number
668 ,p_party_site_id IN OUT NOCOPY Number
669 ,p_last_update_date IN OUT NOCOPY Date
670 ,p_party_site_ovn IN OUT NOCOPY Number
671 ,p_location_ovn IN OUT NOCOPY Number
672 ,p_rowid IN OUT NOCOPY Varchar2
673 -- Out Variables
674 ,p_return_status OUT NOCOPY Varchar2
675 ,p_msg_data OUT NOCOPY Varchar2
676 );
677 -- =============================================================================
678 -- Person_Address_API:
679 -- =============================================================================
680 PROCEDURE Person_Address_API
681 (p_HR_Address_Rec IN OUT NOCOPY Per_Addresses%ROWTYPE
682 ,p_validate IN Boolean DEFAULT FALSE
683 ,p_action IN Varchar2 DEFAULT 'CREATE'
684 ,p_effective_date IN Date
685 ,p_pradd_ovlapval_override IN Boolean DEFAULT FALSE
686 ,p_validate_county IN Boolean DEFAULT TRUE
687 ,p_primary_flag IN Varchar2 DEFAULT 'Y'
688 ,p_HR_address_id OUT NOCOPY Number
689 ,p_HR_object_version_number OUT NOCOPY Number);
690
691 -- =============================================================================
692 -- ~ Chk_OSS_Install: Check to see if the OSS product is installed or not. This
693 -- ~ check is req. before launching the student search page.
694 -- =============================================================================
695 Function Chk_OSS_Install
696 Return Varchar2;
697
698 END PQP_HRTCA_Integration;