# NetworkFlow

## public void deleteAllByProduct(ProductBean product)

--clearStepNetworkDates(product, session);
DELETE FROM PBM_STEP_NETWORK_DATES WHERE PRODUCT_ID = :PID;

--clearStepNetwork(product, session);
DELETE FROM PBM_STEP_NETWORK WHERE PRODUCT_ID = :PID;

--clearLinkNetwork(product, session);
DELETE FROM PBM_LINK_NETWORK WHERE PRODUCT_ID = :PID;

## networkService.transformAndSave(network);

-- generateNetwork()
--      partNumberDAO.createPartNumberTemporaryTable(product);
-- CREATE_PART_NUMBER_TEMPORARY_TABLE.sql

INSERT
INTO PBM_PART_NUMBER_TMP
  (
    PART_NUMBER_ID,
    ATA100,
    CEMB,
    DBT_CODE,
    DBT_DESCRITION,
    DESCRIPTION,
    DESIGN,
    GOOD_GROUP,
    LEAD_TIME,
    LINK_STATUS,
    LRV,
    ORIGIN,
    PART_NUMBER,
    PROCEDENCE,
    SUPPLIER_CODE,
    SUPPLIER_DESCRIPTION,
    SUPPLY_STRATEGY,
    TOOLING_CLASS,
    TYPE,
    SYSTEM,
    FINAL_DATE,
    APL_DATE,
    PRODUCT_ID,
    PART_NUMBER_UID,
    SOLID_MF,
    STOCK_TRAFFIC,
    ITEM,
    CLASS_TYPE,
    DESIGN_SUPPLIER,
    FAMILY_MACHINED_CODE,
    FAMILY_MACHINED_DESC,
    FAMILY_STAMPED_CODE,
    FAMILY_STAMPED_DESC,
    MACHINED_TECHNOLOGY_CODE,
    MACHINED_TECHNOLOGY_DESC,
    PLANT,
    PROCEDENCE_SUPPLIER,
    RAW_MATERIAL_CODE,
    RAW_MATERIAL_DESC,
    REVISION,
    DESIGN_SUPPLIER_CODE,
    PROCEDENCE_SUPPLIER_CODE,
    PRODUCT_TYPE,
    SERIAL_NUMBER
  )
SELECT PART_NUMBER_ID,
  ATA100,
  CEMB,
  DBT_CODE,
  DBT_DESCRITION,
  DESCRIPTION,
  DESIGN,
  GOOD_GROUP,
  LEAD_TIME,
  LINK_STATUS,
  LRV,
  ORIGIN,
  PART_NUMBER,
  PROCEDENCE,
  SUPPLIER_CODE,
  SUPPLIER_DESCRIPTION,
  SUPPLY_STRATEGY,
  TOOLING_CLASS,
  TYPE,
  SYSTEM,
  FINAL_DATE,
  APL_DATE,
  PRODUCT_ID,
  PART_NUMBER_UID,
  SOLID_MF,
  STOCK_TRAFFIC,
  ITEM,
  CLASS_TYPE,
  DESIGN_SUPPLIER,
  FAMILY_MACHINED_CODE,
  FAMILY_MACHINED_DESC,
  FAMILY_STAMPED_CODE,
  FAMILY_STAMPED_DESC,
  MACHINED_TECHNOLOGY_CODE,
  MACHINED_TECHNOLOGY_DESC,
  PLANT,
  PROCEDENCE_SUPPLIER,
  RAW_MATERIAL_CODE,
  RAW_MATERIAL_DESC,
  REVISION,
  DESIGN_SUPPLIER_CODE,
  PROCEDENCE_SUPPLIER_CODE,
  :TYPE,
  :SERIAL
FROM PBM_PART_NUMBER
WHERE PRODUCT_ID      = :PID
AND LOGICALLY_DELETED = 'N';

-- networkService.transformAndSave(network);
--    networkDAO.transformAndTransfer(network.getProduct());
--        transferStepNetwork(product, session);

INSERT
INTO PBM_STEP_NETWORK_TMP
  (
    STEP_NETWORK_ID,
    PACKAGE_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    PN_CONNECTED,
    PRODUCT_ID
  )
SELECT PBM_SQ_STEP_NETWORK.NEXTVAL,
  J.PACKAGE_ID,
  J.PART_NUMBER_ID,
  J.STEP_ID,
  J.MP_STEP_ID,
  J.PN_CONNECTED,
  J.PRODUCT_ID
FROM PBM_NETWORK N,
JSON_TABLE(STEP_NETWORK FORMAT JSON, '$.obj[*]' COLUMNS ( PACKAGE_ID PATH '$.packageId', PART_NUMBER_ID PATH '$.partNumberId', STEP_ID PATH '$.stepId', MP_STEP_ID PATH '$.mpStepId', PN_CONNECTED PATH '$.connected', PRODUCT_ID PATH '$.productId') ) J
WHERE N.PRODUCT_ID = :PID;

-- networkService.transformAndSave(network);
--    networkDAO.transformAndTransfer(network.getProduct());
--        transferLinkNetwork(product, session);

INSERT
INTO PBM_LINK_NETWORK_TMP
  (
    LINK_NETWORK_ID,
    PACKAGE_ID,
    PART_NUMBER_ID,
    NEXT_PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    PN_CONNECTED,
    NEXT_PN_CONNECTED,
    PRODUCT_ID
  )
SELECT PBM_SQ_LINK_NETWORK.NEXTVAL,
  J.PACKAGE_ID,
  J.PART_NUMBER_ID,
  J.NEXT_PART_NUMBER_ID,
  J.STEP_ID,
  J.MP_STEP_ID,
  J.NEXT_STEP_ID,
  J.NEXT_MP_STEP_ID,
  J.PN_CONNECTED,
  J.NEXT_PN_CONNECTED,
  J.PRODUCT_ID
FROM PBM_NETWORK N,
JSON_TABLE(LINK_NETWORK FORMAT JSON, '$.obj[*]' COLUMNS ( PACKAGE_ID PATH '$.packageId', PART_NUMBER_ID PATH '$.partNumberId', NEXT_PART_NUMBER_ID PATH '$.nextPartNumberId', STEP_ID PATH '$.stepId', MP_STEP_ID PATH '$.mpStepId', NEXT_STEP_ID PATH '$.nextStepId', NEXT_MP_STEP_ID PATH '$.nextMpStepId', PN_CONNECTED PATH '$.connected', NEXT_PN_CONNECTED PATH '$.nextConnected', PRODUCT_ID PATH '$.productId') ) J
WHERE N.PRODUCT_ID = :PID;

## stepQualificationDAO.autoQualification(product, qualification);

-- loadActiveNonRecStepQualificationToTemporaryTable(product);
-- network/ACTIVE_USER_STEP_QUALIFICATION.sql

INSERT INTO PBM_ACT_STEP_QUALIFICATION_TMP (
    PRODUCT_ID,
    CYCLE,
    END_DATE,
    PART_NUMBER_ID,
    STEP_ID,
    KIT_TIMES_ID,
    PROFILE_ID,
    CREATION_DATE
  )
SELECT
  SQ.PRODUCT_ID,
  SQ.CYCLE,
  SQ.END_DATE,
  SQ.PART_NUMBER_ID,
  SQ.STEP_ID,
  SQ.KIT_TIMES_ID,
  Q.PROFILE_ID,
  SQ.CREATION_DATE
FROM PBM_STEP_QUALIFICATION SQ
JOIN PBM_PART_NUMBER_TMP P
ON P.PART_NUMBER_ID 	= SQ.PART_NUMBER_ID
JOIN PBM_QUALIFICATION Q
ON Q.QUALIFICATION_ID 	= SQ.QUALIFICATION_ID
AND Q.ACTIVE 			= 'Y'
WHERE SQ.PRODUCT_ID 	= :PID
AND NVL(SQ.CREATED_BY,0) <> 0;

-- loadActiveUserStepQualificationToTemporaryTable(product);
-- ACTIVE_STEP_NON_REC_QUALIFICATION.sql

