SQL JOIN between inventory transactions and invoices

As of Apps 9, the inventory transaction history column SOURCE_REF5 now contains the delivery number, where it was blank in Apps 8. I came up with a solution to use the DENSE_RANK function to point, say, the first partial delivery to the first invoice on that line, the second to the second, etc. It’s slow and very hacky, though, and I’d hate to be the next developer who’s asked to try to understand it. I’ve decided to not trust it for production code.

In the meantime, I’m working with the sum of the revenue and the sum of the costs, grouping each by the order line.

I still can’t see how to tie a partial delivery to the correct invoice line. Does anyone have any ideas? (Bump.)

Hi, Not helpful response from me but just to say I have run into the same problem  I still can’t see how to tie a partial delivery to the correct invoice line.

Glad to see it is not just me. I may need to show the End User this thread if he starts banging on the table about it!

Have you looked at “Detail Statistics - Invoiced Sales” view?  (CUST_ORD_INVO_STAT).  This has a “Total Cost” field….

Linda

They might leave you alone if you tell them I’ve been using IFS for almost 12 years and have been developing software for over 20. 😂

Thanks! That’s at least a new rabbit hole for me to explore. Perhaps the routine that builds those statistics might have some clues.

This rabbit hole was the correct starting point. Thanks again.

(Looking at our history before and after our upgrade, it appears this solution works in 9 but not in 8.)

I’m going to be following you to see how you get on when someone asks you about credits/returns :-)  

Your prediction came true in less than 24 hours. Here’s the solution I worked out from that….

SELECT *

  FROM customer_order_inv_join coij

  LEFT JOIN cust_delivery_inv_ref_tab cdir

    ON cdir.company    = coij.company

   AND cdir.invoice_id = coij.invoice_id

   AND cdir.item_id    = coij.item_id

  LEFT JOIN inventory_transaction_hist ith

    ON (    ith.source_ref1 = coij.order_no

        AND ith.source_ref2 = coij.line_no

        AND ith.source_ref3 = coij.release_no

        AND ith.source_ref4 = coij.line_item_no

        AND ith.source_ref5 = cdir.deliv_no

        AND ith.transaction_code = 'OESHIP'

        AND ith.source_ref_type_db = 'CUST ORDER')

    OR (    ith.source_ref1 = coij.rma_no

        AND ith.source_ref2 IS NULL

        AND ith.source_ref3 IS NULL

        AND ith.source_ref4 = coij.rma_line_no

        AND ith.source_ref5 IS NULL

        AND ith.transaction_code = 'OERETURN'

        AND ith.source_ref_type_db = 'RMA')

This solution performs horribly without additional indexing.

Well done and thank you.  

For info “Cost” returns “Unit Cost” rather than “Total Cost"

(COST) * (QUANTITY) or 

ifsapp.Inventory_Transaction_Cost_API.Get_Sum_Unit_Cost(transaction_id,'TRUE','TRUE') *
QUANTITY

will pick up the total. 

Hi everyone,

we came across the same solution and I just show the relevant joins.
We prefix every IFS object in the DWH with extr_IFS to have a quick separation to let’s say extr_NAV_ or other datasource from the “old” ERP System. (also extr_xls extr_txt and so on)
The transformed tables start with sql_xx

SELECT ….

,COIL.CATALOG_NO    
,HIST.LOT_BATCH_NO
--into tmp_sql_ifs_fact_invoice_lines
FROM
 [extr_IFS_CUSTOMER_ORDER_INV_JOIN]                          as COIL
LEFT OUTER JOIN

 extr_IFS_CUST_DELIVERY_INV_REF                                as CDIR
ON
 COIL.INVOICE_ID        = CDIR.INVOICE_ID
AND
 COIL.ITEM_ID            = CDIR.ITEM_ID

and other Left outer joins…...

…..

LEFT OUTER JOIN
 sql_ifs_hlp_ITH_OESHIP                                        as HIST
