Expense Report: Master Update

Stored Procedure

create or replace PROCEDURE update_expense_report( p_report_id IN VARCHAR2 ) 
IS 
v_cc_num NUMBER(3);
v_cc_amount NUMBER(9);
v_pc_num NUMBER(3);
v_pc_amount NUMBER(9);
BEGIN 

select count(e.expense_id), nvl(sum(e.PAYMENT_AMOUNT),0)
into v_cc_num, v_cc_amount
from expenses e, expense_report_items i 
where e.EXPENSE_ID = i.expense_id 
and i.report_id = p_report_id
and card_type = 'cc';

select count(e.expense_id), nvl(sum(e.PAYMENT_AMOUNT),0)
into v_pc_num, v_pc_amount
from expenses e, expense_report_items i 
where e.EXPENSE_ID = i.expense_id 
and i.report_id = p_report_id
and card_type = 'pc';


UPDATE EXPENSE_REPORTS
SET 
COMPANY_CARD_RECEIPT_NUM = v_cc_num,
PRIVATE_CARD_RECEIEPT_NUM = v_pc_num,
COMPANY_CARD_AMOUNT = v_cc_amount,
PRIVATE_CARD_AMOUNT = v_pc_amount
WHERE EXPENSE_REPORT_ID = p_report_id;

END; 

Page 41 - Process 추가

• name: update report
  • when: Delete

begin
update_expense_report( p_report_id => :P41_EXPENSE_REPORT_ID);
end;

Page 43 - Process 추가

• name: update report

begin
update_expense_report( p_report_id => :P43_REPORT_ID);
end;