Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

SOLVED
Go to solution
Highlighted
bugra
Super Collector

Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

I have a sql problem in my portlet. Problem is topic above. My data source is written below. I use sql view which is named AL_V for this portlet. I think problem is easy but i cannot find where the problem is could anyone help me?

 

Thanks

 

SELECT RESOURCE_NAME RES, USER_ID RESOURCE_ID, S_A SCHEDULED_ACTUAL, TO_CHAR(WEEK, 'DD/MM/YYYY') PERIOD, EFFORT_TOTAL EFFORT, COLOR COLOR
FROM AL_V
WHERE EFFORT_TOTAL IS NOT NULL
AND TO_CHAR(WEEK,'DD/MON/YYYY') = (SELECT START_DATE
                                   FROM KNTA_PERIODS
                                   WHERE (SEQ = (SELECT SEQ_PERIOD_TYPE_3
                                                from KRML_CALENDAR_DAYS
                                                WHERE TO_CHAR(SYSDATE, 'DD/MON/YYYY') = CALENDAR_DATE)))
AND USER_ID IN (select distinct users.user_id
        from rsc_resource_pool_managers rpm, knta_users users, rsc_rp_distribution_entries rrde
        where rpm.manager_user_id = [SYS.USER_ID]
        and rpm.resource_pool_id= rrde.resource_pool_id
        and rrde.distribution_percent>0
        and rrde.resource_id= users.user_id)
ORDER BY SCHEDULED_ACTUAL ASC

 

------------------------------------------------------

Sql code of AL_V view

 

