[Home] [Help]
75: select /*+ ORDERED */
76: nvl(nvl(pp.prl_information1, hoi.org_information2),'CTR_EE')
77: from /* Payroll and Business Group details */
78: pay_all_payrolls_f pp,
79: hr_organization_information hoi
80: where pp.payroll_id = g_payroll_id
81: and g_effective_date
82: between pp.effective_start_date and pp.effective_end_date
83: and hoi.organization_id(+) = pp.business_group_id
191: hoi2.org_information1 tax_office_name,
192: hoi1.org_information1 salary_payer_name,
193: hoi1.org_information6||hoi1.org_information7||hoi1.org_information8 salary_payer_address
194: from hr_all_organization_units hou,
195: hr_organization_information hoi1,
196: hr_organization_information hoi2
197: where hou.organization_id = g_organization_id
198: and hoi1.organization_id(+) = hou.organization_id
199: and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
192: hoi1.org_information1 salary_payer_name,
193: hoi1.org_information6||hoi1.org_information7||hoi1.org_information8 salary_payer_address
194: from hr_all_organization_units hou,
195: hr_organization_information hoi1,
196: hr_organization_information hoi2
197: where hou.organization_id = g_organization_id
198: and hoi1.organization_id(+) = hou.organization_id
199: and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
200: and hoi2.organization_id(+) = hou.organization_id
1183: paei.aei_information8 beneficiary_name,
1184: paei.aei_information9 beneficiary_relship,
1185: fnd_number.canonical_to_number(paei.aei_information10) linc_prem
1186: from per_assignment_extra_info paei,
1187: hr_organization_information hoi
1188: where paei.assignment_id = p_assignment_id
1189: and paei.information_type = 'JP_ASS_LIG_INFO'
1190: -- include PC for customized valuset
1191: --and paei.aei_information1 in ('GIP','LINC')
1198: and hoi.organization_id = g_organization_id
1199: -- irregular case for duplicate org
1200: and not exists(
1201: select null
1202: from hr_organization_information hoi2
1203: where hoi2.org_information1 = hoi.org_information1
1204: and hoi2.org_information_context = hoi.org_information_context
1205: and hoi2.organization_id = hoi.organization_id
1206: and hoi2.org_information_id < hoi.org_information_id)
1233: paei.aei_information9 beneficiary_name,
1234: paei.aei_information10 beneficiary_relship,
1235: fnd_number.canonical_to_number(paei.aei_information11) linc_prem
1236: from per_assignment_extra_info paei,
1237: hr_organization_information hoi
1238: where paei.assignment_id = p_assignment_id
1239: and paei.information_type = 'JP_ASS_LIP_INFO'
1240: -- include PC for customized valuset
1241: --and paei.aei_information1 in ('GIP','LINC')
1248: and hoi.organization_id = g_organization_id
1249: -- irregular case for duplicate org
1250: and not exists(
1251: select null
1252: from hr_organization_information hoi2
1253: where hoi2.org_information1 = hoi.org_information1
1254: and hoi2.org_information_context = hoi.org_information_context
1255: and hoi2.organization_id = hoi.organization_id
1256: and hoi2.org_information_id < hoi.org_information_id)
1284: paei.aei_information9 beneficiary_relship,
1285: decode(to_char(sign(g_effective_date - c_st_upd_date_2007)),'-1',paei.aei_information10,null) maturity_repayment,
1286: fnd_number.canonical_to_number(paei.aei_information11) annual_prem
1287: from per_assignment_extra_info paei,
1288: hr_organization_information hoi
1289: where paei.assignment_id = p_assignment_id
1290: and paei.information_type = 'JP_ASS_AI_INFO'
1291: -- include PC for customized valuset
1292: --and paei.aei_information13 = 'AP'
1299: and hoi.organization_id = g_organization_id
1300: -- irregular case for duplicate org
1301: and not exists(
1302: select null
1303: from hr_organization_information hoi2
1304: where hoi2.org_information1 = hoi.org_information1
1305: and hoi2.org_information_context = hoi.org_information_context
1306: and hoi2.organization_id = hoi.organization_id
1307: and hoi2.org_information_id < hoi.org_information_id)