ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46F5251A9E05362FB09050512Y select distinct sysdate dptn_current_date, cont.person_id dpnt_prtt_per_id, pcd.to_be_sent_dt dpnt_to_be_Sent_Dt , per.first_name || ' '|| per.last_name||' '||per.suffix dpnt_full_name, per.first_name || ' '|| per.last_name dpnt_PER_name, per.national_identifier dpnt_ssn_id, decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line1,pdd.address_line1 ) dpnt_addr1, decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line2,pdd.address_line2) dpnt_addr2, decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line3,pdd.address_line3) dpnt_addr3, decode(cont.RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.town_or_city,pdd.town_or_city) dpnt_City, decode(cont.RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.region_2,pdd.region_2) dpnt_State, decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.postal_code,pdd.postal_code)"dpnt_Zip Code" , pbg.name "dpnt_Company_Name", pdp.cvg_strt_dt "dpnt_Coverage_Start_Date", prol.name "dpnt_Plan Administrator Name" , pcm.per_cm_id , loc.address_line_1 ||loc.address_line_2||loc.address_line_3 dpnt_loc_address, loc.address_line_1 loc_address1 , loc.address_line_2 loc_address2 , loc.address_line_3 loc_address3 , loc.town_or_city dpnt_loc_City, loc.region_2 dpnt_loc_State, loc.postal_code "dpnt_loc_Zip_Code" , loc.telephone_number_1 dpnt_loc_ph_1, loc.telephone_number_2 dpnt_loc_ph_2, loc.telephone_number_3 dpnt_loc_ph_3, pdp.dpnt_person_id, BEN_BENCBRIN_XMLP_PKG.CF_Dep_Check_FlagFormula(pdp.dpnt_person_id,:pl_typ_id,:pgm_id,pdp.cvg_strt_dt,:p_effective_date,:P_BUSINESS_GROUP_ID) CF_DEP_CHECK_FLAG from PER_CONTACT_RELATIONSHIPS cont , 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_addresses pdd, per_addresses prtpdd, per_business_groups pbg, ben_elig_cvrd_dpnt_f pdp, ben_prtt_enrt_rslt_f pen, ben_pgm_f pgm, ben_pl_f pln , ben_pl_regn_f prg, ben_regn_f reg , ben_popl_org_f porg, ben_popl_org_role_f prol, hr_all_organization_units org , hr_locations loc where cont.business_group_id = :p_business_group_id and contact_type = 'S' /* Spouse*/ and :p_effective_date between nvl(cont.date_start,:p_effective_date ) and nvl(cont.date_end, :p_effective_date ) and cont.person_id = pcm.person_id and cont.person_id = :per_id and :p_effective_date between pcm.effective_start_date and pcm.effective_end_Date and cont.business_group_id = pcm.business_group_id and pcm.per_cm_id = pcd.per_cm_id and pcm.per_cm_id = :per_cm_id --and pcd.sent_dt is null AND pcd.per_cm_prvdd_stat_cd = 'ACTIVE' and pcm.business_group_id = pcd.business_group_id and :p_effective_date between pcd.effective_start_date and pcd.effective_end_Date --- and pcm.cm_typ_id = cct.cm_typ_id and cct.shrt_name = 'CBRININF' and pcm.business_group_id = cct.business_group_id and :p_effective_date between cct.effective_start_date and cct.effective_end_Date --- and pcm.per_cm_id = pcu.per_cm_id and pcm.business_group_id = pcu.business_group_id and :p_effective_date between pcu.effective_start_date and pcu.effective_end_Date --- and pcu.cm_typ_usg_id = ctu.cm_typ_usg_id and pcm.business_group_id = ctu.business_group_id and :p_effective_date between ctu.effective_start_date and ctu.effective_end_Date --- and cont.contact_person_id = per.person_id and cont.business_group_id = per.business_group_id and :p_effective_date between per.effective_start_date and per.effective_end_Date --- and per.person_id = pdd.person_id (+) and (pdd.primary_flag = 'Y' or pdd.person_id is null) and per.business_group_id = pdd.business_group_id (+) --- and cont.person_id = prtpdd.person_id (+) and (prtpdd.primary_flag = 'Y' or prtpdd.person_id is null) and per.business_group_id = pdd.business_group_id (+) --- and pcm.business_group_id = pbg.business_group_id --- and per.person_id = pdp.dpnt_person_id and pcm.business_group_id = pdp.business_group_id and :p_effective_date between pdp.effective_start_date and pdp.effective_end_Date --- and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and pen.per_in_ler_id = pcm.per_in_ler_id and pen.enrt_cvg_thru_dt = to_date('12/31/4712','MM/DD/YYYY') and pen.prtt_enrt_rslt_Stat_cd is null and pen.sspndd_flag = 'N' and ctu.pl_typ_id = pen.pl_typ_id and pcm.business_group_id = pen.business_group_id and :p_effective_date between pen.effective_start_date and pen.effective_end_Date --- and pen.pgm_id = pgm.pgm_id and pen.business_group_id = pgm.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 pen.business_group_id = pln.business_group_id and pen.effective_start_date between pln.effective_start_date and pln.effective_end_Date and pln.pl_id = prg.pl_id AND pln.invk_flx_cr_pl_flag = 'N' AND pln.imptd_incm_calc_cd is null and pln.business_group_id = prg.business_group_id and :p_effective_date between prg.effective_start_date and prg.effective_end_Date --- and prg.regn_id = reg.regn_id and reg.STTRY_CITN_NAME = 'COBRA' and prg.business_group_id = reg.business_group_id and :p_effective_date between reg.effective_start_date and reg.effective_end_Date --- and ( (pen.pgm_id = porg.pgm_id and porg.pl_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 porg.organization_id = org.organization_id --- and org.location_id = loc.location_id BEN BENCBRIN_XML AR BEN البيانات الأولية لمزايا COBRA Imported from BI Publisher Application: المزايا المتقدمة Source: البيانات الأولية لمزايا COBRA (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG D BEN COBRA-Leistungen: Einführung Imported from BI Publisher Application: Advanced Benefits Source: COBRA-Leistungen: Einführung (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG E BEN Información Inicial de Beneficios COBRA Imported from BI Publisher Application: Advanced Benefits Source: Información Inicial de Beneficios COBRA (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG F BEN Informations initiales des avantages COBRA Imported from BI Publisher Application: Advanced Benefits Source: Informations initiales des avantages COBRA (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG I BEN Informazioni iniziali benefit COBRA Imported from BI Publisher Application: Advanced Benefits Source: Informazioni iniziali benefit COBRA (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG JA BEN COBRA福利厚生初期化情報 Imported from BI Publisher Application: Advanced Benefits Source: COBRA福利厚生初期化情報(XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG KO BEN COBRA 복리후생 초기 정보 Imported from BI Publisher Application: Advanced Benefits Source: COBRA 복리후생 초기 정보(XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG PTB BEN COBRA - Informações Iniciais do Benefício Imported from BI Publisher Application: Advanced Benefits Source: COBRA - Informações Iniciais do Benefício (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG RU BEN Начальные сведения о пособии COBRA Imported from BI Publisher Application: Пособия Source: Начальные сведения о пособии COBRA (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG S BEN Ursprunglig information för COBRA-förmån Imported from BI Publisher Application: Advanced Benefits Source: Ursprunglig information för COBRA-förmån (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG TR BEN COBRA (Konsolide Omnibus Bütçe Uzlaşma Yasası) Yan Hak Başlangıç Bilgileri Imported from BI Publisher Application: Kazanç Yönetimi Source: COBRA (Konsolide Omnibus Bütçe Uzlaşma Yasası) Yan Hak Başlangıç Bilgileri (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG US BEN COBRA Benefit Initial Information Imported from BI Publisher Application: Advanced Benefits Source: COBRA Benefit Initial Information (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG ZHS BEN COBRA 福利初始信息 Imported from BI Publisher Application: 高级福利 Source: COBRA 福利初始信息 (XML) Short Name: BENCBRIN_XML DB package: BEN_BENCBRIN_XMLP_PKG BI Publisher :cp_effective_date :p_benfts_grp_id :p_business_group_id :p_effective_date :p_location_id :p_organization_id :p_person_id :per_cm_id :per_id :pgm_id :pl_typ_id 1 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 di validità JA PER_DATES 有効日 KO PER_DATES 유효 일자 PTB PER_DATES Data de Efetivação RU PER_DATES Дата действия S PER_DATES Giltighetsdatum TR PER_DATES Geçerlilik Tarihi US PER_DATES Effective Date ZHS PER_DATES 有效日期 2 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 que se va a 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 人员姓名 要处理的人员 3 30 :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 业务组 业务组 4 40 :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 Organization ZHS 组织 组织 5 50 :p_benfts_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 Leistungsgruppen Leistungsgruppen E Grupos de Beneficios Grupos de Beneficios F Groupes d'avantages Groupes d'avantages I Gruppi benefit Gruppi di benefit JA 福利厚生グループ 福利厚生グループ KO 복리후생 그룹 복리후생 그룹 PTB Grupos de Benefícios Grupos de Benefícios RU Группы пособий Группы пособий S Förmånsgrupper Förmånsgrupper TR Yan Hak Grupları Yan Hak Grupları US Benefit Groups Benefit Groups ZHS 福利组 福利组 6 60 :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 Standorte Standorte E Ubicaciones Ubicaciones F Lieux Lieux I Ubicazioni Ubicazioni JA 事業所 事業所 KO 위치 위치 PTB Locais Locais RU Расположения Расположения S Platser Platser TR Yerler Yerler US Locations Locations ZHS 地点 地点