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

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

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

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

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

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

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

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

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

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

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

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 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;

    -- 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 = 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
        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 = 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+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

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

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

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

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

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

BEGIN
  DECLARE v_issue_date DATE;
  DECLARE v_issue_to VARCHAR(100);
  DECLARE v_issued_by VARCHAR(100);
  DECLARE v_oa_no VARCHAR(100);
  DECLARE v_key VARCHAR(255);
  DECLARE v_prev_key VARCHAR(255) DEFAULT '';
  DECLARE v_serial INT DEFAULT 0;
  DECLARE done INT DEFAULT FALSE;

  -- Cursor for unique combinations
  DECLARE cur CURSOR FOR
    SELECT DISTINCT issue_date, issue_to, issued_by, oa_no
    FROM tbldispatchcomponent
    WHERE issue_date BETWEEN '2025-04-01' AND '2026-03-31'
    ORDER BY issue_date, issue_to, issued_by, oa_no;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO v_issue_date, v_issue_to, v_issued_by, v_oa_no;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Create unique key from the combination
    SET v_key = CONCAT(v_issue_date, '|', v_issue_to, '|', v_issued_by, '|', v_oa_no);

    IF v_key != v_prev_key THEN
      SET v_serial = v_serial + 1;
      SET v_prev_key = v_key;

      -- Update all matching rows at once
      UPDATE tbldispatchcomponent
      SET piv_no = CONCAT('PIV/', v_oa_no, '/', LPAD(v_serial, 4, '0'), '/25-26')
      WHERE issue_date = v_issue_date
        AND issue_to = v_issue_to
        AND issued_by = v_issued_by
        AND oa_no = v_oa_no;
    END IF;

  END LOOP;

  CLOSE cur;
END

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

IN `p_po_no` VARCHAR(100)

BEGIN
    DECLARE v_total_parts_count INT DEFAULT 0;
	DECLARE v_total_order_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_parts_receipt_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_accept_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_reject_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_rework_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_rework_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_part_issue_qty DECIMAL(10,3) DEFAULT 0.000;
    DECLARE v_total_rework_rcpt_qty DECIMAL(10,3) DEFAULT 0.000;

  
    DECLARE v_po_exists INT DEFAULT 0;

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

    START TRANSACTION;

    -- Check vendor_po_no exists in vendor_po_detl
    SELECT COUNT(*) INTO v_po_exists
    FROM vendor_po_detl
    WHERE vendor_po_no = p_po_no;

    IF v_po_exists > 0 THEN
        
        SELECT 
            COUNT(DISTINCT part_id),
            COALESCE(SUM(vendor_order_qty), 0),
            COALESCE(SUM(accepted_qty), 0),
            COALESCE(SUM(rejected_qty), 0),
            COALESCE(SUM(rework_qty), 0),
            COALESCE(SUM(rework_issue_qty), 0),
            COALESCE(SUM(part_issue_qty), 0)
        INTO 
		v_total_parts_count,
		v_total_order_qty,
		v_total_accept_qty,
		v_total_reject_qty,
		v_total_rework_qty,
		v_total_rework_issue_qty,
		v_total_part_issue_qty
        FROM vendor_po_detl
        WHERE vendor_po_no = p_po_no;
            
		SELECT 
            COALESCE(SUM(part_rcpt_qty), 0),
            COALESCE(SUM(rework_rcpt_qty), 0)
        INTO 
		v_total_parts_receipt_qty,
		v_total_rework_rcpt_qty
        FROM part_rcpt_detl
        WHERE vendor_po_no = p_po_no;
         
 END IF;
    
    
        UPDATE vendor_po
        SET 
        total_parts_count = v_total_parts_count,
        total_qty_count = v_total_order_qty,
        total_accepted_qty = v_total_accept_qty,
        total_received_qty = v_total_parts_receipt_qty,
        total_reject_qty = v_total_reject_qty,
        total_rework_qty = v_total_rework_qty,
        total_rwk_recv_qty = v_total_rework_rcpt_qty,
        total_rework_issue_qty = v_total_rework_issue_qty,
        total_issued_qty = v_total_part_issue_qty
        WHERE vendor_po_no = p_po_no;
    

    COMMIT;

END

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

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

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

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

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