This discussion is archived
1 Reply Latest reply: Dec 5, 2012 10:14 AM by 977964 RSS

101511: non-ORACLE exception

977964 Newbie
Currently Being Moderated
Hello Gurus,
I am trying to update DFF in After report trigger. When I do srw message of the Update statement and run it in toad, it updates the rows correctly but it is not updating the rows. I get "101511: non-ORACLE" exception. Could you please help

FUNCTION AfterReport
RETURN BOOLEAN
IS
v_sql varchar2(2000);
BEGIN

srw.message('50000','UPDATE wip.wip_discrete_jobs
SET attribute10 = ''Y'' , attribute_category = 101
WHERE wip_entity_id IN
(SELECT distinct we.wip_entity_id
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS MIL
, MTL_ITEM_LOCATIONS MIL2
, MTL_SECONDARY_INVENTORIES MSINV
, MTL_ITEM_QUANTITIES_VIEW MOQ2
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MSITL
, WIP_ACCOUNTING_CLASSES WAC
, MTL_SYSTEM_ITEMS_VL MSI2
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_SCHEDULE_GROUPS SG
, WIP_DISCRETE_JOBS WDJ
WHERE WDJ.ORGANIZATION_ID = '||:P_ORGANIZATION_ID||
' AND WDJ.STATUS_TYPE IN ' ||:X_Limit_Status||
' AND SG.SCHEDULE_GROUP_ID'|| :P_SG_OUTER||' = WDJ.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID '||:P_SG_OUTER||' = WDJ.ORGANIZATION_ID
AND ML1.LOOKUP_TYPE = ''WIP_JOB_STATUS''
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML1.ENABLED_FLAG = ''Y''
AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) =' ||:P_ORGANIZATION_ID||
' AND MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSITL.ORGANIZATION_ID(+) ='|| :P_ORGANIZATION_ID||
' AND MSITL.LANGUAGE(+) = USERENV(''LANG'')
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE IN (1,6)
AND WE.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID||' '||
:X_Limit_Jobs ||' '||
:X_Limit_Dates ||' '||
:X_Schedule_Group ||' '||
' AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID||
' AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID ||
' AND WRO.WIP_SUPPLY_TYPE <> 6
AND ( ( WRO.REQUIRED_QUANTITY > 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 )
OR ( WRO.REQUIRED_QUANTITY < 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 ) )'||
:X_Limit_Supply_Type||' '||
:X_Limit_Subinv||
' AND ML2.LOOKUP_TYPE = ''WIP_SUPPLY_PICK''
AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND ML2.ENABLED_FLAG = ''Y''
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MOQ2.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND MSINV.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MSINV.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE
AND (MSINV.DISABLE_DATE IS NULL OR MSINV.DISABLE_DATE > TRUNC(SYSDATE))
AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND MIL2.ORGANIZATION_ID(+) =' ||:P_ORGANIZATION_ID||')');
BEGIN
srw.do_sql (
'UPDATE wip.wip_discrete_jobs
SET attribute1 = ''Y'' , attribute_category = 101
WHERE wip_entity_id IN
(SELECT we.wip_entity_id,
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS MIL
, MTL_ITEM_LOCATIONS MIL2
, MTL_SECONDARY_INVENTORIES MSINV
, MTL_ITEM_QUANTITIES_VIEW MOQ2
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MSITL
, WIP_ACCOUNTING_CLASSES WAC
, MTL_SYSTEM_ITEMS_VL MSI2
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_SCHEDULE_GROUPS SG
, WIP_DISCRETE_JOBS WDJ
WHERE WDJ.ORGANIZATION_ID = '||:P_ORGANIZATION_ID||
' AND WDJ.STATUS_TYPE IN ' ||:X_Limit_Status||
' AND SG.SCHEDULE_GROUP_ID'|| :P_SG_OUTER||' = WDJ.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID '||:P_SG_OUTER||' = WDJ.ORGANIZATION_ID
AND ML1.LOOKUP_TYPE = ''WIP_JOB_STATUS''
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML1.ENABLED_FLAG = ''Y''
AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) =' ||:P_ORGANIZATION_ID||
' AND MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSITL.ORGANIZATION_ID(+) ='|| :P_ORGANIZATION_ID||
' AND MSITL.LANGUAGE(+) = USERENV(''LANG'')
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE IN (1,6)
AND WE.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID||' '||
:X_Limit_Jobs ||' '||
:X_Limit_Dates ||' '||
:X_Schedule_Group ||' '||
' AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID||
' AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID ='|| :P_ORGANIZATION_ID ||
' AND WRO.WIP_SUPPLY_TYPE <> 6
AND ( ( WRO.REQUIRED_QUANTITY > 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 )
OR ( WRO.REQUIRED_QUANTITY < 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 ) )'||
:X_Limit_Supply_Type||' '||
:X_Limit_Subinv||
' AND ML2.LOOKUP_TYPE = ''WIP_SUPPLY_PICK''
AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND ML2.ENABLED_FLAG = ''Y''
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MOQ2.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND MSINV.ORGANIZATION_ID(+) = '||:P_ORGANIZATION_ID||
' AND MSINV.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE
AND (MSINV.DISABLE_DATE IS NULL OR MSINV.DISABLE_DATE > TRUNC(SYSDATE))
AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND MIL2.ORGANIZATION_ID(+) =' ||:P_ORGANIZATION_ID||');');

