eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_JP_LTAX_IMP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 154


procedure insert_session(
  p_effective_date in date)
is
begin
--
  insert into fnd_sessions(
    session_id,
    effective_date)
  select
    userenv('sessionid'),
    p_effective_date
  from dual
  where not exists(
    select null
    from   fnd_sessions
    where  session_id = userenv('sessionid')
    and    effective_date = p_effective_date);

Line: 174


end insert_session;

Line: 179


procedure delete_session
is
begin
--
  delete from fnd_sessions
  where	session_id = userenv('sessionid');

Line: 188


end delete_session;

Line: 474


  select fcp.plsql_dir
  from   fnd_concurrent_requests fcr,
         fnd_concurrent_processes fcp
  where  fcr.request_id = g_request_id
  and    fcp.concurrent_process_id = fcr.controlling_manager;

Line: 489


    g_file_tbl.delete;

Line: 490


    g_imp_data_tbl.delete;

Line: 491


    g_ass_data_tbl.delete;

Line: 492


    g_ass_id_tbl.delete;

Line: 493


    g_ass_ind_tbl.delete;

Line: 494


    g_ass_amd_ind_tbl.delete;

Line: 495


    g_imp_file_ind_tbl.delete;

Line: 496


    g_mth_tbl.delete;

Line: 498


    g_dup_file_wng_tbl.delete;

Line: 499


    g_no_file_wng_tbl.delete;

Line: 500


    g_diff_ltax_wng_tbl.delete;

Line: 501


    g_inv_data_wng_tbl.delete;

Line: 502


    g_incon_data_wng_tbl.delete;

Line: 503


    g_no_ass_wng_tbl.delete;

Line: 504


    g_non_res_wng_tbl.delete;

Line: 505


    g_dup_ass_wng_tbl.delete;

Line: 506


    g_upd_eev_wng_tbl.delete;

Line: 507


    g_no_upd_wng_tbl.delete;

Line: 508


    g_sp_with_wng_tbl.delete;

Line: 509


    g_inv_ass_wng_tbl.delete;

Line: 744


  select nvl(pjsn_act.report_district_code,pjsn_act.district_code) rep_district_code,
         pjsn_act.organization_id,
         pjsn_rep.swot_number,
         pjsn_rep.input_file_name
  from   hr_organization_information hoi,
         hr_all_organization_units hou,
         pay_jp_swot_numbers pjsn_act,
         pay_jp_swot_numbers pjsn_rep
  where  hoi.org_information_context = 'CLASS'
  and    hoi.org_information1 = 'JP_TAX_SWOT'
  and    hoi.organization_id = nvl(g_organization_id,hoi.organization_id)
  and    hou.organization_id = hoi.organization_id
  and    hou.business_group_id + 0 = g_business_group_id
  and    hou.date_from <= g_effective_eoy
  and    nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
  and    pjsn_act.organization_id = hou.organization_id
  and    pjsn_rep.organization_id = pjsn_act.organization_id
  and    pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
  and    substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
  and    nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
  and    (pjsn_rep.swot_number is not null
          or pjsn_rep.input_file_name is not null)
  group by
    nvl(pjsn_act.report_district_code,pjsn_act.district_code),
    pjsn_act.organization_id,
    pjsn_rep.swot_number,
    pjsn_rep.input_file_name
  order by
    pjsn_rep.input_file_name,
    nvl(pjsn_act.report_district_code,pjsn_act.district_code),
    pjsn_rep.swot_number,
    pjsn_act.organization_id;

Line: 779


  select /*+ ORDERED */
         pa.assignment_id,
         pa.assignment_number,
         nvl(pjsn_act.report_district_code,pjsn_act.district_code) rep_district_code,
         pjsn_act.organization_id,
         pjsn_rep.swot_number,
         pjsn_rep.input_file_name,
         ptp.regular_payment_date
  from   hr_organization_information hoi,
         hr_all_organization_units hou,
         pay_jp_swot_numbers pjsn_act,
         pay_jp_swot_numbers pjsn_rep,
         per_addresses pad,
         per_periods_of_service ppos,
         per_all_assignments_f pa,
         per_time_periods ptp
  where  hoi.org_information_context = 'CLASS'
  and    hoi.org_information1 = 'JP_TAX_SWOT'
  and    hoi.organization_id = nvl(g_organization_id,hoi.organization_id)
  and    hou.organization_id = hoi.organization_id
  and    hou.business_group_id + 0 = g_business_group_id
  and    hou.date_from <= g_effective_eoy
  and    nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
  and    pjsn_act.organization_id = hou.organization_id
  and    pjsn_rep.organization_id = pjsn_act.organization_id
  and    pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
  and    substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
  and    nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
  and    (pjsn_rep.swot_number is not null
          or pjsn_rep.input_file_name is not null)
  -- group by required for reporting and actual swot
  and    pad.town_or_city = substrb(pjsn_act.district_code,1,5)
  and    pad.address_type in ('JP_R','JP_C')
  and    pad.business_group_id + 0 = g_business_group_id
  and    ppos.person_id = pad.person_id
  and    nvl(ppos.actual_termination_date, g_effective_soy)
         between pad.date_from and nvl(pad.date_to,hr_api.g_eot)
  and    pa.period_of_service_id = ppos.period_of_service_id
  and    pa.effective_start_date <= g_effective_eom
  and    pa.effective_end_date >= g_effective_som
  and    pa.primary_flag = 'Y'
  and    ptp.payroll_id = pa.payroll_id
  and    ptp.regular_payment_date
         between g_effective_som and g_effective_eom
  and    ptp.regular_payment_date
         between pa.effective_start_date and pa.effective_end_date
  and    pay_jp_balance_pkg.get_entry_value_number(c_itx_org_iv_id,pa.assignment_id,ptp.regular_payment_date) = pjsn_act.organization_id
  group by
    pa.assignment_id,
    pa.assignment_number,
    nvl(pjsn_act.report_district_code,pjsn_act.district_code),
    pjsn_act.organization_id,
    pjsn_rep.swot_number,
    pjsn_rep.input_file_name,
    ptp.regular_payment_date
  order by
    pjsn_rep.input_file_name,
    nvl(pjsn_act.report_district_code,pjsn_act.district_code),
    pjsn_rep.swot_number,
    pjsn_act.organization_id,
    pa.assignment_id;

Line: 845


  select hasa.assignment_id,
         pa.assignment_number,
         hasa.include_or_exclude
  from   hr_assignment_set_amendments hasa,
         per_all_assignments_f pa,
         per_time_periods ptp
  where  hasa.assignment_set_id = g_assignment_set_id
  and    pa.assignment_id = hasa.assignment_id
  and    pa.effective_start_date <= g_effective_eom
  and    pa.effective_end_date >= g_effective_som
  and    pa.primary_flag = 'Y'
  and    ptp.payroll_id = pa.payroll_id
  and    ptp.regular_payment_date
         between g_effective_som and g_effective_eom
  and    ptp.regular_payment_date
         between pa.effective_start_date and pa.effective_end_date;

Line: 1457


  select pa.assignment_id,
         pa.payroll_id,
         pa.assignment_number,
         ptp.regular_payment_date,
         greatest(ppos.actual_termination_date,nvl(ppos.final_process_date,ppos.actual_termination_date)) final_process_date
  from   per_all_people_f pp,
         per_all_assignments_f pa,
         per_time_periods ptp,
         per_periods_of_service ppos
  where  pp.business_group_id + 0 = g_business_group_id
  and    pp.effective_start_date <= g_effective_eom
  and    pp.effective_end_date >= g_effective_som
  and    pp.employee_number = p_employee_number
  and    pa.person_id = pp.person_id
  and    pa.primary_flag = 'Y'
  and    pa.effective_start_date <= g_effective_eom
  and    pa.effective_end_date >= g_effective_som
  and    ptp.payroll_id = pa.payroll_id
  and    ptp.regular_payment_date
         between g_effective_som and g_effective_eom
  and    ptp.regular_payment_date
         between pa.effective_start_date and pa.effective_end_date
  and    ptp.regular_payment_date
         between pp.effective_start_date and pp.effective_end_date
  and    ppos.person_id = pp.person_id
  and    ppos.period_of_service_id = pa.period_of_service_id
  and    ptp.regular_payment_date
         between ppos.date_start and nvl(greatest(ppos.actual_termination_date,nvl(ppos.final_process_date,ppos.actual_termination_date)),ptp.regular_payment_date)
  and    exists(
           select null
           from   per_addresses pad,
                  pay_jp_swot_numbers pjsn_act,
                  pay_jp_swot_numbers pjsn_rep,
                  hr_all_organization_units hou,
                  hr_organization_information hoi
           where  pad.person_id = pp.person_id
           and    pad.address_type in ('JP_C','JP_R')
           and    nvl(ppos.actual_termination_date,g_effective_soy)
                  between pad.date_from and nvl(pad.date_to,hr_api.g_eot)
           and    substrb(pjsn_act.district_code,1,5) = pad.town_or_city
           and    pjsn_rep.organization_id = pjsn_act.organization_id
           and    pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
           and    substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
           and    nvl(pjsn_act.report_district_code,pjsn_act.district_code) = p_district_code
           and    nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
           and    pjsn_rep.swot_number = p_swot_number
           and    hou.organization_id = pjsn_rep.organization_id
           and    hou.business_group_id + 0 = g_business_group_id
           and    hou.date_from <= g_effective_eoy
           and    nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
           and    hou.organization_id = nvl(g_organization_id,hou.organization_id)
           and    hou.organization_id = hoi.organization_id
           and    hoi.org_information_context = 'CLASS'
           and    hoi.org_information1 = 'JP_TAX_SWOT'
           --exclude different itax are mixed in one file (swot_number should be different).
           and    pay_jp_balance_pkg.get_entry_value_number(c_itx_org_iv_id,pa.assignment_id,ptp.regular_payment_date) = hoi.organization_id);

Line: 2057


  select ptp.regular_payment_date payment_date,
         ptp.start_date upload_date
  from   per_time_periods ptp
  where  ptp.payroll_id = p_payroll_id
  and    ptp.period_type = 'Calendar Month'
  and    ptp.regular_payment_date >= g_effective_som
  and    ptp.regular_payment_date < add_months(g_effective_som,12)
  order by ptp.start_date;

Line: 2165


      g_ee_tbl.delete;

Line: 2210


      l_new_value_tbl.delete;

Line: 2651


  insert_session(g_session_date);

Line: 2703


  delete_session;

Line: 2952


    pay_batch_element_entry_api.delete_batch_header(
      p_validate              => false,
      p_batch_id              => l_batch_id,
      p_object_version_number => l_batch_ovn);