INSERT INTO PBM_ACT_STEP_QUALIFICATION_TMP (
    PRODUCT_ID,
    CYCLE,
    END_DATE,
    PART_NUMBER_ID,
    STEP_ID,
    KIT_TIMES_ID,
    PROFILE_ID,
    CREATION_DATE
  )
SELECT
  P.PRODUCT_ID,
  SQ.CYCLE,
  SQ.END_DATE,
  P.PART_NUMBER_ID,
  SQ.STEP_ID,
  SQ.KIT_TIMES_ID,
  Q.PROFILE_ID,
  SQ.CREATION_DATE
FROM PBM_NON_REC_STEP_QUALIFICATION SQ
JOIN PBM_PART_NUMBER_TMP P
ON P.PART_NUMBER_UID 	= SQ.PART_NUMBER_UID
JOIN PBM_QUALIFICATION Q
ON Q.QUALIFICATION_ID 	= SQ.QUALIFICATION_ID
AND Q.ACTIVE 			= 'Y'
AND Q.SCENARIO_ID 		= :SID
WHERE P.PRODUCT_ID 		= :PID;

-- loadActiveStepQualificationToTemporaryTable(product);
-- ACTIVE_STEP_QUALIFICATION.sql

INSERT INTO PBM_ACT_STEP_QUALIF_CONS_TMP (
    PRODUCT_ID,
    PART_NUMBER_ID,
    STEP_ID,
    KIT_TIMES_ID,
    CREATION_DATE
  )
SELECT
  SQ.PRODUCT_ID,
  SQ.PART_NUMBER_ID,
  SQ.STEP_ID,
  SQ.KIT_TIMES_ID,
  SQ.CREATION_DATE
FROM PBM_STEP_QUALIFICATION SQ
JOIN PBM_PART_NUMBER_TMP P ON P.PART_NUMBER_ID 	= SQ.PART_NUMBER_ID
JOIN PBM_QUALIFICATION Q ON Q.QUALIFICATION_ID 	= SQ.QUALIFICATION_ID AND Q.ACTIVE = 'Y'
LEFT JOIN PBM_ACT_STEP_QUALIFICATION_TMP T ON T.PART_NUMBER_ID = P.PART_NUMBER_ID AND T.STEP_ID = SQ.STEP_ID
WHERE SQ.PRODUCT_ID 	= :PID
AND NVL(T.PART_NUMBER_ID,0) = 0
AND NVL(SQ.CREATED_BY,0) = 0;

-- consolidateStepQualification(product);
-- CONSOLIDATE_STEP_QUALIFICATION.sql

INSERT
INTO PBM_ACT_STEP_QUALIFICATION_TMP (
  PART_NUMBER_ID,
  STEP_ID,
  PRODUCT_ID,
  KIT_TIMES_ID,
  CREATION_DATE
)
SELECT
  PART_NUMBER_ID,
  STEP_ID,
  PRODUCT_ID,
  KIT_TIMES_ID,
  CREATION_DATE
FROM (
  SELECT
    PART_NUMBER_ID,
    STEP_ID,
    PRODUCT_ID,
    KIT_TIMES_ID,
    CREATION_DATE,
    ROW_NUMBER() OVER (PARTITION BY STEP_ID, PART_NUMBER_ID ORDER BY CREATION_DATE DESC) SEQ
  FROM PBM_ACT_STEP_QUALIF_CONS_TMP
  WHERE PRODUCT_ID = :PID
) WHERE SEQ = 1;

-- stepQualification(product, qualification, session);
-- network/STEP_QUALIFICATION.sql

INSERT INTO PBM_QUALIFICATION (QUALIFICATION_ID, PARENT_ID, CREATION_DATE, MODIFICATION_DATE, "CONDITION", DESCRIPTION, SCENARIO_ID, USER_ID, ACTIVE, PROFILE_ID)
VALUES(PBM_SQ_QUALIFICATION.nextval, null, current_timestamp, null, null, null, 1, null, 'Y', null);

SELECT max(QUALIFICATION_ID) FROM PBM_QUALIFICATION;

INSERT
INTO PBM_ACT_STEP_QUALIFICATION_TMP (
    PRODUCT_ID,
    QUALIFICATION_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    KIT_TIMES_ID,
    IS_NEW,
    CREATION_DATE
  )
SELECT
  PRODUCT_ID,
  :QID,
  PART_NUMBER_ID,
  STEP_ID,
  MP_STEP_ID,
  KIT_TIMES_ID,
  IS_NEW,
  SYSDATE
FROM (
  SELECT
    SN.STEP_ID,
    SN.PRODUCT_ID,
    SN.MP_STEP_ID,
    SN.PART_NUMBER_ID,
    MP.KIT_TIMES_ID,
    'Y' AS IS_NEW
  FROM PBM_STEP_NETWORK_TMP SN
  JOIN PBM_REL_PART_NUMBER_MP MP
  ON MP.PART_NUMBER_ID = SN.PART_NUMBER_ID
  --AND MP.PRODUCT_ID = SN.PRODUCT_ID
  LEFT JOIN PBM_ACT_STEP_QUALIFICATION_TMP SQ
  ON SQ.STEP_ID         = SN.STEP_ID
  AND SQ.PART_NUMBER_ID = SN.PART_NUMBER_ID
  WHERE SN.PRODUCT_ID       = :PID
  AND NVL(SQ.KIT_TIMES_ID,0) <> DECODE(SQ.PROFILE_ID,NULL,MP.KIT_TIMES_ID,NULL)
  GROUP BY SN.PRODUCT_ID,
    SN.PART_NUMBER_ID,
    SN.STEP_ID,
    SN.MP_STEP_ID,
    MP.KIT_TIMES_ID
);

-- transferToTemporaryStepTable(product, scenario, session);
-- network/TEMPORARY_STEP_QUALIFICATION.sql

INSERT
INTO PBM_STEP_QUALIFICATION_TMP (
    PRODUCT_ID,
    CYCLE,
    END_DATE,
    STEP_ID,
    PART_NUMBER_ID,
    CREATION_DATE,
    PROFILE_ID,
    KIT_TIMES_ID,
    MACROPROCESS_ID
  )
SELECT PRODUCT_ID,
  CYCLE,
  END_DATE,
  STEP_ID,
  PART_NUMBER_ID,
  CREATION_DATE,
  PROFILE_ID,
  KIT_TIMES_ID,
  MACROPROCESS_ID
FROM
  (SELECT SQ.PRODUCT_ID,
    SQ.CYCLE,
    SQ.END_DATE,
    SQ.STEP_ID,
    SQ.PART_NUMBER_ID,
    SQ.CREATION_DATE,
    SQ.PROFILE_ID,
    K.KIT_TIMES_ID,
    K.MACROPROCESS_ID,
    ROW_NUMBER() OVER(PARTITION BY SQ.STEP_ID, SQ.PART_NUMBER_ID ORDER BY SQ.CREATION_DATE DESC) SEQ
  FROM PBM_ACT_STEP_QUALIFICATION_TMP SQ
  LEFT JOIN PBM_KIT_TIMES K
  ON K.KIT_TIMES_ID       = SQ.KIT_TIMES_ID
  AND K.SCENARIO_ID       = :SID
  AND K.LOGICALLY_DELETED = 'N'
  WHERE SQ.PRODUCT_ID     = :PID
  ) WHERE SEQ = 1;

-- preventNullCycles(product, session);
-- network/PREVENT_NULL_CYCLES.sql

UPDATE PBM_STEP_QUALIFICATION_TMP Q
SET
  (
    Q.KIT_TIMES_ID,
    Q.MACROPROCESS_ID
  )
  =
  (
  SELECT
  	MP.KIT_TIMES_ID,
    MP.MACROPROCESS_ID
  FROM PBM_STEP_QUALIFICATION_TMP SQ
  INNER JOIN PBM_CONS_KIT_TIMES_TMP MP
  ON MP.PART_NUMBER_ID       = SQ.PART_NUMBER_ID
  WHERE SQ.STEP_ID           = Q.STEP_ID
  AND SQ.PART_NUMBER_ID      = Q.PART_NUMBER_ID
  AND NVL(SQ.KIT_TIMES_ID,0) = 0
  AND SQ.PRODUCT_ID = :PID
  )
