I believe I encounter a bug in rdbms 12.1.0.2 which it is serious matter.
SO RESULTS ARE DIFFERENT IN DIFFERENT VERSION OF DATABASE. HOW COME???????????????
-------------------------------------- Begin bldcase.sql --------------------------------------------------
create table FND_REQUEST_SETS_T
( USER_REQUEST_SET_NAME varchar(240),
REQUEST_SET_ID number,
APPLICATION_ID number );
insert into fnd_request_sets_t values ('Unifi ASN Process Set','744','20040');
insert into fnd_request_sets_t values ('Unifi Intercompany AP Invoices','784','20032');
insert into fnd_request_sets_t values ('Unifi Intercompany AR Invoices','794','20032');
insert into fnd_request_sets_t values ('Unifi Interorg Sales Exception','1342','20032');
insert into fnd_request_sets_t values ('Unifi Min Max Planning Report ','1511','20032');
insert into fnd_request_sets_t values ('Unifi Month End InterOrg Sales','1343','20032');
insert into fnd_request_sets_t values ('Unifi: Create Releases for DOM','787','20040');
insert into fnd_request_sets_t values ('Unifi: DOMS Inventory Reconcil','740','20032');
insert into fnd_request_sets_t values ('Unifi: DOMS Inventory Transact','741','20040');
create table fnd_request_group_units_t
( UNIT_APPLICATION_ID number,
REQUEST_UNIT_ID number,
REQUEST_UNIT_TYPE varchar2(1)
);
insert into fnd_request_group_units_t values ('20040','20040','A');
insert into fnd_request_group_units_t values ('20032','20032','A');
insert into fnd_request_group_units_t values ('20032','740','S');
commit;
--------------------------------------------- end bldcase.sql ------------------------------------------------
--------------------------------------- begin funny.sql -------------------------------------------------
SELECT
substr(s.user_request_set_name,1,50) set_name,
s.request_set_id,
s.application_id
FROM fnd_request_sets_t s
WHERE (
(s.application_id IN
(SELECT u.unit_application_id
FROM fnd_request_group_units_t u
WHERE u.request_unit_type = 'A'))
OR
(s.application_id, s.request_set_id) IN
(SELECT u.unit_application_id, u.request_unit_id
FROM fnd_request_group_units_t u
WHERE u.request_unit_type = 'S')
)
ORDER BY s.user_request_set_name
/
---------------------------------------- end funny.sql -----------------------------------------------------
By the way, the code is my debug drill down to the core problem from Oracle EBS Standard Forms which mean I cannot changed the code.
FNDRSRUN.fmb
in SETLOVDEF record group.