CREATE OR REPLACE FORCE VIEW "PPMPROD_USR"."AL_V" ("RESOURCE_NAME", "USER_ID", "S_A", "COLOR", "WEEK", "EFFORT_TOTAL")
AS
  SELECT "RESOURCE_NAME",
    "USER_ID",
    "S_A",
    "COLOR",
    "WEEK",
    "EFFORT_TOTAL"
  FROM
    (SELECT ku.FULL_NAME RESOURCE_NAME,
      tts.RESOURCE_ID USER_ID,
      ('4.Talep - Gerçekleşen Efor') S_A,
      ('GREEN') COLOR,
      kp.START_DATE WEEK,
      SUM(tsl.ACTUAL_EFFORT) EFFORT_TOTAL
    FROM TM_TIME_SHEETS tts
    JOIN
      (SELECT ttsl.TIME_SHEET_ID,
        ttsl.TIME_SHEET_LINE_ID,
        ttsl.WORK_ITEM_ID,
        act.ACTUALS_ID,
        act.ACTUAL_EFFORT
      FROM TM_TIME_SHEET_LINES ttsl
      JOIN
        (SELECT ta.TIME_SHEET_LINE_ID,
          ta.ACTUALS_ID,
          tae.ACTUAL_EFFORT
        FROM TM_ACTUALS ta
        JOIN TM_ACTUALS_EFFORT tae
        ON ta.ACTUALS_ID                 =tae.ACTUALS_ID
        WHERE tae.ACTUAL_EFFORT         <> 0
        AND ta.TOTALS_FLAG               = 'Y'
        ) act ON ttsl.TIME_SHEET_LINE_ID = act.TIME_SHEET_LINE_ID
      WHERE ttsl.WORK_ITEM_SET_ID        = 30282
      ) tsl ON tts.TIME_SHEET_ID         = tsl.TIME_SHEET_ID
    JOIN KTMG_PERIODS kp
    ON tts.PERIOD_ID = kp.PERIOD_ID
    JOIN KNTA_USERS ku
    ON ku.USER_ID = tts.RESOURCE_ID
    GROUP BY tts.RESOURCE_ID,
      ku.FULL_NAME,
      kp.START_DATE
    UNION ALL
    SELECT tablo1.FULL_NAME RESOURCE_NAME,
      tablo1.USER_ID,
      tablo1.S_A,
      tablo1.COLOR,
      kp.START_DATE WEEK,
      SUM(ROUND(tablo1.EFFORT,1)) EFFORT_TOTAL
    FROM
      (SELECT ku.FULL_NAME,
        kdpv.USER_ID,
        kdpv.CALENDAR_DAY DAY,
        ('3.Talep - Planlanan') S_A,
        ('GREENYELLOW') COLOR,
        SUM(kdpv.EFFORT) EFFORT
      FROM KRSC_DAILY_PLANS2_V kdpv,
        KNTA_USERS ku
      WHERE kdpv.USER_ID = ku.USER_ID
      AND kdpv.GROUP_ID  = 30282
      AND kdpv.EFFORT    > 0
      GROUP BY ku.FULL_NAME,
        kdpv.USER_ID,
        kdpv.CALENDAR_DAY
      ORDER BY ku.FULL_NAME,
        kdpv.USER_ID,
        kdpv.CALENDAR_DAY
      ) tablo1,
      KRML_CALENDAR_DAYS kcd,
      KNTA_PERIODS kp
    WHERE tablo1.DAY          = kcd.CALENDAR_DATE
    AND kp.PERIOD_TYPE        = 'WEEK'
    AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
    GROUP BY tablo1.FULL_NAME,
      tablo1.USER_ID,
      tablo1.S_A,
      tablo1.COLOR,
      kp.START_DATE
    UNION ALL
    SELECT ku.FULL_NAME RESOURCE_NAME,
      tts.RESOURCE_ID USER_ID,
      ('2.Oper.Talep - Gerçekleşen') S_A,
      ('DARKRED') COLOR,
      kp.START_DATE WEEK,
      SUM(tsl.ACTUAL_EFFORT) EFFORT_TOTAL
    FROM TM_TIME_SHEETS tts
    JOIN
      (SELECT ttsl.TIME_SHEET_ID,
        ttsl.TIME_SHEET_LINE_ID,
        ttsl.WORK_ITEM_ID,
        act.ACTUALS_ID,
        act.ACTUAL_EFFORT
      FROM TM_TIME_SHEET_LINES ttsl
      JOIN
        (SELECT ta.TIME_SHEET_LINE_ID,
          ta.ACTUALS_ID,
          tae.ACTUAL_EFFORT
        FROM TM_ACTUALS ta
        JOIN TM_ACTUALS_EFFORT tae
        ON ta.ACTUALS_ID                 =tae.ACTUALS_ID
        WHERE tae.ACTUAL_EFFORT         <> 0
        AND ta.TOTALS_FLAG               = 'Y'
        ) act ON ttsl.TIME_SHEET_LINE_ID = act.TIME_SHEET_LINE_ID
      WHERE ttsl.WORK_ITEM_SET_ID        = 30283
      ) tsl ON tts.TIME_SHEET_ID         = tsl.TIME_SHEET_ID
    JOIN KTMG_PERIODS kp
    ON tts.PERIOD_ID = kp.PERIOD_ID
    JOIN KNTA_USERS ku
    ON ku.USER_ID = tts.RESOURCE_ID
    GROUP BY tts.RESOURCE_ID,
      ku.FULL_NAME,
      kp.START_DATE
    UNION ALL
    SELECT tablo1.FULL_NAME RESOURCE_NAME,
      tablo1.USER_ID,
      ('1.Oper.Talep - Planlanan') S_A,
      ('LIGHTSALMON') COLOR,
      tablo1.StartDate WEEK,
      SUM(tablo1.SCHEDULED_EFFORT) EFFORT_TOTAL
    FROM
      (SELECT kfwi.REQUEST_ID,
        krt.REQUEST_TYPE_NAME,
        ku.FULL_NAME,
        ku.USER_ID,
        Calendar1.StartDate,
        kfwi.SCHEDULED_EFFORT
      FROM KCRT_FG_WORK_ITEMS kfwi,
        KCRT_REQUESTS kr,
        KNTA_USERS ku,
        KCRT_REQUEST_TYPES krt,
        (SELECT kcd.CALENDAR_DATE CalendarDate,
          kp.START_DATE StartDate
        FROM KRML_CALENDAR_DAYS kcd,
          KNTA_PERIODS kp
        WHERE kp.PERIOD_TYPE      = 'WEEK'
        AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
        ) Calendar1
      WHERE kfwi.REQUEST_TYPE_ID                  = '30283'
      AND kfwi.REQUEST_ID                         = kr.REQUEST_ID
      AND ku.USER_ID                              = kr.ASSIGNED_TO_USER_ID
      AND kfwi.REQUEST_TYPE_ID                    = krt.REQUEST_TYPE_ID
      AND TO_CHAR(kfwi.USR_SCHEDULED_FINISH_DATE) = Calendar1.CalendarDate
      ORDER BY kfwi.REQUEST_ID DESC
      ) tablo1
    LEFT JOIN
      (SELECT kfwi.REQUEST_ID,
        krt.REQUEST_TYPE_NAME,
        ku.FULL_NAME,
        ku.USER_ID,
        Calendar1.StartDate,
        kfwi.ACTUAL_EFFORT
      FROM KCRT_FG_WORK_ITEMS kfwi,
        KCRT_REQUESTS kr,
        KNTA_USERS ku,
        KCRT_REQUEST_TYPES krt,
        (SELECT kcd.CALENDAR_DATE CalendarDate,
          kp.START_DATE StartDate
        FROM KRML_CALENDAR_DAYS kcd,
          KNTA_PERIODS kp
        WHERE kp.PERIOD_TYPE      = 'WEEK'
        AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
        ) Calendar1
      WHERE kfwi.REQUEST_TYPE_ID               = '30283'
      AND kfwi.REQUEST_ID                      = kr.REQUEST_ID
      AND ku.USER_ID                           = kr.ASSIGNED_TO_USER_ID
      AND kfwi.REQUEST_TYPE_ID                 = krt.REQUEST_TYPE_ID
      AND TO_CHAR(kfwi.USR_ACTUAL_FINISH_DATE) = Calendar1.CalendarDate
      ORDER BY kfwi.REQUEST_ID DESC
      ) tablo2
    ON tablo1.REQUEST_ID = tablo2.REQUEST_ID
    GROUP BY tablo1.FULL_NAME,
      tablo1.USER_ID,
      tablo1.StartDate
    ) tablo
  ORDER BY WEEK ASC,
    S_A ASC;

