Sample Code (TSQL)

                    

CREATE OR REPLACE PROCEDURE public.validate_n_transaction1(userid INT4)
AS $BODY$
DECLARE
    -- Declare Variables
    implementer INT;
    contractcount INT;
    chartofaccount INT;
    budgettype INT;
    healthfacility INT;
    curr INT;
    dupli INT;
    totalRec INT;
    totalInval INT;
    ExpenseZero INT;
    FieldCost INT;
    ManagementCost INT;
    InvalidChartOfAccount INT;
    InvalidFacility INT;
    InvalidICR INT;
    InvalidICR2 INT;
    InvalidFacilityforContract INT;
    ProvinceId INT;
    FacilityId INT;
    PartyId INT;
    PartyValidationForContract INT;
    ContractCode VARCHAR(50);
BEGIN
    -- Reset Reason field for the given user
    UPDATE public.Staging_NTransaction_slot1
    SET Reason = '?'
    WHERE Reason <> '?' 
        AND Reason IS NOT NULL 
        AND UserId = userid;

    -- Retrieve Contract Code
    SELECT DISTINCT ContractNo INTO ContractCode
    FROM public.Staging_NTransaction_slot1
    WHERE UserId = userid;

    -- Fetch Province ID linked to Contract
    SELECT provinceID INTO ProvinceId
    FROM public.contractProvince
    WHERE contractId IN (
        SELECT contractId
        FROM public.contractInfo
        WHERE contractCode IN (
            SELECT ContractNo
            FROM public.Staging_NTransaction_slot1  
            WHERE UserId = userid
        )
    );

    -- Count invalid records
    SELECT COUNT(*) INTO totalInval
    FROM public.Staging_NTransaction_slot1
    WHERE (Reason IS NOT NULL OR Reason = '?') 
        AND UserId = userid;

    -- Reset invalid reasons if they exist
    IF totalInval > 0 THEN
        UPDATE public.Staging_NTransaction_slot1
        SET Reason = NULL
        WHERE Reason = '?' 
            AND UserId = userid;
    END IF;

    -- Check Facility ID existence
    SELECT COUNT(*) INTO FacilityId
    FROM public.Staging_NTransaction_slot1
    WHERE FacilityId = 100000;

    -- Get Party ID from Contract
    SELECT leadId INTO PartyId
    FROM public.contractInfo
    WHERE contractCode = ContractCode;

    -- Count Invalid Implementers
    SELECT COUNT(*) INTO implementer
    FROM public.Staging_NTransaction_slot1
    WHERE Organization NOT IN (
        SELECT DISTINCT partyAbbrivation 
        FROM public.partyInfo
    ) AND UserId = userid;

    -- Count Invalid Contracts
    SELECT COUNT(*) INTO contractcount
    FROM public.Staging_NTransaction_slot1
    WHERE ContractNo NOT IN (
        SELECT contractCode 
        FROM public.contractInfo
        INNER JOIN public.partyInfo ON partyInfo.partyID = contractInfo.leadId
    ) AND UserId = userid;

    -- Count Invalid Budget Types
    SELECT COUNT(*) INTO budgettype
    FROM public.Staging_NTransaction_slot1
    WHERE ExpenseType NOT IN ('Field Cost', 'Management Cost')  
        AND UserId = userid;

    -- Validate Chart of Accounts
    SELECT COUNT(*) INTO chartofaccount
    FROM public.Staging_NTransaction_slot1
    WHERE ChartOfAccount NOT IN (
        SELECT coACode 
        FROM emis.coaMapping 
        WHERE Organization IN (
            SELECT partyAbbrivation 
            FROM public.partyInfo
        )
    ) AND UserId = userid;

    -- Validate Health Facilities
    SELECT COUNT(*) INTO healthfacility
    FROM public.Staging_NTransaction_slot1
    WHERE FacilityId NOT IN (
        SELECT facilityId 
        FROM public.facilityInfo
    ) AND UserId = userid;

    -- Validate Currency
    SELECT COUNT(*) INTO curr
    FROM public.Staging_NTransaction_slot1
    WHERE Currency NOT IN (
        SELECT currAbr 
        FROM emis.currencyInfo
    ) AND UserId = userid;

    -- Check if Expense is Zero or Negative
    SELECT COUNT(*) INTO ExpenseZero
    FROM public.Staging_NTransaction_slot1
    WHERE Expense <= 0 
        AND UserId = userid;

    -- Validate Field Cost Facility ID
    SELECT COUNT(*) INTO FieldCost
    FROM public.Staging_NTransaction_slot1
    WHERE FacilityId >= 100000 
        AND ExpenseType = 'Field Cost' 
        AND UserId = userid;

    -- Validate Management Cost Facility ID
    SELECT COUNT(*) INTO ManagementCost
    FROM public.Staging_NTransaction_slot1
    WHERE FacilityId < 100000 
        AND ExpenseType = 'Management Cost' 
        AND UserId = userid;

    -- Validate Chart of Accounts for incorrect mapping
    SELECT COUNT(*) INTO InvalidChartOfAccount
    FROM public.Staging_NTransaction_slot1
    WHERE ChartOfAccount IN (
        SELECT coACode 
        FROM emis.coaMapping 
        WHERE emisCoaId IN (
            SELECT coaId 
            FROM emis.chartOfAccounts 
            WHERE flagId IN (2, 3)
        )
    ) AND Expense > 0;

    -- Validate Invalid ICR values
    SELECT COUNT(*) INTO InvalidICR
    FROM public.Staging_NTransaction_slot1
    WHERE (ICR > 0.2 OR ICR < 0) 
        AND UserId = userid;

    -- Validate ICR linkage to contract
    SELECT COUNT(*) INTO InvalidICR2
    FROM public.Staging_NTransaction_slot1
    WHERE ICR NOT IN (
        SELECT indirectCostPerc 
        FROM public.contractInfo 
        WHERE TenantId IN (
            SELECT TenantId 
            FROM public.Users 
            WHERE UserId = userid
        )
    );

    -- Validate Facility-Contract Relationship
    SELECT COUNT(*) INTO InvalidFacilityforContract
    FROM public.Staging_NTransaction_slot1
    WHERE FacilityId NOT IN (
        SELECT FacilityId 
        FROM public.chkFacilityContractValidation1 
        WHERE provinceId = ProvinceId
    ) AND UserId = userid;

    -- Validate Implementer Name for Contract
    SELECT COUNT(*) INTO PartyValidationForContract
    FROM public.Staging_NTransaction_slot1
    WHERE Organization NOT IN (
        SELECT partyAbbrivation 
        FROM public.partyInfo 
        WHERE partyID = PartyId
    ) AND UserId = userid;

    -- Check for Duplicates
    SELECT COUNT(*) INTO dupli
    FROM public.Staging_NTransaction_slot1 stg
    WHERE stg.UserId = userid 
        AND (stg.Organization || stg.ContractNo || stg.FacilityId || stg.Currency || stg.ExpenseType || 
             stg.ChartOfAccount || stg.Year || stg.Month || stg.TenantId) 
        IN (SELECT prt.partyAbbrivation || con.contractCode || hf.facilityId || curr.currAbr || 
                   btype.budgetTypeName || comp.coACode || nt.Year || nt.Month || nt.TenantId 
            FROM emis.NewTransactions nt
            INNER JOIN emis.budgetType btype ON nt.BudgetTypeId = btype.budgetTypeId
            INNER JOIN public.partyInfo prt ON nt.PartyId = prt.partyID
            INNER JOIN public.facilityInfo hf ON nt.FacilityId = hf.facilityId
            INNER JOIN emis.currencyInfo curr ON nt.CurrId = curr.currId
            INNER JOIN public.contractInfo con ON nt.ContractId = con.contractId
            INNER JOIN emis.coaMapping comp ON prt.partyID = comp.partyId 
                                            AND nt.PartyId = comp.partyId 
                                            AND nt.EMISCoAId = comp.emisCoaId);

    -- Update Reason if Duplicate Exists
    IF dupli > 0 THEN
        UPDATE public.Staging_NTransaction_slot1
        SET Reason = 'This record already exists in the database!'
        WHERE UserId = userid 
            AND (Organization || ContractNo || FacilityId || Currency || ExpenseType || 
                 ChartOfAccount || Year || Month || TenantId) 
            IN (SELECT prt.partyAbbrivation || con.contractCode || hf.facilityId || curr.currAbr || 
                       btype.budgetTypeName || comp.coACode || nt.Year || nt.Month || nt.TenantId 
                FROM emis.NewTransactions nt
                INNER JOIN emis.budgetType btype ON nt.BudgetTypeId = btype.budgetTypeId
                INNER JOIN public.partyInfo prt ON nt.PartyId = prt.partyID
                INNER JOIN public.facilityInfo hf ON nt.FacilityId = hf.facilityId
                INNER JOIN emis.currencyInfo curr ON nt.CurrId = curr.currId
                INNER JOIN public.contractInfo con ON nt.ContractId = con.contractId
                INNER JOIN emis.coaMapping comp ON prt.partyID = comp.partyId 
                                                AND nt.PartyId = comp.partyId 
                                                AND nt.EMISCoAId = comp.emisCoaId);
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

                

                    
CREATE OR REPLACE PROCEDURE public.insbudget(userid INT4)
AS $BODY$
DECLARE 
    records INT;
    allrecords INT;
