[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_ASSGNMNT_M_C
Source
1 Package Body EDW_HR_ASSGNMNT_M_C AS
2 /* $Header: hriepasg.pkb 120.1 2005/06/07 05:47:38 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_ASSGNMNT_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_ASSGNMNT_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_ASSGNMNT_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_ASSGNMNT_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 EDW_HR_ASSGNMNT_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_ASSGNMNT_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
46 to_char(EDW_HR_ASSGNMNT_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_ASGN_ASSGNMNT_LSTG(EDW_HR_ASSGNMNT_M_C.g_push_date_range1, EDW_HR_ASSGNMNT_M_C.g_push_date_range2);
60
61
62 l_duration := sysdate - l_temp_date;
63
64 edw_log.put_line('Total rows inserted : '||g_row_count);
65 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
66 edw_log.put_line(' ');
67 -- ---------------------------------------------------------------------------
68 -- END OF Collection , Developer Customizable Section
69 -- ---------------------------------------------------------------------------
70 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2 );
71 commit;
72
73 Exception When others then
74 Errbuf:=sqlerrm;
75 Retcode:=sqlcode;
76 l_exception_msg := Retcode || ':' || Errbuf;
77 EDW_HR_ASSGNMNT_M_C.g_exception_msg := l_exception_msg;
78 rollback;
79 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_HR_ASSGNMNT_M_C.g_exception_msg, g_push_date_range1, g_push_date_range2);
80
81 commit;
82 End;
83
84
85 Procedure Push_EDW_HR_ASGN_ASSGNMNT_LSTG(p_from_date IN date, p_to_date IN DATE) IS
86 l_date1 DATE;
87 l_date2 DATE;
88 l_rows_inserted NUMBER :=0;
89 BEGIN
90 edw_log.put_line('Starting Push_EDW_HR_ASGN_ASSGNMNT_LSTG');
91 l_date1 := p_from_date;
92 l_date2 := p_to_date;
93 Insert Into
94 EDW_HR_ASGN_ASSGNMNT_LSTG@EDW_APPS_TO_WH(
95 PRIMARY_FLAG,
96 ASSIGNMENT_ID,
97 EFFECTIVE_START_DATE,
98 TITLE,
99 NORMAL_HOURS_FREQUENCY,
100 NORMAL_HOURS,
101 ASSIGNMENT_TYPE,
102 MANAGER_FLAG,
103 TIME_NORMAL_START,
104 PROBATION_PERIOD_UNIT,
105 PROBATION_PERIOD,
106 PERFORMANCE_REVIEW_PERIOD,
107 HOURLY_SALARIED_FLAG,
108 LAST_UPDATE_DATE,
109 CREATION_DATE,
110 USER_ATTRIBUTE1,
111 USER_ATTRIBUTE2,
112 USER_ATTRIBUTE3,
113 USER_ATTRIBUTE4,
114 ASSIGNMENT_PK,
115 ALL_FK,
116 NAME,
117 ASSIGNMENT_DP,
118 BUSINESS_GROUP,
119 ASSIGNMENT_NUMBER,
120 START_DATE,
121 END_DATE,
122 USER_ATTRIBUTE5,
123 ASSIGNMENT_STATUS_TYPE_ID,
124 BUSINESS_GROUP_ID,
125 PEOPLE_GROUP_ID,
126 EFFECTIVE_END_DATE,
127 ASSIGNMENT_STATUS,
128 TIME_NORMAL_END,
129 PERF_REVIEW_PERIOD_FRQNCY,
130 SLRY_REVIEW_PERIOD,
131 SLRY_REVIEW_PERIOD_FRQNCY,
132 INSTANCE,
133 OPERATION_CODE,
134 COLLECTION_STATUS)
135 select PRIMARY_FLAG,
136 ASSIGNMENT_ID,
137 EFFECTIVE_START_DATE,
138 TITLE,
139 NORMAL_HOURS_FREQUENCY,
140 NORMAL_HOURS,
141 ASSIGNMENT_TYPE,
142 MANAGER_FLAG,
143 TIME_NORMAL_START,
144 PROBATION_PERIOD_UNIT,
145 PROBATION_PERIOD,
146 PERFORMANCE_REVIEW_PERIOD,
147 HOURLY_SALARIED_FLAG,
148 LAST_UPDATE_DATE,
149 CREATION_DATE,
150 USER_ATTRIBUTE1,
151 USER_ATTRIBUTE2,
152 USER_ATTRIBUTE3,
153 USER_ATTRIBUTE4,
154 ASSIGNMENT_PK,
155 NVL(ALL_FK, 'NA_EDW'),
156 NAME,
157 ASSIGNMENT_DP,
158 BUSINESS_GROUP,
159 ASSIGNMENT_NUMBER,
160 START_DATE,
161 END_DATE,
162 USER_ATTRIBUTE5,
163 ASSIGNMENT_STATUS_TYPE_ID,
164 BUSINESS_GROUP_ID,
165 PEOPLE_GROUP_ID,
166 EFFECTIVE_END_DATE,
167 ASSIGNMENT_STATUS,
168 TIME_NORMAL_END,
169 PERF_REVIEW_PERIOD_FRQNCY,
170 SLRY_REVIEW_PERIOD,
171 SLRY_REVIEW_PERIOD_FRQNCY,
172 INSTANCE,
173 NULL, -- OPERATION_CODE
174 'READY'
175 from EDW_HR_ASGN_ASSGNMNT_LCV@APPS_TO_APPS
176 where last_update_date between l_date1 and l_date2;
177
178
179 l_rows_inserted := sql%rowcount;
180 EDW_HR_ASSGNMNT_M_C.g_row_count := EDW_HR_ASSGNMNT_M_C.g_row_count + l_rows_inserted ;
181 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
182 ' rows into the EDW_HR_ASGN_ASSGNMNT_LSTG staging table');
183 edw_log.put_line('Commiting records for EDW_HR_ASGN_ASSGNMNT_LSTG');
184 commit;
185
186 edw_log.put_line('Completed Push_EDW_HR_ASGN_ASSGNMNT_LSTG');
187 Exception When others then
188 raise;
189 commit;
190 END;
191 End EDW_HR_ASSGNMNT_M_C;