ON
 COIL.CONTRACT                = HIST.CONTRACT
AND
 COIL.ORDER_NO                = HIST.SOURCE_REF1
AND
 CO.LINE_NO                    = HIST.SOURCE_REF2
AND
 COIL.CATALOG_NO            = HIST.PART_NO
AND
 CDIR.DELIV_NO                = HIST.SOURCE_REF5           

the hlp_IHR_OESHIP is a “simple” group as follows:

*****************

-- sql_ifs_hlp_ITH_OESHIP.sql
-- Zuordnung der LotBatches zur COL.
-- Hilfszähler ITHCountGrp zum Nullen der nächst höheren Ebene.
-- last Changes:
-- 13.03.19: MK created
-- 26.03.19: MK Dubletten aus Shipmentrückläufern über höchste Transaction_ID (max)
--           einer Gruppe über Gruppierung abgefangen.
-- 08.04.19: MK SOURCE_REF1 mit SingleLB = S(ingle) oder M(ulti)
--           nächste Ebene Source_ref2 ist entweder M oder S (nimm Min-> entweder M oder S wenn nur S)
-- 04.06.19: Erweiterung Transactioncodes um: PODIRSH – Direktlieferung, CO-DELV-OU – Lieferung Consi.lager bzw. Co-Consume für die Berechnung
--           In Prüfung: INTPODIRSH – Direktlieferung IC  ?
--                       OESHIPNI  - ?
-- 10.07.19: SORDER_NO aus LotBatch abgeleitet.

SELECT  
 CONTRACT
,SOURCE_REF1
,SOURCE_REF2
,SOURCE_REF3
,SOURCE_REF4
,SOURCE_REF5
,PART_NO
,LOT_BATCH_NO
,CASE WHEN (left(LOT_BATCH_NO,3) <> 'MIG'
      AND  PATINDEX('%-%',LOT_BATCH_NO) > 1)
      THEN left(LOT_BATCH_NO, PATINDEX('%-%',LOT_BATCH_NO)-1)
      ELSE LOT_BATCH_NO
 END                        as SORDER_NO
,max(TRANSACTION_ID)        as TRANSACTION_ID  -- wenn Rücknahmen von Shipments erfolgt sind, nur letzte nehmen.
,max(COST)                    as COST
,max(QUANTITY)                as QUANTITY
,CASE WHEN ROW_NUMBER() over (partition by
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO
            ORDER BY
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO + '_' +
                 cast(max(TRANSACTION_ID) as varchar)ASC)
            = 1         
            THEN 1 ELSE 0 END
                                                        as ITHCountGrp
,CASE WHEN ROW_NUMBER() over (partition by
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO
            ORDER BY
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO + '_' +
                 cast(max(TRANSACTION_ID) as varchar)ASC)
            = 1         
            THEN 'S' ELSE 'M' END                        as SingleLB
,1                                                       as ITHCount        

---------------------
 ,CONTRACT + '_' +  SOURCE_REF1 + '_' +  SOURCE_REF2 + '_' + right('000' + cast(SOURCE_REF3 as varchar),4) + '_' +
 right('000' + cast(SOURCE_REF4 as varchar),4) + '_' +  right('000' + cast(SOURCE_REF5 as varchar),4) + '_' + PART_NO + '_' +  LOT_BATCH_NO
 as UniqueKey

FROM
 extr_IFS_INVENTORY_TRANSACTION_HIST2
WHERE
-- TRANSACTION_CODE in ('OESHIP','PODIRSH','CO-DELV-OU') 18.06.19
 TRANSACTION_CODE in ('OESHIP','PODIRSH','INTPODIRSH','OESHIPNI','CO-CONSUME')
--AND SOURCE_REF1   in ('110000208','110000394', '110000613','110000904')
--AND SOURCE_REF1 = '110003287'
--and SOURCE_REF3 <> 1
GROUP BY
 CONTRACT, SOURCE_REF1, SOURCE_REF2, SOURCE_REF3, SOURCE_REF4, SOURCE_REF5,PART_NO, LOT_BATCH_NO
