[Home] [Help]
585: ) IS
586: BEGIN
587: OPEN x_revs FOR
588: SELECT a.revision, a.effectivity_date, NVL(a.description, '')
589: FROM mtl_item_revisions a
590: WHERE a.organization_id = p_organization_id
591: AND a.inventory_item_id = p_inventory_item_id
592: AND a.implementation_date is not null --BUG 7204523 Added to restrict the revisions that are not yet implemented.
593: AND a.revision LIKE (p_revision)
2346: OPEN x_revs FOR
2347: SELECT revision
2348: , effectivity_date
2349: , NVL(description, '')
2350: FROM mtl_item_revisions
2351: WHERE organization_id = p_organization_id
2352: AND inventory_item_id = p_inventory_item_id
2353: AND revision LIKE (p_revision)
2354: ORDER BY UPPER(revision);
2356: OPEN x_revs FOR
2357: SELECT UNIQUE mir.revision
2358: , mir.effectivity_date
2359: , NVL(mir.description, '')
2360: FROM mtl_item_revisions mir, mtl_physical_inventory_tags mpit
2361: WHERE mir.organization_id = p_organization_id
2362: AND mir.inventory_item_id = p_inventory_item_id
2363: AND mir.revision LIKE (p_revision)
2364: AND mir.inventory_item_id = mpit.inventory_item_id
2903: OPEN x_revs FOR
2904: SELECT revision
2905: , effectivity_date
2906: , NVL(description, '')
2907: FROM mtl_item_revisions
2908: WHERE organization_id = p_organization_id
2909: AND inventory_item_id = p_inventory_item_id
2910: AND revision LIKE (p_revision)
2911: ORDER BY UPPER(revision);
2913: OPEN x_revs FOR
2914: SELECT UNIQUE mir.revision
2915: , mir.effectivity_date
2916: , NVL(mir.description, '')
2917: FROM mtl_item_revisions mir, mtl_cycle_count_entries mcce
2918: WHERE mir.organization_id = p_organization_id
2919: AND mir.inventory_item_id = p_inventory_item_id
2920: AND mir.revision LIKE (p_revision)
2921: AND mir.inventory_item_id = mcce.inventory_item_id
3193: OPEN x_revs FOR
3194: SELECT DISTINCT a.revision
3195: , a.effectivity_date
3196: , NVL(a.description, '')
3197: FROM mtl_item_revisions a, mtl_txn_request_lines b
3198: WHERE b.organization_id = p_organization_id
3199: AND b.inventory_item_id = p_inventory_item_id
3200: AND b.lpn_id = p_lpn_id
3201: AND b.inspection_status = 1 /* yet to be inspected */
4649: OPEN x_revs FOR
4650: SELECT DISTINCT a.revision
4651: , a.effectivity_date
4652: , NVL(a.description, '')
4653: FROM mtl_item_revisions a, rcv_transactions_v b
4654: WHERE b.to_organization_id = p_organization_id
4655: AND b.item_id = p_inventory_item_id
4656: AND b.po_header_id = p_source_id
4657: AND b.inspection_status_code = 'NOT INSPECTED'
4664: OPEN x_revs FOR
4665: SELECT DISTINCT a.revision
4666: , a.effectivity_date
4667: , NVL(a.description, '')
4668: FROM mtl_item_revisions a, rcv_transactions_v b
4669: WHERE b.to_organization_id = p_organization_id
4670: AND b.item_id = p_inventory_item_id
4671: AND b.receipt_source_code <> 'VENDOR'
4672: AND b.shipment_header_id = p_source_id
4680: OPEN x_revs FOR
4681: SELECT DISTINCT a.revision
4682: , a.effectivity_date
4683: , NVL(a.description, '')
4684: FROM mtl_item_revisions a, rcv_transactions_v b
4685: WHERE b.to_organization_id = p_organization_id
4686: AND b.item_id = p_inventory_item_id
4687: AND b.receipt_source_code <> 'VENDOR'
4688: AND b.oe_order_header_id = p_source_id
4696: OPEN x_revs FOR
4697: SELECT DISTINCT a.revision
4698: , a.effectivity_date
4699: , NVL(a.description, '')
4700: FROM mtl_item_revisions a, rcv_transactions_v b
4701: WHERE b.to_organization_id = p_organization_id
4702: AND b.item_id = p_inventory_item_id
4703: AND b.shipment_header_id = TO_CHAR(p_source_id)
4704: AND b.inspection_status_code = 'NOT INSPECTED'
4949: OPEN x_revs FOR
4950: SELECT DISTINCT wlc.revision
4951: , mir.effectivity_date
4952: , NVL(mir.description, '')
4953: FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
4954: WHERE wlc.organization_id = p_organization_id
4955: AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
4956: AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
4957: AND mir.organization_id = wlc.organization_id
5422: OPEN x_revs FOR
5423: SELECT DISTINCT wlc.revision
5424: , mir.effectivity_date
5425: , NVL(mir.description, '')
5426: FROM mtl_item_revisions mir, wms_lpn_contents_v wlc
5427: WHERE wlc.organization_id = p_organization_id
5428: AND wlc.inventory_item_id = TO_NUMBER(p_inventory_item_id)
5429: AND NVL(wlc.parent_lpn_id, '0') = NVL(TO_NUMBER(p_lpn_id), NVL(wlc.parent_lpn_id, '0'))
5430: AND wlc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5484: OPEN x_revs FOR
5485: SELECT DISTINCT miv.revision
5486: , miv.effectivity_date
5487: , NVL(miv.description, '')
5488: FROM mtl_item_revisions miv, rcv_supply rs
5489: WHERE miv.organization_id = p_organization_id
5490: AND miv.inventory_item_id = p_inventory_item_id
5491: AND rs.shipment_header_id(+) = p_shipment_header_id
5492: AND (rs.shipment_header_id IS NULL
5506: OPEN x_revs FOR
5507: SELECT DISTINCT miv.revision
5508: , miv.effectivity_date
5509: , NVL(miv.description, '')
5510: FROM mtl_item_revisions miv, rcv_supply rs
5511: WHERE miv.organization_id = p_organization_id
5512: AND miv.inventory_item_id = p_inventory_item_id
5513: AND rs.po_header_id(+) = p_po_header_id
5514: AND (rs.po_header_id IS NULL