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_ISDF_RPT SQL Statements

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

Line: 37


  SELECT  INCLUDE_OR_EXCLUDE
  INTO  l_inc_or_exc
  FROM  HR_ASSIGNMENT_SET_AMENDMENTS
  WHERE ASSIGNMENT_ID = p_assignment_id
      AND ASSIGNMENT_SET_ID = p_assignment_set_id;

Line: 99


  SELECT 1
  INTO  l_dummy
  FROM  HR_ASSIGNMENT_SET_AMENDMENTS
  WHERE  ASSIGNMENT_SET_ID = p_assignment_set_id
  AND  ROWNUM  = 1;

Line: 121


  SELECT  0
  INTO  l_dummy
  FROM  HR_ASSIGNMENT_SET_AMENDMENTS
  WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
  AND   INCLUDE_OR_EXCLUDE = 'I'
  AND   ROWNUM  = 1;

Line: 248


  p_sqlstr := ' 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 ';

Line: 276


  SELECT  distinct pjiav.assignment_id, pjiav.effective_date
  FROM  per_all_assignments_f paa,
        per_all_people_f pap,
        pay_assignment_actions pas,
        pay_jp_isdf_assact_v pjiav
  WHERE  paa.person_id between p_start_person_id and p_end_person_id
  AND  paa.person_id = pap.person_id
  AND  sysdate between pap.effective_start_date and pap.effective_end_date
  AND  sysdate between paa.effective_start_date and paa.effective_end_date
  AND  pas.assignment_id = paa.assignment_id
  AND  pas.payroll_action_id = pay_act_id
  AND  pjiav.assignment_action_id = pas.assignment_action_id
  AND  pjiav.assignment_id = pas.assignment_id
  AND  (pjiav.transaction_status = decode(trans_stat,'N','A')
       or pjiav.transaction_status = decode(trans_stat,'N','F')
       or pjiav.transaction_status = decode(trans_stat,'Y','A')
       or pjiav.transaction_status = decode(trans_stat,'Y','F')
       or pjiav.transaction_status = decode(trans_stat,'Y','N')
       or pjiav.transaction_status = decode(trans_stat,'Y','U'));  -- Last condition in where clause added for Bug Fix:5487428

Line: 308


  SELECT  legislative_parameters
  INTO  l_legislative_parameters
  FROM  pay_payroll_actions
  WHERE payroll_action_id = P_PAYROLL_ACTION_ID;

Line: 332


    Select p.business_group_id into g_bg_id
    from pay_payroll_actions p
    where p.payroll_action_id = p_payroll_action_id ;

Line: 347


      SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;

Line: 355


      SELECT formula_id INTO l_formula_id
      FROM hr_assignment_sets
      WHERE assignment_set_id = l_ass_set_id;

Line: 365


            SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;

Line: 377


            SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;

Line: 391


          SELECT pay_assignment_actions_s.nextval into l_assact from dual;

