DBA Data[Home] [Help]

APPS.INV_UI_RCV_LOVS dependencies on MTL_SYSTEM_ITEMS_VL

Line 443: , mtl_system_items_vl msi

439: , pl.item_revision
440: , msi.concatenated_segments
441: , msi.outside_operation_flag
442: from po_lines_trx_v pl -- CLM project, bug 9403291
443: , mtl_system_items_vl msi
444: where pl.item_id = msi.inventory_item_id (+)
445: and Nvl(msi.organization_id, p_organization_id) = p_organization_id
446: and pl.po_header_id = p_po_header_id
447: and exists (SELECT 'Valid PO Shipments'

Line 491: , mtl_system_items_vl msi

487: FROM rcv_supply rsup
488: -- bug 2805640
489: , mtl_units_of_measure mum
490: , po_lines_trx_v pl -- CLM project, bug 9403291
491: , mtl_system_items_vl msi
492: WHERE rsup.po_line_id = pl.po_line_id
493: -- bug 2805640
494: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
495: AND pl.item_id = msi.inventory_item_id (+)

Line 2954: 'MTL_SYSTEM_ITEMS_VL' and item desc is selected from this table.*/

2950: END IF; -- bug 9776756
2951: -- CLM project
2952: IF p_mobile_form = 'RECEIPT' THEN
2953: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
2954: 'MTL_SYSTEM_ITEMS_VL' and item desc is selected from this table.*/
2955:
2956: /*Bug # 8687063 : Because of the fix done for bug 6437363, the query was returning
2957: zero row for expense item (We do not enter item while creating PO in this case).
2958: For this case,we have value if item_id in po_lines_all table as null,

Line 2978: , mtl_system_items_vl msi

2974: pl.Item_Id)
2975: uom_code
2976: from po_lines_trx_v pl -- CLM project, bug 9403291
2977: , mtl_units_of_measure mum
2978: , mtl_system_items_vl msi
2979: where pl.item_id = msi.inventory_item_id (+)
2980: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
2981: -- and msi.organization_id = p_organization_id -- Bug 6437363
2982: and (pl.item_id is null or msi.organization_id = p_organization_id) --Bug 8687063

Line 3024: , mtl_system_items_vl msi

3020: pl.Item_Id)
3021: uom_code
3022: from po_lines_trx_v pl -- CLM project, bug 9403291
3023: , mtl_units_of_measure mum
3024: , mtl_system_items_vl msi
3025: , mtl_related_items mri
3026: where msi.organization_id = p_organization_id -- Bug 6437363
3027: and msi.inventory_item_id = p_inventory_item_id -- Bug 6311550
3028: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE

Line 3082: , mtl_system_items_vl msi

3078: uom_code
3079: FROM rcv_supply rsup
3080: , mtl_units_of_measure mum
3081: , po_lines_trx_v pl -- CLM project, bug 9403291
3082: , mtl_system_items_vl msi
3083: WHERE rsup.po_line_id = pl.po_line_id
3084: and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
3085: AND pl.item_id = msi.inventory_item_id (+)
3086: and Nvl(msi.organization_id, p_organization_id) = p_organization_id

Line 3379: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3375: --- releaseBased PJM Transaction
3376: -- *****************************
3377: open x_items for
3378: -- Bug# 6747729
3379: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3380: select concatenated_segments,
3381: inventory_item_id,
3382: description,
3383: Nvl(revision_qty_control_code,1),

Line 3429: from mtl_system_items_vl, /* Bug 5581528 */

3425: NVL(DUAL_UOM_DEVIATION_LOW,0),
3426: stock_enabled_flag,
3427: ' ',
3428: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
3429: from mtl_system_items_vl, /* Bug 5581528 */
3430: po_lines_all pol -- Bug 14645109 s added
3431: WHERE organization_id = p_Organization_Id
3432: and concatenated_segments like p_concatenated_segments||l_append
3433: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 3470: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3466: -- Bug 14645109 s remove this )
3467: UNION ALL
3468: -- Substitute Item SQL
3469: -- Bug# 6747729
3470: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3471: select distinct msi.concatenated_segments,
3472: msi.inventory_item_id,
3473: Nvl(pol.item_description, msi.description), -- Bug 14645109
3474: Nvl(msi.revision_qty_control_code,1),

Line 3522: ,mtl_system_items_vl msi /* Bug 5581528 */

3518: ' ',
3519: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
3520: from po_lines_trx_v pol -- CLM project, bug 9403291
3521: ,mtl_related_items mri
3522: ,mtl_system_items_vl msi /* Bug 5581528 */
3523: /*,mtl_system_items_kfv msia */ /* Bug 6334679*/
3524: where msi.organization_id = p_organization_id
3525: and msi.concatenated_segments like p_concatenated_segments||l_append
3526: and pol.po_header_id = p_poHeaderID

