[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_RQN_VCNCY_M_C
Source
1 Package Body EDW_HR_RQN_VCNCY_M_C AS
2 /* $Header: hriepvac.pkb 120.1 2005/06/07 06:04:28 anmajumd 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_RQN_VCNCY_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_RQN_VCNCY_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_RQN_VCNCY_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_RQN_VCNCY_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 EDW_HR_RQN_VCNCY_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_RQN_VCNCY_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
46 to_char(EDW_HR_RQN_VCNCY_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('Pushing data');
55
56 l_temp_date := sysdate;
57
58
59 Push_EDW_HR_RQVC_VACANCY_LSTG(EDW_HR_RQN_VCNCY_M_C.g_push_date_range1, EDW_HR_RQN_VCNCY_M_C.g_push_date_range2);
60 Push_EDW_HR_RQVC_REQUISTN_LSTG(EDW_HR_RQN_VCNCY_M_C.g_push_date_range1, EDW_HR_RQN_VCNCY_M_C.g_push_date_range2);
61
62
63 l_duration := sysdate - l_temp_date;
64
65 edw_log.put_line('Total rows inserted : '||g_row_count);
66 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
67 edw_log.put_line(' ');
68 -- ---------------------------------------------------------------------------
69 -- END OF Collection , Developer Customizable Section
70 -- ---------------------------------------------------------------------------
71 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2 );
72 commit;
73
74 Exception When others then
75 Errbuf:=sqlerrm;
76 Retcode:=sqlcode;
77 l_exception_msg := Retcode || ':' || Errbuf;
78 EDW_HR_RQN_VCNCY_M_C.g_exception_msg := l_exception_msg;
79 rollback;
80 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_HR_RQN_VCNCY_M_C.g_exception_msg, g_push_date_range1, g_push_date_range2);
81
82 commit;
83 End;
84
85
86 Procedure Push_EDW_HR_RQVC_VACANCY_LSTG(p_from_date IN date, p_to_date IN DATE) IS
87 l_date1 DATE;
88 l_date2 DATE;
89 l_rows_inserted NUMBER :=0;
90 BEGIN
91 edw_log.put_line('Starting Push_EDW_HR_RQVC_VACANCY_LSTG');
92 l_date1 := p_from_date;
93 l_date2 := p_to_date;
94 Insert Into
95 EDW_HR_RQVC_VACANCY_LSTG@EDW_APPS_TO_WH(
96 VACANCY_PK,
97 REQUISITION_FK,
98 INSTANCE,
99 VACANCY_STATUS_CODE,
100 VACANCY_STATUS,
101 NUMBER_OF_OPENINGS,
102 NAME,
103 VACANCY_DP,
104 BUSINESS_GROUP_ID,
105 REQUISITION_ID,
106 VACANCY_ID,
107 VACANCY_START_DATE,
108 VACANCY_END_DATE,
109 LAST_UPDATE_DATE,
110 CREATION_DATE,
111 USER_ATTRIBUTE1,
112 USER_ATTRIBUTE2,
113 USER_ATTRIBUTE3,
114 USER_ATTRIBUTE4,
115 USER_ATTRIBUTE5,
116 BUDGET_MSRMNT_TYPE,
117 BUDGET_MSRMNT_TYPE_CODE,
118 BUDGET_MSRMNT_VALUE,
119 OPERATION_CODE,
120 COLLECTION_STATUS)
121 select VACANCY_PK,
122 NVL(REQUISITION_FK, 'NA_EDW'),
123 INSTANCE,
124 VACANCY_STATUS_CODE,
125 VACANCY_STATUS,
126 NUMBER_OF_OPENINGS,
127 NAME,
128 VACANCY_DP,
129 BUSINESS_GROUP_ID,
130 REQUISITION_ID,
131 VACANCY_ID,
132 VACANCY_START_DATE,
133 VACANCY_END_DATE,
134 LAST_UPDATE_DATE,
135 CREATION_DATE,
136 USER_ATTRIBUTE1,
137 USER_ATTRIBUTE2,
138 USER_ATTRIBUTE3,
139 USER_ATTRIBUTE4,
140 USER_ATTRIBUTE5,
141 BUDGET_MSRMNT_TYPE,
142 BUDGET_MSRMNT_TYPE_CODE,
143 BUDGET_MSRMNT_VALUE,
144 NULL, -- OPERATION_CODE
145 'READY'
146 from EDW_HR_RQVC_VACANCY_LCV@APPS_TO_APPS
147 where last_update_date between l_date1 and l_date2;
148
149
150 l_rows_inserted := sql%rowcount;
151 EDW_HR_RQN_VCNCY_M_C.g_row_count := EDW_HR_RQN_VCNCY_M_C.g_row_count + l_rows_inserted ;
152 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
153 ' rows into the EDW_HR_RQVC_VACANCY_LSTG staging table');
154 edw_log.put_line('Commiting records for EDW_HR_RQVC_VACANCY_LSTG');
155 commit;
156
157 edw_log.put_line('Completed Push_EDW_HR_RQVC_VACANCY_LSTG');
158 Exception When others then
159 raise;
160 commit;
161 END;
162
163
164 Procedure Push_EDW_HR_RQVC_REQUISTN_LSTG(p_from_date IN date, p_to_date IN DATE) IS
165 l_date1 DATE;
166 l_date2 DATE;
167 l_rows_inserted NUMBER :=0;
168 BEGIN
169 edw_log.put_line('Starting Push_EDW_HR_RQVC_REQUISTN_LSTG');
170 l_date1 := p_from_date;
171 l_date2 := p_to_date;
172 Insert Into
173 EDW_HR_RQVC_REQUISTN_LSTG@EDW_APPS_TO_WH(
174 REQUISITION_PK,
175 ALL_FK,
176 INSTANCE,
177 NAME,
178 REQUISITION_DP,
179 BUSINESS_GROUP_ID,
180 REQUISITION_ID,
181 START_DATE,
182 END_DATE,
183 LAST_UPDATE_DATE,
184 CREATION_DATE,
185 USER_ATTRIBUTE1,
186 USER_ATTRIBUTE2,
187 USER_ATTRIBUTE3,
188 USER_ATTRIBUTE4,
189 USER_ATTRIBUTE5,
190 OPERATION_CODE,
191 COLLECTION_STATUS)
192 select REQUISITION_PK,
193 NVL(ALL_FK, 'NA_EDW'),
194 INSTANCE,
195 NAME,
196 REQUISITION_DP,
197 BUSINESS_GROUP_ID,
198 REQUISITION_ID,
199 START_DATE,
200 END_DATE,
201 LAST_UPDATE_DATE,
202 CREATION_DATE,
203 USER_ATTRIBUTE1,
204 USER_ATTRIBUTE2,
205 USER_ATTRIBUTE3,
206 USER_ATTRIBUTE4,
207 USER_ATTRIBUTE5,
208 NULL, -- OPERATION_CODE
209 'READY'
210 from EDW_HR_RQVC_REQUISTN_LCV@APPS_TO_APPS
211 where last_update_date between l_date1 and l_date2;
212
213
214 l_rows_inserted := sql%rowcount;
215 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
216 ' rows into the EDW_HR_RQVC_REQUISTN_LSTG staging table');
217 edw_log.put_line('Commiting records for EDW_HR_RQVC_REQUISTN_LSTG');
218 commit;
219
220 edw_log.put_line('Completed Push_EDW_HR_RQVC_REQUISTN_LSTG');
221 Exception When others then
222 raise;
223 commit;
224 END;
225 End EDW_HR_RQN_VCNCY_M_C;