[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_PRSN_TYP_M_C
Source
1 Package Body EDW_HR_PRSN_TYP_M_C AS
2 /* $Header: hrieppty.pkb 120.2 2006/04/06 02:23:23 jtitmas noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7
8
9 Procedure Push(Errbuf in out NOCOPY Varchar2,
10 Retcode in out NOCOPY Varchar2,
11 p_from_date IN VARCHAR2,
12 p_to_date IN VARCHAR2) IS
13 l_dimension_name Varchar2(30) :='EDW_HR_PRSN_TYP_M' ;
14 l_temp_date Date:=Null;
15 l_rows_inserted Number:=0;
16 l_duration Number:=0;
17 l_exception_msg Varchar2(2000):=Null;
18
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 Begin
23 Errbuf :=NULL;
24 Retcode:=0;
25 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
26 errbuf := fnd_message.get;
27 Return;
28 END IF;
29
30 IF (p_from_date IS NULL) THEN
31 EDW_HR_PRSN_TYP_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
32 EDW_COLLECTION_UTIL.g_offset;
33 ELSE
34 EDW_HR_PRSN_TYP_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
35 END IF;
36
37 IF (p_to_date IS NULL) THEN
38 EDW_HR_PRSN_TYP_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 EDW_HR_PRSN_TYP_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
41 END IF;
42
43
44 edw_log.put_line( 'The collection range is from '||
45 to_char(EDW_HR_PRSN_TYP_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
46 to_char(EDW_HR_PRSN_TYP_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
47 edw_log.put_line(' ');
48
49 -- -----------------------------------------------------------------------------
50 -- Start of Collection , Developer Customizable Section
51 -- -----------------------------------------------------------------------------
52
53 edw_log.put_line(' ');
54 edw_log.put_line('Populating HRI Person Type table');
55 HRI_EDW_DIM_PERSON_TYPE.populate_person_types;
56 edw_log.put_line('Finished populating table');
57
58 edw_log.put_line(' ');
59 edw_log.put_line('Pushing data');
60
61 l_temp_date := sysdate;
62
63
64 Push_EDW_HR_PTYP_PRSN_TYP_LSTG(EDW_HR_PRSN_TYP_M_C.g_push_date_range1, EDW_HR_PRSN_TYP_M_C.g_push_date_range2);
65
66
67 l_duration := sysdate - l_temp_date;
68
69 edw_log.put_line('Total rows inserted : '||g_row_count);
70 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
71 edw_log.put_line(' ');
72 -- ---------------------------------------------------------------------------
73 -- END OF Collection , Developer Customizable Section
74 -- ---------------------------------------------------------------------------
75 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2 );
76 commit;
77
78 Exception When others then
79 Errbuf:=sqlerrm;
80 Retcode:=sqlcode;
81 l_exception_msg := Retcode || ':' || Errbuf;
82 EDW_HR_PRSN_TYP_M_C.g_exception_msg := l_exception_msg;
83 rollback;
84 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_HR_PRSN_TYP_M_C.g_exception_msg, g_push_date_range1, g_push_date_range2);
85
86 commit;
87 End;
88
89
90 Procedure Push_EDW_HR_PTYP_PRSN_TYP_LSTG(p_from_date IN date, p_to_date IN DATE) IS
91 l_date1 DATE;
92 l_date2 DATE;
93 l_rows_inserted NUMBER :=0;
94 BEGIN
95 edw_log.put_line('Starting Push_EDW_HR_PTYP_PRSN_TYP_LSTG');
96 l_date1 := p_from_date;
97 l_date2 := p_to_date;
98
99 -- Set collect to date to sysdate below so that changes to the collected
100 -- table are picked up (changes to collected table will be timestamped
101 -- later than collect to date which is defaulted to sysdate at process
102 -- submit time)
103 Insert Into
104 EDW_HR_PTYP_PRSN_TYP_LSTG@EDW_APPS_TO_WH(
105 PERSON_TYPE_PK,
106 ALL_FK,
107 NAME,
108 PERSON_TYPE_DP,
109 INSTANCE,
110 POTENTIAL_WORKER_FLAG,
111 PW_EMPLOYEE_TYPE,
112 EMP_DIRECT_HIRE_FLAG,
113 DH_PERMANENT_TYPE,
114 DH_INTERN_TYPE,
115 DH_FXD_TRM_LOWER_TYPE,
116 DH_FXD_TRM_UPPER_TYPE,
117 DH_EMP_STUDENT_FLAG,
118 EMP_CNTNGNT_WRKFRC_FLAG,
119 CW_AGNCY_CNTRCTR_TYPE,
120 CW_SELF_EMPLOYED_TYPE,
121 CW_CONSULTANT_TYPE,
122 PW_EX_EMPLOYEE_TYPE,
123 PW_APPLICANT_TYPE,
124 NON_WORKER_FLAG,
125 NW_CONTACT_FLAG,
126 NW_DEPENDENT_TYPE,
127 NW_RETIREE_TYPE,
128 NW_BENEFICIARY_TYPE,
129 SRVIVING_FMLY_MBR_TYPE,
130 SRVIVING_SPOUSE_TYPE,
131 EX_APPLICANT_TYPE,
132 OTHER_TYPE,
133 PARTICIPANT_TYPE,
134 FORMER_SPOUSE_TYPE,
135 FORMER_FMLY_MBR_TYPE,
136 LAST_UPDATE_DATE,
137 CREATION_DATE,
138 USER_ATTRIBUTE1,
139 USER_ATTRIBUTE2,
140 USER_ATTRIBUTE3,
141 USER_ATTRIBUTE4,
142 USER_ATTRIBUTE5,
143 PW_EMPLOYEE_FLAG,
144 DH_PERMANENT_FLAG,
145 DH_INTERN_FLAG,
146 DH_FXD_TRM_LOWER_FLAG,
147 DH_FXD_TRM_UPPER_FLAG,
148 CW_AGNCY_CNTRCTR_FLAG,
149 CW_SELF_EMPLOYED_FLAG,
150 CW_CONSULTANT_FLAG,
151 PW_EX_EMPLOYEE_FLAG,
152 PW_APPLICANT_FLAG,
153 NW_DEPENDENT_FLAG,
154 NW_RETIREE_FLAG,
155 NW_BENEFICIARY_FLAG,
156 SRVIVING_FMLY_MBR_FLAG,
157 SRVIVING_SPOUSE_FLAG,
158 EX_APPLICANT_FLAG,
159 OTHER_FLAG,
160 PARTICIPANT_FLAG,
161 FORMER_SPOUSE_FLAG,
162 FORMER_FMLY_MBR_FLAG,
163 OPERATION_CODE,
164 COLLECTION_STATUS)
165 select PERSON_TYPE_PK,
166 NVL(ALL_FK, 'NA_EDW'),
167 NAME,
168 PERSON_TYPE_DP,
169 INSTANCE,
170 POTENTIAL_WORKER_FLAG,
171 PW_EMPLOYEE_TYPE,
172 EMP_DIRECT_HIRE_FLAG,
173 DH_PERMANENT_TYPE,
174 DH_INTERN_TYPE,
175 DH_FXD_TRM_LOWER_TYPE,
176 DH_FXD_TRM_UPPER_TYPE,
177 DH_EMP_STUDENT_FLAG,
178 EMP_CNTNGNT_WRKFRC_FLAG,
179 CW_AGNCY_CNTRCTR_TYPE,
180 CW_SELF_EMPLOYED_TYPE,
181 CW_CONSULTANT_TYPE,
182 PW_EX_EMPLOYEE_TYPE,
183 PW_APPLICANT_TYPE,
184 NON_WORKER_FLAG,
185 NW_CONTACT_FLAG,
186 NW_DEPENDENT_TYPE,
187 NW_RETIREE_TYPE,
188 NW_BENEFICIARY_TYPE,
189 SRVIVING_FMLY_MBR_TYPE,
190 SRVIVING_SPOUSE_TYPE,
191 EX_APPLICANT_TYPE,
192 OTHER_TYPE,
193 PARTICIPANT_TYPE,
194 FORMER_SPOUSE_TYPE,
195 FORMER_FMLY_MBR_TYPE,
196 LAST_UPDATE_DATE,
197 CREATION_DATE,
198 USER_ATTRIBUTE1,
199 USER_ATTRIBUTE2,
200 USER_ATTRIBUTE3,
201 USER_ATTRIBUTE4,
202 USER_ATTRIBUTE5,
203 PW_EMPLOYEE_FLAG,
204 DH_PERMANENT_FLAG,
205 DH_INTERN_FLAG,
206 DH_FXD_TRM_LOWER_FLAG,
207 DH_FXD_TRM_UPPER_FLAG,
208 CW_AGNCY_CNTRCTR_FLAG,
209 CW_SELF_EMPLOYED_FLAG,
210 CW_CONSULTANT_FLAG,
211 PW_EX_EMPLOYEE_FLAG,
212 PW_APPLICANT_FLAG,
213 NW_DEPENDENT_FLAG,
214 NW_RETIREE_FLAG,
215 NW_BENEFICIARY_FLAG,
216 SRVIVING_FMLY_MBR_FLAG,
217 SRVIVING_SPOUSE_FLAG,
218 EX_APPLICANT_FLAG,
219 OTHER_FLAG,
220 PARTICIPANT_FLAG,
221 FORMER_SPOUSE_FLAG,
222 FORMER_FMLY_MBR_FLAG,
223 NULL, -- OPERATION_CODE
224 'READY'
225 from EDW_HR_PTYP_PRSN_TYP_LCV@APPS_TO_APPS
226 where last_update_date between l_date1 and sysdate;
227
228
229 l_rows_inserted := sql%rowcount;
230 EDW_HR_PRSN_TYP_M_C.g_row_count := EDW_HR_PRSN_TYP_M_C.g_row_count + l_rows_inserted ;
231 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
232 ' rows into the EDW_HR_PTYP_PRSN_TYP_LSTG staging table');
233 edw_log.put_line('Commiting records for EDW_HR_PTYP_PRSN_TYP_LSTG');
234 commit;
235
236 edw_log.put_line('Completed Push_EDW_HR_PTYP_PRSN_TYP_LSTG');
237 Exception When others then
238 raise;
239 commit;
240 END;
241 End EDW_HR_PRSN_TYP_M_C;