[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_UNSPSC_HOOK
Source
1 PACKAGE BODY EDW_POA_UNSPSC_HOOK as
2 /*$Header: poahkunb.pls 115.5 2002/01/24 17:54:37 pkm ship $ */
3 function Post_Dim_Collect(p_object_name varchar2) return boolean is
4 CURSOR c_items IS
5 Select Function_PK from EDW_SPSC_FUNCTION_LSTG
6 where Update_Fact_Flag = 'Y';
7 begin
8 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Entering UNSPSC Post Dimension Hook Procedure');
9
10 IF (fnd_profile.value('POA_DNB_HOOKS') = 'N') THEN
11 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Existing POA facts to reference UNSPSC Dimension');
12
13 FOR litem in c_items LOOP
14 -- Update all facts
15
16 -- Updating Distribution fact
17 Update POA_EDW_PO_DIST_F Fact
18 set (UNSPSC_FK_Key) =
19 (select UNSPSC.Function_PK_Key
20 from EDW_SPSC_FUNCTION_LTC UNSPSC
21 where (litem.Function_PK = UNSPSC.Function_PK))
22 where (Fact.Item_FK_Key IN
23 (select IRev.Item_Revision_PK_Key
24 from POA_DNB_ITEMS Com,
25 EdW_Item_ItemRev_LTC IRev,
26 EdW_Item_ItemOrg_LTC IOrg
27 where ((litem.Function_PK = Com.Function) and
28 (Com.Item_PK = IOrg.Item_Number_FK) and
29 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
30
31 -- Update Receiving Fact
32 Update POA_EDW_RCV_TXNS_F Fact
33 set (UNSPSC_FK_Key) =
34 (select UNSPSC.Function_PK_Key
35 from EDW_SPSC_FUNCTION_LTC UNSPSC
36 where (litem.Function_PK = UNSPSC.Function_PK))
37 where (Fact.Item_Revision_FK_Key IN
38 (select IRev.Item_Revision_PK_Key
39 from POA_DNB_ITEMS Com,
40 EdW_Item_ItemRev_LTC IRev,
41 EdW_Item_ItemOrg_LTC IOrg
42 where ((litem.Function_PK = Com.Function) and
43 (Com.Item_PK = IOrg.Item_Number_FK) and
44 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
45
46 -- Update PO Agreement Lines
47 Update POA_EDW_ALINES_F Fact
48 set (UNSPSC_FK_Key) =
49 (select UNSPSC.Function_PK_Key
50 from EDW_SPSC_FUNCTION_LTC UNSPSC
51 where (litem.Function_PK = UNSPSC.Function_PK))
52 where (Fact.Item_Revision_FK_Key IN
53 (select IRev.Item_Revision_PK_Key
54 from POA_DNB_ITEMS Com,
55 EdW_Item_ItemRev_LTC IRev,
56 EdW_Item_ItemOrg_LTC IOrg
57 where ((litem.Function_PK = Com.Function) and
58 (Com.Item_PK = IOrg.Item_Number_FK) and
59 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
60
61
62 -- Update Cutomer Measure Fact
63 Update POA_EDW_CSTM_MSR_F Fact
64 set (UNSPSC_FK_Key) =
65 (select UNSPSC.Function_PK_Key
66 from EDW_SPSC_FUNCTION_LTC UNSPSC
67 where (litem.Function_PK = UNSPSC.Function_PK))
68 where (Fact.Item_FK_Key IN
69 (select IRev.Item_Revision_PK_Key
70 from POA_DNB_ITEMS Com,
71 EdW_Item_ItemRev_LTC IRev,
72 EdW_Item_ItemOrg_LTC IOrg
73 where ((litem.Function_PK = Com.Function) and
74 (Com.Item_PK = IOrg.Item_Number_FK) and
75 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
76
77
78 -- Update Supplier Performance
79 Update POA_EDW_SUP_PERF_F Fact
80 set (UNSPSC_FK_Key) =
81 (select UNSPSC.Function_PK_Key
82 from EDW_SPSC_FUNCTION_LTC UNSPSC
83 where (litem.Function_PK = UNSPSC.Function_PK))
84 where (Fact.Item_FK_Key IN
85 (select IRev.Item_Revision_PK_Key
86 from POA_DNB_ITEMS Com,
87 EdW_Item_ItemRev_LTC IRev,
88 EdW_Item_ItemOrg_LTC IOrg
89 where ((litem.Function_PK = Com.Function) and
90 (Com.Item_PK = IOrg.Item_Number_FK) and
91 (IOrg.Item_Org_PK = IRev.Item_Org_FK))));
92
93 END LOOP;
94
95 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updated Existing POA Facts');
96
97 -- Call AP to update its Facts
98 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating FII Facts');
99
100 return EDW_SICM_UNSPSC_HOOK.Post_Dim_Collect(p_object_name);
101 ELSE
102 EDW_OWB_COLLECTION_UTIL.write_to_log_file('DNB is disabled');
103 END IF;
104
105 return true;
106 end Post_Dim_Collect;
107
108 END EDW_POA_UNSPSC_HOOK;
109