Hi @Arjun

The query I posted above is just an example but the real query used is much more complex (see below) and I'm not sure I can reproduce it by using OTBI.

What I would like to know, it's if I used the secured view per_assignment_secured_list_v, is it possible give access only to the assignment that the user should have access to? For the moment this query below return assignments that the user should not be able to view has the assignment is not in his AoR.

WITH calendar_param AS
(
SELECT
nvl(:calendar_at_date, trunc(sysdate)) AS at_date
FROM
dual
),
orga AS (
SELECT
per_org.name AS orga,
lower(per_org.language) AS lang_code,
org_tree2.pk1_value
FROM
per_org_tree_node_rf org_tree2,
hr_organization_units_f_tl per_org,
hr_all_organization_units_f org
WHERE
org_tree2.tree_structure_code = 'PER_ORG_TREE_STRUCTURE'
AND org_tree2.tree_code = 'SOC_ENT_TREE'
AND org_tree2.distance = (
SELECT
MAX(org_tree3.distance)
FROM
per_org_tree_node_rf org_tree3
WHERE
org_tree3.tree_structure_code = org_tree2.tree_structure_code
AND org_tree3.tree_code = org_tree2.tree_code
AND org_tree3.tree_version_id = org_tree2.tree_version_id
AND org_tree3.tree_node_id = org_tree2.tree_node_id
) - 3
AND per_org.organization_id = org_tree2.ancestor_pk1_value
AND org.organization_id = per_org.organization_id
AND trunc(SYSDATE) BETWEEN org.effective_start_date AND org.effective_end_date
AND lower(per_org.language) = 'us'
),
perimeter_4 AS (
SELECT
org.organization_code AS perimeter4_code,
per_org.name AS perimeter4,
lower(per_org.language) AS lang_code,
org_tree2.pk1_value
FROM
per_org_tree_node_rf org_tree2,
hr_organization_units_f_tl per_org,
hr_all_organization_units_f org
WHERE
org_tree2.tree_structure_code = 'PER_ORG_TREE_STRUCTURE'
AND org_tree2.tree_code = 'SOC_ENT_TREE'
AND org_tree2.distance = (
SELECT
MAX(org_tree3.distance)
FROM
per_org_tree_node_rf org_tree3
WHERE
org_tree3.tree_structure_code = org_tree2.tree_structure_code
AND org_tree3.tree_code = org_tree2.tree_code
AND org_tree3.tree_version_id = org_tree2.tree_version_id
AND org_tree3.tree_node_id = org_tree2.tree_node_id
) - 4
AND per_org.organization_id = org_tree2.ancestor_pk1_value
AND org.organization_id = per_org.organization_id
AND trunc(SYSDATE) BETWEEN org.effective_start_date AND org.effective_end_date
AND lower(per_org.language) = 'us'
),
hrbp1 AS (
SELECT
hrbp1_name.full_name,
hrbp1_name.display_name,
hrbp1_name.person_id,
hrbp1.responsibility_name,
hrbp1.responsibility_type,
hrbp1.top_organization_id,
hrbp1.country,
hrbp1.assignment_id,
hrbp1_name.list_name,
ROW_NUMBER()
OVER(PARTITION BY hrbp1.country, hrbp1.top_organization_id
ORDER BY
hrbp1.legal_entity_id ASC
) row_num
FROM
per_asg_responsibilities hrbp1,
per_person_names_f hrbp1_name
WHERE
hrbp1_name.person_id = hrbp1.person_id
AND hrbp1_name.name_type = 'GLOBAL'
AND trunc(SYSDATE) BETWEEN hrbp1_name.effective_start_date AND hrbp1_name.effective_end_date
AND hrbp1.responsibility_type = 'SOC_HRBP_T1'
AND hrbp1.responsibility_name LIKE '%HRBP Tier 1%'
AND hrbp1.status = 'Active'
),
hrbp2 AS (
SELECT
hrbp2_name.full_name,
hrbp2_name.display_name,
hrbp2_name.person_id,
hrbp2.responsibility_name,
hrbp2.responsibility_type,
hrbp2.top_organization_id,
hrbp2.country,
hrbp2.assignment_id,
hrbp2_name.list_name,
ROW_NUMBER()
OVER(PARTITION BY hrbp2.country, hrbp2.top_organization_id
ORDER BY
hrbp2.legal_entity_id ASC
) row_num
FROM
per_asg_responsibilities hrbp2,
per_person_names_f hrbp2_name
WHERE
hrbp2_name.person_id = hrbp2.person_id
AND hrbp2_name.name_type = 'GLOBAL'
AND trunc(SYSDATE) BETWEEN hrbp2_name.effective_start_date AND hrbp2_name.effective_end_date
AND hrbp2.responsibility_type = 'SOC_HRBP_T2'
AND hrbp2.responsibility_name LIKE '%HRBP Tier 2%'
AND hrbp2.status = 'Active'
)
SELECT
position.position_code,
position_local.name AS position_local_name,
position_tl.name AS position_name,
LPAD(people.person_number, 5, '0') AS person_number,
extid.ext_identifier_number AS payroll_id,
trigram.ext_identifier_number AS trigram,
lookup_name.meaning AS title_employee,
name.first_name AS "First name",
name.middle_names AS "Middle name",
name.last_name AS "Last name",
name.display_name AS "Full name",
emp_cat_lookup.meaning AS empl_category,
assign.ASS_ATTRIBUTE2 AS empl_sub_category,
contract_lookup.meaning AS contract_type,
loc_details.attribute1 AS employee_legal_entity,
jobcd.job_code,
jobcd.name AS job_title,
jobfam.job_family_code,
jobfamtl.job_family_name,
jobcd.attribute1 AS job_sub_family,
orgun.name AS employee_department,
orgun2.attribute2 AS employee_department_code,
assign.ass_attribute14 AS variable_type,
sup_position.position_code AS manager_position_number,
sup_name.display_name AS manager_name,
LPAD(sup_details.person_number, 5, '0') AS manager_number,
orguntl.name AS business_unit,
loc_tl.territory_short_name AS work_country,
orgestbtl.name AS establishment,
loc_tr.location_name,
orga.orga AS oraganisation,
perimeter_4.perimeter4,
CASE
WHEN instr(perimeter_4.perimeter4, '') > 0 THEN
substr(perimeter_4.perimeter4,
1,
instr(perimeter_4.perimeter4, '') - 1)
ELSE
NULL
END AS perimeter_code,
CASE
WHEN instr(perimeter_4.perimeter4, '') > 0 THEN
substr(perimeter_4.perimeter4,
instr(perimeter_4.perimeter4, '') + 1)
ELSE
NULL
END AS perimeter_desc,
position.active_status AS "Position status",
position_cc_value.flex_value AS destination_code,
position_cc_description.description AS destination_name,
CASE
WHEN position_cc_value.flex_value IN ( 'VARMGC', 'VARMGI', 'VARMGL', 'VARMGS' ) THEN
'Direct'
WHEN position_cc_value.flex_value IN ( 'APPCUS', 'APPDVT', 'APPEIS', 'APPFBO', 'APPMGT',
'APPMKG', 'APPPAB', 'APPPMO', 'APPSTS', 'BOGEN',
'BOILC', 'BOQTY', 'COMDTE', 'COMFBO', 'COMKAS',
'COMMGT', 'COMNA', 'COMSLS', 'COMTSS', 'COMWH',
'GCSCSD', 'GCSFI', 'GCSHR', 'GCSIT', 'GCSLD',
'GCSMD', 'GSDGSM', 'GSDGTD', 'GSDMGT', 'GSDSTD',
'INDFBO', 'INDGEN', 'INDMIS', 'INDMPA', 'OAOIOT',
'OAOMCM', 'OAOMGT', 'OAOTOA', 'PRESTA', 'PURCH',
'SCHFBO', 'SCHWAR' ) THEN
'Indirect'
ELSE
NULL
END AS cost_type,
position.attribute1 AS section_code,
position_local_cc_description.description AS section_name,
(
SELECT
SUM(elm_val.screen_entry_value)
FROM
cmp_salary cmp,
pay_element_entries_f elm,
pay_element_entry_values_f elm_val,
pay_element_types_f elm_type,
pay_element_types_tl elm_type_tl,
pay_input_values_vl piv
WHERE
CMP.person_id = assign.person_id
AND cmp.assignment_id = assign.assignment_id
AND calendar_param.at_date BETWEEN cmp.date_from AND cmp.date_to
AND elm.person_id = cmp.person_id
AND elm.element_entry_id = cmp.element_entry_id
AND elm.element_entry_id = elm_val.element_entry_id
AND calendar_param.at_date BETWEEN elm_val.effective_start_date AND elm_val.effective_end_date
AND calendar_param.at_date BETWEEN elm.effective_start_date AND elm.effective_end_date
AND elm_type.element_type_id = elm.element_type_id
AND calendar_param.at_date BETWEEN elm_type.effective_start_date AND elm_type.effective_end_date
AND elm_type.base_element_name LIKE 'SOC_FIXED_PAY_ANNUAL_%'
AND elm_type_tl.element_type_id = elm.element_type_id
AND elm_type_tl.language = 'US'
AND piv.input_value_id = elm_val.input_value_id
AND elm_val.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.uom = 'M'
) AS "Fixed Pay",
(
SELECT
SUM(elm_val.screen_entry_value)
FROM
pay_element_entries_f elm,
pay_element_entry_values_f elm_val,
pay_element_types_f elm_type,
pay_element_types_tl elm_type_tl,
pay_input_values_vl piv,
pay_entry_usages peu,
pay_rel_groups_dn pasg
WHERE
elm.person_id = assign.person_id
AND elm.element_entry_id = elm_val.element_entry_id
AND assign.effective_start_date BETWEEN elm_val.effective_start_date AND elm_val.effective_end_date
AND assign.effective_start_date BETWEEN elm.effective_start_date AND elm.effective_end_date
AND elm_type.element_type_id = elm.element_type_id
AND assign.effective_start_date BETWEEN elm_type.effective_start_date AND elm_type.effective_end_date
and peu.payroll_assignment_id = pasg.relationship_group_id
and pasg.assignment_id = assign.assignment_id
and elm.element_entry_id = peu.element_entry_id
AND elm_type.base_element_name IN
(
'Fixe amount - BASES3 - CI/FR',
'Fixe amount - BASES - RS/SI',
'Fixe amount - BASES - FR',
'Fixe amount - BASES2 - FR',
'Fixe amount - BASES - IT',
'Fixe amount - BASES3 - IT',
'Fixe amount - BASES - SI',
'Fixe amount - BASES - CI/FR'
)
AND elm_type_tl.element_type_id = elm.element_type_id
AND elm_type_tl.language = 'US'
AND piv.input_value_id = elm_val.input_value_id
AND elm_val.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.uom = 'M'
) AS "Seniority",
(
SELECT
SUM(elm_val.screen_entry_value)
FROM
pay_element_entries_f elm,
pay_element_entry_values_f elm_val,
pay_element_types_f elm_type,
pay_element_types_tl elm_type_tl,
pay_input_values_vl piv,
pay_entry_usages peu,
pay_rel_groups_dn pasg
WHERE
elm.person_id = assign.person_id
AND elm.element_entry_id = elm_val.element_entry_id
AND assign.effective_start_date BETWEEN elm_val.effective_start_date AND elm_val.effective_end_date
AND assign.effective_start_date BETWEEN elm.effective_start_date AND elm.effective_end_date
AND elm_type.element_type_id = elm.element_type_id
AND assign.effective_start_date BETWEEN elm_type.effective_start_date AND elm_type.effective_end_date
and peu.payroll_assignment_id = pasg.relationship_group_id
and pasg.assignment_id = assign.assignment_id
and elm.element_entry_id = peu.element_entry_id
AND elm_type.base_element_name LIKE 'Fixe amount - VARIP%'
AND elm_type_tl.element_type_id = elm.element_type_id
AND elm_type_tl.language = 'US'
AND piv.input_value_id = elm_val.input_value_id
AND elm_val.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.uom = 'M'
) AS "Profit sharing IP",
(
SELECT
SUM(elm_val.screen_entry_value)
FROM
pay_element_entries_f elm,
pay_element_entry_values_f elm_val,
pay_element_types_f elm_type,
pay_element_types_tl elm_type_tl,
pay_input_values_vl piv,
pay_entry_usages peu,
pay_rel_groups_dn pasg
WHERE
elm.person_id = assign.person_id
AND elm.element_entry_id = elm_val.element_entry_id
AND assign.effective_start_date BETWEEN elm_val.effective_start_date AND elm_val.effective_end_date
AND assign.effective_start_date BETWEEN elm.effective_start_date AND elm.effective_end_date
AND elm_type.element_type_id = elm.element_type_id
AND assign.effective_start_date BETWEEN elm_type.effective_start_date AND elm_type.effective_end_date
and peu.payroll_assignment_id = pasg.relationship_group_id
and pasg.assignment_id = assign.assignment_id
and elm.element_entry_id = peu.element_entry_id
AND elm_type.base_element_name LIKE 'Fixe amount - VARSTI%'
AND elm_type_tl.element_type_id = elm.element_type_id
AND elm_type_tl.language = 'US'
AND piv.input_value_id = elm_val.input_value_id
AND elm_val.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.uom = 'M'
) AS "STI Short term incentive",
(
SELECT sa.ANNUAL_SALARY
FROM CMP_SALARY sa
WHERE sa.assignment_id = assign.assignment_id
AND calendar_param.at_date BETWEEN sa.date_from AND sa.date_to
FETCH FIRST 1 ROWS ONLY
) AS annual_salary,
(
SELECT sa.ANNUAL_ROUNDING_CODE
FROM CMP_SALARY sa
WHERE sa.assignment_id = assign.assignment_id
AND calendar_param.at_date BETWEEN sa.date_from AND sa.date_to
FETCH FIRST 1 ROWS ONLY
) AS annual_rounding_code,

