1 PACKAGE BODY EDW_POA_ALINES_HOOK as
2 /*$Header: poahkalb.pls 115.13 2002/01/25 14:02:33 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 Agreement 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_ALINES_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_Revision_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_Revision_FK IN
22 (select IRev.Item_Revision_PK
23 from EdW_Item_ItemRev_LTC IRev,
24 EdW_Item_ItemOrg_LTC IOrg,
25 POA_DNB_ITEMS Com
26 where (Com.Item_PK = IOrg.Item_Number_FK) and
27 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
28
29 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Staging Tables to reference DUNS and SIC Code Dimension');
30 Update POA_EDW_ALINES_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_ALINES_HOOK;
46
[Home] [Help]