[Home] [Help]
3048: IS
3049: SELECT MSI.CONCATENATED_SEGMENTS
3050: , BOR.ALTERNATE_ROUTING_DESIGNATOR
3051: , COUNT(OPERATION_SEQ_NUM)
3052: FROM BOM_OPERATION_SEQUENCES BOS
3053: , BOM_OPERATIONAL_ROUTINGS BOR
3054: , MTL_SYSTEM_ITEMS_KFV MSI
3055: WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
3056: AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
3057: AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
3058: AND BOR.ORGANIZATION_ID = p_model_org_id
3059: AND NOT EXISTS
3060: ( SELECT 'x'
3061: FROM BOM_OPERATION_SEQUENCES bos1
3062: , BOM_OPERATIONAL_ROUTINGS bor1
3063: WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
3064: AND bor1.ORGANIZATION_ID = p_organization_id
3065: AND bos1.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3074: IS
3075: SELECT MSI.CONCATENATED_SEGMENTS
3076: , BOR.ALTERNATE_ROUTING_DESIGNATOR
3077: , COUNT(OPERATION_SEQ_NUM)
3078: FROM BOM_OPERATION_SEQUENCES BOS
3079: , BOM_OPERATIONAL_ROUTINGS BOR
3080: , MTL_SYSTEM_ITEMS_KFV MSI
3081: WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
3082: AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
3083: AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
3084: AND BOR.ORGANIZATION_ID = p_model_org_id
3085: AND NOT EXISTS
3086: ( SELECT 'x'
3087: FROM BOM_OPERATION_SEQUENCES bos1
3088: , BOM_OPERATIONAL_ROUTINGS bor1
3089: WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
3090: AND bor1.ORGANIZATION_ID = p_organization_id
3091: AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
3101: CURSOR l_child_1_cnt ( p_org_id NUMBER )
3102: IS
3103: SELECT COUNT(bos.OPERATION_SEQUENCE_ID)
3104: FROM BOM_OPERATIONAL_ROUTINGS boru
3105: , BOM_OPERATION_SEQUENCES bos
3106: WHERE bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3107: AND boru.ORGANIZATION_ID = p_org_id;
3108:
3109: /* vmutyala modified the l_child_2 cursor because bor1.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3115: SELECT msi.CONCATENATED_SEGMENTS
3116: , boru.ALTERNATE_ROUTING_DESIGNATOR
3117: , COUNT(bor.RESOURCE_ID)
3118: FROM BOM_OPERATION_RESOURCES bor
3119: , BOM_OPERATION_SEQUENCES bos
3120: , BOM_OPERATIONAL_ROUTINGS boru
3121: , MTL_SYSTEM_ITEMS_KFV msi
3122: WHERE boru.ORGANIZATION_ID = p_model_org_id
3123: AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3126: AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3127: AND NOT EXISTS
3128: ( SELECT 'x'
3129: FROM BOM_OPERATIONAL_ROUTINGS boru1
3130: , BOM_OPERATION_SEQUENCES bos1
3131: , BOM_OPERATION_RESOURCES bor1
3132: WHERE boru1.ORGANIZATION_ID = p_organization_id
3133: AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3134: AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3146: SELECT msi.CONCATENATED_SEGMENTS
3147: , boru.ALTERNATE_ROUTING_DESIGNATOR
3148: , COUNT(bor.RESOURCE_ID)
3149: FROM BOM_OPERATION_RESOURCES bor
3150: , BOM_OPERATION_SEQUENCES bos
3151: , BOM_OPERATIONAL_ROUTINGS boru
3152: , MTL_SYSTEM_ITEMS_KFV msi
3153: WHERE boru.ORGANIZATION_ID = p_model_org_id
3154: AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3157: AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3158: AND NOT EXISTS
3159: ( SELECT 'x'
3160: FROM BOM_OPERATIONAL_ROUTINGS boru1
3161: , BOM_OPERATION_SEQUENCES bos1
3162: , BOM_OPERATION_RESOURCES bor1
3163: WHERE boru1.ORGANIZATION_ID = p_organization_id
3164: AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3165: AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3176: CURSOR l_child_2_cnt ( p_org_id NUMBER )
3177: IS
3178: SELECT COUNT(bor.RESOURCE_SEQ_NUM)
3179: FROM BOM_OPERATIONAL_ROUTINGS boru
3180: , BOM_OPERATION_SEQUENCES bos
3181: , BOM_OPERATION_RESOURCES bor
3182: WHERE bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3183: AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3184: AND boru.ORGANIZATION_ID = p_org_id;
3193: , boru.ALTERNATE_ROUTING_DESIGNATOR
3194: , COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3195: FROM BOM_SUB_OPERATION_RESOURCES bsor
3196: , BOM_OPERATION_RESOURCES bor
3197: , BOM_OPERATION_SEQUENCES bos
3198: , BOM_OPERATIONAL_ROUTINGS boru
3199: , MTL_SYSTEM_ITEMS_KFV msi
3200: WHERE boru.ORGANIZATION_ID = p_model_org_id
3201: AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3207: AND NOT EXISTS
3208: ( SELECT 'x'
3209: FROM BOM_SUB_OPERATION_RESOURCES bsor1
3210: , BOM_OPERATION_RESOURCES bor1
3211: , BOM_OPERATION_SEQUENCES bos1
3212: , BOM_OPERATIONAL_ROUTINGS boru1
3213: WHERE boru1.ORGANIZATION_ID = p_organization_id
3214: AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3215: AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3232: , boru.ALTERNATE_ROUTING_DESIGNATOR
3233: , COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3234: FROM BOM_SUB_OPERATION_RESOURCES bsor
3235: , BOM_OPERATION_RESOURCES bor
3236: , BOM_OPERATION_SEQUENCES bos
3237: , BOM_OPERATIONAL_ROUTINGS boru
3238: , MTL_SYSTEM_ITEMS_KFV msi
3239: WHERE boru.ORGANIZATION_ID = p_model_org_id
3240: AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3246: AND NOT EXISTS
3247: ( SELECT 'x'
3248: FROM BOM_SUB_OPERATION_RESOURCES bsor1
3249: , BOM_OPERATION_RESOURCES bor1
3250: , BOM_OPERATION_SEQUENCES bos1
3251: , BOM_OPERATIONAL_ROUTINGS boru1
3252: WHERE boru1.ORGANIZATION_ID = p_organization_id
3253: AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3254: AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3272: CURSOR l_child_3_cnt ( p_org_id NUMBER )
3273: IS
3274: SELECT COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3275: FROM BOM_OPERATIONAL_ROUTINGS boru
3276: , BOM_OPERATION_SEQUENCES bos
3277: , BOM_OPERATION_RESOURCES bor
3278: , BOM_SUB_OPERATION_RESOURCES bsor
3279: WHERE bsor.RESOURCE_ID = bor.RESOURCE_ID
3280: AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID