Oracle Forms Tips

===============================================================

To Make record status - "QUERY" if it somehow changing in "INSERT" or "CHANGED"

Write the below code in POST QUERY trigger after all the processing.

app_record.set_status('QUERY');

===============================================================

Query to know path of log file and out file of a concurrent program

=============================
SELECT logfile_name, outfile_name
FROM fnd_concurrent_requests
WHERE request_id = 449842
=============================

Query to know all the menus/ Responsibilities name where one given form is refered

Query to know all the menus/ Responsibilities name where one given form is refered
============================================================
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN ( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define'))
UNION
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT MENU_ID
FROM fnd_menu_entries_vl
WHERE SUB_MENU_ID IN(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN
( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define')));
==============================================================

Creation of Dependable value set

:$FLEX$.XX_ORG_NAME

useful queries for the same this type of value set-
(SELECT DISTINCT forecast_designator,ORGANIZATION_ID,forecast_set,ROW_NUMBER () OVER (PARTITION BY forecast_designator ORDER BY forecast_designator) rn FROM mrp_forecast_designators_v)
WHERE organization_id = NVL(:$FLEX$.XX_ORG_NAME, organization_id) AND forecast_set = 'KANBAN' AND rn =1

FNDLOAD for Request set

===========================================================
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct GEN_ASCP_MSCPDX_DEF.ldt REQ_SET REQUEST_SET_NAME="GEN_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="XX_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_DEF.ldt
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt
============================================

PLSQL Block to attach concurrent program to request group

Run anonyms plsql block to attach this concurrent program to request group.
BEGIN
fnd_program.add_to_group ('XX_PO_NOTIFY_RECEIVE','PO','All Reports','PO');
END;

FNDLOAD for Concurrent Program

===================================================
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"

---------------------------------------------------------------------

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"

FNDLOAD for Alert

=========================================================
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt ALR_ALERTS APPLICATION_SHORT_NAME='PO' ALERT_NAME=' Email to requestes for PO Receipt'
-----------------------------------------------------------

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt

Query to get Application Short Name, Base Path, Application Id If you know Application Name

SELECT fa.application_short_name, fat.application_id, fa.basepath,
fa.product_code
FROM fnd_application_tl fat, fnd_application fa
WHERE fat.application_id = fa.application_id
AND fat.application_name = 'Purchasing'

Query to get the request group of a concurrent program

======================================================

SELECT REQUEST_GROUP_NAME
FROM FND_REQUEST_GROUPS where REQUEST_GROUP_ID in
(SELECT REQUEST_GROUP_ID
FROM FND_REQUEST_GROUP_UNITS WHERE (request_unit_id=820) )
----------------------------------------------------------
SELECT CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE queue_control_flag = 'N'
and USER_CONCURRENT_PROGRAM_NAME = 'TK Electronic Customs Interface - Set'
order by application_id, user_concurrent_program_name

/*if you do not find this request group directly attached to in any of the responsibilkity then go to the function and query in parameter field
with this request group , then you will get the function name - get the function id ,
--through this function id you will get to know in which menu this is attached (through below query ),
through menu name you will get to know the tresponsibility name.*/

SELECT entry_sequence, prompt, description, grant_flag, menu_id,
sub_menu_id, function_id, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, row_id
FROM fnd_menu_entries_vl
WHERE function_id = 26045

SELECT menu_name, user_menu_name, TYPE, description, menu_id,
last_update_date, last_updated_by, last_update_login, creation_date,
created_by, row_id
FROM fnd_menus_vl
where menu_id = 79018
---------------------------------
Request set
SELECT request_set_id, user_request_set_name, request_set_name, description,
start_date_active, end_date_active, print_together_flag,
allow_constraints_flag,application_id,
concurrent_program_id, owner, created_by, creation_date,
last_update_date, last_updated_by, last_update_login, icon_name,
printer, start_stage
FROM fnd_request_sets_vl
WHERE ('SYS' = 'SYS' OR owner = 1304)
AND (user_request_set_name = 'TK Electronic Customs Interface - Set')
ORDER BY application_id, request_set_name

========================================================