WHERE NVL(Q.KIT_TIMES_ID,0) = 0
AND Q.PRODUCT_ID = :PID;

-- consolidateCycles(product, session);
-- network/CONSOLIDATE_CYCLES.sql

INSERT INTO PBM_STEP_CYCLES_TMP (
	PRODUCT_ID,
	PART_NUMBER_ID,
	STEP_ID,
	MP_STEP_ID,
	END_DATE,
	CYCLE
  )
SELECT
	SQ.PRODUCT_ID,
  	SQ.PART_NUMBER_ID,
  	SQ.STEP_ID,
  	SQ.MP_STEP_ID,
  	SQ.END_DATE,
  	NVL(SQ.CYCLE,KC.LEADTIME)
FROM PBM_CONS_STEP_VALUE_TMP SQ
LEFT JOIN PBM_KIT_CYCLE_VALUE KC
ON KC.MP_STEP_ID    = SQ.MP_STEP_ID
AND KC.KIT_CYCLE_ID = SQ.KIT_CYCLE_ID
WHERE SQ.PRODUCT_ID = :PID;

## linkQualificationDAO.autoQualification(product, qualification);

-- loadActiveLinkQualificationToTemporaryTable(product);
-- network/ACTIVE_LINK_QUALIFICATION.sql

INSERT
INTO PBM_ACT_LINK_QUALIFICATION_TMP (
    LAG,
    PRODUCT_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID,
    PROFILE_ID,
    CREATION_DATE
  ) SELECT
    LQ.LAG,
    LQ.PRODUCT_ID,
    LQ.PART_NUMBER_ID,
    LQ.STEP_ID,
    LQ.MP_STEP_ID,
    LQ.NEXT_STEP_ID,
    LQ.MP_NEXT_STEP_ID,
    LQ.KIT_TIMES_ID,
    Q.PROFILE_ID,
    LQ.CREATION_DATE
FROM PBM_LINK_QUALIFICATION LQ
JOIN PBM_PART_NUMBER P
ON P.PART_NUMBER_ID 	= LQ.PART_NUMBER_ID
AND P.LOGICALLY_DELETED = 'N'
AND P.PRODUCT_ID 		= LQ.PRODUCT_ID
JOIN PBM_QUALIFICATION Q
ON Q.QUALIFICATION_ID   = LQ.QUALIFICATION_ID
AND Q.ACTIVE        	= 'Y'
WHERE LQ.PRODUCT_ID     = :PID;

-- loadActiveNonRecLinkQualificationToTemporaryTable(product);
-- network/ACTIVE_NON_REC_LINK_QUALIFICATION.sql

INSERT
INTO PBM_ACT_LINK_QUALIFICATION_TMP (
    LAG,
    PRODUCT_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID,
    PROFILE_ID,
    CREATION_DATE
  ) SELECT
    LQ.LAG,
    P.PRODUCT_ID,
    P.PART_NUMBER_ID,
    LQ.STEP_ID,
    LQ.MP_STEP_ID,
    LQ.NEXT_STEP_ID,
    LQ.MP_NEXT_STEP_ID,
    LQ.KIT_TIMES_ID,
    Q.PROFILE_ID,
    LQ.CREATION_DATE
FROM PBM_NON_REC_LINK_QUALIFICATION LQ
JOIN PBM_PART_NUMBER_TMP P
ON P.PART_NUMBER_UID 	= LQ.PART_NUMBER_UID
JOIN PBM_QUALIFICATION Q
ON Q.QUALIFICATION_ID   = LQ.QUALIFICATION_ID
AND Q.ACTIVE        	= 'Y'
AND Q.SCENARIO_ID 		= :SID
WHERE P.PRODUCT_ID     	= :PID;

---------

DELETE
FROM PBM_ACT_LINK_QUALIFICATION_TMP ALQ
WHERE NOT EXISTS
  ( SELECT 1 FROM PBM_REL_MP_STEP MP WHERE MP.MP_STEP_ID = ALQ.MP_STEP_ID AND ALQ.PROFILE_ID <> 0
  ) AND ALQ.PROFILE_ID IS NULL;

DELETE
FROM PBM_ACT_LINK_QUALIFICATION_TMP ALQ
WHERE NOT EXISTS
  ( SELECT 1 FROM PBM_REL_MP_STEP MP WHERE MP.MP_STEP_ID = ALQ.MP_NEXT_STEP_ID AND ALQ.PROFILE_ID <> 0
  ) AND ALQ.PROFILE_ID IS NULL;

---------



-- linkQualification(product, qualification, session);
--    linkInnerQualification(product, qualification, session);
--    network/INNER_LINK_QUALIFICATION.sql

INSERT
INTO PBM_ACT_LINK_QUALIFICATION_TMP (
	PRODUCT_ID,
	QUALIFICATION_ID,
	PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID,
    IS_NEW,
    CREATION_DATE
  )
SELECT
  PRODUCT_ID,
  :QID,
  NEXT_PART_NUMBER_ID,
  STEP_ID,
  MP_STEP_ID,
  NEXT_STEP_ID,
  MP_NEXT_STEP_ID,
  KIT_TIMES_ID,
  IS_NEW,
  SYSDATE
FROM (
  SELECT
  	LN.PRODUCT_ID,
  	LN.NEXT_PART_NUMBER_ID,
    LN.STEP_ID,
    LN.MP_STEP_ID,
    LN.NEXT_STEP_ID,
    LN.MP_NEXT_STEP_ID,
    MP.KIT_TIMES_ID,
    'Y' AS IS_NEW
  FROM PBM_LINK_NETWORK_TMP LN
  INNER JOIN PBM_REL_MP_STEP MS
  ON MS.MP_STEP_ID = LN.MP_NEXT_STEP_ID
  -- INNER JOIN PBM_CONS_KIT_TIMES_TMP MP

  -- Alterei esse trecho para teste

  INNER JOIN (SELECT MP.*, PN.PRODUCT_ID FROM PBM_REL_PART_NUMBER_MP MP
  INNER JOIN PBM_PART_NUMBER PN ON MP.PART_NUMBER_ID = PN.PART_NUMBER_ID) MP
  ON MP.PART_NUMBER_ID = LN.NEXT_PART_NUMBER_ID
  AND MP.PRODUCT_ID    = LN.PRODUCT_ID
  LEFT JOIN PBM_ACT_LINK_QUALIFICATION_TMP LQ
  ON LQ.STEP_ID  = LN.STEP_ID
  AND LQ.NEXT_STEP_ID         = LN.NEXT_STEP_ID
  AND LQ.PART_NUMBER_ID       = LN.NEXT_PART_NUMBER_ID
  AND LQ.PRODUCT_ID           = LN.PRODUCT_ID
  WHERE LN.PRODUCT_ID         = :PID
  AND MS.CONNECTOR           <> 'Y'
  AND NVL(LQ.KIT_TIMES_ID,0) <> DECODE(LQ.PROFILE_ID,NULL,MP.KIT_TIMES_ID,NULL)
  GROUP BY LN.PRODUCT_ID,
    LN.NEXT_PART_NUMBER_ID, LN.STEP_ID,
    LN.MP_STEP_ID, LN.NEXT_STEP_ID,
    LN.MP_NEXT_STEP_ID, MP.KIT_TIMES_ID
  );

--    linkOuterQualification(product, qualification, session);
--    network/OUTER_LINK_QUALIFICATION.sql
INSERT
INTO PBM_ACT_LINK_QUALIFICATION_TMP (
    PRODUCT_ID,
    QUALIFICATION_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID,
    IS_NEW,
    CREATION_DATE
  )
SELECT
	PRODUCT_ID,
	:QID,
  	NEXT_PART_NUMBER_ID,
  	STEP_ID,
  	MP_STEP_ID,
  	NEXT_STEP_ID,
  	MP_NEXT_STEP_ID,
  	KIT_TIMES_ID,
  	IS_NEW,
  	SYSDATE
