VIEW: APPS.AMW_EXPORT_KEY_ACC_V

Source

View Text - Preformatted

SELECT distinct 'PROC_HIER' as ACC_CTX, APHD.PROCESS_ID as ACC_PPID, APV.display_name as PDN, APV.PROCESS_CODE as PCD, AFKAT.name as ANM, AFKAB.natural_account_value as natural_account_value, AFKAB.NATURAL_ACCOUNT_ID as natural_account_id, -100 as party_id, (select natural_account_value||'-'||account_group_id||'-'||natural_account_id||decode(parent_natural_account_id,null,null,'-'||parent_natural_account_id) from amw_fin_key_accounts_vl where natural_account_id=afkab.natural_account_id and natural_account_value=afkab.natural_account_value and end_date is null and rownum=1) as naid FROM AMW_PROCESS_VL APV ,AMW_ACCT_ASSOCIATIONS AAA ,AMW_FIN_KEY_ACCOUNTS_B AFKAB ,AMW_FIN_KEY_ACCOUNTS_TL AFKAT ,AMW_PROC_HIERARCHY_DENORM APHD WHERE AAA.OBJECT_TYPE='PROCESS' AND AAA.PK1=APV.process_id AND APV.end_date IS NULL AND AAA.natural_account_id=AFKAB.natural_account_id AND AFKAB.end_date IS NULL AND AFKAB.parent_natural_account_id IS NULL AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID and afkat.language=userenv('lang') AND APHD.PARENT_CHILD_ID=APV.PROCESS_ID AND APHD.PROCESS_ID=-1 and APHD.UP_DOWN_IND='D' and aaa.deletion_date is null and aphd.hierarchy_type='L' union all SELECT distinct 'PROC_HIER' ACC_CTX ,APHD.PROCESS_ID ACC_PPID ,APV.display_name PDN ,APV.PROCESS_CODE PCD ,AFKAT.name ANM ,AFKAB.natural_account_value ,AFKAB.NATURAL_ACCOUNT_ID ,aorv.party_id as party_id ,(select natural_account_value||'-'||account_group_id||'-'||natural_account_id||decode(parent_natural_account_id,null,null,'-'||parent_natural_account_id) from amw_fin_key_accounts_vl where natural_account_id=afkab.natural_account_id and natural_account_value=afkab.natural_account_value and end_date is null and rownum=1) naid FROM AMW_PROCESS_VL APV ,AMW_ACCT_ASSOCIATIONS AAA ,AMW_FIN_KEY_ACCOUNTS_B AFKAB ,AMW_FIN_KEY_ACCOUNTS_TL AFKAT ,AMW_PROC_HIERARCHY_DENORM APHD ,/*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 AAA.OBJECT_TYPE='PROCESS' AND AAA.PK1=APV.process_id AND APV.end_date IS NULL AND AAA.natural_account_id=AFKAB.natural_account_id AND AFKAB.end_date IS NULL AND AFKAB.parent_natural_account_id IS NULL AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID and afkat.language=userenv('lang') AND APHD.PARENT_CHILD_ID=APV.PROCESS_ID AND APHD.PROCESS_ID=-1 and APHD.UP_DOWN_IND='D' /*and aorv.role_name='AMW_RL_PROC_OWNER_ROLE'*/ and apv.process_id=aorv.pk1_value and aaa.deletion_date is null and aphd.hierarchy_type='L' UNION ALL SELECT distinct 'PROC_BASIC' ACC_CTX ,aphd.process_id ACC_ppid ,APV.display_name PDN ,APV.PROCESS_CODE PCD ,AFKAT.name ANM ,AFKAB.natural_account_value ,AFKAB.NATURAL_ACCOUNT_ID ,-100 as party_id ,(select natural_account_value||'-'||account_group_id||'-'||natural_account_id||decode(parent_natural_account_id,null,null,'-'||parent_natural_account_id) from amw_fin_key_accounts_vl where natural_account_id=afkab.natural_account_id and natural_account_value=afkab.natural_account_value and end_date is null and rownum=1) naid FROM AMW_PROCESS_VL APV ,AMW_ACCT_ASSOCIATIONS AAA ,AMW_FIN_KEY_ACCOUNTS_B AFKAB ,AMW_FIN_KEY_ACCOUNTS_TL AFKAT ,(select process_id as process_id, process_id as parent_child_id from amw_process where end_date is null union select process_id, parent_child_id from AMW_PROC_HIERARCHY_DENORM where process_id <> -1 and up_down_ind='D' and hierarchy_type='L') APHD WHERE AAA.OBJECT_TYPE='PROCESS' AND AAA.PK1=APV.process_id AND APV.end_date IS NULL AND AAA.natural_account_id=AFKAB.natural_account_id AND AFKAB.end_date IS NULL AND AFKAB.parent_natural_account_id IS NULL AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID and afkat.language=userenv('lang') and aaa.deletion_date is null and aphd.parent_child_id = apv.process_id UNION ALL SELECT distinct 'PROC_BASIC' ACC_CTX ,/*apV.process_id*/aphd.process_id aCC_ppid ,APV.display_name PDN ,APV.PROCESS_CODE PCD ,AFKAT.name ANM ,AFKAB.natural_account_value ,AFKAB.NATURAL_ACCOUNT_ID ,aorv.party_id as party_id ,(select natural_account_value||'-'||account_group_id||'-'||natural_account_id||decode(parent_natural_account_id,null,null,'-'||parent_natural_account_id) from amw_fin_key_accounts_vl where natural_account_id=afkab.natural_account_id and natural_account_value=afkab.natural_account_value and end_date is null and rownum=1) naid FROM AMW_PROCESS_VL APV ,AMW_ACCT_ASSOCIATIONS AAA ,AMW_FIN_KEY_ACCOUNTS_B AFKAB ,AMW_FIN_KEY_ACCOUNTS_TL AFKAT ,(select process_id as process_id, process_id as parent_child_id from amw_process where end_date is null union select process_id, parent_child_id from AMW_PROC_HIERARCHY_DENORM where process_id <> -1 and up_down_ind='D' and hierarchy_type='L') APHD ,/*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 AAA.OBJECT_TYPE='PROCESS' AND AAA.PK1=APV.process_id AND APV.end_date IS NULL AND AAA.natural_account_id=AFKAB.natural_account_id and afkat.language=userenv('lang') AND AFKAB.end_date IS NULL AND AFKAB.parent_natural_account_id IS NULL AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID /*and aorv.role_name='AMW_RL_PROC_OWNER_ROLE'*/ and apv.process_id=aorv.pk1_value and aaa.deletion_date is null and aphd.parent_child_id = apv.process_id

