[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE gt_insert_wage_info IS TABLE OF gt_wage_info INDEX BY BINARY_INTEGER;
SELECT MIN(pes.element_set_id)
FROM pay_element_sets PES
WHERE PES.element_set_name = p_element_set_name
AND NVL(PES.business_group_id,p_business_group_id) = p_business_group_id
AND NVL(PES.legislation_code,p_legislation_code) = p_legislation_code;
FUNCTION get_insert_action_info( p_insert_wage_info IN gt_insert_wage_info)
--************************************************************************
-- FUNCTION
-- get_insert_action_info
--
-- DESCRIPTION
-- Removes redundant insurance period if employee payroll has been in the
-- middle of the month and payroll has been run more than once in a month
-- ACCESS
-- PRIVATE
--
-- PREREQUISITES
-- None
--
-- CALLED BY
-- proc_sal_arch
--************************************************************************
RETURN gt_insert_wage_info
AS
--
lt_res_tb gt_insert_wage_info;
lc_procedure := gc_package||'get_insert_action_info';
ln_count := p_insert_wage_info.count;
hr_utility.set_location('p_insert_wage_info count = '||ln_count,1);
lt_res_tb := p_insert_wage_info;
FOR i in p_insert_wage_info.first..p_insert_wage_info.last
--
LOOP
--
EXIT WHEN (lc_final_flag = 'Y');
IF ( (TRUNC(p_insert_wage_info(i).insured_start_date) = TRUNC(p_insert_wage_info(i-1).insured_start_date))
AND (TRUNC(p_insert_wage_info(i).insured_end_date) = TRUNC(p_insert_wage_info(i-1).insured_end_date)) ) THEN
--
IF ( i < p_insert_wage_info.last) THEN
--
ld_insured_start_date := p_insert_wage_info(i+1).insured_start_date;
ld_insured_end_date := p_insert_wage_info(i+1).insured_end_date;
ln_insured_days := p_insert_wage_info(i+1).insured_days;
IF (i < p_insert_wage_info.last) THEN
--
ld_insured_start_date := p_insert_wage_info(i+1).insured_start_date;
ld_insured_end_date := p_insert_wage_info(i+1).insured_end_date;
ln_insured_days := p_insert_wage_info(i+1).insured_days;
ld_insured_start_date := p_insert_wage_info(i).insured_start_date;
ld_insured_end_date := p_insert_wage_info(i).insured_end_date;
ln_insured_days := p_insert_wage_info(i).insured_days;
ld_insured_start_date := p_insert_wage_info(i).insured_start_date;
ld_insured_end_date := p_insert_wage_info(i).insured_end_date;
ln_insured_days := p_insert_wage_info(i).insured_days;
IF ( (NVL(ln_insured_days,0) >= 11) AND (p_insert_wage_info(i).exclude_period = 'N') ) THEN
--
ln_ins_rows := ln_ins_rows +1;
IF p_insert_wage_info(i).exclude_period = 'N' THEN
--
ln_row_count := ln_row_count + 1;
IF TRUNC(ld_final_insured_date) BETWEEN p_insert_wage_info(i).period_start_date AND p_insert_wage_info(i).period_end_date THEN
--
lc_final_flag := 'Y';
lt_res_tb(i).payment_date := p_insert_wage_info(i).payment_date; -- Payment Date
lt_res_tb(i).period_start_date := p_insert_wage_info(i).period_start_date; -- Pay Period Start Date
lt_res_tb(i).period_end_date := p_insert_wage_info(i).period_end_date; -- Pay Period End Date
lt_res_tb(i).base_days := p_insert_wage_info(i).base_days; -- Pay Period Base Days
lt_res_tb(i).wage_amount_a := p_insert_wage_info(i).wage_amount_a; -- Wage Amount A
lt_res_tb(i).wage_amount_b := p_insert_wage_info(i).wage_amount_b; -- Wage Amount B
lt_res_tb(i).remarks := p_insert_wage_info(i).remarks; -- Remarks
lt_res_tb(i).exclude_period := p_insert_wage_info(i).exclude_period; -- Exclude Period
lt_res_tb(i).line_number := p_insert_wage_info(i).line_number; -- Line Number
END get_insert_action_info;
PROCEDURE proc_insert_row( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
,p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_effective_date IN pay_payroll_actions.effective_date%TYPE
,p_termination_date IN per_periods_of_service.actual_termination_date%TYPE
,p_payroll_id IN pay_payrolls_f.payroll_id%TYPE
,p_hire_date IN per_periods_of_service.date_start%TYPE
,p_last_std_process_date IN per_periods_of_service.last_standard_process_date%TYPE
,p_line_number OUT NOCOPY NUMBER)
IS
--***************************************************************************
-- PROCEDURE
-- proc_insert_row
--
-- DESCRIPTION
-- This procedure is used to process non payroll data
--
-- ACCESS
-- PUBLIC
--
-- PARAMETERS
--==========
-- NAME TYPE DESCRIPTION
------------------- -------- ---------------------------------------
-- p_payroll_action_id IN This parameter passes Payroll Action Id
-- p_assignment_id IN This parameter passes Assignment Id
-- p_effective_date IN This Parameter Passes Effective Date
-- p_termination_date IN This Parameter passes the Termination Date
-- p_payroll_id IN This Parameter passes the Payroll Id
-- p_hire_date IN This parameter passes the hire date
-- p_last_std_process_date IN This parameter passes the last standard process date
-- p_ins_start_date OUT Passes back Insurance start date
-- PREREQUISITES
-- None
--
-- CALLED BY
-- None
--***********************************************************************
lc_procedure VARCHAR2(200);
SELECT PTP.start_date,
PTP.end_date
FROM per_time_periods PTP
WHERE PTP.payroll_id = p_payroll_id
AND p_termination_date BETWEEN PTP.start_date AND PTP.end_date
ORDER BY PTP.start_date DESC;
lc_procedure := gc_package||'proc_insert_row';
END proc_insert_row;
SELECT PAA.assignment_action_id,
PPA.effective_date,
PPA.date_earned,
PTP.start_date,
PTP.end_date,
PAF.payroll_id,
PAF.effective_start_date,
PAF.effective_end_date
FROM pay_assignment_actions PAA
,pay_payroll_actions PPA
,per_time_periods PTP
,per_assignments_f PAF
WHERE PAA.assignment_id = p_assignment_id
AND PAF.assignment_id = PAA.assignment_id
AND PAA.action_status = 'C'
AND PPA.payroll_action_id = PAA.payroll_action_id
AND PPA.effective_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
AND PPA.effective_date
AND TRUNC(PTP.start_date) <= TRUNC(p_termination_date)
AND PPA.element_set_id = gn_sal_ele_set_id
AND PPA.action_type in ('R','Q','G','L')
AND NOT EXISTS(
SELECT null
FROM pay_action_interlocks PAI,
pay_assignment_actions PAAI,
pay_payroll_actions PPAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAI.assignment_action_id = PAI.locking_action_id
AND PPAI.payroll_action_id = PAAI.payroll_action_id
AND PPAI.action_type = 'V')
AND PTP.payroll_id = PPA.payroll_id
AND PPA.date_earned BETWEEN PTP.start_date AND PTP.end_date
AND PPA.date_earned BETWEEN PAF.effective_start_date AND PAF.effective_end_date
ORDER BY PAA.assignment_action_id DESC;
SELECT PAA.assignment_action_id,
PPA.effective_date,
PPA.date_earned,
PTP.start_date,
PTP.end_date,
PAF.payroll_id,
PAF.effective_start_date,
PAF.effective_end_date
FROM pay_assignment_actions PAA
,pay_payroll_actions PPA
,per_time_periods PTP
,per_assignments_f PAF
WHERE PAA.assignment_id = p_assignment_id
AND PAF.assignment_id = PAA.assignment_id
AND PAA.action_status = 'C'
AND PPA.payroll_action_id = PAA.payroll_action_id
AND PPA.effective_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
AND PPA.effective_date
AND TRUNC(PTP.start_date) <= TRUNC(p_termination_date)
AND PPA.element_set_id = gn_sal_ele_set_id
AND PPA.action_type in ('R','Q','G','L')
AND NOT EXISTS(
SELECT null
FROM pay_action_interlocks PAI,
pay_assignment_actions PAAI,
pay_payroll_actions PPAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAI.assignment_action_id = PAI.locking_action_id
AND PPAI.payroll_action_id = PAAI.payroll_action_id
AND PPAI.action_type = 'V')
AND PTP.payroll_id = PPA.payroll_id
AND PPA.effective_date BETWEEN PTP.start_date AND PTP.end_date
AND PPA.effective_date BETWEEN PAF.effective_start_date AND PAF.effective_end_date
ORDER BY PAA.assignment_action_id DESC; -- Bug 9693280
SELECT PTP.start_date,
PTP.end_date
FROM per_time_periods PTP
WHERE PTP.payroll_id = p_payroll_id
AND PTP.start_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
AND NVL(p_last_std_process_date,PTP.start_date)
AND PTP.start_date <= p_termination_date
ORDER BY PTP.start_date DESC;
SELECT PDB.defined_balance_id
,PBT.balance_type_id
FROM pay_balance_types PBT
,pay_balance_dimensions PBD
,pay_defined_balances PDB
WHERE PBT.balance_name = p_balance_name
AND PBD.database_item_suffix = p_database_item_suffix
AND PBT.balance_type_id = PDB.balance_type_id
AND PBD.balance_dimension_id = PDB.balance_dimension_id;
SELECT 'Y'
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_balance_feeds_f FEED,
pay_run_results RR,
pay_run_result_values TARGET
WHERE ASSACT.assignment_action_id = p_assignment_action_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND RR.status in ('P','PA')
AND PACT.action_type in ('R','Q','G','L')
AND TARGET.run_result_id = RR.run_result_id
AND FEED.input_value_id = TARGET.input_value_id
AND FEED.balance_type_id = p_balance_type_id
AND PACT.effective_date BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
SELECT ASSACT.assignment_action_id
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_balance_feeds_f FEED,
pay_run_results RR,
pay_run_result_values TARGET
WHERE PACT.payroll_action_id = ASSACT.payroll_action_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND RR.status in ('P','PA')
AND PACT.action_type in ('R','Q','G','L')
AND TARGET.run_result_id = RR.run_result_id
AND FEED.input_value_id = TARGET.input_value_id
AND FEED.balance_type_id = p_balance_type_id
AND PACT.element_set_id = gn_sal_ele_set_id
AND PACT.date_earned BETWEEN FEED.effective_start_date AND FEED.effective_end_date
AND PACT.date_earned BETWEEN (p_date_earned+1) AND add_months(p_date_earned,1)
AND ASSACT.assignment_id= p_assignment_id; -- #Bug 9732572
lt_insert_wage_info gt_insert_wage_info;
lt_insert_action_info gt_insert_wage_info;
lt_assact_tbl.DELETE;
proc_insert_row( p_assignment_action_id => p_assignment_action_id
,p_payroll_action_id => p_payroll_action_id
,p_assignment_id => p_assignment_id
,p_effective_date => p_effective_date
,p_termination_date => p_termination_date
,p_payroll_id => p_payroll_id
,p_hire_date => p_hire_date
,p_last_std_process_date => p_last_std_process_date
,p_line_number => ln_line_count);
lt_insert_wage_info(i).payment_date := lt_assact_tbl(assact_cnt).effective_date; -- Payment Date
lt_insert_wage_info(i).insured_start_date := ld_insured_start_date; -- Insured Period Start Date
lt_insert_wage_info(i).insured_end_date := ld_insured_end_date; -- Insured Period End Date
lt_insert_wage_info(i).insured_days := ln_wage_pay_days; -- Insured Period Base Days
lt_insert_wage_info(i).period_start_date := ld_period_start_date; -- Pay Period Start Date
lt_insert_wage_info(i).period_end_date := ld_period_end_date; -- Pay Period End Date
lt_insert_wage_info(i).base_days := ln_base_days; -- Pay Period Base Days
lt_insert_wage_info(i).wage_amount_a := ln_wage_amount_a; -- Wage Amount A
lt_insert_wage_info(i).wage_amount_b := ln_wage_amount_b; -- Wage Amount B
lt_insert_wage_info(i).remarks := lc_remarks; -- Remarks
lt_insert_wage_info(i).exclude_period := NVL(lc_exclude_period,'N'); -- Exclude Period
lt_insert_wage_info(i).line_number := ln_line_number; -- Line Number
lt_insert_action_info := get_insert_action_info(p_insert_wage_info => lt_insert_wage_info);
j := lt_insert_action_info.first;
hr_utility.set_location('Inserting Data into Pay action Information ',30);
IF (lt_insert_action_info(j).wage_amount_a IS NOT NULL OR lt_insert_action_info(j).wage_amount_b IS NOT NULL) THEN
--
ln_total_wage := NVL(lt_insert_action_info(j).wage_amount_a,0) + NVL(lt_insert_action_info(j).wage_amount_b,0);
, p_action_information1 => fnd_date.date_to_canonical(lt_insert_action_info(j).payment_date) -- Payment Date
, p_action_information2 => fnd_date.date_to_canonical(lt_insert_action_info(j).insured_start_date) -- Insured Period Start Date
, p_action_information3 => fnd_date.date_to_canonical(lt_insert_action_info(j).insured_end_date) -- Insured Period End Date
, p_action_information4 => fnd_number.number_to_canonical(lt_insert_action_info(j).insured_days) -- Insured Period Base Days
, p_action_information5 => fnd_date.date_to_canonical(lt_insert_action_info(j).period_start_date) -- Pay Period Start Date
, p_action_information6 => fnd_date.date_to_canonical(lt_insert_action_info(j).period_end_date) -- Pay Period End Date
, p_action_information7 => fnd_number.number_to_canonical(lt_insert_action_info(j).base_days) -- Pay Period Base Days
, p_action_information8 => fnd_number.number_to_canonical(lt_insert_action_info(j).wage_amount_a) -- Wage Amount A
, p_action_information9 => fnd_number.number_to_canonical(lt_insert_action_info(j).wage_amount_b) -- Wage Amount B
, p_action_information10 => fnd_number.number_to_canonical(ln_total_wage) -- Wage Amount Total
, p_action_information11 => lt_insert_action_info(j).remarks -- Remarks
, p_action_information12 => lt_insert_action_info(j).exclude_period -- Exclude Period
, p_action_information13 => fnd_number.number_to_canonical(lt_insert_action_info(j).line_number) -- Line Number
);
j := lt_insert_action_info.next(j);
SELECT paa.assignment_action_id,
ppa.effective_date,
ppa.date_earned
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND paa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date
BETWEEN p_period_start_date and p_period_end_date
AND ppa.element_set_id = gn_spb_ele_set_id
AND ppa.action_type in ('R','Q','G','L')
AND NOT EXISTS(
SELECT null
FROM pay_action_interlocks pai,
pay_assignment_actions paa2,
pay_payroll_actions ppa2
WHERE pai.locked_action_id = paa.assignment_action_id
AND paa2.assignment_action_id = pai.locking_action_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type = 'V')
ORDER BY paa.action_sequence;
SELECT PDB.defined_balance_id
FROM pay_balance_types PBT
,pay_balance_dimensions PBD
,pay_defined_balances PDB
WHERE PBT.balance_name = p_balance_name
AND PBD.database_item_suffix = p_database_item_suffix
AND PBT.balance_type_id = PDB.balance_type_id
AND PBD.balance_dimension_id = PDB.balance_dimension_id;
lt_spb_assact_tbl.delete;
p_sql := ' SELECT distinct p.person_id' ||
' FROM per_people_f p,' ||
' pay_payroll_actions pa' ||
' WHERE pa.payroll_action_id = :payroll_action_id' ||
' AND p.business_group_id = pa.business_group_id' ||
' ORDER BY p.person_id';
SELECT pay_core_utils.get_parameter('BG',legislative_parameters)
,pay_core_utils.get_parameter('ASSETID',legislative_parameters)
,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD')
,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD')
,pay_core_utils.get_parameter('LIO',legislative_parameters)
,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')
FROM pay_payroll_actions PPA
WHERE PPA.payroll_action_id = p_payroll_action_id;
SELECT NVL(org_information15,gc_date_earned)
,NVL(org_information16,12)
FROM hr_organization_information HOI
WHERE HOI.org_information_context= 'JP_LI_UNION_INFO'
AND organization_id= p_organization_id;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'RANGE_PERSON_ID';
SELECT PAF.assignment_id
,PPS.actual_termination_date
,PPS.projected_termination_date
FROM per_people_f PPF
,per_assignments_f PAF
,per_periods_of_service PPS
,pay_population_ranges PPR
,pay_payroll_actions PPA
WHERE PPF.person_id = PAF.person_id
AND PPF.person_id = PPS.person_id
AND PPA.payroll_action_id = p_payroll_action_id
AND PPA.payroll_action_id = PPR.payroll_action_id
AND PPR.chunk_number = p_chunk
AND PPR.person_id = PPF.person_id
AND PAF.business_group_id = p_business_group_id
AND PPA.business_group_id = PAF.business_group_id
AND PPS.period_of_service_id = PAF.period_of_service_id
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN p_start_date AND p_end_date
AND NVL(get_life_ins_org_id(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) = p_organization_id
AND NVL(get_ei_type(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) IN ('EE','EE_AGED','EX','EX_AGED')
AND NVL(PPS.actual_termination_date,PPS.projected_termination_date) BETWEEN get_ei_qualify_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))
AND NVL(get_ei_dis_qual_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))-1,TO_DATE('31/12/4712','dd/mm/yyyy'))
AND get_term_rpt_flag(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))= 'Y'
ORDER BY PAF.assignment_id;
SELECT PAF.assignment_id
,PPS.actual_termination_date
,PPS.projected_termination_date
FROM per_people_f PPF
,per_assignments_f PAF
,per_periods_of_service PPS
WHERE PPF.person_id = PAF.person_id
AND PPF.person_id = PPS.person_id
AND PAF.business_group_id = p_business_group_id
AND PPF.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND PPS.period_of_service_id = PAF.period_of_service_id
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN p_start_date AND p_end_date
AND NVL(get_life_ins_org_id(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) = p_organization_id
AND NVL(get_ei_type(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) IN ('EE','EE_AGED','EX','EX_AGED')
AND NVL(PPS.actual_termination_date,PPS.projected_termination_date)BETWEEN get_ei_qualify_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))
AND NVL(get_ei_dis_qual_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))-1,TO_DATE('31/12/4712','dd/mm/yyyy'))
AND get_term_rpt_flag(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))= 'Y'
ORDER BY PAF.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
FROM dual;
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT PPF.employee_number EMPLOYEE_NUMBER
, get_ui_num(p_assignment_id,NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) UI_REGISTERED_NUMBER
, PPF.last_name LAST_NAME_KANA
, PPF.first_name FIRST_NAME_KANA
, PPF.per_information18 LAST_NAME
, PPF.per_information19 FIRST_NAME
, NVL(PPOS.actual_termination_date,PPOS.projected_termination_date) TERMINATION_DATE
, PAD.postal_code EMP_ZIP_CODE
, PAD.address_line1 ADDRESS_LINE1
, PAD.address_line2 ADDRESS_LINE2
, PAD.address_line3 ADDRESS_LINE3
, PAD.telephone_number_1 PHONE_NUM
, PAF.assignment_id ASSIGNMENT_ID
, PAF.payroll_id PAYROLL_ID
, PPOS.date_start HIRE_DATE
, PPOS.last_standard_process_date LAST_STD_PROCESS_DATE
FROM per_people_f PPF
, per_assignments_f PAF
, per_addresses PAD
, per_periods_of_service PPOS
WHERE PAF.person_id = PPF.person_id
AND PAD.person_id(+) = PPF.person_id
AND PAD.address_type(+) = 'JP_C'
AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN NVL(PAD.date_from,NVL(PPOS.actual_termination_date,PPOS.projected_termination_date))
AND NVL(PAD.date_to,TO_DATE('31/12/4712','dd/mm/yyyy')) --#Bug9648137
AND PPF.person_id = PPOS.person_id
AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN PAF.effective_start_date
AND PAF.effective_end_date
AND PAF.assignment_id = p_assignment_id
AND PPOS.period_of_service_id = NVL(PAF.period_of_service_id,PPOS.period_of_service_id)
ORDER BY PPF.effective_start_date;
SELECT HOI.org_information1 LOCATION_NUMBER
,HOI.org_information2 BUSINESS_ADDRESS1
,HOI.org_information3 BUSINESS_ADDRESS2
,HOI.org_information4 BUSINESS_ADDRESS3
,HOI.org_information5 EMPLOYER_ADDRESS1
,HOI.org_information6 EMPLOYER_ADDRESS2
,HOI.org_information7 EMPLOYER_ADDRESS3
,HOI.org_information8 LOCATION_NAME
,HOI.org_information9 EMPLOYER_NAME
,HOI.org_information10 EMPLOYER_FULL_NAME
,HOI.org_information11 COMPANY_PHONE
FROM hr_organization_information HOI
WHERE HOI.org_information_context = 'JP_LI_UNION_INFO'
AND HOI.organization_id = gr_parameters.labor_insorg_id;