FROM (
  SELECT
  	LN.PRODUCT_ID,
  	LN.NEXT_PART_NUMBER_ID,
  	LN.STEP_ID,
    LN.MP_STEP_ID,
    LN.NEXT_STEP_ID,
    LN.MP_NEXT_STEP_ID,
    MP.KIT_TIMES_ID,
    'Y' AS IS_NEW
  FROM PBM_LINK_NETWORK_TMP LN
  INNER JOIN PBM_REL_MP_STEP MS
  ON MS.MP_STEP_ID = LN.MP_NEXT_STEP_ID

  -- INNER JOIN PBM_CONS_KIT_TIMES_TMP MP
  -- Alterei esse trecho para teste

  INNER JOIN (SELECT MP.*, PN.PRODUCT_ID FROM PBM_REL_PART_NUMBER_MP MP
  INNER JOIN PBM_PART_NUMBER PN ON MP.PART_NUMBER_ID = PN.PART_NUMBER_ID) MP

  ON MP.PART_NUMBER_ID = LN.PART_NUMBER_ID
  AND MP.PRODUCT_ID    = LN.PRODUCT_ID
  LEFT JOIN PBM_ACT_LINK_QUALIFICATION_TMP LQ
  ON LQ.STEP_ID = LN.STEP_ID
  AND LQ.NEXT_STEP_ID = LN.NEXT_STEP_ID
  AND LQ.PART_NUMBER_ID = LN.NEXT_PART_NUMBER_ID
  AND LQ.PRODUCT_ID = LN.PRODUCT_ID
  WHERE LN.PRODUCT_ID = :PID
  AND MS.CONNECTOR = 'Y'
  AND NVL(LQ.KIT_TIMES_ID,0) <> DECODE(LQ.PROFILE_ID,NULL,MP.KIT_TIMES_ID,NULL)
  GROUP BY LN.PRODUCT_ID,
    LN.NEXT_PART_NUMBER_ID,
    LN.STEP_ID,
    LN.MP_STEP_ID,
    LN.NEXT_STEP_ID,
    LN.MP_NEXT_STEP_ID,
    MP.KIT_TIMES_ID
  );

-- consolidateActiveLinkQualificationToTemporaryTable(product, session);
--    consolidateActiveLinkUserQualificationToTemporaryTable(product, session);
--    network/CONSOLIDATE_ACTIVE_USER_LINK_QUALIFICATION.sql
INSERT
INTO PBM_LINK_QUALIFICATION_TMP (
    LAG,
    PRODUCT_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID,
    PROFILE_ID
  )
SELECT
	LAG,
	PRODUCT_ID,
	PART_NUMBER_ID,
  	STEP_ID,
  	MP_STEP_ID,
  	NEXT_STEP_ID,
  	MP_NEXT_STEP_ID,
  	KIT_TIMES_ID,
  	PROFILE_ID
FROM (
	SELECT
      LAG,
      PRODUCT_ID,
      PART_NUMBER_ID,
      STEP_ID,
      MP_STEP_ID,
      NEXT_STEP_ID,
      MP_NEXT_STEP_ID,
      KIT_TIMES_ID,
      PROFILE_ID,
      ROW_NUMBER() OVER(PARTITION BY PART_NUMBER_ID, STEP_ID,
      NEXT_STEP_ID ORDER BY CREATION_DATE DESC) SEQ
    FROM
      PBM_ACT_LINK_QUALIFICATION_TMP
    WHERE
      NVL(PROFILE_ID,0) <> 0
      AND PRODUCT_ID = :PID
) WHERE SEQ = 1;

--    consolidateActiveLinkAutoQualificationToTemporaryTable(product, session);
--    network/CONSOLIDATE_ACTIVE_AUTO_LINK_QUALIFICATION.sql
INSERT
INTO PBM_LINK_QUALIFICATION_TMP (
    PRODUCT_ID,
    PART_NUMBER_ID,
    STEP_ID,
    MP_STEP_ID,
    NEXT_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_TIMES_ID
  )
SELECT
	PRODUCT_ID,
	  PART_NUMBER_ID,
  	STEP_ID,
  	MP_STEP_ID,
  	NEXT_STEP_ID,
  	MP_NEXT_STEP_ID,
  	KIT_TIMES_ID
FROM (
	SELECT
      A.PRODUCT_ID,
      A.PART_NUMBER_ID,
      A.STEP_ID,
      A.MP_STEP_ID,
      A.NEXT_STEP_ID,
      A.MP_NEXT_STEP_ID,
      A.KIT_TIMES_ID,
      ROW_NUMBER() OVER(PARTITION BY A.PART_NUMBER_ID, A.STEP_ID,
      A.NEXT_STEP_ID, A.MP_STEP_ID, A.MP_NEXT_STEP_ID ORDER BY
      A.CREATION_DATE DESC) SEQ
    FROM
      PBM_ACT_LINK_QUALIFICATION_TMP A
    WHERE
      NVL(PROFILE_ID,0)  = 0
      AND A.PRODUCT_ID   = :PID
      AND NOT EXISTS
      (
        SELECT
          C.PART_NUMBER_ID
        FROM
          PBM_LINK_QUALIFICATION_TMP C
        WHERE
          C.PART_NUMBER_ID = A.PART_NUMBER_ID
        AND C.STEP_ID      = A.STEP_ID
        AND C.NEXT_STEP_ID = A.NEXT_STEP_ID
        AND C.PRODUCT_ID   = A.PRODUCT_ID
        AND C.PRODUCT_ID   = :PID
      )
) WHERE SEQ = 1;



-- consolidateLags(product, session);
--  consolidateUserLagsTemp(product, session);
--  network/CONSOLIDATE_USER_LAGS_TMP.sql
INSERT
INTO PBM_CONS_LINK_VALUE_TMP (
    PRODUCT_ID,
    PART_NUMBER_ID,
    NEXT_PART_NUMBER_ID,
    STEP_ID,
    NEXT_STEP_ID,
    MP_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_LAG_ID,
    LAG
  )
SELECT
	LN.PRODUCT_ID,
  	LN.PART_NUMBER_ID,
  	LN.NEXT_PART_NUMBER_ID,
  	LN.STEP_ID,
  	LN.NEXT_STEP_ID,
  	LN.MP_STEP_ID,
  	LN.MP_NEXT_STEP_ID,
  	KT.KIT_LAG_ID,
  	LQ.LAG
FROM PBM_LINK_NETWORK_TMP LN
INNER JOIN PBM_LINK_QUALIFICATION_TMP LQ
ON LQ.STEP_ID            = LN.STEP_ID
AND LQ.NEXT_STEP_ID      = LN.NEXT_STEP_ID
AND LQ.PART_NUMBER_ID    = LN.NEXT_PART_NUMBER_ID
AND LQ.PRODUCT_ID        = LN.PRODUCT_ID
AND NVL(LQ.PROFILE_ID,0) <>  0
LEFT JOIN PBM_KIT_TIMES KT
ON KT.KIT_TIMES_ID		 = LQ.KIT_TIMES_ID
AND KT.LOGICALLY_DELETED = 'N'
AND KT.SCENARIO_ID 		 = :SID
WHERE LN.PRODUCT_ID 	 = :PID;


--  consolidateAutoLagsTemp(product, session);
--  network/CONSOLIDATE_AUTO_LAGS_TMP.sql

-- Query ofensora SQL_ID 296qrrncghufz

INSERT
INTO PBM_CONS_LINK_VALUE_TMP
  (
    PRODUCT_ID,
    PART_NUMBER_ID,
    NEXT_PART_NUMBER_ID,
    STEP_ID,
    NEXT_STEP_ID,
    MP_STEP_ID,
    MP_NEXT_STEP_ID,
    KIT_LAG_ID,
    LAG
  )
