Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / extract unit cost and ...
Business Central forum
Answered

extract unit cost and unit cost + freight on purchase receipt using config package for intrastat reporting?

(0) ShareShare
ReportReport
Posted on by jaybeegee 107

Hi!

We have a requirement to extract import/export data for Intrastat reporting and will eventual use the inbuilt functionality in BC but due to the urgency we will start with xls exports for Day1.

We have created a config file using table 121 (Purchase Receipt Line) which pretty much contains all the info and then using some XL basic transformations and vlookups we managed to get close to mapping the template.

There is a requirement to populate amongst other things the item cost exc vat and the statistical value which is item cost + freight exc vat  and we struggling to compute the latter.

When we issue the purchase receipt we confirm the receipts per item type and the freight costs are added at the bottom. Our understanding is that this cost is spread across all items received in the particular shipment - how can extract this information easily from BC?

thanks,

jay

Categories:
Financial Management
Purchasing
  • Suggested answer
    MahGah Profile Picture
    MahGah 15,411 on at
    RE: extract unit cost and unit cost + freight on purchase receipt using config package for intrastat reporting?

    First PO must be invoiced. Seems like in my environment relationship is based on Item Ledger Entry. But check yours. 

    I think (maybe) you can do this:

    Use ODATA (web service) and publish Purchase Receipt , Value Entry ,Item Ledger Entry. Then in Excel build a Power Query to join them together and design the report. 

    https://www.encorebusiness.com/blog/how-to-setup-excel-query-for-dynamics-365-business-central/

    https://archerpoint.com/how-to-use-business-central-odata-feeds-to-get-data-into-excel-for-reporting/

  • jaybeegee Profile Picture
    jaybeegee 107 on at
    RE: extract unit cost and unit cost + freight on purchase receipt using config package for intrastat reporting?

    hi and thank you for taking the time to respond..

    Yes unit cost exc vat I have and it is the cost + shipping that is troubling us.

    I will look into the Value Entries (table 5802) - thank you for that. Do you know which field is common to the value entries and purchase receipt line please?

    thanks once again.

    jay

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,411 on at
    RE: extract unit cost and unit cost + freight on purchase receipt using config package for intrastat reporting?

    Hi

    I see Unit Cost (LCY) in Purchase Receipt Line I believe this is the line without VAT. Can you check this

    For freight, if you are using item charge then you can divide it based on some criteria but to access it you need to look into Value Entries (table 5802) then filter based on Item Charge No. and then build the relationship there.

    I hope this help

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,768 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,985 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans