PRPTPAALLEMPLOYEE_202506301426.sql - PT BIA - Redmine

Bug #3691 » PRPTPAALLEMPLOYEE_202506301426.sql

Tri Rizqiaty, 06/30/2025 02:33 PM

    
      ??ALTER PROCEDURE [dbo].[PRPTPAALLEMPLOYEE]

          (

            @CompanyID VARCHAR(5) ,

            @EmployeeStatus VARCHAR(5) ,

            @EmployeeType VARCHAR(5) ,

            @EmployeeArea VARCHAR(5) ,

            @EmployeeOffice VARCHAR(5) ,

            @PayrollGroup VARCHAR(5) ,

            @Date VARCHAR(12) ,

            @UserID VARCHAR(12) ,

            @MenuID VARCHAR(10)

          )

      AS 

      --DECLARE @CompanyID VARCHAR(20) = '1000'

      --DECLARE @EmployeeStatus VARCHAR(20) = '01'

      --DECLARE @EmployeeType VARCHAR(20)= ''

      --DECLARE @EmployeeArea VARCHAR(20)= ''

      --DECLARE @EmployeeOffice VARCHAR(20) = ''

      --DECLARE @PayrollGroup VARCHAR(20) = '2000'

      --DECLARE @Date VARCHAR(12) = '20250630'

      --DECLARE @UserID VARCHAR(10) = 'shofwan'

      --DECLARE @MenuID VARCHAR(10) = 'PAR19'

      ------------------ // Get Otorisasi //---------------

      DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )

      INSERT  INTO @TableResult

              EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)

                  @UserID = @UserID

       -- varchar(max)

      ------------------ // End Get Otorisasi //---------------

      DECLARE @now VARCHAR(12) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

      DECLARE @PermanentDate VARCHAR(5)

      SELECT  @PermanentDate = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_PRMNTDATE'

      DECLARE @PermanentDate2 VARCHAR(5)

      SELECT  @PermanentDate2 = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATETYP'

      DECLARE @HiringDate VARCHAR(5)

      SELECT  @HiringDate = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_HIRINGDATE'

      DECLARE @LastAssign VARCHAR(5)

      SELECT  @LastAssign = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_LASTASSGNMNT'

      DECLARE @Resign VARCHAR(5)

      SELECT  @Resign = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIGN'

      DECLARE @Expired VARCHAR(5)

      SELECT  @Expired = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATESPEC'

      DECLARE @joingroup VARCHAR(5)

      SELECT  @joingroup = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATETYP_JOINGROUP'

      DECLARE @Resident VARCHAR(5)

      SELECT  @Resident = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENT'

      DECLARE @HP VARCHAR(5)

      SELECT  @HP = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_COMMTYPHP'

      DECLARE @Email VARCHAR(5)

      SELECT  @Email = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_COMMTYPEMAIL'

      DECLARE @IDKTP VARCHAR(5)

      SELECT  @IDKTP = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDKTP'

      DECLARE @IDPassport VARCHAR(5)

      SELECT  @IDPassport = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDPSSPRT'

      DECLARE @IDSIMA VARCHAR(5)

      SELECT  @IDSIMA = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMA'

      DECLARE @IDSIMB VARCHAR(5)

      SELECT  @IDSIMB = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMB'

      DECLARE @IDSIMC VARCHAR(5)

      SELECT  @IDSIMC = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMC'

      DECLARE @ResidentKTP VARCHAR(5)

      SELECT  @ResidentKTP = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENTKTP'

      DECLARE @ResidentKost VARCHAR(5)

      SELECT  @ResidentKost = Value1

      FROM    PCMEPGENPARAM

      WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENTKOST'

      DECLARE @par_pension VARCHAR(10)

      SELECT  @par_pension = Value1

      FROM    dbo.PCMEPGENPARAM

      WHERE   Parameter = 'MDREMINDERMAXAGEPENSION'

      DECLARE @tbl_3 TABLE

          (

            emp_id VARCHAR(18) ,

            edu_level VARCHAR(180) ,

            major VARCHAR(600) ,

            gpa VARCHAR(180) ,

            edu_level_desc VARCHAR(200) ,

            edu_start VARCHAR(80) ,

            institution VARCHAR(200)

          )

          --INSERT  INTO @tbl_3

          --        SELECT  a.* ,

          --                edu_level.description

          --        FROM    ( SELECT    emp_id ,

          --                            MAX(edu_level) AS edu_level ,

          --                            major ,

          --                            institution ,

          --                            CONVERT(DECIMAL(18, 2), gpa) AS gpa,

          --                            MAX (start_date) AS edu_start

          --                  FROM      dbo.hr_md_emp_md0008 AS md08

          --                  GROUP BY  emp_id ,

          --                            major ,

          --                            institution ,

          --                            gpa

          --                ) AS a

      --    --                LEFT JOIN base_cust_ref_edu_level AS edu_level ON a.edu_level = edu_level.code

      --INSERT  INTO @tbl_3

      --       -- SELECT  TOP(1)

      --        SELECT  EmployeeID ,

      --      -- start_date, 

      --       --edu_type,

      --                edu.EducationLevel ,

      --                Major ,

      --                --Institution ,

      --                CASE WHEN inst.InstitutionDescription = 'OTHERS'

      --                     THEN edu.OtherInstitution

      --                     ELSE inst.InstitutionDescription

      --                END AS Institution ,

      --                GPA ,

      --                edu_level.EducationLevelDescription ,

      --                edu.StartDate

      --        FROM    dbo.PHRPA0008 AS edu

      --                LEFT JOIN PHRPAEDULVL AS edu_level ON edu.EducationLevel = edu_level.EducationLevel

      --                LEFT JOIN PHRPAINST inst ON edu.Institution = inst.InstitutionType

      --        WHERE   edu.EndDate = ( SELECT  MAX(EndDate)

      --                                FROM    dbo.PHRPA0008 AS x

      --                                WHERE   x.EmployeeID = edu.EmployeeID

      --                                        AND x.EducationType = 'F'

      --                              )

      --                AND edu.StartDate <= @Date

      --                AND edu.EndDate >= @Date

      --                AND edu.LastEducation = '1'

      --        ORDER BY edu.Sequence DESC

      INSERT  INTO @tbl_3

              SELECT  EmployeeID ,

                      edu.EducationLevel ,

                      Major ,

                      GPA ,

                      edu_level.EducationLevelDescription ,

                      edu.StartDate ,

                      CASE WHEN inst.InstitutionDescription = 'OTHERS'

                           THEN edu.OtherInstitution

                           ELSE inst.InstitutionDescription

                      END AS Institution

              FROM    dbo.PHRPA0008 AS edu

                      LEFT JOIN PHRPAEDULVL AS edu_level ON edu.EducationLevel = edu_level.EducationLevel

                      LEFT JOIN PHRPAINST inst ON edu.Institution = inst.InstitutionType

              WHERE   edu.LastEducation = '1'

              ORDER BY edu.Sequence DESC

      DECLARE @tbl_1 TABLE

          (

            level1 VARCHAR(50) ,

            level1_desc VARCHAR(500) ,

            level2 VARCHAR(50) ,

            level2_desc VARCHAR(500) ,

            level3 VARCHAR(50) ,

            level3_desc VARCHAR(500) ,

            level4 VARCHAR(50) ,

            level4_desc VARCHAR(500) ,

            level5 VARCHAR(50) ,

            level5_desc VARCHAR(500) ,

            level6 VARCHAR(50) ,

            level6_desc VARCHAR(500) ,

            level7 VARCHAR(50) ,

            level7_desc VARCHAR(500) ,

            level8 VARCHAR(50) ,

            level8_desc VARCHAR(500) ,

            emp_id VARCHAR(50)

          )

          --INSERT  INTO @tbl_1

                  --SELECT DISTINCT

                  --        a.org_id_01 ,

                  --        a.org_name_01 ,

                  --        a.org_id_02 ,

                  --        a.org_name_02 ,

                  --        a.org_id_03 ,

                  --        a.org_name_03 ,

                  --        a.org_id_04 ,

                  --        a.org_name_04 ,

                  --        a.org_id_05 ,

                  --        a.org_name_05 ,

                  --        a.org_id_06 ,

                  --        a.org_name_06 ,

                  --        a.org_id_07 ,

                  --        a.org_name_07 ,

                  --        a.org_id_08 ,

                  --        a.org_name_08 ,

                  --        md2.EmployeeID

                  --FROM    dbo.GetOrgInOrgWide('00000001', @Date) AS a

                  --        INNER JOIN dbo.PHRPA0002 AS md2 ON a.org_id = md2.Organization

                  --                                           AND md2.StartDate <= @Date

                  --                                           AND md2.EndDate >= @Date                                                          	 

      DECLARE @tbl_2 TABLE

          (

            emp_id VARCHAR(18) ,

            movement_type VARCHAR(12) ,

            jumlah DECIMAL(18, 0)

          )	

      INSERT  INTO @tbl_2

              SELECT DISTINCT

                      EmployeeID ,

                      MovementType ,

                      COUNT(StartDate)

              FROM    dbo.PHRPA0002 WITH ( NOLOCK )

              --WHERE   MovementType = '60'

              GROUP BY EmployeeID ,

                      MovementType

      DECLARE @tbl_md2_last TABLE

          (

            --landscape VARCHAR(3) ,

            emp_id VARCHAR(18) ,

            start_date VARCHAR(8) ,

            end_date VARCHAR(8) ,

            movement_type VARCHAR(10) ,

            reason VARCHAR(20) ,

            company_id VARCHAR(4) ,

            costcenter VARCHAR(18) ,

            employee_area VARCHAR(50) ,

            employee_office VARCHAR(50) ,

            employee_status VARCHAR(50) ,

            employee_type VARCHAR(50) ,

            employee_subtype VARCHAR(50) ,

            tax_office VARCHAR(50) ,

            payroll_group VARCHAR(20) ,

            organization VARCHAR(50) ,

            position VARCHAR(50) ,

            job VARCHAR(50) ,

            remark VARCHAR(MAX),

      	  divisioncode VARCHAR(10)

                --additional_1 VARCHAR(8) ,

                --datedifff VARCHAR(MAX)

          )

      INSERT  INTO @tbl_md2_last

              SELECT  tbl2.* --,

                          --DATEDIFF(DAY, tbl2.realDate, @Date) datedifff

              FROM    ( SELECT    EmployeeID ,

                                  StartDate ,

                                  EndDate ,

                                  MovementType ,

                                  MovementReason ,

                                  CompanyID ,

                                  CostCenter ,

                                  EmployeeArea ,

                                  EmployeeOffice ,

                                  EmployeeStatus ,

                                  EmployeeType ,

                                  EmployeeSubType ,

                                  TaxOffice ,

                                  PayrollGroup ,

                                  Organization ,

                                  Position ,

                                  Job ,

                                  Notes ,

      							DivisionCode

                                      --CASE WHEN ISNULL(additional_1, '') = ''

                                      --     THEN StartDate

                                      --     ELSE additional_1

                                      --END AS realDate

                        FROM      dbo.PHRPA0002 WITH ( NOLOCK )

                      ) AS tbl2

      SELECT DISTINCT 

              md01.EmployeeID ,

              md01.FullName ,

              md01.ExternalID ,

      		md01.NumberOfChildren,

              md02_last.company_id ,

              CONVERT(DATE, md02_hiring.StartDate) AS join_date ,

              CONVERT(DATE, md39_permanent.Date) AS permanent_date ,

              CONVERT(DATE, md39_joingroup.Date) AS join_group ,

              mov_type_last.MovementTypeDescription AS effective_type_status ,

              CONVERT(DATE, md02_last.start_date) AS effective_date ,

                  --CONVERT(DATE, md02_last.additional_1) AS real_date ,

              md02_last.employee_status ,

              emp_status_last.EmployeeStatusDescription AS emp_status ,

              --CONVERT(DATE, md02_resign.StartDate) AS resign_date ,

              CONVERT(DATE,md02_resign.StartDate) AS resign_date ,

      		CONVERT(DATE, md02_enddate.EndDate) AS end_date,

              emp_type_last.EmployeeTypeDescription AS employee_type ,

              CASE WHEN ( md39_contract.Date >= @Date )

                   THEN CONVERT(DATE, md39_contract.Date)

                   ELSE NULL

              END expired_status ,

              --tbl_2.jumlah AS contract_extent ,

              ws_type.WorkScheduleTypeDesc AS workschedule_type ,

              md03.TaxStatus ,

              md02_last.employee_area AS area_code ,

              emp_area_last.EmployeeAreaDescription AS area_desc ,

              md02_last.employee_office AS office_code ,

              emp_office_last.EmployeeOfficeDesc AS office_desc ,

              md02_last.payroll_group AS payroll_group_id ,

              payroll_group_last.PayrollGroupDescription AS payroll_group ,

              emp_sub_type_desc.EmployeeSubTypeDesc AS employee_subtype ,

              tbl_1.level1 ,

              tbl_1.level1_desc ,

              tbl_1.level2 ,

              tbl_1.level2_desc ,

              tbl_1.level3 ,

              tbl_1.level3_desc ,

              tbl_1.level4 ,

              tbl_1.level4_desc ,

              tbl_1.level5 ,

              tbl_1.level5_desc ,

              tbl_1.level6 ,

              tbl_1.level6_desc ,

              tbl_1.level7 ,

              tbl_1.level7_desc ,

              tbl_1.level8 ,

              tbl_1.level8_desc ,

                  --md02_last.costcenter AS cc_code ,

                  --cc.description AS cc_desc ,

                  md02_last.position AS pos_code ,

      			pos.ObjectDescription AS pos_desc,

                  --p.description AS pos_desc ,

                  --md02_last.job AS job_code ,

                  --j.description AS job_desc ,

              pay_grade.PayrollGradeDescription AS pay_grade ,

              md01.NickName ,

              gender.GenderDescription AS gender ,

              md01.BirthPlace ,

              CONVERT (DATE, md01.BirthDate) AS birth_date ,

              FLOOR(DATEDIFF(DAY, md01.BirthDate, @Date) / 365.25) AS age ,

              dbo.GetDateDiffTahunBulanHari(md02_hiring.StartDate, @now) AS MasaKerja ,

              CASE WHEN ( FLOOR(DATEDIFF(DAY, md01.BirthDate, @Date) / 365.25) ) > @par_pension

                   THEN 'YES'

                   ELSE ''

              END AS Flag_Pension ,

              md06_resident.Address ,

              ( SELECT    ProvinceDescription

                FROM      PHRPAPROV

                WHERE     Province = md06_resident.Province

              ) AS province ,

                  --md06_resident.location ,

              md06_resident.PostalCode ,

              md06_resident.TelephoneNumber ,

                  --md12_hp.CommunicationDescription AS cellphone ,

              md12_email.CommunicationDescription AS email ,

              religion.ReligionDescription AS religion ,

              --marital_status.MaritalStatusDescription AS marital_status ,

      		--tax_status.TaxStatusDescription AS marital_status,

      		md03.TaxStatus AS marital_status,

              --md11.BloodType ,

              bld.BloodTypeDescription AS BloodType ,

              --md13_ktp.IDDescription AS id ,

      		md01.IDCard AS id,

              CONVERT(DATE, md13_ktp.EndDate) AS ktp_expired ,

              md06_ktp.Address AS ktp_address ,

              ( SELECT    ProvinceDescription

                FROM      PHRPAPROV

                WHERE     Province = md06_ktp.Province

              ) AS ktp_province ,

              md06_ktp.PostalCode AS ktp_postalcode ,

                  --( SELECT    description

                  --  FROM      base_cust_ref_id_type

                  --  WHERE     code = md13_a.id_type

                  --) AS id_a ,

                  --md13_a.id_description AS id_a_no ,

                  --CONVERT(DATE, md13_a.end_date) AS id_a_expired ,

                  --( SELECT    description

                  --  FROM      base_cust_ref_id_type

                  --  WHERE     code = md13_b.id_type

                  --) AS id_b ,

                  --md13_b.id_description AS id_b_no ,

                  --CONVERT(DATE, md13_b.end_date) AS id_b_expired ,

                  --( SELECT    description

                  --  FROM      base_cust_ref_id_type

                  --  WHERE     code = md13_c.id_type

                  --) AS id_c ,

              md13_c.IDDescription AS id_c_no ,

              CONVERT(DATE, md13_c.EndDate) AS id_c_expired ,

              md13_passport.IDDescription AS id_password_no ,

              CONVERT(DATE, md13_passport.EndDate) AS id_password_expired ,

                  --md06_cp.contact_person AS cp_name ,

              md06_cp.Address AS cp_address ,

              md12_hp.CommunicationDescription AS cp_tlp, --md06_cp.TelephoneNumber AS cp_tlp , -----// edit tri 20250610 redmine 3659

              md06_cp.CellphoneNumber AS cp_cell ,

              md03.NPWP ,

                  --CASE WHEN md03.npwp_date = '' THEN ''

                  --     ELSE dbo.fn_formatdatetime(md03.npwp_date, 'dd mmm yyyy')

                  --END AS npwp_date ,

                  --md03.BPJSTKType ,

                  --jams_type.description AS jams_type ,

                 -- md03.BPJSTKNumber ,

                  --CASE WHEN md03.additional_1 = '' THEN ''

                  --     ELSE dbo.fn_formatdatetime(md03.additional_1, 'dd mmm yyyy')

                  --END AS additional_1 ,

                  --pension_type.PensionTypeDescription AS pension_type ,

                  --md03.PensionType ,

                  --CASE WHEN md03.additional_2 = '' THEN ''

                  --     ELSE dbo.fn_formatdatetime(md03.additional_2, 'dd mmm yyyy')

                  --END AS additional_2 ,

              cust_bank_id.BankDescription AS bank_id ,

              md03.BankAccount ,

              md03.BankAccountName ,

              tbl_3.edu_level_desc ,

              tbl_3.major ,

              tbl_3.institution ,

              tbl_3.gpa ,

              md02_last.remark ,

      		md02_last.reason,

      		emp_mov_reason.MovementReasonDesc,

      		emp_division.DivisionCodeDescription,

      		emp_division.EstateCode,

      		emp_class.PayrollGrade,

      		emp_class.PayrollClass,

      		pay_class.PayrollClassDescription,

      		emp_office_last.CodeOffice,

      		'00'+emp_office_last.CodeOffice AS Plant,

      		emp_ethnic.EthnicDescription,

      		md13a.BenefitNo AS BPJSKESEHATAN,

      		md13.BenefitNo AS BPJSKETENAGAKERJAAN

      		    --CASE WHEN ISNULL(gapok.amount, '') = '' THEN 0

                  --     ELSE CONVERT(DECIMAL, dbo.GetPEN(@landscape, gapok.amount))

                  --END amount_gapok ,

                  --CASE WHEN ( SELECT  valuation

                  --            FROM    hr_cu0300

                  --            WHERE   code = makan.wage_type

                  --          ) = 'I2'

                  --     THEN ( SELECT  amount

                  --            FROM    hr_cu0304

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = makan.wage_type

                  --                    AND ( pay_grade = md03.pay_grade

                  --                          OR pay_grade = '*'

                  --                        )

                  --                    AND ( pay_class = md03.pay_class

                  --                          OR pay_class = '*'

                  --                        )

                  --                    AND ( pay_area = md03.pay_area

                  --                          OR pay_area = '*'

                  --                        )

                  --          )

                  --     ELSE ( CASE WHEN ISNULL(makan.amount, '') = '' THEN 0

                  --                 ELSE CONVERT(DECIMAL, dbo.GetPEN(makan.amount))

                  --            END )

                  --END amount_makan ,

                  --CASE WHEN ( SELECT  valuation

                  --            FROM    hr_cu0300

                  --            WHERE   code = ttp.wage_type

                  --          ) = 'I2'

                  --     THEN ( SELECT  amount

                  --            FROM    hr_cu0304

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = ttp.wage_type

                  --                    AND ( pay_grade = md03.pay_grade

                  --                          OR pay_grade = '*'

                  --                        )

                  --                    AND ( pay_class = md03.pay_class

                  --                          OR pay_class = '*'

                  --                        )

                  --                    AND ( pay_area = md03.pay_area

                  --                          OR pay_area = '*'

                  --                        )

                  --          )

                  --     ELSE ( CASE WHEN ISNULL(ttp.amount, '') = '' THEN 0

                  --                 ELSE CONVERT(DECIMAL, dbo.GetPEN(@landscape,

                  --                                                  ttp.amount))

                  --            END )

                  --END amount_transport ,

                  --CASE WHEN ( SELECT  valuation

                  --            FROM    hr_cu0300

                  --            WHERE   code = hp.wage_type

                  --          ) = 'I2'

                  --     THEN ( SELECT  amount

                  --            FROM    hr_cu0304

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = hp.wage_type

                  --                    AND ( pay_grade = md03.pay_grade

                  --                          OR pay_grade = '*'

                  --                        )

                  --                    AND ( pay_class = md03.pay_class

                  --                          OR pay_class = '*'

                  --                        )

                  --                    AND ( pay_area = md03.pay_area

                  --                          OR pay_area = '*'

                  --                        )

                  --          )

                  --     ELSE ( SELECT  amount

                  --            FROM    hr_cu0303

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = hp.wage_type

                  --          )

                  --END amount_hp ,

                  --CASE WHEN ( SELECT  valuation

                  --            FROM    hr_cu0300

                  --            WHERE   code = tj.wage_type

                  --          ) = 'I2'

                  --     THEN ( SELECT  amount

                  --            FROM    hr_cu0304

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = tj.wage_type

                  --                    AND ( pay_grade = md03.pay_grade

                  --                          OR pay_grade = '*'

                  --                        )

                  --                    AND ( pay_class = md03.pay_class

                  --                          OR pay_class = '*'

                  --                        )

                  --                    AND ( pay_area = md03.pay_area

                  --                          OR pay_area = '*'

                  --                        )

                  --          )

                  --     ELSE ( SELECT  amount

                  --            FROM    hr_cu0303

                  --            WHERE   start_date <= @Date

                  --                    AND end_date >= @Date

                  --                    AND wage_type = tj.wage_type	

                  --          )

                  --END amount_jabatan

      FROM    dbo.PHRPA0001 md01 --@tbl_personal AS md01

              LEFT JOIN PHRPAGENDER AS gender ON md01.Gender = gender.Gender

              LEFT JOIN dbo.PHRPA0039 pa39 ON md01.EmployeeID = pa39.EmployeeID

                                              AND pa39.DateType = '0001'

              LEFT JOIN PHRPARELIGI AS religion ON md01.Religion = religion.Religion

      		LEFT JOIN PHRPA0003 AS taxmd on  md01.EmployeeID=taxmd.EmployeeID and taxmd.EndDate='99991231'

      		LEFT JOIN PHRPYTAXSTAT AS tax_status on taxmd.TaxStatus=tax_status.TaxStatus

              LEFT JOIN PHRPAMSTAT AS marital_status ON md01.MaritalStatus = marital_status.MaritalStatus

      		and md01.EndDate='99991231'

              LEFT JOIN PHRPA0002 AS md02_hiring ON md01.EmployeeID = md02_hiring.EmployeeID -- Hiring Date

                                                    AND md02_hiring.MovementType = @HiringDate

              --LEFT JOIN PHRPA0002 AS md02_permanent ON md01.EmployeeID = md02_permanent.EmployeeID -- Hiring Permanent

              --                                         --AND md02_permanent.MovementType = @PermanentDate

              --                                         --AND md02_permanent.employeetype = '01'

      								--				   AND md02_permanent.StartDate <= @Date

                                                   --AND md02_permanent.EndDate >= @Date

              LEFT JOIN @tbl_md2_last AS md02_last ON md01.EmployeeID = md02_last.emp_id

                                                      AND md02_last.start_date <= @Date		-- Last Assignment dirubah

                                                      AND md02_last.end_date >= @Date

                                                      AND md02_last.payroll_group <> @LastAssign

      		LEFT JOIN PHROM0001 AS pos ON  md02_last.position=pos.ObjectID

      												AND pos.ObjectClass='P'

                                                          --AND md02_last.datedifff = ( SELECT

                                                                    --MIN(tbl_last.datedifff)

                                                                    --FROM

                                                                    --@tbl_md2_last tbl_last

                                                                    --WHERE

                                                                    --tbl_last.start_date <= @Date  --dirubah

                                                                    --AND tbl_last.end_date >= @Date

                                                                    --AND tbl_last.payroll_group <> '99'

                                                                    --AND md01.emp_id = tbl_last.emp_id

                                                                    --)

              LEFT JOIN PCMEPMOVTYP AS mov_type_last ON md02_last.movement_type = mov_type_last.MovementType

              LEFT JOIN PCMEPEMPTYP AS emp_type_last ON md02_last.employee_type = emp_type_last.EmployeeType

              LEFT JOIN PCMEPEMPAREA AS emp_area_last ON md02_last.employee_area = emp_area_last.EmployeeArea

              LEFT JOIN PCMEPEMPOFF AS emp_office_last ON md02_last.employee_office = emp_office_last.EmployeeOffice

              LEFT JOIN PHRPYPGRUP AS payroll_group_last ON md02_last.payroll_group = payroll_group_last.PayrollGroup

              LEFT JOIN PCMEPEMPSTAT AS emp_status_last ON md02_last.employee_status = emp_status_last.EmployeeStatus

              LEFT JOIN PCMEPEMPSTYP AS emp_sub_type_desc ON md02_last.employee_subtype = emp_sub_type_desc.EmployeeSubType

      		LEFT JOIN PCMEPMOVR AS emp_mov_reason ON md02_last.reason = emp_mov_reason.MovementReason AND emp_mov_reason.MovementType='17'

      		LEFT JOIN PCMEMPDVSCODE AS  emp_division ON md02_last.divisioncode = emp_division.DivisionCode

      		LEFT JOIN PHRPA0003 AS  emp_class ON md02_last.emp_id = emp_class.EmployeeID and emp_class.EndDate='99991231'

      		left join PHRPYPCLASS AS pay_class on emp_class.PayrollClass=pay_class.PayrollClass and pay_class.EndDate='99991231' and pay_class.PayrollArea=taxmd.PayrollArea

      		--Update 23/11/2022 by Shofwan

      		LEFT JOIN PHRPAETHNIC AS  emp_ethnic ON md01.Ethnic = emp_ethnic.Ethnic

      		LEFT JOIN PHRPA0022 AS md13 ON md13.EmployeeID= md01.EmployeeID AND md13.BenefitType='B001' and md13.EndDate='99991231'

      		LEFT JOIN PHRPA0022 AS md13a ON md13a.EmployeeID= md01.EmployeeID AND md13a.BenefitType='B002' and md13a.EndDate='99991231'

                  --LEFT OUTER JOIN hr_md_orm_object cc ON cc.landscape = @landscape

                  --                                       AND cc.start_date <= @Date

                  --                                       AND cc.end_date >= @Date

                  --                                       AND md02_last.costcenter = cc.object

                  --                                       AND cc.class = 'CC'

                  --LEFT OUTER JOIN hr_md_orm_object o ON o.landscape = @landscape

                  --                                      AND o.start_date <= @Date

                  --                                      AND o.end_date >= @Date

                  --                                      AND md02_last.organization = o.object

                  --                                      AND o.class = 'O'

                  --LEFT OUTER JOIN hr_md_orm_object p ON p.landscape = @landscape

                  --                                      AND p.start_date <= @Date

                  --                                      AND p.end_date >= @Date

                  --                                      AND md02_last.position = p.object

                  --                         --AND md02_last.payroll_group <> '99'

                  --                                      AND p.class = 'P'

                  --LEFT OUTER JOIN hr_md_orm_object j ON j.landscape = @landscape

                  --                                      AND j.start_date <= @Date

                  --                                      AND j.end_date >= @Date

                  --                                      AND md02_last.job = j.object

                  --                                      AND j.class = 'J'

              --LEFT JOIN phrpa0039 AS md39_group ON md01.EmployeeID = md39_group.EmployeeID				-- Date Spesification, Join Date Group

              --                                     AND md39_group.DateType = '10'

              --                                     AND md39_group.StartDate <= @Date

              --                                     AND md39_group.EndDate >= @Date

              LEFT JOIN PHRPA0002 AS md02_resign ON md01.EmployeeID = md02_resign.EmployeeID			-- Resign

                                                    AND md02_resign.MovementType = @Resign

      		LEFT JOIN PHRPA0002 AS md02_enddate ON md01.EmployeeID=md02_enddate.EmployeeID

      												AND md02_enddate.EndDate='99991231'

                                                          --AND md02_resign.end_date = '99991231'

              --LEFT JOIN phrpa0002 AS md02_contract ON md01.EmployeeID = md02_contract.EmployeeID

              --                                        AND md02_contract.MovementType = '19'

      		LEFT JOIN PHRPA0039 AS md39_permanent ON md01.EmployeeID = md39_permanent.EmployeeID

      			and md39_permanent.DateType='0004'

              LEFT JOIN PHRPA0039 AS md39_contract ON md01.EmployeeID = md39_contract.EmployeeID		-- Date Spesification, expired contract dengan code 02

                                                      AND md39_contract.DateType = @Expired

                                                      AND md39_contract.Sequence = ( SELECT

                                                                    MAX(md39_contracte.Sequence)

                                                                    FROM

                                                                    PHRPA0039 md39_contracte

                                                                    WHERE

                                                                    md39_contracte.EmployeeID = md39_contract.EmployeeID

                                                                    AND md39_contracte.DateType = @Expired

                                                                    )

              LEFT JOIN PHRPA0039 AS md39_joingroup ON md01.EmployeeID = md39_joingroup.EmployeeID		-- Date Spesification, expired contract dengan code 02

                                                       AND md39_joingroup.DateType = @joingroup

                                                       AND md39_joingroup.Sequence = ( SELECT

                                                                    MAX(md39_joingroupe.Sequence)

                                                                    FROM

                                                                    PHRPA0039 md39_joingroupe

                                                                    WHERE

                                                                    md39_joingroupe.EmployeeID = md39_joingroup.EmployeeID

                                                                    AND md39_joingroupe.DateType = @joingroup

                                                                    )                                                     

              --LEFT JOIN hr_md_emp_md0002 AS md02_appointment ON md01.emp_id = md02_appointment.emp_id		-- Appointment

              --                                                  AND md02_appointment.movement_type = '20'

              LEFT JOIN PHRPA0025 AS md25 ON md01.EmployeeID = md25.EmployeeID							-- Working Schedule

                                             AND md25.StartDate <= @Date

                                             AND md25.EndDate >= @Date

              LEFT JOIN PHRTMWSTYP AS ws_type ON md25.WorkScheduleType = ws_type.WorkScheduleType

              LEFT JOIN PHRPA0003 AS md03 ON md01.EmployeeID = md03.EmployeeID						--Payroll Basic

                                             AND md03.StartDate <= @Date

                                             AND md03.EndDate >= @Date

                  --LEFT JOIN PHRPYPENSTYP AS pension_type ON md03.PensionType = pension_type.PensionType

              LEFT JOIN PHRPYBANKTYP AS cust_bank_id ON md03.BankType = cust_bank_id.BankType

              LEFT JOIN PHRPYPGRAD AS pay_grade ON md03.PayrollGrade = pay_grade.PayrollGrade and md03.PayrollClass=pay_grade.PayrollClass

              LEFT JOIN PHRPA0006 AS md06_resident ON md01.EmployeeID = md06_resident.EmployeeID

                                                      AND md06_resident.AddressType = @Resident

                                                             --AND md06_resident.end_date = '99991231'

                                                      AND md06_resident.StartDate <= @Date

                                                      AND md06_resident.EndDate >= @Date

                                                      AND md06_resident.Sequence = ( SELECT

                                                                    MAX(md06_st.Sequence)

                                                                    FROM

                                                                    PHRPA0006 md06_st

                                                                    WHERE

                                                                    md06_st.AddressType = @Resident

                                                                    AND md06_st.EmployeeID = md06_resident.EmployeeID

                                                                    )

              LEFT JOIN PHRPA0012 AS md12_hp ON md01.EmployeeID = md12_hp.EmployeeID

                                                AND md12_hp.CommunicationType = @HP

                                                AND md12_hp.Sequence = ( SELECT

                                                                    MAX(md12_st.Sequence)

                                                                    FROM

                                                                    PHRPA0012 md12_st

                                                                    WHERE

                                                                    md12_st.EmployeeID = md12_hp.EmployeeID

                                                                    AND md12_st.CommunicationType = @HP

                                                                    )

              LEFT JOIN PHRPA0012 AS md12_email ON md01.EmployeeID = md12_email.EmployeeID

                                                   AND md12_email.CommunicationType = @Email

                                                   AND md12_email.Sequence = ( SELECT

                                                                    MAX(md12e_st.Sequence)

                                                                    FROM

                                                                    PHRPA0012 md12e_st

                                                                    WHERE

                                                                    md12e_st.EmployeeID = md12_email.EmployeeID

                                                                    AND md12e_st.CommunicationType = @Email

                                                                    )

              LEFT JOIN PHRPA0011 AS md11 ON md01.EmployeeID = md11.EmployeeID

              LEFT JOIN PHRPABLODTYP AS bld ON md11.BloodType = bld.BloodType

              LEFT JOIN PHRPA0013 AS md13_ktp ON md01.EmployeeID = md13_ktp.EmployeeID

                                                 AND md13_ktp.IDType = @IDKTP

                                                 AND md13_ktp.Sequence = ( SELECT

                                                                    MAX(md13_ktpe.Sequence)

                                                                    FROM

                                                                    PHRPA0013 md13_ktpe

                                                                    WHERE

                                                                    md13_ktpe.EmployeeID = md13_ktp.EmployeeID

                                                                    AND md13_ktpe.IDType = @IDKTP

                                                                    )

              LEFT JOIN PHRPA0013 AS md13_a ON md01.EmployeeID = md13_a.EmployeeID

                                               AND md13_a.IDType = @IDSIMA

              LEFT JOIN PHRPA0013 AS md13_b ON md01.EmployeeID = md13_b.EmployeeID

                                               AND md13_b.IDType = @IDSIMB

              LEFT JOIN PHRPA0013 AS md13_c ON md01.EmployeeID = md13_c.EmployeeID

                                               AND md13_c.IDType = @IDSIMC

              LEFT JOIN PHRPA0013 AS md13_passport ON md01.EmployeeID = md13_passport.EmployeeID

                                                      AND md13_passport.IDType = @IDPassport

              LEFT JOIN PHRPA0006 AS md06_ktp ON md01.EmployeeID = md06_ktp.EmployeeID

                                                 AND md06_ktp.AddressType = @ResidentKTP

                                                 AND md06_ktp.StartDate <= @Date

                                                 AND md06_ktp.EndDate >= @Date

                                                        --AND md06_ktp.end_date = '99991231'

                                                 AND md06_ktp.Sequence = ( SELECT

                                                                    MAX(md6_st.Sequence)

                                                                    FROM

                                                                    PHRPA0006 md6_st

                                                                    WHERE

                                                                    md6_st.AddressType = @ResidentKTP

                                                                    AND md6_st.EmployeeID = md06_ktp.EmployeeID

                                                                    )

              LEFT JOIN PHRPA0006 AS md06_cp ON md01.EmployeeID = md06_cp.EmployeeID

                                                AND md06_cp.AddressType = @ResidentKost

                                                AND md06_cp.StartDate <= @Date

                                                AND md06_cp.EndDate >= @Date

                                                AND md06_cp.Sequence = ( SELECT

                                                                    MAX(md6_st.Sequence)

                                                                    FROM

                                                                    PHRPA0006 md6_st

                                                                    WHERE

                                                                    md6_st.AddressType = @ResidentKost

                                                                    AND md6_st.EmployeeID = md06_cp.EmployeeID

                                                                    )

              INNER JOIN PHRPA0015 AS md15 ON md01.EmployeeID = md15.EmployeeID

              LEFT JOIN @tbl_1 AS tbl_1 ON md01.EmployeeID = tbl_1.emp_id

              INNER JOIN @TableResult AS tr ON tr.EmployeeID = md01.EmployeeID

              LEFT JOIN @tbl_2 AS tbl_2 ON md01.EmployeeID = tbl_2.emp_id

              LEFT JOIN @tbl_3 AS tbl_3 ON md01.EmployeeID = tbl_3.emp_id

                  --LEFT JOIN dbo.phrpa0004 gapok ON md02_last.emp_id = gapok.emp_id -- GAPOK

                  --                                        AND gapok.start_date <= @Date

                  --                                        AND gapok.end_date >= @Date

                  --                                        AND gapok.wage_type = '1000'

                  --LEFT JOIN dbo.phrpa0004 makan ON md02_last.emp_id = makan.emp_id -- MAKAN

                  --                                        AND makan.start_date <= @Date

                  --                                        AND makan.end_date >= @Date

                  --                                        AND ( makan.wage_type IN (

                  --                                              '1200', '1201' )

                  --                                              OR makan.wage_type IN (

                  --                                              SELECT

                  --                                                  val1

                  --                                              FROM

                  --                                                  base_cust_parameter

                  --                                              WHERE

                  --                                                  param = 'IMFI_WAGE_TYPE_MK'

                  --                                                  AND start_date <= @Date

                  --                                                  AND end_date >= @Date )

                  --                                            )

                  --LEFT JOIN dbo.phrpa0004 ttp ON md02_last.emp_id = ttp.emp_id -- TRANSPORT

                  --                                      AND ttp.start_date <= @Date

                  --                                      AND ttp.end_date >= @Date

                  --                                      AND ( ttp.wage_type IN (

                  --                                            '1203', '1210', '1212' )

                  --                                            OR ttp.wage_type IN (

                  --                                            SELECT

                  --                                                  val1

                  --                                            FROM  base_cust_parameter

                  --                                            WHERE param = 'IMFI_WAGE_TYPE_TP'

                  --                                                  AND start_date <= @Date

                  --                                                  AND end_date >= @Date )

                  --                                          )

                  --LEFT JOIN dbo.phrpa0004 hp ON md02_last.emp_id = hp.emp_id -- HP

                  --                                     AND hp.start_date <= @Date

                  --                                     AND hp.end_date >= @Date

                  --                                     AND ( hp.wage_type = '1205'

                  --                                           OR ( hp.wage_type >= '1231'

                  --                                                AND hp.wage_type <= '1235'

                  --                                              )

                  --                                           OR hp.wage_type IN (

                  --                                           SELECT val1

                  --                                           FROM   base_cust_parameter

                  --                                           WHERE  param = 'IMFI_WAGE_TYPE_HP'

                  --                                                  AND start_date <= @Date

                  --                                                  AND end_date >= @Date )

                  --                                         )

                  --LEFT JOIN dbo.phrpa0004 tj ON md02_last.EmployeeID = tj.EmployeeID -- JABATAN

                  --                                     AND tj.StartDate <= @Date

                  --                                     AND tj.EndDate >= @Date

                  --                                     AND ( tj.WageType = '1202'

                  --                                           OR ( tj.WageType >= '1221'

                  --                                                AND tj.WageType <= '1229'

                  --                                              )

                  --                                           OR tj.WageType IN (

                  --                                           SELECT val1

                  --                                           FROM   base_cust_parameter

                  --                                           WHERE  param = 'IMFI_WAGE_TYPE_TJ'

                  --                                                  AND start_date <= @Date

                  --                                                  AND end_date >= @Date )

                  --                                         )

      WHERE   ( md02_last.company_id = @CompanyID

                OR @CompanyID = ''

              )

              AND md01.StartDate <= @Date

              AND md01.EndDate >= @Date

              AND ( md02_last.payroll_group = @PayrollGroup

                    OR @PayrollGroup = ''

                  )

              AND ( md02_last.employee_status = @EmployeeStatus

                    OR @EmployeeStatus = ''

                  )

              AND ( md02_last.employee_area = @EmployeeArea

                    OR @EmployeeArea = ''

                  )

              AND ( md02_last.employee_office = @EmployeeOffice

                    OR @EmployeeOffice = ''

                  )

              AND ( md02_last.employee_type = @EmployeeType

                    OR @EmployeeType = ''

                  )

              AND ( md02_last.company_id = @CompanyID

                    OR @CompanyID = ''

                  )

      		--AND md02_last.emp_id = '20050767'

      		--AND md01.EmployeeID ='11010018'

      ORDER BY md02_last.employee_office ,

              md01.EmployeeID

(2-2/4)

Project

General

Profile

PT BIA

Bug #3691 » PRPTPAALLEMPLOYEE_202506301426.sql