SELECT LNT.PRODUCT_ID,
  LNT.PART_NUMBER_ID,
  LNT.NEXT_PART_NUMBER_ID,
  LNT.STEP_ID,
  LNT.NEXT_STEP_ID,
  LNT.MP_STEP_ID,
  LNT.MP_NEXT_STEP_ID,
  KT.KIT_LAG_ID,
  LQ.LAG
FROM PBM_LINK_NETWORK_TMP LNT
INNER JOIN PBM_LINK_QUALIFICATION_TMP LQ
ON LQ.STEP_ID            = LNT.STEP_ID
AND LQ.MP_STEP_ID        = LNT.MP_STEP_ID
AND LQ.NEXT_STEP_ID      = LNT.NEXT_STEP_ID
AND LQ.MP_NEXT_STEP_ID   = LNT.MP_NEXT_STEP_ID
AND LQ.PART_NUMBER_ID    = LNT.NEXT_PART_NUMBER_ID
AND LQ.PRODUCT_ID        = LNT.PRODUCT_ID
AND NVL(LQ.PROFILE_ID,0) = 0
LEFT JOIN PBM_KIT_TIMES KT
ON KT.KIT_TIMES_ID       = LQ.KIT_TIMES_ID
AND KT.LOGICALLY_DELETED = 'N'
AND KT.SCENARIO_ID       = :SID
WHERE LNT.PRODUCT_ID      = :PID


-- network/CONSOLIDATE_LAGS.sql
INSERT INTO PBM_LINK_LAGS_TMP (
	PRODUCT_ID,
	PART_NUMBER_ID,
	NEXT_PART_NUMBER_ID,
	STEP_ID, NEXT_STEP_ID,
	MP_STEP_ID,
	MP_NEXT_STEP_ID,
	LAG
)
SELECT
	LQ.PRODUCT_ID,
  	LQ.PART_NUMBER_ID,
  	LQ.NEXT_PART_NUMBER_ID,
  	LQ.STEP_ID,
  	LQ.NEXT_STEP_ID,
  	LQ.MP_STEP_ID,
  	LQ.MP_NEXT_STEP_ID,
  	NVL(LQ.LAG,KL.LAG)
FROM PBM_CONS_LINK_VALUE_TMP LQ
LEFT JOIN PBM_KIT_LAG_VALUE KL
ON KL.MP_STEP_SOURCE_ID   = LQ.MP_STEP_ID
AND KL.MP_STEP_DESTINY_ID = LQ.MP_NEXT_STEP_ID
AND KL.KIT_LAG_ID         = LQ.KIT_LAG_ID
WHERE LQ.PRODUCT_ID       = :PID;

## stepNetwork.updateDates(product);

### createTemporaryDateTables(session, productId, scenarioId);

#### insertAplDates(session, productId, scenarioId);

--    network/INSERT_ASSEMBLY_DATE.sql
INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  SN.STEP_ID,
  PN.PART_NUMBER_ID,
  P.NECESSITY_DATE,
  'Mandatory Finish'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_PART_NUMBER PN
ON PN.PART_NUMBER_ID     = SN.PART_NUMBER_ID
AND PN.LOGICALLY_DELETED = 'N'
AND PN.PRODUCT_ID        = SN.PRODUCT_ID
INNER JOIN PBM_PRODUCT P
ON P.PRODUCT_ID = SN.PRODUCT_ID
INNER JOIN
  (SELECT AP.PART_NUMBER
  FROM PBM_ASSEMBLY_POSITION AP
  WHERE AP.PRODUCT_ID = :PID
  MINUS
  SELECT AP.PART_NUMBER
  FROM PBM_REL_ASSEMBLY_POSITION RA
  INNER JOIN PBM_ASSEMBLY_POSITION AP
  ON AP.ASSEMBLY_POSITION_ID = RA.SOURCE_POSITION
  AND AP.PRODUCT_ID          = :PID
  ) A
ON A.PART_NUMBER    = PN.PART_NUMBER
WHERE SN.PRODUCT_ID = :PID;

--    network/INSERT_PACKAGE_DATE.sql

INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  SN.STEP_ID,
  PN.PART_NUMBER_ID,
  PK.NECESSITY_DATE,
  'Mandatory Finish'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_PART_NUMBER PN
ON PN.PART_NUMBER_ID     = SN.PART_NUMBER_ID
AND PN.LOGICALLY_DELETED = 'N'
AND PN.PRODUCT_ID        = SN.PRODUCT_ID
INNER JOIN PBM_PACKAGE PK
ON PK.PART_NUMBER      = PN.PART_NUMBER
AND PK.PRODUCT_ID      = PN.PRODUCT_ID
AND PK.NECESSITY_DATE IS NOT NULL
WHERE SN.PRODUCT_ID    = :PID;

--    network/INSERT_QUALIFIED_DATES.sql

INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  SN.STEP_ID,
  SN.PART_NUMBER_ID,
  SQ.END_DATE,
  'Finish On or After'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP_QUALIFICATION_TMP SQ
ON SQ.PART_NUMBER_ID = SN.PART_NUMBER_ID
AND SQ.STEP_ID       = SN.STEP_ID
AND SQ.PRODUCT_ID    = SN.PRODUCT_ID
AND SQ.END_DATE     IS NOT NULL
LEFT OUTER JOIN PBM_APL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

--    network/INSERT_REC_PLANNED_DATES.sql
INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  RD.PLANNED_DATE,
  'Finish On or After'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.LOGICALLY_DELETED = 'N'
AND S.RECURRENT         = 'Y'
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_RECURRING_DATE RD
ON RD.PART_NUMBER_ID = SN.PART_NUMBER_ID
AND RD.EVENT_ID      = S.FINAL_PROPOSAL_DATE_CODE
AND RD.PLANNED_DATE IS NOT NULL
AND RD.PRODUCT_ID    = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_APL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

--    network/INSERT_DYNAMIC_PLANNED_DATES.sql
INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.PLANNED_DATE,
  'Finish On or After'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.LOGICALLY_DELETED = 'N'
AND S.RECURRENT         = 'N'
AND S.STEP_TYPE_ID      = 3
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID            = SN.PART_NUMBER_ID
AND NR.EVENT_ID                 = S.FINAL_PROPOSAL_DATE_CODE
AND NVL(NR.SHEET_NUM,'_NULO')   = NVL(S.SHEET_NUM,'_NULO')
AND NVL(NR.REPRES_TYPE,'_NULO') = NVL(S.REPRES_TYPE,'_NULO')
AND NR.PLANNED_DATE            IS NOT NULL
AND NR.PRODUCT_ID               = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_APL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

--    network/INSERT_NON_REC_PLANNED_DATES.sql
INSERT
INTO PBM_APL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE,
    COD_TIPO_DAT_APL
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.PLANNED_DATE,
  'Finish On or After'
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.LOGICALLY_DELETED = 'N'
AND S.RECURRENT         = 'N'
AND S.STEP_TYPE_ID     <> 3
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID = SN.PART_NUMBER_ID
AND NR.EVENT_ID      = S.FINAL_PROPOSAL_DATE_CODE
AND NR.PLANNED_DATE IS NOT NULL
AND NR.PRODUCT_ID    = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_APL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

#### insertFinalDates(session, productId, scenarioId);

--    network/INSERT_REC_ACTUAL_END_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  RD.EFFECTIVE_DATE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.RECURRENT         = 'Y'
AND S.LOGICALLY_DELETED = 'N'
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_RECURRING_DATE RD
ON RD.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND RD.EVENT_ID        = S.ACTUAL_END_DATE_CODE
AND RD.EFFECTIVE_DATE IS NOT NULL
AND RD.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID;

--    network/INSERT_DYNAMIC_ACTUAL_END_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.EFFECTIVE_DATE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.RECURRENT         = 'N'
AND S.STEP_TYPE_ID      = 3
AND S.LOGICALLY_DELETED = 'N'
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID            = SN.PART_NUMBER_ID
AND NR.EVENT_ID                 = S.ACTUAL_END_DATE_CODE
AND NVL(NR.SHEET_NUM,'_NULO')   = NVL(S.SHEET_NUM,'_NULO')
AND NVL(NR.REPRES_TYPE,'_NULO') = NVL(S.REPRES_TYPE,'_NULO')
AND NR.EFFECTIVE_DATE          IS NOT NULL
AND NR.PRODUCT_ID               = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID             = :PID;

