User:Kumki/sandbox

CREATE OR REPLACE PROCEDURE PLAN_ETL.PRC_LOAD_SCS_BOM_ROUTE_CLEAN AS /*************************************************************************************************************************************  **   **         This procedure truncates and reloads stg_scs_route_clean,stg_scs_bom_clean  tables and loading calendardetail. **  ** - --Modification History -- **                 STARs or   **  Date           Programmer              SIR Num                               Description ** ========     ==============            =========                  ===========================================   ** 08/20/2013    ABHINAV SINGH            Initial write                   To truncate and populate stg_scs_route_clean, **                                                                       stg_scs_bom_clean from  scs_route and scs_bom **  **   *************************************************************************************************************************************/   /************************************************************************************************************************************   ** Source Table      : ** External Table   : ** Staging Table    : ** Temp Table       : stg_scs_bom_clean,stg_scs_route_clean ** Final Table      : validation_result ** Log Table        : scdb_loader_log *****************************************************************************************************/

/****************************************************************************************************  ** Declare variables                                                                                                                                                                          * ****************************************************************************************************/  v_insert_count        NUMBER := 0; v_update_count       NUMBER := 0; v_delete_count       NUMBER := 0; v_error_count        NUMBER := 0; v_rowcount           NUMBER := 0; v_start_time         DATE; v_start_time_ch      VARCHAR2 (30); v_end_time           DATE; v_end_time_ch        VARCHAR2 (30); V_Process_Id         NUMBER; v_process_type       VARCHAR2 (30); v_information        VARCHAR2 (1000); /* current_process identifies the block of code where exception is raised, used for debugging */ v_current_process    VARCHAR2 (100) := NULL; v_error_code         VARCHAR2 (9) := NULL; v_error_text         scdb_loader_log.information%TYPE := NULL; v_error_msg          VARCHAR2 (500) := NULL; v_sql                VARCHAR2 (32000) := NULL; /*Variables specific to application.*/ v_module_name        VARCHAR2 (30) := 'PRC_LOAD_SCS_BOM_ROUTE_CLN'; v_table1_name        VARCHAR2 (40) := 'stg_scs_bom_clean'; v_table2_name        VARCHAR2 (40) := 'stg_scs_route_clean'; v_table_name         VARCHAR2 (40) := 'validation_results';

scdb_loader_log_err  EXCEPTION; procedure_err        EXCEPTION; BEGIN DBMS_OUTPUT.ENABLE (1000000); /***********************************************************************************************  ** Get the Start Time of the Procedure and add the entry into the AUDIT Table                  * ***********************************************************************************************/

v_current_process := 'Audit Insert'; v_start_time := SYSDATE; v_start_time_ch := TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'); v_process_type := 'INSERT';

SELECT scdb_loader_log_seq.NEXTVAL INTO v_process_id FROM DUAL;

DBMS_OUTPUT.PUT_LINE (     v_module_name || ' Procedure Started: ' || v_start_time_ch);

BEGIN INSERT INTO scdb_loader_log (process_name,                                  begin_date,                                   end_date,                                   elapse,                                   information,                                   error_count,                                   insert_count,                                   update_count,                                   delete_count,                                   source_name,                                   target_name,                                   created_by,                                   process_id,                                   process_type,                                   source_count,                                   target_count) VALUES (v_module_name,                  v_start_time,                   NULL,                   0,                   NULL,                   NULL,                   0,                   0,                   0,                   NULL,                   NULL,                   v_module_name,                   v_process_id,                   v_process_type,                   NULL,                   NULL); EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE scdb_loader_log_err; END;

COMMIT;

/*Truncate stg_scs_bom_clean and Reload it from scs_bom*/

BEGIN v_current_process := 'Truncate table ' || v_table1_name;