8 REPLIES
Rash
Regular Collector

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

Try combinng of todate with tochar

 

To_data(TO_CHAR(WEEK,'DD/MON/YYYY') ,'DDMMYYYY 24HH:MM:SS')

 

Utkarsh_Mishra
Honored Contributor

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

Hi Bugra,

 

Modify this section..

 

  AND TO_CHAR(kfwi.USR_ACTUAL_FINISH_DATE) = Calendar1.CalendarDate

 

here you are converting one date and not other.... either put this on both side

 

 to_date(kfwi.USR_ACTUAL_FINISH_DATE, 'dd-mm-yyyy') = to_date(Calendar1.CalendarDate, 'dd-mm-yyyy')

 

Same need oto be done in other date conditions as well... in your code

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
bugra
Super Collector

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

Hi again ,

 

Thanks for the answers but  I tried both of the techniques of the post but i still get same message. I changed all date types into one mask DD/MM/YYYY but still i can't get results for this error. Changed AL_V and Sql code is below. Where do I make a mistake ?

 

Buğra

 

SELECT RESOURCE_NAME RES, USER_ID RESOURCE_ID, S_A SCHEDULED_ACTUAL, TO_DATE(WEEK,'DD/MM/YYYY') PERIOD, EFFORT_TOTAL EFFORT, COLOR COLOR
FROM AL_V
WHERE EFFORT_TOTAL IS NOT NULL
AND  TO_DATE(WEEK,'DD/MM/YYYY')=(SELECT TO_DATE(START_DATE,'DD/MM/YYYY') FROM KNTA_PERIODS WHERE (SEQ = (SELECT SEQ_PERIOD_TYPE_3 from KRML_CALENDAR_DAYS
WHERE TO_DATE(SYSDATE, 'DD/MM/YYYY') = TO_DATE(CALENDAR_DATE,'DD/MM/YYYY'))))
AND USER_ID IN (select distinct users.user_id
        from rsc_resource_pool_managers rpm, knta_users users, rsc_rp_distribution_entries rrde
        where rpm.manager_user_id = 30366 and rpm.resource_pool_id= rrde.resource_pool_id
        and rrde.distribution_percent>0 and rrde.resource_id= users.user_id)
ORDER BY SCHEDULED_ACTUAL ASC

 

----------------------------------------------------------------------------------------------------

 

