create or replace FUNCTION calculateUtilized(
    capitalization_date Date,
    write_off_date_actual DATE,
    usefulLife NUMBER,
    p_end_date DATE
) RETURN NUMBER
AS
    utilized NUMBER;
BEGIN
    -- Calculate the difference in months based on the condition
--    IF  (write_off_date_actual is  null or (write_off_date_actual is not null and write_off_date_actual > SYSDATE)) THEN 
        utilized := trunc(MONTHS_BETWEEN(  p_end_date,calculate_firstDayOfNextMonth_a(capitalization_date)));
--           
--
--    ELSE
--        utilized := CEIL(MONTHS_BETWEEN(write_off_date_actual, SYSDATE));



--    END IF;

    -- Set to 0 if negative
    IF utilized < 0 THEN
        utilized := 0;


    END IF;

    -- Cap at usefulLife
    IF utilized > usefulLife THEN
        utilized := usefulLife;


    END IF;

    RETURN utilized;
END calculateUtilized;
/
create or replace FUNCTION calculateAccumulatedDep(
    initial_cost NUMBER,
    salvage_value NUMBER,
    useful_life_months NUMBER,
    adjustment NUMBER,
    write_off_date_actual DATE,
    capitalization_date DATE,
    enable3digitsRounding VARCHAR2,
    p_end_date DATE
) RETURN NUMBER
AS
    accumulated_dep NUMBER;

BEGIN


    -- Check the preferencesvalue and calculate monthly_dep accordingly
    IF enable3digitsRounding = 'true' THEN

              accumulated_dep:=  LEAST(trunc((calculateMonthlyDep(trunc(initial_cost,3), trunc(NVL(salvage_value,0),3), trunc(NVL(useful_life_months,0),3),enable3digitsRounding) * calculateUtilized(capitalization_date,write_off_date_actual,NVL(useful_life_months,0), p_end_date) +  NVL(trunc(adjustment,3), 0)),3),  trunc((initial_cost - NVL(salvage_value,0)),3));

            ELSE
              accumulated_dep:=  LEAST(trunc((calculateMonthlyDep(initial_cost, NVL(salvage_value,0), NVL(useful_life_months,0),enable3digitsRounding) * calculateUtilized(capitalization_date,write_off_date_actual,NVL(useful_life_months,0), p_end_date) +  NVL(adjustment, 0)),9), trunc( (initial_cost - NVL(salvage_value,0)),9));

        END IF;

    RETURN accumulated_dep;

END calculateAccumulatedDep;
/
create or replace FUNCTION calculateNetCost(
    initial_cost NUMBER,
    salvage_value NUMBER,
    useful_life_months NUMBER,
    adjustment NUMBER,
    write_off_date_actual DATE,
    capitalization_date DATE,
    enable3digitsRounding VARCHAR2,
    p_end_date DATE

) RETURN NUMBER
AS
    net_cost NUMBER;

BEGIN
   IF enable3digitsRounding = 'true'  THEN
               net_cost:= greatest(trunc(trunc(initial_cost,3) - calculateAccumulatedDep(trunc(initial_cost,3) ,trunc(NVL(salvage_value,0),3) , trunc(NVL(useful_life_months,0),3) ,trunc(NVL(adjustment, 0),3) , write_off_date_actual ,capitalization_date,enable3digitsRounding, p_end_date),3),trunc(NVL(salvage_value,0),3));


            ELSE
               net_cost:= greatest(trunc(initial_cost - calculateAccumulatedDep(initial_cost ,NVL(salvage_value,0) , NVL(useful_life_months,0) ,NVL(adjustment, 0) , write_off_date_actual ,capitalization_date,enable3digitsRounding, p_end_date ),9),NVL(salvage_value,0));

end if;
 IF calculateUtilized( capitalization_date,write_off_date_actual, NVL(useful_life_months,0), p_end_date) = useful_life_months THEN
        net_cost := NVL(salvage_value,0);

    END IF;


    RETURN net_cost;

END calculateNetCost;
/
CREATE OR REPLACE FUNCTION get_consolidated_assets_by_date(
    p_start_date IN DATE,
    p_end_date   IN DATE,
    p_oracle_asset_id IN VARCHAR2 DEFAULT NULL,  
    p_po_number IN VARCHAR2 DEFAULT NULL,
     p_po_date IN DATE DEFAULT NULL, 
     p_version_id IN VARCHAR2
) RETURN SYS_REFCURSOR
AS
    result_cursor SYS_REFCURSOR;
