[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_CSTM_MSR_HOOK
Source
1 PACKAGE BODY EDW_POA_CSTM_MSR_HOOK as
2 /*$Header: poahkcmb.pls 115.12 2002/01/24 17:54:20 pkm ship $ */
3
4 function Pre_Fact_Collect(p_object_name varchar2) return boolean is
5 begin
6 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Entering Custom Measure Pre Fact Hook Procedure');
7
8 IF (fnd_profile.value('POA_DNB_HOOKS') = 'N') THEN
9 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Staging Table to reference UNSPSC Dimension');
10
11 Update POA_EDW_CSTM_MSR_FSTG Fact
12 set UNSPSC_FK =
13 (select Com.Function
14 from EdW_Item_ItemRev_LTC IRev,
15 EdW_Item_ItemOrg_LTC IOrg,
16 POA_DNB_ITEMS Com
17 where ((Fact.Item_FK = IRev.Item_Revision_PK) and
18 (Com.Item_PK = IOrg.Item_Number_FK) and
19 (IOrg.Item_Org_PK = IRev.Item_Org_FK)))
20 where ((Fact.Collection_Status = 'READY') and
21 (Fact.Item_FK IN (select IRev.Item_Revision_PK
22 from EdW_Item_ItemRev_LTC IRev,
23 EdW_Item_ItemOrg_LTC IOrg,
24 POA_DNB_ITEMS Com
25 where (Com.Item_PK = IOrg.Item_Number_FK) and
26 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
27
28 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Staging Tables to reference DUNS and SIC Code Dimension');
29
30 Update POA_EDW_CSTM_MSR_FSTG Fact
31 set (DUNS_FK, SIC_CODE_FK) =
32 (select Com.DUNS, Com.SIC_Code
33 from POA_DNB_TRD_PRTNR Com
34 where (Fact.Supplier_Site_FK = Com.Trading_Partner_PK))
35 where ((Fact.Collection_Status = 'READY') and
36 (Fact.Supplier_Site_FK IN (select Trading_Partner_PK
37 from POA_DNB_TRD_PRTNR)));
38 ELSE
39 EDW_OWB_COLLECTION_UTIL.write_to_log_file('DNB is disabled');
40 END IF;
41
42 return true;
43 end Pre_Fact_Collect;
44
45 END EDW_POA_CSTM_MSR_HOOK;
46