Query for customers

[Former Member]

Hi Gurus,

Can you please inform which tables should I refer to create a query to fetch all of this-

Sold to party - Name of Sold to party-Ship to Party- Name of ship to party-country code- street-streetno.- city-class-account group-sales org-Dist. channel-division-partner function-last time shipment sent to customer (month/year)

I have tried KNA1-KNVP but unable to join with some table to get the last coloumn.

Please help!

Thanks

Bawa

[Former Member]

Hi,

Use KNA1,KNVV, KNVP tables for customer related information.

VTTK shipment header information,

VTTP shipment item

VTTS stage of shipment informantion.

Regards,

Chandra

[Former Member]

Thanks for your help!

Now, to be more clear on this issue.

I am looking to pull out report on all the customers who has not ordered in 2007 & 2008 so far so as to identify the customers who are not active in past couple of months.

I need the report which can show SP and all the partner functions assigned to that SP

Thanks again!

Bawa

[Lakshmipathi]

Not in one stretch this is possible. Go to VA05 or VA05N, give the period 01-01-07 to 31-12-07 (for 2007) and execute. You will get list of customers who have ordered. You have to compare with your customer list TCode VCUST and exclude the customers from VCUST. The remaining customer list are those who have not ordered in 2007. The same way you have to do for 2008.

thanks

G. Lakshmipathi

[Former Member]

Hi Lakshmi,

I have no access to VCAST probably its not available in 4.6.

I tried your idea and downloaded the cust from sales orders in 2007 and cust list from KNA1 but I am not sure how to compare it in Excel.

Can you help????

Thanks

Bawa

[Lakshmipathi]

Dear Bawa

Compare the customer code through "vlookup" in excel which is the easiest and shortest way. Wherever the customer code is not there in the excel sheet downloaded from KNA1, you can assume those customers have not ordered in 2007.

thanks

G. Lakshmipathi

[Former Member]

Dear Bawa,

The tables relevant for Customer Master Data are as under:

KNA1 Customer master

KNB1 Customer / company

KNVV Customer sales data

KNBK Bank details

KNVH Customer hierarchy

KNVP Customer partners

KNVS Shipment data for customer

KNVK Contact persons

KNVI Customer master tax indicator

Best Regards,

Amit

[Former Member]

Hi Gurus,

Thanks for your help!

Now, to be more clear on this issue.

I am looking to pull out report on all the customers who has not ordered in 2007 & 2008 so far so as to identify the customers who are not active in past couple of months.

I need the report which can show SP and all the partner functions assigned to that SP

Thanks again!

Bawa

Edited by: Bawa Bawa on Nov 21, 2008 10:53 AM

[Former Member]

Hi there,

If you are talking with respect to a sales order, tehn we 1st have to get the VBAK-VKORG for sales org, In VBPA you will get sold-to, ship-to & other partners. VBPA-PARVW will give the partner fucntion of the sales order & VBPA-KUNNR to get the customer num.

To get the street name 7 other address, then go to table KNA1, give the KUNNR (customer num) num & then pick up the ADRNR. Then to go ADRC table, give the ADRNR to get all the streeet, city, postcode details.

For sales org, go to TVKO --> give the VKORG num --> pick the ADRNR --> go to ADRC --> Enter the ADRNR -> pick the street, city, post code etc.

Regards,

Sivanand

[Former Member]

Hi,

Try this :-

KNVP, KNA1, KNVV

Join as KNVP to KNVV

& KNVP to KNA1It will give you all field except last time shipment sent to customer (month/year

regards

San

[Former Member]

Is there a way out to fetch "last shipment sent to customer (Month/Year)"

Thanks

Bawa

[Former Member]

Can you tell me the table for last shipment then it may possible

Regards

San