To get employee personal number, name , mail id and phone in d365 report.

(0) Share

Report

Posted on by Dineshkarlekar

1,836

I am trying to create report in d365 , from human resource module I want to get employee personal number, name , mail id and phone.

how can I achive that as i am using hcmworker, dirperson, and logisticselectronicaddress table ,can anyone please help me out with it .

I have the same question (0)

All responses (16)

Answers (2)

Martin Dráb 237,896 Most Valuable Professional on at

Like (1)

Report

What problem do you have with it? It's difficult to help you with an unknown problem. You already seem to know anout the tables, which tell us what the problem isn't - but now what it is.

By the way, wouldn't it be easier if you used a data entity instead of tables directly? Data entities already provided simplified a view, so you don't need to worry about these details.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
I write a runnable class before doing the code for report but I employee contact number is missng in output also i want to tell you that the phone no and mail id is store in same field i.e logisticsElectronicAddress.Locator so ho do I get phone no and mail id in my report my runnable class code is below

while SELECT * FROM HcmWorker ORDER BY DirPerson.Name desc JOIN DirPerson where HcmWorker.Person == DirPerson.RecId OUTER JOIN * FROM LogisticsElectronicAddress where DirPerson.PrimaryContactPhone == LogisticsElectronicAddress.RecId OUTER JOIN * FROM LogisticsElectronicAddress where DirPerson.PrimaryContactEmail == LogisticsElectronicAddress.RecId OUTER JOIN RecId FROM DirPartyTable where DirPerson.RecId == DirPartyTable.RecId EXISTS JOIN * FROM HcmEmployment WHERE HcmWorker.RecId == HcmEmployment.Worker { Info(strFmt("%1 -%2 -%3 -%4 -%5 -%6",hcmWorker.PersonnelNumber,dirPerson.Name,logisticsElectronicAddress.Locator, logisticsElectronicAddress.Locator,logisticsElectronicAddress.LocatorExtension, hcmEmployment.EmploymentType)); }

Was this reply helpful? Yes No
Suggested answer

dakotaaba 105 on at

Like (1)

Report

Hi Dineshkarlekar,

You are on the right direction. To further take reference on how the data is being displayed. You can refer the HCMWorker (AOTName) form.

In brief, this should happen:

PersonnelNumber must be retrieved from HCMWorker.

Name from DirPerson

Phone and Email from LogisticElectronicAddress

Since the question is focused on how to retrieve Phone & Email from the same Locator field, the answer to this is below:

Declare 2 variables for the LogisticElectronicAddress: say for example LPhone, LEmail

For getting Phone, Outerjoin Lphone with DirPerson and add a link Lphone.RecId = DirPerson.PrimaryContactPhone. Use Locator field from Lphone to display Phone

Similay for Email, Outerjoin LEmail with DirPerson and add a link LEmail.RecId = DirPerson.PrimaryContactEmail. Use Locator field from LEmail to display Email

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report

I did the same in above job code but not getting the phone no in output

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,896 Most Valuable Professional on at

Like (1)

Report
An obvious bug in your code is that you're using LogisticsElectronicAddress twice and you hope that logisticsElectronicAddress.Locator will contain an email and a phone number at the same time, which is clearly impossible. Use two table buffers for LogisticsElectronicAddress :

HcmWorker hcmWorker; DirPerson dirPerson; LogisticsElectronicAddress primaryPhone; LogisticsElectronicAddress primaryEmail; while select hcmWorker join dirPerson where dirPerson.RecId == hcmWorker.Person outer join primaryPhone where primaryPhone.RecId == dirPerson.PrimaryContactPhone outer join primaryEmail where primaryEmail.RecId == dirPerson.PrimaryContactEmail { info(strFmt("%1 -%2 -%3 -%4 -%5", hcmWorker.PersonnelNumber, dirPerson.Name, primaryEmail.Locator, primaryPhone.Locator, primaryPhone.LocatorExtension)); }

If it doesn't return the email and the phone number, it likely means that dirPerson.PrimaryContactPhone and dirPerson.PrimaryContactEmail are empty.

Was this reply helpful? Yes No

Dineshkarlekar

1,836 on at

Like (0)

Report

hi Martin ,

thanks for reply ,

I have completed employee list report and I am getting required fields in my output .

posting the code below, thank you for correcting my code.

[DataContractAttribute]
public class EmpDetailsContract
{
    HcmPersonnelNumberId      PersonnelNumber;

    [DataMemberAttribute(identifierStr(HcmPersonnelNumberId))]
    public  HcmPersonnelNumberId   parmHcmPersonnelNumberId(HcmPersonnelNumberId  _HcmPersonnelNumberId =  PersonnelNumber)
    {
        PersonnelNumber   = _HcmPersonnelNumberId;
        return			  PersonnelNumber;
    }

}

class EmpDetailsController	extends SrsReportRunController
{
 
    EmpDetailsContract       Contract;
    HcmWorker               dataTableOne;


    protected void PrePromptModifyContract()
    {
        super();
        if ( this.parmArgs() && this.parmArgs().record())
        {
            contract = this .parmReportContract().parmRdpContract() as EmpDetailsContract;
            dataTableOne = this.parmArgs().record();
            contract.parmHcmPersonnelNumberId(dataTableOne.PersonnelNumber);
        }
    }

    static void main(Args args)
    {
        HcmWorker          dataTableOne;
        EmpDetailsController  ssrsController;
        datatableone = args.record() as HcmWorker;
        ssrsController=new  EmpDetailsController();
        ssrsController.parmArgs(args);

        ssrsController.parmReportName(ssrsReportStr(EmpDetailsRpt,PrecisionDesign1 ));
        ssrsController.parmShowDialog(false);
        ssrsController.parmLoadFromSysLastValue(false);
        ssrsController.startOperation();
    }

}

[SRSReportParameterAttribute(classstr(EmpDetailsContract))]
Public class EmpDetailsDp  extends SRSReportDataProviderBase
{
    HumanResourceTmp				HumanResourceTmp;
    [SrsReportDataSetAttribute(tableStr('HumanResourceTmp'))]
    public HumanResourceTmp getHumanResourceTmp()
    {
        select * from HumanResourceTmp;
        return		  HumanResourceTmp;
    }

    public void processReport()
    {
        HcmPersonnelNumberId				 PersonnelNumber;
        EmpDetailsContract  contract = this.parmDataContract();
        PersonnelNumber = contract.parmHcmPersonnelNumberId();

  
        Query q = new Query();

        QueryBuildDataSource  HcmWorkerDs  = q.addDataSource(tableNum(HcmWorker));

        QueryBuildDataSource dirPersonDs = HcmWorkerDs.addDataSource(tableNum(DirPerson));
        dirPersonDs.addLink(fieldNum(DirPerson, RecId),fieldNum(HcmWorker,Person ));
        dirPersonDs.joinMode(JoinMode::InnerJoin);

        QueryBuildDataSource  dirPartyTableDs = dirPersonDs.addDataSource(tableNum(DirPartyTable));
        dirPartyTableDs.addLink(fieldNum(DirPartyTable, RecId),fieldNum(DirPerson, RecId));
        dirPartyTableDs.joinMode(JoinMode::OuterJoin);
 

        if (PersonnelNumber != "")
        {
            QueryBuildRange qbr = HcmWorkerDs.addRange(fieldNum(HcmWorker,PersonnelNumber));
            qbr.value(queryValue(PersonnelNumber));
        }

        QueryRun qr = new QueryRun(q);

        while(qr.next())
        {

            HcmWorker                           hcmWorker		= qr.get(tableNum(HcmWorker));
            DirPerson		                    dirPerson	    = qr.get(tableNum(DirPerson));
            DirPartyTable                       dirPartyTable   = qr.get(tableNum(DirPartyTable));
            LogisticsElectronicAddress          logisticsElectronicAddress , logisticsElectronicAddress1;


               SELECT
            * FROM logisticsElectronicAddress
            where  logisticsElectronicAddress.RecId  == dirPartyTable.PrimaryContactPhone;

              SELECT
                   * FROM logisticsElectronicAddress1
                where  logisticsElectronicAddress1.RecId  == dirPartyTable.PrimaryContactEmail;
               

            {

                HumanResourceTmp.clear();
                HumanResourceTmp.PersonnelNumber     =  hcmWorker.PersonnelNumber;
                HumanResourceTmp.Name	       	     =  dirPartyTable.Name;
                if (logisticsElectronicAddress1.Type == LogisticsElectronicAddressMethodType::Email)
                {
                    HumanResourceTmp.Email	         =  logisticsElectronicAddress1.Locator;
                }
				
                if (logisticsElectronicAddress.Type  == LogisticsElectronicAddressMethodType::Phone)
                {
                    HumanResourceTmp.Phone           =  logisticsElectronicAddress.Locator;
                }
              
                HumanResourceTmp.LocatorExtension    =  logisticsElectronicAddress.LocatorExtension;
                //HumanResourceTmp.EmploymentType    =  hcmEmployment.EmploymentType;
                HumanResourceTmp.insert();

            }
        }

    }

}

