1 PACKAGE BODY INVIDIT1 AS
2 /* $Header: INVIDI1B.pls 120.21 2011/10/31 03:35:07 yifwang 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 -- Start of changes for bug12898193
730 select name
731 into X_picking_rule
732 from wms_rules_vl
733 where rule_id = X_picking_rule_id;
734 -- End of changes for bug12898193
735 exception
736 when NO_DATA_FOUND then
737 X_picking_rule := null;
738 end;
739 end if;
740
741 if (X_atp_rule_id is not null) then
742 begin
743 select rule_name
744 into X_atp_rule
745 from mtl_atp_rules
746 where rule_id = X_atp_rule_id;
747 exception
748 when NO_DATA_FOUND then
749 X_atp_rule := null;
750 end;
751 end if;
752
753 if (X_payment_terms_id is not null) then
754 begin
755 select name
756 into X_payment_terms
757 from ra_terms
758 where term_id = X_payment_terms_id;
759 exception
760 when NO_DATA_FOUND then
761 X_payment_terms := null;
762 end;
763 end if;
764
765 if (X_default_shipping_org is not null) then
766 begin
767 select name
768 into X_default_shipping_org_dsp
769 from hr_organization_units
770 where organization_id = X_default_shipping_org;
771 exception
772 when NO_DATA_FOUND then
773 X_default_shipping_org_dsp := null;
774 end;
775 end if;
776
777 end if; -- ONT installed
778
779 if ( INV_Item_Util.g_Appl_Inst.ar <> 0 ) then
780
781 if (X_accounting_rule_id is not null) then
782 begin
783 select name
784 into X_accounting_rule
785 from ra_rules
786 where rule_id = X_accounting_rule_id;
787 exception
788 when NO_DATA_FOUND then
789 X_accounting_rule := null;
790 end;
791 end if;
792
793 if (X_invoicing_rule_id is not null) then
794 begin
795 select name
796 into X_invoicing_rule
797 from ra_rules
798 where rule_id = X_invoicing_rule_id;
799 exception
800 when NO_DATA_FOUND then
801 X_invoicing_rule := null;
802 end;
803 end if;
804
805 end if; -- ar_installed
806
807 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 ) then
808
809 if (X_atp_rule_id is not null) then
810 begin
811 select rule_name
812 into X_atp_rule
813 from mtl_atp_rules
814 where rule_id = X_atp_rule_id;
815 exception
816 when NO_DATA_FOUND then
817 X_atp_rule := null;
818 end;
819 end if;
820
821 if (X_source_organization_id is not null) then
822 begin
823 select mp.organization_code,hou.name
824 into X_source_organization, X_source_org_name
825 from hr_organization_units hou
826 ,mtl_parameters mp
827 where hou.organization_id = mp.organization_id
828 and mp.organization_id = X_source_organization_id;
829 exception
830 when NO_DATA_FOUND then
831 X_source_organization := null;
832 X_source_org_name := null;
833 end;
834 end if;
835
836 end if; -- inv_installed
837
838 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 or
839 INV_Item_Util.g_Appl_Inst.po <> 0 ) then
840
841 if (X_weight_uom_code is not null) then
842 begin
843 select unit_of_measure_tl
844 into X_weight_uom
845 from mtl_units_of_measure_vl
846 where uom_code = X_weight_uom_code;
847 exception
848 when NO_DATA_FOUND then
849 X_weight_uom := null;
850 end;
851 end if;
852
853 if (X_volume_uom_code is not null) then
854 begin
855 select unit_of_measure_tl
856 into X_volume_uom
857 from mtl_units_of_measure_vl
858 where uom_code = X_volume_uom_code;
859 exception
860 when NO_DATA_FOUND then
861 X_volume_uom := null;
862 end;
863 end if;
864
865 end if; -- inv or po installed
866
867 if ( INV_Item_Util.g_Appl_Inst.inv <> 0 or
868 INV_Item_Util.g_Appl_Inst.po <> 0 or
869 INV_Item_Util.g_Appl_Inst.ar <> 0 or
870 INV_Item_Util.g_Appl_Inst.ONT <> 0 ) then
871
872 if (X_item_type is not null) then
873 begin
874 select meaning
875 into X_item_type_dsp
876 from fnd_common_lookups
877 where lookup_code = X_item_type
878 and lookup_type = 'ITEM_TYPE';
879 exception
880 when NO_DATA_FOUND then
881 X_item_type_dsp := null;
882 end;
883 end if;
884
885 if (X_conversion is not null) then
886 begin
887 select meaning
888 into X_conversion_dsp
889 from mfg_lookups
890 where lookup_type = 'MTL_CONVERSION_TYPE'
891 and lookup_code = X_conversion;
892 exception
893 when NO_DATA_FOUND then
894 X_conversion_dsp := null;
895 end;
896 end if;
897
898 end if; -- if po, inv, ar, or ONT installed
899
900 -- Resolve service_duration_period_code
901 --
902 if ( X_service_duration_per_code is not null ) then
903 begin
904 select unit_of_measure_tl
905 into X_service_duration_period
906 from mtl_units_of_measure_vl
907 where uom_code = X_service_duration_per_code;
908 exception
909 when NO_DATA_FOUND then
910 X_service_duration_period := null;
911 end;
912 end if;
913
914 -- Get the primary UOM and UOM Class for item's uom code
915 --
916 if ( p_primary_uom_code is not null ) then
917 begin
918 select unit_of_measure_tl, uom_class
919 into x_primary_uom, x_uom_class
920 from mtl_units_of_measure_vl
921 where uom_code = p_primary_uom_code;
922 exception
923 when NO_DATA_FOUND then
924 x_primary_uom := null;
925 x_uom_class := null;
926 end;
927 end if;
928
929 if ( INV_Item_Util.g_Appl_Inst.OKS <> 0 ) then -- Contracts Service installed
930
931 if ( X_coverage_schedule_id is not null ) then
932 begin
933 select name
934 into X_coverage_schedule
935 from oks_coverage_templts_v
936 where id = X_coverage_schedule_id;
937 exception
938 when no_data_found then
939 X_coverage_schedule := null;
940 end;
941 end if;
942
943 end if;
944
945 if (X_container_type is not null) then
946 begin
947 select meaning
948 into X_container_type_dsp
949 from fnd_common_lookups
950 where lookup_code = X_container_type
951 and lookup_type = 'CONTAINER_TYPE';
952 exception
953 when NO_DATA_FOUND then
954 X_container_type_dsp := null;
955 end;
956 end if;
957
958 IF ( INV_Item_Util.g_Appl_Inst.INV <> 0 ) THEN
959
960 IF ( p_dimension_uom_code is not null ) then
961 BEGIN
962 SELECT unit_of_measure_tl
963 INTO x_dimension_uom
964 FROM mtl_units_of_measure_vl
965 WHERE uom_code = p_dimension_uom_code;
966 EXCEPTION
967 WHEN no_data_found THEN
968 x_dimension_uom := null;
969 END;
970 END IF;
971
972 END IF;
973
974 --Jalaj Srivastava Bug 5934365
975 --No need to check for wms install for lot status
976 --material status in R12 is core INV functionality.
977 --IF ( INV_Item_Util.g_Appl_Inst.WMS <> 0 ) THEN
978
979 IF ( p_default_lot_status_id is not null ) THEN
980 BEGIN
981 SELECT status_code
982 INTO x_default_lot_status
983 FROM mtl_material_statuses_vl
984 WHERE status_id = p_default_lot_status_id
985 AND lot_control = 1;
986 EXCEPTION
987 WHEN no_data_found THEN
988 x_default_lot_status := null;
989 END;
990 END IF;
991
992 -- Fix for Bug#6644711
993 IF ( p_default_material_status_id is not null ) THEN
994 BEGIN
995 SELECT status_code
996 INTO x_default_material_status
997 FROM mtl_material_statuses_vl
998 WHERE status_id = p_default_material_status_id
999 AND onhand_control = 1;
1000 EXCEPTION
1001 WHEN no_data_found THEN
1002 x_default_material_status := null;
1003 END;
1004 END IF;
1005
1006 IF ( p_default_serial_status_id is not null ) THEN
1007 BEGIN
1008 SELECT status_code
1009 INTO x_default_serial_status
1010 FROM mtl_material_statuses_vl
1011 WHERE status_id = p_default_serial_status_id
1012 AND serial_control = 1;
1013 EXCEPTION
1014 WHEN no_data_found THEN
1015 x_default_serial_status := null;
1016 END;
1017 END IF;
1018
1019 --END IF;
1020
1021 if (p_eam_activity_type_code is not null) then
1022 begin
1023 select meaning
1024 into x_eam_activity_type
1025 from mfg_lookups
1026 where lookup_type = 'MTL_EAM_ACTIVITY_TYPE'
1027 and lookup_code = p_eam_activity_type_code;
1028 exception
1029 when NO_DATA_FOUND then
1030 x_eam_activity_type := null;
1031 end;
1032 end if;
1033
1034 if (p_eam_activity_cause_code is not null) then
1035 begin
1036 select meaning
1037 into x_eam_activity_cause
1038 from mfg_lookups
1039 where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
1040 and lookup_code = p_eam_activity_cause_code;
1041 exception
1042 when NO_DATA_FOUND then
1043 x_eam_activity_cause := null;
1044 end;
1045 end if;
1046
1047 if (p_eam_act_shutdown_status is not null) then
1048 begin
1049 select meaning
1050 into x_eam_act_shutdown_status_dsp
1051 from mfg_lookups
1052 where lookup_type = 'BOM_EAM_SHUTDOWN_TYPE'
1053 and lookup_code = p_eam_act_shutdown_status;
1054 exception
1055 when NO_DATA_FOUND then
1056 x_eam_act_shutdown_status_dsp := null;
1057 end;
1058 end if;
1059 --Added as part of 11.5.9 ENH
1060 if (p_eam_activity_source_code is not null) then
1061 begin
1062 select meaning
1063 into x_eam_activity_source
1064 from fnd_lookup_values_vl
1065 where lookup_type = 'MTL_EAM_ACTIVITY_SOURCE'
1066 and lookup_code = p_eam_activity_source_code;
1067 exception
1068 when NO_DATA_FOUND then
1069 x_eam_activity_source := null;
1070 end;
1071 end if;
1072
1073 IF ( p_secondary_uom_code IS NOT NULL ) THEN
1074 BEGIN
1075 SELECT unit_of_measure_tl, uom_class
1076 INTO x_secondary_uom, x_secondary_uom_class
1077 FROM mtl_units_of_measure_vl
1078 WHERE uom_code = p_secondary_uom_code;
1079 EXCEPTION
1080 WHEN no_data_found THEN
1081 x_secondary_uom := NULL;
1082 x_secondary_uom_class := NULL;
1083 END;
1084 END IF;
1085
1086 IF x_secondary_uom_class IS NULL THEN
1087 BEGIN
1088 SELECT secondary_uom_code
1089 INTO l_sec_uom_code
1090 FROM mtl_system_items
1091 WHERE inventory_item_id = X_item_id
1092 AND secondary_uom_code IS NOT NULL
1093 AND rownum = 1;
1094 EXCEPTION
1095 WHEN no_data_found THEN
1096 l_sec_uom_code := NULL;
1097 END;
1098 IF l_sec_uom_code IS NOT NULL THEN
1099 BEGIN
1100 SELECT uom_class
1101 INTO x_secondary_uom_class
1102 FROM mtl_units_of_measure_vl
1103 WHERE uom_code = l_sec_uom_code;
1104 EXCEPTION
1105 WHEN no_data_found THEN
1106 x_secondary_uom_class := NULL;
1107 END;
1108 END IF;
1109 END IF;
1110
1111 /* No need for this FK -- the form field is a poplist.
1112
1113 IF ( p_contract_item_type_code IS NOT NULL ) THEN
1114 BEGIN
1115 SELECT meaning
1116 INTO x_contract_item_type
1117 FROM fnd_lookup_values_vl
1118 WHERE lookup_type = 'OKB_CONTRACT_ITEM_TYPE'
1119 AND lookup_code = p_contract_item_type_code;
1120 EXCEPTION
1121 WHEN no_data_found THEN
1122 x_contract_item_type := NULL;
1123 END;
1124 END IF;
1125 */
1126
1127 IF ( p_Folder_Category_Set_id IS NOT NULL ) THEN
1128 BEGIN
1129
1130 OPEN csr_Folder_Category;
1131
1132 FETCH csr_Folder_Category
1133 INTO
1134 x_Folder_Item_Category_id
1135 , x_Folder_Item_Category;
1136
1137 CLOSE csr_Folder_Category;
1138
1139 EXCEPTION
1140 -- WHEN no_data_found THEN
1141 WHEN others THEN
1142
1143 IF ( csr_Folder_Category%ISOPEN ) THEN
1144 CLOSE csr_Folder_Category;
1145 END IF;
1146
1147 x_Folder_Item_Category_id := NULL;
1148 x_Folder_Item_Category := NULL;
1149
1150 END;
1151 END IF;
1152 -- Populate Item Transaction Default SubInventories for 11.5.9
1153 BEGIN
1154 X_Default_Move_Order_Sub_Inv := NULL;
1155 X_Default_Receiving_Sub_Inv := NULL;
1156 X_Default_Shipping_Sub_Inv := NULL;
1157 FOR I IN csr_Default_SubInventories LOOP
1158 IF I.Default_Type = 3 THEN
1159 X_Default_Move_Order_Sub_Inv := I.subinventory_code;
1160 ELSIF I.Default_Type = 2 THEN
1161 X_Default_Receiving_Sub_Inv := I.subinventory_code;
1162 ELSIF I.Default_Type = 1 THEN
1163 X_Default_Shipping_Sub_Inv := I.subinventory_code;
1164 END IF;
1165 END LOOP;
1166 EXCEPTION
1167 WHEN NO_DATA_FOUND THEN
1168 X_Default_Move_Order_Sub_Inv := NULL;
1169 X_Default_Receiving_Sub_Inv := NULL;
1170 X_Default_Shipping_Sub_Inv := NULL;
1171 END;
1172
1173 BEGIN
1174 IF X_charge_periodicity_code IS NOT NULL THEN
1175 SELECT UNIT_OF_MEASURE INTO X_charge_unit_of_measure
1176 from mtl_units_of_measure_vl --Bug 5174403
1177 WHERE UOM_CODE = X_charge_periodicity_code;
1178 END IF;
1179 EXCEPTION
1180 WHEN NO_DATA_FOUND THEN
1181 X_charge_unit_of_measure := NULL;
1182 END;
1183
1184 BEGIN
1185 IF X_inv_item_status_code IS NOT NULL THEN
1186 select inventory_item_status_code_tl INTO X_inv_item_status_code_tl
1187 from mtl_item_status
1188 where inventory_item_status_code = X_inv_item_status_code;
1189 END IF;
1190 EXCEPTION
1191 WHEN NO_DATA_FOUND THEN
1192 X_inv_item_status_code_tl := NULL;
1193 END;
1194
1195 END Populate_Fields;
1196
1197
1198 -- Function to check on entry if source organization is valid
1199 -- Returns: 0 - source org is ok
1200 -- 1 - source org does not have item and/or does not use
1201 -- the same set of books
1202 -- 2 - sub is nettable or null
1203 -- 3 - interorg network is not defined
1204 --
1205 FUNCTION Validate_Source_Org(X_org_id number,
1206 X_item_id number,
1207 X_new_item_id number,
1208 X_source_org number,
1209 X_mrp_plan number,
1210 X_source_sub varchar2
1211 ) return number is
1212 source_item number;
1213 nettable_sub number;
1214 org_network number;
1215
1216 begin
1217
1218 -- If this returns a row, item exists in source org
1219 /* Fix for bug 5844510-Commented the below query.
1220 select count(1)
1221 into source_item
1222 from dual
1223 where X_source_org in (
1224 select organization_id
1225 from mtl_system_items
1226 where (inventory_item_id = nvl(X_new_item_id, -11)
1227 or inventory_item_id = nvl(X_item_id, -11))
1228 )
1229 and rownum = 1;
1230 */
1231 /* Fix for bug 5844510- Replaced the above query with one below. This was done to improve its performance.
1232 Here it is sufficient to check whether the item exists in the source org.
1233 Note that source_org is already validated against OOD view in INVPVD6B.pls*/
1234
1235 select count(1)
1236 into source_item
1237 from mtl_system_items_b
1238 where (inventory_item_id = nvl(X_new_item_id, -11)
1239 or inventory_item_id = nvl(X_item_id, -11))
1240 and organization_id= X_source_org;
1241
1242 if (source_item = 0) then
1243 return(1);
1244 end if;
1245
1246 if (X_source_org = X_org_id) then
1247 -- return code of 2 does not seem to be used anywhere.
1248 -- however, in future if to be used, mrp_plan of 3,7,9 to function
1249 -- similar. enhancement for MRP planning code attribute
1250 if (X_mrp_plan = 3 or
1251 X_mrp_plan = 7 or
1252 X_mrp_plan = 9 ) then
1253 -- If this returns a row, the sub is nettable or null
1254 select count(1)
1255 into nettable_sub
1256 from mtl_secondary_inventories
1257 where secondary_inventory_name=nvl(X_source_sub, secondary_inventory_name)
1258 and availability_type = 1
1259 and rownum = 1;
1260 end if;
1261
1262 if (nettable_sub = 1) then
1263 return(2);
1264 end if;
1265
1266 else
1267 -- If this returns a row, the interorg network is defined
1268 select count(1)
1269 into org_network
1270 from mtl_interorg_parameters
1271 where to_organization_id = X_org_id
1272 and from_organization_id = X_source_org
1273 and rownum = 1;
1274
1275 -- No org network defined, so return 3
1276 if (org_network = 0) then
1277 return(3);
1278 end if;
1279
1280 end if; -- if source_org = org_id
1281
1282 return(0);
1283
1284 end Validate_Source_Org;
1285
1286 --2463543 :Below is used in Item Search Form. Exclusively Built for INVIVCSU
1287 PROCEDURE Item_Search_Execute_Query
1288 (p_grp_handle_id IN NUMBER,
1289 p_org_id IN NUMBER DEFAULT NULL,
1290 p_item_mask IN VARCHAR2 DEFAULT NULL,
1291 p_item_description IN VARCHAR2 DEFAULT NULL,
1292 p_base_item_id IN NUMBER DEFAULT NULL,
1293 p_status IN VARCHAR2 DEFAULT NULL,
1294 p_catalog_grp_id IN NUMBER DEFAULT NULL,
1295 p_catalog_complete IN VARCHAR2 DEFAULT NULL,
1296 p_manufacturer_id IN NUMBER DEFAULT NULL,
1297 p_mfg_part_num IN VARCHAR2 DEFAULT NULL,
1298 p_vendor_id IN NUMBER DEFAULT NULL,
1299 p_default_assignment IN VARCHAR2 DEFAULT NULL,
1300 p_vendor_product_num IN VARCHAR2 DEFAULT NULL,
1301 p_contract IN VARCHAR2 DEFAULT NULL,
1302 p_blanket_agreement IN VARCHAR2 DEFAULT NULL,
1303 p_xref_type IN dbms_sql.Varchar2_Table,
1304 p_xref_val IN dbms_sql.Varchar2_Table,
1305 p_relationship_type IN dbms_sql.Number_Table,
1306 p_related_item IN dbms_sql.Number_Table,
1307 p_category_set IN dbms_sql.Number_Table,
1308 p_category_id IN dbms_sql.Number_Table,
1309 p_element_name IN dbms_sql.Varchar2_Table,
1310 p_element_val IN dbms_sql.Varchar2_Table) IS
1311
1312 l_cursor NUMBER;
1313 l_rowcount NUMBER;
1314 sql_stmt VARCHAR2(30000);
1315
1316 l_supplier_stmt VARCHAR2(2000);
1317 l_sup_tab_list VARCHAR2(200);
1318 l_sup_where_clause VARCHAR2(2000);
1319
1320 l_xref_stmt VARCHAR2(2000);
1321 l_xref_row_stmt VARCHAR2(2000);
1322 l_xref_bind NUMBER := 1;
1323
1324 l_relation_stmt VARCHAR2(2000);
1325 l_relation_row_stmt VARCHAR2(2000);
1326 l_relation_bind NUMBER := 1;
1327
1328 l_category_stmt VARCHAR2(2000);
1329 l_category_row_stmt VARCHAR2(2000);
1330 l_category_bind NUMBER := 1;
1331
1332 l_element_stmt VARCHAR2(2000);
1333 l_element_row_stmt VARCHAR2(2000);
1334 l_element_bind NUMBER := 1;
1335 BEGIN
1336
1337 l_cursor := DBMS_SQL.OPEN_CURSOR;
1338 sql_stmt := 'INSERT INTO MTL_CATALOG_SEARCH_ITEMS ( ' ||
1339 ' SELECT :handle, MSI.INVENTORY_ITEM_ID, '||
1340 ' MSI.ORGANIZATION_ID,MSI.DESCRIPTION, '||
1341 ' MSI.PRIMARY_UOM_CODE, MSI.RESERVABLE_TYPE '||
1342 ' FROM MTL_SYSTEM_ITEMS_VL MSI '||
1343 ' WHERE 1= 1 ';
1344
1345 IF p_org_id IS NOT NULL THEN
1346 sql_stmt := sql_stmt || ' AND MSI.ORGANIZATION_ID = :p_org_id ';
1347 END IF;
1348
1349 IF p_item_mask IS NOT NULL THEN
1350 sql_stmt := sql_stmt || ' AND MSI.CONCATENATED_SEGMENTS LIKE :p_item_mask ';
1351 END IF;
1352
1353 -- FP bug fix for 12.1.1. The bug # is 7303779
1354 -- Fixed by Sean on 10/14/08. Added upper function
1355 -- to make the description search case insensitive
1356 IF p_item_description IS NOT NULL THEN
1357 sql_stmt := sql_stmt || ' AND upper(MSI.DESCRIPTION) LIKE upper(:p_item_description) ';
1358 END IF;
1359 -- END of fix 730799
1360 IF p_base_item_id IS NOT NULL THEN
1361 sql_stmt := sql_stmt || ' AND MSI.BASE_ITEM_ID = :p_base_item_id ' ;
1362 END IF;
1363
1364 IF p_status IS NOT NULL THEN
1365 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_STATUS_CODE = :p_status ';
1366 END IF;
1367
1368 IF p_catalog_grp_id IS NOT NULL THEN
1369 sql_stmt := sql_stmt || ' AND MSI.ITEM_CATALOG_GROUP_ID = :p_catalog_grp_id ';
1370 END IF;
1371
1372 IF p_catalog_complete IS NOT NULL THEN
1373 sql_stmt := sql_stmt || ' AND MSI.CATALOG_STATUS_FLAG = :p_catalog_complete ';
1374 END IF;
1375
1376 -- Start Purchase Details Query Building
1377 IF p_manufacturer_id IS NOT NULL THEN
1378 sql_stmt := sql_stmt || ' AND EXISTS (SELECT NULL FROM MTL_MFG_PART_NUMBERS MPN ' ||
1379 ' WHERE MPN.MANUFACTURER_ID = :p_manufacturer_id '||
1380 ' AND MPN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1381 IF p_mfg_part_num IS NOT NULL THEN
1382 sql_stmt := sql_stmt || ' AND MPN.MFG_PART_NUM = :p_mfg_part_num ';
1383 END IF;
1384 sql_stmt := sql_stmt || ')';
1385 END IF;
1386
1387 IF p_vendor_id IS NOT NULL THEN
1388 l_sup_tab_list := 'MRP_SOURCING_RULES MS, MRP_SR_ASSIGNMENTS MA, MRP_SR_RECEIPT_ORG MR, MRP_SR_SOURCE_ORG M1';
1389 l_sup_where_clause := 'M1.VENDOR_ID = :p_vendor_id AND MA.assignment_set_id = :p_default_assignment ' ||
1390 'AND MA.sourcing_rule_id = MS.sourcing_rule_id AND MR.sr_receipt_id = M1.sr_receipt_id ' ||
1391 'AND MA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1392 END IF;
1393
1394 IF p_vendor_product_num IS NOT NULL THEN
1395 IF l_sup_tab_list IS NOT NULL THEN
1396 l_sup_tab_list := l_sup_tab_list || ', ';
1397 END IF;
1398 l_sup_tab_list := l_sup_tab_list || 'PO_LINES_ALL L';
1399 IF l_sup_where_clause IS NOT NULL THEN
1400 l_sup_where_clause := l_sup_where_clause || ' AND ';
1401 END IF;
1402 l_sup_where_clause := l_sup_where_clause || 'L.VENDOR_PRODUCT_NUM = :p_vendor_product_num ' ||
1403 'AND L.ITEM_ID = MSI.INVENTORY_ITEM_ID';
1404 END IF;
1405
1406 IF p_contract IS NOT NULL THEN
1407 IF l_sup_tab_list IS NOT NULL THEN
1408 l_sup_tab_list := l_sup_tab_list || ', ';
1409 END IF;
1410 l_sup_tab_list := l_sup_tab_list || 'PO_HEADERS_ALL H';
1411 IF INSTR(l_sup_tab_list,'PO_LINES_ALL') = 0 THEN
1412 l_sup_tab_list := l_sup_tab_list || ',PO_LINES_ALL L';
1413 IF l_sup_where_clause IS NOT NULL THEN
1414 l_sup_where_clause := l_sup_where_clause || ' AND ';
1415 END IF;
1416 l_sup_where_clause := l_sup_where_clause || ' L.ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1417 END IF;
1418 IF l_sup_where_clause IS NOT NULL THEN
1419 l_sup_where_clause := l_sup_where_clause || ' AND ';
1420 END IF;
1421 l_sup_where_clause := l_sup_where_clause || 'H.SEGMENT1 = :p_contract AND H.PO_HEADER_ID = L.PO_HEADER_ID';
1422 END IF;
1423
1424 IF p_blanket_agreement IS NOT NULL THEN
1425 IF l_sup_tab_list IS NOT NULL THEN
1426 l_sup_tab_list := l_sup_tab_list || ', ';
1427 END IF;
1428 l_sup_tab_list := l_sup_tab_list || 'PO_HEADERS_ALL H2';
1429 IF INSTR(l_sup_tab_list,'PO_LINES_ALL') = 0 THEN
1430 l_sup_tab_list := l_sup_tab_list || ', PO_LINES_ALL L';
1431 IF l_sup_where_clause IS NOT NULL THEN
1432 l_sup_where_clause := l_sup_where_clause || ' AND ';
1433 END IF;
1434 l_sup_where_clause := l_sup_where_clause || ' L.ITEM_ID = MSI.INVENTORY_ITEM_ID ';
1435 END IF;
1436
1437 ----- Bug 10433088 : ORA-9007 : missing right parenthesis being thrown while searching for blanket agreement as an AND is missing
1438
1439 IF l_sup_where_clause IS NOT NULL THEN
1440 l_sup_where_clause := l_sup_where_clause || ' AND ';
1441 END IF;
1442
1443
1444 l_sup_where_clause := l_sup_where_clause || 'H2.SEGMENT1 = :p_blanket_agreement ' ||
1445 ' AND H2.PO_HEADER_ID = L.PO_HEADER_ID';
1446 END IF;
1447
1448 IF l_sup_tab_list IS NOT NULL THEN
1449 l_supplier_stmt := 'SELECT NULL FROM ' || l_sup_tab_list || ' WHERE ' || l_sup_where_clause;
1450 sql_stmt := sql_stmt || ' AND EXISTS (' || l_supplier_stmt || ')';
1451 END IF;
1452
1453 -- Start Xref Details Query Building
1454 IF p_xref_type.COUNT <> 0 THEN
1455 FOR i IN p_xref_type.FIRST .. p_xref_type.LAST LOOP
1456 l_xref_row_stmt := '(SELECT MCR.INVENTORY_ITEM_ID FROM MTL_CROSS_REFERENCES MCR ' ||
1457 ' WHERE MCR.CROSS_REFERENCE_TYPE = :xref_type' || l_xref_bind ||
1458 ' AND MCR.CROSS_REFERENCE = :xref_val' || l_xref_bind || ')';
1459 l_xref_bind := l_xref_bind + 1;
1460 IF l_xref_stmt IS NOT NULL THEN
1461 l_xref_stmt := l_xref_stmt || ' INTERSECT ';
1462 END IF;
1463 l_xref_stmt := l_xref_stmt || l_xref_row_stmt;
1464 END LOOP;
1465 END IF;
1466 IF l_xref_stmt IS NOT NULL THEN
1467 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_xref_stmt || ')';
1468 END IF;
1469
1470 -- Start Item Relation Query Building.
1471 IF p_relationship_type.COUNT <> 0 THEN
1472 FOR i IN p_relationship_type.FIRST .. p_relationship_type.LAST LOOP
1473 l_relation_row_stmt := '(SELECT MRI.INVENTORY_ITEM_ID FROM MTL_RELATED_ITEMS_VIEW MRI ' ||
1474 ' WHERE MRI.RELATIONSHIP_TYPE_ID = :relation_type' || l_relation_bind ||
1475 ' AND MRI.RELATED_ITEM_ID = :related_item' || l_relation_bind || ')';
1476 l_relation_bind := l_relation_bind + 1;
1477 IF l_relation_stmt IS NOT NULL THEN
1478 l_relation_stmt := l_relation_stmt || ' INTERSECT ';
1479 END IF;
1480 l_relation_stmt := l_relation_stmt || l_relation_row_stmt;
1481 END LOOP;
1482 END IF;
1483 IF l_relation_stmt IS NOT NULL THEN
1484 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_relation_stmt || ')';
1485 END IF;
1486
1487 -- Start Item Category Query Building.
1488 IF p_category_set.COUNT <> 0 THEN
1489 FOR i IN p_category_set.FIRST .. p_category_set.LAST LOOP
1490 l_category_row_stmt := '(SELECT MIC.INVENTORY_ITEM_ID FROM MTL_ITEM_CATEGORIES MIC ' ||
1491 ' WHERE MIC.CATEGORY_SET_ID = :category_set' || l_category_bind ||
1492 ' AND MIC.CATEGORY_ID = :category_id' || l_category_bind ||
1493 ' AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID)';
1494 l_category_bind := l_category_bind + 1;
1495 IF l_category_stmt IS NOT NULL THEN
1496 l_category_stmt := l_category_stmt || ' INTERSECT ';
1497 END IF;
1498 l_category_stmt := l_category_stmt || l_category_row_stmt;
1499 END LOOP;
1500
1501 END IF;
1502 IF l_category_stmt IS NOT NULL THEN
1503 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_category_stmt || ')';
1504 END IF;
1505
1506 -- Start Item Catalog Group Query Building.
1507 IF p_element_name.COUNT <> 0 THEN
1508 FOR i IN p_element_name.FIRST .. p_element_name.LAST LOOP
1509 IF(p_element_val(i) IS NOT NULL) THEN
1510 l_element_row_stmt := '(SELECT DEV.INVENTORY_ITEM_ID FROM MTL_DESCR_ELEMENT_VALUES DEV ' ||
1511 ' WHERE DEV.ELEMENT_NAME = :element_name' || l_element_bind ||
1512 ' AND DEV.ELEMENT_VALUE = :element_val' || l_element_bind || ')';
1513 l_element_bind := l_element_bind + 1;
1514 IF l_element_stmt IS NOT NULL THEN
1515 l_element_stmt := l_element_stmt || ' INTERSECT ';
1516 END IF;
1517 l_element_stmt := l_element_stmt || l_element_row_stmt;
1518 END IF;
1519 END LOOP;
1520 END IF;
1521 IF l_element_stmt IS NOT NULL THEN
1522 sql_stmt := sql_stmt || ' AND MSI.INVENTORY_ITEM_ID IN (' || l_element_stmt || ')';
1523 END IF;
1524
1525 sql_stmt := sql_stmt || ')';
1526
1527 DBMS_SQL.PARSE(l_cursor, sql_stmt, dbms_sql.native);
1528 DBMS_SQL.BIND_VARIABLE(l_cursor, 'handle', p_grp_handle_id);
1529
1530 IF p_org_id IS NOT NULL THEN
1531 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_org_id', p_org_id);
1532 END IF;
1533
1534 IF p_item_mask IS NOT NULL THEN
1535 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_item_mask', p_item_mask);
1536 END IF;
1537
1538 IF p_item_description IS NOT NULL THEN
1539 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_item_description', p_item_description);
1540 END IF;
1541
1542 IF p_base_item_id IS NOT NULL THEN
1543 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_base_item_id', p_base_item_id);
1544 END IF;
1545
1546 IF p_status IS NOT NULL THEN
1547 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_status', p_status);
1548 END IF;
1549
1550 IF p_catalog_grp_id IS NOT NULL THEN
1551 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_catalog_grp_id', p_catalog_grp_id);
1552 END IF;
1553
1554 IF p_catalog_complete IS NOT NULL THEN
1555 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_catalog_complete', p_catalog_complete);
1556 END IF;
1557
1558 IF p_manufacturer_id IS NOT NULL THEN
1559 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_manufacturer_id', p_manufacturer_id);
1560 IF p_mfg_part_num IS NOT NULL THEN
1561 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_mfg_part_num' , p_mfg_part_num);
1562 END IF;
1563 END IF;
1564
1565 IF p_vendor_id IS NOT NULL THEN
1566 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_vendor_id', p_vendor_id);
1567 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_default_assignment', p_default_assignment);
1568 END IF;
1569
1570 IF p_vendor_product_num IS NOT NULL THEN
1571 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_vendor_product_num', p_vendor_product_num);
1572 END IF;
1573
1574 IF p_contract IS NOT NULL THEN
1575 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_contract', p_contract);
1576 END IF;
1577
1578 IF p_blanket_agreement IS NOT NULL THEN
1579 DBMS_SQL.BIND_VARIABLE(l_cursor, 'p_blanket_agreement', p_blanket_agreement);
1580 END IF;
1581
1582 IF p_xref_type.COUNT <> 0 THEN
1583 l_xref_bind := 1;
1584 FOR i IN p_xref_type.FIRST .. p_xref_type.LAST LOOP
1585 DBMS_SQL.BIND_VARIABLE(l_cursor, 'xref_type' || l_xref_bind , p_xref_type(i));
1586 DBMS_SQL.BIND_VARIABLE(l_cursor, 'xref_val' || l_xref_bind , p_xref_val(i));
1587 l_xref_bind := l_xref_bind + 1;
1588 END LOOP;
1589 END IF;
1590
1591 IF p_relationship_type.COUNT <> 0 THEN
1592 l_relation_bind := 1;
1593 FOR i IN p_relationship_type.FIRST .. p_relationship_type.LAST LOOP
1594 DBMS_SQL.BIND_VARIABLE(l_cursor, 'relation_type' || l_relation_bind, p_relationship_type(i));
1595 DBMS_SQL.BIND_VARIABLE(l_cursor, 'related_item' || l_relation_bind, p_related_item(i));
1596 l_relation_bind := l_relation_bind + 1;
1597 END LOOP;
1598 END IF;
1599
1600 IF p_category_set.COUNT <> 0 THEN
1601 l_category_bind := 1;
1602 FOR i IN p_category_set.FIRST .. p_category_set.LAST LOOP
1603 DBMS_SQL.BIND_VARIABLE(l_cursor, 'category_set' || l_category_bind, p_category_set(i));
1604 DBMS_SQL.BIND_VARIABLE(l_cursor, 'category_id' || l_category_bind, p_category_id(i));
1605 l_category_bind := l_category_bind + 1;
1606 END LOOP;
1607 END IF;
1608
1609 IF p_element_name.COUNT <> 0 THEN
1610 l_element_bind := 1;
1611 FOR i IN p_element_name.FIRST .. p_element_name.LAST LOOP
1612 IF(p_element_val(i) IS NOT NULL) THEN
1613 DBMS_SQL.BIND_VARIABLE(l_cursor, 'element_name' || l_element_bind, p_element_name(i));
1614 DBMS_SQL.BIND_VARIABLE(l_cursor, 'element_val' || l_element_bind, p_element_val(i));
1615 l_element_bind := l_element_bind + 1;
1616 END IF;
1617 END LOOP;
1618 END IF;
1619
1620 l_rowcount := DBMS_SQL.EXECUTE(l_cursor);
1621
1622 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1623
1624 END;
1625
1626 END INVIDIT1;
[Home] [Help]