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