SELECT "RESOURCE_NAME","USER_ID","S_A", "COLOR","WEEK", "EFFORT_TOTAL"
  FROM
    (SELECT ku.FULL_NAME RESOURCE_NAME,tts.RESOURCE_ID USER_ID,('4.Talep - Gerçekleşen Efor') S_A,('GREEN') COLOR,TO_DATE(kp.START_DATE,'DD/MM/YYYY') WEEK,SUM(tsl.ACTUAL_EFFORT) EFFORT_TOTAL
    FROM TM_TIME_SHEETS tts
  JOIN
      (SELECT ttsl.TIME_SHEET_ID,ttsl.TIME_SHEET_LINE_ID, ttsl.WORK_ITEM_ID, act.ACTUALS_ID, act.ACTUAL_EFFORT
      FROM TM_TIME_SHEET_LINES ttsl
      JOIN
        (SELECT ta.TIME_SHEET_LINE_ID,ta.ACTUALS_ID,tae.ACTUAL_EFFORT
        FROM TM_ACTUALS ta
        JOIN TM_ACTUALS_EFFORT tae ON ta.ACTUALS_ID =tae.ACTUALS_ID
        WHERE tae.ACTUAL_EFFORT <> 0 AND ta.TOTALS_FLAG= 'Y') act ON ttsl.TIME_SHEET_LINE_ID = act.TIME_SHEET_LINE_ID
  WHERE ttsl.WORK_ITEM_SET_ID = 30282) tsl ON tts.TIME_SHEET_ID = tsl.TIME_SHEET_ID
  JOIN KTMG_PERIODS kp ON tts.PERIOD_ID = kp.PERIOD_ID
  JOIN KNTA_USERS ku ON ku.USER_ID = tts.RESOURCE_ID
  GROUP BY tts.RESOURCE_ID,ku.FULL_NAME,kp.START_DATE
  UNION ALL
  SELECT tablo1.FULL_NAME RESOURCE_NAME,tablo1.USER_ID,tablo1.S_A,tablo1.COLOR,TO_DATE(kp.START_DATE,'DD/MM/YYYY') WEEK,SUM(ROUND(tablo1.EFFORT,1)) EFFORT_TOTAL
    FROM
      (SELECT ku.FULL_NAME,kdpv.USER_ID,to_Date(kdpv.CALENDAR_DAY,'DD/MM/YYYY') DAY,('3.Talep - Planlanan') S_A,('GREENYELLOW') COLOR,SUM(kdpv.EFFORT) EFFORT
      FROM KRSC_DAILY_PLANS2_V kdpv,KNTA_USERS ku
      WHERE kdpv.USER_ID = ku.USER_ID AND kdpv.GROUP_ID  = 30282 AND kdpv.EFFORT    > 0
      GROUP BY ku.FULL_NAME,kdpv.USER_ID,kdpv.CALENDAR_DAY
      ORDER BY ku.FULL_NAME,kdpv.USER_ID,kdpv.CALENDAR_DAY
      ) tablo1,
      KRML_CALENDAR_DAYS kcd,KNTA_PERIODS kp
    WHERE TO_DATE(tablo1.DAY,'DD/MM/YYYY') = to_DATE(kcd.CALENDAR_DATE,'DD/MM/YYYY') AND kp.PERIOD_TYPE  = 'WEEK' AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
    GROUP BY tablo1.FULL_NAME,tablo1.USER_ID,tablo1.S_A, tablo1.COLOR,kp.START_DATE
    UNION ALL
    SELECT ku.FULL_NAME RESOURCE_NAME,tts.RESOURCE_ID USER_ID, ('2.Oper.Talep - Gerçekleşen') S_A,('DARKRED') COLOR,to_date(kp.START_DATE,'DD/MM/YYYY') WEEK,SUM(tsl.ACTUAL_EFFORT) EFFORT_TOTAL
    FROM TM_TIME_SHEETS tts
    JOIN
      (SELECT ttsl.TIME_SHEET_ID,ttsl.TIME_SHEET_LINE_ID,ttsl.WORK_ITEM_ID,act.ACTUALS_ID,act.ACTUAL_EFFORT
      FROM TM_TIME_SHEET_LINES ttsl
      JOIN
        (SELECT ta.TIME_SHEET_LINE_ID,ta.ACTUALS_ID,tae.ACTUAL_EFFORT
        FROM TM_ACTUALS ta
        JOIN TM_ACTUALS_EFFORT tae ON ta.ACTUALS_ID=tae.ACTUALS_ID
        WHERE tae.ACTUAL_EFFORT <> 0 AND ta.TOTALS_FLAG  = 'Y') act ON ttsl.TIME_SHEET_LINE_ID = act.TIME_SHEET_LINE_ID
      WHERE ttsl.WORK_ITEM_SET_ID = 30283) tsl ON tts.TIME_SHEET_ID = tsl.TIME_SHEET_ID
    JOIN KTMG_PERIODS kp ON tts.PERIOD_ID = kp.PERIOD_ID
    JOIN KNTA_USERS ku ON ku.USER_ID = tts.RESOURCE_ID
    GROUP BY tts.RESOURCE_ID,ku.FULL_NAME,kp.START_DATE
    UNION ALL
    SELECT tablo1.FULL_NAME RESOURCE_NAME,tablo1.USER_ID,('1.Oper.Talep - Planlanan') S_A,('LIGHTSALMON') COLOR,to_date(tablo1.StartDate,'DD/MM/YYYY') WEEK,SUM(tablo1.SCHEDULED_EFFORT) EFFORT_TOTAL
    FROM
      (SELECT kfwi.REQUEST_ID,krt.REQUEST_TYPE_NAME,ku.FULL_NAME,ku.USER_ID,TO_DATE(Calendar1.StartDate,'DD/MM/YYYY') StartDate,kfwi.SCHEDULED_EFFORT
      FROM KCRT_FG_WORK_ITEMS kfwi,KCRT_REQUESTS kr,KNTA_USERS ku,KCRT_REQUEST_TYPES krt,
        (SELECT to_Date(kcd.CALENDAR_DATE,'DD/MM/YYYY') CalendarDate,to_DATE(kp.START_DATE,'DD/MM/YYYY') StartDate
        FROM KRML_CALENDAR_DAYS kcd,KNTA_PERIODS kp
        WHERE kp.PERIOD_TYPE = 'WEEK'
        AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
       ) Calendar1
      WHERE kfwi.REQUEST_TYPE_ID = '30283' AND kfwi.REQUEST_ID = kr.REQUEST_ID AND ku.USER_ID = kr.ASSIGNED_TO_USER_ID AND kfwi.REQUEST_TYPE_ID = krt.REQUEST_TYPE_ID
      AND TO_DATE(kfwi.USR_SCHEDULED_FINISH_DATE,'DD/MM/YYYY') = TO_DATE(Calendar1.CalendarDate,'DD/MM/YYYY')
      ORDER BY kfwi.REQUEST_ID DESC
      ) tablo1
    LEFT JOIN
      (SELECT kfwi.REQUEST_ID,krt.REQUEST_TYPE_NAME,ku.FULL_NAME,ku.USER_ID,to_date(Calendar1.StartDate,'DD/MM/YYYY') StartDate,kfwi.ACTUAL_EFFORT
      FROM KCRT_FG_WORK_ITEMS kfwi, KCRT_REQUESTS kr,KNTA_USERS ku,KCRT_REQUEST_TYPES krt,
        (SELECT to_date(kcd.CALENDAR_DATE,'DD/MM/YYYY') CalendarDate,to_date(kp.START_DATE,'DD/MM/YYYY') StartDate
        FROM KRML_CALENDAR_DAYS kcd,KNTA_PERIODS kp
        WHERE kp.PERIOD_TYPE = 'WEEK' AND kcd.SEQ_PERIOD_TYPE_3 = kp.SEQ
        ) Calendar1
      WHERE kfwi.REQUEST_TYPE_ID = '30283' AND kfwi.REQUEST_ID = kr.REQUEST_ID AND ku.USER_ID = kr.ASSIGNED_TO_USER_ID AND kfwi.REQUEST_TYPE_ID = krt.REQUEST_TYPE_ID
      AND TO_DATE(kfwi.USR_ACTUAL_FINISH_DATE,'DD/MM/YYYY') = TO_DATE(Calendar1.CalendarDate,'DD/MM/YYYY')
      ORDER BY kfwi.REQUEST_ID DESC
      ) tablo2
    ON tablo1.REQUEST_ID = tablo2.REQUEST_ID
    GROUP BY tablo1.FULL_NAME,tablo1.USER_ID,tablo1.StartDate
    ) tablo
  ORDER BY WEEK ASC,
    S_A ASC

