APPS.EAM_WO_NETWORK_VALIDATE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 81

    SELECT  WSR.SCHED_RELATIONSHIP_ID,
            WE1.WIP_ENTITY_NAME             AS PARENT_JOB,
            WE2.WIP_ENTITY_NAME             AS CHILD_JOB,
            WDJ1.SCHEDULED_START_DATE       AS PARENT_START_DATE,
            WDJ1.SCHEDULED_COMPLETION_DATE  AS PARENT_COMPLETION_DATE,
            WDJ2.SCHEDULED_START_DATE       AS CHILD_START_DATE,
            WDJ2.SCHEDULED_COMPLETION_DATE  AS CHILD_COMPLETION_DATE
    FROM    WIP_SCHED_RELATIONSHIPS WSR,
            WIP_ENTITIES WE1,
            WIP_ENTITIES WE2,
            WIP_DISCRETE_JOBS WDJ1,
            WIP_DISCRETE_JOBS WDJ2
    WHERE   WSR.RELATIONSHIP_STATUS = 3
    AND     WSR.RELATIONSHIP_TYPE = l_relationship_type
    AND     WSR.PARENT_OBJECT_TYPE_ID = 1
    AND     WSR.CHILD_OBJECT_TYPE_ID = 1
    AND     WE1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
    AND     WE2.WIP_ENTITY_ID   = WSR.CHILD_OBJECT_ID
    AND     WDJ1.WIP_ENTITY_ID = WE1.WIP_ENTITY_ID
    AND     WDJ2.WIP_ENTITY_ID = WE2.WIP_ENTITY_ID
    AND     WSR.TOP_LEVEL_OBJECT_ID = topLvlObj
    AND     WSR.TOP_LEVEL_OBJECT_TYPE_ID = topLvlObjType
    AND	    WDJ2.STATUS_TYPE = nvl(l_status_check,WDJ2.STATUS_TYPE);

Line: 138

        SELECT  WSR.TOP_LEVEL_OBJECT_ID,
                WSR.TOP_LEVEL_OBJECT_TYPE_ID
        INTO    l_top_level_object_id,
                l_top_level_object_type_id
        FROM    WIP_SCHED_RELATIONSHIPS WSR
        WHERE   WSR.CHILD_OBJECT_ID         = l_work_object_id
        AND     WSR.CHILD_OBJECT_TYPE_ID    = l_work_object_type_id
        AND     WSR.RELATIONSHIP_TYPE       = 1;

Line: 160

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 1
    WHERE   WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     WSR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id;

Line: 165

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 1
    WHERE   EWR.TOP_LEVEL_OBJECT_TYPE_ID  = l_top_level_object_type_id
    AND     EWR.TOP_LEVEL_OBJECT_ID       = l_top_level_object_id;

Line: 177

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 3
    WHERE   WSR.SCHED_RELATIONSHIP_ID IN
                (
                SELECT  WSR1.SCHED_RELATIONSHIP_ID
                FROM    WIP_SCHED_RELATIONSHIPS  WSR1,
                        WIP_DISCRETE_JOBS       WDJ1,
                        WIP_DISCRETE_JOBS       WDJ2
                WHERE   WSR1.PARENT_OBJECT_TYPE_ID  = 1
                AND     WSR1.CHILD_OBJECT_TYPE_ID   = 1
                AND     WDJ1.WIP_ENTITY_ID          = WSR1.PARENT_OBJECT_ID
                AND     WDJ2.WIP_ENTITY_ID          = WSR1.CHILD_OBJECT_ID
                AND     WDJ1.SCHEDULED_COMPLETION_DATE   > WDJ2.SCHEDULED_START_DATE
                AND     WSR1.TOP_LEVEL_OBJECT_ID        = l_top_level_object_id
                AND     WSR1.TOP_LEVEL_OBJECT_TYPE_ID   = l_top_level_object_type_id
                AND     WSR1.RELATIONSHIP_TYPE      = 2
                );

