[Home] [Help]
PACKAGE BODY: APPS.FII_AP_DISCOUNTS_SUM_C
Source
1 Package Body FII_AP_DISCOUNTS_SUM_C AS
2 /* $Header: FIIAP14B.pls 120.1 2005/06/13 11:14:23 sgautam noship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('EDW_DEBUG'), 'N');
5 g_fii_schema VARCHAR2(30);
6 g_tablespace VARCHAR2(30);
7
8 g_errbuf VARCHAR2(2000) := NULL;
9 g_retcode VARCHAR2(200) := NULL;
10 g_exception_msg VARCHAR2(200) := NULL;
11 g_cur_qtr_start_date DATE;
12 g_cur_qtr_end_date DATE;
13 g_today DATE;
14
15 --------------------------------------------------
16 -- PROCEDURE POPULATE_FII_AP_DISC_SUMMARY
17 ---------------------------------------------------
18 procedure POPULATE_FII_AP_DISC_SUMMARY is
19 l_stmt VARCHAR2(6000);
20 l_state VARCHAR2(100);
21 Begin
22
23 if g_debug_flag = 'Y' then
24 edw_log.put_line('Truncate table FII_AP_DISCOUNTS_SUMMARY');
25 end if;
26 l_state := 'Truncate FII_AP_DISCOUNTS_SUMMARY';
27 l_stmt := 'truncate table '||g_fii_schema||'.FII_AP_DISCOUNTS_SUMMARY';
28
29 if g_debug_flag = 'Y' then
30 edw_log.debug_line('');
31 edw_log.debug_line(l_stmt);
32 end if;
33 execute immediate l_stmt;
34
35 if g_debug_flag = 'Y' then
36 edw_log.put_line('Populate table FII_AP_DISCOUNTS_SUMMARY');
37 end if;
38 l_state := 'Insert into FII_AP_DISCOUNTS_SUMMARY';
39 l_stmt := 'insert into '||g_fii_schema||'.FII_AP_DISCOUNTS_SUMMARY (
40 OPERATING_UNIT_PK_KEY,
41 OPERATING_UNIT_NAME,
42 TRADING_PARTNER_PK_KEY,
43 TRADING_PARTNER_NAME,
44 RECORD_ID,
45 INVOICE_NUM,
46 INVOICE_DATE,
47 INVOICE_UNIQUE_IDENTIFIER,
48 INVOICE_AMOUNT,
49 DISCOUNT_AVAILABLE,
50 DISCOUNT_LOST,
51 DISCOUNT_DATE,
52 RECORD_TYPE)
53 select org.oper_operating_unit_pk_key OPERATING_UNIT_PK_KEY,
54 org.oper_name OPERATING_UNIT_NAME,
55 partner.tprt_trade_partner_pk_key TRADING_PARTNER_PK_KEY,
56 partner.tprt_name TRADING_PARTNER_NAME,
57 invp.inv_payment_pk RECORD_ID,
58 invp.inv_num INVOICE_NUM,
59 to_date(NULL) INVOICE_DATE,
60 invp.inv_fk_key INVOICE_UNIQUE_IDENTIFIER,
61 NVL(invp.payment_amt_g,0) INVOICE_AMOUNT,
62 0 DISCOUNT_AVAILABLE,
63 NVL(invp.disc_amt_lost_g,0) DISCOUNT_LOST,
64 to_date(NULL) DISCOUNT_DATE,
65 ''L'' RECORD_TYPE
66 from fii_ap_inv_paymts_f invp,
67 edw_organization_m org,
68 edw_trd_partner_m partner
69 WHERE invp.check_date between to_date('''
70 ||to_char(g_cur_qtr_start_date, 'DD-MM-YYYY')
71 ||''', ''DD-MM-YYYY'') and to_date('''
72 || to_char(g_today, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')
73 AND invp.org_fk_key = org.orga_organization_pk_key
74 AND invp.org_fk_key > 0
75 AND invp.supplier_fk_key = partner.tplo_tpartner_loc_pk_key
76 AND invp.supplier_fk_key > 0
77 AND NVL(invp.disc_amt_lost_g,0) > 0
78 UNION ALL
79 select org.oper_operating_unit_pk_key OPERATING_UNIT_PK_KEY,
80 org.oper_name OPERATING_UNIT_NAME,
81 partner.tprt_trade_partner_pk_key TRADING_PARTNER_PK_KEY,
82 partner.tprt_name TRADING_PARTNER_NAME,
83 schp.sch_payment_pk RECORD_ID,
84 schp.invoice_num INVOICE_NUM,
85 time.calendar_date INVOICE_DATE,
86 schp.inv_fk_key INVOICE_UNIQUE_IDENTIFIER,
87 decode(schp.inv_amt_having_disc_g,0, schp.inv_amt_not_having_disc_g,
88 schp.inv_amt_having_disc_g) INVOICE_AMOUNT,
89 NVL(schp.remaining_disc_amt_at_risk_g,0) DISCOUNT_AVAILABLE,
90 0 DISCOUNT_LOST,
91 DECODE(sign(first_disc_date-to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')),1,first_disc_date,
92 0,first_disc_date,
93 (DECODE(sign(second_disc_date-to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')),1,second_disc_date,
94 0, second_disc_date,third_disc_date))) DISCOUNT_DATE,
95 ''R'' RECORD_TYPE
96 from fii_ap_sch_paymts_f schp,
97 edw_organization_m org,
98 edw_time_cal_day_ltc time,
99 edw_trd_partner_m partner
100 WHERE schp.org_fk_key = org.orga_organization_pk_key
101 AND schp.org_fk_key > 0
102 AND schp.supplier_fk_key = partner.tplo_tpartner_loc_pk_key
103 AND schp.supplier_fk_key > 0
104 AND NVL(schp.remaining_disc_amt_at_risk_g,0) > 0
105 AND schp.inv_date_fk_key = time.CAL_DAY_PK_KEY
106 AND (schp.first_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
107 and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'') OR
108 schp.second_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
109 and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'') OR
110 schp.third_disc_date between to_date(''' ||to_char(g_today,'DD-MM-YYYY')|| ''',''DD-MM-YYYY'')
111 and to_date('''||to_char(g_cur_qtr_end_date,'DD-MM-YYYY')||''',''DD-MM-YYYY''))';
112
113 if g_debug_flag = 'Y' then
114 edw_log.debug_line('');
115 edw_log.debug_line(l_stmt);
116 end if;
117 execute immediate l_stmt;
118
119 if g_debug_flag = 'Y' then
120 edw_log.put_line('Inserted ' || sql%rowcount || ' rows into FII_AP_DISCOUNTS_SUMMARY');
121 end if;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 g_errbuf:=sqlerrm;
126 g_retcode:=sqlcode;
127 g_exception_msg := g_retcode || ':' || g_errbuf;
128 if g_debug_flag = 'Y' then
129 edw_log.put_line('Error occured while ' || l_state);
130 edw_log.put_line(g_exception_msg);
131 end if;
132 raise;
133
134 END POPULATE_FII_AP_DISC_SUMMARY;
135
136 ----------------------------------------------------------
137 -- PROCEDURE LOAD
138 -----------------------------------------------------------
139 Procedure Load (Errbuf IN OUT NOCOPY VARCHAR2,
140 Retcode IN OUT NOCOPY VARCHAR2) IS
141 l_status VARCHAR2(30);
142 l_industry VARCHAR2(30);
143 l_stmt VARCHAR2(4000);
144 l_dir VARCHAR2(100);
145 Begin
146
147 l_stmt := ' ALTER SESSION SET global_names = false';
148 EXECUTE IMMEDIATE l_stmt;
149
150 -- DEBUG
151 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
152 edw_log.g_debug := TRUE;
153 END IF;
154
155 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
156 if l_dir is null then
157 l_dir:='/sqlcom/log';
158 end if;
159 if g_debug_flag = 'Y' then
160 edw_log.put_names('FII_AP_TR_BKLG_SUMMARY.log','FII_AP_TR_BKLG_SUMMARY.out',l_dir);
161
162
163 fii_util.put_timestamp;
164 end if;
165
166 -- --------------------------------------------------------
167 -- Find the schema owner and tablespace
168 -- FII_AP_DISCOUNTS_SUMMARY is using
169 -- --------------------------------------------------------
170 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
171 THEN NULL;
172 END IF;
173
174 SELECT tablespace_name
175 INTO g_tablespace
176 FROM all_tables
177 WHERE table_name = 'FII_AP_DISCOUNTS_SUMMARY'
178 AND owner = g_fii_schema;
179
180 if g_debug_flag = 'Y' then
181 edw_log.put_line('Getting current quarter start date and end date');
182 fii_util.start_timer;
183 end if;
184 g_cur_qtr_start_date := FII_TIME_WH_API.get_curr_eqtr_start;
185 g_cur_qtr_end_date := FII_TIME_WH_API.get_curr_eqtr_end;
186 g_today := FII_TIME_WH_API.today;
187 if g_debug_flag = 'Y' then
188 fii_util.stop_timer;
189 fii_util.print_timer('Duration');
190
191 fii_util.start_timer;
192 end if;
193 POPULATE_FII_AP_DISC_SUMMARY;
194 if g_debug_flag = 'Y' then
195 fii_util.stop_timer;
196 fii_util.print_timer('Duration');
197 end if;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 raise;
202 END Load;
203
204 END FII_AP_DISCOUNTS_SUM_C;