MSRFWOR - Refresh Snapshots fails with ORA-1722 when running Data Collections (Doc ID 300408.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.5 to 11.5.10.2 [Release 11.5]
Information in this document applies to any platform.
EXECUTABLE:MSRFWOR - Refresh Collection Snapshots
EXECUTABLE:MSCPDP - Planning Data Pull

Symptoms

1. MSRFWOR - Refresh Snapshots fails with ORA-1722 when running Data Collections

2. It does not error when running the program as a standalone request

3. The statement below is the cause of the failure.
-- When a profile that has a to_number setting has a character entry, then the ORA-1722 will occur.
-- It is programatically impossible to restrict the entry to a number in the applications
-- Therefore it is the users responsibility to setup these profiles correctly.

UPDATE MRP_AP_APPS_INSTANCES SET LRN= MRP_AP_REFRESH_S.CURRVAL, LRD= SYSDATE,
LAST_UPDATE_DATE= SYSDATE, LAST_UPDATED_BY= FND_GLOBAL.USER_ID, BOM_HOUR_UOM_CODE
=FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE'), MRP_MPS_CONSUMPTION =DECODE
FND_PROFILE.VALUE('MRP_MPS_CONSUMPTION'), 'Y', 1, '1', 1, 2),
MRP_SHIP_ARRIVE_FLAG =DECODE( FND_PROFILE.VALUE('MRP_SHIP_ARRIVE_FLAG'),
'Y', 1, '1', 1, 2), CRP_SPREAD_LOAD =DECODE(
FND_PROFILE.VALUE('CRP_SPREAD_LOAD'), 'Y',1, '1', 1, 2),
MSO_ITEM_DMD_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_ITEM_DMD_PENALTY')), MSO_ITEM_CAP_PENALTY =TO_NUMBER
( FND_PROFILE.VALUE('MSO_ITEM_CAP_PENALTY')), MSO_ORG_DMD_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_ORG_DMD_PENALTY')), MSO_ORG_ITEM_PENALTY =TO_NUMBER
( FND_PROFILE.VALUE('MSO_ORG_ITEM_PENALTY')), MSO_ORG_RES_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_ORG_RES_PENALTY')), MSO_ORG_TRSP_PENALTY =TO_NUMBER
( FND_PROFILE.VALUE('MSO_ORG_TRSP_PENALTY')), MSC_AGGREG_RES_NAME =TO_NUMBER
FND_PROFILE.VALUE('MSC_AGGREG_RES_NAME')), MSO_RES_PENALTY =TO_NUMBER
( FND_PROFILE.VALUE('MSO_RES_PENALTY')), MSO_SUP_CAP_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_SUP_CAP_PENALTY')), MSC_BOM_SUBST_PRIORITY =TO_NUMBER
( FND_PROFILE.VALUE('MSC_BOM_SUBST_PRIORITY')), MSO_TRSP_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_TRSP_PENALTY')), MSC_ALT_BOM_COST =TO_NUMBER
( FND_PROFILE.VALUE('MSC_ALT_BOM_COST')), MSO_FCST_PENALTY =TO_NUMBER
FND_PROFILE.VALUE('MSO_FCST_PENALTY')), MSO_SO_PENALTY =TO_NUMBER
( FND_PROFILE.VALUE('MSO_SO_PENALTY')), MSC_ALT_OP_RES =TO_NUMBER
FND_PROFILE.VALUE('MSC_RESOURCE_TYPE')), MSC_ALT_RES_PRIORITY =TO_NUMBER
( FND_PROFILE.VALUE('MSC_ALT_RES_PRIORITY')), MSC_SIMUL_RES_SEQ =TO_NUMBER
FND_PROFILE.VALUE('MSC_SIMUL_RES_SEQ')), MRP_BIS_AV_DISCOUNT
=NVL(TO_NUMBER(FND_PROFILE.VALUE('MRP_BIS_AV_DISCOUNT')),0), MRP_BIS_PRICE_LIST
=TO_NUMBER
( FND_PROFILE.VALUE('MRP_BIS_PRICE_LIST')), MSC_DMD_PRIORITY_FLEX_NUM=NVL(TO_NUMBER
( FND_PROFILE.VALUE('MSC_DMD_PRIORITY_FLEX_NUM')),0), MSC_BATCHABLE_FLAG =TO_NUMBER
( FND_PROFILE.VALUE('MSC_BATCHABLE_FLAG')), MSC_BATCHING_WINDOW =TO_NUMBER
FND_PROFILE.VALUE('MSC_BATCHING_WINDOW')), MSC_MIN_CAPACITY =TO_NUMBER
FND_PROFILE.VALUE('MSC_MIN_CAPACITY')), MSC_MAX_CAPACITY =TO_NUMBER
FND_PROFILE.VALUE('MSC_MAX_CAPACITY')), MSC_UNIT_OF_MEASURE =TO_NUMBER
( FND_PROFILE.VALUE('MSC_UNIT_OF_MEASURE')), MSC_SO_OFFSET_DAYS =TO_NUMBER
NVL(FND_PROFILE.VALUE ('MSC_SO_OFFSET_DAYS'),99999)), ITEM_NAME_FROM_KFV = :lv_item_name_kfv
END OF STMT

4. Verified the issue by the log file and trace file

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms