ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46F5351A9E05362FB09050512Y select sysdate current_date, per.person_id per_id, per.business_group_id, per.last_name, per.first_name, RTRIM(per.first_name) ||' '|| RTRIM(per.last_name)|| ' '||Rtrim(per.suffix) fname, RTRIM(per.first_name) ||' '|| RTRIM(per.last_name) person_name, per.employee_number, per.national_identifier , pcm.lf_evt_ocrd_dt pcm_ocrd_dt, pcd.address_id pcd_adr_id, pcd.per_cm_prvdd_id , ler.name ler_name, ler.typ_cd Ler_type, pil.per_in_ler_id pil_id, epe.enrt_cvg_strt_dt, epe.enrt_cvg_strt_dt+60 resp_dt, epe.enrt_cvg_strt_dt-1 as enrt_cvg_thru_dt, pln.name pl_name, pln.pl_id pl_id , pln.COBRA_PYMT_DUE_DY_NUM, opt.name opt_name, ecr.cmcd_val, ecr.cmcd_acty_ref_perd_cd, adr.primary_flag, adr.address_line1 addr1, adr.address_line2 addr2, adr.address_line3 addr3, adr.town_or_city city, adr.region_2 state, adr.postal_code zip, adr.country country, cpr.name admin_name, pbg.name org_name, org.name admin_org_name, loc.address_line_1 loc_addr1, loc.address_line_2 loc_addr2, loc.address_line_3 loc_addr3, loc.town_or_city loc_city, loc.region_2 loc_state, loc.postal_code loc_zip, loc.country loc_country, loc.telephone_number_1 loc_phone, bcq.cbr_elig_perd_strt_dt, bcq.cbr_elig_perd_end_dt, months_between ( bcq.cbr_elig_perd_end_dt+1, bcq.cbr_elig_perd_strt_dt) as due_months, BEN_BEN_US_COBNL_XMLP_PKG.cf_1formula(cpr.name, loc.address_line_1, loc.address_line_2, loc.address_line_3,loc.town_or_city, loc.region_2, loc.postal_code, loc.telephone_number_1,pcd.per_cm_prvdd_id) CF_admin_detail, BEN_BEN_US_COBNL_XMLP_PKG.cp_effective_date_p cp_effective_date, BEN_BEN_US_COBNL_XMLP_PKG.CP_ler_text_p(ler.typ_cd,ler.name,pcm.lf_evt_ocrd_dt) CP_ler_text, BEN_BEN_US_COBNL_XMLP_PKG.cf_cmcd_acty_rep_prdformula(ecr.cmcd_acty_ref_perd_cd) CF_cmcd_acty_rep_prd from ben_per_cm_f pcm ,ben_per_cm_prvdd_f pcd ,ben_cm_typ_f cct ,per_all_people_f per ,per_addresses adr ,ben_per_in_ler pil ,ben_ler_f ler ,ben_elig_per_elctbl_chc epe ,ben_enrt_rt ecr ,ben_pgm_f pgm ,ben_pl_f pln ,ben_opt_f opt ,ben_oipl_f oip ,ben_pl_regn_f prg ,ben_regn_f reg ,ben_popl_org_f cpo ,ben_popl_org_role_f cpr ,hr_all_organization_units org ,hr_locations_all loc ,hr_all_organization_units pbg ,ben_cbr_quald_bnf bcq ,ben_cbr_per_in_ler bcp where pcm.business_group_id = :P_business_group_id and (:P_person_id is null or pcm.person_id = :P_person_id) AND pcm.per_cm_id = pcd.per_cm_id AND pcd.per_cm_prvdd_stat_cd = 'ACTIVE' AND pcd.sent_dt is NULL AND pcm.cm_typ_id = cct.cm_typ_id AND cct.shrt_name = 'CBRANOTF' and pcm.person_id = per.person_id and (:P_bnfts_grp_id is null or per.benefit_group_id= :P_bnfts_grp_id) and ( (:p_location_id is null and :P_organization_id is null ) or ( exists ( select 'x' from per_all_assignments_f ass where (:p_location_id is null or ass.location_id= :p_location_id ) and ( :P_organization_id is null or ass.organization_id = :P_organization_id) and per.person_id = ass.person_id and ass.primary_flag = 'Y' and ass.assignment_type <> 'C' and :p_effective_date between ass.effective_start_date AND ass.effective_end_date ) ) ) AND ( (pcd.address_id is null and per.person_id = adr.person_id AND nvl(adr.primary_flag,'Y') = 'Y' and :p_effective_date between adr.DATE_FROM and nvl(adr.DATE_TO,to_date('31-12-4712','DD-MM-YYYY')) ) OR (pcd.address_id is not null and pcd.address_id = adr.address_id) ) and pcm.per_in_ler_id = pil.per_in_ler_id and pil.per_in_ler_stat_cd NOT IN ('BCKDT', 'VOIDD') and pil.ler_id = ler.ler_id and pcm.per_in_ler_id = epe.per_in_ler_id and epe.elctbl_flag = 'Y' AND epe.pgm_id = pgm.pgm_id (+) AND (:p_pgm_id is null or epe.pgm_id = :p_pgm_id ) and (epe.pgm_id is null or pgm.pgm_typ_cd IN ('COBRANFLX', 'COBRAFLX') ) AND epe.pl_id = pln.pl_id AND epe.oipl_id = oip.oipl_id(+) AND oip.opt_id = opt.opt_id(+) AND pln.invk_flx_cr_pl_flag = 'N' AND pln.imptd_incm_calc_cd is null AND epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id (+) and pln.pl_id = prg.pl_id and reg.regn_id = prg.regn_id and reg.sttry_citn_name = 'COBRA' and ( ( cpo.pl_id is not null and cpo.pgm_id is null and cpo.pl_id = pln.pl_id AND cpr.popl_org_id = cpo.popl_org_id AND cpr.org_role_typ_cd = 'ADM' ) or (cpo.pgm_id is not null and cpo.pl_id is null and cpo.pgm_id = pgm.pgm_id and pln.pl_id not in ( select cpo.pl_id from ben_popl_org_f cpo1 where cpo1.pl_id is not null and cpo1.pgm_id is null and cpo1.pl_id = pln.pl_id ) AND cpr.popl_org_id = cpo.popl_org_id AND cpr.popl_org_id = cpo.popl_org_id ) ) AND cpo.organization_id = org.organization_id AND org.location_id = loc.location_id and pbg.organization_id = pcm.business_group_id and bcp.per_in_ler_id = pcm.per_in_ler_id and bcp.cbr_quald_bnf_id = bcq.cbr_quald_bnf_id and bcq.QUALD_BNF_PERSON_ID = pcm.person_id and bcq.QUALD_BNF_FLAG = 'Y' and bcq.pgm_id = epe.pgm_id and (bcq.pl_typ_id is null or bcq.pl_typ_id = epe.pl_typ_id) AND :p_effective_date between pcm.effective_start_date AND pcm.effective_end_date AND :p_effective_date between pcd.effective_start_date AND pcd.effective_end_date AND :p_effective_date between cct.effective_start_date AND cct.effective_end_date AND :p_effective_date between per.effective_start_date AND per.effective_end_date and :p_effective_date between ler.effective_start_date AND ler.effective_end_date and :p_effective_date between pgm.effective_start_date (+) AND pgm.effective_end_date (+) and :p_effective_date between pln.effective_start_date AND pln.effective_end_date and :p_effective_date between oip.effective_start_date (+) AND oip.effective_end_date (+) and :p_effective_date between opt.effective_start_date (+) AND opt.effective_end_date (+) and :p_effective_date between prg.effective_start_date AND prg.effective_end_date and :p_effective_date between reg.effective_start_date AND reg.effective_end_date and :p_effective_date between cpo.effective_start_date AND cpo.effective_end_date and :p_effective_date between cpr.effective_start_date AND cpr.effective_end_date --order by pcd.to_be_sent_dt, per.last_name ,epe.enrt_cvg_strt_dt order by fname,per_id,business_group_id,last_name,first_name,person_name,employee_number,national_identifier,pcm_ocrd_dt, pcd_adr_id,current_date,Ler_type,per_cm_prvdd_id,admin_org_name,ler_name,pil_id,primary_flag,addr1,addr2,addr3,city,state,zip, country,admin_name,org_name,loc_addr1,loc_addr2,loc_addr3,loc_city,loc_state,loc_zip,loc_country,loc_phone,cbr_elig_perd_strt_dt,cbr_elig_perd_end_dt, due_months,pl_name,enrt_cvg_thru_dt,resp_dt,enrt_cvg_strt_dt,pl_id,COBRA_PYMT_DUE_DY_NUM,cmcd_acty_ref_perd_cd BEN BENCOBN1_XML AR BEN خطاب تبليغ بمزايا COBRA (بتنسيق XML) Imported from BI Publisher Description: خطاب تبليغ بمزايا COBRA Application: المزايا المتقدمة Source: خطاب تبليغ بمزايا COBRA (بتنسيق XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG D BEN COBRA Benachrichtigung über Leistungen Imported from BI Publisher Description: COBRA Benachrichtigung über Leistungen Application: Advanced Benefits Source: COBRA Benachrichtigung über Leistungen (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG E BEN Carta de Notificación de Beneficios de COBRA Imported from BI Publisher Description: Carta de notificación de beneficios de COBRA Application: Advanced Benefits Source: Carta de Notificación de Beneficios de COBRA (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG F BEN Lettre de notification d'avantage COBRA Imported from BI Publisher Description: Lettre de notification d'avantage COBRA Application: Advanced Benefits Source: Lettre de notification d'avantage COBRA (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG I BEN Lettera notifica benefit COBRA Imported from BI Publisher Description: Lettera notifica benefit COBRA Application: Advanced Benefits Source: Lettera notifica benefit COBRA (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG JA BEN COBRA福利厚生通知書 Imported from BI Publisher Description: COBRA福利厚生通知書 Application: Advanced Benefits Source: COBRA福利厚生通知書(XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG KO BEN COBRA 복리후생 통지서 Imported from BI Publisher Description: COBRA 복리후생 통지서 Application: Advanced Benefits Source: COBRA 복리후생 통지서(XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG PTB BEN Carta de Notificação de Benefício COBRA Imported from BI Publisher Description: Carta de Notificação de Benefício COBRA Application: Advanced Benefits Source: Carta de Notificação de Benefício COBRA (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG RU BEN Уведомление о пособии COBRA Imported from BI Publisher Description: Уведомление о пособии COBRA Application: Пособия Source: Уведомление о пособии COBRA (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG S BEN Brev om underrättelse om COBRA-förmån Imported from BI Publisher Description: Brev om underrättelse om COBRA-förmån Application: Advanced Benefits Source: Brev om underrättelse om COBRA-förmån (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG TR BEN COBRA Kazanç Bildirim Yazışması Imported from BI Publisher Description: COBRA Kazanç Bildirim Yazışması Application: Kazanç Yönetimi Source: COBRA Kazanç Bildirim Yazışması (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG US BEN COBRA Benefit Notification Letter Imported from BI Publisher Description: COBRA Benefit Notification Letter Application: Advanced Benefits Source: COBRA Benefit Notification Letter (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG ZHS BEN COBRA 福利通知函 Imported from BI Publisher Description: COBRA 福利通知函 Application: 高级福利 Source: COBRA 福利通知函 (XML) Short Name: BENCOBN1_XML DB package: BEN_BEN_US_COBNL_XMLP_PKG BI Publisher :cp_effective_date :cp_ler_text :p_bnfts_grp_id :p_business_group_id :p_conc_request_id :p_date_mask :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 Organization ZHS 组织 组织 5 40 :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 Kazanç Grupları Kazanç Grupları US Benefit Groups Benefit Groups 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 Standorte Standorte E Ubicaciones Ubicaciones F Lieux Lieux I Ubicazioni Ubicazioni JA 事業所 事業所 KO 위치 위치 PTB Locais Locais RU Расположения Расположения S Placeringar Placeringar TR Yerleşimler Yerleşimler US Locations Locations 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 Programm E Programa Programa F Programme Programme I Programma Programma JA プログラム プログラム KO 프로그램 프로그램 PTB Programa Programa RU Программа Программа TR Program Programı US Program Program ZHS 方案 方案