Drop files here

SQL upload ( 0 ) x -

Page-related settings Click on the bar to scroll to top of page
Press Ctrl+Enter to execute query Press Enter to execute query
ascending
descending
Order:
Debug SQL
Count
Execution order
Time taken
Order by:
Group queries
Ungroup queries
Collapse Expand Show trace Hide trace Count Time taken
Bookmarks
Refresh
Add
No bookmarks
Add bookmark
Options
Set default





Collapse Expand Requery Edit Explain Profiling Bookmark Query failed Database : Queried time :
Customize browse mode.
Browse mode
Documentation Use only icons, only text or both. Restore default value
Documentation Use only icons, only text or both. Restore default value
Documentation Whether a user should be displayed a "show all (rows)" button. Restore default value
Documentation Number of rows displayed when browsing a result set. If the result set contains more rows, "Previous" and "Next" links will be shown. Restore default value
Documentation SMART - i.e. descending order for columns of type TIME, DATE, DATETIME and TIMESTAMP, ascending order otherwise. Restore default value
Documentation Highlight row pointed by the mouse cursor. Restore default value
Documentation Highlight selected rows. Restore default value
Documentation Restore default value
Documentation Restore default value
Documentation Repeat the headers every X cells, 0 deactivates this feature. Restore default value
Documentation Maximum number of characters shown in any non-numeric column on browse view. Restore default value
Documentation These are Edit, Copy and Delete links. Restore default value
Documentation Whether to show row links even in the absence of a unique key. Restore default value
Documentation Default sort order for tables with a primary key. Restore default value
Documentation When browsing tables, the sorting of each table is remembered. Restore default value
Documentation For display Options Restore default value 
SELECT * FROM `proc`
[ Edit inline ] [ Edit ] [ Explain SQL ] [ Create PHP code ] [ Refresh ]
|
|
|
|
Filter rows:
Sort by key:
Full texts db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'test' AND `proc`.`name` = 'AddGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
 		test AddGeometryColumn PROCEDURE AddGeometryColumn SQL CONTAINS_SQL NO DEFINER 
catalog varchar(64), t_schema varchar(64),
   t_name varchar(64), geometry_column varchar(64), t_srid int

begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
@ 2015-11-24 22:42:53 2015-11-24 22:42:53 latin1 latin1_swedish_ci latin1_swedish_ci 
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'test' AND `proc`.`name` = 'DropGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
 		test DropGeometryColumn PROCEDURE DropGeometryColumn SQL CONTAINS_SQL NO DEFINER 
catalog varchar(64), t_schema varchar(64),
   t_name varchar(64), geometry_column varchar(64)

begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
@ 2015-11-24 22:42:53 2015-11-24 22:42:53 latin1 latin1_swedish_ci latin1_swedish_ci 
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spFetchData' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spFetchData PROCEDURE spFetchData SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255), IN `stage_nos` VARCHAR(255)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val INT;
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    DECLARE cur CURSOR FOR
        SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id
        WHERE 
            FIND_IN_SET(ps.part_id, part_ids) > 0
            AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val, no_of_stages_val;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Call the second procedure to process the fetched data
        CALL spProcessData(part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val, no_of_stages_val);
    END LOOP;

    CLOSE cur;
END
root@localhost 2025-08-22 06:30:30 2025-08-22 06:30:30 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val INT;
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    DECLARE cur CURSOR FOR
        SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id
        WHERE 
            FIND_IN_SET(ps.part_id, part_ids) > 0
            AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val, no_of_stages_val;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Call the second procedure to process the fetched data
        CALL spProcessData(part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val, no_of_stages_val);
    END LOOP;

    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spProcessOANumbers' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spProcessOANumbers PROCEDURE spProcessOANumbers SQL CONTAINS_SQL NO DEFINER 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oa_no_val VARCHAR(100);

    -- Declare cursor for distinct oa_no values
    DECLARE cur CURSOR FOR
        SELECT DISTINCT oa_no FROM proj_detl;

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variable
        FETCH cur INTO oa_no_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Call spStatusInsert for each oa_no
        CALL spStatusInsert(oa_no_val);
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2025-08-22 06:30:54 2025-08-22 06:30:54 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oa_no_val VARCHAR(100);

    -- Declare cursor for distinct oa_no values
    DECLARE cur CURSOR FOR
        SELECT DISTINCT oa_no FROM proj_detl;

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variable
        FETCH cur INTO oa_no_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Call spStatusInsert for each oa_no
        CALL spStatusInsert(oa_no_val);
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spStatusUpdate' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spStatusUpdate PROCEDURE spStatusUpdate SQL CONTAINS_SQL NO DEFINER 
IN `oa` VARCHAR(100)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2025-08-22 06:31:33 2025-08-22 06:31:33 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spUpdatePartStatusWeighted' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spUpdatePartStatusWeighted PROCEDURE spUpdatePartStatusWeighted SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255)

BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
root@localhost 2025-08-22 06:31:44 2025-08-22 06:31:44 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'updateGrnSummary1' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test updateGrnSummary1 PROCEDURE updateGrnSummary1 SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
            COALESCE(SUM(rework_issue_qty),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2025-11-20 11:55:41 2025-11-20 11:55:41 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
            COALESCE(SUM(rework_issue_qty),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'updateGrnSummary' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test updateGrnSummary PROCEDURE updateGrnSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-02-10 11:44:32 2026-02-10 11:44:32 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'updateInspSummary' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test updateInspSummary PROCEDURE updateInspSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-02-10 11:45:19 2026-02-10 11:45:19 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'updateDcSummary' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test updateDcSummary PROCEDURE updateDcSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_dc_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
root@localhost 2025-12-01 11:53:40 2025-12-01 11:53:40 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'spStatusUpdate' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db spStatusUpdate PROCEDURE spStatusUpdate SQL CONTAINS_SQL NO DEFINER 
IN `oa` VARCHAR(100)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2026-03-09 06:51:20 2026-03-09 06:51:20 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'spUpdatePartStatusWeighted' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db spUpdatePartStatusWeighted PROCEDURE spUpdatePartStatusWeighted SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255)

BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
root@localhost 2026-03-09 06:51:32 2026-03-09 06:51:32 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'updateDcSummary' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db updateDcSummary PROCEDURE updateDcSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_dc_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
root@localhost 2026-03-09 06:52:05 2026-03-09 06:52:05 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'updateGrnSummary1' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db updateGrnSummary1 PROCEDURE updateGrnSummary1 SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
            COALESCE(SUM(rework_issue_qty),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-03-09 06:52:16 2026-03-09 06:52:16 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
            COALESCE(SUM(rework_issue_qty),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'updateGrnSummary' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db updateGrnSummary PROCEDURE updateGrnSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-03-09 06:52:35 2026-03-09 06:52:35 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spProcessData' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spProcessData PROCEDURE spProcessData SQL CONTAINS_SQL NO DEFINER 
IN `part_id_val` INT, IN `stage_no_val` INT, IN `reqd_total_qty_val` INT, IN `mr_na_val` INT, IN `no_of_stages_val` INT

BEGIN
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty1 DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);

	SET mat_reqd_qty = 0.00;
    SET mat_rec_qty = 0.00;
    SET mat_recpt = 0.00;
    
   IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_recd_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

    SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
    FROM vendor_po_detl
    WHERE part_id = part_id_val AND stg_no = stage_no_val  AND vendor_part_status = 'issued';

    SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
    FROM part_rcpt_detl
    WHERE part_id = part_id_val AND stg_no = stage_no_val;

    SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
    INTO acpt_qty, rework_qty, reject_qty
    FROM inspection_details
    WHERE part_id = part_id_val AND stg_no = stage_no_val;

    SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty1
    FROM part_issue_details
    WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);
    
    IF rework_qty>0
    THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    FROM part_issue_details
    WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    ELSE 
    SET rework_issue_qty = 0;
    END IF;

    SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
    FROM tbldispatchcomponent
    WHERE part_id = part_id_val;

    IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;

    -- Update t_parts_status table
    UPDATE t_parts_status
    SET total_qty = reqd_total_qty_val,
        part_approve = reqd_total_qty_val,
        mat_req = mat_reqd_qty,
        mat_recpt = mat_recpt, -- Corrected variable name
        vendor_po = ven_order_qty,
        mfg = rcpt_qty,
        part_recpt = rcpt_qty,
        accept = acpt_qty,
        rework = rework_qty,
        reject = reject_qty,
        part_issue = (part_issue_qty1+rework_issue_qty),
        assembly = assembly_issue_qty_update
    WHERE part_id = part_id_val AND stage_no = stage_no_val;

END
root@localhost 2025-08-22 06:30:43 2025-08-22 06:30:43 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty1 DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);

	SET mat_reqd_qty = 0.00;
    SET mat_rec_qty = 0.00;
    SET mat_recpt = 0.00;
    
   IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_recd_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

    SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
    FROM vendor_po_detl
    WHERE part_id = part_id_val AND stg_no = stage_no_val  AND vendor_part_status = 'issued';

    SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
    FROM part_rcpt_detl
    WHERE part_id = part_id_val AND stg_no = stage_no_val;

    SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
    INTO acpt_qty, rework_qty, reject_qty
    FROM inspection_details
    WHERE part_id = part_id_val AND stg_no = stage_no_val;

    SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty1
    FROM part_issue_details
    WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);
    
    IF rework_qty>0
    THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    FROM part_issue_details
    WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    ELSE 
    SET rework_issue_qty = 0;
    END IF;

    SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
    FROM tbldispatchcomponent
    WHERE part_id = part_id_val;

    IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;

    -- Update t_parts_status table
    UPDATE t_parts_status
    SET total_qty = reqd_total_qty_val,
        part_approve = reqd_total_qty_val,
        mat_req = mat_reqd_qty,
        mat_recpt = mat_recpt, -- Corrected variable name
        vendor_po = ven_order_qty,
        mfg = rcpt_qty,
        part_recpt = rcpt_qty,
        accept = acpt_qty,
        rework = rework_qty,
        reject = reject_qty,
        part_issue = (part_issue_qty1+rework_issue_qty),
        assembly = assembly_issue_qty_update
    WHERE part_id = part_id_val AND stage_no = stage_no_val;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'mechalonic_db' AND `proc`.`name` = 'updateInspSummary' AND `proc`.`type` = 'PROCEDURE'
 		mechalonic_db updateInspSummary PROCEDURE updateInspSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-03-09 06:52:46 2026-03-09 06:52:46 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'cimtrix' AND `proc`.`name` = 'spStatusUpdate' AND `proc`.`type` = 'PROCEDURE'
 		cimtrix spStatusUpdate PROCEDURE spStatusUpdate SQL CONTAINS_SQL NO DEFINER 
