
 / 

 / 

 / 

 / 

  CREATE OR REPLACE FUNCTION FN_GET_TAXABLE_AMT (
    p_tran_id IN VARCHAR2,
    p_line_no IN NUMBER
) RETURN NUMBER IS
    v_taxable_amt NUMBER;
BEGIN
    SELECT taxtran.taxable_amt
    INTO v_taxable_amt
    FROM taxtran
    INNER JOIN tax
        ON TAXTRAN.TAX_CODE = TAX.TAX_CODE
    LEFT JOIN sreturndet
        ON taxtran.TRAN_ID = sreturndet.TRAN_ID
       AND taxtran.LINE_NO = FN_RCON_LINENO_NUM(sreturndet.line_no)
       AND NVL(sreturndet.ret_rep_flag, 'P') = 'R'
    WHERE taxtran.TRAN_ID = p_tran_id
      AND trim(taxtran.LINE_NO) = trim(p_line_no)
      AND TAXTRAN.TRAN_CODE = 'S-RET'
      AND tax.print_tax = 'Y'
      AND taxtran.tax_amt <> 0;

    RETURN v_taxable_amt;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

 / 

  CREATE OR REPLACE FUNCTION FN_GET_TAXTRAN_SUM (
    p_site_code       IN VARCHAR2,
    p_tran_date_start IN DATE,
    p_tran_date_end   IN DATE,
    p_item_ser_start  IN VARCHAR2,
    p_item_ser_end    IN VARCHAR2,
    p_cust_code_start IN VARCHAR2,
    p_cust_code_end   IN VARCHAR2,
    p_confirmed       IN VARCHAR2
) RETURN NUMBER IS
    v_sum_tax_amt NUMBER;
BEGIN
    SELECT SUM(taxtran.tax_amt)
    INTO v_sum_tax_amt
    FROM taxtran
    JOIN tax
        ON taxtran.tax_code = tax.tax_code
    JOIN sreturn
        ON taxtran.tran_id = sreturn.tran_id
    JOIN sreturndet
        ON sreturn.tran_id = sreturndet.tran_id
       AND taxtran.line_no = fn_rcon_lineno_num(sreturndet.line_no)
    WHERE taxtran.tran_code = 'S-RET'
      AND NVL(sreturndet.ret_rep_flag, 'P') = 'R'
      AND sreturn.confirmed = p_confirmed
      AND sreturn.site_code IN (
          SELECT COLUMN_VALUE
          FROM TABLE(fn_get_site_input(p_site_code))
      )
      AND sreturn.tran_date BETWEEN p_tran_date_start AND p_tran_date_end
      AND sreturn.item_ser BETWEEN p_item_ser_start AND p_item_ser_end
      AND sreturn.cust_code BETWEEN p_cust_code_start AND p_cust_code_end
      AND tax.print_tax = 'Y'
      AND taxtran.tax_amt <> 0;

    RETURN v_sum_tax_amt;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
    WHEN OTHERS THEN
        RAISE;
