ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46F5551A9E05362FB09050512 select pcm.person_id, pcd.to_be_sent_dt, pcd.per_cm_prvdd_id, per.first_name || ' '|| per.last_name ||' '|| per.suffix full_name, per.national_identifier, pdd.town_or_city City, pdd.region_2 State, pdd.postal_code "Zip Code" , pbg.name "Company Name", ptp.name plan_type, pln.name pl_name , pen.enrt_cvg_strt_dt "Coverage Start Date", pen.enrt_cvg_thru_dt "Coverage End Date" , pen.orgnl_enrt_dt, pen.prtt_enrt_rslt_id prtt_enrt_rslt_idm1, pcm.per_in_ler_id , opt.name "Option Name" , nvl(epo.wait_perd_strt_dt , pep.wait_perd_strt_dt ) wait_perd_Strt_dt , nvl(epo.wait_perd_cmpltn_date,pep.wait_perd_cmpltn_dt) wait_ped_cmpln_dt, prol.name "Plan Administrator Name" , loc.address_line_1, loc.address_line_2, loc.address_line_3, loc.town_or_city ||' '|| loc.region_2 || ' '|| loc.postal_code loc_City , loc.telephone_number_1 loc_ph_1, loc.telephone_number_2 loc_ph_2, loc.telephone_number_3 loc_ph_3, BEN_BENHIPAA_XMLP_PKG.cf_wait_start_dtformula(pen.enrt_cvg_thru_dt, pen.orgnl_enrt_dt, nvl ( epo.wait_perd_strt_dt , pep.wait_perd_strt_dt ),pcd.per_cm_prvdd_id) CF_wait_start_dt, BEN_BENHIPAA_XMLP_PKG.cf_wait_perd_cmpltn_dtformula(pen.enrt_cvg_thru_dt, pen.orgnl_enrt_dt, nvl ( epo.wait_perd_cmpltn_date , pep.wait_perd_cmpltn_dt )) CF_wait_perd_cmpltn_dt from ben_per_cm_prvdd_f pcd, ben_per_cm_f pcm, ben_cm_typ_usg_f ctu, ben_cm_typ_f cct, ben_per_cm_usg_f pcu,per_all_people_f per, per_all_assignments_f ass , per_addresses pdd, per_business_groups pbg, ben_prtt_enrt_rslt_f pen, ben_elig_per_f pep, ben_elig_per_opt_f epo, ben_pl_f pln, ben_pl_regn_f prg, ben_regn_f reg , ben_pl_typ_f ptp, ben_pgm_f pgm, ben_oipl_f oipl, ben_opt_f opt, ben_popl_org_f porg, ben_popl_org_role_f prol, hr_all_organization_units org, hr_locations loc where pcm.per_cm_id = pcd.per_cm_id and :p_effective_date between pcm.effective_start_date and pcm.effective_end_Date and :p_business_group_id = pcm.business_group_id and pcm.person_id = nvl(:p_person_id, pcm.person_id) and pcd.sent_dt is null and :p_effective_date between pcd.effective_start_date and pcd.effective_end_Date and pcm.business_group_id = pcd.business_group_id and pcm.cm_typ_id = cct.cm_typ_id and pcm.business_group_id = cct.business_group_id and :p_effective_date between cct.effective_start_date and cct.effective_end_Date and cct.shrt_name = 'HIPAAPRT' and pcu.per_cm_id = pcm.per_cm_id and pcu.cm_typ_usg_id = ctu.cm_typ_usg_id and :p_effective_date between pcu.effective_start_date and pcu.effective_end_Date and :p_effective_date between ctu.effective_start_date and ctu.effective_end_Date and pcm.business_group_id = pcu.business_group_id and pcm.business_group_id = ctu.business_group_id and pcm.person_id = per.person_id and nvl(per.benefit_group_id,-1) = nvl(:P_bnfts_grp_id,nvl(per.benefit_group_id,-1) ) and :p_effective_date between per.effective_start_date and per.effective_end_Date and per.person_id = ass.person_id and ass.primary_flag = 'Y' and ( ass.assignment_type NOT IN ('C','B') or (ass.assignment_type = 'B' and not exists (select ass1.assignment_type from per_all_assignments_f ass1 where per.person_id = ass1.person_id and ass1.assignment_type = 'E' and ass1.primary_flag = 'Y' and nvl(ass1.location_id,-1) = nvl(:p_location_id ,nvl(ass1.location_id,-1) ) and nvl(ass1.organization_id,-1) = nvl(:P_organization_id,nvl(ass1.organization_id,-1) ) and :p_effective_date between ass1.effective_start_date and ass1.effective_end_Date) ) ) and nvl(ass.location_id,-1) = nvl(:p_location_id ,nvl(ass.location_id,-1) ) and nvl(ass.organization_id,-1) = nvl(:P_organization_id,nvl(ass.organization_id,-1) ) and :p_effective_date between ass.effective_start_date and ass.effective_end_Date and pcm.business_group_id = per.business_group_id and pdd.person_id = per.person_id and nvl(pdd.primary_flag,'N') = 'Y' and pcm.business_group_id = pdd.business_group_id and pcm.business_group_id = pbg.business_group_id and pcm.person_id = pen.person_id and pen.prtt_enrt_rslt_Stat_cd is null and :p_effective_date between pen.effective_start_date and pen.effective_end_Date and ctu.pl_typ_id = pen.pl_typ_id and pen.pgm_id = nvl(:P_Pgm_id , pen.pgm_id) and pen.pgm_id = pgm.pgm_id and pcm.business_group_id = pen.business_group_id and :p_effective_date between pgm.effective_start_date and pgm.effective_end_Date and pen.pl_id = pln.pl_id and :p_effective_date between pln.effective_start_date and pln.effective_end_Date and pen.business_group_id = pln.business_group_id and pln.pl_id = prg.pl_id and :p_effective_date between prg.effective_start_date and prg.effective_end_Date and pln.business_group_id = prg.business_group_id and prg.regn_id = reg.regn_id and :p_effective_date between reg.effective_start_date and reg.effective_end_Date and prg.business_group_id = reg.business_group_id and reg.STTRY_CITN_NAME = 'HIPAA' and pen.pl_typ_id = ptp.pl_typ_id and :p_effective_date between ptp.effective_start_date and ptp.effective_end_Date and pen.business_group_id = ptp.business_group_id and pcm.person_id = pep.person_id and pen.per_in_ler_id = pep.per_in_ler_id and pen.pgm_id = pep.pgm_id and pen.pl_id = pep.pl_id and pcm.business_group_id = pep.business_group_id and :p_effective_Date between pep.effective_start_date and pep.effective_end_Date and pen.oipl_id = oipl.oipl_id (+) and :p_effective_date between oipl.effective_start_date (+) and oipl.effective_end_Date (+) and oipl.opt_id = opt.opt_id (+) and :P_effective_date between opt.effective_start_date (+) and opt.effective_end_Date (+) and pep.elig_per_id = epo.elig_per_id (+) and (oipl.opt_id = epo.opt_id or oipl.opt_id is null ) and :p_effective_date between epo.effective_start_date (+) and epo.effective_end_Date (+) and ( (pen.pgm_id = porg.pgm_id and porg.pl_id is null ) and not exists (select porg1.popl_org_id from ben_popl_org_f porg1 where porg1.pl_id = pen.pl_id and porg1.pgm_id is null ) or ( pen.pl_id = porg.pl_id and porg.pgm_id is null )) and porg.popl_org_id = prol.popl_org_id (+) and ( prol.org_role_typ_cd = 'ADM' or prol.popl_org_id is null ) and pbg.organization_id = org.organization_id and org.location_id = loc.location_id Y BEN BENHIPAA_XML AR BEN خطاب HIPAA (بتنسيق XML) Imported from BI Publisher Description: خطاب HIPAA Application: المزايا المتقدمة Source: خطاب HIPAA (بتنسيق XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG D BEN HIPAA-Schreiben Imported from BI Publisher Description: HIPAA-Schreiben Application: Advanced Benefits Source: HIPAA-Schreiben (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG E BEN Carta de HIPAA Imported from BI Publisher Description: Carta de HIPAA Application: Advanced Benefits Source: Carta de HIPAA (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG F BEN Lettre HIPAA Imported from BI Publisher Description: Lettre HIPAA Application: Advanced Benefits Source: Lettre HIPAA (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG I BEN Lettera HIPAA Imported from BI Publisher Description: Lettera HIPAA Application: Advanced Benefits Source: Lettera HIPAA (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG JA BEN HIPAAレター Imported from BI Publisher Description: HIPAAレター Application: Advanced Benefits Source: HIPAAレター(XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG KO BEN HIPAA 서신 Imported from BI Publisher Description: HIPAA 서신 Application: Advanced Benefits Source: HIPAA 서신(XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG PTB BEN Carta HIPAA Imported from BI Publisher Description: Carta HIPAA Application: Advanced Benefits Source: Carta HIPAA (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG RU BEN Письмо HIPAA Imported from BI Publisher Description: Письмо HIPAA Application: Пособия Source: Письмо HIPAA (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG S BEN HIPAA-brev Imported from BI Publisher Description: HIPAA-brev Application: Advanced Benefits Source: HIPAA-brev (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG TR BEN HIPAA Yazışması Imported from BI Publisher Description: HIPAA Yazışması` Application: Kazanç Yönetimi Source: HIPAA Yazışması (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG US BEN HIPAA Letter Imported from BI Publisher Description: HIPAA Letter` Application: Advanced Benefits Source: HIPAA Letter (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG ZHS BEN HIPAA 信函 Imported from BI Publisher Description: HIPAA 信函` Application: 高级福利 Source: HIPAA 信函 (XML) Short Name: BENHIPAA_XML DB package: BEN_BENHIPAA_XMLP_PKG BI Publisher :p_bnfts_grp_id :p_business_group_id :p_conc_request_id :p_effective_date :p_location_id :p_organization_id :p_person_id :p_pgm_id 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') Y AR مجموعة العمل مجموعة الأعمال D Unternehmensgruppe Unternehmensgruppe E Grupo de Trabajo Grupo de Trabajo F Entité professionnelle Entité professionnelle I Business group Business group JA ビジネス・グループ ビジネス・グループ KO 업무 그룹 업무 그룹 PTB Grupo Funcional Grupo Funcional RU Бизнес-группа Бизнес-группа S Verksamhetsgrupp Verksamhetsgrupp TR İş Grubu İş Grubu US Business Group Business Group ZHS 业务组 业务组 2 10 :p_effective_date Date select fnd_date.date_to_displaydate(sysdate) from sys.dual Y AR PER_DATES تاريخ الفعالية D PER_DATES Gültigkeitsdatum E PER_DATES Fecha en Vigor F PER_DATES Date de validité I PER_DATES Data validità JA PER_DATES 有効日 KO PER_DATES 유효 일자 PTB PER_DATES Data Efetivação RU PER_DATES Дата действия S PER_DATES Giltighetsdatum TR PER_DATES Geçerlilik Tarihi US PER_DATES Effective Date ZHS PER_DATES 有效日期 3 20 :p_person_id LOV Oracle BEN_US_PERSON Y Y select a.person_id id, a.full_name value, null description from per_people_f a where a.business_group_id= fnd_global.per_business_group_id and to_date(:$flex$.per_dates,'YYYY/MM/DD HH24:MI:SS') between a.effective_start_date and a.effective_end_date order by a.last_name,a.first_name AR اسم الشخص الشخص المراد معالجة بياناته D Personenname Zu verarbeitende Person E Nombre de Persona Persona para procesar F Nom de la personne Personne à traiter I Nome persona Persona da elaborare JA 個人名 処理対象者 KO 개인 이름 처리 대상 개인 PTB Nome da Pessoa Pessoa a ser processada RU Имя лица Лицо для обработки S Namn Person att bearbeta TR Kişi Adı İşlenecek kişi US Person Name Person to process ZHS 人员姓名 要处理的人员 4 30 :p_organization_id LOV Oracle HR_US_ORGANIZATIONS Y select hou.organization_id id, hou.name value, null description from hr_organization_units hou,hr_organization_information hoi where hou.business_group_id=fnd_global.per_business_group_id and hou.organization_id=hoi.organization_id and hoi.org_information_context='CLASS' and hoi.org_information1='HR_ORG' order by hou.name AR المنشأة المنشأة D Organisation Organisation E Organización Organización F Organisation Organisation I Organizzazione Organizzazione JA 組織 組織 KO 조직 조직 PTB Organização Organização RU Организация Организация S Organisation Organisation TR Organizasyon Organizasyon US Organization Organizaion ZHS 组织 组织 5 40 :p_bnfts_grp_id LOV Oracle BEN_BENEFITS_GROUP Y select benfts_grp_id id, name value, null description from ben_benfts_grp where business_group_id= fnd_global.per_business_group_id order by name AR المجموعة التابع لها المجموعة التابع لها D Leistungsgruppe Leistungsgruppe E Grupo de Beneficios Grupo de beneficios F Groupe d'avantages Groupe d'avantages I Gruppo benefit Gruppo di benefit JA 福利厚生グループ 福利厚生グループ KO 복리후생 그룹 복리후생 그룹 PTB Grupo de Benefícios Grupo de Benefícios RU Группа пособий Группа пособий S Förmånsgrupp Förmånsgrupp TR Kazanç Grubu Kazanç Grubu US Benefit Group Benefits Group ZHS 福利组 福利组 6 50 :p_location_id LOV Oracle HR_US_LOCATIONS Y select hl.location_id id, hl.location_code value, null description from hr_locations hl where location_use='HR' order by location_code AR الموقع الموقع D Standort Standort E Ubicación Ubicación F Lieu Lieu I Ubicazione Ubicazione JA 事業所 事業所 KO 위치 위치 PTB Local Local RU Расположение Расположение S Placering Adress TR Yerleşim Yerleşim US Location Location ZHS 地点 地点 7 60 :p_pgm_id LOV Oracle BEN_PGM Y select pgm_id id, name value, null description from ben_pgm_f a where a.business_group_id= fnd_global.per_business_group_id and a.pgm_typ_cd<>'GSP' and a.effective_start_date=(select max(b.effective_start_date) from ben_pgm_f b where a.pgm_id=b.pgm_id) order by name AR البرنامج اسم البرنامج D Programm Programmbezeichnung E Programa Nombre de programa F Programme Nom du programme I Programma Nome programma JA プログラム プログラム名 KO 프로그램 프로그램명 PTB Programa Nome do Programa RU Программа Имя программы S Program Program TR Program Program Adı US Program Programe Name ZHS 方案 方案名称 :$flex$.per_dates PER_DATES Y Person Name