NameSpace: Report Functions
Function Name |
Description |
Syntax |
---|---|---|
GETFISCALENDDATE |
Function returns end date for a fiscal year |
getfiscalenddate (ORGANIZATION VARCHAR2) |
GETFISCALSTARTDATE |
Function returns start date of a fiscal year |
getfiscalstartdate (ORGANIZATION VARCHAR2) |
GETGENREPPARAMDESC |
||
O7OZCLAIVMRS |
Returns the VMRS description |
O7OZCLAIVMRS ('EN', SYSLEVEL, ASMLEVEL, COMPLEVEL, 'SYS') |
R5GETSYSDATE |
Function returns sysdate |
Function takes no arguments. eg R5GETSYSDATE() |
REPACTDMA |
Returns Direct Material Cost |
repactdma ( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) repactdma ( source string, code string, class string,datefrom DATE,dateto DATE, warranty string ) |
REPACTFIX |
Returns Service Cost |
repactfix( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) |
REPACTHIR |
Returns Hired Labor Cost |
repacthir ( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) |
REPACTLAB |
Returns Labor Cost |
repactlab( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) |
REPACTMAT |
Returns Material Cost |
repactmat ( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) |
REPACTTOOL |
Returns Tool Cost |
repacttool ( 'EVT', WCL_EVENT, NULL, NULL, NULL, WCL_WARRANTY) |
REPALLOCATEQTY |
Returns the quantity allocated for a particular part |
REPALLOCATEQTY(part VARCHAR2,org VARCHAR2,store VARCHAR2 |
REPCOMMENTS |
Function returns comments from R5ADDETAILS |
REPCOMMENTS( p_code VARCHAR2,p_org VARCHAR2,p_ent VARCHAR2,p_line VARCHAR2) |
REPCUSTOMATTRIB |
Returns custom attributes from R5Propertyvalues |
REPCUSTOMATTRIB(p_code VARCHAR2,p_org VARCHAR2,p_attrib VARCHAR2,p_entity VARCHAR2) |
REPDATEADD |
Returns the calculated date after adding value to the date passed, based on the date part parameter |
repdateadd ('dd', 2, [EVT_TARGET]) this will add two days to EVT_TARGET and return the calculated date. repdateadd ('dd', 2, [EVT_TARGET]). Values need to pass for date_part are as follows dd', 'd' -Days 'mm', 'm' - Months 'yyyy', 'yy' - Years 'qq', 'q' - Quarters 'wk', 'ww' - Weeks 'hh', 'h' - Hours 'mi', 'n' - Minutes 'ss', 's' - Seconds 'ms' - Milliseconds |
REPDATEDIFF |
Returns the calculated date after adding value to the date passed, based on the date part parameter |
repdateadd ('dd', 2, [EVT_TARGET]) this will add two days to EVT_TARGET and return the calculated date. REPDATEDIFF( date_part string, start_date datetime, end_date datetime) -- Values need to pass for date_part are as follows 'dd', 'd' - Days 'mm', 'm' - Months 'yyyy', 'yy' - years 'qq', 'q' - Quarters 'wk', 'ww' - Weeks 'hh', 'h' - Hours 'mi', 'n' - Minutes 'ss', 's' - Seconds 'ms' - Milliseconds |
REPEVTSYSTEMS |
Returns the systems associated with event code passed to the function |
REPEVTSYSTEMS(deforg string, evtcode string) |
REPEXCH |
Function returns the exchange rate |
REPEXCH (event VARCHAR2,reportingcurr VARCHAR2,actual_current VARCHAR2) |
REPFMTMONNUM |
Function returns monetary number in user locale format |
repfmtnum (r5User VARCHAR2, NumberToFormat NUMBER) |
REPFMTNUM |
Function returns non- monetary number in user locale format |
repfmtmonnum (User VARCHAR2, NumToFmt NUMBER) |
REPFMTNUM2 |
Function returns non- monetary number in user locale format without rounding |
repfmtnum2 (User VARCHAR2, NumToFmt NUMBER) |
REPFMTWHOLENUM |
Function returns whole number in user locale format |
repfmtwholenum (User VARCHAR2, NumToFmt NUMBER) |
REPGETADDRESS |
Function returns address from R5ADDRESS |
REPGETADDRESS(rentity VARCHAR2,rtype VARCHAR2,code VARCHAR2) |
REPGETADDTEXT |
Function returns comment from R5ADDETAILS |
REPGETADDTEXT(rentity varchar, clang varchar,code varchar) |
REPGETCOMPDATE |
Function returns Date in format 'YYYY-MM-DD' |
REPGETCOMPDATE(udate DATE) |
REPGETDELADD |
Get Delivery Address from R5DELADDRESSES. |
REPGETDELADD(code varchar2) |
REPGETDESC |
Returns the description of an entity |
REPGETDESC('EN','UCOD',[Work].[R5E VENTS].[EVT_STATUS],'EVST',NULL) |
REPGETEXCHANGE RATE |
GETEXCHANGERATE(reportingCurrenc y VARCHAR2,eventOrganisation VARCHAR2,exchangeDate DATE) |
|
REPGETFMTDATE |
Returns the Date as per the user Locale. |
REPGETFMTDATI(locale varchar2, date Date) |
REPGETFMTDATI |
Returns the Date and Time as per the user Locale |
REPGETFMTDATI(locale varchar2, date Date) |
REPGETHIERCODE |
Function returns Hierarchy code |
REPGETHIERCODE(rectype (Hierarchy type) VARCHAR,rcodev (parameter for hierarchy to be generated) VARCHAR, chkcodev (parameter to be checked in hierarchy) VARCHAR,rcoden (numeric parameter for hierarchy to be generated) NUMBER,chkcoden (numeric parameter to be checked in hierarchy) NUMBER,rorg (organization parameter) VARCHAR2,seqno (sequence number of hierarchy element) NUMBER) |
REPGETHOURS |
Function returns the time of day in HH24:MI:SS |
REPGETHOURS(seconds Number) |
REPGETORDTERM |
Function return order Terms from R5ORDERTERMS |
REPGETORDTERM(code varchar2) |
REPGETPARAMDESC |
Function returns parameter description |
REPGETPARAMDESC(rlang varchar2, rentity varchar2, code varchar2, rtype varchar2, type varchar) |
REPGETSYSDATE |
Returns the offset date of the user organization |
REPGETSYSDATE (org VARCHAR2) |
REPGETTEXT |
Function used to get the Boilertexts |
REPGETTEXT(botfunction VARCHAR2,botlang VARCHAR2,botnumber NUMBER) |
REPGETVMRSDESC |
Returns the VMRS description |
repgetvmrsdesc ('EN', R5ACTIVITIES.ACT_SYSLEVEL, R5ACTIVITIES.ACT_ASMLEVEL, R5ACTIVITIES.ACT_COMPLEVEL) |
REPGETWODESC |
Function returns the works order description |
REPGETWODESC(sLang nvarchar(2), sRentity varchar(4), sEntcode varchar(4000), sRtype varchar(4), sUtype varchar(4)) |
REPGTEXCH |
Function returns gtexch |
REPGTEXCH(curr IN VARCHAR2,vorg IN VARCHAR2,exchdate IN DATE) |
REPGTTIME |
Function returns GTTime |
REPGTTIME( corg IN VARCHAR2) |
REPHOURS |
Function returns Labor hours |
REPHOURS( cevent IN VARCHAR2) |
REPINTRANSITQTY |
Returns the intransit quantity for the selected part. |
REPINTRANSITQTY(part varchar2,org varchar2,store varchar2) |
REPLABOUR |
Function returns labor cost |
REPLABOUR( cevent IN VARCHAR2) |
REPMATERIAL |
Function returns material cost |
REPMATERIAL( cevent IN VARCHAR2) |
REPNORMALSCORE |
Function to return Normal Score based on the query output or calculated value |
REPNORMALSCORE(cKpicode VARCHAR2,cKpitype VARCHAR2,nValue NUMBER) |
REPONORDERQTY |
Function returns the ordered quantity for the part |
REPONORDERQTY(part VARCHAR2,org VARCHAR2,store VARCHAR2) |
REPONREQUIQTY |
Function returns the requisitioned quantity |
REPONREQUIQTY(part VARCHAR2,org VARCHAR2,store VARCHAR2) |
REPORGDEFCUR |
Function returns currency from R5ORGANISATION |
REPORGDEFCUR(vorg VARCHAR2) |
REPPARAMDATE |
Returns Date parameter string in user format |
Parameter date should be in YYYY-MM- DD format. |
REPPRINTED |
Function updates the print Flag in Tables |
REPPRINTED(code VARCHAR2,destype VARCHAR2,code1 VARCHAR2,code2 VARCHAR2,code3 VARCHAR2,code4 VARCHAR2) |
REPRESERVEDQTY |
Returns the reserved quantity |
REPRESERVEDQTY(part varchar,org varchar, store varchar) |
REPTOOLS |
Function returns tool cost |
REPTOOLS( cevent IN VARCHAR2) |
Other functions
Function Name |
Description |
Syntax |
---|---|---|
O7ACTQTY |
Returns the activity quantity in an event |
o7actqty ([Work].[R5ACTIVITIES].[ACT_EVENT],[Work].[R5ACTIVITIES].[ACT_ACT]) |
O7AXTDMA |
Returns the extra charges for direct material receipts |
o7axtdma ([Purchasing]. [R5ORDERS].[ORD_CODE],[Purchasing].[R5ORDERS].[ORD_ORG],[Purchasing].[R5ORDERLINES].[ORL_ORDLINE], 20) |
O7BINQTY |
Returns the total parts quantity in bin |
o7binqty ([Work].[R5ACTIVITIES].[ACT_EVENT],[Work].[R5ACTIVITIES].[ACT_ACT],[Materials].[R5BINSTOCK].[BIS_PART],[Materials].[R5PARTS].[PAR_ORG],[Materials].[R5BINSTOCK].[BIS_STORE],[Materials].[R5BINSTOCK].[BIS_BIN],[Materials].[R5BINSTOCK].[BIS_LOT]) |
O7CALCCOSTS |
Returns the total PO line costs for a project |
o7calccosts ([Purchasing].[R5ORDERLINES].[ORL_PROJECT],[Purchasing].[R5ORDERLINES].[ORL_ORDER],[Purchasing].[R5ORDERLINES].[ORL_ORDER_ORG]) |
O7CALCORDVALUE |
Returns the PO value for a project |
o7calcordvalue ([Purchasing].[R5ORDERLINES].[ORL_PROJECT],[Purchasing].[R5ORDERLINES].[ORL_ORDER],[Purchasing].[R5ORDERLINES].[ORL_ORDER_ORG]) |
O7CCODE |
Returns the system R-code for an entity |
o7ccode ([Base].[R5UCODES].[UCO_CODE],[Base].[R5UCODES].[UCO_RENTITY]) |
O7COUNTITEMS |
Returns the number of PO line items for a Project |
o7countitems ([Purchasing].[R5ORDERLINES].[ORL_PROJECT],[Purchasing].[R5ORDERLINES].[ORL_ORDER],[Purchasing].[R5ORDERLINES].[ORL_ORDER_ORG]) |
O7COUNTPARTLINES |
Returns the total material part lines |
o7countpartlines ([Materials].[R5MATLPARTS].[MLP_MATLIST],[Materials].[R5MATLPARTS].[MLP_MATLREV]) |
O7CPSTOR |
Returns the parent store code |
o7cpstor ([Materials].[R5STORES].[STR_CODE]) |
O7GETACT |
Returns the activity number for the event. The second parameter could be NULL, 'Services on Reqs' or 'Services on POs'. |
o7getact ([Work].[R5ACTIVITIES].[ACT_EVENT],null,[Work].[R5ACTIVITIES].[ACT_SUPPLIER],[Work].[R5ACTIVITIES].[ACT_SUPPLIER_ORG]) |
O7GETAPPROVEDRE QLINE |
Returns the approved line number for a given requisition. The second parameter could be NULL, or 'Services for POs'. |
o7getapprovedreqline([Materials].[R5REQUISLINES].[RQL_REQ],null) |
O7GETBLANKETLINE |
Returns the blanket line for a given blanket PO, if there exists a unique line |
o7getblanketline ([Purchasing].[R5BLANKETORDLINES].[BLL_BLANKETORDER]) |
O7GETINVTAX |
Returns the tax percentage that needs to be included in inventory price |
o7getinvtax ([Materials].[R5TAXCODERATES].[TCR_TAX],'01-JAN-2002'). Second parameter should be a date between)[Materials].[R5TAXRATEVALUES].[TXV_START] and [Materials].[R5TAXRATEVALUES].[TXV_END] |
O7GETLEADTIME |
Returns the lead time for an order |
o7getleadtime ([Materials].[R5TRANSLINES].[TRL_ORDER],[Materials].[R5TRANSLINES].[TRL_ORDER_ORG],[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_QTY]) |
O7GETPRC |
Returns the part price |
o7getprc ([Materials].[R5TRANSTOCK].[TST_STORE],[Materials].[R5TRANSTOCK].[TST_PART],[Materials].[R5TRANSTOCK].[TST_PART_ORG],[Materials].[R5STORES].[STR_ORG],'B','P',NULL) |
O7GETRECVDATE |
Returns the order received date from the transaction |
o7getrecvdate (([Materials].[R5TRANSLINES].[TRL_ORDER],[Materials].[R5TRANSLINES].[TRL_ORDER_ORG],[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_QTY],'recvdate') |
O7GETREQTOTAL |
Returns the sum of the price for a given requisition code |
o7getreqtotal ([Materials].[R5REQUISITIONS].[REQ_CODE]) |
O7GETTAX |
Returns the tax rate |
o7gettax ([Materials].[R5TAXCODERATES].[TCR_TAX],'01-JAN-2002'). Second parameter should be a date between)[Materials].[R5TAXRATEVALUES].[TXV_START] and [Materials].[R5TAXRATEVALUES].[TXV_END] |
O7GETTSKPRICE |
Returns the task price |
o7gettskprice ([Work].[R5TASKPRICES].[TPR_TASK],[Work].[R5TASKPRICES].[TPR_ORG],[Work].[R5TASKPRICES].[TPR_REVISION],'R5') |
O7GETUSAGE |
Returns the equipment primary usage |
o7getusage ([Objects].[R5OBJECTS].[OBJ_CODE],[Objects].[R5OBJECTS].[OBJ_ORG]) |
O7GTCORE |
Returns the core value of a part |
o7getcore ([Materials].[R5STOCK].[STO_STORE],[Materials].[R5STOCK].[STO_PART],[Materials].[R5STOCK].[STO_PART_ORG],[Materials].[R5STOCK].[STO_CLASS_ORG],NULL,NULL) |
O7GTEXCH |
Returns the default exchange rate for a given currency |
o7gtexch ('USD',[Base].[R5ORGANIZATION].[ORG_CODE],'1-JAN-2004') |
O7GTHOUR |
Returns the total booked hours for a WO activity |
o7gthour ([Work].[R5ACTIVITIES].[ACT_EVENT],[Work].[R5ACTIVITIES].[ACT_ACT]) |
O7GTTIME |
Returns system time for specified organization |
o7gttime ([Base].[R5ORGANIZATION].[ORG_CODE]) |
O7GTTOOL |
Returns an indicator to verify whether tools are required for the event |
o7gttool ([Work].[R5ACTIVITIES].[ACT_EVENT],[Work].[R5ACTIVITIES].[ACT_ACT],[Work].[R5ACTIVITIES].[ACT_MRC]) |
O7GTUCOD |
Returns the user U-code |
o7gtucod ([Base].[R5UCODES].[UCO_RCODE],[Base].[R5UCODES].[UCO_RENTITY]) |
O7ISSQTY |
Returns the issued quantity per event/activity/part/store |
o7issqty ([Materials].[R5TRANSLINES].[TRL_EVENT],[Materials].[R5TRANSLINES].[TRL_ACT],[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_STORE]) |
O7MATRET |
Returns the material quantity returned from an event |
o7matret (O7MATRET([Materials].[R5TRANSLINES].[TRL_ORDER],[Materials].[R5TRANSLINES].[TRL_EVENT],'EVNT',[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_LOT]) |
O7MLPQTY |
Returns the total planned parts quantity for an event activity in a store |
o7mlpqty ([Materials].[R5TRANSLINES].[TRL_EVENT],[Materials].[R5TRANSLINES].[TRL_ACT],[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_STORE]) |
O7OBJCAT |
Returns the category of an object |
o7objcat ([Objects].[R5OBJECTS].[OBJ_CODE],[Objects].[R5OBJECTS].[OBJ_ORG]) |
O7PLNQTY |
Returns the planned part quantity for a material line |
o7plnqty ([Materials].[R5TRANSLINES].[TRL_EVENT],[Materials].[R5TRANSLINES].[TRL_ACT],[Materials].[R5TRANSLINES].[TRL_PART],[Materials].[R5TRANSLINES].[TRL_PART_ORG],[Materials].[R5TRANSLINES].[TRL_STORE],1) |
O7RESQTY |
Returns the reserved part quantity for an event activity |
o7resqty ([Work].[R5RESERVATIONS].[RES_EVENT],[Work].[R5RESERVATIONS].[RES_ACT],[Work].[R5RESERVATIONS].[RES_PART],[Work].[R5RESERVATIONS].[RES_PART_ORG],[Work].[R5RESERVATIONS].[RES_STORE]) |
O7RETQTY |
Returns the total quantity returned from an event activity to the supplier |
o7retqty ([Work].[R5ACTIVITIES].[ACT_EVENT],[Work].[R5ACTIVITIES].[ACT_ACT]) |
O7STOQTY |
Returns the available part quantity in a store |
o7stoqty ([Work].[R5RESERVATIONS].[RES_EVENT],[Work].[R5RESERVATIONS].[RES_ACT],[Work].[R5RESERVATIONS].[RES_PART],[Work].[R5RESERVATIONS].[RES_PART_ORG],[Work].[R5RESERVATIONS].[RES_STORE]) |
STO_QTY |
Returns the total stock quantity of parts in a store |
sto_qty([Materials].[R5STOCK].[STO_PART],[Materials].[R5STOCK].[STO_STORE]) |