1 Reply Latest reply: Dec 5, 2012 12:14 PM by 977964 RSS

    101511: non-ORACLE exception

    977964
      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.