DBMS_OUTPUT.PUT_LINE (           'Starting '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

EXECUTE IMMEDIATE 'truncate table ' || v_table1_name;

DBMS_OUTPUT.PUT_LINE (           'Completed '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

/*Insert into stg_scs_bom_clean*/ v_current_process := 'INSERT INTO ' || v_table1_name;

INSERT INTO stg_scs_bom_clean (build_priority,                                    order_priority,                                     mcm_priority,                                     produced_material_id,                                     consumed_material_id,                                     primary_material_id,                                     consumed_qty,                                     bom_comment,                                     bom_group,                                     bin_grade,                                     bin_yield,                                     bomgrp_priority,                                     delete_flag,                                     reason_code,                                     produced_material,                                     consumed_material,                                     primary_material,                                     build_priority_new, bom_group_new, bomgrp_priority_new)        (SELECT build_priority, order_priority, mcm_priority, produced_material_id, consumed_material_id, primary_material_id, consumed_qty, bom_comment, bom_group, bin_grade, bin_yield, bomgrp_priority, 'N', NULL, a.material, b.material, c.material, NULL, NULL, NULL FROM PLAN_ETL.stg_scs_bom f,                PLAN_ETL.scs_material a,                 PLAN_ETL.scs_material b,                 PLAN_ETL.scs_material c           WHERE     f.produced_material_id = a.material_id(+) AND f.consumed_material_id = b.material_id(+) AND f.primary_material_id = c.material_id(+));

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE procedure_err; END;

/*Truncate and reload stg_scs_route_clean*/

BEGIN v_current_process := 'Truncate table ' || v_table2_name; DBMS_OUTPUT.PUT_LINE (           'Starting '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

EXECUTE IMMEDIATE 'truncate table ' || v_table2_name;

DBMS_OUTPUT.PUT_LINE (           'Completed '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

v_current_process := 'INSERT INTO ' || v_table2_name;

/*Insert into stg_scs_route_clean*/ INSERT INTO stg_scs_route_clean (route_group,                                      fact_seq,                                       operation_id,                                       produced_material_id,                                       plan_item_id,                                       uom,                                       site_id,                                       cycle_time,                                       yield,                                       sbe_stop_build,                                       make_stop_build,                                       fact_resp,                                       process_step,                                       route_comment,                                       routegrp_priority,                                       delete_flag,                                       reason_code,                                       routegrp_priority_new, operation, site, produced_material, plan_item, route_group_new)        (SELECT route_group, fact_seq, route.operation_id, produced_material_id, plan_item_id, uom, route.site_id, cycle_time, yield, sbe_stop_build, make_stop_build, fact_resp, process_step, route_comment, routegrp_priority, 'N', NULL, NULL, opr.operation operation, sit.site site, mat.material produced_material, pln.material plan_item, NULL FROM PLAN_ETL.stg_scs_route route, PLAN_ETL.scs_material mat, PLAN_ETL.scs_material pln, PLAN_ETL.scs_site sit, PLAN_ETL.scs_operation opr WHERE    route.operation_id = opr.operation_id(+) AND route.site_id = sit.site_id(+) AND route.produced_material_id = mat.material_id(+) AND route.plan_item_id = pln.material_id(+));

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE procedure_err; END;

BEGIN /*Update delete flag to Y and reason code to 6009 in stg_scs_bom_clean if consumed_material_id is matching with the produced_material_id and inserting the corresponding values into the validation_results table*/

v_current_process := 'Truncate table ' || v_table_name; DBMS_OUTPUT.PUT_LINE (           'Starting '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

EXECUTE IMMEDIATE 'truncate table ' || v_table_name;

DBMS_OUTPUT.PUT_LINE (           'Completed '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

v_current_process := 'Consume/Route: produced and consumed are same';

UPDATE stg_scs_bom_clean SET delete_flag = 'Y', reason_code = 6009 WHERE consumed_material_id = produced_material_id;

INSERT INTO PLAN_ETL.validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) (SELECT 'Mapping_errors' AS system_id,                '6009' AS code,                 'Consume/Route: produced and consumed are same'                    AS description,                 produced_material AS item,                 NULL AS location,                 'Y' AS bom_rejected            FROM PLAN_ETL.stg_scs_bom_clean           WHERE consumed_material_id = produced_material_id);

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

/*Update delete flag Y and reason_code 6000 in stg_scs_bom_clean for the mismatch between planning_facility and facility in clean table and stg_mfl_ctl and inserting the corresponding values into the validation_results table*/ v_current_process := 'Consume/Route: Device, Generic, Assembly item planning facility does not match WOPCNTL ';

MERGE INTO PLAN_ETL.stg_scs_bom_clean tgt USING (SELECT bom.produced_material                   FROM PLAN_ETL.scs_material matl, PLAN_ETL.stg_scs_bom_clean bom, PLAN_ETL.item it                   WHERE bom.produced_material = matl.material                         AND it.item_id = matl.old_material                         AND matl.component_type IN                                ('DEVICE', 'GENERIC', 'ASSEMBLY')                         AND it.fac_am_sal <> matl.planning_facility) src ON (tgt.produced_material = tgt.produced_material) WHEN MATCHED THEN UPDATE SET delete_flag = 'Y', reason_code = 6000;

INSERT INTO PLAN_ETL. validation_results (system_id,                                     code,                                      item,                                      location,                                      description,                                      bom_rejected,                                      route_rejected,                                      refresh_dttm) SELECT 'Mapping_errors' AS system_id, '6000' AS code, 'Consume/Route: Device, Generic, Assembly item planning facility does not match WOPCNTL ' AS description, bom.produced_material AS item, NULL AS location, 'Y' AS bom_rejected, NULL AS route_rejected, SYSDATE AS refresh_dttm FROM scs_material matl, stg_scs_bom_clean bom, item it         WHERE     bom.produced_material = matl.material AND it.item_id = matl.old_material AND matl.component_type IN ('DEVICE', 'GENERIC', 'ASSEMBLY') AND it.fac_am_sal <> matl.planning_facility;

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

/*Updating delete flag Y and reason_code 6001 in stg_scs_bom_clean if produced_material_id doesn't match with plan_item_id or consume_material_id doesn't match with plan_item_id*/

v_current_process := 'Consume/Route: produced and/or consumed materials do not have routes';

/*THIRD*/ /*CASE1*/ MERGE INTO PLAN_ETL.stg_scs_bom_clean tgt USING (SELECT DISTINCT a.produced_material_id                   FROM PLAN_ETL.stg_scs_bom_clean a                   WHERE NOT EXISTS                                (SELECT 1 FROM PLAN_ETL.stg_scs_route_clean b                                 WHERE (a.produced_material_id =                                            b.produced_material_id                                         OR a.produced_material_id =                                               b.plan_item_id))) src ON (tgt.produced_material_id = src.produced_material_id) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6001;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) (SELECT 'Mapping_errors' AS system_id,                '6001' AS code,                 'Consume/Route: produced and/or consumed materials do not have routes'                    AS description,                 produced_material AS item,                 NULL AS location,                 'Y' AS bom_rejected            FROM stg_scs_bom_clean           WHERE produced_material_id IN                    (SELECT DISTINCT a.produced_material_id FROM stg_scs_bom_clean a                     WHERE NOT EXISTS (SELECT 1                                     FROM stg_scs_route_clean b                                     WHERE (a.produced_material_id = b.produced_material_id OR a.produced_material_id = b.plan_item_id))));

/*CASE 2*/

MERGE INTO stg_scs_bom_clean tgt USING (SELECT DISTINCT a.produced_material_id                   FROM stg_scs_bom_clean a                   WHERE NOT EXISTS                                (SELECT 1 FROM stg_scs_route_clean b                                 WHERE (a.consumed_material_id =                                            b.produced_material_id                                         OR a.consumed_material_id =                                               b.plan_item_id))) src ON (tgt.produced_material_id = src.produced_material_id) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6001;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) SELECT 'Mapping_errors' AS system_id, '6001' AS code, 'Consume/Route: produced and/or consumed materials do not have routes' AS description, produced_material AS item, NULL AS location, 'Y' AS bom_rejected FROM stg_scs_bom_clean WHERE produced_material_id IN                  (SELECT DISTINCT a.produced_material_id                      FROM stg_scs_bom_clean a                     WHERE NOT EXISTS                                  (SELECT 1 FROM stg_scs_route_clean b                                   WHERE (a.consumed_material_id =                                              b.produced_material_id                                           OR a.consumed_material_id =                                                 b.plan_item_id)));

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

/*Update delete flag Y and reason_code 6001 for stg_scs_route_clean for plan_item in stg_scs_route_clean (where route_group does not have CREATE or CREATE2 as fac_seq 1) has corresponding records in stg_scs_bom_clean with plan_item as produced_material*/

v_current_process := 'Consume/Route: plan item of the route is not produced by any BOM';

/*FOURTH*/

MERGE INTO stg_scs_route_clean tgt USING (SELECT DISTINCT b.produced_material_id, b.route_group                   FROM stg_scs_route_clean b                   WHERE b.plan_item_id NOT IN                            (SELECT DISTINCT produced_material_id FROM stg_scs_bom_clean)                        AND NOT EXISTS                                    (SELECT 1 FROM stg_scs_route_clean c                                     WHERE     c.operation LIKE ('CREATE%') AND c.fact_seq = 1 AND b.plan_item = c.plan_item AND b.route_group = c.route_group)) src ON (tgt.produced_material_id = src.produced_material_id                 AND tgt.route_group = src.route_group) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6002;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) SELECT 'Mapping_errors' AS system_id, '6002' AS code, 'Consume/Route: plan item of the route is not produced by any BOM' AS description, b.produced_material AS item, NULL AS location, 'Y' AS bom_rejected FROM stg_scs_route_clean b         WHERE produced_material_id IN                   (SELECT DISTINCT b.produced_material_id                      FROM stg_scs_route_clean b                     WHERE b.plan_item_id NOT IN                              (SELECT DISTINCT produced_material_id FROM stg_scs_bom_clean)                          AND NOT EXISTS                                      (SELECT 1 FROM stg_scs_route_clean c                                       WHERE c.operation LIKE ('CREATE%') AND c.fact_seq = 1 AND b.plan_item = c.plan_item AND b.route_group = c.route_group));

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

/*Update delete flag Y and reason code 6011 into stg_scs_route and stg_scs_bom_clean for CPW in stg_mfl_chip_data is null or 0 for operations CREATE, CREATE2 and CUSTOMIZE*/

v_current_process := 'Item Operation CPW is set to ZERO';

/*FIFTH DOUBT*/

/* case 1 for stg_scs_route_clean */ MERGE INTO stg_scs_route_clean tgt USING (SELECT DISTINCT produced_material                   FROM stg_scs_route_clean                   WHERE operation IN ('CREATE', 'CREATE2', 'CUSTOMIZE')                         AND plan_item IN                                (SELECT chip_item FROM stg_mfl_chip_data WHERE chip_cpw IS NULL OR chip_cpw = 0)) src ON (tgt.produced_material = src.produced_material) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6011;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) (SELECT 'Mapping_errors' AS system_id,                '6011' AS code,                 'Item Operation CPW is set to ZERO' AS description,                 produced_material AS item,                 NULL AS location,                 'Y' AS bom_rejected            FROM stg_scs_route_clean           WHERE produced_material IN                    (SELECT DISTINCT produced_material FROM stg_scs_route_clean WHERE operation IN ('CREATE', 'CREATE2', 'CUSTOMIZE') AND plan_item IN                                  (SELECT chip_item                                      FROM stg_mfl_chip_data                                     WHERE chip_cpw IS NULL OR chip_cpw = 0)));

/* case 2 for stg_scs_bom_clean */

MERGE INTO stg_scs_bom_clean tgt USING (SELECT DISTINCT b.produced_material                   FROM stg_scs_route_clean a, stg_scs_bom_clean b                   WHERE a.plan_item = b.produced_material                         AND a.operation IN                                ('CREATE', 'CREATE2', 'CUSTOMIZE')                         AND a.plan_item IN                                (SELECT chip_item FROM stg_mfl_chip_data WHERE chip_cpw IS NULL OR chip_cpw = 0)) src ON (tgt.produced_material = src.produced_material) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6011;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) SELECT 'Mapping_errors' AS system_id, '6011' AS code, 'Item Operation CPW is set to ZERO' AS description, produced_material AS item, NULL AS location, 'Y' AS bom_rejected FROM stg_scs_route_clean WHERE produced_material IN                  (SELECT DISTINCT b.produced_material                      FROM stg_scs_route_clean a, stg_scs_bom_clean b                     WHERE a.plan_item = b.produced_material                           AND a.operation IN                                  ('CREATE', 'CREATE2', 'CUSTOMIZE')                           AND a.plan_item IN                                  (SELECT chip_item FROM stg_mfl_chip_data WHERE chip_cpw IS NULL OR chip_cpw = 0));