(
SELECT sa.ANNUAL_FT_SALARY
FROM CMP_SALARY sa
WHERE sa.assignment_id = assign.assignment_id
AND calendar_param.at_date BETWEEN sa.date_from AND sa.date_to
FETCH FIRST 1 ROWS ONLY
) AS annual_ft_salary,

(
SELECT
sa.currency_code
FROM
cmp_salary sa
WHERE
sa.assignment_id = assign.assignment_id
AND calendar_param.at_date BETWEEN sa.date_from AND sa.date_to
) AS "Currency",
contract.end_reason AS "Contract end reason",
-- HRBP info
hrbp1.display_name AS hrbp_tier_1,
hrbp2.display_name AS hrbp_tier_2,
assign.ass_attribute6 AS payroll_officer,
to_char(person.date_of_birth, 'YYYY-MM-DD') AS date_of_birth,
TRUNC(MONTHS_BETWEEN(trunc(SYSDATE), person.date_of_birth) / 12) as age,
TRUNC(MONTHS_BETWEEN(trunc(SYSDATE), periodofserv.date_start) / 12) as years_of_service,
NVL(( SELECT DISTINCT pleg.sex
FROM per_people_legislative_f pleg
WHERE pleg.person_id = assign.person_id
AND trunc(SYSDATE) BETWEEN pleg.effective_start_date AND pleg.effective_end_date
AND pleg.sex <> ' '), ' ') sex,
CASE
WHEN assign.employee_category IN ( 'BC', 'FR_07', 'SOC_GSE_IT_OPERAIO' ) THEN
'Blue'
ELSE
'White'
END AS white_blue,
MEAS.value as calculated_FTE,
position.fte,
assign.effective_start_date,
assign.effective_end_date,
assign.organization_id,
to_char(periodofserv.date_start, 'YYYY-MM-DD') AS start_date,
to_char(contract.effective_start_date, 'YYYY-MM-DD') AS contract_start_date,
nullif(to_char(contract.effective_end_date, 'YYYY-MM-DD'),'4712-12-31') AS termination_effective_date,
nullif(to_char(contract.contract_end_date, 'YYYY-MM-DD'),'4712-12-31') AS expected_end_date,
to_char(assign.ASS_ATTRIBUTE_DATE1, 'YYYY-MM-DD') AS payroll_seniority_date,
assign.ASS_ATTRIBUTE10 AS scatti_grade,
assign.ASS_ATTRIBUTE12 AS classification

