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!
The solution lies in CUST_DELIVERY_INV_REF_TAB.
SELECT coij.order_no,
coij.line_no,
coij.release_no,
coij.line_item_no,
cdir.deliv_no,
ith.quantity,
ith.cost,
ith.transaction_id
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.source_ref_type_db = 'CUST ORDER'
AND ith.transaction_code = 'OESHIP';
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.
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.