COMMIT;

/*Update delete flag Y and reason code 6010 for stg_scs_route_clean if cycle_time yield are null or 0 for operations other than BINNING/DOWNBIN*/ v_current_process := 'Item Operation Cycle Time or Yield is set to Zero';

/*SIXTH*/

MERGE INTO stg_scs_route_clean tgt USING (SELECT produced_material,                        route_group,                         fact_seq,                         operation_id,                         plan_item                    FROM stg_scs_route_clean                   WHERE operation NOT IN ('BINNING', 'DOWNBIN')                         AND (   cycle_time IS NULL OR cycle_time = 0 OR yield = 0 OR yield IS NULL)) src ON (   tgt.produced_material = src.produced_material                  AND tgt.route_group = src.route_group                  AND tgt.fact_seq = src.fact_seq                  AND tgt.operation_id = src.operation_id                  AND tgt.plan_item = src.plan_item) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6010;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      route_rejected) SELECT 'Mapping_errors' AS system_id, '6010' AS code, 'Item Operation Cycle Time or Yield is set to Zero' AS description, plan_item AS item, NULL AS location, 'Y' AS route_rejected FROM stg_scs_route_clean WHERE (produced_material,                route_group,                 fact_seq,                 operation_id,                 plan_item) IN                   (SELECT produced_material,                           route_group,                           fact_seq,                           operation_id,                           plan_item                      FROM stg_scs_route_clean                     WHERE operation NOT IN ('BINNING', 'DOWNBIN')                           AND (   cycle_time IS NULL OR cycle_time = 0 OR yield = 0 OR yield IS NULL));

/* case 1 for stg_scs_bom_clean t o sycn up the route and bom tables */

MERGE INTO stg_scs_bom_clean tgt USING (SELECT DISTINCT a.produced_material_id                   FROM stg_scs_bom_clean a                   WHERE NOT EXISTS                                (SELECT 1 FROM stg_scs_route_clean b                                 WHERE (a.produced_material_id =                                            b.produced_material_id                                         OR a.produced_material_id =                                               b.plan_item_id) AND b.delete_flag = 'N')) src ON (tgt.produced_material_id = src.produced_material_id) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6010;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) SELECT 'Mapping_errors' AS system_id, '6010' AS code, 'Item Operation Cycle Time or Yield is set to Zero' AS description, produced_material AS item, NULL AS location, 'Y' AS bom_rejected FROM stg_scs_bom_clean WHERE (produced_material_id) IN                  (SELECT DISTINCT a.produced_material_id                      FROM stg_scs_bom_clean a                     WHERE NOT EXISTS                                  (SELECT 1 FROM stg_scs_route_clean b                                   WHERE (a.produced_material_id =                                              b.produced_material_id                                           OR a.produced_material_id =                                                 b.plan_item_id) AND b.delete_flag = 'N'));

/* case 2 for stg_scs_bom_clean  to sycn up the route and bom tables */

