Fusion BIP security

Ensure that the queries which you are building drive through one or more of the following views:

PER_POSITION_SECURED_LIST_V

PER_PUB_PERS_SECURED_LIST_V

CMP_SALARY_SECURED_LIST_V

PER_ASSIGNMENT_SECURED_LIST_V

PER_DEPARTMENT_SECURED_LIST_V

PER_JOB_SECURED_LIST_V

PER_LDG_SECURED_LIST_V

PER_LEGAL_EMPL_SECURED_LIST_V

PER_LOCATION_SECURED_LIST_V

PAY_PAYROLL_SECURED_LIST_V

PER_PERSON_SECURED_LIST_V

PER_GRADE_SECURED_LIST_V

Terminated Employees Details

SELECT PersonNamePEO.FULL_NAME "Full Name",
PersonDetailsPEO.person_number "Person Number",
  PPNF_MGR.full_name "Supervisor Full Name",
  TO_CHAR(PeriodOfServicePEO.ACTUAL_TERMINATION_DATE,'MM-DD-YYYY') "Actual Termination Date",
  JobTranslationPEO.name "Job Name",
  TO_CHAR(PersonPEO.start_date,'MM-DD-YYYY') "Date of Hire",
  HrLocation.location_code "Location Name",
  OrganizationUnitTranslationPEO.name "Organization Name",
  (
  SELECT TO_CHAR(MAX(perpos.date_start),'MM-DD-YYYY')
  FROM per_periods_of_service perpos,
    per_all_assignments_m perpaam
  WHERE perpos.person_id          =PersonDetailsPEO.person_id
  AND perpaam.period_of_service_id=perpos.period_of_service_id
  AND perpaam.person_id           =perpos.person_id
  AND perpaam.ACTION_CODE        IN('REHIRE','HIRE')
  AND perpaam.effective_end_date <=PeriodOfServicePEO.ACTUAL_TERMINATION_DATE
  ) "Latest Date of Hire",
  TO_CHAR(PeriodOfServicePEO.original_date_of_hire,'MM-DD-YYYY') "ORIGINAL_DATE_OF_HIRE",
  PeriodOfServicePEO.last_updated_by "Terminated By",
  TO_CHAR(PeriodOfServicePEO.last_update_date,'MM-DD-YYYY') "Termination Last Update Date",
  AssignmentPEO.assignment_number "Assignment Number",
  TO_CHAR(AssignmentPEO.effective_start_date,'MM-DD-YYYY') "Effective Start Date",
  TO_CHAR(AssignmentPEO.effective_end_date,'MM-DD-YYYY') "Effective End Date",
  PEA.EMAIL_ADDRESS,
  NVL(PART.ACTION_REASON,'No Reason') "Leaving Reason",
  NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER "Social Security Number"
FROM PER_PERSONS PersonPEO,
  PER_ALL_PEOPLE_F PersonDetailsPEO,
  PER_PERSON_NAMES_F_V PersonNamePEO,
  PER_ALL_ASSIGNMENTS_M AssignmentPEO,
  PER_PERSON_TYPES_TL PersonTypesTranslationPEO,
  PER_PERIODS_OF_SERVICE PeriodOfServicePEO,
  PER_NATIONAL_IDENTIFIERS NationalIdentifierPEO,
  HR_ORGANIZATION_UNITS_F_TL OrganizationUnitTranslationPEO,
  PER_JOBS_F_TL JobTranslationPEO,
  hr_locations_all HrLocation,
  PER_ACTION_REASONS_B PARB,
  PER_ACTION_REASONS_TL PART,
  per_assignment_supervisors_f PASF,
  per_person_names_f PPNF_MGR,
  PER_EMAIL_ADDRESSES PEA,
  PER_ACTIONS_B ActionsPEO,
  HR_ORGANIZATION_UNITS_F_TL GRETranslationPEO,
  HR_ORGANIZATION_UNITS_F_TL BUTranslationPEO,
  hr_all_positions_f_tl PosiTL,
  PER_PERSON_SECURED_LIST_V PPSLV
