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