[Home] [Help]
PACKAGE BODY: APPS.POA_SIC_CODE_HOOK
Source
1 PACKAGE BODY POA_SIC_CODE_HOOK as
2 /*$Header: poahkscb.pls 115.3 2002/01/24 17:54:32 pkm ship $ */
3
4 function Post_Dim_Collect(p_object_name varchar2) return boolean IS
5 CURSOR c_sic IS
6 Select SIC_CODE_PK from EDW_SICM_SIC_LSTG
7 where Update_Fact_Flag = 'Y';
8 begin
9 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Entering SIC Code Post Dimension Hook Procedure');
10
11 IF (fnd_profile.value('POA_DNB_HOOKS') = 'N') THEN
12 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updating Exiting POA Facts to reference SIC Code Dimension');
13
14 FOR lsic in c_sic LOOP
15 -- Update all facts
16
17 -- Updating PO Contract Lines
18 Update POA_EDW_ALINES_F Fact
19 set (SIC_CODE_FK_KEY) =
20 (select SIC.SIC_CODE_PK_KEY
21 from EDW_SICM_SIC_LTC SIC
22 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
23 where (Fact.Supplier_Site_FK_Key IN
24 (select TPartner_Loc_PK_Key
25 from POA_DNB_SIC_CODE Com,
26 POA_DNB_TRD_PRTNR dnb,
27 EDW_TPRT_TPARTNER_LOC_LTC TPrt
28 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
29 (dnb.SIC_CODE = Com.SIC_CODE) and
30 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
31
32
33 -- Updating PO Contract
34 Update POA_EDW_CONTRACT_F Fact
35 set (SIC_CODE_FK_KEY) =
36 (select SIC.SIC_CODE_PK_KEY
37 from EDW_SICM_SIC_LTC SIC
38 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
39 where (Fact.Supplier_Site_FK_Key IN
40 (select TPartner_Loc_PK_Key
41 from POA_DNB_SIC_CODE Com,
42 POA_DNB_TRD_PRTNR dnb,
43 EDW_TPRT_TPARTNER_LOC_LTC TPrt
44 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
45 (dnb.SIC_CODE = Com.SIC_CODE) and
46 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
47
48
49 -- Updating Custom Measure
50 Update POA_EDW_CSTM_MSR_F Fact
51 set (SIC_CODE_FK_KEY) =
52 (select SIC.SIC_CODE_PK_KEY
53 from EDW_SICM_SIC_LTC SIC
54 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
55 where (Fact.Supplier_Site_FK_Key IN
56 (select TPartner_Loc_PK_Key
57 from POA_DNB_SIC_CODE Com,
58 POA_DNB_TRD_PRTNR dnb,
59 EDW_TPRT_TPARTNER_LOC_LTC TPrt
60 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
61 (dnb.SIC_CODE = Com.SIC_CODE) and
62 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
63
64
65 -- Updating PO Distributions
66 Update POA_EDW_PO_DIST_F Fact
67 set (SIC_CODE_FK_KEY) =
68 (select SIC.SIC_CODE_PK_KEY
69 from EDW_SICM_SIC_LTC SIC
70 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
71 where (Fact.Supplier_Site_FK_Key IN
72 (select TPartner_Loc_PK_Key
73 from POA_DNB_SIC_CODE Com,
74 POA_DNB_TRD_PRTNR dnb,
75 EDW_TPRT_TPARTNER_LOC_LTC TPrt
76 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
77 (dnb.SIC_CODE = Com.SIC_CODE) and
78 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
79
80
81 -- Updating Recieving
82 Update POA_EDW_RCV_TXNS_F Fact
83 set (SIC_CODE_FK_KEY) =
84 (select SIC.SIC_CODE_PK_KEY
85 from EDW_SICM_SIC_LTC SIC
86 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
87 where (Fact.Supplier_Site_FK_Key IN
88 (select TPartner_Loc_PK_Key
89 from POA_DNB_SIC_CODE Com,
90 POA_DNB_TRD_PRTNR dnb,
91 EDW_TPRT_TPARTNER_LOC_LTC TPrt
92 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
93 (dnb.SIC_CODE = Com.SIC_CODE) and
94 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
95
96
97 -- Updating Supplier Performance
98 Update POA_EDW_SUP_PERF_F Fact
99 set (SIC_CODE_FK_KEY) =
100 (select SIC.SIC_CODE_PK_KEY
101 from EDW_SICM_SIC_LTC SIC
102 where (lsic.SIC_CODE_PK = SIC.SIC_CODE_PK))
103 where (Fact.Supplier_Site_FK_Key IN
104 (select TPartner_Loc_PK_Key
105 from POA_DNB_SIC_CODE Com,
106 POA_DNB_TRD_PRTNR dnb,
107 EDW_TPRT_TPARTNER_LOC_LTC TPrt
108 where ((lsic.SIC_CODE_PK = Com.SIC_CODE) and
109 (dnb.SIC_CODE = Com.SIC_CODE) and
110 (TPrt.TPartner_Loc_PK = dnb.Trading_Partner_PK))));
111
112 END LOOP;
113
114 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Updated POA Facts');
115 ELSE
116 EDW_OWB_COLLECTION_UTIL.write_to_log_file('DNB is disabled');
117 END IF;
118
119 return true;
120 end Post_Dim_Collect;
121
122 END POA_SIC_CODE_HOOK;
123