1 PACKAGE BODY INVIDIT1 AS
2 /* $Header: INVIDI1B.pls 120.18.12010000.2 2008/10/16 04:04:04 xiaozhou ship $ */
3
4 PROCEDURE Get_Startup_Info
5 (
6 X_org_id IN NUMBER
7 , X_mode IN VARCHAR2
8 , X_master_org_id OUT NOCOPY NUMBER
9 , X_master_org_name OUT NOCOPY VARCHAR2
10 , X_master_org_code OUT NOCOPY VARCHAR2
11 , X_master_chart_of_accounts OUT NOCOPY number
12 , X_updateable_item OUT NOCOPY varchar2
13 , X_default_status OUT NOCOPY varchar2
14 , x_default_uom_b OUT NOCOPY VARCHAR2
15 , x_default_uom OUT NOCOPY VARCHAR2
16 , x_default_uom_code OUT NOCOPY VARCHAR2
17 , x_default_uom_class OUT NOCOPY VARCHAR2
18 , x_time_uom_class OUT NOCOPY VARCHAR2
19 , x_default_lot_status_id OUT NOCOPY NUMBER
20 , x_default_lot_status OUT NOCOPY VARCHAR2
21 , x_default_serial_status_id OUT NOCOPY NUMBER
22 , x_default_serial_status OUT NOCOPY VARCHAR2
23 , x_Item_Category_Set_id OUT NOCOPY NUMBER
24 , x_Item_Category_Structure_id OUT NOCOPY NUMBER
25 , x_Item_Category_Validate_Flag OUT NOCOPY VARCHAR2--Bug:3578024
26 , x_Item_Category_Set_Ctrl_level OUT NOCOPY VARCHAR2--Bug:3723668
27 , x_Default_Template_id OUT NOCOPY NUMBER
28 , x_Default_Template_Name OUT NOCOPY VARCHAR2
29 , X_icgd_option OUT NOCOPY varchar2
30 , X_allow_item_desc_update_flag OUT NOCOPY varchar2
31 , X_rfq_required_flag OUT NOCOPY varchar2
32 , X_receiving_flag OUT NOCOPY varchar2
33 , X_taxable_flag OUT NOCOPY varchar2
34 , X_org_locator_control OUT NOCOPY number
35 , X_org_expense_account OUT NOCOPY number
36 , X_org_encumbrance_account OUT NOCOPY number
37 , X_org_cost_of_sales_account OUT NOCOPY number
38 , X_org_sales_account OUT NOCOPY number
39 , X_serial_generation OUT NOCOPY number
40 , X_lot_generation OUT NOCOPY number
41 , X_cost_method OUT NOCOPY number
42 , X_category_flex_structure OUT NOCOPY number
43 , X_bom_enabled_status OUT NOCOPY number
44 , X_purchasable_status OUT NOCOPY number
45 , X_transactable_status OUT NOCOPY number
46 , X_stockable_status OUT NOCOPY number
47 , X_wip_status OUT NOCOPY number
48 , X_cust_ord_status OUT NOCOPY number
49 , X_int_ord_status OUT NOCOPY number
50 , X_invoiceable_status OUT NOCOPY number
51 , X_order_by_segments OUT NOCOPY varchar2
52 , X_product_family_templ_id OUT NOCOPY number
53 , X_encumbrance_reversal_flag OUT NOCOPY NUMBER --* Added for Bug #3818342
54 /* Start Bug 3713912 */
55 ,X_recipe_enabled_status OUT NOCOPY number,
56 X_process_exec_enabled_status OUT NOCOPY number
57 /* End Bug 3713912 */
58 /* Adding attributes for R12 */
59 , X_tp_org OUT NOCOPY VARCHAR2
60
61 )
62 IS
63
64 master_org number;
65 uom_default varchar2(25);
66
67 -- Set default values for the Default Material Statuses
68 --
69 c_default_lot_status_id CONSTANT NUMBER := 1;
70 c_default_serial_status_id CONSTANT NUMBER := 1;
71
72 l_Item_Category_Set_id NUMBER;
73 l_Default_Template_id NUMBER;
74
75 icgd_profile_exists boolean;
76 v_operating_unit number;
77 v_status_attr varchar2(50);
78 v_status_ctrl number;
79 v_segs varchar2(15) := null;
80 v_enabled_segs varchar2(150) := null;
81
82 -- Retrieve the status control code for the status attributes
83 CURSOR status_attr_control is
84 select attribute_name, status_control_code
85 from mtl_item_attributes
86 where status_control_code is not null;
87
88
89 -- This sql statement retrieves each of the enabled item flex segments
90 -- in order
91 -- This info is used to dynamically build the ORDER_BY clause in
92 -- the Items form
93 --
94 CURSOR flex_segs is
95 select application_column_name
96 from fnd_id_flex_segments
97 where application_id = 401
98 and id_flex_code = 'MSTK'
99 and id_flex_num = 101
100 and enabled_flag = 'Y'
101 order by segment_num;
102
103 BEGIN
104 -- Get master org and master org code for this organization
105
106 BEGIN
107 select a.master_organization_id, b.organization_code
108 , DECODE(X_mode,'DEFINE',NVL(b.encumbrance_reversal_flag,2),NVL(a.encumbrance_reversal_flag,2)) --* Added for Bug #3818342
109 into master_org, X_master_org_code, X_encumbrance_reversal_flag
110 from mtl_parameters a, mtl_parameters b
111 where a.organization_id = X_org_id
112 and a.master_organization_id = b.organization_id;
113
114 X_master_org_id := master_org;
115
116 EXCEPTION
117 when NO_DATA_FOUND then
118 null;
119 END;
120
121 -- Get chart of accounts for master org
122
123 begin
124
125 SELECT lgr.CHART_OF_ACCOUNTS_ID
126 into X_master_chart_of_accounts
127 FROM gl_ledgers lgr,
128 hr_organization_information hoi
129 where hoi.organization_id = master_org
130 and (HOI.ORG_INFORMATION_CONTEXT|| '') ='Accounting Information'
131 and TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI.ORG_INFORMATION1,'0123456789',' ')), NULL, HOI.ORG_INFORMATION1,-99999)) = LGR.LEDGER_ID
132 and lgr.object_type_code = 'L'
133 and rownum = 1;
134
135
136 exception
137 when NO_DATA_FOUND then
138 null;
139 end;
140
141 -- Get profiles used in form.
142 --
143 fnd_profile.get('INV_UPDATEABLE_ITEM', X_updateable_item);
144 fnd_profile.get('INV_STATUS_DEFAULT', X_default_status);
145
146 -- Put default uom in a local variable so it can be used in later select.
147 fnd_profile.get('INV_UOM_DEFAULT', uom_default);
148
149 x_default_uom_b := uom_default;
150
151 fnd_profile.get('TIME_UOM_CLASS', X_time_uom_class);
152 fnd_profile.get('USE_NAME_ICG_DESC', X_icgd_option);
153
154
155 -- Get user specified category set for item folder.
156 --
157 --FND_PROFILE.Get ('INV_USER_CATEGORY_SET', l_Item_Category_Set_id);
158
159 IF ( FND_PROFILE.Defined ('INV_ITEM_FOLDER_CATEGORY_SET') ) THEN
160 l_Item_Category_Set_id := FND_PROFILE.Value ('INV_ITEM_FOLDER_CATEGORY_SET');
161 END IF;
162
163 IF ( l_Item_Category_Set_id IS NOT NULL ) THEN
164 BEGIN
165
166 SELECT structure_id, validate_flag, control_level --Bug:3578024
167 INTO x_Item_Category_Structure_id, x_Item_Category_Validate_Flag, x_Item_Category_Set_Ctrl_level
168 FROM mtl_category_sets_b
169 WHERE category_set_id = l_Item_Category_Set_id;
170
171 x_Item_Category_Set_id := l_Item_Category_Set_id;
172
173 EXCEPTION
174 WHEN no_data_found THEN
175 x_Item_Category_Set_id := NULL;
176 x_Item_Category_Structure_id := NULL;
177
178 END;
179 END IF;
180
181 -- Get optional default template to be used to initialize new items.
182 --
183 IF ( FND_PROFILE.Defined ('INV_ITEM_DEFAULT_TEMPLATE') ) THEN
184 l_Default_Template_id := FND_PROFILE.Value ('INV_ITEM_DEFAULT_TEMPLATE');
185 END IF;
186
187 IF ( l_Default_Template_id IS NOT NULL ) THEN
188 BEGIN
189
190 SELECT template_name
191 INTO x_Default_Template_Name
192 FROM mtl_item_templates
193 WHERE template_id = l_Default_Template_id;
194
195 x_Default_Template_id := l_Default_Template_id;
196
197 EXCEPTION
198 WHEN no_data_found THEN
199 x_Default_Template_id := NULL;
200 x_Default_Template_Name := NULL;
201
202 END;
203 END IF;
204
205 -- Get asset category flex structure
206 --
207 if ( INV_Item_Util.g_Appl_Inst.fa <> 0 ) then
208 BEGIN
209
210 select category_flex_structure
211 into X_category_flex_structure
212 from fa_system_controls;
213
214 EXCEPTION
215 when NO_DATA_FOUND then
216 X_category_flex_structure := null;
217
218 END;
219 end if;
220 -- Get uom_code and uom_class for default primary uom
221 --
222 if ( uom_default is not null ) then
223 begin
224 select
225 unit_of_measure_tl, uom_code, uom_class
226 into
227 x_default_uom, x_default_uom_code, x_default_uom_class
228 from
229 mtl_units_of_measure_vl
230 where
231 unit_of_measure = uom_default;
232
233 exception
234 when NO_DATA_FOUND then
235 X_default_uom_code := null;
236 X_default_uom_class := null;
237 end;
238
239 end if;
240
241 --Jalaj Srivastava Bug 5934365
242 --No need to check for wms install for lot status
243 --material status in R12 is core INV functionality.
244 --IF ( INV_Item_Util.g_Appl_Inst.WMS <> 0 ) THEN
245
246 IF ( c_default_lot_status_id is not null ) THEN
247 BEGIN
248 SELECT status_code
249 INTO x_default_lot_status
250 FROM mtl_material_statuses_vl
251 WHERE status_id = c_default_lot_status_id
252 AND lot_control = 1;
253
254 x_default_lot_status_id := c_default_lot_status_id;
255
256 EXCEPTION
257 WHEN no_data_found THEN
258 x_default_lot_status := null;
259 END;
260 END IF;
261
262 IF ( c_default_serial_status_id is not null ) THEN
263 BEGIN
264 SELECT status_code
265 INTO x_default_serial_status
266 FROM mtl_material_statuses_vl
267 WHERE status_id = c_default_serial_status_id
268 AND serial_control = 1;
269
270 x_default_serial_status_id := c_default_serial_status_id;
271
272 EXCEPTION
273 WHEN no_data_found THEN
274 x_default_serial_status := null;
275 END;
276 END IF;
277
278 --END IF;
279 -- Get defaults for purchasing attributes
280 --
281 if ( INV_Item_Util.g_Appl_Inst.po <> 0 ) then
282 BEGIN
283 select DECODE(ORG_INFORMATION_CONTEXT,
284 'Accounting Information',
285 TO_NUMBER(ORG_INFORMATION3),
286 TO_NUMBER(NULL)) operating_unit
287 into V_operating_unit
288 from hr_organization_information
289 where organization_id = X_org_id
290 and (org_information_context|| '') ='Accounting Information';
291
292
293 select allow_item_desc_update_flag,
294 rfq_required_flag,
295 receiving_flag,
296 taxable_flag
297 into X_allow_item_desc_update_flag,
298 X_rfq_required_flag,
299 X_receiving_flag,
300 X_taxable_flag
301 from po_system_parameters_all
302 where nvl(org_id, -11) = nvl(v_operating_unit, -11);
303
304 EXCEPTION
305 when NO_DATA_FOUND then
306 X_allow_item_desc_update_flag := null;
307 x_rfq_required_flag := null;
308 X_receiving_flag := null;
309 X_taxable_flag := null;
310
311 END;
312 end if;
313
314 -- Get organization info for master org
315 -- Accounts are used for defaults when
316 -- creating an item, so use the master org
317 --
318 BEGIN
319 select mp.cost_of_sales_account,
320 mp.encumbrance_account,
321 mp.sales_account,
322 mp.expense_account,
323 hr.name
324 into X_org_cost_of_sales_account,
325 X_org_encumbrance_account,
326 X_org_sales_account,
327 X_org_expense_account,
328 X_master_org_name
329 from mtl_parameters mp, hr_organization_units hr
330 where mp.organization_id = master_org
331 and mp.organization_id = hr.organization_id;
332
333 -- Get this info for the current org
334 --
335 select decode(mp.stock_locator_control_code, '5', '1',
336 '4', '1',
337 mp.stock_locator_control_code),
338 mp.primary_cost_method,
339 mp.lot_number_generation,
340 mp.serial_number_generation,
341 mp.trading_partner_org_flag
342 into X_org_locator_control,
343 X_cost_method,
344 X_lot_generation,
345 X_serial_generation,
346 X_tp_org
347 from mtl_parameters mp
348 where mp.organization_id = X_org_id;
349
350 EXCEPTION
351 when NO_DATA_FOUND then
352 null;
353 END;
354
355 OPEN status_attr_control;
356
357 LOOP
358 FETCH status_attr_control INTO v_status_attr, v_status_ctrl;
359 EXIT when status_attr_control%NOTFOUND;
360
361 if v_status_attr = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG' then
362 X_bom_enabled_status := v_status_ctrl;
363
364 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG' then
365 X_purchasable_status := v_status_ctrl;
366
367 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG' then
368 X_transactable_status := v_status_ctrl;
369
370 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG' then
371 X_stockable_status := v_status_ctrl;
372
373 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG' then
374 X_wip_status := v_status_ctrl;
375
376 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG' then
377 X_cust_ord_status := v_status_ctrl;
378
379 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG' then
380 X_int_ord_status := v_status_ctrl;
381
382 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG' then
383 X_invoiceable_status := v_status_ctrl;
384 /* Start Bug 3713912 */
385 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG' then
386 X_recipe_enabled_status := v_status_ctrl;
387 elsif v_status_attr = 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG' then
388 X_process_exec_enabled_status := v_status_ctrl;
389 /* End Bug 3713912 */
390
391 end if;
392
393 END LOOP;
394
395 CLOSE status_attr_control;
396
397
398 -- Retrieve each of the enabled item flex segments into a variable
399 -- Concatenate all of them together to return 1 string in the format:
400 -- segment1,segment2
401 --
402 OPEN flex_segs;
403 LOOP
404 FETCH flex_segs into v_segs;
405 EXIT when flex_segs%NOTFOUND;
406
407 -- if this is the first segment, don't concat the ',' before it
408 if v_enabled_segs is null then
409 v_enabled_segs := v_segs;
410 else
411 v_enabled_segs := v_enabled_segs || ',' || v_segs;
412 end if;
413
414 END LOOP;
415
416 CLOSE flex_segs;
417
418 X_order_by_segments := v_enabled_segs;
419
420 BEGIN
421 -- Return template_id of the 'Product Family' template
422 -- if one is defined in the profile option
423 --
424 X_product_family_templ_id := fnd_profile.value('INV_ITEMS_PRODUCT_FAMILY_TEMPLATE');
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 X_product_family_templ_id := 0;
429 END;
430
431 END Get_Startup_Info;
432
433
434 -- Get product installation status
435 -- application_id product
436 -- -------------- -------
437 -- 140 Fixed Assets
438 -- 170 Service
439 -- 201 Purchasing
440 -- 222 Receivables
441 -- 300 Order Entry
442 -- 401 Inventory
443 -- 702 BOM
444 -- 703 Engineering
445 -- 704 MRP
446 -- 706 WIP
447 --
448 PROCEDURE Get_Installs(X_inv_install OUT NOCOPY number,
449 X_po_install OUT NOCOPY number,
450 X_ar_install OUT NOCOPY number,
451 X_oe_install OUT NOCOPY number,
452 X_bom_install OUT NOCOPY number,
453 X_eng_install OUT NOCOPY number,
454 X_cs_install OUT NOCOPY number,
455 X_mrp_install OUT NOCOPY number,
456 X_wip_install OUT NOCOPY number,
457 X_fa_install OUT NOCOPY number,
458 X_pjm_unit_eff_enabled OUT NOCOPY VARCHAR2
459 ) is
460 -- local variables
461
462 is_installed boolean;
463 indust varchar2(10);
464 inv_installed varchar2(10);
465 po_installed varchar2(10);
466 ar_installed varchar2(10);
467 oe_installed varchar2(10);
468 bom_installed varchar2(10);
469 eng_installed varchar2(10);
470 cs_installed varchar2(10);
471 mrp_installed varchar2(10);
472 wip_installed varchar2(10);
473 fa_installed varchar2(10);
474
475 begin
476
477 -- If it's installed, set it to application_id; if not, set it to 0
478 -- (form logic needs it to be 0 if not installed
479 -- status = 'I' indicates the product is fully installed
480 --
481 is_installed := fnd_installation.get(appl_id => 401, dep_appl_id => 401,
482 status => inv_installed, industry => indust);
483 if (inv_installed = 'I') then
484 X_inv_install := 401;
485 else
486 X_inv_install := 0;
487 end if;
488
489 is_installed := fnd_installation.get(appl_id => 201, dep_appl_id => 201,
490 status => po_installed, industry => indust);
491 if (po_installed = 'I') then
492 X_po_install := 201;
493 else
494 X_po_install := 0;
495 end if;
496
497 is_installed := fnd_installation.get(appl_id => 222, dep_appl_id => 222,
498 status => ar_installed, industry => indust);
499 if (ar_installed = 'I') then
500 X_ar_install := 222;
501 else
502 X_ar_install := 0;
503 end if;
504
505 is_installed := fnd_installation.get(appl_id => 300, dep_appl_id => 300,
506 status => oe_installed, industry => indust);
507 if (oe_installed = 'I') then
508 X_oe_install := 300;
509 else
510 X_oe_install := 0;
511 end if;
512
513 is_installed := fnd_installation.get(appl_id => 702, dep_appl_id => 702,
514 status => bom_installed, industry => indust);
515 if (bom_installed = 'I') then
516 X_bom_install := 702;
517 else
518 X_bom_install := 0;
519 end if;
520
521 is_installed := fnd_installation.get(appl_id => 703, dep_appl_id => 703,
522 status => eng_installed, industry => indust);
523 if (eng_installed = 'I') then
524 X_eng_install := 703;
525 else
526 X_eng_install := 0;
527 end if;
528
529 is_installed := fnd_installation.get(appl_id => 170, dep_appl_id => 170,
530 status => cs_installed, industry => indust);
531 if (cs_installed = 'I') then
532 X_cs_install := 170;
533 else
534 X_cs_install := 0;
535 end if;
536
537 is_installed := fnd_installation.get(appl_id => 704, dep_appl_id => 704,
538 status => mrp_installed, industry => indust);
539 if (mrp_installed = 'I') then
540 X_mrp_install := 704;
541 else
542 X_mrp_install := 0;
543 end if;
544
545 is_installed := fnd_installation.get(appl_id => 706, dep_appl_id => 706,
546 status => wip_installed, industry => indust);
547 if (wip_installed = 'I') then
548 X_wip_install := 706;
549 else
550 X_wip_install := 0;
551 end if;
552
553 is_installed := fnd_installation.get(appl_id => 140, dep_appl_id => 140,
554 status => fa_installed, industry => indust);
555 if (fa_installed = 'I') then
556 X_fa_install := 140;
557 else
558 X_fa_install := 0;
559 end if;
560
561 -- Parameter gets value Y/N depending on whether Model/Unit Effectivity
562 -- has been enabled or not.
563 --
564 X_pjm_unit_eff_enabled := PJM_UNIT_EFF.Enabled();
565
566 end Get_Installs;
567
568
569 -- Resolve foreign key references
570 --
571 PROCEDURE Populate_Fields
572 (
573 X_org_id IN NUMBER
574 , X_item_id IN NUMBER
575 , X_buyer_id IN NUMBER,
576 X_hazard_class_id IN NUMBER,
577 X_un_number_id IN NUMBER,
578 X_picking_rule_id IN NUMBER,
579 X_atp_rule_id IN NUMBER,
580 X_payment_terms_id IN NUMBER,
581 X_accounting_rule_id IN NUMBER,
582 X_invoicing_rule_id IN NUMBER,
583 X_default_shipping_org IN NUMBER,
584 X_source_organization_id IN NUMBER,
585 X_weight_uom_code IN VARCHAR2,
586 X_volume_uom_code IN VARCHAR2,
587 X_item_type IN VARCHAR2,
588 X_container_type IN VARCHAR2,
589 X_conversion IN NUMBER,
590 X_buyer OUT NOCOPY varchar2,
591 X_hazard_class OUT NOCOPY varchar2,
592 X_un_number OUT NOCOPY varchar2,
593 X_un_description OUT NOCOPY varchar2,
594 X_picking_rule OUT NOCOPY varchar2,
595 X_atp_rule OUT NOCOPY varchar2,
596 X_payment_terms OUT NOCOPY varchar2,
597 X_accounting_rule OUT NOCOPY varchar2,
598 X_invoicing_rule OUT NOCOPY varchar2,
599 X_default_shipping_org_dsp OUT NOCOPY varchar2,
600 X_source_organization OUT NOCOPY varchar2,
601 X_source_org_name OUT NOCOPY varchar2,
602 X_weight_uom OUT NOCOPY varchar2,
603 X_volume_uom OUT NOCOPY varchar2,
604 X_item_type_dsp OUT NOCOPY varchar2,
605 X_container_type_dsp OUT NOCOPY varchar2,
606 X_conversion_dsp OUT NOCOPY varchar2,
607 X_service_duration_per_code IN VARCHAR2
608 , X_service_duration_period OUT NOCOPY VARCHAR2
609 , X_coverage_schedule_id IN number
610 , X_coverage_schedule OUT NOCOPY varchar2
611 , p_primary_uom_code IN VARCHAR2
612 , x_primary_uom OUT NOCOPY VARCHAR2
613 , x_uom_class OUT NOCOPY VARCHAR2
614 , p_dimension_uom_code IN VARCHAR2
615 , p_default_lot_status_id IN NUMBER
616 , p_default_serial_status_id IN NUMBER
617 , x_dimension_uom OUT NOCOPY VARCHAR2
618 , x_default_lot_status OUT NOCOPY VARCHAR2
619 , x_default_serial_status OUT NOCOPY VARCHAR2
620 , p_eam_activity_type_code IN VARCHAR2
621 , p_eam_activity_cause_code IN VARCHAR2
622 , p_eam_act_shutdown_status IN VARCHAR2
623 , x_eam_activity_type OUT NOCOPY VARCHAR2
624 , x_eam_activity_cause OUT NOCOPY VARCHAR2
625 , x_eam_act_shutdown_status_dsp OUT NOCOPY VARCHAR2
626 , p_secondary_uom_code IN VARCHAR2
627 , x_secondary_uom OUT NOCOPY VARCHAR2
628 --Jalaj Srivastava Bug 5017588
629 , x_secondary_uom_class OUT NOCOPY VARCHAR2
630 , p_Folder_Category_Set_id IN NUMBER
631 , x_Folder_Item_Category_id OUT NOCOPY NUMBER
632 , x_Folder_Item_Category OUT NOCOPY VARCHAR2
633 --Added as part of 11.5.9 ENH
634 , p_eam_activity_source_code IN VARCHAR2
635 , x_eam_activity_source OUT NOCOPY VARCHAR2
636 -- Item Transaction Defaults for 11.5.9
637 , X_Default_Move_Order_Sub_Inv OUT NOCOPY VARCHAR2
638 , X_Default_Receiving_Sub_Inv OUT NOCOPY VARCHAR2
639 , X_Default_Shipping_Sub_Inv OUT NOCOPY VARCHAR2
640 , X_charge_periodicity_code IN VARCHAR2
641 , X_charge_unit_of_measure OUT NOCOPY VARCHAR2
642 , X_inv_item_status_code IN VARCHAR2
643 , X_inv_item_status_code_tl OUT NOCOPY VARCHAR2
644 , p_default_material_status_id IN NUMBER
645 , x_default_material_status OUT NOCOPY VARCHAR2
646 )
647 IS
648
649 CURSOR csr_Folder_Category
650 IS
651 SELECT category_id, NULL
652 FROM mtl_item_categories
653 WHERE
654 inventory_item_id = X_item_id
655 AND organization_id = X_org_id
656 AND category_set_id = p_Folder_Category_Set_id;
657
658 CURSOR csr_Default_SubInventories
659 IS
660 SELECT subinventory_code, default_type
661 FROM mtl_item_sub_defaults
662 WHERE inventory_item_id = X_Item_Id
663 AND organization_id = X_org_id; --Bug:2791548
664
665 l_sec_uom_code VARCHAR2(10);
666
667 BEGIN
668
669 -- For each product that is fully installed, resolve foreign key references
670 --
671 if ( INV_Item_Util.g_Appl_Inst.po <> 0 ) then
672
673 if (X_buyer_id is not null) then
674 --Modifying the procedure to query buyers from other organisations when the profile is Y
675 --For bug no. 3845910- Anmurali
676
677 begin
678 SELECT full_name INTO X_buyer
679 FROM per_people_f
680 WHERE person_id = X_buyer_id
681 AND trunc(sysdate) between effective_start_date and effective_end_date;
682 exception
683 when OTHERS then
684 BEGIN
685 SELECT full_name INTO X_buyer
686 FROM per_people_f
687 WHERE person_id = X_buyer_id;
688 EXCEPTION
689 WHEN OTHERS THEN
690 X_buyer := null;
691 END;
692 end;
693 --End of alteration for bug no. 3845910- Anmurali
694 end if;
695
696 if (X_hazard_class_id is not null) then
697 begin
698 select hazard_class
699 into X_hazard_class
700 from po_hazard_classes
701 where hazard_class_id = X_hazard_class_id;
702 exception
703 when NO_DATA_FOUND then
704 X_hazard_class := null;
705 end;
706
707 end if;
708
709 if (X_un_number_id is not null) then
710 begin
711 select un_number, description
712 into X_un_number, X_un_description
713 from po_un_numbers
714 where un_number_id = X_un_number_id;
715
716 exception
717 when NO_DATA_FOUND then
718 X_un_number := null;
719 X_un_description := null;
720 end;
721 end if;
722
723 end if; -- po_installed
724
725 if ( INV_Item_Util.g_Appl_Inst.ONT <> 0 ) then
726
727 if (X_picking_rule_id is not null) then
728 begin
729 select picking_rule_name
730 into X_picking_rule
731 from mtl_picking_rules
732 where picking_rule_id = X_picking_rule_id;
733 exception
734 when NO_DATA_FOUND then
735 X_picking_rule := null;
736 end;
737 end if;
738
739 if (X_atp_rule_id is not null) then
740 begin
741 select rule_name
742 into X_atp_rule
743 from mtl_atp_rules
744 where rule_id = X_atp_rule_id;
745 exception
746 when NO_DATA_FOUND then
747 X_atp_rule := null;
748 end;
749 end if;
750
751 if (X_payment_terms_id is not null) then
752 begin
753 select name
754 into X_payment_terms
755 from ra_terms
756 where term_id = X_payment_terms_id;
757 exception
758 when NO_DATA_FOUND then
759 X_payment_terms := null;
760 end;
761 end if;
762
763 if (X_default_shipping_org is not null) then
764 begin
765 select name
766 into X_default_shipping_org_dsp
767 from hr_organization_units
768 where organization_id = X_default_shipping_org;
769 exception
770 when NO_DATA_FOUND then
771 X_default_shipping_org_dsp := null;
772 end;
773 end if;
774
775 end if; -- ONT installed
776
777 if ( INV_Item_Util.g_Appl_Inst.ar <> 0 ) then
778
779 if (X_accounting_rule_id is not null) then
780 begin
781 select name
782 into X_accounting_rule
783 from ra_rules
784 where rule_id = X_accounting_rule_id;
785 exception
786 when NO_DATA_FOUND then
787 X_accounting_rule := null;
788 end;
789 end if;
790
791 if (X_invoicing_rule_id is not null) then
792 begin
793 select name
794 into X_invoicing_rule
795 from ra_rules
796 where rule_id = X_invoicing_rule_id;
797 exception
798 when NO_DATA_FOUND then
799 X_invoicing_rule := null;
800 end;
801 end if;
802
803 end if; -- ar_installed
804
805 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 ) then
806
807 if (X_atp_rule_id is not null) then
808 begin
809 select rule_name
810 into X_atp_rule
811 from mtl_atp_rules
812 where rule_id = X_atp_rule_id;
813 exception
814 when NO_DATA_FOUND then
815 X_atp_rule := null;
816 end;
817 end if;
818
819 if (X_source_organization_id is not null) then
820 begin
821 select mp.organization_code,hou.name
822 into X_source_organization, X_source_org_name
823 from hr_organization_units hou
824 ,mtl_parameters mp
825 where hou.organization_id = mp.organization_id
826 and mp.organization_id = X_source_organization_id;
827 exception
828 when NO_DATA_FOUND then
829 X_source_organization := null;
830 X_source_org_name := null;
831 end;
832 end if;
833
834 end if; -- inv_installed
835
836 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 or
837 INV_Item_Util.g_Appl_Inst.po <> 0 ) then
838
839 if (X_weight_uom_code is not null) then
840 begin
841 select unit_of_measure_tl
842 into X_weight_uom
843 from mtl_units_of_measure_vl
844 where uom_code = X_weight_uom_code;
845 exception
846 when NO_DATA_FOUND then
847 X_weight_uom := null;
848 end;
849 end if;
850
851 if (X_volume_uom_code is not null) then
852 begin
853 select unit_of_measure_tl
854 into X_volume_uom
855 from mtl_units_of_measure_vl
856 where uom_code = X_volume_uom_code;
857 exception
858 when NO_DATA_FOUND then
859 X_volume_uom := null;
860 end;
861 end if;
862
863 end if; -- inv or po installed
864
865 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 or
866 INV_Item_Util.g_Appl_Inst.po <> 0 or
867 INV_Item_Util.g_Appl_Inst.ar <> 0 or
868 INV_Item_Util.g_Appl_Inst.ONT <> 0 ) then
869
870 if (X_item_type is not null) then
871 begin
872 select meaning
873 into X_item_type_dsp
874 from fnd_common_lookups
875 where lookup_code = X_item_type
876 and lookup_type = 'ITEM_TYPE';
877 exception
878 when NO_DATA_FOUND then
879 X_item_type_dsp := null;
880 end;
881 end if;
882
883 if (X_conversion is not null) then
884 begin
885 select meaning
886 into X_conversion_dsp
887 from mfg_lookups
888 where lookup_type = 'MTL_CONVERSION_TYPE'
889 and lookup_code = X_conversion;
890 exception
891 when NO_DATA_FOUND then
892 X_conversion_dsp := null;
893 end;
894 end if;
895
896 end if; -- if po, inv, ar, or ONT installed
897
898 -- Resolve service_duration_period_code
899 --
900 if ( X_service_duration_per_code is not null ) then
901 begin
902 select unit_of_measure_tl
903 into X_service_duration_period
904 from mtl_units_of_measure_vl
905 where uom_code = X_service_duration_per_code;
906 exception
907 when NO_DATA_FOUND then
908 X_service_duration_period := null;
909 end;
910 end if;
911
912 -- Get the primary UOM and UOM Class for item's uom code
913 --
914 if ( p_primary_uom_code is not null ) then
915 begin
916 select unit_of_measure_tl, uom_class
917 into x_primary_uom, x_uom_class
918 from mtl_units_of_measure_vl
919 where uom_code = p_primary_uom_code;
920 exception
921 when NO_DATA_FOUND then
922 x_primary_uom := null;
923 x_uom_class := null;
924 end;
925 end if;
926
927 if ( INV_Item_Util.g_Appl_Inst.OKS <> 0 ) then -- Contracts Service installed
928
929 if ( X_coverage_schedule_id is not null ) then
930 begin
931 select name
932 into X_coverage_schedule
933 from oks_coverage_templts_v
934 where id = X_coverage_schedule_id;
935 exception
936 when no_data_found then
937 X_coverage_schedule := null;
938 end;
939 end if;
940
941 end if;
942
943 if (X_container_type is not null) then
944 begin
945 select meaning
946 into X_container_type_dsp
947 from fnd_common_lookups
948 where lookup_code = X_container_type
949 and lookup_type = 'CONTAINER_TYPE';
950 exception
951 when NO_DATA_FOUND then
952 X_container_type_dsp := null;
953 end;
954 end if;
955
956 IF ( INV_Item_Util.g_Appl_Inst.INV <> 0 ) THEN
957
958 IF ( p_dimension_uom_code is not null ) then
959 BEGIN
960 SELECT unit_of_measure_tl
961 INTO x_dimension_uom
962 FROM mtl_units_of_measure_vl
963 WHERE uom_code = p_dimension_uom_code;
964 EXCEPTION
965 WHEN no_data_found THEN
966 x_dimension_uom := null;
967 END;
968 END IF;
969
970 END IF;
971
972 --Jalaj Srivastava Bug 5934365
973 --No need to check for wms install for lot status
974 --material status in R12 is core INV functionality.
975 --IF ( INV_Item_Util.g_Appl_Inst.WMS <> 0 ) THEN
976
977 IF ( p_default_lot_status_id is not null ) THEN
978 BEGIN
979 SELECT status_code
980 INTO x_default_lot_status
981 FROM mtl_material_statuses_vl
982 WHERE status_id = p_default_lot_status_id
983 AND lot_control = 1;
984 EXCEPTION
985 WHEN no_data_found THEN
986 x_default_lot_status := null;
987 END;
988 END IF;
989
990 -- Fix for Bug#6644711
991 IF ( p_default_material_status_id is not null ) THEN
992 BEGIN
993 SELECT status_code
994 INTO x_default_material_status
995 FROM mtl_material_statuses_vl
996 WHERE status_id = p_default_material_status_id
997 AND onhand_control = 1;
998 EXCEPTION
999 WHEN no_data_found THEN
1000 x_default_material_status := null;
1001 END;
1002 END IF;
1003
1004 IF ( p_default_serial_status_id is not null ) THEN
1005 BEGIN
1006 SELECT status_code
1007 INTO x_default_serial_status
1008 FROM mtl_material_statuses_vl
1009 WHERE status_id = p_default_serial_status_id
1010 AND serial_control = 1;
1011 EXCEPTION
1012 WHEN no_data_found THEN
1013 x_default_serial_status := null;
1014 END;
1015 END IF;
1016
1017 --END IF;
1018
1019 if (p_eam_activity_type_code is not null) then
1020 begin
1021 select meaning
1022 into x_eam_activity_type
1023 from mfg_lookups
1024 where lookup_type = 'MTL_EAM_ACTIVITY_TYPE'
1025 and lookup_code = p_eam_activity_type_code;
1026 exception
1027 when NO_DATA_FOUND then
1028 x_eam_activity_type := null;
1029 end;
1030 end if;
1031
1032 if (p_eam_activity_cause_code is not null) then
1033 begin
1034 select meaning
1035 into x_eam_activity_cause
1036 from mfg_lookups
1037 where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
1038 and lookup_code = p_eam_activity_cause_code;
1039 exception
1040 when NO_DATA_FOUND then
1041 x_eam_activity_cause := null;
1042 end;
1043 end if;
1044
1045 if (p_eam_act_shutdown_status is not null) then
1046 begin
1047 select meaning
1048 into x_eam_act_shutdown_status_dsp
1049 from mfg_lookups
1050 where lookup_type = 'BOM_EAM_SHUTDOWN_TYPE'
1051 and lookup_code = p_eam_act_shutdown_status;
1052 exception
1053 when NO_DATA_FOUND then
1054 x_eam_act_shutdown_status_dsp := null;
1055 end;
1056 end if;
1057 --Added as part of 11.5.9 ENH
1058 if (p_eam_activity_source_code is not null) then
1059 begin
1060 select meaning
1061 into x_eam_activity_source
1062 from fnd_lookup_values_vl
1063 where lookup_type = 'MTL_EAM_ACTIVITY_SOURCE'
1064 and lookup_code = p_eam_activity_source_code;
1065 exception
1066 when NO_DATA_FOUND then
1067 x_eam_activity_source := null;
1068 end;
1069 end if;
1070
1071 IF ( p_secondary_uom_code IS NOT NULL ) THEN
1072 BEGIN
1073 SELECT unit_of_measure_tl, uom_class
1074 INTO x_secondary_uom, x_secondary_uom_class
1075 FROM mtl_units_of_measure_vl
1076 WHERE uom_code = p_secondary_uom_code;
1077 EXCEPTION
1078 WHEN no_data_found THEN
1079 x_secondary_uom := NULL;
1080 x_secondary_uom_class := NULL;
1081 END;
1082 END IF;
1083
1084 IF x_secondary_uom_class IS NULL THEN
1085 BEGIN
1086 SELECT secondary_uom_code
1087 INTO l_sec_uom_code
1088 FROM mtl_system_items
1089 WHERE inventory_item_id = X_item_id
1090 AND secondary_uom_code IS NOT NULL
1091 AND rownum = 1;
1092 EXCEPTION
1093 WHEN no_data_found THEN
1094 l_sec_uom_code := NULL;
1095 END;
1096 IF l_sec_uom_code IS NOT NULL THEN
1097 BEGIN
1098 SELECT uom_class
1099 INTO x_secondary_uom_class
1100 FROM mtl_units_of_measure_vl
1101 WHERE uom_code = l_sec_uom_code;
1102 EXCEPTION
1103 WHEN no_data_found THEN
1104 x_secondary_uom_class := NULL;
1105 END;
1106 END IF;
1107 END IF;
1108
1109 /* No need for this FK -- the form field is a poplist.
1110
1111 IF ( p_contract_item_type_code IS NOT NULL ) THEN
1112 BEGIN
1113 SELECT meaning
1114 INTO x_contract_item_type
1115 FROM fnd_lookup_values_vl
1116 WHERE lookup_type = 'OKB_CONTRACT_ITEM_TYPE'
1117 AND lookup_code = p_contract_item_type_code;
1118 EXCEPTION
1119 WHEN no_data_found THEN
1120 x_contract_item_type := NULL;
1121 END;
1122 END IF;
1123 */
1124
1125 IF ( p_Folder_Category_Set_id IS NOT NULL ) THEN
1126 BEGIN
1127
1128 OPEN csr_Folder_Category;
1129
1130 FETCH csr_Folder_Category
1131 INTO
1132 x_Folder_Item_Category_id
1133 , x_Folder_Item_Category;
1134
1135 CLOSE csr_Folder_Category;
1136
1137 EXCEPTION
1138 -- WHEN no_data_found THEN
1139 WHEN others THEN
1140
1141 IF ( csr_Folder_Category%ISOPEN ) THEN
1142 CLOSE csr_Folder_Category;
1143 END IF;
1144
1145 x_Folder_Item_Category_id := NULL;
1146 x_Folder_Item_Category := NULL;
1147
1148 END;
1149 END IF;
1150 -- Populate Item Transaction Default SubInventories for 11.5.9
1151 BEGIN
1152 X_Default_Move_Order_Sub_Inv := NULL;
1153 X_Default_Receiving_Sub_Inv := NULL;
1154 X_Default_Shipping_Sub_Inv := NULL;
1155 FOR I IN csr_Default_SubInventories LOOP
1156 IF I.Default_Type = 3 THEN
1157 X_Default_Move_Order_Sub_Inv := I.subinventory_code;
1158 ELSIF I.Default_Type = 2 THEN
1159 X_Default_Receiving_Sub_Inv := I.subinventory_code;
1160 ELSIF I.Default_Type = 1 THEN
1161 X_Default_Shipping_Sub_Inv := I.subinventory_code;
1162 END IF;
1163 END LOOP;
1164 EXCEPTION
1165 WHEN NO_DATA_FOUND THEN
1166 X_Default_Move_Order_Sub_Inv := NULL;
1167 X_Default_Receiving_Sub_Inv := NULL;
1168 X_Default_Shipping_Sub_Inv := NULL;
1169 END;
1170
1171 BEGIN
1172 IF X_charge_periodicity_code IS NOT NULL THEN
1173 SELECT UNIT_OF_MEASURE INTO X_charge_unit_of_measure
1174 from mtl_units_of_measure_vl --Bug 5174403
1175 WHERE UOM_CODE = X_charge_periodicity_code;
1176 END IF;
1177 EXCEPTION
1178 WHEN NO_DATA_FOUND THEN
1179 X_charge_unit_of_measure := NULL;
1180 END;
1181
1182 BEGIN
1183 IF X_inv_item_status_code IS NOT NULL THEN
1184 select inventory_item_status_code_tl INTO X_inv_item_status_code_tl
1185 from mtl_item_status
1186 where inventory_item_status_code = X_inv_item_status_code;
1187 END IF;
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN
1190 X_inv_item_status_code_tl := NULL;
1191 END;
1192
1193 END Populate_Fields;
1194
1195
1196 -- Function to check on entry if source organization is valid
1197 -- Returns: 0 - source org is ok
1198 -- 1 - source org does not have item and/or does not use
1199 -- the same set of books
1200 -- 2 - sub is nettable or null
1201 -- 3 - interorg network is not defined
1202 --
1203 FUNCTION Validate_Source_Org(X_org_id number,
1204 X_item_id number,
1205 X_new_item_id number,
1206 X_source_org number,
1207 X_mrp_plan number,
1208 X_source_sub varchar2
1209 ) return number is
1210 source_item number;
1211 nettable_sub number;
1212 org_network number;
1213
1214 begin
1215
1216 -- If this returns a row, item exists in source org
1217 /* Fix for bug 5844510-Commented the below query.
1218 select count(1)
1219 into source_item
1220 from dual
1221 where X_source_org in (
1222 select organization_id
1223 from mtl_system_items
1224 where (inventory_item_id = nvl(X_new_item_id, -11)
1225 or inventory_item_id = nvl(X_item_id, -11))
1226 )
1227 and rownum = 1;
1228 */
1229 /* Fix for bug 5844510- Replaced the above query with one below. This was done to improve its performance.
1230 Here it is sufficient to check whether the item exists in the source org.
1231 Note that source_org is already validated against OOD view in INVPVD6B.pls*/
1232
1233 select count(1)
1234 into source_item
1235 from mtl_system_items_b
1236 where (inventory_item_id = nvl(X_new_item_id, -11)
1237 or inventory_item_id = nvl(X_item_id, -11))
1238 and organization_id= X_source_org;
1239
1240 if (source_item = 0) then
1241 return(1);
1242 end if;
1243
1244 if (X_source_org = X_org_id) then
1245 -- return code of 2 does not seem to be used anywhere.
1246 -- however, in future if to be used, mrp_plan of 3,7,9 to function
1247 -- similar. enhancement for MRP planning code attribute
1248 if (X_mrp_plan = 3 or
1249 X_mrp_plan = 7 or
1250 X_mrp_plan = 9 ) then
1251 -- If this returns a row, the sub is nettable or null
1252 select count(1)
1253 into nettable_sub
1254 from mtl_secondary_inventories
1255 where secondary_inventory_name=nvl(X_source_sub, secondary_inventory_name)
1256 and availability_type = 1
1257 and rownum = 1;
1258 end if;
1259
1260 if (nettable_sub = 1) then
1261 return(2);
1262 end if;
1263
1264 else
1265 -- If this returns a row, the interorg network is defined
1266 select count(1)
1267 into org_network
1268 from mtl_interorg_parameters
1269 where to_organization_id = X_org_id
1270 and from_organization_id = X_source_org
1271 and rownum = 1;
1272
1273 -- No org network defined, so return 3
1274 if (org_network = 0) then
1275 return(3);
1276 end if;
1277
1278 end if; -- if source_org = org_id
1279
1280 return(0);
1281
1282 end Validate_Source_Org;
1283
1284 --2463543 :Below is used in Item Search Form. Exclusively Built for INVIVCSU
1285 PROCEDURE Item_Search_Execute_Query
1286 (p_grp_handle_id IN NUMBER,
1287 p_org_id IN NUMBER DEFAULT NULL,
1288 p_item_mask IN VARCHAR2 DEFAULT NULL,
1289 p_item_description IN VARCHAR2 DEFAULT NULL,
1290 p_base_item_id IN NUMBER DEFAULT NULL,
1291 p_status IN VARCHAR2 DEFAULT NULL,
1292 p_catalog_grp_id IN NUMBER DEFAULT NULL,
1293 p_catalog_complete IN VARCHAR2 DEFAULT NULL,
1294 p_manufacturer_id IN NUMBER DEFAULT NULL,
1295 p_mfg_part_num IN VARCHAR2 DEFAULT NULL,
1296 p_vendor_id IN NUMBER DEFAULT NULL,
1297 p_default_assignment IN VARCHAR2 DEFAULT NULL,
1298 p_vendor_product_num IN VARCHAR2 DEFAULT NULL,
1299 p_contract IN VARCHAR2 DEFAULT NULL,
1300 p_blanket_agreement IN VARCHAR2 DEFAULT NULL,
1301 p_xref_type IN dbms_sql.Varchar2_Table,
1302 p_xref_val IN dbms_sql.Varchar2_Table,
1303 p_relationship_type IN dbms_sql.Number_Table,
1304 p_related_item IN dbms_sql.Number_Table,
1305 p_category_set IN dbms_sql.Number_Table,
1306 p_category_id IN dbms_sql.Number_Table,
1307 p_element_name IN dbms_sql.Varchar2_Table,
1308 p_element_val IN dbms_sql.Varchar2_Table) IS
1309
1310 l_cursor NUMBER;
1311 l_rowcount NUMBER;
1312 sql_stmt VARCHAR2(30000);
1313
1314 l_supplier_stmt VARCHAR2(2000);
1315 l_sup_tab_list VARCHAR2(200);
1316 l_sup_where_clause VARCHAR2(2000);
1317
1318 l_xref_stmt VARCHAR2(2000);
1319 l_xref_row_stmt VARCHAR2(2000);
1320 l_xref_bind NUMBER := 1;
1321
1322 l_relation_stmt VARCHAR2(2000);
1323 l_relation_row_stmt VARCHAR2(2000);
1324 l_relation_bind NUMBER := 1;
1325
1326 l_category_stmt VARCHAR2(2000);
1327 l_category_row_stmt VARCHAR2(2000);
1328 l_category_bind NUMBER := 1;
1329
1330 l_element_stmt VARCHAR2(2000);
1331 l_element_row_stmt VARCHAR2(2000);
1332 l_element_bind NUMBER := 1;
1333 BEGIN
1334
1335 l_cursor := DBMS_SQL.OPEN_CURSOR;
1336 sql_stmt := 'INSERT INTO MTL_CATALOG_SEARCH_ITEMS ( ' ||
1337 ' SELECT :handle, MSI.INVENTORY_ITEM_ID, '||
1338 ' MSI.ORGANIZATION_ID,MSI.DESCRIPTION, '||
1339 ' MSI.PRIMARY_UOM_CODE, MSI.RESERVABLE_TYPE '||
1340 ' FROM MTL_SYSTEM_ITEMS_VL MSI '||
1341 ' WHERE 1= 1 ';
1342
1343 IF p_org_id IS NOT NULL THEN
1344 sql_stmt := sql_stmt || ' AND MSI.ORGANIZATION_ID = :p_org_id ';
1345 END IF;
1346
1347 IF p_item_mask IS NOT NULL THEN
1348 sql_stmt := sql_stmt || ' AND MSI.CONCATENATED_SEGMENTS LIKE :p_item_mask ';
1349 END IF;
1350
1351 -- FP bug fix for 12.1.1. The bug # is 7303779
1352 -- Fixed by Sean on 10/14/08. Added upper function
1353 -- to make the description search case insensitive
1354 IF p_item_description IS NOT NULL THEN
1355 sql_stmt := sql_stmt || ' AND upper(MSI.DESCRIPTION) LIKE upper(:p_item_description) ';
1356 END IF;
1357 -- END of fix 730799
1358 IF p_base_item_id IS NOT NULL THEN
1359 sql_stmt := sql_stmt || ' AND MSI.BASE_ITEM_ID = :p_base_item_id ' ;
1360 END IF;
1361
1362 IF p_status IS NOT NULL THEN
1363 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_STATUS_CODE = :p_status ';
1364 END IF;
1365
1366 IF p_catalog_grp_id IS NOT NULL THEN
1367 sql_stmt := sql_stmt || ' AND MSI.ITEM_CATALOG_GROUP_ID = :p_catalog_grp_id ';
1368 END IF;
1369
1370 IF p_catalog_complete IS NOT NULL THEN
1371 sql_stmt := sql_stmt || ' AND MSI.CATALOG_STATUS_FLAG = :p_catalog_complete ';
1372 END IF;
1373
1374 -- Start Purchase Details Query Building
1375 IF p_manufacturer_id IS NOT NULL THEN
1376 sql_stmt := sql_stmt || ' AND EXISTS (SELECT NULL FROM MTL_MFG_PART_NUMBERS MPN ' ||
1377 ' WHERE MPN.MANUFACTURER_ID = :p_manufacturer_id '||
1378 ' AND MPN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1379 IF p_mfg_part_num IS NOT NULL THEN
1380 sql_stmt := sql_stmt || ' AND MPN.MFG_PART_NUM = :p_mfg_part_num ';
1381 END IF;
1382 sql_stmt := sql_stmt || ')';
1383 END IF;
1384
1385 IF p_vendor_id IS NOT NULL THEN
1386 l_sup_tab_list := 'MRP_SOURCING_RULES MS, MRP_SR_ASSIGNMENTS MA, MRP_SR_RECEIPT_ORG MR, MRP_SR_SOURCE_ORG M1';
1387 l_sup_where_clause := 'M1.VENDOR_ID = :p_vendor_id AND MA.assignment_set_id = :p_default_assignment ' ||
1388 'AND MA.sourcing_rule_id = MS.sourcing_rule_id AND MR.sr_receipt_id = M1.sr_receipt_id ' ||
1389 'AND MA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1390 END IF;
1391
1392 IF p_vendor_product_num IS NOT NULL THEN
1393 IF l_sup_tab_list IS NOT NULL THEN
1394 l_sup_tab_list := l_sup_tab_list || ', ';
1395 END IF;
1396 l_sup_tab_list := l_sup_tab_list || 'PO_LINES_ALL L';
1397 IF l_sup_where_clause IS NOT NULL THEN
1398 l_sup_where_clause := l_sup_where_clause || ' AND ';
1399 END IF;
1400 l_sup_where_clause := l_sup_where_clause || 'L.VENDOR_PRODUCT_NUM = :p_vendor_product_num ' ||
1401 'AND L.ITEM_ID = MSI.INVENTORY_ITEM_ID';
1402 END IF;
1403
1404 IF p_contract IS NOT NULL THEN
1405 IF l_sup_tab_list IS NOT NULL THEN
1406 l_sup_tab_list := l_sup_tab_list || ', ';
1407 END IF;
1408 l_sup_tab_list := l_sup_tab_list || 'PO_HEADERS_ALL H';
1409 IF INSTR(l_sup_tab_list,'PO_LINES_ALL') = 0 THEN
1410 l_sup_tab_list := l_sup_tab_list || ',PO_LINES_ALL L';
1411 IF l_sup_where_clause IS NOT NULL THEN
1412 l_sup_where_clause := l_sup_where_clause || ' AND ';
1413 END IF;
1414 l_sup_where_clause := l_sup_where_clause || ' L.ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1415 END IF;
1416 IF l_sup_where_clause IS NOT NULL THEN
1417 l_sup_where_clause := l_sup_where_clause || ' AND ';
1418 END IF;
1419 l_sup_where_clause := l_sup_where_clause || 'H.SEGMENT1 = :p_contract AND H.PO_HEADER_ID = L.PO_HEADER_ID';
1420 END IF;
1421
1422 IF p_blanket_agreement IS NOT NULL THEN
1423 IF l_sup_tab_list IS NOT NULL THEN
1424 l_sup_tab_list := l_sup_tab_list || ', ';
1425 END IF;
1426 l_sup_tab_list := l_sup_tab_list || 'PO_HEADERS_ALL H2';
1427 IF INSTR(l_sup_tab_list,'PO_LINES_ALL') = 0 THEN
1428 l_sup_tab_list := l_sup_tab_list || ', PO_LINES_ALL L';
1429 IF l_sup_where_clause IS NOT NULL THEN
1430 l_sup_where_clause := l_sup_where_clause || ' AND ';
1431 END IF;
1432 l_sup_where_clause := l_sup_where_clause || ' L.ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1433 END IF;
1434 l_sup_where_clause := l_sup_where_clause || 'H2.SEGMENT1 = :p_blanket_agreement ' ||
1435 ' AND H2.PO_HEADER_ID = L.PO_HEADER_ID';
1436 END IF;
1437
1438 IF l_sup_tab_list IS NOT NULL THEN
1439 l_supplier_stmt := 'SELECT NULL FROM ' || l_sup_tab_list || ' WHERE ' || l_sup_where_clause;
1440 sql_stmt := sql_stmt || ' AND EXISTS (' || l_supplier_stmt || ')';
1441 END IF;
1442
1443 -- Start Xref Details Query Building
1444 IF p_xref_type.COUNT <> 0 THEN
1445 FOR i IN p_xref_type.FIRST .. p_xref_type.LAST LOOP
1446 l_xref_row_stmt := '(SELECT MCR.INVENTORY_ITEM_ID FROM MTL_CROSS_REFERENCES MCR ' ||
1447 ' WHERE MCR.CROSS_REFERENCE_TYPE = :xref_type' || l_xref_bind ||
1448 ' AND MCR.CROSS_REFERENCE = :xref_val' || l_xref_bind || ')';
1449 l_xref_bind := l_xref_bind + 1;
1450 IF l_xref_stmt IS NOT NULL THEN
1451 l_xref_stmt := l_xref_stmt || ' INTERSECT ';
1452 END IF;
1453 l_xref_stmt := l_xref_stmt || l_xref_row_stmt;
1454 END LOOP;
1455 END IF;
1456 IF l_xref_stmt IS NOT NULL THEN
1457 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_xref_stmt || ')';
1458 END IF;
1459
1460 -- Start Item Relation Query Building.
1461 IF p_relationship_type.COUNT <> 0 THEN
1462 FOR i IN p_relationship_type.FIRST .. p_relationship_type.LAST LOOP
1463 l_relation_row_stmt := '(SELECT MRI.INVENTORY_ITEM_ID FROM MTL_RELATED_ITEMS_VIEW MRI ' ||
1464 ' WHERE MRI.RELATIONSHIP_TYPE_ID = :relation_type' || l_relation_bind ||
1465 ' AND MRI.RELATED_ITEM_ID = :related_item' || l_relation_bind || ')';
1466 l_relation_bind := l_relation_bind + 1;
1467 IF l_relation_stmt IS NOT NULL THEN
1468 l_relation_stmt := l_relation_stmt || ' INTERSECT ';
1469 END IF;
1470 l_relation_stmt := l_relation_stmt || l_relation_row_stmt;
1471 END LOOP;
1472 END IF;
1473 IF l_relation_stmt IS NOT NULL THEN
1474 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_relation_stmt || ')';
1475 END IF;
1476
1477 -- Start Item Category Query Building.
1478 IF p_category_set.COUNT <> 0 THEN
1479 FOR i IN p_category_set.FIRST .. p_category_set.LAST LOOP
1480 l_category_row_stmt := '(SELECT MIC.INVENTORY_ITEM_ID FROM MTL_ITEM_CATEGORIES MIC ' ||
1481 ' WHERE MIC.CATEGORY_SET_ID = :category_set' || l_category_bind ||
1482 ' AND MIC.CATEGORY_ID = :category_id' || l_category_bind ||
1483 ' AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID)';
1484 l_category_bind := l_category_bind + 1;
1485 IF l_category_stmt IS NOT NULL THEN
1486 l_category_stmt := l_category_stmt || ' INTERSECT ';
1487 END IF;
1488 l_category_stmt := l_category_stmt || l_category_row_stmt;
1489 END LOOP;
1490
1491 END IF;
1492 IF l_category_stmt IS NOT NULL THEN
1493 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_category_stmt || ')';
1494 END IF;
1495
1496 -- Start Item Catalog Group Query Building.
1497 IF p_element_name.COUNT <> 0 THEN
1498 FOR i IN p_element_name.FIRST .. p_element_name.LAST LOOP
1499 IF(p_element_val(i) IS NOT NULL) THEN
1500 l_element_row_stmt := '(SELECT DEV.INVENTORY_ITEM_ID FROM MTL_DESCR_ELEMENT_VALUES DEV ' ||
1501 ' WHERE DEV.ELEMENT_NAME = :element_name' || l_element_bind ||
1502 ' AND DEV.ELEMENT_VALUE = :element_val' || l_element_bind || ')';
1503 l_element_bind := l_element_bind + 1;
1504 IF l_element_stmt IS NOT NULL THEN
1505 l_element_stmt := l_element_stmt || ' INTERSECT ';
1506 END IF;
1507 l_element_stmt := l_element_stmt || l_element_row_stmt;
1508 END IF;
1509 END LOOP;
1510 END IF;
1511 IF l_element_stmt IS NOT NULL THEN
1512 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_element_stmt || ')';
1513 END IF;
1514
1515 sql_stmt := sql_stmt || ')';
1516
1517 DBMS_SQL.PARSE(l_cursor, sql_stmt, dbms_sql.native);
1518 DBMS_SQL.BIND_VARIABLE(l_cursor, 'handle', p_grp_handle_id);
1519
1520 IF p_org_id IS NOT NULL THEN
1521 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_org_id', p_org_id);
1522 END IF;
1523
1524 IF p_item_mask IS NOT NULL THEN
1525 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_item_mask', p_item_mask);
1526 END IF;
1527
1528 IF p_item_description IS NOT NULL THEN
1529 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_item_description', p_item_description);
1530 END IF;
1531
1532 IF p_base_item_id IS NOT NULL THEN
1533 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_base_item_id', p_base_item_id);
1534 END IF;
1535
1536 IF p_status IS NOT NULL THEN
1537 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_status', p_status);
1538 END IF;
1539
1540 IF p_catalog_grp_id IS NOT NULL THEN
1541 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_catalog_grp_id', p_catalog_grp_id);
1542 END IF;
1543
1544 IF p_catalog_complete IS NOT NULL THEN
1545 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_catalog_complete', p_catalog_complete);
1546 END IF;
1547
1548 IF p_manufacturer_id IS NOT NULL THEN
1549 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_manufacturer_id', p_manufacturer_id);
1550 IF p_mfg_part_num IS NOT NULL THEN
1551 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_mfg_part_num' , p_mfg_part_num);
1552 END IF;
1553 END IF;
1554
1555 IF p_vendor_id IS NOT NULL THEN
1556 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_vendor_id', p_vendor_id);
1557 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_default_assignment', p_default_assignment);
1558 END IF;
1559
1560 IF p_vendor_product_num IS NOT NULL THEN
1561 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_vendor_product_num', p_vendor_product_num);
1562 END IF;
1563
1564 IF p_contract IS NOT NULL THEN
1565 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_contract', p_contract);
1566 END IF;
1567
1568 IF p_blanket_agreement IS NOT NULL THEN
1569 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_blanket_agreement', p_blanket_agreement);
1570 END IF;
1571
1572 IF p_xref_type.COUNT <> 0 THEN
1573 l_xref_bind := 1;
1574 FOR i IN p_xref_type.FIRST .. p_xref_type.LAST LOOP
1575 DBMS_SQL.BIND_VARIABLE(l_cursor, 'xref_type' || l_xref_bind , p_xref_type(i));
1576 DBMS_SQL.BIND_VARIABLE(l_cursor, 'xref_val' || l_xref_bind , p_xref_val(i));
1577 l_xref_bind := l_xref_bind + 1;
1578 END LOOP;
1579 END IF;
1580
1581 IF p_relationship_type.COUNT <> 0 THEN
1582 l_relation_bind := 1;
1583 FOR i IN p_relationship_type.FIRST .. p_relationship_type.LAST LOOP
1584 DBMS_SQL.BIND_VARIABLE(l_cursor, 'relation_type' || l_relation_bind, p_relationship_type(i));
1585 DBMS_SQL.BIND_VARIABLE(l_cursor, 'related_item' || l_relation_bind, p_related_item(i));
1586 l_relation_bind := l_relation_bind + 1;
1587 END LOOP;
1588 END IF;
1589
1590 IF p_category_set.COUNT <> 0 THEN
1591 l_category_bind := 1;
1592 FOR i IN p_category_set.FIRST .. p_category_set.LAST LOOP
1593 DBMS_SQL.BIND_VARIABLE(l_cursor, 'category_set' || l_category_bind, p_category_set(i));
1594 DBMS_SQL.BIND_VARIABLE(l_cursor, 'category_id' || l_category_bind, p_category_id(i));
1595 l_category_bind := l_category_bind + 1;
1596 END LOOP;
1597 END IF;
1598
1599 IF p_element_name.COUNT <> 0 THEN
1600 l_element_bind := 1;
1601 FOR i IN p_element_name.FIRST .. p_element_name.LAST LOOP
1602 IF(p_element_val(i) IS NOT NULL) THEN
1603 DBMS_SQL.BIND_VARIABLE(l_cursor, 'element_name' || l_element_bind, p_element_name(i));
1604 DBMS_SQL.BIND_VARIABLE(l_cursor, 'element_val' || l_element_bind, p_element_val(i));
1605 l_element_bind := l_element_bind + 1;
1606 END IF;
1607 END LOOP;
1608 END IF;
1609
1610 l_rowcount := DBMS_SQL.EXECUTE(l_cursor);
1611
1612 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1613
1614 END;
1615
1616 END INVIDIT1;
[Home] [Help]