Line: 440


  SELECT to_char(pjip.effective_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') year,
         pjip.effective_date,
         pjip.tax_office_name,
         pjip.salary_payer_name,
         pjip.salary_payer_address
  FROM   pay_jp_isdf_pact_v pjip,
         pay_assignment_actions paa
  WHERE  paa.assignment_action_id = p_mag_asg_action_id
  AND    paa.payroll_action_id = pjip.payroll_action_id;

Line: 454


  SELECT pjie.last_name_kana,
         pjie.first_name_kana,
         pjie.last_name,
         pjie.first_name,
         pjie.address,
         pjie.employee_number /* Enh:5671124 : Employee_number addition */
  FROM   pay_jp_isdf_emp_v pjie
  WHERE  pjie.assignment_action_id = p_mag_asg_action_id;

Line: 467


  select decode(pjicd.life_gen_ins_prem,           0,null,pjicd.life_gen_ins_prem)            life_gen_ins_prem,
         decode(pjicd.life_pens_ins_prem,          0,null,pjicd.life_pens_ins_prem)           life_pens_ins_prem,
         decode(pjicd.life_gen_ins_calc_prem,      0,null,pjicd.life_gen_ins_calc_prem)       life_gen_ins_calc_prem,
         decode(pjicd.life_pens_ins_calc_prem,     0,null,pjicd.life_pens_ins_calc_prem)      life_pens_ins_calc_prem,
         decode(pjicd.life_ins_deduction,          0,null,pjicd.life_ins_deduction)           life_ins_deduction,
         decode(pjicd.earthquake_ins_prem,         0,null,pjicd.earthquake_ins_prem)          earthquake_ins_prem,
         decode(pjicd.nonlife_long_ins_prem,       0,null,pjicd.nonlife_long_ins_prem)        nonlife_long_ins_prem,
         decode(pjicd.nonlife_short_ins_prem,      0,null,pjicd.nonlife_short_ins_prem)       nonlife_short_ins_prem,
         decode(pjicd.earthquake_ins_calc_prem,    0,null,pjicd.earthquake_ins_calc_prem)     earthquake_ins_calc_prem,
         decode(pjicd.nonlife_long_ins_calc_prem,  0,null,pjicd.nonlife_long_ins_calc_prem)   nonlife_long_ins_calc_prem,
         decode(pjicd.nonlife_short_ins_calc_prem, 0,null,pjicd.nonlife_short_ins_calc_prem)  nonlife_short_ins_calc_prem,
         decode(pjicd.nonlife_ins_deduction,       0,null,pjicd.nonlife_ins_deduction)        nonlife_ins_deduction,
         decode(pjicd.social_ins_deduction,        0,null,pjicd.social_ins_deduction)         social_ins_deduction,
         decode(pjicd.mutual_aid_deduction,        0,null,pjicd.mutual_aid_deduction)         mutual_aid_deduction,
         decode(pjicd.sp_earned_income_calc,       0,null,pjicd.sp_earned_income_calc)        sp_earned_income_calc,
         decode(pjicd.sp_business_income_calc,     0,null,pjicd.sp_business_income_calc)      sp_business_income_calc,
         decode(pjicd.sp_miscellaneous_income_calc,0,null,pjicd.sp_miscellaneous_income_calc) sp_miscellaneous_income_calc,
         decode(pjicd.sp_dividend_income_calc,     0,null,pjicd.sp_dividend_income_calc)      sp_dividend_income_calc,
         decode(pjicd.sp_real_estate_income_calc,  0,null,pjicd.sp_real_estate_income_calc)   sp_real_estate_income_calc,
         decode(pjicd.sp_retirement_income_calc,   0,null,pjicd.sp_retirement_income_calc)    sp_retirement_income_calc,
         decode(pjicd.sp_other_income_calc,        0,null,pjicd.sp_other_income_calc)         sp_other_income_calc,
         decode(pjicd.sp_income_calc,              0,null,pjicd.sp_income_calc)               sp_income_calc,
         decode(pjicd.spouse_income,               0,null,pjicd.spouse_income)                spouse_income,
         decode(pjicd.spouse_deduction,            0,null,pjicd.spouse_deduction)             spouse_deduction
   from  pay_jp_isdf_calc_dct_v pjicd
   where pjicd.assignment_action_id=p_mag_asg_action_id
   and   pjicd.status <> 'D';

Line: 499


  SELECT pjima.enterprise_contract_prem,
         pjima.pension_prem,
         pjima.disable_sup_contract_prem
  FROM   pay_jp_isdf_mutual_aid_v pjima
  WHERE  pjima.assignment_action_id=p_mag_asg_action_id
  and    pjima.status <> 'D';

Line: 510


  SELECT pjis.full_name_kana,
         pjis.full_name,
         pjis.address,
         pjis.emp_income
  FROM   pay_jp_isdf_spouse_v pjis
  WHERE  pjis.assignment_action_id=p_mag_asg_action_id
  and    pjis.status <> 'D';

Line: 522


  SELECT pjisi.sp_earned_income,
         pjisi.sp_business_income,
         pjisi.sp_business_income_exp,
         pjisi.sp_miscellaneous_income,
         pjisi.sp_miscellaneous_income_exp,
         pjisi.sp_dividend_income,
         pjisi.sp_dividend_income_exp,
         pjisi.sp_real_estate_income,
         pjisi.sp_real_estate_income_exp,
         pjisi.sp_retirement_income,
         pjisi.sp_retirement_income_exp,
         pjisi.sp_other_income,
         pjisi.sp_other_income_exp,
         pjisi.sp_other_income_exp_dct
   FROM  pay_jp_isdf_spouse_inc_v pjisi
   WHERE pjisi.assignment_action_id=p_mag_asg_action_id
   and   pjisi.status <> 'D';

Line: 545


  SELECT pjilg.ins_company_name,
         pjilg.ins_type,
         pjilg.ins_period,
         pjilg.contractor_name,
         pjilg.beneficiary_name,
         pjilg.beneficiary_relship,
         pjilg.annual_prem
  FROM   pay_jp_isdf_life_gen_v pjilg
  WHERE  pjilg.assignment_action_id=p_mag_act_info_id
  and    pjilg.status <> 'D';

Line: 575


  SELECT pjilp.ins_company_name,
         pjilp.ins_type,
         pjilp.ins_period_start_date,
         pjilp.ins_period,
         pjilp.contractor_name,
         pjilp.beneficiary_name,
         pjilp.beneficiary_relship,
         pjilp.annual_prem
  FROM   pay_jp_isdf_life_pens_v pjilp
  WHERE  pjilp.assignment_action_id=p_mag_act_info_id
  and    pjilp.status <> 'D';

Line: 608


  select pjin.nonlife_ins_term_type,
         pjin.ins_company_name,
         pjin.ins_type,
         pjin.ins_period,
         pjin.contractor_name,
         pjin.beneficiary_name,
         pjin.beneficiary_relship,
         pjin.maturity_repayment,
         pjin.annual_prem
  from   pay_jp_isdf_nonlife_v pjin
  where  pjin.assignment_action_id=p_mag_act_info_id
  and    pjin.status <> 'D';

Line: 644


  SELECT pjis.ins_type,
         pjis.ins_payee_name,
         pjis.debtor_name,
         pjis.beneficiary_relship,
         pjis.annual_prem
  FROM   pay_jp_isdf_social_v pjis
  WHERE  pjis.assignment_action_id=p_mag_act_info_id
  and    pjis.status <> 'D';

Line: 719


  vXMLTable.DELETE;

Line: 877


  SELECT count(DISTINCT(action_information_id))
  INTO   count_life_gen
  FROM   pay_jp_isdf_life_gen_v pjilg
  WHERE  pjilg.assignment_action_id = l_mag_asg_action_id
  and    pjilg.status <> 'D';

Line: 891


  SELECT count(DISTINCT(action_information_id))
  INTO   count_life_pens
  FROM   pay_jp_isdf_life_pens_v pjilp
  WHERE  pjilp.assignment_action_id = l_mag_asg_action_id
  and    pjilp.status <> 'D';

Line: 905


  SELECT count(DISTINCT(action_information_id))
  INTO   count_nonlife
  FROM   pay_jp_isdf_nonlife_v pjin
  WHERE  pjin.assignment_action_id = l_mag_asg_action_id
  and    pjin.status <> 'D';

Line: 919


  SELECT count(DISTINCT(action_information_id))
  INTO   count_social
  FROM   pay_jp_isdf_social_v pjis
  WHERE  pjis.assignment_action_id = l_mag_asg_action_id
  and    pjis.status <> 'D';

Line: 1072


      select to_char(ins_period_start_date_lp(d),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
      into ps_date
      from dual;

Line: 1107


      select to_char(ins_period_start_date_lp(e),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
      into ps_date
      from dual;

Line: 1314


  Description : This procedure selects the xml from vXMLTable and writes it
                into a clob variable. This clob variable is then returned.
*****************************************************************************/
PROCEDURE WritetoCLOB (p_write_xml OUT NOCOPY CLOB)
IS
  l_xfdf_string       CLOB;

Line: 1411


  SELECT legislative_parameters
  INTO   l_legislative_parameters
  FROM   pay_payroll_actions
  WHERE  payroll_action_id = l_cur_pact;

Line: 1419


  SELECT paa1.assignment_action_id
  INTO   l_old_assact_id
  FROM   pay_assignment_actions paa,
         pay_assignment_actions paa1
  WHERE  paa.assignment_action_id = l_cur_assact
  AND    paa.assignment_id = paa1.assignment_id
  AND    paa1.payroll_action_id = l_pact_id;

Line: 1462


  vXMLTable.DELETE; -- delete the pl/sql table