[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_RPT_CUR_PVT
Source
1 PACKAGE BODY jmf_shikyu_rpt_cur_pvt AS
2 --$Header: JMFVCURB.pls 120.25 2008/03/22 06:21:14 kdevadas ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFVCURB.pls |
10 --| |
11 --| DESCRIPTION: Body file of the package for creating temporary |
12 --| data for the Shikyu Cost Update Analysis report. |
13 --| |
14 --| FUNCTION/PROCEDURE: cuar_get_cost_data |
15 --| cuar_get_unreceived_po |
16 --| cuar_get_unshipped_so |
17 --| cuar_get_rma_so |
18 --| cuar_get_item_cost |
19 --| get_uom_primary |
20 --| get_uom_primary_code |
21 --| get_uom_primary_qty |
22 --| get_uom_primary_qty_from_code |
23 --| |
24 --| HISTORY: |
25 --| 28-MAY-2005 fwang Created. |
26 --| 18-NOV-2005 shu added code for setting request completed |
27 --| with warning if SHIKYU profile is disable |
28 --| 28-Nov-2005 Sherly updated for a few of issues for unreceived |
29 --| and unshipped |
30 --| 30-Nov-2005 Sherly updated function currency part and RMA |
31 --| Dec-5-2005 Sherly updated cuar_get_cost_data by adding a |
32 --| parameter "function_currency" |
33 --| Sherly updated cuar_get_unrecived_po and |
34 --| cuar_get_unshipped_so for exception |
35 --| Dec-6-2005 Sherly udpated function IS_CURRENT_PERIOD to add |
36 --| sysdate logic |
37 --| Dec-8-2005 Sherly udpated function cuar_get_unreceived_po and |
38 --| cuar_get_unshipped_so to excelude the qty = |
39 --| 0 or froze cost = plan cost. |
40 --| Dec-9-2005 Sherly updated function cuar_get_rma_so to exclude |
41 --| lines with amount = 0 |
42 --| Dec-12-2005 Sherly updated function cuar_get_unreceived_po and |
43 --| cuar_get_unshipped_so to replace org_id with|
44 --| ship_to_location_id when joined with mtl |
45 --| Dec-21-2005 Sherly update procedure cuar_get_rma_so to fix |
46 --| some performance issue |
47 --| Jan-10-2006 Sherly udpated all procedures to standarize the |
48 --| log info |
49 --| Jan-18-2006 Sherly updated cuar_get_unreceived_po and |
50 --| cuar_get_unshipped_so for log message |
51 --| translation |
52 --| FEB-07-2006 Amy solve potential issue of date conversion |
53 --| MAY-16-2006 Amy solve bug 5212968 |
54 --| MAY-23-2006 Amy solve bug 5232878: |
55 --| Modified l_cur_get_unreceived_po in |
56 --| cuar_get_unreceived_po to get vendor_id and |
57 --| vendor_site_id from |
58 --| hr_organization_information for the |
59 --| TP Organization instead of OEM Organization.|
60 --| Also, set the concurrent request status to |
61 --| warning if the un-shipped SOs are not |
62 --| defined in functional currency, and if no |
63 --| values were supplied for the |
64 --| 'Currency conversion type' and |
65 --| 'Currency conversion date' concurrent |
66 --| request parameters. |
67 --| Jul-6-2006 Amy updated procedure cuar_get_unshipped_so to solve bug5232878 |
68 --| Jul-7-2006 Amy updated procedure cuar_get_unreceived_po and cuar_get_unshipped_so to solve project number related issue. |
69 --| Aug-14-2006 Amy updated procedure cuar_get_unshipped_so to fix bug#5462851. |
70 --| Sep-01-2006 Amy updated procedure cuar_get_rma_so to fix bug#5506431. |
71 --| Sep-20-2006 Amy updated procedure cuar_get_unreceived_po to add release number |
72 --| Nov-07-2006 Amy updated procedure cuar_get_unreceived_po to add default coverstion type/date in price conversion function |
73 --| 04-OCT-2007 kdevadas Buy/Sell Subcontracting changes |
74 --| Reference - GBL_BuySell_TDD.doc |
75 --| Reference - GBL_BuySell_FDD.doc |
76 --| 04-OCT-2007 kdevadas Bug 6773949 |
77 --| Cost Update Analysis Report should display |
78 --| data only Chargeable Subcontracting enabled |
79 --| OEM orgs |
80 --+===========================================================================+
81
82 --=============================================
83 -- CONSTANTS
84 --=============================================
85 g_pkg_name CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_RPT_CUR_PVT';
86 g_module_prefix CONSTANT VARCHAR2(50) := 'jmf.plsql.' || g_pkg_name || '.';
87
88 --=============================================
89 -- GLOBAL VARIABLES
90 --=============================================
91
92 g_debug_level NUMBER := fnd_log.g_current_runtime_level;
93 g_proc_level NUMBER := fnd_log.level_procedure;
94
95 g_rate_not_found VARCHAR2(1) := 'N';
96
97 --g_unexp_level NUMBER := fnd_log.level_unexpected;
98 g_excep_level NUMBER := fnd_log.level_exception;
99
100 --========================================================================
101 -- PROCEDURE : cuar_get_cost_data PUBLIC
102 -- PARAMETERS: p_cost_type_id cost type id
103 -- : p_ou_id operating unit id
104 -- : p_inv_org_name_from oem inventory org name from
105 -- : p_inv_org_name_to oem inventory org name to
106 -- : p_run report run type
107 -- : p_currency_cnv_type currency conversion type
108 -- : p_currency_cnv_date currency conversion date
109 -- : p_rate_not_found Currency conversion Rate not found flag
110 -- COMMENT : used as portal to choose process according to run type
111 -- PRE-COND :
112 -- EXCEPTIONS:
113 --========================================================================
114 PROCEDURE cuar_get_cost_data
115 (
116 p_cost_type_id IN NUMBER
117 ,p_org_id IN NUMBER
118 ,p_inv_org_name_from IN VARCHAR2
119 ,p_inv_org_name_to IN VARCHAR2
120 ,p_run IN VARCHAR2
121 ,p_currency_cnv_type IN VARCHAR2
122 ,p_currency_cnv_date IN VARCHAR2
123 ,p_function_currency IN VARCHAR2
124 ,p_rate_not_found OUT NOCOPY VARCHAR2
125 ) IS
126 l_func_currency_code jmf_shikyu_cur_rpt_temp.func_currency_code%TYPE;
127 l_currency_cnv_date DATE;
128 l_api_name CONSTANT VARCHAR2(30) := 'cuar_get_cost_data';
129
130
131 BEGIN
132
133 --g_debug_level := fnd_log.g_current_runtime_level;
134
135 IF (g_proc_level >= g_debug_level)
136 THEN
137 fnd_log.STRING(g_proc_level
138 ,g_module_prefix || l_api_name || '.begin'
139 ,NULL);
140 END IF;
141
142 l_func_currency_code :=p_function_currency ;
143
144 DELETE FROM jmf_shikyu_cur_rpt_temp;
145 COMMIT;
146
147 /* the input date format depends on server's setting.
148 To avoid the potential issues,use function which is not using mask.
149 */
150 l_currency_cnv_date := fnd_date.canonical_to_date(p_currency_cnv_date);
151
152 IF p_run = 'RUN_BEFORECOSTUPDATE'
153 THEN
154 cuar_get_unreceived_po(p_cost_type_id => p_cost_type_id
155 ,p_org_id => p_org_id
156 ,p_inv_org_name_from => p_inv_org_name_from
157 ,p_inv_org_name_to => p_inv_org_name_to
158 ,p_currency_cnv_type => p_currency_cnv_type
159 ,p_currency_cnv_date => l_currency_cnv_date
160 ,p_func_currency_code => l_func_currency_code
161 );
162
163 cuar_get_unshipped_so(p_cost_type_id => p_cost_type_id
164 ,p_org_id => p_org_id
165 ,p_inv_org_name_from => p_inv_org_name_from
166 ,p_inv_org_name_to => p_inv_org_name_to
167 ,p_currency_cnv_type => p_currency_cnv_type
168 ,p_currency_cnv_date => l_currency_cnv_date
169 ,p_func_currency_code => l_func_currency_code
170 );
171
172 ELSIF p_run = 'RUN_ATPERIODEND'
173 THEN
174 cuar_get_rma_so(p_org_id => p_org_id
175 ,p_inv_org_name_from => p_inv_org_name_from
176 ,p_inv_org_name_to => p_inv_org_name_to
177 ,p_func_currency_code => l_func_currency_code);
178 END IF;
179
180 p_rate_not_found := g_rate_not_found;
181
182 EXCEPTION
183 WHEN no_data_found THEN
184 IF (g_excep_level >= g_debug_level)
185 THEN
186 fnd_log.STRING(g_excep_level
187 ,g_module_prefix || l_api_name || '.exception'
188 ,'no_data_found');
189 END IF;
190 WHEN OTHERS THEN
191
192 IF (g_excep_level >= g_debug_level)
193 THEN
194 fnd_log.STRING(g_excep_level
195 ,g_module_prefix || l_api_name || '.exception'
196 ,SQLERRM);
197 END IF;
198 END cuar_get_cost_data;
199
200 --========================================================================
201 -- PROCEDURE : cuar_get_unreceived_po PUBLIC
202 -- PARAMETERS: p_cost_type_id cost type id
203 -- : p_ou_id operating unit id
204 -- : p_inv_org_name_from oem inventory org name from
205 -- : p_inv_org_name_to oem inventory org name to
206 -- : p_currency_cnv_type currency conversion type
207 -- : p_currency_cnv_date currency conversion date
208 -- : p_func_currency_code functional currency code
209 -- COMMENT : collect appropriate unreceived po qty data and insert into
210 -- the temporary table
211 -- PRE-COND :
212 -- EXCEPTIONS:
213 --========================================================================
214 PROCEDURE cuar_get_unreceived_po
215 (
216 p_cost_type_id IN NUMBER
217 ,p_org_id IN NUMBER
218 ,p_inv_org_name_from IN VARCHAR2
219 ,p_inv_org_name_to IN VARCHAR2
220 ,p_currency_cnv_type IN VARCHAR2
221 ,p_currency_cnv_date IN DATE
222 ,p_func_currency_code IN VARCHAR2
223 ) IS
224 l_api_name CONSTANT VARCHAR2(30) := 'cuar_get_unreceived_po';
225 CURSOR l_cur_get_unreceived_po(lp_cost_type_id NUMBER, lp_org_id NUMBER, lp_inv_org_name_from VARCHAR2, lp_inv_org_name_to VARCHAR2, lp_currency_cnv_type VARCHAR2, lp_currency_cnv_date DATE, lp_func_currency_code VARCHAR2) IS
226 SELECT 'UnReceived'
227 ,haotl.NAME
228 --updated to fix project_number related issue start
229 --,pa.segment1
230 ,NVL((SELECT DISTINCT segment1 AS project_number
231 FROM pa_projects_all
232 WHERE pa_projects_all.project_id(+) = sub.project_id),
233 (SELECT DISTINCT project_number
234 FROM pjm_seiban_numbers
235 WHERE pjm_seiban_numbers.project_id(+) = sub.project_id)) segment1
236 --updated to fix project_number related issue end
237 ,tasks.task_number
238 ,ven.vendor_name
239 ,pv.vendor_site_code
240 ,h.segment1
241 ,l.line_num
242 ,sub.osa_item_id
243 ,jmf_shikyu_rpt_util.get_item_number(l.org_id
244 ,l.item_id)
245 ,mtl.description
246 ,sub.osa_item_price
247 ,sub.currency
248 ,loc.quantity - loc.quantity_received unreceived_qty
249 ,l.unit_meas_lookup_code
250 -- ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.org_id
251 ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.ship_to_organization_id
252 ,sub.osa_item_id
253 ,1) frozend_cost
254 -- ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.org_id
255 ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.ship_to_organization_id
256 ,sub.osa_item_id
257 ,lp_cost_type_id)
258 ,decode(sub.currency,lp_func_currency_code,sub.osa_item_price,jmf_shikyu_rpt_util.convert_amount(sub.currency
259 ,lp_func_currency_code
260 ,decode(lp_currency_cnv_date,null,sysdate,lp_currency_cnv_date)
261 ,decode(lp_currency_cnv_type,null,h.rate_type,lp_currency_cnv_type)
262 ,sub.osa_item_price))
263 ,jmf_shikyu_rpt_cur_pvt.get_uom_primary_qty(l.item_id
264 ,loc.ship_to_organization_id
265 ,2
266 ,1
267 -- ,loc.unit_meas_lookup_code)
268 ,l.unit_meas_lookup_code) --UOM exchange rate
269 ,pra.release_num --Added to display release number
270 /* FROM po_headers_all h
271 ,po_lines_all l
272 ,po_line_locations_all loc
273 ,jmf_subcontract_orders sub
274 ,mtl_system_items_vl mtl
275 ,po_vendor_sites_all pv
276 ,hr_all_organization_units_tl haotl
277 ,pa_projects_all pa
278 ,pa_tasks tasks
279 ,po_vendors ven
280 WHERE \*h.type_lookup_code IN ('STANDARD') AND *\
281 h.po_header_id = sub.subcontract_po_header_id
282 AND l.po_line_id = sub.subcontract_po_line_id
283 AND sub.project_id = pa.project_id(+)
284 AND sub.task_id = tasks.task_id(+)
285 AND loc.line_location_id = sub.subcontract_po_shipment_id
286 AND pv.vendor_site_id(+) = h.vendor_site_id
287 AND mtl.inventory_item_id = l.item_id
288 AND haotl.organization_id(+) = loc.ship_to_organization_id
289 AND haotl.LANGUAGE = userenv('LANG')
290 AND h.vendor_id = ven.vendor_id
291 AND loc.ship_to_organization_id = mtl.organization_id
292 -- AND loc.org_id = mtl.organization_id
293 AND h.org_id = lp_org_id
294 AND haotl.NAME >= nvl(lp_inv_org_name_from
295 ,haotl.NAME)
296 AND haotl.NAME <= nvl(lp_inv_org_name_to
297 ,haotl.NAME);*/
298 --updated to fix project_number related issue start
299 --FROM pa_projects_all pa
300 -- ,pa_tasks tasks
301 FROM pa_tasks tasks
302 --updated to fix project_number related issue end
303 ,po_vendor_sites_all pv
304 ,po_vendors ven
305 ,mtl_system_items_vl mtl
306 ,jmf_subcontract_orders sub
307 ,po_line_locations_all loc
308 ,po_lines_all l
309 ,po_headers_all h
310 ,hr_organization_information hoi
311 ,mtl_interorg_parameters mip
312 ,hr_all_organization_units_tl haotl
313 --Added to display release_number
314 ,po_releases_all pra
315 --updated to fix project_number related issue start
316 --WHERE /*h.type_lookup_code IN ('standard') AND */
317 --pa.project_id(+) = sub.project_id
318 --AND tasks.task_id(+) = sub.task_id
319 WHERE tasks.task_id(+) = sub.task_id
320 --updated to fix project_number related issue end
321 AND pv.vendor_site_id(+) = h.vendor_site_id
322 AND ven.vendor_id = h.vendor_id
323 AND haotl.organization_id(+) = loc.ship_to_organization_id
324 -- AND loc.org_id = mtl.organization_id
325 AND mtl.inventory_item_id = l.item_id
326 AND mtl.organization_id = loc.ship_to_organization_id
327 AND sub.subcontract_po_header_id = loc.po_header_id
328 AND sub.subcontract_po_line_id = loc.po_line_id
329 AND sub.subcontract_po_shipment_id = loc.line_location_id
330 --Added to display release_number
331 AND loc.po_release_id = pra.po_release_id(+)
332 AND loc.po_line_id = l.po_line_id
333 AND loc.po_header_id = l.po_header_id
334 AND l.po_header_id = h.po_header_id
335 AND h.org_id = lp_org_id
336 AND h.vendor_id = hoi.org_information3
337 AND h.vendor_site_id=hoi.org_information4
338 AND hoi.org_information_context = 'Customer/Supplier Association' --to identify the flexfield
339 AND hoi.organization_id = mip.to_organization_id
340 --AND mip.shikyu_enabled_flag = 'Y';
341 --AND mip.subcontracting_type in ('B','C') -- 12.1 Buy/Sell Subcontracting changes
342 AND mip.subcontracting_type = 'C' -- Bug 6773949
343 and mip.from_organization_id =haotl.organization_id
344 --AND hoi.organization_id = haotl.organization_id
345 AND haotl.LANGUAGE = userenv('LANG')
346 AND haotl.NAME >= nvl(lp_inv_org_name_from
347 ,haotl.NAME)
348 AND haotl.NAME <= nvl(lp_inv_org_name_to
349 ,haotl.NAME);
350
351 l_source jmf_shikyu_cur_rpt_temp.SOURCE%TYPE;
352 l_inventory_org_name jmf_shikyu_cur_rpt_temp.inventory_org_name%TYPE;
353 l_project_num jmf_shikyu_cur_rpt_temp.project_num%TYPE;
354 l_task_num jmf_shikyu_cur_rpt_temp.task_num%TYPE;
355 l_vendor_name jmf_shikyu_cur_rpt_temp.vendor_name%TYPE;
356 l_vendor_site_code jmf_shikyu_cur_rpt_temp.vendor_site_code%TYPE;
357 l_order_num jmf_shikyu_cur_rpt_temp.order_num%TYPE;
358 l_line_num jmf_shikyu_cur_rpt_temp.line_num%TYPE;
359 l_item_id jmf_shikyu_cur_rpt_temp.item_id%TYPE;
360 l_item_name jmf_shikyu_cur_rpt_temp.item_name%TYPE;
361 l_item_desc jmf_shikyu_cur_rpt_temp.item_desc%TYPE;
362 l_unit_price jmf_shikyu_cur_rpt_temp.unit_price%TYPE;
363 l_currency jmf_shikyu_cur_rpt_temp.currency%TYPE;
364 l_quantity jmf_shikyu_cur_rpt_temp.quantity%TYPE;
365 l_uom_code jmf_shikyu_cur_rpt_temp.uom_code%TYPE;
366 l_unit_cost_frozen jmf_shikyu_cur_rpt_temp.unit_cost_frozen%TYPE;
367 l_unit_cost_plan jmf_shikyu_cur_rpt_temp.unit_cost_plan%TYPE;
368 l_func_unit_price jmf_shikyu_cur_rpt_temp.func_unit_price%TYPE;
369 l_qty_rate jmf_shikyu_cur_rpt_temp.primary_qty%TYPE;
370 l_func_currency_code jmf_shikyu_cur_rpt_temp.func_currency_code%TYPE;
371 l_message varchar2(200);
372 l_order_line_id jmf_shikyu_cur_rpt_temp.order_line_id%TYPE;--Added to display release number(When unreceived then mean release_num)
373
374 BEGIN
375
376 -- g_debug_level := fnd_log.g_current_runtime_level;
377
378 IF (g_proc_level >= g_debug_level)
379 THEN
380 fnd_log.STRING(g_proc_level
381 ,g_module_prefix || l_api_name || '.begin'
382 ,NULL);
383 END IF;
384 l_func_currency_code := p_func_currency_code;
385
386 OPEN l_cur_get_unreceived_po(p_cost_type_id
387 ,p_org_id
388 ,p_inv_org_name_from
389 ,p_inv_org_name_to
390 ,p_currency_cnv_type
391 ,p_currency_cnv_date
392 ,p_func_currency_code);
393
394 LOOP
395 FETCH l_cur_get_unreceived_po
396 INTO l_source,
397 l_inventory_org_name,
398 l_project_num,
399 l_task_num,
400 l_vendor_name,
401 l_vendor_site_code,
402 l_order_num,
403 l_line_num,
404 l_item_id,
405 l_item_name,
406 l_item_desc,
407 l_unit_price,
408 l_currency,
409 l_quantity,
410 l_uom_code,
411 l_unit_cost_frozen,
412 l_unit_cost_plan,
413 l_func_unit_price,
414 l_qty_rate,
415 l_order_line_id;--Added to display release number(When unreceived means release_num)
416
417 EXIT WHEN l_cur_get_unreceived_po%NOTFOUND;
418
419 -- handle corruency conversion rate exception
420 IF l_func_unit_price = -1 THEN
421 -- p_rate_not_found := 'Y';
422 g_rate_not_found := 'Y';
423
424 IF (g_excep_level >= g_debug_level)
425 THEN
426
427 fnd_message.set_name('JMF'
428 ,'JMF_SHK_CURR_RATE_NOTFOUND');
429
430 fnd_message.set_token('FROMCURRENCY'
431 ,l_currency);
432 fnd_message.set_token('TOCURRENCY'
433 ,p_func_currency_code);
434 fnd_message.set_token('DOCTYPE'
435 ,'PO');
436 fnd_message.set_token('DOCNUMBER'
437 ,l_order_num);
438
439 l_message := fnd_message.GET();
440
441
442 fnd_file.put_line(fnd_file.LOG, l_message );
443
444 fnd_log.STRING(g_excep_level
445 ,g_module_prefix || l_api_name || '.exception'
446 ,l_message);
447
448 END IF;
449
450 -- handle UOM conversion rate exception
451 ELSIF l_qty_rate = -99999 THEN
452 -- p_rate_not_found := 'Y';
453 g_rate_not_found :='Y';
454
455 IF (g_excep_level >= g_debug_level)
456 THEN
457
458 fnd_message.set_name('JMF'
459 ,'JMF_SHK_UOM_RATE_NOTFOUND');
460
461 fnd_message.set_token('ITEMNUM'
462 ,l_item_name);
463 fnd_message.set_token('DOCTYPE'
464 ,'PO');
465 fnd_message.set_token('DOCNUMBER'
466 ,l_order_num);
467
468
469 l_message := fnd_message.GET();
470
471 fnd_file.put_line(fnd_file.LOG, l_message);
472 fnd_log.STRING(g_excep_level
473 ,g_module_prefix || l_api_name || '.exception'
474 ,l_message);
475
476 END IF;
477
478 ELSIF (l_quantity <> 0) AND (l_unit_cost_frozen <> l_unit_cost_plan ) THEN
479
480
481 INSERT INTO jmf_shikyu_cur_rpt_temp
482 (SOURCE
483 ,inventory_org_name
484 ,project_num
485 ,task_num
486 ,vendor_name
487 ,vendor_site_code
488 ,order_num
489 ,line_num
490 ,item_id
491 ,item_name
492 ,item_desc
493 ,unit_price
494 ,currency
495 ,quantity
496 ,uom_code
497 ,unit_cost_frozen
498 ,unit_cost_plan
499 ,func_unit_price
500 ,primary_qty
501 ,func_currency_code
502 ,order_line_id)--Added to display release number(When unreceived means release_num)
503 VALUES
504 (l_source
505 ,l_inventory_org_name
506 ,l_project_num
507 ,l_task_num
508 ,l_vendor_name
509 ,l_vendor_site_code
510 ,l_order_num
511 ,l_line_num
512 ,l_item_id
513 ,l_item_name
514 ,l_item_desc
515 ,l_unit_price
516 ,l_currency
517 ,l_quantity
518 ,l_uom_code
519 ,l_unit_cost_frozen *l_qty_rate
520 ,l_unit_cost_plan *l_qty_rate
521 ,l_func_unit_price
522 ,l_qty_rate
523 ,l_func_currency_code
524 ,l_order_line_id);--Added to display release number(When unreceived means release_num)
525
526
527 END IF;
528 END LOOP;
529
530 COMMIT;
531 CLOSE l_cur_get_unreceived_po;
532
533 EXCEPTION
534 WHEN no_data_found THEN
535 IF (g_excep_level >= g_debug_level)
536 THEN
537 fnd_log.STRING(g_excep_level
538 ,g_module_prefix || l_api_name || '.exception'
539 ,'no_data_found');
540 END IF;
541 WHEN OTHERS THEN
542
543 ROLLBACK;
544
545 IF (g_excep_level >= g_debug_level)
546 THEN
547 fnd_log.STRING(g_excep_level
548 ,g_module_prefix || l_api_name || '.exception'
549 ,SQLERRM);
550 END IF;
551 END cuar_get_unreceived_po;
552
553 --========================================================================
554 -- PROCEDURE : cuar_get_unshipped_so PUBLIC
555 -- PARAMETERS: p_cost_type_id cost type id
556 -- : p_ou_id operating unit id
557 -- : p_inv_org_name_from oem inventory org name from
558 -- : p_inv_org_name_to oem inventory org name to
559 -- : p_currency_cnv_type currency conversion type
560 -- : p_currency_cnv_date currency conversion date
561 -- : p_func_currency_code functional currency code
562 -- COMMENT : collect appropriate unshipped so qty data and insert into
563 -- the temporary table
564 -- PRE-COND :
565 -- EXCEPTIONS:
566 --========================================================================
567 PROCEDURE cuar_get_unshipped_so
568 (
569 p_cost_type_id IN NUMBER
570 ,p_org_id IN NUMBER
571 ,p_inv_org_name_from IN VARCHAR2
572 ,p_inv_org_name_to IN VARCHAR2
573 ,p_currency_cnv_type IN VARCHAR2
574 ,p_currency_cnv_date IN DATE
575 ,p_func_currency_code IN VARCHAR2
576 ) IS
577 l_api_name CONSTANT VARCHAR2(30) := 'cuar_get_unshipped_so';
578 CURSOR l_cur_get_unshipped_so(lp_cost_type_id NUMBER, lp_org_id NUMBER, lp_inv_org_name_from VARCHAR2, lp_inv_org_name_to VARCHAR2, lp_currency_cnv_type VARCHAR2, lp_currency_cnv_date DATE, lp_func_currency_code VARCHAR2) IS
579 SELECT 'UnShipped'
580 ,haotl.NAME
581 --updated to fix project_number related issue start
582 --,pa.segment1
583 ,NVL((SELECT DISTINCT segment1 AS project_number
584 FROM pa_projects_all
585 WHERE pa_projects_all.project_id(+) = sol.project_id),
586 (SELECT DISTINCT project_number
587 FROM pjm_seiban_numbers
588 WHERE pjm_seiban_numbers.project_id(+) = sol.project_id)) segment1
589 --updated to fix project_number related issue end
590 ,tasks.task_number
591 ,soh.order_number
592 ,sol.line_number
593 ,sol.inventory_item_id
594 ,jmf_shikyu_rpt_util.get_item_number(sol.org_id
595 ,sol.inventory_item_id) item_num
596 ,mtl.description
597 ,sol.unit_selling_price
598 ,soh.transactional_curr_code
599 -- Updated to fix bug 5462851 start
600 -- To get unshipped Quantity
601 -- ,sol.ordered_quantity - nvl(sol.shipped_quantity,0) unshipped_qty
602 ,repo.allocated_quantity unshipped_quantity
603 -- Updated to fix bug 5462851 end
604 ,sol.pricing_quantity_uom
605 ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(sol.ship_from_org_id
606 ,sol.inventory_item_id
607 ,1) frozend_cost
608 ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(sol.ship_from_org_id
609 ,sol.inventory_item_id
610 ,lp_cost_type_id) planned_cost
611 ,decode(soh.transactional_curr_code,lp_func_currency_code
612 , sol.unit_selling_price,jmf_shikyu_rpt_util.convert_amount(soh.transactional_curr_code
613 ,lp_func_currency_code
614 /* ,lp_currency_cnv_date
615 ,lp_currency_cnv_type*/
616 ,decode(lp_currency_cnv_date,null,sysdate,lp_currency_cnv_date)
617 ,decode(lp_currency_cnv_type,null,soh.CONVERSION_TYPE_CODE,lp_currency_cnv_type)
618 ,sol.unit_selling_price)) convert_amount
619 ,jmf_shikyu_rpt_cur_pvt.get_uom_primary_qty_from_code(sol.inventory_item_id
620 ,sol.ship_from_org_id
621 ,2
622 ,1
623 ,sol.pricing_quantity_uom) exchange_UOM-- returns qty in primary UOM for 1 UOM in document
624 ,sol.line_id
625 FROM oe_order_headers_all soh
626 ,oe_order_lines_all sol
627 ,hr_all_organization_units_tl haotl
628 --updated to fix project_number related issue start
629 --,pa_projects_all pa
630 --updated to fix project_number related issue end
631 ,pa_tasks tasks
632 ,jmf_shikyu_replenishments repo
633 ,mtl_system_items_vl mtl
634 --updated to fix project_number related issue start
635 --WHERE pa.project_id(+) = sol.project_id
636 -- AND tasks.task_id(+) = sol.task_id
637 WHERE tasks.task_id(+) = sol.task_id
638 --updated to fix project_number related issue end
639 AND haotl.organization_id = sol.ship_from_org_id
640 AND haotl.LANGUAGE = userenv('LANG')
641 AND mtl.inventory_item_id = sol.inventory_item_id
642 -- AND mtl.organization_id = sol.org_id
643 AND mtl.organization_id = sol.ship_from_org_id
644 -- Added to fix bug 5462851 start
645 AND repo.allocated_quantity > 0
646 AND sol.shipped_quantity IS NULL
647 -- Added to fix bug 5462851 end
648 AND repo.replenishment_so_header_id = soh.header_id
649 AND repo.replenishment_so_line_id = sol.line_id
650 AND soh.flow_status_code NOT IN ('ENTERED'
651 ,'CANCELLED'
652 ,'CLOSED')
653 AND sol.org_id = lp_org_id
654 AND haotl.NAME >= nvl(lp_inv_org_name_from
655 ,haotl.NAME)
656 AND haotl.NAME <= nvl(lp_inv_org_name_to
657 ,haotl.NAME)
658 /* 12.1 Buy/Sell Subcontracting changes */
659 /* Cost update analysis report is applicable only for Chargeable Sucbontracting */
660 AND nvl(JMF_SHIKYU_GRP. GET_SUBCONTRACTING_TYPE(repo.oem_organization_id, repo.tp_organization_id),
661 NULL) = 'C' ;
662
663
664 l_source jmf_shikyu_cur_rpt_temp.SOURCE%TYPE;
665 l_inventory_org_name jmf_shikyu_cur_rpt_temp.inventory_org_name%TYPE;
666 l_project_num jmf_shikyu_cur_rpt_temp.project_num%TYPE;
667 l_task_num jmf_shikyu_cur_rpt_temp.task_num%TYPE;
668 l_vendor_name jmf_shikyu_cur_rpt_temp.vendor_name%TYPE;
669 l_vendor_site_code jmf_shikyu_cur_rpt_temp.vendor_site_code%TYPE;
670 l_order_num jmf_shikyu_cur_rpt_temp.order_num%TYPE;
671 l_line_num jmf_shikyu_cur_rpt_temp.line_num%TYPE;
672 l_item_id jmf_shikyu_cur_rpt_temp.item_id%TYPE;
673 l_item_name jmf_shikyu_cur_rpt_temp.item_name%TYPE;
674 l_item_desc jmf_shikyu_cur_rpt_temp.item_desc%TYPE;
675 l_unit_price jmf_shikyu_cur_rpt_temp.unit_price%TYPE;
676 l_currency jmf_shikyu_cur_rpt_temp.currency%TYPE;
677 l_quantity jmf_shikyu_cur_rpt_temp.quantity%TYPE;
678 l_uom_code jmf_shikyu_cur_rpt_temp.uom_code%TYPE;
679 l_unit_cost_frozen jmf_shikyu_cur_rpt_temp.unit_cost_frozen%TYPE;
680 l_unit_cost_plan jmf_shikyu_cur_rpt_temp.unit_cost_plan%TYPE;
681 l_func_unit_price jmf_shikyu_cur_rpt_temp.func_unit_price%TYPE;
682 l_qty_rate jmf_shikyu_cur_rpt_temp.primary_qty%TYPE;
683 l_func_currency_code jmf_shikyu_cur_rpt_temp.func_currency_code%TYPE;
684 l_order_line_id jmf_shikyu_cur_rpt_temp.order_line_id%TYPE;
685 l_message varchar2(200);
686
687 BEGIN
688 --g_debug_level := fnd_log.g_current_runtime_level;
689
690 IF (g_proc_level >= g_debug_level)
691 THEN
692 fnd_log.STRING(g_proc_level
693 ,g_module_prefix || l_api_name || '.begin'
694 ,NULL);
695 END IF;
696 l_func_currency_code := p_func_currency_code;
697
698 OPEN l_cur_get_unshipped_so(p_cost_type_id
699 ,p_org_id
700 ,p_inv_org_name_from
701 ,p_inv_org_name_to
702 ,p_currency_cnv_type
703 ,p_currency_cnv_date
704 ,p_func_currency_code);
705
706 LOOP
707 FETCH l_cur_get_unshipped_so
708 INTO l_source,
709 l_inventory_org_name,
710 l_project_num,
711 l_task_num,
712 l_order_num,
713 l_line_num,
714 l_item_id,
715 l_item_name,
716 l_item_desc,
717 l_unit_price,
718 l_currency,
719 l_quantity,
720 l_uom_code,
721 l_unit_cost_frozen ,
722 l_unit_cost_plan ,
723 l_func_unit_price,
724 l_qty_rate,
725 l_order_line_id;
726
727 EXIT WHEN l_cur_get_unshipped_so%NOTFOUND;
728
729 -- handle currency conversion rate not found exception
730 IF l_func_unit_price = -1 THEN
731
732 g_rate_not_found := 'Y';
733
734 IF (g_excep_level >= g_debug_level)
735 THEN
736
737 -- p_rate_not_found := 'Y';
738 -- g_rate_not_found := 'Y';
739 --need translation?
740
741 fnd_message.set_name('JMF'
742 ,'JMF_SHK_CURR_RATE_NOTFOUND');
743
744 fnd_message.set_token('FROMCURRENCY'
745 ,l_currency);
746 fnd_message.set_token('TOCURRENCY'
747 ,p_func_currency_code);
748 fnd_message.set_token('DOCTYPE'
749 ,'SO');
750 fnd_message.set_token('DOCNUMBER'
751 ,l_order_num);
752
753 l_message := fnd_message.GET();
754
755 fnd_file.put_line(fnd_file.LOG, l_message);
756 fnd_log.STRING(g_excep_level
757 ,g_module_prefix || l_api_name || '.exception'
758 ,l_message);
759
760 END IF;
761
762
763 -- handle UOM conversion rate not found exception
764 ELSIF l_qty_rate = -99999 THEN
765
766 g_rate_not_found := 'Y';
767 IF (g_excep_level >= g_debug_level)
768 THEN
769
770 -- p_rate_not_found := 'Y';
771 -- g_rate_not_found := 'Y';
772
773 fnd_message.set_name('JMF'
774 ,'JMF_SHK_UOM_RATE_NOTFOUND');
775
776 fnd_message.set_token('ITEMNUM'
777 ,l_item_name);
778 fnd_message.set_token('DOCTYPE'
779 ,'SO');
780 fnd_message.set_token('DOCNUMBER'
781 ,l_order_num);
782
783
784 fnd_file.put_line(fnd_file.LOG, l_message);
785 fnd_log.STRING(g_excep_level
786 ,g_module_prefix || l_api_name || '.exception'
787 ,l_message);
788
789 END IF;
790
791
792
793 ELSIF (l_quantity <> 0) AND (l_unit_cost_frozen <> l_unit_cost_plan ) THEN
794
795 INSERT INTO jmf_shikyu_cur_rpt_temp
796 (SOURCE
797 ,inventory_org_name
798 ,project_num
799 ,task_num
800 ,vendor_name
801 ,vendor_site_code
802 ,order_num
803 ,line_num
804 ,item_id
805 ,item_name
806 ,item_desc
807 ,unit_price
808 ,currency
809 ,quantity
810 ,uom_code
811 ,unit_cost_frozen
812 ,unit_cost_plan
813 ,func_unit_price
814 ,primary_qty
815 ,func_currency_code
816 ,order_line_id)
817 VALUES
818 (l_source
819 ,l_inventory_org_name
820 ,l_project_num
821 ,l_task_num
822 ,l_vendor_name
823 ,l_vendor_site_code
824 ,l_order_num
825 ,l_line_num
826 ,l_item_id
827 ,l_item_name
828 ,l_item_desc
829 ,l_unit_price
830 ,l_currency
831 ,l_quantity
832 ,l_uom_code
833 ,l_unit_cost_frozen * l_qty_rate --convert into UOM in document
834 ,l_unit_cost_plan * l_qty_rate --convert into UOM in document
835 ,l_func_unit_price
836 ,l_qty_rate -- exchange UOM Rate
837 ,l_func_currency_code
838 ,l_order_line_id);
839
840 END IF;
841
842 END LOOP;
843
844 COMMIT;
845 CLOSE l_cur_get_unshipped_so;
846
847 EXCEPTION
848 WHEN no_data_found THEN
849 IF (g_excep_level >= g_debug_level)
850 THEN
851 fnd_log.STRING(g_excep_level
852 ,g_module_prefix || l_api_name || '.exception'
853 ,'no_data_found');
854 END IF;
855 WHEN OTHERS THEN
856
857 ROLLBACK;
858
859 IF (g_excep_level >= g_debug_level)
860 THEN
861 fnd_log.STRING(g_excep_level
862 ,g_module_prefix || l_api_name || '.exception'
863 ,SQLERRM);
864 END IF;
865 END cuar_get_unshipped_so;
866
867 --========================================================================
868 -- PROCEDURE : cuar_get_rma_so PUBLIC
869 -- PARAMETERS: p_cost_type_id cost type id
870 -- : p_ou_id operating unit id
871 -- : p_inv_org_name_from oem inventory org name from
872 -- : p_inv_org_name_to oem inventory org name to
873 -- : p_currency_cnv_type currency conversion type
874 -- : p_currency_cnv_date currency conversion date
875 -- : p_func_currency_code functional currency code
876 -- COMMENT : collect appropriate rma so qty data and insert into
877 -- the temporary table
878 -- PRE-COND :
879 -- EXCEPTIONS:
880 --========================================================================
881 PROCEDURE cuar_get_rma_so
882 (
883 p_org_id IN NUMBER
884 ,p_inv_org_name_from IN VARCHAR2
885 ,p_inv_org_name_to IN VARCHAR2
886 ,p_func_currency_code IN VARCHAR2
887 ) IS
888 l_api_name CONSTANT VARCHAR2(30) := 'cuar_get_rma_so';
889 -- l_start_date DATE;
890 -- l_end_date DATE;
891 -- l_set_of_book_id NUMBER;
892 l_transaction_id rcv_transactions.transaction_id%TYPE;
893 l_rcv_line_id oe_order_lines_all.line_id%TYPE;
894 l_reference_line_id oe_order_lines_all.reference_line_id%TYPE;
895 l_org_id NUMBER;
896 l_cur_rcv_order_number oe_order_headers_all.order_number%TYPE;
897 l_cur_rcv_line_number oe_order_lines_all.line_number%TYPE;
898 l_cur_rcv_ordered_quantity NUMBER;
899 l_cur_rcv_shipped_quantity NUMBER;
900 l_creation_date DATE;
901
902 CURSOR l_cur_rcv_info(lp_inv_org_name_from VARCHAR2
903 , lp_inv_org_name_to VARCHAR2
904 , lp_org_id NUMBER) IS
905 SELECT rcv.transaction_id
906 ,oel.line_id
907 ,oel.reference_line_id
908 ,oel.org_id
909 ,oeh.order_number
910 ,oel.line_number
911 ,oel.ordered_quantity
912 ,oel.shipped_quantity
913 ,oel.ship_from_org_id
914 ,haotl.NAME
915 ,rcv.creation_date
916 ,rcv.transaction_id
917 FROM rcv_transactions rcv
918 ,oe_order_lines_all oel
919 ,oe_order_headers_all oeh
920 ,hr_all_organization_units_tl haotl
921 WHERE oel.org_id = lp_org_id
922 AND rcv.transaction_type = 'DELIVER'
923 AND oel.line_id = rcv.oe_order_line_id
924 AND rcv.organization_id = oel.ship_from_org_id
925 AND oel.header_id = oeh.header_id
926 AND haotl.organization_id(+) = oel.ship_from_org_id
927 AND haotl.LANGUAGE = userenv('LANG')
928 AND haotl.NAME >= nvl(lp_inv_org_name_from
929 ,haotl.NAME)
930 AND haotl.NAME <= nvl(lp_inv_org_name_to
931 ,haotl.NAME)
932 /* 12.1 Buy/Sell Subcontracting changes */
933 /* Cost update analysis report is applicable only for Chargeable Sucbontracting */
934 AND nvl(JMF_SHIKYU_GRP. GET_SUBCONTRACTING_TYPE(oel.ship_from_org_id, oel.ship_to_org_id),
935 NULL) = 'C' ;
936
937
938 l_source jmf_shikyu_cur_rpt_temp.SOURCE%TYPE;
939 l_inventory_org_name jmf_shikyu_cur_rpt_temp.inventory_org_name%TYPE;
940 l_project_num jmf_shikyu_cur_rpt_temp.project_num%TYPE;
941 l_task_num jmf_shikyu_cur_rpt_temp.task_num%TYPE;
942 l_order_num jmf_shikyu_cur_rpt_temp.order_num%TYPE;
943 l_line_num jmf_shikyu_cur_rpt_temp.line_num%TYPE;
944 l_item_id jmf_shikyu_cur_rpt_temp.item_id%TYPE;
945 l_item_name jmf_shikyu_cur_rpt_temp.item_name%TYPE;
946 l_item_desc jmf_shikyu_cur_rpt_temp.item_desc%TYPE;
947 l_quantity jmf_shikyu_cur_rpt_temp.quantity%TYPE;
948 l_flow_status_code oe_order_lines_all.flow_status_code%TYPE;
949 l_unit_cost_frozen jmf_shikyu_cur_rpt_temp.unit_cost_frozen%TYPE;
950 l_unit_cost_plan jmf_shikyu_cur_rpt_temp.unit_cost_plan%TYPE;
951 l_primary_qty jmf_shikyu_cur_rpt_temp.primary_qty%TYPE;
952 l_func_currency_code jmf_shikyu_cur_rpt_temp.func_currency_code%TYPE;
953 l_order_line_id jmf_shikyu_cur_rpt_temp.order_line_id%TYPE;
954 l_no_ori_so_flag VARCHAR2(5);
955 l_no_appropriate_so_flag VARCHAR2(5);
956 l_rma_over_shipped_flag VARCHAR2(5);
957 l_rma_qty NUMBER;
958 l_shipped_quantity NUMBER;
959
960 l_rcv_transaction_id NUMBER;
961 l_ship_from_org_id NUMBER;
962 l_om_ship_from_org_id NUMBER;
963 l_message VARCHAR2(100);
964 l_actual_shipment_date Date;
965
966 BEGIN
967 --g_debug_level := fnd_log.g_current_runtime_level;
968
969 IF (g_proc_level >= g_debug_level)
970 THEN
971 fnd_log.STRING(g_proc_level
972 ,g_module_prefix || l_api_name || '.begin'
973 ,NULL);
974 END IF;
975 --set_of_book_id
976 --l_set_of_book_id := fnd_profile.VALUE('GL_SET_OF_BKS_ID');
977
978 --get functional currency
979 l_func_currency_code := p_func_currency_code;
980
981 --get appropriate rcv
982 OPEN l_cur_rcv_info(p_inv_org_name_from
983 ,p_inv_org_name_to
984 ,p_org_id);
985 --loop through each rma trx in certain period according to parameters
986 LOOP
987 FETCH l_cur_rcv_info
988 INTO l_transaction_id
989 , l_rcv_line_id
990 , l_reference_line_id
991 , l_org_id
992 , l_cur_rcv_order_number
993 , l_cur_rcv_line_number
994 , l_cur_rcv_ordered_quantity
995 , l_cur_rcv_shipped_quantity
996 , l_ship_from_org_id
997 , l_inventory_org_name
998 , l_creation_date
999 , l_rcv_transaction_id;
1000
1001 --EXIT;
1002 EXIT WHEN l_cur_rcv_info%NOTFOUND;
1003
1004 IF is_current_period(l_creation_date ,l_ship_from_org_id) THEN
1005 --no related normal sales order
1006 l_no_ori_so_flag := 'N';
1007
1008 IF l_reference_line_id IS NULL
1009 THEN
1010
1011 l_no_ori_so_flag := 'Y';
1012
1013 IF (g_excep_level >= g_debug_level)
1014 THEN
1015
1016
1017 fnd_message.set_name('JMF'
1018 ,'JMF_SHK_RMA_REFERENCE_MISS');
1019 fnd_message.set_token('ORDERNUM'
1020 ,l_cur_rcv_order_number);
1021 fnd_message.set_token('LINENUM'
1022 ,l_cur_rcv_line_number);
1023
1024 l_message := fnd_message.GET();
1025
1026 fnd_file.put_line(fnd_file.LOG, l_message);
1027 --l_message := 'RMA order with the number ' || l_cur_rcv_order_number || '-' ||
1028 -- l_cur_rcv_line_number ||
1029 -- ' doesn''t refer to any original sales order';
1030 fnd_log.STRING(g_excep_level
1031 ,g_module_prefix || l_api_name || '.evaluate'
1032 ,l_message);
1033
1034 END IF; -- (g_proc_level >= g_debug_level)
1035 END IF; --l_reference_line_id IS NULL
1036
1037
1038 -- related normal sales order exists
1039 IF l_no_ori_so_flag = 'N'
1040 THEN
1041 --get corresponding info for each sale order line
1042 BEGIN
1043 l_no_appropriate_so_flag := 'N';
1044 SELECT pa.segment1
1045 ,tasks.task_number
1046 ,soh.order_number
1047 ,sol.line_number
1048 ,sol.inventory_item_id
1049 ,jmf_shikyu_rpt_util.get_item_number(sol.org_id
1050 ,sol.inventory_item_id) item_num
1051 ,mtl.description
1052 ,sol.shipped_quantity
1053 ,sol.flow_status_code
1054 ,sol.ship_from_org_id
1055 ,sol.actual_shipment_date
1056 INTO l_project_num
1057 ,l_task_num
1058 ,l_order_num
1059 ,l_line_num
1060 ,l_item_id
1061 ,l_item_name
1062 ,l_item_desc
1063 ,l_shipped_quantity
1064 ,l_flow_status_code
1065 ,l_om_ship_from_org_id
1066 ,l_actual_shipment_date
1067 FROM oe_order_headers_all soh
1068 ,oe_order_lines_all sol
1069 ,pa_projects_all pa
1070 ,pa_tasks tasks
1071 ,jmf_shikyu_replenishments repo
1072 ,mtl_system_items_vl mtl
1073 WHERE pa.project_id(+) = sol.project_id
1074 AND tasks.task_id(+) = sol.task_id
1075 AND mtl.inventory_item_id = sol.inventory_item_id
1076 AND mtl.organization_id = l_ship_from_org_id
1077 AND repo. replenishment_so_header_id = soh.header_id
1078 AND repo. replenishment_so_line_id = sol.line_id
1079 AND sol.line_id = l_reference_line_id
1080 AND soh.header_id =sol.header_id;
1081
1082 EXCEPTION
1083 WHEN no_data_found THEN
1084 l_no_appropriate_so_flag := 'Y';
1085 IF (g_excep_level >= g_debug_level)
1086 THEN
1087 fnd_log.STRING(g_excep_level
1088 ,g_module_prefix || l_api_name || '.exception'
1089 ,'no_data_found');
1090 END IF; --(g_proc_level >= g_debug_level)
1091 WHEN OTHERS THEN
1092 IF (g_excep_level >= g_debug_level)
1093 THEN
1094 fnd_log.STRING(g_excep_level
1095 ,g_module_prefix || l_api_name || '.exception'
1096 ,SQLERRM);
1097 END IF; --(g_proc_level >= g_debug_level)
1098 END;
1099 END IF; -- l_no_ori_so_flag = 'N'
1100
1101 IF l_no_appropriate_so_flag = 'N'
1102 THEN
1103 IF l_flow_status_code = 'ENTERED'
1104 OR l_flow_status_code = 'AWAITING_SHIPPING' --'SHIPPED' or FULFILLED
1105 THEN
1106 l_no_appropriate_so_flag := 'Y';
1107
1108
1109 IF (g_excep_level >= g_debug_level)
1110 THEN
1111
1112 fnd_message.set_name('JMF'
1113 ,'JMF_SHK_RMA_REFERENCE_MISS');
1114 fnd_message.set_token('ORDERNUM'
1115 ,l_order_num);
1116 fnd_message.set_token('LINENUM'
1117 ,l_line_num);
1118
1119 l_message := fnd_message.GET();
1120
1121 fnd_file.put_line(fnd_file.LOG, l_message);
1122 fnd_log.STRING(g_excep_level
1123 ,g_module_prefix || l_api_name || '.evaluate'
1124 ,l_message);
1125
1126 END IF;--(g_proc_level >= g_debug_level)
1127
1128 ELSIF l_cur_rcv_ordered_quantity < l_cur_rcv_shipped_quantity
1129 THEN
1130
1131 fnd_message.set_name('JMF'
1132 ,'JMF_SHK_RMA_OVER_RECEIPT');
1133 fnd_message.set_token('ORDERNUM'
1134 ,l_order_num);
1135 fnd_message.set_token('LINENUM'
1136 ,l_line_num);
1137 l_message := fnd_message.GET();
1138
1139 IF (g_excep_level >= g_debug_level)
1140 THEN
1141
1142 fnd_file.put_line(fnd_file.LOG, l_message);
1143 fnd_log.STRING(g_excep_level
1144 ,g_module_prefix || l_api_name || '.evaluate'
1145 ,l_message);
1146 END IF;--(g_proc_level >= g_debug_level)
1147 END IF; --l_cur_rcv_ordered_quantity < l_cur_rcv_shipped_quantity
1148 END IF; --l_flow_status_code = 'ENTERED'
1149
1150 END IF;
1151
1152 --rma so is related to replenishment order
1153 IF l_no_appropriate_so_flag = 'N'
1154 THEN
1155
1156 -- get shipped cost
1157 -- add AND inventory_item_id = l_item_id
1158 -- AND organization_id= l_ship_from_org_id
1159 -- AND transaction_date = l_actual_shipment_date to fix the FTS issue
1160 -- updated by amy to fix bug 5506431
1161 --SELECT actual_cost
1162 SELECT DISTINCT actual_cost
1163 INTO l_unit_cost_frozen
1164 FROM mtl_material_transactions
1165 WHERE trx_source_line_id = l_reference_line_id
1166 AND source_code = 'ORDER ENTRY'
1167 AND inventory_item_id = l_item_id
1168 AND organization_id= l_om_ship_from_org_id
1169 AND transaction_date = l_actual_shipment_date ;
1170
1171
1172 --get receipt cost
1173 -- add AND rcv_transaction_id = l_rcv_transaction_id to fix the FTS issue
1174 -- updated by amy to fix bug 5506431
1175 --SELECT actual_cost
1176 SELECT DISTINCT actual_cost
1177 INTO l_unit_cost_plan
1178 FROM mtl_material_transactions
1179 WHERE trx_source_line_id =l_rcv_line_id
1180 AND source_code = 'RCV'
1181 AND rcv_transaction_id = l_rcv_transaction_id ;
1182
1183
1184 --over received
1185 IF l_cur_rcv_shipped_quantity > l_shipped_quantity
1186 THEN
1187 l_rma_over_shipped_flag := 'YES';
1188 --l_cur_rcv_shipped_quantity := l_shipped_quantity ;
1189 ELSE
1190 l_rma_over_shipped_flag := 'NO';
1191 END IF;
1192
1193 l_source := 'RMA';
1194 l_primary_qty := l_shipped_quantity;
1195 l_quantity := l_rma_qty;
1196 l_order_line_id := l_reference_line_id;
1197
1198 IF (l_cur_rcv_shipped_quantity <> 0) AND (l_unit_cost_frozen <> l_unit_cost_plan ) THEN
1199
1200 INSERT INTO jmf_shikyu_cur_rpt_temp
1201 (SOURCE
1202 ,inventory_org_name
1203 ,project_num
1204 ,task_num
1205 ,vendor_name
1206 ,vendor_site_code
1207 ,order_num
1208 ,line_num
1209 ,item_id
1210 ,item_name
1211 ,item_desc
1212 ,quantity
1213 ,uom_code
1214 ,unit_cost_frozen
1215 ,unit_cost_plan
1216 ,func_unit_price
1217 ,primary_qty
1218 ,order_line_id
1219 ,func_currency_code)
1220 VALUES
1221 (l_source
1222 ,l_inventory_org_name
1223 ,l_project_num
1224 ,l_task_num
1225 ,NULL
1226 ,NULL
1227 ,l_order_num
1228 ,l_line_num
1229 ,l_item_id
1230 ,l_item_name
1231 ,l_item_desc
1232 ,l_cur_rcv_shipped_quantity
1233 ,NULL
1234 ,l_unit_cost_frozen
1235 ,l_unit_cost_plan
1236 ,NULL
1237 ,l_shipped_quantity
1238 ,l_reference_line_id
1239 ,l_func_currency_code);
1240 COMMIT;
1241 END IF;
1242 END IF; --is_current_period(l_creation_date ,l_ship_from_org_id)
1243
1244 END LOOP;
1245 CLOSE l_cur_rcv_info;
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 ROLLBACK;
1250 IF (g_excep_level >= g_debug_level)
1251 THEN
1252 fnd_log.STRING(g_excep_level
1253 ,g_module_prefix || l_api_name || '.exception'
1254 ,SQLERRM);
1255 END IF;
1256 END cuar_get_rma_so;
1257
1258
1259 --========================================================================
1260 -- FUNCTION : IS_CURRENT_PERIOD PUBLIC
1261 -- PARAMETERS: p_date DATE
1262 -- : p_org_id Inventory org id
1263 --
1264 -- RETURN : will return if input date is in current inventory accounting period
1265 -- COMMENT :
1266 -- PRE-COND :
1267 -- EXCEPTIONS:
1268 --========================================================================
1269 FUNCTION is_current_period
1270 ( p_date IN DATE
1271 ,p_org_id IN NUMBER
1272 ) RETURN BOOLEAN IS
1273 l_number NUMBER;
1274 l_api_name VARCHAR2(30);
1275
1276 BEGIN
1277 l_api_name := 'is_current_period' ;
1278
1279 -- g_debug_level := fnd_log.g_current_runtime_level;
1280
1281 SELECT 1
1282 INTO l_number
1283 FROM ORG_ACCT_PERIODS
1284 WHERE organization_id= p_org_id
1285 AND trunc(p_date,'dd') >= period_start_date
1286 AND trunc(p_date,'dd') <= schedule_close_date
1287 AND trunc(sysdate,'dd') >= period_start_date
1288 AND trunc(sysdate,'dd') <= schedule_close_date ;
1289
1290 RETURN (TRUE);
1291 EXCEPTION
1292 WHEN no_data_found THEN
1293 IF (g_excep_level >= g_debug_level)
1294 THEN
1295 fnd_log.STRING(g_excep_level
1296 ,g_module_prefix || l_api_name || '.exception'
1297 ,'no_data_found');
1298 END IF;
1299
1300 RETURN (FALSE);
1301 WHEN OTHERS THEN
1302 ROLLBACK;
1303 IF (g_excep_level >= g_debug_level)
1304 THEN
1305 fnd_log.STRING(g_excep_level
1306 ,g_module_prefix || l_api_name || '.exception'
1307 ,SQLERRM);
1308 END IF;
1309 END is_current_period ;
1310
1311
1312 --========================================================================
1313 -- FUNCTION : cuar_get_item_cost PUBLIC
1314 -- PARAMETERS: p_ou_id operating unit id
1315 -- : p_item_id item id
1316 -- : p_cst_type_id item cost type id
1317 -- RETURN : will return the item cost
1318 -- COMMENT : get item cost for specific item
1319 -- PRE-COND :
1320 -- EXCEPTIONS:
1321 --========================================================================
1322 FUNCTION cuar_get_item_cost
1323 (
1324 p_org_id IN NUMBER
1325 ,p_item_id IN NUMBER
1326 ,p_cst_type_id IN NUMBER
1327 ) RETURN NUMBER IS
1328 l_item_cost NUMBER;
1329 l_api_name VARCHAR2(30);
1330 BEGIN
1331
1332 l_api_name := 'cuar_get_item_cost' ;
1333
1334 -- g_debug_level := fnd_log.g_current_runtime_level;
1335
1336 SELECT item_cost
1337 INTO l_item_cost
1338 FROM cst_item_costs cost
1339 WHERE cost.cost_type_id = p_cst_type_id
1340 AND cost.inventory_item_id = p_item_id
1341 AND organization_id = p_org_id;
1342 RETURN(l_item_cost);
1343 EXCEPTION
1344 WHEN no_data_found THEN
1345 IF (g_excep_level >= g_debug_level)
1346 THEN
1347 fnd_log.STRING(g_excep_level
1348 ,g_module_prefix || l_api_name || '.exception'
1349 ,'no_data_found');
1350 END IF;
1351 RETURN(0);
1352
1353 WHEN OTHERS THEN
1354 ROLLBACK;
1355 IF (g_excep_level >= g_debug_level)
1356 THEN
1357 fnd_log.STRING(g_excep_level
1358 ,g_module_prefix || l_api_name || '.exception'
1359 ,SQLERRM);
1360 END IF;
1361
1362 END cuar_get_item_cost;
1363
1364 --========================================================================
1365 -- FUNCTION : get_uom_primary PUBLIC
1366 -- PARAMETERS: p_inventory_item_id inventory item id
1367 -- : p_org_id organization id
1368 -- RETURN : will return the primary uom
1369 -- COMMENT : getting the primary UOM
1370 -- PRE-COND :
1371 -- EXCEPTIONS:
1372 --========================================================================
1373
1374 FUNCTION get_uom_primary
1375 (
1376 p_inventory_item_id IN NUMBER
1377 ,p_org_id IN NUMBER
1378 ) RETURN VARCHAR2 IS
1379 l_primary_uom VARCHAR2(25);
1380 l_api_name VARCHAR2(30);
1381 BEGIN
1382
1383 l_api_name := 'get_uom_primary' ;
1384
1385 --g_debug_level := fnd_log.g_current_runtime_level;
1386
1387 SELECT primary_unit_of_measure
1388 INTO l_primary_uom
1389 FROM mtl_system_items_b
1390 WHERE inventory_item_id = p_inventory_item_id
1391 AND organization_id = p_org_id;
1392 RETURN(l_primary_uom);
1393
1394 EXCEPTION
1395 WHEN no_data_found THEN
1396 IF (g_excep_level >= g_debug_level)
1397 THEN
1398 fnd_log.STRING(g_excep_level
1399 ,g_module_prefix || l_api_name || '.exception'
1400 ,'no_data_found');
1401 END IF;
1402 RETURN(NULL);
1403
1404 WHEN OTHERS THEN
1405 ROLLBACK;
1406 IF (g_excep_level >= g_debug_level)
1407 THEN
1408 fnd_log.STRING(g_excep_level
1409 ,g_module_prefix || l_api_name || '.exception'
1410 ,SQLERRM);
1411 END IF;
1412
1413 END get_uom_primary;
1414
1415 --========================================================================
1416 -- FUNCTION : get_uom_primary_code PUBLIC
1417 -- PARAMETERS: p_inventory_item_id inventory item id
1418 -- : p_org_id organization id
1419 -- RETURN : will return the primary uom code
1420 -- COMMENT : getting the primary UOM code
1421 -- PRE-COND :
1422 -- EXCEPTIONS:
1423 --========================================================================
1424 FUNCTION get_uom_primary_code
1425 (
1426 p_inventory_item_id IN NUMBER
1427 ,p_org_id IN NUMBER
1428 ) RETURN VARCHAR2 IS
1429 l_primary_uom_code VARCHAR2(25);
1430 l_api_name VARCHAR2(30);
1431 BEGIN
1432
1433 l_api_name := 'get_uom_primary_code' ;
1434
1435 --g_debug_level := fnd_log.g_current_runtime_level;
1436
1437 SELECT primary_uom_code
1438 INTO l_primary_uom_code
1439 FROM mtl_system_items_b
1440 WHERE inventory_item_id = p_inventory_item_id
1441 AND organization_id = p_org_id;
1442 RETURN(l_primary_uom_code);
1443
1444 EXCEPTION
1445 WHEN no_data_found THEN
1446 IF (g_excep_level >= g_debug_level)
1447 THEN
1448 fnd_log.STRING(g_excep_level
1449 ,g_module_prefix || l_api_name || '.exception'
1450 ,'no_data_found');
1451 END IF;
1452 RETURN(NULL);
1453
1454 WHEN OTHERS THEN
1455 ROLLBACK;
1456 IF (g_excep_level >= g_debug_level)
1457 THEN
1458 fnd_log.STRING(g_excep_level
1459 ,g_module_prefix || l_api_name || '.exception'
1460 ,SQLERRM);
1461 END IF;
1462
1463 END get_uom_primary_code;
1464
1465 --========================================================================
1466 -- FUNCTION : get_uom_primary_qty PUBLIC
1467 -- PARAMETERS: p_inventory_item_id inventory item id
1468 -- : p_org_id organization id
1469 -- : p_precision precision
1470 -- : p_from_quantity quantity of from UOM
1471 -- : p_from_unit from UOM
1472 -- RETURN : will return the quantity with primary uom
1473 -- COMMENT : getting the quantity with primary uom
1474 -- PRE-COND :
1475 -- EXCEPTIONS:
1476 --========================================================================
1477 FUNCTION get_uom_primary_qty
1478 (
1479 p_inventory_item_id IN NUMBER
1480 ,p_org_id IN NUMBER
1481 ,p_precision IN NUMBER
1482 ,p_from_quantity IN NUMBER
1483 ,p_from_unit IN VARCHAR2
1484 ) RETURN NUMBER IS
1485 l_qty VARCHAR2(15);
1486 l_primary_uom VARCHAR(25);
1487 BEGIN
1488 l_primary_uom := get_uom_primary(p_inventory_item_id => p_inventory_item_id
1489 ,p_org_id => p_org_id);
1490 l_qty := inv_convert.inv_um_convert(item_id => p_inventory_item_id
1491 ,PRECISION => p_precision
1492 ,from_quantity => p_from_quantity
1493 ,from_unit => NULL
1494 ,to_unit => NULL
1495 ,from_name => p_from_unit
1496 ,to_name => l_primary_uom);
1497 RETURN(l_qty);
1498 END get_uom_primary_qty;
1499
1500 --========================================================================
1501 -- FUNCTION : get_uom_primary_qty_from_code PUBLIC
1502 -- PARAMETERS: p_inventory_item_id inventory item id
1503 -- : p_org_id organization id
1504 -- : p_precision precision
1505 -- : p_from_quantity quantity of from UOM
1506 -- : p_from_unit from UOM code
1507 -- RETURN : will return the quantity with primary uom code
1508 -- COMMENT : getting the quantity with primary uom code
1509 -- PRE-COND :
1510 -- EXCEPTIONS:
1511 --========================================================================
1512 FUNCTION get_uom_primary_qty_from_code
1513 (
1514 p_inventory_item_id IN NUMBER
1515 ,p_org_id IN NUMBER
1516 ,p_precision IN NUMBER
1517 ,p_from_quantity IN NUMBER
1518 ,p_from_unit IN VARCHAR2
1519 ) RETURN NUMBER IS
1520 l_qty VARCHAR2(15);
1521 l_primary_uom VARCHAR(25);
1522 l_uom VARCHAR(25);
1523 l_api_name VARCHAR2(30);
1524 BEGIN
1525 l_api_name := 'get_uom_primary_qty_from_code' ;
1526
1527 g_debug_level := fnd_log.g_current_runtime_level;
1528
1529 l_primary_uom := get_uom_primary(p_inventory_item_id => p_inventory_item_id
1530 ,p_org_id => p_org_id);
1531 SELECT mtl_units_of_measure.unit_of_measure
1532 INTO l_uom
1533 FROM mtl_units_of_measure
1534 WHERE mtl_units_of_measure.uom_code = p_from_unit;
1535 l_qty := inv_convert.inv_um_convert(item_id => p_inventory_item_id
1536 ,PRECISION => p_precision
1537 ,from_quantity => p_from_quantity
1538 ,from_unit => NULL
1539 ,to_unit => NULL
1540 ,from_name => l_uom
1541 ,to_name => l_primary_uom);
1542 RETURN(l_qty);
1543
1544 EXCEPTION
1545 WHEN no_data_found THEN
1546 IF (g_excep_level >= g_debug_level)
1547 THEN
1548 fnd_log.STRING(g_excep_level
1549 ,g_module_prefix || l_api_name || '.exception'
1550 ,'no_data_found');
1551 END IF;
1552 RETURN(0);
1553
1554 WHEN OTHERS THEN
1555 ROLLBACK;
1556 IF (g_excep_level >= g_debug_level)
1557 THEN
1558 fnd_log.STRING(g_excep_level
1559 ,g_module_prefix || l_api_name || '.exception'
1560 ,SQLERRM);
1561 END IF;
1562
1563 END get_uom_primary_qty_from_code;
1564
1565 END jmf_shikyu_rpt_cur_pvt;