[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_POPULATE_APPR_FACTS
Source
1 PACKAGE BODY ASO_BI_POPULATE_APPR_FACTS AS
2 /* $Header: asovbiapfb.pls 115.3 2003/11/06 10:28:38 rkoratag noship $ */
3
4 -- 1 second
5 ONE_SECOND CONSTANT NUMBER := 0.000011574;
6
7 --
8 -- Procedure for initial load of approvals
9 --
10 PROCEDURE Init_Load_Appr( errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER,
12 p_from_date IN VARCHAR2,
13 p_to_date IN VARCHAR2 )
14 AS
15 l_from_date Date ;
16 l_to_date Date;
17 l_missing_date Boolean := FALSE;
18 BEGIN
19 retcode := 0 ;
20
21 --Refresh Log
22 BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_POPULATE_APPR_FACTS');
23
24
25 IF(BIS_COLLECTION_UTILITIES.Setup(
26 p_object_name => 'ASO_BI_POPULATE_APPR_FACTS') = false)
27 Then
28 errbuf := FND_MESSAGE.Get;
29 retcode := -1;
30 RAISE_APPLICATION_ERROR(-20000,errbuf);
31 End if;
32
33 BIS_COLLECTION_UTILITIES.debug('Start Initial Load for approvals and rules Fact');
34
35 -- Initialize
36 BIS_COLLECTION_UTILITIES.debug('Initialization');
37
38 ASO_BI_UTIL_PVT.INIT;
39
40 -- Truncate the processing tables
41 BIS_COLLECTION_UTILITIES.debug('Cleaning up the tables before processing starts.');
42
43 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
44
45 --As this is a initial load the Base Fact Table is assumed to be empty
46 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_APR_F');
47 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_APR_RUL_F');
48
49 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
50 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 - ONE_SECOND;
51
52 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
53 p_to_date => l_to_date,
54 p_has_missing_date => l_missing_date);
55
56 If(l_missing_date) Then
57 Retcode := -1;
58 Return;
59 End If;
60
61 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
62 p_from_date || ' to ' || p_to_date);
63
64 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
65 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
66
67 ASO_BI_QUOTE_FACT_PVT.InitLoad_Quote_Ids(
68 p_from_date => l_from_date,
69 p_to_date => l_to_date) ;
70
71 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
72 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
73
74 -- Call the initial load
75 ASO_BI_APPR_FACT_PVT.Appr_Init_Load;
76 ASO_BI_APPR_FACT_PVT.Rul_Init_Load;
77
78 BIS_COLLECTION_UTILITIES.wrapup(
79 p_status => TRUE ,
80 p_count => 0,
81 p_period_from => l_from_date,
82 p_period_to => l_to_date);
83
84 retcode := 0;
85 EXCEPTION
86 WHEN OTHERS THEN
87 retcode := -1;
88 errbuf := sqlerrm;
89 BIS_COLLECTION_UTILITIES.Debug('Error in Initial Load of APPROVALS AND RULES Fact:'||errbuf);
90
91 BIS_COLLECTION_UTILITIES.wrapup(
92 p_status => FALSE ,
93 p_message => sqlerrm,
94 p_count => 0,
95 p_period_from => l_from_date,
96 p_period_to => l_to_date);
97 RAISE;
98 end Init_Load_Appr;
99
100
101 --
102 -- Procedure for incremental load of approvals
103 --
104 PROCEDURE Incr_Load_Appr( errbuf OUT NOCOPY VARCHAR2,
105 retcode OUT NOCOPY NUMBER,
106 p_from_date IN VARCHAR2,
107 p_to_date IN VARCHAR2 )
108 AS
109 l_from_date Date ;
110 l_to_date Date;
111 l_missing_date Boolean := FALSE;
112 BEGIN
113 retcode := 0 ;
114
115 --Refresh Log
116 BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_POPULATE_APPR_FACTS');
117
118
119 IF(BIS_COLLECTION_UTILITIES.Setup(
120 p_object_name => 'ASO_BI_POPULATE_APPR_FACTS') = false)
121 Then
122 errbuf := FND_MESSAGE.Get;
123 retcode := -1;
124 RAISE_APPLICATION_ERROR(-20000,errbuf);
125 End if;
126
127 BIS_COLLECTION_UTILITIES.debug('Start Initial Load for approvals and rules Fact');
128
129 -- Initialize
130 BIS_COLLECTION_UTILITIES.debug('Initialization');
131
132 ASO_BI_UTIL_PVT.INIT;
133
134 -- Truncate the processing tables
135 BIS_COLLECTION_UTILITIES.debug('Cleaning up the tables before processing starts.');
136 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
137
138 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
139 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 - ONE_SECOND;
140
141 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
142 p_to_date => l_to_date,
143 p_has_missing_date => l_missing_date);
144
145 If(l_missing_date) Then
146 Retcode := -1;
147 Return;
148 End If;
149
150 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
151 p_from_date || ' to ' || p_to_date);
152
153 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
154 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
155
156 -- Pick up all the modified quotes 4 d period
157 ASO_BI_QUOTE_FACT_PVT.InitLoad_Quote_Ids(
158 p_from_date => l_from_date,
159 p_to_date => l_to_date) ;
160
161 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
162 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
163
164 ASO_BI_APPR_FACT_PVT.Appr_Incremental_Load;
165 ASO_BI_APPR_FACT_PVT.Rul_Incremental_Load;
166
167
168 BIS_COLLECTION_UTILITIES.wrapup(
169 p_status => TRUE ,
170 p_count => 0,
171 p_period_from => l_from_date,
172 p_period_to => l_to_date);
173
174 retcode := 0;
175 EXCEPTION
176 WHEN OTHERS THEN
177 retcode := -1;
178 errbuf := sqlerrm;
179 BIS_COLLECTION_UTILITIES.Debug('Error in Initial Load of APPROVALS AND RULES Fact:'||errbuf);
180
181 BIS_COLLECTION_UTILITIES.wrapup(
182 p_status => FALSE ,
183 p_message => sqlerrm,
184 p_count => 0,
185 p_period_from => l_from_date,
186 p_period_to => l_to_date);
187 RAISE;
188 end Incr_Load_Appr;
189
190 END ASO_BI_POPULATE_APPR_FACTS;