Line 3567: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3563: )
3564: UNION ALL
3565: -- Vendor Item SQL
3566: -- Bug# 6747729
3567: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3568: select distinct pol.vendor_product_num,
3569: msi.inventory_item_id,
3570: Nvl(pol.item_description, msi.description) , -- Bug 14645109
3571: Nvl(msi.revision_qty_control_code,1),

Line 3618: , mtl_system_items_vl msi /* Bug 5581528 */

3614: msi.stock_enabled_flag,
3615: ' ',
3616: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
3617: from po_lines_trx_v pol -- CLM project, bug 9403291
3618: , mtl_system_items_vl msi /* Bug 5581528 */
3619: where organization_id = p_organization_id
3620: and pol.vendor_product_num like p_concatenated_segments
3621: and pol.item_id = msi.inventory_item_id
3622: and pol.vendor_product_num IS NOT NULL

Line 3660: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3656: )
3657: UNION ALL
3658: -- non item Master
3659: -- Bug# 6747729
3660: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3661: select distinct pol.item_description,
3662: to_number(''),
3663: pol.item_description,
3664: 1,

Line 3755: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3751: --End of fix for Bug 3972931
3752: UNION ALL
3753: -- Cross Ref SQL
3754: -- Bug# 6747729
3755: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3756: select distinct mcr.cross_reference,
3757: msi.inventory_item_id,
3758: msi.description,
3759: Nvl(msi.revision_qty_control_code,1),

Line 3806: ,mtl_system_items_vl msi /* Bug 5581528 */

3802: msi.stock_enabled_flag,
3803: ' ',
3804: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
3805: from po_lines_trx_v pol -- CLM project, bug 9403291
3806: ,mtl_system_items_vl msi /* Bug 5581528 */
3807: ,mtl_cross_references mcr
3808: where msi.organization_id = p_organization_id
3809: and ( (mcr.cross_reference_type = p_crossreftype
3810: and mcr.cross_reference like p_concatenated_segments

Line 3855: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

3851: )
3852: )
3853: )
3854: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
3855: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
3856: Now, we do not join the item table when the item id is null in the po lines table.*/
3857:
3858: UNION ALL
3859: select distinct pol.vendor_product_num,

Line 3956: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

3952: ----- lineBased PJM Transaction
3953: -- *****************************
3954: open x_items for
3955: -- Bug# 6747729
3956: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
3957: select concatenated_segments,
3958: inventory_item_id,
3959: description,
3960: Nvl(revision_qty_control_code,1),

Line 4006: from mtl_system_items_vl, /* Bug 5581528 */

4002: NVL(DUAL_UOM_DEVIATION_LOW,0),
4003: stock_enabled_flag,
4004: ' ',
4005: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4006: from mtl_system_items_vl, /* Bug 5581528 */
4007: po_lines_all pol -- Bug 14645109 s added
4008: WHERE organization_id = p_Organization_Id
4009: and concatenated_segments like p_concatenated_segments||l_append
4010: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 4049: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4045: -- Bug 14645109 s remove this )
4046: UNION ALL
4047: -- Substitute Item SQL
4048: -- Bug# 6747729
4049: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4050: select distinct msi.concatenated_segments,
4051: msi.inventory_item_id,
4052: msi.description,
4053: Nvl(msi.revision_qty_control_code,1),

Line 4101: ,mtl_system_items_vl msi /* Bug 5581528 */

4097: ' ',
4098: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4099: from po_lines_trx_v pol -- CLM project, bug 9403291
4100: ,mtl_related_items mri
4101: ,mtl_system_items_vl msi /* Bug 5581528 */
4102: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
4103: where msi.organization_id = p_organization_id
4104: and msi.concatenated_segments like p_concatenated_segments||l_append
4105: and pol.po_header_id = p_poHeaderID

Line 4146: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4142: )
4143: UNION ALL
4144: -- Vendor Item SQL
4145: -- Bug# 6747729
4146: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4147: select distinct pol.vendor_product_num,
4148: msi.inventory_item_id,
4149: Nvl(pol.item_description, msi.description) , -- Bug 14645109 change this
4150: Nvl(msi.revision_qty_control_code,1),

Line 4197: , mtl_system_items_vl msi /* Bug 5581528 */

4193: msi.stock_enabled_flag,
4194: ' ',
4195: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4196: from po_lines_trx_v pol -- CLM project, bug 9403291
4197: , mtl_system_items_vl msi /* Bug 5581528 */
4198: where organization_id = p_organization_id
4199: and pol.vendor_product_num like p_concatenated_segments
4200: and pol.item_id = msi.inventory_item_id
4201: and pol.vendor_product_num IS NOT NULL

Line 4239: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4235: )
4236: UNION ALL
4237: -- non item Master
4238: -- Bug# 6747729
4239: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4240: select distinct pol.item_description,
4241: to_number(''),
4242: pol.item_description,
4243: 1,

Line 4333: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4329: --End of fix for Bug 3972931
4330: UNION ALL
4331: -- Cross Ref SQL
4332: -- Bug# 6747729
4333: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4334: select distinct mcr.cross_reference,
4335: msi.inventory_item_id,
4336: msi.description,
4337: Nvl(msi.revision_qty_control_code,1),

Line 4384: ,mtl_system_items_vl msi /* Bug 5581528 */

4380: msi.stock_enabled_flag,
4381: ' ',
4382: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4383: from po_lines_trx_v pol -- CLM project, bug 9403291
4384: ,mtl_system_items_vl msi /* Bug 5581528 */
4385: ,mtl_cross_references mcr
4386: where msi.organization_id = p_organization_id
4387: and ( (mcr.cross_reference_type = p_crossreftype
4388: and mcr.cross_reference like p_concatenated_segments

Line 4433: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

4429: )
4430: )
4431: )
4432: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
4433: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
4434: Now, we do not join the item table when the item id is null in the po lines table.*/
4435:
4436: UNION ALL
4437: select distinct pol.vendor_product_num,

Line 4548: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4544: --
4545: -- *****************************
4546: open x_items for
4547: -- Bug# 6747729
4548: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4549: select concatenated_segments,
4550: inventory_item_id,
4551: nvl(pol.item_description, description), -- Bug 14645109 change this
4552: Nvl(revision_qty_control_code,1),

Line 4559: Nvl(mtl_system_items_vl.inspection_required_flag, 'N'), -- bug 4610452

4555: Nvl(restrict_subinventories_code, 2),
4556: Nvl(restrict_locators_code, 2),
4557: Nvl(location_control_code, 1),
4558: primary_uom_code,
4559: Nvl(mtl_system_items_vl.inspection_required_flag, 'N'), -- bug 4610452
4560: Nvl(shelf_life_code, 1),
4561: Nvl(shelf_life_days,0),
4562: Nvl(allowed_units_lookup_code, 2),
4563: Nvl(effectivity_control,1),

Line 4598: from mtl_system_items_vl , /* Bug 5581528 */

4594: NVL(DUAL_UOM_DEVIATION_LOW,0),
4595: stock_enabled_flag,
4596: ' ',
4597: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4598: from mtl_system_items_vl , /* Bug 5581528 */
4599: po_lines_trx_v pol --CLM project, bug 9403291 -- bug 4602289
4600: WHERE organization_id = p_Organization_Id
4601: and concatenated_segments like p_concatenated_segments||l_append
4602: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 4637: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4633:
4634: UNION ALL
4635: -- Substitute Item SQL
4636: -- Bug# 6747729
4637: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4638: select distinct msi.concatenated_segments,
4639: msi.inventory_item_id,
4640: Nvl(pol.item_description, msi.description), -- Bug 14645109 change this
4641: Nvl(msi.revision_qty_control_code,1),

Line 4689: ,mtl_system_items_vl msi /* Bug 5581528 */

4685: ' ',
4686: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4687: from po_lines_trx_v pol -- CLM project, bug 9403291
4688: ,mtl_related_items mri
4689: ,mtl_system_items_vl msi /* Bug 5581528 */
4690: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
4691: where msi.organization_id = p_organization_id
4692: and msi.concatenated_segments like p_concatenated_segments||l_append
4693: and pol.po_header_id = p_poHeaderID

Line 4732: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4728: )
4729: UNION ALL
4730: -- Vendor Item SQL
4731: -- Bug# 6747729
4732: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4733: select /*+ INDEX(PO_LINES_ALL PO_LINES_U2) */ --bug 4602289
4734: distinct pol.vendor_product_num,
4735: msi.inventory_item_id,
4736: Nvl(pol.item_description, msi.description), -- Bug 14645109 s change this

Line 4784: , mtl_system_items_vl msi /* Bug 5581528 */

4780: msi.stock_enabled_flag,
4781: ' ',
4782: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4783: from po_lines_trx_v pol -- CLM project, bug 9403291
4784: , mtl_system_items_vl msi /* Bug 5581528 */
4785: where organization_id = p_organization_id
4786: and pol.vendor_product_num like p_concatenated_segments
4787: and pol.item_id = msi.inventory_item_id
4788: and pol.vendor_product_num IS NOT NULL

Line 4825: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4821: )
4822: UNION ALL
4823: -- non item Master
4824: -- Bug# 6747729
4825: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4826: select distinct pol.item_description,
4827: to_number(''),
4828: pol.item_description,
4829: 1,

Line 4919: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

4915: --End of fix for Bug 3972931
4916: UNION ALL
4917: -- Cross Ref SQL
4918: -- Bug# 6747729
4919: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
4920: select distinct mcr.cross_reference,
4921: msi.inventory_item_id,
4922: msi.description,
4923: Nvl(msi.revision_qty_control_code,1),

Line 4970: ,mtl_system_items_vl msi /* Bug 5581528 */

