Hi Experts,
We created a BIRT report with 3 parameters,siteid,worktype and wo status to fetch all the open PRs,POs and inventory reservations against a workorder and the query executed very fast in a SQL developer client but the BIRT report is crashing with out of memory error(The screenshot is attached).
Can you please let us know if the page break property can be a culprit here as we set it to 5k.When we kept it to default of 40 also the report is crashing.Any hints will be appreciated.
------------------------------------------------------------------------------------------------------------------------------------------------------------
with cte1 as (SELECT distinct prl.refwo wonum, prl.siteid
FROM prline prl JOIN pr on prl.prnum = pr.prnum and prl.siteid = pr.siteid and prl.ponum is null and prl.refwo is not null
and pr.status not in ('CAN','CLOSE')
join wpmaterial wp on wp.wonum = prl.refwo and wp.siteid = prl.siteid and (wp.itemnum = prl.itemnum or wp.DESCRIPTION = prl.DESCRIPTION)
),
cte2 as (SELECT distinct pol.refwo wonum, pol.siteid
FROM poline pol JOIN po on pol.ponum = po.ponum and pol.siteid = po.siteid and pol.revisionnum = po.revisionnum
and po.status not in ('REVISD','CAN','CLOSE','PNDREV') and pol.receiptscomplete != 1 and pol.refwo is not null
join wpmaterial wp on wp.wonum = pol.refwo and wp.siteid = pol.siteid and (wp.itemnum = pol.itemnum or wp.DESCRIPTION = pol.DESCRIPTION)
),
cte3 as (SELECT distinct ir.wonum, ir.siteid
FROM invreserve ir
join wpmaterial wp on wp.wonum = ir.wonum and wp.siteid = ir.siteid and (wp.itemnum = ir.itemnum or wp.DESCRIPTION = ir.DESCRIPTION)
),
//cte4 as (SELECT w1.wonum, w1.siteid FROM workorder w1 except SELECT wp.wonum,wp.siteid FROM wpmaterial wp //wocan,can,close
),
unioncte as( select * from cte1 union select * from cte2 union select * from cte3
),
prz as( SELECT pr.prnum , pr.requestedby ,pr.description pr_description,
pr.status ,pr.shiptoattn ,
prl.prlinenum ,prl.linetype ,pr.teufuncarea ,
prl.itemnum ,prl.description PR_LINE_DESCRIPTION,prl.orderqty ,
prl.reqdeliverydate ,prl.refwo,prl.siteid,prl.ponum,prl.polinenum, prl.porevisionnum
FROM prline prl JOIN pr on prl.prnum = pr.prnum and prl.siteid = pr.siteid and pr.status not in ('CAN','CLOSE')
),
poz as( select po.ponum ,po.status , po.teufuncarea ,po.shipto ,po.shiptoattn ,pol.refwo, pol.siteid,
po.orderdate , po.receipts ,po.vendor ,pol.polinenum, pol.linetype , pol.itemnum , pol.description ,
pol.orderqty , pol.receivedqty , pol.unitcost ,pol.linecost ,pol.receiptscomplete
from poline pol join po on pol.ponum = po.ponum and pol.siteid = po.siteid and pol.revisionnum = po.revisionnum
and po.status not in ('REVISD','CAN','CLOSE','PNDREV')
),
workorders as( select workorder.siteid ,workorder.wonum ,workorder.worktype , workorder.targcompdate ,
workorder.wopriority , workorder.description ,workorder.status ,
workorder.statusdate ,workorder.storeroommtlstatus ,
workorder.dirissuemtlstatus ,workorder.workpackmtlstatus ,
workorder.availstatusdate , workorder.plusgtechtype ,workorder.targstartdate ,
workorder.schedstart , workorder.teutargcompdate , workorder.reportedby,
workorder.schedfinish , workorder.plusgmpoverridecod ,workorder.teudept ,
workorder.persongroup , workorder.istask,workorder.woclass,workorder.orgid
from workorder where workorder.orgid='' // very high level we can put woclass-'workorder' and istask=0
)
//main query
SELECT s.shipitemnum SHIP_ITEM_NUMBER, s.siteid, s.createddate CREATED_DATE ,
s.createdby CREATED_BY,p1.displayname CREATED_BY_NAME,s.status SHIP_ITEM_STATUS,
s.description SHIP_ITEM_DESCRIPTION,s.MANIFESTNUM SHIP_MANIFEST_NUMBER,
l.DESCRIPTION MANIFEST_DESCRIPTION, l.type MANIFEST_TYPE,l.STATUS MANIFEST_STATUS,
s.droplocation DROP_LOCATION, s.inout SHIP_ITEM_IN_OUT,
CASE WHEN s.onhold = '1' THEN 'Y' ELSE 'N' END SHIP_ON_HOLD,
s.shipfrom SHIP_FROM,s.shipto SHIP_TO,s.holdlocal HOLD_WHERE,s.holdlocation HOLD_LOCATION,
s.holdreason HOLD_REASON,s.fao SHIP_ITEM_FOA,s.comments SHIP_ITEM_COMMENTS,
prz.prnum PR_NUMBER, prz.requestedby PR_REPOERTED_BY,p2.displayname PR_REPORTED_BY_NAME,prz.pr_description PR_DESCRIPTION,
prz.status PR_STATUS,prz.shiptoattn PR_SHIPTO_ATTN,p4.displayname SHIP_TO_NAME,
prz.prlinenum PR_LINE_NUMBER,prz.linetype PR_LINE_TYPE,prz.teufuncarea PR_BUSINESS_AREA,
prz.itemnum PR_ITEM_NUMBER,prz.pr_line_description PR_LINE_DESCRITPION ,prz.orderqty PR_ORDER_QUANTITY ,
prz.reqdeliverydate PR_REQUIREED_DELIVERY_DATE,
s.storeloc PR_STORE_LOC,
workorder.siteid WORKORDER_STITEID,workorder.wonum WORKORDER_NUMBER ,workorder.worktype WORKORDER_WORKTYPE,
workorder.wopriority WORKORDER_PRIORITY, workorder.description WORKORDER_DESCRIPTION,workorder.status WORKORDER_STATUS,
workorder.statusdate WORKORDER_STATUS_DATE,workorder.storeroommtlstatus STOREROOM_DEMAND_STATUS,
workorder.dirissuemtlstatus DIRECT_ISSUE_DEMAND_STATUS,workorder.workpackmtlstatus OVERALL_LOWEST_STATUS,
workorder.availstatusdate LAST_MATERIAL_UPDATE, workorder.plusgtechtype WORKORDER_TECH_AUTHORITY,workorder.targstartdate WORKORDER_TARG_START,
workorder.schedstart WORKORDER_SCHED_START, workorder.teutargcompdate WORKORER_TARG_COMP_DATE,
workorder.schedfinish WORKORDER_SCHED_COMP_DATE, workorder.plusgmpoverridecod WORKORDER_OVERRIDE_CODE,workorder.teudept WORKORDER_DEPARTMENT,
workorder.persongroup WORKORDER_WORK_GROUP, workorder.reportedby WORKORDER_REPORTED_BY,p3.displayname rEPORTED_BY_NAME,
poz.ponum PO_NUMBER ,poz.status PO_STATUS, poz.teufuncarea PO_FUNCTIONAL_AREA,poz.shipto PO_SHIP_TO,poz.shiptoattn PO_SHIP_TO_ATTN,
p5.displayname PO_SHIP_TO_NAME,poz.orderdate PO_ORDER_DATE, poz.receipts PO_RECEIPT_STATUS,poz.vendor PO_VENDOR ,
C.NAME VENDOR_NAME,
poz.polinenum POL_LINE_NUMBER , poz.linetype POL_LINE_TYPE, poz.itemnum POL_ITEM_NUMBER, poz.description POL_DESCRIPTION,
poz.orderqty POL_ORDERED_QUANTITY, poz.receivedqty POL_RECEIVED_QUANTITY, poz.unitcost POL_UNIT_COST,poz.linecost POL_LINE_COST,
CASE WHEN poz.PONUM IS NOT NULL AND poz.receiptscomplete = 1 THEN 'Y' WHEN poz.PONUM IS NOT NULL THEN 'N' END POL_RECEIPTS_COMPLETE,
DATEDIFF(DAY,getdate() , s.createddate) SHIPITEM_DAYS,
case when prz.reqdeliverydate is not null then DATEDIFF(DAY,getdate() , prz.reqdeliverydate) END PRL_DELIVERY_DAYS ,
case when workorder.targstartdate is not null then DATEDIFF(DAY,getdate() , workorder.targstartdate ) END WO_TARGSTARTDATE_DAYS,
case when workorder.targcompdate is not null then DATEDIFF(DAY,getdate() , workorder.targcompdate ) END WO_TARGCOMPDATE_DAYS,
case when workorder.schedstart is not null then DATEDIFF(DAY,getdate() , workorder.schedstart ) END WO_SCHED_START_DAYS,
case when workorder.schedfinish is not null then DATEDIFF(DAY,getdate() , workorder.schedfinish) END WO_SCHED_FINSIH_DAYS,
case when workorder.availstatusdate is not null then DATEDIFF(DAY,getdate() , workorder.availstatusdate) END WO_MATERIAL_DAYS
FROM workorders workorder
JOIN unioncte z on workorder.wonum = z.wonum and workorder.siteid = z.siteid
LEFT JOIN prz prz on workorder.wonum = prz.refwo and workorder.siteid = prz.siteid
LEFT JOIN poz poz on workorder.wonum = poz.refwo and workorder.siteid = poz.siteid
LEFT JOIN companies c on poz.vendor = c.company
LEFT JOIN lgstshipitems s on workorder.wonum = s.wonum and s.status not in ('cancelled','completed')
LEFT JOIN lgstmanifests l on l.manifestnum = s.manifestnum
LEFT JOIN person p1 on p1.personid = s.createdby
LEFT JOIN person p2 on p2.personid = prz.requestedby
LEFT JOIN person p3 on p3.personid = workorder.reportedby
LEFT JOIN person p4 on p4.personid = prz.shiptoattn
LEFT JOIN person p5 on p5.personid = POz.shiptoattn
where params[where]
and workorder.orgid in (select locationorg from person where upper(personid)= upper(' params[userName].value '))
----------------------------------------------------------------------------------------------------------------------------------------------------------------
#Administration#Customizations#EverythingMaximo------------------------------
Mahadevan Ramakrishnan
------------------------------