IN `oa` VARCHAR(100)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2026-04-27 12:21:44 2026-04-27 12:21:44 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE req_total_qty DECIMAL(10,2);
    DECLARE ven_order_qty DECIMAL(10,2);
    DECLARE rcpt_qty DECIMAL(10,2);
    DECLARE acpt_qty DECIMAL(10,2);
    DECLARE rework_qty DECIMAL(10,2);
    DECLARE reject_qty DECIMAL(10,2);
    DECLARE part_issue_qty_new DECIMAL(10,2);
    DECLARE rework_issue_qty DECIMAL(10,2);
    DECLARE mat_reqd_qty DECIMAL(10,2);
    DECLARE mat_rec_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty DECIMAL(10,2);
    DECLARE assembly_issue_qty_update DECIMAL(10,2);
    DECLARE request_status_mat VARCHAR(255);
    DECLARE mat_recpt DECIMAL(10,2);

    -- Declare variables for cursor
    DECLARE part_id_val INT;
    DECLARE stage_no_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE no_of_stages_val INT;

    -- Declare cursor for the SELECT statement
      DECLARE cur CURSOR FOR
      SELECT 
            ps.part_id, 
            ps.stage_no, 
            ps.total_qty, 
            ps.mr_na, 
            pd.no_of_stages
        FROM 
            t_parts_status ps
        JOIN 
            proj_detl pd ON ps.part_id = pd.part_id where ps.wo_no=oa;
        -- WHERE 
           -- FIND_IN_SET(ps.part_id, part_ids) > 0
            -- AND FIND_IN_SET(ps.stage_no, stage_nos) > 0;


    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO part_id_val, stage_no_val, reqd_total_qty_val, mr_na_val,no_of_stages_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset variables
        SET req_total_qty = reqd_total_qty_val;
        SET ven_order_qty = 0.00;
        SET rcpt_qty = 0.00;
        SET acpt_qty = 0.00;
        SET rework_qty = 0.00;
        SET reject_qty = 0.00;
        SET part_issue_qty_new = 0.00;
        SET mat_reqd_qty = 0.00;
        SET assembly_issue_qty=0.00;
	SET mat_recpt = 0.00;

        -- Calculate values from other tables
        IF stage_no_val = 1 THEN
    SELECT 
        COALESCE(SUM(mat_req_qty), 0)    
    INTO 
        mat_reqd_qty     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val;

    SELECT 
        COALESCE(SUM(mat_req_qty), 0)
    INTO 
        mat_recpt     
    FROM 
        mat_req_detl
    WHERE 
        part_id = part_id_val AND request_status = 'Updated';
        END IF;

        SELECT COALESCE(SUM(vendor_order_qty), 0) INTO ven_order_qty
        FROM vendor_po_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val  and vendor_part_status='issued';

        SELECT COALESCE(SUM(part_rcpt_qty), 0) INTO rcpt_qty
        FROM part_rcpt_detl
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(insp_qty_acpt), 0), COALESCE(SUM(insp_qty_rework), 0), COALESCE(SUM(insp_qty_rej), 0)
        INTO acpt_qty, rework_qty, reject_qty
        FROM inspection_details
        WHERE part_id = part_id_val AND stg_no = stage_no_val;

        SELECT COALESCE(SUM(part_issue_qty), 0) INTO part_issue_qty_new
        FROM part_issue_details
        WHERE part_id = part_id_val AND stg_no = (stage_no_val+1);

-- SET @message = CONCAT('Stage Number: ', stage_no_val+1, ', Part Issue Quantity: ', part_issue_qty_new);
-- SELECT @message;



-- FETCH cur INTO part_id_val;

	IF rework_qty>0
    	THEN SELECT COALESCE(SUM(part_issue_qty), 0) INTO rework_issue_qty
    	FROM part_issue_details
    	WHERE part_id = part_id_val AND stg_no = (stage_no_val);
    	ELSE 
    	SET rework_issue_qty = 0;
    	END IF;

        SELECT COALESCE(SUM(issue_qty), 0) INTO assembly_issue_qty
        FROM tbldispatchcomponent
        WHERE part_id = part_id_val;

	IF stage_no_val = no_of_stages_val THEN
        SET assembly_issue_qty_update = assembly_issue_qty;
    ELSE
        SET assembly_issue_qty_update = 0;
    END IF;


        -- Update t_parts_status table
        UPDATE t_parts_status
        SET total_qty = req_total_qty,
	    part_approve = req_total_qty,
            mat_req = mat_reqd_qty,
            mat_recpt = mat_recpt,
            vendor_po = ven_order_qty,
            mfg = rcpt_qty,
            part_recpt = rcpt_qty,
            accept = acpt_qty,
            rework = rework_qty,
            reject = reject_qty,
            part_issue = (part_issue_qty_new+rework_issue_qty),
            assembly = assembly_issue_qty_update
        WHERE wo_no = oa AND part_id = part_id_val AND stage_no = stage_no_val;

    END LOOP;  -- End of read_loop

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'cimtrix' AND `proc`.`name` = 'spUpdatePartStatusWeighted' AND `proc`.`type` = 'PROCEDURE'
 		cimtrix spUpdatePartStatusWeighted PROCEDURE spUpdatePartStatusWeighted SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255)

BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
root@localhost 2026-04-27 12:21:56 2026-04-27 12:21:56 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    UPDATE t_parts_status ps
    SET ps.status = 
        (
            (
                COALESCE(ps.part_approve * (
                    SELECT activity_weightage 
                    FROM t_status_weightage 
                    WHERE column_name = 'part_approve'
                ), 0)
                +
                CASE WHEN ps.mr_na = 0 THEN
                    COALESCE(ps.mat_req * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_req'
                    ), 0)
                    +
                    COALESCE(ps.mat_recpt * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mat_recpt'
                    ), 0)
                ELSE
                    0
                END
                +
                CASE WHEN ps.vendor_po < ps.total_qty + ps.reject THEN
                    COALESCE((ps.vendor_po) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'vendor_po'
                    ), 0)
                END
                +
                CASE WHEN ps.mfg < ps.total_qty + ps.reject THEN
                    COALESCE((ps.mfg - ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'mfg'
                    ), 0)
                END
                +
                CASE WHEN ps.part_recpt < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_recpt) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_recpt'
                    ), 0)
                END
                +
                CASE WHEN ps.part_issue < ps.total_qty + ps.reject THEN
                    COALESCE((ps.part_issue) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty + ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'part_issue'
                    ), 0)
                END
                +
                CASE WHEN ps.reject < ps.total_qty + ps.reject THEN
                    COALESCE((ps.reject) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty ) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'reject'
                    ), 0)
                END
                +
                CASE WHEN ps.rework < ps.total_qty + ps.reject THEN
                    COALESCE((ps.rework) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'rework'
                    ), 0)
                END
                +
                CASE WHEN ps.accept < ps.total_qty + ps.reject THEN
                    COALESCE((ps.accept) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'accept'
                    ), 0)
                END
                +
                CASE WHEN ps.assembly < ps.total_qty + ps.reject THEN
                    COALESCE((ps.assembly) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                ELSE
                    COALESCE((ps.total_qty) * (
                        SELECT activity_weightage 
                        FROM t_status_weightage 
                        WHERE column_name = 'assembly'
                    ), 0)
                END
            )
            / ps.total_qty
        )
       +
        CASE WHEN ps.mr_na = 1 AND ps.vendor_po > 0 THEN 10 ELSE 0 END
         +
        CASE WHEN ps.reject > 0 AND ps.mr_na = 1 OR (ps.mr_na = 0 AND ps.stage_no > 1 ) THEN (ps.reject*10)/ps.total_qty ELSE 0 END
        +
        CASE WHEN ps.stage_no > 1 AND ps.vendor_po > 0 AND mr_na=0 THEN 10 ELSE 0 END 
    WHERE FIND_IN_SET(ps.part_id, part_ids);
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'cimtrix' AND `proc`.`name` = 'updateDcSummary' AND `proc`.`type` = 'PROCEDURE'
 		cimtrix updateDcSummary PROCEDURE updateDcSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_dc_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
