x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 9 replies
  • Subscribers 47 subscribers
  • Views 4853 views
  • Users 0 members are here
Browse Forums
Announcements
51
topics
34
replies
General Discussion
1k
topics
2k
replies
fabio

Export item data from invoice

Posted By

Hi,

i would like to made the report "Part Sales by Customer Report" with access.

I made the invoice from order. I extract all data from the table oelineitem, but i can't have the information of the qty and the item for each invoice.

For example, :

I create 3 invoice from 1 order, i would like to display the quantity of a certain item for each invoice, but in oelineitem i only have one rows for tthe item with the total qty that is inthe order.

how can i do to have the single qty for invoice for this item?

Parents
  • 0

    I understand you want to use Access, but did you try the report that built in to BW, in OE ?

    I believe its in BW 2011, 2012 & 2013

    OE -> Reports -> Part Sales by Customer  (uses the newer report writer)

    This is a highly customizable report.   Just asking...

  • 0 in reply to Vince Settipane

    I use access for merge this data with other db.

    Another problem in this case is this:

    I create 3 items rows in sales order, then i delete these and write other 5 rows. Into the db the previous order lines aren't deleted and when a made a query in oesalesline I see 3+5 rows but in the sage report i see only the 5 recent rows

Reply
  • 0 in reply to Vince Settipane

    I use access for merge this data with other db.

    Another problem in this case is this:

    I create 3 items rows in sales order, then i delete these and write other 5 rows. Into the db the previous order lines aren't deleted and when a made a query in oesalesline I see 3+5 rows but in the sage report i see only the 5 recent rows

Children
  • 0 in reply to fabio

    this is the SQL that the report works off

    SELECT CUST.ID AS ARCustomer_ID, ITEM.ID AS OELineItem_ID, INV.InvoiceDate,

     INV.InvoiceNo, CUST.Name, CUST.SKARCustomer, INV.SKARInvoice,

     ITEM.Type, ITEM.ShipQty, ITEM.UnitPrice, ITEM.UnitDiscount,

     PART.Description1, ITEM.FKType, INV.SortLink

    FROM ARCustomer CUST

     INNER JOIN ARInvoice INV ON (INV.FKARCustomer = CUST.SKARCustomer)

     LEFT OUTER JOIN OELineItem ITEM ON (ITEM.FKEntity = INV.SortLink)

     LEFT OUTER JOIN ICPart PART ON (PART.SKICPart = ITEM.FKICPart)

    WHERE (INV.SortLink > 0)

     AND (ITEM.Type = 6)

     AND (ITEM.FKType = 'I')

    ORDER BY ARCustomer_ID, OELineItem_ID, INV.InvoiceDate

    is your close ?

*Community Hub is the new name for Sage City