Line: 195

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 3
    WHERE   EWR.WO_RELATIONSHIP_ID IN
                (
                SELECT  EWR1.WO_RELATIONSHIP_ID
                FROM    EAM_WO_RELATIONSHIPS  EWR1,
                        WIP_DISCRETE_JOBS       WDJ1,
                        WIP_DISCRETE_JOBS       WDJ2
                WHERE   EWR1.PARENT_OBJECT_TYPE_ID  = 1
                AND     EWR1.CHILD_OBJECT_TYPE_ID   = 1
                AND     WDJ1.WIP_ENTITY_ID          = EWR1.PARENT_OBJECT_ID
                AND     WDJ2.WIP_ENTITY_ID          = EWR1.CHILD_OBJECT_ID
                AND     WDJ1.SCHEDULED_COMPLETION_DATE   > WDJ2.SCHEDULED_START_DATE
                AND     EWR1.TOP_LEVEL_OBJECT_ID        = l_top_level_object_id
                AND     EWR1.TOP_LEVEL_OBJECT_TYPE_ID   = l_top_level_object_type_id
                AND     EWR1.PARENT_RELATIONSHIP_TYPE      = 2
                );

Line: 226

    SELECT  COUNT(WSR.SCHED_RELATIONSHIP_ID)
    INTO    l_released_rowcount
    FROM    WIP_SCHED_RELATIONSHIPS  WSR,
            WIP_DISCRETE_JOBS       WDJ
    WHERE   WSR.PARENT_OBJECT_TYPE_ID  = 1
    AND     WSR.CHILD_OBJECT_TYPE_ID   = 1
    AND     WDJ.WIP_ENTITY_ID          = WSR.CHILD_OBJECT_ID
    AND     WDJ.STATUS_TYPE            IN (3,4,5,6,7,12,14,15)
    AND     WSR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id
    AND     WSR.TOP_LEVEL_OBJECT_TYPE_ID    = l_top_level_object_type_id
    AND     WSR.RELATIONSHIP_TYPE           = 2
    AND     WSR.RELATIONSHIP_STATUS         = 3;

Line: 358

    delete from wip_scheduling_exceptions
      where exception_type = 2
      and sched_relationship_id in
        (select sched_relationship_id from
         wip_sched_relationships
         start with parent_object_id = l_top_level_object_id
         connect by parent_object_id = prior child_object_id);

Line: 387

            INSERT INTO WIP_SCHEDULING_EXCEPTIONS
            (
            wip_entity_id,
            organization_id,
            mesg_sequence,
            scheduling_source,
            scheduling_source_id,
            message_text,
            message_type,
            marked_flag,
            reported_date,
            last_update_date,
            creation_date,
            created_by,
            last_update_login,
            last_updated_by,
            operation_seq_num,
            resource_seq_num,
            resource_id,
            inventory_item_id,
            instance_id,
            serial_number,
            sched_relationship_id,
            exception_type
            )
            VALUES
            (
            NULL, --wip_entity_id,
            NULL, --organization_id,
            1, --mesg_sequence,
            NULL, --scheduling_source,
            NULL,--scheduling_source_id,
            l_exception_msg, --message_text,
            NULL, --message_type,
            NULL, --marked_flag,
            SYSDATE, --reported_date,
            SYSDATE, --last_update_date,
            SYSDATE, --creation_date,
            -1, --created_by,
            -1, --last_update_login,
            -1, --last_updated_by,
            NULL, --operation_seq_num,
            NULL, --resource_seq_num,
            NULL, --resource_id,
            NULL, --inventory_item_id,
            NULL, --instance_id,
            NULL, --serial_number,
            type_1.sched_relationship_id,
            2 -- exception_type
            );

Line: 468

            INSERT INTO WIP_SCHEDULING_EXCEPTIONS
            (
            wip_entity_id,
            organization_id,
            mesg_sequence,
            scheduling_source,
            scheduling_source_id,
            message_text,
            message_type,
            marked_flag,
            reported_date,
            last_update_date,
            creation_date,
            created_by,
            last_update_login,
            last_updated_by,
            operation_seq_num,
            resource_seq_num,
            resource_id,
            inventory_item_id,
            instance_id,
            serial_number,
            sched_relationship_id,
            exception_type
            )
            VALUES
            (
            NULL, --wip_entity_id,
            NULL, --organization_id,
            1, --mesg_sequence,
            NULL, --scheduling_source,
            NULL,--scheduling_source_id,
            l_exception_msg, --message_text,
            NULL, --message_type,
            NULL, --marked_flag,
            SYSDATE, --reported_date,
            SYSDATE, --last_update_date,
            SYSDATE, --creation_date,
            -1, --created_by,
            -1, --last_update_login,
            -1, --last_updated_by,
            NULL, --operation_seq_num,
            NULL, --resource_seq_num,
            NULL, --resource_id,
            NULL, --inventory_item_id,
            NULL, --instance_id,
            NULL, --serial_number,
            type_2.sched_relationship_id,
            2 -- exception_type
            );

