oracle ebs r12 19c database query running slow solution ex: cst_cg_cost_history_v view

 if query running very slow then add this /*+ optimizer_features_enable('12.1.0.2') */ 
ex: 
select /*+ optimizer_features_enable('12.1.0.2') */  * from dual;
SELECT /*+ optimizer_features_enable('12.1.0.2') */ ACTUAL_COST FROM cst_cg_cost_history_v cst
where cst.inventory_item_id = MMT.INVENTORY_ITEM_ID
AND TRUNC(cst.TRANSACTION_DATE) = TRUNC(MMT.TRANSACTION_DATE)
and cst.organization_id = mmt.organization_id

EBS R12 Custom Form to add search Query

 https://www.youtube.com/watch?v=hLP4fGmy4Bc (check from 9min)
https://erpschools.com/erps/forms/query_find-form-in-oracle-apps
1. open APPSTAND.fmb form.
2. from object_groups drag QUERY_FIND to your form module(name of your custom form) and drop with ctl key pressed,
  once asked for confirmation select sub-class.
3. new QUERY_FIND OBJECTs will be created with same name under DataBlocks, Canvases, Object Groups of your custom form.
4. QUERY_FIND data block
Button New
replace the code with your block ex: DEPT
app_find.new('DEPT');
5. Button Find
:parameter.g_query_find := 'TRUE';
app_find.find('DEPT');
:parameter.g_query_find := 'FALSE';
6. open the canvas QUERY_FIND
drag one text field and give name ex: EMP_NUMBER_Q
7. Drag Trigger named QUERY_FIND from Appstand.fmb (STANDARD_OBJECTS) from main triggers section
and drop it to under your block ex: DEPT
8. open the code of this trigger we just dropped, modify it
--APPS_STANDARD.EVENT('QUERY_FIND'); --comment this
app_find.query_find('DEPT_WIND', --your main block window
'QUERY_FIND',
'QUERY_FIND');
9. under DEPT block new trigger pre-query:
if :parameter.g_query_find = 'TRUE' then
copy(:QUERY_FIND.EMP_NUMBER_Q,'DEPT.EMPNO');
:parameter.g_query_find := 'TRUE';
end if;

EBS R12 KFF in Custom Form

 1. update form level trigger WHEN-NEW-FORM-INSTANCE

declare
cursor get_cao is        
select  to_char(id_flex_num)        
from    fnd_id_flex_structures        
where   id_flex_structure_code = 'XX COA'               
 and id_flex_code = 'GL#';     
 v_cao    varchar2(1000);            
 begin    
  open get_cao;    
 fetch get_cao into v_cao;    
 if get_cao%notfound then        
 v_cao := '101';
     end if;    
     close get_cao;       
app_standard.event('WHEN-NEW-FORM-INSTANCE');    
 fnd_key_flex.define(      
 BLOCK=>'SFF_MO_HEADER_V',       --SFF_MO_HEADER_V block SFF_MOVE_ORDER_CAN in canvas
 FIELD=>'DESTINATION_ACCOUNT_SEGMENTS',  --field for LOV should be not database   
  APPL_SHORT_NAME=>'SQLGL',     
   CODE=>'GL#',      
   NUM=>v_cao,      
   ID=>'DESTINATION_ACCOUNT_CCID',--to show/store the CCID      
   DESCRIPTION=>'',
   TITLE=>'Test Title',      
   VALIDATE=>'FULL',      
   QBE_IN=>'Y',      
   DERIVE_ALWAYS=>'Y',      
   updateable => '',      
   --VRULE=>'\nSUMMARY_FLAG\nI\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN',      
   --vrule => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL='||'"'||'SQLGL'||'"'||'; name=Parent Values are not allowed\nN'
   where_clause => 'summary_flag !=''Y''',      
   QUERY_SECURITY =>'Y');  
   end ;--initialize;     

2. Field Properties
DESTINATION_ACCOUNT_SEGMENTS,  --field for LOV should be not database   
Database Item: No
Query Only: No
List of Values: Enable List Lamp
Validate from List: No

3. field DESTINATION_ACCOUNT_CCID,--to show/store the CCID      

Triggers:

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from Block Level:

• PRE-QUERY --  FND_FLEX.EVENT('PRE-QUERY' );
• POST-QUERY -- FND_FLEX.EVENT('POST-QUERY');
• PRE-INSERT -- 
• PRE-UPDATE -- 
• WHEN-VALIDATE-RECORD  (item Level down)
• WHEN-NEW-ITEM-INSTANCE (item Level down)
• WHEN-VALIDATE-ITEM (item Level down)

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’)

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT(‘KEY-EDIT’);

5. Write an item level trigger WHEN-VALIDATE-ITEM on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM’);

IF :XX_BLOCK.DESTINATION_ACCOUNT_CCID = -1 THEN
FND_MESSAGE.SET_STRING(‘You Have Selected An Undefined Code Combination !’);
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