WHERE PersonPEO.PERSON_ID                    = PersonDetailsPEO.PERSON_ID
AND PersonPEO.PERSON_ID                      = PersonNamePEO.PERSON_ID
AND PersonPEO.PERSON_ID                      = AssignmentPEO.PERSON_ID
AND AssignmentPEO.PERSON_TYPE_ID             = PersonTypesTranslationPEO.PERSON_TYPE_ID(+)
AND (USERENV('LANG'))                        = PersonTypesTranslationPEO.LANGUAGE
AND AssignmentPEO.PERIOD_OF_SERVICE_ID       = PeriodOfServicePEO.PERIOD_OF_SERVICE_ID(+)
AND AssignmentPEO.PERSON_ID                  = PeriodOfServicePEO.PERSON_ID(+)
AND PersonDetailsPEO.PERSON_ID               = NationalIdentifierPEO.PERSON_ID(+)
AND PersonDetailsPEO.PRIMARY_NID_ID          = NationalIdentifierPEO.NATIONAL_IDENTIFIER_ID(+)
AND ( (AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' ) )
AND ( (AssignmentPEO.ASSIGNMENT_TYPE        IN ('E','C' ,'N','P') ))
AND AssignmentPEO.organization_id            = OrganizationUnitTranslationPEO.ORGANIZATION_ID(+)
AND (USERENV('LANG'))                        = OrganizationUnitTranslationPEO.LANGUAGE(+)
AND AssignmentPEO.JOB_ID                     = JobTranslationPEO.JOB_ID(+)
AND (USERENV('LANG'))                        = JobTranslationPEO.LANGUAGE(+)
AND HrLocation.location_id(+)                =AssignmentPEO.location_id

AND ( sysdate BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN AssignmentPEO.EFFECTIVE_START_DATE AND AssignmentPEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+) AND OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+))
AND ( sysdate BETWEEN JobTranslationPEO.EFFECTIVE_START_DATE(+) AND JobTranslationPEO.EFFECTIVE_END_DATE(+))

AND PARB.ACTION_REASON_ID        = PART.ACTION_REASON_ID(+)
AND AssignmentPEO.REASON_CODE    = PARB.ACTION_REASON_CODE(+)
AND PART.language(+)             ='US'
AND PeriodOfServicePEO.person_id = PASF.person_id(+)
AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE BETWEEN PASF.effective_start_date(+) AND PASF.effective_end_date(+)
AND PASF.manager_type(+) = 'LINE_MANAGER'
AND PASF.manager_id      = PPNF_MGR.person_id(+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF_MGR.effective_start_date(+)) AND TRUNC(PPNF_MGR.effective_end_date(+))
AND PPNF_MGR.name_type(+)             = 'GLOBAL'
AND PersonDetailsPEO.primary_email_id = PEA.email_address_id(+)
AND AssignmentPEO.ACTION_CODE         = ActionsPEO.ACTION_CODE(+)
AND AssignmentPEO.BUSINESS_GROUP_ID   =ActionsPEO.BUSINESS_GROUP_ID(+)

AND ActionsPEO.ACTION_TYPE_CODE   = 'EMPL_TERMINATE'
AND AssignmentPEO.legal_entity_id =GRETranslationPEO.ORGANIZATION_ID(+)
AND ( sysdate BETWEEN GRETranslationPEO.EFFECTIVE_START_DATE(+) AND GRETranslationPEO.EFFECTIVE_END_DATE(+))
AND GRETranslationPEO.language(+) ='US'
AND AssignmentPEO.Business_unit_id=BUTranslationPEO.ORGANIZATION_ID(+)
AND ( sysdate BETWEEN BUTranslationPEO.EFFECTIVE_START_DATE(+) AND BUTranslationPEO.EFFECTIVE_END_DATE(+))
AND BUTranslationPEO.language(+)='US'
AND AssignmentPEO.position_id   =PosiTL.position_id(+)
AND (USERENV('LANG'))           = PosiTL.LANGUAGE(+)
AND AssignmentPEO.PERSON_ID     = PPSLV.PERSON_ID
AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE

AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE >= (:p_from_date)
AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE <= (:p_to_date)

Employee Deduction Details

  SELECT PersonDetailsPEO.person_id,
          PersonNamePEO.full_name,
          PersonDetailsPEO.PERSON_NUMBER ,
          AssignmentPEO.assignment_number,
          NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER,
          JobTranslationPEO.name AS JOB_NAME,
          GRETranslationPEO.name as GRE,
          OrganizationUnitTranslationPEO.Name  AS ORG_NAME,
          HRLocationTL.location_name,
          SupervisorNamePEO.full_name AS SUP_NAME,
          BUTranslationPEO.name       AS BU_Name ,
          PosiTL.name                 AS POS_Name,
          BalanceCategoriesPEO.BASE_CATEGORY_NAME,
          PayrollActionPEO.effective_date,
          PayrollActionPEO.date_earned,
          PayrollDPEO.payroll_name,
          TimePeriodPEO.END_DATE,
          TimePeriodPEO.START_DATE,
          BalanceTypesPEO.BALANCE_NAME as ELEMENT_NAME,
          BalanceTypesPEO.CURRENCY_CODE,
          sum(PayrollBalancesPEO.balance_value) PTD_Balance
         
        FROM PAY_RUN_BALANCES PayrollBalancesPEO,
          PAY_PAY_RELATIONSHIPS_DN PayrollRelationshipPEO,
          PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO,
          PAY_PAYROLL_ACTIONS PayrollActionPEO,
          PER_ALL_ASSIGNMENTS_F AssignmentPEO,
          PAY_REL_GROUPS_DN PRG,
          PER_ALL_PEOPLE_F PersonDetailsPEO,
          PAY_DEFINED_BALANCES DefinedBalancesPEO,
          PAY_BALANCE_DIMENSIONS BalanceDimensionsPEO,
          PAY_TIME_PERIODS TimePeriodPEO,
          PAY_BALANCE_TYPES_VL BalanceTypesPEO,
          PAY_BALANCE_CATEGORIES_VL BalanceCategoriesPEO,
          PER_PERSON_NAMES_F_V PersonNamePEO,
          PER_NATIONAL_IDENTIFIERS NationalIdentifierPEO,
          PAY_ALL_PAYROLLS_F PayrollDPEO,
          PER_JOBS_F_TL JobTranslationPEO,
          HR_ORGANIZATION_UNITS_F_TL OrganizationUnitTranslationPEO,
          HR_ORGANIZATION_UNITS_F_TL GRETranslationPEO,
          PER_LOCATION_DETAILS_F HRLocation,
          PER_LOCATION_DETAILS_F_TL HRLocationTL,
          PER_PERSON_NAMES_F_V SupervisorNamePEO,
          HR_ORGANIZATION_UNITS_F_TL BUTranslationPEO,
          --hr_all_positions_f_tl PosiTL,
          PER_ASSIGNMENT_SUPERVISORS_F AssignmentSupervisorPEO,
          PER_PERSON_SECURED_LIST_V PPSLV
        WHERE NVL(PayrollBalancesPEO.payroll_rel_action_id,0)=PayrollRelationshipActionPEO.payroll_rel_action_id
        AND PayrollRelationshipActionPEO.payroll_action_id   =PayrollActionPEO.payroll_action_id
        AND PayrollBalancesPEO.payroll_relationship_id       = PayrollRelationshipPEO.payroll_relationship_id (+)
        AND PayrollRelationshipPEO.person_id                 = AssignmentPEO.person_id
        AND PayrollActionPEO.effective_date BETWEEN AssignmentPEO.effective_start_date AND AssignmentPEO.effective_end_date
        AND PRG.relationship_group_id (+)=PayrollBalancesPEO.payroll_assignment_id
        and AssignmentPEO.ASSIGNMENT_TYPE        in ( 'E','C','N','P')
        and AssignmentPEO.person_id=PersonDetailsPEO.person_id
        AND sysdate between PersonDetailsPEO.effective_start_date and PersonDetailsPEO.effective_end_date
        AND DefinedBalancesPEO.BALANCE_DIMENSION_ID     = BalanceDimensionsPEO.BALANCE_DIMENSION_ID
        AND PayrollBalancesPEO.DEFINED_BALANCE_ID      = DefinedBalancesPEO.DEFINED_BALANCE_ID
        AND PayrollActionPEO.PAYROLL_ID              = TimePeriodPEO.PAYROLL_ID
        AND PayrollActionPEO.EFFECTIVE_DATE between TimePeriodPEO.START_DATE and TimePeriodPEO.END_DATE
        AND DefinedBalancesPEO.BALANCE_TYPE_ID          = BalanceTypesPEO.BALANCE_TYPE_ID
        AND BalanceTypesPEO.BALANCE_CATEGORY_ID     = BalanceCategoriesPEO.BALANCE_CATEGORY_ID
        AND PayrollRelationshipActionPEO.source_id is null
        AND AssignmentPEO.PERSON_ID    = PersonNamePEO.PERSON_ID(+)
        AND PersonNamePEO.name_type(+) = 'GLOBAL'
        AND ( sysdate BETWEEN PersonNamePEO.EFFECTIVE_START_DATE(+) AND PersonNamePEO.EFFECTIVE_END_DATE(+))
        AND PersonDetailsPEO.PERSON_ID           = NationalIdentifierPEO.PERSON_ID(+)
        AND PersonDetailsPEO.PRIMARY_NID_ID      = NationalIdentifierPEO.NATIONAL_IDENTIFIER_ID(+)
        AND PayrollActionPEO.PAYROLL_ID                = PayrollDPEO.PAYROLL_ID
        AND PayrollActionPEO.EFFECTIVE_DATE BETWEEN PayrollDPEO.EFFECTIVE_START_DATE AND PayrollDPEO.EFFECTIVE_END_DATE
        AND AssignmentPEO.JOB_ID                 = JobTranslationPEO.JOB_ID(+)
        AND (USERENV('LANG'))                    = JobTranslationPEO.LANGUAGE(+)
        AND ( sysdate BETWEEN JobTranslationPEO.EFFECTIVE_START_DATE(+) AND JobTranslationPEO.EFFECTIVE_END_DATE(+))
            AND AssignmentPEO.organization_id=OrganizationUnitTranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+) AND OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+))
        AND OrganizationUnitTranslationPEO.language(+)='US'
        AND AssignmentPEO.legal_entity_id             =GRETranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN GRETranslationPEO.EFFECTIVE_START_DATE(+) AND GRETranslationPEO.EFFECTIVE_END_DATE(+))
        AND GRETranslationPEO.language(+)   ='US'
        AND AssignmentPEO.location_id       =HRLocation.location_id(+)
        AND HRLocation.LOCATION_DETAILS_ID  = HRLocationTL.LOCATION_DETAILS_ID(+)
        AND HRLocationTL.LANGUAGE(+)        = USERENV('LANG')
        AND HRLocation.EFFECTIVE_START_DATE = HRLocationTL.EFFECTIVE_START_DATE
        AND HRLocation.EFFECTIVE_END_DATE   = HRLocationTL.EFFECTIVE_END_DATE
        AND sysdate BETWEEN HRLocation.EFFECTIVE_START_DATE(+) AND HRLocation.EFFECTIVE_END_DATE(+)
          AND AssignmentSupervisorPEO.MANAGER_ID = SupervisorNamePEO.PERSON_ID(+)
        AND SupervisorNamePEO.name_type(+)     = 'GLOBAL'
        AND ( sysdate BETWEEN SupervisorNamePEO.EFFECTIVE_START_DATE(+) AND SupervisorNamePEO.EFFECTIVE_END_DATE(+))
            AND AssignmentPEO.Business_unit_id=BUTranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN BUTranslationPEO.EFFECTIVE_START_DATE(+) AND BUTranslationPEO.EFFECTIVE_END_DATE(+))
        AND BUTranslationPEO.language(+)='US'
        --AND AssignmentPEO.position_id   =PosiTL.position_id(+)
        --AND (USERENV('LANG'))           = PosiTL.LANGUAGE(+)
        AND AssignmentPEO.PERSON_ID = PPSLV.PERSON_ID
        AND sysdate BETWEEN PPSLV.effective_start_date AND PPSLV.effective_end_date
        AND AssignmentPEO.ASSIGNMENT_ID        = AssignmentSupervisorPEO.ASSIGNMENT_ID(+)
        AND ('LINE_MANAGER')                   = AssignmentSupervisorPEO.MANAGER_TYPE(+)
        AND ( sysdate BETWEEN AssignmentSupervisorPEO.EFFECTIVE_START_DATE(+) AND AssignmentSupervisorPEO.EFFECTIVE_END_DATE(+))
       
        AND ( (BalanceDimensionsPEO.PERIOD_TYPE          = 'RUN' ) )
        and TimePeriodPEO.STATUS             = 'O'
        AND AssignmentPEO.primary_flag              ='Y'
        AND (AssignmentPEO.assignment_status_type  = 'ACTIVE')
       
  --Pass the list of categories you want to list results for     
        AND BalanceCategoriesPEO.BASE_CATEGORY_NAME in ('Pre-Statutory Deductions','Voluntary Deductions',
       'Total Tax Deductions','Social Insurance Deductions','Involuntary Deductions')
  -- Pass the list of dimensions that you want to result
        AND  BalanceDimensionsPEO.BASE_DIMENSION_NAME  in