root@localhost 2026-04-27 12:22:15 2026-04-27 12:22:15 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    -- DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_dc_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date date;
    DECLARE v_dc_created_by VARCHAR(100);
    DECLARE v_dc_created_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    -- DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);
    DECLARE v_grn_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM dc_summary_details
    WHERE dc_no = p_dc_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_issue_details
    WHERE dc_no = p_dc_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            COALESCE(SUM(part_issue_qty), 0),
            MAX(part_issue_by),
            MAX(part_issue_date),
            COUNT(DISTINCT part_id),
            SUM(IFNULL(dc_value_with_out_tax,0)),
            SUM(IFNULL(dc_value_with_tax,0)),
            MAX(os_planner),
            MAX(grn_no)
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_dc_created_by,
            v_dc_created_date,
            v_parts_count,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
            v_os_planner,
            v_grn_no
        FROM part_issue_details
        WHERE dc_no = p_dc_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_issue_details 
            WHERE dc_no = p_dc_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_issue_details
            WHERE dc_no = p_dc_no
        );  
 END IF;
    -- Pending Quantity
    -- SET v_pending_qty =
       --  IFNULL(v_parts_receipt_qty,0) - 
      --  (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO dc_summary_details (
            dc_no,
            grn_no,
            vendor,
            vendor_po_no,
            parts_issue_qty,
            vendor_po_date,
            dc_created_by,
            dc_created_date,
            os_planner,
            parts_count,
           -- accept_qty,
           -- reject_qty,
          --  rework_qty,
            total_cost_without_tax,
            total_cost_taxable,
           -- pending_qty,
            -- parts_receipt_qty,
            -- rework_issue_qty,
           -- rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_dc_no,
            v_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_part_issue_qty,
            v_vendor_po_date,
            v_dc_created_by,
            v_dc_created_date,
            v_os_planner,
            v_parts_count,
          --  v_accept_qty,
            -- v_reject_qty,
           -- v_rework_qty,
            v_dc_cost_without_tax,
            v_dc_cost_taxable,
           -- v_pending_qty,
          --  v_parts_receipt_qty,
           -- v_rework_issue_qty,
           -- v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE dc_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            parts_issue_qty = v_part_issue_qty,
            dc_created_by = v_dc_created_by,
            dc_created_date = v_dc_created_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
           -- accept_qty = v_accept_qty,
           -- reject_qty = v_reject_qty,
           -- rework_qty = v_rework_qty,
            total_cost_without_tax = v_dc_cost_without_tax,
            total_cost_taxable = v_dc_cost_taxable,
           -- pending_qty = v_pending_qty,
           -- parts_receipt_qty = v_parts_receipt_qty,
           -- rework_issue_qty = v_rework_issue_qty,
           -- rework_issue_qty = v_rework_issue_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE dc_no = p_dc_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'cimtrix' AND `proc`.`name` = 'updateGrnSummary' AND `proc`.`type` = 'PROCEDURE'
 		cimtrix updateGrnSummary PROCEDURE updateGrnSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-04-27 12:22:26 2026-04-27 12:22:26 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor_order_qty DECIMAL(10,3) DEFAULT 0.000;
	DECLARE v_vendor_po_date DATE;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_grn_created_by VARCHAR(100);
    DECLARE v_grn_type VARCHAR(10);
    DECLARE v_grn_status VARCHAR(50);
    DECLARE v_grn_closed_by VARCHAR(100);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM grn_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM part_rcpt_detl
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(grn_created_by),
            MAX(grn_type),
            MAX(os_planner),
            COUNT(DISTINCT part_id),
            SUM(part_rcpt_qty),
            MAX(grn_status),
            MAX(grn_closed_by),
            SUM(IFNULL(grn_value_adisc_before_gst,0)),
            SUM(IFNULL(grn_value_adisc_with_gst,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_parts_receipt_qty,
            v_grn_status,
            v_grn_closed_by,
            v_total_cost_without_tax,
            v_total_cost_taxable
        FROM part_rcpt_detl
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;

        -- Vendor Order Qty (unique per part)
        SELECT COALESCE(SUM(distinct_vendor_qty), 0)
        INTO v_vendor_order_qty
        FROM (
            SELECT MAX(vendor_order_qty) AS distinct_vendor_qty
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
            GROUP BY part_id, vendor_po_no
        ) AS x;

        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM part_rcpt_detl
            WHERE grn_no = p_grn_no
        );
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM part_rcpt_detl 
            WHERE grn_no = p_grn_no);
    END IF;

    -- Inspection values
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN

        SELECT 
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0)
        INTO 
            v_accept_qty,
            v_reject_qty,
            v_rework_qty
        FROM inspection_details
        WHERE grn_no = p_grn_no;
    END IF;

    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_vendor_order_qty,0) -
        (IFNULL(v_parts_receipt_qty,0) - IFNULL(v_rework_issue_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO grn_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_dt,
            grn_created_by,
            grn_type,
            os_planner,
            parts_count,
            vendor_order_qty,
            parts_receipt_qty,
            accept_qty,
            reject_qty,
            rework_qty,
            rework_issue_qty,
            grn_status,
            grn_closed_by,
            pending_qty,
            total_cost_without_tax,
            total_cost_taxable,
            grn_created_date,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_grn_created_by,
            v_grn_type,
            v_os_planner,
            v_parts_count,
            v_vendor_order_qty,
            v_parts_receipt_qty,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_rework_issue_qty,
            v_grn_status,
            v_grn_closed_by,
            v_pending_qty,
            v_total_cost_without_tax,
            v_total_cost_taxable,
            CURDATE(),
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE grn_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            vendor_po_dt = v_vendor_po_date,
            grn_created_by = v_grn_created_by,
            grn_type = v_grn_type,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            vendor_order_qty = v_vendor_order_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            rework_issue_qty = v_rework_issue_qty,
            grn_status = v_grn_status,
            grn_closed_date = CURDATE(),
            grn_closed_by = v_grn_closed_by,
            pending_qty = v_pending_qty,
            total_cost_without_tax = v_total_cost_without_tax,
            total_cost_taxable = v_total_cost_taxable,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'cimtrix' AND `proc`.`name` = 'updateInspSummary' AND `proc`.`type` = 'PROCEDURE'
 		cimtrix updateInspSummary PROCEDURE updateInspSummary SQL CONTAINS_SQL NO DEFINER 
IN `p_grn_no` VARCHAR(100)

BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
root@localhost 2026-04-27 12:22:37 2026-04-27 12:22:37 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE v_parts_count INT DEFAULT 0;
    DECLARE v_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_pending_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_without_tax DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_accpt_cost_taxable DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_vendor VARCHAR(100);
    DECLARE v_vendor_po_no VARCHAR(100);
    DECLARE v_vendor_po_date VARCHAR(100);
    DECLARE v_inspected_by VARCHAR(100);
    DECLARE v_inspected_date VARCHAR(10);
    DECLARE v_os_planner VARCHAR(100);
    DECLARE v_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_oa_no VARCHAR(100);

    DECLARE v_summary_exists INT DEFAULT 0;
    DECLARE v_grn_exists INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check summary entry exists
    SELECT COUNT(*) INTO v_summary_exists
    FROM inspection_summary_details
    WHERE grn_no = p_grn_no;

    -- Check GRN exists in part_rcpt_detl
    SELECT COUNT(*) INTO v_grn_exists
    FROM inspection_details
    WHERE grn_no = p_grn_no;

    IF v_grn_exists > 0 THEN
        
        SELECT 
            MAX(vendor),
            MAX(vendor_po_no),
            MAX(insp_person),
            MAX(insp_date),
            COUNT(DISTINCT part_id),
            COALESCE(SUM(insp_qty_acpt),0),
            COALESCE(SUM(insp_qty_rej),0),
            COALESCE(SUM(insp_qty_rework),0),
         
            SUM(IFNULL(accept_with_out_tax,0)),
            SUM(IFNULL(accept_with_tax,0))
        INTO 
            v_vendor,
            v_vendor_po_no,
            v_inspected_by,
            v_inspected_date,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
          
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable
        FROM inspection_details
        WHERE grn_no = p_grn_no;
        
        -- get rework_issue_qty
        SELECT COALESCE(SUM(part_issue_qty), 0)
        INTO  v_rework_issue_qty
        FROM part_issue_details
        WHERE grn_no = p_grn_no;
        
        -- select vendor_po_date
        SELECT MAX(vendor_po_dt)
        INTO v_vendor_po_date 
        FROM vendor_po 
        WHERE vendor_po_no IN (
            SELECT vendor_po_no 
            FROM inspection_details 
            WHERE grn_no = p_grn_no);
            
        -- get OA number
        SELECT GROUP_CONCAT(DISTINCT oa_no)
        INTO v_oa_no
        FROM proj_detl
        WHERE part_id IN (
            SELECT DISTINCT part_id
            FROM inspection_details
            WHERE grn_no = p_grn_no
        );
            
        -- Part recipt Qty for GRN
        SELECT 
        COALESCE(SUM(part_rcpt_qty), 0), 
        COALESCE(SUM(rework_rcpt_qty), 0),
        MAX(os_planner)
        INTO 
        v_parts_receipt_qty,
        v_rework_rcpt_qty,
        v_os_planner
        FROM part_rcpt_detl 
        WHERE grn_no = p_grn_no ;

    END IF;


    -- Pending Quantity
    SET v_pending_qty =
        IFNULL(v_parts_receipt_qty,0) - 
       (IFNULL(v_accept_qty,0) + IFNULL(v_reject_qty,0) + IFNULL(v_rework_qty,0));

    -- Insert / Update Logic
    IF v_summary_exists = 0 THEN
        
        INSERT INTO inspection_summary_details (
            grn_no,
            vendor,
            vendor_po_no,
            vendor_po_date,
            inspected_by,
            inspected_date,
            os_planner,
            parts_count,
            accept_qty,
            reject_qty,
            rework_qty,
            accepted_cost_without_tax,
            accepted_cost_with_tax,
            pending_qty,
            parts_receipt_qty,
            rework_issue_qty,
            rework_rcpt_qty,
            oa_no,
            last_updated
        ) VALUES (
            p_grn_no,
            v_vendor,
            v_vendor_po_no,
            v_vendor_po_date,
            v_inspected_by,
            v_inspected_date,
            v_os_planner,
            v_parts_count,
            v_accept_qty,
            v_reject_qty,
            v_rework_qty,
            v_accpt_cost_without_tax,
            v_accpt_cost_taxable,
            v_pending_qty,
            v_parts_receipt_qty,
            v_rework_issue_qty,
            v_rework_rcpt_qty,
            v_oa_no,
            NOW()
        );

    ELSE

        UPDATE inspection_summary_details
        SET 
            vendor = v_vendor,
            vendor_po_no = v_vendor_po_no,
            inspected_by = v_inspected_by,
            inspected_date = v_inspected_date,
            os_planner = v_os_planner,
            parts_count = v_parts_count,
            accept_qty = v_accept_qty,
            reject_qty = v_reject_qty,
            rework_qty = v_rework_qty,
            accepted_cost_without_tax = v_accpt_cost_without_tax,
            accepted_cost_with_tax = v_accpt_cost_taxable,
            pending_qty = v_pending_qty,
            parts_receipt_qty = v_parts_receipt_qty,
            rework_issue_qty = v_rework_issue_qty,
            rework_rcpt_qty = v_rework_rcpt_qty,
            oa_no = v_oa_no,
            last_updated = NOW()
        WHERE grn_no = p_grn_no;
    END IF;

    COMMIT;

END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spStatusInsert' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spStatusInsert PROCEDURE spStatusInsert SQL CONTAINS_SQL NO DEFINER 
IN `oa` VARCHAR(100)

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE part_id_val INT;
    DECLARE no_of_stages_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE sa_name_val VARCHAR(255);
    DECLARE part_name_val VARCHAR(255);
    DECLARE stage_weightage_val INT;
    DECLARE stage_no_val INT;
    DECLARE multi_stage_val INT;

    -- Declare cursor for the SELECT statement
    DECLARE cur CURSOR FOR
        SELECT sa_name, part_name, part_id, no_of_stages, reqd_total_qty, mr_na
        FROM proj_detl
        WHERE oa_no = oa AND part_drg_remarks = 'approved' AND status_flag='W'
        ORDER BY part_id;

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO sa_name_val, part_name_val, part_id_val, no_of_stages_val, reqd_total_qty_val, mr_na_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset stage_no_val for each part
        SET stage_no_val = 1;

        -- Loop through stages and insert into t_parts_status
        WHILE stage_no_val <= no_of_stages_val DO
            SET stage_weightage_val = 100 / no_of_stages_val;
            SET multi_stage_val = CASE WHEN no_of_stages_val > 1 THEN 1 ELSE 0 END;

            -- Check if the same part_id and stage_no does not exist
            IF NOT EXISTS (
                SELECT 1
                FROM t_parts_status
                WHERE part_id = part_id_val AND stage_no = stage_no_val
            ) THEN
                -- Insert into t_parts_status
                INSERT INTO t_parts_status (wo_no, part_id, sa_name, part_name, stage_no, stage_weightage, part_approve, mat_req, mat_recpt, vendor_po, mat_issue, mfg, part_recpt, part_issue, rework, reject, accept, assembly, raw_mat_na, mr_na, multi_stage, total_qty, status)
                VALUES (oa, part_id_val, sa_name_val, part_name_val, stage_no_val, stage_weightage_val, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', mr_na_val, multi_stage_val, reqd_total_qty_val, '0');
            END IF;

            SET stage_no_val = stage_no_val + 1;
        END WHILE;
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2025-08-22 06:31:09 2025-08-22 06:31:09 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE part_id_val INT;
    DECLARE no_of_stages_val INT;
    DECLARE reqd_total_qty_val DECIMAL(10,2);
    DECLARE mr_na_val INT;
    DECLARE sa_name_val VARCHAR(255);
    DECLARE part_name_val VARCHAR(255);
    DECLARE stage_weightage_val INT;
    DECLARE stage_no_val INT;
    DECLARE multi_stage_val INT;

    -- Declare cursor for the SELECT statement
    DECLARE cur CURSOR FOR
        SELECT sa_name, part_name, part_id, no_of_stages, reqd_total_qty, mr_na
        FROM proj_detl
        WHERE oa_no = oa AND part_drg_remarks = 'approved' AND status_flag='W'
        ORDER BY part_id;

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO sa_name_val, part_name_val, part_id_val, no_of_stages_val, reqd_total_qty_val, mr_na_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset stage_no_val for each part
        SET stage_no_val = 1;

        -- Loop through stages and insert into t_parts_status
        WHILE stage_no_val <= no_of_stages_val DO
            SET stage_weightage_val = 100 / no_of_stages_val;
            SET multi_stage_val = CASE WHEN no_of_stages_val > 1 THEN 1 ELSE 0 END;

            -- Check if the same part_id and stage_no does not exist
            IF NOT EXISTS (
                SELECT 1
                FROM t_parts_status
                WHERE part_id = part_id_val AND stage_no = stage_no_val
            ) THEN
                -- Insert into t_parts_status
                INSERT INTO t_parts_status (wo_no, part_id, sa_name, part_name, stage_no, stage_weightage, part_approve, mat_req, mat_recpt, vendor_po, mat_issue, mfg, part_recpt, part_issue, rework, reject, accept, assembly, raw_mat_na, mr_na, multi_stage, total_qty, status)
                VALUES (oa, part_id_val, sa_name_val, part_name_val, stage_no_val, stage_weightage_val, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', mr_na_val, multi_stage_val, reqd_total_qty_val, '0');
            END IF;

            SET stage_no_val = stage_no_val + 1;
        END WHILE;
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spStatusPartInsert' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spStatusPartInsert PROCEDURE spStatusPartInsert SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255)

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_part_id INT;
    DECLARE oa_no_val VARCHAR(255);
    DECLARE no_of_stages_val INT;
    DECLARE reqd_total_qty_val INT;
    DECLARE mr_na_val INT;
    DECLARE sa_name_val VARCHAR(255);
    DECLARE part_name_val VARCHAR(255);
    DECLARE stage_weightage_val INT;
    DECLARE stage_no_val INT;
    DECLARE multi_stage_val INT;

    -- Declare cursor for the SELECT statement
    DECLARE cur CURSOR FOR
        SELECT part_id, oa_no, sa_name, part_name, no_of_stages, reqd_total_qty, mr_na
        FROM proj_detl
        WHERE FIND_IN_SET(part_id, part_ids);

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO current_part_id, oa_no_val, sa_name_val, part_name_val, no_of_stages_val, reqd_total_qty_val, mr_na_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset stage_no_val for each part
        SET stage_no_val = 1;

        -- Loop through stages and insert into t_parts_status
        WHILE stage_no_val <= no_of_stages_val DO
            SET stage_weightage_val = 100 / no_of_stages_val;
            SET multi_stage_val = CASE WHEN no_of_stages_val > 1 THEN 1 ELSE 0 END;

            -- Check if the same part_id and stage_no does not exist
            IF NOT EXISTS (
                SELECT 1
                FROM t_parts_status
                WHERE part_id = current_part_id AND stage_no = stage_no_val
            ) THEN
                -- Insert into t_parts_status
                INSERT INTO t_parts_status (wo_no, part_id, sa_name, part_name, stage_no, stage_weightage, part_approve, mat_req, mat_recpt, vendor_po, mat_issue, mfg, part_recpt, part_issue, rework, reject, accept, assembly, raw_mat_na, mr_na, multi_stage, total_qty, status)
                VALUES (oa_no_val, current_part_id, sa_name_val, part_name_val, stage_no_val, stage_weightage_val, reqd_total_qty_val, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', mr_na_val, multi_stage_val, reqd_total_qty_val, '0');
            END IF;

            SET stage_no_val = stage_no_val + 1;
        END WHILE;
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
root@localhost 2025-08-22 06:31:23 2025-08-22 06:31:23 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_part_id INT;
    DECLARE oa_no_val VARCHAR(255);
    DECLARE no_of_stages_val INT;
    DECLARE reqd_total_qty_val INT;
    DECLARE mr_na_val INT;
    DECLARE sa_name_val VARCHAR(255);
    DECLARE part_name_val VARCHAR(255);
    DECLARE stage_weightage_val INT;
    DECLARE stage_no_val INT;
    DECLARE multi_stage_val INT;

    -- Declare cursor for the SELECT statement
    DECLARE cur CURSOR FOR
        SELECT part_id, oa_no, sa_name, part_name, no_of_stages, reqd_total_qty, mr_na
        FROM proj_detl
        WHERE FIND_IN_SET(part_id, part_ids);

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Loop through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH cur INTO current_part_id, oa_no_val, sa_name_val, part_name_val, no_of_stages_val, reqd_total_qty_val, mr_na_val;

        -- Check if we reached the end of the result set
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Reset stage_no_val for each part
        SET stage_no_val = 1;

        -- Loop through stages and insert into t_parts_status
        WHILE stage_no_val <= no_of_stages_val DO
            SET stage_weightage_val = 100 / no_of_stages_val;
            SET multi_stage_val = CASE WHEN no_of_stages_val > 1 THEN 1 ELSE 0 END;

            -- Check if the same part_id and stage_no does not exist
            IF NOT EXISTS (
                SELECT 1
                FROM t_parts_status
                WHERE part_id = current_part_id AND stage_no = stage_no_val
            ) THEN
                -- Insert into t_parts_status
                INSERT INTO t_parts_status (wo_no, part_id, sa_name, part_name, stage_no, stage_weightage, part_approve, mat_req, mat_recpt, vendor_po, mat_issue, mfg, part_recpt, part_issue, rework, reject, accept, assembly, raw_mat_na, mr_na, multi_stage, total_qty, status)
                VALUES (oa_no_val, current_part_id, sa_name_val, part_name_val, stage_no_val, stage_weightage_val, reqd_total_qty_val, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', mr_na_val, multi_stage_val, reqd_total_qty_val, '0');
            END IF;

            SET stage_no_val = stage_no_val + 1;
        END WHILE;
    END LOOP;

    -- Close the cursor
    CLOSE cur;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'precitec_test' AND `proc`.`name` = 'spUpdateProjPartsStatus' AND `proc`.`type` = 'PROCEDURE'
 		precitec_test spUpdateProjPartsStatus PROCEDURE spUpdateProjPartsStatus SQL CONTAINS_SQL NO DEFINER 
IN `part_ids` VARCHAR(255)

BEGIN
    UPDATE proj_detl pd
    JOIN (
        SELECT part_id, SUM(status) / MAX(stage_no) AS calculated_status
        FROM t_parts_status
        WHERE FIND_IN_SET(part_id, part_ids)
        GROUP BY part_id
    ) ps ON pd.part_id = ps.part_id
    SET pd.status = ps.calculated_status
    WHERE FIND_IN_SET(pd.part_id, part_ids);
END
root@localhost 2025-08-22 06:31:59 2025-08-22 06:31:59 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_... utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    UPDATE proj_detl pd
    JOIN (
        SELECT part_id, SUM(status) / MAX(stage_no) AS calculated_status
        FROM t_parts_status
        WHERE FIND_IN_SET(part_id, part_ids)
        GROUP BY part_id
    ) ps ON pd.part_id = ps.part_id
    SET pd.status = ps.calculated_status
    WHERE FIND_IN_SET(pd.part_id, part_ids);
END
NONE
With selected: With selected:
|
|
|
|
Filter rows:
Sort by key:
Query results operations Copy to clipboard Copy to clipboard Export Export Display chart Display chart Create view Create view
Bookmark this SQL query Bookmark this SQL query