View Text - HTML Formatted

SELECT DISTINCT 'PROC_HIER' AS ACC_CTX
, APHD.PROCESS_ID AS ACC_PPID
, APV.DISPLAY_NAME AS PDN
, APV.PROCESS_CODE AS PCD
, AFKAT.NAME AS ANM
, AFKAB.NATURAL_ACCOUNT_VALUE AS NATURAL_ACCOUNT_VALUE
, AFKAB.NATURAL_ACCOUNT_ID AS NATURAL_ACCOUNT_ID
, -100 AS PARTY_ID
, (SELECT NATURAL_ACCOUNT_VALUE||'-'||ACCOUNT_GROUP_ID||'-'||NATURAL_ACCOUNT_ID||DECODE(PARENT_NATURAL_ACCOUNT_ID
, NULL
, NULL
, '-'||PARENT_NATURAL_ACCOUNT_ID)
FROM AMW_FIN_KEY_ACCOUNTS_VL
WHERE NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND NATURAL_ACCOUNT_VALUE=AFKAB.NATURAL_ACCOUNT_VALUE
AND END_DATE IS NULL
AND ROWNUM=1) AS NAID
FROM AMW_PROCESS_VL APV
, AMW_ACCT_ASSOCIATIONS AAA
, AMW_FIN_KEY_ACCOUNTS_B AFKAB
, AMW_FIN_KEY_ACCOUNTS_TL AFKAT
, AMW_PROC_HIERARCHY_DENORM APHD
WHERE AAA.OBJECT_TYPE='PROCESS'
AND AAA.PK1=APV.PROCESS_ID
AND APV.END_DATE IS NULL
AND AAA.NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND AFKAB.END_DATE IS NULL
AND AFKAB.PARENT_NATURAL_ACCOUNT_ID IS NULL
AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID
AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID
AND AFKAT.LANGUAGE=USERENV('LANG')
AND APHD.PARENT_CHILD_ID=APV.PROCESS_ID
AND APHD.PROCESS_ID=-1
AND APHD.UP_DOWN_IND='D'
AND AAA.DELETION_DATE IS NULL
AND APHD.HIERARCHY_TYPE='L' UNION ALL SELECT DISTINCT 'PROC_HIER' ACC_CTX
, APHD.PROCESS_ID ACC_PPID
, APV.DISPLAY_NAME PDN
, APV.PROCESS_CODE PCD
, AFKAT.NAME ANM
, AFKAB.NATURAL_ACCOUNT_VALUE
, AFKAB.NATURAL_ACCOUNT_ID
, AORV.PARTY_ID AS PARTY_ID
, (SELECT NATURAL_ACCOUNT_VALUE||'-'||ACCOUNT_GROUP_ID||'-'||NATURAL_ACCOUNT_ID||DECODE(PARENT_NATURAL_ACCOUNT_ID
, NULL
, NULL
, '-'||PARENT_NATURAL_ACCOUNT_ID)
FROM AMW_FIN_KEY_ACCOUNTS_VL
WHERE NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND NATURAL_ACCOUNT_VALUE=AFKAB.NATURAL_ACCOUNT_VALUE
AND END_DATE IS NULL
AND ROWNUM=1) NAID
FROM AMW_PROCESS_VL APV
, AMW_ACCT_ASSOCIATIONS AAA
, AMW_FIN_KEY_ACCOUNTS_B AFKAB
, AMW_FIN_KEY_ACCOUNTS_TL AFKAT
, AMW_PROC_HIERARCHY_DENORM APHD
, /*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 AAA.OBJECT_TYPE='PROCESS'
AND AAA.PK1=APV.PROCESS_ID
AND APV.END_DATE IS NULL
AND AAA.NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND AFKAB.END_DATE IS NULL
AND AFKAB.PARENT_NATURAL_ACCOUNT_ID IS NULL
AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID
AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID
AND AFKAT.LANGUAGE=USERENV('LANG')
AND APHD.PARENT_CHILD_ID=APV.PROCESS_ID
AND APHD.PROCESS_ID=-1
AND APHD.UP_DOWN_IND='D' /*AND AORV.ROLE_NAME='AMW_RL_PROC_OWNER_ROLE'*/
AND APV.PROCESS_ID=AORV.PK1_VALUE
AND AAA.DELETION_DATE IS NULL
AND APHD.HIERARCHY_TYPE='L' UNION ALL SELECT DISTINCT 'PROC_BASIC' ACC_CTX
, APHD.PROCESS_ID ACC_PPID
, APV.DISPLAY_NAME PDN
, APV.PROCESS_CODE PCD
, AFKAT.NAME ANM
, AFKAB.NATURAL_ACCOUNT_VALUE
, AFKAB.NATURAL_ACCOUNT_ID
, -100 AS PARTY_ID
, (SELECT NATURAL_ACCOUNT_VALUE||'-'||ACCOUNT_GROUP_ID||'-'||NATURAL_ACCOUNT_ID||DECODE(PARENT_NATURAL_ACCOUNT_ID
, NULL
, NULL
, '-'||PARENT_NATURAL_ACCOUNT_ID)
FROM AMW_FIN_KEY_ACCOUNTS_VL
WHERE NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND NATURAL_ACCOUNT_VALUE=AFKAB.NATURAL_ACCOUNT_VALUE
AND END_DATE IS NULL
AND ROWNUM=1) NAID
FROM AMW_PROCESS_VL APV
, AMW_ACCT_ASSOCIATIONS AAA
, AMW_FIN_KEY_ACCOUNTS_B AFKAB
, AMW_FIN_KEY_ACCOUNTS_TL AFKAT
, (SELECT PROCESS_ID AS PROCESS_ID
, PROCESS_ID AS PARENT_CHILD_ID
FROM AMW_PROCESS
WHERE END_DATE IS NULL UNION SELECT PROCESS_ID
, PARENT_CHILD_ID
FROM AMW_PROC_HIERARCHY_DENORM
WHERE PROCESS_ID <> -1
AND UP_DOWN_IND='D'
AND HIERARCHY_TYPE='L') APHD
WHERE AAA.OBJECT_TYPE='PROCESS'
AND AAA.PK1=APV.PROCESS_ID
AND APV.END_DATE IS NULL
AND AAA.NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND AFKAB.END_DATE IS NULL
AND AFKAB.PARENT_NATURAL_ACCOUNT_ID IS NULL
AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID
AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID
AND AFKAT.LANGUAGE=USERENV('LANG')
AND AAA.DELETION_DATE IS NULL
AND APHD.PARENT_CHILD_ID = APV.PROCESS_ID UNION ALL SELECT DISTINCT 'PROC_BASIC' ACC_CTX
, /*APV.PROCESS_ID*/APHD.PROCESS_ID ACC_PPID
, APV.DISPLAY_NAME PDN
, APV.PROCESS_CODE PCD
, AFKAT.NAME ANM
, AFKAB.NATURAL_ACCOUNT_VALUE
, AFKAB.NATURAL_ACCOUNT_ID
, AORV.PARTY_ID AS PARTY_ID
, (SELECT NATURAL_ACCOUNT_VALUE||'-'||ACCOUNT_GROUP_ID||'-'||NATURAL_ACCOUNT_ID||DECODE(PARENT_NATURAL_ACCOUNT_ID
, NULL
, NULL
, '-'||PARENT_NATURAL_ACCOUNT_ID)
FROM AMW_FIN_KEY_ACCOUNTS_VL
WHERE NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND NATURAL_ACCOUNT_VALUE=AFKAB.NATURAL_ACCOUNT_VALUE
AND END_DATE IS NULL
AND ROWNUM=1) NAID
FROM AMW_PROCESS_VL APV
, AMW_ACCT_ASSOCIATIONS AAA
, AMW_FIN_KEY_ACCOUNTS_B AFKAB
, AMW_FIN_KEY_ACCOUNTS_TL AFKAT
, (SELECT PROCESS_ID AS PROCESS_ID
, PROCESS_ID AS PARENT_CHILD_ID
FROM AMW_PROCESS
WHERE END_DATE IS NULL UNION SELECT PROCESS_ID
, PARENT_CHILD_ID
FROM AMW_PROC_HIERARCHY_DENORM
WHERE PROCESS_ID <> -1
AND UP_DOWN_IND='D'
AND HIERARCHY_TYPE='L') APHD
, /*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 AAA.OBJECT_TYPE='PROCESS'
AND AAA.PK1=APV.PROCESS_ID
AND APV.END_DATE IS NULL
AND AAA.NATURAL_ACCOUNT_ID=AFKAB.NATURAL_ACCOUNT_ID
AND AFKAT.LANGUAGE=USERENV('LANG')
AND AFKAB.END_DATE IS NULL
AND AFKAB.PARENT_NATURAL_ACCOUNT_ID IS NULL
AND AFKAB.ACCOUNT_GROUP_ID=AFKAT.ACCOUNT_GROUP_ID
AND AFKAB.NATURAL_ACCOUNT_ID=AFKAT.NATURAL_ACCOUNT_ID /*AND AORV.ROLE_NAME='AMW_RL_PROC_OWNER_ROLE'*/
AND APV.PROCESS_ID=AORV.PK1_VALUE
AND AAA.DELETION_DATE IS NULL
AND APHD.PARENT_CHILD_ID = APV.PROCESS_ID