[Home] [Help]
75: AND (mp.wms_enabled_flag = 'N' OR (mp.wms_enabled_flag = 'Y'
76: AND (plt.outside_operation_flag = 'Y'
77: OR pol.item_id is NULL
78: OR exists (SELECT 1
79: FROM mtl_system_items_kfv msik
80: WHERE msik.inventory_item_id = pol.item_id
81: AND msik.organization_id = p_organization_id
82: AND msik.mtl_transactions_enabled_flag = 'N'))))
83: AND poh.segment1 LIKE (p_po_number)
137: AND Item_Id IS NULL
138: )
139: OR EXISTS
140: (SELECT 1
141: FROM mtl_System_Items_kfv msik
142: WHERE msik.Inventory_Item_Id IN
143: (SELECT Item_Id
144: FROM po_Lines_trx_v
145: WHERE po_Header_Id = poh.po_Header_Id
302: OR (mp.wms_enabled_flag = 'Y'
303: AND (plt.outside_operation_flag = 'Y'
304: OR pol.item_id is NULL
305: OR exists (SELECT 1
306: FROM mtl_system_items_kfv msik
307: WHERE msik.inventory_item_id = pol.item_id
308: AND msik.organization_id = p_organization_id
309: AND msik.mtl_transactions_enabled_flag = 'N'))))
310: -- END BUG 5166887
965: and rsl.shipment_header_id = rsh.shipment_header_id
966: and (mp.wms_enabled_flag = 'N'
967: OR (mp.wms_enabled_flag = 'Y' AND (rsl.item_id is NULL
968: OR exists (SELECT 1
969: FROM mtl_system_items_kfv msik
970: WHERE msik.inventory_item_id = rsl.item_id
971: AND msik.organization_id = p_organization_id
972: AND msik.mtl_transactions_enabled_flag = 'N')
973: OR exists
1138: AND (mp.wms_enabled_flag = 'N'
1139: OR (mp.wms_enabled_flag = 'Y'
1140: AND (rsl.item_id is NULL
1141: OR exists (SELECT 1
1142: FROM mtl_system_items_kfv msik
1143: WHERE msik.inventory_item_id = rsl.item_id
1144: AND msik.organization_id = p_organization_id
1145: AND msik.mtl_transactions_enabled_flag = 'N')
1146: OR exists (select '1'
1489: The following changes have been done while selecting Purchase Orders.
1490: i.e. doc type of PO.
1491: a. In the condition, "AND NVL(poll.approved_flag,'N') = 'Y' " ,
1492: nvl() has been removed.
1493: b. Removed the tables mtl_system_items_kfv and mtl_units_of_measure which are
1494: joined with po_lines_all table through outer join.
1495: c. Added the hint " +index(POH PO_HEADERS_U2) " to use the index PO_HEADERS_U2.
1496: d. Removed the Distinct clause from the select statement.
1497: */
1627: SELECT
1628: 'Valid PO Shipments'
1629: FROM po_lines_trx_v pl , -- CLM project, bug 9403291
1630: mtl_related_items mri ,
1631: mtl_system_items_kfv msi ,
1632: po_line_locations_trx_v poll -- CLM project, bug 9403291
1633: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1634: , mtl_parameters mp,
1635: rcv_parameters rp
1762: FROM po_headers_trx_v poh, -- CLM project, bug 9403291
1763: fnd_lookup_values_vl flv ,
1764: po_lines_trx_v pl , -- CLM project, bug 9403291
1765: mtl_related_items mri ,
1766: mtl_system_items_kfv msi ,
1767: po_line_locations_trx_v poll -- CLM project, bug 9403291
1768: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1769: , mtl_parameters mp,
1770: rcv_parameters rp
1825: FROM po_headers_trx_v poh, -- CLM project, bug 9403291
1826: fnd_lookup_values_vl flv ,
1827: po_lines_trx_v pl , -- CLM project, bug 9403291
1828: mtl_related_items mri ,
1829: mtl_system_items_kfv msi ,
1830: po_line_locations_trx_v poll -- CLM project, bug 9403291
1831: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1832: , mtl_parameters mp,
1833: rcv_parameters rp
2528: SELECT
2529: 'Valid PO Shipments'
2530: FROM po_lines_trx_v pl , -- CLM project, bug 9403291
2531: mtl_related_items mri ,
2532: mtl_system_items_kfv msi ,
2533: po_line_locations_trx_v poll -- CLM project, bug 9403291
2534: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
2535: , mtl_parameters mp,
2536: rcv_parameters rp
2949: END;
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).
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
3527: and pol.item_id = mri.inventory_item_id /* Condition changed by Bug10124916 */
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
4106: and pol.item_id = mri.inventory_item_id
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
4694: and pol.item_id = mri.inventory_item_id /* Condition changed by Bug10124916 */
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
5257: and pol.item_id = mri.inventory_item_id /* Condition changed by Bug10124916 */
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
5787: and pol.item_id = mri.inventory_item_id /* Condition changed by Bug10124916 */
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
6310: and pol.item_id = mri.inventory_item_id /* Condition changed by Bug10124916 */