[Home] [Help]
PACKAGE BODY: APPS.FII_GL_BOOK_M_C
Source
1 Package Body FII_GL_BOOK_M_C AS
2 /* $Header: FIICMBKB.pls 120.2 2002/11/20 20:01:56 djanaswa ship $ */
3
4 G_PUSH_DATE_RANGE1 Date :=Null;
5 G_PUSH_DATE_RANGE2 Date :=Null;
6 g_row_count Number :=0;
7 g_exception_msg varchar2(2000) :=Null;
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
14 l_dimension_name Varchar2(30) :='EDW_GL_BOOK_M' ;
15 l_temp_date Date :=Null;
16 l_rows_inserted Number :=0;
17 l_duration Number :=0;
18 l_exception_msg Varchar2(2000) :=Null;
19 l_from_date Date :=Null;
20 l_to_date Date :=Null;
21
22 -- -------------------------------------------
23 -- Put any additional developer variables here
24 -- -------------------------------------------
25 Begin
26 Errbuf :=NULL;
27 Retcode:=0;
28
29 edw_log.put_line( 'p_from_date = ' || p_from_date );
30 edw_log.put_line( 'p_to_date = ' || p_to_date );
31
32 edw_log.put_line( 'About to set l_from_date' );
33 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
34
35 edw_log.put_line( 'About to set l_to_date' );
36 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
37
38 edw_log.put_line( 'Both date variables are set' );
39
40 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
41 errbuf := fnd_message.get;
42 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
43 END IF;
44
45 FII_GL_BOOK_M_C.g_push_date_range1 := nvl(l_from_date,
46 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
47 FII_GL_BOOK_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
48 edw_log.put_line( 'The collection range is from '||
49 to_char(FII_GL_BOOK_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
50 to_char(FII_GL_BOOK_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
51 edw_log.put_line(' ');
52
53 -- -----------------------------------------------------------------------------
54 -- Start of Collection , Developer Customizable Section
55 -- -----------------------------------------------------------------------------
56
57 edw_log.put_line(' ');
58 edw_log.put_line('Pushing data');
59
60 l_temp_date := sysdate;
61
62
63 Push_EDW_GL_BOOK_FA_BOOK_LSTG(FII_GL_BOOK_M_C.g_push_date_range1, FII_GL_BOOK_M_C.g_push_date_range2);
64 Push_EDW_GL_BOOK_BOOK_LSTG(FII_GL_BOOK_M_C.g_push_date_range1, FII_GL_BOOK_M_C.g_push_date_range2);
65
66
67 l_duration := sysdate - l_temp_date;
68
69 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
70 edw_log.put_line(' ');
71 -- ---------------------------------------------------------------------------
72 -- END OF Collection , Developer Customizable Section
73 -- ---------------------------------------------------------------------------
74 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, g_push_date_range2);
75 commit;
76
77 Exception When others then
78 Errbuf:=sqlerrm;
79 Retcode:=sqlcode;
80 l_exception_msg := Retcode || ':' || Errbuf;
81 FII_GL_BOOK_M_C.g_exception_msg := l_exception_msg;
82 rollback;
83 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, FII_GL_BOOK_M_C.g_exception_msg,g_push_date_range1, g_push_date_range2);
84
85 commit;
86 End;
87
88
89 Procedure Push_EDW_GL_BOOK_FA_BOOK_LSTG(p_from_date IN date, p_to_date IN DATE) IS
90 l_date1 DATE;
91 l_date2 DATE;
92 l_rows_inserted NUMBER :=0;
93
94 BEGIN
95 edw_log.put_line('Starting Push_EDW_GL_BOOK_FA_BOOK_LSTG');
96 l_date1 := p_from_date;
97 l_date2 := p_to_date;
98
99 Insert Into EDW_GL_BOOK_FA_BOOK_LSTG(
100 BOOK_FK,
101 BOOK_TYPE_NAME,
102 CURRENCY_CODE,
103 DELETION_DATE,
104 DEPRE_CALANDAR,
105 FA_BOOK,
106 FA_BOOK_PK,
107 INSTANCE,
108 NAME,
109 PRORATE_CALENDAR,
110 USER_ATTRIBUTE1,
111 USER_ATTRIBUTE2,
112 USER_ATTRIBUTE3,
113 USER_ATTRIBUTE4,
114 USER_ATTRIBUTE5,
115 OPERATION_CODE,
116 COLLECTION_STATUS,
117 CREATION_DATE,
118 LAST_UPDATE_DATE
119 )
120 select NVL(BOOK_FK, 'NA_EDW'),
121 BOOK_TYPE_NAME,
122 CURRENCY_CODE,
123 to_date(NULL), --DELETION_DATE,
124 DEPRE_CALANDAR,
125 FA_BOOK,
126 FA_BOOK_PK,
127 INSTANCE,
128 NAME,
129 PRORATE_CALENDAR,
130 NULL, --USER_ATTRIBUTE1,
131 NULL, --USER_ATTRIBUTE2,
132 NULL, --USER_ATTRIBUTE3,
133 NULL, --USER_ATTRIBUTE4,
134 NULL, --USER_ATTRIBUTE5,
135 NULL, -- OPERATION_CODE
136 'READY',
137 trunc(sysdate),
138 trunc(sysdate)
139 from FII_GL_BOOK_FA_BOOK_LCV
140 where last_update_date between l_date1 and l_date2;
141
142 l_rows_inserted := sql%rowcount;
143 g_row_count := g_row_count + l_rows_inserted ;
144 edw_log.put_line('Commiting records for EDW_GL_BOOK_FA_BOOK_LSTG');
145 commit;
146
147 edw_log.put_line('Completed Push_EDW_GL_BOOK_FA_BOOK_LSTG');
148
149 Exception When others then
150 raise;
151 commit;
152 END;
153
154
155 Procedure Push_EDW_GL_BOOK_BOOK_LSTG(p_from_date IN date, p_to_date IN DATE) IS
156 l_date1 DATE;
157 l_date2 DATE;
158 l_rows_inserted NUMBER :=0;
159
160 BEGIN
161
162 edw_log.put_line('Starting Push_EDW_GL_BOOK_BOOK_LSTG');
163 l_date1 := p_from_date;
164 l_date2 := p_to_date;
165
166 Insert Into EDW_GL_BOOK_BOOK_LSTG(
167 ACCT_PEIROD_TYPE,
168 ALL_FK,
169 BOOK_PK,
170 CHART_OF_ACCTS_ID,
171 CURRENCY_CODE,
172 DELETION_DATE,
173 GL_BOOK,
174 INSTANCE,
175 MRC_SOB_TYPE_CODE,
176 NAME,
177 PERIOD_SET_NAME,
178 TRX_CALENDAR,
179 USER_ATTRIBUTE1,
180 USER_ATTRIBUTE2,
181 USER_ATTRIBUTE3,
182 USER_ATTRIBUTE4,
183 USER_ATTRIBUTE5,
184 OPERATION_CODE,
185 COLLECTION_STATUS,
186 CREATION_DATE,
187 LAST_UPDATE_DATE
188 )
189 select ACCT_PERIOD_TYPE,
190 NVL(ALL_FK, 'NA_EDW'),
191 BOOK_PK,
192 CHART_OF_ACCTS_ID,
193 CURRENCY_CODE,
194 to_date(NULL), -- DELETION_DATE,
195 GL_BOOK,
196 INSTANCE,
197 MRC_SOB_TYPE_CODE,
198 NAME,
199 PERIOD_SET_NAME,
200 TRX_CALENDAR,
201 NULL, --USER_ATTRIBUTE1,
202 NULL, --USER_ATTRIBUTE2,
203 NULL, --USER_ATTRIBUTE3,
204 NULL, --USER_ATTRIBUTE4,
205 NULL, --USER_ATTRIBUTE5,
206 NULL, -- OPERATION_CODE
207 'READY',
208 trunc(sysdate),
209 trunc(sysdate)
210 from FII_GL_BOOK_BOOK_LCV
211 where last_update_date between l_date1 and l_date2;
212
213 edw_log.put_line('Commiting records for EDW_GL_BOOK_BOOK_LSTG');
214 commit;
215
216 edw_log.put_line('Completed Push_EDW_GL_BOOK_BOOK_LSTG');
217
218 Exception When others then
219 raise;
220 commit;
221 END;
222
223 End FII_GL_BOOK_M_C;