1 PACKAGE BODY EDW_SICM_INV_LINES_HOOK as
2 /*$Header: FIIAILHB.pls 120.1 2003/07/24 06:37:05 sgautam ship $ */
3
4 FUNCTION Pre_Fact_Collect(p_object_name varchar2) RETURN BOOLEAN IS
5 begin
6 -- Update DUNS
7 Update FII_AP_INV_LINES_FSTG Fact
8 set (DUNS_FK, SIC_CODE_FK) =
9 (select Com.DUNS, Com.SIC_Code
10 from POA_DNB_TRD_PRTNR Com
11 where (Fact.Supplier_FK = Com.Trading_Partner_PK))
12 where ((Fact.Collection_Status = 'READY') and
13 (Fact.Supplier_FK IN (select Trading_Partner_PK
14 from POA_DNB_TRD_PRTNR)));
15
16 -- Update UNSPSC
17 --Bug 3046583
18 /* Changed the logic to use data from level tables
19 (EdW_Item_ItemRev_LTC,EdW_Item_ItemOrg_LTC)
20 rather than star table (EDW_TIME_M) for updating
21 Invoice Lines Fact with UNSPC information. */
22
23 Update FII_AP_INV_LINES_FSTG Fact
24 set UNSPSC_FK =
25 (select Com.Function
26 from EdW_Item_ItemRev_LTC IRev,
27 EdW_Item_ItemOrg_LTC IOrg,
28 POA_DNB_ITEMS Com
29 where ((Fact.Item_FK = IRev.Item_Revision_PK) and
30 (IOrg.Item_Org_PK=IRev.Item_Org_FK) and
31 (IOrg.Item_Number_FK = Com.Item_PK)))
32 where ((Fact.Collection_Status = 'READY') and
33 (Fact.Item_FK IN (select IRev.Item_Revision_PK
34 from EdW_Item_ItemRev_LTC IRev,
35 EdW_Item_ItemOrg_LTC IOrg,
36 POA_DNB_ITEMS Com
37 where (IOrg.Item_Number_FK = Com.Item_PK) and
38 (IOrg.Item_Org_PK=IRev.Item_Org_FK))));
39
40 return true;
41 end Pre_Fact_Collect;
42 END EDW_SICM_INV_LINES_HOOK;
43
[Home] [Help]