[Home] [Help]
PACKAGE BODY: APPS.GHG_PO_EMISSIONS_PKG
Source
1 PACKAGE BODY GHG_PO_EMISSIONS_PKG AS
2 /*$Header: ghgpoemb.pls 120.5.12020000.2 2012/10/17 04:28:56 sasuren ship $ */
3
4 PROCEDURE create_po_emissions (x_txn_type_lookup_code VARCHAR2,
5 x_invoice_id NUMBER,
6 x_vendor_id NUMBER,
7 x_vendor_site_id NUMBER,
8 x_po_number VARCHAR2,
9 x_created_by NUMBER,
10 x_last_update_login NUMBER,
11 x_mesg IN OUT NOCOPY VARCHAR2,
12 x_measurement_criteria VARCHAR2,
13 x_org_id NUMBER) IS
14
15 NO_RATE_FOUND EXCEPTION;
16
17 v_org_id NUMBER(15);
18 v_set_of_books_id NUMBER(15);
19 v_chart_of_accounts_id NUMBER(15);
20 v_map_accounts_flag GHG_SEGMENT_MAPPINGS_V.map_accounts_flag%TYPE;
21 v_map_operating_unit_flag GHG_SEGMENT_MAPPINGS_V.map_operating_unit_flag%TYPE;
22 v_map_projects_flag GHG_SEGMENT_MAPPINGS_V.map_projects_flag%TYPE;
23 v_facility_id_for_op_unit NUMBER(15) := NULL;
24 v_po_match_emission_date GHG_SYSTEM_PARAMETERS_V.po_match_emission_date%TYPE;
25 v_receipt_match_emission_date GHG_SYSTEM_PARAMETERS_V.receipt_match_emission_date%TYPE;
26 v_emission_date DATE;
27 v_highest_emission_number NUMBER(15);
28 v_facility_id NUMBER(15);
29 v_emission_id NUMBER(15);
30 v_emission_rate_id NUMBER(15);
31 v_rate_unit_of_measure VARCHAR2(25);
32 v_emission_source_id GHG_ITEM_FACTORS_V.source_id%TYPE;
33 v_emission_scope_lookup_code GHG_ITEM_FACTORS_V.emission_scope_lookup_code%TYPE;
34 v_emission_factor NUMBER;
35 v_emission_energy NUMBER;
36 v_supplier_emission_rate_id NUMBER(15);
37 v_supplier_rate_uom VARCHAR2(25);
38 v_supplier_emission_factor NUMBER;
39 v_supplier_emission_energy NUMBER;
40 v_po_unit_of_measure po_lines_v.unit_meas_lookup_code%TYPE;
41 v_code_combination_id NUMBER(15);
42 v_uom_conversion NUMBER;
43 v_rowid VARCHAR2(24);
44 v_debug_info VARCHAR2(100);
45 v_action VARCHAR2(10);
46 v_requery_required VARCHAR2(1) := 'N';
47 v_mesg VARCHAR2(240);
48 v_facility VARCHAR2(100);
49 v_batch_desc VARCHAR2(4000);
50 v_location_code VARCHAR2(100);
51 v_emission_source VARCHAR2(4000);
52 v_invoice_msg VARCHAR2(4000);
53
54 CURSOR get_emissions (i_invoice_id NUMBER) IS
55 SELECT transaction_id
56 FROM GHG_TRANSACTIONS_V
57 WHERE invoice_id = i_invoice_id;
58
59 CURSOR get_invoice_distributions (i_invoice_id NUMBER) IS
60 SELECT ai.invoice_date,
61 ai.invoice_num,
62 ai.gl_date,
63 pvs.match_option,
64 aid.distribution_line_number,
65 aid.invoice_line_number,
66 aid.quantity_invoiced,
67 aid.po_line_id,
68 aid.po_distribution_id,
69 DECODE(aid.receipt_number, NULL, NULL,
70 '. Receipt Num: ' ||aid.receipt_number) receipt_number,
71 aid.rcv_transaction_id,
72 aid.project,
73 aid.task,
74 pl.item_id,
75 pl.unit_meas_lookup_code,
76 pl.po_header_id,
77 pl.item_number,
78 pd.code_combination_id
79 FROM ap_invoices_all ai,
80 po_vendor_sites pvs,
81 ap_invoice_distributions_v aid,
82 po_lines_v pl,
83 po_distributions_v pd,
84 fnd_lookup_values_vl xnlv
85 WHERE ai.invoice_id = i_invoice_id
86 AND pvs.vendor_site_id = ai.vendor_site_id
87 AND aid.invoice_id = ai.invoice_id
88 AND aid.po_distribution_id IS NOT NULL
89 AND xnlv.lookup_code = aid.line_type_lookup_code
90 AND xnlv.lookup_type = 'GHG_PO_MATCH_EMISSION_LINE_TYP'
91 AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
92 AND NVL(xnlv.end_date_active, SYSDATE + 1)
93 AND pl.po_line_id = aid.po_line_id
94 AND pl.item_id IS NOT NULL
95 AND pd.po_distribution_id = aid.po_distribution_id
96 ORDER BY aid.distribution_line_number;
97
98 BEGIN
99
100 v_debug_info := 'Set Context';
101 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id =>x_org_id);
102
103 v_debug_info := 'Delete any existing emissions';
104
105 -- If the current invoice has emissions, then archive them to history and then delete them.
106 FOR emission_rec IN get_emissions(x_invoice_id) LOOP
107 GHG_TRANSACTIONS_HISTORY_PKG.insert_row(x_transaction_id => emission_rec.transaction_id);
108 ghg_transactions_pkg.delete_row(x_emission_id => emission_rec.transaction_id);
109 delete from GHG_TRANSACTION_DETAILS_ALL where transaction_id = emission_rec.transaction_id;
110 END LOOP;
111
112 v_debug_info := 'Get coa_id';
113
114 SELECT asp.org_id,
115 asp.set_of_books_id,
116 gsob.chart_of_accounts_id
117 INTO v_org_id,
118 v_set_of_books_id,
119 v_chart_of_accounts_id
120 FROM ap_system_parameters asp,
121 gl_sets_of_books gsob
122 WHERE gsob.set_of_books_id = asp.set_of_books_id;
123
124 -- Determine how we are mapping the facility.
125 v_debug_info := 'Get mapping setup';
126
127 SELECT map_accounts_flag,
128 map_operating_unit_flag,
129 map_projects_flag
130 INTO v_map_accounts_flag,
131 v_map_operating_unit_flag,
132 v_map_projects_flag
133 FROM GHG_SEGMENT_MAPPINGS_V;
134
135 -- If we are setup to map to operating unit then get the mapping value now.
136 --IF v_map_operating_unit_flag = 'Y' THEN
137
138 --SELECT facility_id
139 --INTO v_facility_id_for_op_unit
140 --FROM XX_GHGAS_facility_ou_map_v;
141
142 --END IF;
143
144 v_debug_info := 'Get supplier GHG system parameters';
145
146 -- Get emission date option.
147 SELECT xnsp.po_match_emission_date,
148 xnsp.receipt_match_emission_date
149 INTO v_po_match_emission_date,
150 v_receipt_match_emission_date
151 FROM GHG_SYSTEM_PARAMETERS_V xnsp;
152
153 v_debug_info := 'Get item emission line number';
154
155 -- Determine the last emission line number for the current invoice.
156 SELECT NVL((MAX(transaction_line_number)), 0)
157 INTO v_highest_emission_number
158 FROM (SELECT MAX(transaction_line_number) transaction_line_number
159 FROM GHG_TRANSACTIONS_V
160 WHERE invoice_id = x_invoice_id
161 UNION
162 SELECT MAX(transaction_line_number) transaction_line_number
163 FROM GHG_TRANSACTIONS_HISTORY_V
164 WHERE invoice_id = x_invoice_id);
165
166 FOR dist_rec IN get_invoice_distributions (x_invoice_id) LOOP
167
168 -- Determine emission date.
169 IF dist_rec.match_option = 'P' THEN
170
171 IF v_po_match_emission_date = 'INVOICE' THEN
172
173 v_emission_date := dist_rec.invoice_date;
174
175 ELSIF v_po_match_emission_date = 'RECEIPT' THEN
176
177 SELECT MAX(rt.transaction_date)
178 INTO v_emission_date
179 FROM rcv_transactions rt
180 WHERE rt.po_header_id IN (SELECT ph.po_header_id
181 FROM po_headers_v ph
182 WHERE ph.segment1 = x_po_number)
183 AND rt.transaction_type = 'RECEIVE';
184
185 IF v_emission_date IS NULL THEN
186 v_emission_date := dist_rec.invoice_date;
187 END IF;
188
189 ELSE
190
191 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_EM_DATE_OPTION');
192 FND_MESSAGE.SET_TOKEN('DATE_OPTION', v_po_match_emission_date);
193 APP_EXCEPTION.RAISE_EXCEPTION;
194
195 END IF;
196
197 ELSE
198
199 IF v_receipt_match_emission_date = 'INVOICE' OR dist_rec.receipt_number IS NULL THEN
200
201 v_emission_date := dist_rec.invoice_date;
202
203 ELSIF v_receipt_match_emission_date = 'RECEIPT' THEN
204
205 SELECT rt.transaction_date
206 INTO v_emission_date
207 FROM rcv_transactions rt
208 WHERE rt.transaction_id = dist_rec.rcv_transaction_id;
209
210 ELSE
211
212 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_EM_DATE_OPTION');
213 FND_MESSAGE.SET_TOKEN('DATE_OPTION', v_receipt_match_emission_date);
214 APP_EXCEPTION.RAISE_EXCEPTION;
215
216 END IF;
217
218 END IF;
219
220 -- Determine the uom conversion, if the uom on the PO is different to the uom on the supplier emission rate record.
221 IF v_rate_unit_of_measure <> dist_rec.unit_meas_lookup_code THEN
222 v_debug_info := 'Get uom conversion';
223 v_uom_conversion := GHG_UTILITIES_PKG.get_conversion (dist_rec.unit_meas_lookup_code,
224 v_emission_rate_id,
225 'GENERAL',
226 'FORM');
227 ELSE
228 v_uom_conversion := 1;
229 END IF;
230
231 --IF dist_rec.destination_type_code = 'INVENTORY' THEN -- taken out as not all po's have a value of inventory (some have expense for example)
232
233 v_debug_info := 'Get organization_id';
234 -- dbms_output.put_line(v_debug_info);
235
236 BEGIN
237 IF v_map_accounts_flag = 'Y' THEN
238 IF v_map_projects_flag = 'Y' THEN
239 IF dist_rec.project IS NOT NULL THEN
240 -- Note that the package being called will issue an error message if it can't find a mapping, so there's no need
241 -- for this trigger to handle this exception.
242 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);
243 ELSE
244 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);
245 END IF;
246 ELSE
247 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);
248 END IF;
249 ELSIF v_map_projects_flag = 'Y' THEN
250 IF dist_rec.project IS NOT NULL THEN
251 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);
252 ELSIF v_map_operating_unit_flag = 'Y' THEN
253 v_facility_id := v_facility_id_for_op_unit;
254 ELSE
255 FND_MESSAGE.SET_NAME('GHG', 'GHG_PROJECT_NOT_SPECIFIED');
256 APP_EXCEPTION.RAISE_EXCEPTION;
257 END IF;
258 ELSIF v_map_operating_unit_flag = 'Y' THEN
259 v_facility_id := v_facility_id_for_op_unit;
260 ELSE
261 -- Sending to Bucket
262 -- To -1 Facfilty
263 v_facility_id := -1;
264 -- FND_MESSAGE.SET_NAME('XX_GHGAS', 'XX_GHGAS_MAPPING_SETUP_MISSING');
265 -- APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 EXCEPTION
268 WHEN OTHERS THEN
269 -- Sending to Bucket
270 -- To -1 Facfilty
271 v_facility_id := -1;
272 --dbms_output.put_line('Error'||sqlerrm);
273 END ;
274
275 -- v_debug_info := 'Facility is :'|| v_facility_id ;
276 -- dbms_output.put_line(v_debug_info);
277
278
279 -- Get Default Sources
280 -- First For Item and then for Supplier
281 -- It we can't find any then we stop
282
283 BEGIN
284
285 -- dbms_output.put_line('DIST================='||dist_rec.item_id);
286
287 SELECT s.source_name,
288 nvl(def.location_code,'ALL') location_code,
289 nvl(def.scope_lookup_code,1) scope_lookup_code
290 INTO v_emission_source,
291 v_location_code,
292 v_emission_scope_lookup_code
293 FROM GHG_SUPPLIER_ITEMS def
294 , GHG_SOURCES s
295 WHERE def.source_id = s.source_id
296 AND def.inventory_item_id = dist_rec.item_id
297 AND s.enabled = 'Y'
298 -- AND def.group_default = 'Y'
299 ;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 BEGIN
303 SELECT s.source_name,
304 def.location_code,
305 def.scope_lookup_code
306 INTO v_emission_source,
307 v_location_code,
308 v_emission_scope_lookup_code
309 FROM GHG_SUPPLIER_ITEMS def
310 , GHG_SOURCES s
311 WHERE def.source_id = s.source_id
312 AND def.supplier_id = x_vendor_id
313 and def.supplier_site_id = x_vendor_site_id
314 AND s.enabled = 'Y'
315 AND def.group_default = 'Y';
316
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 RAISE NO_RATE_FOUND;
320 END;
321 END;
322
323 -- v_debug_info := 'Others :'|| v_emission_source||' '||v_location_code||' '||v_emission_scope_lookup_code ;
324 -- dbms_output.put_line(v_debug_info);
325
326
327
328 SELECT GHG_ORGANIZATION_CODE
329 into v_facility
330 FROM GHG_ORGANIZATIONS
331 WHERE GHG_ORGANIZATION_ID = v_facility_id;
332
333 FND_MESSAGE.SET_NAME('GHG','GHG_INVOICE_BATCH_NUM_PREFIX');
334 v_invoice_msg := FND_MESSAGE.GET;
335
336 --select dist_rec.invoice_num
337 --into v_batch_desc
338 --from ap_suppliers
339 --where vendor_id = x_vendor_id;
340
341 SELECT GHG_TRANSACTIONS_S.NEXTVAL
342 INTO v_emission_id
343 FROM sys.dual;
344
345 v_highest_emission_number := v_highest_emission_number + 1;
346 v_debug_info := 'Insert emission';
347 -- dbms_output.put_line('v_debug_info');
348
349 -- dbms_output.put_line('Ready to insert');
350 -- dbms_output.put_line('v_debug_info:'||dist_rec.item_number);
351
352
353 FND_MESSAGE.SET_NAME('GHG','GHG_PO_MATCH_BATCH_NUM_PREFIX');
354 v_mesg := FND_MESSAGE.GET;
355
356 GHG_TRANSACTIONS_API_PKG.create_transaction(
357 p_org_id => v_org_id,
358 p_batch_type => x_txn_type_lookup_code ,
359 p_batch_sub_type => NULL ,
360 p_batch_number => v_invoice_msg || dist_rec.invoice_num,
361 p_batch_description => dist_rec.invoice_num,
362 p_facility => v_facility,
363 p_emission_source_name => v_emission_source,
364 p_emission_scope => v_emission_scope_lookup_code,
365 p_emission_location => v_location_code,
366 p_emission_usage => dist_rec.quantity_invoiced,
367 p_emission_uom => dist_rec.unit_meas_lookup_code,
368 p_emission_from_date => v_emission_date,
369 p_emission_to_date => v_emission_date,
370 p_emission_description => v_mesg || x_po_number || dist_rec.receipt_number,
371 p_supplier => x_vendor_id,
372 p_supplier_site_name => x_vendor_site_id,
373 p_item_number => dist_rec.item_number,
374 p_ghgas_asset => NULL,
375 p_invoice_id => x_invoice_id,
376 p_invoice_line_num => dist_rec.invoice_line_number,
377 p_invoice_dist_num => dist_rec.distribution_line_number,
378 p_m_criteria => x_measurement_criteria);
379 END LOOP;
380
381 EXCEPTION
382 WHEN NO_RATE_FOUND THEN
383 NULL;
384
385 WHEN OTHERS THEN
386 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
387 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
388 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
389 APP_EXCEPTION.RAISE_EXCEPTION;
390
391
392 END create_po_emissions;
393
394 END GHG_PO_EMISSIONS_PKG;