AX2012 常用表关系(客户地址,联系信息)

//客户地址信息

static void CustAddressInformation(Args _args)

{

    CustTable               custTable;

    DirPartyTable           dirPartyTable;

    DirPartyLocation        dirPartyLocation;

    LogisticsLocation       logisticsLocation;

    LogisticsPostalAddress  logisticsPostalAddress;

    TransDate               dateFrom,dateTo;

    ;

    dateFrom = dateNull();

    dateTo   = dateMax();

    while select validTimeState(dateFrom,dateTo) custTable

        where custTable.AccountNum == 'US-027'

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join dirPartyLocation

                where dirPartyLocation.Party == dirPartyTable.RecId

                join logisticsLocation

                    where logisticsLocation.RecId == dirPartyLocation.Location

                    join logisticsPostalAddress

                        where logisticsPostalAddress.Location == logisticsLocation.RecId

    {

        print strFmt("ACC:%1,ADD:%2,DES:%3",custTable.AccountNum,logisticsPostalAddress.Address,logisticsLocation.Description);

    }

    pause;

    /*

    SELECT C.ACCOUNTNUM,LP.ADDRESS AS 'Address',L.DESCRIPTION,lp.RECID,c.RECID FROM CUSTTABLE C

        JOIN DIRPARTYTABLE D ON D.RECID = C.PARTY

        JOIN DIRPARTYLOCATION DL ON DL.PARTY = D.RECID

        JOIN LOGISTICSLOCATION L ON L.RECID = DL.LOCATION

        JOIN LOGISTICSPOSTALADDRESS LP ON LP.LOCATION = L.RECID

        WHERE C.DATAAREAID ='LYDI' AND C.ACCOUNTNUM ='US-027' AND LP.VALIDFROM > '2013/4/1'

    */

}

客户联系信息

//客户联系信息

static void CustContuctInformation(Args _args)

{

    CustTable                   custTable;

    DirPartyTable               dirPartyTable;

    ContactPerson               contactPerson;

    DirPerson                   dirPerson;

    DirPersonName               dirPersonName;

    LogisticsElectronicAddress  logisticsElectronicAddress;

    DirPartyLocation            dirPartyLocation;

    LogisticsLocation           logisticsLocation;

    LogisticsPostalAddress      logisticsPostalAddress;

    TransDate                   dateFrom,dateTo;

    ;

    //Email

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactEmail;

    //Email

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactEmail;

    //Phone

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactPhone;

    //URL

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactURL;

    //Telex

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactTelex;

    //Fax

    select * from custTable

        join dirPartyTable

            where dirPartyTable.RecId == custTable.Party

            join logisticsElectronicAddress

                where logisticsElectronicAddress.RecId == dirPartyTable.PrimaryContactFax;

    //ContactPerson information

    select validTimeState(dateFrom,dateTo) custTable

        where custTable.AccountNum == 'US-027'

        join contactPerson

            where contactPerson.CustAccount == custTable.AccountNum

            join dirPartyTable

                where dirPartyTable.RecId == contactPerson.Party

                join dirPerson

                    where dirPerson.RecId == dirPartyTable.RecId

                    join dirPersonName

                        where dirPersonName.Person == dirPerson.RecId

                join dirPartyLocation

                    where dirPartyLocation.Party == dirPartyTable.RecId

                    join logisticsLocation

                        where logisticsLocation.RecId == dirPartyLocation.Location

                        join logisticsPostalAddress

                            where logisticsPostalAddress.Location == logisticsLocation.RecId;

    /*

    SELECT C.ACCOUNTNUM,C.DATAAREAID,L.LOCATOR,L.DESCRIPTION FROM CUSTTABLE C

    JOIN DIRPARTYTABLE D ON D.RECID = C.PARTY

    JOIN LOGISTICSELECTRONICADDRESS L ON (L.RECID = D.PRIMARYCONTACTPHONE OR L.RECID = D.PRIMARYCONTACTFAX)

    WHERE C.DATAAREAID ='LYDI' AND C.ACCOUNTNUM ='US-027'

    */

}