END;

 / 

  CREATE UNIQUE INDEX SYS_C0083677 ON EXT_DB_CONNECTION (DB_CONN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0082542 ON SF_PLAN_PARAM (PARAM_TABLE, PRD_CODE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_INDEX ;
 / 

  CREATE OR REPLACE TRIGGER DT_WO_CANCEL BEFORE INSERT or UPDATE OF STATUS ON workorder
referencing old as old new as new FOR EACH ROW
DECLARE
BEGIN
    if :new.status = 'X' and :old.status <> 'X' then
      :new.lot_no := null;
    END IF;
END;


 / 

  CREATE OR REPLACE FUNCTION FN_VALIDATE_EXP_RES (
    EXP_RES IN VARCHAR2,  
    SPEC_REPR IN CHAR
)
RETURN NUMBER 
AS
    MIN_VAL NUMBER;
    MAX_VAL NUMBER;
    RESULT NUMBER := 0;         
BEGIN

    IF SPEC_REPR = 'R' THEN

        IF REGEXP_LIKE(EXP_RES, '^\s*\d+(\.\d+)?\s*-\s*\d+(\.\d+)?\s*$') THEN

            MIN_VAL := TO_NUMBER(REGEXP_SUBSTR(EXP_RES, '^\s*(\d+(\.\d+)?)\s*-', 1, 1, NULL, 1));
            MAX_VAL := TO_NUMBER(REGEXP_SUBSTR(EXP_RES, '-\s*(\d+(\.\d+)?)\s*$', 1, 1, NULL, 1));


            IF MIN_VAL <= MAX_VAL THEN
                RESULT := 1; 
            ELSE
                RESULT := 0; 
            END IF;
        ELSE
            RESULT := 0; 
        END IF;


    ELSIF SPEC_REPR = 'N' THEN

        IF REGEXP_LIKE(EXP_RES, '^-?\d+(\.\d+)?$') THEN
            RESULT := 1; 
        ELSE
            RESULT := 0; 
        END IF;

    ELSE

        RESULT := 1;
    END IF;


    RETURN RESULT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RESULT := 0;
    WHEN VALUE_ERROR THEN
        RESULT := 0; 
    WHEN OTHERS THEN
        RESULT := 0; 



    RETURN RESULT;
END;

 / 

  CREATE UNIQUE INDEX SYS_C0083396 ON DASHBOARD_DEFINITION (PROC_DESIGN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_EXTRACT_ATTRIBUTE_VALUE (
  p_attrib_format IN VARCHAR2,
  p_attrib_value IN VARCHAR2,  -- Changed from NUMBER to VARCHAR2
  p_value_type IN NUMBER
) RETURN VARCHAR2 IS
  v_extracted_value VARCHAR2(4000);
  v_code_value VARCHAR2(4000);
  v_image_path VARCHAR2(4000);
BEGIN
  -- Extract the corresponding attribute value
  BEGIN
    SELECT extracted_value 
    INTO v_extracted_value
    FROM (
      WITH extracted_data AS (
        SELECT 
          REGEXP_SUBSTR(p_attrib_format, '~~([^:]+):', 1, LEVEL, NULL, 1) AS extracted_value,
          REGEXP_SUBSTR(p_attrib_format || '~~', ':([^~~]+)~~', 1, LEVEL, NULL, 1) AS value
        FROM dual
        CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(p_attrib_format, '[^~~]', '')) + 1
      )
      SELECT extracted_value 
      FROM extracted_data
      WHERE value = p_attrib_value
    ) WHERE ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN v_extracted_value := ' ';
  END;

  -- Extract the code value
  BEGIN
    SELECT value 
    INTO v_code_value
    FROM (
      WITH extracted_data AS (
        SELECT 
          REGEXP_SUBSTR(p_attrib_format || '~~', ':([^~~]+)~~', 1, LEVEL, NULL, 1) AS value
        FROM dual
        CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(p_attrib_format, '[^~~]', '')) + 1
      )
      SELECT value FROM extracted_data
      WHERE value = p_attrib_value
    ) WHERE ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN v_code_value := ' ';
  END;

  -- Determine image path
  IF p_attrib_format LIKE '%image%' THEN
    v_image_path := '/wildfly/server/default/deploy/ibase.ear/ibase.war/webitm/images/' || NVL(v_code_value, 'default') || '.svg';
  ELSE
    v_image_path := null;
  END IF;

  -- Return based on value type
  CASE p_value_type
    WHEN 1 THEN RETURN NVL(v_extracted_value, 'UNKNOWN');
    WHEN 2 THEN RETURN v_image_path;
    WHEN 3 THEN RETURN NVL(v_code_value, 'UNKNOWN');
    ELSE RETURN NULL;
  END CASE;

EXCEPTION
  WHEN OTHERS THEN
    RETURN 'ERROR';
END fn_extract_attribute_value;

 / 

  CREATE UNIQUE INDEX RESIGNATION_FORM_X ON RESIGNATION_FORM (RESIGNATION_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0083439 ON ENTITY_DOC_SHARE (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE TRIGGER STOPBATCHLOAD before insert or update on batchload for each row 
begin
    if trim(:new.chg_user) in ('ESCTEST10','BIPIN') then
        raise_application_error(-20025,'Not allowed');
    end if;
end;


 / 

  CREATE UNIQUE INDEX SYS_C001105676 ON USERS_GEOFENCE (USER_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C001102883 ON EMP_EXIT_CLEARANCE_CHECKLIST (CLEARANCE_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX EMP_EXITFORM_HDR_X ON EMP_EXITFORM_HDR (EXIT_INTERVIEW_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX PK_VISUAL_DESIGN_HISTORY ON VISUAL_DESIGN_HISTORY (SEQ_NO, VIS_DESIGN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C00152286 ON WO_OBSRV_HDR (TRAN_ID, WORK_ORDER, OPERATION, SEQ_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

   CREATE SEQUENCE  TIMESLOT_INVTRACE_SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 270 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
 / 

  CREATE UNIQUE INDEX SYS_C001035529 ON TRANS_ITEM_ATTRIBUTES (REF_SER, REF_ID, ATTRIB_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0082581 ON AUDIT_TRAIL_DOC (CHG_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0083394 ON NET_PRINTERS (PRINTER_NAME) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DATA ;
 / 

  CREATE UNIQUE INDEX SYS_C0084086 ON CONDN_ACC (CONDN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_INDEX ;
 / 

  CREATE OR REPLACE FUNCTION FN_DESP_DET_TRANFLOW (as_despid char)
return varchar2 is v_str varchar2(32767);
type rc is ref cursor;

v_str_tmp varchar2(1000);
v_str_tmp1 varchar2(1000);
v_str_tmp2 varchar2(1000);
v_str_tmp3 varchar2(1000);
lc_cnt number(3);
 c rc;

begin
 begin
  lc_cnt := 0;
open c for
select a.descr,b.QUANTITY ,b.rate__stduom from item a ,despatchdet b
where a.item_code = b.item_code
and b.desp_id=as_despid
and rownum <= 20;
 loop
 fetch c into v_str_tmp,v_str_tmp1,v_str_tmp2;
 exit when c%notfound;
 v_str := v_str||'<TR>'||'<TD>'||v_str_tmp||'</TD>'||'<TD>'||v_str_tmp1||'</TD>'||'<TD>'||v_str_tmp2||'</TD>'||'</TR>';
 end loop;
V_STR:=SUBSTR(V_STR,1,LENGTH(V_STR));
V_STR:='<TR>'||'<TD>'||
' Item Description '||'</TD>'||'<TD>'||'Quantity'||'</TD>'||'<TD>'||'Rate'||'</TD>'||'</TR>'||V_STR;
exception when no_data_found then
v_str:=' ';
end;
close c;
 return v_str;
end;

 / 

  CREATE UNIQUE INDEX SYS_C0083150 ON COSTALLOC_BAS (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_FIN_I1 ;
 / 

  CREATE OR REPLACE FUNCTION FN_RGET_TAX_NEW_NL 
(ls_tran_code in char,
ls_tran_id in char,
ls_line_no in char,
ls_disparm_code in char,
ls_disparm_code_1 in char,
ls_disparm_code_2 in char,
ls_type in char)
 
return number is lc_tax_amt number(14,3);

LS_VAR_VALUE VARCHAR(2000);
LS_VAR_VALUE1 VARCHAR(2000);
LS_VAR_VALUE2 VARCHAR(2000);
as_line_no char(3);
lc_exch_rate number(17,6);

begin

begin
---as_line_no := substr('      ' + ltrim(rtrim(ls_line_no)),length(ltrim(rtrim(ls_line_no))) - 2);
if length(ltrim(rtrim(ls_line_no))) = 1 then
   AS_LINE_NO := '  ' || RTRIM(LTRIM(LS_LINE_NO));
end if;
if length(ltrim(rtrim(ls_line_no))) = 2 then
   as_line_no := ' ' || rtrim(ltrim(ls_line_no));
end if;
if length(ltrim(rtrim(ls_line_no))) = 3 then
   as_line_no := rtrim(ltrim(ls_line_no));
end if;

select (rtrim(var_value))
  into ls_var_value
  from disparm
  where prd_code = '999999'
  AND VAR_NAME = LS_DISPARM_CODE;



--tax amt
if ls_type = 'T' then
select nvl(sum(nvl(Tax_Amt,0)),0)
   into lc_tax_amt
   from taxtran
  where tran_code = ls_tran_code
    and tran_id   = ls_tran_id
    AND LINE_NO = AS_LINE_NO
    --and instr(ls_var_value,(','||rtrim(tax_code)||',')) > 0 ;
    AND TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE)) );


-- taxable amt
ELSIF ls_type = 'B' then
select nvl(sum(nvl(Taxable_amt,0)),0)
   into lc_tax_amt
   from taxtran
  where tran_code = ls_tran_code
    and tran_id   = ls_tran_id
    AND LINE_NO = AS_LINE_NO
    --and instr(ls_var_value,(','||rtrim(tax_code)||',')) > 0 ;
    AND TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE)) );


-- tax %
ELSIF ls_type = 'P' then
select MAX(tax_perc)
   into lc_tax_amt
   from taxtran
  where tran_code = ls_tran_code
    and tran_id   = ls_tran_id
    AND LINE_NO = AS_LINE_NO
    --AND INSTR(LS_VAR_VALUE,(','||RTRIM(TAX_CODE)||',')) > 0
    AND TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE)) )
    AND TAX_AMT <>0 ;

-- taxable amt
ELSIF ls_type = 'A' then
select (rtrim(var_value))
  INTO LS_VAR_VALUE1
  from disparm
  where prd_code = '999999'
  and var_name = ls_disparm_code_1;

  SELECT (RTRIM(VAR_VALUE))
  into LS_VAR_VALUE2
  FROM DISPARM
  where prd_code = '999999'
  and var_name = ls_disparm_code_2;

select nvl(sum(CASE
                WHEN tax_type = 'H'
                    THEN nvl(taxable_amt, 0)
                WHEN tax_type = 'I'
                    THEN nvl(taxable_amt, 0)
                WHEN tax_type = 'J'
                    THEN nvl(taxable_amt, 0)
                ELSE 0
                END), 0) into lc_tax_amt
   from taxtran,tax
  WHERE tax.tax_code = taxtran.tax_code
    and tran_code = ls_tran_code
    and tran_id   = ls_tran_id
    and line_no = as_line_no
    And tax_perc <> 0
    AND (TAX.TAX_TYPE) IN ('H','I','J')
    AND
   (

TAX.TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE)) )

OR
TAX.TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE1)) )

