cancel
Showing results for 
Search instead for 
Did you mean: 

query to find particular customers transactions

Former Member
0 Kudos

Hi expert,

I need query to find particular customers transactions and delivery balances, AR invoice etc

Accepted Solutions (0)

Answers (1)

Answers (1)

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

can you add more info about your query?

Kind regards

Agustín Marcos Cividanes

Former Member
0 Kudos

Hi,

I need customers account balances, delivery's, Sales orders,AR invoices, AP invoice purchase for that customers.

former_member212181
Active Contributor
0 Kudos

Hi Vigneshwaran,

Which version of SAP B1 you are using?

Its available in standard Business partner window in 9.1.

Account balance, Open Deliveries, Open sales Orders, opportunities sum are available in BP Window.

Thanks

Unnikrishnan

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

try this query:

select CardCode, CardName, Balance AS Balance, DNotesBal AS Deliverys, OrdersBal AS Orders FROM OCRD

One question: which criteria do you want to obtain the AP invoices.

Kind regards

Agustín Marcos Cividanes

Former Member
0 Kudos

Hi sir,

Ya i know but they asking what are ar invoice raised and cloased, wat are expenses for that customer, and full transaction which are available for that customer they need to know, so that is any query possible to show. Please help to do.

Regards,

J.vigneshwaran

Former Member
0 Kudos

Hi,

Try this first:

SELECT RefDate, Ref1, Ref2, LineMemo, CreatedBy, Credit, DeBit FROM jdt1 T0

where T0.shortname like '[%0]%' AND T0.RefDate Between [%1] and [%2]

Thanks,

Gordon

former_member212181
Active Contributor
0 Kudos

Hi Vigneshwaran,

Its difficult to achieve (But possible) in a single query. Because all required informations are scattered in various tables.

For Eg: Sales Order, Deliveries, Return, AR Invoices, AR Credit Note Incoming payments, Manual JE, Manual Reconciliation.

But we can take most of the data from Journal entry table but this wont cover, Sales Order, Delivery and delivery return.

By the way, How you are booking expenses for customers??

Thanks

Unnikrishnan