[Home] [Help]
PACKAGE BODY: APPS.FII_AR_TRX_DIST_F_D
Source
1 Package Body FII_AR_TRX_DIST_F_D AS
2 /* $Header: FIIAR07B.pls 120.3 2005/06/07 12:14:51 sgautam noship $ */
3
4 g_errbuf varchar2(2000) := NULL;
5 g_retcode varchar2(200) := NULL;
6 g_fii_schema VARCHAR2(30);
7 g_instance_code VARCHAR2(30);
8 g_db_link VARCHAR2(128);
9 g_tablespace VARCHAR2(30);
10 G_TABLE_NOT_EXIST EXCEPTION;
11 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
12
13
14 PROCEDURE Init(p_instance_code IN VARCHAR2) is
15 l_stmt VARCHAR2(200);
16 l_status VARCHAR2(30);
17 l_industry VARCHAR2(30);
18 BEGIN
19
20 -- --------------------------------------------------------
21 -- Set instance code, database link name
22 -- --------------------------------------------------------
23
24 l_stmt := 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
25 execute immediate l_stmt;
26
27 g_instance_code := p_instance_code;
28
29 select warehouse_to_instance_link
30 into g_db_link
31 from edw_source_instances
32 where instance_code = g_instance_code;
33
34 -- --------------------------------------------------------
35 -- Find the schema owner and tablespace FII_AR_TRX_DIST_FSTG is using
36 -- --------------------------------------------------------
37 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
38 THEN NULL;
39 END IF;
40
41 SELECT tablespace_name
42 INTO g_tablespace
43 FROM all_tables
44 WHERE table_name = 'FII_AR_TRX_DIST_FSTG'
45 AND owner = g_fii_schema;
46
47 end Init;
48
49
50
51 ---------------------------------------------------
52 -- PROCEDURE DROP_TABLE
53 ---------------------------------------------------
54 procedure drop_table (p_table_name in varchar2) is
55 l_stmt varchar2(400);
56 Begin
57
58 l_stmt:='drop table '||g_fii_schema||'.'||p_table_name;
59 execute immediate l_stmt;
60
61 Exception
62 WHEN G_TABLE_NOT_EXIST THEN
63 null; -- Oracle 942, table does not exist, no actions
64 WHEN OTHERS THEN
65 raise;
66 End;
67
68
69 ---------------------------------------------------
70 -- PROCEDURE Create_OLTP_TRX_TMP_TABLE
71 ---------------------------------------------------
72 procedure Create_OLTP_TRX_TMP_TABLE is
73 l_stmt varchar2(400);
74 Begin
75
76 -- --------------------------------------------------------
77 -- Had to remove the parallel sub clause to avoid ora 7260
78 -- --------------------------------------------------------
79 l_stmt := 'create table '||g_fii_schema||'.FII_AR_OLTP_TMP_TRX_ID '||
80 '(trx_id NUMBER) tablespace '||g_tablespace||
81 ' PCTFREE 5 storage (INITIAL 4K NEXT 1M)';
82
83
84 execute immediate l_stmt;
85
86 End Create_OLTP_TRX_TMP_TABLE;
87
88 procedure Populate_OLTP_TRX_TMP_TABLE is
89 l_stmt varchar2(400);
90 Begin
91 -- --------------------------------------------------------
92 -- We cannot combine this procedure with Create_OLTP_TRX_TMP_TABLE
93 -- because we run into ora 2041. Need to sepate create table and
94 -- insert stmt into 2 separate transactions. This is true even
95 -- when we use create table as insert syntax. This is because
96 -- the insert clause references a database link
97 -- --------------------------------------------------------
98 l_stmt := 'insert into '||g_fii_schema||'.FII_AR_OLTP_TMP_TRX_ID '||
99 'select /*+ PARALLEL(TRX,10) */ '||
100 ' customer_trx_id trx_id '||
101 'from ra_customer_trx_all@'||g_db_link||' TRX';
102
103 execute immediate l_stmt;
104 commit;
105 end;
106
107
108 --------------------------------------------------
109 -- PROCEDURE Create_EDW_TRX_TMP_TABLE
110 ---------------------------------------------------
111 procedure Create_EDW_TRX_TMP_TABLE is
112 l_stmt VARCHAR2(1000);
113
114 Begin
115
116 -- --------------------------------------------------------
117 -- Had to remove the parallel sub clause to avoid ora 7260
118 -- --------------------------------------------------------
119 l_stmt :='create table '||g_fii_schema||'.FII_AR_EDW_TMP_TRX_ID '||
120 'tablespace '||g_tablespace||' parallel '||
121 'PCTFREE 5 storage (INITIAL 4K NEXT 1M) '||
122 'as select /*+ PARALLEL(F,10) */ '||
123 'distinct '||
124 'to_number(invoice_id) trx_id '||
125 'from fii_ar_trx_dist_f F '||
126 'where transaction_class <> ''ADJ'' '||
127 'and invoice_pk like ''%'||g_instance_code||'%'' ';
128
129 execute immediate l_stmt;
130 commit;
131
132 End Create_EDW_TRX_TMP_TABLE;
133
134
135 ---------------------------------------------------
136 -- PROCEDURE Find_Extra_Trx_EDW
137 ---------------------------------------------------
138
139 PROCEDURE Count_Extra_Trx_EDW (l_count OUT NOCOPY /* file.sql.39 change */ NUMBER) is
140 l_stmt VARCHAR2(100);
141
142 Begin
143 l_stmt := 'select count(*) from '||g_fii_schema||'.FII_AR_EDW_EXTRA_ID ';
144
145 execute immediate l_stmt into l_count;
146
147 End Count_Extra_Trx_EDW;
148
149 ---------------------------------------------------
150 -- PROCEDURE Find_Extra_Trx_EDW
151 ---------------------------------------------------
152 PROCEDURE Find_Extra_Trx_EDW is
153 l_stmt VARCHAR2(1000);
154 Begin
155
156 -- --------------------------------------------------------
157 -- Had to remove the parallel sub clause to avoid ora 7260
158 -- --------------------------------------------------------
159 l_stmt := 'create table '||g_fii_schema||'.FII_AR_EDW_EXTRA_ID '||
160 'tablespace '||g_tablespace||' parallel '||
161 'PCTFREE 5 storage (INITIAL 4K NEXT 1M) '||
162 'as select trx_id '||
163 'from '||g_fii_schema||'.FII_AR_EDW_TMP_TRX_ID '||
164 'where trx_id in '||
165 ' (select /*+ PARALLEL(EDW,5) */ trx_id '||
166 ' from '||g_fii_schema||'.FII_AR_EDW_TMP_TRX_ID EDW '||
167 ' minus '||
168 ' select /*+ PARALLEL(OLTP,5) */ trx_id '||
169 ' from '||g_fii_schema||'.FII_AR_OLTP_TMP_TRX_ID OLTP) ';
170
171 execute immediate l_stmt;
172
173 l_stmt := 'analyze table '||g_fii_schema||'.fii_ar_edw_extra_id estimate statistics';
174 execute immediate l_stmt;
175
176 commit;
177
178 End Find_Extra_Trx_EDW;
179
180
181 ---------------------------------------------------
182 -- PROCEDURE Insert_Staging
183 ---------------------------------------------------
184 PROCEDURE Insert_Staging (l_row OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
185 l_stmt varchar2(2000);
186
187 Begin
188
189
190 l_stmt :=
191 'insert into FII_AR_TRX_DIST_FSTG( '||
192 ' INVOICE_PK, '||
193 ' OPERATION_CODE, '||
194 ' COLLECTION_STATUS, '||
195 'INVOICE_ID, '||
196 ' ORIGINAL_INVOICE_ID, '||
197 ' ORIGINAL_INVOICE_LINE_ID, '||
198 ' END_USER_CUSTOMER_FK, '||
199 ' RESELLER_CUSTOMER_FK, '||
200 ' INVOICE_DATE_FK, '||
201 ' SALES_ORDER_DATE_FK, '||
202 ' INVOICE_LINE_ID, '||
203 ' FUNCTIONAL_CURRENCY_FK, '||
204 ' BILL_TO_CUSTOMER_FK, '||
205 ' BILL_TO_SITE_FK, '||
206 ' CAMPAIGN_ACTL_FK, '||
207 ' CAMPAIGN_INIT_FK, '||
208 ' CELL_ACTL_FK, '||
209 ' CELL_INIT_FK, '||
210 ' EVENT_OFFER_ACTL_FK, '||
211 ' EVENT_OFFER_INIT_FK, '||
212 ' EVENT_OFFER_REG_FK, '||
213 ' GL_ACCT10_FK, '||
214 ' GL_ACCT1_FK, '||
215 ' GL_ACCT2_FK, '||
216 ' GL_ACCT3_FK, '||
217 ' GL_ACCT4_FK, '||
218 ' GL_ACCT5_FK, '||
219 ' GL_ACCT6_FK, '||
220 ' GL_ACCT7_FK, '||
221 ' GL_ACCT8_FK, '||
222 ' GL_ACCT9_FK, '||
223 ' GL_DATE_FK , '||
224 ' SET_OF_BOOKS_FK, '||
225 ' INSTANCE_FK, '||
226 ' ITEM_FK, '||
227 ' MARKET_SEGMENT_FK, '||
228 ' MEDIA_ACTL_FK, '||
229 ' MEDIA_INIT_FK, '||
230 ' OFFER_ACTL_FK, '||
231 ' OFFER_INIT_FK, '||
232 ' ORGANIZATION_FK, '||
233 ' PARENT_ITEM_FK, '||
234 ' PAYMENT_TERM_FK, '||
235 ' PRIM_SALESREP_FK, '||
236 ' PROJECT_FK, '||
237 ' SALESCHANNEL_FK, '||
238 ' SALESREP_FK, '||
239 ' SHIP_TO_CUSTOMER_FK, '||
240 ' SHIP_TO_SITE_FK, '||
241 ' SIC_CODE_FK, '||
242 ' SOLD_TO_CUSTOMER_FK, '||
243 ' SOLD_TO_SITE_FK, '||
244 ' SOURCE_LIST_FK, '||
245 ' TRANSACTION_CURRENCY_FK, '||
246 ' UOM_FK, '||
247 ' USER_FK1, '||
248 ' USER_FK2, '||
249 ' USER_FK3, '||
250 ' USER_FK4, '||
251 ' USER_FK5, '||
252 ' CAMPAIGN_STATUS_INIT_FK, '||
253 ' CAMPAIGN_STATUS_ACTL_FK, '||
254 ' CREATION_DATE, '||
255 ' LAST_UPDATE_DATE) '||
256 'select '||
257 'f.invoice_pk, '||
258 '''DELETE'','||
259 '''READY'','||
260 '''NA_EDW'','||
261 '''NA_EDW'','||
262 '''NA_EDW'','||
263 '''NA_EDW'','||
264 '''NA_EDW'','||
265 '''NA_EDW'','||
266 '''NA_EDW'','||
267 '''NA_EDW'','||
268 '''NA_EDW'','||
269 '''NA_EDW'','||
270 '''NA_EDW'','||
271 '''NA_EDW'','||
272 '''NA_EDW'','||
273 '''NA_EDW'','||
274 '''NA_EDW'','||
275 '''NA_EDW'','||
276 '''NA_EDW'','||
277 '''NA_EDW'','||
278 '''NA_EDW'','||
279 '''NA_EDW'','||
280 '''NA_EDW'','||
281 '''NA_EDW'','||
282 '''NA_EDW'','||
283 '''NA_EDW'','||
284 '''NA_EDW'','||
285 '''NA_EDW'','||
286 '''NA_EDW'','||
287 '''NA_EDW'','||
288 '''NA_EDW'','||
289 '''NA_EDW'','||
290 '''NA_EDW'','||
291 '''NA_EDW'','||
292 '''NA_EDW'','||
293 '''NA_EDW'','||
294 '''NA_EDW'','||
295 '''NA_EDW'','||
296 '''NA_EDW'','||
297 '''NA_EDW'','||
298 '''NA_EDW'','||
299 '''NA_EDW'','||
300 '''NA_EDW'','||
301 '''NA_EDW'','||
302 '''NA_EDW'','||
303 '''NA_EDW'','||
304 '''NA_EDW'','||
305 '''NA_EDW'','||
306 '''NA_EDW'','||
307 '''NA_EDW'','||
308 '''NA_EDW'','||
309 '''NA_EDW'','||
310 '''NA_EDW'','||
311 '''NA_EDW'','||
312 '''NA_EDW'','||
313 '''NA_EDW'','||
314 '''NA_EDW'','||
315 '''NA_EDW'','||
316 '''NA_EDW'','||
317 '''NA_EDW'','||
318 '''NA_EDW'','||
319 'sysdate, '||
320 'sysdate '||
321 'from '||g_fii_schema||'.FII_AR_EDW_EXTRA_ID extra, '||
322 'FII_AR_TRX_DIST_F f '||
323 'where f.invoice_id = extra.trx_id '||
324 'and f.invoice_pk like ''%'||g_instance_code||'%'' ';
325
326 execute immediate l_stmt;
327 l_row := SQL%ROWCOUNT;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 g_errbuf :=sqlerrm;
332 g_retcode :=sqlcode;
333
334 End Insert_Staging;
335
336
337 End FII_AR_TRX_DIST_F_D;