[Home] [Help]
PACKAGE BODY: APPS.OZF_MULTI_CURR_MIG_PVT
Source
1 PACKAGE BODY ozf_multi_curr_mig_pvt AS
2 /* $Header: ozfvmmcb.pls 120.0.12010000.2 2010/03/03 07:05:10 nepanda ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- ozf_multi_curr_mig_pvt
10 --
11 -- PURPOSE
12 -- This package contains migration related code for sales team.
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- nirprasa 10/22/2009 Created
18 -- **********************************************************************************************************
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):='ozf_multi_curr_mig_pvt';
21 G_FILE_NAME CONSTANT VARCHAR2(12):='ozfvmmcb.pls';
22
23 --
24 --
25
26 FUNCTION get_functional_curr (p_org_id NUMBER) RETURN VARCHAR2 IS
27
28 CURSOR c_functional_currency IS
29 SELECT
30 gs.currency_code
31 FROM
32 gl_sets_of_books gs,
33 ozf_sys_parameters_all os
34 WHERE
35 os.set_of_books_id = gs.set_of_books_id
36 AND os.org_id = p_org_id;
37
38 l_currency_code VARCHAR2(30);
39
40 BEGIN
41 OPEN c_functional_currency;
42 FETCH c_functional_currency INTO l_currency_code;
43 CLOSE c_functional_currency;
44 return l_currency_code;
45
46 END get_functional_curr;
47
48 PROCEDURE Mig_Utilization_Records (x_errbuf OUT NOCOPY VARCHAR2,
49 x_retcode OUT NOCOPY NUMBER,
50 p_debug_flag IN VARCHAR2)
51 IS
52
53
54
55 TYPE utilIdTbl IS TABLE OF ozf_funds_utilized_all_b.utilization_id%TYPE;
56 TYPE planIdTbl IS TABLE OF ozf_funds_utilized_all_b.plan_id%TYPE;
57 TYPE planCurrCodeTbl IS TABLE OF ozf_funds_utilized_all_b.plan_currency_code%TYPE;
58 TYPE fundReqCurrCodeTbl IS TABLE OF ozf_funds_utilized_all_b.fund_request_currency_code%TYPE;
59 TYPE orgIdTbl IS TABLE OF ozf_funds_utilized_all_b.org_id%TYPE;
60 TYPE currCodeTbl IS TABLE OF ozf_funds_utilized_all_b.currency_code%TYPE;
61 TYPE planCurrAmtTbl IS TABLE OF ozf_funds_utilized_all_b.plan_curr_amount%TYPE;
62 TYPE planCurrAmtRemTbl IS TABLE OF ozf_funds_utilized_all_b.plan_curr_amount_remaining%TYPE;
63 TYPE excTypeTbl IS TABLE OF ozf_funds_utilized_all_b.exchange_rate_type%TYPE;
64 TYPE excDateTbl IS TABLE OF ozf_funds_utilized_all_b.exchange_rate_date%TYPE;
65
66
67 l_utilIdTbl utilIdTbl;
68 l_planIdTbl planIdTbl;
69 l_planCurrCodeTbl planCurrCodeTbl;
70 l_fundReqCurrCodeTbl fundReqCurrCodeTbl;
71 l_currCodeTbl currCodeTbl;
72 l_orgIdTbl orgIdTbl;
73 l_planCurrAmtTbl planCurrAmtTbl;
74 l_planCurrAmtRemTbl planCurrAmtRemTbl;
75 l_excTypeTbl excTypeTbl;
76 l_excDateTbl excDateTbl;
77 l_msg_data VARCHAR2 (32000);
78 l_msg_count NUMBER;
79
80
81
82 CURSOR c_report_header IS
83 select rpad('Offer Name',40, ' ') ||
84 rpad('Status',10, ' ') ||
85 rpad('Transaction Currency',20, ' ') ||
86 rpad('Total Records',20, ' ') ||
87 rpad('Total Amount',20, ' ') ||
88 rpad('Fund Request Currency',20, ' ') column_val
89 from DUAL;
90
91 CURSOR c_report_offers IS
92 SELECT rpad(qpl.description,40, ' ')||
93 rpad(off.status_code,10, ' ')||
94 rpad(utiz.plan_currency_code,20, ' ')||
95 rpad(count(utiz.utilization_id) ,20, ' ')||
96 rpad(sum(utiz.fund_request_amount),20, ' ')||
97 rpad(utiz.fund_request_currency_code ,20, ' '), utiz.plan_id
98 FROM
99 ozf_offers off,
100 qp_list_headers_all qpl,
101 ozf_funds_utilized_all_b utiz
102 WHERE
103 off.qp_list_header_id = utiz.plan_id
104 AND off.transaction_currency_code IS NULL
105 AND off.fund_request_curr_code <> utiz.plan_currency_code
106 AND off.QP_LIST_HEADER_ID = qpl.list_header_id
107 AND NVL(utiz.plan_curr_amount,0) <> 0
108 AND plan_type='OFFR'
109 AND utiz.last_updated_by = -2
110 GROUP BY
111 qpl.description,
112 off.status_code,
113 utiz.fund_request_currency_code,
114 utiz.plan_currency_code,
115 utiz.plan_id;
116
117 CURSOR c_backup_exists IS
118 SELECT 1
119 FROM OZF_MULTI_CURR_UTIL_BCK;
120
121
122 TYPE reportOfferRecTbl IS TABLE OF VARCHAR2(32000);
123 l_reportOfferRecTbl reportOfferRecTbl;
124 l_report_header_rec c_report_header%Rowtype;
125 l_backup_exists NUMBER;
126 l_row_count NUMBER;
127
128
129 BEGIN
130 SAVEPOINT Mig_Utilization_Records;
131
132 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------Accruals to be Migrated for Multi Currency Report ------------------------------*');
133 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
134 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*--------------------------------------------------------------------------------------------------------------*');
135
136 /*Update all 4 new columns. This will get executed only for the first time due to the where clause
137 1) fund_request_amount
138 2) fund_request_amount_remaining
139 3) fund_request_currency_code
140 4) plan_currency_code*/
141
142 UPDATE /* PARALLEL */ ozf_funds_utilized_all_b
143 SET fund_request_amount = plan_curr_amount,
144 fund_request_amount_remaining = plan_curr_amount_remaining,
145 fund_request_currency_code = DECODE(plan_type,'OFFR', (SELECT NVL(transaction_currency_code,fund_request_curr_code)
146 FROM ozf_offers
147 WHERE qp_list_header_id=plan_id)
148 ,'CAMP', (SELECT transaction_currency_code
149 FROM ams_campaigns_vl
150 WHERE campaign_id = plan_id)
151 ,'CSCH', (SELECT transaction_currency_code
152 FROM ams_campaign_schedules_vl
153 WHERE schedule_id = plan_id)
154 ,'DELV', (SELECT transaction_currency_code
155 FROM ams_deliverables_vl
156 WHERE deliverable_id = plan_id)
157 ,'EVEH', (SELECT currency_code_tc
158 FROM ams_event_headers_vl
159 WHERE event_header_id = plan_id)
160 ,'EVEO', (SELECT currency_code_tc
164 FROM qp_list_headers_all
161 FROM ams_event_offers_vl
162 WHERE event_offer_id = plan_id)
163 ,'PRIC',(SELECT currency_code
165 WHERE list_header_id = plan_id)
166 ),
167 plan_currency_code = DECODE(plan_type,'OFFR',
168 DECODE(object_type,'ORDER', (SELECT header.transactional_curr_code
169 FROM oe_order_headers_all header
170 WHERE header.header_id = object_id)
171 ,'TP_ORDER', (SELECT line.currency_code
172 FROM ozf_resale_lines_all line
173 WHERE line.resale_line_id = object_id)
174 ,'INVOICE', (SELECT invoice_currency_code
175 FROM ra_customer_trx_all
176 WHERE customer_trx_id = object_id)
177 ,'PCHO', (SELECT currency_code
178 FROM po_headers_all
179 WHERE po_header_id = object_id)
180 ,'CM', (SELECT invoice_currency_code
181 FROM ra_customer_trx_all
182 WHERE customer_trx_id = object_id)
183 ,'DM', (SELECT invoice_currency_code
184 FROM ra_customer_trx_all
185 WHERE customer_trx_id = object_id)
186 ,(SELECT NVL(transaction_currency_code,fund_request_curr_code)
187 FROM ozf_offers
188 WHERE qp_list_header_id=plan_id)
189 )
190 ,'CAMP', (SELECT transaction_currency_code
191 FROM ams_campaigns_vl
192 WHERE campaign_id = plan_id)
193 ,'CSCH', (SELECT transaction_currency_code
194 FROM ams_campaign_schedules_vl
195 WHERE schedule_id = plan_id)
196 ,'DELV', (SELECT transaction_currency_code
197 FROM ams_deliverables_vl
198 WHERE deliverable_id = plan_id)
199 ,'EVEH', (SELECT currency_code_tc
200 FROM ams_event_headers_vl
201 WHERE event_header_id = plan_id)
202 ,'EVEO',(SELECT currency_code_tc
203 FROM ams_event_offers_vl
204 WHERE event_offer_id = plan_id)
205 ,'PRIC',(SELECT line.currency_code
206 FROM ozf_resale_lines_all line
207 WHERE line.resale_line_id = object_id)
208 ),
209 last_updated_by = -2,
210 last_update_date = sysdate
211 WHERE fund_request_amount IS NULL
212 AND fund_request_amount_remaining IS NULL
213 AND fund_request_currency_code IS NULL
214 AND plan_currency_code IS NULL;
215
216 IF p_debug_flag = 'Y' THEN
217 l_row_count := sql%rowcount;
218 ozf_utility_pvt.write_conc_log (' <===> mandatory columns are updated for <===>'||l_row_count||' rows updated');
219 END IF;
220 /* Check if any rows are updated by the first sql. If Yes (first time when conc. prog is run), then generate the
221 report and re-calculate the 3 columns for which the definition has been modified
222 1) plan_curr_amount
223 2) plan_curr_amount_remaining
224 3) exchange_rate */
225
226 IF l_row_count > 0 THEN
227 OPEN c_report_header;
228 FETCH c_report_header INTO l_report_header_rec;
229 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_report_header_rec.column_val);
230 CLOSE c_report_header;
231
232 IF p_debug_flag = 'Y' THEN
233 ozf_utility_pvt.write_conc_log (' <===> Report Header Added <===>');
234 END IF;
235
236 OPEN c_report_offers;
237 FETCH c_report_offers BULK COLLECT INTO l_reportOfferRecTbl,l_planIdTbl;
238 FOR t_i IN NVL(l_planIdTbl.FIRST, 1) .. NVL(l_planIdTbl.LAST, 0)
239 LOOP
240 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_reportOfferRecTbl(t_i));
241 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*--------------------------------------------------------------------------------------------------------------*');
242 END LOOP;
243 CLOSE c_report_offers;
244
245 IF p_debug_flag = 'Y' THEN
246 ozf_utility_pvt.write_conc_log (' <===> Report Completed <===>');
247 END IF;
248
249 INSERT INTO OZF_MULTI_CURR_UTIL_BCK (SELECT utilization_id,
250 plan_curr_amount,
251 plan_curr_amount_remaining,
252 exchange_rate
253 FROM ozf_funds_utilized_all_b
254 WHERE NVL(plan_curr_amount,0) <> 0
255 AND plan_currency_code <> (SELECT fund_request_curr_code FROM ozf_offers WHERE qp_list_header_id=plan_id AND transaction_currency_code IS NULL)
256 AND plan_type='OFFR'
257 AND last_updated_by = -2);
258
259 IF p_debug_flag = 'Y' THEN
260 ozf_utility_pvt.write_conc_log (' <===> Backup Completed <===>'||sql%rowcount||' rows inserted');
261 END IF;
262
263 UPDATE /* PARALLEL */ ozf_funds_utilized_all_b
264 SET plan_curr_amount = gl_currency_api.convert_closest_amount_sql(fund_request_currency_code,plan_currency_code,
265 exchange_rate_date,exchange_rate_type,NULL,fund_request_amount,-1),
266 plan_curr_amount_remaining = gl_currency_api.convert_closest_amount_sql(fund_request_currency_code,plan_currency_code,
267 exchange_rate_date,exchange_rate_type,NULL,fund_request_amount_remaining,-1),
268 exchange_rate = gl_currency_api.get_closest_rate(plan_currency_code,get_functional_curr(org_id),exchange_rate_date,exchange_rate_type,0),
269 last_updated_by = -2,
270 last_update_date = sysdate
271 WHERE plan_currency_code <> (SELECT fund_request_curr_code FROM ozf_offers WHERE qp_list_header_id=plan_id AND transaction_currency_code IS NULL)
272 AND NVL(plan_curr_amount,0) <> 0
273 AND plan_type='OFFR'
274 AND last_updated_by = -2;
275
276 IF p_debug_flag = 'Y' THEN
277 ozf_utility_pvt.write_conc_log (' <===> Update for plan_curr_amount/remaining exchange_rate Completed <===>'||sql%rowcount||' rows updated');
278 END IF;
279
280 END IF;
281
282
283 EXCEPTION
284 WHEN fnd_api.g_exc_error THEN
285 ROLLBACK TO Mig_Utilization_Records;
286 ozf_utility_pvt.write_conc_log(' Mig_Utilization_Records: exception '||SQLERRM);
287 fnd_msg_pub.count_and_get (
288 p_count=> l_msg_count,
289 p_data=> l_msg_data,
290 p_encoded=> fnd_api.g_false
291 );
292
293 WHEN fnd_api.g_exc_unexpected_error THEN
294 ROLLBACK TO Mig_Utilization_Records;
295 ozf_utility_pvt.write_conc_log(' Mig_Utilization_Records: exception2 '||SQLERRM);
296 fnd_msg_pub.count_and_get (
297 p_count=> l_msg_count,
298 p_data=> l_msg_data,
299 p_encoded=>fnd_api.g_false
300 );
301 WHEN OTHERS THEN
302 ROLLBACK TO Mig_Utilization_Records;
303 ozf_utility_pvt.write_conc_log(' Mig_Utilization_Records: exception '||SQLERRM);
304 fnd_msg_pub.count_and_get (
305 p_count=> l_msg_count,
306 p_data=> l_msg_data,
307 p_encoded=> fnd_api.g_false
308 );
309 FOR I IN 1..l_msg_count LOOP
310 ozf_utility_pvt.write_conc_log(SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254));
311 END LOOP;
312
313 END Mig_Utilization_Records;
314
315 END ozf_multi_curr_mig_pvt;