PACKAGE BODY: APPS.EDW_POA_RCV_TXNS_HOOK

Source

1 PACKAGE BODY EDW_POA_RCV_TXNS_HOOK as
2 /*$Header: poahkrvb.pls 115.12 2002/01/24 17:54:30 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 Recieving  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_RCV_TXNS_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_Revision_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_Revision_FK IN (
23                       select IRev.Item_Revision_PK
24                       from EdW_Item_ItemRev_LTC IRev,
25                            EdW_Item_ItemOrg_LTC IOrg,
26                            POA_DNB_ITEMS Com
27                       where (Com.Item_PK = IOrg.Item_Number_FK) and
28                             (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
29 
30    -- Update DUNS
31      EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Staging Tables to reference DUNS and SIC Code Dimension');
32 
33      Update POA_EDW_RCV_TXNS_FSTG Fact
34      set (DUNS_FK, SIC_CODE_FK) =
35         (select Com.DUNS, Com.SIC_Code
36          from POA_DNB_TRD_PRTNR Com
37          where (Fact.Supplier_Site_FK = Com.Trading_Partner_PK))
38      where ((Fact.Collection_Status = 'READY') and
39             (Fact.Supplier_Site_FK IN (select Trading_Partner_PK
40                                        from POA_DNB_TRD_PRTNR)));
41   ELSE
42     EDW_OWB_COLLECTION_UTIL.write_to_log_file('DNB is disabled');
43   END IF;
44 
45   return true;
46 
47 end Pre_Fact_Collect;
48 
49 end EDW_POA_RCV_TXNS_HOOK;
50