[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT 'x'
INTO l_dummy
FROM HR_ORGANIZATION_UNITS
WHERE organization_id = organization_id1 AND
ROWNUM = 1;
select ppp.proposed_salary,
ppb.pay_basis
from per_pay_proposals ppp,
per_assignments_f paf,
per_pay_bases ppb
where paf.assignment_id = p_assignment_id and
ppp.assignment_id = paf.assignment_id and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.pay_basis in ('ANNUAL', 'MONTHLY') and
ppp.change_date = (select max(change_date) from per_pay_proposals ppp1
where ppp1.assignment_id = paf.assignment_id and
ppp1.approved = 'Y' and ppp1.change_date <= p_session_date);
SELECT ppp.proposed_salary,
ppb.pay_basis
FROM per_pay_proposals ppp,
per_assignments_f paf,
per_pay_bases ppb
WHERE paf.assignment_id = p_assignment_id
AND ppp.assignment_id = paf.assignment_id
AND paf.pay_basis_id = ppb.pay_basis_id
AND ppb.pay_basis IN ('ANNUAL', 'MONTHLY')
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = p_assignment_id
AND ppp1.approved = 'Y'
AND ppp1.change_date <= p_session_date);
SELECT gl_account_segment,payroll_cost_segment
FROM PAY_PAYROLL_GL_FLEX_MAPS
WHERE payroll_id = p_payroll_id and
gl_set_of_books_id = p_set_of_books_id;
SELECT chart_of_accounts_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = p_set_of_books_id;
dbms_sql.parse(l_cursor,'select ' || l_cost_segment || ' from pay_cost_allocation_keyflex where cost_allocation_keyflex_id = :p_cost_keyflex_id',dbms_sql.V7);
dbms_sql.parse(l_cursor,'select code_combination_id from gl_code_combinations where chart_of_accounts_id = :p_chart_of_accounts_id ' || l_sql_string,dbms_sql.V7);
SELECT SUM(DECODE(TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1))
FROM DUAL
CONNECT BY 1=1
AND ROWNUM <= (p_high_date + 1) - p_low_date;
SELECT DECODE(TO_CHAR(p_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1)
FROM DUAL;
SELECT SUM(DECODE(hruserdt.get_table_value(l_business_group_id, l_ws_table_name, l_work_schedule,
TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), p_low_date+ (ROWNUM-1)), 0, 0, 1))
FROM DUAL
CONNECT BY 1=1
AND ROWNUM <= (p_high_date + 1) - p_low_date;
SELECT pcv_information1 work_schedules
FROM pqp_configuration_values
WHERE pcv_information_category = 'PSP_ENABLE_WORK_SCHEDULES'
AND legislation_code IS NULL
AND NVL(business_group_id, l_business_group_id) = l_business_group_id;
SELECT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date <= high_date
AND effective_end_date >= low_date;
SELECT legislation_code
FROM per_business_groups_perf
WHERE business_group_id = l_business_group_id;
SELECT put.user_table_name,
puc.user_column_name,
GREATEST(assign.effective_start_date, low_date),
LEAST(assign.effective_end_date, high_date)
FROM pay_user_tables PUT,
pay_user_columns PUC,
hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN
WHERE PUC.USER_COLUMN_ID (+) = target.SEGMENT4
AND high_date >= ASSIGN.effective_start_date
AND low_date <= ASSIGN.effective_end_date
AND ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND target.id_flex_num = (SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = l_legislation_code
AND rule_type = 'S')
AND NVL(PUC.business_group_id, l_business_group_id) = l_business_group_id
AND NVL(PUC.legislation_code, l_legislation_code) = l_legislation_code
AND PUC.user_table_id = PUT.user_table_id (+)
AND ( PUT.user_table_id IS NULL
OR PUT.user_table_name = (SELECT put.user_table_name
FROM hr_organization_information hoi,
pay_user_tables put
WHERE hoi.organization_id = l_business_group_id
AND hoi.org_information_context ='Work Schedule'
AND hoi.org_information1 = put.user_table_id));
SELECT 'x'
INTO t_dummy
FROM gl_code_combinations
WHERE code_combination_id = code_combination_id1 AND
chart_of_accounts_id = chart_of_accounts_id1 AND
ROWNUM = 1;
SELECT organization_id, organization_account_id,rownum
FROM psp_organization_accounts
WHERE account_type_code = 'G' AND
business_group_id = p_business_group_id AND
set_of_books_id = p_set_of_books_id AND
(p_start_date_active IS NULL OR
p_start_date_active BETWEEN start_date_active AND end_date_active);
SELECT chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = c_set_of_books_id;
SELECT purgeable_flag INTO v_flag
FROM pa_transaction_sources
WHERE transaction_source = 'GOLDE'
FOR UPDATE OF purgeable_flag ;
UPDATE pa_transaction_sources
SET purgeable_flag = 'N' --- v_flag
WHERE transaction_source = 'GOLDE';
SELECT purgeable_flag INTO v_flag
FROM pa_transaction_sources
WHERE transaction_source = 'GOLD'
FOR UPDATE OF purgeable_flag ;
UPDATE pa_transaction_sources
SET purgeable_flag = 'N' ---v_flag
WHERE transaction_source = 'GOLD';
SELECT nvl(completion_date,p_payroll_end_date)
INTO l_project_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT nvl(end_date_active,p_payroll_end_date)
INTO l_award_end_date
FROM gms_awards_all -- Bug 6908158
WHERE award_id = p_award_id;
SELECT nvl(completion_date,p_payroll_end_date)
INTO l_completion_date
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT least(p_payroll_end_date,l_project_end_date,l_award_end_date,l_completion_date)
INTO l_poeta_effective_date
FROM dual;
SELECT nvl(completion_date,p_payroll_end_date)
INTO l_project_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT nvl(completion_date,p_payroll_end_date)
INTO l_completion_date
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT least(p_payroll_end_date,l_project_end_date,l_completion_date)
INTO l_poeta_effective_date
FROM dual;
SELECT count(*)
FROM per_all_assignments_f ainner,
per_assignment_status_types binner
WHERE ainner.person_id = p_person_id
AND ainner.primary_flag = 'Y'
AND ainner.assignment_status_type_id= binner.assignment_status_type_id
AND binner.per_system_status = 'ACTIVE_ASSIGN'
AND p_effective_date between ainner.effective_start_date and ainner.effective_end_date
AND ainner.period_of_service_id IS NOT NULL;
SELECT max(a.effective_end_date)
FROM per_all_assignments_f a,
per_assignment_status_types b
WHERE a.person_id = p_person_id
AND a.primary_flag = 'Y'
AND a.assignment_status_type_id = b.assignment_status_type_id
AND b.per_system_status = 'ACTIVE_ASSIGN'
AND a.period_of_service_id IS NOT NULL -- Included for the Bug fix 2039161
AND (trunc(a.effective_end_date) <= trunc(p_effective_date));
select count(*)
into l_pa_install
from pa_implementations_all p
where business_group_id = p_business_group_id
and set_of_books_id = p_gl_set_of_bks_id
-- Commented for Bug 5498280: MOAC changes
-- and nvl(org_id,-999) = nvl(p_operating_unit,-999);
SELECT count(*)
INTO l_clearing_account
FROM psp_clearing_account
WHERE business_group_id = p_business_group_id;
SELECT count(*)
INTO l_gen_susp_acct
FROM psp_organization_accounts
WHERE business_group_id = p_business_group_id;
select nvl(preaward_date,start_date_active), end_date_active
into
x_award_start_date, x_award_end_date
from gms_awards_all --Bug 6908158
where award_id = p_award_id;
SELECT 'x'
INTO t_dummy
FROM gl_code_combinations
WHERE code_combination_id = code_combination_id1 AND
chart_of_accounts_id = chart_of_accounts_id1 AND
ROWNUM = 1;
SELECT full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT assignment_number
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND period_of_service_id IS NOT NULL;
SELECT payroll_name
FROM pay_all_payrolls_f pap
WHERE pap.payroll_id = p_payroll_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(SUM(schedule_percent), 0)
FROM per_assignments_f paf,
psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE paf.person_id = p_person_id
AND TRUNC(p_effective_date) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND psh.assignment_id = paf.assignment_id
AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND psl.award_id = p_award_id
AND TRUNC(p_effective_date) BETWEEN psl.schedule_begin_date AND psl.schedule_end_date;
SELECT segment1
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_number
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT award_number
FROM gms_awards_all
WHERE award_id = p_award_id;
select default_dist_award_number
from gms_implementations
where award_distribution_option = 'Y'
and default_dist_award_id = p_award_id;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT period_name
FROM per_time_periods
WHERE time_period_id = p_period_id;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND ( (TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date)
OR (effective_start_date = (SELECT MIN(effective_start_date)
FROM pay_element_types_f petf2
WHERE petf2.element_type_id = p_element_type_id)));
SELECT description
FROM psp_payroll_sources
WHERE source_type = p_source_type
AND source_code = p_source_code;
SELECT MEANING
FROM PSP_LOOKUPS
WHERE lookup_code = p_status_code
AND lookup_type = 'PSP_STATUS';
SELECT meaning
FROM FND_LOOKUP_VALUES FLV
WHERE lookup_type = 'PSP_STATUS'
AND lookup_code = p_status_code
AND language = USERENV('LANG');
SELECT MEANING
FROM PSP_LOOKUPS
WHERE lookup_code = p_error_code
AND lookup_type = 'PSP_ERROR_CODE';
SELECT meaning
FROM FND_LOOKUP_VALUES FLV
WHERE lookup_type = 'PSP_ERROR_CODE'
AND lookup_code = p_error_code
AND language = USERENV('LANG');
CURSOR get_curr_for_bg is SELECT currency_code from per_business_groups where
business_group_id=p_business_group_id;
SELECT currency_code
FROM psp_payroll_controls ppc
WHERE ppc.payroll_control_id = p_payroll_control_id;
PSP: Enable Update Encumbrance, as the profile,
PSP: Enable Update Encumbrance will be obsoleted by end dating it to '01-jan-2003'.
The call to the profile PSP: Enable Update Encumbrance , in all the files except
GMS.pll will be removed and this new function START_CAPTURING_UPDATES will
instead called in its place
Date of Creation: 23-Jul-2003
Bug :3075435 Dynamic trigger IMplementation.
**********************************************************************************************/
FUNCTION START_CAPTURING_UPDATES(p_business_group_id IN NUMBER) RETURN VARCHAR2 IS
CURSOR update_enc_cur IS
SELECT 'Y'
FROM psp_enc_end_dates
WHERE default_org_flag = 'Y'
AND business_group_id = p_business_group_id
AND prev_enc_end_date IS NOT NULL;
l_start_capturing_updates VARCHAR2(2);
OPEN update_enc_cur;
FETCH update_enc_cur INTO l_start_capturing_updates;
IF (update_enc_cur%NOTFOUND) THEN
l_start_capturing_updates := 'N';
CLOSE update_enc_cur;
RETURN l_start_capturing_updates;
End START_CAPTURING_UPDATES;
CURSOR chk_insert(p_table_owner varchar2) IS
SELECT 1
FROM all_tab_columns
WHERE table_name = 'PA_TRANSACTION_INTERFACE_ALL'
AND column_name = 'PERSON_BUSINESS_GROUP_ID'
AND owner = p_table_owner; -- bug 3871687
OPEN chk_insert(p_table_owner);
FETCH chk_insert into l_pa_bg_id;
IF chk_insert%NOTFOUND THEN
CLOSE chk_insert;
CLOSE chk_insert;
select status
from fnd_product_installations
where application_id = PSP_APPLICATION_ID;
SELECT 1 hierarchy, PCV_INFORMATION1
FROM pqp_configuration_values pcv
WHERE pcv.business_group_id = p_business_group_id
AND pcv_information_category = 'PSP_ACT_DFF_GROUPING'
UNION ALL
SELECT 2 hierarchy, PCV_INFORMATION1
FROM pqp_configuration_values pcv
WHERE pcv.business_group_id IS NULL
AND pcv_information_category = 'PSP_ACT_DFF_GROUPING'
ORDER BY 1;
SELECT 1 hierarchy, PCV_INFORMATION1
FROM pqp_configuration_values pcv
WHERE pcv.business_group_id = p_business_group_id
AND pcv_information_category = 'PSP_ENC_DFF_GROUPING'
UNION ALL
SELECT 2 hierarchy, PCV_INFORMATION1
FROM pqp_configuration_values pcv
WHERE pcv.business_group_id IS NULL
AND pcv_information_category = 'PSP_ENC_DFF_GROUPING'
ORDER BY 1;
SELECT sponsored_flag
FROM pa_projects_all ppa,
gms_project_types gpt -- Changed from gms_project_types_all for P1 bug 4078481
WHERE gpt.project_type = ppa.project_type
AND ppa.project_type <> 'AWARD_PROJECT'
AND ppa.project_id = p_project_id;
select max(ppf.effective_end_date),min(ppf.effective_start_date)
from per_people_f ppf
where ppf.current_employee_flag = 'Y'
and ppf.person_id = p_person_id
group by ppf.person_id ;
select full_name from per_people_f
where person_id = p_person_id
and p_calculated_date between effective_start_date and effective_end_date;
select max(ppf.effective_end_date),min(ppf.effective_start_date)
from per_people_f ppf
where ppf.current_employee_flag = 'Y'
and ppf.person_id = p_person_id
group by ppf.person_id ;
select max(papf.effective_end_date),min(papf.effective_start_date)
from pay_all_payrolls_f papf
WHERE papf.payroll_id = p_payroll_id;
select max(papf.effective_end_date),min(papf.effective_start_date)
from pay_all_payrolls_f papf
WHERE papf.payroll_id = p_payroll_id;
select payroll_name
from pay_all_payrolls_f papf
WHERE papf.payroll_id = p_payroll_id
and p_calculated_date between papf.effective_start_date and papf.effective_end_date;
select max(hpf.effective_end_date),min(hpf.effective_start_date)
from hr_positions_f hpf
WHERE hpf.position_id = p_position_id;
select max(hpf.effective_end_date),min(hpf.effective_start_date)
from hr_positions_f hpf
WHERE hpf.position_id = p_position_id ;
select name
from hr_positions_f hpf
WHERE hpf.position_id = p_position_id
and p_calculated_date between hpf.effective_start_date and hpf.effective_end_date;
select max(ff.effective_end_date),min(ff.effective_start_date)
from ff_formulas_f ff
WHERE ff.formula_id = p_formula_id;
select max(ff.effective_end_date),min(ff.effective_start_date)
from ff_formulas_f ff
WHERE ff.formula_id = p_formula_id;
select formula_name
from ff_formulas_f ff
WHERE ff.formula_id = p_formula_id
and p_calculated_date between ff.effective_start_date and ff.effective_end_date;
select max(ff.effective_end_date),min(ff.effective_start_date)
from ff_formulas_f ff
WHERE ff.formula_id = p_formula_id;
select description
from ff_formulas_f ff
WHERE ff.formula_id = p_formula_id
and p_calculated_date between ff.effective_start_date and ff.effective_end_date;
select max(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(pjv.date_from)
from per_jobs_v pjv
WHERE pjv.job_id = p_job_id;
select max(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(pjv.date_from)
from per_jobs_v pjv
WHERE pjv.job_id = p_job_id;
select name
from per_jobs_v pjv
WHERE pjv.job_id = p_job_id
and p_calculated_date between pjv.date_from and trunc(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR')));
select max(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(hou.date_from)
from hr_organization_units hou
WHERE hou.organization_id = p_org_id;
select max(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(hou.date_from)
from hr_organization_units hou
WHERE hou.organization_id = p_org_id;
select name
from hr_organization_units hou
WHERE hou.organization_id = p_org_id
and p_calculated_date between hou.date_from and trunc(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR')));
select person_id
from psp_eff_reports
where effort_report_id in
(select effort_report_id
from psp_eff_report_details
where effort_report_detail_id = substr(txn_id, 36,15));
select nvl(current_employee_flag,'N')
from per_all_people_f
where person_id = get_person_id(txn_id)
and sysdate between effective_start_date and effective_end_date;
select count(*) into userid_count
from fnd_user
where employee_id = get_person_id(txn_id)
and trunc(sysdate) between start_date and nvl(end_date,sysdate);
INSERT INTO psp_report_errors
(error_sequence_id, request_id, message_level,
source_id, error_message, payroll_action_id,
source_name, parent_source_id, parent_source_name,
value1, value2, value3, value4, value5,
value6, value7, value8, value9, value10,
information1, information2, information3, information4, information5,
information6, information7, information8, information9, information10)
VALUES
(psp_report_errors_s.NEXTVAL, p_request_id, p_message_level,
p_source_id, p_error_message, p_payroll_action_id,
p_source_name, p_parent_source_id, p_parent_source_name,
p_value1, p_value2, p_value3, p_value4, p_value5,
p_value6, p_value7, p_value8, p_value9, p_value10,
p_information1, p_information2, p_information3, p_information4, p_information5,
p_information6, p_information7, p_information8, p_information9, p_information10);
INSERT INTO psp_report_errors
(error_sequence_id, request_id, message_level, source_id,
retry_request_id, pdf_request_id, error_message)
VALUES
(psp_report_errors_s.NEXTVAL, p_request_id, p_message_level, p_source_id,
p_retry_request_id, p_pdf_request_id, p_error_message);
SELECT NVL(MAX(PHASE),0) INTO l_curr_er_phase FROM PSP_UPGRADE_115 WHERE STATUS ='R' ;
SELECT distinct full_name FROM psp_eff_reports
WHERE request_id = p_request_id;
SELECT DISTINCT per.full_name
FROM psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals pera
WHERE pera.wf_item_key = p_wf_item_key
AND pera.effort_report_detail_id = perd.effort_report_detail_id
AND perd.effort_report_id = per.effort_report_id;
select 1 hierarchy, PCV_INFORMATION1 , pcv_information2
from pqp_configuration_values
where PCV_INFORMATION_CATEGORY = p_PCV_INFORMATION_CATEGORY
and BUSINESS_GROUP_ID = p_business_group_id
and (p_pcv_information1 is null
or p_pcv_information1 = pcv_information1)
UNION ALL
select 2 hierarchy, PCV_INFORMATION1 , pcv_information2
from pqp_configuration_values
where PCV_INFORMATION_CATEGORY = p_PCV_INFORMATION_CATEGORY
and BUSINESS_GROUP_ID IS NULL
and (p_pcv_information1 is null
or p_pcv_information1 = pcv_information1)
ORDER BY 1;
select 1 hierarchy, PCV_INFORMATION1,PCV_INFORMATION2,PCV_INFORMATION3,PCV_INFORMATION4,PCV_INFORMATION5
from pqp_configuration_values
where PCV_INFORMATION_CATEGORY = 'PSP_GL_PTAOE_MAPPING'
and BUSINESS_GROUP_ID = p_business_group_id
UNION ALL
select 2 hierarchy, PCV_INFORMATION1,PCV_INFORMATION2,PCV_INFORMATION3,PCV_INFORMATION4,PCV_INFORMATION5
from pqp_configuration_values
where PCV_INFORMATION_CATEGORY = 'PSP_GL_PTAOE_MAPPING'
and BUSINESS_GROUP_ID is null
ORDER BY 1;
select 'Y'
from psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals pera
where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
and perd.EFFORT_REPORT_DETAIL_ID = pera.EFFORT_REPORT_DETAIL_ID
AND per.STATUS_CODE = p_status_code
and WF_ITEM_KEY = p_wf_itrm_key;
select 'Y'
from psp_eff_reports per,
(select person_id ,STATUS_CODE, per.Start_date,per.end_date,per.EFFORT_REPORT_ID
from psp_eff_reports per,
psp_eff_report_details perd,
psp_eff_report_approvals pera
where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
and perd.EFFORT_REPORT_DETAIL_ID = pera.EFFORT_REPORT_DETAIL_ID
AND per.STATUS_CODE = p_status_code
and pera.WF_ITEM_KEY = p_wf_itrm_key) temp
where per.person_id = temp.person_id
and per.start_date = temp.start_date
and per.end_date = temp.end_date
AND per.EFFORT_REPORT_ID > temp.EFFORT_REPORT_ID;
select max(paf.effective_end_date)
from per_assignments_f paf
where paf.assignment_id = p_assignment_id;
select past.USER_STATUS
from per_assignment_status_types past ,
per_assignments_f paf
where paf.assignment_id = p_assignment_id
and past.ASSIGNMENT_STATUS_TYPE_ID = paf.ASSIGNMENT_STATUS_TYPE_ID
and p_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT org_id
INTO l_org_id
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND organization_id = p_expenditure_organization_id
AND rownum=1;
SELECT org_id
INTO l_org_id
FROM pa_projects_all
WHERE project_id = p_project_Id;