How to recover from a deleted Lov Partial SQL for AOL Form Function (4284430)

Run the following SQL statement in the Stat db if you accidentally delete AOL Form Function only:

INSERT INTO STAT_OBJECT_TYPES

(OBJECT_CLASS,OBJECT_TYPE,DESCR,STATUS_CD,

FILE_EXTENSION,FILE_FORMAT,IMPACT_PROTOCOL,UPDATE_DT,UPDATE_USERID,

DATABASE_SCRIPT,FOR_WEB,FOR_FORM,FOR_DATABASE,FOR_CNCRNTMGR,

OA_CUSTOM,OBJECT_TYPE_CODE)

SELECT *

FROM (SELECT 'L' OBJECT_CLASS,

             MAX(TO_NUMBER(STAT_OBJECT_TYPES.OBJECT_TYPE))+1 OBJECT_TYPE,

             'AOL Form Function' DESCR,

             'A' STATUS_CD,

             ' ' FILE_EXTENSION,

             ' ' FILE_FORMAT,

             0 IMPACT_PROTOCOL,

             SYSDATE UPDATE_DT,

             'SYSTEM' UPDATE_USERID,

             'N' DATABASE_SCRIPT,

             'N' FOR_WEB,

             'N' FOR_FORM,

             'Y' FOR_DATABASE,

             'N' FOR_CNCRNTMGR,

             'N' OA_CUSTOM,

             'AOL' OBJECT_TYPE_CODE

      FROM STAT_OBJECT_TYPES

      WHERE OBJECT_CLASS = 'L')

WHERE NOT EXISTS (SELECT NULL

                  FROM STAT_OBJECT_TYPES

                  WHERE OBJECT_TYPE_CODE = 'AOL'

                     AND DESCR = 'AOL Form Function');

DELETE STAT_OAAOL_OBJTYP_PARAMS

WHERE (REF_ID) IN (SELECT DISTINCT REF_ID

                                      FROM STAT_OBJECT_TYPES A, STAT_OAAOL_OBJTYP B

                                      WHERE A.OBJECT_CLASS = B.OBJECT_CLASS

                                        AND A.OBJECT_TYPE = B.OBJECT_TYPE

                                        AND A.OBJECT_CLASS = 'L'

                                        AND A.DESCR = 'AOL Form Function');

DELETE STAT_OAAOL_OBJTYP

WHERE (OBJECT_CLASS, OBJECT_TYPE) IN (SELECT OBJECT_CLASS, OBJECT_TYPE

                                      FROM STAT_OBJECT_TYPES

                                      WHERE OBJECT_CLASS = 'L'

                                        AND DESCR = 'AOL Form Function');

INSERT INTO STAT_OAAOL_OBJTYP

  (OBJECT_CLASS, OBJECT_TYPE, LCT_PRODUCT, LCT_FILENAME, LOADER_UPLOAD_PARAMS,

  ENTITY_NAME, ENTITY_SEPSTR, UPDATE_DT, UPDATE_USERID,

  LOV_PARTIAL_SQL, REF_ID, OA_VERSION_ID)

SELECT OBJECT_CLASS, OBJECT_TYPE, 'FND', 'afsload.lct', 'CUSTOM_MODE=FORCE',

       'FUNCTION', '::', SYSDATE, 'SYSTEM',

       'FROM FND_FORM_FUNCTIONS_VL     FNC,

     FND_APPLICATION           APP,

     FND_FORM_VL               FRM,

     FND_PRODUCT_INSTALLATIONS PRD

WHERE FRM.APPLICATION_ID = FNC.APPLICATION_ID

  AND FRM.FORM_ID = FNC.FORM_ID

  AND FRM.APPLICATION_ID = APP.APPLICATION_ID

  AND APP.APPLICATION_ID = PRD.APPLICATION_ID

  AND PRD.STATUS in (''I'',''L'',''S'')

  AND (FNC.TYPE IS NULL OR FNC.TYPE IN (''FORM'',''SUBFUNCTION''))', STAT_OAAOL_OBJTYP_SEQ.NEXTVAL, 1

