[Home] [Help]
PACKAGE BODY: APPS.FII_BUDGET_BASE_UPG_C
Source
1 PACKAGE BODY FII_BUDGET_BASE_UPG_C AS
2 /*$Header: FIIBUDUPB.pls 120.1 2006/01/18 20:44:33 lpoon noship $*/
3
4 PROCEDURE UPDATE_TABLE(errbuf IN OUT NOCOPY VARCHAR2,
5 retcode IN OUT NOCOPY VARCHAR2) IS
6
7 l_debug_flag VARCHAR2(1);
8 l_phase VARCHAR2(100);
9 l_unassigned_id NUMBER;
10 l_fii_schema VARCHAR2(30);
11 l_stmt VARCHAR2(300);
12
13 BEGIN
14 l_phase := 'Cache the FII debug mode';
15 l_debug_flag := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
16
17 -- 1. Find the unassigned ID used for UD1 and UD2
18 l_phase := 'Find the unassigned ID used for UD1 and UD2';
19
20 IF l_debug_flag = 'Y' THEN
21 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
22 || l_phase);
23 END IF;
24
25 SELECT flex_value_id
26 INTO l_unassigned_id
27 FROM FND_FLEX_VALUES
28 WHERE flex_value_set_id =
29 (SELECT flex_value_set_id
30 FROM FND_FLEX_VALUE_SETS
31 WHERE flex_value_set_name
32 = 'Financials Intelligence Internal Value Set')
33 AND flex_value = 'UNASSIGNED';
34
35 -- 2. Truncate FII_BUDGET_BASE_UPG_T
36 l_phase := 'Find FII schema name';
37 IF l_debug_flag = 'Y' THEN
38 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
39 || l_phase);
40 END IF;
41
42 l_fii_schema := FII_UTIL.get_schema_name('FII');
43
44 l_phase := 'Truncate FII_BUDGET_BASE_UPG_T before inserting';
45 IF l_debug_flag = 'Y' THEN
46 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
47 || l_phase);
48 END IF;
49
50 l_stmt := 'truncate table '||l_fii_schema||'.FII_BUDGET_BASE_UPG_T';
51 EXECUTE IMMEDIATE l_stmt;
52
53 -- 3. Insert data into FII_BUDGET_BASE_UPG_T from FII_BUDGET_BASE
54 l_phase := 'Insert into FII_BUDGET_BASE_UPG_T';
55 IF l_debug_flag = 'Y' THEN
56 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
57 || l_phase);
58 END IF;
59
60 INSERT /*+ append parallel(t) */
61 INTO FII_BUDGET_BASE_UPG_T t
62 ( plan_type_code, time_id, period_type_id, prim_amount_g, sec_amount_g
63 , creation_date, created_by, last_update_date, last_updated_by
64 , last_update_login, company_cost_center_org_id, line_of_business
65 , natural_account, fin_item, product_code, category_id, fin_category_id
66 , ledger_id, company_id, cost_center_id, user_dim1_id, user_dim2_id
67 , prim_amount_total, sec_amount_total, version_date, upload_date
68 , no_version_flag, posted_date, baseline_amount_prim)
69 SELECT /*+ parallel(b) use_hash(v) */
70 b.plan_type_code
71 , b.time_id
72 , b.period_type_id
73 , b.prim_amount_g
74 , b.sec_amount_g
75 , b.creation_date
76 , b.created_by
77 , b.last_update_date
78 , b.last_updated_by
79 , b.last_update_login
80 , b.company_cost_center_org_id
81 , b.line_of_business
82 , b.natural_account
83 , b.fin_item
84 , b.product_code
85 , b.category_id
86 , b.fin_category_id
87 , nvl(b.ledger_id, -1) ledger_id
88 , nvl(b.company_id, v.company_id) company_id
89 , nvl(b.cost_center_id, v.cost_center_id) cost_center_id
90 , nvl(b.user_dim1_id, l_unassigned_id) user_dim1_id
91 , nvl(b.user_dim2_id, l_unassigned_id) user_dim2_id
92 , b.prim_amount_total
93 , b.sec_amount_total
94 , b.version_date
95 , b.upload_date
96 , b.no_version_flag
97 , b.posted_date
98 , b.baseline_amount_prim
99 FROM FII_BUDGET_BASE b
100 , (SELECT /*+ no_merge */
101 ccc_tbl.organization_id
102 , fv1.flex_value_id company_id
103 , fv2.flex_value_id cost_center_id
104 FROM HR_ORGANIZATION_INFORMATION ccc_tbl
105 , HR_ORGANIZATION_INFORMATION org
106 , FND_FLEX_VALUES fv1
107 , FND_FLEX_VALUES fv2
108 WHERE ccc_tbl.org_information_context = 'CLASS'
109 AND ccc_tbl.org_information1 = 'CC'
110 AND ccc_tbl.org_information2 = 'Y'
111 AND org.org_information_context = 'Company Cost Center'
112 AND org.organization_id = ccc_tbl.organization_id
113 AND fv1.flex_value_set_id = org.org_information2
114 AND fv1.flex_value = org.org_information3
115 AND fv2.flex_value_set_id = org.org_information4
116 AND fv2.flex_value = org.org_information5) v
117 WHERE b.company_cost_center_org_id = v.organization_id (+);
118
119 -- 4. Truncate FII_BUDGET_BASE
120 l_phase := 'Truncate FII_BUDGET_BASE';
121 IF l_debug_flag = 'Y' THEN
122 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
123 || 'Inserted ' || SQL%ROWCOUNT
124 || ' rows into FII_BUDGET_BASE_UPG_T');
125 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
126 || l_phase);
127 END IF;
128
129 l_stmt := 'truncate table '||l_fii_schema||'.FII_BUDGET_BASE';
130 EXECUTE IMMEDIATE l_stmt;
131
132 -- 5. Re-insert data into FII_BUDGET_BASE from FII_BUDGET_BASE_UPG_T
133 l_phase := 'Insert into FII_BUDGET_BASE';
134 IF l_debug_flag = 'Y' THEN
135 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
136 || l_phase);
137 END IF;
138
139 INSERT /*+ append parallel(b) */
140 INTO FII_BUDGET_BASE b
141 ( plan_type_code, time_id, period_type_id, prim_amount_g, sec_amount_g
142 , creation_date, created_by, last_update_date, last_updated_by
143 , last_update_login, company_cost_center_org_id, line_of_business
144 , natural_account, fin_item, product_code, category_id, fin_category_id
145 , ledger_id, company_id, cost_center_id, user_dim1_id, user_dim2_id
146 , prim_amount_total, sec_amount_total, version_date, upload_date
147 , no_version_flag, posted_date, baseline_amount_prim)
148 SELECT /*+ parallel(t) */
149 t.plan_type_code
150 , t.time_id
151 , t.period_type_id
152 , t.prim_amount_g
153 , t.sec_amount_g
154 , t.creation_date
155 , t.created_by
156 , t.last_update_date
157 , t.last_updated_by
158 , t.last_update_login
159 , t.company_cost_center_org_id
160 , t.line_of_business
161 , t.natural_account
162 , t.fin_item
163 , t.product_code
164 , t.category_id
165 , t.fin_category_id
166 , t.ledger_id
167 , t.company_id
168 , t.cost_center_id
169 , t.user_dim1_id
170 , t.user_dim2_id
171 , t.prim_amount_total
172 , t.sec_amount_total
173 , t.version_date
174 , t.upload_date
175 , t.no_version_flag
176 , t.posted_date
177 , t.baseline_amount_prim
178 FROM FII_BUDGET_BASE_UPG_T t;
179
180 -- 6. Truncate MLOG$_FII_BUDGET_BASE
181 l_phase := 'Truncate MLOG$_FII_BUDGET_BASE';
182 IF l_debug_flag = 'Y' THEN
183 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
184 || 'Inserted ' || SQL%ROWCOUNT
185 || ' rows into FII_BUDGET_BASE');
186 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
187 || l_phase);
188 END IF;
189
190 l_stmt:='truncate table '||l_fii_schema||'.'|| 'MLOG$_FII_BUDGET_BASE';
191 EXECUTE IMMEDIATE l_stmt;
192
193 -- 7. Truncate FII_BUDGET_BASE_UPG_T
194 l_phase := 'Truncate FII_BUDGET_BASE_UPG_T before exit';
195 IF l_debug_flag = 'Y' THEN
196 FII_UTIL.put_line(TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' - '
197 || l_phase);
198 END IF;
199
200 l_stmt := 'truncate table '||l_fii_schema||'.FII_BUDGET_BASE_UPG_T';
201 EXECUTE IMMEDIATE l_stmt;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 errbuf := sqlerrm;
206 retcode := sqlcode;
207 FII_UTIL.put_line('
208 ---------------------------------
209 Error in Procedure: UPDATE_TABLE
210 Phase: '||l_phase||'
211 Message: '||errbuf);
212 raise;
213
214 END UPDATE_TABLE;
215
216 END FII_BUDGET_BASE_UPG_C;