USE [Bintang_Prod]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_Bintang_HR_PA_OffLet_PKWT_New]    Script Date: 10/18/2021 2:29:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Rpt_Bintang_HR_PA_OffLet_PKWT_New]
    (
  
      @emp_id VARCHAR(10) ,
      @trans_date VARCHAR(10) ,
      @no VARCHAR(250) ,
	  @landscape VARCHAR(100)
     
    )
AS 

--DECLARE @emp_id VARCHAR(10) = '90001763';
--DECLARE @trans_date VARCHAR(10) = '';
--DECLARE @no VARCHAR(10) = '';
--declare @landscape varchar(100)='100'

DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd');
--DECLARE @landscape VARCHAR(100);
--SET @landscape = '100';

DECLARE @hrgroup VARCHAR(250)
	  SELECT DISTINCT
        @hrgroup = val1
FROM    dbo.base_cust_parameter
WHERE   param = 'BINTANG_OFFLET_HRGROUP'

DECLARE @temp_off VARCHAR (100)
SELECT @temp_off = template_offering
FROM hr_md_app_md0028
WHERE emp_id = @emp_id

DECLARE @gender VARCHAR(100)
SELECT @gender = 
  CASE WHEN md1.gender ='01' THEN 'Bapak' ELSE 'Ibu' END  
  FROM hr_md_app_md0028 md28
  LEFT JOIN dbo.hr_md_emp_md0001 md1 ON md28.pic_orientasi = md1.emp_id

DECLARE @Tbl_Ds TABLE
(
    app_id VARCHAR(8),
    Nama_Ds VARCHAR(MAX),
    Posisi_Ds VARCHAR(MAX),
	Office VARCHAR(MAX)
);

INSERT INTO @Tbl_Ds
SELECT md2.emp_id,
       md1.full_name nama_ds,
       pos.description AS posisi_ds,
	   offe.emp_subarea_description
FROM dbo.hr_md_app_md0002 md2
    LEFT JOIN dbo.hr_md_emp_md0001 md1
        ON md2.direct_supervisor = md1.emp_id
    LEFT JOIN dbo.hr_md_emp_md0002 md2_em
        ON md1.emp_id = md2_em.emp_id
           AND md2_em.start_date <= @now
           AND md2_em.end_date >= @now
    LEFT JOIN dbo.hr_md_orm_object AS pos
        ON pos.landscape = md1.landscape
           AND pos.class = 'P'
           AND pos.object = md2_em.position
           AND pos.start_date <= @now
           AND pos.end_date >= @now
		   LEFT JOIN   base_cust_ref_emp_office offe on md2_em.employee_office = offe.emp_subarea
WHERE md2.emp_id = @emp_id
      AND md2.start_date <= @now
      AND md2.end_date >= @now;