OR
   TAX.TAX_CODE IN ( SELECT COLUMN_VALUE  FROM TABLE(CSV_TO_ROW(LS_VAR_VALUE2)) )
);
end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
lc_tax_amt  := 0 ;
END ;

RETURN LC_TAX_AMT;
END;


 / 

  CREATE UNIQUE INDEX SYS_C0083820 ON COURSE_CHAPTERS (COURSE_CHAPTER) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_WAGE_AMT (as_tranid in char, as_workorder in char )
return  number is
lc_wage number(14,3) := 0;
lc_quantity number(14,3) := 0;

begin

	begin 
        select QTY_REPORTED into lc_quantity from workorder_feedback where tran_id = as_tranid;
    exception when others then
        lc_quantity := 0 ;
	end; 
    lc_wage := lc_quantity * 125 ;

	return lc_wage; 
end;

 / 

  CREATE INDEX ALLWDEDN_DESCR ON ALLWDEDN_BKP (DESCR) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_ADM_I1 ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_ITI_WFAPPRV (
    as_user_code CHAR, 
    as_aprv_lev NUMBER
) RETURN CHAR IS 
    ls_ret_emp_code CHAR(10);
    ls_smgrfac CHAR(10); 
    ls_riskhd CHAR(10);
    lc_actceo CHAR(10);
BEGIN
    ls_ret_emp_code := '';

    IF as_aprv_lev = 1 THEN
        SELECT var_value INTO ls_smgrfac 
        FROM finparm 
        WHERE var_name = 'WF_SMGRFAC';
        ls_ret_emp_code := ls_smgrfac;

    ELSIF as_aprv_lev = 2 THEN
        SELECT var_value INTO ls_riskhd 
        FROM finparm 
        WHERE var_name = 'WF_RISKHD';
        ls_ret_emp_code := ls_riskhd;

    ELSIF as_aprv_lev = 3 THEN
        SELECT var_value INTO lc_actceo 
        FROM finparm 
        WHERE var_name = 'WF_ACTCEO';
        ls_ret_emp_code := lc_actceo;

    END IF;

    RETURN ls_ret_emp_code;