Was this reply helpful? Yes No

dakotaaba 105 on at

Like (1)

Report

Hi Dineshkarlekar - As mentioned above declare 2 variables for the LogisticElectronicAddress as mentioned before (Lphone & Lemail)

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
I want to show user id with personal number in my report , and i want to get only those records which having userid assign with personal number but i am getting all records which do not have user id and records having user id with personal number . i am using dp class in my report in my report not getting what is wrong with it.

[SRSReportParameterAttribute(classstr(EmpDetailsContract))] Public class EmpDetailsDp extends SRSReportDataProviderBase { HumanResourceTmp HumanResourceTmp; [SrsReportDataSetAttribute(tableStr('HumanResourceTmp'))] public HumanResourceTmp getHumanResourceTmp() { select * from HumanResourceTmp; return HumanResourceTmp; } public void processReport() { HcmPersonnelNumberId PersonnelNumber; EmpDetailsContract contract = this.parmDataContract(); PersonnelNumber = contract.parmHcmPersonnelNumberId(); Query q = new Query(); QueryBuildDataSource HcmWorkerDs = q.addDataSource(tableNum(HcmWorker)); QueryBuildDataSource dirPersonDs = HcmWorkerDs.addDataSource(tableNum(DirPerson)); dirPersonDs.addLink(fieldNum(DirPerson, RecId),fieldNum(HcmWorker,Person )); dirPersonDs.joinMode(JoinMode::InnerJoin); QueryBuildDataSource dirPartyTableDs = dirPersonDs.addDataSource(tableNum(DirPartyTable)); dirPartyTableDs.addLink(fieldNum(DirPartyTable, RecId),fieldNum(DirPerson, RecId)); dirPartyTableDs.joinMode(JoinMode::InnerJoin); if (PersonnelNumber != "") { QueryBuildRange qbr = HcmWorkerDs.addRange(fieldNum(HcmWorker,PersonnelNumber)); qbr.value(queryValue(PersonnelNumber)); } QueryRun qr = new QueryRun(q); while(qr.next()) { HcmWorker hcmWorker = qr.get(tableNum(HcmWorker)); DirPerson dirPerson = qr.get(tableNum(DirPerson)); DirPartyTable dirPartyTable = qr.get(tableNum(DirPartyTable)); LogisticsElectronicAddress logisticsElectronicAddress , logisticsElectronicAddress1; DirPersonUser dirPersonUser; UserInfo userInfo; SELECT * FROM logisticsElectronicAddress where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactPhone; SELECT * FROM logisticsElectronicAddress1 where logisticsElectronicAddress1.RecId == dirPartyTable.PrimaryContactEmail; select * from dirPersonUser where dirPersonUser.PersonParty == dirPerson.RecId join userInfo where userinfo.id == dirPersonUser.User; { HumanResourceTmp.clear(); HumanResourceTmp.PersonnelNumber = hcmWorker.PersonnelNumber; HumanResourceTmp.Name = dirPartyTable.Name; if (logisticsElectronicAddress1.Type == LogisticsElectronicAddressMethodType::Email) { HumanResourceTmp.Email = logisticsElectronicAddress1.Locator; } if (logisticsElectronicAddress.Type == LogisticsElectronicAddressMethodType::Phone) { HumanResourceTmp.Phone = logisticsElectronicAddress.Locator; } HumanResourceTmp.LocatorExtension = logisticsElectronicAddress.LocatorExtension; HumanResourceTmp.UserId = dirPersonUser.User; HumanResourceTmp.insert(); } } } }

Was this reply helpful? Yes No
Martin Dráb 237,896 Most Valuable Professional on at

Like (0)

Report

The problem is that you don't have such logic in your code - at least I don't see it. Your query has no filter related to user ID.

Yes, you get dirPersonUser.User, but you insert a record to humanResourceTmp even if no user is found. If you want to have such a condition, and the join to your query. It'll do what you want and performance will be better (you currently run three extra database queries for each record returned by your main query).

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
hi martin ,

thanks for reply.

do i have to add this code in my query.

QueryBuildRange qbr = DirPersonUserDs.addRange(fieldNum(DirPersonUser,User)); qbr.value(queryValue(User));

Was this reply helpful? Yes No