eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BOM_COPY_BILL dependencies on MTL_ITEM_REVISIONS_B

Line 370: FROM mtl_item_revisions_b mir

366: IS
367: SELECT REVISION
368: FROM (
369: SELECT revision
370: FROM mtl_item_revisions_b mir
371: WHERE mir.inventory_item_id = p_item_id
372: AND mir.organization_id = p_org_id
373: AND mir.effectivity_date <= p_rev_date
374: ORDER BY mir.effectivity_date DESC)

Line 426: FROM mtl_item_revisions_b

422: INTO l_return
423: FROM DUAL
424: WHERE EXISTS (
425: SELECT revision_id
426: FROM mtl_item_revisions_b
427: WHERE inventory_item_id = p_from_item_id
428: AND organization_id = p_from_org_id
429: AND revision_id = p_revision_id);
430:

Line 481: FROM mtl_item_revisions_b fmirb,

477: l_revision_id NUMBER;
478: BEGIN
479: SELECT tmirb.revision_id
480: INTO l_revision_id
481: FROM mtl_item_revisions_b fmirb,
482: mtl_item_revisions_b tmirb
483: WHERE tmirb.inventory_item_id = p_item_id
484: AND tmirb.organization_id = p_org_id
485: AND tmirb.revision = fmirb.revision

Line 482: mtl_item_revisions_b tmirb

478: BEGIN
479: SELECT tmirb.revision_id
480: INTO l_revision_id
481: FROM mtl_item_revisions_b fmirb,
482: mtl_item_revisions_b tmirb
483: WHERE tmirb.inventory_item_id = p_item_id
484: AND tmirb.organization_id = p_org_id
485: AND tmirb.revision = fmirb.revision
486: AND fmirb.revision_id = p_revision_id;

Line 513: FROM mtl_item_revisions_b

509: SELECT CONCAT (TO_CHAR (effectivity_date, 'yyyymmddhh24miss'),
510: TO_CHAR (NVL (p_end_item_minor_rev_id, 9999999999999999))
511: )
512: INTO l_rev_code
513: FROM mtl_item_revisions_b
514: WHERE revision_id = p_end_item_rev_id;
515:
516: RETURN l_rev_code;
517: EXCEPTION

Line 672: l_fixed_rev mtl_item_revisions_b.revision%TYPE;

668: -- l_from_bom_item_minor_rev_code VARCHAR2 (30);
669: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
670: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
671: l_no_access_comp_cnt NUMBER;
672: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
673: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
674: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
675: l_use_eco_flag varchar2(1) := 'N';
676: l_error_msg_tbl Error_Handler.Error_Tbl_Type;

Line 673: l_current_item_rev mtl_item_revisions_b.revision%TYPE;

669: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
670: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
671: l_no_access_comp_cnt NUMBER;
672: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
673: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
674: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
675: l_use_eco_flag varchar2(1) := 'N';
676: l_error_msg_tbl Error_Handler.Error_Tbl_Type;
677:

Line 674: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;

670: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
671: l_no_access_comp_cnt NUMBER;
672: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
673: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
674: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
675: l_use_eco_flag varchar2(1) := 'N';
676: l_error_msg_tbl Error_Handler.Error_Tbl_Type;
677:
678: l_default_wip_params NUMBER;

Line 777: FROM mtl_item_revisions_b mir

773: )
774: IS
775: SELECT revision_id,
776: revision
777: FROM mtl_item_revisions_b mir
778: WHERE mir.inventory_item_id = p_item_id
779: AND mir.organization_id = p_org_id
780: AND mir.effectivity_date <= p_rev_date
781: AND ROWNUM < 2

Line 816: FROM mtl_item_revisions_b r,

812: IS
813: SELECT CONCAT (TO_CHAR (r.effectivity_date, 'yyyymmddhh24miss'),
814: maxr.minor_rev_id
815: ) mrev_code
816: FROM mtl_item_revisions_b r,
817: (SELECT NVL (MAX (minor_revision_id), 0) minor_rev_id
818: FROM ego_minor_revisions
819: WHERE obj_name = 'EGO_ITEM' --p_obj_name
820: AND pk1_value = p_pk1_value

Line 974: FROM mtl_item_revisions_b

970: p_revision IN VARCHAR2
971: )
972: IS
973: SELECT effectivity_date
974: FROM mtl_item_revisions_b
975: WHERE inventory_item_id = p_inventory_item_id
976: AND organization_id = p_organization_id
977: AND revision = p_revision;
978: BEGIN

Line 1653: FROM mtl_item_revisions_b fmirb,

