eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ASO_BI_APPR_FACT_PVT

Source


1 PACKAGE BODY ASO_BI_APPR_FACT_PVT  AS
2  /* $Header: asovbiapb.pls 120.3 2006/08/10 05:56:02 vselvapr noship $ */
3 
4  --procedure for the initial load of approval facts
5  Procedure Appr_Init_Load
6  As
7  l_user_id number;
8  l_login_id number;
9  Begin
10 
11  BIS_COLLECTION_UTILITIES.Debug('Start populating the Approvals fact Table');
12 
13  l_user_id := FND_GLOBAL.user_id;
14  l_login_id := FND_GLOBAL.login_id;
15  --insert statement
16 
17 	INSERT /*+ APPEND PARALLEL(FACT) */ INTO ASO_BI_APR_F FACT
18 	(
19 		QUOTE_NUMBER,
20 		QUOTE_VERSION,
21 		QUOTE_HEADER_ID,
22 		RESOURCE_ID,
23 		RESOURCE_GRP_ID,
24 		OBJECT_APPROVAL_ID,
25 		APPROVAL_INSTANCE_ID,
26 		APPROVAL_STATUS,
27 		NUM_APPROVERS,
28 		APR_START_DATE,
29 		APR_END_DATE,
30 		QA_END_DATE,
31 		QUOTE_CREATION_DATE,
32 		QUOTE_LAST_UPDATE_DATE,
33 		QUOTE_EXPIRATION_DATE,
34     creation_date,
35     created_by,
36     last_update_date,
37     last_updated_by,
38     LAST_UPDATE_LOGIN
39 	)
40   SELECT /*+ PARALLEL(QOT) PARALLEL(APR) PARALLEL(APRD) */
41 		QOT.Quote_Number,
42 		QOT.Quote_Version,
43 		QOT.Quote_header_id,
44 		QOT.Resource_id,
45 		QOT.Resource_grp_id,
46 		APR.Object_approval_id,
47 		APR.Approval_instance_id,
48 		APR.Approval_status,
49 		COUNT(*) NUM_APPROVERS,
50 		TRUNC(APR.Start_date)  Apr_start_date ,
51 		TRUNC(APR.End_date)  Apr_end_date ,
52 		TRUNC(APR.end_date)  QA_end_date,
53 		QOT.Quote_creation_date,
54 		QOT.Quote_last_update_date,
55 		QOT.Quote_expiration_date,
56 		SYSDATE,
57 		l_user_id,
58 		SYSDATE,
59     l_user_id,
60 		l_login_id
61 		FROM  ASO_BI_QUOTE_HDRS_ALL QOT,
62 			ASO_APR_OBJ_APPROVALS APR,
63 			ASO_APR_APPROVAL_DETAILS APRD
64 		WHERE
65 			QOT.quote_header_id = APR.object_id
66 			AND APR.object_type = 'Quote'
67 			AND APRD.object_approval_id = APR.object_approval_id
68                AND QOT.recurring_charge_flag = 'N'
69                AND APR.approval_instance_id = (select max(approval_instance_id)
70 		                                     from ASO_APR_OBJ_APPROVALS A
71                                                where A.Object_id = QOT.Quote_header_id)
72 		GROUP BY
73 			QOT.Quote_Number, QOT.Quote_Version,
74 			QOT.Quote_creation_date,QOT.Quote_last_update_date,
75 			QOT.Quote_header_id,
76 			QOT.Resource_id,QOT.Resource_grp_id,
77 			QOT.Quote_expiration_date,
78 			APR.Object_approval_id,APR.Approval_instance_id,APR.Approval_status,
79 			TRUNC(APR.Start_date),APR.End_date,TRUNC(NVL(APR.end_date, QOT.Quote_expiration_date));
80 
81   BIS_COLLECTION_UTILITIES.Debug('Done populating the Approvals fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
82 
83   COMMIT;
84 
85 	end Appr_Init_Load;
86 
87 
88 --Procedure for the incremental load of approval facts
89  Procedure Appr_Incremental_Load
90  As
91  l_user_id number;
92  l_login_id number;
93  Begin
94 
95  BIS_COLLECTION_UTILITIES.Debug('Start populating the Approvals fact Table');
96 
97  l_user_id := FND_GLOBAL.user_id;
98  l_login_id := FND_GLOBAL.login_id;
99 
100   --Delete the modified approvals
101   DELETE FROM ASO_BI_APR_F FACT
102   WHERE FACT.Quote_header_id IN (SELECT Quote_header_id FROM  ASO_BI_QUOTE_IDS);
103 
104   --insert statement
105 	INSERT INTO ASO_BI_APR_F
106 	(
107 		QUOTE_NUMBER,
108 		QUOTE_VERSION,
109 		QUOTE_HEADER_ID,
110 		RESOURCE_ID,
111 		RESOURCE_GRP_ID,
112 		OBJECT_APPROVAL_ID,
113 		APPROVAL_INSTANCE_ID,
114 		APPROVAL_STATUS,
115 		NUM_APPROVERS,
116 		APR_START_DATE,
117 		APR_END_DATE,
118 		QA_END_DATE,
119 		QUOTE_CREATION_DATE,
120 		QUOTE_LAST_UPDATE_DATE,
121 		QUOTE_EXPIRATION_DATE,
122     creation_date,
123     created_by,
124     last_update_date,
125     last_updated_by,
126     LAST_UPDATE_LOGIN
127 	)
128 	SELECT
129 		QOT.Quote_Number,
130 		QOT.Quote_Version,
131 		QOT.Quote_header_id,
132 		QOT.Resource_id,
133 		QOT.Resource_grp_id,
134 		APR.Object_approval_id,
135 		APR.Approval_instance_id,
136 		APR.Approval_status,
137 		COUNT(*) NUM_APPROVERS,
138 		TRUNC(APR.Start_date)  Apr_start_date ,
139 		TRUNC(APR.End_date)  Apr_end_date ,
140 		TRUNC(APR.end_date)  QA_end_date,
141 		QOT.Quote_creation_date,
142 		QOT.Quote_last_update_date,
143 		QOT.Quote_expiration_date,
144 		SYSDATE,
145 		l_user_id,
146 		SYSDATE,
147     l_user_id,
148 		l_login_id
149 		FROM  ASO_BI_QUOTE_HDRS_ALL QOT,
150 			ASO_APR_OBJ_APPROVALS APR,
151 			ASO_APR_APPROVAL_DETAILS APRD,
152                   ASO_BI_QUOTE_IDS QID
153 		WHERE
154 			     QOT.quote_header_id = APR.object_id
155 			AND  APR.object_type = 'Quote'
156 			AND  QID.quote_header_id = APR.object_id
157 			AND  APRD.object_approval_id = APR.object_approval_id
158                   AND  QOT.recurring_charge_flag = 'N'
159                AND APR.approval_instance_id = (select max(approval_instance_id)
160 		                                     from ASO_APR_OBJ_APPROVALS A
161                                                where A.Object_id = QOT.Quote_header_id)
162 		GROUP BY
163 			QOT.Quote_Number, QOT.Quote_Version,
164 			QOT.Quote_creation_date,QOT.Quote_last_update_date,
165 			QOT.Quote_header_id,
166 			QOT.Resource_id,QOT.Resource_grp_id,
167 			QOT.Quote_expiration_date,
168 			APR.Object_approval_id,APR.Approval_instance_id,APR.Approval_status,
169 			TRUNC(APR.Start_date),APR.End_date,TRUNC(NVL(APR.end_date, QOT.Quote_expiration_date));
170 
171    -- Added to fix bug 5413781
172    --Delete the duplicate version of all quotes in ASO_BI_APR_F, retaining the latest version.
173     	     delete from ASO_BI_APR_F a
174 		where a.quote_version < (select MAX(quote_version)
175 	                     		from ASO_BI_APR_F b
176 						  	where a.quote_number = b.quote_number);
177 
178   BIS_COLLECTION_UTILITIES.Debug('Done populating the Approvals fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
179 
180   COMMIT;
181 
182 
183 	end Appr_Incremental_Load;
184 
185 
186 
187 
188  --for the initial load of aproval rules fact
189  Procedure Rul_Init_load
190  As
191  l_user_id number;
192  l_login_id number;
193  Begin
194 
195  BIS_COLLECTION_UTILITIES.Debug('Start populating the Rules fact Table');
196 
197  l_user_id := FND_GLOBAL.user_id;
198  l_login_id := FND_GLOBAL.login_id;
199 
200  --insert statement
201 	INSERT /*+ APPEND PARALLEL(FACT) */ INTO ASO_BI_APR_RUL_F FACT
202 	(
203 		QUOTE_NUMBER,
204 		QUOTE_VERSION,
205 		QUOTE_HEADER_ID,
206 		OBJECT_APPROVAL_ID,
207 		RESOURCE_ID,
208 		RESOURCE_GRP_ID,
209 		APPROVAL_STATUS,
210 		OAM_RULE_ID,
211 		QUOTE_CREATION_DATE,
212 		QUOTE_LAST_UPDATE_DATE,
213 		QUOTE_EXPIRATION_DATE,
214 		APR_START_DATE,
215 		APR_END_DATE,
216 		QA_END_DATE,
217 		CREATION_DATE,
218 		CREATED_BY,
219 		LAST_UPDATE_DATE,
220 		LAST_UPDATED_BY,
221 		LAST_UPDATE_LOGIN
222 	)
223 	SELECT /*+ PARALLEL(APRF) PARALLEL(RUL) */
224 		APRF.Quote_Number,
225 		APRF.Quote_Version,
226 		APRF.Quote_header_id,
227 		APRF.Object_approval_id,
228 		APRF.Resource_id,
229 		APRF.Resource_grp_id,
230 		APRF.Approval_status,
231 		RUL.oam_rule_id,
232 		APRF.Quote_creation_date,
233 		APRF.Quote_last_update_date,
234 		APRF.Quote_expiration_date ,
235 		APRF.Apr_start_date ,
236 		APRF.Apr_end_date  ,
237 		APRF.apr_end_date,
238 		SYSDATE,
239 		l_user_id,
240 		SYSDATE,
241 		l_user_id,
242 		l_login_id
243 		FROM
244 			ASO_BI_APR_F APRF,
245 			ASO_APR_RULES RUL
246 		WHERE
247 			APRF.object_approval_id = RUL.object_approval_id;
248 
249 
250   BIS_COLLECTION_UTILITIES.Debug('Done populating the Rules fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
251 
252   COMMIT;
253 
254 
255 	end Rul_Init_load;
256 
257 
258 --for the incremental load of aproval rules fact
259  Procedure Rul_Incremental_load
260  As
261  l_user_id number;
262  l_login_id number;
263  Begin
264 
265  BIS_COLLECTION_UTILITIES.Debug('Start populating the Rules fact Table');
266 
267  l_user_id := FND_GLOBAL.user_id;
268  l_login_id := FND_GLOBAL.login_id;
269 
270   --Delete the modified approvals
271   DELETE FROM ASO_BI_APR_RUL_F FACT
272   WHERE FACT.Quote_header_id IN (SELECT Quote_header_id FROM  ASO_BI_QUOTE_IDS);
273 
274   --insert statement
275 	INSERT INTO ASO_BI_APR_RUL_F
276 	(
277 		QUOTE_NUMBER,
278 		QUOTE_VERSION,
279 		QUOTE_HEADER_ID,
280 		OBJECT_APPROVAL_ID,
281 		RESOURCE_ID,
282 		RESOURCE_GRP_ID,
283 		APPROVAL_STATUS,
284 		OAM_RULE_ID,
285 		QUOTE_CREATION_DATE,
286 		QUOTE_LAST_UPDATE_DATE,
287 		QUOTE_EXPIRATION_DATE,
288 		APR_START_DATE,
289 		APR_END_DATE,
290 		QA_END_DATE,
291 		CREATION_DATE,
292 		CREATED_BY,
293 		LAST_UPDATE_DATE,
294 		LAST_UPDATED_BY,
295 		LAST_UPDATE_LOGIN
296 	)
297 	SELECT
298 		APRF.Quote_Number,
299 		APRF.Quote_Version,
300 		APRF.Quote_header_id,
301 		APRF.Object_approval_id,
302 		APRF.Resource_id,
303 		APRF.Resource_grp_id,
304 		APRF.Approval_status,
305 		RUL.oam_rule_id,
306 		APRF.Quote_creation_date,
307 		APRF.Quote_last_update_date,
308 		APRF.Quote_expiration_date ,
309 		APRF.Apr_start_date ,
310 		APRF.Apr_end_date  ,
311 		APRF.apr_end_date,
312 		SYSDATE,
313 		l_user_id,
314 		SYSDATE,
315 		l_user_id,
316 		l_login_id
317 		FROM
318 			ASO_BI_APR_F APRF,
319 			ASO_APR_RULES RUL,
320       ASO_BI_QUOTE_IDS QID
321 		WHERE
322 			APRF.Object_approval_id = RUL.Object_approval_id
323       AND QID.Quote_header_id = APRF.Quote_header_id;
324 
325 
326   BIS_COLLECTION_UTILITIES.Debug('Done populating the Rules fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
327 
328   COMMIT;
329 
330 	END Rul_Incremental_load;
331 
332 
333 END ASO_BI_APPR_FACT_PVT ;