4966: msi.stock_enabled_flag,
4967: ' ',
4968: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
4969: from po_lines_trx_v pol -- CLM project, bug 9403291
4970: ,mtl_system_items_vl msi /* Bug 5581528 */
4971: ,mtl_cross_references mcr
4972: where msi.organization_id = p_organization_id
4973: and ( (mcr.cross_reference_type = p_crossreftype
4974: and mcr.cross_reference like p_concatenated_segments

Line 5018: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

5014: )
5015: )
5016: )
5017: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
5018: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
5019: Now, we do not join the item table when the item id is null in the po lines table.*/
5020:
5021: UNION ALL
5022: select distinct pol.vendor_product_num,

Line 5168: from mtl_system_items_vl, /* Bug 5581528 */

5164: NVL(DUAL_UOM_DEVIATION_LOW,0),
5165: stock_enabled_flag,
5166: ' ',
5167: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5168: from mtl_system_items_vl, /* Bug 5581528 */
5169: po_lines_all pol -- Bug 14645109 s
5170: WHERE organization_id = p_Organization_Id
5171: and concatenated_segments like p_concatenated_segments||l_append
5172: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 5252: ,mtl_system_items_vl msi /* Bug 5581528 */

5248: ' ',
5249: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5250: from po_lines_trx_v pol -- CLM project, bug 9403291
5251: ,mtl_related_items mri
5252: ,mtl_system_items_vl msi /* Bug 5581528 */
5253: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
5254: where msi.organization_id = p_organization_id
5255: and msi.concatenated_segments like p_concatenated_segments||l_append
5256: and pol.po_header_id = p_poHeaderID

Line 5340: , mtl_system_items_vl msi /* Bug 5581528 */

5336: msi.stock_enabled_flag,
5337: ' ',
5338: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5339: from po_lines_trx_v pol -- CLM project, bug 9403291
5340: , mtl_system_items_vl msi /* Bug 5581528 */
5341: where organization_id = p_organization_id
5342: and pol.vendor_product_num like p_concatenated_segments
5343: and pol.item_id = msi.inventory_item_id
5344: and pol.vendor_product_num IS NOT NULL

Line 5513: ,mtl_system_items_vl msi /* Bug 5581528 */

5509: msi.stock_enabled_flag,
5510: ' ',
5511: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5512: from po_lines_trx_v pol -- CLM project, bug 9403291
5513: ,mtl_system_items_vl msi /* Bug 5581528 */
5514: ,mtl_cross_references mcr
5515: where msi.organization_id = p_organization_id
5516: and ( (mcr.cross_reference_type = p_crossreftype
5517: and mcr.cross_reference like p_concatenated_segments

Line 5554: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

5550: -- End for Bug 7440217
5551: )--Bug 3972931-Added the filter condition based on ship_to_organization_id
5552: )
5553: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
5554: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
5555: Now, we do not join the item table when the item id is null in the po lines table.*/
5556:
5557: UNION ALL
5558: select distinct pol.vendor_product_num,

Line 5697: from mtl_system_items_vl , /* Bug 5581528 */

5693: NVL(DUAL_UOM_DEVIATION_LOW,0),
5694: stock_enabled_flag,
5695: ' ',
5696: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5697: from mtl_system_items_vl , /* Bug 5581528 */
5698: po_lines_all pl -- Bug 14645109 s
5699: WHERE organization_id = p_Organization_Id
5700: and concatenated_segments like p_concatenated_segments||l_append
5701: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 5782: ,mtl_system_items_vl msi /* Bug 5581528 */

5778: ' ',
5779: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5780: from po_lines_trx_v pol -- CLM project, bug 9403291
5781: ,mtl_related_items mri
5782: ,mtl_system_items_vl msi /* Bug 5581528 */
5783: /*,mtl_system_items_kfv msia*/ /* Bug 6334679 */
5784: where msi.organization_id = p_organization_id
5785: and msi.concatenated_segments like p_concatenated_segments||l_append
5786: and pol.po_header_id = p_poHeaderID

Line 5869: ,mtl_system_items_vl msi /* Bug 5581528 */

5865: msi.stock_enabled_flag,
5866: ' ',
5867: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
5868: from po_lines_trx_v pol -- CLM project, bug 9403291
5869: ,mtl_system_items_vl msi /* Bug 5581528 */
5870: where organization_id = p_organization_id
5871: and pol.vendor_product_num like p_concatenated_segments
5872: and pol.item_id = msi.inventory_item_id
5873: and pol.vendor_product_num IS NOT NULL

Line 6039: ,mtl_system_items_vl msi /* Bug 5581528 */

6035: msi.stock_enabled_flag,
6036: ' ',
6037: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6038: from po_lines_trx_v pol -- CLM project, bug 9403291
6039: ,mtl_system_items_vl msi /* Bug 5581528 */
6040: ,mtl_cross_references mcr
6041: where msi.organization_id = p_organization_id
6042: and ( (mcr.cross_reference_type = p_crossreftype
6043: and mcr.cross_reference like p_concatenated_segments

Line 6079: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

6075: and pll.po_line_id = p_poLineID
6076: and pll.ship_to_organization_id = p_Organization_Id)--Bug 3972931-Added the filter condition based on ship_to_organization_id
6077: )
6078: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
6079: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
6080: Now, we do not join the item table when the item id is null in the po lines table.*/
6081:
6082: UNION ALL
6083: select distinct pol.vendor_product_num,

