on 11-02-2011 10:54 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.