[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_TYPE_M_C
Source
1 Package Body FII_AP_INV_TYPE_M_C AS
2 /* $Header: FIIAP03B.pls 120.4 2004/10/05 19:23:23 phu ship $ */
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 g_collect_er varchar2(1); --Added for Merrill Lynch, 03-DEC-02
8
9
10 Procedure Push(Errbuf in out NOCOPY Varchar2,
11 Retcode in out NOCOPY Varchar2,
12 p_from_date IN Varchar2,
13 p_to_date IN Varchar2) IS
14 l_dimension_name Varchar2(30) :='EDW_INV_TYPE_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 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
29 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
30 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
31 errbuf := fnd_message.get;
32 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
33 END IF;
34 FII_AP_INV_TYPE_M_C.g_push_date_range1 := nvl(l_from_date,
35 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
36 FII_AP_INV_TYPE_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
37 edw_log.put_line( 'The collection range is from '||
38 to_char(FII_AP_INV_TYPE_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
39 to_char(FII_AP_INV_TYPE_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
40 edw_log.put_line(' ');
41
42 -- -----------------------------
43 -- Read in profile option value --**Added for Merrill Lynch, 03-DEC-02
44 -- -----------------------------
45 g_collect_er := NVL(FND_PROFILE.value('FII_COLLECT_ER'),'N');
46
47 -- -----------------------------------------------------------------------------
48 -- Start of Collection , Developer Customizable Section
49 -- -----------------------------------------------------------------------------
50
51 edw_log.put_line(' ');
52 edw_log.put_line('Pushing data');
53
54 l_temp_date := sysdate;
55
56
57 Push_EDW_IVTY_INV_LSTG(FII_AP_INV_TYPE_M_C.g_push_date_range1, FII_AP_INV_TYPE_M_C.g_push_date_range2);
58 Push_EDW_IVTY_INV_TYPE_LSTG(FII_AP_INV_TYPE_M_C.g_push_date_range1, FII_AP_INV_TYPE_M_C.g_push_date_range2);
59
60
61 l_duration := sysdate - l_temp_date;
62
63 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
64 edw_log.put_line(' ');
65 -- ---------------------------------------------------------------------------
66 -- END OF Collection , Developer Customizable Section
67 -- ---------------------------------------------------------------------------
68 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, g_push_date_range2);
69 commit;
70
71 Exception When others then
72 Errbuf:=sqlerrm;
73 Retcode:=sqlcode;
74 l_exception_msg := Retcode || ':' || Errbuf;
75 FII_AP_INV_TYPE_M_C.g_exception_msg := l_exception_msg;
76 rollback;
77 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, FII_AP_INV_TYPE_M_C.g_exception_msg,g_push_date_range1, g_push_date_range2);
78
79 commit;
80 End;
81
82
83 Procedure Push_EDW_IVTY_INV_LSTG(p_from_date IN date, p_to_date IN DATE) IS
84 l_date1 DATE;
85 l_date2 DATE;
86 l_rows_inserted NUMBER :=0;
87
88 l_stmt VARCHAR2(5000); -- Added for Merrill Lynch,04-DEC-02
89 l_er_stmt varchar2(100) :=NULL; -- Added for Merrill Lynch,04-DEC-02
90
91 BEGIN
92 edw_log.put_line('Starting Push_EDW_IVTY_INV_LSTG');
93 l_date1 := p_from_date;
94 l_date2 := p_to_date;
95
96 IF (g_collect_er <> 'Y') THEN
97 l_er_stmt := ' AND inv_type_fk <> ''EXPENSE REPORT'''; -- Added for Merrill Lynch,04-DEC-02
98 END IF;
99
100 --** Modified for Merrill Lynch,04-DEC-02
101 l_stmt := 'Insert Into
102 EDW_IVTY_INV_LSTG(
103 CANCELLED_DATE,
104 CREATION_DATE,
105 INSTANCE,
106 INV_DP,
107 INV_ID,
108 INV_NAME,
109 INV_PK,
110 INV_SOURCE,
111 INV_TYPE_FK,
112 LAST_UPDATE_DATE,
113 NAME,
114 USER_ATTRIBUTE1,
115 USER_ATTRIBUTE2,
116 USER_ATTRIBUTE3,
117 USER_ATTRIBUTE4,
118 USER_ATTRIBUTE5,
119 OPERATION_CODE,
120 COLLECTION_STATUS)
121 select CANCELLED_DATE,
122 CREATION_DATE,
123 substrb(INSTANCE, 1, 40),
124 substrb(INV_DP, 1, 160),
125 INV_ID,
126 substrb(INV_NAME, 1, 30),
127 substrb(INV_PK, 1, 120),
128 substrb(INV_SOURCE, 1, 20),
129 substrb(NVL(INV_TYPE_FK, ''NA_EDW''), 1, 120),
130 LAST_UPDATE_DATE,
131 substrb(NAME, 1, 50),
132 substrb(USER_ATTRIBUTE1, 1, 240),
133 substrb(USER_ATTRIBUTE2, 1, 240),
134 substrb(USER_ATTRIBUTE3, 1, 240),
135 substrb(USER_ATTRIBUTE4, 1, 240),
136 substrb(USER_ATTRIBUTE5, 1, 240),
137 NULL, /* OPERATION_CODE */
138 ''READY''
139 from FII_AP_IVTY_INV_LCV
140 where last_update_date between :l_date1 and :l_date2'||l_er_stmt;
141 --**
142
143 --** Added for Merrill Lynch,04-DEC-02
144 edw_log.debug_line('');
145 edw_log.debug_line(l_stmt);
146 execute immediate l_stmt using l_date1,l_date2;
147 --**
148
149 l_rows_inserted := sql%rowcount;
150
151 edw_log.debug_line('Inserted '|| l_rows_inserted ||' rows into EDW_IVTY_INV_LSTG table'); -- Added for Merrill Lynch,04-DEC-02
152
153 g_row_count := g_row_count + l_rows_inserted ;
154 edw_log.put_line('Commiting records for EDW_IVTY_INV_LSTG');
155 commit;
156
157 edw_log.put_line('Completed Push_EDW_IVTY_INV_LSTG');
158 Exception When others then
159 raise;
160 commit;
161 END;
162
163
164 Procedure Push_EDW_IVTY_INV_TYPE_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
169 l_stmt VARCHAR2(5000); -- Added for Merrill Lynch,04-DEC-02
170 l_er_stmt VARCHAR2(100) := NULL; -- Added for Merrill Lynch,04-DEC-02
171
172 BEGIN
173 edw_log.put_line('Starting Push_EDW_IVTY_INV_TYPE_LSTG');
174 l_date1 := p_from_date;
175 l_date2 := p_to_date;
176
177 IF (g_collect_er <> 'Y') THEN -- Added for Merrill Lynch,04-DEC-02
178 l_er_stmt := ' WHERE inv_type_pk <> ''EXPENSE REPORT''';
179 END IF;
180
181 --** Modified for Merrill Lynch,04-DEC-02
182 l_stmt := ' Insert Into
183 EDW_IVTY_INV_TYPE_LSTG(
184 ALL_FK,
185 CREATION_DATE,
186 DESCRIPTION,
187 INSTANCE,
188 INV_TYPE,
189 INV_TYPE_DP,
190 INV_TYPE_PK,
191 LAST_UPDATE_DATE,
192 NAME,
193 USER_ATTRIBUTE1,
194 USER_ATTRIBUTE2,
195 USER_ATTRIBUTE3,
196 USER_ATTRIBUTE4,
197 USER_ATTRIBUTE5,
198 OPERATION_CODE,
199 COLLECTION_STATUS)
200 select NVL(ALL_FK, ''NA_EDW''),
201 CREATION_DATE,
202 substrb(DESCRIPTION, 1, 50),
203 substrb(INSTANCE, 1, 40),
204 substrb(INV_TYPE, 1, 80),
205 substrb(INV_TYPE_DP, 1, 160),
206 substrb(INV_TYPE_PK, 1, 120),
207 LAST_UPDATE_DATE,
208 substrb(NAME, 1, 50),
209 substrb(USER_ATTRIBUTE1, 1, 240),
210 substrb(USER_ATTRIBUTE2, 1, 240),
211 substrb(USER_ATTRIBUTE3, 1, 240),
212 substrb(USER_ATTRIBUTE4, 1, 240),
213 substrb(USER_ATTRIBUTE5, 1, 240),
214 NULL, /* OPERATION_CODE */
215 ''READY''
216 from FII_AP_IVTY_INV_TYPE_LCV'||l_er_stmt;
217 --**
218
219 --** Added for Merrill Lynch,04-DEC-02
220 edw_log.debug_line('');
221 edw_log.debug_line(l_stmt);
222 execute immediate l_stmt;
223
224 edw_log.debug_line('Inserted '||sql%rowcount||' rows into EDW_IVTY_INV_TYPE_LSTG table');
225 --**
226
227 edw_log.put_line('Commiting records for EDW_IVTY_INV_TYPE_LSTG');
228 commit;
229
230 edw_log.put_line('Completed Push_EDW_IVTY_INV_TYPE_LSTG');
231 Exception When others then
232 raise;
233 commit;
234 END;
235 End FII_AP_INV_TYPE_M_C;