[Home] [Help]
PACKAGE BODY: APPS.FII_AP_TRANS_BACKLOG_SUM_C
Source
1 Package Body FII_AP_TRANS_BACKLOG_SUM_C AS
2 /* $Header: FIIAP13B.pls 120.1 2005/06/13 11:11:37 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_today DATE;
12
13 --------------------------------------------------
14 -- PROCEDURE POPULATE_FII_AP_TR_BLG_SUMMARY
15 ---------------------------------------------------
16 procedure POPULATE_FII_AP_TR_BLG_SUMMARY is
17 l_stmt VARCHAR2(6000);
18 l_state VARCHAR2(100);
19 Begin
20
21 if g_debug_flag = 'Y' then
22 edw_log.put_line('Truncate table FII_AP_TRANS_BACKLOG_SUMMARY');
23 end if;
24
25 l_state := 'Truncate FII_AP_TRANS_BACKLOG_SUMMARY';
26 l_stmt := 'truncate table '||g_fii_schema||'.FII_AP_TRANS_BACKLOG_SUMMARY';
27
28 if g_debug_flag = 'Y' then
29 edw_log.debug_line('');
30 edw_log.debug_line(l_stmt);
31 end if;
32 execute immediate l_stmt;
33
34 if g_debug_flag = 'Y' then
35 edw_log.put_line('Populate table FII_AP_TRANS_BACKLOG_SUMMARY');
36 end if;
37 l_state := 'Insert into FII_AP_TRANS_BACKLOG_SUMMARY';
38 l_stmt := 'insert into '||g_fii_schema||'.FII_AP_TRANS_BACKLOG_SUMMARY (
39 operating_unit_pk_key,
40 operating_unit_name,
41 trading_partner_pk_key,
42 trading_partner_name,
43 sch_payment_id,
44 open_payment_amount,
45 invoice_pk_key,
46 invoice_num,
47 invoice_amount,
48 payment_due_date,
49 invoice_date,
50 days_outstanding)
51 select org.oper_operating_unit_pk_key OPERATING_UNIT_PK_KEY,
52 org.oper_name OPERATING_UNIT_NAME,
53 partner.tprt_trade_partner_pk_key TRADING_PARTNER_PK_KEY,
54 partner.tprt_name TRADING_PARTNER_NAME,
55 schp.sch_payment_pk_key SCH_PAYMENT_ID,
56 nvl(schp.REMAINING_INV_AMT_AT_RISK_G,0) OPEN_PAYMENT_AMOUNT,
57 schp.inv_fk_key INVOICE_PK_KEY,
58 schp.invoice_num INVOICE_NUM,
59 decode(schp.inv_amt_having_disc_g,0, schp.inv_amt_not_having_disc_g,
60 schp.inv_amt_having_disc_g) INVOICE_AMOUNT,
61 schp.due_date PAYMENT_DUE_DATE,
62 time.calendar_date INVOICE_DATE,
63 round(to_date(''' || to_char(g_today,'DD-MM-YYYY') || ''',''DD-MM-YYYY'') - schp.due_date) days_outstanding
64 from fii_ap_sch_paymts_f schp,
65 edw_organization_m org,
66 edw_time_cal_day_ltc time,
67 edw_trd_partner_m partner
68 WHERE schp.REMAINING_INV_AMT_AT_RISK_G > 0
69 AND schp.payment_status_flag <> ''Y''
70 AND schp.due_date <= to_date('''||to_char(g_today,'DD-MM-YYYY')||''',''DD-MM-YYYY'')
71 AND schp.inv_date_fk_key = time.CAL_DAY_PK_KEY
72 AND schp.org_fk_key = org.ORGA_ORGANIZATION_PK_KEY
73 AND schp.org_fk_key > 0
74 AND schp.supplier_fk_key = partner.tplo_tpartner_loc_pk_key
75 AND schp.supplier_fk_key > 0
76 AND schp.inv_fk_key > 0';
77
78 if g_debug_flag = 'Y' then
79 edw_log.debug_line('');
80 edw_log.debug_line(l_stmt);
81 end if;
82 execute immediate l_stmt;
83
84 if g_debug_flag = 'Y' then
85 edw_log.put_line('Inserted ' || sql%rowcount || ' rows into FII_AP_TRANS_BACKLOG_SUMMARY');
86 end if;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 g_errbuf:=sqlerrm;
91 g_retcode:=sqlcode;
92 g_exception_msg := g_retcode || ':' || g_errbuf;
93 if g_debug_flag = 'Y' then
94 edw_log.put_line('Error occured while ' || l_state);
95 edw_log.put_line(g_exception_msg);
96 end if;
97 raise;
98
99 END POPULATE_FII_AP_TR_BLG_SUMMARY;
100
101 ----------------------------------------------------------
102 -- PROCEDURE LOAD
103 -----------------------------------------------------------
104 Procedure Load (Errbuf IN OUT NOCOPY VARCHAR2,
105 Retcode IN OUT NOCOPY VARCHAR2) IS
106 l_status VARCHAR2(30);
107 l_industry VARCHAR2(30);
108 l_stmt VARCHAR2(4000);
109 l_dir VARCHAR2(100);
110 Begin
111
112 l_stmt := ' ALTER SESSION SET global_names = false';
113 EXECUTE IMMEDIATE l_stmt;
114
115 -- DEBUG
116 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
117 edw_log.g_debug := TRUE;
118 END IF;
119
120 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
121 if l_dir is null then
122 l_dir:='/sqlcom/log';
123 end if;
124
125 if g_debug_flag = 'Y' then
126 edw_log.put_names('FII_AP_TR_BKLG_SUMMARY.log','FII_AP_TR_BKLG_SUMMARY.out',l_dir);
127
128
129 fii_util.put_timestamp;
130 end if;
131
132 -- --------------------------------------------------------
133 -- Find the schema owner and tablespace
134 -- FII_AP_TRANS_BACKLOG_SUMMARY is using
135 -- --------------------------------------------------------
136 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
137 THEN NULL;
138 END IF;
139
140 SELECT tablespace_name
141 INTO g_tablespace
142 FROM all_tables
143 WHERE table_name = 'FII_AP_TRANS_BACKLOG_SUMMARY'
144 AND owner = g_fii_schema;
145
146 g_today := FII_TIME_WH_API.today;
147
148 if g_debug_flag = 'Y' then
149 fii_util.start_timer;
150 end if;
151
152 POPULATE_FII_AP_TR_BLG_SUMMARY;
153
154 if g_debug_flag = 'Y' then
155 fii_util.stop_timer;
156 fii_util.print_timer('Duration');
157 end if;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 raise;
162 END Load;
163
164 END FII_AP_TRANS_BACKLOG_SUM_C;