( 'Core Relationship No Calculation Breakdown, Tax Unit Run')
  -- pass the Person number below   
    and (PersonDetailsPEO.PERSON_NUMBER        IN (:p_person_number)
        OR 'All'           IN (:p_person_number
       ||'All'))
   --Pass check date for which you want to view data     
       and PayrollActionPEO.effective_date<=:p_to_date
group by
        PersonDetailsPEO.person_id,
          PersonDetailsPEO.PERSON_NUMBER ,
          AssignmentPEO.assignment_number,
          NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER,
          BalanceCategoriesPEO.BASE_CATEGORY_NAME,
          PayrollActionPEO.effective_date,
          PayrollActionPEO.date_earned,
          PayrollDPEO.payroll_name,
          TimePeriodPEO.END_DATE,
          TimePeriodPEO.START_DATE,
          SupervisorNamePEO.full_name ,
          BUTranslationPEO.name       ,
          PosiTL.name               ,
          BalanceTypesPEO.BALANCE_NAME,
          BalanceTypesPEO.CURRENCY_CODE,
          PersonNamePEO.full_name ,
          JobTranslationPEO.name,
          GRETranslationPEO.name,
          OrganizationUnitTranslationPEO.Name,
          HRLocationTL.location_name
          

Filter items by color

If you've applied different cell or font colors or a conditional format, you can filter by the colors or icons that are shown in your table.