BEGIN
    -- Insert new budget records if they do not already exist
    INSERT INTO emis.budget (
        contractId, hfID, partyId, coaId, facilityTypeId, 
        budgetStartDate, budgetEndDate, noOfMonth, unitCost, 
        currencyId, noOfUnit, totalCost, ICR, TenantId, 
        UserId, budgetTypeId, BudgetUID, enteredBy, createdDate
    )
    SELECT 
        c.contractId,
        f.facilityId,
        p.partyID,
        cm.emisCoaId,
        f.facilityTypeId,
        sb.StartDate,
        sb.EndDate,
        sb.NoOfMonths,
        sb.UnitCost,
        ci.currId,
        sb.Units,
        sb.TotalCost,
        sb.IC,
        sb.TenantId,
        sb.UserId,
        bt.budgetTypeId,
        CONCAT(p.partyID, '-', f.facilityId, '-', sb.ChartOfAccount, '-', sb.Contract, '-', 
               TO_CHAR(sb.StartDate, 'YYYY-MM-DD'), '-', TO_CHAR(sb.EndDate, 'YYYY-MM-DD'), '-', 
               ci.currId, '-', bt.budgetTypeId) AS UID,
        sb.UserId,
        CURRENT_TIMESTAMP
    FROM emis.coaMapping cm
    INNER JOIN public.Staging_Budget sb ON cm.coACode = sb.ChartOfAccount
    INNER JOIN emis.currencyInfo ci ON sb.Currency = ci.currAbr
    INNER JOIN public.contractInfo c ON sb.Contract = c.contractCode
    INNER JOIN emis.budgetType bt ON sb.BudgetType = bt.budgetTypeName
    INNER JOIN public.facilityInfo f ON sb.HealthFacility = f.facilityId
    INNER JOIN public.partyInfo p ON cm.partyId = p.partyID
    WHERE sb.UserId = userid
    AND CONCAT(p.partyID, '-', f.facilityId, '-', sb.ChartOfAccount, '-', sb.Contract, '-', 
               TO_CHAR(sb.StartDate, 'YYYY-MM-DD'), '-', TO_CHAR(sb.EndDate, 'YYYY-MM-DD'), '-', 
               ci.currId, '-', bt.budgetTypeId) 
        NOT IN (SELECT BudgetUID FROM emis.budget);

    -- Validate and delete processed staging records
    SELECT COUNT(*) INTO records 
    FROM public.Staging_Budget 
    WHERE Reason IS NULL AND UserId = userid;
    
    SELECT COUNT(*) INTO allrecords 
    FROM public.Staging_Budget 
    WHERE Reason IS NULL;
        
    IF records = allrecords THEN
        -- If all records are valid, truncate the staging table
        TRUNCATE TABLE public.Staging_Budget;
    ELSE
        -- Otherwise, delete only the records belonging to the current user
        DELETE FROM public.Staging_Budget 
        WHERE UserId = userid;
    END IF;