--    network/INSERT_NON_REC_ACTUAL_END_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.EFFECTIVE_DATE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.RECURRENT         = 'N'
AND S.STEP_TYPE_ID     <> 3
AND S.LOGICALLY_DELETED = 'N'
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND NR.EVENT_ID        = S.ACTUAL_END_DATE_CODE
AND NR.EFFECTIVE_DATE IS NOT NULL
AND NR.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID;

--    network/INSERT_REC_ACTUAL_START_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  RD.EFFECTIVE_DATE + SN.CYCLE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.LOGICALLY_DELETED = 'N'
AND S.RECURRENT         = 'Y'
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_RECURRING_DATE RD
ON RD.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND RD.EVENT_ID        = S.ACTUAL_START_DATE_CODE
AND RD.EFFECTIVE_DATE IS NOT NULL
AND RD.PRODUCT_ID      = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_FINAL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

--    network/INSERT_DYNAMIC_ACTUAL_START_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.EFFECTIVE_DATE + SN.CYCLE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.RECURRENT         = 'N'
AND S.LOGICALLY_DELETED = 'N'
AND S.STEP_TYPE_ID      = 3
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID            = SN.PART_NUMBER_ID
AND NR.EVENT_ID                 = S.ACTUAL_START_DATE_CODE
AND NVL(NR.SHEET_NUM,'_NULO')   = NVL(S.SHEET_NUM,'_NULO')
AND NVL(NR.REPRES_TYPE,'_NULO') = NVL(S.REPRES_TYPE,'_NULO')
AND NR.EFFECTIVE_DATE          IS NOT NULL
AND NR.PRODUCT_ID               = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_FINAL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

--    network/INSERT_NON_REC_ACTUAL_START_DATES.sql
INSERT
INTO PBM_FINAL_DATES_TMP
  (
    PRODUCT_ID,
    STEP_ID,
    PART_NUMBER_ID,
    EFFECTIVE_DATE
  )
SELECT SN.PRODUCT_ID,
  S.STEP_ID,
  SN.PART_NUMBER_ID,
  NR.EFFECTIVE_DATE + SN.CYCLE
FROM PBM_STEP_CYCLES_TMP SN
INNER JOIN PBM_STEP S
ON SN.STEP_ID           = S.STEP_ID
AND S.RECURRENT         = 'N'
AND S.LOGICALLY_DELETED = 'N'
AND S.STEP_TYPE_ID     <> 3
AND S.SCENARIO_ID       = :SID
INNER JOIN PBM_PRODUCT_NON_RECURRING_DATE NR
ON NR.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND NR.EVENT_ID        = S.ACTUAL_START_DATE_CODE
AND NR.EFFECTIVE_DATE IS NOT NULL
AND NR.PRODUCT_ID      = SN.PRODUCT_ID
LEFT OUTER JOIN PBM_FINAL_DATES_TMP DT
ON DT.PART_NUMBER_ID   = SN.PART_NUMBER_ID
AND DT.STEP_ID         = SN.STEP_ID
AND DT.PRODUCT_ID      = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID    = :PID
AND DT.EFFECTIVE_DATE IS NULL;

### updateDatesOnStepNetwork(session, productId);

-- network/UPDATE_APL_DATES.sql
UPDATE PBM_STEP_NETWORK_TMP SN
SET
  (
    SN.COD_TIPO_DAT_APL,
    SN.APL_DATE
  )
  =
  (SELECT T.COD_TIPO_DAT_APL,
    T.EFFECTIVE_DATE
  FROM
    (SELECT A.COD_TIPO_DAT_APL,
      A.EFFECTIVE_DATE,
      ROW_NUMBER() OVER(PARTITION BY A.STEP_ID, A.PART_NUMBER_ID ORDER BY A.EFFECTIVE_DATE ASC) SEQ
    FROM PBM_APL_DATES_TMP A
    WHERE A.PART_NUMBER_ID = SN.PART_NUMBER_ID
    AND A.STEP_ID          = SN.STEP_ID
    AND A.PRODUCT_ID       = SN.PRODUCT_ID
    ) T
  WHERE T.SEQ = 1
  )
WHERE SN.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_APL_DATES_TMP A
  WHERE A.PART_NUMBER_ID = SN.PART_NUMBER_ID
  AND A.STEP_ID          = SN.STEP_ID
  AND A.PRODUCT_ID       = SN.PRODUCT_ID);

-- network/UPDATE_FINAL_DATES.sql
UPDATE PBM_STEP_NETWORK_TMP SN
SET
  (
    SN.FINAL_DATE
  )
  =
  (SELECT T.EFFECTIVE_DATE
  FROM
    (SELECT F.EFFECTIVE_DATE,
      ROW_NUMBER() OVER(PARTITION BY F.STEP_ID, F.PART_NUMBER_ID ORDER BY F.EFFECTIVE_DATE ASC) SEQ
    FROM PBM_FINAL_DATES_TMP F
    WHERE F.PART_NUMBER_ID = SN.PART_NUMBER_ID
    AND F.STEP_ID          = SN.STEP_ID
    AND F.PRODUCT_ID       = SN.PRODUCT_ID
    ) T
  WHERE T.SEQ = 1
  )
WHERE SN.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_FINAL_DATES_TMP F
  WHERE F.PART_NUMBER_ID = SN.PART_NUMBER_ID
  AND F.STEP_ID          = SN.STEP_ID
  AND F.PRODUCT_ID       = SN.PRODUCT_ID);

## stepNetworkDAO.removeNonRecurringTasksAndCommit(product);

### updateOnStepNetwork (session, product);

-- insertPartNumberEventTemporary(session, product);
-- network/INSERT_PART_NUMBER_EVENTS.sql
INSERT
INTO PBM_PART_NUMBER_EVENT_TMP
  (
    PART_NUMBER_ID,
    PART_NUMBER_UID,
    EVENT_ID,
    STEP_ID,
    PRODUCT_TYPE,
    SERIAL_NUMBER,
    PRODUCT_ID
  )
SELECT PN.PART_NUMBER_ID,
  PN.PART_NUMBER_UID,
  S.EVENT_ID,
  S.STEP_ID,
  PN.PRODUCT_TYPE,
  PN.SERIAL_NUMBER,
  PN.PRODUCT_ID
FROM PBM_STEP_NETWORK_TMP STEP_N
INNER JOIN PBM_PART_NUMBER_TMP PN
ON PN.PART_NUMBER_ID = STEP_N.PART_NUMBER_ID
AND PN.PRODUCT_ID    = STEP_N.PRODUCT_ID
INNER JOIN
  (SELECT STEP_ID,
    ACTUAL_END_DATE_CODE AS EVENT_ID
  FROM PBM_STEP
  WHERE LOGICALLY_DELETED          = 'N'
  AND RECURRENT                    = 'N'
  AND IS_TRIGGER                   = 'Y'
  AND SCENARIO_ID                  = :SID
  AND NVL(ACTUAL_END_DATE_CODE,0) <> 0
  ) S
ON S.STEP_ID            = STEP_N.STEP_ID
WHERE STEP_N.PRODUCT_ID = :PID;

-- insertConsolidationPartNumberEventTemporary(session, product);
-- network/INSERT_CONS_PART_NUMBER_EVENTS.sql
INSERT INTO PBM_CONS_PART_NUMBER_EVENT_TMP (
  STEP_ID,
  PART_NUMBER_ID,
  PRODUCT_TYPE,
  SERIAL_NUMBER
)
SELECT
  PN.STEP_ID,
  PN.PART_NUMBER_ID,
  NR.PRODUCT_TYPE,
  NR.SERIAL_NUMBER
FROM PBM_PART_NUMBER_EVENT_TMP PN
INNER JOIN PBM_STEP_NON_RECURRENT_DATES NR
ON NR.PART_NUMBER_UID  = PN.PART_NUMBER_UID
AND NR.EVENT_ID        = PN.EVENT_ID
WHERE PN.PRODUCT_ID    = :PID;

-- consolidatePartNumberEventTemporary(session, product);
-- network/REMOVE_CONS_PART_NUMBER_EVENTS.sql
DELETE FROM PBM_CONS_PART_NUMBER_EVENT_TMP WHERE PRODUCT_TYPE = :TYPE AND SERIAL_NUMBER = :SERIAL

-- removeTasksByTrigger(session, product);
-- network/REMOVE_TASKS_BY_TRIGGER.sql
MERGE INTO PBM_STEP_NETWORK_TMP S
USING PBM_CONS_PART_NUMBER_EVENT_TMP E
ON (S.PART_NUMBER_ID = E.PART_NUMBER_ID AND S.STEP_ID = E.STEP_ID)
WHEN MATCHED THEN UPDATE SET S.PN_CONNECTED = 'N'
WHERE S.PRODUCT_ID = :PID;

-- removeRemainingNonRecurrent(session, product);
-- network/REMOVE_REMAINING_NON_RECURRENT.sql
-- Query ofensora SQL_ID ahmdz2scz07kc
UPDATE PBM_STEP_NETWORK_TMP T
SET T.PN_CONNECTED = 'N'
WHERE T.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_STEP_NETWORK_TMP SN
  INNER JOIN PBM_STEP S
  ON S.STEP_ID            = SN.STEP_ID
  AND S.LOGICALLY_DELETED = 'N'
  AND S.SCENARIO_ID       = :SID
  AND S.RECURRENT         = 'N'
  INNER JOIN PBM_STEP_NETWORK_TMP SD
  ON SD.PART_NUMBER_ID   = SN.PART_NUMBER_ID
  AND SD.PRODUCT_ID      = SN.PRODUCT_ID
  AND SD.PN_CONNECTED    = 'N'
  WHERE SN.PRODUCT_ID    = :PID
  AND SN.STEP_NETWORK_ID = T.STEP_NETWORK_ID
  );

### updateOnLinkNetwork(session, product) até updateNextLinkNetwork(session, product);

-- updateOnLinkNetwork(session, product)
-- network/UPDATE_ON_LINK_NETWORK.sql
UPDATE PBM_LINK_NETWORK_TMP L
SET (
    L.TASK_ID,
    L.PN_CONNECTED
) = (
  SELECT
    S.STEP_NETWORK_ID,
    S.PN_CONNECTED
  FROM PBM_STEP_NETWORK_TMP S
  WHERE S.PART_NUMBER_ID = L.PART_NUMBER_ID
    AND S.STEP_ID = L.STEP_ID
)
WHERE L.PRODUCT_ID = :PID;

-- updateNextOnLinkNetwork(session, product);
-- network/UPDATE_NEXT_ON_LINK_NETWORK.sql

UPDATE PBM_LINK_NETWORK_TMP L
SET (
    L.NEXT_TASK_ID,
    L.NEXT_PN_CONNECTED
) = (
  SELECT
    S.STEP_NETWORK_ID,
    S.PN_CONNECTED
  FROM PBM_STEP_NETWORK_TMP S
  WHERE S.PART_NUMBER_ID = L.NEXT_PART_NUMBER_ID
    AND S.STEP_ID = L.NEXT_STEP_ID
)
WHERE L.PRODUCT_ID = :PID;

-- removeNonRecurringLinks(session, product);
-- network/REMOVE_NON_RECURRING_LINKS.sql

UPDATE PBM_LINK_NETWORK_TMP A
SET A.PN_CONNECTED    = 'N',
  A.NEXT_PN_CONNECTED = 'N'
WHERE A.PRODUCT_ID    = :PID
AND EXISTS
(
    SELECT
        T.LINK_NETWORK_ID
    FROM
    (
        SELECT
            L.LINK_NETWORK_ID
        FROM PBM_LINK_NETWORK_TMP L
        WHERE L.PRODUCT_ID = :PID
        MINUS
        SELECT
            LN.LINK_NETWORK_ID
        FROM PBM_LINK_NETWORK_TMP LN
        WHERE LN.PRODUCT_ID = :PID
            AND LN.PN_CONNECTED = 'Y'
                AND LN.NEXT_PN_CONNECTED = 'Y'
        CONNECT BY PRIOR LN.TASK_ID = LN.NEXT_TASK_ID
        START WITH LN.NEXT_TASK_ID IN
        (
            SELECT
                SN.STEP_NETWORK_ID
            FROM PBM_STEP_NETWORK_TMP SN
            INNER JOIN PBM_PART_NUMBER_TMP PN
            ON PN.PART_NUMBER_ID = SN.PART_NUMBER_ID
                AND PN.PRODUCT_ID = SN.PRODUCT_ID
            INNER JOIN
            (
                SELECT
                    A.PART_NUMBER
                FROM PBM_ASSEMBLY_POSITION A
                WHERE A.PRODUCT_ID = :PID
                MINUS
                SELECT
                    A.PART_NUMBER
                FROM PBM_REL_ASSEMBLY_POSITION RAP
                INNER JOIN PBM_ASSEMBLY_POSITION A
                ON A.ASSEMBLY_POSITION_ID = RAP.SOURCE_POSITION
                WHERE A.PRODUCT_ID = :PID
            ) U
            ON U.PART_NUMBER = PN.PART_NUMBER
        WHERE SN.PRODUCT_ID= :PID
        )
    ) T
  WHERE T.LINK_NETWORK_ID = A.LINK_NETWORK_ID
);

-- removeRemainingTasks(session, product);
-- network/REMOVE_REMAINING_TASKS.sql
UPDATE
    PBM_STEP_NETWORK_TMP S
SET S.PN_CONNECTED = 'N'
WHERE S.PRODUCT_ID = :PID
AND EXISTS
  (
    SELECT
        T.STEP_NETWORK_ID
    FROM
    (
        SELECT
            STEP_NETWORK_ID
        FROM PBM_STEP_NETWORK_TMP
        WHERE PRODUCT_ID = :PID
            AND PN_CONNECTED = 'Y'
        MINUS
        (
            SELECT
                TASK_ID
            FROM PBM_LINK_NETWORK_TMP
            WHERE PRODUCT_ID = :PID
                AND PN_CONNECTED = 'Y'
                    AND NEXT_PN_CONNECTED = 'Y'
            UNION
            SELECT
                NEXT_TASK_ID
            FROM PBM_LINK_NETWORK_TMP
            WHERE  PRODUCT_ID = :PID
                AND PN_CONNECTED = 'Y'
                    AND NEXT_PN_CONNECTED = 'Y'
        )
    ) T
  WHERE T.STEP_NETWORK_ID = S.STEP_NETWORK_ID
);

-- updateStepNetwork(session, product);
-- network/UPDATE_STEP_NETWORK.sql

DELETE FROM PBM_STEP_NETWORK_TMP WHERE PN_CONNECTED = 'N' AND PRODUCT_ID = :PID;

-- updateLinkNetwork(session, product);
-- network/UPDATE_LINK_NETWORK.sql

DELETE FROM PBM_LINK_NETWORK_TMP WHERE PN_CONNECTED = 'N' AND PRODUCT_ID = :PID;

-- updateNextLinkNetwork(session, product);
-- network/UPDATE_NEXT_LINK_NETWORK.sql

DELETE FROM PBM_LINK_NETWORK_TMP WHERE NEXT_PN_CONNECTED = 'N' AND PRODUCT_ID = :PID;

## stepNetwork.applyStepNetwork(product);

-- network/APPLY_STEP_NETWORK.sql
-- Query ofensora - SQL_ID 3hzxx2x4gnagb

INSERT
INTO PBM_STEP_NETWORK
  (
    STEP_NETWORK_ID,
    CYCLE,
    COD_TIPO_DAT_APL,
    APL_DATE,
    FINAL_DATE,
    PART_NUMBER_ID,
    PACKAGE_ID,
    STEP_ID,
    PN_CONNECTED,
    PRODUCT_ID
  )
