How to Create Global Temporary Tables


CREATE GLOBAL TEMPORARY TABLE temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS
NOCACHE;


ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.



CREATE GLOBAL TEMPORARY TABLE temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS
NOCACHE;


ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.

Example:

CREATE GLOBAL TEMPORARY TABLE APPS.XXJG_ITEM_ONHAND_QTY
(
  ORGANIZATION_ID               NUMBER,
  INVENTORY_ITEM_ID             NUMBER,
  ONHAND_QUANTITIES_ID          NUMBER,
  LOT_NUMBER                    VARCHAR2(40 BYTE),
  SUBINVENTORY_CODE             VARCHAR2(10 BYTE),
  PRIMARY_UOM_CODE              VARCHAR2(10 BYTE),
  PRIMARY_TRANSACTION_QUANTITY  NUMBER(38,10),
  PRIMARY_RESERVATION_QUANTITY  NUMBER(38,10),
  USABLE_QUANTITY               NUMBER(38,10),
  CREATION_DATE                 DATE
)
ON COMMIT PRESERVE ROWS
RESULT_CACHE (MODE DEFAULT)
NOCACHE;

How to find all Form Personalization in Apps


SELECT FORM_ID, FORM_NAME,APPLICATION_NAME, FFV.USER_FORM_NAME, FFV.DESCRIPTION
FROM FND_FORM_VL FFV
    ,FND_APPLICATION_TL FAT
WHERE FFV.APPLICATION_ID=FAT.APPLICATION_ID
AND FORM_NAME IN
                (SELECT FORM_NAME --,FUNCTION_NAME, DESCRIPTION, TRIGGER_EVENT,  TRIGGER_OBJECT,CONDITION
                FROM FND_FORM_CUSTOM_RULES
                WHERE ENABLED='Y'
                GROUP BY FORM_NAME)