**********************
*Legal Entity Details*
**********************
SELECT
XEP.NAME,
XEP.LEGAL_ENTITY_IDENTIFIER,
XEP.PSU_FLAG,
XEP.LEGAL_EMPLOYER_FLAG,
XJV.REGISTRATION_CODE_LE REGISTRATION_CODE,
XR.REGISTRATION_NUMBER,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.POSTAL_CODE,
HL.STATE,
HL.PROVINCE,
HL.COUNTRY,
HL.COUNTY,
FTV.TERRITORY_SHORT_NAME,
FTV.DESCRIPTION
FROM
XLE_REGISTRATIONS XR,
XLE_JURISDICTIONS_VL XJV,
HZ_LOCATIONS HL,
FND_TERRITORIES_VL FTV,
XLE_ENTITY_PROFILES XEP
WHERE
XR.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND XR.SOURCE_ID = XEP.LEGAL_ENTITY_ID
AND XJV.JURISDICTION_ID = XR.JURISDICTION_ID
AND HL.LOCATION_ID = xR.LOCATION_ID
AND FTV.TERRITORY_CODE = HL.COUNTRY
AND SYSDATE BETWEEN NVL(XR.EFFECTIVE_FROM, SYSDATE -1) AND NVL(XR.EFFECTIVE_TO, SYSDATE +1)
ORDER BY XEP.LEGAL_ENTITY_IDENTIFIER
**************************************
*Legal Entity Details Without Address*
**************************************
SELECT
A.LEGAL_ENTITY_IDENTIFIER,
A.NAME,
A.LE_INFORMATION_CONTEXT,
A.PSU_FLAG,
A.LEGAL_EMPLOYER_FLAG,
B.SOURCE_TABLE,
B.REGISTRATION_NUMBER
FROM
XLE_ENTITY_PROFILES A,
XLE_REGISTRATIONS B
WHERE
B.SOURCE_TABLE IN ('XLE_ENTITY_PROFILES', 'XLE_ETB_PROFILES')
AND B.REGISTERED_NAME = A.NAME
**********************
*Ledger Options Setup*
**********************
SELECT
A.LEDGER_ID,
A.NAME,
A.LEDGER_CATEGORY_CODE,
B.ID_FLEX_STRUCTURE_CODE,
A.CURRENCY_CODE,
A.PERIOD_SET_NAME,
A.FIRST_LEDGER_PERIOD_NAME,
A.SUSPENSE_ALLOWED_FLAG,
A.ALLOW_INTERCOMPANY_POST_FLAG,
A.TRACK_ROUNDING_IMBALANCE_FLAG,
A.ENABLE_AVERAGE_BALANCES_FLAG,
A.ENABLE_BUDGETARY_CONTROL_FLAG,
A.REQUIRE_BUDGET_JOURNALS_FLAG,
A.ENABLE_JE_APPROVAL_FLAG,
A.ENABLE_AUTOMATIC_TAX_FLAG,
A.CONSOLIDATION_LEDGER_FLAG,
A.TRANSLATE_EOD_FLAG,
A.SLA_ACCOUNTING_METHOD_CODE,
A.SLA_DESCRIPTION_LANGUAGE,
C.CONCATENATED_SEGMENTS AS RET_EARNINGS_ACC,
D.CONCATENATED_SEGMENTS AS ROUND_ACC,
E.CONCATENATED_SEGMENTS AS SLACUR_BAL_SUS_ACC,
A.ENABLE_RECONCILIATION_FLAG,
A.CREATE_JE_FLAG,
A.SLA_LEDGER_CASH_BASIS_FLAG,
A.USSGL_OPTION_CODE,
A.VALIDATE_JOURNAL_REF_DATE,
A.JRNLS_GROUP_BY_DATE_FLAG,
A.REVAL_FROM_PRI_LGR_CURR,
A.AUTOREV_AFTER_OPEN_PRD_FLAG,
A.PRIOR_PRD_NOTIFICATION_FLAG,
A.POP_UP_STAT_ACCOUNT_FLAG,
A.THRESHOLD_AMOUNT,
A.SEQUENCING_MODE_CODE,
A.DOC_SEQUENCING_OPTION_CODE,
A.ENF_SEQ_DATE_CORRELATION_CODE,
A.AR_DOC_SEQUENCING_OPTION_FLAG,
A.AP_DOC_SEQUENCING_OPTION_FLAG,
A.NET_CLOSING_BAL_FLAG,
A.AUTOMATE_SEC_JRNL_REV_FLAG,
A.MINIMUM_THRESHOLD_AMOUNT,
A.STRICT_PERIOD_CLOSE_FLAG
FROM GL_LEDGERS A,
FND_ID_FLEX_STRUCTURES B,
GL_CODE_COMBINATIONS C,
GL_CODE_COMBINATIONS D,
GL_CODE_COMBINATIONS E
WHERE A.CHART_OF_ACCOUNTS_ID = B.ID_FLEX_NUM
AND A.RET_EARN_CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
AND A.ROUNDING_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND A.SLA_ENTERED_CUR_BAL_SUS_CCID = E.CODE_COMBINATION_ID
****************
*Table Set Data*
****************
SELECT
A.REFERENCE_GROUP_NAME,
A.DETERMINANT_TYPE,
C.NAME,
B.SET_CODE
FROM
FND_SETID_ASSIGNMENTS A,
FND_SETID_SETS_VL B,
HR_ALL_ORGANIZATION_UNITS_TL C
WHERE
A.REFERENCE_GROUP_NAME IN ('AR_REMIT_TO_ADDRESS','HZ_CUSTOMER_ACCOUNT_SITE','PER_DEPARTMENTS','PER_GRADES','ZX_TAX_CLASSIFICATION_CODES','PER_JOBS','FND_SETID_SETS_VL')
AND A.SET_ID = B.SET_ID
AND C.ORGANIZATION_ID = A.DETERMINANT_VALUE
AND C.LANGUAGE = 'US'
ORDER BY 3,1
*****************************
Business Unit Set Assignment
*****************************
Select A.SET_CODE,
C.BU_NAME,
b.REFERENCE_GROUP_NAME
from
FND_SETID_SETS A,
FND_SETID_ASSIGNMENTS B,
FUN_ALL_BUSINESS_UNITS_V C
where a.SET_ID = b.SET_ID
and b.DETERMINANT_VALUE = c.BU_ID
and c.BU_NAME in ('WSP UAE','WSP KSA','WSP QAT','WSP KWT','WSP OMN','WSP ZAF','WSP MOZ','WSP DRC','WSP GHA','WSP MRT','WSP IND')
and a.set_code <>'COMMON'
and a.LANGUAGE = 'US'
**********************
*Accounting Sequences*
**********************
SELECT
A.HEADER_NAME,
A.GAPLESS_FLAG,
A.OBSOLETE_FLAG,
A.DESCRIPTION,
B.VERSION_NAME,
B.HEADER_NAME,
B.INITIAL_VALUE,
B.START_DATE,
B.END_DATE,
B.CURRENT_VALUE,
B.USE_STATUS_CODE
FROM
FUN_SEQ_HEADERS A,
FUN_SEQ_VERSIONS B
WHERE
A.SEQ_HEADER_ID = B.SEQ_HEADER_ID
*********************************
*Accounting Sequences Assignment*
*********************************
select
C.NAME LEDGER_NAME,
D.NAME LEGAL_ENTITY_NAME,
A.TABLE_NAME,
B.CONTROL_ATTRIBUTE_STRUCTURE,
a.EVENT_CODE,
a.DATE_TYPE,
a.SORT_OPTION
a.REQUIRE_ASSIGN_FLAG,
e.HEADER_NAME,
b.START_DATE,
b.END_DATE,
b.JOURNAL_SOURCE,
b.JOURNAL_CATEGORY
From
FUN_SEQ_CONTEXTS A,
FUN_SEQ_ASSIGNMENTS B,
GL_LEDGERS C,
XLE_ENTITY_PROFILES D,
FUN_SEQ_HEADERS e
where
B.SEQ_CONTEXT_ID = A.SEQ_CONTEXT_ID
and c.LEDGER_ID = A.CONTEXT_VALUE
and A.CONTEXT_TYPE = 'LEDGER_AND_CURRENCY'
and a.LEGAL_ENTITY_ID = D.LEGAL_ENTITY_ID (+)
and e.SEQ_HEADER_ID = b.SEQ_HEADER_ID
*****************
Currency Codes
*****************
select
A.CURRENCY_CODE,
b.NAME,
a.precision,
a.enabled_flag
from FND_CURRENCIES A,
FND_CURRENCIES_TL B
where
a.CURRENCY_CODE = b.CURRENCY_CODE
*****************
Exchange Rates
*****************
select
A.FROM_CURRENCY,
A.TO_CURRENCY,
to_char(A.CONVERSION_DATE,'DD-MM-YYYY'),
B.User_CONVERSION_TYPE,
A.CONVERSION_RATE,
A.STATUS_CODE
from GL_DAILY_RATES A, GL_DAILY_CONVERSION_TYPES B
where A.FROM_CURRENCY =NVL(:CUR_FROM,A.FROM_CURRENCY )
and A.TO_CURRENCY = NVL(:CUR_TO,A.TO_CURRENCY )
and A.CONVERSION_TYPE = b.CONVERSION_TYPE
and A.CONVERSION_TYPE in (select c.CONVERSION_TYPE from GL_DAILY_CONVERSION_TYPES c where c.USER_CONVERSION_TYPE = NVL(:CUR_TYPE, c.USER_CONVERSION_TYPE ))
and A.CONVERSION_DATE between NVL(:Date_From,A.CONVERSION_DATE) and NVL(:Date_To, A.CONVERSION_DATE)
*********************
*Environment Details*
*********************
SELECT
EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
FROM
FUSION.ASK_DEPLOYED_DOMAINS
WHERE
1=1
AND DEPLOYED_DOMAIN_NAME = 'FADomain'
****************
*Segment Values*
****************
select a.VALUE_CATEGORY,
a.FLEX_VALUE,
b.DESCRIPTION,
to_char(a.START_DATE_ACTIVE,'mm/dd/yyyy') as Start_Date,
to_char(a.End_DATE_ACTIVE,'mm/dd/yyyy') as End_Date,
a.ENABLED_FLAG,
substr(a.COMPILED_VALUE_ATTRIBUTES,1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,1)) as Summary_flg,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,1)+1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,2)-INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,1)) as Allow_Posting_Flg,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,2)+1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,3)-INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,2)) as Allow_Budgeting_Flg,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,3)+1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,4)-INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,3)) as Account_Type_Flg,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,4)+1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,5)-INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,4)) as Third_Party_Control_Acc,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,5)+1,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,6)-INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,5)) as Reconcile_Flg,
substr(a.COMPILED_VALUE_ATTRIBUTES,INSTR(a.COMPILED_VALUE_ATTRIBUTES, chr(10),1,6)+1) as Financial_Category,
a.CREATED_BY,
a.LAST_UPDATED_BY
from
FND_FLEX_VALUES A,
FND_FLEX_VALUES_TL B
where VALUE_CATEGORY = 'Account XXXX'
and a.FLEX_VALUE_ID = b.FLEX_VALUE_ID
and b.LANGUAGE = 'US'
********************
*Segment Definition*
********************
select distinct segment_name from FND_ID_FLEX_SEGMENTS where ID_FLEX_NUM = 9079
*********************
*Cost Center Details*
*********************
SELECT
ORG_INFORMATION3 COMPANY,
ORG_INFORMATION1 COST_CENTER_NUMBER,
ORG_INFORMATION6 MANAGE_ID,
PERNAMES.FULL_NAME,
FND_DES.DESCRIPTION COST_CENTER_DESCRIPTION,
P.PERSON_NUMBER COST_CENTER_MGR_EMP_NO,
PEA.EMAIL_ADDRESS COST_CENTER_MGR_EMAIL,
FND_VAL.attribute1 Cost_Center_Global_Function,
job.name as cost_center_manager_job
FROM
HR_ORGANIZATION_INFORMATION_F ORGINFO,
PER_ALL_PEOPLE_F P,
PER_PERSON_NAMES_F PERNAMES,
FND_VS_VALUES_B FND_VAL,
FND_VS_VALUES_TL FND_DES,
PER_EMAIL_ADDRESSES PEA,
PER_JOBS_F_VL Job,
PER_ALL_ASSIGNMENTS_M PAAM
WHERE 1=1
AND ORGINFO.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
AND TRUNC(SYSDATE) BETWEEN ORGINFO.EFFECTIVE_START_DATE AND ORGINFO.EFFECTIVE_END_DATE
AND ORG_INFORMATION6= P.PERSON_ID
AND P.PERSON_ID = PERNAMES.PERSON_ID
AND PERNAMES.NAME_TYPE = 'GLOBAL'
AND ORGINFO.ORG_INFORMATION1 = FND_VAL.VALUE
AND FND_VAL.VALUE_SET_ID = '82243'
AND FND_DES.VALUE_ID = FND_VAL.VALUE_ID
AND FND_DES.LANGUAGE='US'
AND PEA.EMAIL_ADDRESS_ID = P.PRIMARY_EMAIL_ID
AND P.PERSON_ID = PEA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEA.DATE_FROM AND PEA.DATE_TO
and job.attribute_category = 'US'
and job.job_id = PAAM.job_id
and TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
and PAAM.assignment_type = 'E'
and p.person_id = paam.person_id
----------------------
Basic Employee Details
-----------------------
SELECT
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FULL_NAME EMPLOYEE_NAME,
HAOU.NAME LEGAL_EMPLOYER,
PAAM.ASSIGNMENT_NUMBER,
PAAM.PRIMARY_FLAG,
HL.LOCATION_NAME,
PD.ORGANIZATION_ID,
PD.NAME DEPT_NAME,
PD.ATTRIBUTE7 ORGANIZATION_TYPE,
PJ.NAME JOB_NAME,
POS.NAME POSITION_NAME ,
PG.NAME GRADE_NAME,
(SELECT NAME FROM PER_GRADE_LADDERS_F_TL WHERE GRADE_LADDER_ID = PAAM.GRADE_LADDER_PGM_ID AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
) GRADE_LADDER,
(SELECT PGST.NAME FROM PER_ASSIGN_GRADE_STEPS_F P1, PER_GRADE_STEPS_F_TL PGST WHERE P1.GRADE_STEP_ID = PGST.GRADE_STEP_ID AND P1.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID AND TRUNC(SYSDATE) BETWEEN PGST.EFFECTIVE_START_DATE AND PGST.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE AND P1.EFFECTIVE_END_DATE
) GRADE_STEP
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERIODS_OF_SERVICE PPS,
HR_ALL_ORGANIZATION_UNITS HAOU,
HR_ALL_ORGANIZATION_UNITS PD,
PER_JOBS PJ,
PER_GRADES PG,
HR_LOCATIONS HL,
HR_ALL_POSITIONS_F_TL POS
WHERE 1=1
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PD.ORGANIZATION_ID(+) = PAAM.ORGANIZATION_ID
AND PAAM.LEGAL_ENTITY_ID =HAOU.ORGANIZATION_ID(+)
AND PAAM.JOB_ID = PJ.JOB_ID(+)
AND PG.GRADE_ID(+)= PAAM.GRADE_ID
AND HL.LOCATION_ID(+)= PAAM.LOCATION_ID
AND POS.POSITION_ID(+) = PAAM.POSITION_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PG.EFFECTIVE_START_DATE(+) AND PG.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE(+) AND PJ.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE(+) AND POS.EFFECTIVE_END_DATE(+)
AND PAAM.ASSIGNMENT_TYPE IN ('E','C')
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PPNF.NAME_TYPE='GLOBAL'
ORDER BY TREE.DEPTH
**************************************
*RMCS Tables *
**************************************
*RMCS Contract Header *
**************************************
SELECT * FROM VRM_CUSTOMER_CONTRACT_HEADERS
SELECT * FROM VRM_ACCOUNTING_EVENTS
*****************************************
*RMCS Contract to Source Contract Tables*
*****************************************
SELECT * FROM VRM_SOURCE_DOCUMENTS
***************************************
*Enterprise Contract Management Tables*
***************************************
SELECT * FROM OKC_K_HEADERS_ALL_B WHERE CONTRACT_NUMBER = '4406057064'
**************************************
*RMCS to PPM Comparison SQL *
**************************************
SELECT
VCCH.CUSTOMER_CONTRACT_NUMBER AS RMCS_CONT_NUM,
TO_CHAR(VSD.DOCUMENT_ID),
VSD.DOCUMENT_NUMBER,
VCCH.CONTRACT_CURRENCY_CODE AS RMCS_CUR_CODE,
VCCH.CONTR_CUR_TRANSACTION_PRICE AS RMCS_TRANS_PRICE,
VCCH.CONTR_CUR_TOTAL_RECOG_REV_AMT AS RMCS_REV_REC_AMT,
VCCH.CONTR_CUR_TOTAL_BILLED_AMT AS RMCS_TOT_BILL_AMT,
TO_CHAR(VCCH.REFERENCE) RMCS_DOC_REF,
VCCH.LATEST_IMMATERIAL_CHANGE_CODE,
VSD.CURRENCY_CODE CONT_CUR_CODE,
PCIRF.PC_REVENUE AS REVENUE_RECOGNIZED,
PCIRF.PFC_REVENUE AS CONTRACT_PRICE,
PCIRF.PFC_BILLED AS BILLED_AMOUNT
FROM
VRM_CUSTOMER_CONTRACT_HEADERS VCCH,
VRM_SOURCE_DOCUMENTS VSD,
PJS_CN_INV_REV_F PCIRF,
OKC_K_HEADERS_ALL_B OKHAB,
FUN_ALL_BUSINESS_UNITS_V FABU
WHERE
TO_CHAR(VCCH.REFERENCE) = TO_CHAR(VSD.DOCUMENT_ID)
AND VSD.DOCUMENT_NUMBER = OKHAB.CONTRACT_NUMBER
AND OKHAB.ID = PCIRF.CONTRACT_ID
AND PCIRF.AGGR_LEVEL = 'C'
AND OKHAB.VERSION_TYPE = 'C'
AND VCCH.CONTRACT_CURRENCY_CODE = PCIRF.CURRENCY_CODE
AND FABU.BU_ID = PCIRF.BUSINESS_UNIT_ID
AND FABU.BU_NAME = :BUSINESS_UNIT
AND VSD.DOCUMENT_NUMBER = '4406057037'
SELECT
VSD.DOCUMENT_ID,
VPSE.*
FROM OKC_K_HEADERS_ALL_B OKHAB,
VRM_SOURCE_DOCUMENTS VSD,
VRM_SOURCE_DOC_LINES VSDL,
VRM_POL_SATISFACTION_EVENTS VPSE
WHERE
OKHAB.ID = VSD.DOC_ID_INT_1
AND OKHAB.VERSION_TYPE = 'C'
AND VSD.DOCUMENT_ID = VSDL.DOCUMENT_ID
AND CONTRACT_NUMBER = '4406057037'
AND VSDL.DOCUMENT_LINE_ID = VPSE.DOCUMENT_LINE_ID
SELECT * FROM VRM_REVENUE_DOCUMENTS_ALL
SELECT * FROM VRM_REVENUE_DOCUMENT_ERRORS
Look Up Tables
select LOOKUP_TYPE,
LOOKUP_CODE,
MEANING,
DESCRIPTION,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE in ('WSP_EXM_ GSS_ASNGTYPE','WSP_EXM_ GSS_AUDITBU')
and ENABLED_FLAG='Y'
SELECT
ORG_INFORMATION3 COMPANY,
ORG_INFORMATION1 COST_CENTER_NUMBER,
ORG_INFORMATION6 MANAGE_ID,
PERNAMES.FULL_NAME,
FND_DES.DESCRIPTION COST_CENTER_DESCRIPTION,
P.PERSON_NUMBER COST_CENTER_MGR_EMP_NO,
PEA.EMAIL_ADDRESS COST_CENTER_MGR_EMAIL,
FND_VAL.attribute1 Cost_Center_Global_Function,
job.name as cost_center_manager_job
FROM
HR_ORGANIZATION_INFORMATION_F ORGINFO,
PER_ALL_PEOPLE_F P,
PER_PERSON_NAMES_F PERNAMES,
FND_VS_VALUES_B FND_VAL,
FND_VS_VALUES_TL FND_DES,
PER_EMAIL_ADDRESSES PEA,
PER_JOBS_F_VL Job,
PER_ALL_ASSIGNMENTS_M PAAM
WHERE 1=1
AND ORGINFO.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
AND TRUNC(SYSDATE) BETWEEN ORGINFO.EFFECTIVE_START_DATE AND ORGINFO.EFFECTIVE_END_DATE
AND ORG_INFORMATION6= P.PERSON_ID
AND P.PERSON_ID = PERNAMES.PERSON_ID
AND PERNAMES.NAME_TYPE = 'GLOBAL'
AND ORGINFO.ORG_INFORMATION1 = FND_VAL.VALUE
AND FND_VAL.VALUE_SET_ID = '82243'
AND FND_DES.VALUE_ID = FND_VAL.VALUE_ID
AND FND_DES.LANGUAGE='US'
AND PEA.EMAIL_ADDRESS_ID = P.PRIMARY_EMAIL_ID
AND P.PERSON_ID = PEA.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEA.DATE_FROM AND PEA.DATE_TO
and job.attribute_category = 'US'
and job.job_id = PAAM.job_id
and TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
and PAAM.assignment_type = 'E'
and p.person_id = paam.person_id
-----------------------
Data Access for users
-----------------------
SELECT gl.name Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'DATA ACCESS SET' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.gl_access_sets gl,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE gl.ACCESS_SET_ID = role.ACCESS_SET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT bu.bu_name Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'BUSINESS UNIT' Security_Context
FROM fusion.FUN_ALL_BUSINESS_UNITS_V bu,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE role.org_id = bu.bu_id
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT led.NAME Security_Context_Value, pr.role_name Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'LEDGERS' Security_Context
FROM fusion.GL_LEDGERS led,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE role.LEDGER_ID = led.LEDGER_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT book.book_type_name Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'ASSET BOOK' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FA_BOOK_CONTROLS book,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE book.BOOK_CONTROL_ID = role.book_id
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT interco.INTERCO_ORG_NAME Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'INTERCOMPANY ORGANIZATION' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FUN_INTERCO_ORGANIZATIONS interco,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE interco.INTERCO_ORG_ID= role.INTERCO_ORG_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT cost.COST_ORG_NAME Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'COST ORGANIZATION' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.CST_COST_ORGS_V cost,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE cost.COST_ORG_ID= role.CST_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT mfg.DEF_SUPPLY_SUBINV Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'MANUFACTURING PLANT' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.RCS_MFG_PARAMETERS mfg,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE mfg.ORGANIZATION_ID= role.MFG_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT budget.NAME Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'CONTROL BUDGET' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.XCC_CONTROL_BUDGETS budget,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE budget.CONTROL_BUDGET_ID = role.CONTROL_BUDGET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT st.SET_NAME Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'REFERENCE DATA SET' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FND_SETID_SETS_VL st,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE st.SET_ID = role.SET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT inv.ORGANIZATION_CODE Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'INVENTORY ORGANIZATION' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.INV_ORG_PARAMETERS inv,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
UNION
SELECT hr.CLASSIFICATION_CODE Security_Context_Value, pr.ROLE_NAME Job_Role_Name, pu.username User_Name, role.ACTIVE_FLAG Is_Data_Access_Active
,'PROJECT ORGANIZATION CLASSIFICATION' Security_Context
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.HR_ORG_UNIT_CLASSIFICATIONS_F hr,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE hr.ORG_UNIT_CLASSIFICATION_ID = role.ORG_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
and pu.username = :P_UserName
select distinct book_type_code from FA_BOOK_CONTROLS
select distinct segment1 from FA_CATEGORIES_B
select distinct segment2 from FA_CATEGORIES_B where segment1 =:MAJ_CAT