ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4714051A9E05362FB09050512 SELECT ROW_NUMBER() OVER(ORDER BY action_information3) as Seq_Num ,REPLACE(action_information3, '-', '') as RRN ,RPAD(action_information4,30,' ') as Employee_name ,NVL(action_information5,:P_CONT_PHONE_NO) as Contact ,action_information6 as Korean_foreign_identifier ,action_information7 as Residence_identifier ,action_information8 as Residence_country_code ,action_information9 as Start_date_of_working_Period ,action_information10 as End_date_of_working_Period ,action_information11 as Taxable_earnings ,action_information12 as Non_taxable_earnings FROM pay_action_information pai WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP' AND PAI.ACTION_INFORMATION_CATEGORY = 'KR_SER_EMP_DETAILS' AND pai.ACTION_CONTEXT_ID IN ( SELECT MAX(ASSIGNMENT_ACTION_ID) ASSIGNMENT_ACTION_ID FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA WHERE PPA.action_type IN ('X') AND PPA.action_status = 'C' and PPA.report_type = 'SER' and PPA.report_qualifier = 'KR' AND PPA.REPORT_CATEGORY='ARCHIVE' AND pay_core_utils.get_parameter('BP',ppa.legislative_parameters)=:P_LEGAL_EMP_ID AND pay_core_utils.get_parameter('TY',ppa.legislative_parameters)=:P_TARGET_YEAR AND pay_core_utils.get_parameter('REP_PERIOD',ppa.legislative_parameters) = :P_REPORTING_PERIOD AND PPA.PAYROLL_ACTION_ID = NVL(:P_PAYROLL_ACTION_ID,PPA.PAYROLL_ACTION_ID) AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID GROUP BY ASSIGNMENT_ID ) Y PAY PAYKRSER KO PAY 근로소득간이지급명세서 전산매체 Imported from BI Publisher Application: Payroll Source: 근로소득간이지급명세서 전산매체 Short Name: PAYKRSER DB package: US PAY KR Simple Earnings Report Efile Imported from BI Publisher Application: Payroll Source: KR Simple Earnings Report Efile Short Name: PAYKRSER DB package: BI Publisher :p_cont_phone_no :p_legal_emp_id :p_payroll_action_id :p_reporting_period :p_target_year 1 10 :p_legal_emp_id LOV Oracle PER_KR_BUSINESS_PLACE_ID Y select hou1.organization_id id, substr(hou2.name || ' - ' || hou1.name,1,255) value, hoi4.org_information2 || ' - ' || hoi2.org_information2 description from hr_organization_information hoi4,hr_organization_information hoi3,hr_organization_units hou2,hr_organization_information hoi2,hr_organization_information hoi1,hr_organization_units hou1 where hou1.business_group_id=fnd_profile.value('PER_BUSINESS_GROUP_ID') and hoi1.organization_id=hou1.organization_id and hoi1.org_information_context='CLASS' and hoi1.org_information1='KR_BUSINESS_PLACE' and hoi2.organization_id=hoi1.organization_id and hoi2.org_information_context='KR_BUSINESS_PLACE_REGISTRATION' and hou2.organization_id=to_number(hoi2.org_information10) and hoi3.organization_id=hou2.organization_id and hoi3.org_information_context='CLASS' and hoi3.org_information1='HR_LEGAL' and hoi4.organization_id=hoi3.organization_id and hoi4.org_information_context='KR_CORPORATE_INFORMATION' order by hou2.name,hou1.name Y KO P_LEGAL_EMP_ID 1차 사업장 US P_LEGAL_EMP_ID Primary Business Place 2 20 :p_target_year Number SELECT CASE WHEN EXTRACT(MONTH FROM fs.effective_date) <= 6 THEN EXTRACT(YEAR FROM fs.effective_date)-1 ELSE EXTRACT(YEAR FROM fs.effective_date) END AS TARGET_YEAR from fnd_sessions fs where fs.session_id = userenv('sessionid') Y KO TARGET_YEAR 대상 년도 US TARGET_YEAR Target Year 3 30 :p_reporting_period LOV Oracle PAY_KR_SER_REPORT_TIME Y select hrl.lookup_code id, hrl.meaning value, null description from hr_lookups hrl where hrl.lookup_type='KR_SER_REPORT_TIME' order by hrl.lookup_code SELECT hrl.meaning AS TARGET_YEAR from fnd_sessions fs, hr_lookups hrl where fs.session_id = userenv('sessionid') and hrl.lookup_type = 'KR_SER_REPORT_TIME' and hrl.lookup_code = decode(mod(EXTRACT(MONTH FROM fs.effective_date),6),0,'H2','H1') Y KO REPORTING_PERIOD 보고 기간 US REPORTING_PERIOD Reporting Period 4 40 :p_payroll_action_id LOV Oracle PAY_KR_SER_ARCHIVE_ID Y select ppa.payroll_action_id id, ppa.payroll_action_id value, null description from pay_payroll_actions ppa,pay_payrolls_f ppf,hr_assignment_sets has,hr_lookups hrl where ppf.payroll_id=pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters) and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date and pay_core_utils.get_parameter('ASSG_SET_ID',ppa.legislative_parameters)=has.assignment_set_id(+) and hrl.lookup_type='KR_SER_REPORT_TIME' and hrl.lookup_code=pay_core_utils.get_parameter('REP_PERIOD',ppa.legislative_parameters) and pay_core_utils.get_parameter('BP',ppa.legislative_parameters)=:$flex$.p_legal_emp_id and pay_core_utils.get_parameter('TY',ppa.legislative_parameters)=:$flex$.target_year and ((:$flex$.target_year>=2022 and pay_core_utils.get_parameter('REP_MONTH',ppa.legislative_parameters)=:$flex$.reporting_month) or :$flex$.target_year<2022) and hrl.lookup_code=:$flex$.reporting_period and ppa.action_type='X' and ppa.action_status='C' and ppa.report_type='SER' and ppa.report_qualifier='KR' and ppa.report_category='ARCHIVE' order by ppa.request_id desc KO 급여 조치 아카이브 ID US Payroll Action Archive ID 5 50 :p_report_date Date select fs.effective_date from fnd_sessions fs where fs.session_id = userenv('sessionid') Y KO 보고서 일자 보고서 일자 US Report Date Report Date 6 60 :p_tax_reporting_org Char select corp_e_f.org_information2 from hr_organization_information corp_e_f, hr_organization_information bp_reg where bp_reg.organization_id = :$FLEX$.PER_KR_BUSINESS_PLACE_ID and bp_reg.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' and corp_e_f.organization_id = to_number(bp_reg.org_information10) and corp_e_f.org_information_context = 'KR_CORPORATE_E_FILE_REP' Y KO 담당자 부서 담당자 부서 US Tax Reporting Organization Tax Reporting Organization 7 70 :p_tax_reporter Char select corp_e_f.org_information3 from hr_organization_information corp_e_f, hr_organization_information bp_reg where bp_reg.organization_id = :$FLEX$.PER_KR_BUSINESS_PLACE_ID and bp_reg.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' and corp_e_f.organization_id = to_number(bp_reg.org_information10) and corp_e_f.org_information_context = 'KR_CORPORATE_E_FILE_REP' Y KO 담당자 성명 담당자 성명 US Tax Reporter Tax Reporter 8 80 :p_cont_phone_no Char select corp_e_f.org_information4 from hr_organization_information corp_e_f, hr_organization_information bp_reg where bp_reg.organization_id = :$FLEX$.PER_KR_BUSINESS_PLACE_ID and bp_reg.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' and corp_e_f.organization_id = to_number(bp_reg.org_information10) and corp_e_f.org_information_context = 'KR_CORPORATE_E_FILE_REP' Y KO 담당자 전화번호 담당자 전화번호 US Contact Phone Number Contact Phone Number 9 90 :p_home_tax_id Char select corp_e_f.org_information1 from hr_organization_information corp_e_f, hr_organization_information bp_reg where bp_reg.organization_id = :$FLEX$.PER_KR_BUSINESS_PLACE_ID and bp_reg.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' and corp_e_f.organization_id = to_number(bp_reg.org_information10) and corp_e_f.org_information_context = 'KR_CORPORATE_E_FILE_REP' Y KO 홈택스 ID 홈택스 ID US Home Tax ID Home Tax ID :$FLEX$.PER_KR_BUSINESS_PLACE_ID P_LEGAL_EMP_ID Contact Phone Number :$FLEX$.PER_KR_BUSINESS_PLACE_ID P_LEGAL_EMP_ID Home Tax ID :$FLEX$.PER_KR_BUSINESS_PLACE_ID P_LEGAL_EMP_ID Tax Reporter :$FLEX$.PER_KR_BUSINESS_PLACE_ID P_LEGAL_EMP_ID Tax Reporting Organization :$flex$.p_legal_emp_id P_LEGAL_EMP_ID Payroll Action :$flex$.reporting_period REPORTING_PERIOD Payroll Action :$flex$.target_year TARGET_YEAR Payroll Action