BEGIN
    OPEN result_cursor FOR
        SELECT
           TRIM(REPLACE(REPLACE(oracle_asset_id, CHR(13), ''), CHR(10), '')) AS oracle_asset_id,
            version_id,
            SUM(initial_cost) AS initial_cost,
            LISTAGG(DISTINCT NVL(salvage_value, '0'), ';' ON OVERFLOW TRUNCATE) 
                WITHIN GROUP (ORDER BY NVL(salvage_value, '0')) AS salvage_value,
            LISTAGG(DISTINCT useful_life_months, ',' ON OVERFLOW TRUNCATE) 
                WITHIN GROUP (ORDER BY useful_life_months) AS useful_life_months,
            SUM(
                CASE
                    WHEN enable3digitsroubingpref.preferencesvalue = 'true' THEN
                        TRUNC(adjustment, 3)
                    ELSE
                        adjustment
                END
            ) AS adjustment,
            LISTAGG(DISTINCT TO_CHAR(capitalization_date, 'DD-MON-YY'), ',' ON OVERFLOW TRUNCATE) 
                WITHIN GROUP (ORDER BY capitalization_date) AS capitalization_date,
            LISTAGG(DISTINCT po_number, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY po_number) AS po_number,
            LISTAGG(DISTINCT po_date, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY po_date) AS po_date,
            LISTAGG(DISTINCT vendor_name, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY vendor_name) AS vendor_name,
            LISTAGG(DISTINCT vendor_number, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY vendor_number) AS vendor_number,
            LISTAGG(DISTINCT project_number, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY project_number) AS project_number,
            LISTAGG(DISTINCT po_line_number, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY po_line_number) AS po_line_number,
            LISTAGG(DISTINCT release_number, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY release_number) AS release_number,
            SUM(
                CASE
                    WHEN calculatenetcost(initial_cost, salvage_value, useful_life_months, adjustment, write_off_date_actual,
                                          capitalization_date, enable3digitsroubingpref.preferencesvalue, p_end_date) = 0
                         AND setmonthlydepto0pref.preferencesvalue = 'true' THEN
                        0
                    ELSE
                        calculatemonthlydep(initial_cost, salvage_value, useful_life_months, enable3digitsroubingpref.preferencesvalue)
                END
            ) AS monthly_dep,
            SUM(
                calculatenetcost(initial_cost, salvage_value, useful_life_months, adjustment, write_off_date_actual,
                                 capitalization_date, enable3digitsroubingpref.preferencesvalue, p_end_date)
            ) AS netcost,
            SUM(
                CASE
                    WHEN useful_life_months = calculateutilized(capitalization_date, write_off_date_actual, useful_life_months, p_end_date) THEN
                        CASE
                            WHEN enable3digitsroubingpref.preferencesvalue = 'true' THEN
                                TRUNC((initial_cost - NVL(salvage_value, 0)), 3)
                            ELSE
                                ROUND((initial_cost - NVL(salvage_value, 0)), 9)
                        END
                    ELSE
                        calculateaccumulateddep(initial_cost, salvage_value, useful_life_months, adjustment, write_off_date_actual,
                                                capitalization_date, enable3digitsroubingpref.preferencesvalue, p_end_date)
                END
            ) AS accumulated_depreciation,
            LISTAGG(TO_CHAR(calculate_firstdayofnextmonth_a(capitalization_date), 'DD-MON-YY'), ',' ON OVERFLOW TRUNCATE) 
                WITHIN GROUP (ORDER BY calculate_firstdayofnextmonth_a(capitalization_date)) AS d_a
        FROM
            fr_asset fr,
            nepgeneralpreferences enable3digitsroubingpref,
            nepgeneralpreferences setmonthlydepto0pref
        WHERE
            oracle_asset_id IS NOT NULL
            AND installation_date IS NOT NULL
            AND capitalization_date IS NOT NULL
            AND initial_cost IS NOT NULL
            AND useful_life_months IS NOT NULL
            AND enable3digitsroubingpref.preferencestype = '63'
            AND setmonthlydepto0pref.preferencestype = '65'
            AND (
                write_off_date IS NULL
                OR (write_off_date IS NOT NULL AND write_off_date_actual IS NOT NULL AND write_off_date_actual > SYSDATE)
            )
            AND capitalization_date BETWEEN p_start_date AND p_end_date
            AND (p_oracle_asset_id IS NULL OR TRIM(REPLACE(REPLACE(oracle_asset_id, CHR(13), ''), CHR(10), '')) = p_oracle_asset_id) 
            AND (p_po_date IS NULL OR po_date = p_po_date)  
AND (
    p_po_number IS NULL
    OR EXISTS (
        SELECT 1
        FROM (
            SELECT TRIM(REGEXP_SUBSTR(p_po_number, '[^,]+', 1, LEVEL)) AS po_val
            FROM dual
            CONNECT BY LEVEL <= REGEXP_COUNT(p_po_number, ',') + 1
        )
        WHERE po_val = fr.po_number
    )
)            and fr.version_id=p_version_id
        GROUP BY
          TRIM(REPLACE(REPLACE(oracle_asset_id, CHR(13), ''), CHR(10), '')),
            version_id;
 
    RETURN result_cursor;
END;