1 package body FII_PA_COST_HOOK as
2 /* $Header: FIIPA15B.pls 120.0 2002/08/24 05:00:26 appldev noship $ */
3
4 -- ----------------------------
5 -- function PRE_FACT_COLL
6 -- ----------------------------
7
8 function pre_fact_coll return boolean is
9
10 cursor task_c is
11 select attribute1, attribute2, attribute3, attribute4
12 from fii_system_event_log log
13 where log.event_type = 'DNRM:FII_PA_COST_F'
14 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
15 and log.status = 'PROCESSING';
16
17 begin
18
19 -- Step 1. Define the scope of pre-processing. Changing status to
20 -- 'PROCESSING' guarantees that subsequent steps will work on
21 -- the same set of data ignoring any new records that may be
22 -- concurrently created in the Log table by the Project Dimension
23 -- collection process.
24
25 update fii_system_event_log log
26 set status = 'PROCESSING'
27 where log.event_type = 'DNRM:FII_PA_COST_F'
28 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
29 and log.status = 'READY';
30
31
32 -- Step 2. remove redundant events from the event log table, i.e. leave only
33 -- the latest event for each task
34
35 delete from fii_system_event_log log
36 where log.event_type = 'DNRM:FII_PA_COST_F'
37 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
38 and log.status = 'PROCESSING'
39 and log.event_id not in
40 (
41 select max( event_id )
42 from fii_system_event_log log
43 where log.event_type = 'DNRM:FII_PA_COST_F'
44 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
45 and log.status = 'PROCESSING'
46 );
47
48 -- Step 3. update log table with task and org data
49
50 update fii_system_event_log log
51 set (attribute2, attribute3, attribute4) =
52 (
53 select to_char(task.task_pk_key), task.denorm_task_org_fk, to_char(org.organization_pk_key)
54 from edw_proj_task_ltc task,
55 edw_orga_org_ltc org
56 where log.attribute1 = task.task_pk
57 and task.denorm_task_org_fk = org.organization_pk
58 )
59 where log.event_type = 'DNRM:FII_PA_COST_F'
60 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
61 and log.status = 'PROCESSING';
62
63 -- Step 4. update PROJECT_ORG_FK in the fact staging table
64 -- with Task Owning Organization from the Task Level table
65
66 update fii_pa_cost_fstg fstg
67 set project_org_fk =
68 (
69 select denorm_task_org_fk
70 from edw_proj_task_ltc task
71 where fstg.project_fk = task.task_pk
72 )
73 where fstg.collection_status = 'READY'
74 and fstg.edw_record_type = 'ORACLE';
75
76 -- Step 5. update PROJECT_ORG_FK in the fact staging table
77 -- with Task Owning Organization from the Log table. This step is required to
78 -- overrride possible changes to DENORM_TASK_ORG_FK made in Task Level
79 -- table by Project Dimension collection process between Steps 3 and 4.
80 -- Step 5 guarantees that FSTG and F tables are always in sync in
81 -- respect to the LTC tables.
82
83 update fii_pa_cost_fstg fstg
84 set project_org_fk =
85 (
86 select attribute3
87 from fii_system_event_log log
88 where log.event_type = 'DNRM:FII_PA_COST_F'
89 and log.event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
90 and log.status = 'PROCESSING'
91 and fstg.project_fk = log.attribute1
92 )
93 where collection_status = 'READY'
94 and edw_record_type = 'ORACLE'
95 and project_fk in
96 (
97 select attribute1
98 from fii_system_event_log
99 where event_type = 'DNRM:FII_PA_COST_F'
100 and event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
101 and status = 'PROCESSING'
102 );
103
104
105 -- Step 6. update fact table
106
107 for c in task_c loop
108 if ( c.attribute2 is not null ) and ( c.attribute4 is not null ) then
109 update fii_pa_cost_f fact
110 set fact.project_org_fk_key = to_number( c.attribute4 )
111 where fact.project_fk_key = to_number( c.attribute2 );
112 end if;
113 end loop;
114
115 -- Step 7. delete processed records from the Log table
116
117 delete from fii_system_event_log
118 where event_type = 'DNRM:FII_PA_COST_F'
119 and event_object = 'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK'
120 and status = 'PROCESSING';
121
122 -- Step 8. commit changes.
123
124 commit;
125
126 return true;
127
128 exception
129 when others then
130 rollback;
131 return false;
132 end pre_fact_coll;
133
134 end;
[Home] [Help]