BOM Delete Items

select
        bdg.DELETE_GROUP_NAME           group_name1,
        bdg.DESCRIPTION                 description1,
        ml1.meaning                     delete_type,
        ml2.meaning                     action_type,
        to_char(bdg.DATE_LAST_SUBMITTED,'DD-MON-RR  HH24:MI')         date_sub,
        ml3.MEANING                     entity,
        bdse.COMPONENT_CONCAT_SEGMENTS  Component,
        bdse.DESCRIPTION                description4,
        bdse.OPERATION_SEQ_NUM          op,
        bdse.ITEM_NUM                   seq,
        bde.ITEM_CONCAT_SEGMENTS        bill,
        bde.ALTERNATE_DESIGNATOR        alternate,
        to_char(bdse.EFFECTIVITY_DATE,'DD-MON-RR HH24:MI')           effectivity,
        to_char(bdse.DISABLE_DATE,'DD-MON-RR HH24:MI')               disable,
        ml4.MEANING                     status,
        bde.DELETE_DATE                 del_dat,
        bdse.DELETE_ENTITY_SEQUENCE_ID,
        bdse.COMPONENT_SEQUENCE_ID,
        ood.organization_code  org_code_comp
from BOM_DELETE_GROUPS               bdg,
        BOM_DELETE_ENTITIES             bde,
        BOM_DELETE_SUB_ENTITIES         bdse,
        MFG_LOOKUPS                     ml1,
        MFG_LOOKUPS                     ml2,
        MFG_LOOKUPS                     ml3,
        MFG_LOOKUPS                     ml4,
        ORG_ORGANIZATION_DEFINITIONS ood
where   ml1.LOOKUP_CODE = bdg.DELETE_TYPE
and     ml1.LOOKUP_TYPE = 'BOM_DELETE_TYPE'
and     ml2.LOOKUP_CODE = bdg.ACTION_TYPE
and     ml2.LOOKUP_TYPE = 'BOM_DELETE_ACTION_TYPE'
and     ml3.LOOKUP_TYPE = 'BOM_DELETE_ENTITY_TYPE'
and     ml3.LOOKUP_CODE = DECODE(bdse.COMPONENT_SEQUENCE_ID, '', 5,4)
and     'BOM_DELETE_STATUS_TYPE'  = ml4.lookup_type
and      bdse.DELETE_STATUS_TYPE = ml4.lookup_code
and     bdse.DELETE_STATUS_TYPE in (1,2,3,4)
and     bdse.DELETE_ENTITY_SEQUENCE_ID = bde.DELETE_ENTITY_SEQUENCE_ID
and     bde.DELETE_GROUP_SEQUENCE_ID = bdg.DELETE_GROUP_SEQUENCE_ID
and     bdg.ORGANIZATION_ID = :P_ORGANIZATION_ID
and    ood.organization_id = bdg.organization_id
&C_where_type
&C_where_delete_type
&C_where_group
&C_where_entity_status
&C_where_sub_entity_status
&C_eng_flag_where
and     bdg.DELETE_TYPE = 4
ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,19 ASC , bdg.DELETE_GROUP_NAME , bdse.COMPONENT_CONCAT_SEGMENTS , bde.ITEM_CONCAT_SEGMENTS , bde.ALTERNATE_DESIGNATOR , bdse.OPERATION_SEQ_NUM