aonestar.public

Parameters

Name	Type	Mode
startdate	date	IN
enddate	date	IN
book_type	text	IN (DEFAULT NULL)
bookno	text	IN (DEFAULT NULL)
docid	bigint	IN (DEFAULT NULL)
docno	text	IN (DEFAULT NULL)
customer	text	IN (DEFAULT NULL)
taxid	text	IN (DEFAULT NULL)
reference	text	IN (DEFAULT NULL)
room_no	text	IN (DEFAULT NULL)
openby	text	IN (DEFAULT NULL)
cancelby	text	IN (DEFAULT NULL)
interface_from	text	IN (DEFAULT NULL)
doc_status	text	IN (DEFAULT NULL)
invoice_date	date	OUT
invoice_no	text	OUT
invoice_book_type	text	OUT
book_no	text	OUT
cust_name	text	OUT
cust_tax_id	text	OUT
cust_branch_type	text	OUT
cust_branch_no	integer	OUT
localvat	t_money	OUT
zerovat	t_money	OUT
nonvat	t_money	OUT
vatable	t_money	OUT
valueaddtax	t_money	OUT
ar_amt	t_money	OUT
total	t_money	OUT
clxinv	boolean	OUT

Definition

DECLARE 
    v_result jsonb;
    v_params jsonb;
BEGIN
    book_type := coalesce(book_type, 'ALL');
  
    v_params := json_object(
        'user_name'     : fn_current_user(),
        'platform'      : '',
        'bookno'        : bookno,
        'docid'         : docid,
        'docno'         : docno,
        'startdate'     : startdate,
        'enddate'       : enddate,
        'customer'      : customer,
        'taxid'         : taxid,
        'reference'     : reference,
        'room_no'       : room_no,
        'openby'        : openby,
        'cancelby'      : cancelby,
        'interface_from': interface_from,
        'doc_status'    : doc_status
    )::jsonb;

    v_result := sp_call_accsys_api('/taxcontrol/get_outputtax_document_report', v_params)::jsonb->>'result_data';  
    
    IF (v_result is json array) THEN 
        RETURN QUERY
        Select * 
        from jsonb_to_recordset(v_result)
        as T(invoice_date date,
            invoice_no text,
            invoice_book_type text,
            book_no text,
            cust_name text,
            cust_tax_id text,
            cust_branch_type text,
            cust_branch_no int,
            localvat t_money,
            zerovat t_money,
            nonvat t_money,
            vatable t_money,
            valueaddtax t_money,
            ar_amt t_money,
            total t_money,
            clxinv boolean)
        where book_type is null or book_type = 'ALL' or t.invoice_book_type = book_type;
    END IF;
END

sp_saletax_report

Parameters

Definition