[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_DUNS_HOOK
Source
1 PACKAGE BODY EDW_POA_DUNS_HOOK as
2 /*$Header: poahkdnb.pls 115.4 2002/01/24 17:54:25 pkm ship $ */
3
4 function Post_Dim_Collect(p_object_name varchar2) return boolean is
5 CURSOR c_duns IS
6 Select DUNS_NUM_PK from EDW_DUNS_NUMBER_LSTG
7 where Update_Fact_Flag = 'Y';
8 begin
9 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Entering Duns Post Dimension Hook rocedure');
10
11 IF (fnd_profile.value('POA_DNB_HOOKS') = 'N') THEN
12 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Exisiting POA Facts to refer to DUNS Dimension');
13
14 FOR lduns in c_duns LOOP
15 -- Update all facts
16
17 -- Updating PO Contract Lines
18 Update POA_EDW_ALINES_F Fact
19 set (DUNS_FK_KEY) =
20 (select DUNS.DUNS_NUM_PK_KEY
21 from EDW_DUNS_NUMBER_LTC DUNS
22 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
23 where (Fact.Supplier_Site_FK_Key IN
24 (select TPartner_Loc_PK_Key
25 from POA_DNB_TRD_PRTNR Com,
26 EDW_TPRT_TPARTNER_LOC_LTC TPrt
27 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
28 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
29
30
31 -- Updating PO Contract
32 Update POA_EDW_CONTRACT_F Fact
33 set (DUNS_FK_KEY) =
34 (select DUNS.DUNS_NUM_PK_KEY
35 from EDW_DUNS_NUMBER_LTC DUNS
36 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
37 where (Fact.Supplier_Site_FK_Key IN
38 (select TPartner_Loc_PK_Key
39 from POA_DNB_TRD_PRTNR Com,
40 EDW_TPRT_TPARTNER_LOC_LTC TPrt
41 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
42 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
43
44 -- Updating Custom Measure
45 Update POA_EDW_CSTM_MSR_F Fact
46 set (DUNS_FK_KEY) =
47 (select DUNS.DUNS_NUM_PK_KEY
48 from EDW_DUNS_NUMBER_LTC DUNS
49 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
50 where (Fact.Supplier_Site_FK_Key IN
51 (select TPartner_Loc_PK_Key
52 from POA_DNB_TRD_PRTNR Com,
53 EDW_TPRT_TPARTNER_LOC_LTC TPrt
54 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
55 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
56
57 -- Updating PO Distributions
58 Update POA_EDW_PO_DIST_F Fact
59 set (DUNS_FK_KEY) =
60 (select DUNS.DUNS_NUM_PK_KEY
61 from EDW_DUNS_NUMBER_LTC DUNS
62 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
63 where (Fact.Supplier_Site_FK_Key IN
64 (select TPartner_Loc_PK_Key
65 from POA_DNB_TRD_PRTNR Com,
66 EDW_TPRT_TPARTNER_LOC_LTC TPrt
67 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
68 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
69
70 -- Updating Recieving
71 Update POA_EDW_RCV_TXNS_F Fact
72 set (DUNS_FK_KEY) =
73 (select DUNS.DUNS_NUM_PK_KEY
74 from EDW_DUNS_NUMBER_LTC DUNS
75 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
76 where (Fact.Supplier_Site_FK_Key IN
77 (select TPartner_Loc_PK_Key
78 from POA_DNB_TRD_PRTNR Com,
79 EDW_TPRT_TPARTNER_LOC_LTC TPrt
80 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
81 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
82
83 -- Updating Supplier Performance
84 Update POA_EDW_SUP_PERF_F Fact
85 set (DUNS_FK_KEY) =
86 (select DUNS.DUNS_NUM_PK_KEY
87 from EDW_DUNS_NUMBER_LTC DUNS
88 where (lduns.DUNS_NUM_PK = DUNS.DUNS_NUM_PK))
89 where (Fact.Supplier_Site_FK_Key IN
90 (select TPartner_Loc_PK_Key
91 from POA_DNB_TRD_PRTNR Com,
92 EDW_TPRT_TPARTNER_LOC_LTC TPrt
93 where ((lduns.DUNS_NUM_PK = Com.DUNS) and
94 (TPrt.TPartner_Loc_PK = Com.TRADING_PARTNER_PK))));
95
96 END LOOP;
97 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updated POA Facts');
98 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating FII Facts to refer to DUNS Dimension');
99 -- Call AP to update its Facts
100 return EDW_SICM_DUNS_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_DUNS_HOOK;
109