PACKAGE BODY: APPS.EDW_POA_DIST_HOOK

Source

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