[Home] [Help]
41: , to_char(poh.org_id) --
42: FROM rcv_supply ms
43: , rcv_transactions rt
44: , po_headers_all poh
45: , po_lines_all pol
46: , po_line_types plt
47: , mtl_parameters mp
48: WHERE poh.po_header_id = ms.po_header_id
49: AND ms.quantity > 0
74: OR rt.routing_header_id IS NULL)
75: ORDER BY decode(rtrim(poh.segment1,'0123456789'),null,null,poh.segment1),
76: decode(rtrim(poh.segment1,'0123456789'),null,to_number(poh.segment1),null); --
77:
78: /* Modified the above Query for Bug # 7113772. Removed the join from po_lines_all and po_line_types due to bad performance */
79:
80: SELECT DISTINCT poh.Segment1 ,
81: poh.po_Header_Id ,
82: poh.Type_LookUp_Code ,
107: FROM po_Line_Types
108: WHERE Outside_Operation_Flag = 'Y'
109: AND Line_Type_Id IN
110: (SELECT Line_Type_Id
111: FROM po_Lines_All
112: WHERE po_Header_Id = poh.po_Header_Id
113: )
114: )
115: OR EXISTS
113: )
114: )
115: OR EXISTS
116: (SELECT 1
117: FROM po_Lines_All
118: WHERE po_Header_Id = poh.po_Header_Id
119: AND Item_Id IS NULL
120: )
121: OR EXISTS
122: (SELECT 1
123: FROM mtl_System_Items_kfv msik
124: WHERE msik.Inventory_Item_Id IN
125: (SELECT Item_Id
126: FROM po_Lines_All
127: WHERE po_Header_Id = poh.po_Header_Id
128: )
129: AND msik.Organization_Id = p_organization_id
130: AND msik.mtl_Transactions_Enabled_Flag = 'N'
248: to_char(poh.org_id)
249: FROM RCV_SUPPLY RSUP,
250: RCV_TRANSACTIONS RT,
251: PO_LOOKUP_CODES PLC,
252: PO_LINES_ALL pol,
253: PO_LINE_LOCATIONS_ALL PLL,
254: PO_HEADERS_ALL POH,
255: po_line_types plt,--BUG 5166887
256: mtl_parameters mp --BUG 5166887
410: , pl.item_id
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
458: uom_code
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
941: AND msik.organization_id = p_organization_id
942: AND msik.mtl_transactions_enabled_flag = 'N')
943: OR exists
944: (select '1' from po_headers_all poh
945: , po_lines_all pol
946: , po_line_types plt
947: where rt.po_header_id is not null
948: and rt.po_header_id = poh.po_header_id
949: and poh.po_header_id = pol.po_header_id
1112: AND msik.organization_id = p_organization_id
1113: AND msik.mtl_transactions_enabled_flag = 'N')
1114: OR exists (select '1'
1115: from po_headers_all poh
1116: , po_lines_all pol
1117: , po_line_types plt
1118: where rt.po_header_id is not null
1119: and rt.po_header_id = poh.po_header_id
1120: and poh.po_header_id = pol.po_header_id
1458: i.e. doc type of PO.
1459: a. In the condition, "AND NVL(poll.approved_flag,'N') = 'Y' " ,
1460: nvl() has been removed.
1461: b. Removed the tables mtl_system_items_kfv and mtl_units_of_measure which are
1462: joined with po_lines_all table through outer join.
1463: c. Added the hint " +index(POH PO_HEADERS_U2) " to use the index PO_HEADERS_U2.
1464: d. Removed the Distinct clause from the select statement.
1465: */
1466:
1526: AND EXISTS
1527: (
1528: SELECT
1529: 'Valid PO Shipments'
1530: FROM po_lines_all pl,
1531: po_line_locations_all poll
1532: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1533: , mtl_parameters mp,
1534: rcv_parameters rp
1589: AND EXISTS
1590: (
1591: SELECT
1592: 'Valid PO Shipments'
1593: FROM po_lines_all pl ,
1594: mtl_related_items mri ,
1595: mtl_system_items_kfv msi ,
1596: po_line_locations_all poll
1597: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1641: -- and cross ref item case.
1642:
1643: -- Added the Hint /*+LEADING(PL)*/ as part of Fix for Bug 4638235
1644: -- incase Item ID is given and Doc No is not given.
1645: -- Query needs to be driven from PO_LINES_ALL table. Here subquery
1646: -- has been made as join.
1647: OPEN x_doc_num_lov FOR
1648: SELECT /*+LEADING(PL)*/
1649: -- DISTINCT
1664: lookup_code FIELD13,
1665: to_char(poh.org_id) FIELD14 --
1666: FROM po_headers poh,
1667: fnd_lookup_values_vl flv ,
1668: po_lines_all pl,
1669: po_line_locations_all poll
1670: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1671: , mtl_parameters mp,
1672: rcv_parameters rp
1722: lookup_code FIELD13,
1723: to_char(poh.org_id) FIELD14 --
1724: FROM po_headers poh,
1725: fnd_lookup_values_vl flv ,
1726: po_lines_all pl ,
1727: mtl_related_items mri ,
1728: mtl_system_items_kfv msi ,
1729: po_line_locations_all poll
1730: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1784: lookup_code FIELD13,
1785: to_char(poh.org_id) FIELD14 --
1786: FROM po_headers poh,
1787: fnd_lookup_values_vl flv ,
1788: po_lines_all pl ,
1789: mtl_related_items mri ,
1790: mtl_system_items_kfv msi ,
1791: po_line_locations_all poll
1792: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1865: AND EXISTS
1866: (
1867: SELECT
1868: 'Valid PO Shipments'
1869: FROM po_lines_all pl ,
1870: po_line_locations_all poll
1871: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1872: , mtl_parameters mp,
1873: rcv_parameters rp
2410: AND EXISTS
2411: (
2412: SELECT
2413: 'Valid PO Shipments'
2414: FROM po_lines_all pl ,
2415: po_line_locations_all poll
2416: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
2417: , mtl_parameters mp,
2418: rcv_parameters rp
2474: AND EXISTS
2475: (
2476: SELECT
2477: 'Valid PO Shipments'
2478: FROM po_lines_all pl ,
2479: mtl_related_items mri ,
2480: mtl_system_items_kfv msi ,
2481: po_line_locations_all poll
2482: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
2555: AND EXISTS
2556: (
2557: SELECT
2558: 'Valid PO Shipments'
2559: FROM po_lines_all pl ,
2560: po_line_locations_all poll
2561: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
2562: , mtl_parameters mp,
2563: rcv_parameters rp
2899: , inv_ui_item_lovs.get_conversion_rate(mum.uom_code,
2900: p_organization_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
2943: , inv_ui_item_lovs.get_conversion_rate(mum.uom_code,
2944: p_organization_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
3002: pl.Item_Id)
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 (+)
3038: , pda.po_distribution_id
3039: , (pda.quantity_ordered - pda.quantity_delivered)
3040: , woov.repetitive_schedule_id
3041: FROM po_headers_all phl
3042: , po_lines_all pla
3043: , po_distributions_all pda
3044: , po_releases_all prl
3045: , wip_osp_jobs_val_v wojv
3046: , wip_osp_lines_val_v wolv
3076: , pda.po_distribution_id
3077: , (pda.quantity_ordered - pda.quantity_delivered)
3078: , woov.repetitive_schedule_id
3079: FROM po_headers_all phl
3080: , po_lines_all pla
3081: , po_distributions_all pda
3082: , wip_osp_jobs_val_v wojv
3083: , wip_osp_lines_val_v wolv
3084: , wip_osp_operations_val_v woov
3128: , pr.po_release_id
3129: , pr.release_date
3130: from po_releases_all pr
3131: , po_line_locations_all pll
3132: , po_lines_all pl
3133: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
3134: , mtl_parameters mp
3135: , rcv_parameters rp
3136: -- End for Bug 7440217
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
3295: where pol.po_header_id = p_poHeaderID
3296: and exists (select 1
3297: from po_line_locations_all pll
3298: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
3371: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
3372: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
3464: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
3472: and pol.item_id = msi.inventory_item_id
3473: and pol.vendor_product_num IS NOT NULL
3474: and pol.po_header_id = p_poHeaderID
3475: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3476: and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
3477: where pol.po_header_id = p_poHeaderID
3478: and exists (select 1
3479: from po_line_locations_all pll
3480: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
3553: 'P',
3554: 0,
3555: 0,
3556: 'N'
3557: from po_lines_all pol
3558: , mtl_units_of_measure mum
3559: -- Bug 2619063, 2614016
3560: -- Modified to select the base uom for the uom class defined on po.
3561: where mum.uom_class = (SELECT mum2.uom_class
3643: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
3660: and mcr.inventory_item_id = msi.inventory_item_id
3661: and pol.item_id = msi.inventory_item_id
3662: and pol.po_header_id = p_poHeaderID
3663: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3664: and msi.inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
3665: where pol.po_header_id = p_poHeaderID
3666: and exists (select 1
3667: from po_line_locations_all pll
3668: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
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
3755: pol.po_header_id = p_poHeaderID
3756: and pol.po_line_id = p_poLineID
3757: and exists (select 1
3758: from po_line_locations_all pll
3832: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
3833: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
3926: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
3934: and pol.item_id = msi.inventory_item_id
3935: and pol.vendor_product_num IS NOT NULL
3936: and pol.po_header_id = p_poHeaderID
3937: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3938: and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
3939: pl.po_header_id = p_poHeaderID
3940: and pl.po_line_id = p_poLineID
3941: and exists (select 1
3942: from po_line_locations_all pll
4016: 'P',
4017: 0,
4018: 0,
4019: 'N'
4020: from po_lines_all pol
4021: , mtl_units_of_measure mum
4022: -- Bug 2619063, 2614016
4023: -- Modified to select the base uom for the uom class defined on po.
4024: where mum.uom_class = (SELECT mum2.uom_class
4107: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
4124: and mcr.inventory_item_id = msi.inventory_item_id
4125: and pol.item_id = msi.inventory_item_id
4126: and pol.po_header_id = p_poHeaderID
4127: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4128: and msi.inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
4129: pl.po_header_id = p_poHeaderID
4130: and pl.po_line_id = p_poLineID
4131: and exists (select 1
4132: from po_line_locations_all pll
4161: else
4162: -- *****************************
4163: --- headerBased PJM Transaction
4164: -- Bug 4602289
4165: -- In the first query PO_LINES_ALL is
4166: -- joined to enforce a nested loop
4167: -- which would perform better for queries
4168: -- with/without item
4169: -- HeaderBased PJM PO_LINES_U2 is enforced
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')
4227: and inventory_item_id = pol.item_id -- bug 4602289
4304: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
4305: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
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,
4358: Nvl(msi.revision_qty_control_code,1),
4397: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
4407: and pol.po_header_id = p_poHeaderID
4408: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4409: and inventory_item_id IN
4410: ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
4411: p_poHeaderID
4412: and exists (select 1
4413: from po_line_locations_all pll
4414: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
4486: 'P',
4487: 0,
4488: 0,
4489: 'N'
4490: from po_lines_all pol
4491: , mtl_units_of_measure mum
4492: -- Bug 2619063, 2614016
4493: -- Modified to select the base uom for the uom class defined on po.
4494: where mum.uom_class = (SELECT mum2.uom_class
4577: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
4595: and pol.item_id = msi.inventory_item_id
4596: and pol.po_header_id = p_poHeaderID
4597: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4598: and msi.inventory_item_id IN
4599: ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
4600: p_poHeaderID
4601: and exists (select 1
4602: from po_line_locations_all pll
4603: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
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
4696: where pol.po_header_id = p_poHeaderID
4697: and exists (select 1
4698: from po_line_locations_all pll
4699: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
4765: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
4766: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
4851: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
4859: and pol.item_id = msi.inventory_item_id
4860: and pol.vendor_product_num IS NOT NULL
4861: and pol.po_header_id = p_poHeaderID
4862: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
4863: and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
4864: where pol.po_header_id = p_poHeaderID
4865: and exists (select 1
4866: from po_line_locations_all pll
4867: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
4933: 'P',
4934: 0,
4935: 0,
4936: 'N'
4937: from po_lines_all pol
4938: , mtl_units_of_measure mum
4939: -- Bug 2619063, 2614016
4940: -- Modified to select the base uom for the uom class defined on po.
4941: where mum.uom_class = (SELECT mum2.uom_class
5017: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
5034: and mcr.inventory_item_id = msi.inventory_item_id
5035: and pol.item_id = msi.inventory_item_id
5036: and pol.po_header_id = p_poHeaderID
5037: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5038: and msi.inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
5039: where pol.po_header_id = p_poHeaderID
5040: and exists (select 1
5041: from po_line_locations_all pll
5042: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
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
5121: pl.po_header_id = p_poHeaderID
5122: and pl.po_line_id = p_poLineID
5123: and exists (select 1
5124: from po_line_locations_all pll
5189: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
5190: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
5274: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
5282: and pol.item_id = msi.inventory_item_id
5283: and pol.vendor_product_num IS NOT NULL
5284: and pol.po_header_id = p_poHeaderID
5285: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5286: and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
5287: pl.po_header_id = p_poHeaderID
5288: and pl.po_line_id = p_poLineID
5289: and exists (select 1
5290: from po_line_locations_all pll
5355: 'P',
5356: 0,
5357: 0,
5358: 'N'
5359: from po_lines_all pol
5360: , mtl_units_of_measure mum
5361: -- Bug 2619063, 2614016
5362: -- Modified to select the base uom for the uom class defined on po.
5363: where mum.uom_class = (SELECT mum2.uom_class
5437: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
5454: and mcr.inventory_item_id = msi.inventory_item_id
5455: and pol.item_id = msi.inventory_item_id
5456: and pol.po_header_id = p_poHeaderID
5457: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5458: and msi.inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
5459: pl.po_header_id = p_poHeaderID
5460: and pl.po_line_id = p_poLineID
5461: and exists (select 1
5462: from po_line_locations_all pll
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
5541: ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id = p_poHeaderID
5542: and exists (select 1
5543: from po_line_locations_all pll
5544: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
5545: , mtl_parameters mp,
5608: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
5609: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
5692: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
5702: and pol.po_header_id = p_poHeaderID
5703: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5704: and inventory_item_id IN
5705: ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
5706: p_poHeaderID
5707: and exists (select 1
5708: from po_line_locations_all pll
5709: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
5773: 'P',
5774: 0,
5775: 0,
5776: 'N'
5777: from po_lines_all pol
5778: , mtl_units_of_measure mum
5779: -- Bug 2619063, 2614016
5780: -- Modified to select the base uom for the uom class defined on po.
5781: where mum.uom_class = (SELECT mum2.uom_class
5855: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
5873: and pol.item_id = msi.inventory_item_id
5874: and pol.po_header_id = p_poHeaderID
5875: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
5876: and msi.inventory_item_id IN
5877: ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id = p_poHeaderID
5878: and exists (select 1
5879: from po_line_locations_all pll
5880: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
5881: , mtl_parameters mp,
6030: NVL(MSI.TRACKING_QUANTITY_IND,'P'),
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
6040: and pol.vendor_product_num IS NOT NULL
6041: and pol.po_header_id = Nvl(p_poheaderid,pol.po_header_id)
6042: and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
6043: and inventory_item_id IN (SELECT pl.item_id
6044: FROM po_lines_all pl
6045: WHERE pl.po_header_id = rsl.po_header_id
6046: and pl.po_line_id = rsl.po_line_id
6047: and exists (select 1
6048: from po_line_locations_all pll
6124: 'P',
6125: 0,
6126: 0,
6127: 'N'
6128: from po_lines_all pol
6129: , mtl_units_of_measure mum
6130: , rcv_shipment_lines rsl
6131: -- Bug 2619063, 2614016
6132: -- Modified to select the base uom for the uom class defined on po.
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'
6917: FROM po_lines_all pol,
6918: mtl_related_items mri,
6919: po_line_locations_all pll
6920: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
6921: , mtl_parameters mp,
6992: NVL(MSI.DUAL_UOM_DEVIATION_HIGH,0),
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
7134: 0,
7135: 0,
7136: 'N'
7137: FROM
7138: po_lines_all pol,
7139: mtl_units_of_measure mum
7140: -- Bug 2619063, 2614016
7141: -- Modified to select the base uom for the uom class defined on po.
7142: WHERE mum.uom_class = (SELECT mum2.uom_class