Hi all,
When I run One level cost I got error but All level cost not. After run All level cost then One level cost works. Here is some details. It seems a bug.
Title: One Level Part Cost Calculation fails with ORA-01858 on manufactured multi-level parts (column order mismatch in Insert_Roll_Up_Cost_One_Lvl___)
Environment: IFS Cloud 25.2.4 SU4 (site BPM20, cost set 8)
Symptom:
Calling the CalculateOneLevel action (PartCostHandling projection) — or One Level
Part Cost Calculation in Aurena/IFSAPP — fails for manufactured parts whose
component is itself a rolled-up manufactured sub-assembly. Example: part 3068631
(-> 306863101 -> 3045917). Calculate All Levels succeeds; a subsequent One Level
then succeeds. Error returned to client:
ORA-01858: a non-numeric character was found where a numeric was expected
Diagnosis (root cause):
Backtrace of the failing call:
ORA-01858 at IFSAPP.COST_CALCULATION_API line 9155 (FETCH ... BULK COLLECT)
<- COST_CALCULATION_API line 4970 (Insert_Roll_Up_Cost_One_Lvl___)
<- PART_COST_API line 8077 (Cost_Calculation_API.Roll_Up_Cost_One_Level)
<- PART_COST_API line 5906 (Part_Cost_API.Calculate_One_Level -> Calculate_Cost__)
COST_CALCULATION_API.Insert_Roll_Up_Cost_One_Lvl___ opens a dynamic SELECT on
PART_COST_BUCKET_UNION ending "... accum_scrap_cost, rowversion,
cost_calculation_source" and FETCH ... BULK COLLECT INTO a
TABLE OF part_cost_bucket_tab%ROWTYPE. The fetch is positional.
Deployed PART_COST_BUCKET_TAB column order is:
... 30 ACCUM_SCRAP_COST, 31 COST_CALCULATION_SOURCE (VARCHAR2), 32 ROWVERSION (DATE)
i.e. positions 31/32 are the reverse of the SELECT list order.
Result: the VARCHAR2 value of COST_CALCULATION_SOURCE ('COSTROLLUP') is fetched
into the ROWVERSION (DATE) record field, causing TO_DATE('COSTROLLUP') -> ORA-01858.
It only triggers when a fetched child bucket has a non-null cost_calculation_source
('COSTROLLUP'), i.e. when rolling up a manufactured sub-assembly component; purchased
components (NULL source) are unaffected.
This is the same column-order-mismatch class as MFZ-28370 (which corrected the
Insert_New_Record_Arr__/Insert_New_Record___/Do_Cost_Move_From_One___ routines to
use explicit column lists), but Insert_Roll_Up_Cost_One_Lvl___ still fetches
positionally and is not covered.
Request:
Please confirm whether Insert_Roll_Up_Cost_One_Lvl___ should use an explicit column
list (as per MFZ-28370), and/or whether PART_COST_BUCKET_TAB and its partition tables
(PART_COST_BUCKET_TWO_TAB ... _ELEVEN_TAB) should be rebuilt to the delivered column
order in this environment. Provide the corrective / patch reference.
Workaround in use: run Calculate All Levels before One Level.
