Skip to main content
Business Central forum
Answered

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

editSubscribe (0) ShareShare
ReportReport
Posted on by 96

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

Attachments
  • Verified answer
    MahGah Profile Picture
    MahGah 15,395 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 96 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,395 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

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,126 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,646 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans