This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Hello ,
We have a PM projection report which works perfectly fine with PM seasons date with in the same year.
For example : If PM season Dates set for April1st to October 20th.
My issue is Report is not showing any PM records ,If the Dates are set from December1st to March31st.
Please suggest How can I fix the attached view that we are using to develop report .
Thank you in Advance.
Thanks
Deepika.
----------
CREATE or replace VIEW PMFORCAST AS
SELECT 'PM' AS CLASS,
pm.orgid,
pm.siteid,
ownergroup AS laborgroup,
pm.pmnum AS pmnum,
pm.PERSONGROUP,
pm.pmnum
|| '-'
|| LPAD (p.x, 5, '0') AS wopmparent,
NULL AS CHILD,
frequency,
frequnit,
NULL AS meterreading,
working_day (TO_CHAR (DECODE (p.x, 0, DECODE (pm.lastcompdate, NULL, pm.laststartdate, pm.lastcompdate ), DECODE (frequnit, 'DAYS', nextdate + frequency * (p.x - 1), 'WEEKS', nextdate + frequency * (p.x - 1) * 7, 'MONTHS', ADD_MONTHS (nextdate, frequency * (p.x - 1) ), 'YEARS', ADD_MONTHS (nextdate, (12 * frequency ) * (p.x - 1) ), NULL ) ), 'DD-MON-YYYY' ), pm.monday, pm.tuesday, pm.wednesday, pm.thursday, pm.friday, pm.saturday, pm.sunday ) AS lastdate,
working_day (TO_CHAR (DECODE (frequnit, 'DAYS', nextdate + frequency * p.x, 'WEEKS', nextdate + frequency * p.x * 7, 'MONTHS', ADD_MONTHS (nextdate, frequency * p.x ), 'YEARS', ADD_MONTHS (nextdate, (12 * frequency ) * p.x ), NULL ), 'DD-MON-YYYY' ), pm.monday, pm.tuesday, pm.wednesday, pm.thursday, pm.friday, pm.saturday, pm.sunday ) AS startdate,
pm.supervisor,
pm.description pmdesc,
pm.LOCATION,
lo.description locdesc,
(SELECT q.jpnum
FROM pmsequence q
WHERE INTERVAL =
(SELECT MAX (INTERVAL)
FROM pmsequence
WHERE MOD (pm.pmcounter + p.x, INTERVAL) = 0
AND pmnum = pm.pmnum
AND orgid = pm.orgid
AND siteid = pm.siteid
)
AND q.pmnum = pm.pmnum
AND q.orgid = pm.orgid
AND q.siteid = pm.siteid
) AS seqjpnum,
(SELECT SUM(jl.quantity *jl.LABORHRS)
FROM joblabor jl
WHERE pm.jpnum = jl.jpnum
AND pm.orgid = jl.orgid
AND pm.siteid = jl.siteid
) AS LABORHRS,
pm.assetnum,
ass.description AS assetdesc,
ass.location AS assetlocation,
DECODE (
(SELECT DISTINCT(PMNUM) FROM pmseasons WHERE pmseasons.pmnum = pm.pmnum
AND pmseasons.siteid = pm.siteid
AND pmseasons.orgid = pm.orgid
), NULL, 'N', 'Y' ) AS seasonal,
(SELECT j.jpduration
FROM pmsequence q,
jobplan j
WHERE INTERVAL =
(SELECT MAX (INTERVAL)
FROM pmsequence
WHERE MOD (pm.pmcounter + p.x,INTERVAL) = 0
AND pmnum = pm.pmnum
AND orgid = pm.orgid
AND siteid = pm.siteid
)
AND q.jpnum =j.JPNUM
AND q.siteid =j.siteid
AND q.pmnum = pm.pmnum
AND q.orgid = pm.orgid
AND q.siteid = pm.siteid
) AS jpduation
FROM pm,
locations lo,
asset ass,
tco_pivot p
WHERE nextdate IS NOT NULL
AND pm.LOCATION = lo.LOCATION(+)
AND pm.siteid = lo.siteid(+)
AND pm.orgid = lo.orgid(+)
AND pm.assetnum = ass.assetnum(+)
AND pm.siteid = ass.siteid(+)
AND pm.orgid = ass.orgid(+)
AND pm.frequency > 0
AND p.x <= DECODE (frequnit, 'DAYS', 365, 'WEEKS', 52, 'MONTHS', 12, 'YEARS', 1 ) -- limit to 52 periods (days,weeks,months,years)
AND pm.status = 'ACTIVE'
AND ( NOT EXISTS
(SELECT NULL
FROM pmseasons
WHERE pmseasons.pmnum = pm.pmnum
AND pmseasons.siteid = pm.siteid
AND pmseasons.orgid = pm.orgid
)
OR EXISTS
(SELECT NULL
FROM pmseasons
WHERE pmseasons.pmnum = pm.pmnum
AND pmseasons.siteid = pm.siteid
AND pmseasons.orgid = pm.orgid
AND TO_CHAR (working_day (TO_CHAR (DECODE (frequnit, 'DAYS', nextdate + frequency * p.x, 'WEEKS', nextdate + frequency * p.x * 7, 'MONTHS', ADD_MONTHS (nextdate, frequency * p.x ), 'YEARS', ADD_MONTHS (nextdate, ( 12 * frequency ) * p.x ), NULL ), 'DD-MON-YYYY' ), pm.monday, pm.tuesday, pm.wednesday, pm.thursday, pm.friday, pm.saturday, pm.sunday ), 'DDD' )
BETWEEN TO_NUMBER (TO_CHAR (TO_DATE ( LPAD (startday, 2, '0' )
|| '-'
|| startmonth
|| '-2049', 'DD-FMMONTH-YYYY' ), 'DDD' ) )
AND TO_NUMBER (TO_CHAR (TO_DATE ( LPAD (endday, 2, '0' )
|| '-'
|| endmonth
|| '-2049', 'DD-FMMONTH-YYYY' ), 'DDD' ) )
) )