END;
$BODY$
LANGUAGE plpgsql;

                

                    
SELECT 
    st1."FacilityId"::TEXT || TO_CHAR(st1."Year" * 100 + st1."Month", 'FM00000000') AS "FacilityId",
    st1."Organization",
    st1."ContractNo",
    st1."Currency",
    st1."Year",
    st1."Month",
    SUM(st1."Expense") AS "Expense",
    SUM(st1."Expense" * st1."ICR" / 100::DOUBLE PRECISION) AS "IndCost",
    SUM(st1."Expense") + SUM(st1."Expense" * st1."ICR" / 100::DOUBLE PRECISION) AS "Total",
    st1."TenantId",
    fi."facilityId" || '-' || fi."hfName" AS "Facility",
    ft."facTypeShort" AS "FacilityType"
FROM "facilityTypes" ft
JOIN "facilityInfo" fi ON ft."facTypeCode" = fi."facilityTypeId"
JOIN "Staging_NTransaction_slot1" st1 ON fi."facilityId" = st1."FacilityId"
GROUP BY 
    st1."FacilityId", st1."Currency", st1."TenantId", st1."ContractNo", 
    st1."Organization", fi."facilityId", fi."hfName", ft."facTypeShort", 
    st1."Year", st1."Month"

UNION ALL

