Query to show account additional fields _ Details required
We Need to know whether that is a common account additional fields or add attribute under individual account.
So please send us the following screenshots.
1. Admin-> Account additional fields list view page.
2. Accounts->Edit account and show the additional field added.
Also, send us the output of the following queries after executing one after the other under Reports->New Query report.
Select * from Accountci
Select * from Accountdefinition
Sample Query:
SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", rctd.FIRST_NAME AS "Time Spent Technician", LONGTODATE(ct.TS_STARTTIME) AS "Time Spent Starttime", ct.DESCRIPTION AS "Time Spent Description",
CAST(ct.TIMESPENT AS FLOAT)/1000/3600 "TimeSpent", ct.TOTAL_CHARGE AS "Time Spent Total_Charge", attribute_303 "Customer Billing Cycle" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID INNER JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID inner join accountcontract ac on ac.accountid = asm.accountid left join billhistory bh on ac.contractid=bh.contractid left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid left join billcycle bc ON ac.contractid=bc.contractid left join ci on ci.ciid=ad.ciid left join baseelement be on be.ciid=ci.ciid LEFT JOIN Accountci aci ON be.ciid=aci.ciid WHERE wo.ISPARENT='1' AND std.STATUSNAME = 'Closed' AND sp.serviceplanname='Time & Materials' AND wo.completedtime>=<from_lastmonth> and wo.completedtime<=<to_lastmonth> group by Ad.ORG_NAME, wo.WORKORDERID, rctd.FIRST_NAME, ct.TS_STARTTIME, ct.DESCRIPTION , ct.TOTAL_CHARGE, attribute_303, ct.TimeSpent ORDER BY 2 ASC
New to ADSelfService Plus?
Related Articles
Enable Account Details for basic technicians
Account Details is an icon that is found right next to the Account drop-down in the application. For 9.4 Builds By default Account Details tab is shown ONLY for SDAccountAdmin and SDAdmin roles. 1. Take a trimmed backup/MSSQL Backup/Server ...Query to show workstation additional fields
SELECT workstation.WORKSTATIONNAME AS "Machine Name", workstation.MANUFACTURER AS "Manufacturer", workstation.SERVICETAG AS "Service Tag", workstation.MODEL AS "Model", aao.NAME AS "Vendor Name", wof.udf_char1 "Cubical Number", wof.udf_char2 ...Query to show Software license details with additional fields, allocated workstations and its users with email address
PGSQL & MSSQL: SELECT resources.resourcename "License Name", Max(softwarelist.softwarename) "Managed Software", slt.licensetype "License Type", Max(softwarelicenses.licensekey) "License Key", softwarelicenses.installationscount "No of CALs", ...Query to show user additional fields along with requests
MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", technician.attribute_302 "Technician Business Unit", aau.FIRST_NAME AS "Requester", ...Query to show purchase order full details
PGSQL: SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency", paao.NAME AS "Vendor Name", po.SHIPTO AS "Ship To", po.BILLTO ...