1 PACKAGE BODY ISC_EDW_BACKLOGS_F_P AS
2 /* $Header: ISCF01PB.pls 115.5 2004/02/26 00:18:48 scheung ship $*/
3
4 g_dummy VARCHAR2(30);
5 g_dummy_int NUMBER;
6 cid NUMBER;
7 g_errbuf VARCHAR2(200) := NULL;
8 g_retcode NUMBER := 0;
9 l_exception_msg VARCHAR2(2000) :=NULL;
10
11 CURSOR snap_c IS
12 SELECT backlog.backlogs_pk c_pk,
13 nvl(time.cday_calendar_date,sysdate+1) c_date
14 FROM ISC_EDW_BACKLOGS_F backlog,
15 EDW_TIME_M time
16 WHERE backlog.date_balance_fk_key = time.cday_cal_day_pk_key
17 ORDER BY 2 DESC ;
18
19
20 -- we wish to purge the _F table:
21 -- #1. for a date range (day level) : from_date to to_date (higher priority than #2)
22 -- #2. if any of the dates from #1 are null, any snapshot taken prior n days from sysdate
23
24 PROCEDURE INSERT_FSTG( PK IN VARCHAR2) IS
25 BEGIN
26 cid := DBMS_SQL.Open_Cursor;
27 DBMS_SQL.Parse( cid,
28 'INSERT INTO ISC_EDW_BACKLOGS_FSTG (
29 BASE_UOM_FK,
30 BILL_TO_LOCATION_FK,
31 BILL_TO_CUST_FK,
32 CUSTOMER_FK,
33 DATE_BALANCE_FK,
34 DEMAND_CLASS_FK,
35 GL_BOOK_FK,
36 INSTANCE_FK,
37 INV_ORG_FK,
38 ORDER_CATEGORY_FK,
39 OPERATING_UNIT_FK,
40 ORDER_SOURCE_FK,
41 ITEM_ORG_FK,
42 TOP_MODEL_ITEM_FK,
43 TASK_FK,
44 SALES_CHANNEL_FK,
45 ORDER_TYPE_FK,
46 SALES_PERSON_FK,
47 SHIP_TO_LOCATION_FK,
48 SHIP_TO_CUST_FK,
49 TRX_CURRENCY_FK,
50 USER_FK1,
51 USER_FK2,
52 USER_FK3,
53 USER_FK4,
54 USER_FK5,
55 BACKLOGS_PK,
56 COLLECTION_STATUS,
57 OPERATION_CODE)
58 VALUES (
59 ''NA_EDW'',
60 ''NA_EDW'',
61 ''NA_EDW'',
62 ''NA_EDW'',
63 ''NA_EDW'',
64 ''NA_EDW'',
65 ''NA_EDW'',
66 ''NA_EDW'',
67 ''NA_EDW'',
68 ''NA_EDW'',
69 ''NA_EDW'',
70 ''NA_EDW'',
71 ''NA_EDW'',
72 ''NA_EDW'',
73 ''NA_EDW'',
74 ''NA_EDW'',
75 ''NA_EDW'',
76 ''NA_EDW'',
77 ''NA_EDW'',
78 ''NA_EDW'',
79 ''NA_EDW'',
80 ''NA_EDW'',
81 ''NA_EDW'',
82 ''NA_EDW'',
83 ''NA_EDW'',
84 ''NA_EDW'',
85 '''||pk||''' ,
86 ''READY'',
87 ''DELETE'')',
88 DBMS_SQL.Native);
89 g_dummy_int:=DBMS_SQL.Execute(cid);
90 DBMS_SQL.Close_Cursor(cid);
91
92 EXCEPTION WHEN OTHERS THEN
93 DBMS_SQL.Close_Cursor(cid);
94 g_errbuf := sqlerrm;
95 g_retcode := -1;
96 l_exception_msg := g_retcode || ':' || g_errbuf;
97 ROLLBACK;
98 EDW_LOG.Put_Line('Other errors in Insert_Fstg : '|| l_exception_msg);
99 RAISE;
100
101 END INSERT_FSTG;
102
103
104 PROCEDURE DELETE_FACT( Errbuf IN OUT NOCOPY VARCHAR2,
105 Retcode IN OUT NOCOPY VARCHAR2,
106 p_nb_days IN NUMBER,
107 p_from_date IN VARCHAR2,
108 p_to_date IN VARCHAR2) IS
109
110 l_from_date DATE := NULL;
111 l_to_date DATE := NULL;
112 l_count NUMBER := 0;
113
114 BEGIN
115 Errbuf := NULL;
116 Retcode := '0';
117 IF (p_from_date IS NOT NULL AND p_to_date IS NOT NULL)
118 THEN
119 BEGIN
120 l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
121 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
122
123 FOR snap_rec IN snap_c
124 LOOP
125 IF snap_rec.c_date BETWEEN l_from_date AND l_to_date
126 THEN
127 BEGIN
128 INSERT_FSTG(snap_rec.c_pk);
129 l_count := l_count + 1;
130 END;
131 END IF;
132 END LOOP;
133 EDW_LOG.Put_Line('Marking '||l_count||' rows to be deleted from the Backlog Fact during next load');
134 EDW_LOG.Put_Line('All snapshots taken between '||l_from_date||' and '||l_to_date||' will be deleted.'); COMMIT;
135 END;
136 ELSE
137 BEGIN
138 IF p_nb_days < 0
139 THEN EDW_LOG.Put_Line('Please enter a positive number for the Number of Days');
140 ELSE
141 BEGIN
142 IF p_nb_days IS NULL
143 THEN EDW_LOG.Put_Line('All parameters are NULL,
144 please enter the following parameters :
145
146 - both "FROM DATE" and "TO DATE", corresponding to the periode you want to PURGE,
147 or
148 - the "NUMBER OF DAYS" from today, corresponding to the period that you want to KEEP');
149 ELSE
150 BEGIN
151 FOR snap_rec IN snap_c
152 LOOP
153 IF snap_rec.c_date < (sysdate - p_nb_days)
154 THEN
155 INSERT_FSTG(snap_rec.c_pk);
156 l_count := l_count + 1;
157 END IF;
158 END LOOP;
159 EDW_LOG.Put_Line('Marking '||l_count||' rows to be deleted from the Backlog Fact during next load.');
160 EDW_LOG.Put_Line('All snapshots taken prior to '||(sysdate - p_nb_days)||' will be deleted.');
161 END;
162 COMMIT;
163 END IF;
164 END;
165 END IF;
166 END;
167 END IF;
168
169 EXCEPTION WHEN OTHERS THEN
170 DBMS_SQL.Close_Cursor(cid);
171 g_errbuf := sqlerrm;
172 g_retcode := -1;
173 l_exception_msg := g_retcode || ':' || g_errbuf;
174 ROLLBACK;
175 EDW_LOG.Put_Line('Other errors in Delete_Fact : '|| l_exception_msg);
176 RAISE;
177
178 END DELETE_FACT;
179
180
181 END ISC_EDW_BACKLOGS_F_P;
[Home] [Help]