FROM STAT_OBJECT_TYPES

WHERE OBJECT_CLASS = 'L' AND DESCR = 'AOL Form Function';

INSERT INTO STAT_OAAOL_OBJTYP

  (OBJECT_CLASS, OBJECT_TYPE, LCT_PRODUCT, LCT_FILENAME, LOADER_UPLOAD_PARAMS,

  ENTITY_NAME, ENTITY_SEPSTR, UPDATE_DT, UPDATE_USERID,

  LOV_PARTIAL_SQL, REF_ID, OA_VERSION_ID)

SELECT OBJECT_CLASS, OBJECT_TYPE, 'FND', 'afsload.lct', 'CUSTOM_MODE=FORCE',

       'FUNCTION', '::', SYSDATE, 'SYSTEM',

       'FROM FND_FORM_FUNCTIONS_VL     FNC,

     FND_APPLICATION           APP,

     FND_FORM_VL               FRM,

     FND_PRODUCT_INSTALLATIONS PRD

WHERE FRM.APPLICATION_ID = FNC.APPLICATION_ID

  AND FRM.FORM_ID = FNC.FORM_ID

  AND FRM.APPLICATION_ID = APP.APPLICATION_ID

  AND APP.APPLICATION_ID = PRD.APPLICATION_ID

  AND PRD.STATUS in (''I'',''L'',''S'')

  AND (FNC.TYPE IS NULL OR FNC.TYPE IN (''FORM'',''SUBFUNCTION''))', STAT_OAAOL_OBJTYP_SEQ.CURRVAL, 2

FROM STAT_OBJECT_TYPES

WHERE OBJECT_CLASS = 'L' AND DESCR = 'AOL Form Function';

INSERT INTO STAT_OAAOL_OBJTYP_PARAMS

  (ENTITY_PARAMETER_ID, ENTITY_PARAMETER_NAME, ENTITY_COLUMN_NAME, ENTITY_DISPLAY_NAME,

  UPDATE_DT, UPDATE_USERID, REF_ID)

SELECT 1, 'FUNC_APP_SHORT_NAME', 'APP.APPLICATION_SHORT_NAME', 'App',

       SYSDATE, 'SYSTEM', STAT_OAAOL_OBJTYP_SEQ.CURRVAL

FROM STAT_OBJECT_TYPES

WHERE OBJECT_CLASS = 'L' AND DESCR = 'AOL Form Function';

INSERT INTO STAT_OAAOL_OBJTYP_PARAMS

  (ENTITY_PARAMETER_ID, ENTITY_PARAMETER_NAME, ENTITY_COLUMN_NAME, ENTITY_DISPLAY_NAME,

  UPDATE_DT, UPDATE_USERID, REF_ID)

SELECT 2, 'FUNCTION_NAME', 'FNC.FUNCTION_NAME', 'Function',

       SYSDATE, 'SYSTEM', STAT_OAAOL_OBJTYP_SEQ.CURRVAL

FROM STAT_OBJECT_TYPES

WHERE OBJECT_CLASS = 'L' AND DESCR = 'AOL Form Function';

INSERT INTO STAT_OAAOL_OBJTYP_PARAMS

  (ENTITY_PARAMETER_ID, ENTITY_PARAMETER_NAME, ENTITY_COLUMN_NAME, ENTITY_DISPLAY_NAME,

  UPDATE_DT, UPDATE_USERID, REF_ID)

SELECT 3, NULL, 'FNC.USER_FUNCTION_NAME', 'Name',

       SYSDATE, 'SYSTEM', STAT_OAAOL_OBJTYP_SEQ.CURRVAL

FROM STAT_OBJECT_TYPES

WHERE OBJECT_CLASS = 'L' AND DESCR = 'AOL Form Function';

COMMIT;