SELECT SN.STEP_NETWORK_ID,
  SC.CYCLE,
  SN.COD_TIPO_DAT_APL,
  SN.APL_DATE,
  SN.FINAL_DATE,
  SN.PART_NUMBER_ID,
  SN.PACKAGE_ID,
  SN.STEP_ID,
  SN.PN_CONNECTED,
  SN.PRODUCT_ID
FROM PBM_STEP_NETWORK_TMP SN
JOIN PBM_STEP_CYCLES_TMP SC
ON SC.PART_NUMBER_ID = SN.PART_NUMBER_ID
AND SC.STEP_ID       = SN.STEP_ID
AND SC.PRODUCT_ID    = SN.PRODUCT_ID
WHERE SN.PRODUCT_ID  = :PID;

## linkNetwork.applyLinkNetwork(product);

-- network/APPLY_LINK_NETWORK.sql

INSERT
INTO PBM_LINK_NETWORK
  (
    LINK_NETWORK_ID,
    LAG,
    NEXT_PART_NUMBER_ID,
    NEXT_STEP_ID,
    PACKAGE_ID,
    PART_NUMBER_ID,
    STEP_ID,
    NEXT_PN_CONNECTED,
    PN_CONNECTED,
    NEXT_TASK_ID,
    TASK_ID,
    PRODUCT_ID
  )
SELECT LN.LINK_NETWORK_ID,
  LLT.LAG,
  LN.NEXT_PART_NUMBER_ID,
  LN.NEXT_STEP_ID,
  LN.PACKAGE_ID,
  LN.PART_NUMBER_ID,
  LN.STEP_ID,
  LN.NEXT_PN_CONNECTED,
  LN.PN_CONNECTED,
  LN.NEXT_TASK_ID,
  LN.TASK_ID,
  LN.PRODUCT_ID
FROM PBM_LINK_NETWORK_TMP LN
JOIN PBM_LINK_LAGS_TMP LLT
    ON LLT.PART_NUMBER_ID = LN.PART_NUMBER_ID
        AND LLT.NEXT_PART_NUMBER_ID = LN.NEXT_PART_NUMBER_ID
            AND LLT.MP_STEP_ID = LN.MP_STEP_ID
                AND LLT.MP_NEXT_STEP_ID = LN.MP_NEXT_STEP_ID
            		AND LLT.STEP_ID = LN.STEP_ID
                		AND LLT.NEXT_STEP_ID = LN.NEXT_STEP_ID
                    		AND LLT.PRODUCT_ID = LN.PRODUCT_ID
WHERE LN.PRODUCT_ID = :PID;

## stepNetwork.updateNetwork(product);

-- setAssemblyPositionCycle(productId);
UPDATE PBM_STEP_NETWORK S
SET S.CYCLE =
  (SELECT AP.LEADTIME
  FROM PBM_STEP_NETWORK SN
  INNER JOIN PBM_PART_NUMBER PN
  ON PN.PART_NUMBER_ID     =  SN.PART_NUMBER_ID
  AND PN.LOGICALLY_DELETED =  'N'
  AND PN.PRODUCT_ID        =  SN.PRODUCT_ID
  INNER JOIN PBM_ASSEMBLY_POSITION AP
  ON AP.PART_NUMBER      = PN.PART_NUMBER
  AND AP.PRODUCT_ID      = SN.PRODUCT_ID
  WHERE SN.PRODUCT_ID    = :PID
  AND SN.STEP_NETWORK_ID = S.STEP_NETWORK_ID )
WHERE S.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_STEP_NETWORK SN
  INNER JOIN PBM_PART_NUMBER PN
  ON PN.PART_NUMBER_ID     = SN.PART_NUMBER_ID
  AND PN.LOGICALLY_DELETED = 'N'
  AND PN.PRODUCT_ID        = SN.PRODUCT_ID
  INNER JOIN PBM_ASSEMBLY_POSITION AP
  ON AP.PART_NUMBER      = PN.PART_NUMBER
  AND AP.PRODUCT_ID      = SN.PRODUCT_ID
  WHERE SN.PRODUCT_ID    = :PID
  AND SN.STEP_NETWORK_ID = S.STEP_NETWORK_ID );

-- setAssemblyPositionLag(productId);
UPDATE PBM_LINK_NETWORK A
SET A.LAG =
  (SELECT R.LAG
  FROM PBM_REL_ASSEMBLY_POSITION R
  INNER JOIN
    (SELECT LN.LINK_NETWORK_ID,
      SA.ASSEMBLY_POSITION_ID AS SOURCE_POSITION,
      DA.ASSEMBLY_POSITION_ID AS DESTINY_POSITION
    FROM PBM_LINK_NETWORK LN
    INNER JOIN PBM_PART_NUMBER PNS
    ON PNS.PART_NUMBER_ID     = LN.PART_NUMBER_ID
    AND PNS.PRODUCT_ID        = LN.PRODUCT_ID
    AND PNS.LOGICALLY_DELETED = 'N'
    INNER JOIN PBM_PART_NUMBER PND
    ON PND.PART_NUMBER_ID     = LN.NEXT_PART_NUMBER_ID
    AND PND.PRODUCT_ID        = LN.PRODUCT_ID
    AND PND.LOGICALLY_DELETED = 'N'
    INNER JOIN PBM_ASSEMBLY_POSITION DA
    ON DA.PART_NUMBER = PND.PART_NUMBER
    AND DA.PRODUCT_ID = PND.PRODUCT_ID
    INNER JOIN PBM_ASSEMBLY_POSITION SA
    ON SA.PART_NUMBER        = PNS.PART_NUMBER
    AND SA.PRODUCT_ID        = PNS.PRODUCT_ID
    WHERE LN.PRODUCT_ID      = :PID
    ) T ON T.SOURCE_POSITION = R.SOURCE_POSITION
  AND T.DESTINY_POSITION     = R.DESTINY_POSITION
  WHERE A.LINK_NETWORK_ID    = T.LINK_NETWORK_ID
  )
WHERE A.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_LINK_NETWORK LN
  INNER JOIN PBM_PART_NUMBER PNS
  ON PNS.PART_NUMBER_ID     = LN.PART_NUMBER_ID
  AND PNS.PRODUCT_ID        = LN.PRODUCT_ID
  AND PNS.LOGICALLY_DELETED = 'N'
  INNER JOIN PBM_PART_NUMBER PND
  ON PND.PART_NUMBER_ID     = LN.NEXT_PART_NUMBER_ID
  AND PND.PRODUCT_ID        = LN.PRODUCT_ID
  AND PND.LOGICALLY_DELETED = 'N'
  INNER JOIN PBM_ASSEMBLY_POSITION DA
  ON DA.PART_NUMBER = PND.PART_NUMBER
  AND DA.PRODUCT_ID = PND.PRODUCT_ID
  INNER JOIN PBM_ASSEMBLY_POSITION SA
  ON SA.PART_NUMBER      = PNS.PART_NUMBER
  AND SA.PRODUCT_ID      = PNS.PRODUCT_ID
  WHERE LN.PRODUCT_ID    = :PID
  AND LN.LINK_NETWORK_ID = A.LINK_NETWORK_ID
  );

-- setPackageCycle(productId);
UPDATE PBM_STEP_NETWORK S
SET S.CYCLE        = 0
WHERE S.PRODUCT_ID = :PID
AND EXISTS
  (SELECT *
  FROM PBM_STEP_NETWORK SN
  INNER JOIN PBM_PART_NUMBER PN
  ON PN.PART_NUMBER_ID     = SN.PART_NUMBER_ID
  AND PN.PRODUCT_ID        = SN.PRODUCT_ID
  AND PN.LOGICALLY_DELETED = 'N'
  INNER JOIN PBM_PACKAGE PK
  ON PK.PART_NUMBER      = PN.PART_NUMBER
  AND PK.PRODUCT_ID      = PN.PRODUCT_ID
  WHERE SN.PRODUCT_ID    = :PID
  AND SN.STEP_NETWORK_ID = S.STEP_NETWORK_ID);