FROM
per_assignment_secured_list_v /PER_ALL_ASSIGNMENTS_M/ assign
INNER JOIN calendar_param ON 1 = 1
INNER JOIN per_all_people_f people ON assign.person_id = people.person_id
AND calendar_param.at_date BETWEEN people.effective_start_date AND people.effective_end_date
INNER JOIN per_persons person ON assign.person_id = person.person_id
LEFT OUTER JOIN per_person_names_f_v name ON assign.person_id = name.person_id
AND calendar_param.at_date BETWEEN name.effective_start_date AND name.effective_end_date
AND name.name_type = 'GLOBAL'
LEFT OUTER JOIN per_jobs_f_vl jobcd ON jobcd.job_id = assign.job_id
AND calendar_param.at_date BETWEEN jobcd.EFFECTIVE_START_DATE AND jobcd.EFFECTIVE_END_DATE
LEFT OUTER JOIN per_job_family_f jobfam ON jobfam.job_family_id = jobcd.job_family_id
AND calendar_param.at_date BETWEEN jobfam.effective_start_date AND jobfam.effective_end_date
LEFT OUTER JOIN per_job_family_f_tl jobfamtl ON jobfamtl.job_family_id = jobcd.job_family_id
AND calendar_param.at_date BETWEEN jobfamtl.effective_start_date AND jobfamtl
.effective_end_date
AND jobfamtl.language = 'US'
LEFT OUTER JOIN fnd_lookup_values_tl lookup_name ON lookup_name.lookup_type = 'TITLE'
AND name.title = lookup_name.lookup_code
AND lookup_name.language = lookup_name.source_lang
AND lookup_name.language = 'US'
LEFT OUTER JOIN hr_all_positions_f position ON assign.position_id = position.position_id
AND calendar_param.at_date BETWEEN position.effective_start_date AND position.
effective_end_date
LEFT OUTER JOIN fnd_flex_values_vl position_cc_value ON TO_CHAR(position.cost_center) = TO_CHAR(position_cc_value.flex_value_id
)
LEFT OUTER JOIN fnd_flex_values_vl position_local_cc_value ON TO_CHAR(position.attribute1) = TO_CHAR(position_local_cc_value.
flex_value)
AND position_local_cc_value.value_category = 'SOC_LOCAL_COST_CENTER'
LEFT OUTER JOIN fnd_flex_values_tl position_local_cc_description ON TO_CHAR(position_local_cc_description.flex_value_id) = TO_CHAR
(position_local_cc_value.flex_value_id)
AND position_local_cc_description.language = position_local_cc_description
.source_lang
AND position_local_cc_description.language = 'US'
LEFT OUTER JOIN fnd_flex_values_tl position_cc_description ON TO_CHAR(position_cc_description.flex_value_id) = TO_CHAR(position_cc_value
.flex_value_id)
AND position_cc_description.language = position_cc_description.
source_lang
AND position_cc_description.language = 'US'
LEFT OUTER JOIN hr_all_positions_f_tl position_tl ON position.position_id = position_tl.position_id
AND position_tl.language = 'US'
AND position_tl.language = position_tl.source_lang
AND calendar_param.at_date BETWEEN position_tl.effective_start_date AND position_tl.effective_end_date
LEFT OUTER JOIN hr_all_positions_f_tl position_local ON position_local.position_id = assign.position_id
AND position_local.language = 'F'
AND calendar_param.at_date BETWEEN position_local.effective_start_date AND position_local.effective_end_date
LEFT OUTER JOIN per_contracts_f contract ON contract.contract_id = assign.contract_id and calendar_param.at_date between contract.EFFECTIVE_START_DATE and contract.EFFECTIVE_END_DATE
LEFT OUTER JOIN hcm_lookups contract_lookup ON contract_lookup.lookup_type = 'CONTRACT_TYPE'
AND contract_lookup.lookup_code = contract.type
LEFT OUTER JOIN hcm_lookups emp_cat_lookup ON emp_cat_lookup.lookup_type = 'EMPLOYEE_CATG'
AND emp_cat_lookup.lookup_code = assign.employee_category
LEFT OUTER JOIN PER_ASSIGN_WORK_MEASURES_F MEAS ON assign.assignment_id = MEAS.assignment_id AND calendar_param.at_date between MEAS.EFFECTIVE_START_DATE and MEAS.EFFECTIVE_END_DATE AND MEAS.UNIT = 'FTE'
INNER JOIN per_periods_of_service periodofserv ON assign.person_id = periodofserv.person_id
AND assign.period_of_service_id = periodofserv.period_of_service_id
AND periodofserv.primary_flag = 'Y'

