[Home] [Help]
632: ) IS
633: BEGIN
634: OPEN x_revs FOR
635: SELECT a.revision, a.effectivity_date, NVL(a.description, '')
636: FROM mtl_item_revisions a
637: WHERE a.organization_id = p_organization_id
638: AND a.inventory_item_id = p_inventory_item_id
639: /* Bug# 8912324: Commented the code below so as to allow unimplemented item
640: revisions for other transactions except misc issue/ receipt */
689: ) IS
690: BEGIN
691: OPEN x_revs FOR
692: SELECT a.revision, a.effectivity_date, NVL(a.description, '')
693: FROM mtl_item_revisions a
694: WHERE a.organization_id = p_organization_id
695: AND a.inventory_item_id = p_inventory_item_id
696: AND a.implementation_date is not null --BUG 7204523 Added to restrict the revisions that are not yet implemented.
697: AND a.revision LIKE (p_revision)
2487: OPEN x_revs FOR
2488: SELECT revision
2489: , effectivity_date
2490: , NVL(description, '')
2491: FROM mtl_item_revisions
2492: WHERE organization_id = p_organization_id
2493: AND inventory_item_id = p_inventory_item_id
2494: AND revision LIKE (p_revision)
2495: ORDER BY UPPER(revision);
2497: OPEN x_revs FOR
2498: SELECT UNIQUE mir.revision
2499: , mir.effectivity_date
2500: , NVL(mir.description, '')
2501: FROM mtl_item_revisions mir, mtl_physical_inventory_tags mpit
2502: WHERE mir.organization_id = p_organization_id
2503: AND mir.inventory_item_id = p_inventory_item_id
2504: AND mir.revision LIKE (p_revision)
2505: AND mir.inventory_item_id = mpit.inventory_item_id
3058: OPEN x_revs FOR
3059: SELECT revision
3060: , effectivity_date
3061: , NVL(description, '')
3062: FROM mtl_item_revisions
3063: WHERE organization_id = p_organization_id
3064: AND inventory_item_id = p_inventory_item_id
3065: AND revision LIKE (p_revision)
3066: ORDER BY UPPER(revision);
3068: OPEN x_revs FOR
3069: SELECT UNIQUE mir.revision
3070: , mir.effectivity_date
3071: , NVL(mir.description, '')
3072: FROM mtl_item_revisions mir, mtl_cycle_count_entries mcce
3073: WHERE mir.organization_id = p_organization_id
3074: AND mir.inventory_item_id = p_inventory_item_id
3075: AND mir.revision LIKE (p_revision)
3076: AND mir.inventory_item_id = mcce.inventory_item_id
3419: OPEN x_revs FOR
3420: SELECT DISTINCT a.revision
3421: , a.effectivity_date
3422: , NVL(a.description, '')
3423: FROM mtl_item_revisions a, mtl_txn_request_lines b
3424: WHERE b.organization_id = p_organization_id
3425: AND b.inventory_item_id = p_inventory_item_id
3426: AND b.lpn_id = p_lpn_id
3427: AND b.inspection_status is not null --8405606
4922: OPEN x_revs FOR
4923: SELECT DISTINCT a.revision
4924: , a.effectivity_date
4925: , NVL(a.description, '')
4926: FROM mtl_item_revisions a, rcv_transactions_v b
4927: WHERE b.to_organization_id = p_organization_id
4928: AND b.item_id = p_inventory_item_id
4929: AND b.po_header_id = p_source_id
4930: AND b.inspection_status_code = 'NOT INSPECTED'
4937: OPEN x_revs FOR
4938: SELECT DISTINCT a.revision
4939: , a.effectivity_date
4940: , NVL(a.description, '')
4941: FROM mtl_item_revisions a, rcv_transactions_v b
4942: WHERE b.to_organization_id = p_organization_id
4943: AND b.item_id = p_inventory_item_id
4944: AND b.receipt_source_code <> 'VENDOR'
4945: AND b.shipment_header_id = p_source_id
4953: OPEN x_revs FOR
4954: SELECT DISTINCT a.revision
4955: , a.effectivity_date
4956: , NVL(a.description, '')
4957: FROM mtl_item_revisions a, rcv_transactions_v b
4958: WHERE b.to_organization_id = p_organization_id
4959: AND b.item_id = p_inventory_item_id
4960: AND b.receipt_source_code <> 'VENDOR'
4961: AND b.oe_order_header_id = p_source_id
4969: OPEN x_revs FOR
4970: SELECT DISTINCT a.revision
4971: , a.effectivity_date
4972: , NVL(a.description, '')
4973: FROM mtl_item_revisions a, rcv_transactions_v b
4974: WHERE b.to_organization_id = p_organization_id
4975: AND b.item_id = p_inventory_item_id
4976: AND b.shipment_header_id = TO_CHAR(p_source_id)
4977: AND b.inspection_status_code = 'NOT INSPECTED'
5230: OPEN x_revs FOR
5231: SELECT DISTINCT wlc.revision
5232: , mir.effectivity_date
5233: , NVL(mir.description, '')
5234: FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
5235: WHERE wlc.organization_id = p_organization_id
5236: AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
5237: AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
5238: AND mir.organization_id = wlc.organization_id
5723: OPEN x_revs FOR
5724: SELECT DISTINCT wlc.revision
5725: , mir.effectivity_date
5726: , NVL(mir.description, '')
5727: FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
5728: WHERE wlc.organization_id = p_organization_id
5729: AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
5730: AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
5731: AND wlc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5786: OPEN x_revs FOR
5787: SELECT DISTINCT miv.revision
5788: , miv.effectivity_date
5789: , NVL(miv.description, '')
5790: FROM mtl_item_revisions miv, rcv_supply rs
5791: WHERE miv.organization_id = p_organization_id
5792: AND miv.inventory_item_id = p_inventory_item_id
5793: AND rs.shipment_header_id(+) = p_shipment_header_id
5794: AND (rs.shipment_header_id IS NULL
5813: OPEN x_revs FOR
5814: SELECT DISTINCT miv.revision
5815: , miv.effectivity_date
5816: , NVL(miv.description, '')
5817: FROM mtl_item_revisions miv, rcv_supply rs
5818: WHERE miv.organization_id = p_organization_id
5819: AND miv.inventory_item_id = p_inventory_item_id
5820: AND rs.shipment_header_id(+) = p_shipment_header_id
5821: AND (rs.shipment_header_id IS NULL
5835: OPEN x_revs FOR
5836: SELECT DISTINCT miv.revision
5837: , miv.effectivity_date
5838: , NVL(miv.description, '')
5839: FROM mtl_item_revisions miv, rcv_supply rs
5840: WHERE miv.organization_id = p_organization_id
5841: AND miv.inventory_item_id = p_inventory_item_id
5842: AND rs.po_header_id(+) = p_po_header_id
5843: AND (rs.po_header_id IS NULL