1. Click the arrow  in the table header of the column that has color formatting or conditional formatting applied.
2. Click Filter by Color and then pick the cell color, font color, or icon you want to filter by.
   
   The types of color options you’ll have available depend on the types of format you have applied.

Security Roles In OBIEE11g

Hi All,

By Default OBIEE11g provided the 3 default roles.

BI Consumer.
BI Author.
BI Administrator.

 Let's see the deference's.

1.BI Consumer: The base-level role that grants the user access to existing analyses, dashboards and agents, allows them to run or schedule existing BI Publisher reports, but not create any new ones. The Consumer can only view and run existing dashboards, analysis and reports provided to them. These objects will be published in a shared area with proper security rights. Consumers typically are the broadest user base across the institution.

2. BI Author: A role that is also recursively granted the Bi Consumer role that also allows users to create new analyses, dashboards and other BI objects. The Author can create and edit dashboards, analyses and reports. Authors will include a narrower user base than Consumers.

3. BI Administrator: Recursively granted the BIAuthor (and therefore BIConsumer) roles that allows the user to administer all parts of the system, including modifying catalog permissions and privilege. The Administrator can edit and create new repositories and catalogs. They also have full control over all aspects of the OBIEE tool suite.

OBIEE Security Groups
These roles correspond to a set of LDAP groups within the embedded Weblogic Server LDAP Server that have almost the same names (plural rather than singular) as these application roles:

1. BIConsumers 
2. BIAuthors 
3. BIAdministrators 

It’s these LDAP groups that you assign users to, not application roles, with Fusion Middleware then mapping these LDAP groups into their corresponding application roles. Later on, we’ll look at how and why you might want to create another LDAP group and corresponding application role like these, which we’ll call BIAnalyst; for now though, let’s look at how you create a new user and grant them one of the existing roles.

EBS Query to get segment structure description of an Chart of Account Code

SELECT

ST.ID_FLEX_STRUCTURE_CODE  "Chart of Account Code"

,SG.ID_FLEX_NUM            "Chart of Account Num"

,SG.SEGMENT_NAME               "Segment Name"

,SG.APPLICATION_COLUMN_NAME    "Column Name"

,SG.FLEX_VALUE_SET_ID          "Value Set Id"

,VS.FLEX_VALUE_SET_NAME

FROM

FND_ID_FLEX_STRUCTURES ST

INNER JOIN FND_ID_FLEX_SEGMENTS SG ON ST.APPLICATION_ID = SG.APPLICATION_ID AND ST.ID_FLEX_CODE = SG.ID_FLEX_CODE AND ST.ID_FLEX_NUM = SG.ID_FLEX_NUM

INNER JOIN FND_FLEX_VALUE_SETS VS ON SG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID

LEFT OUTER JOIN FND_ID_FLEX_SEGMENTS SG1 ON VS.PARENT_FLEX_VALUE_SET_ID = SG1.FLEX_VALUE_SET_ID AND SG.ID_FLEX_NUM = SG1.ID_FLEX_NUM AND SG.APPLICATION_ID = SG1.APPLICATION_ID AND SG.ID_FLEX_CODE = SG1.ID_FLEX_CODE

WHERE

ST.APPLICATION_ID = 101

AND ST.ID_FLEX_CODE = 'GL#'

AND ST.ENABLED_FLAG = 'Y'

and SG.ID_FLEX_NUM =101

ORDER BY 1,2,3;

Masking Characters in OBIEE

In most of the cases like Bank Account Number, Personal Phone Number we need to mask few of the characters with 'X' for security purpose. Use below syntax for it.

REPEAT('x', LENGTH("Target Bank"."Target Bank Account Number")-4)||RIGHT("Target Bank"."Target Bank Account Number", 4)

Here "Target Bank"."Target Bank Account Number" is Bank account Number and I want to mask all the Characters with 'X' except last 4 digits. This will expose only last 4 digits as shown below.

Example: xxxxx8456

Payroll Details Query

select pbt.balance_name, pet.element_name, piv.name input_value_name,piv.effective_start_date
from pay_balance_types pbt,
pay_balance_feeds_f pbf,
Pay_input_values_f piv,
pay_element_types_f pet
where pbt.balance_type_id = pbf.balance_type_id
and trunc(sysdate) between pbf.effective_start_date and pbf.effective_end_date
and piv.input_value_id = pbf.input_value_id
and pbf.effective_start_date between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and pet.effective_start_date between piv.effective_start_date and piv.effective_end_date
order by 1,2

select pbt.balance_name, pbd.dimension_name, pbd.description
from pay_defined_balances pdf,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pdf.balance_type_id = pbt.balance_type_id
and pdf.balance_dimension_id = pbd.balance_dimension_id
order by 1,2