[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_SUP_PERF_HOOK
Source
1 PACKAGE BODY EDW_POA_SUP_PERF_HOOK as
2 /*$Header: poahkspb.pls 115.12 2002/01/24 17:54:35 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 Supplier performance 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_SUP_PERF_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_SUP_PERF_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
39 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating target_price_t...');
40
41 Update POA_EDW_SUP_PERF_FSTG
42 set target_price_t = DECODE(price_g,
43 to_number(NULL), to_number(NULL),
44 0, to_number(NULL),
45 target_price_g * price_t / price_g)
46 where collection_status = 'READY';
47 ELSE
48 EDW_OWB_COLLECTION_UTIL.write_to_log_file('DNB is disabled');
49 END IF;
50
51 return true;
52 end Pre_Fact_Collect;
53
54 END EDW_POA_SUP_PERF_HOOK;
55