Line: 541

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 2
    WHERE   WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     WSR.TOP_LEVEL_OBJECT_ID      = l_top_level_object_id
    AND     WSR.RELATIONSHIP_STATUS     <> 3;

Line: 547

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 2
    WHERE   EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     EWR.TOP_LEVEL_OBJECT_ID      = l_top_level_object_id
    AND     EWR.RELATIONSHIP_STATUS     <> 3;

Line: 573

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 0
    WHERE   WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     WSR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id;

Line: 578

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 0
    WHERE   EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     EWR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id;

Line: 592

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 0
    WHERE   WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     WSR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id;

Line: 597

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 0
    WHERE   EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     EWR.TOP_LEVEL_OBJECT_ID         = l_top_level_object_id;

Line: 618

    UPDATE  WIP_SCHED_RELATIONSHIPS WSR
    SET     WSR.RELATIONSHIP_STATUS = 0
    WHERE   WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     WSR.TOP_LEVEL_OBJECT_ID      = l_top_level_object_id;

Line: 623

    UPDATE  EAM_WO_RELATIONSHIPS EWR
    SET     EWR.RELATIONSHIP_STATUS = 0
    WHERE   EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
    AND     EWR.TOP_LEVEL_OBJECT_ID      = l_top_level_object_id;

Line: 677

    SELECT  WSR.CHILD_OBJECT_ID,
            WSR.CHILD_OBJECT_TYPE_ID
    FROM    WIP_SCHED_RELATIONSHIPS WSR
    WHERE   WSR.PARENT_OBJECT_ID        = l_p_object
    AND     WSR.PARENT_OBJECT_TYPE_ID   = l_p_object_type
    AND     WSR.RELATIONSHIP_TYPE       = 1;

Line: 718

        UPDATE  WIP_SCHED_RELATIONSHIPS WSR
        SET     WSR.RELATIONSHIP_STATUS = 3
        WHERE   WSR.SCHED_RELATIONSHIP_ID IN
                    (
                    SELECT  WSR1.SCHED_RELATIONSHIP_ID
                    FROM    WIP_SCHED_RELATIONSHIPS WSR1,
                            WIP_DISCRETE_JOBS WDJ1,
                            WIP_DISCRETE_JOBS WDJ2
                    WHERE   WSR1.CHILD_OBJECT_TYPE_ID   = child.child_object_type_id
                    AND     WSR1.CHILD_OBJECT_ID        = child.child_object_id
                    AND     WSR1.PARENT_OBJECT_ID       = l_parent_object_id
                    AND     WSR1.PARENT_OBJECT_TYPE_ID  = l_parent_object_type_id
                    AND     WDJ1.WIP_ENTITY_ID          = l_parent_object_id
                    AND     WDJ2.WIP_ENTITY_ID          = child.child_object_id
                    AND     l_parent_object_type_id     = 1
                    AND     child.child_object_type_id  = 1
                    AND     WSR1.relationship_type      = 1
                    AND     (WDJ2.SCHEDULED_START_DATE  < WDJ1.SCHEDULED_START_DATE
                            OR
                            WDJ2.SCHEDULED_COMPLETION_DATE > WDJ1.SCHEDULED_COMPLETION_DATE)
		    AND NOT (   WDJ2.STATUS_TYPE = 7  OR
     		               (WDJ2.STATUS_TYPE in (12,14,15) and WDJ2.DATE_COMPLETED IS NULL)
			     )
	 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
                    );

Line: 745

        UPDATE  EAM_WO_RELATIONSHIPS EWR
        SET     EWR.RELATIONSHIP_STATUS = 3
        WHERE   EWR.WO_RELATIONSHIP_ID IN
                    (
                    SELECT  EWR1.WO_RELATIONSHIP_ID
                    FROM    EAM_WO_RELATIONSHIPS EWR1,
                            WIP_DISCRETE_JOBS WDJ1,
                            WIP_DISCRETE_JOBS WDJ2
                    WHERE   EWR1.CHILD_OBJECT_TYPE_ID   = child.child_object_type_id
                    AND     EWR1.CHILD_OBJECT_ID        = child.child_object_id
                    AND     EWR1.PARENT_OBJECT_ID       = l_parent_object_id
                    AND     EWR1.PARENT_OBJECT_TYPE_ID  = l_parent_object_type_id
                    AND     WDJ1.WIP_ENTITY_ID          = l_parent_object_id
                    AND     WDJ2.WIP_ENTITY_ID          = child.child_object_id
                    AND     l_parent_object_type_id     = 1
                    AND     child.child_object_type_id  = 1
                    AND     EWR1.parent_relationship_type      = 1
                    AND     (WDJ2.SCHEDULED_START_DATE  < WDJ1.SCHEDULED_START_DATE
                            OR
                            WDJ2.SCHEDULED_COMPLETION_DATE > WDJ1.SCHEDULED_COMPLETION_DATE)
		    AND NOT (   WDJ2.STATUS_TYPE = 7  OR
     		               ((WDJ2.STATUS_TYPE in (12,14,15)) and (WDJ2.DATE_COMPLETED IS NULL))
			     )
	 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
                    );