DFF in Custom form and register in EBS

 custom table with attributes  attribute_category  VARCHAR2(150),
                            attribute1  VARCHAR2(240),
                            attribute2  VARCHAR2(240),
                            attribute3  VARCHAR2(240),
                            attribute4  VARCHAR2(240),
                            attribute5  VARCHAR2(240);

Register Table:
Declare
v_appl_short_name   VARCHAR2 (40) := 'XXCUST';
   v_tab_name          VARCHAR2 (32) := 'XX_MO_LINES'; 
   v_tab_type          VARCHAR2 (50) := 'T';
   v_next_extent       NUMBER        := 512;
   v_pct_free          NUMBER;
   v_pct_used          NUMBER;
BEGIN
  -- — Unregister the custom table if it exists
   ad_dd.delete_table (p_appl_short_name             => 'XXCUST', p_tab_name => v_tab_name);
   --— Register the custom tabl
   FOR tab_details IN (SELECT table_name, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent
                         FROM dba_tables
                        WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_table (p_appl_short_name             => v_appl_short_name,
                            p_tab_name                    => tab_details.table_name,
                            p_tab_type                    => v_tab_type,
                            p_next_extent                 => NVL (tab_details.next_extent, 512),
                            p_pct_free                    => NVL (tab_details.pct_free, 10),
                            p_pct_used                    => NVL (tab_details.pct_used, 70)
                           );
   END LOOP;
   --— Register the columns of custom table
   FOR all_tab_cols IN (SELECT column_name, column_id, data_type, data_length, nullable
                          FROM all_tab_columns
                         WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_column (p_appl_short_name             => v_appl_short_name,
                             p_tab_name                    => v_tab_name,
                             p_col_name                    => all_tab_cols.column_name,
                             p_col_seq                     => all_tab_cols.column_id,
                             p_col_type                    => all_tab_cols.data_type,
                             p_col_width                   => all_tab_cols.data_length,
                             p_nullable                    => all_tab_cols.nullable,
                             p_translate                   => 'N',
                             p_precision                   => NULL,
                             p_scale                       => NULL
                            );
   END LOOP;
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = v_tab_name)
   LOOP
      ad_dd.register_primary_key (p_appl_short_name             => v_appl_short_name,
                                  p_key_name                    => all_keys.constraint_name,
                                  p_tab_name                    => all_keys.table_name,
                                  p_description                 => 'Register primary key',
                                  p_key_type                    => 'S',
                                  p_audit_flag                  => 'N',
                                  p_enabled_flag                => 'Y'
                                 );
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name AND
                           constraint_name = all_keys.constraint_name)
      LOOP
         ad_dd.register_primary_key_column (p_appl_short_name             => v_appl_short_name,
                                            p_key_name                    => all_keys.constraint_name,
                                            p_tab_name                    => all_keys.table_name,
                                            p_col_name                    => all_columns.column_name,
                                            p_col_sequence                => all_columns.POSITION
                                           );
      END LOOP;
   END LOOP;
   COMMIT;
END;

Step 3: Register the DFF in Oracle Apps
Responsibility: Application Developer
Navigation: Flexfield > Descriptive > Register

Give Name, Title, Description and TableName all as same.
Structure Column : ATTRIBUTE_CATEGORY

4. 

Go to Segments to create new DFF fields.

5.

In Custom Form, create one non-database field and name eg: DFF
Subclass Information: TEXT_ITEM_DESC_FLEX
Required: No
Canvas: <Set the name of the Canvas manually since the item was created manually>
Database Item: No
Insert Allowed: Yes
Update Allowed: Yes
List of Values: ENABLE_LIST_LAMP
Validate From List: No

6. create procedure under the Program Units

PROCEDURE XX_MO_LINES_V_DFF_P (event VARCHAR2)

IS

BEGIN

   IF (event = 'WHEN-NEW-FORM-INSTANCE')

   THEN

      fnd_descr_flex.define (BLOCK                         => 'SFF_MO_LINES_V',-- — BLOCK

                             FIELD                         => 'DFF', --NON DB FIELD

                             appl_short_name               => 'XXCUST',

                             desc_flex_name                => 'SFF_MO_LINES' --DFF FLEX NAME

                            );

   ELSE

      NULL;

   END IF;

END;

Triggers Form Level:

Open the WHEN-NEW-FORM-INSTANCE trigger.

XX_MO_LINES_V_DFF_P('WHEN-NEW-FORM-INSTANCE');

Note:

Oracle apps provides the API named, FND_FLEX, for Descriptive flexfield events. You can to write all the block level triggers to have consistent normal behaviour of the descriptive flexfield.

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM ‘);

PRE-QUERY: FND_FLEX.EVENT(‘PRE-QUERY’);

POST-QUERY: FND_FLEX.EVENT(‘POST-QUERY’);

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-RECORD’);

WHEN-NEW-ITEM-INSTANCE: FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’);

PRE-INSERT: FND_FLEX.EVENT(‘PRE-INSERT’);

PRE-UPDATE: FND_FLEX.EVENT(‘PRE-UPDATE’);