[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* deleted code for get_clearing_ccid, bug 2007521 */
Procedure get_suspense_account(p_organization_id in number,
p_organization_name in varchar2,
p_effective_date in date,
p_gms_pa_install in varchar2,
p_person_id in number,
p_business_group_id in number,
p_set_of_books_id in number,
p_distribution_interface_id in number,
x_suspense_account out NOCOPY number,
x_return_status out NOCOPY varchar2,
x_suspense_auto_glccid out nocopy number,
x_suspense_auto_exp_type out nocopy varchar2);
Procedure update_record_with_error(X_distribution_interface_id IN Number,
X_error_code IN Varchar2,
X_return_status OUT NOCOPY Varchar2);
Procedure update_record_with_valid(X_distribution_interface_id IN Number,
X_return_status OUT NOCOPY varchar2);
Procedure update_record_with_exp(X_distribution_interface_id IN Number,
X_expenditure_type IN Varchar2,
X_return_status OUT NOCOPY Varchar2);
Procedure update_record_with_na(X_distribution_interface_id IN Number,
X_gl_code_combination_id IN Number,
X_return_status OUT NOCOPY Varchar2);
SELECT *
FROM psp_distribution_interface
WHERE batch_name = p_batch_name and
status_code <> 'V' FOR UPDATE;
SELECT distribution_interface_id
FROM psp_distribution_interface
WHERE batch_name = p_batch_name
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
FOR UPDATE OF distribution_interface_id NOWAIT;
SELECT *
FROM psp_distribution_interface
WHERE batch_name = p_batch_name
ORDER BY source_code,time_period_id;
/* deleted cursors get_count_for_gl_csr, get_count_for_project_csr Bug 2007521 */
CURSOR get_batch_name_csr is
SELECT count(*)
FROM psp_payroll_controls
WHERE source_type = 'P' and
batch_name = p_batch_name and
business_group_id = p_business_group_id and
set_of_books_id = p_set_of_books_id;
SELECT COUNT(DISTINCT NVL(currency_code, 'bg_currency'))
FROM psp_distribution_interface
WHERE batch_name = p_batch_name
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id;
SELECT currency_code
FROM psp_distribution_interface
WHERE batch_name = p_batch_name
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND currency_code IS NOT NULL
AND ROWNUM = 1;
SELECT end_date
FROM per_time_periods ptp
WHERE ptp.time_period_id = p_time_period_id;
SELECT currency_code
FROM gl_sets_of_books gsob
WHERE set_of_books_id = p_set_of_books_id;
select pcv_information7 suspense,
pcv_information9 pregen
from pqp_configuration_values
where pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
and legislation_code is null
and nvl(business_group_id, p_business_group_id) = p_business_group_id;
select count(*) into l_count_status_v from psp_distribution_interface
where batch_name=p_batch_name and status_code='V';
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_valid(X_distribution_interface_id=>
g_pregen_rec.distribution_interface_id,
X_return_status => l_return_status);
update_record_with_error(X_distribution_interface_id=>
g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => l_return_status);
update_record_with_valid(X_distribution_interface_id=> g_pregen_rec.distribution_interface_id,
X_return_status => l_return_status);
update_record_with_valid(X_distribution_interface_id=> g_pregen_rec.distribution_interface_id,
X_return_status => l_return_status);
/* 2007521: Introduced update statement, to revert sticking suspense a/c if
there are some other errors. Give chance to user to correct all errors */
if nvl(g_use_pre_gen_suspense,'N') = 'Y' then
update psp_distribution_interface
set suspense_org_account_id = null,
status_code = 'E'
where batch_name = p_batch_name and
suspense_org_account_id is not null;
INSERT INTO PSP_PRE_GEN_DIST_LINES ( pre_gen_dist_line_id,
distribution_interface_id,
person_id,
assignment_id,
element_type_id,
distribution_date,
effective_date,
distribution_amount,
dr_cr_flag,
payroll_control_id,
source_type,
source_code,
time_period_id,
batch_name,
status_code,
set_of_books_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
suspense_org_account_id,
suspense_reason_code,
effort_report_id,
version_num,
summary_line_id,
reversal_entry_flag,
user_defined_field,
business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
suspense_auto_glccid,
suspense_auto_exp_type)
SELECT psp_distribution_lines_s.nextval,
a.distribution_interface_id,
a.person_id,
a.assignment_id,
a.element_type_id,
a.distribution_date,
--- Replaced dist date with period end date for GL -2876055
decode(nvl(a.gl_code_combination_id, susp.gl_code_combination_id)
, null, a.distribution_date, t.end_date), -- added nvl for 5164744
---a.distribution_date,
ROUND(a.distribution_amount, g_precision), -- Introduced ROUND for bug fix 2651379; Corrected precision for bug fix 2916848
fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data');
/* 2007521: Deleted creation of balancing lines for Projects/GL, S and T does this */
UPDATE psp_distribution_interface
SET status_code = 'T'
WHERE batch_name = p_batch_name;
select psp_payroll_controls_s.nextval into l_control_id from dual;
PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_PAYROLL_CONTROL_ID => l_control_id,
X_PAYROLL_ACTION_ID => 0,
X_PAYROLL_SOURCE_CODE => l_ft_Source_Code,
X_SOURCE_TYPE => 'P',
X_PAYROLL_ID => l_ft_payroll_id,
X_TIME_PERIOD_ID => l_ft_time_period_id,
X_NUMBER_OF_CR => l_ft_number_of_cr,
X_NUMBER_OF_DR => l_ft_number_of_dr,
X_TOTAL_DR_AMOUNT => NULL,
X_TOTAL_CR_AMOUNT => NULL,
X_BATCH_NAME => p_batch_name,
X_SUBLINES_DR_AMOUNT => NULL,
X_SUBLINES_CR_AMOUNT => NULL,
X_DIST_CR_AMOUNT => l_ft_cr_amount,
X_DIST_DR_AMOUNT => l_ft_dr_amount,
X_OGM_DR_AMOUNT => NULL,
X_OGM_CR_AMOUNT => NULL,
X_GL_DR_AMOUNT => NULL,
X_GL_CR_AMOUNT => NULL,
X_STATUS_CODE => 'N',
X_MODE => 'R',
X_GL_POSTING_OVERRIDE_DATE =>g_for_total_rec.gl_posting_override_date ,
X_GMS_POSTING_OVERRIDE_DATE =>g_for_total_rec.gms_posting_override_date,
X_business_group_id => p_business_group_id,
X_set_of_books_id => l_set_of_books_id ,
X_GL_PHASE => NULL,
X_GMS_PHASE => NULL,
X_ADJ_SUM_BATCH_NAME => NULL,
-- Introduced the following for bug fix 2916848
x_currency_code => g_currency_code,
x_exchange_rate_type => l_exchange_rate_type);
fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data in Payroll Controls');
UPDATE psp_pre_gen_dist_lines
SET payroll_control_id = l_control_id
WHERE time_period_id = l_ft_time_period_id and
batch_name = p_batch_name and
source_type = 'P' and
source_code = l_ft_source_code and
set_of_books_id= l_set_of_books_id and
business_group_id = p_business_group_id;
select psp_payroll_controls_s.nextval into l_control_id from dual;
PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_PAYROLL_CONTROL_ID => l_control_id,
X_PAYROLL_ACTION_ID => 0,
X_PAYROLL_SOURCE_CODE => l_ft_Source_Code,
X_SOURCE_TYPE => 'P',
X_PAYROLL_ID => l_ft_payroll_id,
X_TIME_PERIOD_ID => l_ft_time_period_id,
X_NUMBER_OF_CR => l_ft_number_of_cr,
X_NUMBER_OF_DR => l_ft_number_of_dr,
X_TOTAL_DR_AMOUNT => NULL,
X_TOTAL_CR_AMOUNT => NULL,
X_BATCH_NAME => p_batch_name,
X_SUBLINES_DR_AMOUNT => NULL,
X_SUBLINES_CR_AMOUNT => NULL,
X_DIST_CR_AMOUNT => l_ft_cr_amount,
X_DIST_DR_AMOUNT => l_ft_dr_amount,
X_OGM_DR_AMOUNT => NULL,
X_OGM_CR_AMOUNT => NULL,
X_GL_DR_AMOUNT => NULL,
X_GL_CR_AMOUNT => NULL,
X_STATUS_CODE => 'N',
X_MODE => 'R',
X_GL_POSTING_OVERRIDE_DATE => g_for_total_rec.gl_posting_override_date,
X_GMS_POSTING_OVERRIDE_DATE =>g_for_total_rec.gms_posting_override_date ,
X_business_group_id => p_business_group_id,
X_set_of_books_id => l_set_of_books_id,
X_GL_PHASE => NULL,
X_GMS_PHASE => NULL,
X_ADJ_SUM_BATCH_NAME=> NULL,
-- Introduced the following for bug fix 2916848
x_currency_code => g_currency_code,
x_exchange_rate_type => l_exchange_rate_type);
fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data in Payroll Controls');
UPDATE psp_pre_gen_dist_lines
SET payroll_control_id = l_control_id
WHERE time_period_id = l_ft_time_period_id and
batch_name = p_batch_name and
source_type = 'P' and
source_code = l_ft_source_code and
set_of_books_id= l_set_of_books_id and
business_group_id = p_business_group_id;
SELECT a.person_id
FROM Per_People_F a
WHERE a.Person_ID = x_person_id
-- AND a.current_employee_flag ='Y' --Added for bug 2624259. Commented for Bug 3424494
AND (x_effective_date BETWEEN a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE)
AND x_effective_date <= ( SELECT max(b.effective_end_date)
FROM per_assignments_f b,
pay_payrolls_f c
WHERE a.person_id = b.person_id
AND b.business_group_id = x_business_group_id
AND c.payroll_id = b.payroll_id
AND b.assignment_type ='E' --Added for bug 2624259.
AND c.gl_set_of_books_id = X_set_of_books_id);
SELECT ppf.person_id
FROM per_people_f ppf
WHERE ppf.person_id = x_person_id
AND (x_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
AND x_effective_date <= (SELECT MAX(paf.effective_end_date)
FROM per_assignments_f paf,
pay_payrolls_f ppf2
WHERE paf.person_id = x_person_id
AND paf.business_group_id = x_business_group_id
AND ppf2.payroll_id = paf.payroll_id
AND paf.assignment_type ='E'
AND ppf2.gl_set_of_books_id = x_set_of_books_id);
SELECT assignment_id
FROM per_assignments_f a, pay_payrolls_f b
WHERE assignment_id = x_assignment_id
AND a.assignment_type ='E' --Added for bug 2624259.
and a.payroll_id=b.payroll_id
AND x_effective_date between a.effective_start_date and a.effective_end_date
AND x_effective_date between b.effective_start_date and b.effective_end_date
AND a.business_group_id = X_business_group_id
AND b.gl_set_of_books_id = X_set_of_books_id;
SELECT a.payroll_id
FROM pay_payrolls_f a, per_assignments_f b
WHERE a.payroll_id = x_payroll_id
AND x_effective_date between a.effective_start_date and a.effective_end_date
AND a.payroll_id = b.payroll_id
AND b.assignment_id = X_assignment_id
AND (X_effective_date between b.effective_start_date and b.effective_end_date)
AND a.business_group_id = X_business_group_id
AND a.gl_set_of_books_id = X_set_of_books_id;
SELECT Time_Period_id
FROM Per_Time_Periods
WHERE Payroll_id = x_Payroll_ID
and Time_Period_ID = x_Payroll_Period_ID
and (x_Effective_Date between start_date and end_date);
SELECT source_code
FROM PSP_PAYROLL_SOURCES
WHERE source_code = x_Payroll_Source_Code and
source_type = 'P';
SELECT a.element_type_id
FROM psp_element_types a,
per_time_periods c
WHERE a.element_type_id = x_Element_Type_ID and
c.time_period_id = x_payroll_period_id
-- and ((c.start_date between a.start_date_active and a.end_date_active)
-- or (c.end_date between a.start_date_active and a.end_date_active)
-- or ((a.start_date_active < c.start_date) and (a.end_date_active > c.end_date)))
-- Introduced this for bug fix 2916848
AND c.start_date <= a.end_date_active
AND c.end_date >= a.start_date_active
AND EXISTS (SELECT 1
FROM pay_element_types_f pef
WHERE pef.element_type_id = a.element_type_id
AND ( pef.output_currency_code = g_currency_code
OR g_currency_code = 'STAT')
AND pef.effective_end_date >= a.start_date_active
AND pef.effective_start_date <= a.end_date_active)
-- Introduced for bug fix 3098050
AND a.business_group_id = x_business_group_id
AND a.set_of_books_id = x_set_of_books_id;
SELECT project_id
FROM gms_projects_expend_v
where project_id = x_project_id;
SELECT organization_id
FROM pa_organizations_expend_v
WHERE organization_id = x_exp_org_id
AND active_flag = 'Y';
SELECT task_id
FROM pa_tasks_expend_v
WHERE project_id = x_project_id and
task_id = x_task_id;
SELECT award_id
FROM gms_awards_basic_v
WHERE award_id = x_award_id
and project_id = x_project_id
and ROWNUM = 1; */
SELECT et.expenditure_type
FROM pa_expenditure_types_expend_v et
WHERE et.system_linkage_function IN ('STRAIGHT_TIME', 'ST') and
exists(select a.expenditure_type
from gms_allowable_expenditures a
where a.expenditure_type = et.expenditure_type
and a.allowability_schedule_id = (select allowable_schedule_id
from gms_awards
where award_id = x_award_id))
and et.expenditure_type = x_Expenditure_Type;
SELECT et.expenditure_type
FROM pa_expenditure_types_expend_v et
WHERE et.system_linkage_function IN ('STRAIGHT_TIME', 'ST')
and et.expenditure_type = x_Expenditure_Type;
Procedure update_record_with_error(X_distribution_interface_id IN Number,
X_error_code IN Varchar2,
X_return_status OUT NOCOPY varchar2) IS
begin
UPDATE psp_distribution_interface
SET status_code = 'E',
error_code = x_error_code
WHERE distribution_interface_id = x_distribution_interface_id;
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_error : Error while updating');
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_error : Unexpected error');
Procedure update_record_with_valid(X_distribution_interface_id IN Number,
X_return_status OUT NOCOPY varchar2) IS
begin
UPDATE psp_distribution_interface
SET status_code = 'V',
error_code = NULL
WHERE distribution_interface_id = x_distribution_interface_id;
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_valid : Error while updating');
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_valid : Unexpected error');
SELECT start_date,end_date
into l_payroll_begin_date,l_payroll_end_date
FROM per_time_periods
WHERE time_period_id = x_time_period_id;
SELECT nvl(completion_date,l_payroll_end_date)
INTO l_project_end_date
FROM pa_projects_all
WHERE project_id = x_project_id;
SELECT nvl(end_date_active,l_payroll_end_date)
INTO l_award_end_date
FROM gms_awards
WHERE award_id = x_award_id;
SELECT nvl(completion_date,l_payroll_end_date)
INTO l_task_end_date
FROM pa_tasks
WHERE task_id = x_task_id;
SELECT nvl(actual_termination_date,l_payroll_end_date)
into l_termination_date
FROM per_periods_of_service
WHERE person_id = x_person_id and
(date_start between l_payroll_begin_date and l_payroll_end_date) ;
SELECT least(l_payroll_end_date,l_project_end_date,l_award_end_date,l_task_end_date,l_termination_date)
INTO l_effective_date
FROM dual;
SELECT *
FROM psp_distribution_interface
WHERE batch_name = x_batch_name
AND status_code <> 'V'; --Introduced for bug 2651339
SELECT count(*)
FROM psp_payroll_controls
WHERE source_type = 'P' and
batch_name = x_batch_name;
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_valid(X_distribution_interface_id=>
g_pregen_rec.distribution_interface_id,
X_return_status => l_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_valid(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_return_status => x_return_status);
update_record_with_exp(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_expenditure_type => l_new_expenditure_type,
X_return_status => x_return_status);
update_record_with_error(
X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_valid(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_error(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_error_code => l_return_code,
X_return_status => x_return_status);
update_record_with_valid(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_return_status => x_return_status);
update_record_with_valid(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_return_status => x_return_status);
update_record_with_na(X_distribution_interface_id =>
g_pregen_rec.distribution_interface_id,
X_gl_code_combination_id => l_new_gl_code_combination_id,
X_return_status => x_return_status);
/* 2007521: Introduced update statement, to revert sticking suspense a/c if
there are some other errors. Give chance to user to correct all errors */
if g_use_pre_gen_suspense = 'Y' then
update psp_distribution_interface
set suspense_org_account_id = null,
status_code = 'E'
where batch_name = x_batch_name and
suspense_org_account_id is not null;
Procedure update_record_with_exp(X_distribution_interface_id IN Number,
X_expenditure_type IN Varchar2,
X_return_status OUT NOCOPY Varchar2) IS
begin
UPDATE psp_distribution_interface
SET expenditure_type = X_expenditure_type
WHERE distribution_interface_id = X_distribution_interface_id;
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_exp : Error while updating');
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_exp : Unexpected error');
Procedure update_record_with_na(X_distribution_interface_id IN Number,
X_gl_code_combination_id IN Number,
X_return_status OUT NOCOPY Varchar2) IS
begin
UPDATE psp_distribution_interface
SET gl_code_combination_id = X_gl_code_combination_id
WHERE distribution_interface_id = X_distribution_interface_id;
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_na : Error while updating');
fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_na : Unexpected error');
SELECT organization_account_id,
gl_code_combination_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id
FROM psp_organization_accounts
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND organization_id = p_organization_id
AND account_type_code = p_account_type_code
AND p_effective_date BETWEEN start_date_active AND
nvl(end_date_active, p_effective_date);
SELECT organization_account_id,
gl_code_combination_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id
FROM psp_organization_accounts
WHERE organization_account_id = p_suspense_account_id;
SELECT full_name
FROM per_people_f
WHERE person_id =p_person_id;
select element_type_id,
assignment_id
from psp_distribution_interface
where distribution_interface_id = p_distribution_interface_id;
select ppf.full_name,
paf.assignment_number,
pet.element_name,
hou.name
from per_all_people_f ppf,
per_all_assignments_f paf,
pay_element_types_f pet,
hr_all_organization_units hou
where ppf.person_id = p_person_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = l_assignment_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and pet.element_type_id = l_element_type_id
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and hou.organization_id = paf.organization_id;
SELECT hou.organization_id,
hou.name
FROM per_assignments_f paf,
hr_organization_units hou
WHERE paf.business_group_id = p_business_group_id
AND paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_effective_date between hou.date_from and nvl(hou.date_to,p_effective_date)
AND p_business_group_id = hou.business_group_id
AND paf.organization_id = hou.organization_id;
update psp_distribution_interface
set suspense_org_account_id = v_suspense_account,
error_code = p_suspense_reason_code,
status_code = 'V',
suspense_auto_glccid = l_suspense_auto_glccid, --- added for 5080403
suspense_auto_exp_type = l_suspense_auto_exp_type
where distribution_interface_id = p_distribution_interface_id;
select 1
from gl_code_combinations
where CODE_COMBINATION_ID = X_CODE_COMBINATION_ID;