ORDER BY
 CONTRACT, SOURCE_REF1, SOURCE_REF2, right('000' + cast(SOURCE_REF3 as varchar),4),
 right('000' + cast(SOURCE_REF4 as varchar),4), right('000' + cast(SOURCE_REF5 as varchar),4),PART_NO, LOT_BATCH_NO, TRANSACTION_ID

*****************

So take CIL or something similar. Join it to CUST_DELIVERY_INV_REF.
Then you’ve got the 5. Reference!

The intersting part is that you can have more than one ITH record combined to the COLine.
Say 600 pcs diveded into 400 from LotBatch 1 and 200 from LB2.
In our model we went deeper than the granularity of the CustomeOrderLine or CustomerInvoiceLine.
We went down to “what are the LotBatch parts within one COL or CIL?”.

Be aware that than you have to “null” some measures the second (and so on) record resulting from your join. If not, you got dublicates and the mysterious enhanced turnover! :-)

The “SingleLB” (S=Single or M = multilotbatch) “flag” is used in the main script to devide:
,CASE WHEN ROW_NUMBER() over (partition by
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO
            ORDER BY
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO + '_' +
                 cast(max(TRANSACTION_ID) as varchar)ASC)
            = 1         
            THEN 'S' ELSE 'M' END                        as SingleLB

The ITHCountGrp is used to null all but the first in a row (of joined detail records)
,CASE WHEN ROW_NUMBER() over (partition by
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO
            ORDER BY
                CONTRACT + '_' + SOURCE_REF1 + '_' + SOURCE_REF2 + '_' +  PART_NO + '_' +
                 cast(max(TRANSACTION_ID) as varchar)ASC)
            = 1         
            THEN 1 ELSE 0 END
                                                        as ITHCountGrp

-- Invoiced Qty:
,CASE WHEN COIL.SERIES_ID = 'CR' THEN -1 ELSE 1 END *
 isnull(Hist.ITHCountGrp,1) * COIL.[INVOICED_QTY]            as InvoicedQty            

-- Nur einmal pro mehrere LBs! (once even if more than one LB,
only the first one is used, all the rest nulled.

Divide:

-- ID6 ProductSales: Reiner Artikelumsatz, keine Chargen.
 CASE WHEN isnull(SLBP.SingleLB,'?') = 'M'                    -- Umsatz im MLB Fall aufteilen
      THEN
        case when COIL.[CHARGE_SEQ_NO] is null                        
             then [NET_DOM_AMOUNT]
             else 0 end
        * isnull(HIST.Quantity,0) /  COIL.INVOICED_QTY        -- aufgeteilt i.S.v. Teilmenge zu Gesamtmenge
      ELSE                                                                             -- for example: take 200 from 600
        case when COIL.[CHARGE_SEQ_NO] is null                        
             then [NET_DOM_AMOUNT]
             else 0 end                                        -- nicht aufteilen
      END                                                      as ProductSales -- 2 records if multilotbatch with 2 LBs

hth and all the best!

Michael

By the way: The relation REF5 to CDIR (funny how we tend to use the same Alias!!!)
took me several hours of database surfing.
Not the most elegant way but sucessful at last.

-- Analyse_Script_for_SOURCE_REF5..sql

SELECT 'SELECT * FROM ' as text1, TABLE_NAME, ';' as text2 from
(
SELECT
TABLE_NAME --,COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE
-- TABLE_NAME in( 'PERS', 'COMPANY_PERSON_ALL','PI_EMP_COST_CENTER','PI_EMP_CONTRACT_DATA','PI_EMP_COST_CENTER_SHARE')
 COLUMN_NAME = 'SOURCE_REF5' -- or ‘DELIV_NO’
ORDER BY TABLE_NAME
) 

With this script I just tested table for table and searched for the specific connection.

Hi All,

This is very helpful….😀

Thanks

Gayan