[Home] [Help]
411: , pl.item_revision
412: , msi.concatenated_segments
413: , msi.outside_operation_flag
414: from po_lines_all pl
415: , mtl_system_items_vl msi
416: where pl.item_id = msi.inventory_item_id (+)
417: and Nvl(msi.organization_id, p_organization_id) = p_organization_id
418: and pl.po_header_id = p_po_header_id
419: and exists (SELECT 'Valid PO Shipments'
459: FROM rcv_supply rsup
460: -- bug 2805640
461: , mtl_units_of_measure mum
462: , po_lines_all pl
463: , mtl_system_items_vl msi
464: WHERE rsup.po_line_id = pl.po_line_id
465: -- bug 2805640
466: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
467: AND pl.item_id = msi.inventory_item_id (+)
2885: po_line_number := p_po_line_num;
2886: END;
2887: IF p_mobile_form = 'RECEIPT' THEN
2888: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
2889: 'MTL_SYSTEM_ITEMS_VL' and item desc is selected from this table.*/
2890: OPEN x_po_line_num_lov FOR
2891: -- Bug 6437363 : Modified the query for better performance.
2892: select distinct pl.line_num
2893: , pl.po_line_id
2901: pl.Item_Id)
2902: uom_code
2903: from po_lines_all pl
2904: , mtl_units_of_measure mum
2905: , mtl_system_items_vl msi
2906: where pl.item_id = msi.inventory_item_id (+)
2907: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
2908: and msi.organization_id = p_organization_id -- Bug 6437363
2909: and pl.po_header_id = p_po_header_id
2945: pl.Item_Id)
2946: uom_code
2947: from po_lines_all pl
2948: , mtl_units_of_measure mum
2949: , mtl_system_items_vl msi
2950: , mtl_related_items mri
2951: where msi.organization_id = p_organization_id -- Bug 6437363
2952: and msi.inventory_item_id = p_inventory_item_id -- Bug 6311550
2953: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
3003: uom_code
3004: FROM rcv_supply rsup
3005: , mtl_units_of_measure mum
3006: , po_lines_all pl
3007: , mtl_system_items_vl msi
3008: WHERE rsup.po_line_id = pl.po_line_id
3009: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
3010: AND pl.item_id = msi.inventory_item_id (+)
3011: and Nvl(msi.organization_id, p_organization_id) = p_organization_id
3239: --- releaseBased PJM Transaction
3240: -- *****************************
3241: open x_items for
3242: -- Bug# 6747729
3243: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3244: select concatenated_segments,
3245: inventory_item_id,
3246: description,
3247: Nvl(revision_qty_control_code,1),
3286: NVL(TRACKING_QUANTITY_IND,'P'),
3287: NVL(DUAL_UOM_DEVIATION_HIGH,0),
3288: NVL(DUAL_UOM_DEVIATION_LOW,0),
3289: stock_enabled_flag
3290: from mtl_system_items_vl /* Bug 5581528 */
3291: WHERE organization_id = p_Organization_Id
3292: and concatenated_segments like p_concatenated_segments
3293: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3294: and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
3324: )
3325: UNION ALL
3326: -- Substitute Item SQL
3327: -- Bug# 6747729
3328: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3329: select distinct msi.concatenated_segments,
3330: msi.inventory_item_id,
3331: msi.description,
3332: Nvl(msi.revision_qty_control_code,1),
3373: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
3374: msi.stock_enabled_flag
3375: from po_lines_all pol
3376: ,mtl_related_items mri
3377: ,mtl_system_items_vl msi /* Bug 5581528 */
3378: /*,mtl_system_items_kfv msia */ /* Bug 6334679*/
3379: where msi.organization_id = p_organization_id
3380: and msi.concatenated_segments like p_concatenated_segments
3381: and pol.po_header_id = p_poHeaderID
3417: )
3418: UNION ALL
3419: -- Vendor Item SQL
3420: -- Bug# 6747729
3421: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3422: select distinct pol.vendor_product_num,
3423: msi.inventory_item_id,
3424: msi.description,
3425: Nvl(msi.revision_qty_control_code,1),
3465: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
3466: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
3467: msi.stock_enabled_flag
3468: from po_lines_all pol
3469: , mtl_system_items_vl msi /* Bug 5581528 */
3470: where organization_id = p_organization_id
3471: and pol.vendor_product_num like p_concatenated_segments
3472: and pol.item_id = msi.inventory_item_id
3473: and pol.vendor_product_num IS NOT NULL
3506: )
3507: UNION ALL
3508: -- non item Master
3509: -- Bug# 6747729
3510: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3511: select distinct pol.item_description,
3512: to_number(''),
3513: pol.item_description,
3514: 1,
3596: --End of fix for Bug 3972931
3597: UNION ALL
3598: -- Cross Ref SQL
3599: -- Bug# 6747729
3600: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3601: select distinct mcr.cross_reference,
3602: msi.inventory_item_id,
3603: msi.description,
3604: Nvl(msi.revision_qty_control_code,1),
3644: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
3645: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
3646: msi.stock_enabled_flag
3647: from po_lines_all pol
3648: ,mtl_system_items_vl msi /* Bug 5581528 */
3649: ,mtl_cross_references mcr
3650: where msi.organization_id = p_organization_id
3651: and ( (mcr.cross_reference_type = p_crossreftype
3652: and mcr.cross_reference like p_concatenated_segments
3699: ----- lineBased PJM Transaction
3700: -- *****************************
3701: open x_items for
3702: -- Bug# 6747729
3703: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3704: select concatenated_segments,
3705: inventory_item_id,
3706: description,
3707: Nvl(revision_qty_control_code,1),
3746: NVL(TRACKING_QUANTITY_IND,'P'),
3747: NVL(DUAL_UOM_DEVIATION_HIGH,0),
3748: NVL(DUAL_UOM_DEVIATION_LOW,0),
3749: stock_enabled_flag
3750: from mtl_system_items_vl /* Bug 5581528 */
3751: WHERE organization_id = p_Organization_Id
3752: and concatenated_segments like p_concatenated_segments
3753: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3754: and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol WHERE
3785: )
3786: UNION ALL
3787: -- Substitute Item SQL
3788: -- Bug# 6747729
3789: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3790: select distinct msi.concatenated_segments,
3791: msi.inventory_item_id,
3792: msi.description,
3793: Nvl(msi.revision_qty_control_code,1),
3834: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
3835: msi.stock_enabled_flag
3836: from po_lines_all pol
3837: ,mtl_related_items mri
3838: ,mtl_system_items_vl msi /* Bug 5581528 */
3839: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
3840: where msi.organization_id = p_organization_id
3841: and msi.concatenated_segments like p_concatenated_segments
3842: and pol.po_header_id = p_poHeaderID
3879: )
3880: UNION ALL
3881: -- Vendor Item SQL
3882: -- Bug# 6747729
3883: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3884: select distinct pol.vendor_product_num,
3885: msi.inventory_item_id,
3886: msi.description,
3887: Nvl(msi.revision_qty_control_code,1),
3927: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
3928: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
3929: msi.stock_enabled_flag
3930: from po_lines_all pol
3931: , mtl_system_items_vl msi /* Bug 5581528 */
3932: where organization_id = p_organization_id
3933: and pol.vendor_product_num like p_concatenated_segments
3934: and pol.item_id = msi.inventory_item_id
3935: and pol.vendor_product_num IS NOT NULL
3969: )
3970: UNION ALL
3971: -- non item Master
3972: -- Bug# 6747729
3973: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3974: select distinct pol.item_description,
3975: to_number(''),
3976: pol.item_description,
3977: 1,
4060: --End of fix for Bug 3972931
4061: UNION ALL
4062: -- Cross Ref SQL
4063: -- Bug# 6747729
4064: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4065: select distinct mcr.cross_reference,
4066: msi.inventory_item_id,
4067: msi.description,
4068: Nvl(msi.revision_qty_control_code,1),
4108: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
4109: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4110: msi.stock_enabled_flag
4111: from po_lines_all pol
4112: ,mtl_system_items_vl msi /* Bug 5581528 */
4113: ,mtl_cross_references mcr
4114: where msi.organization_id = p_organization_id
4115: and ( (mcr.cross_reference_type = p_crossreftype
4116: and mcr.cross_reference like p_concatenated_segments
4171: --
4172: -- *****************************
4173: open x_items for
4174: -- Bug# 6747729
4175: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4176: select concatenated_segments,
4177: inventory_item_id,
4178: description,
4179: Nvl(revision_qty_control_code,1),
4182: Nvl(restrict_subinventories_code, 2),
4183: Nvl(restrict_locators_code, 2),
4184: Nvl(location_control_code, 1),
4185: primary_uom_code,
4186: Nvl(mtl_system_items_vl.inspection_required_flag, 'N'), -- bug 4610452
4187: Nvl(shelf_life_code, 1),
4188: Nvl(shelf_life_days,0),
4189: Nvl(allowed_units_lookup_code, 2),
4190: Nvl(effectivity_control,1),
4218: NVL(TRACKING_QUANTITY_IND,'P'),
4219: NVL(DUAL_UOM_DEVIATION_HIGH,0),
4220: NVL(DUAL_UOM_DEVIATION_LOW,0),
4221: stock_enabled_flag
4222: from mtl_system_items_vl , /* Bug 5581528 */
4223: po_lines_all pol -- bug 4602289
4224: WHERE organization_id = p_Organization_Id
4225: and concatenated_segments like p_concatenated_segments
4226: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4257:
4258: UNION ALL
4259: -- Substitute Item SQL
4260: -- Bug# 6747729
4261: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4262: select distinct msi.concatenated_segments,
4263: msi.inventory_item_id,
4264: msi.description,
4265: Nvl(msi.revision_qty_control_code,1),
4306: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4307: msi.stock_enabled_flag
4308: from po_lines_all pol
4309: ,mtl_related_items mri
4310: ,mtl_system_items_vl msi /* Bug 5581528 */
4311: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
4312: where msi.organization_id = p_organization_id
4313: and msi.concatenated_segments like p_concatenated_segments
4314: and pol.po_header_id = p_poHeaderID
4349: )
4350: UNION ALL
4351: -- Vendor Item SQL
4352: -- Bug# 6747729
4353: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4354: select /*+ INDEX(PO_LINES_ALL PO_LINES_U2) */ --bug 4602289
4355: distinct pol.vendor_product_num,
4356: msi.inventory_item_id,
4357: msi.description,
4398: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
4399: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4400: msi.stock_enabled_flag
4401: from po_lines_all pol
4402: , mtl_system_items_vl msi /* Bug 5581528 */
4403: where organization_id = p_organization_id
4404: and pol.vendor_product_num like p_concatenated_segments
4405: and pol.item_id = msi.inventory_item_id
4406: and pol.vendor_product_num IS NOT NULL
4439: )
4440: UNION ALL
4441: -- non item Master
4442: -- Bug# 6747729
4443: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4444: select distinct pol.item_description,
4445: to_number(''),
4446: pol.item_description,
4447: 1,
4530: --End of fix for Bug 3972931
4531: UNION ALL
4532: -- Cross Ref SQL
4533: -- Bug# 6747729
4534: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4535: select distinct mcr.cross_reference,
4536: msi.inventory_item_id,
4537: msi.description,
4538: Nvl(msi.revision_qty_control_code,1),
4578: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
4579: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4580: msi.stock_enabled_flag
4581: from po_lines_all pol
4582: ,mtl_system_items_vl msi /* Bug 5581528 */
4583: ,mtl_cross_references mcr
4584: where msi.organization_id = p_organization_id
4585: and ( (mcr.cross_reference_type = p_crossreftype
4586: and mcr.cross_reference like p_concatenated_segments
4687: NVL(TRACKING_QUANTITY_IND,'P'),
4688: NVL(DUAL_UOM_DEVIATION_HIGH,0),
4689: NVL(DUAL_UOM_DEVIATION_LOW,0),
4690: stock_enabled_flag
4691: from mtl_system_items_vl /* Bug 5581528 */
4692: WHERE organization_id = p_Organization_Id
4693: and concatenated_segments like p_concatenated_segments
4694: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4695: and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
4767: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4768: msi.stock_enabled_flag
4769: from po_lines_all pol
4770: ,mtl_related_items mri
4771: ,mtl_system_items_vl msi /* Bug 5581528 */
4772: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
4773: where msi.organization_id = p_organization_id
4774: and msi.concatenated_segments like p_concatenated_segments
4775: and pol.po_header_id = p_poHeaderID
4852: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
4853: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
4854: msi.stock_enabled_flag
4855: from po_lines_all pol
4856: , mtl_system_items_vl msi /* Bug 5581528 */
4857: where organization_id = p_organization_id
4858: and pol.vendor_product_num like p_concatenated_segments
4859: and pol.item_id = msi.inventory_item_id
4860: and pol.vendor_product_num IS NOT NULL
5018: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
5019: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5020: msi.stock_enabled_flag
5021: from po_lines_all pol
5022: ,mtl_system_items_vl msi /* Bug 5581528 */
5023: ,mtl_cross_references mcr
5024: where msi.organization_id = p_organization_id
5025: and ( (mcr.cross_reference_type = p_crossreftype
5026: and mcr.cross_reference like p_concatenated_segments
5112: NVL(TRACKING_QUANTITY_IND,'P'),
5113: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5114: NVL(DUAL_UOM_DEVIATION_LOW,0),
5115: stock_enabled_flag
5116: from mtl_system_items_vl /* Bug 5581528 */
5117: WHERE organization_id = p_Organization_Id
5118: and concatenated_segments like p_concatenated_segments
5119: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5120: and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
5191: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5192: msi.stock_enabled_flag
5193: from po_lines_all pol
5194: ,mtl_related_items mri
5195: ,mtl_system_items_vl msi /* Bug 5581528 */
5196: /*,mtl_system_items_kfv msia*/ /* Bug 6334679 */
5197: where msi.organization_id = p_organization_id
5198: and msi.concatenated_segments like p_concatenated_segments
5199: and pol.po_header_id = p_poHeaderID
5275: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
5276: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5277: msi.stock_enabled_flag
5278: from po_lines_all pol
5279: ,mtl_system_items_vl msi /* Bug 5581528 */
5280: where organization_id = p_organization_id
5281: and pol.vendor_product_num like p_concatenated_segments
5282: and pol.item_id = msi.inventory_item_id
5283: and pol.vendor_product_num IS NOT NULL
5438: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
5439: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5440: msi.stock_enabled_flag
5441: from po_lines_all pol
5442: ,mtl_system_items_vl msi /* Bug 5581528 */
5443: ,mtl_cross_references mcr
5444: where msi.organization_id = p_organization_id
5445: and ( (mcr.cross_reference_type = p_crossreftype
5446: and mcr.cross_reference like p_concatenated_segments
5532: NVL(TRACKING_QUANTITY_IND,'P'),
5533: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5534: NVL(DUAL_UOM_DEVIATION_LOW,0),
5535: stock_enabled_flag
5536: from mtl_system_items_vl /* Bug 5581528 */
5537: WHERE organization_id = p_Organization_Id
5538: and concatenated_segments like p_concatenated_segments
5539: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5540: and inventory_item_id IN
5610: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5611: msi.stock_enabled_flag
5612: from po_lines_all pol
5613: ,mtl_related_items mri
5614: ,mtl_system_items_vl msi /* Bug 5581528 */
5615: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
5616: where msi.organization_id = p_organization_id
5617: and msi.concatenated_segments like p_concatenated_segments
5618: and pol.po_header_id = p_poHeaderID
5693: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
5694: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5695: msi.stock_enabled_flag
5696: from po_lines_all pol
5697: , mtl_system_items_vl msi /* Bug 5581528 */
5698: where organization_id = p_organization_id
5699: and pol.vendor_product_num like p_concatenated_segments
5700: and pol.item_id = msi.inventory_item_id
5701: and pol.vendor_product_num IS NOT NULL
5856: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
5857: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
5858: msi.stock_enabled_flag
5859: from po_lines_all pol
5860: ,mtl_system_items_vl msi /* Bug 5581528 */
5861: ,mtl_cross_references mcr
5862: where msi.organization_id = p_organization_id
5863: and ( (mcr.cross_reference_type = p_crossreftype
5864: and mcr.cross_reference like p_concatenated_segments
5961: NVL(TRACKING_QUANTITY_IND,'P'),
5962: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5963: NVL(DUAL_UOM_DEVIATION_LOW,0),
5964: stock_enabled_flag
5965: from mtl_system_items_vl msn, /* Bug 5581528 */
5966: rcv_shipment_lines rsl
5967: WHERE msn.organization_id = p_Organization_Id
5968: and msn.concatenated_segments like p_concatenated_segments
5969: and (msn.purchasing_enabled_flag = 'Y' OR msn.stock_enabled_flag = 'Y')
6031: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
6032: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
6033: msi.stock_enabled_flag
6034: from po_lines_all pol
6035: ,mtl_system_items_vl msi /* Bug 5581528 */
6036: , rcv_shipment_lines rsl
6037: where organization_id = p_Organization_Id
6038: and pol.vendor_product_num like p_concatenated_segments
6039: and pol.item_id = msi.inventory_item_id
6211: NVL(MSN.TRACKING_QUANTITY_IND,'P'),
6212: NVL(MSN.DUAL_UOM_DEVIATION_HIGH,0),
6213: NVL(MSN.DUAL_UOM_DEVIATION_LOW,0),
6214: msn.stock_enabled_flag
6215: from mtl_system_items_vl msn, /* Bug 5581528 */
6216: rcv_shipment_lines rsl,
6217: mtl_cross_references mcr
6218: WHERE msn.organization_id = p_Organization_Id
6219: and ( mcr.cross_reference_type = g_gtin_cross_ref_type
6294: NVL(TRACKING_QUANTITY_IND,'P'),
6295: NVL(DUAL_UOM_DEVIATION_HIGH,0),
6296: NVL(DUAL_UOM_DEVIATION_LOW,0),
6297: stock_enabled_flag
6298: from mtl_system_items_vl /* Bug 5581528 */
6299: WHERE organization_id = p_Organization_Id
6300: and concatenated_segments like p_concatenated_segments
6301: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
6302: and inventory_item_id IN (SELECT oel.inventory_item_id FROM
6359: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
6360: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
6361: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
6362: msi.stock_enabled_flag
6363: from mtl_system_items_vl msi /* Bug 5581528 */
6364: ,mtl_cross_references mcr
6365: WHERE msi.organization_id = p_Organization_Id
6366: and ( mcr.cross_reference_type = g_gtin_cross_ref_type
6367: AND mcr.cross_reference LIKE g_crossref
6439: NVL(TRACKING_QUANTITY_IND,'P'),
6440: NVL(DUAL_UOM_DEVIATION_HIGH,0),
6441: NVL(DUAL_UOM_DEVIATION_LOW,0),
6442: stock_enabled_flag
6443: from mtl_system_items_vl Bug 5581528
6444: WHERE organization_id = p_Organization_Id
6445: and concatenated_segments like p_concatenated_segments
6446: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
6447: and exists (SELECT 1 FROM po_requisition_lines_all prl,
6511: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
6512: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
6513: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
6514: msi.stock_enabled_flag
6515: from mtl_system_items_vl msi Bug 5581528
6516: ,mtl_cross_references mcr
6517: WHERE msi.organization_id = p_Organization_Id
6518: and ( mcr.cross_reference_type = g_gtin_cross_ref_type
6519: AND mcr.cross_reference LIKE g_crossref
6544:
6545: --Commented the above query for Bug# 7216416
6546: --Start of Fix for Bug# 7216416
6547:
6548: SELECT /*+ leading(rsl1) use_nl(rsl1 mtl_system_items_vl)
6549: index(mtl_system_items_vl MTL_SYSTEM_ITEMS_B_U1) */
6550:
6551: concatenated_segments ,
6552: inventory_item_id ,
6545: --Commented the above query for Bug# 7216416
6546: --Start of Fix for Bug# 7216416
6547:
6548: SELECT /*+ leading(rsl1) use_nl(rsl1 mtl_system_items_vl)
6549: index(mtl_system_items_vl MTL_SYSTEM_ITEMS_B_U1) */
6550:
6551: concatenated_segments ,
6552: inventory_item_id ,
6553: description ,
6593: NVL(TRACKING_QUANTITY_IND,'P') ,
6594: NVL(DUAL_UOM_DEVIATION_HIGH,0) ,
6595: NVL(DUAL_UOM_DEVIATION_LOW,0) ,
6596: stock_enabled_flag
6597: FROM mtl_system_items_vl,
6598: /* Bug 5581528 */
6599: (
6600: SELECT DISTINCT rsl.Item_Id
6601: FROM po_Requisition_Lines_All prl,
6673: NVL(TRACKING_QUANTITY_IND,'P') ,
6674: NVL(DUAL_UOM_DEVIATION_HIGH,0) ,
6675: NVL(DUAL_UOM_DEVIATION_LOW,0) ,
6676: stock_enabled_flag
6677: FROM mtl_system_items_vl,
6678: /* Bug 5581528 */
6679: (
6680: SELECT DISTINCT rsl.Item_Id
6681: FROM po_Requisition_Lines_All prl,
6747: NVL(MSI.TRACKING_QUANTITY_IND,'P') ,
6748: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0) ,
6749: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0) ,
6750: msi.stock_enabled_flag
6751: FROM mtl_system_items_vl msi
6752: /* Bug 5581528 */
6753: ,
6754: mtl_cross_references mcr
6755: WHERE msi.organization_id = p_Organization_Id
6850: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
6851: NVL(msi.DUAL_UOM_DEVIATION_LOW,0),
6852: msi.stock_enabled_flag
6853: FROM
6854: mtl_system_items_vl msi /* Bug 5581528 */
6855: WHERE msi.organization_id = p_organization_Id
6856: AND msi.concatenated_segments LIKE p_concatenated_segments
6857: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
6858: UNION -- ALL
6908: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
6909: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
6910: msi.stock_enabled_flag
6911: FROM
6912: mtl_system_items_vl msi /* Bug 5581528 */
6913: WHERE msi.organization_id = p_organization_Id
6914: AND msi.concatenated_segments LIKE p_concatenated_segments
6915: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
6916: AND EXISTS (SELECT '1'
6993: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
6994: msi.stock_enabled_flag
6995: FROM
6996: po_lines_all pol,
6997: mtl_system_items_vl msi /* Bug 5581528 */
6998: WHERE msi.organization_id = p_organization_Id
6999: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
7000: AND pol.vendor_product_num like p_concatenated_segments
7001: AND pol.item_id = msi.inventory_item_id
7069: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
7070: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
7071: msi.stock_enabled_flag
7072: FROM
7073: mtl_system_items_vl msi, /* Bug 5581528 */
7074: mtl_cross_references mcr
7075: WHERE msi.organization_id = p_organization_Id
7076: AND ( (mcr.cross_reference_type = p_crossreftype
7077: AND mcr.cross_reference LIKE p_concatenated_segments
7223: NVL(SECONDARY_DEFAULT_IND,''),
7224: NVL(TRACKING_QUANTITY_IND,'P'),
7225: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7226: NVL(DUAL_UOM_DEVIATION_LOW,0)
7227: from mtl_system_items_vl
7228: WHERE organization_id = p_Organization_Id
7229: and concatenated_segments like p_concatenated_segments
7230: and mtl_transactions_enabled_flag = 'Y'
7231: and bom_item_type=4
7276: NVL(SECONDARY_DEFAULT_IND,''),
7277: NVL(TRACKING_QUANTITY_IND,'P'),
7278: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7279: NVL(DUAL_UOM_DEVIATION_LOW,0)
7280: from mtl_system_items_vl msik, /* Bug 5581528 */
7281: mtl_cross_references mcr
7282: WHERE msik.organization_id = p_Organization_Id
7283: AND msik.inventory_item_id = mcr.inventory_item_id
7284: AND mcr.cross_reference_type = g_gtin_cross_ref_type
7541: NVL(TRACKING_QUANTITY_IND,'P'),
7542: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7543: NVL(DUAL_UOM_DEVIATION_LOW,0)
7544: FROM
7545: mtl_system_items_vl msik /* Bug 5581528 */
7546: WHERE
7547: organization_id = p_Organization_Id
7548: AND concatenated_segments like p_concatenated_segments
7549: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
7600: NVL(TRACKING_QUANTITY_IND,'P'),
7601: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7602: NVL(DUAL_UOM_DEVIATION_LOW,0)
7603: FROM
7604: mtl_system_items_vl msik, /* Bug 5581528 */
7605: mtl_cross_references mcr
7606: WHERE
7607: msik.organization_id = p_organization_id
7608: AND msik.inventory_item_id = mcr.inventory_item_id
7667: NVL(TRACKING_QUANTITY_IND,'P'),
7668: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7669: NVL(DUAL_UOM_DEVIATION_LOW,0)
7670: FROM
7671: mtl_system_items_vl msik /* Bug 5581528 */
7672: WHERE
7673: organization_id = p_Organization_Id
7674: AND concatenated_segments like p_concatenated_segments
7675: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
7721: NVL(TRACKING_QUANTITY_IND,'P'),
7722: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7723: NVL(DUAL_UOM_DEVIATION_LOW,0)
7724: FROM
7725: mtl_system_items_vl msik, /* Bug 5581528 */
7726: mtl_cross_references mcr
7727: WHERE
7728: msik.organization_id = p_organization_id
7729: AND msik.inventory_item_id = mcr.inventory_item_id
7782: NVL(TRACKING_QUANTITY_IND,'P'),
7783: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7784: NVL(DUAL_UOM_DEVIATION_LOW,0)
7785: FROM
7786: mtl_system_items_vl msik /* Bug 5581528 */
7787: WHERE
7788: organization_id = p_Organization_Id
7789: AND concatenated_segments like p_concatenated_segments
7790: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
7836: NVL(TRACKING_QUANTITY_IND,'P'),
7837: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7838: NVL(DUAL_UOM_DEVIATION_LOW,0)
7839: FROM
7840: mtl_system_items_vl msik, /* Bug 5581528 */
7841: mtl_cross_references mcr
7842: WHERE
7843: msik.organization_id = p_organization_id
7844: AND msik.inventory_item_id = mcr.inventory_item_id
7897: NVL(TRACKING_QUANTITY_IND,'P'),
7898: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7899: NVL(DUAL_UOM_DEVIATION_LOW,0)
7900: FROM
7901: mtl_system_items_vl msik /* Bug 5581528 */
7902: WHERE
7903: organization_id = p_Organization_Id
7904: AND concatenated_segments like p_concatenated_segments
7905: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
7951: NVL(TRACKING_QUANTITY_IND,'P'),
7952: NVL(DUAL_UOM_DEVIATION_HIGH,0),
7953: NVL(DUAL_UOM_DEVIATION_LOW,0)
7954: FROM
7955: mtl_system_items_vl msik, /* Bug 5581528 */
7956: mtl_cross_references mcr
7957: WHERE
7958: msik.organization_id = p_organization_id
7959: AND msik.inventory_item_id = mcr.inventory_item_id
8013: NVL(TRACKING_QUANTITY_IND,'P'),
8014: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8015: NVL(DUAL_UOM_DEVIATION_LOW,0)
8016: FROM
8017: mtl_system_items_vl msik /* Bug 5581528 */
8018: WHERE
8019: organization_id = p_Organization_Id
8020: AND concatenated_segments like p_concatenated_segments
8021: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
8067: NVL(TRACKING_QUANTITY_IND,'P'),
8068: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8069: NVL(DUAL_UOM_DEVIATION_LOW,0)
8070: FROM
8071: mtl_system_items_vl msik, /* Bug 5581528 */
8072: mtl_cross_references mcr
8073: WHERE
8074: msik.organization_id = p_organization_id
8075: AND msik.inventory_item_id = mcr.inventory_item_id
8128: NVL(TRACKING_QUANTITY_IND,'P'),
8129: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8130: NVL(DUAL_UOM_DEVIATION_LOW,0)
8131: FROM
8132: mtl_system_items_vl msik /* Bug 5581528 */
8133: WHERE
8134: organization_id = p_Organization_Id
8135: AND concatenated_segments like p_concatenated_segments
8136: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
8182: NVL(TRACKING_QUANTITY_IND,'P'),
8183: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8184: NVL(DUAL_UOM_DEVIATION_LOW,0)
8185: FROM
8186: mtl_system_items_vl msik, /* Bug 5581528 */
8187: mtl_cross_references mcr
8188: WHERE
8189: msik.organization_id = p_organization_id
8190: AND msik.inventory_item_id = mcr.inventory_item_id
8243: NVL(TRACKING_QUANTITY_IND,'P'),
8244: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8245: NVL(DUAL_UOM_DEVIATION_LOW,0)
8246: FROM
8247: mtl_system_items_vl msik /* Bug 5581528 */
8248: WHERE
8249: organization_id = p_Organization_Id
8250: AND concatenated_segments like p_concatenated_segments
8251: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
8297: NVL(TRACKING_QUANTITY_IND,'P'),
8298: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8299: NVL(DUAL_UOM_DEVIATION_LOW,0)
8300: FROM
8301: mtl_system_items_vl msik, /* Bug 5581528 */
8302: mtl_cross_references mcr
8303: WHERE
8304: msik.organization_id = p_organization_id
8305: AND msik.inventory_item_id = mcr.inventory_item_id