END;

 / 

  CREATE UNIQUE INDEX SYS_C00708548 ON TNC_IGNORE (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0082938 ON SORDITEM_SPEC (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE INDEX KY_PRD_DATE ON PERIOD (FR_DATE, TO_DATE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_INDEX ;
 / 

  CREATE OR REPLACE TRIGGER DT_CHK_DESP_COSTRATE 
BEFORE insert or update  ON despatchdet
referencing old as old new as new
FOR EACH ROW
declare
   ll_count number(3) := 0;
   lc_invrate stock.rate%type := 0;
   ls_sitecode char(5);

BEGIN
    begin
        select site_code into ls_sitecode from sorddet where sale_order = :new.sord_no and line_no = :new.line_no__sord;
    exception when others then
        ls_sitecode := ' ';
    end;
    if length(trim(ls_sitecode)) > 0 then
    begin
        select ROUND(rate, 4) into lc_invrate from stock 
        where item_code = :new.item_code
        and site_code = ls_sitecode
        and loc_code = :new.loc_code
        and lot_no = :new.lot_no
        and lot_sl = :new.lot_sl;
    exception when others then
        lc_invrate := 0;
    end;

       if nvl(:new.cost_rate,0) <> nvl(lc_invrate,0) then
            raise_application_error( -20601, 'For line # [' ||:new.line_no ||'] cost_rate [' || to_char(:new.cost_rate) || '] not matching with stock rate [' || to_char(lc_invrate) || ']' );
       end if;

    end if;
END ;


 / 

  CREATE OR REPLACE FUNCTION FN_GET_DATE_YEAR (as_tran_date IN VARCHAR2)
RETURN VARCHAR2
IS
  tran_date DATE;
  current_date VARCHAR2(2);
  current_year VARCHAR2(4);
  result VARCHAR2(6);
BEGIN
  -- Convert input string to a date
  tran_date := TO_DATE(as_tran_date, 'DD-MM-YY');

  -- Extract day and year
  current_date := TO_CHAR(tran_date, 'DD');
  current_year := TO_CHAR(tran_date, 'YYYY');

  -- Concatenate day and year
  result := current_date || current_year;

  RETURN result;
END;

 / 

  CREATE UNIQUE INDEX SYS_C001105005 ON CRM_CONTACTS (CONTACT_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_PORCPTRANSER (as_tranid in char)
return char
Is
ls_transer porcp.tran_ser%type;
begin
    begin
        select trim(tran_ser) into ls_transer from porcp where tran_id = as_tranid;
    exception when others then
        ls_transer := ' ';
    end;
    return ls_transer;
end;

 / 

  CREATE UNIQUE INDEX SYS_C001089432 ON MAILING_LIST (MAIL_LIST_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C001089431 ON MKTG_CAMPAIGN (CAMPAIGN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0083382 ON DWH_TABLES (TABLE_NAME) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX EMP_EXITFORM_EMP_X ON EMP_EXITFORM_EMP (EXIT_INTERVIEW_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX PK_SALES_QUOTDET_HIST ON SALES_QUOTDET_HIST (QUOT_NO, LINE_NO, VERSION_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION BUILD_ALPHANUMERIC_STRINGS (
    str1 IN VARCHAR2 
) RETURN VARCHAR2 DETERMINISTIC IS 
    -- Local Variables
    clean_str1 VARCHAR2(4000);
    i NUMBER;
    char_code NUMBER;
    result VARCHAR2(4000);

BEGIN

    -- Initialize cleaned strings
    clean_str1 := '';

    -- Clean str1 - Keep only alphanumeric characters
    IF str1 IS NOT NULL THEN
        FOR i IN 1..LENGTH(str1) LOOP
            char_code := ASCII(SUBSTR(str1, i, 1));

            -- Check if character is alphanumeric (A-Z, a-z, 0-9)
            IF (char_code BETWEEN 48 AND 57) OR  -- 0-9
               (char_code BETWEEN 65 AND 90) OR  -- A-Z
               (char_code BETWEEN 97 AND 122) THEN -- a-z
                clean_str1 := clean_str1 || UPPER(SUBSTR(str1, i, 1));
            END IF;
        END LOOP;
    END IF;
    RESULT := clean_str1;
    RETURN result;

EXCEPTION
    WHEN OTHERS THEN
        -- Log the error (optional)
        -- INSERT INTO error_log (error_message, timestamp) VALUES (SQLERRM, SYSTIMESTAMP);
        RAISE; -- Re-raise the exception
END build_alphanumeric_strings;

 / 

  CREATE UNIQUE INDEX SYS_C0083611 ON DWH_PURCHASE (RECEIPT_NO, ITEM_CODE, LOT_NO, LOT_SL, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DIS_I1 ;
 / 

  CREATE UNIQUE INDEX SYS_C0083699 ON SF_PLAN_PARAM_STAN (PARAM_TABLE, PRD_CODE, STAN_TYPE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_INDEX ;
 / 

  CREATE OR REPLACE TRIGGER DT_CHK_MDRCR_SO 
BEFORE INSERT ON misc_drcr_rcp FOR EACH ROW
declare
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ls_status char(1);

BEGIN
    if trim(:new.item_ser) = 'FLAT' and :new.sale_order is not null and length(trim(:new.sale_order)) > 0 then
        select status into ls_status from sorder where sale_order = :new.sale_order;
        if ls_status = 'C' or ls_status = 'X' then
            raise_application_error( -20601, 'Sales order  [' || trim(:new.sale_order) || '] status is closed/cancelled demad cannot be generated');
        end if;
    end if;
END ;


 / 

  CREATE UNIQUE INDEX SYS_C0083258 ON HTL_PAX_HDR (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DIS_I1 ;
 / 

  CREATE UNIQUE INDEX SYS_C0082960 ON UPTIME_LOG (REQUEST_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DATA ;
 / 

  CREATE UNIQUE INDEX SYS_C0083530 ON RESOURCE_BASIS (RES_BAS) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_FIN_I1 ;
 / 

  CREATE UNIQUE INDEX SYS_C0083650 ON SITE_REPL_PREF (SITE_CODE, SITE_CODE__SUPP) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0083374 ON EMP_SERVICEPRD_AMD (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION DDF_GET_REPRINTCNT (as_parameter_val char, as_report_name char) 
 return number is rptcnt rpt_print_log.REPRINT_CNT%type; 
begin 
SELECT (MAX(REPRINT_CNT)) into rptcnt FROM rpt_print_log WHERE REPORT_NAME=as_report_name 
AND PARAMETER_VAL=as_parameter_val ; 
return rptcnt; 
end;	


 / 

  CREATE OR REPLACE TRIGGER DT_CHK_CLOSEREF 
BEFORE insert or UPDATE of status ON porddet FOR EACH ROW
declare
ls_invacct finparm.var_value%type; 
ls_refser char(6);


BEGIN
   if :new.status = 'C' and :old.status <> 'C' and (:new.REF_ID__CLOSE is null or :new.CLOSE_PO is null) then
       raise_application_error( -20601, 'order detail status is updated as C (Closed) but reference id closing is null');
   end if;
END ;


 / 

  CREATE UNIQUE INDEX WO_OBSRV_DET_X ON WO_OBSRV_DET (TRAN_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX TRANSPORTER_BILL_CHGS_X ON TRANSPORTER_BILL_CHGS (TRAN_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0082568 ON ROUTE_STATUS_HIST (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

   CREATE SEQUENCE  RPTCTR  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
 / 

  CREATE OR REPLACE FUNCTION FN_GETITEMPRINTAREA (as_item_code char)
return number is
prnt_area number(5) :=0;
begin
	select (nvl(phy_attrib_8,0) * nvl(phy_attrib_9,0)) into prnt_area from item where item_code =as_item_code;

return prnt_area;
end;

 / 

  CREATE UNIQUE INDEX SYS_C0083090 ON VQ_PROC_RET (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DIS_I1 ;
 / 

  CREATE UNIQUE INDEX EMP_CLEARANCE_HDR_X ON EMP_EXIT_CLEARANCE (CLEARANCE_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX EMP_EXITFORM_HR_X ON EMP_EXITFORM_HR (EXIT_INTERVIEW_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SORDERTYPE_DET_PK ON SORDERTYPE_DET (ORDER_TYPE, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_MAX_LTRAN_DATE (
    p_site_code  IN VARCHAR2,
    p_item_code  IN VARCHAR2,
    p_lot_no     IN VARCHAR2,
    p_loc_code   IN VARCHAR2
) RETURN DATE IS
    v_max_ltran_date DATE;
BEGIN
    SELECT MAX(LTRAN_DATE)
    INTO v_max_ltran_date
    FROM stock
    WHERE site_code = p_site_code
      AND item_code = p_item_code
      AND lot_no = p_lot_no
      AND loc_code = p_loc_code;

    RETURN v_max_ltran_date;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END fn_get_max_ltran_date;

 / 

  CREATE OR REPLACE FUNCTION GET_MAX_LTRAN_DATE (
    p_site_code  IN VARCHAR2,
    p_item_code  IN VARCHAR2,
    p_lot_no     IN VARCHAR2,
    p_loc_code   IN VARCHAR2
) RETURN DATE IS
    v_max_ltran_date DATE;
BEGIN
    SELECT MAX(LTRAN_DATE)
    INTO v_max_ltran_date
    FROM stock
    WHERE site_code = p_site_code
      AND item_code = p_item_code
      AND lot_no = p_lot_no
      AND loc_code = p_loc_code;

    RETURN v_max_ltran_date;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_max_ltran_date;

 / 

  CREATE UNIQUE INDEX PK_SALES_QUOT_HIST ON SALES_QUOT_HIST (QUOT_NO, VERSION_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION DF_GETUSERPHONENO (user_code IN VARCHAR2)
RETURN VARCHAR2
IS
    phone_number VARCHAR2(30);
BEGIN
    -- Ensure the column and table exist
    SELECT mobile_no 
    INTO phone_number 
    FROM users 
    WHERE TRIM(code) = TRIM(user_code);

    RETURN phone_number;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'no data found'; 
    WHEN OTHERS THEN
        RETURN NULL; 
END;


 / 

  CREATE UNIQUE INDEX FREIGHT_RATE_DET_PK ON FREIGHT_RATE_DET (FRT_LIST, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_TAX_PERC (
    p_tran_id IN VARCHAR2,
    p_line_no IN NUMBER
) RETURN NUMBER IS
    v_tax_perc NUMBER;
BEGIN
    SELECT TAXTRAN.TAX_PERC
    INTO v_tax_perc
    FROM taxtran
    INNER JOIN tax
        ON TAXTRAN.TAX_CODE = TAX.TAX_CODE
    LEFT JOIN sreturndet
        ON taxtran.TRAN_ID = sreturndet.TRAN_ID
       AND taxtran.LINE_NO = FN_RCON_LINENO_NUM(sreturndet.line_no)
       AND NVL(sreturndet.ret_rep_flag, 'P') = 'R'
    WHERE trim(taxtran.TRAN_ID) = trim(p_tran_id)
      AND trim(taxtran.LINE_NO) = trim(p_line_no)
      AND TAXTRAN.TRAN_CODE = 'S-RET'
      AND tax.print_tax = 'Y'
      AND taxtran.tax_amt <> 0;

    RETURN v_tax_perc;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

 / 

  CREATE UNIQUE INDEX SYS_C0084139 ON DISTORDER_ALLOC (DIST_ORDER, LINE_NO, ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_DIS_I1 ;
 / 

  CREATE UNIQUE INDEX PK_ENTERPRISE ON ENTERPRISE_CP_ACC (ENTERPRISE, ENTERPRISE_ACC) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0084138 ON RESOURCE_CONS_DET (TRAN_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_FIN_I1 ;
 / 

  CREATE UNIQUE INDEX SYS_C0083322 ON CONDN_ACC_DET (CONDN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_INDEX ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_TAX_AMT (
    p_tran_id IN VARCHAR2,
    p_line_no IN NUMBER
) RETURN NUMBER IS
    v_tax_amt NUMBER;
BEGIN
    SELECT sum(TAXTRAN.TAX_AMT)
    INTO v_tax_amt
    FROM taxtran
    INNER JOIN tax
        ON TAXTRAN.TAX_CODE = TAX.TAX_CODE
    LEFT JOIN sreturndet
        ON taxtran.TRAN_ID = sreturndet.TRAN_ID
       AND taxtran.LINE_NO = FN_RCON_LINENO_NUM(sreturndet.line_no)
       AND NVL(sreturndet.ret_rep_flag, 'P') = 'R'
    WHERE trim(taxtran.TRAN_ID) = trim(p_tran_id)
      AND trim(taxtran.LINE_NO) = trim(p_line_no)
      AND TAXTRAN.TRAN_CODE = 'S-RET'
      AND tax.print_tax = 'Y'
      AND taxtran.tax_amt <> 0;

    RETURN v_tax_amt;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

 / 

  CREATE UNIQUE INDEX JOB_PROFILE_EMP_X ON JOB_PROFILE_EMP (PROFILE_ID_EMP, SITE_CODE, EMP_CODE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_ADM_I1 ;
 / 

  CREATE UNIQUE INDEX SYS_C0082792 ON EMP_STATUSCHG_FIELD (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE TRIGGER DT_EINV_FAS 
AFTER UPDATE OF CONFIRMED ON asset_sales_hdr
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare ls_seq varchar2(20);
ll_errcode number;
ls_errmsg VARCHAR2(255);

ll_count number := 0;
----PRAGMA AUTONOMOUS_TRANSACTION;set echo on;

BEGIN

 --IF :old.confirmed = 'N' and :new.confirmed = 'Y' THEN
  IF ((:old.confirmed = 'N' and :new.confirmed = 'Y') or (nvl(:old.reproc_count,0) <> :new.reproc_count and :new.confirmed = 'Y') )
  THEN
    begin
        delete from e_invoice_det where tran_id = (select tran_id from e_invoice where ref_ser = 'FASALE' and ref_id = :new.tran_id);
        delete from e_invoice where ref_ser = 'FASALE' and ref_id = :new.tran_id;

        select count(1) into ll_count from e_invoice where  ref_ser = 'FASALE' and ref_id = :new.tran_id;
    exception when others then
        ll_count := 0;
    end;
    if ll_count = 0 then    
        begin
            select lpad(to_char(EINV_SEQ.nextval),20,'0') into ls_seq from dual;
        exception when others then
            ll_errcode := to_char(SQLCODE);
            ls_errmsg := SQLERRM;
            raise_application_error( -20601, 'Not able to generate next sequence (EINV_SEQ) for e-invoice err_code [' || ll_errcode || '] - [' ||  ls_errmsg || ']' );
        end;
        begin

            insert into e_invoice (
            TRAN_ID,
            TRAN_DATE,
            tran_type,
            einv_opt,
            REF_SER,
            REF_ID,
            REF_DATE,
            gstn_sell,
            trade_name_sell,
            legal_name_sell,
            add1_sell,
            add2_sell,
            city_sell,
            pin_sell,
            state_sell,
            tele_sell,
            email_id_sell,
            SITE_CODE__SHIP,
            gstn_ship,
            trade_name_ship,
            legal_name_ship,
            add1_ship,
            add2_ship,
            city_ship,
            pin_ship,
            state_ship,
            tele_ship,
            email_id_ship,
            CUST_CODE,
            gstn_buy,
            trade_name_buy,
            legal_name_buy,
            pos_buy,
            add1_buy,
            add2_buy,
            city_buy,
            state_code__buy,
            drug_lic_no_buy,
            pin_buy,
            state_buy,
            tele_buy,
            email_id_buy,
            CUST_CODE__BIL,
            CUST_CODE__DLV,
            name_dlv,
            add1_dlv,
            add2_dlv,
            pin_dlv,
            state_dlv,
            state_code__dlv,
            drug_lic_no_dlv,
            INV_AMT,
            NET_AMT,
            ASSESABLE_VALUE,
            DISC_AMT,
            ORDER_NO,
            IGST_AMT,
            CGST_AMT,
            SGST_AMT,
            CESS_AMT,
            OTHER_AMT,
            proc_stat,
            proc_date,
            resp_stat,
            distance,
            Trans_Mode,
            TRAN_gstn,
            lr_date,
            lr_no,
            lorry_no,
            Vehicle_Type,
            DOC_NO,
            DOC_DATE,
            SUPPLY_TYPE,
            gst_port,
            refund_claim,
            gst_currency,
            count_code,
            export_duty,
            tran_name
            )
            SELECT ls_seq as tran_id,
            :new.TRAN_DATE as tran_date,
            :new.TRAN_TYPE as tran_type,
            buyer.einv_reqd as einv_opt,
            'FASALE'  as ref_ser,
            :new.tran_id ref_id ,
            :new.tran_date as ref_date,
            (SELECT REG_NO FROM SITEREGNO WHERE SITEREGNO.SITE_CODE= seller.site_code AND ref_code='GSTIN_NO' ) as gstn_sell,
            seller.descr trade_name_sell,
            seller.descr legal_name_sell,
            seller.ADD1  add1_sell,
            seller.ADD2 add2_sell,
            seller.CITY city_sell,
            seller.PIN pin_sell,
            (SELECT GST_CODE FROM STATE WHERE STATE.STATE_CODE = seller.STATE_CODE) state_sell,
            seller.TELE1 tele_sell,
            seller.EMAIL_ADDR email_id_sell,
            (select ar.site_code  from asset_sales_det d,asset_register ar where ar.asset_code = d.asset_code and  d.tran_id = :new.tran_id and rownum = 1) as SITE_CODE__SHIP,
            (SELECT REG_NO FROM SITEREGNO WHERE SITEREGNO.SITE_CODE= ship.site_code AND ref_code='GSTIN_NO' ) gstn_ship,
            ship.descr trade_name_ship,
            ship.descr legal_name_ship,
            ship.ADD1 add1_ship,
            ship.ADD2 add2_ship,
            ship.CITY city_ship,
            ship.PIN pin_ship,
            (SELECT GST_CODE FROM STATE WHERE STATE.STATE_CODE = ship.STATE_CODE) state_ship,
            ship.TELE1 tele_ship,
            ship.EMAIL_ADDR email_id_ship,
            :new.cust_code cust_code,
            case when buyer.TAX_REG_2='UNREGISTER' then 'URP' else buyer.TAX_REG_2 end gstn_buy,
            buyer.CUST_NAME  trade_name_buy,
            buyer.CUST_NAME  legal_name_buy,
            case when fn_supplytype('FASALE', trim(:new.tran_id), trim(buyer_state.count_code), trim(seller_state.count_code), :new.site_code) = 'B2B' then (SELECT GST_CODE FROM STATE WHERE STATE_CODE = ship.STATE_CODE) else '96' end as pos_buy,
            buyer.ADDR1  add1_buy,
            buyer.ADDR2  add2_buy,
            buyer.CITY  city_buy,
            buyer.state_code  state_code__buy,
            buyer.drug_lic_no  drug_lic_no_buy,
            buyer.PIN  pin_buy,
            (SELECT GST_CODE FROM STATE WHERE STATE.STATE_CODE = buyer.STATE_CODE) state_buy,
            buyer.TELE1  tele_buy,
            buyer.EMAIL_ADDR  email_id_buy,
            :new.CUST_CODE CUST_CODE__BIL,
            :new.CUST_CODE CUST_CODE__DLV,
            dlv.CUST_NAME  name_dlv,
            dlv.ADDR1  add1_dlv,
            dlv.ADDR2  add2_dlv,
            dlv.PIN  pin_dlv,
            (SELECT GST_CODE FROM STATE WHERE STATE.STATE_CODE = dlv.STATE_CODE)  state_dlv,
            dlv.state_code  state_code__dlv,
            dlv.drug_lic_no  drug_lic_no_dlv,
            round(((select sum(sale_amt) from asset_sales_det where tran_id = :new.tran_id) + (select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in ('FRS01','SDISP','SDISF','SOTH','SFRGT','SINSR') )) * :new.exch_rate,2) as INV_AMT,
            round(((select sum(sale_amt) from asset_sales_det where tran_id = :new.tran_id) + nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in (select tax_code from tax where tax_type IN ('I','H','G','J','K')) and tax_perc <> 0 and tax_amt <> 0),0)+nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in (select tax_code from tax where tax_type IN ('D')) and tax_perc < 0 and tax_amt < 0),0) + (select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in ('FRS01','SDISP','SDISF','SOTH','SFRGT','SINSR') )) * :new.exch_rate,2) AS NET_AMT,
             round((select sum(sale_amt) from asset_sales_det where tran_id = :new.tran_id) ,2) AS ASSESABLE_VALUE,
            --round(:new.DISC_AMT,2),
            0,
            null ORDER_NO,
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
                and tax_code in (select tax_code from tax where tax_type ='I') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as IGST_AMT,
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
                and tax_code in (select tax_code from tax where tax_type ='H') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as CGST_AMT,
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and  taxtran.tran_id=:new.tran_id
                and tax_code in (select tax_code from tax where tax_type ='G') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as SGST_AMT,
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in (select tax_code from tax where tax_type ='J') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as CESS_AMT,
----additional round bracket added before the *.:new.exch_rate
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
            and tax_code in (select tax_code from tax where tax_type ='K') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as OTHER_AMT,
----additional round bracket added before the *.:new.exch_rate
            --0 as OTHER_AMT,
            'N' proc_stat,
            sysdate proc_date,
            'P' resp_stat,
            --0 distance,
			(select nvl(distance,0) from site_customer where site_code = seller.site_code and cust_code = :new.cust_code) as distance,
             '1' as Trans_Mode, -- Road-1, Rail-2, Air-3, Ship-4
            ' '  Tran_gstn,
            --:new.lr_date,
            --:new.lr_no,
            null,
            null,
            --:new.lorry_no,
            null,
            'R' Vehicle_Type,
            null DOC_NO,
            null DOC_DATE,
            fn_supplytype('FASALE', trim(:new.tran_id), trim(buyer_state.count_code), trim(seller_state.count_code), :new.site_code) as SUPPLY_TYPE,
            --nvl(station.gst_port,' ') gst_port,
            ' ' as gst_port,
            fn_refundclaim('FASALE', trim(:new.tran_id))  refund_claim,
            'INR' gst_currency,
            country.iso3166 as count_code,
            nvl(((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=:new.tran_id
                and tax_code in (select tax_code from tax where tax_type ='L') and tax_perc <> 0 and tax_amt <> 0)) * :new.exch_rate,0) as  export_duty,
----additional round bracket added before the *.:new.exch_rate
            ' ' Tran_name
            FROM --INVOICE INV,
            --SORDER SO,
            CUSTOMER BUYER,
            CUSTOMER DLV,
            site seller,
            site ship,
            state seller_state,
            state buyer_state,
            currency,
            country
            --,
            --station,
            --despatch
            WHERE --SO.sale_order = :new.sale_order -- inv.sale_order
            --and 
			buyer.cust_code = :new.CUST_CODE --cust_code --inv.CUST_CODE
            and DLV.cust_code = :new.CUST_CODE
            and seller.site_code = (select ar.site_code  from asset_sales_det d,asset_register ar where ar.asset_code = d.asset_code and  d.tran_id = :new.tran_id and rownum = 1)
            and ship.site_code = (select ar.site_code  from asset_sales_det d,asset_register ar where ar.asset_code = d.asset_code and  d.tran_id = :new.tran_id and rownum = 1)
            and seller_state.state_code = seller.state_code
            and buyer_state.state_code = buyer.state_code
            and currency.curr_code = buyer.curr_code
            and country.count_code = buyer_state.count_code;

        exception when others then
            ll_errcode := to_char(SQLCODE);
            ls_errmsg := SQLERRM;
            --
            raise_application_error( -20601, 'Not able to insert data into e_invoice table errCode [' || ll_errcode || '] - [' || ls_errmsg ||']');
        end;

        begin    
            insert into e_invoice_det (
            TRAN_ID,
            LINE_NO,
            ITEM_CODE,
            item_descr,
            LINE_TYPE,
            INV_AMT,
            ASS_AMT,
            DISCOUNT,
            IGST_AMT,
            CGST_AMT,
            SGST_AMT,
            CESS_AMT,
            OTHER_AMT,
            UNIT,
            QUANTITY,
            RATE,
            HSN_CODE,
            LOT_NO,
            EXP_DATE,
            MFG_DATE,
            TOT_ITEM_VAL,
            IGST_PERC,
            CGST_PERC,
            SGST_PERC,
            CESS_PERC,
            GST_RATE,
            service_yn
            )
            SELECT  
            ls_seq as tran_id,
            it.line_no   AS LINE_NO,
            a.ITEM_CODE as ITEM_CODE ,
            item.DESCR as ITEM_DESC,
            ' '   AS LINE_TYPE,
            round(it.sale_amt,2) as  INV_AMT,
            it.SALE_AMT as ASS_AMT, 
            0  AS DISCOUNT,
            round((nvl((select tax_amt from taxtran where  taxtran.tran_code = 'FASALE' and taxtran.tran_id=it.tran_id
             and trim(line_no)=trim(it.line_no) and tax_code in (select tax_code from tax where tax_type ='I') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as IGST_AMT, 
            round((nvl((select tax_amt from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=it.tran_id
             and trim(line_no)=trim(it.line_no) and tax_code in (select tax_code from tax where tax_type ='H') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as CGST_AMT, 
            round((nvl((select tax_amt from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=it.tran_id
             and trim(line_no)=trim(it.line_no) and tax_code in (select tax_code from tax where tax_type ='G') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as SGST_AMT, 
            round((nvl((select tax_amt from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=it.tran_id
             and trim(line_no)=it.line_no and tax_code in (select tax_code from tax where tax_type ='J') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as CESS_AMT, 
            round((nvl((select sum(tax_amt) from taxtran where taxtran.tran_code = 'FASALE' and taxtran.tran_id=it.tran_id
             and trim(line_no)=it.line_no and tax_code in (select tax_code from tax where tax_type in('C') ) and tax_code not in ('FRS01','SDISP','SDISF','SOTH','SFRGT','SINSR') and tax_perc <> 0 and tax_amt <> 0),0)) * :new.exch_rate,2) as OTHER_AMT, 
            (SELECT CASE WHEN udf_str2 IS NULL THEN 'OTH' ELSE udf_str2  END FROM uom WHERE uom.unit=item.unit ) AS UNIT,
            1 AS QUANTITY,
            it.sale_amt AS  RATE,
            item.hsn_no AS HSN_CODE,
            'NOT-APPLICABLE' as LOT_NO,
            null as EXP_DATE,
            null as MFG_DATE,
            round(it.tot_amt,2)  as TOT_ITEM_VAL,
            fn_gst_rate_amt('FASALE',it.tran_id, it.line_no, 'I', 'R') as IGST_PERC,
            fn_gst_rate_amt('FASALE',it.tran_id, it.line_no, 'H', 'R') as CGST_PERC,
            fn_gst_rate_amt('FASALE',it.tran_id, it.line_no, 'G', 'R') as SGST_PERC,
            fn_gst_rate_amt('FASALE',it.tran_id, it.line_no, 'J', 'R') as CESS_PERC,
            fn_get_gst_rate('FASALE',it.tran_id, it.line_no) as GST_RATE,
            --(select case when item.stk_opt = '0' then 'Y' else 'N' end from item where item.item_code = it.item_code)
            --(select case when nvl(itemser.ser_type,'0') = '4' then 'Y' else 'N' end from item,itemser where item.item_code = it.item_code and item.item_ser = itemser.item_ser)
			'N'
            FROM asset_sales_det it,  asset_register a, item
            WHERE  a.asset_code= it.asset_code
			and item.item_code = a.item_code
            AND it.tran_id = :new.tran_id;
        exception when others then
            ll_errcode := to_char(SQLCODE);
            ls_errmsg := SQLERRM;
            --
            raise_application_error( -20601, 'Not able to insert data into e_invoice_det table errCode [' || ll_errcode || '] - [' || ls_errmsg ||']');
        end;
        --
    end if;

  END IF;    

END;


 / 

  CREATE UNIQUE INDEX SYS_C0083121 ON TRAN_TYPE_MASTER (TRAN_TYPE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE TRIGGER CUST_STOCK_AFTER_INS_UPD 
AFTER UPDATE of confirmed ON CUST_STOCK FOR EACH ROW

DECLARE
CONFVAL CHAR(1);
REFCOUNT NUMBER(10,2) DEFAULT 0;
BEGIN
   SELECT COUNT(REF_ID) INTO REFCOUNT FROM  DWH_TRAN_LOAD WHERE REF_ID=:NEW.TRAN_ID AND REF_SER='S-CSTK' ;--AND REF_DATE=:NEW.TRAN_DATE AND UPDATE_STAT='P' ;
   CONFVAL:=:NEW.CONFIRMED;

   IF(CONFVAL = 'Y') THEN
       -- Insert record into DWH_TRAN_LOAD table
       INSERT INTO DWH_TRAN_LOAD
       ( REF_ID,
         REF_SER,
         REF_DATE,
         UPDATE_STAT
       )
       VALUES
       (
         :NEW.TRAN_ID,
         'S-CSTK',
         :NEW.TRAN_DATE,
         'P'
       );
   END IF;
END;


 / 

  CREATE UNIQUE INDEX SYS_C001102913 ON APPR_KRA_CAL (APPR_NO, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0082956 ON SQL_EXEC_LOG (TRAN_ID, ENTERPRISE, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

   CREATE SEQUENCE  RPTLOG  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
 / 

  CREATE UNIQUE INDEX USERTYPE_LIC_CHRGS_PK ON USERTYPE_LIC_CHRGS (USER_LIC_TYPE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX TIMESLOT_INVTRACE_PK ON TIMESLOT_INVTRACE (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE OR REPLACE FUNCTION FN_CHK_TAX_ENV (
    IN_string     IN VARCHAR2,
    IN_date       IN DATE,       -- Invoice date
    IN_tran_code  IN VARCHAR2,
    IN_tran_date  IN DATE        -- Transaction date
)
RETURN NUMBER IS
    v_result      NUMBER := 0;
    v_setting     VARCHAR2(1);
    v_cutoff_date DATE;
    v_fin_year    VARCHAR2(7);
BEGIN
    IF TRIM(IN_string) = 'SR' THEN
        -- Check config
        SELECT NVL(VAR_VALUE, 'N')
        INTO v_setting
        FROM disparm
        WHERE VAR_NAME = 'SRET_INVREF_MANDATORY'
          AND PRD_CODE = '999999';

        IF v_setting = 'Y' THEN
            -- Calculate financial year based on invoice date
            IF EXTRACT(MONTH FROM IN_date) <=    3 THEN
                -- Jan?Mar ? belongs to previous FY (e.g., Feb-2024 ? 2023-24)
                v_fin_year := TO_CHAR(IN_date, 'YYYY') - 1 || '-' || TO_CHAR(IN_date, 'YY');
            ELSE
                -- Apr?Dec ? same FY (e.g., Dec-2024 ? 2024-25)
                v_fin_year := TO_CHAR(IN_date, 'YYYY') || '-' || ( TO_CHAR(IN_date, 'YY') + 1) ;
            END IF;

            -- Build cutoff date: 31-OCT of second year
            v_cutoff_date := TO_DATE('31-OCT-' || SUBSTR(v_fin_year, 6, 2), 'DD-MON-RR');

            IF IN_tran_date > v_cutoff_date
               AND TRIM(IN_tran_code) <> 'GSTEXP' THEN
                v_result := 1;
            END IF;
        END IF;
    END IF;

    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;

 / 

  CREATE UNIQUE INDEX SYS_C0083723 ON RESOURCE_CONS (TRAN_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_FIN_I1 ;
 / 

  CREATE OR REPLACE FUNCTION FN_ASSETPRINTINGCOST (as_item_code char)
return number is
prnt_area number(5) :=0;
prnt_cost number(5) :=0;
prnt_rate number(5) :=0;
begin
	select (nvl(phy_attrib_8,0) * nvl(phy_attrib_9,0)) into prnt_area from item where item_code =as_item_code;

    select (nvl(phy_attrib_8,0) * nvl(phy_attrib_9,0)) into prnt_area from item where item_code =as_item_code;
    select nvl(b.rate,0) into prnt_rate from taxset a, taxrate b where a.tax_set = b.tax_set and a.tax_code='PRNT1';

    prnt_cost:= prnt_area*prnt_rate;

return prnt_cost;
end;

 / 

  CREATE UNIQUE INDEX HTL_REQ_ROOM_AMD ON HTL_REQ_ROOM_AMD (REQ_AMD_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_ADM_I1 ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_SUPPLIER_NAME (
    p_lot_no    IN qc_Order.lot_no%TYPE,
    p_item_code IN qc_Order.item_code%TYPE
) RETURN VARCHAR2
IS
    v_supp_name supplier.supp_name%TYPE;
    v_supp_code varchar(10);
BEGIN
    select v_supp_code into v_supp_code from qc_order where item_code=p_lot_no and lot_no=p_item_code;
    select v_supp_name into v_supp_name from supplier where supp_code=v_supp_code ;

    RETURN v_supp_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RETURN NULL;
END fn_get_supplier_name;
 / 

  CREATE OR REPLACE TRIGGER DT_CHK_SCCLOSE 
BEFORE insert or UPDATE of confirmed ON scontract FOR EACH ROW
declare
ls_invacct finparm.var_value%type; 
ls_refser char(6);


BEGIN

   if :new.confirmed = 'Y' and :old.confirmed <> 'Y' and (:new.status = 'C' or :new.status = 'X') then
       raise_application_error( -20601, 'Contrac status is C (Closed) cannot be confirmed');
   end if;
END ;


 / 

  CREATE OR REPLACE TRIGGER DT_IND_ORDQTY BEFORE UPDATE OF ord_qty ON indent referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
  ll_docount number(6) := 0;
  ls_msg varchar2(2000);
  jobWorkType char(3);
  subcontractType char(3);
  pordTypeImpService char(3);

begin
    if nvl(:new.ord_qty,0) < 0 then
         raise_application_error( -20601, 'Order quantity cannot be updated with negatrive value');
    end if;
end;


 / 

  CREATE UNIQUE INDEX UK_QCITMSPCD ON QCITEM_SPEC_DET (ITEM_CODE, SPEC_REF, SPEC_CODE) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE USERS ;
 / 

  CREATE UNIQUE INDEX SYS_C0083280 ON COSTALLOC_BAS_DET (TRAN_ID, LINE_NO) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE ITM_FIN_I1 ;
 / 

  CREATE OR REPLACE FUNCTION FN_GET_RATE_TYPE (
    p_tran_id IN VARCHAR2,
    p_line_no IN NUMBER
) RETURN VARCHAR2 IS
    v_rate_type VARCHAR2(50);
BEGIN
    SELECT distinct taxtran.rate_type
    INTO v_rate_type
    FROM taxtran
    INNER JOIN tax
        ON TAXTRAN.TAX_CODE = TAX.TAX_CODE
    LEFT JOIN sreturndet
        ON taxtran.TRAN_ID = sreturndet.TRAN_ID
       AND taxtran.LINE_NO = FN_RCON_LINENO_NUM(sreturndet.line_no)
       AND NVL(sreturndet.ret_rep_flag, 'P') = 'R'
    WHERE trim(taxtran.TRAN_ID) = trim(p_tran_id)
      AND trim(taxtran.LINE_NO) = trim(p_line_no)
      AND TAXTRAN.TRAN_CODE = 'S-RET'
      AND tax.print_tax = 'Y'
      AND taxtran.tax_amt <> 0;

    RETURN v_rate_type;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

 / 

-- No of Rows = 95