Line: 785

    SELECT  COUNT(SCHED_RELATIONSHIP_ID)
    INTO    l_released_rowcount
    FROM    WIP_SCHED_RELATIONSHIPS  WSR,
            WIP_DISCRETE_JOBS       WDJ
    WHERE   WSR.PARENT_OBJECT_TYPE_ID  = 1
    AND     WSR.CHILD_OBJECT_TYPE_ID   = 1
    AND     WSR.PARENT_OBJECT_ID         = l_parent_object_id
    AND     WSR.PARENT_OBJECT_TYPE_ID    = l_parent_object_type_id
    AND     WSR.RELATIONSHIP_TYPE           = 1
    AND     WSR.RELATIONSHIP_STATUS         = 3
    AND     WDJ.WIP_ENTITY_ID          = WSR.CHILD_OBJECT_ID
    AND     WDJ.STATUS_TYPE            IN (3,4,5,6,7,12,14,15);

Line: 914

	   SELECT wdj.status_type,ewod.user_defined_status_id,ewod.pending_flag
           INTO l_wo_status,l_user_defined_status,l_pending_flag
           FROM wip_discrete_jobs wdj,eam_work_order_details_v ewod
           WHERE wdj.wip_entity_id=p_work_object_id
           and ewod.wip_entity_id=wdj.wip_entity_id;

Line: 923

               SELECT wdj.status_type
               INTO l_parent_status
               FROM  wip_discrete_jobs wdj,wip_sched_relationships wsr
               WHERE wsr.child_object_id =p_work_object_id
               and wsr.child_object_type_id = p_work_object_type_id
               and wsr.relationship_type = 1
               and wdj.wip_entity_id = wsr.parent_object_id;

Line: 955

              SELECT COUNT(*)
              INTO l_invalid_child
              FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
              WHERE wsr.parent_object_id=p_work_object_id
              AND wsr.parent_object_type_id= p_work_object_type_id
              AND wsr.child_object_type_id=p_work_object_type_id
              AND wsr.child_object_id=wdj.wip_entity_id
              AND wsr.relationship_type = 1
              AND wdj.status_type NOT IN (17,7,6);

Line: 965

              SELECT COUNT(*)
              INTO l_invalid_child
              FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
              WHERE wsr.parent_object_id=p_work_object_id
              AND wsr.parent_object_type_id= p_work_object_type_id
              AND wsr.child_object_type_id=p_work_object_type_id
              AND wsr.child_object_id=wdj.wip_entity_id
              AND wsr.relationship_type = 1
              AND wdj.status_type NOT IN (17,7,1,6);

Line: 975

              SELECT COUNT(*)
              INTO l_invalid_child
              FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
              WHERE wsr.parent_object_id=p_work_object_id
              AND wsr.parent_object_type_id= p_work_object_type_id
              AND wsr.child_object_type_id=p_work_object_type_id
              AND wsr.child_object_id=wdj.wip_entity_id
              AND wsr.relationship_type = 1
              AND wdj.status_type NOT IN (3,6,17,7,1,12,14,15,4,5);

Line: 985

              SELECT COUNT(*)
              INTO l_invalid_child
              FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
              WHERE wsr.parent_object_id=p_work_object_id
              AND wsr.parent_object_type_id= p_work_object_type_id
              AND wsr.child_object_type_id=p_work_object_type_id
              AND wsr.child_object_id=wdj.wip_entity_id
              AND wsr.relationship_type = 1
              AND wdj.status_type NOT IN (7,12,14,15);

Line: 995

              SELECT COUNT(*)
              INTO l_invalid_child
              FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
              WHERE wsr.parent_object_id=p_work_object_id
              AND wsr.parent_object_type_id= p_work_object_type_id
              AND wsr.child_object_type_id=p_work_object_type_id
              AND wsr.child_object_id=wdj.wip_entity_id
              AND wsr.relationship_type = 1
              AND wdj.status_type NOT IN (4,5,12,14,15,7);