Utkarsh_Mishra
Honored Contributor

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

can you please share thie view code... KRSC_DAILY_PLANS2_V

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
bugra
Super Collector

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

Sure.. Here is the code

 

CREATE OR REPLACE FORCE VIEW "PPMPROD_USR"."KRSC_DAILY_PLANS2_V" ("CALENDAR_DAY", "SEQ_PERIOD_TYPE_1", "SEQ_PERIOD_TYPE_2", "SEQ_PERIOD_TYPE_3", "SEQ_PERIOD_TYPE_4", "USER_ID", "SKILL_ID", "WORK_ITEM_ENTITY_ID", "WORK_ITEM_NAME", "WORK_ITEM_ID", "GROUP_ENTITY_ID", "GROUP_NAME", "GROUP_ID", "WORKLOAD_CATEGORY_CODE", "WORKLOAD_FLAG", "PLANNED_EFFORT", "VISIBLE_PARAMETER1", "EFFORT")
AS
  SELECT dp.CALENDAR_DAY,
    dp.SEQ_PERIOD_TYPE_1,
    dp.SEQ_PERIOD_TYPE_2,
    dp.SEQ_PERIOD_TYPE_3,
    dp.SEQ_PERIOD_TYPE_4,
    dp.USER_ID,
    dp.SKILL_ID,
    dp.WORK_ITEM_ENTITY_ID,
    dp.WORK_ITEM_NAME,
    dp.WORK_ITEM_ID,
    dp.GROUP_ENTITY_ID,
    dp.GROUP_NAME,
    dp.GROUP_ID,
    dp.WORKLOAD_CATEGORY_CODE,
    dp.WORKLOAD_FLAG,
    dp.PLANNED_EFFORT,
    rhd.VISIBLE_PARAMETER1,
    CASE
      WHEN rhd.VISIBLE_PARAMETER1 IS NULL
      THEN dp.PLANNED_EFFORT
      WHEN dp.CALENDAR_DAY > TO_DATE(rhd.VISIBLE_PARAMETER1,'YYYY/MM/DD')
      THEN 0
      WHEN dp.CALENDAR_DAY <= TO_DATE(rhd.VISIBLE_PARAMETER1,'YYYY/MM/DD')
      THEN dp.PLANNED_EFFORT
      ELSE 0
    END EFFORT
  FROM KRSC_DAILY_PLANS_V dp
  JOİN KCRT_REQ_HEADER_DETAILS rhd
  ON rhd.REQUEST_ID = dp.WORK_ITEM_ID ;

