[Home] [Help]
PACKAGE BODY: APPS.FII_AP_OP_INDICATOR_SUM_C
Source
1 Package Body FII_AP_OP_INDICATOR_SUM_C AS
2 /* $Header: FIIAP12B.pls 120.2 2005/06/13 10:58:56 sgautam noship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
5
6 g_fii_schema VARCHAR2(30);
7 g_tablespace VARCHAR2(30);
8
9 g_errbuf VARCHAR2(2000) := NULL;
10 g_retcode VARCHAR2(200) := NULL;
11 g_exception_msg VARCHAR2(200) := NULL;
12 g_cur_qtr_start_date DATE;
13 g_today DATE;
14
15 G_TABLE_NOT_EXIST EXCEPTION;
16 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
17
18 ---------------------------------------------------
19 -- PROCEDURE DROP_TABLE
20 ---------------------------------------------------
21 procedure drop_table (p_table_name in varchar2) is
22 l_stmt varchar2(400);
23 Begin
24
25 l_stmt:='drop table '||g_fii_schema||'.'||p_table_name;
26
27 if g_debug_flag = 'Y' then
28 edw_log.debug_line('');
29 edw_log.debug_line(l_stmt);
30 end if;
31 execute immediate l_stmt;
32
33 Exception
34 WHEN G_TABLE_NOT_EXIST THEN
35 null; -- Oracle 942, table does not exist, no actions
36 WHEN OTHERS THEN
37 raise;
38 End Drop_Table;
39
40 --------------------------------------------------
41 -- PROCEDURE CREATE_FII_AP_OP_IND_SUM_TEMP
42 ---------------------------------------------------
43 procedure CREATE_FII_AP_OP_IND_SUM_TEMP is
44 l_stmt VARCHAR2(4000);
45 l_state VARCHAR2(200);
46 Begin
47 drop_table('FII_AP_OP_IND_SUMMARY_TEMP1');
48 drop_table('FII_AP_OP_IND_SUMMARY_TEMP2');
49
50 if g_debug_flag = 'Y' then
51 edw_log.put_line('Creating FII_AP_OP_IND_SUMMARY_TEMP tables');
52 end if;
53
54 l_state := 'Create FII_AP_OP_IND_SUMMARY_TEMP1';
55 l_stmt := 'create table '||g_fii_schema||'.FII_AP_OP_IND_SUMMARY_TEMP1(
56 operating_unit_pk_key NUMBER,
57 operating_unit_name VARCHAR2(150),
58 trading_partner_pk_key NUMBER,
59 trading_partner_name VARCHAR2(120),
60 inv_lines_count NUMBER,
61 inv_count NUMBER,
62 inv_amount NUMBER)
63 TABLESPACE '||g_tablespace||'
64 PCTFREE 5
65 storage (INITIAL 4K NEXT 32K)';
66
67 if g_debug_flag = 'Y' then
68 edw_log.debug_line('');
69 edw_log.debug_line(l_stmt);
70 end if;
71 execute immediate l_stmt;
72
73 l_state := 'Create FII_AP_OP_IND_SUMMARY_TEMP2';
74 l_stmt := 'create table '||g_fii_schema||'.FII_AP_OP_IND_SUMMARY_TEMP2(
75 operating_unit_pk_key NUMBER,
76 operating_unit_name VARCHAR2(150),
77 trading_partner_pk_key NUMBER,
78 trading_partner_name VARCHAR2(120),
79 inv_payment_count NUMBER,
80 inv_payment_amount NUMBER)
81 TABLESPACE '||g_tablespace||'
82 PCTFREE 5
83 storage (INITIAL 4K NEXT 32K)';
84
85 if g_debug_flag = 'Y' then
86 edw_log.debug_line('');
87 edw_log.debug_line(l_stmt);
88 end if;
89 execute immediate l_stmt;
90
91 if g_debug_flag = 'Y' then
92 edw_log.put_line('Populating FII_AP_OP_IND_SUMMARY_TEMP tables');
93 end if;
94
95 -- --------------------------------------
96 -- Populate FII_AP_OP_IND_SUMMARY_TEMP tables
97 -- --------------------------------------
98 l_state := 'Populate FII_AP_OP_IND_SUMMARY_TEMP1';
99 l_stmt := 'INSERT INTO '||g_fii_schema||'.FII_AP_OP_IND_SUMMARY_TEMP1 (
100 operating_unit_pk_key,
101 operating_unit_name,
102 trading_partner_pk_key,
103 trading_partner_name,
104 inv_lines_count,
105 inv_count,
106 inv_amount)
107 SELECT org.OPER_OPERATING_UNIT_PK_KEY operating_unit_pk_key,
108 org.oper_name operating_unit_name,
109 partner.TPRT_TRADE_PARTNER_PK_KEY trading_partner_pk_key,
110 partner.tprt_name trading_partner_name,
111 count(invl.inv_line_pk_key) inv_lines_count,
112 count(distinct invl.inv_fk_key) inv_count,
113 sum(nvl(invl.inv_line_amt_g, 0)) inv_amount
114 FROM fii_ap_inv_lines_f invl,
115 edw_organization_m org,
116 edw_trd_partner_m partner
117 WHERE invl.org_fk_key = org.orga_organization_pk_key
118 AND invl.org_fk_key > 0
119 AND invl.supplier_fk_key = partner.TPLO_TPARTNER_LOC_PK_KEY
120 AND invl.supplier_fk_key > 0
121 AND invl.inv_date between to_date('''
122 ||to_char(g_cur_qtr_start_date, 'DD-MM-YYYY')
123 ||''', ''DD-MM-YYYY'') and to_date('''
124 || to_char(g_today, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')
125 GROUP BY org.OPER_OPERATING_UNIT_PK_KEY,
126 org.oper_name,
127 partner.TPRT_TRADE_PARTNER_PK_KEY,
128 partner.tprt_name';
129
130 if g_debug_flag = 'Y' then
131 edw_log.debug_line('');
132 edw_log.debug_line(l_stmt);
133 end if;
134 execute immediate l_stmt;
135
136 if g_debug_flag = 'Y' then
137 edw_log.debug_line('Inserted ' || sql%rowcount || ' rows into FII_AP_OP_IND_SUMMARY_TEMP1');
138 end if;
139
140 l_state := 'Populate FII_AP_OP_IND_SUMMARY_TEMP2';
141 l_stmt := 'INSERT INTO '||g_fii_schema||'.FII_AP_OP_IND_SUMMARY_TEMP2 (
142 operating_unit_pk_key,
143 operating_unit_name,
144 trading_partner_pk_key,
145 trading_partner_name,
146 inv_payment_amount,
147 inv_payment_count)
148 select org.OPER_OPERATING_UNIT_PK_KEY operating_unit_pk_key,
149 org.oper_name operating_unit_name,
150 partner.TPRT_TRADE_PARTNER_PK_KEY trading_partner_pk_key,
151 partner.tprt_name trading_partner_name,
152 sum(nvl(invp.payment_amt_g, 0)) inv_payment_amount,
153 count(invp.inv_payment_pk) inv_payment_count
154 from fii_ap_inv_paymts_f invp,
155 edw_organization_m org,
156 edw_trd_partner_m partner
157 WHERE invp.org_fk_key = org.orga_organization_pk_key
158 AND invp.org_fk_key > 0
159 AND invp.supplier_fk_key = partner.TPLO_TPARTNER_LOC_PK_KEY
160 AND invp.supplier_fk_key > 0
161 AND invp.creation_date between to_date('''
162 ||to_char(g_cur_qtr_start_date, 'DD-MM-YYYY')
163 ||''', ''DD-MM-YYYY'') and to_date('''
164 || to_char(g_today, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')
165 GROUP BY org.OPER_OPERATING_UNIT_PK_KEY,
166 org.oper_name,
167 partner.TPRT_TRADE_PARTNER_PK_KEY,
168 partner.tprt_name';
169
170 if g_debug_flag = 'Y' then
171 edw_log.debug_line('');
172 edw_log.debug_line(l_stmt);
173 end if;
174 execute immediate l_stmt;
175
176 if g_debug_flag = 'Y' then
177 edw_log.debug_line('Inserted ' || sql%rowcount || ' rows into FII_AP_OP_IND_SUMMARY_TEMP2');
178 end if;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 g_errbuf:=sqlerrm;
183 g_retcode:=sqlcode;
184 g_exception_msg := g_retcode || ':' || g_errbuf;
185 if g_debug_flag = 'Y' then
186 edw_log.put_line('Error occured while ' || l_state);
187 edw_log.put_line(g_exception_msg);
188 end if;
189 raise;
190 END CREATE_FII_AP_OP_IND_SUM_TEMP;
191
192 --------------------------------------------------
193 -- PROCEDURE POPULATE_FII_AP_OP_IND_SUMMARY
194 ---------------------------------------------------
195 procedure POPULATE_FII_AP_OP_IND_SUMMARY is
196 l_stmt VARCHAR2(6000);
197 l_state VARCHAR2(100);
198 Begin
199
200 if g_debug_flag = 'Y' then
201 edw_log.put_line('Truncate table FII_AP_OP_INDICATOR_SUMMARY');
202 end if;
203 l_state := 'Truncate FII_AP_OP_INDICATOR_SUMMARY';
204 l_stmt := 'truncate table '||g_fii_schema||'.FII_AP_OP_INDICATOR_SUMMARY';
205
206 if g_debug_flag = 'Y' then
207 edw_log.debug_line('');
208 edw_log.debug_line(l_stmt);
209 end if;
210 execute immediate l_stmt;
211
212 if g_debug_flag = 'Y' then
213 edw_log.put_line('Populate table FII_AP_OP_INDICATOR_SUMMARY');
214 end if;
215 l_state := 'Insert into FII_AP_OP_INDICATOR_SUMMARY';
216 l_stmt := 'insert into '||g_fii_schema||'.FII_AP_OP_INDICATOR_SUMMARY (
217 operating_unit_pk_key,
218 operating_unit_name,
219 trading_partner_pk_key,
220 trading_partner_name,
221 inv_lines_count,
222 inv_count,
223 inv_amount,
224 inv_payment_count,
225 inv_payment_amount)
226 select f.operating_unit_pk_key,
227 f.operating_unit_name,
228 f.trading_partner_pk_key,
229 f.trading_partner_name,
230 sum(f.inv_lines_count),
231 sum(f.inv_count),
232 sum(f.inv_amount),
233 sum(f.inv_payment_count),
234 sum(f.inv_payment_amount)
235 FROM (select operating_unit_pk_key,
236 operating_unit_name,
237 trading_partner_pk_key,
238 trading_partner_name,
239 inv_amount,
240 inv_lines_count,
241 inv_count,
242 0 inv_payment_amount,
243 0 inv_payment_count
244 FROM ' ||g_fii_schema||'.fii_ap_op_ind_summary_temp1
245 UNION ALL
246 SELECT operating_unit_pk_key,
247 operating_unit_name,
248 trading_partner_pk_key,
249 trading_partner_name,
250 0 inv_amount,
251 0 inv_lines_count,
252 0 inv_count,
253 inv_payment_amount,
254 inv_payment_count
255 FROM ' ||g_fii_schema||'.fii_ap_op_ind_summary_temp2) f
256 GROUP BY f.operating_unit_pk_key,
257 f.operating_unit_name,
258 f.trading_partner_pk_key,
259 f.trading_partner_name';
260
261 if g_debug_flag = 'Y' then
262 edw_log.debug_line('');
263 edw_log.debug_line(l_stmt);
264 end if;
265 execute immediate l_stmt;
266
267 if g_debug_flag = 'Y' then
268 edw_log.put_line('Inserted ' || sql%rowcount || ' rows into FII_AP_OP_INDICATOR_SUMMARY');
269 end if;
270
271 l_state := 'Drop FII_AP_OP_IND_SUMMARY_TEMP tables';
272 DROP_TABLE('FII_AP_OP_IND_SUMMARY_TEMP1');
273 DROP_TABLE('FII_AP_OP_IND_SUMMARY_TEMP2');
274 EXCEPTION
275 WHEN OTHERS THEN
276 g_errbuf:=sqlerrm;
277 g_retcode:=sqlcode;
278 g_exception_msg := g_retcode || ':' || g_errbuf;
279 if g_debug_flag = 'Y' then
280 edw_log.put_line('Error occured while ' || l_state);
281 edw_log.put_line(g_exception_msg);
282 end if;
283 raise;
284
285 END POPULATE_FII_AP_OP_IND_SUMMARY;
286
287 -----------------------------------------------------------
288 -- PROCEDURE Load
289 -----------------------------------------------------------
290 Procedure Load (Errbuf IN OUT NOCOPY VARCHAR2,
291 Retcode IN OUT NOCOPY VARCHAR2) IS
292 l_status VARCHAR2(30);
293 l_industry VARCHAR2(30);
294 l_stmt VARCHAR2(4000);
295 l_dir VARCHAR2(100);
296 Begin
297
298 l_stmt := ' ALTER SESSION SET global_names = false';
299 EXECUTE IMMEDIATE l_stmt;
300
301 -- DEBUG
302 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
303 edw_log.g_debug := TRUE;
304 END IF;
305
306 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
307 if l_dir is null then
308 l_dir:='/sqlcom/log';
309 end if;
310 if g_debug_flag = 'Y' then
311 edw_log.put_names('FII_AP_OP_IND_SMMARY.log','FII_AP_OP_IND_SUMMARY.out',l_dir);
312 end if;
313
314 if g_debug_flag = 'Y' then
315 fii_util.put_timestamp;
316 end if;
317
318 -- --------------------------------------------------------
319 -- Find the schema owner and tablespace
320 -- FII_AP_OP_INDICATOR_SUMMARY is using
321 -- --------------------------------------------------------
322 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
323 THEN NULL;
324 END IF;
325
326 SELECT tablespace_name
327 INTO g_tablespace
328 FROM all_tables
329 WHERE table_name = 'FII_AP_OP_INDICATOR_SUMMARY'
330 AND owner = g_fii_schema;
331
332 if g_debug_flag = 'Y' then
333 edw_log.put_line('Getting current quarter start date');
334 end if;
335 if g_debug_flag = 'Y' then
336 fii_util.start_timer;
337 end if;
338 g_cur_qtr_start_date := FII_TIME_WH_API.get_curr_eqtr_start;
339 g_today := FII_TIME_WH_API.today;
340
341 if g_debug_flag = 'Y' then
342 fii_util.stop_timer;
343 fii_util.print_timer('Duration');
344
345 fii_util.start_timer;
346 end if;
347 CREATE_FII_AP_OP_IND_SUM_TEMP;
348 if g_debug_flag = 'Y' then
349 fii_util.stop_timer;
350 fii_util.print_timer('Duration');
351
352 fii_util.start_timer;
353 end if;
354
355 POPULATE_FII_AP_OP_IND_SUMMARY;
356 if g_debug_flag = 'Y' then
357 fii_util.stop_timer;
358 fii_util.print_timer('Duration');
359 end if;
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 raise;
364 END Load;
365
366 END FII_AP_OP_INDICATOR_SUM_C;