[Home] [Help]
PACKAGE BODY: APPS.CN_COLLECTION_AIA_OM_PUB
Source
1 PACKAGE BODY CN_COLLECTION_AIA_OM_PUB AS
2 /* $Header: CNPCLTROMB.pls 120.1 2010/10/26 10:43:51 sseshaiy noship $*/
3
4 g_pkg_name constant VARCHAR2(30) := 'CN_COLLECTION_AIA_OM_PUB';
5 g_file_name constant VARCHAR2(15) := 'CNPCLTROMB.pls';
6 g_cn_debug VARCHAR2(1) := fnd_profile.VALUE('CN_DEBUG');
7
8 PROCEDURE debugmsg(msg VARCHAR2) IS
9 BEGIN
10
11 IF g_cn_debug = 'Y' THEN
12 cn_message_pkg.debug(SUBSTR(msg, 1, 254));
13 --fnd_file.PUT_LINE(fnd_file.LOG, msg);
14 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
15 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cn.plsql.CN_COLLECTION_AIA_OM_PUB', msg);
16 end if;
17 END IF;
18
19 -- comment out dbms_output before checking in file
20 -- dbms_output.put_line(substr(msg,1,254));
21 END debugmsg;
22
23 -- Function name : get_employee_number
24 -- Type : Public.
25 -- Pre-reqs :
26
27 FUNCTION get_employee_number(p_salesrep_id IN cn_aia_order_capture.salesrep_id%TYPE,
28 p_org_id cn_aia_order_capture.org_id%TYPE) RETURN cn_aia_order_capture.employee_number%TYPE IS
29
30 cursor get_emp_num_cur IS
31 SELECT salesrep_number
32 FROM jtf_rs_salesreps
33 WHERE salesrep_id = p_salesrep_id
34 AND org_id = p_org_id;
35
36 l_empnum_cr get_emp_num_cur%ROWTYPE;
37 l_employee_num cn_aia_order_capture.employee_number%TYPE;
38 BEGIN
39 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_employee_number: p_salesrep_id, org_id: ' || p_salesrep_id || ' , ' || p_org_id);
40 l_employee_num := '-1';
41
42 if(p_salesrep_id is not null) Then
43 FOR l_empnum_cr IN get_emp_num_cur
44 LOOP
45 l_employee_num := l_empnum_cr.salesrep_number;
46 END LOOP;
47 END If;
48
49 RETURN l_employee_num;
50
51 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_employee_number: l_employee_num: ' || l_employee_num);
52
53 EXCEPTION
54 WHEN others THEN
55 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_employee_number: exception others: ' || sqlerrm(SQLCODE()));
56 RETURN l_employee_num;
57 END;
58
59 -- Function name : get_exchange_rate
60 -- Type : Public.
61 -- Pre-reqs :
62 FUNCTION get_exchange_rate(p_from_currency IN cn_aia_order_capture.amt_curcy_cd%TYPE,
63 p_conversion_date IN cn_aia_order_capture.processed_date%TYPE,
64 p_org_id IN cn_aia_order_capture.org_id%TYPE) RETURN cn_aia_order_capture.exchange_rate%TYPE IS
65
66 CURSOR c1(l_to_currency varchar2,l_conversion_type varchar2) IS
67 SELECT conversion_date
68 FROM gl_daily_rates
69 WHERE from_currency = p_from_currency
70 AND to_currency = l_to_currency
71 AND conversion_type = l_conversion_type
72 AND conversion_date = p_conversion_date
73 AND rownum < 2
74 ORDER BY conversion_date DESC;
75
76 CURSOR c2(l_to_currency varchar2,l_conversion_type varchar2) IS
77 SELECT MAX(conversion_date) conversion_date
78 FROM gl_daily_rates
79 WHERE from_currency = p_from_currency
80 AND to_currency = l_to_currency
81 AND conversion_type = l_conversion_type
82 AND conversion_date < p_conversion_date;
83
84 l_exchange_rate cn_aia_order_capture.exchange_rate%TYPE;
85 l_to_currency cn_aia_order_capture.amt_curcy_cd%TYPE;
86 l_conversion_date DATE;
90 BEGIN
87 l_conversion_type VARCHAR2(30);
88 l_check_max CHAR(1) := 'Y';
89
91 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: p_curr_code, org_id: ' || p_from_currency || ' , ' || p_org_id);
92 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate:p_conversion_date: ' || p_conversion_date);
93 --
94 l_to_currency := cn_global_var.get_currency_code(p_org_id);
95 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: l_to_currency: ' || l_to_currency);
96 --
97 if( p_from_currency = l_to_currency) Then
98 return 1;
99 end if;
100
101 l_conversion_type := nvl(CN_SYSTEM_PARAMETERS.VALUE('CN_CONVERSION_TYPE', p_org_id), 'Corporate');
102 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: l_conversion_type: ' || l_conversion_type);
103 --
104 FOR rec IN c1(l_to_currency,l_conversion_type)
105 LOOP
106 l_conversion_date := rec.conversion_date;
107 l_check_max := 'N';
108 END LOOP;
109 --
110 IF (l_check_max = 'Y') THEN
111 FOR rec IN c2(l_to_currency,l_conversion_type)
112 LOOP
113 IF (rec.conversion_date IS NOT NULL) THEN
114 l_conversion_date := rec.conversion_date;
115 l_check_max := 'N';
116 END IF;
117 END LOOP;
118 END IF;
119 --
120 IF (l_check_max = 'Y') THEN
121 RETURN l_exchange_rate;
122 ELSE
123 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: l_conversion_date: ' || l_conversion_date);
124 l_exchange_rate := gl_currency_api.get_rate(p_from_currency,
125 l_to_currency,
126 l_conversion_date,
127 l_conversion_type);
128 END IF;
129
130 --
131 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: l_exchange_rate: ' || l_exchange_rate);
132
133 RETURN l_exchange_rate;
134
135 EXCEPTION
136 WHEN others THEN
137 debugmsg('CN_COLLECTION_AIA_OM_PUB.get_exchange_rate: exception others: ' || sqlerrm(SQLCODE()));
138 RETURN l_exchange_rate;
139 END;
140
141 -- API name : oic_pre_load_data_process
142 -- Type : Public.
143 -- Pre-reqs :
144
145 PROCEDURE oic_pre_load_data_process(errbuf OUT NOCOPY VARCHAR2,
146 retcode OUT NOCOPY NUMBER,
147 p_org_id IN NUMBER,
148 x_return_status OUT nocopy VARCHAR2) IS
149
150 CURSOR fetch_aia_salesreps_cur IS
151 SELECT DISTINCT salesrep_id,
152 amt_curcy_cd,processed_date, revenue_type
153 FROM cn_aia_order_capture
154 WHERE
155 preprocess_flag = fnd_api.g_false AND
156 org_id = p_org_id
157 AND update_flag = 'N';
158
159 type salesreps_tbl_type IS TABLE OF fetch_aia_salesreps_cur % rowtype INDEX BY pls_integer;
160 salesreps_tbl salesreps_tbl_type;
161 l_employee_num VARCHAR2(30) := '-1';
162 l_exchange_rate NUMBER := 0;
163 l_revenue_type VARCHAR2(30) := 'REVENUE';
164
165 BEGIN
166 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: start: ');
167 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: p_org_id : ' || p_org_id);
168 SAVEPOINT oic_pre_load;
169 x_return_status := fnd_api.g_ret_sts_success;
170
171 OPEN fetch_aia_salesreps_cur;
172 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: inside fetch_aia_salesreps_cur: ');
173 LOOP
174 FETCH fetch_aia_salesreps_cur bulk collect
175 INTO salesreps_tbl limit 1000;
176
177 FOR indx IN 1 .. salesreps_tbl.COUNT
178 LOOP
179 l_employee_num := get_employee_number(salesreps_tbl(indx).salesrep_id,p_org_id);
180 l_exchange_rate := get_exchange_rate(salesreps_tbl(indx).amt_curcy_cd,salesreps_tbl(indx).processed_date,p_org_id);
181 l_revenue_type := nvl(salesreps_tbl(indx).revenue_type, 'REVENUE');
182
183 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: l_employee_num : ' || l_employee_num);
184 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: l_exchange_rate : ' || l_exchange_rate);
185 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: l_revenue_type : ' || l_revenue_type);
186
187
188 UPDATE cn_aia_order_capture
189 SET employee_number = l_employee_num,
190 exchange_rate = l_exchange_rate,
191 revenue_type = l_revenue_type,
192 adjust_status = 'MANUAL',
193 --preprocess_flag = fnd_api.g_false,
194 update_flag = 'Y'
195 WHERE (salesrep_id = salesreps_tbl(indx).salesrep_id)
196 AND amt_curcy_cd = salesreps_tbl(indx).amt_curcy_cd
197 AND processed_date = salesreps_tbl(indx).processed_date
198 AND nvl(revenue_type, 'REVENUE') = nvl(salesreps_tbl(indx).revenue_type, 'REVENUE')
199 AND update_flag = 'N'
200 AND preprocess_flag = fnd_api.g_false
201 AND org_id = p_org_id;
202
203 END LOOP;
204
205 EXIT
206 WHEN fetch_aia_salesreps_cur % NOTFOUND;
207 END LOOP;
208
209 CLOSE fetch_aia_salesreps_cur;
210
211 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: after fetch_aia_salesreps_cur close statement: ');
212 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: end: ');
213
214 COMMIT;
215
216 EXCEPTION
217 WHEN others THEN
218 x_return_status := 'F';
219 retcode := 2;
220 errbuf := SQLERRM(SQLCODE());
221 debugmsg('CN_COLLECTION_AIA_OM_PUB.oic_pre_load_data_process: exception others: ' || errbuf);
222 ROLLBACK TO oic_pre_load;
223
224 END oic_pre_load_data_process;
225
226 -- API name : pre_aia_om_load_process
230 --+
227 -- Type : Public.
228 -- Pre-reqs :
229 -- Usage :
231 -- Desc :
232 --
233 --
234
235 PROCEDURE pre_aia_om_load_process(errbuf OUT NOCOPY VARCHAR2,
236 retcode OUT NOCOPY NUMBER,
237 p_org_id IN NUMBER
238 ) IS
239
240 x_return_status VARCHAR2(1);
241
242 BEGIN
243 debugmsg('CN_COLLECTION_AIA_OM_PUB.pre_aia_om_load_process: start: ');
244 x_return_status := fnd_api.g_ret_sts_success;
245 retcode := 0;
246 errbuf := '';
247
248 cn_cust_aia_ord_proc_pub.ct_aia_om_pre_processing(x_return_status => x_return_status);
249
250 debugmsg('CN_COLLECTION_AIA_OM_PUB.pre_aia_om_load_process: after cn_cust_aia_ord_proc_pub.ct_aia_om_pre_processing call: ');
251 debugmsg('CN_COLLECTION_AIA_OM_PUB.pre_aia_om_load_process: x_return_status: ' || x_return_status);
252
253 IF(x_return_status <> 'S') THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256
257 oic_pre_load_data_process(errbuf => errbuf,
258 retcode => retcode,
259 p_org_id => p_org_id,
260 x_return_status => x_return_status);
261
262 debugmsg('CN_COLLECTION_AIA_OM_PUB.pre_aia_om_load_process: after oic_pre_load_data_process call: ');
263 debugmsg('CN_COLLECTION_AIA_OM_PUB.pre_aia_om_load_process: x_return_status: ' || x_return_status);
264
265 IF(x_return_status <> 'S') THEN
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267 END IF;
268
269 EXCEPTION
270 WHEN others THEN
271 x_return_status := 'F';
272 retcode := 2;
273 errbuf := errbuf || ' : ' || SQLERRM(SQLCODE());
274 debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:exception others: ' || errbuf);
275 --RAISE FND_API.G_EXC_ERROR;
276 raise_application_error (-20002,errbuf);
277
278 END pre_aia_om_load_process;
279
280 -- API name : post_aia_om_load_process
281 -- Type : Public.
282 -- Pre-reqs :
283 -- Usage :
284 --+
285 -- Desc :
286 --
287 --
288 PROCEDURE post_aia_om_load_process(x_return_status OUT nocopy VARCHAR2) IS
289
290 BEGIN
291
292 x_return_status := fnd_api.g_ret_sts_success;
293
294 END post_aia_om_load_process;
295
296 END CN_COLLECTION_AIA_OM_PUB;