Utkarsh_Mishra
Honored Contributor
Solution

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

From your query I had removed the condition for WORK_ITEM_SET_ID and REQUEST_TYPE_ID (commented)..

 

 

And for me it is displaying the data without any error.

 

So now the only possibility is that some of the request is having the corrupt data.

 

You need to identfy such data..... I would suggest to include this condition..

 

ROWNUM <50

 

example... where rownum = 1  ORDER BY WEEK ASC,    S_A ASC

 

then gradually increase.. if for 50 also error is coming then... set it to lesser values

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Celil
Esteemed Contributor

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

Hi Buğra,

 

I have run your 2 view and portlet sql. They have got some errors which are not same with yours but the reason maybe difference between your and my PPM versions.

 

I think there are three possible root cause for the error; 

  1. rhd.VISIBLE_PARAMETER1 field may not return date for all time in KRSC_DAILY_PLANS2_V. Please check it with below sql. select distinct VISIBLE_PARAMETER1 from KCRT_REQ_HEADER_DETAILS
  2. dp.PLANNED_EFFORT field may not return a number value for all time in KRSC_DAILY_PLANS2_V too. Please check it with same way and please note that null is recognized like char.
  3. TO_DATE(rhd.VISIBLE_PARAMETER1,'YYYY/MM/DD') is in different format in KRSC_DAILY_PLANS2_V as from TO_DATE(kp.START_DATE,'DD/MM/YYYY')  in AL_V

Celil

Celil

IT Governance Professional
& PPM Solution Architect
bugra
Super Collector

Re: Portlet SQL Error - ORA - 01841 (full) year must be between -4713 and +9999, and not be 0

 

 

I cannot try solutions because of i cannot reach PPM outside the office, now I tested it and it works.There are some problems which is written by Celil but main problem is solved and now portlet works successfully.

 

Thanks for all answers, code is worked correctly.

//Add this to "OnDomLoad" event