Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / extract unit cost and ...
Small and medium business | Business Central, N...
Answered

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

(0) ShareShare
ReportReport
Posted on by jaybeegee 135

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

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,428 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 135 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,428 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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans