[Home] [Help]
PACKAGE BODY: APPS.MSD_PULL_LEVEL_VALUES
Source
1 PACKAGE BODY MSD_PULL_LEVEL_VALUES AS
2 /* $Header: msdplvlb.pls 120.4 2010/09/09 11:31:04 rissingh ship $ */
3
4 --Constants
5 C_YES CONSTANT NUMBER := 1;
6 C_NO CONSTANT NUMBER := 2;
7
8 -- v_launched_from NUMBER := to_number(NULL); --jarorad
9
10 Procedure ins( a in varchar2) is
11 Begin
12 /* Enabled for Debugging only
13 insert into msd_test values ('VM' || to_char(sysdate, 'hh24:mi') || ' ' || a);
14 commit;
15 */
16 null;
17 End;
18
19 procedure pull_level_values_data(
20 errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_comp_refresh IN NUMBER) IS
23 -- ,p_launched_from IN NUMBER DEFAULT NULL) IS --jarorad
24 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE ;
25 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE ;
26 x_instance VARCHAR2(40) := '';
27 x_level_id NUMBER := 0;
28 v_sql_stmt varchar2(4000);
29 g_retcode varchar2(5) := '0';
30
31 l_seq_num NUMBER := 0;
32
33 /* OPM Comment Rajesh Patangya */
34 x_delete_flag VARCHAR2(1);
35
36 /******************************************************
37 Cursor to get distinct Instance, Max Data and Min Date
38 ******************************************************/
39 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
40 /* PBI Fix Bug 3840123. Cursor with order by that included level id
41 * failed on 8i environment. Removed order by.
42 *
43 *
44 * MAINTAIN ORDER OF COLUMNS.
45 *
46 */
47
48 Cursor Relationship is
49 select distinct
50 mla.instance,
51 ml.level_type_code,
52 mla.level_id,
53 mla.parent_level_id
54 from msd_st_level_associations mla, msd_levels ml
55 where mla.level_id = ml.level_id AND
56 mla.instance <> '0'
57 and ml.plan_type is null --vinekuma
58 /* Bug no 3799518. Enable load of level_org_asscns in legacy load as stand alone */
59 union
60 select distinct
61 mla.instance,
62 ml.level_type_code,
63 mla.level_id,
64 mla.parent_level_id
65 from msd_level_associations mla,
66 msd_levels ml
67 where mla.level_id = ml.level_id
68 and ml.plan_type is null --vinekuma
69 and exists (select 1
70 from msd_st_level_org_asscns mlo
71 where mla.instance = mlo.instance
72 and mla.level_id = mlo.level_id
73 and mlo.instance <> '0'
74 and rownum < 2);
75
76 /* Cursor for level cleanup */
77 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
78 Cursor Level_Cursor is
79 select distinct
80 mla.instance,
81 mla.level_id,
82 ml.level_type_code
83 from msd_st_level_associations mla, msd_levels ml
84 where mla.level_id = ml.level_id AND
85 mla.instance <> '0' AND
86 ml.plan_type is null --vinekuma
87
88 order by instance, level_type_code, mla.level_id;
89
90 Begin
91
92 retcode :=0;
93
94 -- v_launched_from := nvl(p_launched_from,C_DP); --jarorad
95
96 IF (nvl(p_comp_refresh, C_YES) = C_NO) THEN
97 x_delete_flag := 'N';
98 ELSE
99 x_delete_flag := 'Y';
100 END IF;
101
102
103 /* DWK Fetch new seq number for deleted level values */
104 SELECT msd.msd_last_refresh_number_s.nextval
105 INTO l_seq_num from dual;
106
107 MSD_COMMON_UTILITIES.COLLECT_ILP := 2;
108
109 For Relationship_Rec IN Relationship LOOP
110
111 if (Relationship_Rec.instance = x_instance AND Relationship_Rec.level_id = x_level_id) then
112
113 ins('Going to Translate 1: ' || relationship_rec.level_id || ' ' ||
114 relationship_rec.parent_level_id );
115
116
117 MSD_TRANSLATE_LEVEL_VALUES.translate_level_parent_values(
118 errbuf => errbuf,
119 retcode => retcode,
120 p_source_table => x_source_table,
121 p_dest_table => x_dest_table,
122 p_instance_id => Relationship_Rec.instance,
123 p_level_id => Relationship_Rec.level_id,
124 p_level_value_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_COLUMN,
125 p_level_value_pk_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_PK_COLUMN,
126 p_level_value_desc_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_DESC_COLUMN,
127 p_parent_level_id => Relationship_Rec.parent_level_id,
128 p_parent_value_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_COLUMN,
129 p_parent_value_pk_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_PK_COLUMN,
130 p_parent_value_desc_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_DESC_COLUMN,
131 p_update_lvl_table => 0,
132 /* OPM Comment Rajesh Patangya */
133 p_delete_flag => x_delete_flag,
134 p_seq_num => l_seq_num
135 --,p_launched_from => v_launched_from --jarorad
136 ) ;
137
138 --update return code
139 if nvl(retcode,'0') <> '0' then
140 g_retcode := retcode;
141 end if;
142
143 ins('RETCODE ' || retcode || ' ' || Relationship_Rec.level_id || ' ' ||
144 Relationship_Rec.parent_level_id);
145 if (nvl(retcode,0) = 0 ) then
146
147 Delete from msd_st_level_associations
148 where instance = Relationship_Rec.instance
149 and level_id = Relationship_Rec.level_id
150 and parent_level_id = Relationship_Rec.parent_level_id ;
151
152 end if ;
153
154 commit ;
155
156 else
157
158
159 MSD_TRANSLATE_LEVEL_VALUES.translate_level_parent_values(
160 errbuf => errbuf,
161 retcode => retcode,
162 p_source_table => x_source_table,
163 p_dest_table => x_dest_table,
164 p_instance_id => Relationship_Rec.instance,
165 p_level_id => Relationship_Rec.level_id,
166 p_level_value_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_COLUMN,
167 p_level_value_pk_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_PK_COLUMN,
168 p_level_value_desc_column => MSD_COMMON_UTILITIES.LEVEL_VALUE_DESC_COLUMN,
169 p_parent_level_id => Relationship_Rec.parent_level_id,
170 p_parent_value_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_COLUMN,
171 p_parent_value_pk_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_PK_COLUMN,
172 p_parent_value_desc_column => MSD_COMMON_UTILITIES.PARENT_LEVEL_VALUE_DESC_COLUMN,
173 p_update_lvl_table => 1,
174 /* OPM Comment Rajesh Patangya */
175 p_delete_flag => x_delete_flag,
176 p_seq_num => l_seq_num
177 --,p_launched_from => v_launched_from --jarorad
178 ) ;
179
180
181 -- update return code
182 if nvl(retcode,'0') <> '0' then
183 g_retcode := retcode;
184 end if;
185
186 if (nvl(retcode,0) = 0 ) then
187
188
189 Delete from msd_st_level_values
190 where instance = Relationship_Rec.instance
191 and level_id = Relationship_Rec.level_id ;
192
193 Delete from msd_st_level_associations
194 where instance = Relationship_Rec.instance
195 and level_id = Relationship_Rec.level_id
196 and parent_level_id = Relationship_Rec.parent_level_id ;
197
198 end if ;
199 commit ;
200
201 end if;
202
203 x_instance := Relationship_Rec.instance;
204 x_level_id := Relationship_Rec.level_id;
205
206 End Loop ;
207
208 /* DWK. Cleanup deleted level value table after pull */
209 msd_translate_level_values.clean_deleted_level_values( errbuf, retcode);
210
211 For Level_Rec IN Level_Cursor LOOP
212 MSD_COLLECT_LEVEL_VALUES.fix_orphans(Level_Rec.instance,
213 Level_Rec.level_id,
214 MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE,
215 MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE,
216 null);
217 end loop;
218
219
220 Delete from msd_st_level_values
221 where level_id in (
222 select level_id
223 from msd_levels
224 where level_type_code = '1'
225 and plan_type is null) ; --vinekuma
226
227
228 /* esubrama - Supersession Data pull */
229 msd_item_relationships_pkg.pull_supersession_data (
230 errbuf => errbuf,
231 retcode => retcode );
232
233
234 -- done
235 retcode := g_retcode;
236
237
238 commit;
239
240 /* Added by esubrama */
241 MSD_ANALYZE_TABLES.analyze_table(null,2);
242 MSD_ANALYZE_TABLES.analyze_table(null,1);
243
244 exception
245 when others then
246 errbuf := substr(SQLERRM,1,150);
247 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
248 retcode := -1 ;
249
250
251 End pull_level_values_data ;
252
253
254 END ;