PACKAGE BODY: APPS.FII_PROJECT_HOOK

Source

1 package body FII_PROJECT_HOOK as
2 /* $Header: FIIPA17B.pls 120.0 2002/08/24 05:00:40 appldev noship $ */
3 
4 -- ----------------------------
5 -- function PRE_FACT_COLL
6 -- ----------------------------
7 
8 function pre_dimension_coll return boolean is
9 
10   --most recently changed tasks
11 
12   cursor the_scope is
13   select
14     lstg.task_pk
15   from
16     (
17       select
18         task_pk,
19         denorm_task_org_fk
20       from
21         edw_proj_task_lstg
22       where
23         ( task_pk, creation_date ) in
24         (
25           select
26             task_pk              task_pk,
27             max(creation_date)   creation_date
28           from
29             edw_proj_task_lstg
30           where
31                 collection_status = 'READY'
32             and edw_record_type   = 'ORACLE'
33           group by
34             task_pk
35         )
36     )                   lstg,
37     edw_proj_task_ltc   ltc
38   where
39         ltc.task_pk = lstg.task_pk
40     and nvl(ltc.denorm_task_org_fk, 'NULL') <> nvl( lstg.denorm_task_org_fk, 'NULL' );
41 
42 begin
43 
44   for c in the_scope loop
45 
46     -- create log record for Project Cost Fact
47     insert into fii_system_event_log
48     (
49       EVENT_TYPE,
50       EVENT_ID,
51       EVENT_OBJECT,
52       ATTRIBUTE1,
53       STATUS
54     )
55     values
56     (
57       'DNRM:FII_PA_COST_F',
58       fii_system_event_log_s.nextval,
59       'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK',
60       c.task_pk,
61       'READY'
62     );
63 
64     -- create log record for Project Revenue Fact
65 
66     insert into fii_system_event_log
67     (
68       EVENT_TYPE,
69       EVENT_ID,
70       EVENT_OBJECT,
71       ATTRIBUTE1,
72       STATUS
73     )
74     values
75     (
76       'DNRM:FII_PA_REVENUE_F',
77       fii_system_event_log_s.nextval,
78       'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK',
79       c.task_pk,
80       'READY'
81     );
82 
83     -- create log record for Project Budget Fact
84 
85     insert into fii_system_event_log
86     (
87       EVENT_TYPE,
88       EVENT_ID,
89       EVENT_OBJECT,
90       ATTRIBUTE1,
91       STATUS
92     )
93     values
94     (
95       'DNRM:FII_PA_BUDGET_F',
96       fii_system_event_log_s.nextval,
97       'EDW_PROJ_TASK_LTC.DENORM_TASK_ORG_FK',
98       c.task_pk,
99       'READY'
100     );
101 
102   end loop;
103 
104   commit;
105 
106   return true;
107 
108 exception
109   when others then
110     rollback;
111     return false;
112 end pre_dimension_coll;
113 
114 end;