[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert_Row ( p_location_code => ''
, p_business_group_name => ''
, p_status => l_status
, p_error_msg => l_error_msg
, p_rec_type => l_rec_type
, p_entity_type => ''
, p_copy_cnt => null
, p_modify_cnt => null
, p_exp_copy_cnt => null
, p_exp_modify_cnt => null
, p_entity_name => ''
, p_entity_inconsistency => ''
, p_put_orgs => true
);
SELECT loc.location_code, hou1.name, loc.location_id
INTO l_location_code, l_business_group_name, l_location_id
FROM hr_locations loc
, hr_all_organization_units hou
, hr_all_organization_units hou1
WHERE loc.location_id = hou.location_id
AND hou1.organization_id = hou.business_group_id
AND hou.organization_id = g_model_org_id;
SELECT loc.location_code, hou1.name, loc.location_id
INTO l_location_code, l_business_group_name, l_location_id
FROM hr_locations loc
, hr_all_organization_units hou
, hr_all_organization_units hou1
WHERE loc.location_id = hou.location_id
AND hou1.organization_id = hou.business_group_id
AND hou.organization_id = g_organization_id;
SELECT creation_date
INTO l_creation_date
FROM hr_locations locs
WHERE locs.location_id = l_location_id;
Insert_Row ( p_location_code => l_location_code
, p_business_group_name => l_business_group_name
, p_status => l_status
, p_error_msg => l_message
, p_rec_type => l_rec_type
, p_entity_type => ''
, p_copy_cnt => null
, p_modify_cnt => null
, p_exp_copy_cnt => null
, p_exp_modify_cnt => null
, p_entity_name => ''
, p_entity_inconsistency => ''
, p_put_orgs => false
);
SELECT COUNT(organization_id)
FROM bom_parameters
WHERE ORGANIZATION_ID = p_org_id;
SELECT COUNT(organization_id)
FROM rcv_parameters
WHERE ORGANIZATION_ID = p_org_id;
SELECT COUNT(organization_id)
FROM wip_parameters
WHERE ORGANIZATION_ID = p_org_id;
SELECT COUNT(organization_id)
FROM wsh_shipping_parameters
WHERE ORGANIZATION_ID = p_org_id;
SELECT COUNT(organization_id)
FROM mrp_parameters
WHERE ORGANIZATION_ID = p_org_id;
SELECT DISTINCT hier.NAME
FROM PER_ORG_STRUCTURE_ELEMENTS mdl
, PER_ORG_STRUCTURE_VERSIONS ver
, PER_ORGANIZATION_STRUCTURES hier
WHERE ORGANIZATION_ID_CHILD = p_model_org_id
AND ver.ORG_STRUCTURE_VERSION_ID = mdl.ORG_STRUCTURE_VERSION_ID
AND hier.ORGANIZATION_STRUCTURE_ID = ver.ORGANIZATION_STRUCTURE_ID
AND NOT EXISTS
( SELECT 'x'
FROM PER_ORG_STRUCTURE_ELEMENTS new
WHERE ORGANIZATION_ID_CHILD = p_organization_id
AND new.ORG_STRUCTURE_VERSION_ID = mdl.ORG_STRUCTURE_VERSION_ID
);
SELECT COUNT(pos.ORG_STRUCTURE_ELEMENT_ID)
FROM PER_ORG_STRUCTURE_ELEMENTS pos
WHERE pos.ORGANIZATION_ID_CHILD = p_org_id;
SELECT msi.SECONDARY_INVENTORY_NAME
FROM MTL_SECONDARY_INVENTORIES msi
WHERE msi.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_SECONDARY_INVENTORIES msi2
WHERE msi2.ORGANIZATION_ID = p_organization_id
AND msi.SECONDARY_INVENTORY_NAME = msi2.SECONDARY_INVENTORY_NAME
);
SELECT COUNT(msi.SECONDARY_INVENTORY_NAME)
FROM MTL_SECONDARY_INVENTORIES msi
WHERE msi.ORGANIZATION_ID = p_org_id;
SELECT br1.RESOURCE_CODE rescode
FROM BOM_RESOURCES br1
WHERE br1.ORGANIZATION_ID = g_model_org_id
AND br1.COST_ELEMENT_ID IN (3,4) /* vmutyala added this condition to distinguish resource overheads and resources in the report */
AND NOT EXISTS ( SELECT 'x'
FROM BOM_RESOURCES br2
WHERE br2.ORGANIZATION_ID = g_organization_id
AND br2.RESOURCE_CODE = br1.RESOURCE_CODE
AND br2.COST_ELEMENT_ID IN (3,4) /* vmutyala added this condition to distinguish
resource overheads and resources in the report */
);
SELECT COUNT(br.RESOURCE_CODE)
FROM BOM_RESOURCES br
WHERE br.ORGANIZATION_ID = p_org_id
AND br.COST_ELEMENT_ID IN (3,4); /* vmutyala added this condition to distinguish resource overheads
SELECT bd1.DEPARTMENT_CODE
FROM BOM_DEPARTMENTS bd1
WHERE bd1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS ( SELECT 'x'
FROM BOM_DEPARTMENTS bd2
WHERE bd2.ORGANIZATION_ID=p_organization_id
AND bd2.DEPARTMENT_CODE=bd1.DEPARTMENT_CODE
);
SELECT COUNT(bd.DEPARTMENT_CODE)
FROM BOM_DEPARTMENTS bd
WHERE bd.ORGANIZATION_ID = p_org_id;
SELECT bdc1.DEPARTMENT_CLASS_CODE
FROM BOM_DEPARTMENT_CLASSES bdc1
WHERE bdc1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM BOM_DEPARTMENT_CLASSES bdc2
WHERE bdc2.ORGANIZATION_ID = p_organization_id
AND bdc2.DEPARTMENT_CLASS_CODE = bdc1.DEPARTMENT_CLASS_CODE
);
SELECT COUNT(ent.DEPARTMENT_CLASS_CODE)
FROM BOM_DEPARTMENT_CLASSES ent
WHERE ent.ORGANIZATION_ID = p_org_id;
SELECT bad1.ALTERNATE_DESIGNATOR_CODE
FROM BOM_ALTERNATE_DESIGNATORS bad1
WHERE bad1.ORGANIZATION_ID=g_model_org_id
AND NOT EXISTS
( SELECT bad2.ALTERNATE_DESIGNATOR_CODE
FROM BOM_ALTERNATE_DESIGNATORS bad2
WHERE bad2.ORGANIZATION_ID=g_organization_id
AND bad2.ALTERNATE_DESIGNATOR_CODE=bad1.ALTERNATE_DESIGNATOR_CODE
);
SELECT COUNT(bad.ALTERNATE_DESIGNATOR_CODE)
FROM BOM_ALTERNATE_DESIGNATORS bad
WHERE bad.ORGANIZATION_ID = p_org_id;
SELECT mp.ORGANIZATION_CODE
FROM MTL_INTERORG_PARAMETERS mip1
, MTL_PARAMETERS mp
WHERE mip1.FROM_ORGANIZATION_ID = p_model_org_id
AND mp.ORGANIZATION_ID = mip1.TO_ORGANIZATION_ID
AND NOT EXISTS
( SELECT 'x'
FROM MTL_INTERORG_PARAMETERS mip2
WHERE mip2.FROM_ORGANIZATION_ID = p_organization_id
AND mip2.TO_ORGANIZATION_ID = mip1.TO_ORGANIZATION_ID
);
SELECT mp.ORGANIZATION_CODE
FROM MTL_INTERORG_PARAMETERS mip1
, MTL_PARAMETERS mp
WHERE mip1.TO_ORGANIZATION_ID = p_model_org_id
AND mp.ORGANIZATION_ID = mip1.FROM_ORGANIZATION_ID
AND NOT EXISTS
( SELECT 'x'
FROM MTL_INTERORG_PARAMETERS mip2
WHERE mip2.TO_ORGANIZATION_ID = p_organization_id
AND mip2.FROM_ORGANIZATION_ID = mip1.FROM_ORGANIZATION_ID
);
SELECT COUNT(mip.TO_ORGANIZATION_ID)
FROM MTL_INTERORG_PARAMETERS mip
WHERE mip.FROM_ORGANIZATION_ID = p_org_id;
SELECT COUNT(mip.FROM_ORGANIZATION_ID)
FROM MTL_INTERORG_PARAMETERS mip
WHERE mip.TO_ORGANIZATION_ID = p_org_id;
SELECT msi.CONCATENATED_SEGMENTS
FROM BOM_BILL_OF_MATERIALS bom
, MTL_SYSTEM_ITEMS_KFV msi
WHERE msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
AND bom.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM BOM_BILL_OF_MATERIALS bom1
WHERE bom1.ORGANIZATION_ID = p_organization_id
AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
/*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
);
SELECT COUNT(ASSEMBLY_ITEM_ID)
FROM BOM_STRUCTURES_B --myerrams, Bug: 4913484. Replaced the view BOM_BILL_OF_MATERIALS with BOM_STRUCTURES_B table
WHERE ORGANIZATION_ID = p_org_id;
SELECT kfv.CONCATENATED_SEGMENTS, COUNT(bic.COMPONENT_SEQUENCE_ID)
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, MTL_SYSTEM_ITEMS_KFV kfv
WHERE bom.ORGANIZATION_ID = p_model_org_id
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND kfv.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
AND kfv.ORGANIZATION_ID = bom.ORGANIZATION_ID
AND NOT EXISTS
(
SELECT 'x'
FROM BOM_INVENTORY_COMPONENTS bic1
, BOM_BILL_OF_MATERIALS bom1
WHERE bom1.ORGANIZATION_ID = p_organization_id
AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
/*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
)
GROUP BY kfv.CONCATENATED_SEGMENTS;
SELECT COUNT(bic.COMPONENT_SEQUENCE_ID)
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
WHERE bom.ORGANIZATION_ID = p_org_id
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID;
SELECT msi.CONCATENATED_SEGMENTS, COUNT(bsc.COMPONENT_SEQUENCE_ID)
FROM BOM_SUBSTITUTE_COMPONENTS bsc
, BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, MTL_SYSTEM_ITEMS_KFV msi
WHERE bom.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND bsc.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
AND NOT EXISTS
(
SELECT 'x'
FROM BOM_INVENTORY_COMPONENTS bic1
, BOM_BILL_OF_MATERIALS bom1
, BOM_SUBSTITUTE_COMPONENTS bsc1
WHERE bom1.ORGANIZATION_ID = p_organization_id
AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
AND bsc1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
/*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
AND nvl(bsc.ACD_TYPE,1) = nvl(bsc1.ACD_TYPE,1)
AND bsc.SUBSTITUTE_COMPONENT_ID = bsc1.SUBSTITUTE_COMPONENT_ID
)
GROUP BY MSI.CONCATENATED_SEGMENTS;
SELECT COUNT(bsc.SUBSTITUTE_COMPONENT_ID)
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, BOM_SUBSTITUTE_COMPONENTS bsc
WHERE bom.ORGANIZATION_ID = p_org_id
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND bsc.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
SELECT msi.CONCATENATED_SEGMENTS, COUNT(brd.COMPONENT_SEQUENCE_ID)
FROM BOM_REFERENCE_DESIGNATORS brd
, BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, MTL_SYSTEM_ITEMS_KFV msi
WHERE bom.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND brd.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
AND NOT EXISTS
(
SELECT 'x'
FROM BOM_INVENTORY_COMPONENTS bic1
, BOM_BILL_OF_MATERIALS bom1
, BOM_REFERENCE_DESIGNATORS brd1
WHERE bom1.ORGANIZATION_ID = p_organization_id
AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
AND brd1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
/*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
AND brd.COMPONENT_REFERENCE_DESIGNATOR = brd1.COMPONENT_REFERENCE_DESIGNATOR
AND nvl(brd.ACD_TYPE,1) = nvl(brd1.ACD_TYPE,1)
)
GROUP BY MSI.CONCATENATED_SEGMENTS;
SELECT COUNT(brd.COMPONENT_REFERENCE_DESIGNATOR)
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, BOM_REFERENCE_DESIGNATORS brd
WHERE bom.ORGANIZATION_ID = p_org_id
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND brd.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
SELECT msi.CONCATENATED_SEGMENTS, COUNT(bco.COMPONENT_SEQUENCE_ID)
FROM BOM_COMPONENT_OPERATIONS bco
, BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, MTL_SYSTEM_ITEMS_KFV msi
WHERE bom.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND bco.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
AND NOT EXISTS
(
SELECT 'x'
FROM BOM_INVENTORY_COMPONENTS bic1
, BOM_BILL_OF_MATERIALS bom1
, BOM_COMPONENT_OPERATIONS bco1
WHERE bom1.ORGANIZATION_ID = p_organization_id
AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
AND bco1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
/*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
)
GROUP BY MSI.CONCATENATED_SEGMENTS;
SELECT COUNT(bco.COMP_OPERATION_SEQ_ID)
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_BILL_OF_MATERIALS bom
, BOM_COMPONENT_OPERATIONS bco
WHERE bom.ORGANIZATION_ID = p_org_id
AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
AND bco.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
SELECT ent.ORG_INFORMATION_CONTEXT
FROM HR_ORGANIZATION_INFORMATION ent
WHERE ent.ORGANIZATION_ID = p_model_org_id
AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
AND ORG_INFORMATION1 = 'INV'
)
OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
AND ORG_INFORMATION_CONTEXT IN
( SELECT ORG_INFORMATION_TYPE
FROM HR_ORG_INFO_TYPES_BY_CLASS
WHERE ORG_CLASSIFICATION = 'INV'
)
)
)
MINUS
SELECT ent.ORG_INFORMATION_CONTEXT
FROM HR_ORGANIZATION_INFORMATION ent
WHERE ent.ORGANIZATION_ID = p_organization_id
AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
AND ORG_INFORMATION1 = 'INV'
)
OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
AND ORG_INFORMATION_CONTEXT IN
( SELECT ORG_INFORMATION_TYPE
FROM HR_ORG_INFO_TYPES_BY_CLASS
WHERE ORG_CLASSIFICATION = 'INV'
)
)
);
SELECT COUNT(ent.ORG_INFORMATION_CONTEXT)
FROM HR_ORGANIZATION_INFORMATION ent
WHERE ent.ORGANIZATION_ID = p_org_id
AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
AND ORG_INFORMATION1 = 'INV'
)
OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
AND ORG_INFORMATION_CONTEXT IN
( SELECT ORG_INFORMATION_TYPE
FROM HR_ORG_INFO_TYPES_BY_CLASS
WHERE ORG_CLASSIFICATION = 'INV'
)
)
);
SELECT kfv.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_B msi1
, MTL_SYSTEM_ITEMS_KFV kfv
WHERE kfv.ORGANIZATION_ID = msi1.ORGANIZATION_ID
AND kfv.INVENTORY_ITEM_ID = msi1.INVENTORY_ITEM_ID
AND msi1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_SYSTEM_ITEMS_B msi2
WHERE msi2.INVENTORY_ITEM_ID = msi1.INVENTORY_ITEM_ID
AND msi2.ORGANIZATION_ID = p_organization_id
);
SELECT COUNT(msi.INVENTORY_ITEM_ID)
FROM MTL_SYSTEM_ITEMS_B msi
WHERE msi.ORGANIZATION_ID = p_org_id;
SELECT distinct kfv.CONCATENATED_SEGMENTS
FROM MTL_ITEM_CATEGORIES mic1
, MTL_CATEGORIES_KFV kfv
WHERE kfv.CATEGORY_ID = mic1.CATEGORY_ID
AND mic1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_ITEM_CATEGORIES mic2
WHERE mic2.CATEGORY_ID = mic1.CATEGORY_ID
AND mic2.ORGANIZATION_ID = p_organization_id
);
SELECT COUNT(DISTINCT kfv.CONCATENATED_SEGMENTS)
FROM MTL_ITEM_CATEGORIES mic1
, MTL_CATEGORIES_KFV kfv
WHERE kfv.CATEGORY_ID = mic1.CATEGORY_ID
AND mic1.ORGANIZATION_ID = p_org_id;
SELECT kfv.CONCATENATED_SEGMENTS||': '||rev1.REVISION
FROM MTL_ITEM_REVISIONS rev1
, MTL_SYSTEM_ITEMS_KFV kfv
WHERE kfv.ORGANIZATION_ID = rev1.ORGANIZATION_ID
AND kfv.INVENTORY_ITEM_ID = rev1.INVENTORY_ITEM_ID
AND rev1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_ITEM_REVISIONS rev2
WHERE rev2.REVISION = rev1.REVISION
AND rev2.INVENTORY_ITEM_ID = rev1.INVENTORY_ITEM_ID
AND rev2.ORGANIZATION_ID = p_organization_id
);
SELECT COUNT(rev.REVISION_ID)
FROM MTL_ITEM_REVISIONS_B rev
WHERE rev.ORGANIZATION_ID=p_org_id;
SELECT kfv.CONCATENATED_SEGMENTS||': '||sub1.SECONDARY_INVENTORY
FROM MTL_ITEM_SUB_INVENTORIES sub1
, MTL_SYSTEM_ITEMS_KFV kfv
WHERE kfv.ORGANIZATION_ID = sub1.ORGANIZATION_ID
AND kfv.INVENTORY_ITEM_ID = sub1.INVENTORY_ITEM_ID
AND sub1.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_ITEM_SUB_INVENTORIES sub2
WHERE sub2.SECONDARY_INVENTORY = sub1.SECONDARY_INVENTORY
AND sub2.INVENTORY_ITEM_ID = sub1.INVENTORY_ITEM_ID
AND sub2.ORGANIZATION_ID = p_organization_id
);
SELECT COUNT(sub.SECONDARY_INVENTORY)
FROM MTL_ITEM_SUB_INVENTORIES sub
WHERE sub.ORGANIZATION_ID=p_org_id;
SELECT kfv1.CONCATENATED_SEGMENTS
FROM MTL_ITEM_LOCATIONS loc1
, MTL_ITEM_LOCATIONS_KFV kfv1
WHERE kfv1.ORGANIZATION_ID=loc1.ORGANIZATION_ID
AND kfv1.INVENTORY_LOCATION_ID=loc1.INVENTORY_LOCATION_ID
AND loc1.ORGANIZATION_ID=p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_ITEM_LOCATIONS loc2
, MTL_ITEM_LOCATIONS_KFV kfv2
WHERE loc2.ORGANIZATION_ID=p_organization_id
AND kfv2.INVENTORY_LOCATION_ID=loc2.INVENTORY_LOCATION_ID
AND kfv2.ORGANIZATION_ID=loc2.ORGANIZATION_ID
AND kfv2.CONCATENATED_SEGMENTS=kfv1.CONCATENATED_SEGMENTS
);
SELECT COUNT(loc.INVENTORY_LOCATION_ID)
FROM MTL_ITEM_LOCATIONS loc
WHERE loc.ORGANIZATION_ID=p_org_id;
SELECT DISTINCT stdops.OPERATION_CODE
FROM BOM_STANDARD_OPERATIONS stdops
WHERE stdops.ORGANIZATION_ID = p_model_org_id
AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
AND NOT EXISTS
( SELECT 'x'
FROM BOM_STANDARD_OPERATIONS stdops1
WHERE stdops1.ORGANIZATION_ID = p_organization_id
AND stdops1.OPERATION_CODE = stdops.OPERATION_CODE
);
SELECT COUNT(stdops.STANDARD_OPERATION_ID)
FROM BOM_STANDARD_OPERATIONS stdops
WHERE stdops.ORGANIZATION_ID = p_org_id
AND stdops.Line_Id is null and stdops.Operation_type = 1; --myerrams, testing
SELECT stdops.OPERATION_CODE
, COUNT(stdopres.RESOURCE_ID)
FROM BOM_STD_OP_RESOURCES stdopres
, BOM_STANDARD_OPERATIONS stdops
, BOM_RESOURCES res
WHERE stdopres.STANDARD_OPERATION_ID = stdops.STANDARD_OPERATION_ID
AND stdops.ORGANIZATION_ID = p_model_org_id
AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
AND res.RESOURCE_ID = stdopres.RESOURCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_STD_OP_RESOURCES stdopres1
, BOM_STANDARD_OPERATIONS stdops1
, BOM_RESOURCES res1
WHERE stdops1.ORGANIZATION_ID = p_organization_id
AND stdopres1.STANDARD_OPERATION_ID = stdops1.STANDARD_OPERATION_ID
AND stdopres1.RESOURCE_SEQ_NUM = stdopres.RESOURCE_SEQ_NUM
AND res1.RESOURCE_ID = stdopres1.RESOURCE_ID
AND res1.RESOURCE_CODE = res.RESOURCE_CODE
AND stdops1.OPERATION_CODE = stdops.OPERATION_CODE --myerrams, testing
)
GROUP BY stdops.OPERATION_CODE;
SELECT COUNT(stdopres.RESOURCE_ID)
FROM BOM_STD_OP_RESOURCES stdopres
, BOM_STANDARD_OPERATIONS stdops
WHERE stdopres.STANDARD_OPERATION_ID = stdops.STANDARD_OPERATION_ID
AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
AND stdops.ORGANIZATION_ID = p_org_id;
SELECT stdops.OPERATION_CODE
, COUNT(stdsubopres.SUBSTITUTE_GROUP_NUM)
FROM BOM_STD_SUB_OP_RESOURCES stdsubopres
, BOM_STD_OP_RESOURCES stdopres
, BOM_STANDARD_OPERATIONS stdops
, BOM_RESOURCES res
WHERE stdops.ORGANIZATION_ID = p_model_org_id
AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
AND stdopres.SUBSTITUTE_GROUP_NUM = stdsubopres.SUBSTITUTE_GROUP_NUM
AND stdops.STANDARD_OPERATION_ID = stdopres.STANDARD_OPERATION_ID
AND stdops.STANDARD_OPERATION_ID = stdsubopres.STANDARD_OPERATION_ID
AND res.RESOURCE_ID = stdsubopres.RESOURCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_STD_SUB_OP_RESOURCES stdsubopres1
, BOM_STD_OP_RESOURCES stdopres1
, BOM_STANDARD_OPERATIONS stdops1
, BOM_RESOURCES res1
WHERE stdops1.STANDARD_OPERATION_ID = stdsubopres1.STANDARD_OPERATION_ID
AND stdops1.STANDARD_OPERATION_ID = stdopres1.STANDARD_OPERATION_ID
AND stdops1.ORGANIZATION_ID = p_organization_id
AND res1.RESOURCE_ID = stdsubopres1.RESOURCE_ID
AND res1.RESOURCE_CODE = res.RESOURCE_CODE
AND stdopres1.SUBSTITUTE_GROUP_NUM = stdsubopres1.SUBSTITUTE_GROUP_NUM
AND stdsubopres.SUBSTITUTE_GROUP_NUM = stdsubopres1.SUBSTITUTE_GROUP_NUM
AND stdsubopres.REPLACEMENT_GROUP_NUM = stdsubopres1.REPLACEMENT_GROUP_NUM
)
GROUP BY stdops.OPERATION_CODE;
SELECT COUNT(stdsubopres.SUBSTITUTE_GROUP_NUM)
FROM BOM_STD_SUB_OP_RESOURCES stdsubopres
, BOM_STD_OP_RESOURCES stdopres
, BOM_STANDARD_OPERATIONS stdops
WHERE stdops.ORGANIZATION_ID = p_org_id
AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
AND stdopres.SUBSTITUTE_GROUP_NUM = stdsubopres.SUBSTITUTE_GROUP_NUM
AND stdops.STANDARD_OPERATION_ID = stdopres.STANDARD_OPERATION_ID
AND stdops.STANDARD_OPERATION_ID = stdsubopres.STANDARD_OPERATION_ID;
SELECT DISTINCT msi.CONCATENATED_SEGMENTS, boru.ALTERNATE_ROUTING_DESIGNATOR
FROM BOM_OPERATIONAL_ROUTINGS boru
, MTL_SYSTEM_ITEMS_KFV msi
WHERE msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND boru.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM BOM_OPERATIONAL_ROUTINGS boru1
WHERE boru1.ORGANIZATION_ID = p_organization_id
AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
);
SELECT COUNT(boru.ASSEMBLY_ITEM_ID)
FROM BOM_OPERATIONAL_ROUTINGS boru
WHERE boru.ORGANIZATION_ID = p_org_id;
SELECT MSI.CONCATENATED_SEGMENTS
, BOR.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(OPERATION_SEQ_NUM)
FROM BOM_OPERATION_SEQUENCES BOS
, BOM_OPERATIONAL_ROUTINGS BOR
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
AND BOR.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATIONAL_ROUTINGS bor1
WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
AND bor1.ORGANIZATION_ID = p_organization_id
AND bos1.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, BOR.ALTERNATE_ROUTING_DESIGNATOR;
SELECT MSI.CONCATENATED_SEGMENTS
, BOR.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(OPERATION_SEQ_NUM)
FROM BOM_OPERATION_SEQUENCES BOS
, BOM_OPERATIONAL_ROUTINGS BOR
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
AND BOR.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATIONAL_ROUTINGS bor1
WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
AND bor1.ORGANIZATION_ID = p_organization_id
AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
AND bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
AND nvl(bor1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
= nvl(bor.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, BOR.ALTERNATE_ROUTING_DESIGNATOR;
SELECT COUNT(bos.OPERATION_SEQUENCE_ID)
FROM BOM_OPERATIONAL_ROUTINGS boru
, BOM_OPERATION_SEQUENCES bos
WHERE bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND boru.ORGANIZATION_ID = p_org_id;
SELECT msi.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(bor.RESOURCE_ID)
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS boru
, MTL_SYSTEM_ITEMS_KFV msi
WHERE boru.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_OPERATIONAL_ROUTINGS boru1
, BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATION_RESOURCES bor1
WHERE boru1.ORGANIZATION_ID = p_organization_id
AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
AND bor1.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
AND bor1.ACD_TYPE = bor.ACD_TYPE
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR;
SELECT msi.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(bor.RESOURCE_ID)
FROM BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS boru
, MTL_SYSTEM_ITEMS_KFV msi
WHERE boru.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_OPERATIONAL_ROUTINGS boru1
, BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATION_RESOURCES bor1
WHERE boru1.ORGANIZATION_ID = p_organization_id
AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
AND nvl(bor1.ACD_TYPE, 1) = nvl(bor.ACD_TYPE, 1)
AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR;
SELECT COUNT(bor.RESOURCE_SEQ_NUM)
FROM BOM_OPERATIONAL_ROUTINGS boru
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATION_RESOURCES bor
WHERE bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND boru.ORGANIZATION_ID = p_org_id;
SELECT msi.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(bsor.SUBSTITUTE_GROUP_NUM)
FROM BOM_SUB_OPERATION_RESOURCES bsor
, BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS boru
, MTL_SYSTEM_ITEMS_KFV msi
WHERE boru.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND bsor.RESOURCE_ID = bor.RESOURCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_SUB_OPERATION_RESOURCES bsor1
, BOM_OPERATION_RESOURCES bor1
, BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATIONAL_ROUTINGS boru1
WHERE boru1.ORGANIZATION_ID = p_organization_id
AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
AND bsor1.OPERATION_SEQUENCE_ID = bor1.OPERATION_SEQUENCE_ID
AND bsor1.RESOURCE_ID = bor1.RESOURCE_ID
AND bsor1.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
AND bsor1.RESOURCE_ID = bsor.RESOURCE_ID
AND bsor1.SUBSTITUTE_GROUP_NUM = bsor.SUBSTITUTE_GROUP_NUM
AND bsor1.REPLACEMENT_GROUP_NUM = bsor.REPLACEMENT_GROUP_NUM
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR;
SELECT msi.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR
, COUNT(bsor.SUBSTITUTE_GROUP_NUM)
FROM BOM_SUB_OPERATION_RESOURCES bsor
, BOM_OPERATION_RESOURCES bor
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS boru
, MTL_SYSTEM_ITEMS_KFV msi
WHERE boru.ORGANIZATION_ID = p_model_org_id
AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND bsor.RESOURCE_ID = bor.RESOURCE_ID
AND NOT EXISTS
( SELECT 'x'
FROM BOM_SUB_OPERATION_RESOURCES bsor1
, BOM_OPERATION_RESOURCES bor1
, BOM_OPERATION_SEQUENCES bos1
, BOM_OPERATIONAL_ROUTINGS boru1
WHERE boru1.ORGANIZATION_ID = p_organization_id
AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
AND bsor1.OPERATION_SEQUENCE_ID = bor1.OPERATION_SEQUENCE_ID
AND bsor1.RESOURCE_ID = bor1.RESOURCE_ID
-- AND bsor1.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
-- AND bsor1.RESOURCE_ID = bsor.RESOURCE_ID
AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
AND nvl(bor1.ACD_TYPE, 1) = nvl(bor.ACD_TYPE, 1)
AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
AND bsor1.SUBSTITUTE_GROUP_NUM = bsor.SUBSTITUTE_GROUP_NUM
AND bsor1.REPLACEMENT_GROUP_NUM = bsor.REPLACEMENT_GROUP_NUM
)
GROUP BY MSI.CONCATENATED_SEGMENTS
, boru.ALTERNATE_ROUTING_DESIGNATOR;
SELECT COUNT(bsor.SUBSTITUTE_GROUP_NUM)
FROM BOM_OPERATIONAL_ROUTINGS boru
, BOM_OPERATION_SEQUENCES bos
, BOM_OPERATION_RESOURCES bor
, BOM_SUB_OPERATION_RESOURCES bsor
WHERE bsor.RESOURCE_ID = bor.RESOURCE_ID
AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
AND boru.ORGANIZATION_ID = p_org_id;
SELECT kfv.CONCATENATED_SEGMENTS
FROM MTL_RTG_ITEM_REVISIONS rev
, MTL_SYSTEM_ITEMS_KFV kfv
WHERE kfv.ORGANIZATION_ID = rev.ORGANIZATION_ID
AND kfv.INVENTORY_ITEM_ID = rev.INVENTORY_ITEM_ID
AND rev.ORGANIZATION_ID = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM MTL_RTG_ITEM_REVISIONS rev1
WHERE rev1.ORGANIZATION_ID = p_organization_id
AND rev1.INVENTORY_ITEM_ID = rev.INVENTORY_ITEM_ID
AND rev1.PROCESS_REVISION = rev.PROCESS_REVISION
);
SELECT COUNT(rev.PROCESS_REVISION)
FROM MTL_RTG_ITEM_REVISIONS rev
WHERE rev.ORGANIZATION_ID = p_org_id;
SELECT bd.DEPARTMENT_CODE, br.RESOURCE_CODE
FROM BOM_DEPARTMENT_RESOURCES ent
, BOM_DEPARTMENTS bd
, BOM_RESOURCES br
WHERE br.RESOURCE_ID = ent.RESOURCE_ID
AND ent.RESOURCE_ID IN ( SELECT br.RESOURCE_ID
FROM BOM_RESOURCES br
WHERE br.ORGANIZATION_ID=p_model_org_id
)
AND bd.DEPARTMENT_ID = ent.DEPARTMENT_ID
AND ent.DEPARTMENT_ID IN ( SELECT bd.DEPARTMENT_ID
FROM BOM_DEPARTMENTS bd
WHERE bd.ORGANIZATION_ID=p_model_org_id
)
MINUS
SELECT bd.DEPARTMENT_CODE, br.RESOURCE_CODE
FROM BOM_DEPARTMENT_RESOURCES ent
, BOM_DEPARTMENTS bd
, BOM_RESOURCES br
WHERE br.RESOURCE_ID = ent.RESOURCE_ID
AND ent.RESOURCE_ID IN ( SELECT br.RESOURCE_ID
FROM BOM_RESOURCES br
WHERE br.ORGANIZATION_ID=p_organization_id
)
AND bd.DEPARTMENT_ID = ent.DEPARTMENT_ID
AND ent.DEPARTMENT_ID IN ( SELECT bd.DEPARTMENT_ID
FROM BOM_DEPARTMENTS bd
WHERE bd.ORGANIZATION_ID=p_organization_id
);
SELECT COUNT(ent.RESOURCE_ID)
FROM BOM_DEPARTMENT_RESOURCES ent
WHERE ent.RESOURCE_ID IN
(
SELECT br.RESOURCE_ID
FROM BOM_RESOURCES br
WHERE br.ORGANIZATION_ID=p_org_id
)
AND ent.DEPARTMENT_ID IN
(
SELECT bd.DEPARTMENT_ID
FROM BOM_DEPARTMENTS bd
WHERE bd.ORGANIZATION_ID=p_org_id
);
SELECT br1.RESOURCE_CODE, cct1.COST_TYPE
FROM CST_RESOURCE_COSTS crc1
, CST_COST_TYPES cct1
, BOM_RESOURCES br1
WHERE crc1.ORGANIZATION_ID = p_model_org_id
AND br1.RESOURCE_ID = crc1.RESOURCE_ID
AND cct1.COST_TYPE_ID = crc1.COST_TYPE_ID
AND NOT EXISTS ( SELECT 'x'
FROM CST_RESOURCE_COSTS crc2
, CST_COST_TYPES cct2
, BOM_RESOURCES br2
WHERE crc2.ORGANIZATION_ID = p_organization_id
AND br2.RESOURCE_ID = crc2.RESOURCE_ID
AND br2.RESOURCE_CODE = br1.RESOURCE_CODE
AND cct2.COST_TYPE_ID = crc2.COST_TYPE_ID
AND cct2.COST_TYPE = cct1.COST_TYPE
);
SELECT COUNT(crc.RESOURCE_ID)
FROM CST_RESOURCE_COSTS crc
WHERE crc.ORGANIZATION_ID = p_org_id;
SELECT br1.RESOURCE_CODE
, cct1.COST_TYPE
, br2.RESOURCE_CODE
FROM CST_RESOURCE_OVERHEADS crh1
, CST_COST_TYPES cct1
, BOM_RESOURCES br1
, BOM_RESOURCES br2
WHERE crh1.ORGANIZATION_ID = p_model_org_id
AND br1.RESOURCE_ID = crh1.RESOURCE_ID
AND br2.RESOURCE_ID = crh1.OVERHEAD_ID
AND cct1.COST_TYPE_ID = crh1.COST_TYPE_ID
AND NOT EXISTS ( SELECT 'x'
FROM CST_RESOURCE_OVERHEADS crh2
, CST_COST_TYPES cct2
, BOM_RESOURCES br3
WHERE crh2.ORGANIZATION_ID = p_organization_id
AND br3.RESOURCE_ID = crh2.RESOURCE_ID
AND br3.RESOURCE_CODE = br1.RESOURCE_CODE
AND cct2.COST_TYPE_ID = crh2.COST_TYPE_ID
AND cct2.COST_TYPE = cct1.COST_TYPE
);
SELECT COUNT(crh.RESOURCE_ID)
FROM CST_RESOURCE_OVERHEADS crh
WHERE crh.ORGANIZATION_ID = p_org_id;
Insert_Row ( p_location_code => ''
, p_business_group_name => ''
, p_status => ''
, p_error_msg => l_message
, p_rec_type => 'ENTITY_TYPE_SUMMARY'
, p_entity_type => g_entity_type
, p_copy_cnt => g_copy_cnt
, p_exp_copy_cnt => g_exp_copy_cnt
, p_modify_cnt => g_modify_cnt
, p_exp_modify_cnt => g_exp_modify_cnt
, p_entity_name => ''
, p_entity_inconsistency => ''
, p_put_orgs => true
);
Insert_Row ( p_location_code => ''
, p_business_group_name => ''
, p_status => ''
, p_error_msg => l_message
, p_rec_type => 'INCONSISTENT_ENTITY_SUMMARY'
, p_entity_type => g_entity_type
, p_copy_cnt => null
, p_exp_copy_cnt => null
, p_modify_cnt => null
, p_exp_modify_cnt => null
, p_entity_name => g_entity_names(G_THRESHOLD)
, p_entity_inconsistency => l_entity_err
, p_put_orgs => true
);
INSERT INTO mtl_copy_org_report
( GROUP_CODE
, MODEL_ORGANIZATION_CODE
, ORGANIZATION_CODE
, ERROR_MSG
, REC_TYPE
, ENTITY_TYPE
, ENTITY_NAME
, ENTITY_INCONSISTENCY
)
VALUES
( g_group_code
, g_model_org_code
, g_organization_code
, l_message
, 'INCONSISTENT_ENTITY_SUMMARY'
, g_entity_type
, ' - '||g_entity_names(i)
, l_entity_err
);
PROCEDURE Insert_Row
( p_location_code IN VARCHAR2
, p_business_group_name IN VARCHAR2
, p_status IN VARCHAR2
, p_error_msg IN VARCHAR2
, p_rec_type IN VARCHAR2
, p_entity_type IN VARCHAR2
, p_copy_cnt IN NUMBER
, p_modify_cnt IN NUMBER
, p_exp_copy_cnt IN NUMBER
, p_exp_modify_cnt IN NUMBER
, p_entity_name IN VARCHAR2
, p_entity_inconsistency IN VARCHAR2
, p_put_orgs IN BOOLEAN
)
IS
--l_message VARCHAR2(1000);
l_api_name VARCHAR2(100):=' Insert_Row ';
, '> Insert_Row '
);
INSERT INTO MTL_COPY_ORG_REPORT
( group_code
, model_organization_code
, organization_code
, location_code
, business_group_name
, status
, error_msg
, rec_type
, entity_type
, copied_count
, modified_count
, expected_copied_count
, expected_modified_count
, entity_name
, entity_inconsistency
)
VALUES
( g_group_code
, l_model_org
, l_new_org
, p_location_code
, p_business_group_name
, p_status
, p_error_msg
, p_rec_type
, p_entity_type
, p_copy_cnt
, p_modify_cnt
, p_exp_copy_cnt
, p_exp_modify_cnt
, p_entity_name
, p_entity_inconsistency
);
, '< Insert_Row '
);
END Insert_Row;
SELECT organization_id
INTO l_org_id
FROM mtl_parameters
WHERE organization_code = p_org_code;
SELECT FND_MESSAGE.GET()
INTO l_message
FROM dual;
DELETE mtl_copy_org_report
WHERE group_code = g_group_code;
SELECT 1
INTO l_receiving_subinv_count
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = p_organization_id
AND SUBINVENTORY_TYPE = 2
AND ROWNUM = 1;
SELECT wac1.class_code
FROM wip_accounting_classes wac1
WHERE wac1.organization_id = p_model_org_id
AND NOT EXISTS
( SELECT 'x'
FROM wip_accounting_classes wac2
WHERE wac2.organization_id = p_organization_id
AND wac1.class_code = wac2.class_code
);
SELECT COUNT(wac.class_code)
FROM wip_accounting_classes wac
WHERE wac.organization_id = p_org_id;