[Home] [Help]
PACKAGE BODY: APPS.HR_360_MESSAGE
Source
1 PACKAGE body HR_360_MESSAGE as
2 /* $Header: perhrhd360vw.pkb 120.6 2008/03/19 09:51:41 sathkris noship $ */
3 procedure HR_360_PERSON_VIEW ( P_EMPLOYEE_NUMBER VARCHAR2 ,
4 p_effective_date date default sysdate,
5 P_BUSINESS_GROUP_ID NUMBER,
6 p_tp_site_id number,
7 p_trxn_id varchar2)
8
9 is
10
11 l_params WF_PARAMETER_LIST_T;
12
13 L_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%type;
14 L_USER_PERSON_TYPE VARCHAR2(60);
15 L_DATE_OF_BIRTH DATE;
16 L_TOWN_OF_BIRTH PER_ALL_PEOPLE_F.TOWN_OF_BIRTH%type;
17 L_COUNTRY_OF_BIRTH PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH%type;
18 L_DATE_OF_DEATH DATE;
19 L_ORIGINAL_DATE_OF_HIRE DATE;
20 L_EFFECTIVE_START_DATE DATE;
21 L_SEX VARCHAR2(30);
22 L_MARITAL_STATUS VARCHAR2(30);
23 L_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
24 L_PRE_NAME_ADJUNCT PER_ALL_PEOPLE_F.PRE_NAME_ADJUNCT%type;
25 L_SUFFIX VARCHAR2(30);
26 L_TITLE VARCHAR2(30);
27 L_LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%type;
28 L_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%type;
29 L_MIDDLE_NAMES PER_ALL_PEOPLE_F.MIDDLE_NAMES%type;
30 L_ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%type;
31 L_DATE_FROM DATE;
32 L_COUNTRY PER_ADDRESSES.COUNTRY%type;
33 L_ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%type;
34 L_ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%type;
35 L_ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%type;
36 L_TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%type;
37 L_TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%type;
38 L_REGION_1 PER_ADDRESSES.REGION_1%type;
39 L_REGION_2 PER_ADDRESSES.REGION_1%type;
40 L_POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%type;
41 L_EMAIL_ADDRESS PER_ALL_PEOPLE_F.EMAIL_ADDRESS%type;
42 L_PHONE_TYPE PER_PHONES.PHONE_TYPE%type;
43 L_PHONE_NUMBER PER_PHONES.PHONE_NUMBER%type;
44 L_NATIONALITY VARCHAR2(30);
45 L_NATIONAL_IDENTIFIER PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%type;
46 L_CONTACT_NAME VARCHAR2(240);
47 L_CONTACT_ADDRESSLINE1 PER_ADDRESSES.ADDRESS_LINE1%type;
48 L_CONTACT_ADDRESSLINE2 PER_ADDRESSES.ADDRESS_LINE2%type;
49 L_CONTACT_ADDRESSLINE3 PER_ADDRESSES.ADDRESS_LINE3%type;
50 L_CONTACT_COUNTRY PER_ADDRESSES.COUNTRY%type;
51 L_CONTACT_TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%type;
52 L_CONTACT_TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%type;
53 L_CONTACT_REGION_1 PER_ADDRESSES.REGION_1%type;
54 L_CONTACT_REGION_2 PER_ADDRESSES.REGION_1%type;
55 L_CONTACT_POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%type;
56 L_RELATIONSHIP VARCHAR2(30);
57 l_EMP_JOB_NAME VARCHAR2(700);
58 L_EMP_GRADE_NAME VARCHAR2(240);
59 L_POSITION_NAME VARCHAR2(240);
60 L_EMPLOYMENT_CATEGORY VARCHAR2(30);
61 L_NORMAL_HOURS number;
62 L_ASG_LOCATION VARCHAR2(60) ;
63 L_ASSIGNMENT_STATUS VARCHAR2(80);
64 L_DEPARTMENT_NAME VARCHAR2 (240);
65 L_BUSINESS_GROUP_NAME VARCHAR2 (240) ;
66 L_PAYROLL_NAME VARCHAR2(80);
67 L_SUPERVISOR_NAME VARCHAR2(240) ;
68
69 --- VAR DECL -----
70 l_style varchar2(30);
71 l_end_user_col_name1 varchar2(60);
72 l_end_user_col_name2 varchar2(60);
73 l_end_user_col_name3 varchar2(60);
74 l_end_user_col_name4 varchar2(60);
75 l_end_user_col_name5 varchar2(60);
76 l_end_user_col_name6 varchar2(60);
77 l_end_user_col_name7 varchar2(60);
78 l_end_user_col_name8 varchar2(60);
79 l_end_user_col_name9 varchar2(60);
80 l_end_user_col_name10 varchar2(60);
81 l_end_user_col_name11 varchar2(60);
82 l_end_user_col_name12 varchar2(60);
83 l_end_user_col_name13 varchar2(60);
84 l_end_user_col_name14 varchar2(60);
85 l_end_user_col_name15 varchar2(60);
86 l_end_user_col_name16 varchar2(60);
87 l_end_user_col_name17 varchar2(60);
88 l_end_user_col_name18 varchar2(60);
89 l_end_user_col_name19 varchar2(60);
90 l_end_user_col_name20 varchar2(60);
91 l_end_user_col_name21 varchar2(60);
92 l_end_user_col_name22 varchar2(60);
93 l_end_user_col_name23 varchar2(60);
94 l_end_user_col_name24 varchar2(60);
95 l_end_user_col_name25 varchar2(60);
96
97 l_app_col_name1 varchar2(30);
98 l_app_col_name2 varchar2(30);
99 l_app_col_name3 varchar2(30);
100 l_app_col_name4 varchar2(30);
101 l_app_col_name5 varchar2(30);
102 l_app_col_name6 varchar2(30);
103 l_app_col_name7 varchar2(30);
104 l_app_col_name8 varchar2(30);
105 l_app_col_name9 varchar2(30);
106 l_app_col_name10 varchar2(30);
107 l_app_col_name11 varchar2(30);
108 l_app_col_name12 varchar2(30);
109 l_app_col_name13 varchar2(30);
110 l_app_col_name14 varchar2(30);
111 l_app_col_name15 varchar2(30);
112 l_app_col_name16 varchar2(30);
113 l_app_col_name18 varchar2(30);
114 l_app_col_name19 varchar2(30);
115 l_app_col_name20 varchar2(30);
116 l_app_col_name21 varchar2(30);
117 l_app_col_name22 varchar2(30);
118 l_app_col_name23 varchar2(30);
119 l_app_col_name24 varchar2(30);
120 l_app_col_name25 varchar2(30);
121 p_tp_site_id_act varchar2(100);
122
123 L_APP_COL_VALUE VARCHAR2(150);
124
125
126 L_SEQNUM NUMBER(30);
127 l_con_personid number;
128 l_con_bgid number;
129
130 cursor csr_check_person_add is
131 select style,ADDRESS_ID
132 from per_addresses padr, per_all_people_f papf
133 where papf.employee_number =P_EMPLOYEE_NUMBER
134 and papf.business_group_id =P_BUSINESS_GROUP_ID
135 AND P_EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
136 AND PADR.PERSON_ID=PAPF.PERSON_ID
137 AND PADR.business_group_id =PAPF.BUSINESS_GROUP_ID
138 AND PRIMARY_FLAG ='Y'
139 and p_effective_date
140 between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) ;
141
142 CURSOR CSR_GET_LEG_SPEC_COLS (P_STYLE VARCHAR2) IS
143 SELECT end_user_column_name ,APPLICATION_COLUMN_NAME
144 FROM fnd_descr_flex_col_usage_vl
145 WHERE(application_id = 800)
146 AND(descriptive_flexfield_name = 'Address Structure')
147 AND(descriptive_flex_context_code =P_STYLE)
148 ORDER BY column_seq_num;
149
150 CURSOR CSR_GET_CONTACT_ADD (P_PERSONID NUMBER, P_BG_ID NUMBER) IS
151 select style,ADDRESS_ID
152 from per_addresses padr
153 where person_id=P_PERSONID and BUSINESS_GROUP_ID =P_BG_ID;
154
155 /*Modified the cursor to select the employee number for the ex-employees also
156 for the bug 6892089*/
157
158 CURSOR CSR_PERSON_FULL_VIEW IS
159
160 SELECT PPF.PERSON_ID,
161 DECODE ( ppf.CURRENT_NPW_FLAG , 'Y',ppf.NPW_NUMBER,ppf.EMPLOYEE_NUMBER) EMPLOYEE_NUMBER,
162 HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(p_effective_date , PPF.PERSON_ID) ,
163 PPF.DATE_OF_BIRTH,
164 PPF.TOWN_OF_BIRTH,
165 PPF.COUNTRY_OF_BIRTH,
166 PPF.DATE_OF_DEATH,
167 PPF.ORIGINAL_DATE_OF_HIRE,
168 PPF.EFFECTIVE_START_DATE,
169 HL1.MEANING SEX,
170 HL4.MEANING MARITAL_STATUS,
171 PPF.FULL_NAME,
172 PPF.PRE_NAME_ADJUNCT,
173 PPF.SUFFIX,
174 HL3.MEANING TITLE,
175 PPF.LAST_NAME,
176 PPF.FIRST_NAME,
177 PPF.MIDDLE_NAMES,
178 PPF.EMAIL_ADDRESS,
179 PHONE_TYPE,
180 PHONE_NUMBER,
181 HL2.MEANING NATIONALITY,
182 PPF.NATIONAL_IDENTIFIER ,
183 PCR.FULL_NAME CONTACT_NAME,
184 PCR.MEANING RELATIONSHIP,
185 JBT.NAME EMP_JOB_NAME,
186 GDT.NAME EMP_GRADE_NAME,
187 HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME,
188 HR_GENERAL.DECODE_LOOKUP('EMP_CAT', PA.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY,
189 PA.NORMAL_HOURS,
190 LOCTL.LOCATION_CODE ASG_LOCATION ,
191 NVL(AMDTL.USER_STATUS , STTL.USER_STATUS) ASSIGNMENT_STATUS ,
192 OTL.NAME DEPARTMENT_NAME ,
193 OTL1.NAME BUSINESS_GROUP_NAME ,
194 PAY.PAYROLL_NAME PAYROLL_NAME,
195 PPF1.FULL_NAME SUPERVISOR_NAME,
196 PCR.CONTACT_PERSON_ID,
197 PCR.BUSINESS_GROUP_ID
198
199
200 FROM PER_ALL_PEOPLE_F ppf,
201 PER_ALL_PEOPLE_F ppf1,
202 PER_PHONES ppn ,
203 hr_lookups HL1 ,
204 HR_LOOKUPS HL2 ,
205 HR_LOOKUPS HL3 ,
206 HR_LOOKUPS HL4 ,
207 PER_ALL_ASSIGNMENTS_F PA,
208 PER_GRADES PG ,
209 PER_JOBS J,
210 PER_GRADES_TL GDT,
211 PER_JOBS_TL JBT ,
212 HR_LOCATIONS_ALL_TL LOCTL,
213 HR_LOCATIONS_ALL LOC,
214 PER_ASSIGNMENT_STATUS_TYPES ST ,
215 PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
216 PER_ASS_STATUS_TYPE_AMENDS AMD,
217 PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL,
218 HR_ALL_ORGANIZATION_UNITS O,
219 HR_ALL_ORGANIZATION_UNITS_TL OTL ,
220 HR_ALL_ORGANIZATION_UNITS_TL OTL1 ,
221 pay_all_payrolls_f pay,
222
223
224 ( SELECT FULL_NAME ,
225 PCR1.PERSON_ID ,
226 PCR1.BUSINESS_GROUP_ID,
227 C.MEANING,
228 PCR1.CONTACT_PERSON_ID
229
230 FROM PER_ALL_PEOPLE_F PPF1 ,
231 PER_CONTACT_RELATIONSHIPS PCR1 ,
232 HR_LOOKUPS C
233
234 WHERE PPF1.PERSON_ID = CONTACT_PERSON_ID
235 AND PRIMARY_CONTACT_FLAG = 'Y'
236 AND C.LOOKUP_TYPE = 'CONTACT'
237 AND C.LOOKUP_CODE = PCR1.CONTACT_TYPE
238 AND PCR1.BUSINESS_GROUP_ID= PPF1.BUSINESS_GROUP_ID
239 )PCR
240
241 WHERE ppn.PARENT_ID (+) = PPF.PERSON_ID
242 -- Modified for the bug 6895752 starts here
243 /*AND ( ppn.parent_id is null
244 OR ( ppn.parent_id is not null
245 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
246 AND PPN.PHONE_TYPE = 'W1' ))*/
247 -- Modified for the bug 6895752 ends here
248
249 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
250 AND PPN.PHONE_TYPE (+) = 'W1'
251
252 AND HL1.LOOKUP_TYPE (+) = 'SEX'
253 AND HL1.LOOKUP_CODE (+) = ppf.SEX
254 AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
255 AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
256 AND HL3.LOOKUP_TYPE (+) = 'TITLE'
257 AND HL3.LOOKUP_CODE (+) = PPF.TITLE
258 AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
259 AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
260 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
261 AND PCR.PERSON_ID(+) =PPF.PERSON_ID
262 AND PCR.BUSINESS_GROUP_ID(+) = ppf.business_group_id
263 AND PPF.EMPLOYEE_NUMBER =P_EMPLOYEE_NUMBER
264 and ppf.business_group_id =P_BUSINESS_GROUP_ID
265
266 -- FOR JOBS AND GRADES DATA
267 AND PPF.PERSON_ID= PA.PERSON_ID
268 AND PPF.BUSINESS_GROUP_ID=PA.BUSINESS_GROUP_ID
269 AND PA.ASSIGNMENT_TYPE IN ('E','C')
270 AND PA.PRIMARY_FLAG ='Y'
271 AND PA.GRADE_ID = PG.GRADE_ID (+)
272 AND PA.GRADE_ID =GDT.GRADE_ID (+)
273 AND GDT.LANGUAGE(+) = USERENV('LANG')
274 AND PA.JOB_ID = JBT.JOB_ID (+)
275 AND JBT.LANGUAGE(+) = USERENV('LANG')
276 AND PA.JOB_ID = J.JOB_ID (+)
277 -- FOR LOCATION DATA
278 AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
279 AND LOC.LOCATION_ID = LOCTL.LOCATION_ID (+)
280 AND DECODE(LOCTL.LOCATION_ID, NULL, '1', LOCTL.LANGUAGE)= DECODE(LOCTL.LOCATION_ID, NULL, '1', USERENV('LANG'))
281 -- FOR ASG STATUS
282
283 AND PA.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
284 AND PA.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
285 AND PA.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
286 AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
287 AND STTL.LANGUAGE = USERENV('LANG')
288 AND AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID (+)
289 AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', AMDTL.LANGUAGE) =
290 DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', USERENV('LANG'))
291 -- FOR ORGANIZATION NAME
292
293 AND PA.ORGANIZATION_ID = O.ORGANIZATION_ID
294 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
295 AND OTL.LANGUAGE = USERENV('LANG')
296 AND PA.BUSINESS_GROUP_ID = OTL1.ORGANIZATION_ID
297 AND OTL1.LANGUAGE = USERENV('LANG')
298 -- FOR PAYROLL DATA
299 AND PA.PAYROLL_ID = PAY.PAYROLL_ID (+)
300 AND p_effective_date BETWEEN PAY.EFFECTIVE_START_DATE(+) AND PAY.EFFECTIVE_END_DATE (+)
301 AND PA.BUSINESS_GROUP_ID=PAY.BUSINESS_GROUP_ID (+)
302
303 AND p_effective_date BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE
304 -- AND PPF.PERSON_ID in( 317570 ,317599)
305 -- SUPERVISOR NAME
306 AND PA.SUPERVISOR_ID = PPF1.PERSON_ID(+)
307 AND p_effective_date BETWEEN PPF1.EFFECTIVE_START_DATE(+) AND PPF1.EFFECTIVE_END_DATE(+)
308 ;
309
310 type G_ADD_COLS is record
311 (end_user_column_name varchar2(30),
312 APPLICATION_COLUMN_NAME varchar2(30)
313 );
314 --
315 type g_ADD_table is table of G_ADD_COLS index by binary_integer;
316 G_ADD_ATTR g_ADD_table;
317 G_ADD_ATTR_TO_INSERT g_ADD_table ;
318
319 L_SQL VARCHAR2(2000);
320 L_SQL1 VARCHAR2(2000);
321 L_ADDRESS_ID NUMBER;
322 K NUMBER:=1;
323 J NUMBER :=24;
324 L_CURRENT_DATE DATE;
325 P_UNIQUE_KEY NUMBER;
326 L_PERSON_ID NUMBER;
327
328 L_REP_NAME VARCHAR2(240);
329 L_REP_EMPLOYEE_NUM VARCHAR2(30);
330 L_REP_ASG_STATUS VARCHAR2(80);
331 l_rep_clob clob;
332
333 -- Reportee data
334
335 CURSOR CSR_REPORTEES_DATA IS
336
337 SELECT ppf.full_name, employee_number ,
338 NVL(AMDTL.USER_STATUS , STTL.USER_STATUS) ASSIGNMENT_STATUS
339 FROM per_all_assignments_f papf , per_all_people_f ppf ,
340 PER_ASSIGNMENT_STATUS_TYPES ST ,
341 PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
342 PER_ASS_STATUS_TYPE_AMENDS AMD,
343 PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL
344
345 WHERE
346 SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
347 and papf.business_group_id = P_BUSINESS_GROUP_ID
348 and papf.supervisor_id = L_PERSON_ID
349 and papf.assignment_type <> 'B'
350 and papf.primary_flag = 'Y'
351 and papf.person_id=ppf.person_id
352 -- for asg status
353
354 AND PApf.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
355 AND PApf.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
356 AND PApf.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
357 AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
358 AND STTL.LANGUAGE = USERENV('LANG')
359 AND AMD.ASS_STATUS_TYPE_AMEND_ID = AMDTL.ASS_STATUS_TYPE_AMEND_ID (+)
360 AND DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', AMDTL.LANGUAGE) =
361 DECODE(AMDTL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', USERENV('LANG')) ;
362
363
364 -- END OF CUROSR
365
366
367 BEGIN
368
369 SELECT party_site_id into p_tp_site_id_act
370 from ECX_TP_HEADERS
371 where TP_HEADER_ID = p_tp_site_id;
372
373 OPEN CSR_PERSON_FULL_VIEW;
374
375 FETCH CSR_PERSON_FULL_VIEW INTO
376 L_PERSON_ID ,L_EMPLOYEE_NUMBER ,L_USER_PERSON_TYPE ,L_DATE_OF_BIRTH ,L_TOWN_OF_BIRTH ,L_COUNTRY_OF_BIRTH ,
377 L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE ,L_EFFECTIVE_START_DATE ,L_SEX ,L_MARITAL_STATUS ,
378 L_FULL_NAME ,L_PRE_NAME_ADJUNCT ,L_SUFFIX ,L_TITLE ,L_LAST_NAME ,L_FIRST_NAME ,L_MIDDLE_NAMES ,
379 L_EMAIL_ADDRESS ,
380 L_PHONE_TYPE ,L_PHONE_NUMBER ,L_NATIONALITY ,L_NATIONAL_IDENTIFIER ,L_CONTACT_NAME ,
381 L_RELATIONSHIP ,l_EMP_JOB_NAME ,L_EMP_GRADE_NAME ,
382 L_POSITION_NAME ,L_EMPLOYMENT_CATEGORY ,L_NORMAL_HOURS ,L_ASG_LOCATION ,
383 L_ASSIGNMENT_STATUS ,L_DEPARTMENT_NAME ,L_BUSINESS_GROUP_NAME ,L_PAYROLL_NAME,
384 L_SUPERVISOR_NAME,l_con_personid,l_con_bgid;
385
386 CLOSE CSR_PERSON_FULL_VIEW;
387
388 select hrhd_delta_sync_seq.nextval into L_SEQNUM from dual;
389
390 L_CURRENT_DATE :=SYSDATE;
391 INSERT INTO HR_360_PERSON_VIEW (
392 EMPLOYEE_NUMBER ,
393 USER_PERSON_TYPE ,
394 DATE_OF_BIRTH ,
395 TOWN_OF_BIRTH ,
396 COUNTRY_OF_BIRTH ,
397 DATE_OF_DEATH ,
398 ORIGINAL_DATE_OF_HIRE ,
399 EFFECTIVE_START_DATE ,
400 SEX ,
401 MARITAL_STATUS ,
402 FULL_NAME ,
403 PRE_NAME_ADJUNCT ,
404 SUFFIX,
405 TITLE ,
406 LAST_NAME ,
407 FIRST_NAME ,
408 MIDDLE_NAMES,
409 EMAIL_ADDRESS,
410 PHONE_TYPE ,
411 PHONE_NUMBER ,
412 NATIONALITY ,
413 NATIONAL_IDENTIFIER ,
414 CONTACT_NAME ,
415 RELATIONSHIP ,
416 EMP_JOB_NAME ,
417 EMP_GRADE_NAME ,
418 POSITION_NAME ,
419 EMPLOYMENT_CATEGORY ,
420 NORMAL_HOURS ,
421 ASG_LOCATION ,
422 ASSIGNMENT_STATUS ,
423 DEPARTMENT_NAME ,
424 BUSINESS_GROUP_NAME ,
425 PAYROLL_NAME ,
426 SUPERVISOR_NAME ,
427 RECSEQ,STATUS,PER_STATUS_DATE,
428 TRANSACTION_ID) VALUES
429 (
430 L_EMPLOYEE_NUMBER ,L_USER_PERSON_TYPE ,L_DATE_OF_BIRTH ,L_TOWN_OF_BIRTH ,L_COUNTRY_OF_BIRTH ,
431 L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE ,L_EFFECTIVE_START_DATE ,L_SEX ,L_MARITAL_STATUS ,
432 L_FULL_NAME ,L_PRE_NAME_ADJUNCT ,L_SUFFIX ,L_TITLE ,L_LAST_NAME ,L_FIRST_NAME ,L_MIDDLE_NAMES ,
433 L_EMAIL_ADDRESS ,L_PHONE_TYPE ,L_PHONE_NUMBER ,L_NATIONALITY ,L_NATIONAL_IDENTIFIER ,
437 L_SUPERVISOR_NAME,L_EMPLOYEE_NUMBER||'-'||L_SEQNUM,'QUEUED',L_CURRENT_DATE,P_TRXN_ID);
434 L_CONTACT_NAME ,l_RELATIONSHIP ,l_EMP_JOB_NAME ,L_EMP_GRADE_NAME ,
435 L_POSITION_NAME ,L_EMPLOYMENT_CATEGORY ,L_NORMAL_HOURS ,L_ASG_LOCATION ,
436 L_ASSIGNMENT_STATUS ,L_DEPARTMENT_NAME ,L_BUSINESS_GROUP_NAME ,L_PAYROLL_NAME,
438
439 commit;
440
441
442
443
444 commit;
445
446 OPEN csr_check_person_add ;
447 FETCH csr_check_person_add INTO L_STYLE,L_ADDRESS_ID;
448 CLOSE csr_check_person_add;
449
450 IF l_style IS NOT NULL and L_ADDRESS_ID is not null THEN
451
452 OPEN CSR_GET_LEG_SPEC_COLS(l_style );
453 FETCH CSR_GET_LEG_SPEC_COLS BULK COLLECT INTO G_ADD_ATTR ;
454 CLOSE CSR_GET_LEG_SPEC_COLS;
455
456 FOR I IN G_ADD_ATTR.FIRST .. G_ADD_ATTR.LAST
457 LOOP
458
459 L_SQL:= ' begin
460 SELECT ' ||G_ADD_ATTR(I).APPLICATION_COLUMN_NAME ||','||''''||G_ADD_ATTR(I).end_user_column_name||''''||
461 ' INTO :1 ,:2 FROM PER_ADDRESSES WHERE ADDRESS_ID = '||L_ADDRESS_ID ||';
462 end;';
463
464 EXECUTE IMMEDIATE l_sql using in out L_APP_COL_VALUE,IN OUT l_end_user_col_name1 ;
465
466
467 L_SQL1:='UPDATE HR_360_PERSON_VIEW
468 SET PER_ADD_LABLE'||J||'='||''''||L_APP_COL_VALUE||''''||',PER_ADD_LABLE'||K||
469 '='||''''||l_end_user_col_name1||''''||'WHERE RECSEQ='||''''||L_EMPLOYEE_NUMBER||'-'||L_SEQNUM||'''';
470
471
472
473 EXECUTE IMMEDIATE l_sql1;
474
475
476 J:=J+1;
477 K:=K+1;
478
479 end LOOP;
480
481 -- inserting contact address data
482
483
484 END IF;
485
486
487
488 update HR_360_PERSON_VIEW
489 set address_STYLE=l_style
490 where RECSEQ=L_EMPLOYEE_NUMBER||'-'||L_SEQNUM ;
491
492
493
494 L_STYLE :=NULL;
495 K:=1;
496 J:=24;
497
498 OPEN CSR_GET_CONTACT_ADD (l_con_personid ,l_CON_BGID);
499 FETCH CSR_GET_CONTACT_ADD INTO L_STYLE,L_ADDRESS_ID;
500 CLOSE CSR_GET_CONTACT_ADD;
501
502
503 IF l_style IS NOT NULL and L_ADDRESS_ID is not null THEN
504
505 OPEN CSR_GET_LEG_SPEC_COLS(l_style );
506 FETCH CSR_GET_LEG_SPEC_COLS BULK COLLECT INTO G_ADD_ATTR ;
507 CLOSE CSR_GET_LEG_SPEC_COLS;
508
509
510 FOR I IN G_ADD_ATTR.FIRST .. G_ADD_ATTR.LAST
511 LOOP
512
513 L_SQL:= ' begin
514 SELECT ' ||G_ADD_ATTR(I).APPLICATION_COLUMN_NAME ||','||''''||G_ADD_ATTR(I).end_user_column_name||''''||
515 ' INTO :1 ,:2 FROM PER_ADDRESSES WHERE ADDRESS_ID = '||L_ADDRESS_ID ||';
516 end;';
517
518 -- EXECUTE IMMEDIATE l_sql using in out L_APP_COL_VALUE,IN OUT l_end_user_col_name1 ;
519
520
521 L_SQL1:='UPDATE HR_360_PERSON_VIEW
522 SET CON_ADD_LABLE'||J||'='||''''||L_APP_COL_VALUE||''''||',CON_ADD_LABLE'||K||
523 '='||''''||l_end_user_col_name1||''''||'WHERE RECSEQ='||''''||L_EMPLOYEE_NUMBER||'-'||L_SEQNUM||'''' ;
524
525
526
527
528
529
530 J:=J+1;
531 K:=K+1;
532
533
534 end LOOP;
535 END IF;
536
537 -- END OF INSERTING CONTACTS ADDRESS DATA.
538
539 open CSR_REPORTEES_DATA;
540 loop
541 fetch CSR_REPORTEES_DATA into L_REP_NAME ,L_REP_EMPLOYEE_NUM ,L_REP_ASG_STATUS;
542 exit when CSR_REPORTEES_DATA%notfound;
543
544
545 insert into hr_360_per_reportee(
546 SUPERVISOR_ID,
547 REPORTEE_NAME,
548 REPORTEE_EMP_NUMBER,
549 REPORTEE_ASG_STATUS,
550 event_key)
551 values(L_EMPLOYEE_NUMBER ,L_REP_NAME,L_REP_EMPLOYEE_NUM,L_REP_ASG_STATUS,
552 L_EMPLOYEE_NUMBER||'-'||L_SEQNUM);
553
554 end loop;
555 close CSR_REPORTEES_DATA;
556
557 commit;
558
559
560
561 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
562 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'PERVW',l_params);
563 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_tp_site_id_act), l_params);
564 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', L_EMPLOYEE_NUMBER||'-'||to_char(L_SEQNUM), l_params);
565 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
566 p_event_key => L_EMPLOYEE_NUMBER||'-'||to_char(L_SEQNUM),
567 p_parameters => l_params);
568
569
570
571 COMMIT;
572
573 END;
574
575 procedure hr_wflow_360
576 (itemtype in varchar2,
577 itemkey in varchar2,
578 actid in number,
579 funcmode in varchar2,
580 resultout in out NOCOPY varchar2)
581 is
582 l_params WF_PARAMETER_LIST_T;
583 L_SEQNUM number;
584 p_person_id varchar2(30);
585 p_bg_id varchar2(30);
586 p_eff_dt varchar2(40);
587 p_tp_id varchar2(30);
588 p_eff_date date;
589 p_txn_id varchar2(200);
590 begin
591 p_person_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PARAMETER1');
592 p_bg_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PARAMETER2');
593 p_eff_dt := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PARAMETER3');
594 p_tp_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PARAMETER4');
595 p_txn_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PARAMETER5');
596 p_eff_date := nvl(to_date(p_eff_dt,'DD/MM/YYYY'),sysdate);
597
598 HR_360_MESSAGE.HR_360_PERSON_VIEW(P_EMPLOYEE_NUMBER => p_person_id,
599 P_EFFECTIVE_DATE => p_eff_date,
600 P_BUSINESS_GROUP_ID => to_number(p_bg_id),
601 p_tp_site_id => p_tp_id,
602 p_trxn_id => p_txn_id);
603
604 resultout := 'COMPLETE';
605 exception
609 end;
606 when OTHERS then
607 resultout := 'FAILED';
608
610
611 end HR_360_MESSAGE;