Home » Applications » Oracle Fusion Apps & E-Business Suite » Getting ORA-01841: (full) year must be between -4713 and +9999 in hierarchichal query (R12 Oracle Apps,Windows)
Getting ORA-01841: (full) year must be between -4713 and +9999 in hierarchichal query [message #550582] Tue, 10 April 2012 10:42
Messages: 110
Registered: September 2008
Location: India
Senior Member
Dear Friends,

I have a query which is giving error :

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

The requirement is to retrieve the organization plus its sub organizations.
Parameters are organization_id and year
The issue seems to be in hierarchical part of the query.

However, if I pass the organization id's directly(hardcode) in the 'IN' clause of the hierarchical queryas shown below,it retrieves the correct result.

Also, the hierarchical query retrives the correct values if run alone.

AND haou.organization_id IN (97,91,140,112) 
                                 AND paaf.organization_id = haou.organization_id

Please find my complete code with this post below:
SELECT   TO_CHAR (TO_DATE (pac.segment2, 'rrrr/mm/dd HH24:MI:SS'),
                         ) MONTH,
                 COUNT (pac.segment1) action_taken
            --to_char(to_date(pac.segment2,'yyyy/mm/dd HH24:MI:SS'),'yyyy') Year
        FROM     per_all_people_f papf,
                 per_all_assignments_f paaf,
                 hr_all_organization_units haou,
                 per_person_analyses ppa,
                 per_analysis_criteria pac
           WHERE papf.person_id = ppa.person_id
             AND paaf.person_id = ppa.person_id
             AND papf.person_id = paaf.person_id
             AND papf.business_group_id=haou.business_group_id
             -- AND paaf.organization_id = haou.organization_id
             AND haou.organization_id IN (SELECT distinct org.organization_id
                                 hr_all_organization_units org,
                                 per_org_structure_elements pose
                                 WHERE 1=1
                                 AND org.organization_id = pose.organization_id_child
                                 --AND pose.org_structure_version_id = 3067
                                 --and org.name  like '201.Financiale Services'
                                 START WITH
                                 pose.organization_id_parent = :org_id   -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
                                 CONNECT BY PRIOR
                                 pose.organization_id_child = pose.organization_id_parent
                                 AND paaf.organization_id = haou.organization_id
             AND ppa.id_flex_num = pac.id_flex_num
             AND ppa.id_flex_num IN (
                    SELECT DISTINCT id_flex_num
                               FROM fnd_id_flex_structures_vl
                              WHERE id_flex_structure_code LIKE
                     -- If name is not there i will add ID_FLEX_STRUCTURE_CODE
                                AND id_flex_code = 'PEA'
                                AND application_id = 800)
             AND ppa.analysis_criteria_id = pac.analysis_criteria_id
             AND to_char(TO_DATE(pac.segment2,'rrrr/mm/dd HH24:MI:SS'),'rrrr') = :yr
             AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                     AND papf.effective_end_date
             AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                     AND paaf.effective_end_date
             AND pac.segment7 = 'Y'
        GROUP BY TO_CHAR (TO_DATE (pac.segment2, 'rrrr/mm/dd HH24:MI:SS'),


[Updated on: Tue, 10 April 2012 11:40]

Report message to a moderator

Previous Topic: Proxy error
Goto Forum:

Current Time: Sat Jul 26 08:21:55 CDT 2014

Total time taken to generate the page: 0.10141 seconds