VIEW: APPS.AMW_EXPORT_RCM_V

Source

View Text - Preformatted

SELECT CTX ,PPID ,PPN ,PDN ,pcde ,PID ,PON ,POD ,RNM ,RID ,RR ,RDS ,RT1 ,RT2 ,RT3 ,RT4 ,RT5 ,RT6 ,RT7 ,RT8 ,RT9 ,RT10 ,RLKL ,RIMP ,RMAT ,RMVA ,RAPP ,RCLASS ,CNM ,RC ,CDS ,APN ,ADE ,AOE ,CAPP ,CLOC ,CTYP ,CAUT ,CAPL ,CSRC ,CPEV ,CBGR ,CJOB ,CPRV ,CDET ,CDIS ,CKEY ,CVSO ,CVNM ,CVIN ,CCLASS ,CCM1 ,CCM2 ,CCM3 ,CCM4 ,CCM5 ,CCM6 ,CCM7 ,CCM8 ,CCM9 ,CCM10 ,CCT1 ,CCT2 ,CCT3 ,CCT4 ,CCT5 ,CCT6 ,CCT7 ,CCT8 ,CCT9 ,CCT10 ,CAS1 ,CAS2 ,CAS3 ,CAS4 ,CAS5 ,CAS6 ,CAS7 ,CAS8 ,CAS9 ,CAS10 ,-100 party_id ,UOM_CODE FROM amw_basic_rcm_v UNION ALL select CTX ,PPID ,PPN ,PDN ,pcde ,PID ,PON ,POD ,RNM ,RID ,RR ,RDS ,RT1 ,RT2 ,RT3 ,RT4 ,RT5 ,RT6 ,RT7 ,RT8 ,RT9 ,RT10 ,RLKL ,RIMP ,RMAT ,RMVA ,RAPP ,RCLASS ,CNM ,RC ,CDS ,APN ,ADE ,AOE ,CAPP ,CLOC ,CTYP ,CAUT ,CAPL ,CSRC ,CPEV ,CBGR ,CJOB ,CPRV ,CDET ,CDIS ,CKEY ,CVSO ,CVNM ,CVIN ,CCLASS ,CCM1 ,CCM2 ,CCM3 ,CCM4 ,CCM5 ,CCM6 ,CCM7 ,CCM8 ,CCM9 ,CCM10 ,CCT1 ,CCT2 ,CCT3 ,CCT4 ,CCT5 ,CCT6 ,CCT7 ,CCT8 ,CCT9 ,CCT10 ,CAS1 ,CAS2 ,CAS3 ,CAS4 ,CAS5 ,CAS6 ,CAS7 ,CAS8 ,CAS9 ,CAS10 ,aorv.party_id as party_id ,UOM_CODE FROM amw_basic_rcm_v ,/*amw_owner_roles_v*/ /*performance tuning: instead of joing to amw_owner_roles_v, using below subquery instead*/ (select fm.menu_name as role_name ,TO_NUMBER(REPLACE(fg.grantee_key,'HZ_PARTY:','')) party_id ,fg.grantee_key ,fg.instance_pk1_value as pk1_value,fg.instance_pk2_value,fg.instance_pk3_value ,fg.instance_pk4_value,fg.instance_pk5_value from fnd_grants fg, fnd_menus fm where fg.grantee_type='USER' and fg.instance_type='INSTANCE' and fg.menu_id=fm.menu_id and fg.grantee_key like 'HZ_PARTY%' and fm.menu_name='AMW_RL_PROC_OWNER_ROLE' and nvl(fg.end_date,sysdate+1) >= trunc(sysdate)) aorv WHERE pid=aorv.pk1_value

View Text - HTML Formatted

SELECT CTX
, PPID
, PPN
, PDN
, PCDE
, PID
, PON
, POD
, RNM
, RID
, RR
, RDS
, RT1
, RT2
, RT3
, RT4
, RT5
, RT6
, RT7
, RT8
, RT9
, RT10
, RLKL
, RIMP
, RMAT
, RMVA
, RAPP
, RCLASS
, CNM
, RC
, CDS
, APN
, ADE
, AOE
, CAPP
, CLOC
, CTYP
, CAUT
, CAPL
, CSRC
, CPEV
, CBGR
, CJOB
, CPRV
, CDET
, CDIS
, CKEY
, CVSO
, CVNM
, CVIN
, CCLASS
, CCM1
, CCM2
, CCM3
, CCM4
, CCM5
, CCM6
, CCM7
, CCM8
, CCM9
, CCM10
, CCT1
, CCT2
, CCT3
, CCT4
, CCT5
, CCT6
, CCT7
, CCT8
, CCT9
, CCT10
, CAS1
, CAS2
, CAS3
, CAS4
, CAS5
, CAS6
, CAS7
, CAS8
, CAS9
, CAS10
, -100 PARTY_ID
, UOM_CODE
FROM AMW_BASIC_RCM_V UNION ALL SELECT CTX
, PPID
, PPN
, PDN
, PCDE
, PID
, PON
, POD
, RNM
, RID
, RR
, RDS
, RT1
, RT2
, RT3
, RT4
, RT5
, RT6
, RT7
, RT8
, RT9
, RT10
, RLKL
, RIMP
, RMAT
, RMVA
, RAPP
, RCLASS
, CNM
, RC
, CDS
, APN
, ADE
, AOE
, CAPP
, CLOC
, CTYP
, CAUT
, CAPL
, CSRC
, CPEV
, CBGR
, CJOB
, CPRV
, CDET
, CDIS
, CKEY
, CVSO
, CVNM
, CVIN
, CCLASS
, CCM1
, CCM2
, CCM3
, CCM4
, CCM5
, CCM6
, CCM7
, CCM8
, CCM9
, CCM10
, CCT1
, CCT2
, CCT3
, CCT4
, CCT5
, CCT6
, CCT7
, CCT8
, CCT9
, CCT10
, CAS1
, CAS2
, CAS3
, CAS4
, CAS5
, CAS6
, CAS7
, CAS8
, CAS9
, CAS10
, AORV.PARTY_ID AS PARTY_ID
, UOM_CODE
FROM AMW_BASIC_RCM_V
, /*AMW_OWNER_ROLES_V*/ /*PERFORMANCE TUNING: INSTEAD OF JOING TO AMW_OWNER_ROLES_V
, USING BELOW SUBQUERY INSTEAD*/ (SELECT FM.MENU_NAME AS ROLE_NAME
, TO_NUMBER(REPLACE(FG.GRANTEE_KEY
, 'HZ_PARTY:'
, '')) PARTY_ID
, FG.GRANTEE_KEY
, FG.INSTANCE_PK1_VALUE AS PK1_VALUE
, FG.INSTANCE_PK2_VALUE
, FG.INSTANCE_PK3_VALUE
, FG.INSTANCE_PK4_VALUE
, FG.INSTANCE_PK5_VALUE
FROM FND_GRANTS FG
, FND_MENUS FM
WHERE FG.GRANTEE_TYPE='USER'
AND FG.INSTANCE_TYPE='INSTANCE'
AND FG.MENU_ID=FM.MENU_ID
AND FG.GRANTEE_KEY LIKE 'HZ_PARTY%'
AND FM.MENU_NAME='AMW_RL_PROC_OWNER_ROLE'
AND NVL(FG.END_DATE
, SYSDATE+1) >= TRUNC(SYSDATE)) AORV
WHERE PID=AORV.PK1_VALUE