[SAP B1] Customer Refund Report Query

[Requirements]

- Customer requested a query printing out outgoing payment related to customer refund with the given columns as below.

S.NO.

Run Date/ Payment Date

Account

Customer Name

Customer code

Amount

Currency

Outgoing GL

REMARKS

BANK NAME

Bank Account name

Bank code

BANK NO

 

[OS] 

-

 

[Development Language]

HANA DB SQL

 

[IDE]

HANA DB Studio/SAP Business One Query Manager

 

[Setting]

HANA DB and SAP Business One

 

[Code]

SELECT 
	ROW_NUMBER() OVER (ORDER BY T0."DocDate", T0."DocNum") AS "S.NO."
	,TO_NVARCHAR(T0."DocDate", 'YYYY-MM-DD') AS "Run Date/Payment Date"
	,T0."CardName" AS "Customer Name"
	,T0."CardCode" AS "Customer Code"
	,T0."DocTotal" AS "Amount"
	,T0."DocCurr" AS "Currency"
	,T0."TrsfrAcct" AS "Outgoing GL"
	,T0."JrnlMemo" AS "REMARKS"
	,T2."BankName" AS "BANK NAME"
	,T1."CardName" AS "Bank Account Name"
	,T1."BankCode" AS "Bank Code"
	,T1."DflAccount" AS "BANK NO"		

FROM OVPM T0
	INNER JOIN OCRD T1 ON T1."CardCode" = T0."CardCode"
	INNER JOIN ODSC T2 ON T2."BankCode" = T1."BankCode"
	INNER JOIN OJDT T3 ON T3."TransId" = T0."TransId"
	INNER JOIN JDT1 T4 ON T4."TransId" = T3."TransId"
	
WHERE T0."JrnlMemo" LIKE '%REFUND%'
	AND LEFT(T4."ContraAct", 1) NOT IN ('C', 'K')
	AND ((T0."DocDate" >= [%0] OR [%0] IS NULL OR [%0] = '')
        AND (T0."DocDate" <= [%1] OR [%1] IS NULL OR [%1] = ''))

 

 

[Test]

S.NO.	Run Date/Payment Date	Account	Customer Name	Customer Code	Amount	Currency	Outgoing GL	REMARKS	BANK NAME	Bank Account Name	Bank Code	BANK NO
1	2025-10-24	119151	Kim,Nam-han	KO547012	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	NONGHYUB	Kim,Nam-han	011	3021855633931
2	2025-10-24	119151	Noh,Tae-jin	KO547289	2,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	WOORI BANK	Noh,Tae-jin	020	1002246642644
3	2025-10-24	119151	Yoon,Go-ock	KO546895	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	NONGHYUB	Yoon,Go-ock	011	3521972096833
4	2025-10-24	119151	Jang, Sunjung	KO547913	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	Shinhan Bank	Jang, Sunjung	088	100014923388
5	2025-10-24	119151	Lee, Yungchul	C0000003	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	INDUSTRIAL BANK OF KOREA	Lee, Yungchul	003	29109095701016
6	2025-10-24	119151	Im, Nam-sub	C0000002	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	Shinhan Bank	Im, Nam-sub	088	110253346645
7	2025-10-24	119151	Seo, Jungju	C0000001	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	POST BANK	Seo, Jungju	071	51047902090781
8	2025-10-24	119151	Park,Jee-hun	KO547516	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	NONGHYUB	Park,Jee-hun	011	3521004430283
9	2025-10-24	119151	Choi, Taehwan	C0000007	2,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	Shinhan Bank	Choi, Taehwan	088	140010134750
10	2025-10-24	119151	Gu, Jaheon	C0000009	1,000,000	KRW	119151	REFUND CUSTOMER SIMPLE OVER PAY	KOOKMIN BANK	Gu, Jaheon	004	93770200735372
11	2025-10-24	119151	Kim, Ziyoung	C0000005	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	KOOKMIN BANK	Kim, Ziyoung	004	34830204127294
12	2025-10-24	119151	Hong, Chunghoe	C0000006	1,000,000	KRW	119151	REFUND REQUEST BY OVER TERMINATION	KOOKMIN BANK	Hong, Chunghoe	004	79680200008365
13	2025-10-24	119151	Korea Trasportation Safety Authorit	KO540547	4,574,900	KRW	119151	REFUND CUSTOMER	WOORI BANK	Korea Trasportation Safety Authorit	020	1005501246201

 

[Lesson Learned]

- Line number print

ROW_NUMBER() OVER (ORDER BY T0."DocDate", T0."DocNum") 

 

- Date format change

TO_NVARCHAR(T0."DocDate", 'YYYY-MM-DD')

 

- Filtering out a result of text

LEFT(T4."ContraAct", 1) NOT IN ('C', 'K')

 

- SAP Business One Query Manager input parameter handling with no input, printing all

((T0."DocDate" >= [%0] OR [%0] IS NULL OR [%0] = '')
        AND (T0."DocDate" <= [%1] OR [%1] IS NULL OR [%1] = ''))