SELECT 
    st2."FacilityId"::TEXT || TO_CHAR(st2."Year" * 100 + st2."Month", 'FM00000000') AS "FacilityId",
    st2."Organization",
    st2."ContractNo",
    st2."Currency",
    st2."Year",
    st2."Month",
    SUM(st2."Expense") AS "Expense",
    SUM(st2."Expense" * st2."ICR" / 100::DOUBLE PRECISION) AS "IndCost",
    SUM(st2."Expense") + SUM(st2."Expense" * st2."ICR" / 100::DOUBLE PRECISION) AS "Total",
    st2."TenantId",
    fi."facilityId" || '-' || fi."hfName" AS "Facility",
    ft."facTypeShort" AS "FacilityType"
FROM "facilityTypes" ft
JOIN "facilityInfo" fi ON ft."facTypeCode" = fi."facilityTypeId"
JOIN "Staging_NTransaction_slot2" st2 ON fi."facilityId" = st2."FacilityId"
GROUP BY 
    st2."FacilityId", st2."Currency", st2."TenantId", st2."ContractNo", 
    st2."Organization", fi."facilityId", fi."hfName", ft."facTypeShort", 
    st2."Year", st2."Month"

UNION ALL

SELECT 
    st3."FacilityId"::TEXT || TO_CHAR(st3."Year" * 100 + st3."Month", 'FM00000000') AS "FacilityId",
    st3."Organization",
    st3."ContractNo",
    st3."Currency",
    st3."Year",
    st3."Month",
    SUM(st3."Expense") AS "Expense",
    SUM(st3."Expense" * st3."ICR" / 100::DOUBLE PRECISION) AS "IndCost",
    SUM(st3."Expense") + SUM(st3."Expense" * st3."ICR" / 100::DOUBLE PRECISION) AS "Total",
    st3."TenantId",
    fi."facilityId" || '-' || fi."hfName" AS "Facility",
    ft."facTypeShort" AS "FacilityType"
FROM "facilityTypes" ft
JOIN "facilityInfo" fi ON ft."facTypeCode" = fi."facilityTypeId"
JOIN "Staging_NTransaction_slot3" st3 ON fi."facilityId" = st3."FacilityId"
GROUP BY 
    st3."FacilityId", st3."Currency", st3."TenantId", st3."ContractNo", 
    st3."Organization", fi."facilityId", fi."hfName", ft."facTypeShort", 
    st3."Year", st3."Month"

UNION ALL

SELECT 
    st4."FacilityId"::TEXT || TO_CHAR(st4."Year" * 100 + st4."Month", 'FM00000000') AS "FacilityId",
    st4."Organization",
    st4."ContractNo",
    st4."Currency",
    st4."Year",
    st4."Month",
    SUM(st4."Expense") AS "Expense",
    SUM(st4."Expense" * st4."ICR" / 100::DOUBLE PRECISION) AS "IndCost",
    SUM(st4."Expense") + SUM(st4."Expense" * st4."ICR" / 100::DOUBLE PRECISION) AS "Total",
    st4."TenantId",
    fi."facilityId" || '-' || fi."hfName" AS "Facility",
    ft."facTypeShort" AS "FacilityType"
FROM "facilityTypes" ft
JOIN "facilityInfo" fi ON ft."facTypeCode" = fi."facilityTypeId"
JOIN "Staging_NTransaction_slot4" st4 ON fi."facilityId" = st4."FacilityId"
GROUP BY 
    st4."FacilityId", st4."Currency", st4."TenantId", st4."ContractNo", 
    st4."Organization", fi."facilityId", fi."hfName", ft."facTypeShort", 
    st4."Year", st4."Month";