Line 6223: from mtl_system_items_vl /* Bug 5581528 */

6219: NVL(DUAL_UOM_DEVIATION_LOW,0),
6220: stock_enabled_flag,
6221: ' ',
6222: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6223: from mtl_system_items_vl /* Bug 5581528 */
6224: WHERE organization_id = p_Organization_Id
6225: and concatenated_segments like p_concatenated_segments||l_append
6226: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
6227: and inventory_item_id IN

Line 6305: ,mtl_system_items_vl msi /* Bug 5581528 */

6301: ' ',
6302: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6303: from po_lines_trx_v pol -- CLM project, bug 9403291
6304: ,mtl_related_items mri
6305: ,mtl_system_items_vl msi /* Bug 5581528 */
6306: /*,mtl_system_items_kfv msia */ /* Bug 6334679 */
6307: where msi.organization_id = p_organization_id
6308: and msi.concatenated_segments like p_concatenated_segments||l_append
6309: and pol.po_header_id = p_poHeaderID

Line 6391: , mtl_system_items_vl msi /* Bug 5581528 */

6387: msi.stock_enabled_flag,
6388: ' ',
6389: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6390: from po_lines_trx_v pol -- CLM project, bug 9403291
6391: , mtl_system_items_vl msi /* Bug 5581528 */
6392: where organization_id = p_organization_id
6393: and pol.vendor_product_num like p_concatenated_segments
6394: and pol.item_id = msi.inventory_item_id
6395: and pol.vendor_product_num IS NOT NULL

Line 6560: ,mtl_system_items_vl msi /* Bug 5581528 */

6556: msi.stock_enabled_flag,
6557: ' ',
6558: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6559: from po_lines_trx_v pol -- CLM project, bug 9403291
6560: ,mtl_system_items_vl msi /* Bug 5581528 */
6561: ,mtl_cross_references mcr
6562: where msi.organization_id = p_organization_id
6563: and ( (mcr.cross_reference_type = p_crossreftype
6564: and mcr.cross_reference like p_concatenated_segments

Line 6600: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.

6596: and pll.po_line_id = pl.po_line_id
6597: and pll.ship_to_organization_id = p_Organization_Id)--Bug 3972931-Added the filter condition based on ship_to_organization_id
6598: )
6599: /* Bug 12348588 When use mobile to receive PO, item lov not display supplier item for one time item.
6600: That is because there is no record in item table: mtl_system_items_vl when the item is one time item.
6601: Now, we do not join the item table when the item id is null in the po lines table.*/
6602:
6603: UNION ALL
6604: select distinct pol.vendor_product_num,

Line 6758: mtl_system_items_vl msn, /* Bug 5581528 */