SELECT DISTINCT
       @no AS no_surat,
       dbo.fn_formatdatetime_indonesia(@trans_date, 'dd mmmm yyyy') AS tanggal,
       md1.emp_id,
       md1.full_name AS nama,
       temp_of.template_offering_desc,
       md28.month,
       pos.description AS posisi_desc,
       cc.description AS cc_desc,
       app_subty.description AS app_subtype,
       --pgrade.description AS pay_grade_desc,
	    RIGHT(pgrade.description, len (pgrade.description)-5) AS pay_grade_desc,
       md28.homebase,
       td.Nama_Ds,
       td.Posisi_Ds,
	   @hrgroup AS HR_Group_Head,
	   md1_pic.full_name AS pic_name,
	   CASE WHEN md1_pic.gender ='01' THEN 'Bapak' ELSE 'Ibu' END AS gender,
	   CASE WHEN md28.template_offering = '001' THEN
       'Saudara/i kami tawarkan bekerja di PT. Asuransi Bintang Tbk. ,dengan status kepegawaian adalah ' +ISNULL([template_offering_desc],'')+ ' untuk periode ' +ISNULL([month],'') +' bulan dan Jabatan Saudara/i adalah '  + ISNULL(pos.description,'')+ ' untuk unit ' + ISNULL(cc.description,'') + ' pangkat ' + ISNULL(app_subty.description,'') +' grade ' + ISNULL (pgrade.pay_grade,'') + ' - ' + CASE WHEN pgrade.description <> '' THEN RIGHT(pgrade.description, len (pgrade.description)-5) ELSE '' END +' kota asal '+  ISNULL(md28.homebase,'') +'.'
	   ELSE
	   'Saudara/i kami tawarkan bekerja di PT. Asuransi Bintang Tbk. ,dengan status kepegawaian adalah ' +ISNULL([template_offering_desc],'')+ ' dan Jabatan Saudara/i adalah  '   + ISNULL(pos.description,'') + ' untuk unit ' + ISNULL(cc.description,'') + ' pangkat ' + ISNULL(app_subty.description,'') +' grade ' + ISNULL (pgrade.pay_grade,'') + ' - ' + CASE WHEN pgrade.description <> '' THEN RIGHT(pgrade.description, len (pgrade.description)-5) ELSE '' END +' kota asal '+  ISNULL(md28.homebase,'') +'.'
	END AS point_2,


	   CASE WHEN md28.template_offering = '001' THEN 
	   'Sesuai dengan ketentuan yang berlaku di perusahaan ketika Saudara dinyatakan sehat dari hasil Medical Check Up, maka Saudara akan ditetapkan sebagai Pegawai PT. Asuransi Bintang Tbk. dan selama periode diatas maka Saudara/i diwajibkan untuk melaksanakan tugas dan tanggung jawab (termasuk target-target pekerjaan, baik secara kuantitatif maupun kualitatif yang harus Saudara/i capai) yang akan disampaikan oleh '+ ISNULL(td.Nama_Ds,'') + ' selaku '+ ISNULL(td.Posisi_Ds,'') + ' PT. Asuransi Bintang, Tbk '+ ISNULL(td.Office,'')+' atau pejabat lain yang ditunjuk. Namun apabila kinerja Saudara dinilai tidak sesuai target yang ditetapkan dan disepakati ataupun hasil Medical Check Up dinyatakan tidak sehat maka hubungan kerja ini diakhiri.'
	   ELSE 'Sesuai dengan ketentuan yang berlaku di perusahaan, selama 3 (tiga) bulan terhitung mulai dari tanggal efektif Saudara/i bergabung, Saudara/i akan menjalani masa orientasi atau masa percobaan untuk melaksanakan tugas dan tanggung jawab, dengan status sebagai pegawai Masa Percobaan. Uraian tugas dan tanggung jawab akan disampaikan oleh '+ ISNULL(td.Nama_Ds,'') + ' selaku '+ ISNULL(td.Posisi_Ds,'') +' PT. Asuransi Bintang, Tbk '+ ISNULL(td.Office,'') +' .' END AS point_3,

	    CASE WHEN md28.template_offering = '001' THEN
		'Saudara/i akan bekerja selama 5 (lima) hari dalam 1 (satu) minggu, dari hari Senin sampai dengan Jumat. Jam kerja dimulai dari jam 08.00 sampai jam 17.00 dengan waktu istirahat jam 12.00 sampai 13.00. Di luar waktu tersebut apabila diperlukan perusahaan, Saudara/i wajib untuk hadir dan bekerja.'
		ELSE 'Pimpinan Saudara/i akan menetapkan target-target pekerjaan, baik secara kuantitatif maupun kualitatif yang harus Saudara/i capai. Apabila dalam masa orientasi/percobaaan, Saudara/i dapat mencapai target yang telah ditetapkan & disepakati dan kinerja Saudara/i dinilai baik serta Saudara/i dinyatakan sehat dari hasil Medical Check Up, maka Saudara/i akan ditetapkan sebagai Pegawai Tetap PT Asuransi Bintang Tbk. Namun apabila kinerja Saudara/i dinilai tidak sesuai target yang ditetapkan dan disepakati ataupun hasil Medical Check Up / PCR dinyatakan tidak sehat maka hubungan kerja ini diakhiri. ' END AS point_4,
		

		CASE WHEN md28.template_offering = '001' then
		'Saudara/i akan menerima pendapatan dengan perincian (lampiran 1) selain pendapatan yang disebutkan pada lampiran, Saudara/i akan menerima:'
		ELSE 'Saudara/i akan bekerja selama 5 (lima) hari dalam 1 (satu) minggu, dari hari Senin sampai dengan Jumat. Jam kerja dimulai dari jam 08.00 sampai jam 17.00 dengan waktu istirahat jam 12.00 sampai 13.00. Di luar waktu tersebut apabila diperlukan perusahaan, Saudara/i wajib untuk hadir dan bekerja.'

