Last day of month for February

First convert parameter date to number then
number to date then
date to last day

LAST_DAY( date );

select last_day(to_date(to_number(to_char(:p_sdate,'rrrr')||'0201'),'rrrr/mm/dd')) from dual;

Transaction History Log Trigger

CREATE OR REPLACE TRIGGER JVDETAIL_LOG
  before insert or update or delete ON JVDETAIL
  for each row
declare
  oldvalue     varchar2(2000) := null;
  newvalue     varchar2(2000) := null;
  changedvalue long := null;
begin
  if inserting then
    newvalue := 'DATE=' || :NEW.JDATE || '; ' || 'REF NO=' || :NEW.REFNO || '; ' ||
                'PATIENT ID=' || :NEW.PATIENT_ID || '; ' ||
                'ADMISSION DATE=' || :NEW.ADMISSION_DATE || '; ' ||
                'ACCOUNT CODE=' || :NEW.ACCOUNT_CODE || '; ' || 'DEBIT=' ||
                :NEW.DEBIT || '; ' || 'CREDIT=' || :NEW.CREDIT || '; ' ||
                'NARRATION=' || :NEW.NARRATION || '; ' || 'QUANTITY=' ||
                :NEW.QUANTITY || '; ' || 'RATE=' || :NEW.RATE || '; ' ||
                'ITEM CODE=' || :NEW.ITEM_CODE || '; ' || 'VAT RATE=' ||
                :NEW.VAT_RATE || '; ' || 'DEPT=' || :NEW.DEPT || '; ' ||
                'ADMISSION NO=' || :NEW.ADMISSION_NO || '; ' || 'STATUS=' ||
                :NEW.STATUS || '; ' || 'UNIT=' || :NEW.UNIT || '; ' ||
                'BILL NO=' || :NEW.BILL_NO || '; ' || 'BILL DATE=' ||
                :NEW.BILL_DATE || '; ' || 'TR MODE=' || :NEW.TR_MODE || '; ' ||
                'TR NAME=' || :NEW.TR_NAME || '; ' || 'TR NO=' ||
                :NEW.NARRATION || '; ' || 'TR VALIDITY=' ||
                :NEW.TR_VALIDITY || '; ';
    insert into hisaudit
    values
      (USER,
       'JVDETAIL',
       'MR/VOUCHER DETAILS',
       SYSDATE,
       'I',
       :new.refno,
       newvalue);
  elsif updating then
    changedvalue := CHECK_VALUE('JDATE',
                                TO_CHAR(:OLD.JDATE),
                                TO_CHAR(:NEW.JDATE)) ||
                    CHECK_VALUE('REFNO', :OLD.REFNO, :NEW.REFNO) ||
                    CHECK_VALUE('PATIENT_ID',
                                :OLD.PATIENT_ID,
                                :NEW.PATIENT_ID) ||
                    CHECK_VALUE('ADMISSION_DATE',
                                :OLD.ADMISSION_DATE,
                                :NEW.ADMISSION_DATE) ||
                    CHECK_VALUE('ACCOUNT CODE',
                                :OLD.ACCOUNT_CODE,
                                :NEW.ACCOUNT_CODE) ||
                    CHECK_VALUE('DEBIT', :OLD.DEBIT, :NEW.DEBIT) ||
                    CHECK_VALUE('CREDIT', :OLD.CREDIT, :NEW.CREDIT) ||
                    CHECK_VALUE('NARRATION', :OLD.NARRATION, :NEW.NARRATION) ||
                    CHECK_VALUE('QUANTITY', :OLD.QUANTITY, :NEW.QUANTITY) ||
                    CHECK_VALUE('RATE', :OLD.RATE, :NEW.RATE) ||
                    CHECK_VALUE('ITEM CODE', :OLD.ITEM_CODE, :NEW.ITEM_CODE) ||
                    CHECK_VALUE('VAT_RATE', :OLD.VAT_RATE, :NEW.VAT_RATE) ||
                    CHECK_VALUE('DEPT', :OLD.DEPT, :NEW.DEPT) ||
                    CHECK_VALUE('ADMISSION NO',
                                :OLD.ADMISSION_NO,
                                :NEW.ADMISSION_NO) ||
                    CHECK_VALUE('STATUS', :OLD.STATUS, :NEW.STATUS) ||
                    CHECK_VALUE('UNIT', :OLD.UNIT, :NEW.UNIT) ||
                    CHECK_VALUE('BILL_NO', :OLD.BILL_NO, :NEW.BILL_NO) ||
                    CHECK_VALUE('BILL_DATE', :OLD.BILL_DATE, :NEW.BILL_DATE) ||
                    CHECK_VALUE('TR_MODE', :OLD.TR_MODE, :NEW.TR_MODE) ||
                    CHECK_VALUE('TR_NAME', :OLD.TR_NAME, :NEW.TR_NAME) ||
                    CHECK_VALUE('TR_NO', :OLD.TR_NO, :NEW.TR_NO) ||
                    CHECK_VALUE('TR_VALIDITY',
                                :OLD.TR_VALIDITY,
                                :NEW.TR_VALIDITY);
    insert into hisaudit
    values
      (USER,
       'MR',
       'MONEY RECEIPT HEADING INFORMATION',
       SYSDATE,
       'U',
       :old.refno,
       changedvalue);
  else
    oldvalue := 'DATE=' || :OLD.JDATE || '; ' || 'REF NO=' || :OLD.REFNO || '; ' ||
                'PATIENT ID=' || :OLD.PATIENT_ID || '; ' ||
                'ADMISSION DATE=' || :OLD.ADMISSION_DATE || '; ' ||
                'ACCOUNT CODE=' || :OLD.ACCOUNT_CODE || '; ' || 'DEBIT=' ||
                :OLD.DEBIT || '; ' || 'CREDIT=' || :OLD.CREDIT || '; ' ||
                'NARRATION=' || :OLD.NARRATION || '; ' || 'QUANTITY=' ||
                :OLD.QUANTITY || '; ' || 'RATE=' || :OLD.RATE || '; ' ||
                'ITEM CODE=' || :OLD.ITEM_CODE || '; ' || 'VAT RATE=' ||
                :OLD.VAT_RATE || '; ' || 'DEPT=' || :OLD.DEPT || '; ' ||
                'ADMISSION NO=' || :OLD.ADMISSION_NO || '; ' || 'STATUS=' ||
                :OLD.STATUS || '; ' || 'UNIT=' || :OLD.UNIT || '; ' ||
                'BILL NO=' || :OLD.BILL_NO || '; ' || 'BILL DATE=' ||
                :OLD.BILL_DATE || '; ' || 'TR MODE=' || :OLD.TR_MODE || '; ' ||
                'TR NAME=' || :OLD.TR_NAME || '; ' || 'TR NO=' ||
                :OLD.NARRATION || '; ' || 'TR VALIDITY=' ||
                :OLD.TR_VALIDITY || '; ';
    insert into hisaudit
    values
      (USER,
       'MR',
       'MONEY RECEIPT HEADING INFORMATION',
       SYSDATE,
       'D',
       :old.refno,
       oldvalue);
  end if;