srw.do_sql ('COMMIT;');
EXCEPTION
WHEN SRW.DO_SQL_FAILURE
THEN
SRW.MESSAGE (10101, SQLERRM);
END;
BEGIN
SRW.USER_EXIT ('FND SRWEXIT');
EXCEPTION
WHEN srw.user_exit_failure
THEN
srw.MESSAGE (1, 'Failed in SRWEXIT');
END;
RETURN (TRUE);
END;

===========================================
log file
===========================================

MSG-50000: UPDATE wip.wip_discrete_jobs
SET attribute10 = 'Y' , attribute_category = 101
WHERE wip_entity_id IN
(SELECT distinct we.wip_entity_id
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS MIL
, MTL_ITEM_LOCATIONS MIL2
, MTL_SECONDARY_INVENTORIES MSINV
, MTL_ITEM_QUANTITIES_VIEW MOQ2
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MSITL
, WIP_ACCOUNTING_CLASSES WAC
, MTL_SYSTEM_ITEMS_VL MSI2
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_SCHEDULE_GROUPS SG
, WIP_DISCRETE_JOBS WDJ
WHERE WDJ.ORGANIZATION_ID = 122 AND WDJ.STATUS_TYPE IN (1,3,4,6) AND SG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML1.ENABLED_FLAG = 'Y'
AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) =122 AND MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSITL.ORGANIZATION_ID(+) =122 AND MSITL.LANGUAGE(+) = USERENV('LANG')
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE IN (1,6)
AND WE.ORGANIZATION_ID =122 AND WE.WIP_ENTITY_NAME BETWEEN 'kv001' AND 'kv001' AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID =122 AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID =122 AND WRO.WIP_SUPPLY_TYPE <> 6
AND ( ( WRO.REQUIRED_QUANTITY > 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 )
OR ( WRO.REQUIRED_QUANTITY < 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 ) ) AND ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND ML2.ENABLED_FLAG = 'Y'
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = 122 AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MOQ2.ORGANIZATION_ID(+) = 122 AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND MSINV.ORGANIZATION_ID(+) = 122 AND MSINV.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE
AND (MSINV.DISABLE_DATE IS NULL OR MSINV.DISABLE_DATE > TRUNC(SYSDATE))
AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND MIL2.ORGANIZATION_ID(+) =122)
MSG-10101: 101511: non-ORACLE exception

Thanks,
Ven.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points