MERGE INTO stg_scs_bom_clean tgt USING (SELECT DISTINCT a.produced_material_id                   FROM stg_scs_bom_clean a                   WHERE NOT EXISTS                                (SELECT 1 FROM stg_scs_route_clean b                                 WHERE (a.consumed_material_id =                                            b.produced_material_id                                         OR a.consumed_material_id =                                               b.plan_item_id) AND b.delete_flag = 'N')) src ON (tgt.produced_material_id = src.produced_material_id) WHEN MATCHED THEN UPDATE SET tgt.delete_flag = 'Y', tgt.reason_code = 6010;

INSERT INTO validation_results (system_id,                                     code,                                      description,                                      item,                                      location,                                      bom_rejected) SELECT 'Mapping_errors' AS system_id, '6010' AS code, 'Item Operation Cycle Time or Yield is set to Zero' AS description, produced_material AS item, NULL AS location, 'Y' AS bom_rejected FROM stg_scs_bom_clean WHERE (produced_material_id) IN                  (SELECT DISTINCT a.produced_material_id                      FROM stg_scs_bom_clean a                     WHERE NOT EXISTS                                  (SELECT 1 FROM stg_scs_route_clean b                                   WHERE (a.consumed_material_id =                                              b.produced_material_id                                           OR a.consumed_material_id =                                                 b.plan_item_id) AND b.delete_flag = 'N'));

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

BEGIN v_current_process := 'Execute prc_load_6012_rejects_dev';

PRC_LOAD_6012_REJECTS_DEV; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE procedure_err; END;

/*Update delete flag Y and reason_code 6007 for stg_scs_route_clean to check SYMBOL operation takes place at the same location*/

v_current_process := 'SYMBOL operation with no matching DOWNBIN operation at the same location';

MERGE INTO stg_scs_route_clean tgt USING (SELECT route.plan_item,                        route.site,                         route.route_group,                         route.produced_material                    FROM stg_scs_bom_clean bom,                         stg_scs_route_clean route,                         stg_scs_route_clean route2                   WHERE     route.operation LIKE '%SYMBOL%'                         AND route.plan_item = bom.produced_material                         AND bom.consumed_material = route2.plan_item                         AND route2.operation LIKE '%BINNING%'                         AND route.site <> route2.site                         AND route.route_group = route2.route_group) src ON (   src.produced_material = tgt.produced_material                  AND src.plan_item = tgt.plan_item                  AND src.route_group = tgt.route_group                  AND src.site = tgt.site) WHEN MATCHED THEN UPDATE SET delete_flag = 'Y', reason_code = 6007;

INSERT INTO validation_results (system_id,                                     code,                                      item,                                      location,                                      description,                                      bom_rejected,                                      route_rejected,                                      refresh_dttm) SELECT 'Mapping_errors' AS system_id, '6007' AS code, plan_item AS item, NULL AS location, 'SYMBOL operation with no matching DOWNBIN operation at the same location' AS description, NULL AS bom_rejected, 'Y' AS route_rejected, SYSDATE AS refresh_dttm FROM stg_scs_route_clean route WHERE plan_item IN                  (SELECT route.plan_item                      FROM stg_scs_bom_clean bom,                           stg_scs_route_clean route,                           stg_scs_route_clean route2                     WHERE     route.operation LIKE '%SYMBOL%'                           AND route.plan_item = bom.produced_material                           AND bom.consumed_material = route2.plan_item                           AND route2.operation LIKE '%BINNING%'                           AND route.site <> route2.site                           AND route.route_group = route2.route_group);

v_insert_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE procedure_err; END;

BEGIN v_current_process := 'Resequence bom_group and bomgrp_priority, and build_priority';

MERGE INTO stg_scs_bom_clean tgt USING (SELECT DENSE_RANK                         OVER ( PARTITION BY produced_material ORDER BY                              build_priority, produced_material, consumed_material ASC)                           AS build_priority_new,                         DENSE_RANK                          OVER (PARTITION BY produced_material_id ORDER BY bom_group)                           AS bomgrp_priority_new,                         produced_material_id,                         consumed_material_id,                         primary_material_id,                         bom_group                    FROM stg_scs_bom_clean                   WHERE delete_flag = 'N') src ON (   src.produced_material_id = tgt.produced_material_id                  AND src.consumed_material_id = tgt.consumed_material_id                  AND src.primary_material_id = tgt.primary_material_id                  AND src.bom_group = tgt.bom_group) WHEN MATCHED THEN UPDATE SET tgt.build_priority_new = src.build_priority_new, tgt.bomgrp_priority_new = src.bomgrp_priority_new;

v_update_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT;

v_current_process := 'Resequence route_group and routegrp_priority  ';

MERGE INTO stg_scs_route_clean tgt USING (SELECT DENSE_RANK                         OVER (PARTITION BY plan_item_id ORDER BY route_group)                           routegrp_priority_new,                         SITE_ID,                         PRODUCED_MATERIAL_ID,                         FACT_SEQ,                         ROUTE_GROUP                    FROM stg_scs_route_clean                   WHERE delete_flag = 'N') src ON (   src.SITE_ID = tgt.SITE_ID                  AND src.PRODUCED_MATERIAL_ID = tgt.PRODUCED_MATERIAL_ID                  AND src.FACT_SEQ = tgt.FACT_SEQ                  AND src.ROUTE_GROUP = tgt.ROUTE_GROUP) WHEN MATCHED THEN UPDATE SET tgt.routegrp_priority_new = src.routegrp_priority_new;

v_update_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE (v_current_process || ' ' || SQL%ROWCOUNT); COMMIT; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE procedure_err; END;

/****************************************************************  ** GATHERING STATS  * ****************************************************************/

v_current_process := 'gathering stats';

BEGIN DBMS_OUTPUT.PUT_LINE (           'Starting '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS')); v_error_text := analyze_table (v_table_name);

IF v_error_text IS NOT NULL THEN v_error_count := v_error_count + 1; RAISE procedure_err; END IF;

DBMS_OUTPUT.PUT_LINE (           'Completed '         || v_current_process         || ' '         || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS')); END;

COMMIT;

DBMS_OUTPUT.put_line (     ' Processing of tmp_resource_calendars_prc ended at : '      || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS'));

/************************************************************************************************ ** Get the Ending Time of the Procedure and add the entry into the AUDIT Table                 * ***********************************************************************************************/

v_current_process := 'Audit Update';

BEGIN                                             /* Update AUDIT table */ SELECT SYSDATE, TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS') INTO v_end_time, v_end_time_ch FROM DUAL;

v_information := 'Completed Successfully';

UPDATE PLAN_ETL.scdb_loader_log SET error_count = 0, insert_count = v_insert_count, update_count = v_update_count, delete_count = v_delete_count, information = v_information WHERE process_id = v_process_id; EXCEPTION WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); RAISE scdb_loader_log_err; END;                                              /* Update AUDIT table */

COMMIT; DBMS_OUTPUT.PUT_LINE (     v_module_name || ' Processing Complete ' || v_end_time_ch); /***********************************************************************************************     EXCEPTION BLOCK EXCEPTION WHEN procedure_err THEN v_error_msg := 'Error in ' || v_current_process || ': '        || v_error_code || ': '        || v_error_text;

SELECT SYSDATE, TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS') INTO v_end_time, v_end_time_ch FROM DUAL;

DBMS_OUTPUT.PUT_LINE (v_error_msg || ' ' || v_end_time_ch);

BEGIN UPDATE scdb_loader_log SET error_count = v_error_count, insert_count = v_insert_count, update_count = v_update_count, delete_count = v_delete_count, information = v_error_msg WHERE process_id = v_process_id; END;

COMMIT; WHEN scdb_loader_log_err THEN v_error_msg := 'Error in ' || v_current_process || ': '        || v_error_code || ': '        || v_error_text;

SELECT SYSDATE, TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS') INTO v_end_time, v_end_time_ch FROM DUAL;

DBMS_OUTPUT.PUT_LINE (v_error_msg || ' ' || v_end_time_ch); raise_application_error (-20112, 'Audit Table Error Encountered'); WHEN OTHERS THEN v_error_count := v_error_count + 1; v_error_code := TO_CHAR (SQLCODE); v_error_text := SUBSTR (SQLERRM, 1, 110); v_error_msg := 'Error in ' || v_current_process || ': '        || v_error_code || ': '        || v_error_text;

SELECT SYSDATE, TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS') INTO v_end_time, v_end_time_ch FROM DUAL;

DBMS_OUTPUT.PUT_LINE (           v_module_name         || ' Ended with errors '         || v_error_msg         || ' '         || v_end_time_ch);

BEGIN UPDATE scdb_loader_log SET error_count = v_error_count, insert_count = v_insert_count, update_count = v_update_count, delete_count = v_delete_count, information = v_error_msg WHERE process_id = v_process_id; END;

COMMIT; END; /

exec PRC_LOAD_SCS_BOM_ROUTE_CLEAN