-- manager info
LEFT OUTER JOIN per_assignment_supervisors_f asg_sup ON asg_sup.assignment_id = assign.assignment_id
AND asg_sup.manager_type = 'LINE_MANAGER'
AND asg_sup.primary_flag = 'Y'
AND calendar_param.at_date BETWEEN asg_sup.effective_start_date AND asg_sup
.effective_end_date
LEFT OUTER JOIN per_person_names_f_v sup_name ON asg_sup.manager_id = sup_name.person_id
AND calendar_param.at_date BETWEEN sup_name.effective_start_date AND sup_name
.effective_end_date
LEFT OUTER JOIN per_all_people_f sup_details ON asg_sup.manager_id = sup_details.person_id
AND calendar_param.at_date BETWEEN sup_details.effective_start_date AND sup_details
.effective_end_date
LEFT OUTER JOIN per_all_assignments_m sup_asg2 ON asg_sup.manager_assignment_id = sup_asg2.assignment_id
AND calendar_param.at_date BETWEEN sup_asg2.effective_start_date AND sup_asg2
.effective_end_date
LEFT OUTER JOIN hr_all_positions_f sup_position ON sup_asg2.position_id = sup_position.position_id
AND asg_sup.effective_start_date BETWEEN sup_position.effective_start_date
AND sup_position.effective_end_date
--external ids
LEFT OUTER JOIN per_ext_app_identifiers trigram ON trigram.person_id = assign.person_id
AND calendar_param.at_date BETWEEN trigram.date_from AND nvl(trigram.date_to
, TO_DATE('31/12/4712', 'DD/MM/YYYY'))
AND trigram.ext_identifier_type = 'SOC_TRIGRAM'
LEFT OUTER JOIN per_ext_app_identifiers extid ON extid.person_id = assign.person_id
AND calendar_param.at_date BETWEEN extid.date_from AND nvl(extid.date_to, TO_DATE
('31/12/4712', 'DD/MM/YYYY'))
AND extid.ext_identifier_type = 'ORA_3RD_PARTY_PAY_ID'
--orga info
INNER JOIN per_department_secured_list_v orgun2 ON orgun2.organization_id = COALESCE(position.organization_id, assign.organization_id)
AND calendar_param.at_date BETWEEN orgun2.effective_start_date AND orgun2.effective_end_date
LEFT OUTER JOIN hr_organization_units_f_tl orgun ON orgun.organization_id = orgun2.organization_id
AND calendar_param.at_date BETWEEN orgun.effective_start_date AND orgun.effective_end_date
AND orgun.language = 'US'
LEFT OUTER JOIN hr_organization_units_f_tl orguntl ON orguntl.organization_id = assign.business_unit_id
AND calendar_param.at_date BETWEEN orguntl.effective_start_date AND orguntl.effective_end_date
AND orguntl.language = 'US'
LEFT OUTER JOIN hr_organization_units_f_tl orgestbtl ON orgestbtl.organization_id = assign.establishment_id
AND calendar_param.at_date BETWEEN orgestbtl.effective_start_date AND
orgestbtl.effective_end_date
AND orgestbtl.language = 'US'
LEFT OUTER JOIN per_legal_employers xle ON assign.legal_entity_id = xle.organization_id
AND calendar_param.at_date BETWEEN xle.effective_start_date AND xle.effective_end_date
LEFT OUTER JOIN per_location_details_f loc_details ON assign.location_id = loc_details.location_id
AND calendar_param.at_date BETWEEN loc_details.effective_start_date AND
loc_details.effective_end_date
LEFT OUTER JOIN per_location_details_f_tl loc_tr ON loc_tr.location_details_id = loc_details.location_details_id
AND calendar_param.at_date BETWEEN loc_tr.effective_start_date AND loc_tr.effective_end_date
AND loc_tr.language = 'US'
AND loc_tr.language = loc_tr.source_lang
LEFT OUTER JOIN orga ON orga.pk1_value = COALESCE(position.organization_id, assign.organization_id)
LEFT OUTER JOIN perimeter_4 ON perimeter_4.pk1_value = COALESCE(position.organization_id, assign.organization_id)
LEFT OUTER JOIN per_locations loc ON assign.location_id = loc.location_id
LEFT OUTER JOIN fnd_territories_tl loc_tl ON loc_tl.territory_code = loc.country
AND loc_tl.language = loc_tl.source_lang
AND loc_tl.language = 'US'
-- hrbp
LEFT OUTER JOIN hrbp1 ON hrbp1.country = loc.country
AND perimeter_4.perimeter4_code LIKE '%'
|| TO_CHAR(hrbp1.top_organization_id)
|| '%'
AND hrbp1.row_num = 1
LEFT OUTER JOIN hrbp2 ON hrbp2.country = loc.country
AND perimeter_4.perimeter4_code LIKE '%'
|| TO_CHAR(hrbp2.top_organization_id)
|| '%'
AND hrbp2.row_num = 1
WHERE
assign.assignment_type IN (
'E',
'C'
)
AND assign.primary_flag = 'Y'
AND assign.assignment_status_type = 'ACTIVE'
-- Prompts
AND calendar_param.at_date BETWEEN assign.effective_start_date AND assign.effective_end_date
AND ( coalesce(:location, NULL) IS NULL
OR loc_tr.location_name IN (
:location
) )
AND ( coalesce(:country, NULL) IS NULL
OR loc_tl.territory_short_name IN (
:country
) )
ORDER BY
people.person_number,
assign.effective_start_date DESC