1 PACKAGE BODY CSTPPPUR AS
2 /* $Header: CSTPPURB.pls 120.11 2008/05/13 16:59:34 vjavli ship $ */
3
4 g_bulk_limit NUMBER := 3000; -- bulk fetch limit
5 TYPE rowidstruc IS TABLE OF VARCHAR2(30); /* added for perf bug 4461176 */
6 rowidtab rowidstruc;
7
8 /*---------------------------------------------------------------------------*
9 | PRIVATE PROCEDURES/FUNCTIONS |
10 *----------------------------------------------------------------------------*/
11 /*---------------------------------------------------------------------------*
12 | PUBLIC PROCEDURE |
13 | purge_period_data |
14 *----------------------------------------------------------------------------*/
15 PROCEDURE purge_period_data (
16 i_pac_period_id IN NUMBER,
17 i_legal_entity IN NUMBER,
18 i_cost_group_id IN NUMBER,
19 i_acquisition_flag IN NUMBER DEFAULT 0,
20 i_user_id IN NUMBER,
21 i_login_id IN NUMBER DEFAULT -1,
22 i_request_id IN NUMBER,
23 i_prog_id IN NUMBER DEFAULT -1,
24 i_prog_app_id IN NUMBER DEFAULT -1,
25 o_err_num OUT NOCOPY NUMBER,
26 o_err_code OUT NOCOPY VARCHAR2,
27 o_err_msg OUT NOCOPY VARCHAR2
28 )
29 IS
30
31 l_stmt_num NUMBER;
32 l_err_num NUMBER;
33 l_err_code VARCHAR2(240);
34 l_err_msg VARCHAR2(240);
35 PROCESS_ERROR EXCEPTION;
36 l_count NUMBER;
37
38 BEGIN
39
40 l_count := 0;
41 l_stmt_num := 0;
42 IF (i_acquisition_flag = 1) THEN
43
44 DELETE FROM cst_rcv_acq_cost_details cracd
45 WHERE cracd.header_id in (SELECT header_id FROM cst_rcv_acq_costs
46 WHERE period_id = i_pac_period_id
47 AND cost_group_id = i_cost_group_id);
48
49 DELETE FROM cst_rcv_acq_costs
50 WHERE period_id = i_pac_period_id
51 AND cost_group_id = i_cost_group_id;
52
53 END IF;
54
55
56 l_stmt_num := 10;
57 DELETE FROM cst_pac_low_level_codes
58 WHERE pac_period_id = i_pac_period_id
59 AND cost_group_id = i_cost_group_id;
60
61 l_stmt_num := 20;
62 DELETE /*+ index(cst_pac_explosion_temp CST_PAC_EXPLOSION_TEMP_N1) */
63 FROM cst_pac_explosion_temp
64 WHERE pac_period_id = i_pac_period_id
65 AND cost_group_id = i_cost_group_id;
66
67 l_stmt_num := 30;
68 DELETE FROM wip_pac_period_balances
69 WHERE pac_period_id = i_pac_period_id
70 AND cost_group_id = i_cost_group_id;
71
72 l_stmt_num := 40;
73 DELETE FROM mtl_pac_txn_cost_details
74 WHERE pac_period_id = i_pac_period_id
75 AND cost_group_id = i_cost_group_id
76 AND new_periodic_cost IS NULL
77 AND value_change IS NULL
78 AND percentage_change IS NULL;
79
80 l_stmt_num := 50;
81 DELETE FROM mtl_pac_actual_cost_details
82 WHERE pac_period_id = i_pac_period_id
83 AND cost_group_id = i_cost_group_id;
84
85 l_stmt_num := 55;
86 DELETE FROM wip_pac_actual_cost_details
87 WHERE pac_period_id = i_pac_period_id
88 AND cost_group_id = i_cost_group_id;
89
90 l_stmt_num := 60;
91 DELETE FROM mtl_pac_cost_subelements
92 WHERE pac_period_id = i_pac_period_id
93 AND cost_group_id = i_cost_group_id;
94
95 ---------------------------------------
96 -- Added R12 PAC enhancement
97 ---------------------------------------
98 l_stmt_num := 65;
99 DELETE FROM cst_pac_req_oper_cost_details
100 WHERE pac_period_id = i_pac_period_id
101 AND cost_group_id = i_cost_group_id;
102
103 l_stmt_num := 70;
104
105 DELETE from cst_pac_quantity_layers
106 WHERE cost_layer_id in (SELECT cost_layer_id
107 FROM cst_pac_item_costs
108 WHERE pac_period_id = i_pac_period_id
109 AND cost_group_id = i_cost_group_id);
110
111 l_stmt_num := 80;
112
113 DELETE FROM cst_pac_item_cost_details
114 WHERE cost_layer_id in (SELECT cost_layer_id from cst_pac_item_costs
115 WHERE pac_period_id = i_pac_period_id
116 AND cost_group_id = i_cost_group_id);
117
118 l_stmt_num := 90;
119 DELETE FROM cst_pac_item_costs
120 WHERE pac_period_id = i_pac_period_id
121 AND cost_group_id = i_cost_group_id;
122
123 -- l_stmt_num := 100;
124 -- Deletion from cst_pc_txn_history removed
125 -- as part of performance bug 6751847 fix
126
127 l_stmt_num := 110; -- PAC enhancements project R12
128 DELETE FROM cst_pac_period_balances
129 WHERE pac_period_id = i_pac_period_id
130 AND cost_group_id = i_cost_group_id;
131
132 -- Changes made to support eAM in PAC.
133 -- Update all actual cols in cpeapb abd cpepb to 0
134 -- delete from cpeapb abd wepb where actual and estimate cols are 0
135
136 l_stmt_num := 120;
137 -- Delete ceapb rows with zeros in ALL value columns
138 UPDATE cst_pac_eam_asset_per_balances
139 SET actual_mat_cost = 0,
140 actual_lab_cost = 0,
141 actual_eqp_cost = 0
142 WHERE legal_entity_id = i_legal_entity
143 AND cost_group_id = i_cost_group_id
144 AND cost_type_id = (SELECT cost_type_id
145 FROM cst_pac_periods
146 WHERE pac_period_id = i_pac_period_id);
147
148 l_stmt_num := 130;
149 DELETE FROM cst_pac_eam_asset_per_balances
150 WHERE NVL(actual_mat_cost,0) = 0
151 AND NVL(actual_lab_cost,0) = 0
152 AND NVL(actual_eqp_cost,0) = 0
153 AND NVL(system_estimated_mat_cost,0) = 0
154 AND NVL(system_estimated_lab_cost,0) = 0
155 AND NVL(system_estimated_eqp_cost,0) = 0
156 AND legal_entity_id = i_legal_entity
157 AND cost_group_id = i_cost_group_id
158 AND cost_type_id = (SELECT cost_type_id
159 FROM cst_pac_periods
160 WHERE pac_period_id = i_pac_period_id);
161
162 l_stmt_num := 140;
163 -- Delete cpepb rows with zeros in ALL value columns
164 UPDATE cst_pac_eam_period_balances
165 SET actual_mat_cost = 0,
166 actual_lab_cost = 0,
167 actual_eqp_cost = 0
168 WHERE cost_group_id = i_cost_group_id
169 AND pac_period_id = i_pac_period_id;
170
171 l_stmt_num := 150;
172 DELETE FROM cst_pac_eam_period_balances
173 WHERE NVL(actual_mat_cost,0) = 0
174 AND NVL(actual_lab_cost,0) = 0
175 AND NVL(actual_eqp_cost,0) = 0
176 AND NVL(system_estimated_mat_cost,0) = 0
177 AND NVL(system_estimated_lab_cost,0) = 0
178 AND NVL(system_estimated_eqp_cost,0) = 0
179 AND cost_group_id = i_cost_group_id
180 AND pac_period_id = i_pac_period_id;
181
182 l_stmt_num := 160;
183 purge_distribution_data (i_pac_period_id,i_legal_entity,i_cost_group_id,i_user_id,
184 i_login_id,i_request_id,i_prog_id,i_prog_app_id,
185 l_err_num, l_err_code, l_err_msg);
186 IF (l_err_num <> 0) THEN
187 raise PROCESS_ERROR;
188 END IF;
189
190 ----------------------------------------------------------------------
191 -- Update Process_status to Pending.
192 -- It will :
193 -- a. If i_acquisition_flag = 1 (Being called from phase 1), then
194 -- set the process status for all phases (1-5) to pending
195 -- b. If i_acquisition_flag = 0 (Being called from phase 2) ,then
196 -- set the process status for phases 2-5 to pending. It means
197 -- that this rerun starting from phase 2.
198 -- 2 conditions for setting phase 6 :
199 -- a. If CREATE_ACCT_ENTRIES='N', set phase 6 status to 0 ('N/A').
200 -- b. If CREATE_ACCT_ENTRIES='Y', set phase 6 status to 1 ('N/P').
201 -- Bug 6520942 fix: set Phase 8 status to 0 (N/A) when transfer
202 -- cost flag is not enabled, very similar behavior as that of current
203 -- phase 7 where it is set to 0.
204 -- Set phase 8 status to 1 (unprocessed) when transfer cost flag is
205 -- enabled, very similar to behavior as that of current phase 7 where
206 -- it is set to 1.
207 -- NOTE: When transfer cost flag is disabled in organization cost
208 -- group / cost type association screen, phase 7 and 8 status are set
209 -- to 0. It is important to have the status of phase 7 and 8 set to 0
210 -- even after acquisition cost processor or after periodic cost processor
211 ----------------------------------------------------------------------
212
213 /* changes to support the PAC IO transfer cost processor project.If the transfer cost flag
214 is set then we should set the status to 1 otherwise we should set it not applicable(0) */
215
216
217 l_stmt_num := 170;
218 UPDATE cst_pac_process_phases cppp
219 SET (cppp.process_status,cppp.last_update_date, cppp.process_upto_date) =
220 (SELECT decode(cppp.process_phase,6,
221 decode(NVL(clct.CREATE_ACCT_ENTRIES,'N'),'N',0,1),
222 7,
223 decode(NVL(clct.transfer_cost_flag,'N'),'N',0,1),
224 8,
225 decode(NVL(clct.transfer_cost_flag,'N'),'N',0,1),
226 1),
227 SYSDATE,
228 NULL
229 FROM cst_le_cost_types clct, cst_pac_periods cpp
230 WHERE cpp.pac_period_id = i_pac_period_id
231 AND clct.legal_entity = i_legal_entity
232 AND clct.cost_type_id = cpp.cost_type_id)
233 WHERE cppp.pac_period_id = i_pac_period_id
234 AND cppp.cost_group_id = i_cost_group_id
235 AND cppp.process_phase <> decode(i_acquisition_flag, 0, 1, 0);
236
237
238 EXCEPTION
239
240 WHEN PROCESS_ERROR THEN
241 o_err_num := l_err_num;
242 o_err_code := l_err_code;
243 o_err_msg := 'CSTPPPUR.purge_period_data:' || l_err_msg;
244
245 WHEN OTHERS THEN
246 o_err_num := SQLCODE;
247 o_err_code := NULL;
248 o_err_msg := SUBSTR('CSTPPPUR.purge_period_data(' || to_char(l_stmt_num)
249 || '): ' ||SQLERRM,1,240);
250
251 END purge_period_data;
252
253 PROCEDURE purge_distribution_data (
254 i_pac_period_id IN NUMBER,
255 i_legal_entity IN NUMBER,
256 i_cost_group_id IN NUMBER,
257 i_user_id IN NUMBER,
258 i_login_id IN NUMBER DEFAULT -1,
259 i_request_id IN NUMBER,
260 i_prog_id IN NUMBER DEFAULT -1,
261 i_prog_app_id IN NUMBER DEFAULT -1,
262 o_err_num OUT NOCOPY NUMBER,
263 o_err_code OUT NOCOPY VARCHAR2,
264 o_err_msg OUT NOCOPY VARCHAR2
265 ) IS
266
267 l_stmt_num NUMBER;
268 l_count NUMBER;
269
270 BEGIN
271
272 l_count := 0;
273 l_stmt_num := 10;
274
275 DELETE from cst_ae_lines
276 where ae_header_id in
277 (SELECT ae_header_id
278 FROM cst_ae_headers
279 WHERE period_id = i_pac_period_id
280 AND cost_group_id = i_cost_group_id);
281
282 l_stmt_num := 20;
283 DELETE FROM cst_encumbrance_lines
284 WHERE ae_header_id in
285 (SELECT ae_header_id
286 FROM cst_ae_headers
287 WHERE period_id = i_pac_period_id
288 AND cost_group_id = i_cost_group_id);
289
290 l_stmt_num := 30;
291 DELETE FROM cst_ae_headers
292 WHERE period_id = i_pac_period_id
293 AND cost_group_id = i_cost_group_id;
294
295 l_stmt_num := 40;
296 DELETE FROM CST_PAC_ACCRUAL_RECONCILE_TEMP
297 WHERE period_id = i_pac_period_id
298 AND cost_group_id = i_cost_group_id;
299
300 l_stmt_num := 50;
301 DELETE FROM CST_PAC_ACCRUAL_WRITE_OFFS
302 WHERE period_id = i_pac_period_id
303 AND cost_group_id = i_cost_group_id;
304
305 EXCEPTION
306
307 WHEN OTHERS THEN
308 o_err_num := SQLCODE;
309 o_err_code := NULL;
310 o_err_msg := SUBSTR('CSTPPPUR.purge_distribution_data(' || to_char(l_stmt_num)
311 || '): ' ||SQLERRM,1,240);
312
313 END purge_distribution_data;
314
315
316 END CSTPPPUR;
[Home] [Help]