OA4F_SEC_CST_COST_ORG_LIST |
SELECT
DISTINCT TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_CST_COST_ORG_BOOKS_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE
D.COST_ORG_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D. COST_ORG_ID <>-99999
|
OA4F_SEC_FIN_AP_BUSINESSUNIT_LIST |
SELECT TL.ORGANIZATION_NAME AS NAME,TL.ORGANIZATION_ID AS VALUE FROM DW_LEDGER_D L, DW_INTERNAL_ORG_D D, DW_INTERNAL_ORG_D_TL TL, DW_CONTENT_PARAM_CONFIG PARAM
WHERE (D.PAYABLES_INVOICING_BU_FLAG = 'Y' OR D.PAYABLES_PAYMENT_BU_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.PRIMARY_LEDGER = L.LEDGER_NAME
AND TO_CHAR(L.LEDGER_ID)=PARAM.PARAMETER_VALUE AND PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT' AND PARAM.PARAMETER_VALUE<>'ALL'
AND D.ORGANIZATION_ID<>-99999
UNION
SELECT TL.ORGANIZATION_NAME AS NAME,TL.ORGANIZATION_ID AS VALUE FROM DW_LEDGER_D L, DW_INTERNAL_ORG_D D, DW_INTERNAL_ORG_D_TL TL, DW_CONTENT_PARAM_CONFIG PARAM
WHERE (D.PAYABLES_INVOICING_BU_FLAG = 'Y' OR D.PAYABLES_PAYMENT_BU_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.PRIMARY_LEDGER = L.LEDGER_NAME
AND PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT' AND PARAM.PARAMETER_VALUE='ALL'
AND (TO_CHAR(L.LEDGER_CHART_OF_ACCOUNTS_ID) IN (SELECT PARAM.PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG PARAM WHERE PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL')
)
AND D.ORGANIZATION_ID<>-99999
UNION
SELECT
tl.organization_name AS name,
tl.organization_id AS value
FROM
dw_ledger_set_assignments_d assignments,
dw_ledger_d l,
dw_internal_org_d d,
dw_internal_org_d_tl tl,
dw_content_param_config param
WHERE
param.PARAMETER_VALUE = 'ALL'
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND assignments.ledger_id = l.ledger_id
AND d.primary_ledger = l.ledger_name
AND ( d.payables_invoicing_bu_flag = 'Y' OR d.PAYABLES_PAYMENT_BU_FLAG = 'Y' )
AND d.organization_id = tl.organization_id
AND tl.language = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND (TO_CHAR(L.LEDGER_CHART_OF_ACCOUNTS_ID) IN (SELECT PARAM.PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG PARAM WHERE PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL')
)
AND d.organization_id <> -99999
UNION
SELECT
tl.organization_name AS name,
tl.organization_id AS value
FROM
dw_ledger_set_assignments_d assignments,
dw_ledger_d l,
dw_internal_org_d d,
dw_internal_org_d_tl tl,
dw_content_param_config param
WHERE
TO_CHAR(assignments.ledger_set_id) = param.parameter_value
AND param.PARAMETER_VALUE <> 'ALL'
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND assignments.ledger_id = l.ledger_id
AND d.primary_ledger = l.ledger_name
AND ( d.payables_invoicing_bu_flag = 'Y' OR d.PAYABLES_PAYMENT_BU_FLAG = 'Y' )
AND d.organization_id = tl.organization_id
AND tl.language = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND d.organization_id <> -99999
ORDER BY NAME
|
OA4F_SEC_FIN_AR_BUSINESSUNIT_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM DW_LEDGER_D L,
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL,
DW_CONTENT_PARAM_CONFIG PARAM
WHERE (D.BILLING_REVENUE_MNG_BU_FLAG = 'Y' OR D.CUSTOMER_PAYMENTS_BU_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.PRIMARY_LEDGER = L.LEDGER_NAME
AND TO_CHAR(L.LEDGER_ID)=PARAM.PARAMETER_VALUE
AND PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT' AND PARAM.PARAMETER_VALUE<>'ALL'
AND D.ORGANIZATION_ID<>-99999
UNION
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM DW_LEDGER_D L,
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL,
DW_CONTENT_PARAM_CONFIG PARAM
WHERE (D.BILLING_REVENUE_MNG_BU_FLAG = 'Y' OR D.CUSTOMER_PAYMENTS_BU_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.PRIMARY_LEDGER = L.LEDGER_NAME
AND PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT' AND PARAM.PARAMETER_VALUE='ALL'
AND (TO_CHAR(L.LEDGER_CHART_OF_ACCOUNTS_ID) IN (SELECT PARAM.PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG PARAM WHERE PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL')
)
AND D.ORGANIZATION_ID<>-99999
UNION
SELECT
tl.organization_name AS name,
tl.organization_id AS value
FROM
dw_ledger_set_assignments_d assignments,
dw_ledger_d l,
dw_internal_org_d d,
dw_internal_org_d_tl tl,
dw_content_param_config param
WHERE
param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND param.PARAMETER_VALUE = 'ALL'
AND assignments.ledger_id = l.ledger_id
AND d.primary_ledger = l.ledger_name
AND( d.billing_revenue_mng_bu_flag = 'Y' OR d.CUSTOMER_PAYMENTS_BU_FLAG = 'Y' )
AND d.organization_id = tl.organization_id
AND tl.language = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND (TO_CHAR(L.LEDGER_CHART_OF_ACCOUNTS_ID) IN (SELECT PARAM.PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG PARAM WHERE PARAM.PARAMETER_CODE='PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL')
)
AND d.organization_id <> -99999
UNION
SELECT
tl.organization_name AS name,
tl.organization_id AS value
FROM
dw_ledger_set_assignments_d assignments,
dw_ledger_d l,
dw_internal_org_d d,
dw_internal_org_d_tl tl,
dw_content_param_config param
WHERE
TO_CHAR(assignments.ledger_set_id) = param.parameter_value
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND param.PARAMETER_VALUE <> 'ALL'
AND assignments.ledger_id = l.ledger_id
AND d.primary_ledger = l.ledger_name
AND( d.billing_revenue_mng_bu_flag = 'Y' OR d.CUSTOMER_PAYMENTS_BU_FLAG = 'Y' )
AND d.organization_id = tl.organization_id
AND tl.language = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND d.organization_id <> -99999
ORDER BY NAME
|
OA4F_SEC_FIN_FA_ASSET_BOOK_LIST |
SELECT
BOOK_TYPE_CODE AS name,
BOOK_TYPE_CODE AS value
FROM
DW_FA_BOOK_CONTROLS_D
|
OA4F_SEC_FIN_LEDGER_LIST |
SELECT ledger.ledger_name AS name,
ledger.ledger_id AS value
FROM dw_ledger_d ledger,
dw_content_param_config param
WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT'
AND param.parameter_value = 'ALL'
AND ( TO_CHAR(ledger.ledger_chart_of_accounts_id) IN (SELECT param.parameter_value FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL') )
AND ledger.ledger_id <> - 99999
UNION
SELECT ledger.ledger_name AS name,
ledger.ledger_id AS value
FROM dw_ledger_d ledger,
dw_content_param_config param
WHERE TO_CHAR(ledger.ledger_id) = param.parameter_value
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_ID_LIST_REPORT'
AND param.parameter_value <> 'ALL'
AND ( TO_CHAR(ledger.ledger_chart_of_accounts_id) IN (SELECT param.parameter_value FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL') )
AND ledger.ledger_id <> - 99999
UNION
SELECT ledger.ledger_name AS name,
ledger.ledger_id AS value
FROM dw_ledger_set_d ledgerset,
dw_content_param_config param,
dw_ledger_set_assignments_d assignments,
dw_ledger_d ledger
WHERE ledgerset.ledger_set_id = assignments.ledger_set_id
AND assignments.ledger_id = ledger.ledger_id
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND param.parameter_value = 'ALL'
AND ( TO_CHAR(ledger.ledger_chart_of_accounts_id) IN (SELECT param.parameter_value FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL') )
AND ledgerset.ledger_set_id <> - 99999 AND ledger.ledger_id <> - 99999
UNION
SELECT ledger.ledger_name AS name,
ledger.ledger_id AS value
FROM dw_ledger_set_d ledgerset,
dw_content_param_config param,
dw_ledger_set_assignments_d assignments,
dw_ledger_d ledger
WHERE TO_CHAR(ledgerset.ledger_set_id) = param.parameter_value
AND ledgerset.ledger_set_id = assignments.ledger_set_id
AND assignments.ledger_id = ledger.ledger_id
AND param.parameter_code = 'PARAM_ERP_FINANCIALS_LEDGER_SET_ID_LIST_REPORT'
AND param.parameter_value <> 'ALL'
AND ( TO_CHAR(ledger.ledger_chart_of_accounts_id) IN (SELECT param.parameter_value FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID')
OR EXISTS (SELECT 1 FROM dw_content_param_config param WHERE param.parameter_code = 'PARAM_ERP_FINANCIALS_CHART_OF_ACCOUNT_ID' AND param.parameter_value = 'ALL') )
AND ledgerset.ledger_set_id <> - 99999 AND ledger.ledger_id <> - 99999
|
OA4F_SEC_HCM_BUSINESSUNIT_LIST |
SELECT TL.BUSINESS_UNIT_NAME AS NAME,
TL.BUSINESS_UNIT_ID AS VALUE
FROM DW_BUSINESS_UNIT_D_TL TL
WHERE TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
|
OA4F_SEC_HCM_COUNTRY_LIST |
SELECT TL.COUNTRY_NAME AS NAME, TL.COUNTRY AS VALUE
FROM DW_COUNTRY_D_TL TL WHERE TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
|
OA4F_SEC_HCM_DEPARTMENT_LIST |
SELECT TL.BUSINESS_UNIT_NAME AS NAME,
TL.BUSINESS_UNIT_ID AS VALUE
FROM DW_BUSINESS_UNIT_D_TL TL
WHERE TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
|
OA4F_SEC_HCM_LEGAL_EMPLOYER_LIST |
SELECT TL.LEGAL_EMPLOYER_NAME AS NAME, TL.LEGAL_EMPLOYER_ID AS VALUE FROM DW_LEGAL_EMPLOYER_D_TL TL WHERE LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
|
OA4F_SEC_HCM_SEE_SELF_RECORD |
SELECT YESNO_LKP_CODE AS VALUE,
NAME AS NAME
FROM DW_YESNO_LKP_TL
WHERE CODE_TYPE LIKE 'FA_YESNO'
AND LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND YESNO_LKP_CODE IN ( 'NO')
|
OA4F_SEC_INV_BUSINESSUNIT_LIST |
SELECT TL.BUSINESS_UNIT_NAME AS NAME, TL.BUSINESS_UNIT_ID AS VALUE
FROM DW_BUSINESS_UNIT_D_TL TL
WHERE TL.BUSINESS_UNIT_ID IN (SELECT DISTINCT(INV_BUSINESS_UNIT_ID) FROM DW_INV_ORGANIZATION_D)
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES') AND TL.BUSINESS_UNIT_ID <> '-99999'
|
OA4F_SEC_INV_ORG_TRANSACTIONS_LIST |
SELECT INV_ORGANIZATION_NAME AS NAME, INV_ORGANIZATION_ID AS VALUE FROM DW_INV_ORGANIZATION_D_TL TL
WHERE TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES') AND TL.INV_ORGANIZATION_ID <> '-99999'
|
OA4F_SEC_OM_BUSINESS_UNIT_LIST |
SELECT
TL.BUSINESS_UNIT_NAME AS NAME,
TL.BUSINESS_UNIT_ID AS VALUE
FROM DW_BUSINESS_UNIT_D_TL TL
WHERE TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND TL.BUSINESS_UNIT_ID<>-99999
|
OA4F_SEC_PPM_EXPENDITURE_BUSINESSUNIT_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE (D.PROJECT_ACCOUNTING_BU_FLAG = 'Y' AND D.BUSINESS_UNIT_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG')
AND D.ORGANIZATION_ID<>-99999
|
OA4F_SEC_PPM_PROJECT_BUSINESSUNIT_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE (D.PROJECT_ACCOUNTING_BU_FLAG = 'Y' AND D.BUSINESS_UNIT_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG')
AND D.ORGANIZATION_ID<>-99999
|
OA4F_SEC_PPM_PROJECT_ORGANIZATION_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE (D.PROJECT_ORGANIZATION_FLAG = 'Y' )
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG')
AND D.ORGANIZATION_ID<>-99999
|
OA4F_SEC_PROC_REQ_BUSINESSUNIT_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE (D.REQUISITION_BU_FLAG = 'Y' AND D.BUSINESS_UNIT_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.ORGANIZATION_ID<>-99999
|
OA4F_SEC_PROC_SPEND_PRC_BUSINESSUNIT_LIST |
SELECT
TL.ORGANIZATION_NAME AS NAME,
TL.ORGANIZATION_ID AS VALUE
FROM
DW_INTERNAL_ORG_D D,
DW_INTERNAL_ORG_D_TL TL
WHERE (D.PROCUREMENT_BU_FLAG = 'Y' AND D.BUSINESS_UNIT_FLAG = 'Y')
AND D.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = (SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE PARAMETER_CODE='PARAM_GLOBAL_FAW_LANGUAGES')
AND D.ORGANIZATION_ID<>-99999
|