END AS point_5	,
CASE WHEN 	md28.template_offering = '002' THEN 'Saudara/i akan menerima pendapatan dengan perincian (lampiran 1) selain pendapatan yang disebutkan pada lampiran, Saudara/i akan menerima:'
ELSE 'Hak cuti Saudara/i adalah 12 (duabelas) hari kerja per tahun, sesuai ketentuan perusahaan.' END AS point_6,

CASE WHEN md28.template_offering = '002' THEN 'Hak cuti Saudara/i adalah 12 (duabelas) hari kerja per tahun, sesuai ketentuan perusahaan.'
ELSE 'Bila Saudara/i menyetujui penawaran bekerja ini maka Saudara/i dan PT Asuransi Bintang Tbk akan menandatangani Perjanjian Kerja Waktu Tertentu pada tanggal yang disepakati sebagai tanggal permulaan Saudara/i efektif bekerja.' 
END AS point_7,

CASE WHEN md28.template_offering = '002' THEN 'Bila Saudara/i menyetujui penawaran bekerja ini maka Saudara/i dan PT Asuransi Bintang Tbk akan menandatangani Perjanjian Kerja Waktu Tertentu pada tanggal yang disepakati sebagai tanggal permulaan Saudara/i efektif bekerja.'
ELSE 'Segala ketentuan dalam surat penawaran bekerja ini dan hal lain yang belum diatur akan dituangkan dalam Perjanjian Kerja Waktu Tertentu (tersebut pada point 7), yang akan ditandatangani bersama.'
END AS point_8,

CASE WHEN md28.template_offering = '002' THEN 'Segala ketentuan dalam surat penawaran bekerja ini dan hal lain yang belum diatur akan dituangkan dalam Perjanjian Kerja Waktu Tertentu (tersebut pada point 7), yang akan ditandatangani bersama'
else 'Pada hari pertama Saudara/i efektif bekerja, Saudara/i diminta menemui ' + @gender +' '+md1_pic.full_name+' by zoom untuk mengikuti orientasi Pegawai baru.'
END AS point_9,

CASE WHEN md28.template_offering = '002' THEN 'Pada hari pertama Saudara/i efektif bekerja, Saudara/i diminta menemui ' + @gender +' '+  md1_pic.full_name +' (by zoom) untuk mengikuti orientasi Pegawai baru.'
ELSE '1' END point_10,

CASE WHEN md28.template_offering = '002' THEN '10.'
ELSE '' END poooooinnnn,
CASE WHEN md28.template_offering = '002' THEN 'Penggatian Pengobatan Sesuai Ketentuan Perusahaan'
ELSE '1' END Crasbi1,
mdap6.street




FROM dbo.hr_md_app_md0001 md1
    LEFT JOIN dbo.hr_md_app_md0028 md28
        ON md1.emp_id = md28.emp_id
		
LEFT JOIN dbo.hr_md_emp_md0001 md1_pic ON md28.pic_orientasi = md1_pic.emp_id
    LEFT JOIN base_cust_ref_template_offering temp_of
        ON md28.template_offering = temp_of.template_offering_code
		 AND md28.template_offering = @temp_off
    LEFT JOIN dbo.hr_md_app_md0002 md2
        ON md1.emp_id = md2.emp_id
           AND md2.start_date <= @now
           AND md2.end_date >= @now
    LEFT JOIN dbo.hr_md_orm_object AS pos
        ON pos.landscape = md1.landscape
           AND pos.class = 'P'
           AND pos.object = md2.position
           AND pos.start_date <= @now
           AND pos.end_date >= @now
    LEFT JOIN dbo.hr_md_orm_object AS cc
        ON cc.landscape = md1.landscape
           AND cc.class = 'CC'
           AND cc.object = md2.costcenter
           AND cc.start_date <= @now
           AND cc.end_date >= @now
    LEFT JOIN hr_rec_app_subtype AS app_subty
        ON md2.applicant_subtype = app_subty.app_subtype
    LEFT JOIN dbo.hr_md_app_md0003 md3
        ON md1.emp_id = md3.emp_id
           AND md3.start_date <= @now
           AND md3.end_date >= @now
    LEFT JOIN base_cust_ref_pay_grade pgrade
        ON md3.pay_grade = pgrade.pay_grade
    LEFT JOIN @Tbl_Ds td
        ON td.app_id = md1.emp_id
		LEFT JOIN  hr_md_app_md0006 mdap6 on mdap6.emp_id = md1.emp_id
WHERE md1.emp_id = @emp_id;