aonestar.public

Parameters

Name	Type	Mode
clearing_status	text	IN
folio_id	integer	IN
charge_amount	t_money	IN
refund_payment_id	integer	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

declare
    _sqlstate text;
    _detail   text;
    _hint     text;
    _context  text;
    _msg_text  text;

    v_refund t_money;
    v_user_name text;
    v_deposit record;
    v_result json;
    v_booking_id int;
    v_deposit_id integer;
    v_charge_dept_id int;
    v_remark text;
    v_folio_item record;
    v_reference text;
    v_deposit_ids int[];
    is_post_success bool;
begin
    clearing_status := upper(left(clearing_status, 1));
    v_user_name := coalesce(user_name, fn_current_user());
    
    v_remark     := IIF(clearing_status='N', 'No-Show', 'Cancel Booking');
    v_booking_id := (Select b.id from booking b where b.folio_id = $2);

    perform sys_save_log('D', 'sp_post_deposit_clearing', 
                         format('%L, Folio#: %L, Charge: %L, Refund Media: %L', $1, $2, $3, $4), 
                         force_write := true);  
        
    FOR v_folio_item in (
        select * 
        from folio_items fi 
        where fi.folio_id = $2 and fi.balance <> 0) 
    LOOP       
        IF charge_amount > 0 THEN
            v_charge_dept_id := case clearing_status
                                  when 'N' then fn_sys_param('DEPOSIT','NOSHOW_DEPT_ID', null::INT)
                                  when 'X' then fn_sys_param('DEPOSIT','CANCEL_DEPT_ID', null::INT)
                                end;

            select string_agg(d.id::text, ', '), array_agg(d.id)
            from deposit d left join transactions t on t.id = d.deposit_tran_id  
            where d.booking_id = v_booking_id and d.status = 'R' and t.folio_item = v_folio_item.item_id            
            into v_reference, v_deposit_ids;
                
            v_result := sp_post_transaction(                   
                    folio_id       := $2,
                    dept_id        := v_charge_dept_id,
                    amount         := charge_amount,
                    qty            := 1,
                    folio_seq      := v_folio_item.folio_seq,
                    shift          := null,
                    user_name      := v_user_name,
                    reference      := 'Deposit# '||v_reference,
                    remark         := v_remark,
                    description    := null,
                    charge_date    := null,
                    source_module  := null,
                    allow_redirect := null,
                    post_flag => 'D'
            );
        
             is_post_success := is_result_success(v_result);
        
             perform sp_raise_error_if(not is_post_success , v_result->>'result_msg');
           
             if is_post_success then 
--                for v_deposit_id in (
--                    select d.id 
--                    from deposit d left join transactions t on t.id = d.deposit_tran_id 
--                    where d.booking_id = v_booking_id and d.status = 'R' and t.folio_item = v_folio_item.item_id 
--                )
--                loop  
                    update deposit dep
                    set status          = clearing_status,
                        cancel_date     = fn_system_date(), 
                        cancel_tran_id  = (v_result->'result_data'->>'id')::int, 
                        cancel_user     = user_name,       
                        cancel_time     = current_timestamp,
                        clearing_amount = dep.amount  
                    where dep.id = any(v_deposit_ids);
--                end loop;
            end if;
           
        END IF;
           
        v_refund := abs(v_folio_item.balance) - coalesce(charge_amount, 0);

        perform sys_save_log('D', 'sp_post_deposit_clearing', 
                             format('Balance: %L, Charge: %L, Refund: %L', v_folio_item.balance, charge_amount, v_refund), 
                             force_write := true);  
        
        -- =========================
        -- case refund < ถ้า refund มีค่ามากกว่า 0 ให้ทำ refund >
        -- =========================
        IF v_refund > 0 THEN
            refund_payment_id := coalesce(refund_payment_id, fn_sys_param('DEPOSIT','REFUND_PAYMENT_ID', null::INT));

            for v_deposit in (
                select t.*, d.id as deposit_id
                from deposit d left join transactions t on d.deposit_tran_id = t.id  
                where t.folio_item = v_folio_item.item_id and t.active and t.tran_type = 'C' and d.status = 'R'
            )
            loop
                perform sys_save_log('D', 'sp_post_deposit_clearing', 'Trans: '||to_json(v_deposit)::text, force_write := true);

                v_result := sp_make_payment(
                    folio_id   := $2,
                    folio_seq  := v_folio_item.folio_seq,
                    payment_id := refund_payment_id,
                    amount     := v_deposit.amount,
                    reference  := 'Deposit# '||v_deposit.deposit_id::text,
                    remark     := v_remark,
                    post_flag  := 'D'
                );

                IF is_result_success(v_result) and v_deposit.vat_id is not null THEN

                    v_result := sp_issue_credit_note((v_result->'result_data'->>'id')::int, v_deposit.vat_id, v_user_name);
        
                    perform sys_save_log('D', 'sp_post_deposit_clearing', 'Credit Note Result:' || v_result::text, force_write := true);
                    
                    IF (v_result->>'result_code' <> '0' and v_result->>'msg_code' not in ('91024','91010')) THEN -- 91010: Tax invoice already cancelled/credit note issued
                        PERFORM sp_raise_error('30310', 'Issue credit note failed, %s', v_result->>'result_msg');
                    END IF;
                END IF; 

                update deposit d
                set status = clearing_status,
                    cancel_date = fn_system_date(), 
                    --cancel_tran_id = (v_result->'result_data'->>'id')::int, 
                    cancel_user = user_name,       
                    cancel_time = current_timestamp,
                    refund_amount = v_refund,
                    refund_tran_id = (v_result->'result_data'->>'id')::int  
                where d.id = v_deposit.deposit_id;
            end loop;              
        END IF;

        perform sys_save_log('D', 'sp_post_deposit_clearing', 
                             'Folio details: '|| sp_get_folio_details_js(v_folio_item.folio_id, 2)::text, 
                             force_write := true);
    END LOOP;
    
    IF v_result is null or is_result_success(v_result) THEN 
        UPDATE folio f 
        SET status = 'C', 
            close_date = fn_system_date(), 
            close_time = current_timestamp, 
            close_user = user_name      
        WHERE f.id = $2;       
    END IF; 

    RETURN coalesce(v_result, fn_result_success());   
  
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_post_deposit_clearing', 
        jsonb_build_object('clearing_status', $1, 'folio_id', $2, 'charge_amount', $3, 'refund_dept_id', $4)
    );
END