Fast formula queries for functions, contexts, db is and packages

Fast formula queries for functions, contexts, db is and packages

• 1.
  Fast Formula Queriesfor Functions, Contexts, DBIs and Packages Beloware the SQL's to extractfast formularelateddatafromunderlyingtables: Fast Formula Functionsand theirunderlyingPackages: selectf.BASE_FUNCTION_NAME,f.description, f.definition||'('|| listagg(fp.name||''||decode(fp.class,'I','IN','O','OUT')||' '||decode(fp.data_type,'N','NUMBER','T','VARCHAR2','D','DATE'),',') WITHIN GROUP(ORDER BY fp.sequence_number) ||') RETURN '||decode(f.data_type,'N','NUMBER','T','VARCHAR2','D','DATE') ASfunctionCall fromff_function_parametersfp , FF_FUNCTIONS_VLf where f.function_id=fp.function_id andupper(f.description) notlike '%DEPRECATED%' groupby f.BASE_FUNCTION_NAME,f.description,f.definition,f.data_type Fast Formula'sList with FF Code: select TO_CHAR("FF_FORMULAS_VL"."EFFECTIVE_START_DATE",'DD-MON-YYYY') as "EFFECTIVE_START_DATE", TO_CHAR("FF_FORMULAS_VL"."EFFECTIVE_END_DATE",'DD-MON-YYYY') as"EFFECTIVE_END_DATE", "FF_FORMULAS_VL"."BASE_FORMULA_NAME"as"BASE_FORMULA_NAME", "FF_FORMULAS_VL"."FORMULA_NAME"as "FORMULA_NAME", "FF_FORMULAS_VL"."DESCRIPTION"as"DESCRIPTION", "FF_FORMULAS_VL"."EDIT_STATUS"as "EDIT_STATUS", "FF_FORMULAS_VL"."FORMULA_TEXT"as "FORMULA_TEXT", "FF_FORMULAS_VL"."COMPILE_FLAG"as"COMPILE_FLAG", "FF_FORMULAS_VL"."LEGISLATION_CODE"as"LEGISLATION_CODE", TO_CHAR("FF_FORMULAS_VL"."LAST_UPDATE_DATE",'DD-MON-YYYY') as"LAST_UPDATE_DATE", "FF_FORMULAS_VL"."LAST_UPDATED_BY"as "LAST_UPDATED_BY", "FF_FORMULAS_VL"."CREATED_BY"as "CREATED_BY", TO_CHAR("FF_FORMULAS_VL"."CREATION_DATE",'DD-MON-YYYY') as"CREATION_DATE", "FF_FORMULA_TYPES_TL"."FORMULA_TYPE_NAME" as "FORMULA_TYPE_NAME" from "FUSION"."FF_FORMULA_TYPES_TL""FF_FORMULA_TYPES_TL", "FUSION"."FF_FORMULAS_VL""FF_FORMULAS_VL" where "FF_FORMULAS_VL"."FORMULA_TYPE_ID"="FF_FORMULA_TYPES_TL"."FORMULA_TYPE_ID" Extracting DBIs from a POD: selectfat.module_name,fdg.base_group_name,fdg.group_name,fdi.base_user_name,fdi.user_name, fdi.description,fdi.data_type,fdi.definition_text, fue.base_user_entity_name,fue.descriptionfue_des,fr.base_route_name,fr.multi_row_flag, (selectsubstr(sys.stragg(','||base_context_name),2) context from fusion.ff_route_context_usagesi,fusion.ff_contexts_vl j where i.context_id=j.context_id
• 2.
  and i.route_id=fr.route_id) contexts_used, (selectsubstr(sys.stragg(','||parameter_name),2)context fromfusion.ff_route_parameters where route_id=fr.route_id) parameters, (selectsubstr(sys.stragg(','||base_context_name),2) context from fusion.ff_dbi_groups_vl a,fusion.ff_dbi_usagesb,fusion.ff_database_items_vlc, fusion.ff_contexts_vld where a.context_id=d.context_id and a.dbi_group_id= b.dbi_group_id and b.dbi_id= c.database_item_id and c.user_entity_id=fue.user_entity_id) contexts_set from fusion.ff_database_items_vl fdi,fusion.ff_dbi_usagesfdu,fusion.ff_dbi_groups_vl fdg, fusion.fnd_appl_taxonomy_vlfat, fusion.ff_user_entities_vlfue,fusion.ff_routes_vl fr where fdi.module_idisnotnull and fdi.database_item_id=fdu.dbi_id(+) and fdu.dbi_group_id=fdg.dbi_group_id(+) and fdi.module_id =fat.module_id and fdi.user_entity_id=fue.user_entity_id andfue.route_id=fr.route_id and fdi.module_idisnotnull orderby module_name,fdi.base_user_name Fast Formula Contextsby Formula Type: selectt.base_formula_type_name , ttl.formula_type_name , ttl.description , c.base_context_name from ff_formula_types_bt , ff_formula_types_tl ttl , ff_ftype_context_usagesu , ff_contexts_bc where t.formula_type_id=u.formula_type_id and ttl.formula_type_id=t.formula_type_id and ttl.language =userenv('LANG') /*and ttl.formula_type_name like 'Oracle%Payroll%'*/ and c.context_id=u.context_id Hope these querieshelp. Stay tunedformore updates.