6754: stock_enabled_flag,
6755: DECODE (p_reqHeaderID,to_char(null),' ', rsh.shipment_num),
6756: DECODE (p_reqHeaderID,to_char(null),' ',p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6757: from wms_lpn_contents wlc ,
6758: mtl_system_items_vl msn, /* Bug 5581528 */
6759: rcv_shipment_lines rsl,
6760: rcv_shipment_headers rsh /* Added for Bug9257750 */
6761: WHERE msn.organization_id = p_Organization_Id
6762: and wlc.parent_lpn_id = l_from_lpn_id

Line 6833: ,mtl_system_items_vl msi /* Bug 5581528 */

6829: DECODE (p_reqHeaderID,to_char(null),' ',
6830: p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
6831: from wms_lpn_contents wlc
6832: ,po_lines_trx_v pol -- CLM project, bug 9403291
6833: ,mtl_system_items_vl msi /* Bug 5581528 */
6834: ,rcv_shipment_lines rsl
6835: ,rcv_shipment_headers rsh /* Added for Bug9257750 */
6836: where msi.organization_id = p_Organization_Id
6837: and wlc.parent_lpn_id = l_from_lpn_id

Line 7026: mtl_system_items_vl msn, /* Bug 5581528 */

7022: rsh.shipment_num),
7023: DECODE (p_reqHeaderID,to_char(null),' ',
7024: p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7025: from wms_lpn_contents wlc,
7026: mtl_system_items_vl msn, /* Bug 5581528 */
7027: rcv_shipment_lines rsl,
7028: rcv_shipment_headers rsh, /* Added for Bug9257750 */
7029: mtl_cross_references mcr
7030: WHERE msn.organization_id = p_Organization_Id

Line 7102: from mtl_system_items_vl msn, /* Bug 5581528 */

7098: DECODE (p_reqHeaderID,to_char(null),' ',
7099: rsh.shipment_num),
7100: DECODE (p_reqHeaderID,to_char(null),' ',
7101: p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7102: from mtl_system_items_vl msn, /* Bug 5581528 */
7103: rcv_shipment_lines rsl,
7104: rcv_shipment_headers rsh /* Added for Bug9257750 */
7105: WHERE msn.organization_id = p_Organization_Id
7106: and msn.concatenated_segments like p_concatenated_segments||l_append

Line 7173: ,mtl_system_items_vl msi /* Bug 5581528 */

7169: rsh.shipment_num),
7170: DECODE (p_reqHeaderID,to_char(null),' ',
7171: p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7172: from po_lines_trx_v pol -- CLM project, bug 9403291
7173: ,mtl_system_items_vl msi /* Bug 5581528 */
7174: ,rcv_shipment_lines rsl
7175: ,rcv_shipment_headers rsh /* Added for Bug9257750 */
7176: where msi.organization_id = p_Organization_Id
7177: and pol.vendor_product_num like p_concatenated_segments

Line 7362: from mtl_system_items_vl msn, /* Bug 5581528 */

7358: DECODE (p_reqHeaderID,to_char(null),' ',
7359: rsh.shipment_num),
7360: DECODE (p_reqHeaderID,to_char(null),' ',
7361: p_shipmentHeaderID) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7362: from mtl_system_items_vl msn, /* Bug 5581528 */
7363: rcv_shipment_lines rsl,
7364: rcv_shipment_headers rsh, /* Added for Bug9257750 */
7365: mtl_cross_references mcr
7366: WHERE msn.organization_id = p_Organization_Id

Line 7446: from mtl_system_items_vl /* Bug 5581528 */

7442: NVL(DUAL_UOM_DEVIATION_LOW,0),
7443: stock_enabled_flag,
7444: ' ',
7445: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7446: from mtl_system_items_vl /* Bug 5581528 */
7447: WHERE organization_id = p_Organization_Id
7448: and concatenated_segments like p_concatenated_segments||l_append
7449: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
7450: and inventory_item_id IN (SELECT oel.inventory_item_id FROM

Line 7514: from mtl_system_items_vl msi /* Bug 5581528 */

7510: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0),
7511: msi.stock_enabled_flag,
7512: ' ',
7513: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7514: from mtl_system_items_vl msi /* Bug 5581528 */
7515: ,mtl_cross_references mcr
7516: WHERE msi.organization_id = p_Organization_Id
7517: and ( mcr.cross_reference_type = g_gtin_cross_ref_type
7518: AND mcr.cross_reference LIKE g_crossref

Line 7550: SELECT /*+ leading(rsl1) use_nl(rsl1 mtl_system_items_vl)

7546: -- Bug# 6747729
7547: -- Added code to also fetch stock_enabled_flag from mtl_system_items_v
7548: --Start of Fix for Bug# 7216416
7549:
7550: SELECT /*+ leading(rsl1) use_nl(rsl1 mtl_system_items_vl)
7551: index(mtl_system_items_vl MTL_SYSTEM_ITEMS_B_U1) */
7552:
7553: concatenated_segments ,
7554: inventory_item_id ,

Line 7551: index(mtl_system_items_vl MTL_SYSTEM_ITEMS_B_U1) */

7547: -- Added code to also fetch stock_enabled_flag from mtl_system_items_v
7548: --Start of Fix for Bug# 7216416
7549:
7550: SELECT /*+ leading(rsl1) use_nl(rsl1 mtl_system_items_vl)
7551: index(mtl_system_items_vl MTL_SYSTEM_ITEMS_B_U1) */
7552:
7553: concatenated_segments ,
7554: inventory_item_id ,
7555: description ,

Line 7602: FROM mtl_system_items_vl,

7598: NVL(DUAL_UOM_DEVIATION_LOW,0) ,
7599: stock_enabled_flag,
7600: rsl1.shipment_num,
7601: to_char(rsl1.shipment_header_id) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7602: FROM mtl_system_items_vl,
7603: /* Bug 5581528 */
7604: (
7605: SELECT rsl.Item_Id,rsh.shipment_num shipment_num, /* Added by Bug9257750 */
7606: rsh.shipment_header_id shipment_header_id

Line 7689: FROM mtl_system_items_vl,

7685: NVL(DUAL_UOM_DEVIATION_LOW,0) ,
7686: stock_enabled_flag,
7687: rsl1.shipment_num,
7688: to_char(rsl1.shipment_header_id) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7689: FROM mtl_system_items_vl,
7690: /* Bug 5581528 */
7691: (
7692: SELECT rsl.Item_Id,rsh.shipment_num shipment_num, /* Added by Bug9257750 */
7693: rsh.shipment_header_id shipment_header_id

Line 7770: FROM mtl_system_items_vl msi

7766: NVL(MSI.DUAL_UOM_DEVIATION_LOW,0) ,
7767: msi.stock_enabled_flag,
7768: rsl1.shipment_num,
7769: to_char(rsl1.shipment_header_id) -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7770: FROM mtl_system_items_vl msi
7771: /* Bug 5581528 */
7772: ,
7773: mtl_cross_references mcr,
7774: -- This Select clause added by Bug9257750 to fetch the values of Shipment Number and Shipment Header id

Line 7896: mtl_system_items_vl msi /* Bug 5581528 */

7892: msi.stock_enabled_flag,
7893: ' ',
7894: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7895: FROM
7896: mtl_system_items_vl msi /* Bug 5581528 */
7897: WHERE msi.organization_id = p_organization_Id
7898: AND msi.concatenated_segments LIKE p_concatenated_segments||l_append
7899: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
7900: UNION -- ALL

Line 7957: mtl_system_items_vl msi /* Bug 5581528 */

7953: msi.stock_enabled_flag,
7954: ' ',
7955: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
7956: FROM
7957: mtl_system_items_vl msi /* Bug 5581528 */
7958: WHERE msi.organization_id = p_organization_Id
7959: AND msi.concatenated_segments LIKE p_concatenated_segments||l_append
7960: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
7961: AND EXISTS (SELECT '1'

Line 8045: mtl_system_items_vl msi /* Bug 5581528 */

8041: ' ',
8042: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
8043: FROM
8044: po_lines_trx_v pol, -- CLM project, bug 9403291
8045: mtl_system_items_vl msi /* Bug 5581528 */
8046: WHERE msi.organization_id = p_organization_Id
8047: AND (msi.purchasing_enabled_flag = 'Y' OR msi.stock_enabled_flag = 'Y')
8048: AND pol.vendor_product_num like p_concatenated_segments
8049: AND pol.item_id = msi.inventory_item_id

Line 8124: mtl_system_items_vl msi, /* Bug 5581528 */

8120: msi.stock_enabled_flag,
8121: ' ',
8122: ' ' -- Added by Bug9257750 for values corr to Shipment Number and Shipment Header id
8123: FROM
8124: mtl_system_items_vl msi, /* Bug 5581528 */
8125: mtl_cross_references mcr
8126: WHERE msi.organization_id = p_organization_Id
8127: AND ( (mcr.cross_reference_type = p_crossreftype
8128: AND mcr.cross_reference LIKE p_concatenated_segments

Line 8286: from mtl_system_items_vl

8282: NVL(SECONDARY_DEFAULT_IND,''),
8283: NVL(TRACKING_QUANTITY_IND,'P'),
8284: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8285: NVL(DUAL_UOM_DEVIATION_LOW,0)
8286: from mtl_system_items_vl
8287: WHERE organization_id = p_Organization_Id
8288: and concatenated_segments like p_concatenated_segments||l_append
8289: and mtl_transactions_enabled_flag = 'Y'
8290: and bom_item_type=4

Line 8340: from mtl_system_items_vl msik, /* Bug 5581528 */

8336: NVL(SECONDARY_DEFAULT_IND,''),
8337: NVL(TRACKING_QUANTITY_IND,'P'),
8338: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8339: NVL(DUAL_UOM_DEVIATION_LOW,0)
8340: from mtl_system_items_vl msik, /* Bug 5581528 */
8341: mtl_cross_references mcr
8342: WHERE msik.organization_id = p_Organization_Id
8343: AND msik.inventory_item_id = mcr.inventory_item_id
8344: AND mcr.cross_reference_type = g_gtin_cross_ref_type

Line 8608: mtl_system_items_vl msik /* Bug 5581528 */

8604: NVL(TRACKING_QUANTITY_IND,'P'),
8605: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8606: NVL(DUAL_UOM_DEVIATION_LOW,0)
8607: FROM
8608: mtl_system_items_vl msik /* Bug 5581528 */
8609: WHERE
8610: organization_id = p_Organization_Id
8611: AND concatenated_segments like p_concatenated_segments||l_append
8612: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 8668: mtl_system_items_vl msik, /* Bug 5581528 */

8664: NVL(TRACKING_QUANTITY_IND,'P'),
8665: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8666: NVL(DUAL_UOM_DEVIATION_LOW,0)
8667: FROM
8668: mtl_system_items_vl msik, /* Bug 5581528 */
8669: mtl_cross_references mcr
8670: WHERE
8671: msik.organization_id = p_organization_id
8672: AND msik.inventory_item_id = mcr.inventory_item_id

Line 8736: mtl_system_items_vl msik /* Bug 5581528 */

8732: NVL(TRACKING_QUANTITY_IND,'P'),
8733: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8734: NVL(DUAL_UOM_DEVIATION_LOW,0)
8735: FROM
8736: mtl_system_items_vl msik /* Bug 5581528 */
8737: WHERE
8738: organization_id = p_Organization_Id
8739: AND concatenated_segments like p_concatenated_segments||l_append
8740: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 8791: mtl_system_items_vl msik, /* Bug 5581528 */

8787: NVL(TRACKING_QUANTITY_IND,'P'),
8788: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8789: NVL(DUAL_UOM_DEVIATION_LOW,0)
8790: FROM
8791: mtl_system_items_vl msik, /* Bug 5581528 */
8792: mtl_cross_references mcr
8793: WHERE
8794: msik.organization_id = p_organization_id
8795: AND msik.inventory_item_id = mcr.inventory_item_id

Line 8853: mtl_system_items_vl msik /* Bug 5581528 */

8849: NVL(TRACKING_QUANTITY_IND,'P'),
8850: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8851: NVL(DUAL_UOM_DEVIATION_LOW,0)
8852: FROM
8853: mtl_system_items_vl msik /* Bug 5581528 */
8854: WHERE
8855: organization_id = p_Organization_Id
8856: AND concatenated_segments like p_concatenated_segments||l_append
8857: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 8908: mtl_system_items_vl msik, /* Bug 5581528 */

8904: NVL(TRACKING_QUANTITY_IND,'P'),
8905: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8906: NVL(DUAL_UOM_DEVIATION_LOW,0)
8907: FROM
8908: mtl_system_items_vl msik, /* Bug 5581528 */
8909: mtl_cross_references mcr
8910: WHERE
8911: msik.organization_id = p_organization_id
8912: AND msik.inventory_item_id = mcr.inventory_item_id

Line 8970: mtl_system_items_vl msik /* Bug 5581528 */

8966: NVL(TRACKING_QUANTITY_IND,'P'),
8967: NVL(DUAL_UOM_DEVIATION_HIGH,0),
8968: NVL(DUAL_UOM_DEVIATION_LOW,0)
8969: FROM
8970: mtl_system_items_vl msik /* Bug 5581528 */
8971: WHERE
8972: organization_id = p_Organization_Id
8973: AND concatenated_segments like p_concatenated_segments||l_append
8974: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 9025: mtl_system_items_vl msik, /* Bug 5581528 */

9021: NVL(TRACKING_QUANTITY_IND,'P'),
9022: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9023: NVL(DUAL_UOM_DEVIATION_LOW,0)
9024: FROM
9025: mtl_system_items_vl msik, /* Bug 5581528 */
9026: mtl_cross_references mcr
9027: WHERE
9028: msik.organization_id = p_organization_id
9029: AND msik.inventory_item_id = mcr.inventory_item_id

Line 9088: mtl_system_items_vl msik /* Bug 5581528 */

9084: NVL(TRACKING_QUANTITY_IND,'P'),
9085: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9086: NVL(DUAL_UOM_DEVIATION_LOW,0)
9087: FROM
9088: mtl_system_items_vl msik /* Bug 5581528 */
9089: WHERE
9090: organization_id = p_Organization_Id
9091: AND concatenated_segments like p_concatenated_segments||l_append
9092: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 9143: mtl_system_items_vl msik, /* Bug 5581528 */

9139: NVL(TRACKING_QUANTITY_IND,'P'),
9140: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9141: NVL(DUAL_UOM_DEVIATION_LOW,0)
9142: FROM
9143: mtl_system_items_vl msik, /* Bug 5581528 */
9144: mtl_cross_references mcr
9145: WHERE
9146: msik.organization_id = p_organization_id
9147: AND msik.inventory_item_id = mcr.inventory_item_id

Line 9205: mtl_system_items_vl msik /* Bug 5581528 */

9201: NVL(TRACKING_QUANTITY_IND,'P'),
9202: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9203: NVL(DUAL_UOM_DEVIATION_LOW,0)
9204: FROM
9205: mtl_system_items_vl msik /* Bug 5581528 */
9206: WHERE
9207: organization_id = p_Organization_Id
9208: AND concatenated_segments like p_concatenated_segments||l_append
9209: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 9264: mtl_system_items_vl msik, /* Bug 5581528 */

9260: NVL(TRACKING_QUANTITY_IND,'P'),
9261: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9262: NVL(DUAL_UOM_DEVIATION_LOW,0)
9263: FROM
9264: mtl_system_items_vl msik, /* Bug 5581528 */
9265: mtl_cross_references mcr
9266: WHERE
9267: msik.organization_id = p_organization_id
9268: AND msik.inventory_item_id = mcr.inventory_item_id

Line 9330: mtl_system_items_vl msik /* Bug 5581528 */

9326: NVL(TRACKING_QUANTITY_IND,'P'),
9327: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9328: NVL(DUAL_UOM_DEVIATION_LOW,0)
9329: FROM
9330: mtl_system_items_vl msik /* Bug 5581528 */
9331: WHERE
9332: organization_id = p_Organization_Id
9333: AND concatenated_segments like p_concatenated_segments||l_append
9334: AND (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')

Line 9385: mtl_system_items_vl msik, /* Bug 5581528 */

9381: NVL(TRACKING_QUANTITY_IND,'P'),
9382: NVL(DUAL_UOM_DEVIATION_HIGH,0),
9383: NVL(DUAL_UOM_DEVIATION_LOW,0)
9384: FROM
9385: mtl_system_items_vl msik, /* Bug 5581528 */
9386: mtl_cross_references mcr
9387: WHERE
9388: msik.organization_id = p_organization_id
9389: AND msik.inventory_item_id = mcr.inventory_item_id