cancel
Showing results for 
Search instead for 
Did you mean: 

Q60: How to report sales order number against in voice number

Former Member
0 Kudos

Dear All,

I have this simple report but need to include the relevant sales order in the column aftero the invoice number.

I know it is the base doc but do not know how to build it in.............

SELECT

t0.docduedate as 'Invoice Date',

t0.docstatus as 'Open/Closed',

t0.docnum as 'Invoice Number',

t0.numatcard as 'Customer Ref. No.',

t0.u_rbdelivery as 'Tracking Number',

t0.doctotal as 'Invoice Value',

t0.cardcode as 'Customer Code',

t0.cardname as 'Customer Name',

t0.address2 as 'Delivery Address'

FROM

oinv t0

WHERE

t0.docstatus = '[%0]' and

t0.doctype = 'I' and

left(t0.cardcode,1) = 'K'

Many thanks for any help.

Robin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Robin,

Go to document flow table ( VBFA ) to get sales order number against the invoice number .

01. VFFA-VBELN is your Billing doc number

02. VBFA-POSNN = 00000

03. VBFA-VBTYP_N = M

04. VBFA-VBTYP_V = C

If you plug in above values in VBFA table you will get sales order for that particular billing doc number.

Thanks / Rajesh

Answers (3)

Answers (3)

jignesh_mehta3
Active Contributor
0 Kudos

Hello Robin,

Sales Order Number is available in Invoice itself at Line item level.

The table - Field is VBRP - AUBEL.

Hope this helps,

Thanks,

JIgnesh Mehta

Former Member
0 Kudos

Hi,

You have to use following tables to get the data VBRK (Billing Header), VBRP (Billing Item), KNA1, KNB1 and KNVV (Customer Master Tables), VBAK (Sale Order Header) and FPLTC(Payment cards table)

First pass the input data for example

Customer reference number into VBRK- KUNNR and Date into VBRK-FKDAT and get the relevant VBLENu2018s for the customer.

Pass VBRK-VBELN value into VBRP-VBLEN and get VBRP-AUBEL (Sale order number).

Pass customer number into KNA1-KUNNR and get KNA1-ADRNR (for fetching the address details for the customer) pass this

ADRNR into ADRC-ADDRNUMBER And get all the address details of the customer.

Similarly pass VBRP-AUBEL into VBAK-VBLEN and get VBAK-FPLNR pass this value into FPLTC-FPLNR and get all the card relevant details.

Hope this helps.

Revert back in case of clarifications.

Regards,

Sharan

former_member209761
Active Contributor
0 Kudos

Dear Robin Bellion ,

Input your invoice no as SUBSEQUENT DOCUMENT in VBFA and fetch PRECEDING DOCUMENT where PRECEDING DOCUMENT CATEGORY = C.

Thanks & Regards

Hegal K Charles