[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_RPT_CFR_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_RPT_CFR_PVT AS
2 --$Header: JMFVCFRB.pls 120.40 2007/11/16 11:18:55 kdevadas ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFVCFRB.pls |
10 --| |
11 --| DESCRIPTION: Body file of the package for creating temporary |
12 --| data for SHIKYU Confirmation Report. |
13 --| |
14 --| FUNCTION/PROCEDURE: cfr_before_report_trigger |
15 --| get_onhand_components |
16 --| get_rpt_confirmation_data |
17 --| get_unallocated_components |
18 --| get_unallocated_rep_po |
19 --| get_rep_po_residual_unalloc |
20 --| set_rep_po_residual_unalloc |
21 --| set_rcv_transaction_unalloc |
22 --| get_unconsumed_components |
23 --| get_unconsumed_sub_po |
24 --| get_unconsumed_rep_po |
25 --| get_sub_po_residual_unconsume |
26 --| set_sub_po_residual_unconsume |
27 --| set_rcv_transaction_unconsume |
28 --| validate_cfr_mid_temp |
29 --| add_data_to_cfr_temp |
30 --| rpt_get_crude_data |
31 --| rpt_get_Comp_Estimated_data |
32 --| rpt_get_SubPO_data |
33 --| rpt_get_UnReceived_data |
34 --| rpt_get_Received_data |
35 --| rpt_get_Int_data |
36 --| rpt_debug_show_mid_data |
37 --| rpt_debug_show_temp_data |
38 --| |
39 --| HISTORY: |
40 --| 15-APR-2005 shu Created. |
41 --| 10-OCT-2005 shu Added some output information for debug |
42 --| 11-OCT-2005 shu Modified subcontracting_component column |
43 --| select criteria. |
44 --| 13-OCT-2005 shu Modified source code to avoid the warning |
45 --| message. added debug output code |
46 --| 18-NOV-2005 shu added code for setting request completed with |
47 --| warning if SHIKYU profile is disable |
48 --| 05-DEC-2005 shu Fixed some no data issue due to null columns |
49 --| 07-DEC-2005 shu added procedures rpt_get_xxx for report data |
50 --| 21-DEC-2005 shu tuning SQLperformance in get_onhand_components|
51 --| 16-JAN-2006 shu using FND_LOG.STRING for logging standard |
52 --| 19-JAN-2006 shu changed parameter p_currency_conversion_date |
53 --| from date to varchar2; |
54 --| added rpt_debug_show_mid_data procedure |
55 --| added rpt_debug_show_temp_data procedure |
56 --| 26-JAN-2006 shu fix bug #4997302 for report UOM conversion |
57 --| 08-FEB-2006 shu fix bug #4997302 for date conversion and |
58 --| reversed Price/Cost conversion |
59 --| 14-FEB-2006 shu fix bug #4997302 for the item cost column |
60 --| 13-MAR-2006 amy remove commented code |
61 --| 19-MAY-2006 amy updated procedure cfr_before_report_trigger to fix bug #5212686 |
62 --| 24-MAY-2006 amy updated procedure get_onhand_components to fix sqlid#17703607 in bug #5212686 |
63 --| 22-JUNE-2006 amy updated procedure rpt_get_Comp_Estimated_data and rpt_get_Int_data |
64 --| to fix bug #5231233 |
65 --| 26-JUNE-2006 amy updated procedure get_unconsumed_sub_po AND get_unconsumed_rep_po |
66 --| to fix potensial issue,which occurs when same onhand components are found in different tp organization |
67 --| 28-JUNE-2006 amy updated procedure get_unallocated_rep_po |
68 --| to fix potensial issue that some unallocated repPO are lost in the report |
69 --| this issue was found when fixed bug #5232863 |
70 --| 28-JUNE-2006 amy updated procedure rpt_get_crude_data to fix project_number related issue |
71 --| 19-JUNE-2006 amy updated to fix bug 5391412 |
72 --| Updated procedure rpt_get_SubPO_data to get all received subPOs(partial or fully) |
73 --| instead of get those subPOs causing onhand Qty |
74 --| Renamed original procedure rpt_get_SubPO_data as rpt_get_SubPO_data_Onhand for future use |
75 --| instead of get those subPOs causing onhand Qty |
76 --| Added global variable g_ou_id to pass ou infro to procedure rpt_get_SubPO_data |
77 --| Added global variable CFR_EXT_SUBPO_AFT_ONHAND to identify original subpo data in rpt_temp table |
78 --| 28-JUNE-2006 amy updated procedure rpt_get_SubPO_data to fix bug 5415777 |
79 --| 07-SEP-2006 amy updated procedure rpt_get_crude_data to fix bug 5510828(get project id for table sub not from rcv transactions) |
80 --| updated procedure rpt_get_Comp_Estimated_data to fix bug 5510828(insert project_num/task_num information into rpt temp data(100) |
81 --| updated procedure rpt_get_SubPo_data to fix bug 5510828(insert project_num/task_num information into rpt temp data(110) |
82 --| 18-SEP-2006 amy updated procedure get_unallocated_components to fix bug 5509464(get vendor_id/vendor_site_id from repleninshment po information in receiving transaction query) |
83 --| updated procedure get_unallocated_rep_po to fix bug 5509464(updated sql query criteria to cover the cases when having some rep POs have corresponding rep SOs |
84 --| but are not allocated to a subcontracting po. |
85 --| updated procedure rpt_get_crude_data to fix bug 5509464(updated sql query criteria to cover the cases when having some rep POs have corresponding rep SOs |
86 --| but are not allocated to a subcontracting po. |
87 --| 28-SEP-2006 amy updated procedure get_unallocated_components
88 --| get_unallocated_rep_po |
89 --| set_rep_po_residual_unalloc |
90 --| set_rcv_transaction_unalloc |
91 --| set_sub_po_residual_unconsume |
92 --| set_rcv_transaction_unconsume |
93 --| to fix potential issue of operations between null numbers |
94 --| 17-NOV-2006 amy updated procedure cfr_before_report_trigger, |
95 --| add_data_to_cfr_temp and |
96 --| rpt_get_UnReceived_data to fix bug 5583680 |
97 --| 20-NOV-2006 amy updated procedure rpt_get_Comp_Estimated_data |
98 --| and rpt_get_Int_data to fix bug 5665445. |
99 --| The issue was mainly due to the duplicated |
100 --| conversions of the onhand qty from the |
101 --| Secondary UOM, which caused the resulting |
102 --| onhand qty to be much smaller than the actual |
103 --| onhand qty. |
104 --| 21-NOV-2006 vchu continued bug fix for bug 5583680: |
105 --| modified the query in rpt_get_Received_data |
106 --| to correctly fetched the replenishments |
107 --| received in past xx days. Also, replaced |
108 --| usages of shipping_quantity_uom column of the |
109 --| oe_order_lines_all table by the |
110 --| order_quantity_uom, since this is the correct |
111 --| uom accompanying the shipped_quantity. |
112 --| 21-NOV-2006 vchu bug fix for 5665334: Changed the signature |
113 --| of cfr_before_report_trigger to pass in the |
114 --| ID of the FROM and TO OEM Organization, |
115 --| instead of the name. Also added a new helper |
116 --| procedure to get the name of an Inventory |
117 --| organization given the ID. This procedure is |
118 --| called in cfr_before_report_trigger in order |
119 --| to get the name of the TO and FROM |
120 --| Organization in the current session language. |
121 --| 08-DEC-2006 amy bug fix for 5702139: Updated procedure |
122 --| get_unallocated_rep_po to pass correct uom |
123 --| jmf_shikyu_allocations.uom to parameter p_current_uom_code |
124 --| when call function get_item_primary_quantity |
125 --| to convert allocated quantity |
126 --| 11-DEC-2006 amy bug fix for 5702139: Updated procedure |
127 --| get_unallocated_rep_po again to pass primary |
128 --| uom to parameter p_current_uom_code when |
129 --| the replenishment PO is never allocated to a |
130 --| subcontracting PO |
131 --| 04-OCT-2007 kdevadas 12.1 Buy/Sell Subcontracting changes |
132 --| Reference - GBL_BuySell_TDD.doc |
133 --| Reference - GBL_BuySell_FDD.doc |
134 --| 16-NOV-2007 kdevadas Bug 6630087 - Removed the standard cost check|
135 --| as Buy/Sell subcontracting supports non-std |
136 --| OEM orgs as well |
137 --+===========================================================================+
138
139 --=============================================
140 -- CONSTANTS
141 --=============================================
142 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || G_PKG_NAME || '.';
143
144 --=============================================
145 -- GLOBAL VARIABLES
146 --=============================================
147
148 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
149 --Amy add for fixing bug 5391412 start
150 g_ou_id NUMBER:=0;
151 --Amy add for fixing bug 5391412 end
152
153 --The report region for confirmation report
154 CFR_CRUDE_DATA CONSTANT NUMBER := 10;
155 CFR_EXT_COMPONENT CONSTANT NUMBER := 100;
156 CFR_EXT_SUBCONTRACT_PO CONSTANT NUMBER := 110;
157 CFR_EXT_UN_RCV CONSTANT NUMBER := 120;
158 CFR_EXT_RCV_IN_DAYS CONSTANT NUMBER := 130;
159 --Amy add for fixing bug 5391412 start
160 CFR_EXT_SUBPO_AFT_ONHAND CONSTANT NUMBER := 140;
161 --Amy add for fixing bug 5391412 end
162 CFR_INT_COMPONENT CONSTANT NUMBER := 200;
163
164 --the constant for temporary table rowtype identify for confirmation report
165 CFR_TMP_ONHAND_ROW CONSTANT NUMBER := 10; --The onhand information
166 CFR_TMP_REP_PO_UNALLOCATED_ROW CONSTANT NUMBER := 20; --The replenishment purchase order unallocated info
167 CFR_TMP_REP_PO_UNCONSUMED_ROW CONSTANT NUMBER := 30; --The replenishment purchase order unconsumed info
168 CFR_TMP_SUB_PO_UNCONSUMED_ROW CONSTANT NUMBER := 40; --The subcontracting purchase order info
169 CFR_TMP_RCV_ROW CONSTANT NUMBER := 50; --The receive transactions info
170
171 --the constant for temporary table get_rcv_flag for confirmation report
172 CFR_REP_PO_GET_RCV_FLAG CONSTANT VARCHAR2(1) := 'Y'; --this rep_po line has been used for get_rcv_transactions
173 CFR_SUB_PO_GET_REP_FLAG CONSTANT VARCHAR2(1) := 'Y'; --this sub_po line has been used for get_replenishment po
174
175 -- Bug 5665334
176 --========================================================================
177 -- PROCEDURE : get_organization_name PUBLIC
178 -- PARAMETERS: p_organization_id Inventory Organization ID
179 -- : x_organization_name Organization Name to be returned
180 -- COMMENT : The procedure returns the name of an Inventory Organization
181 -- if the input ID parameter was valid.
182 --========================================================================
183 PROCEDURE get_organization_name
184 ( p_organization_id IN NUMBER
185 , x_organization_name OUT NOCOPY VARCHAR2
186 )
187 IS
188 BEGIN
189
190 x_organization_name := NULL;
191
192 IF p_organization_id IS NOT NULL
193 THEN
194 SELECT name
195 INTO x_organization_name
196 FROM hr_all_organization_units_tl haoutl
197 WHERE haoutl.organization_id = p_organization_id
198 AND haoutl.LANGUAGE = USERENV('LANG');
199 END IF;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 x_organization_name := NULL;
204
205 WHEN OTHERS THEN
206 x_organization_name := NULL;
207
208 END get_organization_name;
209
210 --========================================================================
211 -- PROCEDURE : cfr_before_report_trigger PUBLIC
212 -- PARAMETERS: p_rpt_mode the report mode: External/Internal report
213 -- : p_ou_id Operating unit id
214 -- : p_supplier_name_from the supplier name from
215 -- : p_supplier_name_to the supplier name to
216 -- : p_supplier_site_code_from the supplier site code from
217 -- : p_supplier_site_code_to the supplier site code to
218 -- : p_oem_inv_org_name_from oem inventory org name from
219 -- : p_oem_inv_org_name_to oem inventory org name to
220 -- : p_item_number_from item number from
221 -- : p_item_number_to item number to
222 -- : p_days_received received after the days ago
223 -- : p_sort_by By Supplier/Site or By Item,
224 -- the External report can use only by Supplier/Site
225 -- : p_currency_conversion_type the currency conversion type
226 -- : p_currency_conversion_date the currency conversion date
227 -- COMMENT : this procedure will be called in the before report trigger,
228 -- all the other needed procedures will be called in this procedure
229 -- PRE-COND :
230 -- EXCEPTIONS:
231 --========================================================================
232 PROCEDURE cfr_before_report_trigger
233 (
234 p_rpt_mode IN VARCHAR2
235 ,p_ou_id IN NUMBER
236 ,p_supplier_name_from IN VARCHAR2
237 ,p_supplier_site_code_from IN VARCHAR2
238 ,p_supplier_name_to IN VARCHAR2
239 ,p_supplier_site_code_to IN VARCHAR2
240 -- Bug 5665334
241 ,p_oem_inv_org_id_from IN NUMBER
242 ,p_oem_inv_org_id_to IN NUMBER
243 ,p_item_number_from IN VARCHAR2
244 ,p_item_number_to IN VARCHAR2
245 ,p_days_received IN NUMBER
246 ,p_sort_by IN VARCHAR2
247 ,p_currency_conversion_type IN VARCHAR2
248 ,p_currency_conversion_date IN VARCHAR2 --from Report parameter, use VARCHAR2 not DATE
249 ,p_functional_currency IN VARCHAR2
250 ) IS
251
252 l_api_name CONSTANT VARCHAR2(30) := 'cfr_before_report_trigger';
253 l_currency_conversion_date DATE;
254 --for checking SHIKYU enable profile.
255 l_jmf_shk_not_enabled VARCHAR2(240);
256 l_conc_succ BOOLEAN;
257 l_functional_currency gl_ledgers.currency_code%TYPE;
258
259 -- Bug 5665334
260 l_oem_inv_org_name_from hr_all_organization_units_tl.name%TYPE := NULL;
261 l_oem_inv_org_name_to hr_all_organization_units_tl.name%TYPE := NULL;
262
263 BEGIN
264
265 --Amy add for fixing bug 5391412 start
266 -- set g_ou_id for rpt_get_subPO_data procedure.
267 g_ou_id := p_ou_id;
268 --Amy add for fixing bug 5391412 end
269
270 -- Bug 5665334: Get the name of the OEM Organization From and Organization To
271 get_organization_name
272 ( p_organization_id => p_oem_inv_org_id_from
273 , x_organization_name => l_oem_inv_org_name_from
274 );
275 get_organization_name
276 ( p_organization_id => p_oem_inv_org_id_to
277 , x_organization_name => l_oem_inv_org_name_to
278 );
279
280 -- **** for debug information in readonly UT environment.--- begin ****
281 JMF_SHIKYU_RPT_UTIL.debug_output
282 (
283 p_output_to => 'FND_LOG.STRING'
284 ,p_api_name => G_MODULE_PREFIX || l_api_name || '.begin'
285 ,p_message => 'CVS Version(v 2.26 2006/02/14):' ||
286 ',p_rpt_mode:' || p_rpt_mode ||
287 ',p_ou_id :' || g_ou_id ||
288 ',p_supplier_name_from:' || p_supplier_name_from ||
289 ',p_supplier_site_code_from:' || p_supplier_site_code_from ||
290 ',p_supplier_name_to :' || p_supplier_name_to ||
291 ',p_supplier_site_code_to:' || p_supplier_site_code_to ||
292 ',p_oem_inv_org_id_from:' || p_oem_inv_org_id_from ||
293 ',l_oem_inv_org_name_from:' || l_oem_inv_org_name_from ||
294 ',p_oem_inv_org_id_to:' || p_oem_inv_org_id_to ||
295 ',l_oem_inv_org_name_to:' || l_oem_inv_org_name_to ||
296 ',p_item_number_from:' || p_item_number_from ||
297 ',p_item_number_to:' || p_item_number_to ||
298 ',p_days_received:' || p_days_received ||
299 ',p_sort_by:' || p_sort_by ||
300 ',p_currency_conversion_type:' || p_currency_conversion_type ||
301 ',p_currency_conversion_date:' || p_currency_conversion_date ||
302 ',p_functional_currency:' || p_functional_currency
303 );
304 -- **** for debug information in readonly UT environment.--- end ****
305
306 /*IF p_currency_conversion_date IS NULL */
307 /* the input date format like 30-DEC-2005 in the parameter form
308 Use fnd_date.canonical_to_date rather than use to_date, as some date formate issue.
309 */
310 IF p_currency_conversion_date IS NULL
311 THEN
312 l_currency_conversion_date := SYSDATE;
313 ELSE
314 l_currency_conversion_date := fnd_date.canonical_to_date(p_currency_conversion_date);
315 END IF;
316
317 l_functional_currency := p_functional_currency;
318 IF (p_functional_currency IS NULL)
319 THEN
320 SELECT gl_ledgers.currency_code
321 INTO l_functional_currency
322 FROM gl_ledgers gl_ledgers
323 WHERE gl_ledgers.ledger_id =
324 /* (SELECT DISTINCT xllv.ledger_id
325 FROM xle_le_ou_ledger_v xllv
326 WHERE xllv.operating_unit_id = p_ou_id
327 );*/
328 (select set_of_books_id from hr_operating_units
329 where organization_id = p_ou_id);
330 END IF;
331 -- **** for debug information in readonly UT environment.--- begin ****
332 JMF_SHIKYU_RPT_UTIL.debug_output
333 (
334 p_output_to => 'FND_LOG.STRING'
335 ,p_api_name => G_MODULE_PREFIX || l_api_name
336 ,p_message => 'l_functional_currency:' || l_functional_currency ||
337 ',l_currency_conversion_date:' || l_currency_conversion_date
338 );
339 -- **** for debug information in readonly UT environment.--- end ****
340
341 IF g_fnd_debug = 'Y' AND
342 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
343 THEN
344 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
345 , g_module_prefix || l_api_name || '.begin'
346 , NULL);
347 END IF;
348
349 --check if the SHIKYU enable profile is set to Yes. if no then return one error and stop.
350 IF (NVL(FND_PROFILE.VALUE('JMF_SHK_CHARGE_BASED_ENABLED'), 'N') = 'N')
351 THEN
352 FND_MESSAGE.SET_NAME('JMF', 'JMF_SHK_NOT_ENABLE');
353 l_jmf_shk_not_enabled := FND_MESSAGE.GET;
354
355 fnd_file.PUT_LINE(fnd_file.output, l_jmf_shk_not_enabled);
356
357 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
358 ,message => l_jmf_shk_not_enabled);
359
360 IF g_fnd_debug = 'Y' AND
361 FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
362 THEN
363 IF l_conc_succ
364 THEN
365 fnd_log.STRING( FND_LOG.LEVEL_ERROR
366 ,g_module_prefix || l_api_name || '.Warning'
367 ,l_jmf_shk_not_enabled);
368 END IF;
369 END IF;
370
371 RETURN;
372 END IF;
373
374 --clear old data if any
375 DELETE FROM JMF_SHIKYU_CFR_MID_TEMP;
376 DELETE FROM JMF_SHIKYU_CFR_RPT_TEMP;
377 COMMIT;
378
379 -- Valid parameter validation;
380 -- if all parameters are valid then continue; otherwise raise an error message in log;
381 --get the on hand components information,with inv_org,item_id
382 -- **** for debug information in readonly UT environment.--- begin ****
383 JMF_SHIKYU_RPT_UTIL.debug_output
384 (
385 p_output_to => 'FND_LOG.STRING'
386 ,p_api_name => G_MODULE_PREFIX || l_api_name
387 ,p_message => 'begin, p_rpt_mode:' || p_rpt_mode || ',p_ou_id:' || p_ou_id
388 );
389 -- **** for debug information in readonly UT environment.--- end ****
390 get_onhand_components(p_onhand_row_type => CFR_TMP_ONHAND_ROW --p_onhand_row_type
391 ,p_ou_id => p_ou_id
392 ,p_supplier_name_from => p_supplier_name_from
393 ,p_supplier_site_code_from => p_supplier_site_code_from
394 ,p_supplier_name_to => p_supplier_name_to
395 ,p_supplier_site_code_to => p_supplier_site_code_to
396 ,p_oem_inv_org_name_from => l_oem_inv_org_name_from -- Bug 5665334
397 ,p_oem_inv_org_name_to => l_oem_inv_org_name_to -- Bug 5665334
398 ,p_item_number_from => p_item_number_from
399 ,p_item_number_to => p_item_number_to);
400
401 -- for each record in the onhand information (inv_org, item), get the confirmation data
402 -- **** for debug information in readonly UT environment.--- begin ****
403 JMF_SHIKYU_RPT_UTIL.debug_output
404 (
405 p_output_to => 'FND_LOG.STRING'
406 ,p_api_name => G_MODULE_PREFIX || l_api_name
407 ,p_message => 'begin, p_rpt_mode:' || p_rpt_mode || ',p_ou_id:' || p_ou_id
408 );
409 -- **** for debug information in readonly UT environment.--- end ****
410 get_rpt_confirmation_data(p_onhand_row_type => CFR_TMP_ONHAND_ROW --p_onhand_row_type
411 ,p_rep_po_unalloc_row_type => CFR_TMP_REP_PO_UNALLOCATED_ROW --p_rep_po_unalloc_row_type
412 ,p_rep_po_unconsumed_row_type => CFR_TMP_REP_PO_UNCONSUMED_ROW --p_rep_po_unconsumed_row_type
413 ,p_sub_po_unconsumed_row_type => CFR_TMP_SUB_PO_UNCONSUMED_ROW --p_sub_po_unconsumed_row_type
414 ,p_rcv_transaction_row_type => CFR_TMP_RCV_ROW --p_rcv_transaction_row_type
415 ,p_ou_id => p_ou_id
416 ,p_days_received => p_days_received
417 ,p_currency_conversion_type => p_currency_conversion_type
418 ,p_currency_conversion_date => l_currency_conversion_date);
419
420
421 -- create some data in the mid_temp table to make the data consistent
422 validate_cfr_mid_temp(p_rcv_row_type => CFR_TMP_RCV_ROW);
423
424 -- add the find data to temporary table for report, with the UOM Currency conversion
425 -- **** for debug information in readonly UT environment.--- begin ****
426 JMF_SHIKYU_RPT_UTIL.debug_output
427 (
428 p_output_to => 'FND_LOG.STRING'
429 ,p_api_name => G_MODULE_PREFIX || l_api_name
430 ,p_message => 'begin, p_rpt_mode:' || p_rpt_mode || ',p_ou_id:' || p_ou_id
431 );
432 -- **** for debug information in readonly UT environment.--- end ****
433 add_data_to_cfr_temp(p_rcv_row_type => CFR_TMP_RCV_ROW --p_rcv_row_type
434 ,p_rpt_mode => p_rpt_mode
435 ,p_days_received => p_days_received
436 ,p_currency_conversion_type => p_currency_conversion_type
437 ,p_currency_conversion_date => l_currency_conversion_date
438 ,p_functional_currency => l_functional_currency
439 -- Amy added to fix bug 5583680 start
440 ,p_supplier_name_from => p_supplier_name_from
441 ,p_supplier_site_code_from => p_supplier_site_code_from
442 ,p_supplier_name_to => p_supplier_name_to
443 ,p_supplier_site_code_to => p_supplier_site_code_to
444 ,p_oem_inv_org_name_from => l_oem_inv_org_name_from -- Bug 5665334
445 ,p_oem_inv_org_name_to => l_oem_inv_org_name_to -- Bug 5665334
446 -- Amy added to fix bug 5583680 end
447 );
448
449 -- **** for debug information in readonly UT environment.--- begin ****
450 JMF_SHIKYU_RPT_UTIL.debug_output
451 (
452 p_output_to => 'FND_LOG.STRING'
453 ,p_api_name => G_MODULE_PREFIX || l_api_name
454 ,p_message => 'end, p_rpt_mode:' || p_rpt_mode || ',p_ou_id:' || p_ou_id
455 );
456 -- **** for debug information in readonly UT environment.--- end ****
457
458 COMMIT;
459
460 --print the data in mid temp table for debug.
461 rpt_debug_show_mid_data(
462 p_row_type => NULL
463 ,p_output_to => 'FND_LOG.STRING'
464 );
465 rpt_debug_show_temp_data
466 (
467 p_rpt_data_type => NULL
468 ,p_output_to => 'FND_LOG.STRING'
469 );
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 --Set message name;
474 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
475 fnd_msg_pub.Add;
476
477 IF g_fnd_debug = 'Y' AND
478 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
479 THEN
480 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION
481 , G_MODULE_PREFIX || l_api_name || '.no_data'
482 , 'JMF_SHK_RPT_NO_DATA');
483 END IF;
484
485 WHEN OTHERS THEN
486 IF g_fnd_debug = 'Y' AND
487 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
488 THEN
489 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
490 , G_MODULE_PREFIX || l_api_name || '.exception'
491 , 'EXCEPTION:WHEN OTHERS THEN');
492 END IF;
493
494 ROLLBACK;
495
496 END cfr_before_report_trigger;
497
498 --========================================================================
499 -- PROCEDURE : cfr_get_onhand_components PUBLIC
500 -- PARAMETERS: p_onhand_row_type row type id to identify the
501 -- onhand components information
502 -- : p_ou_id Operating unit id
503 -- : p_supplier_name_from the supplier name from
504 -- : p_supplier_site_code_from the supplier site code from
505 -- : p_supplier_name_to the supplier name to
506 -- : p_supplier_site_code_to the supplier site code to
507 -- : p_oem_inv_org_name_from oem inventory org name from
508 -- : p_oem_inv_org_name_to oem inventory org name to
509 -- : p_item_number_from item id from
510 -- : p_item_number_to item id to
511 -- COMMENT : It is used to get all the onhand compoents primary UOM quantity;
512 -- (reference to the condition:''Operating Unit, 'from OEM INV organization' ,'to OEM INV organization',
513 -- 'From supplier','From site','To supplier','To site','From Item','To item')
514 -- PRE-COND :
515 -- EXCEPTIONS:
516 --========================================================================
517 PROCEDURE get_onhand_components
518 (
519 p_onhand_row_type IN NUMBER
520 ,p_ou_id IN NUMBER
521 ,p_supplier_name_from IN VARCHAR2
522 ,p_supplier_site_code_from IN VARCHAR2
523 ,p_supplier_name_to IN VARCHAR2
524 ,p_supplier_site_code_to IN VARCHAR2
525 ,p_oem_inv_org_name_from IN VARCHAR2
526 ,p_oem_inv_org_name_to IN VARCHAR2
527 ,p_item_number_from IN VARCHAR2
528 ,p_item_number_to IN VARCHAR2
529 ) IS
530
531 -- cursor for the OEM inventory organization,to get the org_id,org_code,org_name
532 -- refer to the org_name from and to condition
533 CURSOR l_cur_get_oem_inv_org_info
534 (lp_ou_id hr_all_organization_units.organization_id%TYPE
535 , lp_oem_inv_org_name_from hr_all_organization_units_vl.NAME%TYPE
536 , lp_oem_inv_org_name_to hr_all_organization_units_vl.NAME%TYPE
537 ) IS
538 SELECT mp.organization_id
539 ,mp.organization_code
540 ,haoutl.NAME
541 FROM mtl_parameters mp
542 ,hr_organization_information hoi
543 ,hr_all_organization_units haou
544 ,HR_ALL_ORGANIZATION_UNITS_TL haoutl
545 WHERE mp.organization_id = hoi.organization_id
546 AND haou.organization_id = hoi.organization_id
547 AND haou.organization_id = haoutl.organization_id
548 AND NVL(mp.trading_partner_org_flag,'N') = 'N'
549 AND hoi.org_information_context = 'Accounting Information'
550 AND hoi.org_information3 = lp_ou_id
551 AND haoutl.NAME >= NVL(lp_oem_inv_org_name_from
552 ,haoutl.NAME)
553 AND haoutl.NAME <= NVL(lp_oem_inv_org_name_to
554 ,haoutl.NAME)
555 AND haoutl.LANGUAGE = USERENV('LANG');
556
557 l_oem_inv_org_id mtl_parameters.organization_id%TYPE;
558 l_oem_inv_org_code mtl_parameters.organization_code%TYPE;
559 l_oem_inv_org_name hr_all_organization_units_vl.NAME%TYPE;
560
561 -- need to be cleared, how to know the restriction from the oem_inv_org and supplier/site
562 -- cursor for geting tp_inv_org_id using the specific oem_inv_org_id, supplier/site
563 CURSOR l_cur_get_tp_inv_org_info
564 ( lp_oem_inv_org_id mtl_interorg_parameters.from_organization_id%TYPE
565 , lp_supplier_name_from po_vendors.vendor_name%TYPE
566 , lp_supplier_name_to po_vendors.vendor_name%TYPE
567 , lp_supplier_site_code_from po_vendor_sites_all.vendor_site_code%TYPE
568 , lp_supplier_site_code_to po_vendor_sites_all.vendor_site_code%TYPE
569 )
570 IS
571 SELECT mip.to_organization_id --tp_org_id
572 ,mp.organization_code --tp_org_code
573 ,pv.vendor_id --supplier_id
574 ,pvs.vendor_site_id --supplier_site_id
575 FROM mtl_interorg_parameters mip
576 ,po_vendors pv
577 ,po_vendor_sites_all pvs
578 ,hr_organization_information hoi
579 ,mtl_parameters mp
580 WHERE mip.from_organization_id = lp_oem_inv_org_id
581 --AND mip.shikyu_enabled_flag = 'Y'
582 AND mip.subcontracting_type in ('B','C') -- 12.1 Buy/Sell Subcontracting Changes
583 AND mp.trading_partner_org_flag = 'Y' --hide for test as there is not data for this column!!!
584 AND hoi.org_information_context = 'Customer/Supplier Association' --to identify the flexfield
585 AND hoi.org_information3 = pv.vendor_id --(Application : Human Resources,Descriptive Flexfield Segment Title: Org Developer DF.)
586 AND hoi.org_information4 = pvs.vendor_site_id
587 AND mip.to_organization_id = hoi.organization_id
588 AND mip.to_organization_id = mp.organization_id
589 AND ((pv.vendor_name IS NULL) OR
590 ((pv.vendor_name >= NVL(lp_supplier_name_from
591 ,pv.vendor_name)) AND
592 (pv.vendor_name <= NVL(lp_supplier_name_to
593 ,pv.vendor_name))))
594 AND ((pvs.vendor_site_code IS NULL) OR
595 (pvs.vendor_site_code >=
596 NVL(lp_supplier_site_code_from
597 ,pvs.vendor_site_code)) AND
598 (pvs.vendor_site_code <=
599 NVL(lp_supplier_site_code_to
600 ,pvs.vendor_site_code)));
601
602 l_tp_inv_org_id mtl_parameters.organization_id%TYPE;
603 l_tp_inv_org_code mtl_parameters.organization_code%TYPE;
604 l_supplier_id po_vendors.vendor_id%TYPE;
605 l_supplier_site_id po_vendor_sites_all.vendor_site_id%TYPE;
606
607 l_api_name CONSTANT VARCHAR2(30) := 'get_onhand_components';
608
609 BEGIN
610
611 IF g_fnd_debug = 'Y' AND
612 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
613 THEN
614 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
615 , g_module_prefix || l_api_name || '.begin'
616 , NULL);
617 END IF;
618 -- **** for debug information in readonly UT environment.--- begin ****
619 JMF_SHIKYU_RPT_UTIL.debug_output
620 (
621 p_output_to => 'FND_LOG.STRING'
622 ,p_api_name => G_MODULE_PREFIX || l_api_name
623 ,p_message => 'begin:' ||
624 ',p_onhand_row_type:' || p_onhand_row_type ||
625 ',p_ou_id :' || p_ou_id ||
626 ',p_supplier_name_from:' || p_supplier_name_from ||
627 ',p_supplier_site_code_from:' || p_supplier_site_code_from ||
628 ',p_supplier_name_to :' || p_supplier_name_to ||
629 ',p_supplier_site_code_to:' || p_supplier_site_code_to ||
630 ',p_oem_inv_org_name_from:' || p_oem_inv_org_name_from ||
631 ',p_oem_inv_org_name_to:' || p_oem_inv_org_name_to ||
632 ',p_item_number_from:' || p_item_number_from ||
633 ',p_item_number_to:' || p_item_number_to
634 );
635 -- **** for debug information in readonly UT environment.--- end ****
636
637 -- Valid parameter validation;
638 -- if all parameters are valid then continue; otherwise raise an error message in log;
639
640 --get the oem_inv_org information, then for each oem_inv_org, find the TP inv_org
641 OPEN l_cur_get_oem_inv_org_info(p_ou_id
642 ,p_oem_inv_org_name_from
643 ,p_oem_inv_org_name_to);
644 LOOP
645 --find the oem organizations
646 FETCH l_cur_get_oem_inv_org_info
647 INTO l_oem_inv_org_id, l_oem_inv_org_code, l_oem_inv_org_name;
648
649 EXIT WHEN l_cur_get_oem_inv_org_info%NOTFOUND; -- no more oem organiztions
650 -- **** for debug information in readonly UT environment.--- begin ****
651 JMF_SHIKYU_RPT_UTIL.debug_output
652 (
653 p_output_to => 'FND_LOG.STRING'
654 ,p_api_name => G_MODULE_PREFIX || l_api_name
655 ,p_message => ',l_oem_inv_org_id:' || l_oem_inv_org_id ||
656 ',l_oem_inv_org_code :' || l_oem_inv_org_code ||
657 ',l_oem_inv_org_name:' || l_oem_inv_org_name
658 );
659 -- **** for debug information in readonly UT environment.--- end ****
660
661 --process for this oem_inv_org
662 --find the tp_inv_orgs that belongs to the oem_inv_org and supplier/sites
663 OPEN l_cur_get_tp_inv_org_info(l_oem_inv_org_id
664 ,p_supplier_name_from
665 ,p_supplier_name_to
666 ,p_supplier_site_code_from
667 ,p_supplier_site_code_to);
668 LOOP
669 --find the tp organizations
670 FETCH l_cur_get_tp_inv_org_info
671 INTO l_tp_inv_org_id, l_tp_inv_org_code, l_supplier_id, l_supplier_site_id;
672
673 EXIT WHEN l_cur_get_tp_inv_org_info%NOTFOUND; -- no more tp organiztions
674 -- **** for debug information in readonly UT environment.--- begin ****
675 JMF_SHIKYU_RPT_UTIL.debug_output
676 (
677 p_output_to => 'FND_LOG.STRING'
678 ,p_api_name => G_MODULE_PREFIX || l_api_name
679 ,p_message => ',l_tp_inv_org_id:' || l_tp_inv_org_id ||
680 ',l_tp_inv_org_code :' || l_tp_inv_org_code ||
681 ',l_supplier_id:' || l_supplier_id ||
682 ',l_supplier_site_id' || l_supplier_site_id
683 );
684 -- **** for debug information in readonly UT environment.--- end ****
685
686 -- get the onhand quantity for each TP inv org items.
687 -- for the specified tp_inv_org_id, find the items
688 -- insert the data into the mid temp table,
689 --???using the get_item_number function to get the flexfield values, or using segment1 for hardcoding?
690 INSERT INTO jmf_shikyu_cfr_mid_temp
691 (row_type --onhand row type
692 ,oem_inv_org_id --oem_inv_org_id
693 ,supplier_id --supplier_id
694 ,site_id --site_id
695 ,tp_inv_org_id --tp_inv_org_id
696 ,item_id --item_id
697 ,primary_unconsumed_quantity --onhand primary uom quantity
698 ,project_id --project_id
699 ,task_id --task_id
700 )
701 SELECT p_onhand_row_type
702 ,l_oem_inv_org_id
703 ,l_supplier_id
704 ,l_supplier_site_id
705 ,onhand.organization_id
706 ,onhand.inventory_item_id
707 ,SUM(onhand.transaction_quantity) primary_uom_qty
708 ,onhand.project_id
709 ,onhand.task_id
710 FROM MTL_ONHAND_QUANTITIES onhand
711 ,MTL_SYSTEM_ITEMS_B_KFV item_f -- the latest view for the item flexfield
712 WHERE onhand.organization_id = l_tp_inv_org_id
713 AND onhand.organization_id = item_f.organization_id
714 AND onhand.inventory_item_id = item_f.inventory_item_id
715 AND item_f.subcontracting_component IS NOT NULL --= 'Y'
716 /* AND item_f.concatenated_segments >=
717 NVL(p_item_number_from, item_f.concatenated_segments)
718 AND item_f.concatenated_segments <=
719 NVL(p_item_number_to, item_f.concatenated_segments)*/
720 AND (p_item_number_from IS NULL
721 OR item_f.concatenated_segments >= p_item_number_from)
722 AND (p_item_number_to IS NULL
723 OR item_f.concatenated_segments <= p_item_number_to)
724 GROUP BY onhand.organization_id
725 ,onhand.inventory_item_id
726 ,onhand.project_id
727 ,onhand.task_id;
728 END LOOP; --end loop of finding the tp organizations
729 CLOSE l_cur_get_tp_inv_org_info;
730
731 END LOOP; --end loop of finding the oem organizations
732 CLOSE l_cur_get_oem_inv_org_info;
733
734 COMMIT; -- for debug on UT ?????
735 --print the data in mid temp table for debug.
736 rpt_debug_show_mid_data(
737 p_row_type => p_onhand_row_type
738 ,p_output_to => 'FND_LOG.STRING'
739 );
740 EXCEPTION
741 WHEN NO_DATA_FOUND THEN
742 --Set message name;
743 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
744 fnd_msg_pub.Add;
745 -- **** for debug information in readonly UT environment.--- begin ****
746 JMF_SHIKYU_RPT_UTIL.debug_output
747 (
748 p_output_to => 'FND_LOG.STRING'
749 ,p_api_name => G_MODULE_PREFIX || l_api_name
750 ,p_message => 'NO_DATA_FOUND'
751 );
752 -- **** for debug information in readonly UT environment.--- end ****
753
754 WHEN OTHERS THEN
755 -- raise log message;
756
757 IF g_fnd_debug = 'Y' AND
758 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
759 THEN
760 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
761 , G_MODULE_PREFIX || l_api_name || '.exception'
762 , NULL);
763 END IF;
764 -- **** for debug information in readonly UT environment.--- begin ****
765 JMF_SHIKYU_RPT_UTIL.debug_output
766 (
767 p_output_to => 'FND_LOG.STRING'
768 ,p_api_name => G_MODULE_PREFIX || l_api_name
769 ,p_message => 'OTHERS'
770 );
771 -- **** for debug information in readonly UT environment.--- end ****
772
773 END get_onhand_components;
774
775 --========================================================================
776 -- PROCEDURE : get_rpt_confirmation_data PUBLIC
777 -- PARAMETERS: p_onhand_row_type row type id to identify the
778 -- onhand components information
779 -- : p_rep_po_unalloc_row_type replenishment po unalloc row in mid-temp table
780 -- : p_rep_po_unconsumed_row_type replenishment po unconsume row in mid-temp table
781 -- : p_sub_po_unconsumed_row_type subcontract po unconsume row in mid-temp table
782 -- : p_days_received received after the days ago
783 -- : p_sort_by By Supplier/Site or By Item, the External report can use only by Supplier/Site
784 -- : p_currency_conversion_type the currency conversion type
785 -- : p_currency_conversion_date the currency conversion date
786 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
787 -- this procedure is used to get the possilbe rcv_transaction data information
788 -- for those onhand SHIKYU components based on LIFO received date.
789 -- first the unallocated qty,then the allocated but unconsumed qty.
790 -- PRE-COND :
791 -- EXCEPTIONS:
792 --========================================================================
793 PROCEDURE get_rpt_confirmation_data
794 (
795 p_onhand_row_type IN NUMBER
796 ,p_rep_po_unalloc_row_type IN NUMBER
797 ,p_rep_po_unconsumed_row_type IN NUMBER
798 ,p_sub_po_unconsumed_row_type IN NUMBER
799 ,p_rcv_transaction_row_type IN NUMBER
800 ,p_ou_id IN NUMBER
801 ,p_days_received IN NUMBER
802 ,p_currency_conversion_type IN VARCHAR2
803 ,p_currency_conversion_date IN DATE
804 ) IS
805 l_api_name CONSTANT VARCHAR2(30) := 'get_rpt_confirmation_data';
806
807 l_supplier_id jmf_shikyu_cfr_mid_temp.supplier_id%TYPE;
808 l_supplier_site_id jmf_shikyu_cfr_mid_temp.site_id%TYPE;
809 l_oem_inv_org_id jmf_shikyu_cfr_mid_temp.oem_inv_org_id%TYPE;
810 l_tp_inv_org_id jmf_shikyu_cfr_mid_temp.tp_inv_org_id%TYPE;
811 l_item_id jmf_shikyu_cfr_mid_temp.item_id%TYPE;
812 l_project_id jmf_shikyu_cfr_mid_temp.project_id%TYPE;
813 l_task_id jmf_shikyu_cfr_mid_temp.task_id%TYPE;
814 l_onhand_quantity jmf_shikyu_cfr_mid_temp.primary_unconsumed_quantity%TYPE;
815 -- get on hand components information
816 CURSOR l_cur_get_onhand_components IS
817 SELECT supplier_id
818 ,site_id
819 ,oem_inv_org_id
820 ,tp_inv_org_id
821 ,item_id
822 ,project_id
823 ,task_id
824 ,primary_unconsumed_quantity
825 FROM jmf_shikyu_cfr_mid_temp
826 WHERE row_type = p_onhand_row_type;
827
828 BEGIN
829 IF g_fnd_debug = 'Y' AND
830 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
831 THEN
832 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
833 , g_module_prefix || l_api_name || '.begin'
834 , NULL);
835 END IF;
836 -- **** for debug information in readonly UT environment.--- begin ****
837 JMF_SHIKYU_RPT_UTIL.debug_output
838 (
839 p_output_to => 'FND_LOG.STRING'
840 ,p_api_name => G_MODULE_PREFIX || l_api_name
841 ,p_message => 'begin:' ||
842 ',p_onhand_row_type:' || p_onhand_row_type ||
843 ',p_rep_po_unalloc_row_type :' || p_rep_po_unalloc_row_type ||
844 ',p_rep_po_unconsumed_row_type:' || p_rep_po_unconsumed_row_type ||
845 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
846 ',p_rcv_transaction_row_type :' || p_rcv_transaction_row_type ||
847 ',p_ou_id:' || p_ou_id ||
848 ',p_days_received:' || p_days_received ||
849 ',p_currency_conversion_type:' || p_currency_conversion_type ||
850 ',p_currency_conversion_date:' || p_currency_conversion_date
851 );
852 -- **** for debug information in readonly UT environment.--- end ****
853
854 -- Valid parameter validation;
855 -- if all parameters are valid then continue; otherwise raise an error message in log;
856 OPEN l_cur_get_onhand_components;
857 LOOP
858 --find unallocated and allocated but unconsumed rcv_transactions for the inv_org,item.
859 FETCH l_cur_get_onhand_components
860 INTO l_supplier_id, l_supplier_site_id, l_oem_inv_org_id, l_tp_inv_org_id, l_item_id, l_project_id, l_task_id, l_onhand_quantity;
861
862 EXIT WHEN l_cur_get_onhand_components%NOTFOUND;
863 -- **** for debug information in readonly UT environment.--- begin ****
864 JMF_SHIKYU_RPT_UTIL.debug_output
865 (
866 p_output_to => 'FND_LOG.STRING'
867 ,p_api_name => G_MODULE_PREFIX || l_api_name
868 ,p_message => ',l_supplier_id:' || l_supplier_id ||
869 ',l_supplier_site_id :' || l_supplier_site_id ||
870 ',l_oem_inv_org_id:' || l_oem_inv_org_id ||
871 ',l_tp_inv_org_id:' || l_tp_inv_org_id ||
872 ',l_item_id :' || l_item_id ||
873 ',l_project_id:' || l_project_id ||
874 ',l_task_id:' || l_task_id ||
875 ',l_onhand_quantity:' || l_onhand_quantity
876 );
877 -- **** for debug information in readonly UT environment.--- end ****
878
879 --get the unallocated rcv_transactions
880 get_unallocated_components(p_rep_po_unalloc_row_type => p_rep_po_unalloc_row_type
881 ,p_rcv_transaction_row_type => p_rcv_transaction_row_type
882 ,p_ou_id => p_ou_id
883 ,p_supplier_id => l_supplier_id
884 ,p_supplier_site_id => l_supplier_site_id
885 ,p_oem_inv_org_id => l_oem_inv_org_id
886 ,p_tp_inv_org_id => l_tp_inv_org_id
887 ,p_item_id => l_item_id
888 ,p_project_id => l_project_id
889 ,p_task_id => l_task_id
890 ,x_need_to_find_pri_qty => l_onhand_quantity);
891
892 --get the allocated but unconsumed rcv_transactions
893 IF l_onhand_quantity > 0
894 THEN
895 get_unconsumed_components(p_sub_po_unconsumed_row_type => p_sub_po_unconsumed_row_type
896 ,p_rep_po_unconsumed_row_type => p_rep_po_unconsumed_row_type
897 ,p_rcv_transaction_row_type => p_rcv_transaction_row_type
898 ,p_ou_id => p_ou_id
899 ,p_supplier_id => l_supplier_id
900 ,p_supplier_site_id => l_supplier_site_id
901 ,p_oem_inv_org_id => l_oem_inv_org_id
902 ,p_tp_inv_org_id => l_tp_inv_org_id
903 ,p_item_id => l_item_id
904 ,p_project_id => l_project_id
905 ,p_task_id => l_task_id
906 ,x_need_to_find_pri_qty => l_onhand_quantity);
907 END IF;
908
909 -- record the l_onhand_quantity to column primary_unallocated_quantity
910 UPDATE jmf_shikyu_cfr_mid_temp
911 SET primary_unallocated_quantity = l_onhand_quantity
912 WHERE row_type = p_onhand_row_type
913 AND supplier_id = l_supplier_id
914 AND site_id = l_supplier_site_id
915 AND oem_inv_org_id = l_oem_inv_org_id
916 AND tp_inv_org_id = l_tp_inv_org_id
917 AND item_id = l_item_id
918 AND ((project_id IS NULL) OR (project_id = l_project_id))
919 AND ((task_id IS NULL) OR (task_id = l_task_id));
920
921 IF l_onhand_quantity > 0
922 THEN
923 -- the found onhand quantity has no matched rcv_transactions
924 -- **** for debug information in readonly UT environment.--- begin ****
925 JMF_SHIKYU_RPT_UTIL.debug_output
926 (
927 p_output_to => 'FND_LOG.STRING'
928 ,p_api_name => G_MODULE_PREFIX || l_api_name
929 ,p_message => '** onhand quantity :' || l_onhand_quantity ||', has no matched rcv_transactions'
930 );
931 -- **** for debug information in readonly UT environment.--- end ****
932 END IF;
933
934 END LOOP; -- end of find the onhand information
935 CLOSE l_cur_get_onhand_components;
936
937 COMMIT; -- for debug on UT ?????
938 IF g_fnd_debug = 'Y' AND
939 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
940 THEN
941 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
942 , g_module_prefix || l_api_name || '.end'
943 , NULL);
944 END IF;
945 EXCEPTION
946 WHEN NO_DATA_FOUND THEN
947 -- raise log message;
948 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
949 fnd_msg_pub.Add;
950
951 WHEN OTHERS THEN
952
953 IF g_fnd_debug = 'Y' AND
954 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
955 THEN
956 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
957 , G_MODULE_PREFIX || l_api_name || '.execption'
958 , NULL);
959 END IF;
960 -- **** for debug information in readonly UT environment.--- begin ****
961 JMF_SHIKYU_RPT_UTIL.debug_output
962 (
963 p_output_to => 'FND_LOG.STRING'
964 ,p_api_name => G_MODULE_PREFIX || l_api_name
965 ,p_message => 'WHEN OTHERS THEN'
966 );
967 -- **** for debug information in readonly UT environment.--- end ****
968
969 END get_rpt_confirmation_data;
970
971 --========================================================================
972 -- PROCEDURE : get_unallocated_components PUBLIC
973 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
974 -- unallocated components information
975 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
976 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
977 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
978 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
979 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
980 -- : p_item_id the item_id got from the onhand info from mid temp table
981 -- : x_need_to_find_pri_qty the need to find quantity under primary UOM
982 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
983 -- this procedure is used to get the possilbe unallocated rcv_transaction data information
984 -- for those onhand SHIKYU components based on LIFO received date.
985 -- PRE-COND :
986 -- EXCEPTIONS:
987 --========================================================================
988 PROCEDURE get_unallocated_components
989 (
990 p_rep_po_unalloc_row_type IN NUMBER
991 ,p_rcv_transaction_row_type IN NUMBER
992 ,p_ou_id IN NUMBER
993 ,p_supplier_id IN NUMBER
994 ,p_supplier_site_id IN NUMBER
995 ,p_oem_inv_org_id IN NUMBER
996 ,p_tp_inv_org_id IN NUMBER
997 ,p_item_id IN NUMBER
998 ,p_project_id IN NUMBER
999 ,p_task_id IN NUMBER
1000 ,x_need_to_find_pri_qty IN OUT NOCOPY NUMBER
1001 ) IS
1002 l_api_name CONSTANT VARCHAR2(30) := 'get_unallocated_components';
1003
1004 -- get rcv_transaction information, rcv_transaction_id, rcv_unprocessed_qty
1005 -- decrease the rcv quantity processed and logged in the jmf_shikyu_cfr_mid_temp table.
1006 CURSOR l_cur_get_rcv_transaction_info IS
1007 SELECT rt.transaction_id
1008 -- Updated to fix potential issue of operations between null numbers
1009 -- ,rt.primary_quantity -
1010 ,NVL(rt.primary_quantity,0) -
1011 NVL((SELECT SUM(NVL(jscmt_rcv.primary_unallocated_quantity
1012 ,0) + NVL(jscmt_rcv.primary_unconsumed_quantity
1013 ,0))
1014 FROM jmf_shikyu_cfr_mid_temp jscmt_rcv
1015 WHERE jscmt_rcv.row_type = 40
1016 AND jscmt_rcv.shikyu_id = rt.transaction_id)
1017 ,0)
1018 --Added to fix bug 5509464 start
1019 ,pha.vendor_id --p_supplier_id
1020 ,pha.vendor_site_id --p_supplier_site_id
1021 --Added to fix bug 5509464 end
1022 FROM jmf_shikyu_cfr_mid_temp mid
1023 ,po_line_locations_all pll
1024 ,rcv_transactions rt
1025 --Added to fix bug 5509464 start
1026 ,po_headers_all pha
1027 --Added to fix bug 5509464 end
1028 WHERE mid.row_type = p_rep_po_unalloc_row_type
1029 AND NVL(mid.get_rcv_flag
1030 ,'N') <> CFR_REP_PO_GET_RCV_FLAG --if the rep_po line have done get rcv process, the get_rcv_flag will be set to 1
1031 AND mid.shikyu_id = pll.line_location_id
1032 AND rt.transaction_type = 'RECEIVE'
1033 AND pll.line_location_id = rt.po_line_location_id
1034 --Added to fix bug 5509464 start
1035 AND pha.po_header_id=pll.po_header_id
1036 --Added to fix bug 5509464 end
1037 ORDER BY rt.transaction_date;
1038
1039 l_rcv_transaction_id rcv_transactions.transaction_id%TYPE; --for cursor
1040 l_rcv_unprocessed_primary_qty rcv_transactions.primary_quantity%TYPE; --for cursor
1041 --Added to fix bug 5509464 start
1042 l_supplier_id po_headers_all.vendor_id%TYPE; --for cursor
1043 l_supplier_site_id po_headers_all.vendor_site_id%TYPE; --for cursor
1044 --Added to fix bug 5509464 end
1045
1046 l_rep_po_residual_pri rcv_transactions.primary_quantity%TYPE;
1047 l_rcv_unallocated_pri rcv_transactions.primary_quantity%TYPE;
1048
1049 BEGIN
1050 IF g_fnd_debug = 'Y' AND
1051 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1052 THEN
1053 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1054 , g_module_prefix || l_api_name || '.begin'
1055 , NULL);
1056 END IF;
1057 -- **** for debug information in readonly UT environment.--- begin ****
1058 JMF_SHIKYU_RPT_UTIL.debug_output
1059 (
1060 p_output_to => 'FND_LOG.STRING'
1061 ,p_api_name => G_MODULE_PREFIX || l_api_name
1062 ,p_message => 'begin:' ||
1063 ',p_rep_po_unalloc_row_type :' || p_rep_po_unalloc_row_type ||
1064 ',p_rcv_transaction_row_type :' || p_rcv_transaction_row_type ||
1065 ',p_ou_id :' || p_ou_id ||
1066 ',p_supplier_id:' || p_supplier_id ||
1067 ',p_supplier_site_id:' || p_supplier_site_id ||
1068 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1069 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1070 ',p_item_id:' || p_item_id ||
1071 ',p_project_id:' || p_project_id ||
1072 ',p_task_id:' || p_task_id
1073 );
1074 -- **** for debug information in readonly UT environment.--- end ****
1075
1076 -- Valid parameter validation;
1077 -- if all parameters are valid then continue; otherwise raise an error message in log;
1078
1079 --step1:find all the replenishment PO with oem_inv_org,supplier/site,tp_inv_org,item that have unallocated components
1080 get_unallocated_rep_po(p_rep_po_unalloc_row_type => p_rep_po_unalloc_row_type
1081 ,p_ou_id => p_ou_id
1082 ,p_supplier_id => p_supplier_id
1083 ,p_supplier_site_id => p_supplier_site_id
1084 ,p_oem_inv_org_id => p_oem_inv_org_id
1085 ,p_tp_inv_org_id => p_tp_inv_org_id
1086 ,p_item_id => p_item_id
1087 ,p_project_id => p_project_id
1088 ,p_task_id => p_task_id);
1089 --step2:find all the received transactions for the all replenishment PO above, based on LIFO receive date
1090 --
1091 OPEN l_cur_get_rcv_transaction_info;
1092 LOOP
1093 --begin rcv_transaction information
1094 EXIT WHEN x_need_to_find_pri_qty <= 0;
1095
1096 FETCH l_cur_get_rcv_transaction_info
1097 --Updated to fix bug 5509464 start
1098 --INTO l_rcv_transaction_id, l_rcv_unprocessed_primary_qty;
1099 INTO l_rcv_transaction_id, l_rcv_unprocessed_primary_qty,l_supplier_id,l_supplier_site_id;
1100 --Updated to fix bug 5509464 end
1101
1102 EXIT WHEN l_cur_get_rcv_transaction_info%NOTFOUND;
1103 -- **** for debug information in readonly UT environment.--- begin ****
1104 JMF_SHIKYU_RPT_UTIL.debug_output
1105 (
1106 p_output_to => 'FND_LOG.STRING'
1107 ,p_api_name => G_MODULE_PREFIX || l_api_name
1108 ,p_message => 'begin:' ||
1109 ',x_need_to_find_pri_qty :' || x_need_to_find_pri_qty ||
1110 ',l_rcv_transaction_id :' || l_rcv_transaction_id ||
1111 ',l_rcv_unprocessed_primary_qty :' || l_rcv_unprocessed_primary_qty
1112 );
1113 -- **** for debug information in readonly UT environment.--- end ****
1114
1115 --step3:find the possible onhand components(for the unallocated )
1116 l_rep_po_residual_pri := get_rep_po_residual_unalloc(p_rep_po_unalloc_row_type => p_rep_po_unalloc_row_type
1117 ,p_ou_id => p_ou_id
1118 ,p_rcv_transaction_id => l_rcv_transaction_id
1119 --Updated to fix bug 5509464 start
1120 --,p_supplier_id => p_supplier_id
1121 --,p_supplier_site_id => p_supplier_site_id
1122 ,p_supplier_id => l_supplier_id
1123 ,p_supplier_site_id => l_supplier_site_id
1124 --Updated to fix bug 5509464 end
1125 ,p_oem_inv_org_id => p_oem_inv_org_id
1126 ,p_tp_inv_org_id => p_tp_inv_org_id
1127 ,p_item_id => p_item_id
1128 ,p_project_id => p_project_id
1129 ,p_task_id => p_task_id); --the quantity for primary uom
1130
1131 IF l_rep_po_residual_pri > 0
1132 THEN
1133 --the possilbe unallocated rcv_transactions primary quantity
1134 l_rcv_unallocated_pri := jmf_shikyu_rpt_util.get_min3(p_number1 => l_rcv_unprocessed_primary_qty
1135 ,p_number2 => l_rep_po_residual_pri
1136 ,p_number3 => x_need_to_find_pri_qty);
1137
1138 --insert data to rcv_temp marking the processed rcv_transaction,
1139 set_rcv_transaction_unalloc(p_rcv_row_type => p_rcv_transaction_row_type
1140 ,p_ou_id => p_ou_id
1141 ,p_rcv_transaction_id => l_rcv_transaction_id
1142 ,p_rcv_unallocated_pri => l_rcv_unallocated_pri
1143 --Updated to fix bug 5509464 start
1144 ,p_supplier_id => p_supplier_id
1145 ,p_supplier_site_id => p_supplier_site_id
1146 --,l_supplier_id => p_supplier_id
1147 --,l_supplier_site_id => p_supplier_site_id
1148 --Updated to fix bug 5509464 end
1149 ,p_oem_inv_org_id => p_oem_inv_org_id
1150 ,p_tp_inv_org_id => p_tp_inv_org_id
1151 ,p_item_id => p_item_id
1152 ,p_project_id => p_project_id
1153 ,p_task_id => p_task_id);
1154
1155 --the residual unallocated quantity for primary quantity
1156 --update replenishment po residual quantity information
1157 set_rep_po_residual_unalloc(p_rep_po_unalloc_row_type => p_rep_po_unalloc_row_type
1158 ,p_rcv_transaction_row_type => p_rcv_transaction_row_type
1159 ,p_ou_id => p_ou_id
1160 ,p_rcv_transaction_id => l_rcv_transaction_id
1161 --Updated to fix bug 5509464 start
1162 --,p_supplier_id => p_supplier_id
1163 --,p_supplier_site_id => p_supplier_site_id
1164 ,p_supplier_id => l_supplier_id
1165 ,p_supplier_site_id => l_supplier_site_id
1166 --Updated to fix bug 5509464 end
1167 ,p_oem_inv_org_id => p_oem_inv_org_id
1168 ,p_tp_inv_org_id => p_tp_inv_org_id
1169 ,p_item_id => p_item_id
1170 ,p_project_id => p_project_id
1171 ,p_task_id => p_task_id
1172 ,p_new_rep_po_unallocated_pri => l_rcv_unallocated_pri);
1173 --update the need to find primary quantity
1174 x_need_to_find_pri_qty := x_need_to_find_pri_qty -
1175 l_rcv_unallocated_pri;
1176
1177 END IF;
1178
1179 END LOOP; -- end of find the rcv_transaction information
1180 CLOSE l_cur_get_rcv_transaction_info;
1181
1182 --step4:update the get_rcv_flags for the rep_po lines, move to above, need the sucess status
1183 COMMIT; -- for debug on UT ?????
1184 IF g_fnd_debug = 'Y' AND
1185 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1186 THEN
1187 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1188 , g_module_prefix || l_api_name || '.end'
1189 , NULL);
1190 END IF;
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND THEN
1193 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
1194 fnd_msg_pub.Add;
1195 WHEN OTHERS THEN
1196 IF g_fnd_debug = 'Y' AND
1197 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1198 THEN
1199 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1200 , G_MODULE_PREFIX || l_api_name || '.exception'
1201 , NULL);
1202 END IF;
1203 -- **** for debug information in readonly UT environment.--- begin ****
1204 JMF_SHIKYU_RPT_UTIL.debug_output
1205 (
1206 p_output_to => 'FND_LOG.STRING'
1207 ,p_api_name => G_MODULE_PREFIX || l_api_name
1208 ,p_message => 'WHEN OTHERS THEN'
1209 );
1210 -- **** for debug information in readonly UT environment.--- end ****
1211
1212 END get_unallocated_components;
1213
1214 --========================================================================
1215 -- PROCEDURE : get_unallocated_rep_po PUBLIC ,get_unallocated_replenishment_po
1216 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
1217 -- unallocated components information
1218 -- : p_ou_id the operating unit id
1219 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
1220 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
1221 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
1222 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
1223 -- : p_item_id the item_id got from the onhand info from mid temp table
1224 -- COMMENT : find the replenishment purchase order that have unallocated receipts for the item
1225 -- and insert the result to mid temp table
1226 -- PRE-COND :
1227 -- EXCEPTIONS:
1228 --========================================================================
1229 PROCEDURE get_unallocated_rep_po
1230 (
1231 p_rep_po_unalloc_row_type IN NUMBER
1232 ,p_ou_id IN NUMBER
1233 ,p_supplier_id IN NUMBER
1234 ,p_supplier_site_id IN NUMBER
1235 ,p_oem_inv_org_id IN NUMBER
1236 ,p_tp_inv_org_id IN NUMBER
1237 ,p_item_id IN NUMBER
1238 ,p_project_id IN NUMBER
1239 ,p_task_id IN NUMBER
1240 ) IS
1241 l_api_name CONSTANT VARCHAR2(30) := 'get_unallocated_rep_po';
1242
1243 BEGIN
1244 IF g_fnd_debug = 'Y' AND
1245 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1246 THEN
1247 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1248 , g_module_prefix || l_api_name || '.begin'
1249 , NULL);
1250 END IF;
1251 -- **** for debug information in readonly UT environment.--- begin ****
1252 JMF_SHIKYU_RPT_UTIL.debug_output
1253 (
1254 p_output_to => 'FND_LOG.STRING'
1255 ,p_api_name => G_MODULE_PREFIX || l_api_name
1256 ,p_message => 'begin:' ||
1257 ',p_rep_po_unalloc_row_type:' || p_rep_po_unalloc_row_type ||
1258 ',p_ou_id :' || p_ou_id ||
1259 ',p_supplier_id:' || p_supplier_id ||
1260 ',p_supplier_site_id:' || p_supplier_site_id ||
1261 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1262 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1263 ',p_item_id:' || p_item_id ||
1264 ',p_project_id:' || p_project_id ||
1265 ',p_task_id:' || p_task_id
1266 );
1267 -- **** for debug information in readonly UT environment.--- end ****
1268
1269 -- Valid parameter validation;
1270 -- if all parameters are valid then continue; otherwise raise an error message in log;
1271
1272 -- the allocated quantity for one po line_location_id, change the UOM in po and shikyu allocation to primary UOM
1273 -- only insert the rep_po that not in mid temp table, if in the table already, keep the data.
1274 INSERT INTO jmf_shikyu_cfr_mid_temp
1275 (row_type
1276 ,shikyu_id
1277 ,uom
1278 ,primary_uom
1279 ,primary_unallocated_quantity
1280 ,supplier_id
1281 ,site_id
1282 ,oem_inv_org_id
1283 ,tp_inv_org_id
1284 ,item_id)
1285 SELECT p_rep_po_unalloc_row_type
1286 ,plla.line_location_id
1287 ,uom_tl.uom_code
1288 ,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(p_tp_inv_org_id
1289 ,p_item_id)
1290 ,JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
1291 ,pla.item_id
1292 ,uom_tl.uom_code
1293 -- Updated to fix potential issue of operations between null numbers
1294 -- ,plla.quantity_received) -
1295 ,NVL(plla.quantity_received,0)) -
1296 (SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.tp_organization_id
1297 ,jsr.shikyu_component_id
1298 -- fix bug 5702139
1299 -- ,uom_tl_s.uom_code
1300 ,decode(jsa.allocated_quantity
1301 ,null
1302 ,uom_tl_s.uom_code
1303 ,jsa.uom)
1304 --Updated to fix bug 5509464 start
1305 --,jsa.allocated_quantity)) pll_allocated
1306 ,nvl(jsa.allocated_quantity,0))) pll_allocated
1307 --Updated to fix bug 5509464 end
1308 FROM jmf_shikyu_allocations jsa
1309 ,jmf_shikyu_replenishments jsr
1310 ,MTL_UNITS_OF_MEASURE_TL uom_tl_s
1311 --Updated to fix bug 5509464 start
1312 --WHERE jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
1313 -- AND jsa.shikyu_component_id = jsr.shikyu_component_id
1314 WHERE jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
1315 AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
1316 --Updated to fix bug 5509464 end
1317 AND jsr.replenishment_po_shipment_id = plla.line_location_id
1318 AND plla.po_line_id = pla.po_line_id
1319 AND uom_tl_s.LANGUAGE = USERENV('LANG')
1320 -- fix bug 5702139
1321 -- AND pla.unit_meas_lookup_code = uom_tl_s.unit_of_measure
1322 AND po_uom_s.get_primary_uom(pla.item_id,p_tp_inv_org_id,null) = uom_tl_s.unit_of_measure -- primary UOM
1323 AND jsr.shikyu_component_id = p_item_id) residual_unallocated_pri
1324 ,pha.vendor_id --p_supplier_id
1325 ,pha.vendor_site_id --p_supplier_site_id
1326 ,p_oem_inv_org_id
1327 ,p_tp_inv_org_id
1328 ,p_item_id
1329 FROM po_headers_all pha
1330 ,po_lines_all pla
1331 ,po_line_locations_all plla
1332 ,MTL_UNITS_OF_MEASURE_TL uom_tl
1333 ,HR_ORGANIZATION_INFORMATION hoi -- Add this table to get information of oem
1334 WHERE pla.po_header_id = pha.po_header_id
1335 AND plla.po_line_id = pla.po_line_id
1336 AND pla.unit_meas_lookup_code = uom_tl.unit_of_measure
1337 AND uom_tl.LANGUAGE = USERENV('LANG')
1338 AND pha.org_id = p_ou_id
1339 AND hoi.ORGANIZATION_ID = p_oem_inv_org_id
1340 AND hoi.org_information_context = 'Customer/Supplier Association' --to identify the flexfield
1341 AND ((pha.vendor_id = hoi.org_information3) OR -- when org_information_context is set to be 'Customer/Supplier Association',this identify the supplier_id of org
1342 (pha.vendor_id IS NULL AND hoi.org_information3 IS NULL))
1343 AND ((pha.vendor_site_id = hoi.org_information4) OR -- when org_information_context is set to be 'Customer/Supplier Association',this identify the supplier_site_id of org
1344 (pha.vendor_site_id IS NULL AND hoi.org_information4 IS NULL))
1345 AND pla.item_id = p_item_id
1346 AND plla.line_location_id NOT IN
1347 (SELECT jscmt.shikyu_id
1348 FROM jmf_shikyu_cfr_mid_temp jscmt
1349 WHERE jscmt.row_type = p_rep_po_unalloc_row_type
1350 AND jscmt.item_id = p_item_id
1351 AND jscmt.oem_inv_org_id = p_oem_inv_org_id
1352 AND jscmt.tp_inv_org_id = p_tp_inv_org_id
1353 AND jscmt.supplier_id = p_supplier_id
1354 AND jscmt.site_id = p_supplier_site_id)
1355 --updated to fix bug 5232863 start
1356 -- AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(pha.org_id
1357 AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
1358 ,pla.item_id
1359 ,uom_tl.uom_code
1360 -- Updated to fix potential issue of operations between null numbers
1361 -- ,plla.quantity_received) >
1362 ,NVL(plla.quantity_received,0)) >
1363 -- (SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.tp_organization_id
1364 (SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
1365 --updated to fix bug 5232863 end
1366 ,jsr.shikyu_component_id
1367 -- fix bug 5702139
1368 -- ,uom_tl_s.uom_code
1369 ,decode(jsa.allocated_quantity
1370 ,null
1371 ,uom_tl_s.uom_code
1372 ,jsa.uom)
1373 --Updated to fix bug 5509464 start
1374 --,jsa.allocated_quantity)) pll_allocated
1375 ,nvl(jsa.allocated_quantity,0))) pll_allocated
1376 --Updated to fix bug 5509464 end
1377 FROM jmf_shikyu_allocations jsa
1378 ,jmf_shikyu_replenishments jsr
1379 ,MTL_UNITS_OF_MEASURE_TL uom_tl_s
1380 --Updated to fix bug 5509464 start
1381 --WHERE jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
1382 -- AND jsa.shikyu_component_id = jsr.shikyu_component_id
1383 WHERE jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
1384 AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
1385 --Updated to fix bug 5509464 end
1386 AND jsr.replenishment_po_shipment_id = plla.line_location_id
1387 AND plla.po_line_id = pla.po_line_id
1388 -- fix bug 5702139
1389 -- AND pla.unit_meas_lookup_code = uom_tl_s.unit_of_measure
1390 AND po_uom_s.get_primary_uom(pla.item_id,p_tp_inv_org_id,null) = uom_tl_s.unit_of_measure -- primary UOM
1391 AND jsr.shikyu_component_id = p_item_id);
1392
1393 --update the residual_unallocated(quantity) column under UOM.
1394 UPDATE jmf_shikyu_cfr_mid_temp
1395 SET quantity = NVL(primary_unallocated_quantity,0) *
1396 JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
1397 ,uom
1398 ,item_id)
1399 WHERE row_type = p_rep_po_unalloc_row_type
1400 AND quantity IS NULL --only for those do not get the primary uom
1401 AND supplier_id = p_supplier_id
1402 AND site_id = p_supplier_site_id
1403 AND oem_inv_org_id = p_oem_inv_org_id
1404 AND tp_inv_org_id = p_tp_inv_org_id
1405 AND item_id = p_item_id;
1406 COMMIT; -- for debug on UT ?????
1407
1408 --print the data in mid temp table for debug.
1409 rpt_debug_show_mid_data(
1410 p_row_type => p_rep_po_unalloc_row_type
1411 ,p_output_to => 'FND_LOG.STRING'
1412 );
1413 IF g_fnd_debug = 'Y' AND
1414 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1415 THEN
1416 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1417 , g_module_prefix || l_api_name || '.end'
1418 , NULL);
1419 END IF;
1420 EXCEPTION
1421 WHEN NO_DATA_FOUND THEN
1422 -- raise log message;
1423 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
1424 fnd_msg_pub.Add;
1425
1426 WHEN OTHERS THEN
1427
1428 IF g_fnd_debug = 'Y' AND
1429 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1430 THEN
1431 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1432 , G_MODULE_PREFIX || l_api_name || '.execption'
1433 , NULL);
1434 END IF;
1435 -- **** for debug information in readonly UT environment.--- begin ****
1436 JMF_SHIKYU_RPT_UTIL.debug_output
1437 (
1438 p_output_to => 'FND_LOG.STRING'
1439 ,p_api_name => G_MODULE_PREFIX || l_api_name
1440 ,p_message => 'WHEN OTHERS THEN'
1441 );
1442 -- **** for debug information in readonly UT environment.--- end ****
1443
1444 END get_unallocated_rep_po;
1445
1446 --========================================================================
1447 -- FUNCTION : get_rep_po_residual_unalloc PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
1448 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
1449 -- unallocated components information
1450 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
1451 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
1452 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
1453 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
1454 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
1455 -- : p_item_id the item_id got from the onhand info from mid temp table
1456 -- COMMENT : find the replenishment purchase order residual unallocated quantity for primary uom
1457 -- PRE-COND :
1458 -- EXCEPTIONS:
1459 --========================================================================
1460 FUNCTION get_rep_po_residual_unalloc
1461 (
1462 p_rep_po_unalloc_row_type IN NUMBER
1463 ,p_ou_id IN NUMBER
1464 ,p_rcv_transaction_id IN NUMBER
1465 ,p_supplier_id IN NUMBER
1466 ,p_supplier_site_id IN NUMBER
1467 ,p_oem_inv_org_id IN NUMBER
1468 ,p_tp_inv_org_id IN NUMBER
1469 ,p_item_id IN NUMBER
1470 ,p_project_id IN NUMBER
1471 ,p_task_id IN NUMBER
1472 ) RETURN NUMBER IS
1473 l_api_name CONSTANT VARCHAR2(30) := 'get_rep_po_residual_unalloc';
1474
1475 l_rep_po_unallocated_pri rcv_transactions.primary_quantity%TYPE;
1476 BEGIN
1477 -- **** for debug information in readonly UT environment.--- begin ****
1478 JMF_SHIKYU_RPT_UTIL.debug_output
1479 (
1480 p_output_to => 'FND_LOG.STRING'
1481 ,p_api_name => G_MODULE_PREFIX || l_api_name
1482 ,p_message => 'begin:' ||
1483 ',p_rep_po_unalloc_row_type:' || p_rep_po_unalloc_row_type ||
1484 ',p_ou_id :' || p_ou_id ||
1485 ',p_rcv_transaction_id' || p_rcv_transaction_id ||
1486 ',p_supplier_id:' || p_supplier_id ||
1487 ',p_supplier_site_id:' || p_supplier_site_id ||
1488 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1489 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1490 ',p_item_id:' || p_item_id ||
1491 ',p_project_id:' || p_project_id ||
1492 ',p_task_id:' || p_task_id
1493 );
1494 -- **** for debug information in readonly UT environment.--- end ****
1495
1496 IF g_fnd_debug = 'Y' AND
1497 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1498 THEN
1499 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1500 , g_module_prefix || l_api_name || '.begin'
1501 , NULL);
1502 END IF;
1503
1504 -- Valid parameter validation;
1505 -- if all parameters are valid then continue; otherwise raise an error message in log;
1506 SELECT cfr_mid.primary_unallocated_quantity
1507 INTO l_rep_po_unallocated_pri
1508 FROM jmf_shikyu_cfr_mid_temp cfr_mid
1509 ,po_line_locations_all poloc
1510 ,rcv_transactions rcv
1511 WHERE rcv.transaction_type = 'RECEIVE'
1512 AND poloc.line_location_id = rcv.po_line_location_id
1513 AND cfr_mid.shikyu_id = poloc.line_location_id
1514 AND rcv.transaction_id = P_rcv_transaction_id
1515 AND cfr_mid.supplier_id = p_supplier_id
1516 AND cfr_mid.site_id = p_supplier_site_id
1517 AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
1518 AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
1519 AND cfr_mid.item_id = p_item_id
1520 AND cfr_mid.row_type = p_rep_po_unalloc_row_type
1521 AND rownum = 1;
1522 COMMIT; -- for debug on UT ?????
1523 --print the data in mid temp table for debug.
1524 rpt_debug_show_mid_data(
1525 p_row_type => p_rep_po_unalloc_row_type
1526 ,p_output_to => 'FND_LOG.STRING'
1527 );
1528
1529 IF g_fnd_debug = 'Y' AND
1530 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1531 THEN
1532 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1533 , g_module_prefix || l_api_name || '.end'
1534 , NULL);
1535 END IF;
1536 RETURN l_rep_po_unallocated_pri;
1537
1538 EXCEPTION
1539 WHEN NO_DATA_FOUND THEN
1540 -- raise log message;
1541 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
1542 fnd_msg_pub.Add;
1543 l_rep_po_unallocated_pri := 0;
1544 RETURN l_rep_po_unallocated_pri;
1545
1546 WHEN OTHERS THEN
1547 IF g_fnd_debug = 'Y' AND
1548 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1549 THEN
1550 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1551 , G_MODULE_PREFIX || l_api_name || '.execption'
1552 , NULL);
1553 END IF;
1554 -- **** for debug information in readonly UT environment.--- begin ****
1555 JMF_SHIKYU_RPT_UTIL.debug_output
1556 (
1557 p_output_to => 'FND_LOG.STRING'
1558 ,p_api_name => G_MODULE_PREFIX || l_api_name
1559 ,p_message => 'Exception: ' || SQLERRM
1560 );
1561 -- **** for debug information in readonly UT environment.--- end ****
1562 l_rep_po_unallocated_pri := 0;
1563 RETURN l_rep_po_unallocated_pri;
1564
1565 END get_rep_po_residual_unalloc;
1566
1567 --========================================================================
1568 -- PROCEDURE : set_rep_po_residual_unalloc PUBLIC ,set_replenishment_po_unallocated quantity for primary uom
1569 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
1570 -- unallocated components information
1571 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
1572 -- : p_rep_po_id row type id to identify the rcv_transaction data
1573 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
1574 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
1575 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
1576 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
1577 -- : p_item_id the item_id got from the onhand info from mid temp table
1578 -- COMMENT : find the replenishment purchase order residual unallocated quantity for primary uom
1579 -- PRE-COND :
1580 -- EXCEPTIONS:
1581 --========================================================================
1582 PROCEDURE set_rep_po_residual_unalloc
1583 (
1584 p_rep_po_unalloc_row_type IN NUMBER
1585 ,p_rcv_transaction_row_type IN NUMBER
1586 ,p_ou_id IN NUMBER
1587 ,p_rcv_transaction_id IN NUMBER
1588 ,p_supplier_id IN NUMBER
1589 ,p_supplier_site_id IN NUMBER
1590 ,p_oem_inv_org_id IN NUMBER
1591 ,p_tp_inv_org_id IN NUMBER
1592 ,p_item_id IN NUMBER
1593 ,p_project_id IN NUMBER
1594 ,p_task_id IN NUMBER
1595 ,p_new_rep_po_unallocated_pri IN NUMBER
1596 ) IS
1597 l_api_name CONSTANT VARCHAR2(30) := 'set_rep_po_residual_unalloc';
1598
1599 BEGIN
1600 -- **** for debug information in readonly UT environment.--- begin ****
1601 JMF_SHIKYU_RPT_UTIL.debug_output
1602 (
1603 p_output_to => 'FND_LOG.STRING'
1604 ,p_api_name => G_MODULE_PREFIX || l_api_name
1605 ,p_message => 'begin:' ||
1606 ',p_rep_po_unalloc_row_type:' || p_rep_po_unalloc_row_type ||
1607 ',p_rcv_transaction_row_type:' || p_rcv_transaction_row_type ||
1608 ',p_ou_id :' || p_ou_id ||
1609 ',p_rcv_transaction_id' || p_rcv_transaction_id ||
1610 ',p_supplier_id:' || p_supplier_id ||
1611 ',p_supplier_site_id:' || p_supplier_site_id ||
1612 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1613 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1614 ',p_item_id:' || p_item_id ||
1615 ',p_new_rep_po_unallocated_pri:' || p_new_rep_po_unallocated_pri ||
1616 ',p_project_id:' || p_project_id ||
1617 ',p_task_id:' || p_task_id
1618 );
1619 -- **** for debug information in readonly UT environment.--- end ****
1620
1621 IF g_fnd_debug = 'Y' AND
1622 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1623 THEN
1624 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1625 , g_module_prefix || l_api_name || '.begin'
1626 , NULL);
1627 END IF;
1628
1629 -- Valid parameter validation;
1630 -- if all parameters are valid then continue; otherwise raise an error message in log;
1631 UPDATE jmf_shikyu_cfr_mid_temp
1632 -- Updated to fix potential issue of operations between null numbers
1633 --SET quantity = quantity -
1634 -- (quantity *
1635 SET quantity = NVL(quantity,0) -
1636 (NVL(quantity,0) *
1637 p_new_rep_po_unallocated_pri /
1638 primary_unallocated_quantity) --residual_unallocated for the UOM
1639 -- Updated to fix potential issue of operations between null numbers
1640 -- ,primary_unallocated_quantity = primary_unallocated_quantity -
1641 ,primary_unallocated_quantity = NVL(primary_unallocated_quantity,0) -
1642 p_new_rep_po_unallocated_pri --residual_unallocated for the primary UOM
1643 WHERE row_type = p_rep_po_unalloc_row_type
1644 AND supplier_id = p_supplier_id
1645 AND site_id = p_supplier_site_id
1646 AND oem_inv_org_id = p_oem_inv_org_id
1647 AND tp_inv_org_id = p_tp_inv_org_id
1648 AND item_id = p_item_id
1649 AND shikyu_id =
1650 (SELECT rcv.po_line_location_id
1651 FROM rcv_transactions rcv
1652 WHERE rcv.transaction_id = p_rcv_transaction_id
1653 AND rcv.transaction_type = 'RECEIVE');
1654
1655 /*
1656 TODO: owner="sunwa" created="2005-11-27"
1657 text="how to deal with if the quantity less then 0"
1658 */
1659 COMMIT; -- for debug on UT ?????
1660 --print the data in mid temp table for debug.
1661 rpt_debug_show_mid_data(
1662 p_row_type => p_rep_po_unalloc_row_type
1663 ,p_output_to => 'FND_LOG.STRING'
1664 );
1665 IF g_fnd_debug = 'Y' AND
1666 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1667 THEN
1668 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1669 , g_module_prefix || l_api_name || '.end'
1670 , NULL);
1671 END IF;
1672 EXCEPTION
1673 WHEN NO_DATA_FOUND THEN
1674 -- raise log message;
1675 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
1676 fnd_msg_pub.Add;
1677
1678 WHEN OTHERS THEN
1679 IF g_fnd_debug = 'Y' AND
1680 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1681 THEN
1682 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1683 , G_MODULE_PREFIX || l_api_name || '.execption'
1684 , NULL);
1685 END IF;
1686 -- **** for debug information in readonly UT environment.--- begin ****
1687 JMF_SHIKYU_RPT_UTIL.debug_output
1688 (
1689 p_output_to => 'FND_LOG.STRING'
1690 ,p_api_name => G_MODULE_PREFIX || l_api_name
1691 ,p_message => 'WHEN OTHERS THEN'
1692 );
1693 -- **** for debug information in readonly UT environment.--- end ****
1694
1695 END set_rep_po_residual_unalloc;
1696
1697 --========================================================================
1698 -- PROCEDURE : set_rcv_transaction_unalloc PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
1699 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
1700 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
1701 -- : p_rcv_unallocated_pri the rcv unallocated quantity for primary uom
1702 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
1703 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
1704 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
1705 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
1706 -- : p_item_id the item_id got from the onhand info from mid temp table
1707 -- COMMENT : update the replenishment po receive transaction unallocated information for primary uom
1708 -- PRE-COND :
1709 -- EXCEPTIONS:
1710 --========================================================================
1711 PROCEDURE set_rcv_transaction_unalloc
1712 (
1713 p_rcv_row_type IN NUMBER
1714 ,p_ou_id IN NUMBER
1715 ,p_rcv_transaction_id IN NUMBER
1716 ,p_rcv_unallocated_pri IN NUMBER
1717 ,p_supplier_id IN NUMBER
1718 ,p_supplier_site_id IN NUMBER
1719 ,p_oem_inv_org_id IN NUMBER
1720 ,p_tp_inv_org_id IN NUMBER
1721 ,p_item_id IN NUMBER
1722 ,p_project_id IN NUMBER
1723 ,p_task_id IN NUMBER
1724 ) IS
1725 l_api_name CONSTANT VARCHAR2(30) := 'set_rcv_transaction_unalloc';
1726
1727 l_jmf_cfr_mid_temp_rcv_rows NUMBER;
1728 BEGIN
1729 -- **** for debug information in readonly UT environment.--- begin ****
1730 JMF_SHIKYU_RPT_UTIL.debug_output
1731 (
1732 p_output_to => 'FND_LOG.STRING'
1733 ,p_api_name => G_MODULE_PREFIX || l_api_name
1734 ,p_message => 'begin:' ||
1735 ',p_rcv_row_type:' || p_rcv_row_type ||
1736 ',p_ou_id :' || p_ou_id ||
1737 ',p_rcv_transaction_id' || p_rcv_transaction_id ||
1738 ',p_supplier_id:' || p_supplier_id ||
1739 ',p_supplier_site_id:' || p_supplier_site_id ||
1740 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1741 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1742 ',p_item_id:' || p_item_id ||
1743 ',p_rcv_unallocated_pri:' || p_rcv_unallocated_pri ||
1744 ',p_project_id:' || p_project_id ||
1745 ',p_task_id:' || p_task_id
1746 );
1747 -- **** for debug information in readonly UT environment.--- end ****
1748
1749 IF g_fnd_debug = 'Y' AND
1750 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1751 THEN
1752 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1753 , g_module_prefix || l_api_name || '.begin'
1754 , NULL);
1755 END IF;
1756
1757 -- Valid parameter validation;
1758 -- if all parameters are valid then continue; otherwise raise an error message in log;
1759 --????
1760 SELECT COUNT(*)
1761 INTO l_jmf_cfr_mid_temp_rcv_rows
1762 FROM jmf_shikyu_cfr_mid_temp
1763 WHERE row_type = p_rcv_row_type
1764 AND shikyu_id = p_rcv_transaction_id;
1765 --find if there is a rcv_transaction line exist
1766 IF l_jmf_cfr_mid_temp_rcv_rows = 1 --Found one row
1767 THEN
1768 --update if exist
1769 UPDATE jmf_shikyu_cfr_mid_temp
1770 -- Updated to fix potential issue of operations between null numbers
1771 -- SET primary_unallocated_quantity = primary_unallocated_quantity +
1772 SET primary_unallocated_quantity = NVL(primary_unallocated_quantity,0) +
1773 p_rcv_unallocated_pri
1774 WHERE row_type = p_rcv_row_type
1775 AND shikyu_id = p_rcv_transaction_id;
1776 ELSE
1777 --NOTFOUND
1778 --add if not exist
1779 INSERT INTO jmf_shikyu_cfr_mid_temp
1780 (row_type
1781 ,shikyu_id
1782 ,primary_unallocated_quantity
1783 ,oem_inv_org_id
1787 ,site_id)
1784 ,tp_inv_org_id
1785 ,item_id
1786 ,supplier_id
1788 VALUES
1789 (p_rcv_row_type
1790 ,p_rcv_transaction_id
1791 ,p_rcv_unallocated_pri
1792 ,p_oem_inv_org_id
1793 ,p_tp_inv_org_id
1794 ,p_item_id
1795 ,p_supplier_id
1796 ,p_supplier_site_id);
1797 END IF;
1798 COMMIT; -- for debug on UT ?????
1799 --print the data in mid temp table for debug.
1800 rpt_debug_show_mid_data(
1801 p_row_type => p_rcv_row_type
1802 ,p_output_to => 'FND_LOG.STRING'
1803 );
1804 IF g_fnd_debug = 'Y' AND
1805 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1806 THEN
1807 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1808 , g_module_prefix || l_api_name || '.end'
1809 , NULL);
1810 END IF;
1811 EXCEPTION
1812 WHEN NO_DATA_FOUND THEN
1813 -- raise log message;
1814 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
1815 fnd_msg_pub.Add;
1816
1817 WHEN OTHERS THEN
1818 IF g_fnd_debug = 'Y' AND
1819 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1820 THEN
1821 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1822 , G_MODULE_PREFIX || l_api_name || '.execption'
1823 , NULL);
1824 END IF;
1825 -- **** for debug information in readonly UT environment.--- begin ****
1826 JMF_SHIKYU_RPT_UTIL.debug_output
1827 (
1828 p_output_to => 'FND_LOG.STRING'
1829 ,p_api_name => G_MODULE_PREFIX || l_api_name
1830 ,p_message => 'WHEN OTHERS THEN'
1831 );
1832 -- **** for debug information in readonly UT environment.--- end ****
1833
1834 END set_rcv_transaction_unalloc;
1835
1836 --========================================================================
1837 -- PROCEDURE : get_unconsumed_components PUBLIC
1838 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
1839 -- unallocated components information
1840 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
1841 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
1842 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
1843 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
1844 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
1845 -- : p_item_id the item_id got from the onhand info from mid temp table
1846 -- : x_need_to_find_pri_qty the need to find quantity under primary UOM
1847 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
1848 -- this procedure is used to get the possilbe allocated but unconsumed rcv_transaction data information
1849 -- for those onhand SHIKYU components based on LIFO received date.
1850 -- PRE-COND :
1851 -- EXCEPTIONS:
1852 --========================================================================
1853 PROCEDURE get_unconsumed_components
1854 (
1855 p_sub_po_unconsumed_row_type IN NUMBER
1856 ,p_rep_po_unconsumed_row_type IN NUMBER
1857 ,p_rcv_transaction_row_type IN NUMBER
1858 ,p_ou_id IN NUMBER
1859 ,p_supplier_id IN NUMBER
1860 ,p_supplier_site_id IN NUMBER
1861 ,p_oem_inv_org_id IN NUMBER
1862 ,p_tp_inv_org_id IN NUMBER
1863 ,p_item_id IN NUMBER
1864 ,p_project_id IN NUMBER
1865 ,p_task_id IN NUMBER
1866 ,x_need_to_find_pri_qty IN OUT NOCOPY NUMBER
1867 ) IS
1868 l_api_name CONSTANT VARCHAR2(30) := 'get_unconsumed_components';
1869
1870 -- get rcv_transaction information, rcv_transaction_id, rcv_unprocessed_qty, from subcontract order, via the replenishment po
1871 /*
1872 TODO: owner="sunwa" created="2005-11-30"
1873 text="how about RepPO received quantity < allocated? can this be happen?"
1874 */
1875 CURSOR l_cur_get_rcv_transaction_info IS
1876 SELECT rt.transaction_id
1877 ,rt.primary_quantity -
1878 NVL((SELECT SUM(NVL(mid_s.primary_unallocated_quantity
1879 ,0) + NVL(mid_s.primary_unconsumed_quantity
1880 ,0))
1881 FROM jmf_shikyu_cfr_mid_temp mid_s
1882 WHERE mid_s.row_type = p_rcv_transaction_row_type
1883 AND mid_s.shikyu_id = rt.transaction_id)
1884 ,0)
1885 FROM jmf_shikyu_allocations alloc
1886 ,jmf_shikyu_replenishments jsr
1887 ,jmf_shikyu_cfr_mid_temp mid
1888 ,rcv_transactions rt
1889 WHERE mid.row_type = p_sub_po_unconsumed_row_type
1890 AND NVL(mid.get_rep_flag
1891 ,'N') <> CFR_REP_PO_GET_RCV_FLAG --if the rep_po line have done get rcv process, the get_rcv_flag will be set to 1
1892 AND mid.shikyu_id = alloc.subcontract_po_shipment_id
1893 AND mid.item_id = alloc.shikyu_component_id
1894 AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
1895 AND jsr.replenishment_po_shipment_id = rt.po_line_location_id
1896 AND rt.transaction_type = 'RECEIVE'
1897 AND jsr.oem_organization_id = p_oem_inv_org_id
1898 AND jsr.tp_organization_id = p_tp_inv_org_id
1899 AND jsr.tp_supplier_id = p_supplier_id
1900 AND jsr.tp_supplier_site_id = p_supplier_site_id
1901 AND jsr.shikyu_component_id = p_item_id
1902 ORDER BY rt.transaction_date;
1903
1904 l_rcv_transaction_id rcv_transactions.transaction_id%TYPE; --for cursor
1905 l_rcv_unprocessed_primary_qty rcv_transactions.primary_quantity%TYPE; --for cursor
1906
1907 l_sub_po_residual_pri rcv_transactions.primary_quantity%TYPE;
1908 l_rcv_unconsumed_pri rcv_transactions.primary_quantity%TYPE;
1909
1910 BEGIN
1911 -- **** for debug information in readonly UT environment.--- begin ****
1912 JMF_SHIKYU_RPT_UTIL.debug_output
1913 (
1914 p_output_to => 'FND_LOG.STRING'
1915 ,p_api_name => G_MODULE_PREFIX || l_api_name
1916 ,p_message => 'begin:' ||
1917 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
1918 ',p_rep_po_unconsumed_row_type:' || p_rep_po_unconsumed_row_type ||
1919 ',p_rcv_transaction_row_type:' || p_rcv_transaction_row_type ||
1920 ',p_ou_id :' || p_ou_id ||
1921 ',p_supplier_id:' || p_supplier_id ||
1922 ',p_supplier_site_id:' || p_supplier_site_id ||
1923 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
1924 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
1925 ',p_item_id:' || p_item_id ||
1926 ',x_need_to_find_pri_qty:' || x_need_to_find_pri_qty ||
1927 ',p_project_id:' || p_project_id ||
1928 ',p_task_id:' || p_task_id
1929 );
1930 -- **** for debug information in readonly UT environment.--- end ****
1931
1932 IF g_fnd_debug = 'Y' AND
1933 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1934 THEN
1935 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
1936 , g_module_prefix || l_api_name || '.begin'
1937 , NULL);
1938 END IF;
1939
1940 -- Valid parameter validation;
1941 -- if all parameters are valid then continue; otherwise raise an error message in log;
1942 --step1:find all the subcontracting PO that not fully received(with oem_inv_org,supplier/site,tp_inv_org,item)
1943 get_unconsumed_sub_po(p_sub_po_unconsumed_row_type => p_sub_po_unconsumed_row_type
1944 ,p_ou_id => p_ou_id
1945 ,p_supplier_id => p_supplier_id
1946 ,p_supplier_site_id => p_supplier_site_id
1947 ,p_oem_inv_org_id => p_oem_inv_org_id
1948 ,p_tp_inv_org_id => p_tp_inv_org_id
1949 ,p_item_id => p_item_id
1950 ,p_project_id => p_project_id
1951 ,p_task_id => p_task_id);
1952 --step2:find all the replenishment po received transactions for the all subcontract PO above, based on LIFO receive date
1953 --
1954 OPEN l_cur_get_rcv_transaction_info;
1955 LOOP
1956 --begin rcv_transaction information
1957 EXIT WHEN x_need_to_find_pri_qty <= 0;
1958
1959 FETCH l_cur_get_rcv_transaction_info
1960 INTO l_rcv_transaction_id, l_rcv_unprocessed_primary_qty;
1961
1962 EXIT WHEN l_cur_get_rcv_transaction_info%NOTFOUND;
1963 -- **** for debug information in readonly UT environment.--- begin ****
1964 JMF_SHIKYU_RPT_UTIL.debug_output
1965 (
1966 p_output_to => 'FND_LOG.STRING'
1967 ,p_api_name => G_MODULE_PREFIX || l_api_name
1968 ,p_message => 'begin:' ||
1969 ',x_need_to_find_pri_qty:' || x_need_to_find_pri_qty ||
1970 ',l_rcv_transaction_id:' || l_rcv_transaction_id ||
1971 ',l_rcv_unprocessed_primary_qty:' || l_rcv_unprocessed_primary_qty
1972 );
1973 -- **** for debug information in readonly UT environment.--- end ****
1974
1975 --step3:find the possible onhand components(for the unconsumed )
1976 l_sub_po_residual_pri := get_sub_po_residual_unconsume(p_sub_po_unconsumed_row_type => p_sub_po_unconsumed_row_type
1977 ,p_ou_id => p_ou_id
1978 ,p_rcv_transaction_id => l_rcv_transaction_id
1979 ,p_supplier_id => p_supplier_id
1980 ,p_supplier_site_id => p_supplier_site_id
1981 ,p_oem_inv_org_id => p_oem_inv_org_id
1982 ,p_tp_inv_org_id => p_tp_inv_org_id
1983 ,p_item_id => p_item_id
1984 ,p_project_id => p_project_id
1985 ,p_task_id => p_task_id); --the quantity for primary uom
1986
1987 IF l_sub_po_residual_pri > 0
1988 THEN
1989 --the possilbe unconsumed rcv_transactions primary quantity
1990 l_rcv_unconsumed_pri := jmf_shikyu_rpt_util.get_min3(p_number1 => l_rcv_unprocessed_primary_qty
1991 ,p_number2 => l_sub_po_residual_pri
1992 ,p_number3 => x_need_to_find_pri_qty);
1993
1994 --insert data to rcv_temp marking the processed rcv_transaction,
1995 set_rcv_transaction_unconsume(p_rcv_row_type => p_rcv_transaction_row_type
1996 ,p_ou_id => p_ou_id
1997 ,p_rcv_transaction_id => l_rcv_transaction_id
1998 ,p_rcv_unconsumed_pri => l_rcv_unconsumed_pri
1999 ,p_supplier_id => p_supplier_id
2000 ,p_supplier_site_id => p_supplier_site_id
2001 ,p_oem_inv_org_id => p_oem_inv_org_id
2002 ,p_tp_inv_org_id => p_tp_inv_org_id
2003 ,p_item_id => p_item_id
2004 ,p_project_id => p_project_id
2005 ,p_task_id => p_task_id);
2006
2007 --the residual unconsumed quantity for primary quantity
2008 --update replenishment po residual unconsumed quantity information
2009 set_sub_po_residual_unconsume(p_sub_po_unconsumed_row_type => p_sub_po_unconsumed_row_type
2010 ,p_rcv_transaction_row_type => p_rcv_transaction_row_type
2011 ,p_ou_id => p_ou_id
2012 ,p_rcv_transaction_id => l_rcv_transaction_id
2013 ,p_supplier_id => p_supplier_id
2014 ,p_supplier_site_id => p_supplier_site_id
2015 ,p_oem_inv_org_id => p_oem_inv_org_id
2016 ,p_tp_inv_org_id => p_tp_inv_org_id
2017 ,p_item_id => p_item_id
2018 ,p_project_id => p_project_id
2019 ,p_task_id => p_task_id
2020 ,p_new_consumed_pri => l_rcv_unconsumed_pri);
2021 --update the need to find primary quantity
2022 x_need_to_find_pri_qty := x_need_to_find_pri_qty - l_rcv_unconsumed_pri;
2023
2024 END IF;
2025
2026 END LOOP; -- end of find the rcv_transaction information
2027 CLOSE l_cur_get_rcv_transaction_info;
2028
2029 --step4:update the get_rcv_flags for the rep_po lines
2030 COMMIT; -- for debug on UT ?????
2031 IF g_fnd_debug = 'Y' AND
2032 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2033 THEN
2034 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2035 , g_module_prefix || l_api_name || '.end'
2036 , NULL);
2037 END IF;
2038 EXCEPTION
2039 WHEN NO_DATA_FOUND THEN
2040 -- raise log message;
2041 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2042 fnd_msg_pub.Add;
2043
2044 WHEN OTHERS THEN
2045 IF g_fnd_debug = 'Y' AND
2046 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2047 THEN
2048 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2049 , G_MODULE_PREFIX || l_api_name || '.execption'
2050 , NULL);
2051 END IF;
2052 -- **** for debug information in readonly UT environment.--- begin ****
2053 JMF_SHIKYU_RPT_UTIL.debug_output
2054 (
2055 p_output_to => 'FND_LOG.STRING'
2056 ,p_api_name => G_MODULE_PREFIX || l_api_name
2057 ,p_message => 'WHEN OTHERS THEN'
2061 END get_unconsumed_components;
2058 );
2059 -- **** for debug information in readonly UT environment.--- end ****
2060
2062
2063 --========================================================================
2064 -- PROCEDURE : get_unconsumed_sub_po PUBLIC ,unconsumed_subcontracting_po
2065 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
2066 -- unallocated components information
2067 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
2068 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
2069 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
2070 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
2071 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
2072 -- : p_item_id the item_id got from the onhand info from mid temp table
2073 -- COMMENT : find the subcontracting purchase order that not fully received
2074 -- PRE-COND :
2075 -- EXCEPTIONS:
2076 --========================================================================
2077 PROCEDURE get_unconsumed_sub_po
2078 (
2079 p_sub_po_unconsumed_row_type IN NUMBER
2080 ,p_ou_id IN NUMBER
2081 ,p_supplier_id IN NUMBER
2082 ,p_supplier_site_id IN NUMBER
2083 ,p_oem_inv_org_id IN NUMBER
2084 ,p_tp_inv_org_id IN NUMBER
2085 ,p_item_id IN NUMBER
2086 ,p_project_id IN NUMBER
2087 ,p_task_id IN NUMBER
2088 ) IS
2089 l_api_name CONSTANT VARCHAR2(30) := 'get_unconsumed_sub_po';
2090
2091 BEGIN
2092 -- **** for debug information in readonly UT environment.--- begin ****
2093 JMF_SHIKYU_RPT_UTIL.debug_output
2094 (
2095 p_output_to => 'FND_LOG.STRING'
2096 ,p_api_name => G_MODULE_PREFIX || l_api_name
2097 ,p_message => 'begin:' ||
2098 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
2099 ',p_ou_id :' || p_ou_id ||
2100 ',p_supplier_id:' || p_supplier_id ||
2101 ',p_supplier_site_id:' || p_supplier_site_id ||
2102 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
2103 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
2104 ',p_item_id:' || p_item_id ||
2105 ',p_project_id:' || p_project_id ||
2106 ',p_task_id:' || p_task_id
2107 );
2108 -- **** for debug information in readonly UT environment.--- end ****
2109
2110 IF g_fnd_debug = 'Y' AND
2111 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2112 THEN
2113 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2114 , g_module_prefix || l_api_name || '.begin'
2115 , NULL);
2116 END IF;
2117
2118 -- Valid parameter validation;
2119 -- if all parameters are valid then continue; otherwise raise an error message in log;
2120 --the subcontract orders that not fully received
2121
2122 -- insert only the new SubPO info
2123 INSERT INTO jmf_shikyu_cfr_mid_temp
2124 (row_type
2125 ,shikyu_id
2126 ,uom
2127 ,primary_uom
2128 ,primary_unconsumed_quantity
2129 ,supplier_id
2130 ,site_id
2131 ,oem_inv_org_id
2132 ,tp_inv_org_id
2133 ,item_id)
2134 SELECT p_sub_po_unconsumed_row_type
2135 ,sub_po.subcontract_po_shipment_id
2136 ,comp.uom
2137 ,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(sub_po.tp_organization_id
2138 ,comp.shikyu_component_id)
2139 ,((SELECT NVL(SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(sub_po.tp_organization_id
2140 ,jsa_a.shikyu_component_id
2141 ,jsa_a.uom
2142 ,NVL(jsa_a.allocated_quantity
2143 ,0)))
2144 ,0)
2145 FROM jmf_shikyu_allocations jsa_a
2146 WHERE jsa_a.subcontract_po_shipment_id =
2147 comp.subcontract_po_shipment_id
2148 AND jsa_a.shikyu_component_id = comp.shikyu_component_id) -
2149 wip_req.quantity_issued
2150 ) primary_possible_unconsumed
2151 ,ph.vendor_id
2152 ,ph.vendor_site_id
2153 ,sub_po.oem_organization_id
2154 ,sub_po.tp_organization_id
2155 ,comp.shikyu_component_id
2156 FROM po_line_locations_all pll
2157 ,jmf_subcontract_orders sub_po
2158 ,po_headers_all ph
2159 ,jmf_shikyu_components comp
2160 ,wip_requirement_operations wip_req
2161 WHERE pll.line_location_id = sub_po.subcontract_po_shipment_id
2162 AND pll.quantity > pll.quantity_received --this can be ignore if allow the allocated qty larger than ordered qty
2163 AND sub_po.oem_organization_id = p_oem_inv_org_id
2164 AND sub_po.tp_organization_id = p_tp_inv_org_id
2165 AND pll.po_header_id = ph.po_header_id
2166 AND ((ph.org_id IS NULL) OR (ph.org_id = p_ou_id))
2167 AND ph.vendor_id = p_supplier_id
2168 AND ph.vendor_site_id = p_supplier_site_id
2169 AND sub_po.wip_entity_id = wip_req.wip_entity_id
2170 AND sub_po.subcontract_po_shipment_id =
2171 comp.subcontract_po_shipment_id
2172 AND comp.shikyu_component_id = wip_req.inventory_item_id
2173 AND comp.shikyu_component_id = p_item_id
2174 AND sub_po.tp_organization_id = wip_req.organization_id
2175 AND wip_req.repetitive_schedule_id IS NULL
2176 AND wip_req.operation_seq_num = 1
2177 --This may cause issue when same components are found in different tp organization,the onhand component are found
2178 -- secondly,will be lost in them mid_temp table.That means in the report will lose some datas which should be displayed.
2179 -- updated to fix this potensial issue.
2180 /* AND NOT ((comp.subcontract_po_shipment_id IN
2181 (SELECT jscmt_s.shikyu_id
2182 FROM jmf_shikyu_cfr_mid_temp jscmt_s
2183 WHERE jscmt_s.row_type = p_sub_po_unconsumed_row_type)) AND
2184 (comp.shikyu_component_id IN
2185 (SELECT jscmt_i.item_id
2186 FROM jmf_shikyu_cfr_mid_temp jscmt_i
2187 WHERE jscmt_i.row_type = p_sub_po_unconsumed_row_type)))*/
2188 AND NOT (comp.subcontract_po_shipment_id IN
2189 (SELECT jscmt_s.shikyu_id
2190 FROM jmf_shikyu_cfr_mid_temp jscmt_s
2191 WHERE jscmt_s.row_type = p_sub_po_unconsumed_row_type
2192 AND jscmt_s.item_id = p_item_id))
2193 ;
2194
2195 --update the primary UOM, primary_residual_unallocated
2196 UPDATE jmf_shikyu_cfr_mid_temp
2197 SET quantity = NVL(primary_unconsumed_quantity,0) *
2198 JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
2199 ,uom
2200 ,item_id)
2201 WHERE row_type = p_sub_po_unconsumed_row_type
2202 AND quantity IS NULL --only for those do not get the quantity of uom
2203 AND supplier_id = p_supplier_id
2204 AND site_id = p_supplier_site_id
2205 AND oem_inv_org_id = p_oem_inv_org_id
2206 AND tp_inv_org_id = p_tp_inv_org_id
2207 AND item_id = p_item_id;
2208 COMMIT; -- for debug on UT ?????
2209 --print the data in mid temp table for debug.
2210 rpt_debug_show_mid_data(
2211 p_row_type => p_sub_po_unconsumed_row_type
2212 ,p_output_to => 'FND_LOG.STRING'
2213 );
2214 IF g_fnd_debug = 'Y' AND
2215 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2216 THEN
2217 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2218 , g_module_prefix || l_api_name || '.end'
2219 , NULL);
2220 END IF;
2221 EXCEPTION
2222 WHEN NO_DATA_FOUND THEN
2223 -- raise log message;
2224 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2225 fnd_msg_pub.Add;
2226
2227 WHEN OTHERS THEN
2228 IF g_fnd_debug = 'Y' AND
2229 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2230 THEN
2231 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2232 , G_MODULE_PREFIX || l_api_name || '.execption'
2233 , NULL);
2234 END IF;
2235 -- **** for debug information in readonly UT environment.--- begin ****
2236 JMF_SHIKYU_RPT_UTIL.debug_output
2237 (
2238 p_output_to => 'FND_LOG.STRING'
2242 -- **** for debug information in readonly UT environment.--- end ****
2239 ,p_api_name => G_MODULE_PREFIX || l_api_name
2240 ,p_message => 'WHEN OTHERS THEN'
2241 );
2243
2244 END get_unconsumed_sub_po;
2245
2246 --========================================================================
2247 -- PROCEDURE : get_unconsumed_rep_po PUBLIC ,get_unconsumed_replenishment_po
2248 -- PARAMETERS: p_rep_po_unconsume_row_type row type id to identify the
2249 -- unconsumed components information
2250 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
2251 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
2252 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
2253 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
2254 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
2255 -- : p_item_id the item_id got from the onhand info from mid temp table
2256 -- COMMENT : find the replenishment purchase order that have unallocated receipts for the item
2257 -- and insert the result to mid temp table
2258 -- PRE-COND :
2259 -- EXCEPTIONS:
2260 --========================================================================
2261 PROCEDURE get_unconsumed_rep_po
2262 (
2263 p_sub_po_unconsumed_row_type IN NUMBER
2264 ,p_rep_po_unconsumed_row_type IN NUMBER
2265 ,p_ou_id IN NUMBER
2266 ,p_supplier_id IN NUMBER
2267 ,p_supplier_site_id IN NUMBER
2268 ,p_oem_inv_org_id IN NUMBER
2269 ,p_tp_inv_org_id IN NUMBER
2270 ,p_item_id IN NUMBER
2271 ,p_project_id IN NUMBER
2272 ,p_task_id IN NUMBER
2273 ) IS
2274 l_api_name CONSTANT VARCHAR2(30) := 'get_unconsumed_rep_po';
2275
2276 BEGIN
2277 -- **** for debug information in readonly UT environment.--- begin ****
2278 JMF_SHIKYU_RPT_UTIL.debug_output
2279 (
2280 p_output_to => 'FND_LOG.STRING'
2281 ,p_api_name => G_MODULE_PREFIX || l_api_name
2282 ,p_message => 'begin:' ||
2283 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
2284 ',p_rep_po_unconsumed_row_type:' || p_rep_po_unconsumed_row_type ||
2285 ',p_ou_id :' || p_ou_id ||
2286 ',p_supplier_id:' || p_supplier_id ||
2287 ',p_supplier_site_id:' || p_supplier_site_id ||
2288 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
2289 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
2290 ',p_item_id:' || p_item_id ||
2291 ',p_project_id:' || p_project_id ||
2292 ',p_task_id:' || p_task_id
2293 );
2294 -- **** for debug information in readonly UT environment.--- end ****
2295
2296 IF g_fnd_debug = 'Y' AND
2297 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2298 THEN
2299 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2300 , g_module_prefix || l_api_name || '.begin'
2301 , NULL);
2302 END IF;
2303
2304 -- Valid parameter validation;
2305 -- if all parameters are valid then continue; otherwise raise an error message in log;
2306
2307 -- the allocated quantity for one po line_location_id, assume that the UOM are the same in po and shikyu allocation
2308 -- need to add the supplier/site oem tp org and item restriction????.
2309 -- update 2005-11-30
2310 INSERT INTO jmf_shikyu_cfr_mid_temp
2311 (row_type
2312 ,shikyu_id
2313 ,uom
2314 ,primary_uom
2315 ,primary_unconsumed_quantity
2316 ,supplier_id
2317 ,site_id
2318 ,oem_inv_org_id
2319 ,tp_inv_org_id
2320 ,item_id)
2321 SELECT p_rep_po_unconsumed_row_type
2322 ,jsr.replenishment_po_shipment_id
2323 ,alloc.uom
2324 ,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(jsr.oem_organization_id
2325 ,alloc.shikyu_component_id)
2326 ,JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.oem_organization_id
2327 ,alloc.shikyu_component_id
2328 ,alloc.uom
2329 ,alloc.allocated_quantity) -
2330 NVL(wro.quantity_issued
2331 ,0)
2332 ,jsr.tp_supplier_id
2333 ,jsr.tp_supplier_site_id
2334 ,jsr.oem_organization_id
2335 ,jsr.tp_organization_id
2336 ,alloc.shikyu_component_id
2337 FROM jmf_shikyu_allocations alloc
2338 ,jmf_shikyu_replenishments jsr
2339 ,jmf_shikyu_cfr_mid_temp mid
2340 ,jmf_subcontract_orders jso
2341 ,wip_requirement_operations wro
2342 WHERE mid.row_type = p_sub_po_unconsumed_row_type
2343 AND NVL(mid.get_rep_flag
2344 ,'N') <> CFR_SUB_PO_GET_REP_FLAG
2345 AND mid.shikyu_id = alloc.subcontract_po_shipment_id
2346 AND mid.item_id = alloc.shikyu_component_id
2347 AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
2348 AND jsr.oem_organization_id = p_oem_inv_org_id
2349 AND jsr.tp_organization_id = p_tp_inv_org_id
2350 AND jsr.tp_supplier_id = p_supplier_id
2351 AND jsr.tp_supplier_site_id = p_supplier_site_id
2352 AND alloc.shikyu_component_id = p_item_id
2353 AND alloc.subcontract_po_shipment_id =
2354 jso.subcontract_po_shipment_id
2355 AND jso.wip_entity_id = wro.wip_entity_id(+)
2356 AND jso.tp_organization_id = wro.organization_id(+)
2357 AND ((wro.operation_seq_num IS NULL) OR
2358 (wro.operation_seq_num = 1))
2359 AND wro.repetitive_schedule_id IS NULL
2360 AND alloc.shikyu_component_id = wro.inventory_item_id
2361 --This may cause issue when same components are found in different tp organization,the onhand component are found
2362 -- secondly,will be lost in them mid_temp table.That means in the report will lose some datas which should be displayed.
2363 -- updated to fix this potensial issue.
2364 /* AND NOT
2365 ((jsr.replenishment_po_shipment_id IN
2366 (SELECT jscmt_s.shikyu_id
2367 FROM jmf_shikyu_cfr_mid_temp jscmt_s
2368 WHERE jscmt_s.row_type = p_rep_po_unconsumed_row_type)) AND
2369 (alloc.shikyu_component_id IN
2370 (SELECT jscmt_i.item_id
2371 FROM jmf_shikyu_cfr_mid_temp jscmt_i
2372 WHERE jscmt_i.row_type = p_rep_po_unconsumed_row_type)));
2373 */ AND NOT (jsr.replenishment_po_shipment_id IN
2374 (SELECT jscmt_s.shikyu_id
2375 FROM jmf_shikyu_cfr_mid_temp jscmt_s
2376 WHERE jscmt_s.row_type = p_rep_po_unconsumed_row_type
2377 AND jscmt_s.item_id = p_item_id));
2378
2379 --update the primary UOM, primary_residual_unallocated
2380 UPDATE jmf_shikyu_cfr_mid_temp
2381 SET quantity = NVL(primary_unconsumed_quantity,0) *
2382 JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
2383 ,uom
2384 ,item_id)
2385 WHERE row_type = p_rep_po_unconsumed_row_type
2386 AND quantity IS NULL --only for those do not get the quantity of uom
2387 AND supplier_id = p_supplier_id
2388 AND site_id = p_supplier_site_id
2389 AND oem_inv_org_id = p_oem_inv_org_id
2390 AND tp_inv_org_id = p_tp_inv_org_id
2391 AND item_id = p_item_id;
2392
2393 COMMIT; -- for debug on UT ?????
2397 ,p_output_to => 'FND_LOG.STRING'
2394 --print the data in mid temp table for debug.
2395 rpt_debug_show_mid_data(
2396 p_row_type => p_rep_po_unconsumed_row_type
2398 );
2399 IF g_fnd_debug = 'Y' AND
2400 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2401 THEN
2402 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2403 , g_module_prefix || l_api_name || '.end'
2404 , NULL);
2405 END IF;
2406 EXCEPTION
2407 WHEN NO_DATA_FOUND THEN
2408 -- raise log message;
2409 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2410 fnd_msg_pub.Add;
2411
2412 WHEN OTHERS THEN
2413 IF g_fnd_debug = 'Y' AND
2414 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2415 THEN
2416 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2417 , G_MODULE_PREFIX || l_api_name || '.execption'
2418 , NULL);
2419 END IF;
2420 -- **** for debug information in readonly UT environment.--- begin ****
2421 JMF_SHIKYU_RPT_UTIL.debug_output
2422 (
2423 p_output_to => 'FND_LOG.STRING'
2424 ,p_api_name => G_MODULE_PREFIX || l_api_name
2425 ,p_message => 'WHEN OTHERS THEN'
2426 );
2427 -- **** for debug information in readonly UT environment.--- end ****
2428
2429 END get_unconsumed_rep_po;
2430
2431 --========================================================================
2432 -- FUNCTION : get_sub_po_residual_unconsume PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
2433 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
2434 -- unallocated components information
2435 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
2436 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
2437 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
2438 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
2439 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
2440 -- : p_item_id the item_id got from the onhand info from mid temp table
2441 -- COMMENT : find the subcontract order residual unconsumed quantity for primary uom
2442 -- PRE-COND :
2443 -- EXCEPTIONS:
2444 --========================================================================
2445 FUNCTION get_sub_po_residual_unconsume
2446 (
2447 p_sub_po_unconsumed_row_type IN NUMBER
2448 ,p_ou_id IN NUMBER
2449 ,p_rcv_transaction_id IN NUMBER
2450 ,p_supplier_id IN NUMBER
2451 ,p_supplier_site_id IN NUMBER
2452 ,p_oem_inv_org_id IN NUMBER
2453 ,p_tp_inv_org_id IN NUMBER
2454 ,p_item_id IN NUMBER
2455 ,p_project_id IN NUMBER
2456 ,p_task_id IN NUMBER
2457 ) RETURN NUMBER IS
2458 l_api_name CONSTANT VARCHAR2(30) := 'get_sub_po_residual_unconsume';
2459
2460 l_sub_po_residual_pri rcv_transactions.primary_quantity%TYPE;
2461 BEGIN
2462 -- **** for debug information in readonly UT environment.--- begin ****
2463 JMF_SHIKYU_RPT_UTIL.debug_output
2464 (
2465 p_output_to => 'FND_LOG.STRING'
2466 ,p_api_name => G_MODULE_PREFIX || l_api_name
2467 ,p_message => 'begin:' ||
2468 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
2469 ',p_rcv_transaction_id:' || p_rcv_transaction_id ||
2470 ',p_ou_id :' || p_ou_id ||
2471 ',p_supplier_id:' || p_supplier_id ||
2472 ',p_supplier_site_id:' || p_supplier_site_id ||
2473 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
2474 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
2475 ',p_item_id:' || p_item_id ||
2476 ',p_project_id:' || p_project_id ||
2477 ',p_task_id:' || p_task_id
2478 );
2479 -- **** for debug information in readonly UT environment.--- end ****
2480
2481 IF g_fnd_debug = 'Y' AND
2482 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2483 THEN
2484 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2485 , g_module_prefix || l_api_name || '.begin'
2486 , NULL);
2487 END IF;
2488
2489 -- Valid parameter validation;
2490 -- if all parameters are valid then continue; otherwise raise an error message in log;
2491 SELECT SUM(NVL(cfr_mid.primary_unconsumed_quantity
2492 ,0))
2493 INTO l_sub_po_residual_pri
2494 FROM jmf_shikyu_cfr_mid_temp cfr_mid
2495 ,rcv_transactions rcv
2496 ,jmf_shikyu_allocations alloc
2497 ,jmf_shikyu_replenishments jsr
2498 WHERE rcv.transaction_id = P_rcv_transaction_id
2499 AND rcv.transaction_type = 'RECEIVE'
2500 AND cfr_mid.row_type = p_sub_po_unconsumed_row_type
2501 AND cfr_mid.supplier_id = p_supplier_id
2502 AND cfr_mid.site_id = p_supplier_site_id
2503 AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
2504 AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
2505 AND cfr_mid.item_id = p_item_id
2506 AND cfr_mid.shikyu_id = alloc.subcontract_po_shipment_id
2507 AND cfr_mid.item_id = alloc.shikyu_component_id
2508 AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
2509 AND jsr.replenishment_po_shipment_id = rcv.po_line_location_id;
2510 IF g_fnd_debug = 'Y' AND
2511 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2512 THEN
2513 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2514 , g_module_prefix || l_api_name || '.end'
2515 , NULL);
2516 END IF;
2517 RETURN l_sub_po_residual_pri;
2518 EXCEPTION
2519 WHEN NO_DATA_FOUND THEN
2520 -- raise log message;
2521 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2522 fnd_msg_pub.Add;
2523 RETURN 0;
2524
2525 WHEN OTHERS THEN
2526 IF g_fnd_debug = 'Y' AND
2527 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2528 THEN
2529 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2530 , G_MODULE_PREFIX || l_api_name || '.execption'
2531 , NULL);
2532 END IF;
2533 RETURN -1;
2534 -- **** for debug information in readonly UT environment.--- begin ****
2535 JMF_SHIKYU_RPT_UTIL.debug_output
2536 (
2537 p_output_to => 'FND_LOG.STRING'
2538 ,p_api_name => G_MODULE_PREFIX || l_api_name
2539 ,p_message => 'WHEN OTHERS THEN'
2540 );
2541 -- **** for debug information in readonly UT environment.--- end ****
2542
2543 END get_sub_po_residual_unconsume;
2544
2545 --========================================================================
2546 -- PROCEDURE : set_sub_po_residual_unconsume PUBLIC ,set_replenishment_po_unallocated quantity for primary uom
2547 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
2548 -- unconsumed components information
2549 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
2550 -- : p_rep_po_id row type id to identify the rcv_transaction data
2551 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
2552 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
2553 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
2554 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
2555 -- : p_item_id the item_id got from the onhand info from mid temp table
2556 -- : p_new_consumed_pri the new consumed primary quantity that need to update in the temp table
2557 -- COMMENT : find the subcontract order residual unaconsumed quantity for primary uom
2558 -- PRE-COND :
2559 -- EXCEPTIONS:
2560 --========================================================================
2561 PROCEDURE set_sub_po_residual_unconsume
2562 (
2563 p_sub_po_unconsumed_row_type IN NUMBER
2564 ,p_rcv_transaction_row_type IN NUMBER
2565 ,p_ou_id IN NUMBER
2566 ,p_rcv_transaction_id IN NUMBER
2567 ,p_supplier_id IN NUMBER
2568 ,p_supplier_site_id IN NUMBER
2569 ,p_oem_inv_org_id IN NUMBER
2570 ,p_tp_inv_org_id IN NUMBER
2571 ,p_item_id IN NUMBER
2572 ,p_project_id IN NUMBER
2573 ,p_task_id IN NUMBER
2574 ,p_new_consumed_pri IN NUMBER
2575 ) IS
2576 l_api_name CONSTANT VARCHAR2(30) := 'set_sub_po_residual_unconsume';
2577
2578 CURSOR lcur_sub_po_temp_info IS
2579 SELECT cfr_mid.shikyu_id
2580 ,cfr_mid.primary_unconsumed_quantity
2581 FROM jmf_shikyu_cfr_mid_temp cfr_mid
2582 ,rcv_transactions rcv
2583 ,jmf_shikyu_allocations alloc
2584 ,jmf_shikyu_replenishments jsr
2585 WHERE rcv.transaction_id = p_rcv_transaction_id
2586 AND rcv.transaction_type = 'RECEIVE'
2587 AND cfr_mid.row_type = p_sub_po_unconsumed_row_type
2588 AND cfr_mid.supplier_id = p_supplier_id
2589 AND cfr_mid.site_id = p_supplier_site_id
2590 AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
2591 AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
2592 AND cfr_mid.item_id = p_item_id
2593 AND cfr_mid.shikyu_id = alloc.subcontract_po_shipment_id
2594 AND cfr_mid.item_id = alloc.shikyu_component_id
2595 AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
2596 AND jsr.replenishment_po_shipment_id = rcv.po_line_location_id
2597 AND cfr_mid.primary_unconsumed_quantity > 0
2598 ORDER BY cfr_mid.shikyu_id DESC;
2599
2600 l_sub_po_id jmf_shikyu_cfr_mid_temp.shikyu_id%TYPE;
2601 l_residual_old_consumed_pri jmf_shikyu_cfr_mid_temp.primary_unconsumed_quantity%TYPE;
2602 l_residual_new_consumed_pri jmf_shikyu_cfr_mid_temp.primary_unconsumed_quantity%TYPE;
2603 l_cur_sub_po_consumed_pri jmf_shikyu_cfr_mid_temp.primary_unconsumed_quantity%TYPE;
2604 BEGIN
2605 -- **** for debug information in readonly UT environment.--- begin ****
2606 JMF_SHIKYU_RPT_UTIL.debug_output
2607 (
2608 p_output_to => 'FND_LOG.STRING'
2609 ,p_api_name => G_MODULE_PREFIX || l_api_name
2610 ,p_message => 'begin:' ||
2611 ',p_sub_po_unconsumed_row_type:' || p_sub_po_unconsumed_row_type ||
2612 ',p_rcv_transaction_row_type:' || p_rcv_transaction_row_type ||
2613 ',p_rcv_transaction_id:' || p_rcv_transaction_id ||
2614 ',p_ou_id :' || p_ou_id ||
2615 ',p_supplier_id:' || p_supplier_id ||
2616 ',p_supplier_site_id:' || p_supplier_site_id ||
2617 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
2618 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
2619 ',p_item_id:' || p_item_id ||
2620 ',p_new_consumed_pri:' || p_new_consumed_pri ||
2621 ',p_project_id:' || p_project_id ||
2622 ',p_task_id:' || p_task_id
2623 );
2624 -- **** for debug information in readonly UT environment.--- end ****
2625
2626 IF g_fnd_debug = 'Y' AND
2627 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2628 THEN
2629 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2630 , g_module_prefix || l_api_name || '.begin'
2631 , NULL);
2632 END IF;
2633
2634 -- Valid parameter validation;
2635 -- if all parameters are valid then continue; otherwise raise an error message in log;
2636 --????
2637 l_residual_new_consumed_pri := p_new_consumed_pri;
2638 OPEN lcur_sub_po_temp_info;
2639 LOOP
2640 --begin process updating the unconsumed qty for the found subcontract order in the mid temp table
2641 EXIT WHEN l_residual_new_consumed_pri <= 0;
2642
2643 FETCH lcur_sub_po_temp_info
2644 INTO l_sub_po_id, l_residual_old_consumed_pri;
2645
2646 EXIT WHEN lcur_sub_po_temp_info%NOTFOUND;
2647
2648 -- **** for debug information in readonly UT environment.--- begin ****
2649 JMF_SHIKYU_RPT_UTIL.debug_output
2650 (
2651 p_output_to => 'FND_LOG.STRING'
2652 ,p_api_name => G_MODULE_PREFIX || l_api_name
2653 ,p_message => 'begin:' ||
2654 ',l_residual_new_consumed_pri:' || l_residual_new_consumed_pri ||
2655 ',l_sub_po_id:' || l_sub_po_id ||
2656 ',l_residual_old_consumed_pri:' || l_residual_old_consumed_pri
2657 );
2658 -- **** for debug information in readonly UT environment.--- end ****
2659
2660 l_cur_sub_po_consumed_pri := jmf_shikyu_rpt_util.get_min2(l_residual_old_consumed_pri
2661 ,l_residual_new_consumed_pri);
2662 UPDATE jmf_shikyu_cfr_mid_temp
2663 -- Updated to fix potential issue of operations between null numbers
2664 -- SET quantity = quantity -
2665 -- (quantity * l_cur_sub_po_consumed_pri /
2666 SET quantity = NVL(quantity,0) -
2667 ( NVL(quantity,0) * l_cur_sub_po_consumed_pri /
2668 primary_unconsumed_quantity) --possible unconsumed for UOM
2669 ,primary_unconsumed_quantity = NVL(primary_unconsumed_quantity,0) -
2670 l_cur_sub_po_consumed_pri --possible unconsumed for primary UOM
2671 WHERE row_type = p_sub_po_unconsumed_row_type
2672 AND shikyu_id = l_sub_po_id
2673 AND oem_inv_org_id = p_oem_inv_org_id
2674 AND tp_inv_org_id = p_tp_inv_org_id
2675 AND item_id = p_item_id;
2676
2677 l_residual_new_consumed_pri := l_residual_new_consumed_pri -
2678 l_cur_sub_po_consumed_pri;
2679
2680 END LOOP; --end process updating the unconsumed qty for the found subcontract order in the mid temp table
2681 CLOSE lcur_sub_po_temp_info;
2682
2683 /*
2684 TODO: owner="sunwa" created="2005-11-30"
2685 text="how about if the l_residual_new_consumed_pri still lager then 0 "
2686 */
2687 -- if l_residual_new_consumed_pri > 0 then ---
2688 COMMIT; -- for debug on UT ?????
2689 rpt_debug_show_mid_data(
2690 p_row_type => p_sub_po_unconsumed_row_type
2691 ,p_output_to => 'FND_LOG.STRING'
2692 );
2693 IF g_fnd_debug = 'Y' AND
2694 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2695 THEN
2696 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2697 , g_module_prefix || l_api_name || '.end'
2698 , NULL);
2699 END IF;
2700 EXCEPTION
2701 WHEN NO_DATA_FOUND THEN
2702 -- raise log message;
2703 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2704 fnd_msg_pub.Add;
2705
2706 WHEN OTHERS THEN
2707 IF g_fnd_debug = 'Y' AND
2708 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2709 THEN
2710 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2711 , G_MODULE_PREFIX || l_api_name || '.execption'
2712 , NULL);
2713 END IF;
2714 -- **** for debug information in readonly UT environment.--- begin ****
2715 JMF_SHIKYU_RPT_UTIL.debug_output
2716 (
2717 p_output_to => 'FND_LOG.STRING'
2718 ,p_api_name => G_MODULE_PREFIX || l_api_name
2719 ,p_message => 'WHEN OTHERS THEN'
2720 );
2721 -- **** for debug information in readonly UT environment.--- end ****
2722
2723 END set_sub_po_residual_unconsume;
2724
2725 --========================================================================
2726 -- PROCEDURE : set_rcv_transaction_unconsume PUBLIC ,get_replenishment_po_unconsumed quantity for primary uom
2727 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
2728 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
2729 -- : p_rcv_unallocated_pri the rcv unallocated quantity for primary uom
2730 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
2731 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
2732 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
2733 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
2734 -- : p_item_id the item_id got from the onhand info from mid temp table
2735 -- COMMENT : update the replenishment po receive transaction unconsumed information for primary uom
2736 -- old_unconsumed = old_unconsumed + p_rcv_unconsumed_pri
2737 -- PRE-COND :
2738 -- EXCEPTIONS:
2739 --========================================================================
2740 PROCEDURE set_rcv_transaction_unconsume
2741 (
2742 p_rcv_row_type IN NUMBER
2743 ,p_ou_id IN NUMBER
2744 ,p_rcv_transaction_id IN NUMBER
2745 ,p_rcv_unconsumed_pri IN NUMBER
2746 ,p_supplier_id IN NUMBER
2747 ,p_supplier_site_id IN NUMBER
2748 ,p_oem_inv_org_id IN NUMBER
2749 ,p_tp_inv_org_id IN NUMBER
2750 ,p_item_id IN NUMBER
2751 ,p_project_id IN NUMBER
2752 ,p_task_id IN NUMBER
2753 ) IS
2754 l_api_name CONSTANT VARCHAR2(30) := 'set_rcv_transaction_unconsume';
2755
2756 l_jmf_cfr_mid_temp_rcv_rows NUMBER;
2757 BEGIN
2758 -- **** for debug information in readonly UT environment.--- begin ****
2759 JMF_SHIKYU_RPT_UTIL.debug_output
2760 (
2761 p_output_to => 'FND_LOG.STRING'
2765 ',p_rcv_transaction_id:' || p_rcv_transaction_id ||
2762 ,p_api_name => G_MODULE_PREFIX || l_api_name
2763 ,p_message => 'begin:' ||
2764 ',p_rcv_row_type:' || p_rcv_row_type ||
2766 ',p_ou_id :' || p_ou_id ||
2767 ',p_supplier_id:' || p_supplier_id ||
2768 ',p_supplier_site_id:' || p_supplier_site_id ||
2769 ',p_oem_inv_org_id :' || p_oem_inv_org_id ||
2770 ',p_tp_inv_org_id:' || p_tp_inv_org_id ||
2771 ',p_item_id:' || p_item_id ||
2772 ',p_rcv_unconsumed_pri:' || p_rcv_unconsumed_pri ||
2773 ',p_project_id:' || p_project_id ||
2774 ',p_task_id:' || p_task_id
2775 );
2776 -- **** for debug information in readonly UT environment.--- end ****
2777
2778 IF g_fnd_debug = 'Y' AND
2779 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2780 THEN
2781 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2782 , g_module_prefix || l_api_name || '.begin'
2783 , NULL);
2784 END IF;
2785
2786 -- Valid parameter validation;
2787 -- if all parameters are valid then continue; otherwise raise an error message in log;
2788 SELECT COUNT(*)
2789 INTO l_jmf_cfr_mid_temp_rcv_rows
2790 FROM jmf_shikyu_cfr_mid_temp
2791 WHERE row_type = p_rcv_row_type
2792 AND shikyu_id = p_rcv_transaction_id;
2793 --find if there is a rcv_transaction line exist
2794 IF l_jmf_cfr_mid_temp_rcv_rows = 1 --Found one row
2795 THEN
2796 --update if exist
2797 UPDATE jmf_shikyu_cfr_mid_temp
2798 -- Updated to fix potential issue of operations between null numbers
2799 -- SET primary_unconsumed_quantity = primary_unconsumed_quantity +
2800 SET primary_unconsumed_quantity = NVL(primary_unconsumed_quantity,0) +
2801 p_rcv_unconsumed_pri
2802 WHERE row_type = p_rcv_row_type
2803 AND shikyu_id = p_rcv_transaction_id;
2804 ELSE
2805 --NOTFOUND
2806 --add if not exist
2807 INSERT INTO jmf_shikyu_cfr_mid_temp
2808 (row_type
2809 ,shikyu_id
2810 ,primary_unconsumed_quantity
2811 ,oem_inv_org_id
2812 ,tp_inv_org_id
2813 ,item_id
2814 ,supplier_id
2815 ,site_id)
2816 VALUES
2817 (p_rcv_row_type
2818 ,p_rcv_transaction_id
2819 ,p_rcv_unconsumed_pri
2820 ,p_oem_inv_org_id
2821 ,p_tp_inv_org_id
2822 ,p_item_id
2823 ,p_supplier_id
2824 ,p_supplier_site_id);
2825 END IF; --IF l_jmf_cfr_mid_temp_rcv_rows = 1
2826 COMMIT; -- for debug on UT ?????
2827 rpt_debug_show_mid_data(
2828 p_row_type => p_rcv_row_type
2829 ,p_output_to => 'FND_LOG.STRING'
2830 );
2831 IF g_fnd_debug = 'Y' AND
2832 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2833 THEN
2834 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2835 , g_module_prefix || l_api_name || '.end'
2836 , NULL);
2837 END IF;
2838 EXCEPTION
2839 WHEN NO_DATA_FOUND THEN
2840 -- raise log message;
2841 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2842 fnd_msg_pub.Add;
2843
2844 WHEN OTHERS THEN
2845
2846 IF g_fnd_debug = 'Y' AND
2847 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2848 THEN
2849 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2850 , G_MODULE_PREFIX || l_api_name || '.execption'
2851 , NULL);
2852 END IF;
2853 -- **** for debug information in readonly UT environment.--- begin ****
2854 JMF_SHIKYU_RPT_UTIL.debug_output
2855 (
2856 p_output_to => 'FND_LOG.STRING'
2857 ,p_api_name => G_MODULE_PREFIX || l_api_name
2858 ,p_message => 'WHEN OTHERS THEN'
2859 );
2860 -- **** for debug information in readonly UT environment.--- end ****
2861
2862 END set_rcv_transaction_unconsume;
2863
2864 --========================================================================
2865 -- PROCEDURE : validate_cfr_mid_temp PUBLIC ,validate the data in mid temp table, do UOM and Currency conversion
2866 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
2867 -- COMMENT : this include UOM and Currency conversion and data check
2868 -- PRE-COND :
2869 -- EXCEPTIONS:
2870 --========================================================================
2871 PROCEDURE validate_cfr_mid_temp(p_rcv_row_type IN NUMBER)
2872 IS
2873 l_api_name CONSTANT VARCHAR2(30) := 'validate_cfr_mid_temp';
2874
2875 BEGIN
2876 -- **** for debug information in readonly UT environment.--- begin ****
2877 JMF_SHIKYU_RPT_UTIL.debug_output
2878 (
2879 p_output_to => 'FND_LOG.STRING'
2880 ,p_api_name => G_MODULE_PREFIX || l_api_name
2881 ,p_message => 'begin:' ||
2882 ',p_rcv_row_type:' || p_rcv_row_type
2883 );
2884 -- **** for debug information in readonly UT environment.--- end ****
2885
2886 IF g_fnd_debug = 'Y' AND
2890 , g_module_prefix || l_api_name || '.begin'
2887 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2888 THEN
2889 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2891 , 'p_rcv_row_type:' || p_rcv_row_type);
2892 END IF;
2893
2894 -- fill the uom, primary_uom column for CFR_TMP_RCV_ROW rows
2895 UPDATE jmf_shikyu_cfr_mid_temp jscmt
2896 SET jscmt.uom = (SELECT rt.unit_of_measure
2897 FROM rcv_transactions rt
2898 WHERE jscmt.row_type = CFR_TMP_RCV_ROW
2899 AND rt.transaction_id = jscmt.shikyu_id)
2900 ,jscmt.primary_uom = (SELECT rt.primary_unit_of_measure
2901 FROM rcv_transactions rt
2902 WHERE jscmt.row_type = CFR_TMP_RCV_ROW
2903 AND rt.transaction_id = jscmt.shikyu_id)
2904 WHERE jscmt.row_type = CFR_TMP_RCV_ROW;
2905 -- update the quantity column for CFR_TMP_RCV_ROW rows
2906 -- quantity of UOM =
2907 UPDATE jmf_shikyu_cfr_mid_temp
2908 SET quantity = (NVL(primary_unallocated_quantity
2909 ,0) + NVL(primary_unconsumed_quantity
2910 ,0)) *
2911 JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(JMF_SHIKYU_RPT_UTIL.uom_to_code(primary_uom)
2912 ,JMF_SHIKYU_RPT_UTIL.uom_to_code(uom)
2913 ,item_id)
2914 WHERE row_type = CFR_TMP_RCV_ROW;
2915 COMMIT; -- for debug on UT ?????
2916 rpt_debug_show_mid_data(
2917 p_row_type => CFR_TMP_RCV_ROW
2918 ,p_output_to => 'FND_LOG.STRING'
2919 );
2920 IF g_fnd_debug = 'Y' AND
2921 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2922 THEN
2923 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2924 , g_module_prefix || l_api_name || '.end'
2925 , NULL);
2926 END IF;
2927 EXCEPTION
2928 WHEN NO_DATA_FOUND THEN
2929 -- raise log message;
2930 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
2931 fnd_msg_pub.Add;
2932
2933 WHEN OTHERS THEN
2934 IF g_fnd_debug = 'Y' AND
2935 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2936 THEN
2937 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2938 , G_MODULE_PREFIX || l_api_name || '.execption'
2939 , NULL);
2940 END IF;
2941 -- **** for debug information in readonly UT environment.--- begin ****
2942 JMF_SHIKYU_RPT_UTIL.debug_output
2943 (
2947 );
2944 p_output_to => 'FND_LOG.STRING'
2945 ,p_api_name => G_MODULE_PREFIX || l_api_name
2946 ,p_message => 'WHEN OTHERS THEN'
2948 -- **** for debug information in readonly UT environment.--- end ****
2949
2950 END validate_cfr_mid_temp;
2951
2952 --========================================================================
2953 -- PROCEDURE : add_data_to_cfr_temp PUBLIC ,process the mid_temp data and add to temp talbe for report builder
2954 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
2955 -- COMMENT : and data merge to temp talbe for report builder
2956 -- PRE-COND :
2957 -- EXCEPTIONS:
2958 --========================================================================
2959 PROCEDURE add_data_to_cfr_temp
2960 (
2961 p_rcv_row_type IN NUMBER
2962 ,p_rpt_mode IN VARCHAR2
2963 ,p_days_received IN NUMBER
2964 ,p_currency_conversion_type IN VARCHAR2
2965 ,p_currency_conversion_date IN DATE
2966 ,p_functional_currency IN VARCHAR2
2967 -- Amy added to fix bug 5583680 start
2968 ,p_supplier_name_from IN VARCHAR2
2969 ,p_supplier_site_code_from IN VARCHAR2
2970 ,p_supplier_name_to IN VARCHAR2
2971 ,p_supplier_site_code_to IN VARCHAR2
2972 ,p_oem_inv_org_name_from IN VARCHAR2
2973 ,p_oem_inv_org_name_to IN VARCHAR2
2974 -- Amy added to fix bug 5583680 end
2975 ) IS
2976 l_api_name CONSTANT VARCHAR2(30) := 'add_data_to_cfr_temp';
2977 l_p_currency_conversion_date DATE;
2978 BEGIN
2979 -- **** for debug information in readonly UT environment.--- begin ****
2980 JMF_SHIKYU_RPT_UTIL.debug_output
2981 (
2982 p_output_to => 'FND_LOG.STRING'
2983 ,p_api_name => G_MODULE_PREFIX || l_api_name
2984 ,p_message => 'begin:' ||
2985 ',p_rcv_row_type:' || p_rcv_row_type ||
2986 ',p_rpt_mode:' || p_rpt_mode ||
2987 ',p_days_received:' || p_days_received ||
2988 ',p_currency_conversion_type:' || p_currency_conversion_type ||
2989 ',p_currency_conversion_date:' || p_currency_conversion_date ||
2990 ',p_functional_currency:' || p_functional_currency
2991 );
2992 -- **** for debug information in readonly UT environment.--- end ****
2993
2994 IF g_fnd_debug = 'Y' AND
2995 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2996 THEN
2997 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
2998 , g_module_prefix || l_api_name || '.begin'
2999 , NULL);
3000 END IF;
3001
3002 IF p_currency_conversion_date IS NULL
3003 THEN
3004 l_p_currency_conversion_date := SYSDATE;
3005 ELSE
3006 l_p_currency_conversion_date := p_currency_conversion_date;
3007 END IF;
3008 -- **** for debug information in readonly UT environment.--- begin ****
3009 JMF_SHIKYU_RPT_UTIL.debug_output
3010 (
3011 p_output_to => 'FND_LOG.STRING'
3012 ,p_api_name => G_MODULE_PREFIX || l_api_name
3013 ,p_message => 'begin:' ||
3014 ',l_p_currency_conversion_date:' || l_p_currency_conversion_date
3015 );
3016 -- **** for debug information in readonly UT environment.--- end ****
3017
3018
3019 --step1: get crude data: add the data for standard po, using po_lines_all
3020 rpt_get_crude_data(p_rpt_mode => p_rpt_mode
3021 ,p_currency_conversion_type => p_currency_conversion_type
3022 ,p_currency_conversion_date => l_p_currency_conversion_date
3023 ,p_functional_currency => p_functional_currency);
3024
3025 -- get the summary estinated qty for SHIKYU_component region in the report
3026 rpt_get_Comp_Estimated_data(p_rpt_mode => p_rpt_mode);
3027
3028 -- get the distinct SubPO info for SHIKYU_component accordingly in the report
3029 rpt_get_SubPO_data(p_rpt_mode => p_rpt_mode
3030 --Amy add for fixing bug 5391412 start
3031 ,p_ou_id => g_ou_id
3032 ,p_days_received => p_days_received);
3033 --Amy add for fixing bug 5391412 end
3034
3035 -- get the data for Un-received Replenishments
3036 -- Amy updated to fix bug 5583680 start
3037 -- rpt_get_UnReceived_data(p_rpt_mode => p_rpt_mode);
3038 rpt_get_UnReceived_data(p_rpt_mode => p_rpt_mode
3039 ,p_supplier_name_from => p_supplier_name_from
3040 ,p_supplier_site_code_from => p_supplier_site_code_from
3041 ,p_supplier_name_to => p_supplier_name_to
3042 ,p_supplier_site_code_to => p_supplier_site_code_to
3043 ,p_oem_inv_org_name_from => p_oem_inv_org_name_from
3044 ,p_oem_inv_org_name_to => p_oem_inv_org_name_to);
3045 -- Amy updated to fix bug 5583680 end
3046
3047 -- get the data for Un-received Replenishments
3048 rpt_get_Received_data( p_rpt_mode => p_rpt_mode
3049 ,p_days_received => p_days_received);
3050
3051 -- get data for internal report
3052 rpt_get_Int_data(p_rpt_mode => p_rpt_mode);
3053
3054 IF g_fnd_debug = 'Y' AND
3055 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3056 THEN
3057 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
3058 , g_module_prefix || l_api_name || '.end'
3059 , NULL);
3060 END IF;
3061 EXCEPTION
3062
3063 WHEN NO_DATA_FOUND THEN
3064 -- raise log message;
3065 FND_MESSAGE.set_name('JMF', 'JMF_SHK_RPT_NO_DATA');
3066 fnd_msg_pub.Add;
3067
3068 WHEN OTHERS THEN
3069 IF g_fnd_debug = 'Y' AND
3070 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3071 THEN
3072 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3073 , G_MODULE_PREFIX || l_api_name || '.execption'
3074 , SQLERRM);
3075 END IF;
3076 -- **** for debug information in readonly UT environment.--- begin ****
3077 JMF_SHIKYU_RPT_UTIL.debug_output
3078 (
3079 p_output_to => 'FND_LOG.STRING'
3080 ,p_api_name => G_MODULE_PREFIX || l_api_name
3081 ,p_message => 'WHEN OTHERS THEN:' || SQLERRM
3082 );
3083 -- **** for debug information in readonly UT environment.--- end ****
3084
3085 END add_data_to_cfr_temp;
3086
3087 --========================================================================
3088 -- PROCEDURE : rpt_get_crude_data PUBLIC ,
3089 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
3090 -- COMMENT : get the crude data into jmf_shikyu_cfr_rpt_temp with
3091 -- RPT_DATA_TYPE = CFR_CRUDE_DATA
3092 -- PRE-COND :
3093 -- EXCEPTIONS:
3094 --========================================================================
3095 PROCEDURE rpt_get_crude_data(
3096 p_rpt_mode IN VARCHAR2
3097 ,p_currency_conversion_type IN VARCHAR2
3098 ,p_currency_conversion_date IN DATE
3099 ,p_functional_currency IN VARCHAR2
3100 ) IS
3101 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_data';
3102
3103 BEGIN
3104 -- **** for debug information in readonly UT environment.--- begin ****
3105 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3106 ,p_api_name => G_MODULE_PREFIX || l_api_name
3107 ,p_message => 'begin:');
3108 -- **** for debug information in readonly UT environment.--- end ****
3109
3110 IF g_fnd_debug = 'Y'
3111 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3112 THEN
3113 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
3114 ,g_module_prefix || l_api_name
3115 ,'.begin');
3116 END IF;
3117
3118 -- insert the onhand items that with SubContracting Order.
3119 INSERT INTO jmf_shikyu_cfr_rpt_temp
3120 (rpt_mode
3121 ,TRANSACTION_ID
3122 ,RPT_DATA_TYPE
3123 ,oem_inv_org_id
3124 ,oem_inv_org_code
3125 ,supplier_id
3126 ,supplier_name
3127 ,site_id
3128 ,site_code
3129 ,site_address
3130 ,contact_id
3131 ,contact_name
3132 ,tp_inv_org_id
3133 ,tp_inv_org_code
3134 ,item_id
3135 ,item_number
3136 ,item_description
3137 ,currency_code
3138 ,functional_currency
3139 ,shikyu_price
3140 ,item_cost --the cost based on UOM after conversion from Pri_Uom
3141 ,value1 --the item qty in Pri Uom
3142 ,value2 --the item price in Pri UOM and Pri Currency
3143 ,value3 --the item cost in Pri UOM and Pri Currency, from cst_item_costs table
3144 ,project_id
3145 ,project_num
3146 ,task_id
3147 ,task_num
3148 ,uom
3149 ,ESTIMATED_QTY
3150 ,primary_uom
3151 ,REP_SO_HEADER_ID
3152 ,REP_SO_NUMBER
3153 ,REP_SO_VERSION_NUMBER
3154 ,REP_SO_LINE_ID
3155 ,REP_SO_LINE
3156 ,SHIPPED_DATE
3157 ,EXPECTED_RCV_DATE --the Rep PO need by date
3158 ,REP_PO_HEADER_ID
3159 ,REP_PO_NUMBER
3160 ,REP_PO_REVISION_NUM
3161 ,REP_PO_RELEASE_ID
3162 ,REP_PO_RELEASE_NUM
3163 ,REP_PO_LINE_ID
3164 ,REP_PO_LINE
3165 ,REP_PO_LINE_LOCATION_ID
3166 ,REP_PO_SHIPMENT
3167 ,REP_PO_DISTRIBUTION_ID
3168 ,SUBPO_HEADER_ID
3169 ,SUBPO_NUMBER
3170 ,SUBPO_LINE_ID
3171 ,SUBPO_LINE_NUM
3172 ,SUBPO_RELEASE_ID
3173 ,SUBPO_RELEASE_NUM
3174 ,SUBPO_SHIPMENT_ID
3175 ,SUBPO_SHIPMENT_NUM)
3176 SELECT p_rpt_mode rpt_mode
3177 ,cfr_mid.shikyu_id TRANSACTION_ID
3178 ,CFR_CRUDE_DATA RPT_DATA_TYPE
3179 ,cfr_mid.oem_inv_org_id oem_inv_org_id
3180 ,mp_oem.organization_code oem_org_code
3181 ,cfr_mid.supplier_id supplier_id
3182 ,pv.vendor_name supplier_name
3183 ,cfr_mid.site_id site_id
3184 ,pvs.vendor_site_code site_code
3185 ,pvs.address_line1 || ',' || pvs.address_line2 || ',' ||
3186 pvs.address_line3 site_address
3187 ,ph.vendor_contact_id contact_id
3188 ,pvc.prefix || ' ' || pvc.first_name || ',' || pvc.middle_name || ',' ||
3189 pvc.middle_name || ',' || pvc.last_name contact_name
3190 ,cfr_mid.tp_inv_org_id tp_inv_org_id
3191 ,mp_tp.organization_code tp_inv_org_code
3192 ,cfr_mid.item_id item_id
3193 ,JMF_SHIKYU_RPT_UTIL.get_item_number(cfr_mid.tp_inv_org_id
3194 ,cfr_mid.item_id) item_number
3195 ,item_v.description item_description
3196 ,ooha.transactional_curr_code currency_code --or jmf_shikyu_components.Currency ,not ph.currency_code
3197 ,p_functional_currency functional_currency
3198 ,oola.unit_selling_price po_unit_price --not pl.unit_price
3199 ,JMF_SHIKYU_RPT_UTIL.convert_amount(ooha.transactional_curr_code
3200 ,p_functional_currency
3201 --Amy update for fixing currency conversing issue start
3202 --when paramter p_currency_conversion_date and p_currency_conversion_type are not specified,
3203 -- use sysdate and conversion_type in so as default value.
3204 --,p_currency_conversion_date
3205 --,p_currency_conversion_type
3206 ,decode(p_currency_conversion_date,null,sysdate,p_currency_conversion_date)
3207 ,decode(p_currency_conversion_type,null,ooha.CONVERSION_TYPE_CODE,p_currency_conversion_type)
3208 --Amy update for fixing currency conversing issue end
3209 ,NVL(cic.item_cost
3210 ,0) *
3211 PO_UOM_S.po_uom_convert_p(cfr_mid.primary_uom
3212 ,cfr_mid.uom
3213 ,cfr_mid.item_id)) item_cost --the cost based on UOM after conversion from Pri_Uom
3214 ,(cfr_mid.quantity *
3215 PO_UOM_S.po_uom_convert_p(cfr_mid.uom
3216 ,cfr_mid.primary_uom
3217 ,cfr_mid.item_id)) value1 --item qty in Primary
3218 ,JMF_SHIKYU_RPT_UTIL.convert_amount(ooha.transactional_curr_code
3219 ,p_functional_currency
3220 --Amy update for fixing currency conversing issue start
3221 --when paramter p_currency_conversion_date and p_currency_conversion_type are not specified,
3222 -- use sysdate and conversion_type in so as default value.
3223 --,p_currency_conversion_date
3224 --,p_currency_conversion_type
3225 ,decode(p_currency_conversion_date,null,sysdate,p_currency_conversion_date)
3226 ,decode(p_currency_conversion_type,null,ooha.CONVERSION_TYPE_CODE,p_currency_conversion_type)
3227 --Amy update for fixing currency conversing issue end
3228 ,oola.unit_selling_price *
3229 PO_UOM_S.po_uom_convert_p(cfr_mid.uom
3230 ,cfr_mid.primary_uom
3231 ,cfr_mid.item_id)) value2 --the item price in Pri UOM and Pri Currency
3232 ,NVL(cic.item_cost
3233 ,0) value3 --Standard_Item_Cost in Pri UOM and Pri Currency
3234 --updated to fix project_id related issue start
3235 --,rcv.project_id project_id
3236 ,sub.project_id project_id
3237 --updated to fix project_id related issue start
3238 --updated to fix project_number related issue start
3239 --,prj.segment1 project_number
3240 ,NVL((SELECT DISTINCT segment1 AS project_number
3241 FROM pa_projects_all
3242 WHERE pa_projects_all.project_id(+) = sub.project_id),
3243 (SELECT DISTINCT project_number
3244 FROM pjm_seiban_numbers
3245 WHERE pjm_seiban_numbers.project_id(+) = sub.project_id)) project_number
3246 --updated to fix project_number related issue end
3247 --updated to fix project_id related issue start
3248 --,rcv.task_id task_id
3249 ,sub.task_id task_id
3250 --updated to fix project_id related issue start
3251 ,task.task_number task_number
3252 ,cfr_mid.uom --should jmf_shikyu_components.uom,need rcv.unit_of_measure to conversion?
3253 ,cfr_mid.quantity --the SHIKYU component quantity find in rcv for unallocated + unconsumed
3254 ,cfr_mid.primary_uom --rcv.primary_unit_of_measure
3255 ,oola.header_id RepSO_header_id
3256 ,ooha.order_number REP_SO_NUMBER
3257 ,ooha.version_number RepSO_Version_number
3258 ,oola.line_id RepSO_line_id
3259 ,oola.line_number REP_SO_LINE
3260 ,oola.actual_shipment_date SHIPPED_DATE
3261 ,poloc.need_by_date --,EXPECTED_RCV_DATE
3262 ,rcv.po_header_id RepPO_header_id
3263 ,ph.segment1 REP_PO_NUMBER
3264 ,rcv.po_revision_num RepPO_Revision_num
3265 ,rcv.po_release_id RepPO_Release_id
3266 ,pra.release_num REP_PO_RELEASE
3267 ,rcv.po_line_id RepPO_Line_id
3268 ,pl.line_num REP_PO_LINE
3269 ,rcv.po_line_location_id RepPO_line_location_id
3270 ,poloc.shipment_num REP_PO_SHIPMENT
3271 ,rcv.po_distribution_id RepPO_distribution_id
3272 ,pha_s.po_header_id SubPO_header_id
3273 ,pha_s.segment1 SubPO_Number
3274 ,pla_s.po_line_id SubPO_line_id
3275 ,pla_s.line_num SubPO_Line_num
3276 ,pra_s.po_release_id SubPO_release_id
3277 ,pra_s.release_num SubPO_release_Num
3278 ,plla_s.line_location_id SubPO_shipment_id
3279 ,plla_s.shipment_num SubPO_shipment_num
3280 FROM jmf_shikyu_cfr_mid_temp cfr_mid
3281 ,jmf_subcontract_orders sub
3282 ,po_line_locations_all poloc
3283 ,rcv_transactions rcv
3284 ,mtl_parameters mp_oem
3285 ,mtl_parameters mp_tp
3286 ,po_vendors pv
3287 ,po_vendor_sites_all pvs
3288 ,po_headers_all ph
3289 ,po_vendor_contacts pvc
3290 ,mtl_system_items_vl item_v
3291 ,po_lines_all pl
3292 ,pa_projects_all prj
3293 ,pa_tasks task
3294 ,po_releases_all pra
3295 ,jmf_shikyu_replenishments jsr
3296 ,oe_order_lines_all oola
3297 ,oe_order_headers_all ooha
3298 ,jmf_shikyu_allocations jsa
3299 ,po_line_locations_all plla_s
3300 ,po_headers_all pha_s
3301 ,po_lines_all pla_s
3302 ,po_releases_all pra_s
3303 ,cst_item_costs cic
3304 WHERE cfr_mid.row_type = CFR_TMP_RCV_ROW
3305 AND cfr_mid.oem_inv_org_id = mp_oem.organization_id
3306 AND cfr_mid.tp_inv_org_id = mp_tp.organization_id
3307 AND cfr_mid.supplier_id = pv.vendor_id(+)
3308 AND cfr_mid.site_id = pvs.vendor_site_id(+)
3309 AND cfr_mid.shikyu_id = rcv.transaction_id
3310 AND rcv.transaction_type = 'RECEIVE'
3311 AND rcv.po_header_id = ph.po_header_id
3312 AND ph.vendor_contact_id = pvc.vendor_contact_id(+)
3313 AND cfr_mid.tp_inv_org_id = item_v.organization_id
3314 AND cfr_mid.item_id = item_v.inventory_item_id
3315 AND rcv.po_line_location_id = poloc.line_location_id
3316 AND pl.po_line_id = poloc.po_line_id
3317 AND rcv.project_id = prj.project_id(+)
3318 AND rcv.task_id = task.task_id(+)
3319 AND poloc.po_release_id = pra.po_release_id(+)
3320 AND poloc.line_location_id = jsr.replenishment_po_shipment_id
3321 AND jsr.replenishment_so_line_id = oola.line_id
3322 AND oola.header_id = ooha.header_id
3323 --Updated to fix bug 5509464 start
3324 /* AND jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
3325 AND jsa.subcontract_po_shipment_id = plla_s.line_location_id
3326 AND sub.subcontract_po_shipment_id = plla_s.line_location_id
3327 AND plla_s.po_header_id = pha_s.po_header_id
3328 AND plla_s.po_line_id = pla_s.po_line_id*/
3329 AND jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
3330 AND jsa.subcontract_po_shipment_id = plla_s.line_location_id(+)
3331 AND plla_s.line_location_id = sub.subcontract_po_shipment_id(+)
3332 AND plla_s.po_header_id = pha_s.po_header_id(+)
3333 AND plla_s.po_line_id = pla_s.po_line_id(+)
3334 --Updated to fix bug 5509464 end
3335 AND plla_s.po_release_id = pra_s.po_release_id(+)
3336 AND oola.inventory_item_id = cic.inventory_item_id(+)
3337 AND oola.ship_from_org_id = cic.organization_id(+);
3338 /* Bug 6630087 - Start */
3339 /*Standard Cost check no longer required
3340 as Buy/Sell Subcontracting is supported for non-standard cost
3341 OEM organizations as well.
3342 Note : The confirmation report will display data for both
3343 Chargeable Subcontracting and Buy/Sell enabled OEM orgs depending
3344 on the parameters chosen in the report */
3345 -- AND cic.cost_type_id = 1; --frozen cost/ standard cost
3346 /* Bug 6630087 - End */
3347
3348 -- should insert the onhand items that without SubContracting Order.
3349 --or it will lose the onhand items
3350 /*
3351 TODO: owner="sunwa" category="Fix" priority="1 - High" created="2006-1-26"
3352 text="should insert the onhand items that without SubContracting Order."
3353 */
3354
3355 COMMIT; -- for debug on UT ?????
3356 IF g_fnd_debug = 'Y' AND
3357 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3358 THEN
3359 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
3360 , g_module_prefix || l_api_name || '.end'
3361 , NULL);
3362 END IF;
3363 EXCEPTION
3364 WHEN NO_DATA_FOUND THEN
3365 -- raise log message;
3366 NULL;
3367
3368 WHEN OTHERS THEN
3369 IF g_fnd_debug = 'Y'
3370 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3371 THEN
3372 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
3373 ,G_MODULE_PREFIX || l_api_name || '.execption'
3374 ,NULL);
3375 END IF;
3376 -- **** for debug information in readonly UT environment.--- begin ****
3377 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3378 ,p_api_name => G_MODULE_PREFIX || l_api_name
3379 ,p_message => 'WHEN OTHERS THEN');
3380 -- **** for debug information in readonly UT environment.--- end ****
3381
3382 END rpt_get_crude_data;
3383
3384 --========================================================================
3385 -- PROCEDURE : rpt_get_Comp_Estimated_data PUBLIC ,
3386 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
3387 -- COMMENT : get the Component Estimated data into jmf_shikyu_cfr_rpt_temp with
3388 -- RPT_DATA_TYPE = CFR_EXT_COMPONENT
3389 -- PRE-COND :
3390 -- EXCEPTIONS:
3391 --========================================================================
3392 PROCEDURE rpt_get_Comp_Estimated_data(p_rpt_mode IN VARCHAR2) IS
3393 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_Comp_Estimated_data';
3394
3395 BEGIN
3396 -- **** for debug information in readonly UT environment.--- begin ****
3397 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3398 ,p_api_name => G_MODULE_PREFIX || l_api_name
3399 ,p_message => 'begin:');
3400 -- **** for debug information in readonly UT environment.--- end ****
3401
3402 IF g_fnd_debug = 'Y'
3403 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3404 THEN
3405 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
3406 ,g_module_prefix || l_api_name
3407 ,'.begin');
3408 END IF;
3409
3410 INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
3411 (rpt_mode
3412 ,RPT_DATA_TYPE
3413 ,oem_inv_org_id
3414 ,supplier_id
3415 ,site_id
3416 ,contact_id
3417 ,tp_inv_org_id
3418 ,item_id
3419 ,shikyu_price
3420 ,currency_code
3421 ,uom
3422 ,project_id
3423 --added to fix project_number related issue start
3424 ,project_num
3425 --added to fix project_number related issue end
3426 ,task_id
3427 --added to fix project_number related issue start
3428 ,task_num
3429 --added to fix project_number related issue end
3430 ,primary_uom
3431 ,ESTIMATED_QTY)
3432 --value SUM(temp.estimated_qty) are supposed to onhand_quantity at item/price level,but got incorrect quantity.
3433 --got incorrect onhand quantity due to use inapposite group.
3434 --updated select statement to add transaction_id into group by to get correct onhand quantity.
3435 /*
3436 TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
3437 text="--updated for fix bug 5231233 Begin"
3438 */
3439 /* SELECT p_rpt_mode RPT_MODE --temp.rpt_mode
3440 ,CFR_EXT_COMPONENT RPT_DATA_TYPE
3441 ,temp.oem_inv_org_id shikyu_oem_inv_org_id
3442 ,temp.supplier_id shikyu_supplier_id
3443 ,temp.site_id shikyu_site_id
3444 ,temp.contact_id
3445 ,temp.tp_inv_org_id shikyu_tp_inv_org_id
3446 ,temp.item_id shikyu_item_id
3447 ,temp.shikyu_price shikyu_price
3448 ,temp.currency_code shikyu_currency
3449 ,temp.uom shikyu_uom
3450 ,temp.project_id
3451 ,temp.task_id
3452 ,temp.primary_uom shikyu_primary_uom
3453 ,SUM(temp.estimated_qty) shikyu_estimated_qty
3454 FROM JMF_SHIKYU_CFR_RPT_TEMP temp
3455 WHERE temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
3456 GROUP BY temp.rpt_mode
3457 ,temp.oem_inv_org_id
3458 ,temp.supplier_id
3459 ,temp.site_id
3460 ,temp.contact_id
3461 ,temp.tp_inv_org_id
3462 ,temp.item_id
3463 ,temp.shikyu_price
3464 ,temp.currency_code
3465 ,temp.uom
3466 ,temp.project_id
3467 ,temp.task_id
3468 ,temp.primary_uom;
3469 */
3470 SELECT p_rpt_mode RPT_MODE --temp.rpt_mode
3471 ,CFR_EXT_COMPONENT RPT_DATA_TYPE
3472 ,temp.oem_inv_org_id shikyu_oem_inv_org_id
3473 ,temp.supplier_id shikyu_supplier_id
3474 ,temp.site_id shikyu_site_id
3475 ,temp.contact_id
3476 ,temp.tp_inv_org_id shikyu_tp_inv_org_id
3477 ,temp.item_id shikyu_item_id
3478 ,temp.shikyu_price shikyu_price
3479 ,temp.currency_code shikyu_currency
3480 ,temp.uom shikyu_uom
3481 ,temp.project_id
3482 --added to fix project_number related issue start
3483 ,temp.project_num
3484 --added to fix project_number related issue end
3485 ,temp.task_id
3486 --added to fix project_number related issue start
3487 ,temp.task_num
3488 --added to fix project_number related issue end
3489 ,temp.primary_uom shikyu_primary_uom
3490 ,SUM(temp.estimated_qty) shikyu_estimated_qty
3491 FROM (SELECT rpt_temp.rpt_mode rpt_mode
3492 ,rpt_temp.oem_inv_org_id oem_inv_org_id
3493 ,rpt_temp.supplier_id supplier_id
3494 ,rpt_temp.site_id site_id
3495 ,rpt_temp.contact_id contact_id
3496 ,rpt_temp.tp_inv_org_id tp_inv_org_id
3497 ,rpt_temp.item_id item_id
3498 ,rpt_temp.shikyu_price shikyu_price
3499 ,rpt_temp.currency_code currency_code
3500 ,rpt_temp.uom uom
3501 ,rpt_temp.project_id project_id
3502 --added to fix project_number related issue start
3503 ,rpt_temp.project_num project_num
3504 --added to fix project_number related issue end
3505 ,rpt_temp.task_id task_id
3506 --added to fix project_number related issue start
3507 ,rpt_temp.task_num task_num
3508 --added to fix project_number related issue end
3509 ,rpt_temp.primary_uom primary_uom
3510 ,rpt_temp.estimated_qty
3511 ,rpt_temp.transaction_id transaction_id
3512 FROM JMF_SHIKYU_CFR_RPT_TEMP rpt_temp
3513 WHERE rpt_temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
3514 GROUP BY rpt_temp.rpt_mode
3515 ,rpt_temp.oem_inv_org_id
3516 ,rpt_temp.supplier_id
3517 ,rpt_temp.site_id
3518 ,rpt_temp.contact_id
3519 ,rpt_temp.tp_inv_org_id
3520 ,rpt_temp.item_id
3521 ,rpt_temp.shikyu_price
3522 ,rpt_temp.currency_code
3523 ,rpt_temp.uom
3524 ,rpt_temp.project_id
3525 --added to fix project_number related issue start
3526 ,rpt_temp.project_num
3527 --added to fix project_number related issue end
3528 ,rpt_temp.task_id
3529 --added to fix project_number related issue start
3530 ,rpt_temp.task_num
3531 --added to fix project_number related issue end
3532 ,rpt_temp.primary_uom
3533 ,rpt_temp.estimated_qty
3534 ,rpt_temp.transaction_id) temp
3535 GROUP BY temp.rpt_mode
3536 ,temp.oem_inv_org_id
3537 ,temp.supplier_id
3538 ,temp.site_id
3539 ,temp.contact_id
3540 ,temp.tp_inv_org_id
3541 ,temp.item_id
3542 ,temp.shikyu_price
3543 ,temp.currency_code
3544 ,temp.uom
3545 ,temp.project_id
3546 --added to fix project_number related issue start
3547 ,temp.project_num
3548 --added to fix project_number related issue end
3549 ,temp.task_id
3550 --added to fix project_number related issue start
3551 ,temp.task_num
3552 --added to fix project_number related issue end
3553 ,temp.primary_uom;
3554 --updated for fix bug #5231233 End
3555
3556 --seems the SUM(temp.estimated_qty) is not the onhand quantity.
3557 --add for fix bug 4997302 Begin
3558 --update the onhand quantity to the ESTIMATED_QTY column, assume the same price and cost, need track
3559 /*
3560 TODO: owner="sunwa" category="Fix" priority="1 - High" created="2006-1-26"
3561 text="--add for fix bug 4997302 Begin"
3562 */
3563 -- As the SUM(temp.estimated_qty) is correct by fixing bug #5231233,sql statement below should be updated
3564 /*
3565 TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
3566 text="--updated for fix bug 5231233 Begin"
3567 */
3568
3569 -- Deleted Update to fix bug 5665445 for incorrect onhand Qty in Secondary UOM case
3570 /* UPDATE JMF_SHIKYU_CFR_RPT_TEMP jscrt
3571 SET jscrt.estimated_qty = PO_UOM_S.po_uom_convert_p(jscrt.primary_uom
3572 ,jscrt.uom
3573 ,jscrt.item_id) *jscrt.ESTIMATED_QTY,
3574 (SELECT jscmt.primary_unconsumed_quantity
3575 FROM jmf_shikyu_cfr_mid_temp jscmt
3576 WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
3577 AND jscmt.tp_inv_org_id =
3578 jscrt.tp_inv_org_id
3579 AND jscmt.item_id = jscrt.item_id),
3580 jscrt.value1 = (SELECT jscmt.primary_unconsumed_quantity
3581 FROM jmf_shikyu_cfr_mid_temp jscmt
3582 WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
3583 AND jscmt.tp_inv_org_id =
3584 jscrt.tp_inv_org_id
3585 AND jscmt.item_id = jscrt.item_id)
3586 jscrt.value1 = jscrt.ESTIMATED_QTY
3587 --updated for fix bug 5231233 End
3588 WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT; --should not CFR_INT_COMPONENT;
3589 --add for fix bug 4997302 End
3590 */
3591
3592 -- update the SHIKYU_components item number, desc, type info
3593 UPDATE JMF_SHIKYU_CFR_RPT_TEMP temp
3594 SET temp.item_number = (SELECT msibk.concatenated_segments
3595 FROM MTL_SYSTEM_ITEMS_B_KFV msibk
3596 WHERE temp.tp_inv_org_id =
3597 msibk.organization_id
3598 AND temp.item_id =
3599 msibk.inventory_item_id)
3600 ,temp.item_description = (SELECT msibk.description
3601 FROM MTL_SYSTEM_ITEMS_B_KFV msibk
3602 WHERE temp.tp_inv_org_id =
3603 msibk.organization_id
3604 AND temp.item_id =
3605 msibk.inventory_item_id)
3606 ,temp.replenishment_type = (SELECT flv.meaning
3607 FROM fnd_lookup_values flv
3608 ,MTL_SYSTEM_ITEMS_B_KFV msibk
3609 WHERE flv.LANGUAGE = USERENV('LANG')
3610 AND flv.lookup_type =
3611 'JMF_SHK_ITEM_REPLEN_TYPE'
3612 AND msibk.subcontracting_component =
3613 flv.lookup_code
3614 AND temp.tp_inv_org_id =
3615 msibk.organization_id
3616 AND temp.item_id =
3617 msibk.inventory_item_id)
3618 WHERE temp.rpt_DATA_TYPE = CFR_EXT_COMPONENT;
3619 COMMIT; -- for debug on UT ?????
3620 IF g_fnd_debug = 'Y' AND
3621 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3622 THEN
3623 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
3624 , g_module_prefix || l_api_name || '.end'
3625 , NULL);
3626 END IF;
3627 EXCEPTION
3628 WHEN NO_DATA_FOUND THEN
3629 -- raise log message;
3630 NULL;
3631
3632 WHEN OTHERS THEN
3633 IF g_fnd_debug = 'Y'
3634 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3635 THEN
3636 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
3637 ,G_MODULE_PREFIX || l_api_name || '.execption'
3638 ,NULL);
3639 END IF;
3640 -- **** for debug information in readonly UT environment.--- begin ****
3641 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3642 ,p_api_name => G_MODULE_PREFIX || l_api_name
3643 ,p_message => 'WHEN OTHERS THEN');
3644 -- **** for debug information in readonly UT environment.--- end ****
3645
3646 END rpt_get_Comp_Estimated_data;
3647
3648 --========================================================================
3649 -- PROCEDURE : rpt_get_SubPO_data PUBLIC ,
3650 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
3651 -- p_ou_id ou_id to identify period infor
3652 -- p_days_received user entered parameter to determine period
3653 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
3654 -- RPT_DATA_TYPE = CFR_EXT_SUBCONTRACT_PO
3655 -- PRE-COND :
3656 -- EXCEPTIONS:
3657 --========================================================================
3658 PROCEDURE rpt_get_SubPO_data(p_rpt_mode IN VARCHAR2,p_ou_id IN NUMBER,p_days_received IN NUMBER) IS
3659 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_SubPO_data';
3660
3661 BEGIN
3662 -- **** for debug information in readonly UT environment.--- begin ****
3663 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3664 ,p_api_name => G_MODULE_PREFIX || l_api_name
3665 ,p_message => 'begin:');
3666 -- **** for debug information in readonly UT environment.--- end ****
3667
3668 IF g_fnd_debug = 'Y'
3669 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3670 THEN
3671 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
3672 ,g_module_prefix || l_api_name
3673 ,'.begin');
3674 END IF;
3675
3676 INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
3677 (RPT_MODE
3678 ,RPT_DATA_TYPE
3679 ,oem_inv_org_id
3680 ,supplier_id
3681 ,site_id
3682 ,tp_inv_org_id
3683 ,item_id
3684 ,shikyu_price
3685 ,currency_code
3686 ,uom
3687 ,project_id
3688 --added to fix project_number related issue start
3689 ,project_num
3690 --added to fix project_number related issue end
3691 ,task_id
3692 --added to fix project_number related issue start
3693 ,task_num
3694 --added to fix project_number related issue end
3695 ,primary_uom
3696 ,subpo_header_id
3697 ,subpo_number
3698 ,subpo_line_num
3699 ,subpo_release_num
3700 ,subpo_shipment_num
3701 ,OSA_ITEM_ID
3702 ,OSA_ITEM_NUMBER
3703 ,OSA_ITEM_DESCRIPTION
3704 ,REQUESTED_COMP_QTY
3705 ,ISSUED_COMP_QTY)
3706 SELECT DISTINCT p_rpt_mode RPT_MODE --temp.rpt_mode
3707 ,CFR_EXT_SUBCONTRACT_PO RPT_DATA_TYPE
3708 ,cfr_mid_item_group.oem_inv_org_id oem_inv_org_id
3709 ,cfr_mid_item_group.supplier_id supplier_id
3710 ,cfr_mid_item_group.site_id site_id
3711 ,cfr_mid_item_group.tp_inv_org_id tp_inv_org_id
3712 ,cfr_mid_item_group.item_id item_id
3713 ,oola.unit_selling_price po_unit_price --not pl.unit_price
3714 ,ooha.transactional_curr_code currency_code --or jmf_shikyu_components.Currency ,not ph.currency_code
3715 ,cfr_mid_item_group.uom --should jmf_shikyu_components.uom,need rcv.unit_of_measure to conversion?
3716 ,jso.project_id project_id
3717 --Added to fix project_number related issue start
3718 ,NVL((SELECT DISTINCT segment1 AS project_number
3719 FROM pa_projects_all
3720 WHERE pa_projects_all.project_id(+) = jso.project_id),
3721 (SELECT DISTINCT project_number
3722 FROM pjm_seiban_numbers
3723 WHERE pjm_seiban_numbers.project_id(+) = jso.project_id)) project_number
3724 --Added to fix project_number related issue end
3725 ,jso.task_id task_id
3726 --Added to get task number start
3727 ,task.task_number task_number
3728 --Added to get task number end
3729 ,cfr_mid_item_group.primary_uom --rcv.primary_unit_of_measure
3730 ,pha_s.po_header_id SubPO_header_id
3731 ,pha_s.segment1 SubPO_Number
3732 ,pla_s.line_num SubPO_Line_num
3733 ,pra_s.release_num SubPO_release_Num
3734 ,plla_s.shipment_num SubPO_shipment_NUm
3735 ,jso.osa_item_id
3736 ,msibk.concatenated_segments
3737 ,msibk.description
3738 ,wro.required_quantity
3739 ,wro.quantity_issued
3740 FROM jmf_subcontract_orders jso
3741 ,jmf_shikyu_components jsc
3742 ,(select DISTINCT cfr_mid_temp.oem_inv_org_id oem_inv_org_id
3743 ,cfr_mid_temp.supplier_id supplier_id
3744 ,cfr_mid_temp.site_id site_id
3745 ,cfr_mid_temp.tp_inv_org_id tp_inv_org_id
3746 ,cfr_mid_temp.item_id item_id
3747 ,cfr_mid_temp.uom
3748 ,cfr_mid_temp.primary_uom
3749 from jmf_shikyu_cfr_mid_temp cfr_mid_temp
3750 where cfr_mid_temp.row_type = CFR_TMP_RCV_ROW) cfr_mid_item_group
3751 ,jmf_shikyu_replenishments jsr
3752 ,jmf_shikyu_allocations jsa
3753 ,oe_order_lines_all oola
3754 ,oe_order_headers_all ooha
3755 ,po_line_locations_all plla_s
3756 ,po_headers_all pha_s
3757 ,po_lines_all pla_s
3758 ,po_releases_all pra_s
3759 ,wip_requirement_operations wro
3760 ,MTL_SYSTEM_ITEMS_B_KFV msibk
3761 ,rcv_transactions rt
3762 --Added to get task start
3763 ,pa_tasks task
3764 --Added to get task end
3765 WHERE jsc.SHIKYU_COMPONENT_ID = cfr_mid_item_group.item_id
3766 AND jsc.OEM_ORGANIZATION_ID = cfr_mid_item_group.oem_inv_org_id
3767 AND jsc.UOM = JMF_SHIKYU_RPT_UTIL.uom_to_code(cfr_mid_item_group.uom)
3768 AND jsc.PRIMARY_UOM = JMF_SHIKYU_RPT_UTIL.uom_to_code(cfr_mid_item_group.primary_uom)
3769 AND jso.TP_ORGANIZATION_ID = cfr_mid_item_group.tp_inv_org_id
3770 AND jso.OEM_ORGANIZATION_ID = cfr_mid_item_group.oem_inv_org_id
3771 AND jso.SUBCONTRACT_PO_SHIPMENT_ID = jsc.SUBCONTRACT_PO_SHIPMENT_ID
3772 AND jsc.SUBCONTRACT_PO_SHIPMENT_ID = jsa.SUBCONTRACT_PO_SHIPMENT_ID
3773 AND jsc.SHIKYU_COMPONENT_ID = jsa.SHIKYU_COMPONENT_ID
3774 AND jsa.REPLENISHMENT_SO_LINE_ID = jsr.REPLENISHMENT_SO_LINE_ID
3775 AND jsr.REPLENISHMENT_SO_LINE_ID = oola.LINE_ID
3776 AND oola.HEADER_ID = ooha.HEADER_ID
3777 AND plla_s.LINE_LOCATION_ID = jsa.SUBCONTRACT_PO_SHIPMENT_ID
3778 AND pla_s.PO_LINE_ID=plla_s.PO_LINE_ID
3779 AND pla_s.PO_HEADER_ID=plla_s.PO_HEADER_ID
3780 AND pha_s.PO_HEADER_ID=pla_s.PO_HEADER_ID
3781 AND plla_s.po_release_id = pra_s.po_release_id(+)
3782 AND jso.osa_item_id = msibk.inventory_item_id
3783 AND jso.tp_organization_id = wro.organization_id
3784 AND jso.wip_entity_id = wro.wip_entity_id
3785 AND jso.interlock_status = 'C' --added to fix bug 5415777
3786 AND wro.operation_seq_num = 1
3787 AND wro.repetitive_schedule_id IS NULL
3788 AND cfr_mid_item_group.item_id = wro.inventory_item_id
3789 AND plla_s.QUANTITY_RECEIVED>0
3790 and plla_s.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
3791 and rt.transaction_date < sysdate+1--period to date
3795 --Added to get task end
3792 and rt.transaction_date >= sysdate-p_days_received --period from date
3793 --Added to get task start
3794 AND jso.task_id = task.task_id(+)
3796 ;
3797
3798 COMMIT; -- for debug on UT ?????
3799
3800 IF g_fnd_debug = 'Y' AND
3801 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3802 THEN
3803 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
3804 , g_module_prefix || l_api_name || '.end'
3805 , NULL);
3806 END IF;
3807 EXCEPTION
3808 WHEN NO_DATA_FOUND THEN
3809 -- raise log message;
3810 NULL;
3811
3812 WHEN OTHERS THEN
3813 IF g_fnd_debug = 'Y'
3814 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3815 THEN
3816 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
3817 ,G_MODULE_PREFIX || l_api_name || '.execption'
3818 ,NULL);
3819 END IF;
3820 -- **** for debug information in readonly UT environment.--- begin ****
3821 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3822 ,p_api_name => G_MODULE_PREFIX || l_api_name
3823 ,p_message => 'WHEN OTHERS THEN');
3824 -- **** for debug information in readonly UT environment.--- end ****
3825
3826 END rpt_get_SubPO_data;
3827
3828 --========================================================================
3829 -- PROCEDURE : rpt_get_UnReceived_data PUBLIC ,
3830 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
3831 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
3832 -- RPT_DATA_TYPE = CFR_EXT_UN_RCV
3833 -- PRE-COND :
3834 -- EXCEPTIONS: do not consider the SO return, and RepSO with RepPO is one to one.
3835 --========================================================================
3836 PROCEDURE rpt_get_UnReceived_data
3837 (
3838 p_rpt_mode IN VARCHAR2
3839 -- Amy added to fix bug 5583680 start
3840 ,p_supplier_name_from IN VARCHAR2
3841 ,p_supplier_site_code_from IN VARCHAR2
3842 ,p_supplier_name_to IN VARCHAR2
3843 ,p_supplier_site_code_to IN VARCHAR2
3844 ,p_oem_inv_org_name_from IN VARCHAR2
3845 ,p_oem_inv_org_name_to IN VARCHAR2) IS
3846 -- Amy added to fix bug 5583680 end
3847
3848 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_UnReceived_data';
3849
3850 BEGIN
3851 -- **** for debug information in readonly UT environment.--- begin ****
3852 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
3853 ,p_api_name => G_MODULE_PREFIX || l_api_name
3854 ,p_message => 'begin:');
3855 -- **** for debug information in readonly UT environment.--- end ****
3856
3857 IF g_fnd_debug = 'Y'
3858 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3859 THEN
3860 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
3861 ,g_module_prefix || l_api_name
3862 ,'.begin');
3863 END IF;
3864
3865 INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
3866 (rpt_mode
3867 ,rpt_data_type
3868 ,Oem_Inv_Org_Id
3869 ,Tp_Inv_Org_Id
3870 ,Supplier_Id
3871 ,Site_Id
3872 ,rep_so_header_id
3873 ,rep_so_number
3874 ,rep_so_line_id
3875 ,rep_so_line
3876 ,rep_po_header_id
3877 ,rep_po_number
3878 ,rep_po_line_id
3879 ,rep_po_line
3880 ,rep_po_line_location_id
3881 ,rep_po_shipment
3882 ,rep_po_release_id
3883 ,rep_po_release_num
3884 ,item_id
3885 ,item_number
3886 ,estimated_qty
3887 ,uom
3888 ,shipped_date
3889 ,expected_rcv_date)
3890 SELECT p_rpt_mode rpt_mode
3891 ,CFR_EXT_UN_RCV rpt_data_type
3892 ,jsr.oem_organization_id -- = oola.ship_from_org_id
3893 ,jsr.tp_organization_id -- = plla.ship_to_organization_id
3894 ,hoi.org_information3 -- the tp org 's supplier = SubPO supplier
3895 ,hoi.org_information4 -- the tp org 's supplier site = SubPO site
3896 ,ooha.header_id rep_so_header_id
3897 ,ooha.order_number rep_so_number
3898 ,oola.line_id rep_so_line_id
3899 ,oola.line_number rep_so_line
3900 ,pha.po_header_id rep_po_header_id
3901 ,pha.segment1 rep_po_number
3902 ,pla.po_line_id rep_po_line_id
3903 ,pla.line_num rep_po_line
3904 ,plla.line_location_id rep_po_line_location_id
3905 ,plla.shipment_num rep_po_shipment
3906 ,pra.po_release_id rep_po_release_id
3907 ,pra.release_num rep_po_release_num
3908 ,oola.inventory_item_id item_id --jsr.shikyu_component_id
3909 ,oola.ordered_item item_number
3910 ,oola.shipped_quantity estimated_qty
3911 ,oola.order_quantity_uom uom
3912 ,oola.actual_shipment_date shipped_date
3913 ,NVL(plla.need_by_date
3914 ,plla.promised_date) expected_rcv_date
3915 FROM oe_order_lines_all oola
3916 ,oe_order_headers_all ooha
3917 ,po_line_locations_all plla
3918 ,po_lines_all pla
3919 ,po_releases_all pra
3920 ,po_headers_all pha
3921 ,jmf_shikyu_replenishments jsr
3922 ,hr_organization_information hoi
3923 -- Amy added to fix bug 5583680 start
3924 ,hr_all_organization_units_tl oem_haoutl
3925 ,hr_organization_information tp_hoi
3926 ,po_vendors pv
3927 ,po_vendor_sites_all pvs
3928 -- Amy added to fix bug 5583680 end
3929 WHERE oola.header_id = ooha.header_id
3930 AND plla.po_line_id = pla.po_line_id
3931 AND plla.po_header_id = pha.po_header_id
3932 AND plla.po_release_id = pra.po_release_id(+)
3933 AND oola.line_id = jsr.replenishment_so_line_id
3934 AND plla.line_location_id = jsr.replenishment_po_shipment_id
3935 AND jsr.tp_organization_id = hoi.organization_id
3936 AND hoi.org_information_context = 'Customer/Supplier Association'
3937 -- Amy updated to fix bug 5583680 start
3938 /*AND (SELECT COUNT(*)
3939 FROM JMF_SHIKYU_CFR_RPT_TEMP jscrt
3940 WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT
3941 AND jscrt.oem_inv_org_id = jsr.oem_organization_id
3942 AND jscrt.tp_inv_org_id = jsr.tp_organization_id) > 0
3943 AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(oola.sold_from_org_id
3944 ,oola.inventory_item_id
3945 ,oola.order_quantity_uom
3946 ,NVL(oola.shipped_quantity
3947 ,0)) >
3948 JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(plla.ship_to_organization_id
3949 ,pla.item_id
3950 ,JMF_SHIKYU_RPT_UTIL.uom_to_code(pla.unit_meas_lookup_code)
3951 ,NVL(plla.quantity_received
3952 ,0));*/
3953 AND jsr.oem_organization_id = oem_haoutl.organization_id
3954 AND oem_haoutl.NAME >= NVL(p_oem_inv_org_name_from
3955 ,oem_haoutl.NAME)
3956 AND oem_haoutl.NAME <= NVL(p_oem_inv_org_name_to
3957 ,oem_haoutl.NAME)
3958 AND oem_haoutl.LANGUAGE = USERENV('LANG')
3959 AND jsr.tp_organization_id = tp_hoi.organization_id
3960 AND tp_hoi.org_information_context = 'Customer/Supplier Association'
3961 AND tp_hoi.org_information3 = pv.vendor_id
3962 AND tp_hoi.org_information4 = pvs.vendor_site_id
3963 AND pv.vendor_name >= NVL(p_supplier_name_from, pv.vendor_name)
3964 AND pv.vendor_name <= NVL(p_supplier_name_to, pv.vendor_name)
3965 AND pvs.vendor_site_code >= NVL(p_supplier_site_code_from, pvs.vendor_site_code)
3966 AND pvs.vendor_site_code <= NVL(p_supplier_site_code_to, pvs.vendor_site_code)
3967 AND NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.oem_organization_id
3968 ,oola.inventory_item_id
3969 ,oola.order_quantity_uom
3970 ,NVL(oola.shipped_quantity
3971 ,0)),0) >
3972 NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(plla.ship_to_organization_id
3973 ,pla.item_id
3974 ,JMF_SHIKYU_RPT_UTIL.uom_to_code(pla.unit_meas_lookup_code)
3975 ,NVL(plla.quantity_received
3976 ,0)),0);
3977 -- Amy updated to fix bug 5583680 end
3978 COMMIT; -- for debug on UT ?????
3979 IF g_fnd_debug = 'Y' AND
3980 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3981 THEN
3982 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
3983 , g_module_prefix || l_api_name || '.end'
3984 , NULL);
3985 END IF;
3986 EXCEPTION
3987 WHEN NO_DATA_FOUND THEN
3988 -- raise log message;
3989 NULL;
3990
3991 WHEN OTHERS THEN
3992 IF g_fnd_debug = 'Y'
3993 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3994 THEN
3995 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
3996 ,G_MODULE_PREFIX || l_api_name || '.execption'
3997 ,NULL);
3998 END IF;
3999 -- **** for debug information in readonly UT environment.--- begin ****
4000 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4001 ,p_api_name => G_MODULE_PREFIX || l_api_name
4002 ,p_message => 'WHEN OTHERS THEN');
4003 -- **** for debug information in readonly UT environment.--- end ****
4004
4005 END rpt_get_UnReceived_data;
4006
4007 --========================================================================
4008 -- PROCEDURE : rpt_get_Received_data PUBLIC ,
4009 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
4010 -- COMMENT : get the received data in days into jmf_shikyu_cfr_rpt_temp with
4011 -- RPT_DATA_TYPE = CFR_EXT_RCV_IN_DAYS
4012 -- PRE-COND :
4013 -- EXCEPTIONS: do not consider the SO return, and RepSO with RepPO is one to one.
4014 --========================================================================
4015 PROCEDURE rpt_get_Received_data(
4016 p_rpt_mode IN VARCHAR2
4017 ,p_days_received IN NUMBER
4018 ) IS
4019 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_Received_data';
4020
4021 BEGIN
4022 -- **** for debug information in readonly UT environment.--- begin ****
4023 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4024 ,p_api_name => G_MODULE_PREFIX || l_api_name
4025 ,p_message => 'begin:');
4026 -- **** for debug information in readonly UT environment.--- end ****
4027
4028 IF g_fnd_debug = 'Y'
4029 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4030 THEN
4031 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
4032 ,g_module_prefix || l_api_name
4033 ,'.begin');
4034 END IF;
4035
4036 -- data that Shipped = received and exptected rcv date is in p_days_received
4037 INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
4038 (rpt_mode
4039 ,rpt_data_type
4040 ,Oem_Inv_Org_Id
4041 ,Tp_Inv_Org_Id
4042 ,Supplier_Id
4043 ,Site_Id
4044 ,rep_so_header_id
4045 ,rep_so_number
4046 ,rep_so_line_id
4047 ,rep_so_line
4048 ,rep_po_header_id
4049 ,rep_po_number
4050 ,rep_po_line_id
4051 ,rep_po_line
4052 ,rep_po_line_location_id
4053 ,rep_po_shipment
4054 ,rep_po_release_id
4055 ,rep_po_release_num
4056 ,item_id
4057 ,item_number
4058 ,estimated_qty
4059 ,uom
4060 ,shipped_date
4061 ,expected_rcv_date)
4062 SELECT p_rpt_mode rpt_mode
4063 ,CFR_EXT_RCV_IN_DAYS rpt_data_type
4064 ,jsr.oem_organization_id -- = oola.ship_from_org_id
4065 ,jsr.tp_organization_id -- = plla.ship_to_organization_id
4066 ,hoi.org_information3 -- the tp org 's supplier = SubPO supplier
4067 ,hoi.org_information4 -- the tp org 's supplier site = SubPO site
4068 ,ooha.header_id rep_so_header_id
4069 ,ooha.order_number rep_so_number
4070 ,oola.line_id rep_so_line_id
4071 ,oola.line_number rep_so_line
4072 ,pha.po_header_id rep_po_header_id
4073 ,pha.segment1 rep_po_number
4074 ,pla.po_line_id rep_po_line_id
4075 ,pla.line_num rep_po_line
4076 ,plla.line_location_id rep_po_line_location_id
4077 ,plla.shipment_num rep_po_shipment
4078 ,pra.po_release_id rep_po_release_id
4079 ,pra.release_num rep_po_release_num
4080 ,oola.inventory_item_id item_id --jsr.shikyu_component_id
4081 ,oola.ordered_item item_number
4082 ,oola.shipped_quantity estimated_qty
4083 ,oola.order_quantity_uom uom
4084 ,oola.actual_shipment_date shipped_date
4085 ,NVL(plla.need_by_date
4086 ,plla.promised_date) expected_rcv_date
4087 FROM oe_order_lines_all oola
4088 ,oe_order_headers_all ooha
4089 ,po_line_locations_all plla
4090 ,po_lines_all pla
4091 ,po_releases_all pra
4092 ,po_headers_all pha
4093 ,jmf_shikyu_replenishments jsr
4094 ,hr_organization_information hoi
4095 WHERE oola.header_id = ooha.header_id
4096 AND plla.po_line_id = pla.po_line_id
4097 AND plla.po_header_id = pha.po_header_id
4098 AND plla.po_release_id = pra.po_release_id(+)
4099 AND oola.line_id = jsr.replenishment_so_line_id
4100 AND plla.line_location_id = jsr.replenishment_po_shipment_id
4101 AND jsr.tp_organization_id = hoi.organization_id
4102 AND hoi.org_information_context = 'Customer/Supplier Association'
4103 AND (SELECT COUNT(*)
4104 FROM JMF_SHIKYU_CFR_RPT_TEMP jscrt
4105 WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT
4106 AND jscrt.oem_inv_org_id = jsr.oem_organization_id
4107 AND jscrt.tp_inv_org_id = jsr.tp_organization_id) > 0
4108 AND (SYSDATE - NVL(plla.need_by_date
4109 ,plla.promised_date)) <= p_days_received
4110 -- Bug 5583680: Fixed data issue for the Received Replenishments in
4111 -- Past xx Days section
4112 AND NVL(oola.shipped_quantity,0) > 0
4113 AND NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.oem_organization_id
4114 ,oola.inventory_item_id
4115 ,oola.order_quantity_uom
4116 ,NVL(oola.shipped_quantity
4117 ,0)),0) =
4118 NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(plla.ship_to_organization_id
4119 ,pla.item_id
4120 ,JMF_SHIKYU_RPT_UTIL.uom_to_code(pla.unit_meas_lookup_code)
4121 ,NVL(plla.quantity_received
4122 ,0)),0);
4123 COMMIT; -- for debug on UT ?????
4124 IF g_fnd_debug = 'Y' AND
4125 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4126 THEN
4127 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
4128 , g_module_prefix || l_api_name || '.end'
4129 , NULL);
4130 END IF;
4131 EXCEPTION
4132 WHEN NO_DATA_FOUND THEN
4133 -- raise log message;
4134 NULL;
4135
4136 WHEN OTHERS THEN
4137 IF g_fnd_debug = 'Y'
4138 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4139 THEN
4140 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
4141 ,G_MODULE_PREFIX || l_api_name || '.execption'
4142 ,NULL);
4143 END IF;
4144 -- **** for debug information in readonly UT environment.--- begin ****
4145 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4146 ,p_api_name => G_MODULE_PREFIX || l_api_name
4147 ,p_message => 'WHEN OTHERS THEN');
4148 -- **** for debug information in readonly UT environment.--- end ****
4149
4150 END rpt_get_Received_data;
4151
4152 --========================================================================
4153 -- PROCEDURE : rpt_get_Int_data PUBLIC ,
4154 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
4155 -- COMMENT : get the Component data into jmf_shikyu_cfr_rpt_temp with
4156 -- RPT_DATA_TYPE = CFR_INT_COMPONENT
4157 -- PRE-COND :
4158 -- EXCEPTIONS:
4159 --========================================================================
4160 PROCEDURE rpt_get_Int_data(p_rpt_mode IN VARCHAR2) IS
4161 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_Int_data';
4162
4163 BEGIN
4164 -- **** for debug information in readonly UT environment.--- begin ****
4165 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4166 ,p_api_name => G_MODULE_PREFIX || l_api_name
4167 ,p_message => 'begin:');
4168 -- **** for debug information in readonly UT environment.--- end ****
4169
4170 IF g_fnd_debug = 'Y'
4171 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4172 THEN
4173 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
4174 ,g_module_prefix || l_api_name
4175 ,'.begin');
4176 END IF;
4177
4178 -- get the data for internal report
4179 INSERT INTO jmf_shikyu_cfr_rpt_temp
4180 (rpt_mode
4181 ,rpt_data_type
4182 ,oem_inv_org_id
4183 ,oem_inv_org_code
4184 ,oem_inv_org_name
4185 ,oem_inv_org_address
4186 ,supplier_id
4187 ,supplier_name
4188 ,site_id
4189 ,site_code
4190 ,site_address
4191 ,tp_inv_org_id
4192 ,tp_inv_org_code
4193 ,project_id
4194 ,project_num
4195 ,task_id
4196 ,task_num
4197 ,item_id
4198 ,item_number
4199 ,item_description
4200 ,estimated_qty
4201 ,primary_uom
4202 ,shikyu_price
4203 ,currency_code
4204 ,uom
4205 ,item_cost
4206 ,functional_currency
4207 ,value1 --Qty in Primary UOM
4208 ,value2 --SHIKYU Price in Pri UOM and Pri Currency
4209 ,value3) --SHIKYU Cost in Pri UOM and Pri Currency
4210 --value SUM(temp.estimated_qty) are supposed to onhand_quantity at item/price level,but got incorrect quantity.
4211 --got incorrect onhand quantity due to use inapposite group.
4212 --updated select statement to add transaction_id into group by to get correct onhand quantity.
4213 /*
4214 TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
4215 text="--updated for fix bug 5231233 Begin"
4216 */
4217 /* SELECT p_rpt_mode rpt_mode
4218 ,CFR_INT_COMPONENT rpt_data_type
4219 ,jscrt.oem_inv_org_id Iss_oem_inv_org_id
4220 ,jscrt.oem_inv_org_code Iss_oem_inv_org_code
4221 ,haou.name Iss_oem_inv_org_name
4222 ,jscrt.oem_inv_org_address Iss_oem_inv_org_address
4223 ,jscrt.supplier_id Iss_supplier_id
4224 ,jscrt.supplier_name Iss_supplier_name
4225 ,jscrt.site_id Iss_site_id
4226 ,jscrt.site_code Iss_site_code
4227 ,jscrt.site_address Iss_site_address
4228 ,jscrt.tp_inv_org_id Iss_tp_inv_org_id
4229 ,jscrt.tp_inv_org_code Iss_tp_inv_org_code
4230 ,jscrt.project_id Iss_project_id
4231 ,jscrt.project_num Iss_project_num
4232 ,jscrt.task_id Iss_task_id
4233 ,jscrt.task_num Iss_task_num
4234 ,jscrt.item_id Iss_item_id
4235 ,jscrt.item_number Iss_item_number
4236 ,jscrt.item_description Iss_item_description
4237 ,SUM(jscrt.estimated_qty) Iss_estimated_qty_Sum
4238 ,jscrt.primary_uom Iss_primary_uom
4239 ,jscrt.shikyu_price Iss_shikyu_price
4240 ,jscrt.currency_code Iss_currency_code
4241 ,jscrt.uom Iss_uom
4242 ,jscrt.item_cost Iss_item_cost
4243 ,jscrt.functional_currency Iss_functional_currency
4244 ,SUM(jscrt.value1) Iss_estimated_qty_Sum_Pri
4245 ,jscrt.value2 Iss_SHIKYU_Price_PriU
4246 ,jscrt.value3 Iss_SHIKYU_Cost_PriU
4247 FROM jmf_shikyu_cfr_rpt_temp jscrt
4248 ,HR_ALL_ORGANIZATION_UNITS haou
4249 WHERE jscrt.oem_inv_org_id = haou.organization_id
4250 AND jscrt.rpt_data_type = CFR_CRUDE_DATA
4251 GROUP BY jscrt.oem_inv_org_id
4252 ,jscrt.oem_inv_org_code
4253 ,haou.name
4254 ,jscrt.oem_inv_org_address
4255 ,jscrt.supplier_id
4256 ,jscrt.supplier_name
4257 ,jscrt.site_id
4258 ,jscrt.site_code
4259 ,jscrt.site_address
4260 ,jscrt.tp_inv_org_id
4261 ,jscrt.tp_inv_org_code
4262 ,jscrt.project_id
4263 ,jscrt.project_num
4264 ,jscrt.task_id
4265 ,jscrt.task_num
4266 ,jscrt.item_id
4267 ,jscrt.item_number
4268 ,jscrt.item_description
4269 ,jscrt.primary_uom
4270 ,jscrt.shikyu_price
4271 ,jscrt.currency_code
4272 ,jscrt.uom
4273 ,jscrt.item_cost
4274 ,jscrt.functional_currency
4275 ,jscrt.value2
4276 ,jscrt.value3
4277 ;
4278 */
4279 SELECT rpt_temp.rpt_mode
4280 ,rpt_temp.rpt_data_type
4281 ,rpt_temp.oem_inv_org_id Iss_oem_inv_org_id
4282 ,rpt_temp.oem_inv_org_code Iss_oem_inv_org_code
4283 ,rpt_temp.oem_inv_org_name Iss_oem_inv_org_name
4284 ,rpt_temp.oem_inv_org_address Iss_oem_inv_org_address
4285 ,rpt_temp.supplier_id Iss_supplier_id
4286 ,rpt_temp.supplier_name Iss_supplier_name
4287 ,rpt_temp.site_id Iss_site_id
4288 ,rpt_temp.site_code Iss_site_code
4289 ,rpt_temp.site_address Iss_site_address
4290 ,rpt_temp.tp_inv_org_id Iss_tp_inv_org_id
4291 ,rpt_temp.tp_inv_org_code Iss_tp_inv_org_code
4292 ,rpt_temp.project_id Iss_project_id
4293 ,rpt_temp.project_num Iss_project_num
4294 ,rpt_temp.task_id Iss_task_id
4295 ,rpt_temp.task_num Iss_task_num
4296 ,rpt_temp.item_id Iss_item_id
4297 ,rpt_temp.item_number Iss_item_number
4298 ,rpt_temp.item_description Iss_item_description
4299 ,SUM(rpt_temp.estimated_qty) Iss_estimated_qty_Sum
4300 ,rpt_temp.primary_uom Iss_primary_uom
4301 ,rpt_temp.shikyu_price Iss_shikyu_price
4302 ,rpt_temp.currency_code Iss_currency_code
4303 ,rpt_temp.uom Iss_uom
4304 ,rpt_temp.item_cost Iss_item_cost
4305 ,rpt_temp.functional_currency Iss_functional_currency
4306 ,SUM(rpt_temp.value1) Iss_estimated_qty_Sum_Pri
4307 ,rpt_temp.value2 Iss_SHIKYU_Price_PriU
4308 ,rpt_temp.value3 Iss_SHIKYU_Cost_PriU
4309 FROM (
4310 SELECT p_rpt_mode rpt_mode
4311 ,CFR_INT_COMPONENT rpt_data_type
4312 ,jscrt.oem_inv_org_id oem_inv_org_id
4313 ,jscrt.oem_inv_org_code oem_inv_org_code
4314 ,haou.name oem_inv_org_name
4315 ,jscrt.oem_inv_org_address oem_inv_org_address
4316 ,jscrt.supplier_id supplier_id
4317 ,jscrt.supplier_name supplier_name
4318 ,jscrt.site_id site_id
4319 ,jscrt.site_code site_code
4320 ,jscrt.site_address site_address
4321 ,jscrt.tp_inv_org_id tp_inv_org_id
4322 ,jscrt.tp_inv_org_code tp_inv_org_code
4323 ,jscrt.project_id project_id
4324 ,jscrt.project_num project_num
4325 ,jscrt.task_id task_id
4326 ,jscrt.task_num task_num
4327 ,jscrt.item_id item_id
4328 ,jscrt.item_number item_number
4329 ,jscrt.item_description item_description
4330 ,jscrt.estimated_qty estimated_qty
4331 ,jscrt.primary_uom primary_uom
4332 ,jscrt.shikyu_price shikyu_price
4333 ,jscrt.currency_code currency_code
4334 ,jscrt.uom uom
4335 ,jscrt.item_cost item_cost
4336 ,jscrt.functional_currency functional_currency
4337 ,jscrt.value1 value1
4338 ,jscrt.value2 value2
4339 ,jscrt.value3 value3
4340 ,jscrt.transaction_id transaction_id
4341 FROM jmf_shikyu_cfr_rpt_temp jscrt
4342 ,HR_ALL_ORGANIZATION_UNITS_TL haou
4343 WHERE jscrt.oem_inv_org_id = haou.organization_id
4344 AND jscrt.rpt_data_type = CFR_CRUDE_DATA
4345 AND haou.language = USERENV('LANG')
4346 GROUP BY jscrt.rpt_mode
4347 ,jscrt.oem_inv_org_id
4348 ,jscrt.oem_inv_org_code
4349 ,haou.name
4350 ,jscrt.oem_inv_org_address
4351 ,jscrt.supplier_id
4352 ,jscrt.supplier_name
4353 ,jscrt.site_id
4354 ,jscrt.site_code
4355 ,jscrt.site_address
4356 ,jscrt.tp_inv_org_id
4357 ,jscrt.tp_inv_org_code
4358 ,jscrt.project_id
4359 ,jscrt.project_num
4360 ,jscrt.task_id
4361 ,jscrt.task_num
4362 ,jscrt.item_id
4363 ,jscrt.item_number
4364 ,jscrt.item_description
4365 ,jscrt.primary_uom
4366 ,jscrt.shikyu_price
4367 ,jscrt.currency_code
4368 ,jscrt.uom
4369 ,jscrt.item_cost
4370 ,jscrt.functional_currency
4371 ,jscrt.value2
4372 ,jscrt.value3
4373 ,jscrt.transaction_id
4374 ,jscrt.estimated_qty
4375 ,jscrt.value1
4376 ) rpt_temp
4377 GROUP BY rpt_temp.rpt_mode
4378 ,rpt_temp.rpt_data_type
4379 ,rpt_temp.oem_inv_org_id
4380 ,rpt_temp.oem_inv_org_code
4381 ,rpt_temp.oem_inv_org_name
4382 ,rpt_temp.oem_inv_org_address
4383 ,rpt_temp.supplier_id
4384 ,rpt_temp.supplier_name
4385 ,rpt_temp.site_id
4386 ,rpt_temp.site_code
4387 ,rpt_temp.site_address
4388 ,rpt_temp.tp_inv_org_id
4389 ,rpt_temp.tp_inv_org_code
4390 ,rpt_temp.project_id
4391 ,rpt_temp.project_num
4392 ,rpt_temp.task_id
4393 ,rpt_temp.task_num
4394 ,rpt_temp.item_id
4395 ,rpt_temp.item_number
4396 ,rpt_temp.item_description
4397 ,rpt_temp.primary_uom
4398 ,rpt_temp.shikyu_price
4399 ,rpt_temp.currency_code
4400 ,rpt_temp.uom
4401 ,rpt_temp.item_cost
4402 ,rpt_temp.functional_currency
4403 ,rpt_temp.value2
4404 ,rpt_temp.value3;
4405 --updated for fix bug #5231233 End
4406
4407 --seems the SUM(temp.estimated_qty) is not the onhand quantity.
4408 --add for fix bug 4997302 Begin
4409 --update the onhand quantity to the ESTIMATED_QTY column, assume the same price and cost, need track
4410 /*
4411 TODO: owner="sunwa" created="2006-1-26"
4412 text="--add for fix bug 4997302 Begin Internal"
4413 */
4414 -- modify the onhand quantity, for
4415 -- As the SUM(temp.estimated_qty) is correct by fixing bug #5231233,sql statement below should be updated
4416 /*
4417 TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
4418 text="--updated for fix bug 5231233 Begin Internal"
4419 */
4420
4421 -- Deleted Update to fix bug 5665445 for incorrect onhand Qty in Secondary UOM case
4422 /* UPDATE JMF_SHIKYU_CFR_RPT_TEMP jscrt
4423 SET jscrt.estimated_qty = PO_UOM_S.po_uom_convert_p(jscrt.primary_uom
4424 ,jscrt.uom
4425 ,jscrt.item_id) *jscrt.ESTIMATED_QTY,
4426 (SELECT jscmt.primary_unconsumed_quantity
4427 FROM jmf_shikyu_cfr_mid_temp jscmt
4428 WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
4429 AND jscmt.tp_inv_org_id =
4430 jscrt.tp_inv_org_id
4431 AND jscmt.item_id = jscrt.item_id),
4432 jscrt.value1 = (SELECT jscmt.primary_unconsumed_quantity
4433 FROM jmf_shikyu_cfr_mid_temp jscmt
4434 WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
4435 AND jscmt.tp_inv_org_id =
4436 jscrt.tp_inv_org_id
4437 AND jscmt.item_id = jscrt.item_id)
4438 jscrt.value1 = jscrt.ESTIMATED_QTY
4439 --updated for fix bug 5231233 End Internal
4440 WHERE jscrt.rpt_data_type = CFR_INT_COMPONENT;
4441 -- modify the cost and price
4442 */
4443 --add for fix bug 4997302 Begin
4444
4445 COMMIT; -- for debug on UT ?????
4446 IF g_fnd_debug = 'Y' AND
4447 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4448 THEN
4449 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
4450 , g_module_prefix || l_api_name || '.end'
4451 , NULL);
4452 END IF;
4453 EXCEPTION
4454 WHEN NO_DATA_FOUND THEN
4455 -- raise log message;
4456 NULL;
4457
4458 WHEN OTHERS THEN
4459 IF g_fnd_debug = 'Y'
4460 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4461 THEN
4462 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
4463 ,G_MODULE_PREFIX || l_api_name || '.execption'
4464 ,NULL);
4465 END IF;
4466 -- **** for debug information in readonly UT environment.--- begin ****
4467 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4468 ,p_api_name => G_MODULE_PREFIX || l_api_name
4469 ,p_message => SQLERRM);
4470 -- **** for debug information in readonly UT environment.--- end ****
4471
4472 END rpt_get_Int_data;
4473
4474 --========================================================================
4475 -- PROCEDURE : rpt_get_SubPO_data_Onhand PUBLIC ,
4476 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
4477 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
4478 -- These subPOs can affect onhand quantity in MP inventory.
4479 -- RPT_DATA_TYPE = CFR_EXT_SUBPO_AFT_ONHAND
4480 -- PRE-COND :
4481 -- EXCEPTIONS:
4482 --========================================================================
4483 PROCEDURE rpt_get_SubPO_data_Onhand(p_rpt_mode IN VARCHAR2) IS
4484 l_api_name CONSTANT VARCHAR2(30) := 'rpt_get_SubPO_data_Onhand';
4485
4486 BEGIN
4487 -- **** for debug information in readonly UT environment.--- begin ****
4488 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4489 ,p_api_name => G_MODULE_PREFIX || l_api_name
4490 ,p_message => 'begin:');
4491 -- **** for debug information in readonly UT environment.--- end ****
4492
4493 IF g_fnd_debug = 'Y'
4494 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4495 THEN
4496 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
4497 ,g_module_prefix || l_api_name
4498 ,'.begin');
4499 END IF;
4500
4501 INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
4502 (RPT_MODE
4503 ,RPT_DATA_TYPE
4504 ,oem_inv_org_id
4505 ,supplier_id
4506 ,site_id
4507 ,tp_inv_org_id
4508 ,item_id
4509 ,shikyu_price
4510 ,currency_code
4511 ,uom
4512 ,project_id
4513 ,task_id
4514 ,primary_uom
4515 ,subpo_header_id
4516 ,subpo_number
4517 ,subpo_line_num
4518 ,subpo_release_num
4519 ,subpo_shipment_num
4520 ,OSA_ITEM_ID
4521 ,OSA_ITEM_NUMBER
4522 ,OSA_ITEM_DESCRIPTION
4523 ,REQUESTED_COMP_QTY
4524 ,ISSUED_COMP_QTY)
4525 SELECT DISTINCT p_rpt_mode RPT_MODE --temp.rpt_mode
4526 ,CFR_EXT_SUBPO_AFT_ONHAND RPT_DATA_TYPE
4527 ,temp.oem_inv_org_id shikyu_oem_inv_org_id
4528 ,temp.supplier_id shikyu_supplier_id
4529 ,temp.site_id shikyu_site_id
4530 ,temp.tp_inv_org_id shikyu_tp_inv_org_id
4531 ,temp.item_id shikyu_item_id
4532 ,temp.shikyu_price shikyu_price
4533 ,temp.currency_code shikyu_currency
4534 ,temp.uom shikyu_uom
4535 ,temp.project_id
4536 ,temp.task_id
4537 ,temp.primary_uom shikyu_primary_uom
4538 ,temp.subpo_header_id
4539 ,temp.subpo_number
4540 ,temp.subpo_line_num
4541 ,temp.subpo_release_num
4542 ,temp.subpo_shipment_num
4543 ,jso.osa_item_id
4544 ,msibk.concatenated_segments
4545 ,msibk.description
4546 ,wro.required_quantity
4547 ,wro.quantity_issued
4548 FROM JMF_SHIKYU_CFR_RPT_TEMP temp
4549 ,jmf_subcontract_orders jso
4550 ,wip_requirement_operations wro
4551 ,MTL_SYSTEM_ITEMS_B_KFV msibk
4552 WHERE temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
4553 AND temp.subpo_shipment_id = jso.subcontract_po_shipment_id
4554 AND jso.oem_organization_id = msibk.organization_id
4555 AND jso.osa_item_id = msibk.inventory_item_id
4556 AND jso.tp_organization_id = wro.organization_id
4557 AND jso.wip_entity_id = wro.wip_entity_id
4558 AND wro.operation_seq_num = 1
4559 AND wro.repetitive_schedule_id IS NULL
4560 AND temp.item_id = wro.inventory_item_id;
4561 COMMIT; -- for debug on UT ?????
4562 IF g_fnd_debug = 'Y' AND
4563 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4564 THEN
4565 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
4566 , g_module_prefix || l_api_name || '.end'
4567 , NULL);
4568 END IF;
4569 EXCEPTION
4570 WHEN NO_DATA_FOUND THEN
4571 -- raise log message;
4572 NULL;
4573
4574 WHEN OTHERS THEN
4575 IF g_fnd_debug = 'Y'
4576 AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4577 THEN
4578 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
4579 ,G_MODULE_PREFIX || l_api_name || '.execption'
4580 ,NULL);
4581 END IF;
4582 -- **** for debug information in readonly UT environment.--- begin ****
4583 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => 'FND_LOG.STRING'
4584 ,p_api_name => G_MODULE_PREFIX || l_api_name
4585 ,p_message => 'WHEN OTHERS THEN');
4586 -- **** for debug information in readonly UT environment.--- end ****
4587
4588 END rpt_get_SubPO_data_Onhand;
4589
4590 --========================================================================
4591 -- PROCEDURE : rpt_debug_show_mid_data PUBLIC ,
4592 -- PARAMETERS: p_row_type row type in jmf_shikyu_cfr_mid_temp
4593 -- p_output_to the parameter for debug_output
4594 -- COMMENT : show the data in temp table jmf_shikyu_cfr_mid_temp
4595 -- using debug_output
4596 -- PRE-COND :
4597 -- EXCEPTIONS:
4598 --========================================================================
4599 PROCEDURE rpt_debug_show_mid_data
4600 (
4601 p_row_type IN VARCHAR2
4602 ,p_output_to IN VARCHAR2
4603 ) IS
4604 l_api_name CONSTANT VARCHAR2(30) := 'rpt_debug_show_mid_data';
4605
4606 CURSOR l_cur_get_mid_temp_data(lp_row_type jmf_shikyu_cfr_mid_temp.row_type%TYPE) IS
4607 SELECT row_type
4608 ,shikyu_id
4609 ,tp_inv_org_id
4610 ,item_id
4611 ,uom
4612 ,quantity
4613 ,primary_uom
4614 ,primary_unallocated_quantity
4615 ,primary_unconsumed_quantity
4616 ,project_id
4617 ,task_id
4618 ,oem_inv_org_id
4619 ,supplier_id
4620 ,site_id
4621 ,ou_id
4622 ,get_rcv_flag
4623 ,get_rep_flag
4624 FROM jmf_shikyu_cfr_mid_temp
4625 WHERE (lp_row_type IS NULL)
4626 OR (row_type = lp_row_type)
4627 ORDER BY row_type
4628 ,shikyu_id
4629 ,item_id;
4630
4631 l_row_type jmf_shikyu_cfr_mid_temp.row_type%TYPE;
4632 l_shikyu_id jmf_shikyu_cfr_mid_temp.shikyu_id%TYPE;
4633 l_tp_inv_org_id jmf_shikyu_cfr_mid_temp.tp_inv_org_id%TYPE;
4634 l_item_id jmf_shikyu_cfr_mid_temp.item_id%TYPE;
4635 l_uom jmf_shikyu_cfr_mid_temp.uom%TYPE;
4636 l_quantity jmf_shikyu_cfr_mid_temp.quantity%TYPE;
4637 l_primary_uom jmf_shikyu_cfr_mid_temp.primary_uom%TYPE;
4638 l_primary_unallocated_quantity jmf_shikyu_cfr_mid_temp.primary_unallocated_quantity%TYPE;
4639 l_primary_unconsumed_quantity jmf_shikyu_cfr_mid_temp.primary_unconsumed_quantity%TYPE;
4640 l_project_id jmf_shikyu_cfr_mid_temp.project_id%TYPE;
4641 l_task_id jmf_shikyu_cfr_mid_temp.task_id%TYPE;
4642 l_oem_inv_org_id jmf_shikyu_cfr_mid_temp.oem_inv_org_id%TYPE;
4643 l_supplier_id jmf_shikyu_cfr_mid_temp.supplier_id%TYPE;
4644 l_site_id jmf_shikyu_cfr_mid_temp.site_id%TYPE;
4645 l_ou_id jmf_shikyu_cfr_mid_temp.ou_id%TYPE;
4646 l_get_rcv_flag jmf_shikyu_cfr_mid_temp.get_rcv_flag%TYPE;
4647 l_get_rep_flag jmf_shikyu_cfr_mid_temp.get_rep_flag%TYPE;
4648
4649 BEGIN
4650 -- **** for debug information in readonly UT environment.--- begin ****
4651 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4652 ,p_api_name => G_MODULE_PREFIX ||
4653 l_api_name
4654 ,p_message => '==jmf_shikyu_cfr_mid_temp data Begin==');
4655 -- **** for debug information in readonly UT environment.--- end ****
4656 OPEN l_cur_get_mid_temp_data(p_row_type);
4657 LOOP
4658 --print the data in l_cur_get_mid_temp_data
4659 FETCH l_cur_get_mid_temp_data
4660 INTO l_row_type
4661 ,l_shikyu_id
4662 ,l_tp_inv_org_id
4663 ,l_item_id
4664 ,l_uom
4665 ,l_quantity
4666 ,l_primary_uom
4667 ,l_primary_unallocated_quantity
4668 ,l_primary_unconsumed_quantity
4669 ,l_project_id
4670 ,l_task_id
4671 ,l_oem_inv_org_id
4672 ,l_supplier_id
4673 ,l_site_id
4674 ,l_ou_id
4675 ,l_get_rcv_flag
4676 ,l_get_rep_flag;
4677
4678 EXIT WHEN l_cur_get_mid_temp_data%NOTFOUND;
4679
4680 -- **** for debug information in readonly UT environment.--- begin ****
4681 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4682 ,p_api_name => G_MODULE_PREFIX ||
4683 l_api_name
4684 ,p_message => 'jmf_shikyu_cfr_mid_temp:' ||
4685 ';row_type[' || l_row_type || ']' ||
4686 ';shikyu_id[' || l_shikyu_id || ']' ||
4687 ';tp_inv_org_id[' || l_tp_inv_org_id || ']' ||
4688 ';item_id[' || l_item_id || ']' ||
4689 ';uom[' || l_uom || ']' ||
4690 ';quantity[' || l_quantity || ']' ||
4691 ';primary_uom[' || l_primary_uom || ']' ||
4692 ';primary_unallocated_quantity[' || l_primary_unallocated_quantity || ']' ||
4693 ';primary_unconsumed_quantity[' || l_primary_unconsumed_quantity || ']' ||
4694 ';project_id[' || l_project_id || ']' ||
4695 ';task_id[' || l_task_id || ']' ||
4696 ';oem_inv_org_id[' || l_oem_inv_org_id || ']' ||
4697 ';supplier_id[' || l_supplier_id || ']' ||
4698 ';site_id[' || l_site_id || ']' ||
4699 ';ou_id[' || l_ou_id || ']' ||
4700 ';get_rcv_flag[' || l_get_rcv_flag || ']' ||
4701 ';get_rep_flag[' || l_get_rep_flag || ']'
4702 );
4703 -- **** for debug information in readonly UT environment.--- end ****
4704
4705 END LOOP; --end loop of l_cur_get_mid_temp_data
4706 CLOSE l_cur_get_mid_temp_data;
4707
4708 -- **** for debug information in readonly UT environment.--- begin ****
4709 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4710 ,p_api_name => G_MODULE_PREFIX ||
4711 l_api_name
4712 ,p_message => '==jmf_shikyu_cfr_mid_temp data end==');
4713 -- **** for debug information in readonly UT environment.--- end ****
4714
4715 IF g_fnd_debug = 'Y' AND
4716 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4717 THEN
4718 fnd_log.STRING( FND_LOG.LEVEL_PROCEDURE
4719 , g_module_prefix || l_api_name || '.end'
4720 , NULL);
4721 END IF;
4722 EXCEPTION
4723 WHEN NO_DATA_FOUND THEN
4724 -- raise log message;
4725 NULL;
4726
4727 WHEN OTHERS THEN
4728 -- **** for debug information in readonly UT environment.--- begin ****
4729 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4730 ,p_api_name => G_MODULE_PREFIX ||
4731 l_api_name ||
4732 '.Exception'
4733 ,p_message => SQLERRM);
4734 -- **** for debug information in readonly UT environment.--- end ****
4735
4736 END rpt_debug_show_mid_data;
4737
4738 --========================================================================
4739 -- PROCEDURE : rpt_debug_show_temp_data PUBLIC ,
4740 -- PARAMETERS: p_rpt_data_type row type in jmf_shikyu_cfr_rpt_temp
4741 -- p_output_to the parameter for debug_output
4742 -- COMMENT : show the data in temp table jmf_shikyu_cfr_rpt_temp
4743 -- using debug_output
4744 -- PRE-COND :
4745 -- EXCEPTIONS:
4746 --========================================================================
4747 PROCEDURE rpt_debug_show_temp_data
4748 (
4749 p_rpt_data_type IN VARCHAR2
4750 ,p_output_to IN VARCHAR2
4751 ) IS
4752 l_api_name CONSTANT VARCHAR2(30) := 'rpt_debug_show_temp_data';
4753
4754 CURSOR l_cur_get_temp_data(lrpt_data_type jmf_shikyu_cfr_rpt_temp.rpt_data_type%TYPE) IS
4755 SELECT rpt_mode
4756 ,rpt_data_type
4757 ,oem_inv_org_id
4758 ,oem_inv_org_code
4759 ,oem_inv_org_name
4760 ,oem_inv_org_address
4761 ,supplier_id
4762 ,supplier_name
4763 ,site_id
4764 ,site_code
4765 ,site_address
4766 ,tp_inv_org_id
4767 ,tp_inv_org_code
4768 ,project_id
4769 ,project_num
4770 ,task_id
4771 ,task_num
4772 ,item_id
4773 ,item_number
4774 ,item_description
4775 ,estimated_qty
4776 ,primary_uom
4777 ,shikyu_price
4778 ,currency_code
4779 ,uom
4780 ,item_cost
4781 ,functional_currency
4782 ,value1 --Qty in Primary UOM
4783 ,value2 --SHIKYU Price in Pri UOM and Pri Currency
4784 ,value3
4785 FROM jmf_shikyu_cfr_rpt_temp
4786 WHERE (lrpt_data_type IS NULL)
4787 OR (rpt_data_type = lrpt_data_type)
4788 ORDER BY rpt_mode
4789 ,rpt_data_type
4790 ,oem_inv_org_id
4791 ,supplier_id
4792 ,site_id;
4793
4794 l_rpt_mode jmf_shikyu_cfr_rpt_temp.rpt_mode%TYPE;
4795 l_rpt_data_type jmf_shikyu_cfr_rpt_temp.rpt_data_type%TYPE;
4796 l_oem_inv_org_id jmf_shikyu_cfr_rpt_temp.oem_inv_org_id%TYPE;
4797 l_oem_inv_org_code jmf_shikyu_cfr_rpt_temp.oem_inv_org_code%TYPE;
4798 l_oem_inv_org_name jmf_shikyu_cfr_rpt_temp.oem_inv_org_name%TYPE;
4799 l_oem_inv_org_address jmf_shikyu_cfr_rpt_temp.oem_inv_org_address%TYPE;
4800 l_supplier_id jmf_shikyu_cfr_rpt_temp.supplier_id%TYPE;
4801 l_supplier_name jmf_shikyu_cfr_rpt_temp.supplier_name%TYPE;
4802 l_site_id jmf_shikyu_cfr_rpt_temp.site_id%TYPE;
4803 l_site_code jmf_shikyu_cfr_rpt_temp.site_code%TYPE;
4804 l_site_address jmf_shikyu_cfr_rpt_temp.site_address%TYPE;
4805 l_tp_inv_org_id jmf_shikyu_cfr_rpt_temp.tp_inv_org_id%TYPE;
4806 l_tp_inv_org_code jmf_shikyu_cfr_rpt_temp.tp_inv_org_code%TYPE;
4807 l_project_id jmf_shikyu_cfr_rpt_temp.project_id%TYPE;
4808 l_project_num jmf_shikyu_cfr_rpt_temp.project_num%TYPE;
4809 l_task_id jmf_shikyu_cfr_rpt_temp.task_id%TYPE;
4810 l_task_num jmf_shikyu_cfr_rpt_temp.task_num%TYPE;
4811 l_item_id jmf_shikyu_cfr_rpt_temp.item_id%TYPE;
4812 l_item_number jmf_shikyu_cfr_rpt_temp.item_number%TYPE;
4813 l_item_description jmf_shikyu_cfr_rpt_temp.item_description%TYPE;
4814 l_estimated_qty jmf_shikyu_cfr_rpt_temp.estimated_qty%TYPE;
4815 l_primary_uom jmf_shikyu_cfr_rpt_temp.primary_uom%TYPE;
4816 l_shikyu_price jmf_shikyu_cfr_rpt_temp.shikyu_price%TYPE;
4817 l_currency_code jmf_shikyu_cfr_rpt_temp.currency_code%TYPE;
4818 l_uom jmf_shikyu_cfr_rpt_temp.uom%TYPE;
4819 l_item_cost jmf_shikyu_cfr_rpt_temp.item_cost%TYPE;
4820 l_functional_currency jmf_shikyu_cfr_rpt_temp.functional_currency%TYPE;
4821 l_value1 jmf_shikyu_cfr_rpt_temp.value1%TYPE;
4822 l_value2 jmf_shikyu_cfr_rpt_temp.value2%TYPE;
4823 l_value3 jmf_shikyu_cfr_rpt_temp.value3%TYPE;
4824
4825 BEGIN
4826 -- **** for debug information in readonly UT environment.--- begin ****
4827 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4828 ,p_api_name => G_MODULE_PREFIX ||
4829 l_api_name
4830 ,p_message => '==jmf_shikyu_cfr_rpt_temp data Begin==');
4831 -- **** for debug information in readonly UT environment.--- end ****
4832 OPEN l_cur_get_temp_data(p_rpt_data_type);
4833 LOOP
4834 --print the data in l_cur_get_mid_temp_data
4835 FETCH l_cur_get_temp_data
4836 INTO l_rpt_mode
4837 ,l_rpt_data_type
4838 ,l_oem_inv_org_id
4839 ,l_oem_inv_org_code
4840 ,l_oem_inv_org_name
4841 ,l_oem_inv_org_address
4842 ,l_supplier_id
4843 ,l_supplier_name
4844 ,l_site_id
4845 ,l_site_code
4846 ,l_site_address
4847 ,l_tp_inv_org_id
4848 ,l_tp_inv_org_code
4849 ,l_project_id
4850 ,l_project_num
4851 ,l_task_id
4852 ,l_task_num
4853 ,l_item_id
4854 ,l_item_number
4855 ,l_item_description
4856 ,l_estimated_qty
4857 ,l_primary_uom
4858 ,l_shikyu_price
4859 ,l_currency_code
4860 ,l_uom
4861 ,l_item_cost
4862 ,l_functional_currency
4863 ,l_value1
4864 ,l_value2
4865 ,l_value3;
4866
4867 EXIT WHEN l_cur_get_temp_data%NOTFOUND;
4868
4869 -- **** for debug information in readonly UT environment.--- begin ****
4870 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4871 ,p_api_name => G_MODULE_PREFIX ||
4872 l_api_name
4873 ,p_message => 'jmf_shikyu_cfr_rpt_temp:' ||
4874 ';rpt_mode[' ||
4875 l_rpt_mode || ']' ||
4876 ';rpt_data_type[' ||
4877 l_rpt_data_type || ']' ||
4878 ';oem_inv_org_id[' ||
4879 l_oem_inv_org_id || ']' ||
4880 ';oem_inv_org_code[' ||
4881 l_oem_inv_org_code || ']' ||
4882 ';oem_inv_org_name[' ||
4883 l_oem_inv_org_name || ']' ||
4884 ';oem_inv_org_address[' ||
4888 ';supplier_name[' ||
4885 l_oem_inv_org_address || ']' ||
4886 ';supplier_id[' ||
4887 l_supplier_id || ']' ||
4889 l_supplier_name || ']' ||
4890 ';site_id[' ||
4891 l_site_id || ']' ||
4892 ';site_code[' ||
4893 l_site_code || ']' ||
4894 ';site_address[' ||
4895 l_site_address || ']' ||
4896 ';tp_inv_org_id[' ||
4897 l_tp_inv_org_id || ']' ||
4898 ';tp_inv_org_code[' ||
4899 l_tp_inv_org_code || ']' ||
4900 ';project_id[' ||
4901 l_project_id || ']' ||
4902 ';project_num[' ||
4903 l_project_num || ']' ||
4904 ';task_id[' ||
4905 l_task_id || ']' ||
4906 ';task_num[' ||
4907 l_task_num || ']' ||
4908 ';item_id[' ||
4909 l_item_id || ']' ||
4910 ';item_number[' ||
4911 l_item_number || ']' ||
4912 ';item_description[' ||
4913 l_item_description || ']' ||
4914 ';estimated_qty[' ||
4915 l_estimated_qty || ']' ||
4916 ';primary_uom[' ||
4917 l_primary_uom || ']' ||
4918 ';shikyu_price[' ||
4919 l_shikyu_price || ']' ||
4920 ';currency_code[' ||
4921 l_currency_code || ']' ||
4922 ';uom[' || l_uom || ']' ||
4923 ';item_cost[' ||
4924 l_item_cost || ']' ||
4925 ';functional_currency[' ||
4926 l_functional_currency || ']' ||
4927 ';value1[' || l_value1 || ']' ||
4928 ';value2[' || l_value2 || ']' ||
4929 ';value3[' || l_value3 || ']');
4930 -- **** for debug information in readonly UT environment.--- end ****
4931
4932 END LOOP; --end loop of l_cur_get_mid_temp_data
4933 CLOSE l_cur_get_temp_data;
4934
4935 -- **** for debug information in readonly UT environment.--- begin ****
4936 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4937 ,p_api_name => G_MODULE_PREFIX ||
4938 l_api_name
4939 ,p_message => '==jmf_shikyu_cfr_rpt_temp data end==');
4940 -- **** for debug information in readonly UT environment.--- end ****
4941
4942 IF g_fnd_debug = 'Y'
4943 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4944 THEN
4945 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
4946 ,g_module_prefix || l_api_name || '.end'
4947 ,NULL);
4948 END IF;
4949 EXCEPTION
4950 WHEN NO_DATA_FOUND THEN
4951 -- raise log message;
4952 NULL;
4953
4954 WHEN OTHERS THEN
4955 -- **** for debug information in readonly UT environment.--- begin ****
4956 JMF_SHIKYU_RPT_UTIL.debug_output(p_output_to => p_output_to
4957 ,p_api_name => G_MODULE_PREFIX ||
4958 l_api_name ||
4959 '.Exception'
4960 ,p_message => SQLERRM);
4961 -- **** for debug information in readonly UT environment.--- end ****
4962
4963 END rpt_debug_show_temp_data;
4964
4965 END JMF_SHIKYU_RPT_CFR_PVT;