Line: 1033

	    SELECT  WSR.CHILD_OBJECT_ID,
		    WSR.CHILD_OBJECT_TYPE_ID
		    FROM    WIP_SCHED_RELATIONSHIPS WSR
		    WHERE   WSR.PARENT_OBJECT_ID        = l_p_object
		    AND     WSR.PARENT_OBJECT_TYPE_ID   = l_p_object_type
		    AND     WSR.RELATIONSHIP_TYPE       = 1;

Line: 1041

              SELECT work_order_status
		FROM eam_work_order_details ewod,eam_wo_statuses_v ewsv
		WHERE ewod.wip_entity_id = p_work_object_id
		  AND ewod.user_defined_status_id = ewsv.status_id;

Line: 1053

	    l_WipEntityId_tbl.delete;

Line: 1054

	    l_workorder_status_tbl.delete;

Line: 1057

	      SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
	      l_WipEntityId_tbl,l_workorder_status_tbl
	      FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
	      WHERE wsr.parent_object_id=p_work_object_id
	      AND wsr.parent_object_type_id= p_work_object_type_id
	      AND wsr.child_object_type_id=p_work_object_type_id
	      AND wsr.child_object_id=wdj.wip_entity_id
	      AND wsr.relationship_type = 1
	      AND wdj.status_type NOT IN (17,7,6)
	      AND lk.lookup_type = 'WIP_JOB_STATUS'
	      AND lk.lookup_code = wdj.status_type;

Line: 1069

	    SELECT wsr.child_object_id,lk.meaning  BULK COLLECT INTO
	      l_WipEntityId_tbl,l_workorder_status_tbl
	      FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
	      WHERE wsr.parent_object_id=p_work_object_id
	      AND wsr.parent_object_type_id= p_work_object_type_id
	      AND wsr.child_object_type_id=p_work_object_type_id
	      AND wsr.child_object_id=wdj.wip_entity_id
	      AND wsr.relationship_type = 1
	      AND wdj.status_type NOT IN (17,7,1,6)
	      AND lk.lookup_type = 'WIP_JOB_STATUS'
	      AND lk.lookup_code = wdj.status_type;

Line: 1081

	      SELECT wsr.child_object_id,lk.meaning  BULK COLLECT INTO
	      l_WipEntityId_tbl,l_workorder_status_tbl
	      FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
	      WHERE wsr.parent_object_id=p_work_object_id
	      AND wsr.parent_object_type_id= p_work_object_type_id
	      AND wsr.child_object_type_id=p_work_object_type_id
	      AND wsr.child_object_id=wdj.wip_entity_id
	      AND wsr.relationship_type = 1
	      AND wdj.status_type NOT IN (3,6,17,7,1,12,14,15,4,5)
      	      AND lk.lookup_type = 'WIP_JOB_STATUS'
	      AND lk.lookup_code = wdj.status_type;

Line: 1093

	       SELECT wsr.child_object_id,lk.meaning  BULK COLLECT INTO
	      l_WipEntityId_tbl,l_workorder_status_tbl
	      FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
	      WHERE wsr.parent_object_id=p_work_object_id
	      AND wsr.parent_object_type_id= p_work_object_type_id
	      AND wsr.child_object_type_id=p_work_object_type_id
	      AND wsr.child_object_id=wdj.wip_entity_id
	      AND wsr.relationship_type = 1
	      AND wdj.status_type NOT IN (7,12,14,15)
      	      AND lk.lookup_type = 'WIP_JOB_STATUS'
	      AND lk.lookup_code = wdj.status_type;

Line: 1105

	     SELECT wsr.child_object_id,lk.meaning  BULK COLLECT INTO
	      l_WipEntityId_tbl,l_workorder_status_tbl
	      FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
	      WHERE wsr.parent_object_id=p_work_object_id
	      AND wsr.parent_object_type_id= p_work_object_type_id
	      AND wsr.child_object_type_id=p_work_object_type_id
	      AND wsr.child_object_id=wdj.wip_entity_id
	      AND wsr.relationship_type = 1
	      AND wdj.status_type NOT IN (4,5,12,14,15,7)
      	      AND lk.lookup_type = 'WIP_JOB_STATUS'
	      AND lk.lookup_code = wdj.status_type;