Report - splitting an invoice line according to deliveries

Hello ​@romsar 

Did you try creating invoice with Create Shipment Invoices functionality. There you create invoice per each shipment/delivery. If you have 3 different shipments for single order then you will have 3 different invoices. You can deliver multiple orders in single shipment and those order lines will be added to the same invoice. 
let me know if this is what you are looking for.

Thanks

Furkam

Hello ​@Furkan Zengin 

No, I have no action to do in IFS, but on the customer invoice report.

To find the different deliveries on a customer invoice line, we run the following sql query :

SELECT DELIVERY FROM customer_order_ivc_rep

WHERE RESULT_KEY = xxxxxx

    AND DELIVERY IS NOT NULL

    AND ORDER_LINE_POS = xxxxx

    AND ROWNUM = 1

Where we get the ORDER_LINE_POS value from this sql query :

SELECT CUSTOMER_ORDER_INV_ITEM.POS

FROM CUSTOMER_ORDER_INV_ITEM

LEFT JOIN INVENTORY_PART ON (INVENTORY_PART.PART_NO = CUSTOMER_ORDER_INV_ITEM.CATALOG_NO AND INVENTORY_PART.CONTRACT=CUSTOMER_ORDER_INV_ITEM.CONTRACT )

LEFT JOIN CUSTOMER_ORDER_LINE_CFV ON (CUSTOMER_ORDER_LINE_CFV.LINE_NO = CUSTOMER_ORDER_INV_ITEM.LINE_NO AND CUSTOMER_ORDER_LINE_CFV.REL_NO = CUSTOMER_ORDER_INV_ITEM.RELEASE_NO AND CUSTOMER_ORDER_LINE_CFV.ORDER_NO = CUSTOMER_ORDER_INV_ITEM.ORDER_NO )

WHERE CUSTOMER_ORDER_INV_ITEM.INVOICE_ID = xxxx

The CUSTOMER_ORDER_COLL_IVC_REP.DELIVERY field displays delivery notes with a comma as separated value, but I need to know the shipped quantity on each customer invoice line and for each delivery note

Hello ​@romsar 

Please review following views. Here you will have connection between invoice, delivery note, shipment and quantities. It may help you to split invoice lines per delivery.

select * from cust_delivery_inv_ref

select * from customer_order_delivery

Hello ​@Furkan Zengin 

So I would need to run this following sql query to get the different delnote N° :
select cust_delivery_inv_ref.ITEM_ID, customer_order_delivery.QTY_SHIPPED, customer_order_delivery.DELNOTE_NO,

        CUST_ORDER_INV_ITEM_UIV_ALL.catalog_no, CUST_ORDER_INV_ITEM_UIV_ALL.description,

        CUST_ORDER_INV_ITEM_UIV_ALL.LINE_NO, CUST_ORDER_INV_ITEM_UIV_ALL.CUSTOMER_PO_NO,

        customer_order_line.customer_po_line_no,

        CUST_ORDER_INV_ITEM_UIV_ALL.VAT_CODE,

        CUST_ORDER_INV_ITEM_UIV_ALL.SALE_UNIT_PRICE,

        customer_order.currency_code

from customer_order_delivery

inner join cust_delivery_inv_ref on cust_delivery_inv_ref.DELIV_NO=customer_order_delivery.DELIV_NO

inner join CUST_ORDER_INV_ITEM_UIV_ALL on CUST_ORDER_INV_ITEM_UIV_ALL.COMPANY=cust_delivery_inv_ref.COMPANY

            AND CUST_ORDER_INV_ITEM_UIV_ALL.INVOICE_ID=cust_delivery_inv_ref.INVOICE_ID

            AND CUST_ORDER_INV_ITEM_UIV_ALL.ITEM_ID=cust_delivery_inv_ref.ITEM_ID        

inner join customer_order_line on customer_order_line.order_no=CUST_ORDER_INV_ITEM_UIV_ALL.order_no

                and customer_order_line.line_no=CUST_ORDER_INV_ITEM_UIV_ALL.line_no

                and customer_order_line.rel_no=CUST_ORDER_INV_ITEM_UIV_ALL.release_no

                and customer_order_line.line_item_no=CUST_ORDER_INV_ITEM_UIV_ALL.line_item_no

                and customer_order_line.contract=CUST_ORDER_INV_ITEM_UIV_ALL.contract

inner join customer_order on customer_order.contract=customer_order_line.contract

                and customer_order.order_no=customer_order_line.order_no

where cust_delivery_inv_ref.COMPANY=xxxxx

and cust_delivery_inv_ref.INVOICE_ID=xxxxx

order by CUST_ORDER_INV_ITEM_UIV_ALL.pos

My query is returning appropriated informations. I reached an invoice with 1 line but 2 deliveries, the query returns 2 lines with a different QTY_SHIPPED and DELNOTE_NO values.

Do you think I can go this way? and how to make sure that it’s returning non invoiced deliveries?

Hello ​@Furkan Zengin 

may you tell me if I can continue this way?

Hello ​@romsar 

I think this approach is correct. You must get the shipped quantity from customer_order_delivery which is correct.

I would rather get the currency not from customer order but from invoice directly. Especially, with 25R1 there is a new functionality to create invoice in different currency than the order currency.

Here is how i am using these views to generate the report I need.

select di.company,
       di.invoice_id,
       di.item_id,
       od.deliv_no,
       od.shipment_id,
       od.order_no,
       od.line_no,
       od.rel_no,
       od.line_item_no,
       ii.identity as "Customer ID",
       ifsapp.customer_info_api.get_name(ii.identity) as "Customer Name",
       ii.catalog_no, 
       ii.description,
       ii.invoiced_qty,
       ii.sale_um,
       ii.sale_unit_price,
       ii.net_dom_amount,
       ii.net_curr_amount,
       ifsapp.invoice_api.get_currency(ii.company, ii.invoice_id) as "Currency",
       ifsapp.invoice_api.get_curr_rate(ii.company, ii.invoice_id) as "Currency Rate",
       (select sum (ip.debit_credit_amount) 
                from ifsapp.mpccom_accounting ip, ifsapp.inventory_transaction_hist2 it
                where it.source_ref1 = od.order_no
                and it.source_ref2 = od.line_no
                and it.source_ref3 = od.rel_no
                and it.source_ref4 = od.line_item_no
                and it.source_ref5 = od.deliv_no
                and ip.accounting_id=it.accounting_id 
                and ip.contract= it.contract 
                and it.source_ref_type_db='CUST ORDER'
                and it.transaction_code in ('OESHIP','OEUNSHIP')
                and ip.str_code='M24') as "Total COGS"
  from ifsapp.cust_delivery_inv_ref di,
       ifsapp.customer_order_delivery od,
       ifsapp.CUST_ORDER_INV_ITEM_UIV_ALL ii
 where od.deliv_no = di.deliv_no
   and di.company = 'XXX'
   and di.invoice_id = 'NNN'
   and di.company=ii.company
   and di.invoice_id=ii.invoice_id
   and di.item_id = ii.item_id
   and od.cancelled_delivery_db = 'FALSE'

Hope this helps