1 PACKAGE JMF_SHIKYU_RPT_CFR_PVT AS
2 --$Header: JMFVCFRS.pls 120.12 2006/11/22 01:01:34 vchu noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFVCFRS.pls |
10 --| |
11 --| DESCRIPTION: Specification file of the package for creating |
12 --| temporary data for SHIKYU Confirmation Report. |
13 --| |
14 --| HISTORY: |
15 --| 15-APR-2005 shu Created. |
16 --| 07-DEC-2005 shu added procedures rpt_get_xxx for report data |
17 --| 19-JAN-2006 shu changed parameter p_currency_conversion_date |
18 --| from date to varchar2; |
19 --| added rpt_debug_show_mid_data procedure |
20 --| 19-JUN-2006 amy updated to fix bug 5391412 |
21 --| Renamed original procedure rpt_get_SubPO_data |
22 --| as rpt_get_SubPO_data_Onhand for future use |
23 --| Added new procedure rpt_get_SubPO_data |
24 --| 17-NOV-2006 amy updated procedure add_data_to_cfr_temp |
25 --| and rpt_get_UnReceived_data to fix bug 5583680|
26 --| 21-NOV-2006 vchu Bug fix for 5665334: Changed the signature |
27 --| of cfr_before_report_trigger to pass in the |
28 --| ID of the FROM and TO OEM Organization, |
29 --| instead of the name. Also added a new helper |
30 --| procedure to get the name of an Inventory |
31 --| organization given the ID. |
32 --+===========================================================================+
33
34 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_RPT_CFR_PVT';
35
36 -- Public function and procedure declarations
37
38 -- Bug 5665334
39 --========================================================================
40 -- PROCEDURE : get_organization_name PUBLIC
41 -- PARAMETERS: p_organization_id Inventory Organization ID
42 -- : x_organization_name Organization Name to be returned
43 -- COMMENT : The procedure returns the name of an Inventory Organization
44 -- if the input ID parameter was valid.
45 --========================================================================
46 PROCEDURE get_organization_name
47 ( p_organization_id IN NUMBER
48 , x_organization_name OUT NOCOPY VARCHAR2
49 );
50
51 --========================================================================
52 -- PROCEDURE : cfr_before_report_trigger PUBLIC
53 -- PARAMETERS: p_rpt_mode the report mode: External/Internal report
54 -- : p_ou_id Operating unit id
55 -- : p_supplier_name_from the supplier name from
56 -- : p_supplier_name_to the supplier name to
57 -- : p_supplier_site_code_from the supplier site code from
58 -- : p_supplier_site_code_to the supplier site code to
59 -- : p_oem_inv_org_name_from oem inventory org name from
60 -- : p_oem_inv_org_name_to oem inventory org name to
61 -- : p_item_number_from item number from
62 -- : p_item_number_to item number to
63 -- : p_days_received received after the days ago
64 -- : p_sort_by By Supplier/Site or By Item,
65 -- the External report can use only by Supplier/Site
66 -- : p_currency_conversion_type the currency conversion type
67 -- : p_currency_conversion_date the currency conversion date
68 -- COMMENT : this procedure will be called in the before report trigger,
69 -- all the other needed procedures will be called in this procedure
70 -- PRE-COND :
71 -- EXCEPTIONS:
72 --========================================================================
73 PROCEDURE cfr_before_report_trigger
74 (
75 p_rpt_mode IN VARCHAR2
76 ,p_ou_id IN NUMBER
77 ,p_supplier_name_from IN VARCHAR2
78 ,p_supplier_site_code_from IN VARCHAR2
79 ,p_supplier_name_to IN VARCHAR2
80 ,p_supplier_site_code_to IN VARCHAR2
81 ,p_oem_inv_org_id_from IN NUMBER -- Bug 5665334
82 ,p_oem_inv_org_id_to IN NUMBER -- Bug 5665334
83 ,p_item_number_from IN VARCHAR2
84 ,p_item_number_to IN VARCHAR2
85 ,p_days_received IN NUMBER
86 ,p_sort_by IN VARCHAR2
87 ,p_currency_conversion_type IN VARCHAR2
88 ,p_currency_conversion_date IN VARCHAR2
89 ,p_functional_currency IN VARCHAR2
90 );
91
92 --========================================================================
93 -- PROCEDURE : cfr_get_onhand_components PUBLIC
94 -- PARAMETERS: p_onhand_row_type row type id to identify the
95 -- onhand components information
96 -- : p_ou_id Operating unit id
97 -- : p_supplier_name_from the supplier name from
98 -- : p_supplier_site_code_from the supplier site code from
99 -- : p_supplier_name_to the supplier name to
100 -- : p_supplier_site_code_to the supplier site code to
101 -- : p_oem_inv_org_name_from oem inventory org name from
102 -- : p_oem_inv_org_name_to oem inventory org name to
103 -- : p_item_number_from item id from
104 -- : p_item_number_to item id to
105 -- COMMENT : It is used to get all the onhand compoents primary UOM quantity;
106 -- (reference to the condition:''Operating Unit, 'from OEM INV organization' ,'to OEM INV organization',
107 -- 'From supplier','From site','To supplier','To site','From Item','To item')
108 -- PRE-COND :
109 -- EXCEPTIONS:
110 --========================================================================
111 PROCEDURE get_onhand_components
112 (
113 p_onhand_row_type IN NUMBER
114 ,p_ou_id IN NUMBER
115 ,p_supplier_name_from IN VARCHAR2
116 ,p_supplier_site_code_from IN VARCHAR2
117 ,p_supplier_name_to IN VARCHAR2
118 ,p_supplier_site_code_to IN VARCHAR2
119 ,p_oem_inv_org_name_from IN VARCHAR2
120 ,p_oem_inv_org_name_to IN VARCHAR2
121 ,p_item_number_from IN VARCHAR2
122 ,p_item_number_to IN VARCHAR2
123 );
124
125 --========================================================================
126 -- PROCEDURE : get_rpt_confirmation_data PUBLIC
127 -- PARAMETERS: p_onhand_row_type row type id to identify the
128 -- onhand components information
129 -- : p_rep_po_unalloc_row_type replenishment po unalloc row in mid-temp table
130 -- : p_rep_po_unconsumed_row_type replenishment po unconsume row in mid-temp table
131 -- : p_sub_po_unconsumed_row_type subcontract po unconsume row in mid-temp table
132 -- : p_days_received received after the days ago
133 -- : p_sort_by By Supplier/Site or By Item, the External report can use only by Supplier/Site
134 -- : p_currency_conversion_type the currency conversion type
135 -- : p_currency_conversion_date the currency conversion date
136 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
137 -- this procedure is used to get the possilbe rcv_transaction data information
138 -- for those onhand SHIKYU components based on LIFO received date.
139 -- first the unallocated qty,then the allocated but unconsumed qty.
140 -- PRE-COND :
141 -- EXCEPTIONS:
142 --========================================================================
143 PROCEDURE get_rpt_confirmation_data
144 (
145 p_onhand_row_type IN NUMBER
146 ,p_rep_po_unalloc_row_type IN NUMBER
147 ,p_rep_po_unconsumed_row_type IN NUMBER
148 ,p_sub_po_unconsumed_row_type IN NUMBER
149 ,p_rcv_transaction_row_type IN NUMBER
150 ,p_ou_id IN NUMBER
151 ,p_days_received IN NUMBER
152 -- , p_sort_by IN VARCHAR2
153 ,p_currency_conversion_type IN VARCHAR2
154 ,p_currency_conversion_date IN DATE
155 );
156
157 --========================================================================
158 -- PROCEDURE : get_unallocated_components PUBLIC
159 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
160 -- unallocated components information
161 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
162 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
163 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
164 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
165 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
166 -- : p_item_id the item_id got from the onhand info from mid temp table
167 -- : x_need_to_find_pri_qty the need to find quantity under primary UOM
168 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
169 -- this procedure is used to get the possilbe unallocated rcv_transaction data information
170 -- for those onhand SHIKYU components based on LIFO received date.
171 -- PRE-COND :
172 -- EXCEPTIONS:
173 --========================================================================
174 PROCEDURE get_unallocated_components
175 (
176 p_rep_po_unalloc_row_type IN NUMBER
177 ,p_rcv_transaction_row_type IN NUMBER
178 ,p_ou_id IN NUMBER
179 ,p_supplier_id IN NUMBER
180 ,p_supplier_site_id IN NUMBER
181 ,p_oem_inv_org_id IN NUMBER
182 ,p_tp_inv_org_id IN NUMBER
183 ,p_item_id IN NUMBER
184 ,p_project_id IN NUMBER
185 ,p_task_id IN NUMBER
186 ,x_need_to_find_pri_qty IN OUT NOCOPY NUMBER
187 );
188
189 --========================================================================
190 -- PROCEDURE : get_unallocated_rep_po PUBLIC ,get_unallocated_replenishment_po
191 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
192 -- unallocated components information
193 -- : p_ou_id the operating unit id
194 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
195 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
196 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
197 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
198 -- : p_item_id the item_id got from the onhand info from mid temp table
202 -- EXCEPTIONS:
199 -- COMMENT : find the replenishment purchase order that have unallocated receipts for the item
200 -- and insert the result to mid temp table
201 -- PRE-COND :
203 --========================================================================
204 PROCEDURE get_unallocated_rep_po
205 (
206 p_rep_po_unalloc_row_type IN NUMBER
207 ,p_ou_id IN NUMBER
208 ,p_supplier_id IN NUMBER
209 ,p_supplier_site_id IN NUMBER
210 ,p_oem_inv_org_id IN NUMBER
211 ,p_tp_inv_org_id IN NUMBER
212 ,p_item_id IN NUMBER
213 ,p_project_id IN NUMBER
214 ,p_task_id IN NUMBER
215 );
216
217 --========================================================================
218 -- FUNCTION : get_rep_po_residual_unalloc PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
219 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
220 -- unallocated components information
221 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
222 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
223 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
224 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
225 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
226 -- : p_item_id the item_id got from the onhand info from mid temp table
227 -- COMMENT : find the replenishment purchase order residual unallocated quantity for primary uom
228 -- PRE-COND :
229 -- EXCEPTIONS:
233 p_rep_po_unalloc_row_type IN NUMBER
230 --========================================================================
231 FUNCTION get_rep_po_residual_unalloc
232 (
234 ,p_ou_id IN NUMBER
235 ,p_rcv_transaction_id IN NUMBER
236 ,p_supplier_id IN NUMBER
237 ,p_supplier_site_id IN NUMBER
238 ,p_oem_inv_org_id IN NUMBER
239 ,p_tp_inv_org_id IN NUMBER
240 ,p_item_id IN NUMBER
241 ,p_project_id IN NUMBER
242 ,p_task_id IN NUMBER
243 ) RETURN NUMBER;
244
245 --========================================================================
246 -- PROCEDURE : set_rep_po_residual_unalloc PUBLIC ,set_replenishment_po_unallocated quantity for primary uom
247 -- PARAMETERS: p_rep_po_unalloc_row_type row type id to identify the
248 -- unallocated components information
249 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
250 -- : p_rep_po_id row type id to identify the rcv_transaction data
251 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
252 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
253 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
254 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
255 -- : p_item_id the item_id got from the onhand info from mid temp table
256 -- COMMENT : find the replenishment purchase order residual unallocated quantity for primary uom
257 -- PRE-COND :
258 -- EXCEPTIONS:
259 --========================================================================
260 PROCEDURE set_rep_po_residual_unalloc
261 (
262 p_rep_po_unalloc_row_type IN NUMBER
263 ,p_rcv_transaction_row_type IN NUMBER
264 ,p_ou_id IN NUMBER
265 ,p_rcv_transaction_id IN NUMBER
266 ,p_supplier_id IN NUMBER
267 ,p_supplier_site_id IN NUMBER
268 ,p_oem_inv_org_id IN NUMBER
269 ,p_tp_inv_org_id IN NUMBER
270 ,p_item_id IN NUMBER
271 ,p_project_id IN NUMBER
272 ,p_task_id IN NUMBER
273 ,p_new_rep_po_unallocated_pri IN NUMBER
274 );
275
276 --========================================================================
277 -- PROCEDURE : set_rcv_transaction_unalloc PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
278 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
279 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
280 -- : p_rcv_unallocated_pri the rcv unallocated quantity for primary uom
281 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
282 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
283 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
284 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
285 -- : p_item_id the item_id got from the onhand info from mid temp table
286 -- COMMENT : update the replenishment po receive transaction unallocated information for primary uom
287 -- PRE-COND :
288 -- EXCEPTIONS:
289 --========================================================================
290 PROCEDURE set_rcv_transaction_unalloc
291 (
292 p_rcv_row_type IN NUMBER
293 ,p_ou_id IN NUMBER
294 ,p_rcv_transaction_id IN NUMBER
295 ,p_rcv_unallocated_pri IN NUMBER
296 ,p_supplier_id IN NUMBER
297 ,p_supplier_site_id IN NUMBER
298 ,p_oem_inv_org_id IN NUMBER
299 ,p_tp_inv_org_id IN NUMBER
300 ,p_item_id IN NUMBER
301 ,p_project_id IN NUMBER
302 ,p_task_id IN NUMBER
303 );
304
305 --========================================================================
306 -- PROCEDURE : get_unconsumed_components PUBLIC
307 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
308 -- unallocated components information
309 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
310 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
311 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
312 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
313 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
314 -- : p_item_id the item_id got from the onhand info from mid temp table
315 -- : x_need_to_find_pri_qty the need to find quantity under primary UOM
316 -- COMMENT : for each line in the on hand data in the jmf_shikyu_cfr_mid_temp table,
317 -- this procedure is used to get the possilbe allocated but unconsumed rcv_transaction data information
318 -- for those onhand SHIKYU components based on LIFO received date.
319 -- PRE-COND :
320 -- EXCEPTIONS:
321 --========================================================================
322 PROCEDURE get_unconsumed_components
323 (
324 p_sub_po_unconsumed_row_type IN NUMBER
325 ,p_rep_po_unconsumed_row_type IN NUMBER
329 ,p_supplier_site_id IN NUMBER
326 ,p_rcv_transaction_row_type IN NUMBER
327 ,p_ou_id IN NUMBER
328 ,p_supplier_id IN NUMBER
330 ,p_oem_inv_org_id IN NUMBER
331 ,p_tp_inv_org_id IN NUMBER
332 ,p_item_id IN NUMBER
333 ,p_project_id IN NUMBER
334 ,p_task_id IN NUMBER
335 ,x_need_to_find_pri_qty IN OUT NOCOPY NUMBER
336 );
337
338 --========================================================================
339 -- PROCEDURE : get_unconsumed_sub_po PUBLIC ,unconsumed_subcontracting_po
340 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
341 -- unallocated components information
342 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
343 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
344 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
345 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
346 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
347 -- : p_item_id the item_id got from the onhand info from mid temp table
348 -- COMMENT : find the subcontracting purchase order that not fully received
349 -- PRE-COND :
350 -- EXCEPTIONS:
351 --========================================================================
352 PROCEDURE get_unconsumed_sub_po
353 (
354 p_sub_po_unconsumed_row_type IN NUMBER
355 ,p_ou_id IN NUMBER
356 ,p_supplier_id IN NUMBER --need oem supplier id ????
357 ,p_supplier_site_id IN NUMBER --need oem supplier site id ????
358 ,p_oem_inv_org_id IN NUMBER
359 ,p_tp_inv_org_id IN NUMBER
360 ,p_item_id IN NUMBER
361 ,p_project_id IN NUMBER
362 ,p_task_id IN NUMBER
363 );
364
365 --========================================================================
366 -- PROCEDURE : get_unconsumed_rep_po PUBLIC ,get_unconsumed_replenishment_po
367 -- PARAMETERS: p_rep_po_unconsume_row_type row type id to identify the
368 -- unconsumed components information
369 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
370 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
371 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
372 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
373 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
374 -- : p_item_id the item_id got from the onhand info from mid temp table
375 -- COMMENT : find the replenishment purchase order that have unallocated receipts for the item
376 -- and insert the result to mid temp table
377 -- PRE-COND :
378 -- EXCEPTIONS:
379 --========================================================================
380 PROCEDURE get_unconsumed_rep_po
381 (
382 p_sub_po_unconsumed_row_type IN NUMBER
383 ,p_rep_po_unconsumed_row_type IN NUMBER
384 ,p_ou_id IN NUMBER
385 ,p_supplier_id IN NUMBER
386 ,p_supplier_site_id IN NUMBER
387 ,p_oem_inv_org_id IN NUMBER
388 ,p_tp_inv_org_id IN NUMBER
389 ,p_item_id IN NUMBER
390 ,p_project_id IN NUMBER
391 ,p_task_id IN NUMBER
392 );
393
394 --========================================================================
395 -- FUNCTION : get_sub_po_residual_unconsume PUBLIC ,get_replenishment_po_unallocated quantity for primary uom
396 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
397 -- unallocated components information
398 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
399 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
400 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
401 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
402 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
403 -- : p_item_id the item_id got from the onhand info from mid temp table
404 -- COMMENT : find the subcontract order residual unconsumed quantity for primary uom
405 -- PRE-COND :
406 -- EXCEPTIONS:
407 --========================================================================
408 FUNCTION get_sub_po_residual_unconsume
409 (
410 p_sub_po_unconsumed_row_type IN NUMBER
411 ,p_ou_id IN NUMBER
412 ,p_rcv_transaction_id IN NUMBER
413 ,p_supplier_id IN NUMBER
414 ,p_supplier_site_id IN NUMBER
415 ,p_oem_inv_org_id IN NUMBER
416 ,p_tp_inv_org_id IN NUMBER
417 ,p_item_id IN NUMBER
418 ,p_project_id IN NUMBER
419 ,p_task_id IN NUMBER
420 ) RETURN NUMBER;
421
422 --========================================================================
423 -- PROCEDURE : set_sub_po_residual_unconsume PUBLIC ,set_replenishment_po_unallocated quantity for primary uom
424 -- PARAMETERS: p_sub_po_unconsumed_row_type row type id to identify the
428 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
425 -- unconsumed components information
426 -- : p_rcv_transaction_row_type row type id to identify the rcv_transaction data
427 -- : p_rep_po_id row type id to identify the rcv_transaction data
429 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
430 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
431 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
432 -- : p_item_id the item_id got from the onhand info from mid temp table
433 -- : p_new_consumed_pri the new consumed primary quantity that need to update in the temp table
434 -- COMMENT : find the subcontract order residual unaconsumed quantity for primary uom
435 -- PRE-COND :
436 -- EXCEPTIONS:
437 --========================================================================
438 PROCEDURE set_sub_po_residual_unconsume
439 (
440 p_sub_po_unconsumed_row_type IN NUMBER
441 ,p_rcv_transaction_row_type IN NUMBER
442 ,p_ou_id IN NUMBER
443 ,p_rcv_transaction_id IN NUMBER
444 ,p_supplier_id IN NUMBER
445 ,p_supplier_site_id IN NUMBER
446 ,p_oem_inv_org_id IN NUMBER
447 ,p_tp_inv_org_id IN NUMBER
448 ,p_item_id IN NUMBER
449 ,p_project_id IN NUMBER
450 ,p_task_id IN NUMBER
451 ,p_new_consumed_pri IN NUMBER
452 );
453
454 --========================================================================
455 -- PROCEDURE : set_rcv_transaction_unconsume PUBLIC ,get_replenishment_po_unconsumed quantity for primary uom
456 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
457 -- : p_rcv_transaction_id row type id to identify the rcv_transaction data
458 -- : p_rcv_unallocated_pri the rcv unallocated quantity for primary uom
459 -- : p_supplier_id the supplier id got from the onhand info from mid temp table
460 -- : p_supplier_site_id the supplier site id got from the onhand info from mid temp table
461 -- : p_oem_inv_org_id the oem_inv_org_id got from the onhand info from mid temp table
462 -- : p_tp_inv_org_id the tp_inv_org_id got from the onhand info from mid temp table
463 -- : p_item_id the item_id got from the onhand info from mid temp table
464 -- COMMENT : update the replenishment po receive transaction unallocated information for primary uom
465 -- PRE-COND :
466 -- EXCEPTIONS:
467 --========================================================================
468 PROCEDURE set_rcv_transaction_unconsume
469 (
470 p_rcv_row_type IN NUMBER
471 ,p_ou_id IN NUMBER
472 ,p_rcv_transaction_id IN NUMBER
473 ,p_rcv_unconsumed_pri IN NUMBER
474 ,p_supplier_id IN NUMBER
475 ,p_supplier_site_id IN NUMBER
476 ,p_oem_inv_org_id IN NUMBER
477 ,p_tp_inv_org_id IN NUMBER
478 ,p_item_id IN NUMBER
479 ,p_project_id IN NUMBER
480 ,p_task_id IN NUMBER
481 );
482
483 --========================================================================
484 -- PROCEDURE : validate_cfr_mid_temp PUBLIC ,validate the data in mid temp table, do UOM and Currency conversion
485 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
486 -- COMMENT : this include UOM and Currency conversion and data check
487 -- PRE-COND :
488 -- EXCEPTIONS:
489 --========================================================================
490 PROCEDURE validate_cfr_mid_temp(p_rcv_row_type IN NUMBER);
491
492 --========================================================================
493 -- PROCEDURE : add_data_to_cfr_temp PUBLIC ,process the mid_temp data and add to temp talbe for report builder
494 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
495 -- COMMENT : and data merge to temp talbe for report builder
496 -- PRE-COND :
497 -- EXCEPTIONS:
498 --========================================================================
499 PROCEDURE add_data_to_cfr_temp
500 (
501 p_rcv_row_type IN NUMBER
502 ,p_rpt_mode IN VARCHAR2
503 ,p_days_received IN NUMBER
504 ,p_currency_conversion_type IN VARCHAR2
505 ,p_currency_conversion_date IN DATE
506 ,p_functional_currency IN VARCHAR2
507 -- Amy added to fix bug 5583680 start
508 ,p_supplier_name_from IN VARCHAR2
509 ,p_supplier_site_code_from IN VARCHAR2
510 ,p_supplier_name_to IN VARCHAR2
511 ,p_supplier_site_code_to IN VARCHAR2
512 ,p_oem_inv_org_name_from IN VARCHAR2
513 ,p_oem_inv_org_name_to IN VARCHAR2
514 -- Amy added to fix bug 5583680 end
515 );
516
517 --========================================================================
518 -- PROCEDURE : rpt_get_crude_data PUBLIC ,
519 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
520 -- COMMENT : get the crude data into jmf_shikyu_cfr_rpt_temp with
521 -- RPT_DATA_TYPE = CFR_CRUDE_DATA
522 -- PRE-COND :
523 -- EXCEPTIONS:
524 --========================================================================
525 PROCEDURE rpt_get_crude_data(
526 p_rpt_mode IN VARCHAR2
527 ,p_currency_conversion_type IN VARCHAR2
528 ,p_currency_conversion_date IN DATE
532 --========================================================================
529 ,p_functional_currency IN VARCHAR2
530 );
531
533 -- PROCEDURE : rpt_get_Comp_Estimated_data PUBLIC ,
534 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
535 -- COMMENT : get the Component Estimated data into jmf_shikyu_cfr_rpt_temp with
536 -- RPT_DATA_TYPE = CFR_EXT_COMPONENT
537 -- PRE-COND :
538 -- EXCEPTIONS:
539 --========================================================================
540 PROCEDURE rpt_get_Comp_Estimated_data(p_rpt_mode IN VARCHAR2);
541
542 --========================================================================
543 -- PROCEDURE : rpt_get_SubPO_data PUBLIC ,
544 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
545 -- p_ou_id ou_id to identify period infor
546 -- p_days_received user entered parameter to determine period
547 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
548 -- RPT_DATA_TYPE = CFR_EXT_SUBCONTRACT_PO
549 -- PRE-COND :
550 -- EXCEPTIONS:
551 --========================================================================
552 PROCEDURE rpt_get_SubPO_data(
553 p_rpt_mode IN VARCHAR2
554 ,p_ou_id IN NUMBER
555 ,p_days_received IN NUMBER);
556
557 --========================================================================
558 -- PROCEDURE : rpt_get_UnReceived_data PUBLIC ,
559 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
560 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
561 -- RPT_DATA_TYPE = CFR_EXT_SUBCONTRACT_PO
562 -- PRE-COND :
563 -- EXCEPTIONS:
564 --========================================================================
565 PROCEDURE rpt_get_UnReceived_data
566 (
567 p_rpt_mode IN VARCHAR2
568 -- Amy added to fix bug 5583680 start
569 ,p_supplier_name_from IN VARCHAR2
570 ,p_supplier_site_code_from IN VARCHAR2
571 ,p_supplier_name_to IN VARCHAR2
572 ,p_supplier_site_code_to IN VARCHAR2
573 ,p_oem_inv_org_name_from IN VARCHAR2
574 ,p_oem_inv_org_name_to IN VARCHAR2
575 -- Amy added to fix bug 5583680 end
576 );
577
578 --========================================================================
579 -- PROCEDURE : rpt_get_Received_data PUBLIC ,
580 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
581 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
582 -- RPT_DATA_TYPE = CFR_EXT_SUBCONTRACT_PO
583 -- PRE-COND :
584 -- EXCEPTIONS:
585 --========================================================================
586 PROCEDURE rpt_get_Received_data(
587 p_rpt_mode IN VARCHAR2
588 ,p_days_received IN NUMBER
589 );
590
591 --========================================================================
592 -- PROCEDURE : rpt_get_Int_data PUBLIC ,
593 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
594 -- COMMENT : get the Component data into jmf_shikyu_cfr_rpt_temp with
595 -- RPT_DATA_TYPE = CFR_INT_COMPONENT
596 -- PRE-COND :
597 -- EXCEPTIONS:
598 --========================================================================
599 PROCEDURE rpt_get_Int_data(p_rpt_mode IN VARCHAR2);
600
601 --========================================================================
602 -- PROCEDURE : rpt_get_SubPO_data_Onhand PUBLIC ,
603 -- PARAMETERS: p_rcv_row_type row type id to identify the rcv_transaction
604 -- COMMENT : get the SubPO info for the component data into jmf_shikyu_cfr_rpt_temp with
605 -- These subPOs can affect onhand quantity in MP inventory.
606 -- RPT_DATA_TYPE = CFR_EXT_SUBCONTRACT_PO_AFT_ONHAND
607 -- PRE-COND :
608 -- EXCEPTIONS:
609 --========================================================================
610 PROCEDURE rpt_get_SubPO_data_Onhand(p_rpt_mode IN VARCHAR2);
611
612 --========================================================================
613 -- PROCEDURE : rpt_debug_show_mid_data PUBLIC ,
614 -- PARAMETERS: p_row_type row type in jmf_shikyu_cfr_mid_temp
615 -- p_output_to the parameter for debug_output
616 -- COMMENT : show the data in temp table jmf_shikyu_cfr_mid_temp
617 -- using debug_output
618 -- PRE-COND :
619 -- EXCEPTIONS:
620 --========================================================================
621 PROCEDURE rpt_debug_show_mid_data
622 (
623 p_row_type IN VARCHAR2
624 ,p_output_to IN VARCHAR2
625 );
626
627 --========================================================================
628 -- PROCEDURE : rpt_debug_show_temp_data PUBLIC ,
629 -- PARAMETERS: p_rpt_data_type row type in jmf_shikyu_cfr_rpt_temp
630 -- p_output_to the parameter for debug_output
631 -- COMMENT : show the data in temp table jmf_shikyu_cfr_rpt_temp
632 -- using debug_output
633 -- PRE-COND :
634 -- EXCEPTIONS:
635 --========================================================================
636 PROCEDURE rpt_debug_show_temp_data
637 (
638 p_rpt_data_type IN VARCHAR2
639 ,p_output_to IN VARCHAR2
640 );
641
642 END JMF_SHIKYU_RPT_CFR_PVT;
[Home] [Help]