z repoet with vendor and customer balaces in one shot

[Former Member]

Hi,

please help me with this scenario

Now company want to create one "Z" report from which they will get Vendor &

Customer Balance in one shot as per their relationship maintained in

Master data. Also we want the Loan amount against that Customer Code.

so tell me the process to create this

Regards,

Pavani reddy

[Former Member]

Hi Pavani,

First, you need to establish a link between a vendor and a customer. The cross-reference is maintained in field LFA1-KUNNR (customer number in vendor master) and KNA1-LIFNR (vendor number in customer master). You need to check if these are symetrical (i.e., if the same customer is linked to the same vendor on both sides). In 6.0 it should be the standard (i.e., once you maintain the link in one master record it should automatically update the other in the background) but not always in earlier releases. In that case you will need to make a decision, what to do with such one-sided links.

Then you need to get the balance of the vendor and the customer per company code:

- As for customer, the main source will be KNC1 (normal transactions) and KNC3 (special GL). These are the tables behind trans. FD10N. In KNC1, he opening balance of a given year is in field UMSAV, and then there is a sum of debits UMxxS and credits UMxxH for each posting period-month (xx = number of the posting period). All have a positive sign, so you need to deduct the credit entries. In KNC3, there is just an opening balance plus sum of debits and sum of credits for the whole year, so you cannot get the special GL balance at the end of any given month (if this would be required, or if you needed a balance as of a certain posting date, then you would need to go to line items - table BSID / BSAD).

- As for vendor, it is completely symmetrical - tables LFC1 for normal payables and LFC3 for special GL, with the same structure as KNC1 and KNC3 respectively. These are the tables behind FK10N. If you need to drill into line items, then it would be BSIK / BSAK.

Need to remind that the opening balances of these tables (UMSAV) are only updated with a balance carryforward transaction at year-end (trans. F.07). I remember from the past, that if you run this transaction and then book a document in the previous year, that the opening balance sometimes does not update automatically. So be prepared that around year-end, you may need to run it repeatedly as long as both years are open.

So, in general, you need to decide (or let the business decide): what to do with assymetrical links between vendors and customers, if they exist? Is it enough to have a balance of normal items as of month-end + special GL as of today, or do they need more? Then you should get all the info in the tables above. Well, except loans from External Treasury that someone else will need to point you to.

Hope it helped. Let me know if you have any other question. Good luck!

[Former Member]

Dear Sir,

Please explain me clearly how to create link between vendor and customer to get vendor name, customer name and vendor balance customer balance in one sheet

The format is as follows

Vendor Code

Customer Code

Vendor Name

Customer Name

Vendor Balance

Customer Balance

Net Value

Loan Amount

Net Amount (After Loan Consider)

Business area/Plant

Regards,

Pavani reddy

[Former Member]

Kindly read carefully, what Jan Pesula has said. Thats the right approach. Speaking more narrowly, in terms of joins, it depends on our input parameter. What is your input parameter?

Most likely, it would be Account Number

In that case relation would be,

LFA1-lifnr  ->LFB1-lifnr

LFB1-bukrs ->BKPF-bukrs

(refer http://scn.sap.com/thread/501841)

Then, for amount, its BSEG-WRBTR.

Thankyou,

KS

[Former Member]

Dear Sir,

I understood.but how all these fields are come into one sheet. please explain me the process

Regards,

Pavani reddy

[Former Member]

Dear Pavan,

Go to SE16N and give table name "LFA1". Deselect all the fileds and then select only LIFNR, KUNNR.

LIFNR - Vendor Code / Vendor Account.

KUNNR - Customer Code

Now check KNA1 and give this customer code. ASK your functional guy, if this customer code refers to the same vendor..?

If yes and ONLY IF 'YES', code something like this:

PARAMETERS: s_lifnr LIKE LFA1-LIFNR.

DATA TABIX TYPE SY-TABIX.

TYPES: BEGIN OF IT_FINAL,

        LIFNR LIKE LFA1-LIFNR,

        NAME1 LIKE LFA1-LIFNR,

        KUNNR LIKE LFA1-KUNNR,

        NAME2 LIKE KNA1-NAME1,

        SHKZG LIKE BSIK-SHKZG,

        WRBTR LIKE BSIK-WRBTR,

        balance LIKE BSIK-WRBTR,

        credit_minus LIKE BSIK-WRBTR,

        debit_plus LIKE BSIK-WRBTR,

           END OF IT_FINAL.

       DATA: gt_final TYPE TABLE OF IT_FINAL,

             gs_final TYPE it_final.

       SELECT a1~LIFNR a1~NAME1 a1~KUNNR a2~NAME1  a3~SHKZG  a3~WRBTR

INTO TABLE gt_final

FROM LFA1 as a1 INNER JOIN KNA1 as a2 ON a2~KUNNR = a1~LIFNR INNER JOIN BSIK as a3 ON a3~LIFNR = a1~LIFNR

WHERE a1~LIFNR = s_lifnr.

SORT gt_final BY LIFNR.

LOOP AT gt_final INTO gs_final.

IF gs_final-SHKZG = 'H'.

gs_final-credit_minus = gs_final-wrbtr.

ENDIF.

IF gs_final-SHKZG = 'S'.

gs_final-debit_plus = gs_final-wrbtr.

ENDIF.

AT END OF LIFNR.

gs_final-balance = gs_final-debit_plus - gs_final-credit_minus.

MODIFY gt_final FROM gs_final INDEX tabix TRANSPORTING balance.

CLEAR gs_final.

ENDAT.

ENDLOOP.

******************

So now you have fields up-to balance. Check from your functional guy/ instructor where to pick last three fields from and keep modifying it_final..

But @ the end it all depends on your business scenario.. what if, your customer code and vendor code does not refer to same party. This whole code is when vendor code is the input parameter.

So ask your functional guy/instructor to give you table name, field name first and conditions.

Let me know further if needed.

Regards,

KS

[Former Member]

Dear Sir,

Please let me know under which table i will get the details of loan amounts of customers

Regards,

pavani reddy