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 781: FROM mtl_item_revisions_b mir

777: )
778: IS
779: SELECT revision_id,
780: revision
781: FROM mtl_item_revisions_b mir
782: WHERE mir.inventory_item_id = p_item_id
783: AND mir.organization_id = p_org_id
784: AND mir.effectivity_date <= p_rev_date
785: AND ROWNUM < 2

Line 820: FROM mtl_item_revisions_b r,

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

Line 978: FROM mtl_item_revisions_b

974: p_revision IN VARCHAR2
975: )
976: IS
977: SELECT effectivity_date
978: FROM mtl_item_revisions_b
979: WHERE inventory_item_id = p_inventory_item_id
980: AND organization_id = p_organization_id
981: AND revision = p_revision;
982:

Line 1705: FROM mtl_item_revisions_b fmirb,

1701: 0 AS from_end_item_minor_rev_id,
1702: 0 AS to_end_item_minor_rev_id,
1703: (
1704: SELECT tmirb.revision_id
1705: FROM mtl_item_revisions_b fmirb,
1706: mtl_item_revisions_b tmirb
1707: WHERE tmirb.inventory_item_id = bic.component_item_id
1708: AND tmirb.organization_id = to_org_id
1709: AND tmirb.revision = fmirb.revision

Line 1706: mtl_item_revisions_b tmirb

1702: 0 AS to_end_item_minor_rev_id,
1703: (
1704: SELECT tmirb.revision_id
1705: FROM mtl_item_revisions_b fmirb,
1706: mtl_item_revisions_b tmirb
1707: WHERE tmirb.inventory_item_id = bic.component_item_id
1708: AND tmirb.organization_id = to_org_id
1709: AND tmirb.revision = fmirb.revision
1710: AND fmirb.revision_id = bic.component_item_revision_id

Line 2105: FROM mtl_item_revisions_b fmirb,

2101: 0 AS from_end_item_minor_rev_id,
2102: 0 AS to_end_item_minor_rev_id,
2103: (
2104: SELECT tmirb.revision_id
2105: FROM mtl_item_revisions_b fmirb,
2106: mtl_item_revisions_b tmirb
2107: WHERE tmirb.inventory_item_id = bic.component_item_id
2108: AND tmirb.organization_id = to_org_id
2109: AND tmirb.revision = fmirb.revision

Line 2106: mtl_item_revisions_b tmirb

2102: 0 AS to_end_item_minor_rev_id,
2103: (
2104: SELECT tmirb.revision_id
2105: FROM mtl_item_revisions_b fmirb,
2106: mtl_item_revisions_b tmirb
2107: WHERE tmirb.inventory_item_id = bic.component_item_id
2108: AND tmirb.organization_id = to_org_id
2109: AND tmirb.revision = fmirb.revision
2110: AND fmirb.revision_id = bic.component_item_revision_id

Line 2479: FROM MTL_ITEM_REVISIONS_B

2475: AND trgt_comps_end_item_rev_id IS NOT NULL
2476: AND p_end_item_rev_id IS NOT NULL
2477: AND ( (
2478: SELECT REVISION
2479: FROM MTL_ITEM_REVISIONS_B
2480: WHERE REVISION_ID = bic.from_end_item_rev_id
2481: ) < (
2482: SELECT REVISION
2483: FROM MTL_ITEM_REVISIONS_B

Line 2483: FROM MTL_ITEM_REVISIONS_B

2479: FROM MTL_ITEM_REVISIONS_B
2480: WHERE REVISION_ID = bic.from_end_item_rev_id
2481: ) < (
2482: SELECT REVISION
2483: FROM MTL_ITEM_REVISIONS_B
2484: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2485: )
2486: AND
2487: (

Line 2489: FROM MTL_ITEM_REVISIONS_B

2485: )
2486: AND
2487: (
2488: SELECT REVISION
2489: FROM MTL_ITEM_REVISIONS_B
2490: WHERE REVISION_ID = p_end_item_rev_id
2491: ) < (
2492: SELECT REVISION
2493: FROM MTL_ITEM_REVISIONS_B

Line 2493: FROM MTL_ITEM_REVISIONS_B

2489: FROM MTL_ITEM_REVISIONS_B
2490: WHERE REVISION_ID = p_end_item_rev_id
2491: ) < (
2492: SELECT REVISION
2493: FROM MTL_ITEM_REVISIONS_B
2494: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2495: )
2496: )
2497: THEN trgt_comps_end_item_rev_id

Line 2506: FROM MTL_ITEM_REVISIONS_B

2502: AND trgt_comps_end_item_rev_id IS NOT NULL
2503: AND p_end_item_rev_id IS NOT NULL
2504: AND ( (
2505: SELECT REVISION
2506: FROM MTL_ITEM_REVISIONS_B
2507: WHERE REVISION_ID = bic.from_end_item_rev_id
2508: ) = (
2509: SELECT REVISION
2510: FROM MTL_ITEM_REVISIONS_B

Line 2510: FROM MTL_ITEM_REVISIONS_B

2506: FROM MTL_ITEM_REVISIONS_B
2507: WHERE REVISION_ID = bic.from_end_item_rev_id
2508: ) = (
2509: SELECT REVISION
2510: FROM MTL_ITEM_REVISIONS_B
2511: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2512: )
2513: AND
2514: (

Line 2516: FROM MTL_ITEM_REVISIONS_B

2512: )
2513: AND
2514: (
2515: SELECT REVISION
2516: FROM MTL_ITEM_REVISIONS_B
2517: WHERE REVISION_ID = p_end_item_rev_id
2518: ) > (
2519: SELECT REVISION
2520: FROM MTL_ITEM_REVISIONS_B

Line 2520: FROM MTL_ITEM_REVISIONS_B

2516: FROM MTL_ITEM_REVISIONS_B
2517: WHERE REVISION_ID = p_end_item_rev_id
2518: ) > (
2519: SELECT REVISION
2520: FROM MTL_ITEM_REVISIONS_B
2521: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2522: )
2523: )
2524: THEN trgt_comps_end_item_rev_id

Line 2530: FROM MTL_ITEM_REVISIONS_B

2526: WHEN bill_or_eco = 1
2527: AND eco_end_item_rev_id IS NOT NULL
2528: AND (
2529: SELECT REVISION
2530: FROM MTL_ITEM_REVISIONS_B
2531: WHERE REVISION_ID = bic.from_end_item_rev_id
2532: ) < (
2533: SELECT REVISION
2534: FROM MTL_ITEM_REVISIONS_B

Line 2534: FROM MTL_ITEM_REVISIONS_B

2530: FROM MTL_ITEM_REVISIONS_B
2531: WHERE REVISION_ID = bic.from_end_item_rev_id
2532: ) < (
2533: SELECT REVISION
2534: FROM MTL_ITEM_REVISIONS_B
2535: WHERE REVISION_ID = eco_end_item_rev_id
2536: )
2537: THEN trgt_comps_end_item_rev_id
2538: ELSE

Line 2541: FROM mtl_item_revisions_b fmirb,

2537: THEN trgt_comps_end_item_rev_id
2538: ELSE
2539: NVL((
2540: SELECT tmirb.revision_id
2541: FROM mtl_item_revisions_b fmirb,
2542: mtl_item_revisions_b tmirb
2543: WHERE tmirb.inventory_item_id = to_item_id
2544: AND tmirb.organization_id = to_org_id
2545: AND tmirb.revision = fmirb.revision

Line 2542: mtl_item_revisions_b tmirb

2538: ELSE
2539: NVL((
2540: SELECT tmirb.revision_id
2541: FROM mtl_item_revisions_b fmirb,
2542: mtl_item_revisions_b tmirb
2543: WHERE tmirb.inventory_item_id = to_item_id
2544: AND tmirb.organization_id = to_org_id
2545: AND tmirb.revision = fmirb.revision
2546: AND fmirb.revision_id = bic.from_end_item_rev_id

Line 2556: FROM MTL_ITEM_REVISIONS_B

2552: WHEN bill_or_eco = 1
2553: AND
2554: (
2555: SELECT REVISION
2556: FROM MTL_ITEM_REVISIONS_B
2557: WHERE REVISION_ID = bic.to_end_item_rev_id
2558: ) <
2559: (
2560: SELECT REVISION

Line 2561: FROM MTL_ITEM_REVISIONS_B

2557: WHERE REVISION_ID = bic.to_end_item_rev_id
2558: ) <
2559: (
2560: SELECT REVISION
2561: FROM MTL_ITEM_REVISIONS_B
2562: WHERE REVISION_ID = trgt_comps_end_item_rev_id
2563: )
2564: THEN NULL
2565: ELSE

Line 2568: FROM mtl_item_revisions_b fmirb,

2564: THEN NULL
2565: ELSE
2566: (
2567: SELECT tmirb.revision_id
2568: FROM mtl_item_revisions_b fmirb,
2569: mtl_item_revisions_b tmirb
2570: WHERE tmirb.inventory_item_id = to_item_id
2571: AND tmirb.organization_id = to_org_id
2572: AND tmirb.revision = fmirb.revision

Line 2569: mtl_item_revisions_b tmirb

2565: ELSE
2566: (
2567: SELECT tmirb.revision_id
2568: FROM mtl_item_revisions_b fmirb,
2569: mtl_item_revisions_b tmirb
2570: WHERE tmirb.inventory_item_id = to_item_id
2571: AND tmirb.organization_id = to_org_id
2572: AND tmirb.revision = fmirb.revision
2573: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 2582: FROM mtl_item_revisions_b fmirb,

2578: 0 AS from_end_item_minor_rev_id,
2579: 0 AS to_end_item_minor_rev_id,
2580: (
2581: SELECT tmirb.revision_id
2582: FROM mtl_item_revisions_b fmirb,
2583: mtl_item_revisions_b tmirb
2584: WHERE tmirb.inventory_item_id = bic.component_item_id
2585: AND tmirb.organization_id = to_org_id
2586: AND tmirb.revision = fmirb.revision

Line 2583: mtl_item_revisions_b tmirb

2579: 0 AS to_end_item_minor_rev_id,
2580: (
2581: SELECT tmirb.revision_id
2582: FROM mtl_item_revisions_b fmirb,
2583: mtl_item_revisions_b tmirb
2584: WHERE tmirb.inventory_item_id = bic.component_item_id
2585: AND tmirb.organization_id = to_org_id
2586: AND tmirb.revision = fmirb.revision
2587: AND fmirb.revision_id = bic.component_item_revision_id

Line 2901: FROM mtl_item_revisions_b fmirb

2897: AND p_end_item_rev_id IS NOT NULL
2898: AND (
2899: (
2900: SELECT fmirb.effectivity_date
2901: FROM mtl_item_revisions_b fmirb
2902: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2903: ) < trgt_comps_eff_date
2904: AND (
2905: SELECT fmirb.effectivity_date

Line 2906: FROM mtl_item_revisions_b fmirb

2902: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2903: ) < trgt_comps_eff_date
2904: AND (
2905: SELECT fmirb.effectivity_date
2906: FROM mtl_item_revisions_b fmirb
2907: WHERE fmirb.revision_id = p_end_item_rev_id
2908: ) < trgt_comps_eff_date
2909: )
2910: -- Explosion in the Past and Effectivity Date is also in the past, then the components

Line 2919: FROM mtl_item_revisions_b fmirb

2915: AND p_end_item_rev_id IS NOT NULL
2916: AND bic.from_end_item_rev_id = p_end_item_rev_id -- Future Exploded Rev
2917: AND (
2918: SELECT fmirb.effectivity_date
2919: FROM mtl_item_revisions_b fmirb
2920: WHERE fmirb.revision_id = p_end_item_rev_id
2921: ) > trgt_comps_eff_date
2922: -- Explosion in the future and Effectivity Rev is also in the future, then the components
2923: -- which are effective at the explosion rev alone will be effective from trgt_comps_eff_date

Line 2930: FROM mtl_item_revisions_b fmirb

2926: WHEN bill_or_eco = 1
2927: AND bic.from_end_item_rev_id IS NOT NULL
2928: AND (
2929: SELECT fmirb.effectivity_date
2930: FROM mtl_item_revisions_b fmirb
2931: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2932: ) < trgt_comps_eff_date
2933: THEN trgt_comps_eff_date
2934: ELSE

Line 2937: FROM mtl_item_revisions_b fmirb

2933: THEN trgt_comps_eff_date
2934: ELSE
2935: (
2936: SELECT fmirb.effectivity_date
2937: FROM mtl_item_revisions_b fmirb
2938: WHERE fmirb.revision_id = bic.from_end_item_rev_id
2939: )
2940: END AS effectivity_date,
2941: x_e_change_notice,

Line 2951: FROM mtl_item_revisions_b fmirb

2947: -- Past disabled components will be copied with disable date as null
2948: WHEN bill_or_eco = 2
2949: AND (
2950: ( SELECT fmirb.effectivity_date
2951: FROM mtl_item_revisions_b fmirb
2952: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2953: ) < x_effectivity_date
2954: )
2955: THEN TO_DATE (NULL)

Line 2960: FROM mtl_item_revisions_b fmirb

2956: -- Past disabled components will be copied with disable date as null
2957: WHEN bill_or_eco = 1
2958: AND (
2959: ( SELECT fmirb.effectivity_date
2960: FROM mtl_item_revisions_b fmirb
2961: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2962: ) < trgt_comps_eff_date
2963: )
2964: THEN TO_DATE (NULL)

Line 2969: FROM mtl_item_revisions_b fmirb

2965: -- Future disabled components should be disabled as per the disable date of component
2966: ELSE
2967: (
2968: SELECT fmirb.effectivity_date
2969: FROM mtl_item_revisions_b fmirb
2970: WHERE fmirb.revision_id = bic.to_end_item_rev_id
2971: )
2972: END AS disable_date,
2973: decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,

Line 3024: FROM mtl_item_revisions_b fmirb,

3020: 0 AS from_end_item_minor_rev_id,
3021: 0 AS to_end_item_minor_rev_id,
3022: (
3023: SELECT tmirb.revision_id
3024: FROM mtl_item_revisions_b fmirb,
3025: mtl_item_revisions_b tmirb
3026: WHERE tmirb.inventory_item_id = bic.component_item_id
3027: AND tmirb.organization_id = to_org_id
3028: AND tmirb.revision = fmirb.revision

Line 3025: mtl_item_revisions_b tmirb

3021: 0 AS to_end_item_minor_rev_id,
3022: (
3023: SELECT tmirb.revision_id
3024: FROM mtl_item_revisions_b fmirb,
3025: mtl_item_revisions_b tmirb
3026: WHERE tmirb.inventory_item_id = bic.component_item_id
3027: AND tmirb.organization_id = to_org_id
3028: AND tmirb.revision = fmirb.revision
3029: AND fmirb.revision_id = bic.component_item_revision_id

Line 3390: FROM MTL_ITEM_REVISIONS_B tmirb

3386: -- which are with smaller revision effective will be effective from eco_end_item_rev_id
3387: AND EXISTS
3388: (
3389: SELECT tmirb.REVISION
3390: FROM MTL_ITEM_REVISIONS_B tmirb
3391: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3392: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3393: )
3394: AND EXISTS

Line 3397: FROM MTL_ITEM_REVISIONS_B tmirb

3393: )
3394: AND EXISTS
3395: (
3396: SELECT tmirb.REVISION
3397: FROM MTL_ITEM_REVISIONS_B tmirb
3398: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3399: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, rev_date)
3400: )
3401: THEN trgt_comps_end_item_rev_id

Line 3410: FROM MTL_ITEM_REVISIONS_B tmirb

3406: AND bic.effectivity_date = rev_date
3407: AND EXISTS
3408: (
3409: SELECT tmirb.REVISION
3410: FROM MTL_ITEM_REVISIONS_B tmirb
3411: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3412: AND tmirb.revision < get_current_item_rev(from_item_id, from_org_id, rev_date)
3413: )
3414: THEN trgt_comps_end_item_rev_id

Line 3420: FROM MTL_ITEM_REVISIONS_B tmirb

3416: WHEN bill_or_eco = 1
3417: AND EXISTS
3418: (
3419: SELECT tmirb.REVISION
3420: FROM MTL_ITEM_REVISIONS_B tmirb
3421: WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
3422: AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3423: )
3424: THEN trgt_comps_end_item_rev_id

Line 3428: FROM mtl_item_revisions_b tmirb

3424: THEN trgt_comps_end_item_rev_id
3425: ELSE
3426: (
3427: SELECT tmirb.revision_id
3428: FROM mtl_item_revisions_b tmirb
3429: WHERE tmirb.inventory_item_id = to_item_id
3430: AND tmirb.organization_id = to_org_id
3431: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
3432: )

Line 3442: FROM mtl_item_revisions_b tmirb

3438: AND bic.disable_date IS NOT NULL
3439: AND EXISTS
3440: (
3441: SELECT tmirb.revision_id
3442: FROM mtl_item_revisions_b tmirb
3443: WHERE tmirb.inventory_item_id = to_item_id
3444: AND tmirb.organization_id = to_org_id
3445: AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3446: )

Line 3450: FROM MTL_ITEM_REVISIONS_B mirb

3446: )
3447: AND EXISTS
3448: (
3449: SELECT mirb.REVISION
3450: FROM MTL_ITEM_REVISIONS_B mirb
3451: WHERE mirb.REVISION_ID = trgt_comps_end_item_rev_id
3452: AND mirb.revision > get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3453: )
3454: THEN to_number(NULL)

Line 3460: FROM mtl_item_revisions_b tmirb

3456: bic.disable_date IS NOT NULL
3457: THEN
3458: (
3459: SELECT tmirb.revision_id
3460: FROM mtl_item_revisions_b tmirb
3461: WHERE tmirb.inventory_item_id = to_item_id
3462: AND tmirb.organization_id = to_org_id
3463: AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
3464: )

Line 3474: FROM mtl_item_revisions_b fmirb,

3470: 0 AS from_end_item_minor_rev_id,
3471: 0 AS to_end_item_minor_rev_id,
3472: (
3473: SELECT tmirb.revision_id
3474: FROM mtl_item_revisions_b fmirb,
3475: mtl_item_revisions_b tmirb
3476: WHERE tmirb.inventory_item_id = bic.component_item_id
3477: AND tmirb.organization_id = to_org_id
3478: AND tmirb.revision = fmirb.revision

Line 3475: mtl_item_revisions_b tmirb

3471: 0 AS to_end_item_minor_rev_id,
3472: (
3473: SELECT tmirb.revision_id
3474: FROM mtl_item_revisions_b fmirb,
3475: mtl_item_revisions_b tmirb
3476: WHERE tmirb.inventory_item_id = bic.component_item_id
3477: AND tmirb.organization_id = to_org_id
3478: AND tmirb.revision = fmirb.revision
3479: AND fmirb.revision_id = bic.component_item_revision_id

Line 4646: FROM mtl_item_revisions_b fmirb,

4642: -- we need not copy.
4643: )
4644: AND NOT EXISTS (
4645: SELECT tmirb.revision_id
4646: FROM mtl_item_revisions_b fmirb,
4647: mtl_item_revisions_b tmirb
4648: WHERE tmirb.inventory_item_id = bcb.component_item_id
4649: AND tmirb.organization_id = to_org_id
4650: AND tmirb.revision = fmirb.revision

Line 4647: mtl_item_revisions_b tmirb

4643: )
4644: AND NOT EXISTS (
4645: SELECT tmirb.revision_id
4646: FROM mtl_item_revisions_b fmirb,
4647: mtl_item_revisions_b tmirb
4648: WHERE tmirb.inventory_item_id = bcb.component_item_id
4649: AND tmirb.organization_id = to_org_id
4650: AND tmirb.revision = fmirb.revision
4651: AND fmirb.revision_id =

Line 6880: FROM mtl_item_revisions_b mir

6876: AND NVL(bsb.alternate_bom_designator,bom_globals.get_primary_ui) = NVL(p_structure_name,bom_globals.get_primary_ui)
6877: ),2) AS assembly_type,
6878: (SELECT revision
6879: FROM (SELECT revision
6880: FROM mtl_item_revisions_b mir
6881: WHERE inventory_item_id = p_item_id
6882: AND organization_id = x_org_list_tbl(l_index)
6883: AND mir.effectivity_date <= p_effectivity_date
6884: ORDER BY effectivity_date DESC, revision DESC)

Line 6888: FROM mtl_item_revisions_b mir

6884: ORDER BY effectivity_date DESC, revision DESC)
6885: WHERE rownum < 2) AS current_item_rev,
6886: (SELECT revision_id
6887: FROM (SELECT revision_id
6888: FROM mtl_item_revisions_b mir
6889: WHERE inventory_item_id = p_item_id
6890: AND organization_id = x_org_list_tbl(l_index)
6891: AND mir.effectivity_date <= p_effectivity_date
6892: ORDER BY effectivity_date DESC, revision DESC)

Line 6896: FROM mtl_item_revisions_b mir

6892: ORDER BY effectivity_date DESC, revision DESC)
6893: WHERE rownum < 2) AS current_item_rev_id,
6894: (SELECT revision_label
6895: FROM (SELECT revision_label
6896: FROM mtl_item_revisions_b mir
6897: WHERE inventory_item_id = p_item_id
6898: AND organization_id = x_org_list_tbl(l_index)
6899: AND mir.effectivity_date <= p_effectivity_date
6900: ORDER BY effectivity_date DESC, revision DESC)

Line 7312: l_fixed_rev mtl_item_revisions_b.revision%TYPE;

7308: l_comp_ctr NUMBER;
7309: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7310: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7311: l_no_access_comp_cnt NUMBER;
7312: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7313: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7314: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7315: l_from_comps num_varray := num_varray();
7316: l_to_comps num_varray := num_varray();

Line 7313: l_current_item_rev mtl_item_revisions_b.revision%TYPE;

7309: l_from_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7310: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7311: l_no_access_comp_cnt NUMBER;
7312: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7313: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7314: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7315: l_from_comps num_varray := num_varray();
7316: l_to_comps num_varray := num_varray();
7317: l_last_copied_comp_seq_id NUMBER := -1;

Line 7314: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;

7310: l_to_eff_ctrl bom_structures_b.effectivity_control%TYPE;
7311: l_no_access_comp_cnt NUMBER;
7312: l_fixed_rev mtl_item_revisions_b.revision%TYPE;
7313: l_current_item_rev mtl_item_revisions_b.revision%TYPE;
7314: l_current_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
7315: l_from_comps num_varray := num_varray();
7316: l_to_comps num_varray := num_varray();
7317: l_last_copied_comp_seq_id NUMBER := -1;
7318: l_default_wip_params NUMBER;

Line 7406: FROM mtl_item_revisions_b mir

7402: )
7403: IS
7404: SELECT revision_id,
7405: revision
7406: FROM mtl_item_revisions_b mir
7407: WHERE mir.inventory_item_id = p_item_id
7408: AND mir.organization_id = p_org_id
7409: AND mir.effectivity_date <= p_rev_date
7410: AND ROWNUM < 2

Line 7477: FROM mtl_item_revisions_b

7473: p_revision IN VARCHAR2
7474: )
7475: IS
7476: SELECT effectivity_date
7477: FROM mtl_item_revisions_b
7478: WHERE inventory_item_id = p_inventory_item_id
7479: AND organization_id = p_organization_id
7480: AND revision = p_revision;
7481:

Line 7987: FROM mtl_item_revisions_b fmirb,

7983: 0 AS from_end_item_minor_rev_id,
7984: 0 AS to_end_item_minor_rev_id,
7985: (
7986: SELECT tmirb.revision_id
7987: FROM mtl_item_revisions_b fmirb,
7988: mtl_item_revisions_b tmirb
7989: WHERE tmirb.inventory_item_id = bic.component_item_id
7990: AND tmirb.organization_id = to_org_id
7991: AND tmirb.revision = fmirb.revision

Line 7988: mtl_item_revisions_b tmirb

7984: 0 AS to_end_item_minor_rev_id,
7985: (
7986: SELECT tmirb.revision_id
7987: FROM mtl_item_revisions_b fmirb,
7988: mtl_item_revisions_b tmirb
7989: WHERE tmirb.inventory_item_id = bic.component_item_id
7990: AND tmirb.organization_id = to_org_id
7991: AND tmirb.revision = fmirb.revision
7992: AND fmirb.revision_id = bic.component_item_revision_id

Line 8356: FROM mtl_item_revisions_b fmirb,

8352: 0 AS from_end_item_minor_rev_id,
8353: 0 AS to_end_item_minor_rev_id,
8354: (
8355: SELECT tmirb.revision_id
8356: FROM mtl_item_revisions_b fmirb,
8357: mtl_item_revisions_b tmirb
8358: WHERE tmirb.inventory_item_id = bic.component_item_id
8359: AND tmirb.organization_id = to_org_id
8360: AND tmirb.revision = fmirb.revision

Line 8357: mtl_item_revisions_b tmirb

8353: 0 AS to_end_item_minor_rev_id,
8354: (
8355: SELECT tmirb.revision_id
8356: FROM mtl_item_revisions_b fmirb,
8357: mtl_item_revisions_b tmirb
8358: WHERE tmirb.inventory_item_id = bic.component_item_id
8359: AND tmirb.organization_id = to_org_id
8360: AND tmirb.revision = fmirb.revision
8361: AND fmirb.revision_id = bic.component_item_revision_id

Line 8721: FROM mtl_item_revisions_b fmirb,

8717: AND (
8718: EXISTS
8719: (
8720: SELECT tmirb.revision_id
8721: FROM mtl_item_revisions_b fmirb,
8722: mtl_item_revisions_b tmirb
8723: WHERE tmirb.inventory_item_id = to_item_id
8724: AND tmirb.organization_id = to_org_id
8725: AND tmirb.revision = fmirb.revision

Line 8722: mtl_item_revisions_b tmirb

8718: EXISTS
8719: (
8720: SELECT tmirb.revision_id
8721: FROM mtl_item_revisions_b fmirb,
8722: mtl_item_revisions_b tmirb
8723: WHERE tmirb.inventory_item_id = to_item_id
8724: AND tmirb.organization_id = to_org_id
8725: AND tmirb.revision = fmirb.revision
8726: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 8731: FROM MTL_ITEM_REVISIONS_B

8727: )
8728: AND
8729: (
8730: SELECT REVISION
8731: FROM MTL_ITEM_REVISIONS_B
8732: WHERE REVISION_ID = bic.to_end_item_rev_id
8733: ) >
8734: (
8735: SELECT REVISION

Line 8736: FROM MTL_ITEM_REVISIONS_B

8732: WHERE REVISION_ID = bic.to_end_item_rev_id
8733: ) >
8734: (
8735: SELECT REVISION
8736: FROM MTL_ITEM_REVISIONS_B
8737: WHERE REVISION_ID = x_end_item_rev_id
8738: )
8739: )
8740: THEN

Line 8743: FROM mtl_item_revisions_b fmirb,

8739: )
8740: THEN
8741: (
8742: SELECT tmirb.revision_id
8743: FROM mtl_item_revisions_b fmirb,
8744: mtl_item_revisions_b tmirb
8745: WHERE tmirb.inventory_item_id = to_item_id
8746: AND tmirb.organization_id = to_org_id
8747: AND tmirb.revision = fmirb.revision

Line 8744: mtl_item_revisions_b tmirb

8740: THEN
8741: (
8742: SELECT tmirb.revision_id
8743: FROM mtl_item_revisions_b fmirb,
8744: mtl_item_revisions_b tmirb
8745: WHERE tmirb.inventory_item_id = to_item_id
8746: AND tmirb.organization_id = to_org_id
8747: AND tmirb.revision = fmirb.revision
8748: AND fmirb.revision_id = bic.to_end_item_rev_id

Line 8758: FROM mtl_item_revisions_b fmirb,

8754: 0 AS from_end_item_minor_rev_id,
8755: 0 AS to_end_item_minor_rev_id,
8756: (
8757: SELECT tmirb.revision_id
8758: FROM mtl_item_revisions_b fmirb,
8759: mtl_item_revisions_b tmirb
8760: WHERE tmirb.inventory_item_id = bic.component_item_id
8761: AND tmirb.organization_id = to_org_id
8762: AND tmirb.revision = fmirb.revision

Line 8759: mtl_item_revisions_b tmirb

8755: 0 AS to_end_item_minor_rev_id,
8756: (
8757: SELECT tmirb.revision_id
8758: FROM mtl_item_revisions_b fmirb,
8759: mtl_item_revisions_b tmirb
8760: WHERE tmirb.inventory_item_id = bic.component_item_id
8761: AND tmirb.organization_id = to_org_id
8762: AND tmirb.revision = fmirb.revision
8763: AND fmirb.revision_id = bic.component_item_revision_id

Line 8849: FROM mtl_item_revisions_b mirb

8845: -- Component Action is exclude or enable
8846: -- we need not copy.
8847: )
8848: AND ( p_cpy_past_eff_comps = 'Y' AND ( ( SELECT mirb.revision
8849: FROM mtl_item_revisions_b mirb
8850: WHERE mirb.revision_id = bev.from_end_item_rev_id
8851: )
8852: <=
8853: (

Line 8855: FROM mtl_item_revisions_b mirb

8851: )
8852: <=
8853: (
8854: SELECT mirb.revision
8855: FROM mtl_item_revisions_b mirb
8856: WHERE mirb.revision_id = p_end_item_rev_id
8857: )
8858: ) -- For first revised item we can have past eff comps as eff on the target date
8859: OR ( p_cpy_past_eff_comps = 'N' AND bev.from_end_item_rev_id = p_end_item_rev_id )

Line 9084: FROM mtl_item_revisions_b fmirb

9080: WHEN bic.to_end_item_rev_id IS NOT NULL
9081: AND (
9082: (
9083: SELECT fmirb.effectivity_date
9084: FROM mtl_item_revisions_b fmirb
9085: WHERE fmirb.revision_id = bic.to_end_item_rev_id
9086: ) > x_effectivity_date
9087: )
9088: THEN (

Line 9090: FROM mtl_item_revisions_b fmirb

9086: ) > x_effectivity_date
9087: )
9088: THEN (
9089: SELECT fmirb.effectivity_date
9090: FROM mtl_item_revisions_b fmirb
9091: WHERE fmirb.revision_id = bic.to_end_item_rev_id
9092: )
9093: -- Past disabled components will be copied with disable date as null
9094: WHEN (

Line 9096: FROM mtl_item_revisions_b fmirb

9092: )
9093: -- Past disabled components will be copied with disable date as null
9094: WHEN (
9095: ( SELECT fmirb.effectivity_date
9096: FROM mtl_item_revisions_b fmirb
9097: WHERE fmirb.revision_id = bic.to_end_item_rev_id
9098: ) < x_effectivity_date
9099: )
9100: THEN TO_DATE (NULL)

Line 9157: FROM mtl_item_revisions_b fmirb,

9153: 0 AS from_end_item_minor_rev_id,
9154: 0 AS to_end_item_minor_rev_id,
9155: (
9156: SELECT tmirb.revision_id
9157: FROM mtl_item_revisions_b fmirb,
9158: mtl_item_revisions_b tmirb
9159: WHERE tmirb.inventory_item_id = bic.component_item_id
9160: AND tmirb.organization_id = to_org_id
9161: AND tmirb.revision = fmirb.revision

Line 9158: mtl_item_revisions_b tmirb

9154: 0 AS to_end_item_minor_rev_id,
9155: (
9156: SELECT tmirb.revision_id
9157: FROM mtl_item_revisions_b fmirb,
9158: mtl_item_revisions_b tmirb
9159: WHERE tmirb.inventory_item_id = bic.component_item_id
9160: AND tmirb.organization_id = to_org_id
9161: AND tmirb.revision = fmirb.revision
9162: AND fmirb.revision_id = bic.component_item_revision_id

Line 9248: FROM mtl_item_revisions_b mirb

9244: -- Component Action is exclude or enable
9245: -- we need not copy.
9246: )
9247: AND ( ( p_cpy_past_eff_comps = 'Y' AND ( SELECT mirb.effectivity_date
9248: FROM mtl_item_revisions_b mirb
9249: WHERE mirb.revision_id = bev.from_end_item_rev_id
9250: ) <= ( SELECT mirb.effectivity_date
9251: FROM mtl_item_revisions_b mirb
9252: WHERE mirb.revision_id = p_end_item_rev_id

Line 9251: FROM mtl_item_revisions_b mirb

9247: AND ( ( p_cpy_past_eff_comps = 'Y' AND ( SELECT mirb.effectivity_date
9248: FROM mtl_item_revisions_b mirb
9249: WHERE mirb.revision_id = bev.from_end_item_rev_id
9250: ) <= ( SELECT mirb.effectivity_date
9251: FROM mtl_item_revisions_b mirb
9252: WHERE mirb.revision_id = p_end_item_rev_id
9253: )) -- For first revised item we can have past eff comps as eff on the target date
9254: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9255: FROM mtl_item_revisions_b mirb

Line 9255: FROM mtl_item_revisions_b mirb

9251: FROM mtl_item_revisions_b mirb
9252: WHERE mirb.revision_id = p_end_item_rev_id
9253: )) -- For first revised item we can have past eff comps as eff on the target date
9254: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9255: FROM mtl_item_revisions_b mirb
9256: WHERE mirb.revision_id = bev.from_end_item_rev_id
9257: ) = ( SELECT mirb.effectivity_date
9258: FROM mtl_item_revisions_b mirb
9259: WHERE mirb.revision_id = p_end_item_rev_id

Line 9258: FROM mtl_item_revisions_b mirb

9254: OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
9255: FROM mtl_item_revisions_b mirb
9256: WHERE mirb.revision_id = bev.from_end_item_rev_id
9257: ) = ( SELECT mirb.effectivity_date
9258: FROM mtl_item_revisions_b mirb
9259: WHERE mirb.revision_id = p_end_item_rev_id
9260: ) )
9261: )
9262: AND EXISTS

Line 9529: FROM mtl_item_revisions_b tmirb

9525: WHEN bic.disable_date IS NOT NULL
9526: AND EXISTS
9527: (
9528: SELECT tmirb.revision_id
9529: FROM mtl_item_revisions_b tmirb
9530: WHERE tmirb.inventory_item_id = to_item_id
9531: AND tmirb.organization_id = to_org_id
9532: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9533: )

Line 9537: FROM MTL_ITEM_REVISIONS_B mirb

9533: )
9534: AND EXISTS
9535: (
9536: SELECT mirb.REVISION
9537: FROM MTL_ITEM_REVISIONS_B mirb
9538: WHERE mirb.REVISION_ID = eco_end_item_rev_id
9539: AND mirb.revision < get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9540: )
9541: THEN

Line 9544: FROM mtl_item_revisions_b tmirb

9540: )
9541: THEN
9542: (
9543: SELECT tmirb.revision_id
9544: FROM mtl_item_revisions_b tmirb
9545: WHERE tmirb.inventory_item_id = to_item_id
9546: AND tmirb.organization_id = to_org_id
9547: AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
9548: )

Line 9557: FROM mtl_item_revisions_b fmirb,

9553: 0 AS from_end_item_minor_rev_id,
9554: 0 AS to_end_item_minor_rev_id,
9555: (
9556: SELECT tmirb.revision_id
9557: FROM mtl_item_revisions_b fmirb,
9558: mtl_item_revisions_b tmirb
9559: WHERE tmirb.inventory_item_id = bic.component_item_id
9560: AND tmirb.organization_id = to_org_id
9561: AND tmirb.revision = fmirb.revision

Line 9558: mtl_item_revisions_b tmirb

9554: 0 AS to_end_item_minor_rev_id,
9555: (
9556: SELECT tmirb.revision_id
9557: FROM mtl_item_revisions_b fmirb,
9558: mtl_item_revisions_b tmirb
9559: WHERE tmirb.inventory_item_id = bic.component_item_id
9560: AND tmirb.organization_id = to_org_id
9561: AND tmirb.revision = fmirb.revision
9562: AND fmirb.revision_id = bic.component_item_revision_id

Line 10676: FROM mtl_item_revisions_b fmirb,

10672: -- we need not copy.
10673: )
10674: AND NOT EXISTS (
10675: SELECT tmirb.revision_id
10676: FROM mtl_item_revisions_b fmirb,
10677: mtl_item_revisions_b tmirb
10678: WHERE tmirb.inventory_item_id = bcb.component_item_id
10679: AND tmirb.organization_id = to_org_id
10680: AND tmirb.revision = fmirb.revision

Line 10677: mtl_item_revisions_b tmirb

10673: )
10674: AND NOT EXISTS (
10675: SELECT tmirb.revision_id
10676: FROM mtl_item_revisions_b fmirb,
10677: mtl_item_revisions_b tmirb
10678: WHERE tmirb.inventory_item_id = bcb.component_item_id
10679: AND tmirb.organization_id = to_org_id
10680: AND tmirb.revision = fmirb.revision
10681: AND fmirb.revision_id =