hi experts

     SELECT   porh.segment1,
     CASE
        WHEN popr.action_code = 'APPROVE'
        AND prall.full_name <> 'Sudheer T. Paraputhra'
        AND grdpr.short_name < 'PS-10'
           THEN prall.full_name
     END directman_pr,[QUOTE]to get the dept.head[/QUOTE]
     porh.description, porl.line_num,
     CASE
        WHEN popr.action_code = 'APPROVE'
        AND prall.full_name <> 'Sudheer T. Paraputhra'
        AND grdpr.short_name < 'PS-10'
        AND poph.action_code = 'SUBMIT'
           THEN TO_NUMBER (  TO_CHAR (  TO_DATE ('1', 'J')
                                      + (  poph.action_date
                                         - popr.action_date
                                        ),
                                      'J'
                                     )
                           - 1
                          )
     END days_pr,[QUOTE]number of days from PR approve action by dept.head to PO submit action by user[/QUOTE]
     poha.segment1 po_num, prall.full_name pr_approved_by,
     popr.action_date pr_approved_date,
     CASE
        WHEN poall_apprv.full_name <> 'Sudheer T. Paraputhra'
        AND grdpo_apprv.short_name < 'PS-10'
        AND poph_apprv.action_code = 'APPROVE'
        AND poph.action_code = 'SUBMIT'
           THEN TO_NUMBER (  TO_CHAR (  TO_DATE ('1', 'J')
                                      + (  poph_apprv.action_date
                                         - poph.action_date
                                        ),
                                      'J'
                                     )
                           - 1
                          )
     END days_po,[QUOTE]number of days from PO submit action to po apporve by dept head[/QUOTE]
     popr.sequence_num pr_seq, popr.action_code pr_action,
     poall.full_name po_approved_by, poph.action_date po_approved_date,
     poph.sequence_num po_seq, poph.action_code po_action,
     poph_apprv.action_date po_approved_date_appr,
     poph_apprv.action_code apprv_action_code
FROM po_requisition_headers_all porh,
     po_requisition_lines_all porl,
     po_req_distributions_all prda,
     po_distributions_all poda,
     po_headers_all poha,
     po_action_history popr,
     po_action_history poph,
     hr.per_all_people_f poall,
     hr.per_all_people_f prall,
     hr.per_all_assignments_f asgpo,
     hr.per_all_assignments_f asgpr,
     per_grades_vl grdpo,
     per_grades_vl grdpr,
     po_headers_all poha_apprv,
     po_distributions_all poda_apprv,
     po_action_history poph_apprv,
     hr.per_all_people_f poall_apprv,
     hr.per_all_assignments_f asgpo_apprv,
     per_grades_vl grdpo_apprv
WHERE poph.employee_id = poall.person_id
 AND popr.employee_id = prall.person_id
 AND poall.person_id = asgpo.person_id
 AND prall.person_id = asgpr.person_id
 AND asgpr.grade_id = grdpr.grade_id(+)
 AND asgpo.grade_id = grdpo.grade_id(+)
 AND prda.distribution_id = poda.req_distribution_id
 AND poha.po_header_id = poda.po_header_id
 AND prda.requisition_line_id = porl.requisition_line_id
 AND porh.requisition_header_id = porl.requisition_header_id
 AND porh.requisition_header_id = popr.object_id
 AND poph.object_type_code = 'PO'
 AND poha.po_header_id = poph.object_id
 AND SYSDATE BETWEEN poall.effective_start_date AND poall.effective_end_date
 AND SYSDATE BETWEEN prall.effective_start_date AND prall.effective_end_date
 AND SYSDATE BETWEEN asgpo.effective_start_date AND asgpo.effective_end_date
 AND SYSDATE BETWEEN asgpr.effective_start_date AND asgpr.effective_end_date
 AND SYSDATE BETWEEN poall_apprv.effective_start_date
                 AND poall_apprv.effective_end_date
 AND SYSDATE BETWEEN asgpo_apprv.effective_start_date
                 AND asgpo_apprv.effective_end_date
 AND prda.distribution_id = poda_apprv.req_distribution_id
 AND poha_apprv.po_header_id = poda_apprv.po_header_id
 AND poph_apprv.employee_id = poall_apprv.person_id
 AND poall_apprv.person_id = asgpo_apprv.person_id
 AND asgpo_apprv.grade_id = grdpo_apprv.grade_id(+)
 AND poha_apprv.po_header_id = poph_apprv.object_id
 AND poph_apprv.object_type_code = 'PO'
--- AND PORH.SEGMENT1='6003'
 AND poph_apprv.action_code = 'APPROVE'
ORDER BY 4, 10, 14

if the AND PORH.SEGMENT1 is uncommented i get no error but i need to comment it in order to get the number of days displayed for pr and po

kindly help

Recommended Answers

All 3 Replies

It is really difficult to understand why ORA-01841 is be raised in your case , without knowing your table structure.

Please be specific about your problem. What problem you are facing? And why you have embedded html code in your query code.

Please be specific about your problem. What problem you are facing? And why you have embedded html code in your query code.

hi
i m new to this dint know how to post comments:$ which is why i put them as quotes

thanks
the issue is solved :)
it was a julian date issue

changed to
to_date(poph.action_date)-to_date(popr.action_date)

thank u all

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.