[Home] [Help]
PACKAGE BODY: APPS.GHG_UTILITIES_PKG
Source
1 PACKAGE BODY GHG_UTILITIES_PKG AS
2 /*$Header: ghgutilb.pls 120.4.12020000.2 2012/10/17 04:40:41 sasuren ship $ */
3
4 FUNCTION emissions_exist (x_invoice_id NUMBER) RETURN VARCHAR2 IS
5
6 v_row_count NUMBER(15);
7 v_org_id NUMBER;
8
9 BEGIN
10
11 select org_id
12 into v_org_id
13 from ap_invoices_all
14 where invoice_id = x_invoice_id;
15
16 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id =>v_org_id);
17 -- Must be if one of the lines hits a facility.
18
19 SELECT count(*)
20 INTO v_row_count
21 FROM GHG_TRANSACTIONS_V
22 WHERE invoice_id = x_invoice_id;
23
24 IF v_row_count > 0 THEN
25 RETURN 'Y';
26 ELSE
27 RETURN 'N';
28 END IF;
29
30 END;
31
32 FUNCTION get_conversion(x_from_uom VARCHAR2,
33 x_from_emission_source_id NUMBER,
34 x_from_type VARCHAR2,
35 x_calling_function VARCHAR2) RETURN NUMBER IS
36
37 v_from_emission_rate_uom VARCHAR2(25);
38 v_base_uom VARCHAR2(25);
39 v_from_emission_conv_rate NUMBER;
40 v_emission_conv_rate NUMBER;
41 v_uom_conversion_rate NUMBER;
42 v_debug_info VARCHAR2(100);
43 v_uom_class VARCHAR2(100);
44 v_uom_base VARCHAR2(10);
45 v_uom_profile VARCHAR2(50):=fnd_profile.value('GHG_UOM_CLASS');
46
47 BEGIN
48
49 v_debug_info := 'Starting get_conversion Function. ';
50
51 select x.uom_class,
52 x.base_uom_flag
53 into v_uom_class,
54 v_uom_base
55 from mtl_units_of_measure_vl x
56 where unit_of_measure = x_from_uom;
57
58
59
60 IF v_uom_class = v_uom_profile THEN
61 IF v_uom_base = 'Y' THEN
62 RETURN 1;
63 END IF;
64
65 select unit_of_measure
66 into v_from_emission_rate_uom
67 from mtl_units_of_measure_vl
68 where uom_class = v_uom_profile
69 and base_uom_flag = 'Y';
70
71 SELECT muc.conversion_rate
72 INTO v_from_emission_conv_rate
73 FROM mtl_units_of_measure_vl mum1,
74 mtl_uom_conversions muc
75 WHERE mum1.unit_of_measure = v_from_emission_rate_uom
76 AND muc.unit_of_measure = mum1.unit_of_measure;
77
78 -- Find the emission uom to the base uom conversion.
79 SELECT muc.conversion_rate
80 INTO v_emission_conv_rate
81 FROM mtl_units_of_measure_vl mum1,
82 mtl_uom_conversions muc
83 WHERE mum1.unit_of_measure = x_from_uom
84 AND muc.unit_of_measure = mum1.unit_of_measure;
85
86 --v_uom_conversion_rate := v_from_emission_conv_rate / v_emission_conv_rate;
87 v_uom_conversion_rate := v_emission_conv_rate / v_from_emission_conv_rate;
88
89 RETURN v_uom_conversion_rate;
90 END IF;
91
92
93 IF x_from_type = 'SUPPLIER' THEN
94
95 -- Work out what the supplier emission rate is denominated in and what the base
96 -- unit of measure for this uom class is.
97 SELECT xner.unit_of_measure,
98 mum2.unit_of_measure
99 INTO v_from_emission_rate_uom,
100 v_base_uom
101 FROM GHG_SUPPLIER_FACTORS_V xner,
102 mtl_units_of_measure_vl mum1,
103 mtl_units_of_measure_vl mum2
104 WHERE xner.COMBINATION_OVERRIDE_TYPE = 'Supplier'
105 AND xner.SOURCE_COMBINATION_ID = x_from_emission_source_id
106 AND mum1.unit_of_measure = xner.unit_of_measure
107 AND mum2.uom_class = mum1.uom_class
108 AND mum2.base_uom_flag = 'Y';
109
110 ELSIF x_from_type = 'ITEM' THEN
111
112 -- Work out what the supplier emission rate is denominated in and what the base
113 -- unit of measure for this uom class is.
114 SELECT xner.unit_of_measure,
115 mum2.unit_of_measure
116 INTO v_from_emission_rate_uom,
117 v_base_uom
118 FROM GHG_ITEM_FACTORS_V xner,
119 mtl_units_of_measure_vl mum1,
120 mtl_units_of_measure_vl mum2
121 WHERE xner.COMBINATION_OVERRIDE_TYPE = 'Item'
122 AND xner.SOURCE_COMBINATION_ID = x_from_emission_source_id
123 AND mum1.unit_of_measure = xner.unit_of_measure
124 AND mum2.uom_class = mum1.uom_class
125 AND mum2.base_uom_flag = 'Y';
126
127 ELSIF x_from_type = 'STDUOM' then
128 select mum1.unit_of_measure,
129 mum2.unit_of_measure
130 INTO v_from_emission_rate_uom,
131 v_base_uom
132 from GHG_SOURCES_ALL xnes,
133 --bug 13451779 (sasuren) : changed from GHG_SOURCES to GHG_SOURCES_ALL
134 GHG_UOM_CLASSES class,
135 mtl_units_of_measure_vl mum1,
136 mtl_units_of_measure_vl mum2
137 where xnes.source_id = x_from_emission_source_id
138 and xnes.ghg_uom_class_code = class.ghg_uom_class_code
139 and mum1.unit_of_measure = class.STANDARD_UOM
140 and mum2.unit_of_measure = x_from_uom
141 AND mum2.uom_class = mum1.uom_class;
142
143
144 ELSE
145
146 -- Work out what the source emission rate is denominated in and what the base
147 -- unit of measure for this uom class is.
148 SELECT xnes.unit_of_measure,
149 mum2.unit_of_measure
150 INTO v_from_emission_rate_uom,
151 v_base_uom
152 FROM GHG_SOURCES xnes,
153 mtl_units_of_measure_vl mum1,
154 mtl_units_of_measure_vl mum2
155 WHERE xnes.source_id = x_from_emission_source_id
156 AND mum1.unit_of_measure = xnes.unit_of_measure
157 AND mum2.uom_class = mum1.uom_class
158 AND mum2.base_uom_flag = 'Y';
159
160 END IF;
161
162 -- If the user has changed the uom then calculate the new uom conversion.
163 IF v_from_emission_rate_uom <> x_from_uom THEN
164
165 -- Find the from emission rate uom to the base uom conversion.
166 SELECT muc.conversion_rate
167 INTO v_from_emission_conv_rate
168 FROM mtl_units_of_measure_vl mum1,
169 mtl_uom_conversions muc
170 WHERE mum1.unit_of_measure = v_from_emission_rate_uom
171 AND muc.unit_of_measure = mum1.unit_of_measure;
172
173 -- Find the emission uom to the base uom conversion.
174 SELECT muc.conversion_rate
175 INTO v_emission_conv_rate
176 FROM mtl_units_of_measure_vl mum1,
177 mtl_uom_conversions muc
178 WHERE mum1.unit_of_measure = x_from_uom
179 AND muc.unit_of_measure = mum1.unit_of_measure;
180
181 --v_uom_conversion_rate := v_from_emission_conv_rate / v_emission_conv_rate;
182 v_uom_conversion_rate := v_emission_conv_rate / v_from_emission_conv_rate;
183
184 RETURN v_uom_conversion_rate;
185
186 ELSE
187
188 RETURN 1;
189
190 END IF;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 IF x_calling_function = 'FORM'
195 OR x_calling_function = 'ETL' THEN --Bug 13451779(sasuren):'ETL' added
196 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
197 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
198 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
199 APP_EXCEPTION.RAISE_EXCEPTION;
200
201 ELSE
202
203 RETURN -1; -- Indicates no data found to emissions import.
204
205 END IF;
206 WHEN OTHERS THEN
207 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
208 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
209 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
210 APP_EXCEPTION.RAISE_EXCEPTION;
211
212 END;
213
214 FUNCTION ghg_supplier (x_vendor_id NUMBER,
215 x_vendor_site_id NUMBER) RETURN VARCHAR2 IS
216
217 v_row_count NUMBER(15);
218
219 BEGIN
220
221 SELECT count(*)
222 into V_ROW_COUNT
223 from GHG_SUPPLIER_ITEMS
224 where SUPPLIER_ID = X_VENDOR_ID
225 AND supplier_site_id = x_vendor_site_id;
226
227 IF v_row_count > 0 THEN
228 RETURN 'Y';
229 ELSE
230 RETURN 'N';
231 END IF;
232
233 END;
234
235 FUNCTION ghg_transaction (x_invoice_id NUMBER) RETURN VARCHAR2 IS
236 CURSOR get_invoice_distributions (p_invoice_id NUMBER) IS
237 SELECT ai.org_id,
238 ai.gl_date,
239 ai.vendor_id ,
240 ai.vendor_site_id,
241 ai.invoice_num,
242 aid.distribution_line_number,
243 aid.invoice_line_number,
244 aid.amount,
245 aid.dist_code_combination_id code_combination_id,
246 aid.project,
247 aid.task,
248 aid.po_number
249 FROM ap_invoices_all ai,
250 ap_invoice_distributions_v aid,
251 fnd_lookup_values_vl xnlv
252 WHERE ai.invoice_id = p_invoice_id
253 AND aid.invoice_id = ai.invoice_id
254 AND xnlv.lookup_code = aid.line_type_lookup_code
255 AND xnlv.lookup_type = 'GHG_EMISSION_LINE_TYPES'
256 AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
257 AND NVL(xnlv.end_date_active, SYSDATE + 1) ;
258
259 v_org_id NUMBER;
260 v_set_of_books_id NUMBER;
261 v_chart_of_accounts_id NUMBER;
262 v_map_accounts_flag VARCHAR2(5);
263 v_map_operating_unit_flag VARCHAR2(5);
264 v_map_projects_flag VARCHAR2(5);
265 v_facility_id_for_op_unit NUMBER;
266 v_ok VARCHAR2(10) := 'NOTOK';
267 v_facility VARCHAR2(100);
268 v_facility_id NUMBER;
269 v_is_po VARCHAR2(10) := 'N';
270
271 BEGIN
272
273 select org_id
274 into v_org_id
275 from ap_invoices_all
276 where invoice_id = x_invoice_id;
277
278 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id =>v_org_id);
279 -- Must be if one of the lines hits a facility.
280
281 SELECT asp.set_of_books_id,
282 gsob.chart_of_accounts_id
283 INTO v_set_of_books_id,
284 v_chart_of_accounts_id
285 FROM ap_system_parameters asp,
286 gl_sets_of_books gsob
287 WHERE gsob.set_of_books_id = asp.set_of_books_id;
288 -- AND org_id = v_org_id;
289
290 -- Determine how we are mapping the facility.
291
292 SELECT map_accounts_flag,
293 map_operating_unit_flag,
294 map_projects_flag
295 INTO v_map_accounts_flag,
296 v_map_operating_unit_flag,
297 v_map_projects_flag
298 FROM GHG_SEGMENT_MAPPINGS_V;
299
300
301 FOR dist_rec in get_invoice_distributions(x_invoice_id) LOOP
302 BEGIN
303 IF v_map_accounts_flag = 'Y' THEN
304 IF v_map_projects_flag = 'Y' THEN
305 IF dist_rec.project IS NOT NULL THEN
306 -- Note that the package being called will issue an error message if it can't find a mapping, so there's no need
307 -- for this trigger to handle this exception.
308 v_facility_id := GHG_ORGANIZATION_MAPPINGS_PKG.find_project_mapping(dist_rec.distribution_line_number, dist_rec.project, dist_rec.task, dist_rec.gl_date);
309 ELSE
310 v_facility_id := GHG_ORGANIZATION_MAPPINGS_PKG.find_flexfield_mapping(dist_rec.distribution_line_number, dist_rec.code_combination_id, v_chart_of_accounts_id, dist_rec.gl_date);
311 END IF;
312 ELSE
313 v_facility_id := GHG_ORGANIZATION_MAPPINGS_PKG.find_flexfield_mapping(dist_rec.distribution_line_number, dist_rec.code_combination_id, v_chart_of_accounts_id, dist_rec.gl_date);
314 END IF;
315 ELSIF v_map_projects_flag = 'Y' THEN
316 IF dist_rec.project IS NOT NULL THEN
317 v_facility_id := GHG_ORGANIZATION_MAPPINGS_PKG.find_project_mapping(dist_rec.distribution_line_number, dist_rec.project, dist_rec.task, dist_rec.gl_date);
318 ELSIF v_map_operating_unit_flag = 'Y' THEN
319 v_facility_id := v_facility_id_for_op_unit;
320 ELSE
321 v_facility_id := -1;
322 END IF;
323 ELSIF v_map_operating_unit_flag = 'Y' THEN
324 v_facility_id := v_facility_id_for_op_unit;
325 ELSE
326 -- Sending to Bucket
327 -- To -1 Facfilty
328 v_facility_id := -1;
329 END IF;
330 EXCEPTION
331 WHEN OTHERS THEN
332 -- Sending to Bucket
333 -- To -1 Facility
334 v_facility_id := -1;
335 END ;
336
337 IF v_facility_id <> -1 AND v_ok = 'NOTOK' THEN
338 v_ok := 'OK';
339 END IF;
340
341 IF dist_rec.po_number IS NOT NULL
342 THEN
343 v_is_po := 'Y';
344 END IF;
345
346 END LOOP;
347
348 IF (v_ok = 'OK') THEN
349 IF v_is_po = 'Y' THEN
350 RETURN 'P';
351 ELSE
352 RETURN 'Y';
353 END IF;
354 ELSE
355 RETURN 'N';
356 END IF;
357
358 END;
359
360 FUNCTION get_default_source (x_supplier_id NUMBER,
361 x_supplier_site_id NUMBER) RETURN VARCHAR2
362 IS
363 vl_emission_source VARCHAR2(255);
364 BEGIN
365 SELECT SOURCE_NAME
366 INTO vl_emission_source
367 FROM GHG_SUPPLIER_ITEMS sd
368 , GHG_SOURCES es
369 WHERE es.source_id = sd.source_id
370 AND sd.supplier_id = x_supplier_id
371 AND sd.supplier_site_id = x_supplier_site_id
372 AND es.enabled= 'Y';
373
374 RETURN vl_emission_source;
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 RETURN NULL;
379
380 END;
381
382
383 PROCEDURE automate_emissions (x_invoice_id NUMBER)
384 IS
385 CURSOR GET_PO_MATCHES IS
386 select a.invoice_id
387 , a.vendor_id
388 , a.vendor_site_id
389 , p.segment1 po_number
390 , a.org_id
391 from ap_invoices_all a
392 , ap_invoice_lines_all b
393 , po_headers_all p
394 where a.invoice_id = b.invoice_id
395 and b.po_header_id = p.po_header_id
396 and a.cancelled_date is null
397 and a.invoice_id = x_invoice_id;
398
399 V_TXN_TYPE_LOOKUP_CODE VARCHAR2(200) := 'INT_INVOICE';
400 V_INVOICE_ID NUMBER := X_INVOICE_ID;
401 V_CREATED_BY NUMBER := fnd_profile.value('USER_ID');
402 V_LAST_UPDATE_LOGIN NUMBER := -1;
403 V_MESG VARCHAR2(4000);
404
405 BEGIN
406 -- Check PO Match
407 FOR PO_RECS in GET_PO_MATCHES LOOP
408 -- mo_global.set_policy_context_server(p_access_mode => 'S' ,p_org_id => X_ORG_ID);\n\
409
410 GHG_PO_EMISSIONS_PKG.CREATE_PO_EMISSIONS(
411 X_TXN_TYPE_LOOKUP_CODE => V_TXN_TYPE_LOOKUP_CODE,
412 X_INVOICE_ID => V_INVOICE_ID,
413 X_VENDOR_ID => PO_RECS.vendor_id,
414 X_VENDOR_SITE_ID => PO_RECS.VENDOR_SITE_ID,
415 X_PO_NUMBER => PO_RECS.PO_NUMBER,
416 X_CREATED_BY => V_CREATED_BY,
417 X_LAST_UPDATE_LOGIN => V_LAST_UPDATE_LOGIN,
418 X_MESG => V_MESG,
419 x_measurement_criteria => 'A',
420 x_org_id => PO_RECS.ORG_ID );
421 END LOOP;
422
423 END;
424
425 FUNCTION get_multiplier(x_invoice_id NUMBER, x_inv_line_num NUMBER, x_dist_line_num NUMBER) RETURN NUMBER
426 IS
427 vl_invoice_total NUMBER;
428 vl_inv_perc NUMBER;
429 BEGIN
430 select sum(amount)
431 into vl_invoice_total
432 from ap_invoice_distributions_v v
433 where invoice_id = x_invoice_id
434 and nvl(reversal_flag,'N') <> 'Y'
435 and line_type_lookup_code in
436 (SELECT LOOKUP_CODE
437 FROM FND_LOOKUPS
438 WHERE lookup_type = 'GHG_EMISSION_LINE_TYPES'
439 AND trunc(sysdate) between trunc(start_date_active) and
440 trunc(nvl(end_date_active, GHG_UTILITIES_PKG.end_date ))
441 );
442
443 select amount/vl_invoice_total
444 into vl_inv_perc
445 from ap_invoice_distributions_v
446 where invoice_id = x_invoice_id
447 and distribution_line_number = x_dist_line_num
448 and invoice_line_number = x_inv_line_num;
449
450 return vl_inv_perc;
451
452 END;
453
454
455 FUNCTION is_ghg_item (x_item_id NUMBER) RETURN VARCHAR2
456 IS
457 vl_result VARCHAR2(1);
458 BEGIN
459
460 SELECT 'Y'
461 INTO vl_result
462 FROM GHG_SUPPLIER_ITEMS
463 WHERE inventory_item_id = x_item_id;
464
465 RETURN 'Y';
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 RETURN 'N';
470
471 END;
472
473
474 END GHG_UTILITIES_PKG;