end;
/

Function for record log old value -> new value

CREATE OR REPLACE function check_value(v_col in varchar2,
                                       v_old in varchar2,
                                       v_new in varchar2) return varchar2 is
  Result varchar2(2000);
begin
  if v_old is null and v_new is null then
    result := '';
  elsif v_old is null and v_new is not null then
    result := v_col || '=' || v_new || '; ';
  elsif v_old is not null and v_new is null then
    result := v_col || '=' || v_old || '->DELETED; ';
  else
    if v_old = v_new then
      result := '';
    else
      result := v_col || '=' || v_old || '->' || v_new || '; ';
    end if;
  end if;
  return(Result);
end;
/

Day end unit stock view

CREATE OR REPLACE FORCE VIEW ST_DAYEND_UNIT_STOCK_V
AS
   SELECT DISTINCT
          'P' store_type,
          p.sv_dt,
          c.item_code,
          p.st_unit_no,
          SUM (
             CASE
                WHEN p.sv_type IN
                        ('GRR', 'IDR', 'IPR', 'OPN', 'PIA', 'DRR', 'DSR')
                THEN
                   NVL (c.retail_qnty, 0)
                WHEN p.sv_type IN ('DSI', 'EXP', 'IDI', 'IPI', 'GRN')
                THEN
                   - (NVL (c.retail_qnty, 0))
                ELSE
                   0
             END)
          OVER (PARTITION BY c.item_code, st_unit_no
                ORDER BY TRUNC (p.sv_dt))
             closing_qnty,
          SUM (
             CASE
                WHEN p.sv_type IN
                        ('GRR', 'IDR', 'IPR', 'OPN', 'PIA', 'DRR', 'DSR')
                THEN
                     (NVL (c.retail_qnty, 0) * NVL (c.prate, 0))
                   + NVL (c.adj_amnt, 0)
                WHEN p.sv_type IN ('DSI', 'EXP', 'IDI', 'IPI', 'GRN')
                THEN
                   - (NVL (c.retail_qnty, 0)) * NVL (c.prate, 0)
                ELSE
                   0
             END)
          OVER (PARTITION BY c.item_code, st_unit_no
                ORDER BY TRUNC (p.sv_dt))
             closing_amnt
     FROM ph_st_voucher p, ph_st_item c
    WHERE p.sv_no = c.sv_no

Finding Duplicate Records in Table

SELECT MIN(rowid),sv_no,item_code
FROM ph_st_item
GROUP BY sv_no, item_code
having count(item_code) >1

Deleting Duplicate Rows from Table

DELETE FROM ph_st_item a
WHERE rowid not in
(SELECT MIN(rowid)
FROM ph_st_item b
where  B.SV_NO = A.SV_NO and B.ITEM_CODE = A.ITEM_CODE
GROUP BY sv_no, item_code)

and substr(sv_no,1,2) = 'PP';