1649: 0 AS from_end_item_minor_rev_id,
1650: 0 AS to_end_item_minor_rev_id,
1651: (
1652: SELECT tmirb.revision_id
1653: FROM mtl_item_revisions_b fmirb,
1654: mtl_item_revisions_b tmirb
1655: WHERE tmirb.inventory_item_id = bic.component_item_id
1656: AND tmirb.organization_id = to_org_id
1657: AND tmirb.revision = fmirb.revision

Line 1654: mtl_item_revisions_b tmirb

1650: 0 AS to_end_item_minor_rev_id,
1651: (
1652: SELECT tmirb.revision_id
1653: FROM mtl_item_revisions_b fmirb,
1654: mtl_item_revisions_b tmirb
1655: WHERE tmirb.inventory_item_id = bic.component_item_id
1656: AND tmirb.organization_id = to_org_id
1657: AND tmirb.revision = fmirb.revision
1658: AND fmirb.revision_id = bic.component_item_revision_id

Line 2052: FROM mtl_item_revisions_b fmirb,

2048: 0 AS from_end_item_minor_rev_id,
2049: 0 AS to_end_item_minor_rev_id,
2050: (
2051: SELECT tmirb.revision_id
2052: FROM mtl_item_revisions_b fmirb,
2053: mtl_item_revisions_b tmirb
2054: WHERE tmirb.inventory_item_id = bic.component_item_id
2055: AND tmirb.organization_id = to_org_id
2056: AND tmirb.revision = fmirb.revision

Line 2053: mtl_item_revisions_b tmirb

2049: 0 AS to_end_item_minor_rev_id,
2050: (
2051: SELECT tmirb.revision_id
2052: FROM mtl_item_revisions_b fmirb,
2053: mtl_item_revisions_b tmirb
2054: WHERE tmirb.inventory_item_id = bic.component_item_id
2055: AND tmirb.organization_id = to_org_id
2056: AND tmirb.revision = fmirb.revision
2057: AND fmirb.revision_id = bic.component_item_revision_id

Line 2426: FROM MTL_ITEM_REVISIONS_B

2422: AND trgt_comps_end_item_rev_id IS NOT NULL
2423: AND p_end_item_rev_id IS NOT NULL
2424: AND ( (
2425: SELECT REVISION
2426: FROM MTL_ITEM_REVISIONS_B
2427: WHERE REVISION_ID = bic.from_end_item_rev_id
2428: ) < (
2429: SELECT REVISION
2430: FROM MTL_ITEM_REVISIONS_B

Line 2430: FROM MTL_ITEM_REVISIONS_B

2426: FROM MTL_ITEM_REVISIONS_B
2427: WHERE REVISION_ID = bic.from_end_item_rev_id
2428: ) < (
2429: SELECT REVISION
2430: FROM MTL_ITEM_REVISIONS_B
2431: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2432: )
2433: AND
2434: (

Line 2436: FROM MTL_ITEM_REVISIONS_B

2432: )
2433: AND
2434: (
2435: SELECT REVISION
2436: FROM MTL_ITEM_REVISIONS_B
2437: WHERE REVISION_ID = p_end_item_rev_id
2438: ) < (
2439: SELECT REVISION
2440: FROM MTL_ITEM_REVISIONS_B

Line 2440: FROM MTL_ITEM_REVISIONS_B

2436: FROM MTL_ITEM_REVISIONS_B
2437: WHERE REVISION_ID = p_end_item_rev_id
2438: ) < (
2439: SELECT REVISION
2440: FROM MTL_ITEM_REVISIONS_B
2441: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2442: )
2443: )
2444: THEN trgt_comps_end_item_rev_id

Line 2453: FROM MTL_ITEM_REVISIONS_B

2449: AND trgt_comps_end_item_rev_id IS NOT NULL
2450: AND p_end_item_rev_id IS NOT NULL
2451: AND ( (
2452: SELECT REVISION
2453: FROM MTL_ITEM_REVISIONS_B
2454: WHERE REVISION_ID = bic.from_end_item_rev_id
2455: ) = (
2456: SELECT REVISION
2457: FROM MTL_ITEM_REVISIONS_B

Line 2457: FROM MTL_ITEM_REVISIONS_B

2453: FROM MTL_ITEM_REVISIONS_B
2454: WHERE REVISION_ID = bic.from_end_item_rev_id
2455: ) = (
2456: SELECT REVISION
2457: FROM MTL_ITEM_REVISIONS_B
2458: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2459: )
2460: AND
2461: (

Line 2463: FROM MTL_ITEM_REVISIONS_B

2459: )
2460: AND
2461: (
2462: SELECT REVISION
2463: FROM MTL_ITEM_REVISIONS_B
2464: WHERE REVISION_ID = p_end_item_rev_id
2465: ) > (
2466: SELECT REVISION
2467: FROM MTL_ITEM_REVISIONS_B

Line 2467: FROM MTL_ITEM_REVISIONS_B

2463: FROM MTL_ITEM_REVISIONS_B
2464: WHERE REVISION_ID = p_end_item_rev_id
2465: ) > (
2466: SELECT REVISION
2467: FROM MTL_ITEM_REVISIONS_B
2468: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2469: )
2470: )
2471: THEN trgt_comps_end_item_rev_id

Line 2477: FROM MTL_ITEM_REVISIONS_B

2473: WHEN bill_or_eco = 1
2474: AND eco_end_item_rev_id IS NOT NULL
2475: AND (
2476: SELECT REVISION
2477: FROM MTL_ITEM_REVISIONS_B
2478: WHERE REVISION_ID = bic.from_end_item_rev_id
2479: ) < (
2480: SELECT REVISION
2481: FROM MTL_ITEM_REVISIONS_B

Line 2481: FROM MTL_ITEM_REVISIONS_B

2477: FROM MTL_ITEM_REVISIONS_B
2478: WHERE REVISION_ID = bic.from_end_item_rev_id
2479: ) < (
2480: SELECT REVISION
2481: FROM MTL_ITEM_REVISIONS_B
2482: WHERE REVISION_ID = eco_end_item_rev_id
2483: )
2484: THEN trgt_comps_end_item_rev_id
2485: ELSE

Line 2488: FROM mtl_item_revisions_b fmirb,

2484: THEN trgt_comps_end_item_rev_id
2485: ELSE
2486: NVL((
2487: SELECT tmirb.revision_id
2488: FROM mtl_item_revisions_b fmirb,
2489: mtl_item_revisions_b tmirb
2490: WHERE tmirb.inventory_item_id = to_item_id
2491: AND tmirb.organization_id = to_org_id
2492: AND tmirb.revision = fmirb.revision

Line 2489: mtl_item_revisions_b tmirb

2485: ELSE
2486: NVL((
2487: SELECT tmirb.revision_id
2488: FROM mtl_item_revisions_b fmirb,
2489: mtl_item_revisions_b tmirb
2490: WHERE tmirb.inventory_item_id = to_item_id
2491: AND tmirb.organization_id = to_org_id
2492: AND tmirb.revision = fmirb.revision
2493: AND fmirb.revision_id = bic.from_end_item_rev_id

Line 2503: FROM MTL_ITEM_REVISIONS_B

2499: WHEN bill_or_eco = 1
2500: AND
2501: (
2502: SELECT REVISION
2503: FROM MTL_ITEM_REVISIONS_B
2504: WHERE REVISION_ID = bic.to_end_item_rev_id
2505: ) <
2506: (
2507: SELECT REVISION

Line 2508: FROM MTL_ITEM_REVISIONS_B

2504: WHERE REVISION_ID = bic.to_end_item_rev_id
2505: ) <
2506: (
2507: SELECT REVISION
2508: FROM MTL_ITEM_REVISIONS_B
2509: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2510: )
2511: THEN NULL
2512: ELSE

Line 2515: FROM mtl_item_revisions_b fmirb,

2511: THEN NULL
2512: ELSE
2513: (
2514: SELECT tmirb.revision_id
2515: FROM mtl_item_revisions_b fmirb,
2516: mtl_item_revisions_b tmirb
2517: WHERE tmirb.inventory_item_id = to_item_id
2518: AND tmirb.organization_id = to_org_id
2519: AND tmirb.revision = fmirb.revision

Line 2516: mtl_item_revisions_b tmirb

2512: ELSE
2513: (
2514: SELECT tmirb.revision_id
2515: FROM mtl_item_revisions_b fmirb,
2516: mtl_item_revisions_b tmirb
2517: WHERE tmirb.inventory_item_id = to_item_id
2518: AND tmirb.organization_id = to_org_id
2519: AND tmirb.revision = fmirb.revision
2520: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 2529: FROM mtl_item_revisions_b fmirb,

2525: 0 AS from_end_item_minor_rev_id,
2526: 0 AS to_end_item_minor_rev_id,
2527: (
2528: SELECT tmirb.revision_id
2529: FROM mtl_item_revisions_b fmirb,
2530: mtl_item_revisions_b tmirb
2531: WHERE tmirb.inventory_item_id = bic.component_item_id
2532: AND tmirb.organization_id = to_org_id
2533: AND tmirb.revision = fmirb.revision

Line 2530: mtl_item_revisions_b tmirb

2526: 0 AS to_end_item_minor_rev_id,
2527: (
2528: SELECT tmirb.revision_id
2529: FROM mtl_item_revisions_b fmirb,
2530: mtl_item_revisions_b tmirb
2531: WHERE tmirb.inventory_item_id = bic.component_item_id
2532: AND tmirb.organization_id = to_org_id
2533: AND tmirb.revision = fmirb.revision
2534: AND fmirb.revision_id = bic.component_item_revision_id

Line 2848: FROM mtl_item_revisions_b fmirb

2844: AND p_end_item_rev_id IS NOT NULL
2845: AND (
2846: (
2847: SELECT fmirb.effectivity_date
2848: FROM mtl_item_revisions_b fmirb
2849: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2850: ) < trgt_comps_eff_date
2851: AND (
2852: SELECT fmirb.effectivity_date

Line 2853: FROM mtl_item_revisions_b fmirb

2849: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2850: ) < trgt_comps_eff_date
2851: AND (
2852: SELECT fmirb.effectivity_date
2853: FROM mtl_item_revisions_b fmirb
2854: WHERE fmirb.revision_id = p_end_item_rev_id
2855: ) < trgt_comps_eff_date
2856: )
2857: -- Explosion in the Past and Effectivity Date is also in the past, then the components

Line 2866: FROM mtl_item_revisions_b fmirb

2862: AND p_end_item_rev_id IS NOT NULL
2863: AND bic.from_end_item_rev_id = p_end_item_rev_id -- Future Exploded Rev
2864: AND (
2865: SELECT fmirb.effectivity_date
2866: FROM mtl_item_revisions_b fmirb
2867: WHERE fmirb.revision_id = p_end_item_rev_id
2868: ) > trgt_comps_eff_date
2869: -- Explosion in the future and Effectivity Rev is also in the future, then the components
2870: -- which are effective at the explosion rev alone will be effective from trgt_comps_eff_date

Line 2877: FROM mtl_item_revisions_b fmirb

2873: WHEN bill_or_eco = 1
2874: AND bic.from_end_item_rev_id IS NOT NULL
2875: AND (
2876: SELECT fmirb.effectivity_date
2877: FROM mtl_item_revisions_b fmirb
2878: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2879: ) < trgt_comps_eff_date
2880: THEN trgt_comps_eff_date
2881: ELSE

Line 2884: FROM mtl_item_revisions_b fmirb

2880: THEN trgt_comps_eff_date
2881: ELSE
2882: (
2883: SELECT fmirb.effectivity_date
2884: FROM mtl_item_revisions_b fmirb
2885: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2886: )
2887: END AS effectivity_date,
2888: x_e_change_notice,

Line 2898: FROM mtl_item_revisions_b fmirb

2894: -- Past disabled components will be copied with disable date as null
2895: WHEN bill_or_eco = 2
2896: AND (
2897: ( SELECT fmirb.effectivity_date
2898: FROM mtl_item_revisions_b fmirb
2899: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2900: ) < x_effectivity_date
2901: )
2902: THEN TO_DATE (NULL)

Line 2907: FROM mtl_item_revisions_b fmirb

2903: -- Past disabled components will be copied with disable date as null
2904: WHEN bill_or_eco = 1
2905: AND (
2906: ( SELECT fmirb.effectivity_date
2907: FROM mtl_item_revisions_b fmirb
2908: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2909: ) < trgt_comps_eff_date
2910: )
2911: THEN TO_DATE (NULL)

Line 2916: FROM mtl_item_revisions_b fmirb

2912: -- Future disabled components should be disabled as per the disable date of component
2913: ELSE
2914: (
2915: SELECT fmirb.effectivity_date
2916: FROM mtl_item_revisions_b fmirb
2917: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2918: )
2919: END AS disable_date,
2920: bic.attribute_category,

Line 2971: FROM mtl_item_revisions_b fmirb,

2967: 0 AS from_end_item_minor_rev_id,
2968: 0 AS to_end_item_minor_rev_id,
2969: (
2970: SELECT tmirb.revision_id
2971: FROM mtl_item_revisions_b fmirb,
2972: mtl_item_revisions_b tmirb
2973: WHERE tmirb.inventory_item_id = bic.component_item_id
2974: AND tmirb.organization_id = to_org_id
2975: AND tmirb.revision = fmirb.revision

Line 2972: mtl_item_revisions_b tmirb

2968: 0 AS to_end_item_minor_rev_id,
2969: (
2970: SELECT tmirb.revision_id
2971: FROM mtl_item_revisions_b fmirb,
2972: mtl_item_revisions_b tmirb
2973: WHERE tmirb.inventory_item_id = bic.component_item_id
2974: AND tmirb.organization_id = to_org_id
2975: AND tmirb.revision = fmirb.revision
2976: AND fmirb.revision_id = bic.component_item_revision_id

Line 3336: FROM MTL_ITEM_REVISIONS_B tmirb

3332: -- which are with smaller revision effective will be effective from eco_end_item_rev_id
3333: AND EXISTS
3334: (
3335: SELECT tmirb.REVISION
3336: FROM MTL_ITEM_REVISIONS_B tmirb
3337: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3338: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3339: )
3340: AND EXISTS

Line 3343: FROM MTL_ITEM_REVISIONS_B tmirb

3339: )
3340: AND EXISTS
3341: (
3342: SELECT tmirb.REVISION
3343: FROM MTL_ITEM_REVISIONS_B tmirb
3344: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3345: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, rev_date)
3346: )
3347: THEN trgt_comps_end_item_rev_id

Line 3356: FROM MTL_ITEM_REVISIONS_B tmirb

3352: AND bic.effectivity_date = rev_date
3353: AND EXISTS
3354: (
3355: SELECT tmirb.REVISION
3356: FROM MTL_ITEM_REVISIONS_B tmirb
3357: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3358: AND tmirb.revision < get_current_item_rev(from_item_id, from_org_id, rev_date)
3359: )
3360: THEN trgt_comps_end_item_rev_id

Line 3366: FROM MTL_ITEM_REVISIONS_B tmirb

3362: WHEN bill_or_eco = 1
3363: AND EXISTS
3364: (
3365: SELECT tmirb.REVISION
3366: FROM MTL_ITEM_REVISIONS_B tmirb
3367: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3368: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3369: )
3370: THEN trgt_comps_end_item_rev_id

Line 3374: FROM mtl_item_revisions_b tmirb

3370: THEN trgt_comps_end_item_rev_id
3371: ELSE
3372: (
3373: SELECT tmirb.revision_id
3374: FROM mtl_item_revisions_b tmirb
3375: WHERE tmirb.inventory_item_id = to_item_id
3376: AND tmirb.organization_id = to_org_id
3377: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3378: )

Line 3388: FROM mtl_item_revisions_b tmirb

3384: AND bic.disable_date IS NOT NULL
3385: AND EXISTS
3386: (
3387: SELECT tmirb.revision_id
3388: FROM mtl_item_revisions_b tmirb
3389: WHERE tmirb.inventory_item_id = to_item_id
3390: AND tmirb.organization_id = to_org_id
3391: AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3392: )

Line 3396: FROM MTL_ITEM_REVISIONS_B mirb

3392: )
3393: AND EXISTS
3394: (
3395: SELECT mirb.REVISION
3396: FROM MTL_ITEM_REVISIONS_B mirb
3397: WHERE mirb.REVISION_ID = trgt_comps_end_item_rev_id
3398: AND mirb.revision > get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3399: )
3400: THEN to_number(NULL)

Line 3406: FROM mtl_item_revisions_b tmirb

3402: bic.disable_date IS NOT NULL
3403: THEN
3404: (
3405: SELECT tmirb.revision_id
3406: FROM mtl_item_revisions_b tmirb
3407: WHERE tmirb.inventory_item_id = to_item_id
3408: AND tmirb.organization_id = to_org_id
3409: AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3410: )

Line 3420: FROM mtl_item_revisions_b fmirb,

3416: 0 AS from_end_item_minor_rev_id,
3417: 0 AS to_end_item_minor_rev_id,
3418: (
3419: SELECT tmirb.revision_id
3420: FROM mtl_item_revisions_b fmirb,
3421: mtl_item_revisions_b tmirb
3422: WHERE tmirb.inventory_item_id = bic.component_item_id
3423: AND tmirb.organization_id = to_org_id
3424: AND tmirb.revision = fmirb.revision

Line 3421: mtl_item_revisions_b tmirb

3417: 0 AS to_end_item_minor_rev_id,
3418: (
3419: SELECT tmirb.revision_id
3420: FROM mtl_item_revisions_b fmirb,
3421: mtl_item_revisions_b tmirb
3422: WHERE tmirb.inventory_item_id = bic.component_item_id
3423: AND tmirb.organization_id = to_org_id
3424: AND tmirb.revision = fmirb.revision
3425: AND fmirb.revision_id = bic.component_item_revision_id

Line 4584: FROM mtl_item_revisions_b fmirb,

4580: -- we need not copy.
4581: )
4582: AND NOT EXISTS (
4583: SELECT tmirb.revision_id
4584: FROM mtl_item_revisions_b fmirb,
4585: mtl_item_revisions_b tmirb
4586: WHERE tmirb.inventory_item_id = bcb.component_item_id
4587: AND tmirb.organization_id = to_org_id
4588: AND tmirb.revision = fmirb.revision

Line 4585: mtl_item_revisions_b tmirb

4581: )
4582: AND NOT EXISTS (
4583: SELECT tmirb.revision_id
4584: FROM mtl_item_revisions_b fmirb,
4585: mtl_item_revisions_b tmirb
4586: WHERE tmirb.inventory_item_id = bcb.component_item_id
4587: AND tmirb.organization_id = to_org_id
4588: AND tmirb.revision = fmirb.revision
4589: AND fmirb.revision_id =

Line 6784: FROM mtl_item_revisions_b mir

6780: AND NVL(bsb.alternate_bom_designator,bom_globals.get_primary_ui) = NVL(p_structure_name,bom_globals.get_primary_ui)
6781: ),2) AS assembly_type,
6782: (SELECT revision
6783: FROM (SELECT revision
6784: FROM mtl_item_revisions_b mir
6785: WHERE inventory_item_id = p_item_id
6786: AND organization_id = x_org_list_tbl(l_index)
6787: AND mir.effectivity_date <= p_effectivity_date
6788: ORDER BY effectivity_date DESC, revision DESC)

Line 6792: FROM mtl_item_revisions_b mir

6788: ORDER BY effectivity_date DESC, revision DESC)
6789: WHERE rownum < 2) AS current_item_rev,
6790: (SELECT revision_id
6791: FROM (SELECT revision_id
6792: FROM mtl_item_revisions_b mir
6793: WHERE inventory_item_id = p_item_id
6794: AND organization_id = x_org_list_tbl(l_index)
6795: AND mir.effectivity_date <= p_effectivity_date
6796: ORDER BY effectivity_date DESC, revision DESC)

Line 6800: FROM mtl_item_revisions_b mir

6796: ORDER BY effectivity_date DESC, revision DESC)
6797: WHERE rownum < 2) AS current_item_rev_id,
6798: (SELECT revision_label
6799: FROM (SELECT revision_label
6800: FROM mtl_item_revisions_b mir
6801: WHERE inventory_item_id = p_item_id
6802: AND organization_id = x_org_list_tbl(l_index)
6803: AND mir.effectivity_date <= p_effectivity_date
6804: ORDER BY effectivity_date DESC, revision DESC)

Line 7216: l_fixed_rev mtl_item_revisions_b.revision%TYPE;

7212: l_comp_ctr NUMBER;
7213: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7214: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7215: l_no_access_comp_cnt NUMBER;
7216: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7217: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7218: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7219: l_from_comps num_varray := num_varray();
7220: l_to_comps num_varray := num_varray();

Line 7217: l_current_item_rev mtl_item_revisions_b.revision%TYPE;

7213: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7214: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7215: l_no_access_comp_cnt NUMBER;
7216: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7217: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7218: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7219: l_from_comps num_varray := num_varray();
7220: l_to_comps num_varray := num_varray();
7221: l_last_copied_comp_seq_id NUMBER := -1;

Line 7218: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;

7214: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7215: l_no_access_comp_cnt NUMBER;
7216: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7217: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7218: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7219: l_from_comps num_varray := num_varray();
7220: l_to_comps num_varray := num_varray();
7221: l_last_copied_comp_seq_id NUMBER := -1;
7222: l_default_wip_params NUMBER;

Line 7306: FROM mtl_item_revisions_b mir

7302: )
7303: IS
7304: SELECT revision_id,
7305: revision
7306: FROM mtl_item_revisions_b mir
7307: WHERE mir.inventory_item_id = p_item_id
7308: AND mir.organization_id = p_org_id
7309: AND mir.effectivity_date <= p_rev_date
7310: AND ROWNUM < 2

Line 7377: FROM mtl_item_revisions_b

7373: p_revision IN VARCHAR2
7374: )
7375: IS
7376: SELECT effectivity_date
7377: FROM mtl_item_revisions_b
7378: WHERE inventory_item_id = p_inventory_item_id
7379: AND organization_id = p_organization_id
7380: AND revision = p_revision;
7381:

Line 7846: FROM mtl_item_revisions_b fmirb,

7842: 0 AS from_end_item_minor_rev_id,
7843: 0 AS to_end_item_minor_rev_id,
7844: (
7845: SELECT tmirb.revision_id
7846: FROM mtl_item_revisions_b fmirb,
7847: mtl_item_revisions_b tmirb
7848: WHERE tmirb.inventory_item_id = bic.component_item_id
7849: AND tmirb.organization_id = to_org_id
7850: AND tmirb.revision = fmirb.revision

Line 7847: mtl_item_revisions_b tmirb

7843: 0 AS to_end_item_minor_rev_id,
7844: (
7845: SELECT tmirb.revision_id
7846: FROM mtl_item_revisions_b fmirb,
7847: mtl_item_revisions_b tmirb
7848: WHERE tmirb.inventory_item_id = bic.component_item_id
7849: AND tmirb.organization_id = to_org_id
7850: AND tmirb.revision = fmirb.revision
7851: AND fmirb.revision_id = bic.component_item_revision_id

Line 8215: FROM mtl_item_revisions_b fmirb,

8211: 0 AS from_end_item_minor_rev_id,
8212: 0 AS to_end_item_minor_rev_id,
8213: (
8214: SELECT tmirb.revision_id
8215: FROM mtl_item_revisions_b fmirb,
8216: mtl_item_revisions_b tmirb
8217: WHERE tmirb.inventory_item_id = bic.component_item_id
8218: AND tmirb.organization_id = to_org_id
8219: AND tmirb.revision = fmirb.revision

Line 8216: mtl_item_revisions_b tmirb

8212: 0 AS to_end_item_minor_rev_id,
8213: (
8214: SELECT tmirb.revision_id
8215: FROM mtl_item_revisions_b fmirb,
8216: mtl_item_revisions_b tmirb
8217: WHERE tmirb.inventory_item_id = bic.component_item_id
8218: AND tmirb.organization_id = to_org_id
8219: AND tmirb.revision = fmirb.revision
8220: AND fmirb.revision_id = bic.component_item_revision_id

Line 8579: FROM mtl_item_revisions_b fmirb,

8575: AND (
8576: EXISTS
8577: (
8578: SELECT tmirb.revision_id
8579: FROM mtl_item_revisions_b fmirb,
8580: mtl_item_revisions_b tmirb
8581: WHERE tmirb.inventory_item_id = to_item_id
8582: AND tmirb.organization_id = to_org_id
8583: AND tmirb.revision = fmirb.revision

Line 8580: mtl_item_revisions_b tmirb

8576: EXISTS
8577: (
8578: SELECT tmirb.revision_id
8579: FROM mtl_item_revisions_b fmirb,
8580: mtl_item_revisions_b tmirb
8581: WHERE tmirb.inventory_item_id = to_item_id
8582: AND tmirb.organization_id = to_org_id
8583: AND tmirb.revision = fmirb.revision
8584: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 8589: FROM MTL_ITEM_REVISIONS_B

8585: )
8586: AND
8587: (
8588: SELECT REVISION
8589: FROM MTL_ITEM_REVISIONS_B
8590: WHERE REVISION_ID = bic.to_end_item_rev_id
8591: ) >
8592: (
8593: SELECT REVISION

Line 8594: FROM MTL_ITEM_REVISIONS_B

8590: WHERE REVISION_ID = bic.to_end_item_rev_id
8591: ) >
8592: (
8593: SELECT REVISION
8594: FROM MTL_ITEM_REVISIONS_B
8595: WHERE REVISION_ID = x_end_item_rev_id
8596: )
8597: )
8598: THEN

Line 8601: FROM mtl_item_revisions_b fmirb,

8597: )
8598: THEN
8599: (
8600: SELECT tmirb.revision_id
8601: FROM mtl_item_revisions_b fmirb,
8602: mtl_item_revisions_b tmirb
8603: WHERE tmirb.inventory_item_id = to_item_id
8604: AND tmirb.organization_id = to_org_id
8605: AND tmirb.revision = fmirb.revision

Line 8602: mtl_item_revisions_b tmirb

8598: THEN
8599: (
8600: SELECT tmirb.revision_id
8601: FROM mtl_item_revisions_b fmirb,
8602: mtl_item_revisions_b tmirb
8603: WHERE tmirb.inventory_item_id = to_item_id
8604: AND tmirb.organization_id = to_org_id
8605: AND tmirb.revision = fmirb.revision
8606: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 8616: FROM mtl_item_revisions_b fmirb,

8612: 0 AS from_end_item_minor_rev_id,
8613: 0 AS to_end_item_minor_rev_id,
8614: (
8615: SELECT tmirb.revision_id
8616: FROM mtl_item_revisions_b fmirb,
8617: mtl_item_revisions_b tmirb
8618: WHERE tmirb.inventory_item_id = bic.component_item_id
8619: AND tmirb.organization_id = to_org_id
8620: AND tmirb.revision = fmirb.revision

Line 8617: mtl_item_revisions_b tmirb

8613: 0 AS to_end_item_minor_rev_id,
8614: (
8615: SELECT tmirb.revision_id
8616: FROM mtl_item_revisions_b fmirb,
8617: mtl_item_revisions_b tmirb
8618: WHERE tmirb.inventory_item_id = bic.component_item_id
8619: AND tmirb.organization_id = to_org_id
8620: AND tmirb.revision = fmirb.revision
8621: AND fmirb.revision_id = bic.component_item_revision_id

Line 8706: FROM mtl_item_revisions_b mirb

8702: -- Component Action is exclude or enable
8703: -- we need not copy.
8704: )
8705: AND ( p_cpy_past_eff_comps = 'Y' AND ( ( SELECT mirb.revision
8706: FROM mtl_item_revisions_b mirb
8707: WHERE mirb.revision_id = bev.from_end_item_rev_id
8708: )
8709: <=
8710: (

Line 8712: FROM mtl_item_revisions_b mirb

8708: )
8709: <=
8710: (
8711: SELECT mirb.revision
8712: FROM mtl_item_revisions_b mirb
8713: WHERE mirb.revision_id = p_end_item_rev_id
8714: )
8715: ) -- For first revised item we can have past eff comps as eff on the target date
8716: OR ( p_cpy_past_eff_comps = 'N' AND bev.from_end_item_rev_id = p_end_item_rev_id )

Line 8941: FROM mtl_item_revisions_b fmirb

8937: WHEN bic.to_end_item_rev_id IS NOT NULL
8938: AND (
8939: (
8940: SELECT fmirb.effectivity_date
8941: FROM mtl_item_revisions_b fmirb
8942: WHERE fmirb.revision_id = bic.to_end_item_rev_id
8943: ) > x_effectivity_date
8944: )
8945: THEN (

Line 8947: FROM mtl_item_revisions_b fmirb

8943: ) > x_effectivity_date
8944: )
8945: THEN (
8946: SELECT fmirb.effectivity_date
8947: FROM mtl_item_revisions_b fmirb
8948: WHERE fmirb.revision_id = bic.to_end_item_rev_id
8949: )
8950: -- Past disabled components will be copied with disable date as null
8951: WHEN (

Line 8953: FROM mtl_item_revisions_b fmirb

8949: )
8950: -- Past disabled components will be copied with disable date as null
8951: WHEN (
8952: ( SELECT fmirb.effectivity_date
8953: FROM mtl_item_revisions_b fmirb
8954: WHERE fmirb.revision_id = bic.to_end_item_rev_id
8955: ) < x_effectivity_date
8956: )
8957: THEN TO_DATE (NULL)

Line 9014: FROM mtl_item_revisions_b fmirb,

9010: 0 AS from_end_item_minor_rev_id,
9011: 0 AS to_end_item_minor_rev_id,
9012: (
9013: SELECT tmirb.revision_id
9014: FROM mtl_item_revisions_b fmirb,
9015: mtl_item_revisions_b tmirb
9016: WHERE tmirb.inventory_item_id = bic.component_item_id
9017: AND tmirb.organization_id = to_org_id
9018: AND tmirb.revision = fmirb.revision

Line 9015: mtl_item_revisions_b tmirb

9011: 0 AS to_end_item_minor_rev_id,
9012: (
9013: SELECT tmirb.revision_id
9014: FROM mtl_item_revisions_b fmirb,
9015: mtl_item_revisions_b tmirb
9016: WHERE tmirb.inventory_item_id = bic.component_item_id
9017: AND tmirb.organization_id = to_org_id
9018: AND tmirb.revision = fmirb.revision
9019: AND fmirb.revision_id = bic.component_item_revision_id

Line 9104: FROM mtl_item_revisions_b mirb

9100: -- Component Action is exclude or enable
9101: -- we need not copy.
9102: )
9103: AND ( ( p_cpy_past_eff_comps = 'Y' AND ( SELECT mirb.effectivity_date
9104: FROM mtl_item_revisions_b mirb
9105: WHERE mirb.revision_id = bev.from_end_item_rev_id
9106: ) <= ( SELECT mirb.effectivity_date
9107: FROM mtl_item_revisions_b mirb
9108: WHERE mirb.revision_id = p_end_item_rev_id

Line 9107: FROM mtl_item_revisions_b mirb

9103: AND ( ( p_cpy_past_eff_comps = 'Y' AND ( SELECT mirb.effectivity_date
9104: FROM mtl_item_revisions_b mirb
9105: WHERE mirb.revision_id = bev.from_end_item_rev_id
9106: ) <= ( SELECT mirb.effectivity_date
9107: FROM mtl_item_revisions_b mirb
9108: WHERE mirb.revision_id = p_end_item_rev_id
9109: )) -- For first revised item we can have past eff comps as eff on the target date
9110: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9111: FROM mtl_item_revisions_b mirb

Line 9111: FROM mtl_item_revisions_b mirb

9107: FROM mtl_item_revisions_b mirb
9108: WHERE mirb.revision_id = p_end_item_rev_id
9109: )) -- For first revised item we can have past eff comps as eff on the target date
9110: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9111: FROM mtl_item_revisions_b mirb
9112: WHERE mirb.revision_id = bev.from_end_item_rev_id
9113: ) = ( SELECT mirb.effectivity_date
9114: FROM mtl_item_revisions_b mirb
9115: WHERE mirb.revision_id = p_end_item_rev_id

Line 9114: FROM mtl_item_revisions_b mirb

9110: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9111: FROM mtl_item_revisions_b mirb
9112: WHERE mirb.revision_id = bev.from_end_item_rev_id
9113: ) = ( SELECT mirb.effectivity_date
9114: FROM mtl_item_revisions_b mirb
9115: WHERE mirb.revision_id = p_end_item_rev_id
9116: ) )
9117: )
9118: AND EXISTS

Line 9385: FROM mtl_item_revisions_b tmirb

9381: WHEN bic.disable_date IS NOT NULL
9382: AND EXISTS
9383: (
9384: SELECT tmirb.revision_id
9385: FROM mtl_item_revisions_b tmirb
9386: WHERE tmirb.inventory_item_id = to_item_id
9387: AND tmirb.organization_id = to_org_id
9388: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9389: )

Line 9393: FROM MTL_ITEM_REVISIONS_B mirb

9389: )
9390: AND EXISTS
9391: (
9392: SELECT mirb.REVISION
9393: FROM MTL_ITEM_REVISIONS_B mirb
9394: WHERE mirb.REVISION_ID = eco_end_item_rev_id
9395: AND mirb.revision < get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9396: )
9397: THEN

Line 9400: FROM mtl_item_revisions_b tmirb

9396: )
9397: THEN
9398: (
9399: SELECT tmirb.revision_id
9400: FROM mtl_item_revisions_b tmirb
9401: WHERE tmirb.inventory_item_id = to_item_id
9402: AND tmirb.organization_id = to_org_id
9403: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9404: )

Line 9413: FROM mtl_item_revisions_b fmirb,

9409: 0 AS from_end_item_minor_rev_id,
9410: 0 AS to_end_item_minor_rev_id,
9411: (
9412: SELECT tmirb.revision_id
9413: FROM mtl_item_revisions_b fmirb,
9414: mtl_item_revisions_b tmirb
9415: WHERE tmirb.inventory_item_id = bic.component_item_id
9416: AND tmirb.organization_id = to_org_id
9417: AND tmirb.revision = fmirb.revision

Line 9414: mtl_item_revisions_b tmirb

9410: 0 AS to_end_item_minor_rev_id,
9411: (
9412: SELECT tmirb.revision_id
9413: FROM mtl_item_revisions_b fmirb,
9414: mtl_item_revisions_b tmirb
9415: WHERE tmirb.inventory_item_id = bic.component_item_id
9416: AND tmirb.organization_id = to_org_id
9417: AND tmirb.revision = fmirb.revision
9418: AND fmirb.revision_id = bic.component_item_revision_id

Line 10523: FROM mtl_item_revisions_b fmirb,

10519: -- we need not copy.
10520: )
10521: AND NOT EXISTS (
10522: SELECT tmirb.revision_id
10523: FROM mtl_item_revisions_b fmirb,
10524: mtl_item_revisions_b tmirb
10525: WHERE tmirb.inventory_item_id = bcb.component_item_id
10526: AND tmirb.organization_id = to_org_id
10527: AND tmirb.revision = fmirb.revision

Line 10524: mtl_item_revisions_b tmirb

10520: )
10521: AND NOT EXISTS (
10522: SELECT tmirb.revision_id
10523: FROM mtl_item_revisions_b fmirb,
10524: mtl_item_revisions_b tmirb
10525: WHERE tmirb.inventory_item_id = bcb.component_item_id
10526: AND tmirb.organization_id = to_org_id
10527: AND tmirb.revision = fmirb.revision
10528: AND fmirb.revision_id =